/// <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);
}
