1 # This file is part of NIT ( http://www.nitlanguage.org ).
3 # Copyright 201 Alexis Laferrière <alexis.laf@xymus.net>
5 # Licensed under the Apache License, Version 2.0 (the "License");
6 # you may not use this file except in compliance with the License.
7 # You may obtain a copy of the License at
9 # http://www.apache.org/licenses/LICENSE-2.0
11 # Unless required by applicable law or agreed to in writing, software
12 # distributed under the License is distributed on an "AS IS" BASIS,
13 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 # See the License for the specific language governing permissions and
15 # limitations under the License.
17 # Services to manipulate a Sqlite3 database
19 # For more information, refer to the documentation of http://www.sqlite.org/docs.html
22 private import native_sqlite3
25 # A connection to a Sqlite3 database
27 private var native_connection
: NativeSqlite3
29 # Is this connection to the DB open?
32 # All `Statement` opened from this connection that must be closed with this connection
33 private var open_statements
= new Array[Statement]
35 # Open a connection to the database file at `path`
38 init(new NativeSqlite3.open
(path
.to_cstring
))
39 if native_connection
.is_valid
then is_open
= true
42 # Close this connection to the DB and all open statements
45 if not is_open
then return
49 # close open statements
50 for stmt
in open_statements
do if stmt
.is_open
then
54 native_connection
.close
57 # Prepare and return a `Statement`, return `null` on error
58 fun prepare
(sql
: Text): nullable Statement
60 var native_stmt
= native_connection
.prepare
(sql
.to_cstring
)
61 if native_stmt
.address_is_null
then return null
63 var stmt
= new Statement(native_stmt
)
64 open_statements
.add stmt
68 # Execute the `sql` statement and return `true` on success
69 fun execute
(sql
: Text): Bool
71 var err
= native_connection
.exec
(sql
.to_cstring
)
75 # Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest`
77 # This method does not escape special characters.
78 fun create_table
(rest
: Text): Bool do return execute
("CREATE TABLE " + rest
)
80 # Insert in the DB with a statement beginning with "INSERT ", followed by `rest`
82 # This method does not escape special characters.
83 fun insert
(rest
: Text): Bool do return execute
("INSERT " + rest
)
85 # Replace in the DB with a statement beginning with "REPLACE", followed by `rest`
87 # This method does not escape special characters.
88 fun replace
(rest
: Text): Bool do return execute
("REPLACE " + rest
)
90 # Select from the DB with a statement beginning with "SELECT ", followed by `rest`
92 # This method does not escape special characters.
93 fun select
(rest
: Text): nullable Statement do return prepare
("SELECT " + rest
)
95 # TODO add more prefix here as needed
97 # The latest error message, or `null` if there is none
98 fun error
: nullable String
100 if not native_connection
.is_valid
then
101 var err
= sys
.sqlite_open_error
102 if err
.is_ok
then return null
106 var err
= native_connection
.error
107 if err
.is_ok
then return null
111 # Returns the id for the last successful insert on the current connection.
112 fun last_insert_rowid
: Int do return native_connection
.last_insert_rowid
115 # Prepared Sqlite3 statement
117 # Instances of this class are created from `Sqlite3DB::prepare` and
118 # its shortcuts: `create_table`, `insert`, `replace` and `select`.
119 # The results should be explored with an `iterator`,
120 # and each call to `iterator` resets the request.
121 # If `close_with_iterator` the iterator calls `close`
122 # on this request upon finishing.
124 private var native_statement
: NativeStatement
126 # Is this statement usable?
129 # Should any `iterator` close this statement on `Iterator::finish`?
131 # If `true`, the default, any `StatementIterator` created by calls to
132 # `iterator` invokes `close` on this request when finished iterating.
133 # Otherwise, `close` must be called manually.
134 var close_with_iterator
= true is writable
136 # Close and finalize this statement
139 if not is_open
then return
142 native_statement
.finalize
145 # Reset this statement and return a `StatementIterator` to iterate over the result
146 fun iterator
: StatementIterator
148 native_statement
.reset
149 return new StatementIterator(self)
153 # A row from a `Statement`
155 # Statement linked to `self`
156 var statement
: Statement
158 # Maps the column name to its value
159 fun map
: Map[String, nullable Sqlite3Data]
161 var ret
= new ArrayMap[String, nullable Sqlite3Data]
162 for i
in [0 .. length
[ do
164 ret
[st
.name
] = st
.value
169 # Number of entries in this row
171 # require: `self.statement.is_open`
174 assert statement_closed
: statement
.is_open
176 return statement
.native_statement
.column_count
179 # Returns the `i`th entry on this row
180 fun [](i
: Int): StatementEntry do return new StatementEntry(statement
, i
)
183 # An entry on a `StatementRow`
185 # Statement linked to `self`
186 var statement
: Statement
188 private var index
: Int
192 # require: `self.statement.is_open`
193 var name
: String is lazy
do
194 assert statement_closed
: statement
.is_open
196 var cname
= statement
.native_statement
.column_name
(index
)
197 assert not cname
.address_is_null
201 # Get the value of this entry according to its Sqlite type
203 # require: `self.statement.is_open`
204 fun value
: nullable Sqlite3Data
206 assert statement_closed
: statement
.is_open
208 var data_type
= statement
.native_statement
.column_type
(index
)
209 if data_type
.is_integer
then return to_i
210 if data_type
.is_float
then return to_f
211 if data_type
.is_blob
then return to_blob
212 if data_type
.is_null
then return null
213 if data_type
.is_text
then return to_s
217 # Get this entry as `Int`
219 # If the Sqlite type of this entry is not an integer, it will be `CAST` to
220 # integer. If `null`, returns 0.
222 # require: `self.statement.is_open`
225 assert statement_closed
: statement
.is_open
227 return statement
.native_statement
.column_int
(index
)
230 # Get this entry as `Float`
232 # If the Sqlite type of this entry is not a floating point, it will be `CAST`
233 # to float. If `null`, returns 0.0.
235 # require: `self.statement.is_open`
238 assert statement_closed
: statement
.is_open
240 return statement
.native_statement
.column_double
(index
)
243 # Get this entry as `String`
245 # If the Sqlite type of this entry is not text, it will be `CAST` to text.
246 # If null, returns an empty string.
248 # require: `self.statement.is_open`
251 assert statement_closed
: statement
.is_open
253 var c_string
= statement
.native_statement
.column_text
(index
)
254 if c_string
.address_is_null
then return ""
258 # Get this entry as `Blob`
260 # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
261 # If null, returns a NULL pointer.
263 # require: `self.statement.is_open`
266 assert statement_closed
: statement
.is_open
268 # By spec, we must get the pointer before the byte count
269 var pointer
= statement
.native_statement
.column_blob
(index
)
270 var length
= statement
.native_statement
.column_bytes
(index
)
272 return new Blob(pointer
, length
)
276 # Iterator over the rows of a statement result
277 class StatementIterator
278 super Iterator[StatementRow]
280 # Statement linked to `self`
281 var statement
: Statement
285 self.item
= new StatementRow(statement
)
286 self.is_ok
= statement
.native_statement
.step
.is_row
289 redef var item
: StatementRow is noinit
291 redef var is_ok
is noinit
293 # require: `self.statement.is_open`
296 assert statement_closed
: statement
.is_open
298 var err
= statement
.native_statement
.step
301 else if err
.is_done
then
306 # FIXME do something with the error?
311 redef fun finish
do if statement
.close_with_iterator
then statement
.close
314 # A data type supported by Sqlite3
315 interface Sqlite3Data end
317 redef universal Int super Sqlite3Data end
319 redef universal Float super Sqlite3Data end
321 redef class String super Sqlite3Data end
325 # Return `self` between `'`s, escaping `\` and `'`
327 # assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
328 fun to_sql_string
: String
330 return "'{self.replace('\\', "\\\\").replace('\'', "''")}'"
333 # Format the date represented by `self` into an escaped string for SQLite
335 # `self` must be composed of 1 to 3 integers separated by '-'.
336 # An incompatible format will result in an invalid date string.
338 # assert "2016-5-1".to_sql_date_string == "'2016-05-01'"
339 # assert "2016".to_sql_date_string == "'2016-01-01'"
340 fun to_sql_date_string
: String
342 var parts
= self.split
("-")
343 for i
in [parts
.length
.. 3[ do parts
[i
] = "1"
345 var year
= parts
[0].justify
(4, 1.0, '0')
346 var month
= parts
[1].justify
(2, 1.0, '0')
347 var day
= parts
[2].justify
(2, 1.0, '0')
348 return "{year}-{month}-{day}".to_sql_string
356 # Pointer to the beginning of the blob