-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql.html
107 lines (91 loc) · 4.7 KB
/
sql.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
<!DOCTYPE HTML>
<html lang="en">
<head>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-CD4ENCFV58"></script>
<!-- Google tag (gtag.js) -->
<script async src="https://www.googletagmanager.com/gtag/js?id=G-64DRFX06T1"></script>
<script >
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'G-64DRFX06T1');
</script>
<title>CQL</title>
<link rel="shortcut icon" href="../favicon.ico" >
<link rel="StyleSheet" href="css/nstyle.css" type="text/css" media="all" >
<meta charset="utf-8">
<meta name="keywords" content="CQL,SQL,Data Integration, Data Migration, Category Theory, ETL" >
<meta name="description" content="Conexus CQL" >
<meta name="keywords" content="CQL, functorial, category theory, data integration, data migration, categorical databases, SQL, categorical query language" >
</head>
<body>
<div id="content">
<h1>Categorical Databases<img src="logo.png" height="32" style="float: right;" alt="logo" ></h1>
<a href="https://categoricaldata.net">Home</a> |
<a href="download.html">Download</a> |
<a href="examples.html">Getting Started</a> |
<a href="help/index.html" target="_blank">Manual</a> |
<a href="https://github.com/CategoricalData/CQL/wiki" target="_blank">Wiki</a> |
<a href="papers.html">Papers</a> |
<a href="screens.html">Screen Shots</a> |
<a href="https://github.com/categoricalData" target="_blank">Github</a> |
<a href="https://groups.google.com/forum/#!forum/categoricaldata" target="_blank">Google Group</a> |
<a href="https://conexus.com" target="_blank">Conexus</a> |
<a href="mailto:[email protected]">Contact</a>
<br><br>
<hr>
<h2>SQL Import/Export Quick Start</h2>
<p> This example is recommended for anyone dealing with SQL data and is built-in to the IDE as QuickSQL. It imports a cloud-based MySQL database about employees and departments into CQL, demonstrates basic CQL operations, and then exports the data back into the MySQL database. Accessing any SQL database management system in CQL requires adding the vendor's JDBC driver to the java classpath when running CQL; this example requires downloading <a href="https://dev.mysql.com/downloads/connector/j/5.1.html">MySQL connector-j version 5.1.47</a> and running CQL from the terminal in a directory containing both jar files (on Windows, replace <code>:</code> by <code>;</code>):</p>
<pre>
java -cp "cql.jar:mysql-connector-java-5.1.47.jar" catdata.ide.IDE
</pre>
<hr >
<p>We start by viewing the database:</p>
<pre>
command ViewSrc = exec_jdbc "jdbc:mysql://mysql.categoricaldata.net:3306/aql_tutorial?user=xxx&password=yyy" {
"SELECT * FROM Employee"
"SELECT * FROM Department"
}
</pre>
<img src="images/examples/sql1.png" alt="sql1" width="700" >
<p>We next import that database into CQL.
The only difference between the SQL and CQL tables is that in CQL we can see
globally unique row numbers (that may vary from run to run) and references to these row numbers.
In this case, we are using a public MySQL DB that uses back-ticks for quotes and requires a time zone parameter. </p>
<pre>
instance EmpDept = import_jdbc_all "jdbc:mysql://mysql.categoricaldata.net:3306/aql_tutorial?user=xxx&password=yyy&serverTimezone=America/New_York" {
options jdbc_quote_char = "`"
}
</pre>
<img src="images/examples/sql2a.png" alt="sql2a" width="700" >
<!--<img src="images/examples/sql2b.png" alt="sql2b" width="700" />-->
<p>We can examine the imported SQL schema, although we will not discuss it further here. </p>
<pre>
schema EmpDeptSch = schemaOf EmpDept
</pre>
<img src="images/examples/sql3a.png" alt="sql3a" width="700" >
<p>To export our imported instance back to SQL, we choose a prefix (here, <code>Out</code>).
We export the data using <code>row</code> as the name of the column for CQL's row numbers.
Note that we export to CQL's built-in SQL Database (indicated by <code>""</code>; see the <a href="jdbc.php">other JDBC SQL example</a> for more details.)
We clean up in case a previous run left tables around.</p>
<pre>
command Export = export_jdbc_instance EmpDept "" "Out" {
options id_column_name = "row"
}
</pre>
<!--<img src="images/examples/sql3b.png" alt="sql3b" width="700" />-->
<p>And now we can see the exported data in SQL.</p>
<pre>
command ViewDst = exec_jdbc "" {
"SELECT * FROM OutEmployee"
"SELECT * FROM OutDepartment"
}
</pre>
<img src="images/examples/sql4.png" alt="sql4" width="700" >
<p>
In its fully generality as described above, round tripping
leads to redundant information. See the <a href="jdbc.php">other JDBC SQL example</a> for a more refined import/export process that removes these redundancies.
</p>
</div>
</body>
</html>