author | wenzelm |
Thu, 09 Feb 2017 11:03:22 +0100 | |
changeset 65007 | b6a1a1d42f5d |
parent 65006 | 632bdf7b8bab |
child 65008 | ed2eedf786f3 |
permissions | -rw-r--r-- |
63788 | 1 |
/* Title: Pure/General/sql.scala |
63778 | 2 |
Author: Makarius |
3 |
||
65006 | 4 |
Support for SQL databases: SQLite and PostgreSQL. |
63778 | 5 |
*/ |
6 |
||
7 |
package isabelle |
|
8 |
||
9 |
||
65006 | 10 |
import java.sql.{DriverManager, Connection, PreparedStatement, ResultSet} |
63779 | 11 |
|
12 |
||
63778 | 13 |
object SQL |
14 |
{ |
|
65006 | 15 |
/** SQL language **/ |
16 |
||
63778 | 17 |
/* concrete syntax */ |
18 |
||
19 |
def quote_char(c: Char): String = |
|
20 |
c match { |
|
21 |
case '\u0000' => "\\0" |
|
22 |
case '\'' => "\\'" |
|
23 |
case '\"' => "\\\"" |
|
24 |
case '\b' => "\\b" |
|
25 |
case '\n' => "\\n" |
|
26 |
case '\r' => "\\r" |
|
27 |
case '\t' => "\\t" |
|
28 |
case '\u001a' => "\\Z" |
|
29 |
case '\\' => "\\\\" |
|
30 |
case _ => c.toString |
|
31 |
} |
|
32 |
||
33 |
def quote_string(s: String): String = |
|
34 |
quote(s.map(quote_char(_)).mkString) |
|
35 |
||
63779 | 36 |
def quote_ident(s: String): String = |
65003
4b4ccf86755c
more portable: SQL standard syntax instead of MySQL extension;
wenzelm
parents:
63791
diff
changeset
|
37 |
quote(s.replace("\"", "\"\"")) |
63779 | 38 |
|
63791 | 39 |
def enclosure(ss: Iterable[String]): String = ss.mkString("(", ", ", ")") |
40 |
||
63779 | 41 |
|
42 |
/* columns */ |
|
43 |
||
44 |
object Column |
|
45 |
{ |
|
63781 | 46 |
def int(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Int] = |
47 |
new Column_Int(name, strict, primary_key) |
|
48 |
def long(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Long] = |
|
49 |
new Column_Long(name, strict, primary_key) |
|
50 |
def double(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Double] = |
|
51 |
new Column_Double(name, strict, primary_key) |
|
52 |
def string(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[String] = |
|
53 |
new Column_String(name, strict, primary_key) |
|
54 |
def bytes(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Bytes] = |
|
65004 | 55 |
new Column_Bytes(name, strict, primary_key, "BLOB") // SQL standard |
56 |
def bytea(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Bytes] = |
|
57 |
new Column_Bytes(name, strict, primary_key, "BYTEA") // PostgreSQL |
|
63779 | 58 |
} |
59 |
||
63781 | 60 |
abstract class Column[+A] private[SQL]( |
63790 | 61 |
val name: String, val strict: Boolean, val primary_key: Boolean) |
62 |
extends Function[ResultSet, A] |
|
63779 | 63 |
{ |
64 |
def sql_name: String = quote_ident(name) |
|
65 |
def sql_type: String |
|
63781 | 66 |
def sql_decl: String = |
67 |
sql_name + " " + sql_type + |
|
68 |
(if (strict) " NOT NULL" else "") + |
|
69 |
(if (primary_key) " PRIMARY KEY" else "") |
|
70 |
||
63780 | 71 |
def string(rs: ResultSet): String = |
72 |
{ |
|
73 |
val s = rs.getString(name) |
|
74 |
if (s == null) "" else s |
|
75 |
} |
|
76 |
def apply(rs: ResultSet): A |
|
77 |
def get(rs: ResultSet): Option[A] = |
|
78 |
{ |
|
79 |
val x = apply(rs) |
|
80 |
if (rs.wasNull) None else Some(x) |
|
81 |
} |
|
63779 | 82 |
|
83 |
override def toString: String = sql_decl |
|
84 |
} |
|
85 |
||
63781 | 86 |
class Column_Int private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
87 |
extends Column[Int](name, strict, primary_key) |
|
63779 | 88 |
{ |
89 |
def sql_type: String = "INTEGER" |
|
63780 | 90 |
def apply(rs: ResultSet): Int = rs.getInt(name) |
63779 | 91 |
} |
92 |
||
63781 | 93 |
class Column_Long private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
94 |
extends Column[Long](name, strict, primary_key) |
|
63779 | 95 |
{ |
65004 | 96 |
def sql_type: String = "BIGINT" |
63780 | 97 |
def apply(rs: ResultSet): Long = rs.getLong(name) |
63779 | 98 |
} |
99 |
||
63781 | 100 |
class Column_Double private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
101 |
extends Column[Double](name, strict, primary_key) |
|
63779 | 102 |
{ |
65004 | 103 |
def sql_type: String = "DOUBLE PRECISION" |
63780 | 104 |
def apply(rs: ResultSet): Double = rs.getDouble(name) |
63779 | 105 |
} |
106 |
||
63781 | 107 |
class Column_String private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
108 |
extends Column[String](name, strict, primary_key) |
|
63779 | 109 |
{ |
110 |
def sql_type: String = "TEXT" |
|
63780 | 111 |
def apply(rs: ResultSet): String = |
112 |
{ |
|
113 |
val s = rs.getString(name) |
|
114 |
if (s == null) "" else s |
|
115 |
} |
|
63779 | 116 |
} |
117 |
||
65004 | 118 |
class Column_Bytes private[SQL]( |
119 |
name: String, strict: Boolean, primary_key: Boolean, val sql_type: String) |
|
63781 | 120 |
extends Column[Bytes](name, strict, primary_key) |
63779 | 121 |
{ |
63780 | 122 |
def apply(rs: ResultSet): Bytes = |
63779 | 123 |
{ |
124 |
val bs = rs.getBytes(name) |
|
63780 | 125 |
if (bs == null) Bytes.empty else Bytes(bs) |
63779 | 126 |
} |
127 |
} |
|
63780 | 128 |
|
129 |
||
130 |
/* tables */ |
|
131 |
||
63791 | 132 |
def table(name: String, columns: List[Column[Any]]): Table = new Table(name, columns) |
63783 | 133 |
|
134 |
class Table private[SQL](name: String, columns: List[Column[Any]]) |
|
63780 | 135 |
{ |
63790 | 136 |
private val columns_index: Map[String, Int] = |
137 |
columns.iterator.map(_.name).zipWithIndex.toMap |
|
138 |
||
63781 | 139 |
Library.duplicates(columns.map(_.name)) match { |
140 |
case Nil => |
|
141 |
case bad => error("Duplicate column names " + commas_quote(bad) + " for table " + quote(name)) |
|
142 |
} |
|
143 |
||
144 |
columns.filter(_.primary_key) match { |
|
145 |
case bad if bad.length > 1 => |
|
146 |
error("Multiple primary keys " + commas_quote(bad.map(_.name)) + " for table " + quote(name)) |
|
147 |
case _ => |
|
148 |
} |
|
149 |
||
63780 | 150 |
def sql_create(strict: Boolean, rowid: Boolean): String = |
63784 | 151 |
"CREATE TABLE " + (if (strict) "" else "IF NOT EXISTS ") + |
63791 | 152 |
quote_ident(name) + " " + enclosure(columns.map(_.sql_decl)) + |
63780 | 153 |
(if (rowid) "" else " WITHOUT ROWID") |
154 |
||
155 |
def sql_drop(strict: Boolean): String = |
|
63784 | 156 |
"DROP TABLE " + (if (strict) "" else "IF EXISTS ") + quote_ident(name) |
63783 | 157 |
|
63791 | 158 |
def sql_create_index( |
159 |
index_name: String, index_columns: List[Column[Any]], |
|
160 |
strict: Boolean, unique: Boolean): String = |
|
161 |
"CREATE " + (if (unique) "UNIQUE " else "") + "INDEX " + |
|
162 |
(if (strict) "" else "IF NOT EXISTS ") + quote_ident(index_name) + " ON " + |
|
163 |
quote_ident(name) + " " + enclosure(index_columns.map(_.name)) |
|
164 |
||
165 |
def sql_drop_index(index_name: String, strict: Boolean): String = |
|
166 |
"DROP INDEX " + (if (strict) "" else "IF EXISTS ") + quote_ident(index_name) |
|
167 |
||
63790 | 168 |
def sql_insert: String = |
63791 | 169 |
"INSERT INTO " + quote_ident(name) + " VALUES " + enclosure(columns.map(_ => "?")) |
170 |
||
171 |
def sql_select(select_columns: List[Column[Any]], distinct: Boolean): String = |
|
172 |
"SELECT " + (if (distinct) "DISTINCT " else "") + |
|
173 |
commas(select_columns.map(_.sql_name)) + " FROM " + quote_ident(name) |
|
63790 | 174 |
|
63783 | 175 |
override def toString: String = |
63791 | 176 |
"TABLE " + quote_ident(name) + " " + enclosure(columns.map(_.toString)) |
63780 | 177 |
} |
63790 | 178 |
|
179 |
||
180 |
/* results */ |
|
181 |
||
182 |
def iterator[A](rs: ResultSet)(get: ResultSet => A): Iterator[A] = new Iterator[A] |
|
183 |
{ |
|
184 |
private var _next: Boolean = rs.next() |
|
185 |
def hasNext: Boolean = _next |
|
186 |
def next: A = { val x = get(rs); _next = rs.next(); x } |
|
187 |
} |
|
65006 | 188 |
|
189 |
||
190 |
||
191 |
/** SQL database operations **/ |
|
192 |
||
193 |
trait Database |
|
194 |
{ |
|
195 |
/* connection */ |
|
196 |
||
197 |
def connection: Connection |
|
198 |
||
199 |
def close() { connection.close } |
|
200 |
||
201 |
def transaction[A](body: => A): A = |
|
202 |
{ |
|
203 |
val auto_commit = connection.getAutoCommit |
|
204 |
val savepoint = connection.setSavepoint |
|
205 |
||
206 |
try { |
|
207 |
connection.setAutoCommit(false) |
|
208 |
val result = body |
|
209 |
connection.commit |
|
210 |
result |
|
211 |
} |
|
212 |
catch { case exn: Throwable => connection.rollback(savepoint); throw exn } |
|
213 |
finally { connection.setAutoCommit(auto_commit) } |
|
214 |
} |
|
215 |
||
216 |
||
217 |
/* statements */ |
|
218 |
||
219 |
def statement(sql: String): PreparedStatement = connection.prepareStatement(sql) |
|
220 |
||
221 |
def insert_statement(table: Table): PreparedStatement = statement(table.sql_insert) |
|
222 |
||
223 |
def select_statement(table: Table, columns: List[Column[Any]], |
|
224 |
sql: String = "", distinct: Boolean = false): PreparedStatement = |
|
225 |
statement(table.sql_select(columns, distinct) + (if (sql == "") "" else " " + sql)) |
|
226 |
||
227 |
||
228 |
/* tables */ |
|
229 |
||
230 |
def tables: List[String] = |
|
231 |
iterator(connection.getMetaData.getTables(null, null, "%", null))(_.getString(3)).toList |
|
232 |
||
233 |
def create_table(table: Table, strict: Boolean = true, rowid: Boolean = true): Unit = |
|
234 |
using(statement(table.sql_create(strict, rowid)))(_.execute()) |
|
235 |
||
236 |
def drop_table(table: Table, strict: Boolean = true): Unit = |
|
237 |
using(statement(table.sql_drop(strict)))(_.execute()) |
|
238 |
||
239 |
def create_index(table: Table, name: String, columns: List[Column[Any]], |
|
240 |
strict: Boolean = true, unique: Boolean = false): Unit = |
|
241 |
using(statement(table.sql_create_index(name, columns, strict, unique)))(_.execute()) |
|
242 |
||
243 |
def drop_index(table: Table, name: String, strict: Boolean = true): Unit = |
|
244 |
using(statement(table.sql_drop_index(name, strict)))(_.execute()) |
|
245 |
} |
|
63778 | 246 |
} |
65006 | 247 |
|
248 |
||
249 |
||
250 |
/** SQLite **/ |
|
251 |
||
252 |
object SQLite |
|
253 |
{ |
|
254 |
def open_database(path: Path): Database = |
|
255 |
{ |
|
256 |
val path0 = path.expand |
|
257 |
val s0 = File.platform_path(path0) |
|
258 |
val s1 = if (Platform.is_windows) s0.replace('\\', '/') else s0 |
|
259 |
val connection = DriverManager.getConnection("jdbc:sqlite:" + s1) |
|
65007 | 260 |
new Database(path0.toString, connection) |
65006 | 261 |
} |
262 |
||
65007 | 263 |
class Database private[SQLite](name: String, val connection: Connection) extends SQL.Database |
65006 | 264 |
{ |
65007 | 265 |
override def toString: String = name |
65006 | 266 |
|
267 |
def rebuild { using(statement("VACUUM"))(_.execute()) } |
|
268 |
} |
|
269 |
} |
|
270 |
||
271 |
||
272 |
||
273 |
/** PostgreSQL **/ |
|
274 |
||
275 |
object PostgreSQL |
|
276 |
{ |
|
277 |
val default_port = 5432 |
|
278 |
||
279 |
def open_database( |
|
280 |
user: String, |
|
281 |
password: String, |
|
282 |
database: String = "", |
|
283 |
host: String = "", |
|
284 |
port: Int = default_port): Database = |
|
285 |
{ |
|
286 |
require(user != "") |
|
287 |
val spec = |
|
288 |
(if (host != "") host else "localhost") + |
|
289 |
(if (port != default_port) ":" + port else "") + "/" + |
|
290 |
(if (database != "") database else user) |
|
291 |
val connection = DriverManager.getConnection("jdbc:postgresql://" + spec, user, password) |
|
65007 | 292 |
new Database(user + "@" + spec, connection) |
65006 | 293 |
} |
294 |
||
65007 | 295 |
class Database private[PostgreSQL](name: String, val connection: Connection) extends SQL.Database |
65006 | 296 |
{ |
65007 | 297 |
override def toString: String = name |
65006 | 298 |
} |
299 |
} |