example JDBCSQL

options
	jdbc_quote_char = "" 
	always_reload = true 
	#jdbc_default_string = "jdbc:postgresql://localhost:5432/postgres?user=postgres&password=password"
	#docker run --rm --name cql-fun -p 5432:5432 -e POSTGRES_PASSWORD=password -d postgres 
	#to try postgres, be sure to replace the jdbcURL for the load command directly below
	
#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 CASCADE;
 DROP TABLE IF EXISTS Department CASCADE;
 DROP TABLE IF EXISTS ExportedEmployee CASCADE;
 DROP TABLE IF EXISTS ExportedDepartment CASCADE;
 DROP TABLE IF EXISTS ExportedTransEmployee CASCADE;
 DROP TABLE IF EXISTS ExportedTransDepartment CASCADE;" 

#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)"

"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, 'lname' 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"
}


##########
#direct method to land sql data

schema S_direct2 = import_jdbc_all "" {
 options allow_sql_import_all_unsafe=true
}

schema S_direct = literal : sql {
	entities
		Employee 
		Department
  	attributes
		manager  name : Employee -> Other
		worksIn   : Employee -> Other
		secretary : Department -> Other
  		name 	  : Department -> Other
 }

instance I = import_jdbc_direct "" "ROW_NUMBER() OVER ()" : S_direct 
instance J2 = import_jdbc_direct "" "ROW_NUMBER() OVER ()" : S_direct2 
##############################
#
#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
}

instance J

instance I

instance J2

command load

START
DROP TABLE IF EXISTS Employee CASCADE;
 DROP TABLE IF EXISTS Department CASCADE;
 DROP TABLE IF EXISTS ExportedEmployee CASCADE;
 DROP TABLE IF EXISTS ExportedDepartment CASCADE;
 DROP TABLE IF EXISTS ExportedTransEmployee CASCADE;
 DROP TABLE IF EXISTS ExportedTransDepartment CASCADE;

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

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 store1

Exported 5 rows.

command view1

START
SELECT * FROM ExportedEmployee

ID 100,  MANAGER 100,  WORKSIN 103,  AGE 103,  FIRST Andrey,  LAST lname
ID 101,  MANAGER 101,  WORKSIN 104,  AGE 102,  FIRST Camille,  LAST lname
ID 102,  MANAGER 100,  WORKSIN 103,  AGE 101,  FIRST Alan,  LAST lname
END

START
SELECT * FROM ExportedDepartment

ID 103,  SECRETARY 102,  NAME Applied Math
ID 104,  SECRETARY 101,  NAME Pure Math
END

command store2

Exported 10 rows.

command view2

START
SELECT * FROM ExportedTransEmployee

SRCID 100,  DSTID 100
SRCID 101,  DSTID 101
SRCID 102,  DSTID 102
END

START
SELECT * FROM ExportedTransDepartment

SRCID 103,  DSTID 103
SRCID 104,  DSTID 104
END

command store_id_query

export_jdbc_query drop view if exists ViewDepartment

create view ViewDepartment as   select concat(concat('(d=', concat(cast(d.id as varchar(256)), ')')), concat('(unfold1=', concat(cast(unfold1.id as varchar(256)), ')'))) as id, d.name as name, concat(concat(concat('(e=', concat(cast(cast(unfold1.id as varchar(256)) as varchar(256)), ')')), concat('(unfold0=', concat(cast(cast(unfold1.manager as varchar(256)) as varchar(256)), ')'))), concat('(unfold2=', concat(cast(cast(unfold1.worksIn as varchar(256)) as varchar(256)), ')'))) 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(cast(e.id as varchar(256)), ')')), concat('(unfold0=', concat(cast(unfold0.id as varchar(256)), ')'))), concat('(unfold2=', concat(cast(unfold2.id as varchar(256)), ')'))) as id, unfold0.first as first, unfold2.name as last, e.age as age, concat(concat(concat('(e=', concat(cast(cast(unfold0.id as varchar(256)) as varchar(256)), ')')), concat('(unfold0=', concat(cast(cast(unfold0.manager as varchar(256)) as varchar(256)), ')'))), concat('(unfold2=', concat(cast(cast(unfold0.worksIn as varchar(256)) as varchar(256)), ')'))) as manager, concat(concat('(d=', concat(cast(cast(unfold2.id as varchar(256)) as varchar(256)), ')')), concat('(unfold1=', concat(cast(cast(unfold2.secretary as varchar(256)) as varchar(256)), ')'))) as worksIn
from ExportedEmployee as e, ExportedEmployee as unfold0, ExportedDepartment as unfold2
  where unfold0.id = e.manager and unfold2.id = e.worksIn

command view_view

START
SELECT * FROM ViewEmployee

ID (e=100)(unfold0=100)(unfold2=103),  FIRST Andrey,  LAST Applied Math,  AGE 103,  MANAGER (e=100)(unfold0=100)(unfold2=103),  WORKSIN (d=103)(unfold1=102)
ID (e=101)(unfold0=101)(unfold2=104),  FIRST Camille,  LAST Pure Math,  AGE 102,  MANAGER (e=101)(unfold0=101)(unfold2=104),  WORKSIN (d=104)(unfold1=101)
ID (e=102)(unfold0=100)(unfold2=103),  FIRST Andrey,  LAST Applied Math,  AGE 101,  MANAGER (e=100)(unfold0=100)(unfold2=103),  WORKSIN (d=103)(unfold1=102)
END

START
SELECT * FROM ViewDepartment

ID (d=103)(unfold1=102),  NAME Applied Math,  SECRETARY (e=102)(unfold0=100)(unfold2=103)
ID (d=104)(unfold1=101),  NAME Pure Math,  SECRETARY (e=101)(unfold0=101)(unfold2=104)
END

START
DROP VIEW ViewEmployee

Updated 0 rows.
END

START
DROP VIEW ViewDepartment

Updated 0 rows.
END