wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 //
2 // Community.CsharpSqlite.SQLiteClient.SqliteConnection.cs
3 //
4 // Represents an open connection to a Sqlite database file.
5 //
6 // Author(s): Vladimir Vukicevic <vladimir@pobox.com>
7 // Everaldo Canuto <everaldo_canuto@yahoo.com.br>
8 // Daniel Morgan <monodanmorg@yahoo.com>
9 // Noah Hart <Noah.Hart@gmail.com>
10 // Stewart Adcock <stewart.adcock@medit.fr>
11 //
12 // Copyright (C) 2002 Vladimir Vukicevic
13 //
14 // Permission is hereby granted, free of charge, to any person obtaining
15 // a copy of this software and associated documentation files (the
16 // "Software"), to deal in the Software without restriction, including
17 // without limitation the rights to use, copy, modify, merge, publish,
18 // distribute, sublicense, and/or sell copies of the Software, and to
19 // permit persons to whom the Software is furnished to do so, subject to
20 // the following conditions:
21 //
22 // The above copyright notice and this permission notice shall be
23 // included in all copies or substantial portions of the Software.
24 //
25 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
26 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
27 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
28 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
29 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
30 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
31 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
32 //
33  
34 using System;
35 using System.Data;
36 using System.IO;
37 using System.Data.Common;
38 using System.Text;
39 using Community.CsharpSqlite;
40  
41 namespace Community.CsharpSqlite.SQLiteClient
42 {
43 /// <summary>
44 /// Represents an open connection to a SQLite3 database.
45 /// </summary>
46 /// <remarks>
47 /// This only supports SQLite version 3, NOT version 2.
48 /// </remarks>
49 public class SqliteConnection : DbConnection, ICloneable
50 {
51  
52 #region Fields
53  
54 private string conn_str;
55 private string db_file;
56 private int db_version;
57 private int db_BusyTimeout;
58 private string db_password;
59 private bool db_IsReadonly;
60 private bool db_IsFailIfMissing;
61 private Encoding db_Encoding; // This is ignored for SQLIte3.
62 private IntPtr sqlite_handle;
63 private Sqlite3.sqlite3 sqlite_handle2;
64 private ConnectionState state;
65 private bool disposed;
66  
67 #endregion
68  
69 #region Constructors and destructors
70  
71 public SqliteConnection ()
72 {
73 db_file = null;
74 db_version = 3;
75 state = ConnectionState.Closed;
76 sqlite_handle = IntPtr.Zero;
77 db_Encoding = null;
78 db_BusyTimeout = 0;
79 }
80  
81 public SqliteConnection (string connstring) : this ()
82 {
83 ConnectionString = connstring;
84 }
85  
86 protected override void Dispose (bool disposing)
87 {
88 try {
89 if (disposing && !disposed) {
90 Close ();
91 conn_str = null;
92 }
93 } finally {
94 disposed = true;
95 base.Dispose (disposing);
96 }
97 }
98  
99 #endregion
100  
101 #region Properties
102  
103 public override string ConnectionString {
104 get { return conn_str; }
105 set { SetConnectionString(value); }
106 }
107  
108 public override int ConnectionTimeout {
109 get { return 0; }
110 }
111  
112 public override string Database {
113 get { return db_file; }
114 }
115  
116 public override ConnectionState State {
117 get { return state; }
118 }
119  
120 [Obsolete("Only meaningful for SQLite2 which is unsupported.")]
121 public Encoding Encoding {
122 get { return db_Encoding; }
123 }
124  
125 public int Version {
126 get { return db_version; }
127 }
128  
129 public override string ServerVersion
130 {
131 get { return Sqlite3.sqlite3_libversion(); }
132 }
133  
134 internal Sqlite3.sqlite3 Handle2
135 {
136 get { return sqlite_handle2; }
137 }
138  
139 internal IntPtr Handle {
140 get { return sqlite_handle; }
141 }
142  
143 public override string DataSource {
144 get { return db_file; }
145 }
146  
147 public int LastInsertRowId {
148 get {
149 return (int) Sqlite3.sqlite3_last_insert_rowid(Handle2);
150 }
151 }
152  
153 public int BusyTimeout {
154 get {
155 return db_BusyTimeout;
156 }
157 set {
158 db_BusyTimeout = value < 0 ? 0 : value;
159 }
160 }
161  
162 #endregion
163  
164 #region Private Methods
165 private void SetConnectionString (string connstring)
166 {
167 if (connstring == null) {
168 Close ();
169 conn_str = null;
170 return;
171 }
172  
173 if (connstring != conn_str) {
174 Close ();
175 conn_str = connstring;
176  
177 db_file = null;
178 db_IsReadonly = false;
179 db_IsFailIfMissing = false;
180  
181 string[] conn_pieces = connstring.Split (new char[]{',',';'}, StringSplitOptions.RemoveEmptyEntries);
182 for (int i = 0; i < conn_pieces.Length; i++) {
183 string piece = conn_pieces [i].Trim ();
184 int firstEqual = piece.IndexOf ('=');
185 if (firstEqual == -1) {
186 throw new InvalidOperationException ("Invalid connection string");
187 }
188 string token = piece.Substring (0, firstEqual);
189 string tvalue = piece.Remove (0, firstEqual + 1).Trim ();
190 string tvalue_lc = tvalue.ToLower (System.Globalization.CultureInfo.InvariantCulture).Trim ();
191 switch (token.ToLower (System.Globalization.CultureInfo.InvariantCulture).Trim ()) {
192 case "data source":
193 case "uri":
194 if (tvalue_lc.StartsWith ("file://")) {
195 db_file = tvalue.Substring (7);
196 } else if (tvalue_lc.StartsWith ("file:")) {
197 db_file = tvalue.Substring (5);
198 } else if (tvalue_lc.StartsWith ("/")) {
199 db_file = tvalue;
200 #if !(SQLITE_SILVERLIGHT || WINDOWS_MOBILE)
201 } else if (tvalue_lc.StartsWith ("|DataDirectory|",
202 StringComparison.OrdinalIgnoreCase)) {
203 AppDomainSetup ads = AppDomain.CurrentDomain.SetupInformation;
204 string filePath = String.Format ("App_Data{0}{1}",
205 Path.DirectorySeparatorChar,
206 tvalue_lc.Substring (15));
207  
208 db_file = Path.Combine (ads.ApplicationBase, filePath);
209 #endif
210 } else {
211 #if !WINDOWS_PHONE
212 throw new InvalidOperationException ("Invalid connection string: invalid URI");
213 #else
214 db_file = tvalue;
215 #endif
216 }
217 break;
218  
219 case "mode": // Ignored for SQLite3.
220 ////int db_mode = Convert.ToInt32 (tvalue);
221 break;
222  
223 case "version":
224 db_version = Convert.ToInt32 (tvalue);
225 if (db_version < 3)
226 throw new InvalidOperationException ("Minimum database version is 3");
227 break;
228  
229 case "encoding": // Ignored for SQLite3.
230 db_Encoding = Encoding.GetEncoding (tvalue);
231 break;
232  
233 case "busy_timeout":
234 db_BusyTimeout = Convert.ToInt32 (tvalue);
235 break;
236  
237 case "read only":
238 case "readonly":
239 db_IsReadonly = ConvertStringToBoolean (tvalue.ToLowerInvariant());
240 break;
241  
242 case "failifmissing":
243 db_IsFailIfMissing = ConvertStringToBoolean (tvalue.ToLowerInvariant());
244 break;
245  
246 case "password":
247 if (!string.IsNullOrEmpty (db_password) && (db_password.Length != 34 || !db_password.StartsWith ("0x")))
248 throw new InvalidOperationException ("Invalid password string: must be 34 hex digits starting with 0x");
249 db_password = tvalue;
250 break;
251 }
252 }
253  
254 if (db_file == null) {
255 throw new InvalidOperationException ("Invalid connection string: no URI");
256 }
257 }
258 }
259  
260 /// <summary>
261 /// Convert the specified string to a boolean value.
262 /// </summary>
263 /// <remarks>
264 /// The string must be one of "true", "yes" (converted to <c>true<c/c>),
265 /// "false", "no" (converted to <c>false<c/c>).
266 /// </remarks>
267 /// <exception cref="ArgumentNullException">Thrown if string is null.</exception>
268 /// <exception cref="ArgumentException">Thrown if string is not converted to valid boolean.</exception>
269 private static bool ConvertStringToBoolean (string value)
270 {
271 if (value == null)
272 throw new ArgumentNullException ("null value cannot be converted to boolean");
273 if (value == "yes" || value == "true")
274 return true;
275 if (value == "no" || value == "false")
276 return false;
277 throw new ArgumentException (string.Format ("Invalid boolean value: \"{0}\"", value));
278 }
279 #endregion
280  
281 #region Internal Methods
282  
283 internal void StartExec ()
284 {
285 // use a mutex here
286 state = ConnectionState.Executing;
287 }
288  
289 internal void EndExec ()
290 {
291 state = ConnectionState.Open;
292 }
293  
294 #endregion
295  
296 #region Public Methods
297  
298 object ICloneable.Clone ()
299 {
300 return new SqliteConnection (ConnectionString);
301 }
302  
303 protected override DbTransaction BeginDbTransaction (IsolationLevel il)
304 {
305 if (state != ConnectionState.Open)
306 throw new InvalidOperationException("Invalid operation: The connection is closed");
307  
308 SqliteTransaction t = new SqliteTransaction();
309 t.SetConnection (this);
310 SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
311 cmd.CommandText = "BEGIN";
312 cmd.ExecuteNonQuery();
313 return t;
314 }
315  
316 public new DbTransaction BeginTransaction ()
317 {
318 return BeginDbTransaction (IsolationLevel.Unspecified);
319 }
320  
321 public new DbTransaction BeginTransaction (IsolationLevel il)
322 {
323 return BeginDbTransaction (il);
324 }
325  
326 public override void Close ()
327 {
328 if (state != ConnectionState.Open) {
329 return;
330 }
331  
332 state = ConnectionState.Closed;
333  
334 if (Version == 3)
335 Sqlite3.sqlite3_close (sqlite_handle2);
336 sqlite_handle = IntPtr.Zero;
337 }
338  
339 public override void ChangeDatabase (string databaseName)
340 {
341 Close ();
342 db_file = databaseName;
343 Open ();
344 }
345  
346 protected override DbCommand CreateDbCommand ()
347 {
348 return new SqliteCommand (null, this);
349 }
350  
351 /// <summary>
352 /// Opens the connection using the parameters provided by the <see cref="ConnectionString">ConnectionString</see>.
353 /// </summary>
354 /// <exception cref="InvalidOperationException">Thrown if no database was specified.</exception>
355 /// <exception cref="InvalidOperationException">Thrown if the connection stater is not closed.</exception>
356 /// <exception cref="ApplicationException">Thrown if a database error occurred.</exception>
357 public override void Open ()
358 {
359 if (conn_str == null) {
360 throw new InvalidOperationException ("No database specified");
361 }
362  
363 if (state != ConnectionState.Closed) {
364 throw new InvalidOperationException ("Connection state is not closed.");
365 }
366  
367 if (Version == 3) {
368 int flags = Sqlite3.SQLITE_OPEN_NOMUTEX;
369 if (!db_IsFailIfMissing && !db_IsReadonly)
370 flags |= Sqlite3.SQLITE_OPEN_CREATE;
371 if (db_IsReadonly) {
372 flags |= Sqlite3.SQLITE_OPEN_READONLY;
373 } else {
374 flags |= Sqlite3.SQLITE_OPEN_READWRITE;
375 }
376  
377 sqlite_handle = (IntPtr)1;
378 int err = Sqlite3.sqlite3_open_v2( db_file, out sqlite_handle2, flags, null );
379 if (err == (int)SqliteError.ERROR)
380 throw new ApplicationException (Sqlite3.sqlite3_errmsg(sqlite_handle2));
381 if (db_BusyTimeout != 0)
382 Sqlite3.sqlite3_busy_timeout(sqlite_handle2, db_BusyTimeout);
383 if ( !string.IsNullOrEmpty( db_password ) )
384 {
385 SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
386 cmd.CommandText = "pragma hexkey='" + db_password + "'";
387 cmd.ExecuteNonQuery();
388 }
389 }
390 state = ConnectionState.Open;
391 }
392  
393 #if !SQLITE_SILVERLIGHT
394 public override DataTable GetSchema( String collectionName )
395 {
396 return GetSchema( collectionName, null );
397 }
398  
399 public override DataTable GetSchema( String collectionName, string[] restrictionValues )
400 {
401 if ( State != ConnectionState.Open )
402 throw new InvalidOperationException( "Invalid operation. The connection is closed." );
403  
404 int restrictionsCount = 0;
405 if ( restrictionValues != null )
406 restrictionsCount = restrictionValues.Length;
407  
408 DataTable metaTable = GetSchemaMetaDataCollections();
409 foreach ( DataRow row in metaTable.Rows )
410 {
411 if ( String.Compare( row["CollectionName"].ToString(), collectionName, true ) == 0 )
412 {
413 int restrictions = (int)row["NumberOfRestrictions"];
414 if ( restrictionsCount > restrictions )
415 throw new ArgumentException( "More restrictions were provided than needed." );
416 }
417 }
418  
419 switch ( collectionName.ToUpper() )
420 {
421 case "METADATACOLLECTIONS":
422 return metaTable;
423 case "DATASOURCEINFORMATION":
424 return GetSchemaDataSourceInformation();
425 case "DATATYPES":
426 return GetSchemaDataTypes();
427 case "RESTRICTIONS":
428 return GetSchemaRestrictions();
429 case "RESERVEDWORDS":
430 return GetSchemaReservedWords();
431 case "TABLES":
432 return GetSchemaTables( restrictionValues );
433 case "COLUMNS":
434 return GetSchemaColumns( restrictionValues );
435 case "VIEWS":
436 return GetSchemaViews( restrictionValues );
437 case "INDEXCOLUMNS":
438 return GetSchemaIndexColumns( restrictionValues );
439 case "INDEXES":
440 return GetSchemaIndexes( restrictionValues );
441 case "UNIQUEKEYS":
442 throw new NotImplementedException( collectionName );
443 case "PRIMARYKEYS":
444 throw new NotImplementedException( collectionName );
445 case "FOREIGNKEYS":
446 return GetSchemaForeignKeys( restrictionValues );
447 case "FOREIGNKEYCOLUMNS":
448 throw new NotImplementedException( collectionName );
449 case "TRIGGERS":
450 return GetSchemaTriggers( restrictionValues );
451 }
452  
453 throw new ArgumentException( "The requested collection is not defined." );
454 }
455  
456 static DataTable metaDataCollections = null;
457 DataTable GetSchemaMetaDataCollections()
458 {
459 if ( metaDataCollections != null )
460 return metaDataCollections;
461  
462 DataTable dt = new DataTable();
463  
464 dt.Columns.Add( "CollectionName", typeof( System.String ) );
465 dt.Columns.Add( "NumberOfRestrictions", typeof( System.Int32 ) );
466 dt.Columns.Add( "NumberOfIdentifierParts", typeof( System.Int32 ) );
467  
468 dt.LoadDataRow( new object[] { "MetaDataCollections", 0, 0 }, true );
469 dt.LoadDataRow( new object[] { "DataSourceInformation", 0, 0 }, true );
470 dt.LoadDataRow( new object[] { "DataTypes", 0, 0 }, true );
471 dt.LoadDataRow( new object[] { "Restrictions", 0, 0 }, true );
472 dt.LoadDataRow( new object[] { "ReservedWords", 0, 0 }, true );
473 dt.LoadDataRow( new object[] { "Tables", 1, 1 }, true );
474 dt.LoadDataRow( new object[] { "Columns", 1, 1 }, true );
475 dt.LoadDataRow( new object[] { "Views", 1, 1 }, true );
476 dt.LoadDataRow( new object[] { "IndexColumns", 1, 1 }, true );
477 dt.LoadDataRow( new object[] { "Indexes", 1, 1 }, true );
478 //dt.LoadDataRow(new object[] { "UniqueKeys", 1, 1 }, true);
479 //dt.LoadDataRow(new object[] { "PrimaryKeys", 1, 1 }, true);
480 dt.LoadDataRow( new object[] { "ForeignKeys", 1, 1 }, true );
481 //dt.LoadDataRow(new object[] { "ForeignKeyColumns", 1, 1 }, true);
482 dt.LoadDataRow( new object[] { "Triggers", 1, 1 }, true );
483  
484 return dt;
485 }
486  
487 DataTable GetSchemaRestrictions()
488 {
489 DataTable dt = new DataTable();
490  
491 dt.Columns.Add( "CollectionName", typeof( System.String ) );
492 dt.Columns.Add( "RestrictionName", typeof( System.String ) );
493 dt.Columns.Add( "ParameterName", typeof( System.String ) );
494 dt.Columns.Add( "RestrictionDefault", typeof( System.String ) );
495 dt.Columns.Add( "RestrictionNumber", typeof( System.Int32 ) );
496  
497 dt.LoadDataRow( new object[] { "Tables", "Table", "TABLENAME", "TABLE_NAME", 1 }, true );
498 dt.LoadDataRow( new object[] { "Columns", "Table", "TABLENAME", "TABLE_NAME", 1 }, true );
499 dt.LoadDataRow( new object[] { "Views", "View", "VIEWNAME", "VIEW_NAME", 1 }, true );
500 dt.LoadDataRow( new object[] { "IndexColumns", "Name", "NAME", "INDEX_NAME", 1 }, true );
501 dt.LoadDataRow( new object[] { "Indexes", "TableName", "TABLENAME", "TABLE_NAME", 1 }, true );
502 dt.LoadDataRow( new object[] { "ForeignKeys", "Foreign_Key_Table_Name", "TABLENAME", "TABLE_NAME", 1 }, true );
503 dt.LoadDataRow( new object[] { "Triggers", "TableName", "TABLENAME", "TABLE_NAME", 1 }, true );
504  
505 return dt;
506 }
507  
508 DataTable GetSchemaTables( string[] restrictionValues )
509 {
510 SqliteCommand cmd = new SqliteCommand(
511 "SELECT type, name, tbl_name, rootpage, sql " +
512 " FROM sqlite_master " +
513 " WHERE (name = :pname or (:pname is null)) " +
514 " AND type = 'table' " +
515 " ORDER BY name", this );
516 cmd.Parameters.Add( "pname", DbType.String ).Value = DBNull.Value;
517 return GetSchemaDataTable( cmd, restrictionValues );
518 }
519  
520 DataTable GetSchemaColumns( string[] restrictionValues )
521 {
522 if ( restrictionValues == null || restrictionValues.Length == 0 )
523 {
524 throw new ArgumentException( "Columns must contain at least one restriction value for the table name." );
525 }
526 ValidateIdentifier( restrictionValues[0] );
527  
528 SqliteCommand cmd = (SqliteCommand)CreateCommand();
529 cmd.CommandText = string.Format( "PRAGMA table_info({0})", restrictionValues[0] );
530 return GetSchemaDataTable( cmd, restrictionValues );
531 }
532  
533 DataTable GetSchemaTriggers( string[] restrictionValues )
534 {
535 SqliteCommand cmd = new SqliteCommand(
536 "SELECT type, name, tbl_name, rootpage, sql " +
537 " FROM sqlite_master " +
538 " WHERE (tbl_name = :pname or :pname is null) " +
539 " AND type = 'trigger' " +
540 " ORDER BY name", this );
541 cmd.Parameters.Add( "pname", DbType.String ).Value = DBNull.Value;
542 return GetSchemaDataTable( cmd, restrictionValues );
543 }
544  
545 DataTable GetSchemaIndexColumns( string[] restrictionValues )
546 {
547 if ( restrictionValues == null || restrictionValues.Length == 0 )
548 {
549 throw new ArgumentException( "IndexColumns must contain at least one restriction value for the index name." );
550 }
551 ValidateIdentifier( restrictionValues[0] );
552  
553 SqliteCommand cmd = (SqliteCommand)CreateCommand();
554 cmd.CommandText = string.Format( "PRAGMA index_info({0})", restrictionValues[0] );
555 return GetSchemaDataTable( cmd, restrictionValues );
556 }
557  
558 DataTable GetSchemaIndexes( string[] restrictionValues )
559 {
560 if ( restrictionValues == null || restrictionValues.Length == 0 )
561 {
562 throw new ArgumentException( "Indexes must contain at least one restriction value for the table name." );
563 }
564 ValidateIdentifier( restrictionValues[0] );
565  
566 SqliteCommand cmd = (SqliteCommand)CreateCommand();
567 cmd.CommandText = string.Format( "PRAGMA index_list({0})", restrictionValues[0] );
568 return GetSchemaDataTable( cmd, restrictionValues );
569 }
570  
571 DataTable GetSchemaForeignKeys( string[] restrictionValues )
572 {
573 if ( restrictionValues == null || restrictionValues.Length == 0 )
574 {
575 throw new ArgumentException( "Foreign Keys must contain at least one restriction value for the table name." );
576 }
577 ValidateIdentifier( restrictionValues[0] );
578  
579 SqliteCommand cmd = (SqliteCommand)CreateCommand();
580 cmd.CommandText = string.Format( "PRAGMA foreign_key_list({0})", restrictionValues[0] );
581 return GetSchemaDataTable( cmd, restrictionValues );
582 }
583  
584 #endif
585 void ValidateIdentifier( string value )
586 {
587 if ( value.Contains( "'" ) )
588 throw new ArgumentException( "Identifiers can not contain a single quote." );
589 }
590  
591 #if !SQLITE_SILVERLIGHT
592 DataTable GetSchemaViews( string[] restrictionValues )
593 {
594 SqliteCommand cmd = new SqliteCommand(
595 "SELECT type, name, tbl_name, rootpage, sql " +
596 " FROM sqlite_master " +
597 " WHERE (name = :pname or :pname is null) " +
598 " AND type = 'view' " +
599 " ORDER BY name", this );
600 cmd.Parameters.Add( "pname", DbType.String ).Value = DBNull.Value;
601 return GetSchemaDataTable( cmd, restrictionValues );
602 }
603  
604 DataTable GetSchemaDataSourceInformation()
605 {
606 DataTable dt = new DataTable();
607  
608 dt.Columns.Add( "CompositeIdentifierSeparatorPattern", typeof( System.String ) );
609 dt.Columns.Add( "DataSourceProductName", typeof( System.String ) );
610 dt.Columns.Add( "DataSourceProductVersion", typeof( System.String ) );
611 dt.Columns.Add( "DataSourceProductVersionNormalized", typeof( System.String ) );
612 #if !WINDOWS_MOBILE
613 dt.Columns.Add( "GroupByBehavior", typeof( System.Data.Common.GroupByBehavior ) );
614 #else
615 dt.Columns.Add("GroupByBehavior", typeof(object));
616 #endif
617 dt.Columns.Add( "IdentifierPattern", typeof( System.String ) );
618 #if !WINDOWS_MOBILE
619 dt.Columns.Add( "IdentifierCase", typeof( System.Data.Common.IdentifierCase ) );
620 #else
621 dt.Columns.Add("IdentifierCase", typeof(object ));
622 #endif
623 dt.Columns.Add( "OrderByColumnsInSelect", typeof( System.Boolean ) );
624 dt.Columns.Add( "ParameterMarkerFormat", typeof( System.String ) );
625 dt.Columns.Add( "ParameterMarkerPattern", typeof( System.String ) );
626 dt.Columns.Add( "ParameterNameMaxLength", typeof( System.Int32 ) );
627 dt.Columns.Add( "ParameterNamePattern", typeof( System.String ) );
628 dt.Columns.Add( "QuotedIdentifierPattern", typeof( System.String ) );
629 #if !WINDOWS_MOBILE
630 dt.Columns.Add( "QuotedIdentifierCase", typeof( System.Data.Common.IdentifierCase ) );
631 #else
632 dt.Columns.Add("QuotedIdentifierCase", typeof(object));
633 #endif
634 dt.Columns.Add( "StatementSeparatorPattern", typeof( System.String ) );
635 dt.Columns.Add( "StringLiteralPattern", typeof( System.String ) );
636 #if !WINDOWS_MOBILE
637 dt.Columns.Add( "SupportedJoinOperators", typeof( System.Data.Common.SupportedJoinOperators ) );
638 #else
639 dt.Columns.Add("SupportedJoinOperators", typeof(object ));
640 #endif
641  
642 // TODO: set correctly
643 dt.LoadDataRow( new object[] { "",
644 "SQLite",
645 ServerVersion,
646 ServerVersion,
647 3,
648 "",
649 1,
650 false,
651 "",
652 "",
653 30,
654 "",
655 2,
656 DBNull.Value,
657 "" },
658 true );
659  
660 return dt;
661 }
662  
663 DataTable GetSchemaDataTypes()
664 {
665 DataTable dt = new DataTable();
666  
667 dt.Columns.Add( "TypeName", typeof( System.String ) );
668 dt.Columns.Add( "ProviderDbType", typeof( System.String ) );
669 dt.Columns.Add( "StorageType", typeof( System.Int32 ) );
670 dt.Columns.Add( "DataType", typeof( System.String ) );
671 // TODO: fill the rest of these
672 /*
673 dt.Columns.Add("ColumnSize", typeof(System.Int64));
674 dt.Columns.Add("CreateFormat", typeof(System.String));
675 dt.Columns.Add("CreateParameters", typeof(System.String));
676 dt.Columns.Add("IsAutoIncrementable",typeof(System.Boolean));
677 dt.Columns.Add("IsBestMatch", typeof(System.Boolean));
678 dt.Columns.Add("IsCaseSensitive", typeof(System.Boolean));
679 dt.Columns.Add("IsFixedLength", typeof(System.Boolean));
680 dt.Columns.Add("IsFixedPrecisionScale",typeof(System.Boolean));
681 dt.Columns.Add("IsLong", typeof(System.Boolean));
682 dt.Columns.Add("IsNullable", typeof(System.Boolean));
683 dt.Columns.Add("IsSearchable", typeof(System.Boolean));
684 dt.Columns.Add("IsSearchableWithLike",typeof(System.Boolean));
685 dt.Columns.Add("IsUnsigned", typeof(System.Boolean));
686 dt.Columns.Add("MaximumScale", typeof(System.Int16));
687 dt.Columns.Add("MinimumScale", typeof(System.Int16));
688 dt.Columns.Add("IsConcurrencyType",typeof(System.Boolean));
689 dt.Columns.Add("IsLiteralSupported",typeof(System.Boolean));
690 dt.Columns.Add("LiteralPrefix", typeof(System.String));
691 dt.Columns.Add("LiteralSuffix", typeof(System.String));
692 */
693  
694 dt.LoadDataRow( new object[] { "INT", "INTEGER", 1, "System.Int32" }, true );
695 dt.LoadDataRow( new object[] { "INTEGER", "INTEGER", 1, "System.Int32" }, true );
696 dt.LoadDataRow( new object[] { "TINYINT", "INTEGER", 1, "System.Byte" }, true );
697 dt.LoadDataRow( new object[] { "SMALLINT", "INTEGER", 1, "System.Int16" }, true );
698 dt.LoadDataRow( new object[] { "MEDIUMINT", "INTEGER", 1, "System.Int32" }, true );
699 dt.LoadDataRow( new object[] { "BIGINT", "INTEGER", 1, "System.Int64" }, true );
700 dt.LoadDataRow( new object[] { "UNSIGNED BIGINT", "INTEGER", 1, "System.UInt64" }, true );
701 dt.LoadDataRow( new object[] { "INT2", "INTEGER", 1, "System.Int16" }, true );
702 dt.LoadDataRow( new object[] { "INT8", "INTEGER", 1, "System.Int64" }, true );
703  
704 dt.LoadDataRow( new object[] { "CHARACTER", "TEXT", 2, "System.String" }, true );
705 dt.LoadDataRow( new object[] { "VARCHAR", "TEXT", 2, "System.String" }, true );
706 dt.LoadDataRow( new object[] { "VARYING CHARACTER", "TEXT", 2, "System.String" }, true );
707 dt.LoadDataRow( new object[] { "NCHAR", "TEXT", 2, "System.String" }, true );
708 dt.LoadDataRow( new object[] { "NATIVE CHARACTER", "TEXT", 2, "System.String" }, true );
709 dt.LoadDataRow( new object[] { "NVARHCAR", "TEXT", 2, "System.String" }, true );
710 dt.LoadDataRow( new object[] { "TEXT", "TEXT", 2, "System.String" }, true );
711 dt.LoadDataRow( new object[] { "CLOB", "TEXT", 2, "System.String" }, true );
712  
713 dt.LoadDataRow( new object[] { "BLOB", "NONE", 3, "System.Byte[]" }, true );
714  
715 dt.LoadDataRow( new object[] { "REAL", "REAL", 4, "System.Double" }, true );
716 dt.LoadDataRow( new object[] { "DOUBLE", "REAL", 4, "System.Double" }, true );
717 dt.LoadDataRow( new object[] { "DOUBLE PRECISION", "REAL", 4, "System.Double" }, true );
718 dt.LoadDataRow( new object[] { "FLOAT", "REAL", 4, "System.Double" }, true );
719  
720 dt.LoadDataRow( new object[] { "NUMERIC", "NUMERIC", 5, "System.Decimal" }, true );
721 dt.LoadDataRow( new object[] { "DECIMAL", "NUMERIC", 5, "System.Decimal" }, true );
722 dt.LoadDataRow( new object[] { "BOOLEAN", "NUMERIC", 5, "System.Boolean" }, true );
723 dt.LoadDataRow( new object[] { "DATE", "NUMERIC", 5, "System.DateTime" }, true );
724 dt.LoadDataRow( new object[] { "DATETIME", "NUMERIC", 5, "System.DateTime" }, true );
725  
726 return dt;
727 }
728  
729 DataTable GetSchemaReservedWords()
730 {
731 DataTable dt = new DataTable();
732  
733 dt.Columns.Add( "ReservedWord", typeof( System.String ) );
734  
735 dt.LoadDataRow( new object[] { "ABORT" }, true );
736 dt.LoadDataRow( new object[] { "ACTION" }, true );
737 dt.LoadDataRow( new object[] { "ADD" }, true );
738 dt.LoadDataRow( new object[] { "AFTER" }, true );
739 dt.LoadDataRow( new object[] { "ALL" }, true );
740 dt.LoadDataRow( new object[] { "ANALYZE" }, true );
741 dt.LoadDataRow( new object[] { "AND" }, true );
742 dt.LoadDataRow( new object[] { "AS" }, true );
743 dt.LoadDataRow( new object[] { "ATTACH" }, true );
744 dt.LoadDataRow( new object[] { "AUTOINCREMENT" }, true );
745 dt.LoadDataRow( new object[] { "BEFORE" }, true );
746 dt.LoadDataRow( new object[] { "BEFORE" }, true );
747 dt.LoadDataRow( new object[] { "BEGIN" }, true );
748 dt.LoadDataRow( new object[] { "BETWEEN" }, true );
749 dt.LoadDataRow( new object[] { "BY" }, true );
750 dt.LoadDataRow( new object[] { "CASCADE" }, true );
751 dt.LoadDataRow( new object[] { "CASE" }, true );
752 dt.LoadDataRow( new object[] { "CAST" }, true );
753 dt.LoadDataRow( new object[] { "CHECK" }, true );
754 dt.LoadDataRow( new object[] { "COLLATE" }, true );
755 dt.LoadDataRow( new object[] { "COLUMN" }, true );
756 dt.LoadDataRow( new object[] { "COMMIT" }, true );
757 dt.LoadDataRow( new object[] { "CONFLICT" }, true );
758 dt.LoadDataRow( new object[] { "CONTRAINT" }, true );
759 dt.LoadDataRow( new object[] { "CREATE" }, true );
760 dt.LoadDataRow( new object[] { "CROSS" }, true );
761 dt.LoadDataRow( new object[] { "CURRENT_DATE" }, true );
762 dt.LoadDataRow( new object[] { "CURRENT_TIME" }, true );
763 dt.LoadDataRow( new object[] { "CURRENT_TIMESTAMP" }, true );
764 dt.LoadDataRow( new object[] { "DATABASE" }, true );
765 dt.LoadDataRow( new object[] { "DEFAULT" }, true );
766 dt.LoadDataRow( new object[] { "DEFERRABLE" }, true );
767 dt.LoadDataRow( new object[] { "DEFERRED" }, true );
768 dt.LoadDataRow( new object[] { "DELETE" }, true );
769 dt.LoadDataRow( new object[] { "DESC" }, true );
770 dt.LoadDataRow( new object[] { "DETACH" }, true );
771 dt.LoadDataRow( new object[] { "DISTINCT" }, true );
772 dt.LoadDataRow( new object[] { "DROP" }, true );
773 dt.LoadDataRow( new object[] { "EACH" }, true );
774 dt.LoadDataRow( new object[] { "ELSE" }, true );
775 dt.LoadDataRow( new object[] { "END" }, true );
776 dt.LoadDataRow( new object[] { "ESCAPE" }, true );
777 dt.LoadDataRow( new object[] { "EXCEPT" }, true );
778 dt.LoadDataRow( new object[] { "EXCLUSIVE" }, true );
779 dt.LoadDataRow( new object[] { "EXISTS" }, true );
780 dt.LoadDataRow( new object[] { "EXPLAIN" }, true );
781 dt.LoadDataRow( new object[] { "FAIL" }, true );
782 dt.LoadDataRow( new object[] { "FOR" }, true );
783 dt.LoadDataRow( new object[] { "FOREIGN" }, true );
784 dt.LoadDataRow( new object[] { "FROM" }, true );
785 dt.LoadDataRow( new object[] { "FULL" }, true );
786 dt.LoadDataRow( new object[] { "GLOB" }, true );
787 dt.LoadDataRow( new object[] { "GROUP" }, true );
788 dt.LoadDataRow( new object[] { "HAVING" }, true );
789 dt.LoadDataRow( new object[] { "IF" }, true );
790 dt.LoadDataRow( new object[] { "IGNORE" }, true );
791 dt.LoadDataRow( new object[] { "IMMEDIATE" }, true );
792 dt.LoadDataRow( new object[] { "IN" }, true );
793 dt.LoadDataRow( new object[] { "INDEX" }, true );
794 dt.LoadDataRow( new object[] { "INITIALLY" }, true );
795 dt.LoadDataRow( new object[] { "INNER" }, true );
796 dt.LoadDataRow( new object[] { "INSERT" }, true );
797 dt.LoadDataRow( new object[] { "INSTEAD" }, true );
798 dt.LoadDataRow( new object[] { "INTERSECT" }, true );
799 dt.LoadDataRow( new object[] { "INTO" }, true );
800 dt.LoadDataRow( new object[] { "IS" }, true );
801 dt.LoadDataRow( new object[] { "ISNULL" }, true );
802 dt.LoadDataRow( new object[] { "JOIN" }, true );
803 dt.LoadDataRow( new object[] { "KEY" }, true );
804 dt.LoadDataRow( new object[] { "LEFT" }, true );
805 dt.LoadDataRow( new object[] { "LIKE" }, true );
806 dt.LoadDataRow( new object[] { "LIMIT" }, true );
807 dt.LoadDataRow( new object[] { "MATCH" }, true );
808 dt.LoadDataRow( new object[] { "NATURAL" }, true );
809 dt.LoadDataRow( new object[] { "NO" }, true );
810 dt.LoadDataRow( new object[] { "NOT" }, true );
811 dt.LoadDataRow( new object[] { "NOT NULL" }, true );
812 dt.LoadDataRow( new object[] { "OF" }, true );
813 dt.LoadDataRow( new object[] { "OFFSET" }, true );
814 dt.LoadDataRow( new object[] { "ON" }, true );
815 dt.LoadDataRow( new object[] { "OR" }, true );
816 dt.LoadDataRow( new object[] { "ORDER" }, true );
817 dt.LoadDataRow( new object[] { "OUTER" }, true );
818 dt.LoadDataRow( new object[] { "PLAN" }, true );
819 dt.LoadDataRow( new object[] { "PRAGMA" }, true );
820 dt.LoadDataRow( new object[] { "PRIMARY" }, true );
821 dt.LoadDataRow( new object[] { "QUERY" }, true );
822 dt.LoadDataRow( new object[] { "RAISE" }, true );
823 dt.LoadDataRow( new object[] { "REFERENCES" }, true );
824 dt.LoadDataRow( new object[] { "REGEXP" }, true );
825 dt.LoadDataRow( new object[] { "REINDEX" }, true );
826 dt.LoadDataRow( new object[] { "RELEASE" }, true );
827 dt.LoadDataRow( new object[] { "RENAME" }, true );
828 dt.LoadDataRow( new object[] { "REPLACE" }, true );
829 dt.LoadDataRow( new object[] { "RESTRICT" }, true );
830 dt.LoadDataRow( new object[] { "RIGHT" }, true );
831 dt.LoadDataRow( new object[] { "ROLLBACK" }, true );
832 dt.LoadDataRow( new object[] { "ROW" }, true );
833 dt.LoadDataRow( new object[] { "SAVEPOOINT" }, true );
834 dt.LoadDataRow( new object[] { "SELECT" }, true );
835 dt.LoadDataRow( new object[] { "SET" }, true );
836 dt.LoadDataRow( new object[] { "TABLE" }, true );
837 dt.LoadDataRow( new object[] { "TEMP" }, true );
838 dt.LoadDataRow( new object[] { "TEMPORARY" }, true );
839 dt.LoadDataRow( new object[] { "THEN" }, true );
840 dt.LoadDataRow( new object[] { "TO" }, true );
841 dt.LoadDataRow( new object[] { "TRANSACTION" }, true );
842 dt.LoadDataRow( new object[] { "TRIGGER" }, true );
843 dt.LoadDataRow( new object[] { "UNION" }, true );
844 dt.LoadDataRow( new object[] { "UNIQUE" }, true );
845 dt.LoadDataRow( new object[] { "UPDATE" }, true );
846 dt.LoadDataRow( new object[] { "USING" }, true );
847 dt.LoadDataRow( new object[] { "VACUUM" }, true );
848 dt.LoadDataRow( new object[] { "VALUES" }, true );
849 dt.LoadDataRow( new object[] { "VIEW" }, true );
850 dt.LoadDataRow( new object[] { "VIRTUAL" }, true );
851 dt.LoadDataRow( new object[] { "WHEN" }, true );
852 dt.LoadDataRow( new object[] { "WHERE" }, true );
853  
854 return dt;
855 }
856  
857 DataTable GetSchemaDataTable( SqliteCommand cmd, string[] restrictionValues )
858 {
859 if ( restrictionValues != null && cmd.Parameters.Count > 0 )
860 {
861 for ( int i = 0; i < restrictionValues.Length; i++ )
862 cmd.Parameters[i].Value = restrictionValues[i];
863 }
864  
865 SqliteDataAdapter adapter = new SqliteDataAdapter( cmd );
866 DataTable dt = new DataTable();
867 adapter.Fill( dt );
868  
869 return dt;
870 }
871 #endif
872 #endregion
873  
874 }
875 }