/// <summary> /// Append all the columns we've added to the original query to the schema /// </summary> /// <param name="tbl"></param> internal void AppendSchemaTable(DataTable tbl) { KeyQuery last = null; for (int n = 0; n < _keyInfo.Length; n++) { if (_keyInfo[n].query == null || _keyInfo[n].query != last) { last = _keyInfo[n].query; if (last == null) // ROWID aliases are treated special { DataRow row = tbl.NewRow(); row[SchemaTableColumn.ColumnName] = _keyInfo[n].columnName; row[SchemaTableColumn.ColumnOrdinal] = tbl.Rows.Count; row[SchemaTableColumn.ColumnSize] = 8; row[SchemaTableColumn.NumericPrecision] = 255; row[SchemaTableColumn.NumericScale] = 255; row[SchemaTableColumn.ProviderType] = DbType.Int64; row[SchemaTableColumn.IsLong] = false; row[SchemaTableColumn.AllowDBNull] = false; row[SchemaTableOptionalColumn.IsReadOnly] = false; row[SchemaTableOptionalColumn.IsRowVersion] = false; row[SchemaTableColumn.IsUnique] = false; row[SchemaTableColumn.IsKey] = true; row[SchemaTableColumn.DataType] = typeof(Int64); row[SchemaTableOptionalColumn.IsHidden] = true; row[SchemaTableColumn.BaseColumnName] = _keyInfo[n].columnName; row[SchemaTableColumn.IsExpression] = false; row[SchemaTableColumn.IsAliased] = false; row[SchemaTableColumn.BaseTableName] = _keyInfo[n].tableName; row[SchemaTableOptionalColumn.BaseCatalogName] = _keyInfo[n].databaseName; row[SchemaTableOptionalColumn.IsAutoIncrement] = true; row["DataTypeName"] = "integer"; tbl.Rows.Add(row); } else { last.Sync(0); using (DataTable tblSub = last._reader.GetSchemaTable()) { foreach (DataRow row in tblSub.Rows) { object[] o = row.ItemArray; DataRow newrow = tbl.Rows.Add(o); newrow[SchemaTableOptionalColumn.IsHidden] = true; newrow[SchemaTableColumn.ColumnOrdinal] = tbl.Rows.Count - 1; } } } } } }
internal void AppendSchemaTable(DataTable tbl) { KeyQuery query = null; for (int i = 0; i < this._keyInfo.Length; i++) { if ((this._keyInfo[i].query == null) || (this._keyInfo[i].query != query)) { query = this._keyInfo[i].query; if (query == null) { DataRow row = tbl.NewRow(); row[SchemaTableColumn.ColumnName] = this._keyInfo[i].columnName; row[SchemaTableColumn.ColumnOrdinal] = tbl.Rows.Count; row[SchemaTableColumn.ColumnSize] = 8; row[SchemaTableColumn.NumericPrecision] = 0xff; row[SchemaTableColumn.NumericScale] = 0xff; row[SchemaTableColumn.ProviderType] = DbType.Int64; row[SchemaTableColumn.IsLong] = false; row[SchemaTableColumn.AllowDBNull] = false; row[SchemaTableOptionalColumn.IsReadOnly] = false; row[SchemaTableOptionalColumn.IsRowVersion] = false; row[SchemaTableColumn.IsUnique] = false; row[SchemaTableColumn.IsKey] = true; row[SchemaTableColumn.DataType] = typeof(long); row[SchemaTableOptionalColumn.IsHidden] = true; row[SchemaTableColumn.BaseColumnName] = this._keyInfo[i].columnName; row[SchemaTableColumn.IsExpression] = false; row[SchemaTableColumn.IsAliased] = false; row[SchemaTableColumn.BaseTableName] = this._keyInfo[i].tableName; row[SchemaTableOptionalColumn.BaseCatalogName] = this._keyInfo[i].databaseName; row[SchemaTableOptionalColumn.IsAutoIncrement] = true; row["DataTypeName"] = "integer"; tbl.Rows.Add(row); } else { query.Sync(0L); using (DataTable table = query._reader.GetSchemaTable()) { foreach (DataRow row2 in table.Rows) { object[] itemArray = row2.ItemArray; DataRow row3 = tbl.Rows.Add(itemArray); row3[SchemaTableOptionalColumn.IsHidden] = true; row3[SchemaTableColumn.ColumnOrdinal] = tbl.Rows.Count - 1; } } } } } }
internal void Sync() { if (!this._isValid) { KeyQuery query = null; for (int i = 0; i < this._keyInfo.Length; i++) { if ((this._keyInfo[i].query == null) || (this._keyInfo[i].query != query)) { query = this._keyInfo[i].query; if (query != null) { query.Sync(this._stmt._sql.GetRowIdForCursor(this._stmt, this._keyInfo[i].cursor)); } } } this._isValid = true; } }
/// <summary> /// Make sure all the subqueries are open and ready and sync'd with the current rowid /// of the table they're supporting /// </summary> internal void Sync() { if (_isValid == true) { return; } KeyQuery last = null; for (int n = 0; n < _keyInfo.Length; n++) { if (_keyInfo[n].query == null || _keyInfo[n].query != last) { last = _keyInfo[n].query; if (last != null) { last.Sync(_stmt._sql.GetRowIdForCursor(_stmt, _keyInfo[n].cursor)); } } } _isValid = true; }
/// <summary> /// This function does all the nasty work at determining what keys need to be returned for /// a given statement. /// </summary> /// <param name="cnn"></param> /// <param name="reader"></param> /// <param name="stmt"></param> internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt) { Dictionary <string, int> catalogs = new Dictionary <string, int>(); Dictionary <string, List <string> > tables = new Dictionary <string, List <string> >(); List <string> list; List <KeyInfo> keys = new List <KeyInfo>(); // Record the statement so we can use it later for sync'ing _stmt = stmt; // Fetch all the attached databases on this connection using (DataTable tbl = cnn.GetSchema("Catalogs")) { foreach (DataRow row in tbl.Rows) { catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture)); } } // Fetch all the unique tables and catalogs used by the current statement using (DataTable schema = reader.GetSchemaTable(false, false)) { foreach (DataRow row in schema.Rows) { // Check if column is backed to a table if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value) { continue; } // Record the unique table so we can look up its keys string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName]; string table = (string)row[SchemaTableColumn.BaseTableName]; if (tables.ContainsKey(catalog) == false) { list = new List <string>(); tables.Add(catalog, list); } else { list = tables[catalog]; } if (list.Contains(table) == false) { list.Add(table); } } // For each catalog and each table, query the indexes for the table. // Find a primary key index if there is one. If not, find a unique index instead foreach (KeyValuePair <string, List <string> > pair in tables) { for (int i = 0; i < pair.Value.Count; i++) { string table = pair.Value[i]; DataRow preferredRow = null; using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table })) { // Loop twice. The first time looking for a primary key index, // the second time looking for a unique index for (int n = 0; n < 2 && preferredRow == null; n++) { foreach (DataRow row in tbl.Rows) { if (n == 0 && (bool)row["PRIMARY_KEY"] == true) { preferredRow = row; break; } else if (n == 1 && (bool)row["UNIQUE"] == true) { preferredRow = row; break; } } } if (preferredRow == null) // Unable to find any suitable index for this table so remove it { pair.Value.RemoveAt(i); i--; } else // We found a usable index, so fetch the necessary table details { using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table })) { // Find the root page of the table in the current statement and get the cursor that's iterating it int database = catalogs[pair.Key]; int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture); int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage); // Now enumerate the members of the index we're going to use using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] })) { KeyQuery query = null; List <string> cols = new List <string>(); for (int x = 0; x < indexColumns.Rows.Count; x++) { bool addKey = true; // If the column in the index already appears in the query, skip it foreach (DataRow row in schema.Rows) { if (row.IsNull(SchemaTableColumn.BaseColumnName)) { continue; } if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] && (string)row[SchemaTableColumn.BaseTableName] == table && (string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key) { indexColumns.Rows.RemoveAt(x); x--; addKey = false; break; } } if (addKey == true) { cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]); } } // If the index is not a rowid alias, record all the columns // needed to make up the unique index and construct a SQL query for it if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table) { // Whatever remains of the columns we need that make up the index that are not // already in the query need to be queried separately, so construct a subquery if (cols.Count > 0) { string[] querycols = new string[cols.Count]; cols.CopyTo(querycols); query = new KeyQuery(cnn, pair.Key, table, querycols); } } // Create a KeyInfo struct for each column of the index for (int x = 0; x < indexColumns.Rows.Count; x++) { string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"]; KeyInfo key = new KeyInfo(); key.rootPage = rootPage; key.cursor = cursor; key.database = database; key.databaseName = pair.Key; key.tableName = table; key.columnName = columnName; key.query = query; key.column = x; keys.Add(key); } } } } } } } } // Now we have all the additional columns we have to return in order to support // CommandBehavior.KeyInfo _keyInfo = new KeyInfo[keys.Count]; keys.CopyTo(_keyInfo); }
private void keyQuery_KeySelected(object sender, KeyEventArgs e) { foreach (KeyQuery query in keyQueries) { if (query != (KeyQuery)sender) { query.Enabled = true; } } KeyQuery q = (KeyQuery)sender; q.Text = e.KeyCode.ToString(); if (q == keyQuery0) { tempSettings.Buttons[0x0] = e.KeyCode; } else if (q == keyQuery1) { tempSettings.Buttons[0x1] = e.KeyCode; } else if (q == keyQuery2) { tempSettings.Buttons[0x2] = e.KeyCode; } else if (q == keyQuery3) { tempSettings.Buttons[0x3] = e.KeyCode; } else if (q == keyQuery4) { tempSettings.Buttons[0x4] = e.KeyCode; } else if (q == keyQuery5) { tempSettings.Buttons[0x5] = e.KeyCode; } else if (q == keyQuery6) { tempSettings.Buttons[0x6] = e.KeyCode; } else if (q == keyQuery7) { tempSettings.Buttons[0x7] = e.KeyCode; } else if (q == keyQuery8) { tempSettings.Buttons[0x8] = e.KeyCode; } else if (q == keyQuery9) { tempSettings.Buttons[0x9] = e.KeyCode; } else if (q == keyQueryA) { tempSettings.Buttons[0xA] = e.KeyCode; } else if (q == keyQueryB) { tempSettings.Buttons[0xB] = e.KeyCode; } else if (q == keyQueryC) { tempSettings.Buttons[0xC] = e.KeyCode; } else if (q == keyQueryD) { tempSettings.Buttons[0xD] = e.KeyCode; } else if (q == keyQueryE) { tempSettings.Buttons[0xE] = e.KeyCode; } else if (q == keyQueryF) { tempSettings.Buttons[0xF] = e.KeyCode; } }
/// <summary> /// This function does all the nasty work at determining what keys need to be returned for /// a given statement. /// </summary> /// <param name="cnn"></param> /// <param name="reader"></param> /// <param name="stmt"></param> internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt) { Dictionary<string, int> catalogs = new Dictionary<string, int>(); Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>(); List<string> list; List<KeyInfo> keys = new List<KeyInfo>(); // Record the statement so we can use it later for sync'ing _stmt = stmt; // Fetch all the attached databases on this connection using (DataTable tbl = cnn.GetSchema("Catalogs")) { foreach (DataRow row in tbl.Rows) { catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture)); } } // Fetch all the unique tables and catalogs used by the current statement using (DataTable schema = reader.GetSchemaTable(false, false)) { foreach (DataRow row in schema.Rows) { // Check if column is backed to a table if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value) continue; // Record the unique table so we can look up its keys string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName]; string table = (string)row[SchemaTableColumn.BaseTableName]; if (tables.ContainsKey(catalog) == false) { list = new List<string>(); tables.Add(catalog, list); } else list = tables[catalog]; if (list.Contains(table) == false) list.Add(table); } // For each catalog and each table, query the indexes for the table. // Find a primary key index if there is one. If not, find a unique index instead foreach (KeyValuePair<string, List<string>> pair in tables) { for (int i = 0; i < pair.Value.Count; i++) { string table = pair.Value[i]; DataRow preferredRow = null; using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table })) { // Loop twice. The first time looking for a primary key index, // the second time looking for a unique index for (int n = 0; n < 2 && preferredRow == null; n++) { foreach (DataRow row in tbl.Rows) { if (n == 0 && (bool)row["PRIMARY_KEY"] == true) { preferredRow = row; break; } else if (n == 1 && (bool)row["UNIQUE"] == true) { preferredRow = row; break; } } } if (preferredRow == null) // Unable to find any suitable index for this table so remove it { pair.Value.RemoveAt(i); i--; } else // We found a usable index, so fetch the necessary table details { using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table })) { // Find the root page of the table in the current statement and get the cursor that's iterating it int database = catalogs[pair.Key]; int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture); int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage); // Now enumerate the members of the index we're going to use using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] })) { KeyQuery query = null; List<string> cols = new List<string>(); for (int x = 0; x < indexColumns.Rows.Count; x++) { bool addKey = true; // If the column in the index already appears in the query, skip it foreach (DataRow row in schema.Rows) { if (row.IsNull(SchemaTableColumn.BaseColumnName)) continue; if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] && (string)row[SchemaTableColumn.BaseTableName] == table && (string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key) { indexColumns.Rows.RemoveAt(x); x--; addKey = false; break; } } if (addKey == true) cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]); } // If the index is not a rowid alias, record all the columns // needed to make up the unique index and construct a SQL query for it if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table) { // Whatever remains of the columns we need that make up the index that are not // already in the query need to be queried separately, so construct a subquery if (cols.Count > 0) { string[] querycols = new string[cols.Count]; cols.CopyTo(querycols); query = new KeyQuery(cnn, pair.Key, table, querycols); } } // Create a KeyInfo struct for each column of the index for (int x = 0; x < indexColumns.Rows.Count; x++) { string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"]; KeyInfo key = new KeyInfo(); key.rootPage = rootPage; key.cursor = cursor; key.database = database; key.databaseName = pair.Key; key.tableName = table; key.columnName = columnName; key.query = query; key.column = x; keys.Add(key); } } } } } } } } // Now we have all the additional columns we have to return in order to support // CommandBehavior.KeyInfo _keyInfo = new KeyInfo[keys.Count]; keys.CopyTo(_keyInfo); }
internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt) { Dictionary <string, int> dictionary = new Dictionary <string, int>(); Dictionary <string, List <string> > dictionary2 = new Dictionary <string, List <string> >(); List <KeyInfo> list2 = new List <KeyInfo>(); this._stmt = stmt; using (DataTable table = cnn.GetSchema("Catalogs")) { foreach (DataRow row in table.Rows) { dictionary.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"])); } } using (DataTable table2 = reader.GetSchemaTable(false, false)) { foreach (DataRow row2 in table2.Rows) { if (row2[SchemaTableOptionalColumn.BaseCatalogName] != DBNull.Value) { List <string> list; string key = (string)row2[SchemaTableOptionalColumn.BaseCatalogName]; string item = (string)row2[SchemaTableColumn.BaseTableName]; if (!dictionary2.ContainsKey(key)) { list = new List <string>(); dictionary2.Add(key, list); } else { list = dictionary2[key]; } if (!list.Contains(item)) { list.Add(item); } } } foreach (KeyValuePair <string, List <string> > pair in dictionary2) { for (int i = 0; i < pair.Value.Count; i++) { string str3 = pair.Value[i]; DataRow row3 = null; string[] restrictionValues = new string[3]; restrictionValues[0] = pair.Key; restrictionValues[2] = str3; using (DataTable table3 = cnn.GetSchema("Indexes", restrictionValues)) { for (int j = 0; (j < 2) && (row3 == null); j++) { foreach (DataRow row4 in table3.Rows) { if ((j == 0) && ((bool)row4["PRIMARY_KEY"])) { row3 = row4; break; } if ((j == 1) && ((bool)row4["UNIQUE"])) { row3 = row4; break; } } } if (row3 == null) { pair.Value.RemoveAt(i); i--; } else { string[] strArray3 = new string[3]; strArray3[0] = pair.Key; strArray3[2] = str3; using (DataTable table4 = cnn.GetSchema("Tables", strArray3)) { int database = dictionary[pair.Key]; int rootPage = Convert.ToInt32(table4.Rows[0]["TABLE_ROOTPAGE"]); int num5 = stmt._sql.GetCursorForTable(stmt, database, rootPage); string[] strArray4 = new string[4]; strArray4[0] = pair.Key; strArray4[2] = str3; strArray4[3] = (string)row3["INDEX_NAME"]; using (DataTable table5 = cnn.GetSchema("IndexColumns", strArray4)) { KeyQuery query = null; List <string> list3 = new List <string>(); for (int k = 0; k < table5.Rows.Count; k++) { bool flag = true; foreach (DataRow row5 in table2.Rows) { if ((!row5.IsNull(SchemaTableColumn.BaseColumnName) && (((string)row5[SchemaTableColumn.BaseColumnName]) == ((string)table5.Rows[k]["COLUMN_NAME"]))) && ((((string)row5[SchemaTableColumn.BaseTableName]) == str3) && (((string)row5[SchemaTableOptionalColumn.BaseCatalogName]) == pair.Key))) { table5.Rows.RemoveAt(k); k--; flag = false; break; } } if (flag) { list3.Add((string)table5.Rows[k]["COLUMN_NAME"]); } } if ((((string)row3["INDEX_NAME"]) != ("sqlite_master_PK_" + str3)) && (list3.Count > 0)) { string[] array = new string[list3.Count]; list3.CopyTo(array); query = new KeyQuery(cnn, pair.Key, str3, array); } for (int m = 0; m < table5.Rows.Count; m++) { string str4 = (string)table5.Rows[m]["COLUMN_NAME"]; KeyInfo info = new KeyInfo { rootPage = rootPage, cursor = num5, database = database, databaseName = pair.Key, tableName = str3, columnName = str4, query = query, column = m }; list2.Add(info); } } } } } } } } this._keyInfo = new KeyInfo[list2.Count]; list2.CopyTo(this._keyInfo); }