private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn)
{
DataTable tbl = new DataTable("IndexColumns");
DataRow row;
List<KeyValuePair<int, string>> primaryKeys = new List<KeyValuePair<int, string>>();
bool maybeRowId;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
tbl.Columns.Add("TABLE_CATALOG", typeof(string));
tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
tbl.Columns.Add("TABLE_NAME", typeof(string));
tbl.Columns.Add("COLUMN_NAME", typeof(string));
tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
tbl.Columns.Add("INDEX_NAME", typeof(string));
tbl.Columns.Add("COLLATION_NAME", typeof(string));
tbl.Columns.Add("SORT_MODE", typeof(string));
tbl.Columns.Add("CONFLICT_OPTION", typeof(int));
if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";
string master = (String.Compare(strCatalog, "temp", true, CultureInfo.InvariantCulture) == 0) ? _tempmasterdb : _masterdb;
tbl.BeginLoadData();
using (SqliteCommand cmdTables = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}] WHERE [type] LIKE 'table'", strCatalog, master), this))
using (SqliteDataReader rdTables = cmdTables.ExecuteReader())
{
while (rdTables.Read())
{
maybeRowId = false;
primaryKeys.Clear();
if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
{
try
{
using (SqliteCommand cmdTable = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].table_info([{1}])", strCatalog, rdTables.GetString(2)), this))
using (SqliteDataReader rdTable = cmdTable.ExecuteReader())
{
while (rdTable.Read())
{
if (rdTable.GetInt32(5) == 1) // is a primary key
{
primaryKeys.Add(new KeyValuePair<int, string>(rdTable.GetInt32(0), rdTable.GetString(1)));
// Is an integer -- could be a rowid if no other primary keys exist in the table
if (String.Compare(rdTable.GetString(2), "INTEGER", true, CultureInfo.InvariantCulture) == 0)
maybeRowId = true;
}
}
}
}
catch (SqliteException)
{
}
// This is a rowid row
if (primaryKeys.Count == 1 && maybeRowId == true)
{
row = tbl.NewRow();
row["CONSTRAINT_CATALOG"] = strCatalog;
row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "{1}_PK_{0}", rdTables.GetString(2), master);
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = rdTables.GetString(2);
row["COLUMN_NAME"] = primaryKeys[0].Value;
row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
row["ORDINAL_POSITION"] = 0; // primaryKeys[0].Key;
row["COLLATION_NAME"] = "BINARY";
row["SORT_MODE"] = "ASC";
row["CONFLICT_OPTION"] = 2;
if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, (string)row["INDEX_NAME"], true, CultureInfo.InvariantCulture) == 0)
tbl.Rows.Add(row);
}
using (SqliteCommand cmdIndexes = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{2}] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2).Replace("'", "''"), master), this))
using (SqliteDataReader rdIndexes = cmdIndexes.ExecuteReader())
{
while (rdIndexes.Read())
{
int ordinal = 0;
if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.InvariantCulture) == 0)
{
try
{
using (SqliteCommand cmdIndex = new SqliteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this))
using (SqliteDataReader rdIndex = cmdIndex.ExecuteReader())
{
while (rdIndex.Read())
{
row = tbl.NewRow();
row["CONSTRAINT_CATALOG"] = strCatalog;
row["CONSTRAINT_NAME"] = rdIndexes.GetString(1);
row["TABLE_CATALOG"] = strCatalog;
row["TABLE_NAME"] = rdIndexes.GetString(2);
row["COLUMN_NAME"] = rdIndex.GetString(2);
row["INDEX_NAME"] = rdIndexes.GetString(1);
row["ORDINAL_POSITION"] = ordinal; // rdIndex.GetInt32(1);
string collationSequence;
int sortMode;
int onError;
_sql.GetIndexColumnExtendedInfo(strCatalog, rdIndexes.GetString(1), rdIndex.GetString(2), out sortMode, out onError, out collationSequence);
if (String.IsNullOrEmpty(collationSequence) == false)
row["COLLATION_NAME"] = collationSequence;
row["SORT_MODE"] = (sortMode == 0) ? "ASC" : "DESC";
row["CONFLICT_OPTION"] = onError;
ordinal++;
if (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
tbl.Rows.Add(row);
}
}
}
catch (SqliteException)
{
}
}
}
}
}
}
}
tbl.EndLoadData();
tbl.AcceptChanges();
return tbl;
}