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 native_connection
= new NativeSqlite3.open
(path
.to_s
)
39 if native_connection
.is_valid
then is_open
= true
42 # Close this connection to the DB and all open statements
47 # close open statements
48 for stmt
in open_statements
do if stmt
.is_open
then
52 native_connection
.close
55 # Prepare and return a `Statement`, return `null` on error
56 fun prepare
(sql
: Text): nullable Statement
58 var native_stmt
= native_connection
.prepare
(sql
.to_s
)
59 if native_stmt
== null then return null
61 var stmt
= new Statement(native_stmt
)
62 open_statements
.add stmt
66 # Execute the `sql` statement and return `true` on success
67 fun execute
(sql
: Text): Bool
69 var err
= native_connection
.exec
(sql
.to_s
)
73 # Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest`
75 # This method does not escape special characters.
76 fun create_table
(rest
: Text): Bool do return execute
("CREATE TABLE " + rest
)
78 # Insert in the DB with a statement beginning with "INSERT ", followed by `rest`
80 # This method does not escape special characters.
81 fun insert
(rest
: Text): Bool do return execute
("INSERT " + rest
)
83 # Replace in the DB with a statement beginning with "REPLACE", followed by `rest`
85 # This method does not escape special characters.
86 fun replace
(rest
: Text): Bool do return execute
("REPLACE " + rest
)
88 # Select from the DB with a statement beginning with "SELECT ", followed by `rest`
90 # This method does not escape special characters.
91 fun select
(rest
: Text): nullable Statement do return prepare
("SELECT " + rest
)
93 # TODO add more prefix here as needed
95 # The latest error message, or `null` if there is none
96 fun error
: nullable String
98 if not native_connection
.is_valid
then
99 var err
= sys
.sqlite_open_error
100 if err
== null then return null
104 var err
= native_connection
.error
105 if err
.is_ok
then return null
109 # Returns the id for the last successful insert on the current connection.
110 fun last_insert_rowid
: Int do return native_connection
.last_insert_rowid
113 # A prepared Sqlite3 statement, created from `Sqlite3DB::prepare` or `Sqlite3DB::select`
115 private var native_statement
: NativeStatement
117 # Is this statement usable?
120 # Close and finalize this statement
124 native_statement
.finalize
127 # Reset this statement and return a `StatementIterator` to iterate over the result
128 fun iterator
: StatementIterator
130 native_statement
.reset
131 return new StatementIterator(self)
135 # A row from a `Statement`
137 # Statement linked to `self`
138 var statement
: Statement
140 # Number of entries in this row
142 # require: `self.statement.is_open`
145 assert statement_closed
: statement
.is_open
147 return statement
.native_statement
.column_count
150 # Returns the `i`th entry on this row
151 fun [](i
: Int): StatementEntry do return new StatementEntry(statement
, i
)
154 # An entry on a `StatementRow`
156 # Statement linked to `self`
157 var statement
: Statement
159 private var index
: Int
163 # require: `self.statement.is_open`
164 var name
: String is lazy
do
165 assert statement_closed
: statement
.is_open
167 return statement
.native_statement
.column_name
(index
)
170 # Get the value of this entry according to its Sqlite type
172 # require: `self.statement.is_open`
173 fun value
: nullable Sqlite3Data
175 assert statement_closed
: statement
.is_open
177 var data_type
= statement
.native_statement
.column_type
(index
)
178 if data_type
.is_integer
then return to_i
179 if data_type
.is_float
then return to_f
180 if data_type
.is_blob
then return to_blob
181 if data_type
.is_null
then return null
182 if data_type
.is_text
then return to_s
186 # Get this entry as `Int`
188 # If the Sqlite type of this entry is not an integer, it will be `CAST` to
189 # integer. If `null`, returns 0.
191 # require: `self.statement.is_open`
194 assert statement_closed
: statement
.is_open
196 return statement
.native_statement
.column_int
(index
)
199 # Get this entry as `Float`
201 # If the Sqlite type of this entry is not a floating point, it will be `CAST`
202 # to float. If `null`, returns 0.0.
204 # require: `self.statement.is_open`
207 assert statement_closed
: statement
.is_open
209 return statement
.native_statement
.column_double
(index
)
212 # Get this entry as `String`
214 # If the Sqlite type of this entry is not text, it will be `CAST` to text.
215 # If null, returns an empty string.
217 # require: `self.statement.is_open`
220 assert statement_closed
: statement
.is_open
222 var native_string
= statement
.native_statement
.column_text
(index
)
223 if native_string
.address_is_null
then return ""
224 return native_string
.to_s_with_copy
227 # Get this entry as `Blob`
229 # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
230 # If null, returns a NULL pointer.
232 # require: `self.statement.is_open`
235 assert statement_closed
: statement
.is_open
237 # By spec, we must get the pointer before the byte count
238 var pointer
= statement
.native_statement
.column_blob
(index
)
239 var length
= statement
.native_statement
.column_bytes
(index
)
241 return new Blob(pointer
, length
)
245 # Iterator over the rows of a statement result
246 class StatementIterator
247 super Iterator[StatementRow]
249 # Statement linked to `self`
250 var statement
: Statement
254 self.item
= new StatementRow(statement
)
255 self.is_ok
= statement
.native_statement
.step
.is_row
258 redef var item
: StatementRow is noinit
260 redef var is_ok
: Bool is noinit
262 # require: `self.statement.is_open`
265 assert statement_closed
: statement
.is_open
267 var err
= statement
.native_statement
.step
270 else if err
.is_done
then
275 # FIXME do something with the error?
281 # A data type supported by Sqlite3
282 interface Sqlite3Data end
284 redef universal Int super Sqlite3Data end
285 redef universal Float super Sqlite3Data end
289 # Return `self` between `'`s and escaping any extra `'`
291 # assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
292 fun to_sql_string
: String
294 return "'{self.replace('\'', "''")}'"
302 # Pointer to the beginning of the blob