author | wenzelm |
Wed, 08 Feb 2017 22:38:08 +0100 | |
changeset 65003 | 4b4ccf86755c |
parent 63791 | c6cbdfaae19e |
child 65004 | fd4d1395fa17 |
permissions | -rw-r--r-- |
63788 | 1 |
/* Title: Pure/General/sql.scala |
63778 | 2 |
Author: Makarius |
3 |
||
63779 | 4 |
Generic support for SQL. |
63778 | 5 |
*/ |
6 |
||
7 |
package isabelle |
|
8 |
||
9 |
||
63779 | 10 |
import java.sql.ResultSet |
11 |
||
12 |
||
63778 | 13 |
object SQL |
14 |
{ |
|
15 |
/* concrete syntax */ |
|
16 |
||
17 |
def quote_char(c: Char): String = |
|
18 |
c match { |
|
19 |
case '\u0000' => "\\0" |
|
20 |
case '\'' => "\\'" |
|
21 |
case '\"' => "\\\"" |
|
22 |
case '\b' => "\\b" |
|
23 |
case '\n' => "\\n" |
|
24 |
case '\r' => "\\r" |
|
25 |
case '\t' => "\\t" |
|
26 |
case '\u001a' => "\\Z" |
|
27 |
case '\\' => "\\\\" |
|
28 |
case _ => c.toString |
|
29 |
} |
|
30 |
||
31 |
def quote_string(s: String): String = |
|
32 |
quote(s.map(quote_char(_)).mkString) |
|
33 |
||
63779 | 34 |
def quote_ident(s: String): String = |
65003
4b4ccf86755c
more portable: SQL standard syntax instead of MySQL extension;
wenzelm
parents:
63791
diff
changeset
|
35 |
quote(s.replace("\"", "\"\"")) |
63779 | 36 |
|
63791 | 37 |
def enclosure(ss: Iterable[String]): String = ss.mkString("(", ", ", ")") |
38 |
||
63779 | 39 |
|
40 |
/* columns */ |
|
41 |
||
42 |
object Column |
|
43 |
{ |
|
63781 | 44 |
def int(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Int] = |
45 |
new Column_Int(name, strict, primary_key) |
|
46 |
def long(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Long] = |
|
47 |
new Column_Long(name, strict, primary_key) |
|
48 |
def double(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Double] = |
|
49 |
new Column_Double(name, strict, primary_key) |
|
50 |
def string(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[String] = |
|
51 |
new Column_String(name, strict, primary_key) |
|
52 |
def bytes(name: String, strict: Boolean = true, primary_key: Boolean = false): Column[Bytes] = |
|
53 |
new Column_Bytes(name, strict, primary_key) |
|
63779 | 54 |
} |
55 |
||
63781 | 56 |
abstract class Column[+A] private[SQL]( |
63790 | 57 |
val name: String, val strict: Boolean, val primary_key: Boolean) |
58 |
extends Function[ResultSet, A] |
|
63779 | 59 |
{ |
60 |
def sql_name: String = quote_ident(name) |
|
61 |
def sql_type: String |
|
63781 | 62 |
def sql_decl: String = |
63 |
sql_name + " " + sql_type + |
|
64 |
(if (strict) " NOT NULL" else "") + |
|
65 |
(if (primary_key) " PRIMARY KEY" else "") |
|
66 |
||
63780 | 67 |
def string(rs: ResultSet): String = |
68 |
{ |
|
69 |
val s = rs.getString(name) |
|
70 |
if (s == null) "" else s |
|
71 |
} |
|
72 |
def apply(rs: ResultSet): A |
|
73 |
def get(rs: ResultSet): Option[A] = |
|
74 |
{ |
|
75 |
val x = apply(rs) |
|
76 |
if (rs.wasNull) None else Some(x) |
|
77 |
} |
|
63779 | 78 |
|
79 |
override def toString: String = sql_decl |
|
80 |
} |
|
81 |
||
63781 | 82 |
class Column_Int private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
83 |
extends Column[Int](name, strict, primary_key) |
|
63779 | 84 |
{ |
85 |
def sql_type: String = "INTEGER" |
|
63780 | 86 |
def apply(rs: ResultSet): Int = rs.getInt(name) |
63779 | 87 |
} |
88 |
||
63781 | 89 |
class Column_Long private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
90 |
extends Column[Long](name, strict, primary_key) |
|
63779 | 91 |
{ |
92 |
def sql_type: String = "INTEGER" |
|
63780 | 93 |
def apply(rs: ResultSet): Long = rs.getLong(name) |
63779 | 94 |
} |
95 |
||
63781 | 96 |
class Column_Double private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
97 |
extends Column[Double](name, strict, primary_key) |
|
63779 | 98 |
{ |
99 |
def sql_type: String = "REAL" |
|
63780 | 100 |
def apply(rs: ResultSet): Double = rs.getDouble(name) |
63779 | 101 |
} |
102 |
||
63781 | 103 |
class Column_String private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
104 |
extends Column[String](name, strict, primary_key) |
|
63779 | 105 |
{ |
106 |
def sql_type: String = "TEXT" |
|
63780 | 107 |
def apply(rs: ResultSet): String = |
108 |
{ |
|
109 |
val s = rs.getString(name) |
|
110 |
if (s == null) "" else s |
|
111 |
} |
|
63779 | 112 |
} |
113 |
||
63781 | 114 |
class Column_Bytes private[SQL](name: String, strict: Boolean, primary_key: Boolean) |
115 |
extends Column[Bytes](name, strict, primary_key) |
|
63779 | 116 |
{ |
117 |
def sql_type: String = "BLOB" |
|
63780 | 118 |
def apply(rs: ResultSet): Bytes = |
63779 | 119 |
{ |
120 |
val bs = rs.getBytes(name) |
|
63780 | 121 |
if (bs == null) Bytes.empty else Bytes(bs) |
63779 | 122 |
} |
123 |
} |
|
63780 | 124 |
|
125 |
||
126 |
/* tables */ |
|
127 |
||
63791 | 128 |
def table(name: String, columns: List[Column[Any]]): Table = new Table(name, columns) |
63783 | 129 |
|
130 |
class Table private[SQL](name: String, columns: List[Column[Any]]) |
|
63780 | 131 |
{ |
63790 | 132 |
private val columns_index: Map[String, Int] = |
133 |
columns.iterator.map(_.name).zipWithIndex.toMap |
|
134 |
||
63781 | 135 |
Library.duplicates(columns.map(_.name)) match { |
136 |
case Nil => |
|
137 |
case bad => error("Duplicate column names " + commas_quote(bad) + " for table " + quote(name)) |
|
138 |
} |
|
139 |
||
140 |
columns.filter(_.primary_key) match { |
|
141 |
case bad if bad.length > 1 => |
|
142 |
error("Multiple primary keys " + commas_quote(bad.map(_.name)) + " for table " + quote(name)) |
|
143 |
case _ => |
|
144 |
} |
|
145 |
||
63780 | 146 |
def sql_create(strict: Boolean, rowid: Boolean): String = |
63784 | 147 |
"CREATE TABLE " + (if (strict) "" else "IF NOT EXISTS ") + |
63791 | 148 |
quote_ident(name) + " " + enclosure(columns.map(_.sql_decl)) + |
63780 | 149 |
(if (rowid) "" else " WITHOUT ROWID") |
150 |
||
151 |
def sql_drop(strict: Boolean): String = |
|
63784 | 152 |
"DROP TABLE " + (if (strict) "" else "IF EXISTS ") + quote_ident(name) |
63783 | 153 |
|
63791 | 154 |
def sql_create_index( |
155 |
index_name: String, index_columns: List[Column[Any]], |
|
156 |
strict: Boolean, unique: Boolean): String = |
|
157 |
"CREATE " + (if (unique) "UNIQUE " else "") + "INDEX " + |
|
158 |
(if (strict) "" else "IF NOT EXISTS ") + quote_ident(index_name) + " ON " + |
|
159 |
quote_ident(name) + " " + enclosure(index_columns.map(_.name)) |
|
160 |
||
161 |
def sql_drop_index(index_name: String, strict: Boolean): String = |
|
162 |
"DROP INDEX " + (if (strict) "" else "IF EXISTS ") + quote_ident(index_name) |
|
163 |
||
63790 | 164 |
def sql_insert: String = |
63791 | 165 |
"INSERT INTO " + quote_ident(name) + " VALUES " + enclosure(columns.map(_ => "?")) |
166 |
||
167 |
def sql_select(select_columns: List[Column[Any]], distinct: Boolean): String = |
|
168 |
"SELECT " + (if (distinct) "DISTINCT " else "") + |
|
169 |
commas(select_columns.map(_.sql_name)) + " FROM " + quote_ident(name) |
|
63790 | 170 |
|
63783 | 171 |
override def toString: String = |
63791 | 172 |
"TABLE " + quote_ident(name) + " " + enclosure(columns.map(_.toString)) |
63780 | 173 |
} |
63790 | 174 |
|
175 |
||
176 |
/* results */ |
|
177 |
||
178 |
def iterator[A](rs: ResultSet)(get: ResultSet => A): Iterator[A] = new Iterator[A] |
|
179 |
{ |
|
180 |
private var _next: Boolean = rs.next() |
|
181 |
def hasNext: Boolean = _next |
|
182 |
def next: A = { val x = get(rs); _next = rs.next(); x } |
|
183 |
} |
|
63778 | 184 |
} |