X-Git-Url: http://nitlanguage.org

diff --git a/lib/sqlite3/sqlite3.nit b/lib/sqlite3/sqlite3.nit
index d8b1625..43af40d 100644
--- a/lib/sqlite3/sqlite3.nit
+++ b/lib/sqlite3/sqlite3.nit
@@ -1,7 +1,6 @@
 # This file is part of NIT ( http://www.nitlanguage.org ).
 #
-# Copyright 2013 Guillaume Auger <jeho@resist.ca>
-# Copyright 2013 Alexis Laferrière <alexis.laf@xymus.net>
+# Copyright 201 Alexis Laferrière <alexis.laf@xymus.net>
 #
 # Licensed under the Apache License, Version 2.0 (the "License");
 # you may not use this file except in compliance with the License.
@@ -15,137 +14,348 @@
 # See the License for the specific language governing permissions and
 # limitations under the License.
 
+# Services to manipulate a Sqlite3 database
+#
+# For more information, refer to the documentation of http://www.sqlite.org/docs.html
 module sqlite3
 
-in "C header" `{
-	#include "sqlite3.h"
-`}
-
-extern class Sqlite3Code `{int`}
-	new ok `{ return SQLITE_OK; `} #         0   /* Successful result */
-	fun is_ok: Bool `{ return recv == SQLITE_OK; `}
-
-	# new  `{ return SQLITE_ERROR; `} #      1   /* SQL error or missing database */
-	# new  `{ return SQLITE_INTERNAL; `} #    2   /* Internal logic error in SQLite */
-	# new  `{ return SQLITE_PERM; `} #        3   /* Access permission denied */
-	# new  `{ return SQLITE_ABORT; `} #       4   /* Callback routine requested an abort */
-	# new  `{ return SQLITE_BUSY; `} #        5   /* The database file is locked */
-	# new  `{ return SQLITE_LOCKED; `} #      6   /* A table in the database is locked */
-	# new  `{ return SQLITE_NOMEM; `} #       7   /* A malloc() failed */
-	# new  `{ return SQLITE_READONLY; `} #    8   /* Attempt to write a readonly database */
-	# new  `{ return SQLITE_INTERRUPT; `} #   9   /* Operation terminated by sqlite3_interrupt()*/
-	# new  `{ return SQLITE_IOERR; `} #      10   /* Some kind of disk I/O error occurred */
-	# new  `{ return SQLITE_CORRUPT; `} #    11   /* The database disk image is malformed */
-	# new  `{ return SQLITE_NOTFOUND; `} #   12   /* Unknown opcode in sqlite3_file_control() */
-	# new  `{ return SQLITE_FULL; `} #       13   /* Insertion failed because database is full */
-	# new  `{ return SQLITE_CANTOPEN; `} #   14   /* Unable to open the database file */
-	# new  `{ return SQLITE_PROTOCOL; `} #   15   /* Database lock protocol error */
-	# new  `{ return SQLITE_EMPTY; `} #      16   /* Database is empty */
-	# new  `{ return SQLITE_SCHEMA; `} #     17   /* The database schema changed */
-	# new  `{ return SQLITE_TOOBIG; `} #     18   /* String or BLOB exceeds size limit */
-	# new  `{ return SQLITE_CONSTRAINT; `} # 19   /* Abort due to constraint violation */
-	# new  `{ return SQLITE_MISMATCH; `} #   20   /* Data type mismatch */
-	# new  `{ return SQLITE_MISUSE; `} #     21   /* Library used incorrectly */
-	# new  `{ return SQLITE_NOLFS; `} #      22   /* Uses OS features not supported on host */
-	# new  `{ return SQLITE_AUTH; `} #       23   /* Authorization denied */
-	# new  `{ return SQLITE_FORMAT; `} #     24   /* Auxiliary database format error */
-	# new  `{ return SQLITE_RANGE; `} #      25   /* 2nd parameter to sqlite3_bind out of range */
-	# new  `{ return SQLITE_NOTADB; `} #     26   /* File opened that is not a database file */
-	# new  `{ return SQLITE_NOTICE; `} #     27   /* Notifications from sqlite3_log() */
-	# new  `{ return SQLITE_WARNING; `} #    28   /* Warnings from sqlite3_log() */
-
-	new row `{ return SQLITE_ROW; `} #        100  /* sqlite3_step() has another row ready */
-	fun is_row: Bool `{ return recv == SQLITE_ROW; `}
-
-	new done `{ return SQLITE_DONE; `} #       101  /* sqlite3_step() has finished executing */
-	fun is_done: Bool `{ return recv == SQLITE_DONE; `}
+private import native_sqlite3
+import core
+
+# A connection to a Sqlite3 database
+class Sqlite3DB
+	private var native_connection: NativeSqlite3
+
+	# Is this connection to the DB open?
+	var is_open = false
+
+	# All `Statement` opened from this connection that must be closed with this connection
+	private var open_statements = new Array[Statement]
+
+	# Open a connection to the database file at `path`
+	init open(path: Text)
+	do
+		init(new NativeSqlite3.open(path.to_cstring))
+		if native_connection.is_valid then is_open = true
+	end
+
+	# Close this connection to the DB and all open statements
+	fun close
+	do
+		if not is_open then return
+
+		is_open = false
+
+		# close open statements
+		for stmt in open_statements do if stmt.is_open then
+			stmt.close
+		end
+
+		native_connection.close
+	end
+
+	# Prepare and return a `Statement`, return `null` on error
+	fun prepare(sql: Text): nullable Statement
+	do
+		var native_stmt = native_connection.prepare(sql.to_cstring)
+		if native_stmt.address_is_null then return null
+
+		var stmt = new Statement(native_stmt)
+		open_statements.add stmt
+		return stmt
+	end
+
+	# Execute the `sql` statement and return `true` on success
+	fun execute(sql: Text): Bool
+	do
+		var err = native_connection.exec(sql.to_cstring)
+		return err.is_ok
+	end
+
+	# Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest`
+	#
+	# This method does not escape special characters.
+	fun create_table(rest: Text): Bool do return execute("CREATE TABLE " + rest)
+
+	# Insert in the DB with a statement beginning with "INSERT ", followed by `rest`
+	#
+	# This method does not escape special characters.
+	fun insert(rest: Text): Bool do return execute("INSERT " + rest)
+
+	# Replace in the DB with a statement beginning with "REPLACE", followed by `rest`
+	#
+	# This method does not escape special characters.
+	fun replace(rest: Text): Bool do return execute("REPLACE " + rest)
+
+	# Select from the DB with a statement beginning with "SELECT ", followed by `rest`
+	#
+	# This method does not escape special characters.
+	fun select(rest: Text): nullable Statement do return prepare("SELECT " + rest)
+
+	# TODO add more prefix here as needed
+
+	# The latest error message, or `null` if there is none
+	fun error: nullable String
+	do
+		if not native_connection.is_valid then
+			var err = sys.sqlite_open_error
+			if err.is_ok then return null
+			return err.to_s
+		end
+
+		var err = native_connection.error
+		if err.is_ok then return null
+		return err.to_s
+	end
+
+	# Returns the id for the last successful insert on the current connection.
+	fun last_insert_rowid: Int do return native_connection.last_insert_rowid
+end
+
+# Prepared Sqlite3 statement
+#
+# Instances of this class are created from `Sqlite3DB::prepare` and
+# its shortcuts: `create_table`, `insert`, `replace` and `select`.
+# The results should be explored with an `iterator`,
+# and each call to `iterator` resets the request.
+# If `close_with_iterator` the iterator calls `close`
+# on this request upon finishing.
+class Statement
+	private var native_statement: NativeStatement
+
+	# Is this statement usable?
+	var is_open = true
+
+	# Should any `iterator` close this statement on `Iterator::finish`?
+	#
+	# If `true`, the default, any `StatementIterator` created by calls to
+	# `iterator` invokes `close` on this request when finished iterating.
+	# Otherwise, `close` must be called manually.
+	var close_with_iterator = true is writable
+
+	# Close and finalize this statement
+	fun close
+	do
+		if not is_open then return
+
+		is_open = false
+		native_statement.finalize
+	end
+
+	# Reset this statement and return a `StatementIterator` to iterate over the result
+	fun iterator: StatementIterator
+	do
+		native_statement.reset
+		return new StatementIterator(self)
+	end
+end
+
+# A row from a `Statement`
+class StatementRow
+	# Statement linked to `self`
+	var statement: Statement
+
+	# Maps the column name to its value
+	fun map: Map[String, nullable Sqlite3Data]
+	do
+		var ret = new ArrayMap[String, nullable Sqlite3Data]
+		for i in [0 .. length[ do
+			var st = self[i]
+			ret[st.name] = st.value
+		end
+		return ret
+	end
+
+	# Number of entries in this row
+	#
+	# require: `self.statement.is_open`
+	fun length: Int
+	do
+		assert statement_closed: statement.is_open
+
+		return statement.native_statement.column_count
+	end
+
+	# Returns the `i`th entry on this row
+	fun [](i: Int): StatementEntry do return new StatementEntry(statement, i)
 end
 
-extern class Statement `{sqlite3_stmt*`}
-
-	fun step: Sqlite3Code `{
-		return sqlite3_step(recv);
-	`}
-
-	fun column_name(i: Int) : String import String::from_cstring `{
-		const char * name = (sqlite3_column_name(recv, i));
-		if(name == NULL){
-			name = "";
-		}
-		char * ret = (char *) name;
-		return new_String_from_cstring(ret);
-	`}
-
-	fun column_bytes(i: Int) : Int `{
-		return sqlite3_column_bytes(recv, i);
-	`}
-
-	fun column_double(i: Int) : Float `{
-		return sqlite3_column_double(recv, i);
-	`}
-
-	fun column_int(i: Int) : Int `{
-		return sqlite3_column_int(recv, i);
-	`}
-
-	fun column_text(i: Int) : String import String::from_cstring `{
-		char * ret = (char *) sqlite3_column_text(recv, i);
-		if( ret == NULL ){
-			ret = "";
-		}
-		return new_String_from_cstring(ret);
-	`}
-
-	fun column_type(i: Int) : Int `{
-		return sqlite3_column_type(recv, i);
-	`}
-
-	#	fun column_blob(i : Int) : String `{
-	#		TODO
-	#	`}
-
-	fun column_count: Int `{
-		return sqlite3_column_count(recv);
-	`}
+# An entry on a `StatementRow`
+class StatementEntry
+	# Statement linked to `self`
+	var statement: Statement
+
+	private var index: Int
+
+	# Name of the column
+	#
+	# require: `self.statement.is_open`
+	var name: String is lazy do
+		assert statement_closed: statement.is_open
+
+		var cname = statement.native_statement.column_name(index)
+		assert not cname.address_is_null
+		return cname.to_s
+	end
+
+	# Get the value of this entry according to its Sqlite type
+	#
+	# require: `self.statement.is_open`
+	fun value: nullable Sqlite3Data
+	do
+		assert statement_closed: statement.is_open
+
+		var data_type = statement.native_statement.column_type(index)
+		if data_type.is_integer then return to_i
+		if data_type.is_float then return to_f
+		if data_type.is_blob then return to_blob
+		if data_type.is_null then return null
+		if data_type.is_text then return to_s
+		abort
+	end
+
+	# Get this entry as `Int`
+	#
+	# If the Sqlite type of this entry is not an integer, it will be `CAST` to
+	# integer. If `null`, returns 0.
+	#
+	# require: `self.statement.is_open`
+	fun to_i: Int
+	do
+		assert statement_closed: statement.is_open
+
+		return statement.native_statement.column_int(index)
+	end
+
+	# Get this entry as `Float`
+	#
+	# If the Sqlite type of this entry is not a floating point, it will be `CAST`
+	# to float. If `null`, returns 0.0.
+	#
+	# require: `self.statement.is_open`
+	fun to_f: Float
+	do
+		assert statement_closed: statement.is_open
+
+		return statement.native_statement.column_double(index)
+	end
+
+	# Get this entry as `String`
+	#
+	# If the Sqlite type of this entry is not text, it will be `CAST` to text.
+	# If null, returns an empty string.
+	#
+	# require: `self.statement.is_open`
+	redef fun to_s
+	do
+		assert statement_closed: statement.is_open
+
+		var c_string = statement.native_statement.column_text(index)
+		if c_string.address_is_null then return ""
+		return c_string.to_s_with_copy
+	end
+
+	# Get this entry as `Blob`
+	#
+	# If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
+	# If null, returns a NULL pointer.
+	#
+	# require: `self.statement.is_open`
+	fun to_blob: Blob
+	do
+		assert statement_closed: statement.is_open
+
+		# By spec, we must get the pointer before the byte count
+		var pointer = statement.native_statement.column_blob(index)
+		var length = statement.native_statement.column_bytes(index)
+
+		return new Blob(pointer, length)
+	end
 end
 
-extern class Sqlite3 `{sqlite3 *`}
-	new open(filename: String) import String::to_cstring `{
-		sqlite3 *self;
-		sqlite3_open(String_to_cstring(filename), &self);
-		return self;
-	`}
+# Iterator over the rows of a statement result
+class StatementIterator
+	super Iterator[StatementRow]
 
-	fun destroy do close
+	# Statement linked to `self`
+	var statement: Statement
 
-	fun close `{ sqlite3_close(recv); `}
+	init
+	do
+		self.item = new StatementRow(statement)
+		self.is_ok = statement.native_statement.step.is_row
+	end
 
-	fun exec(sql : String): Sqlite3Code import String::to_cstring `{
-		return sqlite3_exec(recv, String_to_cstring(sql), 0, 0, 0);
-	`}
+	redef var item: StatementRow is noinit
 
-	fun prepare(sql: String): nullable Statement import String::to_cstring, Statement as nullable `{
-		sqlite3_stmt *stmt;
-		int res = sqlite3_prepare_v2(recv, String_to_cstring(sql), -1, &stmt, 0);
-		if (res == SQLITE_OK)
-			return Statement_as_nullable(stmt);
+	redef var is_ok is noinit
+
+	# require: `self.statement.is_open`
+	redef fun next
+	do
+		assert statement_closed: statement.is_open
+
+		var err = statement.native_statement.step
+		if err.is_row then
+			is_ok = true
+		else if err.is_done then
+			# Clean complete
+			is_ok = false
 		else
-			return null_Statement();
-	`}
-
-	fun last_insert_rowid: Int `{
-		return sqlite3_last_insert_rowid(recv);
-	`}
-
-	fun get_error : Int import String::from_cstring `{
-		return sqlite3_errcode(recv);
-	`}
-
-	fun get_error_str : String import String::from_cstring `{
-		char * err =(char *) sqlite3_errmsg(recv);
-		if(err == NULL){
-			err = "";
-		}
-		return new_String_from_cstring(err);
-	`}
+			# error
+			# FIXME do something with the error?
+			is_ok = false
+		end
+	end
+
+	redef fun finish do if statement.close_with_iterator then statement.close
+end
+
+# A data type supported by Sqlite3
+interface Sqlite3Data end
+
+redef universal Int super Sqlite3Data end
+
+redef universal Float super Sqlite3Data end
+
+redef class String super Sqlite3Data end
+
+redef class Text
+
+	# Return `self` between `'`s, escaping `\` and `'`
+	#
+	#     assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
+	fun to_sql_string: String
+	do
+		return "'{self.replace('\\', "\\\\").replace('\'', "''")}'"
+	end
+
+	# Format the date represented by `self` into an escaped string for SQLite
+	#
+	# `self` must be composed of 1 to 3 integers separated by '-'.
+	# An incompatible format will result in an invalid date string.
+	#
+	#     assert "2016-5-1".to_sql_date_string == "'2016-05-01'"
+	#     assert "2016".to_sql_date_string == "'2016-01-01'"
+	fun to_sql_date_string: String
+	do
+		var parts = self.split("-")
+		for i in [parts.length .. 3[ do parts[i] = "1"
+
+		var year = parts[0].justify(4, 1.0, '0')
+		var month = parts[1].justify(2, 1.0, '0')
+		var day = parts[2].justify(2, 1.0, '0')
+		return "{year}-{month}-{day}".to_sql_string
+	end
+end
+
+# A Sqlite3 blob
+class Blob
+	super Sqlite3Data
+
+	# Pointer to the beginning of the blob
+	var pointer: Pointer
+
+	# Size of the blob
+	var length: Int
 end