author | wenzelm |
Mon, 25 Mar 2019 16:45:08 +0100 | |
changeset 69980 | f2e3adfd916f |
parent 69393 | ed0824ef337e |
child 71601 | 97ccf48c2f0c |
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 |
||
69393
ed0824ef337e
static type for Library.using: avoid Java 11 warnings on "Illegal reflective access";
wenzelm
parents:
69327
diff
changeset
|
9 |
|
65021 | 10 |
import java.time.OffsetDateTime |
11 |
import java.sql.{DriverManager, Connection, PreparedStatement, ResultSet} |
|
63779 | 12 |
|
65740 | 13 |
import scala.collection.mutable |
14 |
||
63779 | 15 |
|
63778 | 16 |
object SQL |
17 |
{ |
|
65006 | 18 |
/** SQL language **/ |
19 |
||
65730 | 20 |
type Source = String |
21 |
||
22 |
||
63778 | 23 |
/* concrete syntax */ |
24 |
||
65321 | 25 |
def escape_char(c: Char): String = |
63778 | 26 |
c match { |
27 |
case '\u0000' => "\\0" |
|
28 |
case '\'' => "\\'" |
|
29 |
case '\"' => "\\\"" |
|
30 |
case '\b' => "\\b" |
|
31 |
case '\n' => "\\n" |
|
32 |
case '\r' => "\\r" |
|
33 |
case '\t' => "\\t" |
|
34 |
case '\u001a' => "\\Z" |
|
35 |
case '\\' => "\\\\" |
|
36 |
case _ => c.toString |
|
37 |
} |
|
38 |
||
65730 | 39 |
def string(s: String): Source = |
69327 | 40 |
s.iterator.map(escape_char(_)).mkString("'", "", "'") |
63778 | 41 |
|
65730 | 42 |
def ident(s: String): Source = |
65677
7d25b8dbdbfa
proper quote (amending 546020a98a91): e.g. relevant for "ISABELLE_BUILD_OPTIONS";
wenzelm
parents:
65673
diff
changeset
|
43 |
Long_Name.implode(Long_Name.explode(s).map(a => quote(a.replace("\"", "\"\"")))) |
63779 | 44 |
|
65730 | 45 |
def enclose(s: Source): Source = "(" + s + ")" |
46 |
def enclosure(ss: Iterable[Source]): Source = ss.mkString("(", ", ", ")") |
|
63791 | 47 |
|
65774 | 48 |
def select(columns: List[Column] = Nil, distinct: Boolean = false): Source = |
49 |
"SELECT " + (if (distinct) "DISTINCT " else "") + |
|
50 |
(if (columns.isEmpty) "*" else commas(columns.map(_.ident))) + " FROM " |
|
65644
7ef438495a02
support for qualified names, which are not quoted (e.g. for SQLite);
wenzelm
parents:
65641
diff
changeset
|
51 |
|
65775 | 52 |
val join_outer: Source = " LEFT OUTER JOIN " |
53 |
val join_inner: Source = " INNER JOIN " |
|
65776 | 54 |
def join(outer: Boolean): Source = if (outer) join_outer else join_inner |
65775 | 55 |
|
65804 | 56 |
def member(x: Source, set: Iterable[String]): Source = |
57 |
set.iterator.map(a => x + " = " + SQL.string(a)).mkString("(", " OR ", ")") |
|
58 |
||
63779 | 59 |
|
65008 | 60 |
/* types */ |
61 |
||
62 |
object Type extends Enumeration |
|
63 |
{ |
|
65011 | 64 |
val Boolean = Value("BOOLEAN") |
65008 | 65 |
val Int = Value("INTEGER") |
66 |
val Long = Value("BIGINT") |
|
67 |
val Double = Value("DOUBLE PRECISION") |
|
68 |
val String = Value("TEXT") |
|
69 |
val Bytes = Value("BLOB") |
|
65014 | 70 |
val Date = Value("TIMESTAMP WITH TIME ZONE") |
65008 | 71 |
} |
72 |
||
65730 | 73 |
def sql_type_default(T: Type.Value): Source = T.toString |
65013 | 74 |
|
65730 | 75 |
def sql_type_sqlite(T: Type.Value): Source = |
65019 | 76 |
if (T == Type.Boolean) "INTEGER" |
77 |
else if (T == Type.Date) "TEXT" |
|
78 |
else sql_type_default(T) |
|
65013 | 79 |
|
65730 | 80 |
def sql_type_postgresql(T: Type.Value): Source = |
65019 | 81 |
if (T == Type.Bytes) "BYTEA" |
82 |
else sql_type_default(T) |
|
65008 | 83 |
|
84 |
||
63779 | 85 |
/* columns */ |
86 |
||
87 |
object Column |
|
88 |
{ |
|
65280 | 89 |
def bool(name: String, strict: Boolean = false, primary_key: Boolean = false): Column = |
65018 | 90 |
Column(name, Type.Boolean, strict, primary_key) |
65280 | 91 |
def int(name: String, strict: Boolean = false, primary_key: Boolean = false): Column = |
65018 | 92 |
Column(name, Type.Int, strict, primary_key) |
65280 | 93 |
def long(name: String, strict: Boolean = false, primary_key: Boolean = false): Column = |
65018 | 94 |
Column(name, Type.Long, strict, primary_key) |
65280 | 95 |
def double(name: String, strict: Boolean = false, primary_key: Boolean = false): Column = |
65018 | 96 |
Column(name, Type.Double, strict, primary_key) |
65280 | 97 |
def string(name: String, strict: Boolean = false, primary_key: Boolean = false): Column = |
65018 | 98 |
Column(name, Type.String, strict, primary_key) |
65280 | 99 |
def bytes(name: String, strict: Boolean = false, primary_key: Boolean = false): Column = |
65018 | 100 |
Column(name, Type.Bytes, strict, primary_key) |
65280 | 101 |
def date(name: String, strict: Boolean = false, primary_key: Boolean = false): Column = |
65018 | 102 |
Column(name, Type.Date, strict, primary_key) |
63779 | 103 |
} |
104 |
||
65018 | 105 |
sealed case class Column( |
65780 | 106 |
name: String, T: Type.Value, strict: Boolean = false, primary_key: Boolean = false, |
107 |
expr: SQL.Source = "") |
|
63779 | 108 |
{ |
66857 | 109 |
def make_primary_key: Column = copy(primary_key = true) |
110 |
||
65691 | 111 |
def apply(table: Table): Column = |
112 |
Column(Long_Name.qualify(table.name, name), T, strict = strict, primary_key = primary_key) |
|
65644
7ef438495a02
support for qualified names, which are not quoted (e.g. for SQLite);
wenzelm
parents:
65641
diff
changeset
|
113 |
|
65780 | 114 |
def ident: Source = |
115 |
if (expr == "") SQL.ident(name) |
|
116 |
else enclose(expr) + " AS " + SQL.ident(name) |
|
65695 | 117 |
|
65730 | 118 |
def decl(sql_type: Type.Value => Source): Source = |
65695 | 119 |
ident + " " + sql_type(T) + (if (strict || primary_key) " NOT NULL" else "") |
63781 | 120 |
|
66856 | 121 |
def defined: String = ident + " IS NOT NULL" |
122 |
def undefined: String = ident + " IS NULL" |
|
123 |
||
68091 | 124 |
def equal(s: String): Source = ident + " = " + string(s) |
125 |
def where_equal(s: String): Source = "WHERE " + equal(s) |
|
65593 | 126 |
|
65730 | 127 |
override def toString: Source = ident |
63779 | 128 |
} |
129 |
||
63780 | 130 |
|
131 |
/* tables */ |
|
132 |
||
65730 | 133 |
sealed case class Table(name: String, columns: List[Column], body: Source = "") |
63780 | 134 |
{ |
63790 | 135 |
private val columns_index: Map[String, Int] = |
136 |
columns.iterator.map(_.name).zipWithIndex.toMap |
|
137 |
||
63781 | 138 |
Library.duplicates(columns.map(_.name)) match { |
139 |
case Nil => |
|
140 |
case bad => error("Duplicate column names " + commas_quote(bad) + " for table " + quote(name)) |
|
141 |
} |
|
142 |
||
65730 | 143 |
def ident: Source = SQL.ident(name) |
65691 | 144 |
|
65730 | 145 |
def query: Source = |
65696 | 146 |
if (body == "") error("Missing SQL body for table " + quote(name)) |
147 |
else SQL.enclose(body) |
|
65649 | 148 |
|
65778 | 149 |
def query_named: Source = query + " AS " + SQL.ident(name) |
65702 | 150 |
|
65730 | 151 |
def create(strict: Boolean = false, sql_type: Type.Value => Source): Source = |
65325 | 152 |
{ |
153 |
val primary_key = |
|
154 |
columns.filter(_.primary_key).map(_.name) match { |
|
155 |
case Nil => Nil |
|
156 |
case keys => List("PRIMARY KEY " + enclosure(keys)) |
|
157 |
} |
|
65701 | 158 |
"CREATE TABLE " + (if (strict) "" else "IF NOT EXISTS ") + |
159 |
ident + " " + enclosure(columns.map(_.decl(sql_type)) ::: primary_key) |
|
63781 | 160 |
} |
161 |
||
65698 | 162 |
def create_index(index_name: String, index_columns: List[Column], |
65730 | 163 |
strict: Boolean = false, unique: Boolean = false): Source = |
63791 | 164 |
"CREATE " + (if (unique) "UNIQUE " else "") + "INDEX " + |
65695 | 165 |
(if (strict) "" else "IF NOT EXISTS ") + SQL.ident(index_name) + " ON " + |
166 |
ident + " " + enclosure(index_columns.map(_.name)) |
|
63791 | 167 |
|
65730 | 168 |
def insert_cmd(cmd: Source, sql: Source = ""): Source = |
65703
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
169 |
cmd + " INTO " + ident + " VALUES " + enclosure(columns.map(_ => "?")) + |
65698 | 170 |
(if (sql == "") "" else " " + sql) |
63791 | 171 |
|
65730 | 172 |
def insert(sql: Source = ""): Source = insert_cmd("INSERT", sql) |
65703
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
173 |
|
65730 | 174 |
def delete(sql: Source = ""): Source = |
65698 | 175 |
"DELETE FROM " + ident + |
176 |
(if (sql == "") "" else " " + sql) |
|
65319 | 177 |
|
65774 | 178 |
def select( |
179 |
select_columns: List[Column] = Nil, sql: Source = "", distinct: Boolean = false): Source = |
|
65698 | 180 |
SQL.select(select_columns, distinct = distinct) + ident + |
181 |
(if (sql == "") "" else " " + sql) |
|
63790 | 182 |
|
65730 | 183 |
override def toString: Source = ident |
63780 | 184 |
} |
63790 | 185 |
|
186 |
||
65012 | 187 |
|
188 |
/** SQL database operations **/ |
|
189 |
||
65740 | 190 |
/* statements */ |
191 |
||
192 |
class Statement private[SQL](val db: Database, val rep: PreparedStatement) |
|
69393
ed0824ef337e
static type for Library.using: avoid Java 11 warnings on "Illegal reflective access";
wenzelm
parents:
69327
diff
changeset
|
193 |
extends AutoCloseable |
65740 | 194 |
{ |
195 |
stmt => |
|
196 |
||
65748 | 197 |
object bool |
65740 | 198 |
{ |
65748 | 199 |
def update(i: Int, x: Boolean) { rep.setBoolean(i, x) } |
200 |
def update(i: Int, x: Option[Boolean]) |
|
201 |
{ |
|
202 |
if (x.isDefined) update(i, x.get) |
|
203 |
else rep.setNull(i, java.sql.Types.BOOLEAN) |
|
204 |
} |
|
65740 | 205 |
} |
65748 | 206 |
object int |
65740 | 207 |
{ |
65748 | 208 |
def update(i: Int, x: Int) { rep.setInt(i, x) } |
209 |
def update(i: Int, x: Option[Int]) |
|
210 |
{ |
|
211 |
if (x.isDefined) update(i, x.get) |
|
212 |
else rep.setNull(i, java.sql.Types.INTEGER) |
|
213 |
} |
|
65740 | 214 |
} |
65748 | 215 |
object long |
65740 | 216 |
{ |
65748 | 217 |
def update(i: Int, x: Long) { rep.setLong(i, x) } |
218 |
def update(i: Int, x: Option[Long]) |
|
219 |
{ |
|
220 |
if (x.isDefined) update(i, x.get) |
|
221 |
else rep.setNull(i, java.sql.Types.BIGINT) |
|
222 |
} |
|
65740 | 223 |
} |
65748 | 224 |
object double |
65740 | 225 |
{ |
65748 | 226 |
def update(i: Int, x: Double) { rep.setDouble(i, x) } |
227 |
def update(i: Int, x: Option[Double]) |
|
228 |
{ |
|
229 |
if (x.isDefined) update(i, x.get) |
|
230 |
else rep.setNull(i, java.sql.Types.DOUBLE) |
|
231 |
} |
|
232 |
} |
|
233 |
object string |
|
234 |
{ |
|
235 |
def update(i: Int, x: String) { rep.setString(i, x) } |
|
236 |
def update(i: Int, x: Option[String]): Unit = update(i, x.orNull) |
|
65740 | 237 |
} |
65748 | 238 |
object bytes |
65740 | 239 |
{ |
65748 | 240 |
def update(i: Int, bytes: Bytes) |
241 |
{ |
|
242 |
if (bytes == null) rep.setBytes(i, null) |
|
243 |
else rep.setBinaryStream(i, bytes.stream(), bytes.length) |
|
244 |
} |
|
245 |
def update(i: Int, bytes: Option[Bytes]): Unit = update(i, bytes.orNull) |
|
65740 | 246 |
} |
65748 | 247 |
object date |
248 |
{ |
|
249 |
def update(i: Int, date: Date): Unit = db.update_date(stmt, i, date) |
|
250 |
def update(i: Int, date: Option[Date]): Unit = update(i, date.orNull) |
|
251 |
} |
|
65740 | 252 |
|
253 |
def execute(): Boolean = rep.execute() |
|
254 |
def execute_query(): Result = new Result(this, rep.executeQuery()) |
|
255 |
||
256 |
def close(): Unit = rep.close |
|
257 |
} |
|
258 |
||
259 |
||
63790 | 260 |
/* results */ |
261 |
||
65740 | 262 |
class Result private[SQL](val stmt: Statement, val rep: ResultSet) |
63790 | 263 |
{ |
65740 | 264 |
res => |
265 |
||
266 |
def next(): Boolean = rep.next() |
|
267 |
||
268 |
def iterator[A](get: Result => A): Iterator[A] = new Iterator[A] |
|
269 |
{ |
|
270 |
private var _next: Boolean = res.next() |
|
271 |
def hasNext: Boolean = _next |
|
272 |
def next: A = { val x = get(res); _next = res.next(); x } |
|
273 |
} |
|
274 |
||
275 |
def bool(column: Column): Boolean = rep.getBoolean(column.name) |
|
276 |
def int(column: Column): Int = rep.getInt(column.name) |
|
277 |
def long(column: Column): Long = rep.getLong(column.name) |
|
278 |
def double(column: Column): Double = rep.getDouble(column.name) |
|
279 |
def string(column: Column): String = |
|
280 |
{ |
|
281 |
val s = rep.getString(column.name) |
|
282 |
if (s == null) "" else s |
|
283 |
} |
|
284 |
def bytes(column: Column): Bytes = |
|
285 |
{ |
|
286 |
val bs = rep.getBytes(column.name) |
|
287 |
if (bs == null) Bytes.empty else Bytes(bs) |
|
288 |
} |
|
289 |
def date(column: Column): Date = stmt.db.date(res, column) |
|
290 |
||
65741 | 291 |
def timing(c1: Column, c2: Column, c3: Column) = |
292 |
Timing(Time.ms(long(c1)), Time.ms(long(c2)), Time.ms(long(c3))) |
|
293 |
||
65740 | 294 |
def get[A](column: Column, f: Column => A): Option[A] = |
295 |
{ |
|
296 |
val x = f(column) |
|
297 |
if (rep.wasNull) None else Some(x) |
|
298 |
} |
|
299 |
def get_bool(column: Column): Option[Boolean] = get(column, bool _) |
|
300 |
def get_int(column: Column): Option[Int] = get(column, int _) |
|
301 |
def get_long(column: Column): Option[Long] = get(column, long _) |
|
302 |
def get_double(column: Column): Option[Double] = get(column, double _) |
|
303 |
def get_string(column: Column): Option[String] = get(column, string _) |
|
304 |
def get_bytes(column: Column): Option[Bytes] = get(column, bytes _) |
|
305 |
def get_date(column: Column): Option[Date] = get(column, date _) |
|
63790 | 306 |
} |
65006 | 307 |
|
65740 | 308 |
|
309 |
/* database */ |
|
310 |
||
69393
ed0824ef337e
static type for Library.using: avoid Java 11 warnings on "Illegal reflective access";
wenzelm
parents:
69327
diff
changeset
|
311 |
trait Database extends AutoCloseable |
65006 | 312 |
{ |
65740 | 313 |
db => |
314 |
||
315 |
||
65008 | 316 |
/* types */ |
317 |
||
65730 | 318 |
def sql_type(T: Type.Value): Source |
65008 | 319 |
|
320 |
||
65006 | 321 |
/* connection */ |
322 |
||
323 |
def connection: Connection |
|
324 |
||
325 |
def close() { connection.close } |
|
326 |
||
327 |
def transaction[A](body: => A): A = |
|
328 |
{ |
|
329 |
val auto_commit = connection.getAutoCommit |
|
330 |
try { |
|
331 |
connection.setAutoCommit(false) |
|
65022 | 332 |
val savepoint = connection.setSavepoint |
333 |
try { |
|
334 |
val result = body |
|
335 |
connection.commit |
|
336 |
result |
|
337 |
} |
|
338 |
catch { case exn: Throwable => connection.rollback(savepoint); throw exn } |
|
65006 | 339 |
} |
340 |
finally { connection.setAutoCommit(auto_commit) } |
|
341 |
} |
|
342 |
||
343 |
||
65740 | 344 |
/* statements and results */ |
345 |
||
346 |
def statement(sql: Source): Statement = |
|
347 |
new Statement(db, connection.prepareStatement(sql)) |
|
65006 | 348 |
|
65740 | 349 |
def using_statement[A](sql: Source)(f: Statement => A): A = |
350 |
using(statement(sql))(f) |
|
65006 | 351 |
|
65748 | 352 |
def update_date(stmt: Statement, i: Int, date: Date): Unit |
65740 | 353 |
def date(res: Result, column: Column): Date |
65006 | 354 |
|
65730 | 355 |
def insert_permissive(table: Table, sql: Source = ""): Source |
65703
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
356 |
|
65006 | 357 |
|
65669 | 358 |
/* tables and views */ |
65006 | 359 |
|
360 |
def tables: List[String] = |
|
65740 | 361 |
{ |
362 |
val result = new mutable.ListBuffer[String] |
|
363 |
val rs = connection.getMetaData.getTables(null, null, "%", null) |
|
364 |
while (rs.next) { result += rs.getString(3) } |
|
365 |
result.toList |
|
366 |
} |
|
65006 | 367 |
|
65730 | 368 |
def create_table(table: Table, strict: Boolean = false, sql: Source = ""): Unit = |
65698 | 369 |
using_statement( |
370 |
table.create(strict, sql_type) + (if (sql == "") "" else " " + sql))(_.execute()) |
|
65006 | 371 |
|
65018 | 372 |
def create_index(table: Table, name: String, columns: List[Column], |
65280 | 373 |
strict: Boolean = false, unique: Boolean = false): Unit = |
65698 | 374 |
using_statement(table.create_index(name, columns, strict, unique))(_.execute()) |
65006 | 375 |
|
65691 | 376 |
def create_view(table: Table, strict: Boolean = false): Unit = |
377 |
{ |
|
378 |
if (strict || !tables.contains(table.name)) { |
|
65731 | 379 |
val sql = "CREATE VIEW " + table + " AS " + { table.query; table.body } |
65698 | 380 |
using_statement(sql)(_.execute()) |
65690 | 381 |
} |
65691 | 382 |
} |
65006 | 383 |
} |
63778 | 384 |
} |
65006 | 385 |
|
386 |
||
387 |
||
388 |
/** SQLite **/ |
|
389 |
||
390 |
object SQLite |
|
391 |
{ |
|
65021 | 392 |
// see https://www.sqlite.org/lang_datefunc.html |
393 |
val date_format: Date.Format = Date.Format("uuuu-MM-dd HH:mm:ss.SSS x") |
|
394 |
||
65890
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
395 |
lazy val init_jdbc: Unit = |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
396 |
{ |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
397 |
val lib_path = Path.explode("$ISABELLE_SQLITE_HOME/" + Platform.jvm_platform) |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
398 |
val lib_name = |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
399 |
File.find_files(lib_path.file) match { |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
400 |
case List(file) => file.getName |
65918 | 401 |
case _ => error("Exactly one file expected in directory " + lib_path.expand) |
65890
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
402 |
} |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
403 |
System.setProperty("org.sqlite.lib.path", File.platform_path(lib_path)) |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
404 |
System.setProperty("org.sqlite.lib.name", lib_name) |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
405 |
|
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
406 |
Class.forName("org.sqlite.JDBC") |
1b004f5974af
refer to already extracted library files, to avoid tmp files produced by SQLiteJDBCLoader, which tend to remain after JVM crash;
wenzelm
parents:
65804
diff
changeset
|
407 |
} |
65292
e3bd1e7ddd23
more robust JDBC initialization, e.g. required for Isabelle/jEdit startup;
wenzelm
parents:
65280
diff
changeset
|
408 |
|
65006 | 409 |
def open_database(path: Path): Database = |
410 |
{ |
|
65292
e3bd1e7ddd23
more robust JDBC initialization, e.g. required for Isabelle/jEdit startup;
wenzelm
parents:
65280
diff
changeset
|
411 |
init_jdbc |
65006 | 412 |
val path0 = path.expand |
413 |
val s0 = File.platform_path(path0) |
|
414 |
val s1 = if (Platform.is_windows) s0.replace('\\', '/') else s0 |
|
415 |
val connection = DriverManager.getConnection("jdbc:sqlite:" + s1) |
|
65007 | 416 |
new Database(path0.toString, connection) |
65006 | 417 |
} |
418 |
||
65007 | 419 |
class Database private[SQLite](name: String, val connection: Connection) extends SQL.Database |
65006 | 420 |
{ |
65007 | 421 |
override def toString: String = name |
65006 | 422 |
|
65730 | 423 |
def sql_type(T: SQL.Type.Value): SQL.Source = SQL.sql_type_sqlite(T) |
65011 | 424 |
|
65748 | 425 |
def update_date(stmt: SQL.Statement, i: Int, date: Date): Unit = |
426 |
if (date == null) stmt.string(i) = (null: String) |
|
427 |
else stmt.string(i) = date_format(date) |
|
65598 | 428 |
|
65740 | 429 |
def date(res: SQL.Result, column: SQL.Column): Date = |
430 |
date_format.parse(res.string(column)) |
|
65021 | 431 |
|
65730 | 432 |
def insert_permissive(table: SQL.Table, sql: SQL.Source = ""): SQL.Source = |
65703
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
433 |
table.insert_cmd("INSERT OR IGNORE", sql = sql) |
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
434 |
|
65698 | 435 |
def rebuild { using_statement("VACUUM")(_.execute()) } |
65006 | 436 |
} |
437 |
} |
|
438 |
||
439 |
||
440 |
||
441 |
/** PostgreSQL **/ |
|
442 |
||
443 |
object PostgreSQL |
|
444 |
{ |
|
445 |
val default_port = 5432 |
|
446 |
||
65292
e3bd1e7ddd23
more robust JDBC initialization, e.g. required for Isabelle/jEdit startup;
wenzelm
parents:
65280
diff
changeset
|
447 |
lazy val init_jdbc: Unit = Class.forName("org.postgresql.Driver") |
e3bd1e7ddd23
more robust JDBC initialization, e.g. required for Isabelle/jEdit startup;
wenzelm
parents:
65280
diff
changeset
|
448 |
|
65006 | 449 |
def open_database( |
450 |
user: String, |
|
451 |
password: String, |
|
452 |
database: String = "", |
|
453 |
host: String = "", |
|
65594 | 454 |
port: Int = 0, |
65636
df804cdba5f9
ssh_close for proper termination after use of database;
wenzelm
parents:
65620
diff
changeset
|
455 |
ssh: Option[SSH.Session] = None, |
df804cdba5f9
ssh_close for proper termination after use of database;
wenzelm
parents:
65620
diff
changeset
|
456 |
ssh_close: Boolean = false): Database = |
65006 | 457 |
{ |
65292
e3bd1e7ddd23
more robust JDBC initialization, e.g. required for Isabelle/jEdit startup;
wenzelm
parents:
65280
diff
changeset
|
458 |
init_jdbc |
e3bd1e7ddd23
more robust JDBC initialization, e.g. required for Isabelle/jEdit startup;
wenzelm
parents:
65280
diff
changeset
|
459 |
|
65641 | 460 |
if (user == "") error("Undefined database user") |
65009 | 461 |
|
65717 | 462 |
val db_host = proper_string(host) getOrElse "localhost" |
65594 | 463 |
val db_port = if (port > 0 && port != default_port) ":" + port else "" |
65717 | 464 |
val db_name = "/" + (proper_string(database) getOrElse user) |
65009 | 465 |
|
65010 | 466 |
val (url, name, port_forwarding) = |
65009 | 467 |
ssh match { |
65010 | 468 |
case None => |
469 |
val spec = db_host + db_port + db_name |
|
470 |
val url = "jdbc:postgresql://" + spec |
|
471 |
val name = user + "@" + spec |
|
472 |
(url, name, None) |
|
65009 | 473 |
case Some(ssh) => |
65594 | 474 |
val fw = |
475 |
ssh.port_forwarding(remote_host = db_host, |
|
65636
df804cdba5f9
ssh_close for proper termination after use of database;
wenzelm
parents:
65620
diff
changeset
|
476 |
remote_port = if (port > 0) port else default_port, |
df804cdba5f9
ssh_close for proper termination after use of database;
wenzelm
parents:
65620
diff
changeset
|
477 |
ssh_close = ssh_close) |
65010 | 478 |
val url = "jdbc:postgresql://localhost:" + fw.local_port + db_name |
479 |
val name = user + "@" + fw + db_name + " via ssh " + ssh |
|
480 |
(url, name, Some(fw)) |
|
65009 | 481 |
} |
482 |
try { |
|
65010 | 483 |
val connection = DriverManager.getConnection(url, user, password) |
484 |
new Database(name, connection, port_forwarding) |
|
65009 | 485 |
} |
486 |
catch { case exn: Throwable => port_forwarding.foreach(_.close); throw exn } |
|
65006 | 487 |
} |
488 |
||
65009 | 489 |
class Database private[PostgreSQL]( |
490 |
name: String, val connection: Connection, port_forwarding: Option[SSH.Port_Forwarding]) |
|
491 |
extends SQL.Database |
|
65006 | 492 |
{ |
65010 | 493 |
override def toString: String = name |
65008 | 494 |
|
65730 | 495 |
def sql_type(T: SQL.Type.Value): SQL.Source = SQL.sql_type_postgresql(T) |
65009 | 496 |
|
65021 | 497 |
// see https://jdbc.postgresql.org/documentation/head/8-date-time.html |
65748 | 498 |
def update_date(stmt: SQL.Statement, i: Int, date: Date): Unit = |
65740 | 499 |
if (date == null) stmt.rep.setObject(i, null) |
69980 | 500 |
else stmt.rep.setObject(i, OffsetDateTime.from(date.to(Date.timezone_utc).rep)) |
65598 | 501 |
|
65740 | 502 |
def date(res: SQL.Result, column: SQL.Column): Date = |
65704 | 503 |
{ |
65740 | 504 |
val obj = res.rep.getObject(column.name, classOf[OffsetDateTime]) |
65704 | 505 |
if (obj == null) null else Date.instant(obj.toInstant) |
506 |
} |
|
65021 | 507 |
|
65730 | 508 |
def insert_permissive(table: SQL.Table, sql: SQL.Source = ""): SQL.Source = |
65703
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
509 |
table.insert_cmd("INSERT", |
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
510 |
sql = sql + (if (sql == "") "" else " ") + "ON CONFLICT DO NOTHING") |
cead65c19f2e
more direct insert_permissive statement, which avoids somewhat fragile nested transactions;
wenzelm
parents:
65702
diff
changeset
|
511 |
|
65009 | 512 |
override def close() { super.close; port_forwarding.foreach(_.close) } |
65006 | 513 |
} |
514 |
} |