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 # Number of entries in this row
160 # require: `self.statement.is_open`
163 assert statement_closed
: statement
.is_open
165 return statement
.native_statement
.column_count
168 # Returns the `i`th entry on this row
169 fun [](i
: Int): StatementEntry do return new StatementEntry(statement
, i
)
172 # An entry on a `StatementRow`
174 # Statement linked to `self`
175 var statement
: Statement
177 private var index
: Int
181 # require: `self.statement.is_open`
182 var name
: String is lazy
do
183 assert statement_closed
: statement
.is_open
185 var cname
= statement
.native_statement
.column_name
(index
)
186 assert not cname
.address_is_null
190 # Get the value of this entry according to its Sqlite type
192 # require: `self.statement.is_open`
193 fun value
: nullable Sqlite3Data
195 assert statement_closed
: statement
.is_open
197 var data_type
= statement
.native_statement
.column_type
(index
)
198 if data_type
.is_integer
then return to_i
199 if data_type
.is_float
then return to_f
200 if data_type
.is_blob
then return to_blob
201 if data_type
.is_null
then return null
202 if data_type
.is_text
then return to_s
206 # Get this entry as `Int`
208 # If the Sqlite type of this entry is not an integer, it will be `CAST` to
209 # integer. If `null`, returns 0.
211 # require: `self.statement.is_open`
214 assert statement_closed
: statement
.is_open
216 return statement
.native_statement
.column_int
(index
)
219 # Get this entry as `Float`
221 # If the Sqlite type of this entry is not a floating point, it will be `CAST`
222 # to float. If `null`, returns 0.0.
224 # require: `self.statement.is_open`
227 assert statement_closed
: statement
.is_open
229 return statement
.native_statement
.column_double
(index
)
232 # Get this entry as `String`
234 # If the Sqlite type of this entry is not text, it will be `CAST` to text.
235 # If null, returns an empty string.
237 # require: `self.statement.is_open`
240 assert statement_closed
: statement
.is_open
242 var native_string
= statement
.native_statement
.column_text
(index
)
243 if native_string
.address_is_null
then return ""
244 return native_string
.to_s_with_copy
247 # Get this entry as `Blob`
249 # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
250 # If null, returns a NULL pointer.
252 # require: `self.statement.is_open`
255 assert statement_closed
: statement
.is_open
257 # By spec, we must get the pointer before the byte count
258 var pointer
= statement
.native_statement
.column_blob
(index
)
259 var length
= statement
.native_statement
.column_bytes
(index
)
261 return new Blob(pointer
, length
)
265 # Iterator over the rows of a statement result
266 class StatementIterator
267 super Iterator[StatementRow]
269 # Statement linked to `self`
270 var statement
: Statement
274 self.item
= new StatementRow(statement
)
275 self.is_ok
= statement
.native_statement
.step
.is_row
278 redef var item
: StatementRow is noinit
280 redef var is_ok
is noinit
282 # require: `self.statement.is_open`
285 assert statement_closed
: statement
.is_open
287 var err
= statement
.native_statement
.step
290 else if err
.is_done
then
295 # FIXME do something with the error?
300 redef fun finish
do if statement
.close_with_iterator
then statement
.close
303 # A data type supported by Sqlite3
304 interface Sqlite3Data end
306 redef universal Int super Sqlite3Data end
308 redef universal Float super Sqlite3Data end
310 redef class String super Sqlite3Data end
314 # Return `self` between `'`s, escaping `\` and `'`
316 # assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
317 fun to_sql_string
: String
319 return "'{self.replace('\\', "\\\\").replace('\'', "''")}'"
322 # Format the date represented by `self` into an escaped string for SQLite
324 # `self` must be composed of 1 to 3 integers separated by '-'.
325 # An incompatible format will result in an invalid date string.
327 # assert "2016-5-1".to_sql_date_string == "'2016-05-01'"
328 # assert "2016".to_sql_date_string == "'2016-01-01'"
329 fun to_sql_date_string
: String
331 var parts
= self.split
("-")
332 for i
in [parts
.length
.. 3[ do parts
[i
] = "1"
334 var year
= parts
[0].justify
(4, 1.0, '0')
335 var month
= parts
[1].justify
(2, 1.0, '0')
336 var day
= parts
[2].justify
(2, 1.0, '0')
337 return "{year}-{month}-{day}".to_sql_string
345 # Pointer to the beginning of the blob