example Demo

// Universal Warehousing Demo

// user defined functions, in Java
typeside Type = literal { 
	java_types
		String = "java.lang.String"
	java_constants
		String = "return input[0]"
	java_functions
		toUpper : String -> String  = "return input[0].toUpperCase()"
}

///////////////////////////////////////////////////////////////////////////
// input schemas

schema AmphibianSchema = literal : Type {
	entities 
		Amphibian Animal
	foreign_keys 
		toAnimal : Amphibian -> Animal    
	attributes	
	     species : Animal -> String
}

schema LandSchema = literal : Type {
	imports
		AmphibianSchema
	entities
		LandAnimal	
	foreign_keys 
		isA : Amphibian  -> LandAnimal
		isA : LandAnimal -> Animal
	path_equations 
		Amphibian.isA.isA = Amphibian.toAnimal     
} 

schema WaterSchema = literal : Type {
	imports
		AmphibianSchema 
	entities
		WaterAnimal	
	foreign_keys 
		isA : Amphibian  -> WaterAnimal
		isA : WaterAnimal -> Animal
	path_equations 
		Amphibian.isA.isA = Amphibian.toAnimal     
} 

///////////////////////////////////////////////////////////////////////////
//input instances

instance LandInstance = import_csv "http://categoricaldata.net/demo/LandDB/" : LandSchema 

instance WaterInstance = import_csv "http://categoricaldata.net/demo/WaterDB/" : WaterSchema 

///////////////////////////////////////////////////////////////////////////.....
//compute the canonical schema colimit, then enhances its semantics

schema_colimit UniversalWarehouseSchema = quotient LandSchema + WaterSchema : Type {
	entity_equations
		LandSchema.Animal = WaterSchema.Animal
		LandSchema.Amphibian = WaterSchema.Amphibian
	path_equations
		Amphibian.LandSchema_Amphibian_toAnimal = Amphibian.WaterSchema_Amphibian_toAnimal			
}

schema_colimit ModifiedWarehouseSchema = modify UniversalWarehouseSchema {
	rename entities
		LandAnimal -> Land
		WaterAnimal -> Water
	rename foreign_keys
		Amphibian.LandSchema_Amphibian_isA -> land_is
		Amphibian.WaterSchema_Amphibian_isA -> water_is
		Amphibian.WaterSchema_Amphibian_toAnimal -> redundantW
		Amphibian.LandSchema_Amphibian_toAnimal -> redundantL
		//Water.WaterAnimal_isA -> isA
		//Land.LandAnimal_isA -> isA
	rename attributes
		Animal.LandSchema_Animal_species  -> land_species
		Animal.WaterSchema_Animal_species -> water_species	
	remove foreign_keys
		Amphibian.redundantL -> land_is	. isA
		Amphibian.redundantW -> water_is. isA
}

////////////////////////////////////////////////////////////////////////////////////////////////
// migrate the data onto the warehouse schema

schema  WarehouseSchema  = getSchema  ModifiedWarehouseSchema
mapping LandToWarehouse  = getMapping ModifiedWarehouseSchema LandSchema
mapping WaterToWarehouse = getMapping ModifiedWarehouseSchema WaterSchema

instance LandInstanceForward  = sigma LandToWarehouse LandInstance
instance WaterInstanceForward = sigma WaterToWarehouse WaterInstance

instance UnmergedWarehouse = coproduct LandInstanceForward + WaterInstanceForward : WarehouseSchema

////////////////////////////////////////////////////////////////////////////////////////////////
// merge duplicates

instance Warehouse = quotient_query UnmergedWarehouse {
	entity Amphibian -> {from a:Amphibian b:Amphibian where toUpper(a.land_is.isA.land_species) = toUpper(b.water_is.isA.water_species)}

options
	quotient_use_chase = false
}

////////////////////////////////////////////////////////////////////////////////////////////////
// export the warehouse to SQL
command storeWH = export_jdbc_instance Warehouse "" ""

//view exported SQL instance
command view1 = exec_jdbc ""  { 
	"SELECT * FROM \"Animal\""
	"SELECT * FROM \"Amphibian\""
	"SELECT * FROM \"Land\""
	"SELECT * FROM \"Water\""

	"DROP TABLE \"Animal\"" //drop tables in case we want to run the demo again
	"DROP TABLE \"Amphibian\""
	"DROP TABLE \"Land\""
	"DROP TABLE \"Water\""
}

////////////////////////////////////////////////////////////////////////////////////////////////
// Application 0 : View warehouse as graph

schema WarehouseAsGraph = pivot Warehouse

////////////////////////////////////////////////////////////////////////////////////////////////
// Application 1 : Project (round-trip) the warehouse back onto the land schema

instance RoundTripLand = delta LandToWarehouse Warehouse

transform RoundTripLandFn = unit LandToWarehouse LandInstance

////////////////////////////////////////////////////////////////////////////////////////////////
// Application 2 : Project further onto the Amphibians schema with a query

query LandToAmphibians = literal : LandSchema -> AmphibianSchema {
	entity Amphibian -> {from amp:Amphibian
	                     //where amp.toAnimal.species = "frog"
						 foreign_keys toAnimal -> {anim -> amp.toAnimal}}  
						 
	entity Animal -> {from anim:Animal
	                  //where anim.species = "frog"
				      attributes species -> anim.species} 				      
}

instance RoundTripAmphibians = eval LandToAmphibians RoundTripLand

////////////////////////////////////////////////////////////////////////////////////////////////
// Application 3 : Check and/or repair additional rules

constraints AllFksInjective = literal : WarehouseSchema {
	forall a1 a2 : Amphibian where a1.land_is  = a2.land_is  -> where a1 = a2 
	forall a1 a2 : Amphibian where a1.water_is = a2.water_is -> where a1 = a2 
	forall l1 l2 : Land      where l1.isA      = l2.isA      -> where l1 = l2
	forall w1 w2 : Water     where w1.isA      = w2.isA      -> where w1 = w2
}

command cmd = check AllFksInjective Warehouse 

instance ANonInjectiveWarehouse = literal : WarehouseSchema {
	generators
		frog1 frog2 : Amphibian
		frog : Land
	equations
		frog1.land_is = frog
		frog2.land_is = frog
}

instance RepairedInjectiveWarehouse = chase AllFksInjective ANonInjectiveWarehouse
Keywords:

coproduct
schema_literal
chase
getMapping
pivot
exec_jdbc
check
constraints_literal
quotient
getSchema
instance_literal
delta
modify
sigma
query_literal
eval
import_csv
quotient_query
export_jdbc_instance
unit
typeside_literal

Options:

quotient_use_chase



instance UnmergedWarehouse

Amphibian
IDland_iswater_is
0913
11114
21215
Animal
IDland_specieswater_species
3Gecko?0
4equine?1
5FROG?2
6?3?4
7?5frog
8?6FISH
Land
IDisA
93
104
115
127
Water
IDisA
133
145
157
168


instance WaterInstanceForward

Amphibian
IDland_iswater_is
034
Animal
IDland_specieswater_species
1?0frog
2?1FISH
Land
IDisA
31
Water
IDisA
41
52


instance WaterInstance

Amphibian
IDisAtoAnimal
042
Animal
IDspecies
1FISH
2frog
WaterAnimal
IDisA
31
42


instance RoundTripLand

Amphibian
IDisAtoAnimal
073
192
Animal
IDspecies
2Gecko
3FROG
4equine
5?0
6?1
LandAnimal
IDisA
73
84
92


instance RepairedInjectiveWarehouse

Amphibian
IDland_iswater_is
023
Animal
IDland_specieswater_species
1?0?1
Land
IDisA
21
Water
IDisA
31


instance ANonInjectiveWarehouse

Amphibian
IDland_iswater_is
034
135
Animal
IDland_specieswater_species
2?0?1
Land
IDisA
32
Water
IDisA
42
52


instance LandInstanceForward

Amphibian
IDland_iswater_is
069
1810
Animal
IDland_specieswater_species
2Gecko?0
3equine?1
4FROG?2
5?3?4
Land
IDisA
62
73
84
Water
IDisA
92
104


instance RoundTripAmphibians

Amphibian
IDtoAnimal
03
12
Animal
IDspecies
2Gecko
3FROG
4equine
5?0
6?1


instance LandInstance

Amphibian
IDisAtoAnimal
063
185
Animal
IDspecies
2?0
3Gecko
4equine
5FROG
LandAnimal
IDisA
63
74
85


instance Warehouse

Amphibian
IDland_iswater_is
0710
1911
Animal
IDland_specieswater_species
2Gecko?0
3FROGfrog
4equine?1
5?2FISH
6?3?4
Land
IDisA
73
84
92
Water
IDisA
103
112
125


command storeWH

Exported 13 rows.

command cmd

Satisfies

command view1

START
SELECT * FROM "Animal"

id 3,  land_species null,  water_species null
id 4,  land_species null,  water_species FISH
id 5,  land_species equine,  water_species null
id 6,  land_species FROG,  water_species frog
id 7,  land_species Gecko,  water_species null
END

START
SELECT * FROM "Amphibian"

id 11,  water_is 9,  land_is 0
id 12,  water_is 10,  land_is 2
END

START
SELECT * FROM "Land"

id 0,  isA 7
id 1,  isA 5
id 2,  isA 6
END

START
SELECT * FROM "Water"

id 8,  isA 4
id 9,  isA 7
id 10,  isA 6
END

START
DROP TABLE "Animal"

Updated 0 rows.
END

START
DROP TABLE "Amphibian"

Updated 0 rows.
END

START
DROP TABLE "Land"

Updated 0 rows.
END

START
DROP TABLE "Water"

Updated 0 rows.
END