public bool VerifyDb()
{
using (var conn = new SqliteConnection(ConnectionString))
{
conn.Open();
var schema = conn.GetSchema("Tables");
Console.WriteLine("Tables = " + schema.Rows.Count);
conn.Close();
return schema.Rows.Count == 2;
}
}
/// <summary>
/// Clones the settings and connection string from an existing connection. If the existing connection is already open, this
/// function will open its own connection, enumerate any attached databases of the original connection, and automatically
/// attach to them.
/// </summary>
/// <param name="connection"></param>
public SqliteConnection(SqliteConnection connection)
: this(connection.ConnectionString)
{
string str;
if (connection.State == ConnectionState.Open)
{
Open();
// Reattach all attached databases from the existing connection
using (DataTable tbl = connection.GetSchema("Catalogs"))
{
foreach (DataRow row in tbl.Rows)
{
str = row[0].ToString();
if (String.Compare(str, "main", true, CultureInfo.InvariantCulture) != 0
&& String.Compare(str, "temp", true, CultureInfo.InvariantCulture) != 0)
{
using (SqliteCommand cmd = CreateCommand())
{
cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "ATTACH DATABASE '{0}' AS [{1}]", row[1], row[0]);
cmd.ExecuteNonQuery();
}
}
}
}
}
}
public static bool DatabaseHelperTableExists(string tableName)
{
SqliteConnection connection = new SqliteConnection(GetConnectionString());
string[] restrictions = new string[4];
restrictions[2] = tableName;
connection.Open();
DataTable table = connection.GetSchema("Tables", restrictions);
connection.Close();
if (table != null)
{
return (table.Rows.Count > 0);
}
return false;
}
/// <summary>
/// 获取所有数据类型信息
/// </summary>
/// <returns></returns>
public DataTable GetSchema()
{
using (SqliteConnection connection = new SqliteConnection(dbConnectionString))
{
connection.Open();
DataTable dt = connection.GetSchema("TABLES");
return dt;
}
}
public Dictionary<string, Table> Tables()
{
Dictionary<string, Table> tables = new Dictionary<string, Table>();
createDatabase(AppSettings.Default.ConnectionString);
using (SqliteConnection conn = new SqliteConnection(AppSettings.Default.ConnectionString)) {
conn.Open();
DataTable tabs = conn.GetSchema("Tables");
DataTable cols = conn.GetSchema("Columns");
DataTable fkeyCols = conn.GetSchema("ForeignKeys");
DataTable indexes = conn.GetSchema("Indexes");
DataTable indexCols = conn.GetSchema("IndexColumns");
DataTable views = conn.GetSchema("Views");
DataTable viewCols = conn.GetSchema("ViewColumns");
foreach(DataRow table in tabs.Rows) {
string name = table["TABLE_NAME"].ToString();
string filter = "TABLE_NAME = " + Quote(name);
Field[] fields = cols.Select(filter, "ORDINAL_POSITION")
.Select(c => new Field(c["COLUMN_NAME"].ToString(), typeFor(c["DATA_TYPE"].ToString()),
lengthFromColumn(c), c["IS_NULLABLE"].ToString() == "True", c["AUTOINCREMENT"].ToString() == "True",
defaultFromColumn(c))).ToArray();
List<Index> tableIndexes = new List<Index>();
foreach (DataRow ind in indexes.Select(filter + " AND PRIMARY_KEY = 'True'")) {
string indexName = ind["INDEX_NAME"].ToString();
tableIndexes.Add(new Index("PRIMARY",
indexCols.Select(filter + " AND INDEX_NAME = " + Quote(indexName), "ORDINAL_POSITION")
.Select(r => fields.First(f => f.Name == r["COLUMN_NAME"].ToString())).ToArray()));
}
foreach (DataRow ind in indexes.Select(filter + " AND PRIMARY_KEY = 'False' AND UNIQUE = 'True'")) {
string indexName = ind["INDEX_NAME"].ToString();
tableIndexes.Add(new Index(indexName,
indexCols.Select(filter + " AND INDEX_NAME = " + Quote(indexName), "ORDINAL_POSITION")
.Select(r => fields.First(f => f.Name == r["COLUMN_NAME"].ToString())).ToArray()));
}
tables[name] = new Table(name, fields, tableIndexes.ToArray());
}
foreach (DataRow fk in fkeyCols.Rows) {
Table detail = tables[fk["TABLE_NAME"].ToString()];
Table master = tables[fk["FKEY_TO_TABLE"].ToString()];
Field masterField = master.FieldFor(fk["FKEY_TO_COLUMN"].ToString());
detail.FieldFor(fk["FKEY_FROM_COLUMN"].ToString()).ForeignKey = new ForeignKey(master, masterField);
}
foreach (DataRow table in views.Select()) {
string name = table["TABLE_NAME"].ToString();
string filter = "VIEW_NAME = " + Quote(name);
Field[] fields = viewCols.Select(filter, "ORDINAL_POSITION")
.Select(c => new Field(c["VIEW_COLUMN_NAME"].ToString(), typeFor(c["DATA_TYPE"].ToString()),
lengthFromColumn(c), c["IS_NULLABLE"].ToString() == "True", false,
defaultFromColumn(c))).ToArray();
Table updateTable = null;
tables.TryGetValue(Regex.Replace(name, "^.*_", ""), out updateTable);
tables[name] = new View(name, fields, new Index[] { new Index("PRIMARY", fields[0]) },
table["VIEW_DEFINITION"].ToString(), updateTable);
}
}
return tables;
}
/// <summary>
/// 获取所有数据类型信息
/// </summary>
/// <returns></returns>
public static DataTable GetSchema(string connectionString)
{
using (var connection = new SqliteConnection(connectionString))
{
connection.Open();
DataTable dt = connection.GetSchema("TABLES");
return dt;
}
}
/// <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"]));
}
}
// 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"]);
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);
}
/// <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"]));
}
}
// 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"]);
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);
}
