cvsimport

[beagle.git] / beagled / Mono.Data.SqliteClient / SqliteCommand.cs

//
// Mono.Data.SqliteClient.SqliteCommand.cs
//
// Represents a Transact-SQL statement or stored procedure to execute against 
// a Sqlite database file.
//
// Author(s):   Vladimir Vukicevic  <vladimir@pobox.com>
//              Everaldo Canuto  <everaldo_canuto@yahoo.com.br>
//              Chris Turchin <chris@turchin.net>
//              Jeroen Zwartepoorte <jeroen@xs4all.nl>
//              Thomas Zoechling <thomas.zoechling@gmx.at>
//              Joshua Tauberer <tauberer@for.net>
//
// Copyright (C) 2002  Vladimir Vukicevic
//
// Permission is hereby granted, free of charge, to any person obtaining
// a copy of this software and associated documentation files (the
// "Software"), to deal in the Software without restriction, including
// without limitation the rights to use, copy, modify, merge, publish,
// distribute, sublicense, and/or sell copies of the Software, and to
// permit persons to whom the Software is furnished to do so, subject to
// the following conditions:
// 
// The above copyright notice and this permission notice shall be
// included in all copies or substantial portions of the Software.
// 
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
//

using System;
using System.Collections;
using System.Text;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Data;
using System.Diagnostics; 
using Group = System.Text.RegularExpressions.Group;

namespace Mono.Data.SqliteClient 
{
        public class SqliteCommand : IDbCommand
        {
                #region Fields
                
                private SqliteConnection parent_conn;
                //private SqliteTransaction transaction;
                private IDbTransaction transaction;
                private string sql;
                private int timeout;
                private CommandType type;
                private UpdateRowSource upd_row_source;
                private SqliteParameterCollection sql_params;
                private bool prepared = false;

                #endregion

                #region Constructors and destructors
                
                public SqliteCommand ()
                {
                        sql = "";
                }
                                
                public SqliteCommand (string sqlText)
                {
                        sql = sqlText;
                }
                
                public SqliteCommand (string sqlText, SqliteConnection dbConn)
                {
                        sql = sqlText;
                        parent_conn = dbConn;
                }
                
                public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
                {
                        sql = sqlText;
                        parent_conn = dbConn;
                        transaction = trans;
                }
                
                public void Dispose ()
                {
                }
                
                #endregion

                #region Properties
                
                public string CommandText 
                {
                        get { return sql; }
                        set { sql = value; prepared = false; }
                }
                
                public int CommandTimeout
                {
                        get { return timeout; }
                        set { timeout = value; }
                }
                
                public CommandType CommandType 
                {
                        get { return type; }
                        set { type = value; }
                }
                
                IDbConnection IDbCommand.Connection 
                {
                        get 
                        { 
                                return parent_conn; 
                        }
                        set 
                        {
                                if (!(value is SqliteConnection)) 
                                {
                                        throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
                                }
                                parent_conn = (SqliteConnection) value;
                        }
                }
                
                public SqliteConnection Connection
                {
                        get { return parent_conn; }
                        set { parent_conn = value; }
                }
                
                IDataParameterCollection IDbCommand.Parameters 
                {
                        get { return Parameters; }
                }
                
                public SqliteParameterCollection Parameters 
                {
                        get
                        {
                                if (sql_params == null) sql_params = new SqliteParameterCollection();
                                return sql_params;
                        }
                }
                
                public IDbTransaction Transaction 
                {
                        get { return transaction; }
                        set { transaction = value; }
                }
                
                public UpdateRowSource UpdatedRowSource 
                {
                        get { return upd_row_source; }
                        set { upd_row_source = value; }
                }
                                
                #endregion

                #region Internal Methods
                
                internal int NumChanges () 
                {
                        if (parent_conn.Version == 3)
                                return Sqlite.sqlite3_changes(parent_conn.Handle);
                        else
                                return Sqlite.sqlite_changes(parent_conn.Handle);
                }
                
                private void BindParameters3 (IntPtr pStmt)
                {
                        if (sql_params == null) return;
                        if (sql_params.Count == 0) return;
                        
                        int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);

                        for (int i = 1; i <= pcount; i++) 
                        {
                                String name = Sqlite.HeapToString (Sqlite.sqlite3_bind_parameter_name (pStmt, i), Encoding.UTF8);
                                if (name == null) continue;
                                
                                SqliteParameter param = sql_params[name];
                                
                                if (param.Value == null) {
                                        Sqlite.sqlite3_bind_null (pStmt, i);
                                        continue;
                                }
                                        
                                Type ptype = param.Value.GetType ();
                                
                                SqliteError err;
                                
                                if (ptype.Equals (typeof (String))) 
                                {
                                        String s = (String)param.Value;
                                        err = Sqlite.sqlite3_bind_text16 (pStmt, i, s, -1, (IntPtr)(-1));
                                } 
                                else if (ptype.Equals (typeof (DBNull))) 
                                {
                                        err = Sqlite.sqlite3_bind_null (pStmt, i);
                                }
                                else if (ptype.Equals (typeof (Boolean))) 
                                {
                                        bool b = (bool)param.Value;
                                        err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
                                } else if (ptype.Equals (typeof (Byte))) 
                                {
                                        err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
                                }
                                else if (ptype.Equals (typeof (Char))) 
                                {
                                        err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
                                } 
                                else if (ptype.Equals (typeof (Int16))) 
                                {
                                        err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
                                } 
                                else if (ptype.Equals (typeof (Int32))) 
                                {
                                        err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
                                }
                                else if (ptype.Equals (typeof (SByte))) 
                                {
                                        err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
                                } 
                                else if (ptype.Equals (typeof (UInt16))) 
                                {
                                        err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
                                }
                                else if (ptype.Equals (typeof (DateTime))) 
                                {
                                        DateTime dt = (DateTime)param.Value;
                                        err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
                                } 
                                else if (ptype.Equals (typeof (Double))) 
                                {
                                        err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
                                }
                                else if (ptype.Equals (typeof (Single))) 
                                {
                                        err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
                                } 
                                else if (ptype.Equals (typeof (UInt32))) 
                                {
                                        err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
                                }
                                else if (ptype.Equals (typeof (Int64))) 
                                {
                                        err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
                                } 
                                else if (ptype.Equals (typeof (Byte[]))) 
                                {
                                        err = Sqlite.sqlite3_bind_blob (pStmt, i, (Byte[])param.Value, ((Byte[])param.Value).Length, (IntPtr)(-1));
                                } 
                                else 
                                {
                                        throw new ApplicationException("Unkown Parameter Type");
                                }
                                if (err != SqliteError.OK) 
                                {
                                        throw new ApplicationException ("Sqlite error in bind " + err);
                                }
                        }
                }

                private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
                {
                        if (parent_conn.Version == 3)
                        {
                                SqliteError err = Sqlite.sqlite3_prepare16 (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
                                if (err != SqliteError.OK)
                                        throw new SqliteSyntaxException (GetError3());
                        }
                        else
                        {
                                IntPtr errMsg;
                                SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
                                
                                if (err != SqliteError.OK) 
                                {
                                        string msg = "unknown error";
                                        if (errMsg != IntPtr.Zero) 
                                        {
                                                msg = Marshal.PtrToStringAnsi (errMsg);
                                                Sqlite.sqliteFree (errMsg);
                                        }
                                        throw new SqliteSyntaxException (msg);
                                }
                        }
                }
                
                // Executes a statement and ignores its result.
                private void ExecuteStatement (IntPtr pStmt) {
                        int cols;
                        IntPtr pazValue, pazColName;
                        ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
                }

                // Executes a statement and returns whether there is more data available.
                internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
                        SqliteError err;
                        
                        if (parent_conn.Version == 3) 
                        {
                                err = Sqlite.sqlite3_step (pStmt);
                                if (err == SqliteError.ERROR)
                                        throw new SqliteExecutionException (GetError3());
                                pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
                                cols = Sqlite.sqlite3_column_count (pStmt);
                        }
                        else 
                        {
                                err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
                                if (err == SqliteError.ERROR)
                                        throw new SqliteExecutionException ();
                        }
                        
                        if (err == SqliteError.BUSY)
                                throw new SqliteBusyException();
                        
                        if (err == SqliteError.MISUSE)
                                throw new SqliteExecutionException();
                                
                        // err is either ROW or DONE.
                        return err == SqliteError.ROW;
                }
                
                #endregion

                #region Public Methods
                
                public void Cancel ()
                {
                }
                
                public string BindParameters2()
                {
                        string text = sql;
                        
                        // There used to be a crazy regular expression here, but it caused Mono
                        // to go into an infinite loop of some sort when there were no parameters
                        // in the SQL string.  That was too complicated anyway.
                        
                        // Here we search for substrings of the form :wwwww where w is a letter or digit
                        // (not sure what a legitimate Sqlite3 identifier is), except those within quotes.
                        
                        char inquote = (char)0;
                        for (int i = 0; i < text.Length; i++) {
                                char c = text[i];
                                if (c == inquote) {
                                        inquote = (char)0;
                                } else if (inquote == (char)0 && (c == '\'' || c == '"')) {
                                        inquote = c;
                                } else if (inquote == (char)0 && c == ':') {
                                        int start = i;
                                        while (++i < text.Length && char.IsLetterOrDigit(text[i])) { } // scan to end
                                        string name = text.Substring(start, i-start);
                                        string value = "'" + Convert.ToString(Parameters[name].Value).Replace("'", "''") + "'";
                                        text = text.Replace(name, value);
                                        i += value.Length - name.Length - 1;
                                }
                        }
                        
                        return text;
                }
                
                public void Prepare ()
                {
                        // There isn't much we can do here.  If a table schema
                        // changes after preparing a statement, Sqlite bails,
                        // so we can only compile statements right before we
                        // want to run them.
                        
                        if (prepared) return;           
                
                        if (Parameters.Count > 0 && parent_conn.Version == 2)
                        {
                                sql = BindParameters2();
                        }
                        
                        prepared = true;
                }
                
                IDbDataParameter IDbCommand.CreateParameter()
                {
                        return CreateParameter ();
                }
                
                public SqliteParameter CreateParameter ()
                {
                        return new SqliteParameter ();
                }
                
                public int ExecuteNonQuery ()
                {
                        int rows_affected;
                        ExecuteReader (CommandBehavior.Default, false, out rows_affected);
                        return rows_affected;
                }
                
                public object ExecuteScalar ()
                {
                        SqliteDataReader r = ExecuteReader ();
                        if (r == null || !r.Read ()) {
                                return null;
                        }
                        object o = r[0];
                        r.Close ();
                        return o;
                }
                
                IDataReader IDbCommand.ExecuteReader ()
                {
                        return ExecuteReader ();
                }
                
                IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
                {
                        return ExecuteReader (behavior);
                }
                
                public SqliteDataReader ExecuteReader ()
                {
                        return ExecuteReader (CommandBehavior.Default);
                }
                
                public SqliteDataReader ExecuteReader (CommandBehavior behavior)
                {
                        int r;
                        return ExecuteReader (behavior, true, out r);
                }
                
                public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
                {
                        Prepare ();
                        
                        // The SQL string may contain multiple sql commands, so the main
                        // thing to do is have Sqlite iterate through the commands.
                        // If want_results, only the last command is returned as a
                        // DataReader.  Otherwise, no command is returned as a
                        // DataReader.
                
                        IntPtr psql; // pointer to SQL command
                        
                        // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit 
                        // character (iso8859).  But if you give the --enable-utf8 option to the configure script, then the 
                        // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB 
                        // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set 
                        // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h 
                        // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
                        // 
                        // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
                        // default.  Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
                        // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
                        // of what Sqlite is treating them as, 
                        
                        // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
                        
                        if (parent_conn.Version == 2)
                                psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
                        else
                                psql = Marshal.StringToCoTaskMemUni (sql.Trim());

                        IntPtr pzTail = psql;
                        IntPtr errMsgPtr;
                        
                        parent_conn.StartExec ();
                        
                        rows_affected = 0;
                        
                        try {
                                while (true) {
                                        IntPtr pStmt;
                                         
                                        GetNextStatement(pzTail, out pzTail, out pStmt);
                                        
                                        if (pStmt == IntPtr.Zero)
                                                throw new Exception();
                                        
                                        // pzTail is positioned after the last byte in the
                                        // statement, which will be the NULL character if
                                        // this was the last statement.
                                        bool last = Marshal.ReadByte(pzTail) == 0;
                                        Exception caught_ex = null;

                                        try {
                                                if (parent_conn.Version == 3)
                                                        BindParameters3 (pStmt);
                                                
                                                if (last && want_results)
                                                        return new SqliteDataReader (this, pStmt, parent_conn.Version);

                                                ExecuteStatement(pStmt);
                                                
                                                if (last) // rows_affected is only used if !want_results
                                                        rows_affected = NumChanges ();
                                        } catch (Exception ex) {
                                                caught_ex = ex;
                                        } finally {
                                                if (! want_results) {
                                                        if (parent_conn.Version == 3) 
                                                                Sqlite.sqlite3_finalize (pStmt);
                                                        else {
                                                                Sqlite.sqlite_finalize (pStmt, out errMsgPtr);

                                                                if (errMsgPtr != IntPtr.Zero) {
                                                                        string errMsg = Marshal.PtrToStringAnsi (errMsgPtr);

                                                                        if (caught_ex != null)
                                                                                throw new SqliteExecutionException (errMsg, caught_ex);
                                                                        else
                                                                                throw new SqliteExecutionException (errMsg);
                                                                }
                                                        }
                                                }
                                        }
                                        
                                        if (last) break;
                                }

                                return null;
                        } finally {
                                parent_conn.EndExec ();
                                Marshal.FreeCoTaskMem (psql);
                        }
                }

                public int LastInsertRowID () 
                {
                        if (parent_conn.Version == 3)
                                return Sqlite.sqlite3_last_insert_rowid(parent_conn.Handle);
                        else
                                return Sqlite.sqlite_last_insert_rowid(parent_conn.Handle);
                }
                
                private string GetError3() {
                        return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));
                }
        #endregion
        }
}