mcs/class/Mono.Data.SqliteClient/Mono.Data.SqliteClient/SqliteCommand.cs

   1 //
   2 // Mono.Data.SqliteClient.SqliteCommand.cs
   3 //
   4 // Represents a Transact-SQL statement or stored procedure to execute against
   5 // a Sqlite database file.
   6 //
   7 // Author(s):   Vladimir Vukicevic  <vladimir@pobox.com>
   8 //              Everaldo Canuto  <everaldo_canuto@yahoo.com.br>
   9 //              Chris Turchin <chris@turchin.net>
  10 //              Jeroen Zwartepoorte <jeroen@xs4all.nl>
  11 //              Thomas Zoechling <thomas.zoechling@gmx.at>
  12 //              Joshua Tauberer <tauberer@for.net>
  13 //
  14 // Copyright (C) 2002  Vladimir Vukicevic
  15 //
  16 // Permission is hereby granted, free of charge, to any person obtaining
  17 // a copy of this software and associated documentation files (the
  18 // "Software"), to deal in the Software without restriction, including
  19 // without limitation the rights to use, copy, modify, merge, publish,
  20 // distribute, sublicense, and/or sell copies of the Software, and to
  21 // permit persons to whom the Software is furnished to do so, subject to
  22 // the following conditions:
  23 //
  24 // The above copyright notice and this permission notice shall be
  25 // included in all copies or substantial portions of the Software.
  26 //
  27 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  28 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  29 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  30 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  31 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  32 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  33 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  34 //
  35
  36 using System;
  37 using System.Collections;
  38 using System.Text;
  39 using System.Runtime.InteropServices;
  40 using System.Text.RegularExpressions;
  41 using System.Data;
  42 using System.Diagnostics;
  43 using Group = System.Text.RegularExpressions.Group;
  44
  45 namespace Mono.Data.SqliteClient
  46 {
  47         public class SqliteCommand : IDbCommand
  48         {
  49                 #region Fields
  50
  51                 private SqliteConnection parent_conn;
  52                 //private SqliteTransaction transaction;
  53                 private IDbTransaction transaction;
  54                 private string sql;
  55                 private int timeout;
  56                 private CommandType type;
  57                 private UpdateRowSource upd_row_source;
  58                 private SqliteParameterCollection sql_params;
  59                 private bool prepared = false;
  60
  61                 static Regex v2Parameters = new Regex(@"(('[^']*?\:[^']*')*[^':]*?)*(?<param>:\w+)+([^':]*?('[^']*?\:[^']*'))*", RegexOptions.ExplicitCapture);
  62
  63                 #endregion
  64
  65                 #region Constructors and destructors
  66
  67                 public SqliteCommand ()
  68                 {
  69                         sql = "";
  70                 }
  71
  72                 public SqliteCommand (string sqlText)
  73                 {
  74                         sql = sqlText;
  75                 }
  76
  77                 public SqliteCommand (string sqlText, SqliteConnection dbConn)
  78                 {
  79                         sql = sqlText;
  80                         parent_conn = dbConn;
  81                 }
  82
  83                 public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
  84                 {
  85                         sql = sqlText;
  86                         parent_conn = dbConn;
  87                         transaction = trans;
  88                 }
  89
  90                 public void Dispose ()
  91                 {
  92                 }
  93
  94                 #endregion
  95
  96                 #region Properties
  97
  98                 public string CommandText
  99                 {
 100                         get { return sql; }
 101                         set { sql = value; }
 102                 }
 103
 104                 public int CommandTimeout
 105                 {
 106                         get { return timeout; }
 107                         set { timeout = value; }
 108                 }
 109
 110                 public CommandType CommandType
 111                 {
 112                         get { return type; }
 113                         set { type = value; }
 114                 }
 115
 116                 IDbConnection IDbCommand.Connection
 117                 {
 118                         get
 119                         {
 120                                 return parent_conn;
 121                         }
 122                         set
 123                         {
 124                                 if (!(value is SqliteConnection))
 125                                 {
 126                                         throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
 127                                 }
 128                                 parent_conn = (SqliteConnection) value;
 129                         }
 130                 }
 131
 132                 public SqliteConnection Connection
 133                 {
 134                         get { return parent_conn; }
 135                         set { parent_conn = value; }
 136                 }
 137
 138                 IDataParameterCollection IDbCommand.Parameters
 139                 {
 140                         get { return Parameters; }
 141                 }
 142
 143                 public SqliteParameterCollection Parameters
 144                 {
 145                         get
 146                         {
 147                                 if (sql_params == null) sql_params = new SqliteParameterCollection();
 148                                 return sql_params;
 149                         }
 150                 }
 151
 152                 public IDbTransaction Transaction
 153                 {
 154                         get { return transaction; }
 155                         set { transaction = value; }
 156                 }
 157
 158                 public UpdateRowSource UpdatedRowSource
 159                 {
 160                         get { return upd_row_source; }
 161                         set { upd_row_source = value; }
 162                 }
 163
 164                 #endregion
 165
 166                 #region Internal Methods
 167
 168                 internal int NumChanges ()
 169                 {
 170                         if (parent_conn.Version == 3)
 171                                 return Sqlite.sqlite3_changes(parent_conn.Handle);
 172                         else
 173                                 return Sqlite.sqlite_changes(parent_conn.Handle);
 174                 }
 175
 176                 private string ReplaceParams(Match m)
 177                 {
 178                         string input = m.Value;
 179                         if (m.Groups["param"].Success)
 180                         {
 181                                 Group g = m.Groups["param"];
 182                                 string find = g.Value;
 183                                 //FIXME: sqlite works internally only with strings, so this assumtion is mostly legit, but what about date formatting, etc?
 184                                 //Need to fix SqlLiteDataReader first to acurately describe the tables
 185                                 SqliteParameter sqlp = Parameters[find];
 186                                 string replace = Convert.ToString(sqlp.Value);
 187                                 if(sqlp.DbType == DbType.String)
 188                                 {
 189                                         replace =  "\"" + replace + "\"";
 190                                 }
 191
 192                                 input = Regex.Replace(input,find,replace);
 193                                 return input;
 194                         }
 195                         else
 196                         return m.Value;
 197                 }
 198
 199                 private void BindParameters3 (IntPtr pStmt)
 200                 {
 201                         if (sql_params == null) return;
 202                         if (sql_params.Count == 0) return;
 203
 204                         int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);
 205
 206                         for (int i = 1; i <= pcount; i++)
 207                         {
 208                                 String name = Sqlite.sqlite3_bind_parameter_name (pStmt, i);
 209                                 SqliteParameter param = sql_params[name];
 210                                 Type ptype = param.Value.GetType ();
 211
 212                                 SqliteError err;
 213
 214                                 if (ptype.Equals (typeof (String)))
 215                                 {
 216                                         String s = (String)param.Value;
 217                                         err = Sqlite.sqlite3_bind_text16 (pStmt, i, s, s.Length, (IntPtr)(-1));
 218                                 }
 219                                 else if (ptype.Equals (typeof (DBNull)))
 220                                 {
 221                                         err = Sqlite.sqlite3_bind_null (pStmt, i);
 222                                 }
 223                                 else if (ptype.Equals (typeof (Boolean)))
 224                                 {
 225                                         bool b = (bool)param.Value;
 226                                         err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
 227                                 } else if (ptype.Equals (typeof (Byte)))
 228                                 {
 229                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
 230                                 }
 231                                 else if (ptype.Equals (typeof (Char)))
 232                                 {
 233                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
 234                                 }
 235                                 else if (ptype.Equals (typeof (Int16)))
 236                                 {
 237                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
 238                                 }
 239                                 else if (ptype.Equals (typeof (Int32)))
 240                                 {
 241                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 242                                 }
 243                                 else if (ptype.Equals (typeof (SByte)))
 244                                 {
 245                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
 246                                 }
 247                                 else if (ptype.Equals (typeof (UInt16)))
 248                                 {
 249                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
 250                                 }
 251                                 else if (ptype.Equals (typeof (DateTime)))
 252                                 {
 253                                         DateTime dt = (DateTime)param.Value;
 254                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
 255                                 }
 256                                 else if (ptype.Equals (typeof (Double)))
 257                                 {
 258                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
 259                                 }
 260                                 else if (ptype.Equals (typeof (Single)))
 261                                 {
 262                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
 263                                 }
 264                                 else if (ptype.Equals (typeof (UInt32)))
 265                                 {
 266                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
 267                                 }
 268                                 else if (ptype.Equals (typeof (Int64)))
 269                                 {
 270                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
 271                                 }
 272                                 else
 273                                 {
 274                                         throw new ApplicationException("Unkown Parameter Type");
 275                                 }
 276                                 if (err != SqliteError.OK)
 277                                 {
 278                                         throw new ApplicationException ("Sqlite error in bind " + err);
 279                                 }
 280                         }
 281                 }
 282
 283                 private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
 284                 {
 285                         if (parent_conn.Version == 3)
 286                         {
 287                                 SqliteError err = Sqlite.sqlite3_prepare16 (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
 288                                 if (err != SqliteError.OK)
 289                                         throw new SqliteSyntaxException (GetError3());
 290                         }
 291                         else
 292                         {
 293                                 IntPtr errMsg;
 294                                 SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
 295
 296                                 if (err != SqliteError.OK)
 297                                 {
 298                                         string msg = "unknown error";
 299                                         if (errMsg != IntPtr.Zero)
 300                                         {
 301                                                 msg = Marshal.PtrToStringAnsi (errMsg);
 302                                                 Sqlite.sqliteFree (errMsg);
 303                                         }
 304                                         throw new SqliteSyntaxException (msg);
 305                                 }
 306                         }
 307                 }
 308
 309                 // Executes a statement and ignores its result.
 310                 private void ExecuteStatement (IntPtr pStmt) {
 311                         int cols;
 312                         IntPtr pazValue, pazColName;
 313                         ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
 314                 }
 315
 316                 // Executes a statement and returns whether there is more data available.
 317                 internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
 318                         SqliteError err;
 319
 320                         if (parent_conn.Version == 3)
 321                         {
 322                                 err = Sqlite.sqlite3_step (pStmt);
 323                                 if (err == SqliteError.ERROR)
 324                                         throw new SqliteExecutionException (GetError3());
 325                                 pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
 326                                 cols = Sqlite.sqlite3_column_count (pStmt);
 327                         }
 328                         else
 329                         {
 330                                 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
 331                                 if (err == SqliteError.ERROR)
 332                                         throw new SqliteExecutionException ();
 333                         }
 334
 335                         if (err == SqliteError.BUSY)
 336                                 throw new SqliteBusyException();
 337
 338                         if (err == SqliteError.MISUSE)
 339                                 throw new SqliteExecutionException();
 340
 341                         // err is either ROW or DONE.
 342                         return err == SqliteError.ROW;
 343                 }
 344
 345                 #endregion
 346
 347                 #region Public Methods
 348
 349                 public void Cancel ()
 350                 {
 351                 }
 352
 353                 public string ProcessParameters()
 354                 {
 355                         string processedText = sql;
 356
 357                         //Regex looks odd perhaps, but it works - same impl. as in the firebird db provider
 358                         //the named parameters are using the ADO.NET standard @-prefix but sqlite is considering ":" as a prefix for v.3...
 359                         //ref: http://www.mail-archive.com/sqlite-users@sqlite.org/msg01851.html
 360                         //Regex r = new Regex(@"(('[^']*?\@[^']*')*[^'@]*?)*(?<param>@\w+)+([^'@]*?('[^']*?\@[^']*'))*",RegexOptions.ExplicitCapture);
 361
 362                         //The above statement is true for the commented regEx, but I changed it to use the :-prefix, because now (12.05.2005 sqlite3)
 363                         //sqlite is using : as Standard Parameterprefix
 364
 365                         MatchEvaluator me = new MatchEvaluator(ReplaceParams);
 366                         processedText = v2Parameters.Replace(sql, me);
 367                         return processedText;
 368                 }
 369
 370                 public void Prepare ()
 371                 {
 372                         // There isn't much we can do here.  If a table schema
 373                         // changes after preparing a statement, Sqlite bails,
 374                         // so we can only compile statements right before we
 375                         // want to run them.
 376
 377                         if (prepared) return;
 378
 379                         if (Parameters.Count > 0 && parent_conn.Version == 2)
 380                         {
 381                                 sql = ProcessParameters();
 382                         }
 383
 384                         prepared = true;
 385                 }
 386
 387                 IDbDataParameter IDbCommand.CreateParameter()
 388                 {
 389                         return CreateParameter ();
 390                 }
 391
 392                 public SqliteParameter CreateParameter ()
 393                 {
 394                         return new SqliteParameter ();
 395                 }
 396
 397                 public int ExecuteNonQuery ()
 398                 {
 399                         int rows_affected;
 400                         ExecuteReader (CommandBehavior.Default, false, out rows_affected);
 401                         return rows_affected;
 402                 }
 403
 404                 public object ExecuteScalar ()
 405                 {
 406                         SqliteDataReader r = ExecuteReader ();
 407                         if (r == null || !r.Read ()) {
 408                                 return null;
 409                         }
 410                         object o = r[0];
 411                         r.Close ();
 412                         return o;
 413                 }
 414
 415                 IDataReader IDbCommand.ExecuteReader ()
 416                 {
 417                         return ExecuteReader ();
 418                 }
 419
 420                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 421                 {
 422                         return ExecuteReader (behavior);
 423                 }
 424
 425                 public SqliteDataReader ExecuteReader ()
 426                 {
 427                         return ExecuteReader (CommandBehavior.Default);
 428                 }
 429
 430                 public SqliteDataReader ExecuteReader (CommandBehavior behavior)
 431                 {
 432                         int r;
 433                         return ExecuteReader (behavior, true, out r);
 434                 }
 435
 436                 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
 437                 {
 438                         // The SQL string may contain multiple sql commands, so the main
 439                         // thing to do is have Sqlite iterate through the commands.
 440                         // If want_results, only the last command is returned as a
 441                         // DataReader.  Otherwise, no command is returned as a
 442                         // DataReader.
 443
 444                         IntPtr psql; // pointer to SQL command
 445
 446                         // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit
 447                         // character (iso8859).  But if you give the --enable-utf8 option to the configure script, then the
 448                         // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB
 449                         // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set
 450                         // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h
 451                         // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
 452                         //
 453                         // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
 454                         // default.  Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
 455                         // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
 456                         // of what Sqlite is treating them as,
 457
 458                         // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
 459
 460                         if (parent_conn.Version == 2)
 461                                 psql = Sqlite.StringToHeap (sql, parent_conn.Encoding);
 462                         else
 463                                 psql = Marshal.StringToCoTaskMemUni (sql);
 464
 465                         IntPtr pzTail = psql;
 466                         IntPtr errMsgPtr;
 467
 468                         parent_conn.StartExec ();
 469
 470                         rows_affected = 0;
 471
 472                         try {
 473                                 while (true) {
 474                                         IntPtr pStmt;
 475
 476                                         GetNextStatement(pzTail, out pzTail, out pStmt);
 477
 478                                         if (pStmt == IntPtr.Zero)
 479                                                 throw new Exception();
 480
 481                                         // pzTail is positioned after the last byte in the
 482                                         // statement, which will be the NULL character if
 483                                         // this was the last statement.
 484                                         bool last = Marshal.ReadByte(pzTail) == 0;
 485
 486                                         try {
 487                                                 if (parent_conn.Version == 3)
 488                                                         BindParameters3 (pStmt);
 489
 490                                                 if (last && want_results)
 491                                                         return new SqliteDataReader (this, pStmt, parent_conn.Version);
 492
 493                                                 ExecuteStatement(pStmt);
 494
 495                                                 if (last) // rows_affected is only used if !want_results
 496                                                         rows_affected = NumChanges ();
 497
 498                                         } finally {
 499                                                 if (parent_conn.Version == 3)
 500                                                         Sqlite.sqlite3_finalize (pStmt);
 501                                                 else
 502                                                         Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
 503                                         }
 504
 505                                         if (last) break;
 506                                 }
 507
 508                                 return null;
 509                         } finally {
 510                                 parent_conn.EndExec ();
 511                                 Marshal.FreeCoTaskMem (psql);
 512                         }
 513                 }
 514
 515                 public int LastInsertRowID ()
 516                 {
 517                         if (parent_conn.Version == 3)
 518                                 return Sqlite.sqlite3_last_insert_rowid(parent_conn.Handle);
 519                         else
 520                                 return Sqlite.sqlite_last_insert_rowid(parent_conn.Handle);
 521                 }
 522
 523                 private string GetError3() {
 524                         return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));
 525                 }
 526         #endregion
 527         }
 528 }