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
110 # A prepared Sqlite3 statement, created from `Sqlite3DB::prepare` or `Sqlite3DB::select`
112 private var native_statement
: NativeStatement
114 private init(ns
: NativeStatement) do self.native_statement
= ns
116 # Is this statement usable?
119 # Close and finalize this statement
123 native_statement
.finalize
126 # Reset this statement and return a `StatementIterator` to iterate over the result
127 fun iterator
: StatementIterator
129 native_statement
.reset
130 native_statement
.step
131 return new StatementIterator(self)
136 # Statement linked to `self`
137 var statement
: Statement
139 private init(s
: Statement) do self.statement
= s
141 # Number of entries in this row
143 # require: `self.statement.is_open`
146 assert statement_closed
: statement
.is_open
148 return statement
.native_statement
.column_count
151 # Returns the `i`th entry on this row
152 fun [](i
: Int): StatementEntry do return new StatementEntry(statement
, i
)
155 # An entry on a `StatementRow`
157 # Statement linked to `self`
158 var statement
: Statement
160 private var index
: Int
162 private init(s
: Statement, i
: Int)
170 # require: `self.statement.is_open`
171 fun name
: String is cached
do
172 assert statement_closed
: statement
.is_open
174 return statement
.native_statement
.column_name
(index
)
177 # Get the value of this entry according to its Sqlite type
179 # require: `self.statement.is_open`
180 fun value
: nullable Sqlite3Data
182 assert statement_closed
: statement
.is_open
184 var data_type
= statement
.native_statement
.column_type
(index
)
185 if data_type
.is_integer
then return to_i
186 if data_type
.is_float
then return to_f
187 if data_type
.is_blob
then return to_blob
188 if data_type
.is_null
then return null
189 if data_type
.is_text
then return to_s
193 # Get this entry as `Int`
195 # If the Sqlite type of this entry is not an integer, it will be `CAST` to
196 # integer. If `null`, returns 0.
198 # require: `self.statement.is_open`
201 assert statement_closed
: statement
.is_open
203 return statement
.native_statement
.column_int
(index
)
206 # Get this entry as `Float`
208 # If the Sqlite type of this entry is not a floating point, it will be `CAST`
209 # to float. If `null`, returns 0.0.
211 # require: `self.statement.is_open`
214 assert statement_closed
: statement
.is_open
216 return statement
.native_statement
.column_double
(index
)
219 # Get this entry as `String`
221 # If the Sqlite type of this entry is not text, it will be `CAST` to text.
222 # If null, returns an empty string.
224 # require: `self.statement.is_open`
227 assert statement_closed
: statement
.is_open
229 var native_string
= statement
.native_statement
.column_text
(index
)
230 if native_string
.address_is_null
then return ""
231 return native_string
.to_s
234 # Get this entry as `Blob`
236 # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
237 # If null, returns a NULL pointer.
239 # require: `self.statement.is_open`
242 assert statement_closed
: statement
.is_open
244 # By spec, we must get the pointer before the byte count
245 var pointer
= statement
.native_statement
.column_blob
(index
)
246 var length
= statement
.native_statement
.column_bytes
(index
)
248 return new Blob(pointer
, length
)
252 # Iterator over the rows of a statement result
253 class StatementIterator
254 super Iterator[StatementRow]
256 # Statement linked to `self`
257 var statement
: Statement
259 private init(s
: Statement)
262 self.item
= new StatementRow(s
)
265 redef var item
: StatementRow
267 redef var is_ok
= true
269 # require: `self.statement.is_open`
272 assert statement_closed
: statement
.is_open
274 var err
= statement
.native_statement
.step
277 else if err
.is_done
then
282 # FIXME do something with the error?
288 # A data type supported by Sqlite3
289 interface Sqlite3Data end
291 redef universal Int super Sqlite3Data end
292 redef universal Float super Sqlite3Data end
296 # Return `self` between `'`s and escaping any extra `'`
298 # assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
299 fun to_sql_string
: String
301 return "'{self.replace('\'', "''")}'"
309 private init(pointer
: Pointer, length
: Int)
311 self.pointer
= pointer