1 /********************************************************
\r
2 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
\r
3 * Written by Robert Simpson (robert@blackcastlesoft.com)
\r
5 * Released to the public domain, use at your own risk!
\r
6 ********************************************************/
\r
8 namespace Mono.Data.Sqlite
\r
12 using System.Data.Common;
\r
13 using System.Collections.Generic;
\r
16 /// This class provides key info for a given SQLite statement.
\r
18 /// Providing key information for a given statement is non-trivial :(
\r
21 internal sealed class SqliteKeyReader : IDisposable
\r
23 private KeyInfo[] _keyInfo;
\r
24 private SqliteStatement _stmt;
\r
25 private bool _isValid;
\r
28 /// Used to support CommandBehavior.KeyInfo
\r
30 private struct KeyInfo
\r
32 internal string databaseName;
\r
33 internal string tableName;
\r
34 internal string columnName;
\r
35 internal int database;
\r
36 internal int rootPage;
\r
37 internal int cursor;
\r
38 internal KeyQuery query;
\r
39 internal int column;
\r
43 /// A single sub-query for a given table/database.
\r
45 private sealed class KeyQuery : IDisposable
\r
47 private SqliteCommand _command;
\r
48 internal SqliteDataReader _reader;
\r
50 internal KeyQuery(SqliteConnection cnn, string database, string table, params string[] columns)
\r
52 using (SqliteCommandBuilder builder = new SqliteCommandBuilder())
\r
54 _command = cnn.CreateCommand();
\r
55 for (int n = 0; n < columns.Length; n++)
\r
57 columns[n] = builder.QuoteIdentifier(columns[n]);
\r
60 _command.CommandText = String.Format("SELECT {0} FROM [{1}].[{2}] WHERE ROWID = ?", String.Join(",", columns), database, table);
\r
61 _command.Parameters.AddWithValue(null, (long)0);
\r
64 internal bool IsValid
\r
66 get { return (_reader != null); }
\r
69 if (value != false) throw new ArgumentException();
\r
70 if (_reader != null)
\r
78 internal void Sync(long rowid)
\r
81 _command.Parameters[0].Value = rowid;
\r
82 _reader = _command.ExecuteReader();
\r
86 public void Dispose()
\r
90 if (_command != null) _command.Dispose();
\r
96 /// This function does all the nasty work at determining what keys need to be returned for
\r
97 /// a given statement.
\r
99 /// <param name="cnn"></param>
\r
100 /// <param name="reader"></param>
\r
101 /// <param name="stmt"></param>
\r
102 internal SqliteKeyReader(SqliteConnection cnn, SqliteDataReader reader, SqliteStatement stmt)
\r
104 Dictionary<string, int> catalogs = new Dictionary<string, int>();
\r
105 Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>();
\r
107 List<KeyInfo> keys = new List<KeyInfo>();
\r
109 // Record the statement so we can use it later for sync'ing
\r
112 // Fetch all the attached databases on this connection
\r
113 using (DataTable tbl = cnn.GetSchema("Catalogs"))
\r
115 foreach (DataRow row in tbl.Rows)
\r
117 catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"]));
\r
121 // Fetch all the unique tables and catalogs used by the current statement
\r
122 using (DataTable schema = reader.GetSchemaTable(false, false))
\r
124 foreach (DataRow row in schema.Rows)
\r
126 // Check if column is backed to a table
\r
127 if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
\r
130 // Record the unique table so we can look up its keys
\r
131 string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
\r
132 string table = (string)row[SchemaTableColumn.BaseTableName];
\r
134 if (tables.ContainsKey(catalog) == false)
\r
136 list = new List<string>();
\r
137 tables.Add(catalog, list);
\r
140 list = tables[catalog];
\r
142 if (list.Contains(table) == false)
\r
146 // For each catalog and each table, query the indexes for the table.
\r
147 // Find a primary key index if there is one. If not, find a unique index instead
\r
148 foreach (KeyValuePair<string, List<string>> pair in tables)
\r
150 for (int i = 0; i < pair.Value.Count; i++)
\r
152 string table = pair.Value[i];
\r
153 DataRow preferredRow = null;
\r
154 using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
\r
156 // Loop twice. The first time looking for a primary key index,
\r
157 // the second time looking for a unique index
\r
158 for (int n = 0; n < 2 && preferredRow == null; n++)
\r
160 foreach (DataRow row in tbl.Rows)
\r
162 if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
\r
164 preferredRow = row;
\r
167 else if (n == 1 && (bool)row["UNIQUE"] == true)
\r
169 preferredRow = row;
\r
174 if (preferredRow == null) // Unable to find any suitable index for this table so remove it
\r
176 pair.Value.RemoveAt(i);
\r
179 else // We found a usable index, so fetch the necessary table details
\r
181 using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
\r
183 // Find the root page of the table in the current statement and get the cursor that's iterating it
\r
184 int database = catalogs[pair.Key];
\r
185 int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"]);
\r
186 int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);
\r
188 // Now enumerate the members of the index we're going to use
\r
189 using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
\r
191 KeyQuery query = null;
\r
193 List<string> cols = new List<string>();
\r
194 for (int x = 0; x < indexColumns.Rows.Count; x++)
\r
196 bool addKey = true;
\r
197 // If the column in the index already appears in the query, skip it
\r
198 foreach (DataRow row in schema.Rows)
\r
200 if (row.IsNull(SchemaTableColumn.BaseColumnName))
\r
203 if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] &&
\r
204 (string)row[SchemaTableColumn.BaseTableName] == table &&
\r
205 (string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key)
\r
207 indexColumns.Rows.RemoveAt(x);
\r
213 if (addKey == true)
\r
214 cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]);
\r
217 // If the index is not a rowid alias, record all the columns
\r
218 // needed to make up the unique index and construct a SQL query for it
\r
219 if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table)
\r
221 // Whatever remains of the columns we need that make up the index that are not
\r
222 // already in the query need to be queried separately, so construct a subquery
\r
223 if (cols.Count > 0)
\r
225 string[] querycols = new string[cols.Count];
\r
226 cols.CopyTo(querycols);
\r
227 query = new KeyQuery(cnn, pair.Key, table, querycols);
\r
231 // Create a KeyInfo struct for each column of the index
\r
232 for (int x = 0; x < indexColumns.Rows.Count; x++)
\r
234 string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"];
\r
235 KeyInfo key = new KeyInfo();
\r
237 key.rootPage = rootPage;
\r
238 key.cursor = cursor;
\r
239 key.database = database;
\r
240 key.databaseName = pair.Key;
\r
241 key.tableName = table;
\r
242 key.columnName = columnName;
\r
256 // Now we have all the additional columns we have to return in order to support
\r
257 // CommandBehavior.KeyInfo
\r
258 _keyInfo = new KeyInfo[keys.Count];
\r
259 keys.CopyTo(_keyInfo);
\r
263 /// How many additional columns of keyinfo we're holding
\r
267 get { return (_keyInfo == null) ? 0 : _keyInfo.Length; }
\r
270 internal void Sync(int i)
\r
273 if (_keyInfo[i].cursor == -1)
\r
274 throw new InvalidCastException();
\r
278 /// Make sure all the subqueries are open and ready and sync'd with the current rowid
\r
279 /// of the table they're supporting
\r
281 internal void Sync()
\r
283 if (_isValid == true) return;
\r
285 KeyQuery last = null;
\r
287 for (int n = 0; n < _keyInfo.Length; n++)
\r
289 if (_keyInfo[n].query == null || _keyInfo[n].query != last)
\r
291 last = _keyInfo[n].query;
\r
295 last.Sync(_stmt._sql.GetRowIdForCursor(_stmt, _keyInfo[n].cursor));
\r
303 /// Release any readers on any subqueries
\r
305 internal void Reset()
\r
308 if (_keyInfo == null) return;
\r
310 for (int n = 0; n < _keyInfo.Length; n++)
\r
312 if (_keyInfo[n].query != null)
\r
313 _keyInfo[n].query.IsValid = false;
\r
317 public void Dispose()
\r
321 if (_keyInfo == null) return;
\r
323 for (int n = 0; n < _keyInfo.Length; n++)
\r
325 if (_keyInfo[n].query != null)
\r
326 _keyInfo[n].query.Dispose();
\r
331 internal string GetDataTypeName(int i)
\r
334 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetDataTypeName(_keyInfo[i].column);
\r
335 else return "integer";
\r
338 internal Type GetFieldType(int i)
\r
341 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetFieldType(_keyInfo[i].column);
\r
342 else return typeof(Int64);
\r
345 internal string GetName(int i)
\r
347 return _keyInfo[i].columnName;
\r
350 internal int GetOrdinal(string name)
\r
352 for (int n = 0; n < _keyInfo.Length; n++)
\r
354 if (String.Compare(name, _keyInfo[n].columnName, StringComparison.OrdinalIgnoreCase) == 0) return n;
\r
359 internal bool GetBoolean(int i)
\r
362 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetBoolean(_keyInfo[i].column);
\r
363 else throw new InvalidCastException();
\r
366 internal byte GetByte(int i)
\r
369 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetByte(_keyInfo[i].column);
\r
370 else throw new InvalidCastException();
\r
373 internal long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
\r
376 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetBytes(_keyInfo[i].column, fieldOffset, buffer, bufferoffset, length);
\r
377 else throw new InvalidCastException();
\r
380 internal char GetChar(int i)
\r
383 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetChar(_keyInfo[i].column);
\r
384 else throw new InvalidCastException();
\r
387 internal long GetChars(int i, long fieldOffset, char[] buffer, int bufferoffset, int length)
\r
390 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetChars(_keyInfo[i].column, fieldOffset, buffer, bufferoffset, length);
\r
391 else throw new InvalidCastException();
\r
394 internal DateTime GetDateTime(int i)
\r
397 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetDateTime(_keyInfo[i].column);
\r
398 else throw new InvalidCastException();
\r
401 internal decimal GetDecimal(int i)
\r
404 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetDecimal(_keyInfo[i].column);
\r
405 else throw new InvalidCastException();
\r
408 internal double GetDouble(int i)
\r
411 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetDouble(_keyInfo[i].column);
\r
412 else throw new InvalidCastException();
\r
415 internal float GetFloat(int i)
\r
418 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetFloat(_keyInfo[i].column);
\r
419 else throw new InvalidCastException();
\r
422 internal Guid GetGuid(int i)
\r
425 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetGuid(_keyInfo[i].column);
\r
426 else throw new InvalidCastException();
\r
429 internal Int16 GetInt16(int i)
\r
432 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetInt16(_keyInfo[i].column);
\r
435 long rowid = _stmt._sql.GetRowIdForCursor(_stmt, _keyInfo[i].cursor);
\r
436 if (rowid == 0) throw new InvalidCastException();
\r
437 return Convert.ToInt16(rowid);
\r
441 internal Int32 GetInt32(int i)
\r
444 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetInt32(_keyInfo[i].column);
\r
447 long rowid = _stmt._sql.GetRowIdForCursor(_stmt, _keyInfo[i].cursor);
\r
448 if (rowid == 0) throw new InvalidCastException();
\r
449 return Convert.ToInt32(rowid);
\r
453 internal Int64 GetInt64(int i)
\r
456 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetInt64(_keyInfo[i].column);
\r
459 long rowid = _stmt._sql.GetRowIdForCursor(_stmt, _keyInfo[i].cursor);
\r
460 if (rowid == 0) throw new InvalidCastException();
\r
461 return Convert.ToInt64(rowid);
\r
465 internal string GetString(int i)
\r
468 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetString(_keyInfo[i].column);
\r
469 else throw new InvalidCastException();
\r
472 internal object GetValue(int i)
\r
474 if (_keyInfo[i].cursor == -1) return DBNull.Value;
\r
477 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.GetValue(_keyInfo[i].column);
\r
479 if (IsDBNull(i) == true)
\r
480 return DBNull.Value;
\r
481 else return GetInt64(i);
\r
484 internal bool IsDBNull(int i)
\r
486 if (_keyInfo[i].cursor == -1) return true;
\r
489 if (_keyInfo[i].query != null) return _keyInfo[i].query._reader.IsDBNull(_keyInfo[i].column);
\r
490 else return _stmt._sql.GetRowIdForCursor(_stmt, _keyInfo[i].cursor) == 0;
\r
494 /// Append all the columns we've added to the original query to the schema
\r
496 /// <param name="tbl"></param>
\r
497 internal void AppendSchemaTable(DataTable tbl)
\r
499 KeyQuery last = null;
\r
501 for (int n = 0; n < _keyInfo.Length; n++)
\r
503 if (_keyInfo[n].query == null || _keyInfo[n].query != last)
\r
505 last = _keyInfo[n].query;
\r
507 if (last == null) // ROWID aliases are treated special
\r
509 DataRow row = tbl.NewRow();
\r
510 row[SchemaTableColumn.ColumnName] = _keyInfo[n].columnName;
\r
511 row[SchemaTableColumn.ColumnOrdinal] = tbl.Rows.Count;
\r
512 row[SchemaTableColumn.ColumnSize] = 8;
\r
513 row[SchemaTableColumn.NumericPrecision] = 255;
\r
514 row[SchemaTableColumn.NumericScale] = 255;
\r
515 row[SchemaTableColumn.ProviderType] = DbType.Int64;
\r
516 row[SchemaTableColumn.IsLong] = false;
\r
517 row[SchemaTableColumn.AllowDBNull] = false;
\r
518 row[SchemaTableOptionalColumn.IsReadOnly] = false;
\r
519 row[SchemaTableOptionalColumn.IsRowVersion] = false;
\r
520 row[SchemaTableColumn.IsUnique] = false;
\r
521 row[SchemaTableColumn.IsKey] = true;
\r
522 row[SchemaTableColumn.DataType] = typeof(Int64);
\r
523 row[SchemaTableOptionalColumn.IsHidden] = true;
\r
524 row[SchemaTableColumn.BaseColumnName] = _keyInfo[n].columnName;
\r
525 row[SchemaTableColumn.IsExpression] = false;
\r
526 row[SchemaTableColumn.IsAliased] = false;
\r
527 row[SchemaTableColumn.BaseTableName] = _keyInfo[n].tableName;
\r
528 row[SchemaTableOptionalColumn.BaseCatalogName] = _keyInfo[n].databaseName;
\r
529 row[SchemaTableOptionalColumn.IsAutoIncrement] = true;
\r
530 row["DataTypeName"] = "integer";
\r
537 using (DataTable tblSub = last._reader.GetSchemaTable())
\r
539 foreach (DataRow row in tblSub.Rows)
\r
541 object[] o = row.ItemArray;
\r
542 DataRow newrow = tbl.Rows.Add(o);
\r
543 newrow[SchemaTableOptionalColumn.IsHidden] = true;
\r
544 newrow[SchemaTableColumn.ColumnOrdinal] = tbl.Rows.Count - 1;
\r