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 Mono.Unix;
  40 using System.Runtime.InteropServices;
  41 using System.Text.RegularExpressions;
  42 using System.Data;
  43 using System.Diagnostics;
  44 using Group = System.Text.RegularExpressions.Group;
  45
  46 namespace Mono.Data.SqliteClient
  47 {
  48         public class SqliteCommand : IDbCommand
  49         {
  50                 #region Fields
  51
  52                 private SqliteConnection parent_conn;
  53                 //private SqliteTransaction transaction;
  54                 private IDbTransaction transaction;
  55                 private string sql;
  56                 private int timeout;
  57                 private CommandType type;
  58                 private UpdateRowSource upd_row_source;
  59                 private SqliteParameterCollection sql_params;
  60                 private bool prepared = false;
  61
  62                 static Regex v2Parameters = new Regex(@"(('[^']*?\:[^']*')*[^':]*?)*(?<param>:\w+)+([^':]*?('[^']*?\:[^']*'))*", RegexOptions.ExplicitCapture);
  63
  64                 #endregion
  65
  66                 #region Constructors and destructors
  67
  68                 public SqliteCommand ()
  69                 {
  70                         sql = "";
  71                 }
  72
  73                 public SqliteCommand (string sqlText)
  74                 {
  75                         sql = sqlText;
  76                 }
  77
  78                 public SqliteCommand (string sqlText, SqliteConnection dbConn)
  79                 {
  80                         sql = sqlText;
  81                         parent_conn = dbConn;
  82                 }
  83
  84                 public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
  85                 {
  86                         sql = sqlText;
  87                         parent_conn = dbConn;
  88                         transaction = trans;
  89                 }
  90
  91                 public void Dispose ()
  92                 {
  93                 }
  94
  95                 #endregion
  96
  97                 #region Properties
  98
  99                 public string CommandText
 100                 {
 101                         get { return sql; }
 102                         set { sql = value; }
 103                 }
 104
 105                 public int CommandTimeout
 106                 {
 107                         get { return timeout; }
 108                         set { timeout = value; }
 109                 }
 110
 111                 public CommandType CommandType
 112                 {
 113                         get { return type; }
 114                         set { type = value; }
 115                 }
 116
 117                 IDbConnection IDbCommand.Connection
 118                 {
 119                         get
 120                         {
 121                                 return parent_conn;
 122                         }
 123                         set
 124                         {
 125                                 if (!(value is SqliteConnection))
 126                                 {
 127                                         throw new InvalidOperationException ("Can't set Connection to something other than a SqliteConnection");
 128                                 }
 129                                 parent_conn = (SqliteConnection) value;
 130                         }
 131                 }
 132
 133                 public SqliteConnection Connection
 134                 {
 135                         get { return parent_conn; }
 136                         set { parent_conn = value; }
 137                 }
 138
 139                 IDataParameterCollection IDbCommand.Parameters
 140                 {
 141                         get { return Parameters; }
 142                 }
 143
 144                 public SqliteParameterCollection Parameters
 145                 {
 146                         get
 147                         {
 148                                 if (sql_params == null) sql_params = new SqliteParameterCollection();
 149                                 return sql_params;
 150                         }
 151                 }
 152
 153                 public IDbTransaction Transaction
 154                 {
 155                         get { return transaction; }
 156                         set { transaction = value; }
 157                 }
 158
 159                 public UpdateRowSource UpdatedRowSource
 160                 {
 161                         get { return upd_row_source; }
 162                         set { upd_row_source = value; }
 163                 }
 164
 165                 #endregion
 166
 167                 #region Internal Methods
 168
 169                 internal int NumChanges ()
 170                 {
 171                         if (parent_conn.Version == 3)
 172                                 return Sqlite.sqlite3_changes(parent_conn.Handle);
 173                         else
 174                                 return Sqlite.sqlite_changes(parent_conn.Handle);
 175                 }
 176
 177                 private string ReplaceParams(Match m)
 178                 {
 179                         string input = m.Value;
 180                         if (m.Groups["param"].Success)
 181                         {
 182                                 Group g = m.Groups["param"];
 183                                 string find = g.Value;
 184                                 //FIXME: sqlite works internally only with strings, so this assumtion is mostly legit, but what about date formatting, etc?
 185                                 //Need to fix SqlLiteDataReader first to acurately describe the tables
 186                                 SqliteParameter sqlp = Parameters[find];
 187                                 string replace = Convert.ToString(sqlp.Value);
 188                                 if(sqlp.DbType == DbType.String)
 189                                 {
 190                                         replace =  "\"" + replace + "\"";
 191                                 }
 192
 193                                 input = Regex.Replace(input,find,replace);
 194                                 return input;
 195                         }
 196                         else
 197                         return m.Value;
 198                 }
 199
 200                 private void BindParameters3 (IntPtr pStmt)
 201                 {
 202                         if (sql_params == null) return;
 203                         if (sql_params.Count == 0) return;
 204
 205                         int pcount = Sqlite.sqlite3_bind_parameter_count (pStmt);
 206
 207                         for (int i = 1; i <= pcount; i++)
 208                         {
 209                                 String name = Sqlite.sqlite3_bind_parameter_name (pStmt, i);
 210                                 SqliteParameter param = sql_params[name];
 211                                 Type ptype = param.Value.GetType ();
 212
 213                                 SqliteError err;
 214
 215                                 if (ptype.Equals (typeof (String)))
 216                                 {
 217                                         String s = (String)param.Value;
 218                                         err = Sqlite.sqlite3_bind_text (pStmt, i, s, s.Length, (IntPtr)(-1));
 219                                 }
 220                                 else if (ptype.Equals (typeof (DBNull)))
 221                                 {
 222                                         err = Sqlite.sqlite3_bind_null (pStmt, i);
 223                                 }
 224                                 else if (ptype.Equals (typeof (Boolean)))
 225                                 {
 226                                         bool b = (bool)param.Value;
 227                                         err = Sqlite.sqlite3_bind_int (pStmt, i, b ? 1 : 0);
 228                                 } else if (ptype.Equals (typeof (Byte)))
 229                                 {
 230                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Byte)param.Value);
 231                                 }
 232                                 else if (ptype.Equals (typeof (Char)))
 233                                 {
 234                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Char)param.Value);
 235                                 }
 236                                 else if (ptype.Equals (typeof (Int16)))
 237                                 {
 238                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int16)param.Value);
 239                                 }
 240                                 else if (ptype.Equals (typeof (Int32)))
 241                                 {
 242                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (Int32)param.Value);
 243                                 }
 244                                 else if (ptype.Equals (typeof (SByte)))
 245                                 {
 246                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (SByte)param.Value);
 247                                 }
 248                                 else if (ptype.Equals (typeof (UInt16)))
 249                                 {
 250                                         err = Sqlite.sqlite3_bind_int (pStmt, i, (UInt16)param.Value);
 251                                 }
 252                                 else if (ptype.Equals (typeof (DateTime)))
 253                                 {
 254                                         DateTime dt = (DateTime)param.Value;
 255                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, dt.ToFileTime ());
 256                                 }
 257                                 else if (ptype.Equals (typeof (Double)))
 258                                 {
 259                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Double)param.Value);
 260                                 }
 261                                 else if (ptype.Equals (typeof (Single)))
 262                                 {
 263                                         err = Sqlite.sqlite3_bind_double (pStmt, i, (Single)param.Value);
 264                                 }
 265                                 else if (ptype.Equals (typeof (UInt32)))
 266                                 {
 267                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (UInt32)param.Value);
 268                                 }
 269                                 else if (ptype.Equals (typeof (Int64)))
 270                                 {
 271                                         err = Sqlite.sqlite3_bind_int64 (pStmt, i, (Int64)param.Value);
 272                                 }
 273                                 else
 274                                 {
 275                                         throw new ApplicationException("Unkown Parameter Type");
 276                                 }
 277                                 if (err != SqliteError.OK)
 278                                 {
 279                                         throw new ApplicationException ("Sqlite error in bind " + err);
 280                                 }
 281                         }
 282                 }
 283
 284                 private void GetNextStatement (IntPtr pzStart, out IntPtr pzTail, out IntPtr pStmt)
 285                 {
 286                         if (parent_conn.Version == 3)
 287                         {
 288                                 SqliteError err = Sqlite.sqlite3_prepare (parent_conn.Handle, pzStart, -1, out pStmt, out pzTail);
 289                                 if (err != SqliteError.OK)
 290                                         throw new SqliteSyntaxException (GetError3());
 291                         }
 292                         else
 293                         {
 294                                 IntPtr errMsg;
 295                                 SqliteError err = Sqlite.sqlite_compile (parent_conn.Handle, pzStart, out pzTail, out pStmt, out errMsg);
 296
 297                                 if (err != SqliteError.OK)
 298                                 {
 299                                         string msg = "unknown error";
 300                                         if (errMsg != IntPtr.Zero)
 301                                         {
 302                                                 msg = Marshal.PtrToStringAnsi (errMsg);
 303                                                 Sqlite.sqliteFree (errMsg);
 304                                         }
 305                                         throw new SqliteSyntaxException (msg);
 306                                 }
 307                         }
 308                 }
 309
 310                 // Executes a statement and ignores its result.
 311                 private void ExecuteStatement (IntPtr pStmt) {
 312                         int cols;
 313                         IntPtr pazValue, pazColName;
 314                         ExecuteStatement (pStmt, out cols, out pazValue, out pazColName);
 315                 }
 316
 317                 // Executes a statement and returns whether there is more data available.
 318                 internal bool ExecuteStatement (IntPtr pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName) {
 319                         SqliteError err;
 320
 321                         if (parent_conn.Version == 3)
 322                         {
 323                                 err = Sqlite.sqlite3_step (pStmt);
 324                                 if (err == SqliteError.ERROR)
 325                                         throw new SqliteExecutionException (GetError3());
 326                                 pazValue = IntPtr.Zero; pazColName = IntPtr.Zero; // not used for v=3
 327                                 cols = Sqlite.sqlite3_column_count (pStmt);
 328                         }
 329                         else
 330                         {
 331                                 err = Sqlite.sqlite_step (pStmt, out cols, out pazValue, out pazColName);
 332                                 if (err == SqliteError.ERROR)
 333                                         throw new SqliteExecutionException ();
 334                         }
 335
 336                         if (err == SqliteError.BUSY)
 337                                 throw new SqliteBusyException();
 338
 339                         if (err == SqliteError.MISUSE)
 340                                 throw new SqliteExecutionException();
 341
 342                         // err is either ROW or DONE.
 343                         return err == SqliteError.ROW;
 344                 }
 345
 346                 #endregion
 347
 348                 #region Public Methods
 349
 350                 public void Cancel ()
 351                 {
 352                 }
 353
 354                 public string ProcessParameters()
 355                 {
 356                         string processedText = sql;
 357
 358                         //Regex looks odd perhaps, but it works - same impl. as in the firebird db provider
 359                         //the named parameters are using the ADO.NET standard @-prefix but sqlite is considering ":" as a prefix for v.3...
 360                         //ref: http://www.mail-archive.com/sqlite-users@sqlite.org/msg01851.html
 361                         //Regex r = new Regex(@"(('[^']*?\@[^']*')*[^'@]*?)*(?<param>@\w+)+([^'@]*?('[^']*?\@[^']*'))*",RegexOptions.ExplicitCapture);
 362
 363                         //The above statement is true for the commented regEx, but I changed it to use the :-prefix, because now (12.05.2005 sqlite3)
 364                         //sqlite is using : as Standard Parameterprefix
 365
 366                         MatchEvaluator me = new MatchEvaluator(ReplaceParams);
 367                         processedText = v2Parameters.Replace(sql, me);
 368                         return processedText;
 369                 }
 370
 371                 public void Prepare ()
 372                 {
 373                         // There isn't much we can do here.  If a table schema
 374                         // changes after preparing a statement, Sqlite bails,
 375                         // so we can only compile statements right before we
 376                         // want to run them.
 377
 378                         if (prepared) return;
 379
 380                         if (Parameters.Count > 0 && parent_conn.Version == 2)
 381                         {
 382                                 sql = ProcessParameters();
 383                         }
 384
 385                         prepared = true;
 386                 }
 387
 388                 IDbDataParameter IDbCommand.CreateParameter()
 389                 {
 390                         return CreateParameter ();
 391                 }
 392
 393                 public SqliteParameter CreateParameter ()
 394                 {
 395                         return new SqliteParameter ();
 396                 }
 397
 398                 public int ExecuteNonQuery ()
 399                 {
 400                         int rows_affected;
 401                         ExecuteReader (CommandBehavior.Default, false, out rows_affected);
 402                         return rows_affected;
 403                 }
 404
 405                 public object ExecuteScalar ()
 406                 {
 407                         SqliteDataReader r = ExecuteReader ();
 408                         if (r == null || !r.Read ()) {
 409                                 return null;
 410                         }
 411                         object o = r[0];
 412                         r.Close ();
 413                         return o;
 414                 }
 415
 416                 IDataReader IDbCommand.ExecuteReader ()
 417                 {
 418                         return ExecuteReader ();
 419                 }
 420
 421                 IDataReader IDbCommand.ExecuteReader (CommandBehavior behavior)
 422                 {
 423                         return ExecuteReader (behavior);
 424                 }
 425
 426                 public SqliteDataReader ExecuteReader ()
 427                 {
 428                         return ExecuteReader (CommandBehavior.Default);
 429                 }
 430
 431                 public SqliteDataReader ExecuteReader (CommandBehavior behavior)
 432                 {
 433                         int r;
 434                         return ExecuteReader (behavior, true, out r);
 435                 }
 436
 437                 public SqliteDataReader ExecuteReader (CommandBehavior behavior, bool want_results, out int rows_affected)
 438                 {
 439                         // The SQL string may contain multiple sql commands, so the main
 440                         // thing to do is have Sqlite iterate through the commands.
 441                         // If want_results, only the last command is returned as a
 442                         // DataReader.  Otherwise, no command is returned as a
 443                         // DataReader.
 444
 445                         IntPtr psql = UnixMarshal.StringToHeap(sql);
 446                         IntPtr pzTail = psql;
 447                         IntPtr errMsgPtr;
 448
 449                         parent_conn.StartExec ();
 450
 451                         rows_affected = 0;
 452
 453                         try {
 454                                 while (true) {
 455                                         IntPtr pStmt;
 456
 457                                         GetNextStatement(pzTail, out pzTail, out pStmt);
 458
 459                                         if (pStmt == IntPtr.Zero)
 460                                                 throw new Exception();
 461
 462                                         // pzTail is positioned after the last byte in the
 463                                         // statement, which will be the NULL character if
 464                                         // this was the last statement.
 465                                         bool last = Marshal.ReadByte(pzTail) == 0;
 466
 467                                         try {
 468                                                 if (parent_conn.Version == 3)
 469                                                         BindParameters3 (pStmt);
 470
 471                                                 if (last && want_results)
 472                                                         return new SqliteDataReader (this, pStmt, parent_conn.Version);
 473
 474                                                 ExecuteStatement(pStmt);
 475
 476                                                 if (last) // rows_affected is only used if !want_results
 477                                                         rows_affected = NumChanges ();
 478
 479                                         } finally {
 480                                                 if (parent_conn.Version == 3)
 481                                                         Sqlite.sqlite3_finalize (pStmt);
 482                                                 else
 483                                                         Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
 484                                         }
 485
 486                                         if (last) break;
 487                                 }
 488
 489                                 return null;
 490                         } finally {
 491                                 parent_conn.EndExec ();
 492                                 UnixMarshal.FreeHeap (psql);
 493                         }
 494                 }
 495
 496                 public int LastInsertRowID ()
 497                 {
 498                         if (parent_conn.Version == 3)
 499                                 return Sqlite.sqlite3_last_insert_rowid(parent_conn.Handle);
 500                         else
 501                                 return Sqlite.sqlite_last_insert_rowid(parent_conn.Handle);
 502                 }
 503
 504                 private string GetError3() {
 505                         return Marshal.PtrToStringAnsi (Sqlite.sqlite3_errmsg (parent_conn.Handle));
 506                 }
 507         #endregion
 508         }
 509 }