[hyena/sqlite] [Hyena.Data.Sqlite] Better error checking in binding
- From: Gabriel Burt <gburt src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [hyena/sqlite] [Hyena.Data.Sqlite] Better error checking in binding
- Date: Thu, 11 Nov 2010 00:17:17 +0000 (UTC)
commit 7fc7ae3b3006500d581234a9d3495b450fc2773c
Author: Gabriel Burt <gabriel burt gmail com>
Date: Wed Nov 10 18:15:03 2010 -0600
[Hyena.Data.Sqlite] Better error checking in binding
.../Hyena.Data.Sqlite/HyenaSqliteCommand.cs | 2 +-
Hyena.Data.Sqlite/Hyena.Data.Sqlite/Sqlite.cs | 99 +++++++-
.../Hyena.Data.Sqlite/Tests/SqliteTests.cs | 270 ++++++++++++--------
3 files changed, 251 insertions(+), 120 deletions(-)
---
diff --git a/Hyena.Data.Sqlite/Hyena.Data.Sqlite/HyenaSqliteCommand.cs b/Hyena.Data.Sqlite/Hyena.Data.Sqlite/HyenaSqliteCommand.cs
index 44ffbdb..a096834 100644
--- a/Hyena.Data.Sqlite/Hyena.Data.Sqlite/HyenaSqliteCommand.cs
+++ b/Hyena.Data.Sqlite/Hyena.Data.Sqlite/HyenaSqliteCommand.cs
@@ -91,7 +91,7 @@ namespace Hyena.Data.Sqlite
result = null;
int execution_ms = 0;
- bool dispose_command = true;
+ bool dispose_command = ReaderDisposes;
var sql_command = connection.CreateStatement (CurrentSqlText);
sql_command.ReaderDisposes = ReaderDisposes;
hconnection.OnExecuting (sql_command);
diff --git a/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Sqlite.cs b/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Sqlite.cs
index da0e920..f0f88a6 100644
--- a/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Sqlite.cs
+++ b/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Sqlite.cs
@@ -12,6 +12,8 @@ namespace Hyena.Data.Sqlite
IntPtr ptr;
internal IntPtr Ptr { get { return ptr; } }
+ internal List<Statement> Statements = new List<Statement> ();
+
public string DbPath { get; private set; }
public long LastInsertRowId {
@@ -24,11 +26,24 @@ namespace Hyena.Data.Sqlite
CheckError (Native.sqlite3_open (Encoding.UTF8.GetBytes (dbPath), out ptr));
if (ptr == IntPtr.Zero)
throw new Exception ("Unable to open connection");
+
+ Native.sqlite3_extended_result_codes (ptr, 1);
}
public void Dispose ()
{
if (ptr != IntPtr.Zero) {
+ lock (Statements) {
+ var stmts = Statements.ToArray ();
+
+ if (stmts.Length > 0)
+ Hyena.Log.DebugFormat ("Connection disposing of {0} remaining statements", stmts.Length);
+
+ foreach (var stmt in stmts) {
+ stmt.Dispose ();
+ }
+ }
+
CheckError (Native.sqlite3_close (ptr));
ptr = IntPtr.Zero;
}
@@ -44,12 +59,17 @@ namespace Hyena.Data.Sqlite
CheckError (errorCode, "");
}
- internal void CheckError (int errorCode, string msg)
+ internal void CheckError (int errorCode, string sql)
{
if (errorCode == 0 || errorCode == 100 || errorCode == 101)
return;
- throw new Exception (msg + Native.sqlite3_errmsg16 (Ptr).PtrToString ());
+ string errmsg = Native.sqlite3_errmsg16 (Ptr).PtrToString ();
+ if (sql != null) {
+ errmsg = String.Format ("{0} (SQL: {1})", errmsg, sql);
+ }
+
+ throw new SqliteException (errorCode, errmsg);
}
public Statement CreateStatement (string sql)
@@ -117,6 +137,16 @@ namespace Hyena.Data.Sqlite
}
}
+ public class SqliteException : Exception
+ {
+ public int ErrorCode { get; private set; }
+
+ public SqliteException (int errorCode, string message) : base (String.Format ("Sqlite error {0}: {1}", errorCode, message))
+ {
+ ErrorCode = errorCode;
+ }
+ }
+
public interface IDataReader : IDisposable
{
bool Read ();
@@ -131,14 +161,17 @@ namespace Hyena.Data.Sqlite
Connection connection;
bool bound;
QueryReader reader;
+ bool disposed;
internal IntPtr Ptr { get { return ptr; } }
internal bool Bound { get { return bound; } }
internal Connection Connection { get { return connection; } }
+ public bool IsDisposed { get { return disposed; } }
+
public string CommandText { get; private set; }
public int ParameterCount { get; private set; }
- public bool ReaderDisposes { get; set; }
+ public bool ReaderDisposes { get; internal set; }
internal event EventHandler Disposed;
@@ -148,7 +181,12 @@ namespace Hyena.Data.Sqlite
this.connection = connection;
IntPtr pzTail = IntPtr.Zero;
- connection.CheckError (Native.sqlite3_prepare16_v2 (connection.Ptr, sql, sql.Length * 2, out ptr, out pzTail), sql);
+ CheckError (Native.sqlite3_prepare16_v2 (connection.Ptr, sql, -1, out ptr, out pzTail));
+
+ lock (Connection.Statements) {
+ Connection.Statements.Add (this);
+ }
+
if (pzTail != IntPtr.Zero && Marshal.ReadByte (pzTail) != 0) {
Dispose ();
throw new ArgumentException ("sql", String.Format ("This sqlite binding does not support multiple commands in one statement:\n {0}", sql));
@@ -158,12 +196,31 @@ namespace Hyena.Data.Sqlite
reader = new QueryReader () { Statement = this };
}
+ internal void CheckDisposed ()
+ {
+ if (disposed)
+ throw new InvalidOperationException ("Statement is disposed");
+ }
+
+ private string ShortSql { get { return CommandText.Substring (0, Math.Min (CommandText.Length, 20)); } }
+
public void Dispose ()
{
+ if (disposed)
+ return;
+
+ disposed = true;
if (ptr != IntPtr.Zero) {
- connection.CheckError (Native.sqlite3_finalize (ptr));
+ // Don't check for error here, because if the most recent evaluation had an error finalize will return it too
+ // See http://sqlite.org/c3ref/finalize.html
+ Native.sqlite3_finalize (ptr);
+
ptr = IntPtr.Zero;
+ lock (Connection.Statements) {
+ Connection.Statements.Remove (this);
+ }
+
var h = Disposed;
if (h != null) {
h (this, EventArgs.Empty);
@@ -178,10 +235,11 @@ namespace Hyena.Data.Sqlite
public Statement Bind (params object [] vals)
{
+ CheckDisposed ();
if (vals == null || vals.Length != ParameterCount || ParameterCount == 0)
throw new ArgumentException ("vals", String.Format ("Statement has {0} parameters", ParameterCount));
- connection.CheckError (Native.sqlite3_reset (ptr));
+ Reset ();
for (int i = 1; i <= vals.Length; i++) {
int code = 0;
@@ -205,16 +263,27 @@ namespace Hyena.Data.Sqlite
code = Native.sqlite3_bind_text16 (Ptr, i, str, str.Length * 2, (IntPtr)(-1));
}
- connection.CheckError (code);
+ CheckError (code);
}
bound = true;
return this;
}
+ internal void CheckError (int code)
+ {
+ connection.CheckError (code, CommandText);
+ }
+
+ private void Reset ()
+ {
+ CheckError (Native.sqlite3_reset (ptr));
+ }
+
public IEnumerator<IDataReader> GetEnumerator ()
{
- connection.CheckError (Native.sqlite3_reset (ptr));
+ CheckDisposed ();
+ Reset ();
while (reader.Read ()) {
yield return reader;
}
@@ -227,17 +296,22 @@ namespace Hyena.Data.Sqlite
public Statement Execute ()
{
+ CheckDisposed ();
+ Reset ();
reader.Read ();
return this;
}
public object QueryScalar ()
{
+ CheckDisposed ();
+ Reset ();
return reader.Read () ? reader[0] : null;
}
public QueryReader Query ()
{
+ CheckDisposed ();
return reader;
}
}
@@ -260,6 +334,7 @@ namespace Hyena.Data.Sqlite
public int FieldCount {
get {
if (column_count == -1) {
+ Statement.CheckDisposed ();
column_count = Native.sqlite3_column_count (Ptr);
}
return column_count;
@@ -268,6 +343,7 @@ namespace Hyena.Data.Sqlite
public bool Read ()
{
+ Statement.CheckDisposed ();
if (Statement.ParameterCount > 0 && !Statement.Bound)
throw new InvalidOperationException ("Statement not bound");
@@ -275,13 +351,14 @@ namespace Hyena.Data.Sqlite
if (code == ROW) {
return true;
} else {
- Statement.Connection.CheckError (code);
+ Statement.CheckError (code);
return false;
}
}
public object this[int i] {
get {
+ Statement.CheckDisposed ();
int type = Native.sqlite3_column_type (Ptr, i);
switch (type) {
case SQLITE_INTEGER:
@@ -302,6 +379,7 @@ namespace Hyena.Data.Sqlite
public object this[string columnName] {
get {
+ Statement.CheckDisposed ();
if (columns == null) {
columns = new Dictionary<string, int> ();
for (int i = 0; i < FieldCount; i++) {
@@ -359,6 +437,9 @@ namespace Hyena.Data.Sqlite
[DllImport(SQLITE_DLL, CharSet = CharSet.Unicode)]
internal static extern int sqlite3_create_collation16(IntPtr db, string strName, int eTextRep, IntPtr ctx, SqliteCollation fcompare);
+ [DllImport(SQLITE_DLL)]
+ internal static extern int sqlite3_extended_result_codes (IntPtr db, int onoff);
+
// Statement functions
[DllImport(SQLITE_DLL, CharSet = CharSet.Unicode)]
internal static extern int sqlite3_prepare16_v2(IntPtr db, string pSql, int nBytes, out IntPtr stmt, out IntPtr ptrRemain);
diff --git a/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Tests/SqliteTests.cs b/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Tests/SqliteTests.cs
index eb9c5bd..f4b7b4b 100644
--- a/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Tests/SqliteTests.cs
+++ b/Hyena.Data.Sqlite/Hyena.Data.Sqlite/Tests/SqliteTests.cs
@@ -34,93 +34,106 @@ using System.Linq;
using NUnit.Framework;
using Hyena.Data.Sqlite;
-namespace Hyena.Data.Sqlite.Tests
+namespace Hyena.Data.Sqlite
{
[TestFixture]
public class SqliteTests
{
+ Connection con;
+ string dbfile = "hyena-data-sqlite-test.db";
+
+ [SetUp]
+ public void Setup ()
+ {
+ con = new Connection (dbfile);
+ }
+
+ [TearDown]
+ public void TearDown ()
+ {
+ Assert.AreEqual (0, con.Statements.Count);
+ con.Dispose ();
+ System.IO.File.Delete (dbfile);
+ }
+
[Test]
public void Test ()
{
- using (var con = new Connection (":memory:")) {
- using (var stmt = con.CreateStatement ("SELECT 'foobar' as version")) {
- Assert.AreEqual ("foobar", stmt.First ()[0]);
- Assert.AreEqual ("foobar", stmt.First ()["version"]);
- }
+ using (var stmt = con.CreateStatement ("SELECT 'foobar' as version")) {
+ Assert.AreEqual ("foobar", stmt.QueryScalar ());
+ Assert.AreEqual ("foobar", stmt.First ()[0]);
+ Assert.AreEqual ("foobar", stmt.First ()["version"]);
+ }
- using (var stmt = con.CreateStatement ("SELECT 2 + 5 as res")) {
- Assert.AreEqual (7, stmt.First ()[0]);
- Assert.AreEqual (7, stmt.First ()["res"]);
+ using (var stmt = con.CreateStatement ("SELECT 2 + 5 as res")) {
+ Assert.AreEqual (7, stmt.QueryScalar ());
+ Assert.AreEqual (7, stmt.First ()[0]);
+ Assert.AreEqual (7, stmt.First ()["res"]);
- try {
- stmt.Bind ();
- Assert.Fail ("should not be able to bind parameterless statement");
- } catch {}
- }
+ try {
+ stmt.Bind ();
+ Assert.Fail ("should not be able to bind parameterless statement");
+ } catch {}
}
}
[Test]
public void TestBinding ()
{
- using (var con = new Connection (":memory:")) {
- using (var stmt = con.CreateStatement ("SELECT ? as version")) {
- try {
- stmt.First ();
- Assert.Fail ("unbound statement should have thrown an exception");
- } catch {}
-
- try {
- stmt.Bind (1, 2);
- Assert.Fail ("bound statement with the wrong number of parameters");
- } catch {}
-
- try {
- stmt.Bind ();
- Assert.Fail ("bound statement with the wrong number of parameters");
- } catch {}
-
- stmt.Bind (21);
- Assert.AreEqual (21, stmt.First ()[0]);
- Assert.AreEqual (21, stmt.First ()["version"]);
-
- stmt.Bind ("ffoooo");
- Assert.AreEqual ("ffoooo", stmt.First ()[0]);
- Assert.AreEqual ("ffoooo", stmt.First ()["version"]);
- }
+ using (var stmt = con.CreateStatement ("SELECT ? as version")) {
+ try {
+ stmt.First ();
+ Assert.Fail ("unbound statement should have thrown an exception");
+ } catch {}
- using (var stmt = con.CreateStatement ("SELECT ? as a, ? as b, ?")) {
- stmt.Bind (1, "two", 3.3);
- Assert.AreEqual (1, stmt.First ()[0]);
- Assert.AreEqual ("two", stmt.First ()["b"]);
- Assert.AreEqual (3.3, stmt.First ()[2]);
- }
+ try {
+ stmt.Bind (1, 2);
+ Assert.Fail ("bound statement with the wrong number of parameters");
+ } catch {}
+
+ try {
+ stmt.Bind ();
+ Assert.Fail ("bound statement with the wrong number of parameters");
+ } catch {}
+
+ stmt.Bind (21);
+ Assert.AreEqual (21, stmt.First ()[0]);
+ Assert.AreEqual (21, stmt.First ()["version"]);
+
+ stmt.Bind ("ffoooo");
+ Assert.AreEqual ("ffoooo", stmt.First ()[0]);
+ Assert.AreEqual ("ffoooo", stmt.First ()["version"]);
+ }
+
+ using (var stmt = con.CreateStatement ("SELECT ? as a, ? as b, ?")) {
+ stmt.Bind (1, "two", 3.3);
+ Assert.AreEqual (1, stmt.QueryScalar ());
+ Assert.AreEqual ("two", stmt.First ()["b"]);
+ Assert.AreEqual (3.3, stmt.First ()[2]);
}
}
[Test]
public void CreateTable ()
{
- using (var con = new Connection (":memory:")) {
- CreateUsers (con);
+ CreateUsers (con);
- using (var stmt = con.CreateStatement ("SELECT COUNT(*) FROM Users")) {
- Assert.AreEqual (2, stmt.First ()[0]);
- }
+ using (var stmt = con.CreateStatement ("SELECT COUNT(*) FROM Users")) {
+ Assert.AreEqual (2, stmt.QueryScalar ());
+ }
- using (var stmt = con.CreateStatement ("SELECT ID, Name FROM Users ORDER BY NAME")) {
- var row1 = stmt.First ();
- Assert.AreEqual ("Aaron", row1["Name"]);
- Assert.AreEqual ("Aaron", row1[1]);
- Assert.AreEqual (2, row1["ID"]);
- Assert.AreEqual (2, row1[0]);
-
- var row2 = stmt.Skip (1).First ();
- Assert.AreEqual ("Gabriel", row2["Name"]);
- Assert.AreEqual ("Gabriel", row2[1]);
- Assert.AreEqual (1, row2["ID"]);
- Assert.AreEqual (1, row2[0]);
- }
+ using (var stmt = con.CreateStatement ("SELECT ID, Name FROM Users ORDER BY NAME")) {
+ var row1 = stmt.First ();
+ Assert.AreEqual ("Aaron", row1["Name"]);
+ Assert.AreEqual ("Aaron", row1[1]);
+ Assert.AreEqual (2, row1["ID"]);
+ Assert.AreEqual (2, row1[0]);
+
+ var row2 = stmt.Skip (1).First ();
+ Assert.AreEqual ("Gabriel", row2["Name"]);
+ Assert.AreEqual ("Gabriel", row2[1]);
+ Assert.AreEqual (1, row2["ID"]);
+ Assert.AreEqual (1, row2[0]);
}
}
@@ -132,6 +145,10 @@ namespace Hyena.Data.Sqlite.Tests
using (var stmt = con.CreateStatement ("CREATE TABLE Users (ID INTEGER PRIMARY KEY, Name TEXT)")) {
stmt.Execute ();
+ try {
+ stmt.Execute ();
+ Assert.Fail ("Shouldn't be able to create table; already exists");
+ } catch {}
}
using (var stmt = con.CreateStatement ("INSERT INTO Users (Name) VALUES (?)")) {
@@ -143,74 +160,107 @@ namespace Hyena.Data.Sqlite.Tests
[Test]
public void CheckInterleavedAccess ()
{
- using (var con = new Connection (":memory:")) {
- CreateUsers (con);
+ CreateUsers (con);
- var q1 = con.Query ("SELECT ID, Name FROM Users ORDER BY NAME");
- var q2 = con.Query ("SELECT ID, Name FROM Users ORDER BY ID");
+ var q1 = con.Query ("SELECT ID, Name FROM Users ORDER BY NAME");
+ var q2 = con.Query ("SELECT ID, Name FROM Users ORDER BY ID");
- Assert.IsTrue (q1.Read ());
- Assert.IsTrue (q2.Read ());
- Assert.AreEqual ("Aaron", q1["Name"]);
- Assert.AreEqual ("Gabriel", q2["Name"]);
+ Assert.IsTrue (q1.Read ());
+ Assert.IsTrue (q2.Read ());
+ Assert.AreEqual ("Aaron", q1["Name"]);
+ Assert.AreEqual ("Gabriel", q2["Name"]);
- Assert.IsTrue (q2.Read ());
- Assert.AreEqual ("Aaron", q2["Name"]);
- Assert.IsTrue (q1.Read ());
- Assert.AreEqual ("Gabriel", q1["Name"]);
+ Assert.IsTrue (q2.Read ());
+ Assert.AreEqual ("Aaron", q2["Name"]);
+ Assert.IsTrue (q1.Read ());
+ Assert.AreEqual ("Gabriel", q1["Name"]);
- q1.Dispose ();
- q2.Dispose ();
- }
+ q1.Dispose ();
+ q2.Dispose ();
+ }
+
+ [Test]
+ public void ConnectionDisposesStatements ()
+ {
+ var stmt = con.CreateStatement ("SELECT 1");
+ Assert.IsFalse (stmt.IsDisposed);
+ con.Dispose ();
+ Assert.IsTrue (stmt.IsDisposed);
}
[Test]
public void MultipleCommands ()
{
- using (var con = new Connection (":memory:")) {
- try {
- using (var stmt = con.CreateStatement ("CREATE TABLE Lusers (ID INTEGER PRIMARY KEY, Name TEXT); INSERT INTO Lusers (Name) VALUES ('Foo')")) {
- stmt.Execute ();
- }
- Assert.Fail ("Mutliple commands aren't supported in this sqlite binding");
- } catch {}
+ try {
+ using (var stmt = con.CreateStatement ("CREATE TABLE Lusers (ID INTEGER PRIMARY KEY, Name TEXT); INSERT INTO Lusers (Name) VALUES ('Foo')")) {
+ stmt.Execute ();
+ }
+ Assert.Fail ("Mutliple commands aren't supported in this sqlite binding");
+ } catch {}
+ }
+
+ [Test]
+ public void Query ()
+ {
+ using (var q = con.Query ("SELECT 7")) {
+ int rows = 0;
+ while (q.Read ()) {
+ Assert.AreEqual (7, q[0]);
+ rows++;
+ }
+ Assert.AreEqual (1, rows);
}
}
[Test]
+ public void QueryScalar ()
+ {
+ Assert.AreEqual (7, con.QueryScalar ("SELECT 7"));
+ }
+
+ [Test]
+ public void Execute ()
+ {
+ try {
+ con.QueryScalar ("SELECT COUNT(*) FROM Users");
+ Assert.Fail ("Should have thrown an exception");
+ } catch {}
+ con.Execute ("CREATE TABLE Users (ID INTEGER PRIMARY KEY, Name TEXT)");
+ Assert.AreEqual (0, con.QueryScalar ("SELECT COUNT(*) FROM Users"));
+ }
+
+ [Test]
public void Functions ()
{
- using (var con = new Connection (":memory:")) {
- con.AddFunction<Md5Function> ();
+ con.AddFunction<Md5Function> ();
- using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?)")) {
- Assert.AreEqual ("ae2b1fca515949e5d54fb22b8ed95575", stmt.Bind (1, "testing").QueryScalar ());
- Assert.AreEqual (null, stmt.Bind (1, null).QueryScalar ());
- }
+ using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?)")) {
+ Assert.AreEqual ("ae2b1fca515949e5d54fb22b8ed95575", stmt.Bind (1, "testing").QueryScalar ());
+ Assert.AreEqual (null, stmt.Bind (1, null).QueryScalar ());
+ }
- using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?, ?)")) {
- Assert.AreEqual ("ae2b1fca515949e5d54fb22b8ed95575", stmt.Bind (2, "test", "ing").QueryScalar ());
- Assert.AreEqual (null, stmt.Bind (2, null, null).QueryScalar ());
- }
+ using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?, ?)")) {
+ Assert.AreEqual ("ae2b1fca515949e5d54fb22b8ed95575", stmt.Bind (2, "test", "ing").QueryScalar ());
+ Assert.AreEqual (null, stmt.Bind (2, null, null).QueryScalar ());
+ }
- using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?, ?, ?)")) {
- Assert.AreEqual (null, stmt.Bind (3, null, "", null).QueryScalar ());
-
- try {
- con.RemoveFunction<Md5Function> ();
- Assert.Fail ("Removed function while statement active");
- } catch (Exception e) {
- Assert.AreEqual ("Unable to delete/modify user-function due to active statements", e.Message);
- }
- }
+ using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?, ?, ?)")) {
+ Assert.AreEqual (null, stmt.Bind (3, null, "", null).QueryScalar ());
try {
- using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?, ?, ?)")) {
- Assert.AreEqual ("ae2b1fca515949e5d54fb22b8ed95575", stmt.QueryScalar ());
- Assert.Fail ("Function HYENA_MD5 should no longer exist");
- }
- } catch {}
+ con.RemoveFunction<Md5Function> ();
+ Assert.Fail ("Removed function while statement active");
+ } catch (SqliteException e) {
+ Assert.AreEqual (5, e.ErrorCode);
+ }
}
+
+ try {
+ using (var stmt = con.CreateStatement ("SELECT HYENA_MD5(?, ?, ?, ?)")) {
+ Assert.AreEqual ("ae2b1fca515949e5d54fb22b8ed95575", stmt.QueryScalar ());
+ Assert.Fail ("Function HYENA_MD5 should no longer exist");
+ }
+ } catch {}
}
}
}
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
