wasCSharpSQLite – Blame information for rev 1
?pathlinks?
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 | } |