example JDBCSQL

//create some example data in a local temporary sql database. (close delay keeps H2 database alive across connections)
command load = exec_jdbc "jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1" { 
"
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;" //multiple statements can be used, but many SQL engines (such as H2, CQL's internal engine) will only print the output of the first statement, and some require ?allowMultiQueries=true in the jdbc string 

"
CREATE TABLE Employee(
 id INT PRIMARY KEY,
 name VARCHAR(255),
 manager INT, 
 worksIn INT
)"

//a bug in the library for CQL's code editor prevents the correct highlighting of 
//multi-line quotes.  Starting the text on the line after the initial quote helps readability.
"
CREATE TABLE Department(
 id INT PRIMARY KEY,
 name VARCHAR(255),
 secretary INT,
)"
 
"
INSERT INTO Employee VALUES 
 (101, 'Alan', 103, 10), 
 (102, 'Camille', 102, 2), 
 (103, 'Andrey', 103, 10)"

"
INSERT INTO Department VALUES
 (10, 'Applied Math', 101),
 (2, 'Pure Math', 102)"

"
ALTER TABLE Employee ADD CONSTRAINT e1
 FOREIGN KEY (manager) REFERENCES Employee (id)"

"
ALTER TABLE Employee ADD CONSTRAINT e2 
 FOREIGN KEY (worksIn) REFERENCES Department (id)"

"
ALTER TABLE Department ADD CONSTRAINT d1
 FOREIGN KEY (secretary) REFERENCES Employee (id)"

// options always_reload = true //true forces pragmas to not be cached between runs (i.e., always run)
}  
 
/* When the JDBC class name and string are left blank, CQL uses the value of the option
    jdbc_default_string.  These can be set globally by placing,
   at the beginning of the file, for example:
   
  options
   
    jdbc_default_string = "jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1"
 */
//look at the example data
command view0 = exec_jdbc "" {  //see note above!!!
"SELECT * FROM Department"
"SELECT * FROM Employee"
}

////////////////////////////////////////////////////////////////////////////////////////////////////////

schema S = literal : sql {
	entities
		Employee 
		Department
	foreign_keys
		manager   : Employee -> Employee
		worksIn   : Employee -> Department
		secretary : Department -> Employee
	path_equations 
		Employee.manager.worksIn = Employee.worksIn
  		Department.secretary.worksIn = Department
  		Employee.manager.manager = Employee.manager
  	attributes
  		first last	: Employee -> Varchar
     	age			: Employee -> Integer
     	name 		: Department -> Varchar
 }

//import an instance by providing queries for each entity
instance J = import_jdbc ""  : S {
//use name as first name
  //use null as last name
  //use id as age	
	Employee -> "SELECT id, manager, worksIn, name AS first, NULL as last, id AS age FROM Employee"
	Department -> "SELECT id, secretary, name FROM Department"

	options
		//aql by default prepends the entity to each imported ID, to get uniqueness across entities.
		//to import the IDs verbatim, set the option below to false
		prepend_entity_on_ids = false
}



//import a transform by providing queries for each entity
transform Jid = import_jdbc ""  : J -> J {
	Employee -> "SELECT id, id FROM Employee"
	Department -> "SELECT id, id FROM Department"

	options
		prepend_entity_on_ids = false
}

//export the instance to SQL
command store1 = export_jdbc_instance J ""  "Exported"
{ options start_ids_at = 100 }

//view exported SQL instance
command view1 = exec_jdbc ""  { 
	"SELECT * FROM \"ExportedEmployee\""
	"SELECT * FROM \"ExportedDepartment\""
}

//export the transform to SQL
command store2 = export_jdbc_transform Jid ""  "ExportedTrans" 
 { options start_ids_at = 100 } //src
 { options start_ids_at = 100 } //dst

//view the exported SQL transform
command view2 = exec_jdbc ""  { 
	"SELECT * FROM ExportedTransEmployee"
	"SELECT * FROM ExportedTransDepartment"
}

//to import all the data in a SQL DB at once onto an auto-generated schema, use the following:
instance import_all_inst = import_jdbc_all "" 
//the schema can be obtained as
schema import_all_schema = schemaOf import_all_inst
//or
schema import_all_schema_2 = import_jdbc_all "" 


////////////////////////////////////////////////////////////
//
//execute command line actions as follows
//pragma cmdline1 = exec_cmdline  { 
//"ls -ltr"
//"echo hi"
//}
//
//execute actions as follows
//pragma js1 = exec_js  { 
//"javax.swing.JOptionPane.showMessageDialog(null, \"hello1\")"
//"javax.swing.JOptionPane.showMessageDialog(null, \"hello2\")"
//}

//store query evaluation as a view

query Q = literal : S -> S {
	entity
		Employee -> 
		{from e:Employee d:Department
		 where e.worksIn = d
		 attributes first -> e.manager.first 
		        last -> d.name 
		        age -> e.age
		        foreign_keys manager -> {e -> e.manager
		            d -> e.manager.worksIn}
		worksIn -> {d -> e.worksIn}
		}
		
		entity Department -> {from d:Department 
		               attributes name -> d.name
		               foreign_keys secretary -> {e -> d.secretary 
		              d -> d}}
		
	
		
}

command store_id_query = export_jdbc_query Q "" "Exported" "View"

command view_view = exec_jdbc ""  { 
	"SELECT * FROM \"ViewEmployee\""
	"SELECT * FROM \"ViewDepartment\""
	"DROP VIEW \"ViewEmployee\""
	"DROP VIEW \"ViewDepartment\"" //clean up is important when re-running the program
}
Keywords:

query_literal
schema_var
exec_jdbc
schemaOf
import_jdbc
export_jdbc_query
import_jdbc
export_jdbc_transform
export_jdbc_instance
import_jdbc_all
import_jdbc_all

Options:

prepend_entity_on_ids
start_ids_at



instance import_all_inst

DEPARTMENT
IDIDNAMESECRETARYDEPARTMENT__SECRETARY__EMPLOYEE_ID
010Applied Math1014
12Pure Math1023
EMPLOYEE
IDIDMANAGERNAMEWORKSINEMPLOYEE__MANAGER__EMPLOYEE_IDEMPLOYEE__WORKSIN__DEPARTMENT_ID
2103103Andrey1020
3102102Camille231
4101103Alan1020
EXPORTEDTRANSDEPARTMENT
IDDSTIDSRCID
5101101
6100100
EXPORTEDTRANSEMPLOYEE
IDDSTIDSRCID
7103103
8102102
9104104
ExportedDepartment
IDidnamesecretaryExportedDepartment__secretary__ExportedEmployee_id
10100Pure Math10413
11101Applied Math10314
ExportedEmployee
IDagefirstidlastmanagerworksInExportedEmployee__manager__ExportedEmployee_idExportedEmployee__worksIn__ExportedDepartment_id
12103Andrey102?01021011211
13102Camille104?11041001310
14101Alan103?21021011211


instance J

Department
IDnamesecretary
0Pure Math3
1Applied Math2
Employee
IDagefirstlastmanagerworksIn
2101Alan?041
3102Camille?130
4103Andrey?241


command view_view

START
SELECT * FROM "ViewEmployee"

id (e=102)(unfold2=101)(unfold0=102),  first Andrey,  last Applied Math,  age 103,  worksIn (d=101)(unfold1=103),  manager (e=102)(unfold2=101)(unfold0=102)
id (e=103)(unfold2=101)(unfold0=102),  first Andrey,  last Applied Math,  age 101,  worksIn (d=101)(unfold1=103),  manager (e=102)(unfold2=101)(unfold0=102)
id (e=104)(unfold2=100)(unfold0=104),  first Camille,  last Pure Math,  age 102,  worksIn (d=100)(unfold1=104),  manager (e=104)(unfold2=100)(unfold0=104)
END

START
SELECT * FROM "ViewDepartment"

id (d=100)(unfold1=104),  name Pure Math,  secretary (e=104)(unfold2=100)(unfold0=104)
id (d=101)(unfold1=103),  name Applied Math,  secretary (e=103)(unfold2=101)(unfold0=102)
END

START
DROP VIEW "ViewEmployee"

Updated 0 rows.
END

START
DROP VIEW "ViewDepartment"

Updated 0 rows.
END

command view2

START
SELECT * FROM ExportedTransEmployee

SRCID 102,  DSTID 102
SRCID 103,  DSTID 103
SRCID 104,  DSTID 104
END

START
SELECT * FROM ExportedTransDepartment

SRCID 100,  DSTID 100
SRCID 101,  DSTID 101
END

command store_id_query

export_jdbc_query drop view if exists "ViewDepartment"

create view "ViewDepartment" as   select concat(concat('(d=', concat(convert(d."id", varchar), ')')), concat('(unfold1=', concat(convert(unfold1."id", varchar), ')'))) as "id", d."name" as "name", concat(concat(concat('(e=', concat(convert(convert(unfold1."id", varchar), varchar), ')')), concat('(unfold2=', concat(convert(convert(unfold1."worksIn", varchar), varchar), ')'))), concat('(unfold0=', concat(convert(convert(unfold1."manager", varchar), varchar), ')'))) as "secretary"
from "ExportedDepartment" as d, "ExportedEmployee" as unfold1
  where unfold1."id" = d."secretary"

drop view if exists "ViewEmployee"

create view "ViewEmployee" as   select concat(concat(concat('(e=', concat(convert(e."id", varchar), ')')), concat('(unfold2=', concat(convert(unfold2."id", varchar), ')'))), concat('(unfold0=', concat(convert(unfold0."id", varchar), ')'))) as "id", unfold0."first" as "first", unfold2."name" as "last", e."age" as "age", concat(concat('(d=', concat(convert(convert(unfold2."id", varchar), varchar), ')')), concat('(unfold1=', concat(convert(convert(unfold2."secretary", varchar), varchar), ')'))) as "worksIn", concat(concat(concat('(e=', concat(convert(convert(unfold0."id", varchar), varchar), ')')), concat('(unfold2=', concat(convert(convert(unfold0."worksIn", varchar), varchar), ')'))), concat('(unfold0=', concat(convert(convert(unfold0."manager", varchar), varchar), ')'))) as "manager"
from "ExportedEmployee" as e, "ExportedDepartment" as unfold2, "ExportedEmployee" as unfold0
  where unfold2."id" = e."worksIn" and unfold0."id" = e."manager"

command store2

Exported 5 rows.

command store1

Exported 5 rows.

command view1

START
SELECT * FROM "ExportedEmployee"

id 102,  worksIn 101,  manager 102,  first Andrey,  last null,  age 103
id 103,  worksIn 101,  manager 102,  first Alan,  last null,  age 101
id 104,  worksIn 100,  manager 104,  first Camille,  last null,  age 102
END

START
SELECT * FROM "ExportedDepartment"

id 100,  secretary 104,  name Pure Math
id 101,  secretary 103,  name Applied Math
END

command view0

START
SELECT * FROM Department

ID 2,  NAME Pure Math,  SECRETARY 102
ID 10,  NAME Applied Math,  SECRETARY 101
END

START
SELECT * FROM Employee

ID 101,  NAME Alan,  MANAGER 103,  WORKSIN 10
ID 102,  NAME Camille,  MANAGER 102,  WORKSIN 2
ID 103,  NAME Andrey,  MANAGER 103,  WORKSIN 10
END

command load

START

DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;

Updated 0 rows.
END

START

CREATE TABLE Employee(
 id INT PRIMARY KEY,
 name VARCHAR(255),
 manager INT, 
 worksIn INT
)

Updated 0 rows.
END

START

CREATE TABLE Department(
 id INT PRIMARY KEY,
 name VARCHAR(255),
 secretary INT,
)

Updated 0 rows.
END

START

INSERT INTO Employee VALUES 
 (101, 'Alan', 103, 10), 
 (102, 'Camille', 102, 2), 
 (103, 'Andrey', 103, 10)

Updated 3 rows.
END

START

INSERT INTO Department VALUES
 (10, 'Applied Math', 101),
 (2, 'Pure Math', 102)

Updated 2 rows.
END

START

ALTER TABLE Employee ADD CONSTRAINT e1
 FOREIGN KEY (manager) REFERENCES Employee (id)

Updated 0 rows.
END

START

ALTER TABLE Employee ADD CONSTRAINT e2 
 FOREIGN KEY (worksIn) REFERENCES Department (id)

Updated 0 rows.
END

START

ALTER TABLE Department ADD CONSTRAINT d1
 FOREIGN KEY (secretary) REFERENCES Employee (id)

Updated 0 rows.
END