/// <summary>
/// Loads the DB schema of the specified DB file
/// </summary>
/// <param name="dbPath">The path to the DB file to load</param>
/// <returns>The DB schema of that file</returns>
public static DbSchema LoadDB(string dbPath)
{
DbSchema schema = new DbSchema();
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
{
DataSource = dbPath,
PageSize = 4096,
UseUTF16Encoding = true
};
using (SQLiteConnection conn = new SQLiteConnection(builder.ConnectionString))
{
conn.Open();
using (SQLiteCommand query = new SQLiteCommand(@"SELECT * FROM SQLITE_MASTER", conn))
{
using (SQLiteDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
string type = (string)reader["type"];
string name = (string)reader["name"];
string tblName = (string)reader["tbl_name"];
// Ignore SQLite internal indexes and tables
if (name.StartsWith("sqlite_"))
{
continue;
}
if (reader["sql"] == DBNull.Value)
{
continue;
}
string sql = (string)reader["sql"];
if (type == "table")
{
schema._tables.Add(tblName, ParseDbTable(ref sql));
}
else if (type == "index")
{
schema._indexes.Add(name, ParseDbIndex(ref sql));
}
else
{
throw DbUpgradeException.SchemaIsNotSupported();
}
} // while
} // using
} // using
} // using
return(schema);
}
/// <summary>
/// Parse the specified SQL statement as a PRIMARY KEYS() section.
/// </summary>
/// <param name="sql">The SQL statement to parse</param>
/// <returns>The list of primary keys (if recognized as a PRIMARY KEYS
/// statement) or null if not recognized.</returns>
private static List <string> ParsePrimaryKeys(ref string sql)
{
if (!ScanToken(ref sql, "PRIMARY KEY"))
{
return(null);
}
if (!ScanToken(ref sql, "("))
{
return(null);
}
string keys = null;
for (int i = 0; i < sql.Length; i++)
{
if (sql[i] == ')')
{
keys = sql.Substring(0, i);
if (i < sql.Length - 1)
{
sql = sql.Substring(i + 1);
}
else
{
sql = string.Empty;
}
break;
}
} // for
if (keys != null)
{
string[] parts = keys.Split(',');
List <string> res = new List <string>();
foreach (string p in parts)
{
string key = p.Trim().Trim('[', ']', '\'', '`', '\"');
res.Add(key);
} // foreach
return(res);
}
else
{
throw DbUpgradeException.SchemaIsNotSupported();
}
}
/// <summary>
/// Parse the header part of a table DDL (CREATE TABLE 'name')
/// </summary>
/// <param name="sql">The SQL string to parse</param>
/// <returns>The name of the parsed table.</returns>
public static string ParseTableName(ref string sql)
{
Match m = _tableHeader.Match(sql);
if (m.Success)
{
int index = m.Index + m.Length;
string tableName = ParsePotentiallyDelimitedToken(sql, ref index);
if (tableName != null)
{
sql = sql.Substring(index);
return(tableName);
}
}
throw DbUpgradeException.SchemaIsNotSupported();
}
private static int ParsePotentialTypeSize(string sql, out int precision, ref int index)
{
int size = -1;
precision = -1;
int saved = index;
// Skip white space
for (; index < sql.Length; index++)
{
if (!Char.IsWhiteSpace(sql[index]))
{
break;
}
}
if (index == sql.Length)
{
index = saved;
return(-1);
}
if (sql[index] == '(')
{
// Parse the size component
StringBuilder digit = new StringBuilder();
for (index++; index < sql.Length; index++)
{
if (!Char.IsWhiteSpace(sql[index]))
{
break;
}
}
if (index == sql.Length || !Char.IsDigit(sql[index]))
{
index = saved;
return(-1);
}
for (; index < sql.Length; index++)
{
if (!Char.IsDigit(sql[index]))
{
break;
}
else
{
digit.Append(sql[index]);
}
}
if (index == sql.Length)
{
index = saved;
return(-1);
}
string tmpsize = digit.ToString();
// Skip white space
for (; index < sql.Length; index++)
{
if (!Char.IsWhiteSpace(sql[index]))
{
break;
}
}
if (index == sql.Length)
{
index = saved;
return(-1);
}
if (sql[index] == ',')
{
// The size has a precision component that we need to parse also.
// Skip white space
for (index++; index < sql.Length; index++)
{
if (!Char.IsWhiteSpace(sql[index]))
{
break;
}
}
if (index == sql.Length)
{
index = saved;
return(-1);
}
// Read the precision component
digit = new StringBuilder();
for (; index < sql.Length; index++)
{
if (!Char.IsDigit(sql[index]))
{
break;
}
else
{
digit.Append(sql[index]);
}
}
if (index == sql.Length)
{
index = saved;
return(-1);
}
string tmpprec = digit.ToString();
if (!int.TryParse(tmpprec, out precision))
{
throw DbUpgradeException.SchemaIsNotSupported();
}
}
for (; index < sql.Length; index++)
{
if (sql[index] == ')')
{
break;
}
}
if (index == sql.Length)
{
index = saved;
return(-1);
}
index++;
if (!int.TryParse(tmpsize, out size))
{
throw DbUpgradeException.SchemaIsNotSupported();
}
return(size);
} // if
else
{
index = saved;
return(-1);
}
}
/// <summary>
/// Parse a single table column row.
/// </summary>
/// <param name="sql">The SQL statement</param>
/// <returns>The DbColumn instance for the column.</returns>
private static DbColumn ParseColumn(ref string sql)
{
// In case this is a PRIMARY KEY constraint - return null immediatly
if (sql.TrimStart(' ', '\t', '\n', '\r').StartsWith("PRIMARY KEY"))
{
return(null);
}
string columnName = null;
DbType columnType = DbType.Int32;
int columnSize = -1;
int columnPrecision = -1;
DbColumn res = new DbColumn();
if (ParseColumnNameType(ref sql, ref columnName, ref columnType, ref columnSize, ref columnPrecision))
{
res.ColumnName = columnName;
res.ColumnSize = columnSize;
res.ColumnType = columnType;
res.ColumnPrecision = columnPrecision;
int index = FindClosingRightParens(sql);
if (index == -1)
{
throw DbUpgradeException.SchemaIsNotSupported();
}
int index2 = sql.IndexOf(",");
if (index2 != -1 && index2 < index)
{
index = index2;
}
string rest = sql.Substring(0, index);
sql = sql.Substring(index);
// TRUE by default
res.IsNullable = true;
// Parse the list of column constraints
Match m = _columnConstraints.Match(rest);
while (m.Success)
{
if (m.Groups[2].Success)
{
if (m.Groups[2].Value == "NOT NULL")
{
res.IsNullable = false;
}
else
{
res.IsNullable = true;
}
}
else if (m.Groups[1].Success)
{
res.IsPrimaryKey = true;
}
else if (m.Groups[5].Success)
{
res.DefaultValue = null;
res.DefaultFunction = m.Groups[5].Value;
}
else if (m.Groups[7].Success)
{
res.DefaultValue = m.Groups[7].Value;
res.DefaultFunction = null;
}
else if (m.Groups[9].Success)
{
res.DefaultValue = double.Parse(m.Groups[9].Value);
res.DefaultFunction = null;
}
else if (m.Groups[11].Success)
{
res.DefaultValue = int.Parse(m.Groups[11].Value);
res.DefaultFunction = null;
}
else if (m.Groups[13].Success)
{
res.Collation = m.Groups[13].Value;
}
else if (m.Groups[14].Success)
{
res.IsUnique = true;
}
else if (m.Groups[15].Success)
{
res.IsAutoIncrement = true;
}
else
{
throw DbUpgradeException.InternalSoftwareError();
}
rest = rest.Substring(m.Index + m.Length);
m = _columnConstraints.Match(rest);
} // while
return(res);
} // if
else
{
// This is not a column - maybe it is a primary key declaration
return(null);
} // else
}
/// <summary>
/// Parse the table columns section of the CREATE TABLE DDL statement.
/// </summary>
/// <param name="sql">The SQL statement to parse</param>
/// <param name="tableName">Name of the table.</param>
/// <returns>
/// The list of DbColumn objects that represent the meta
/// data about all table columns.
/// </returns>
public static List <DbColumn> ParseTableColumns(ref string sql, string tableName)
{
// Skip '('
if (!ScanToken(ref sql, "("))
{
throw DbUpgradeException.SchemaIsNotSupported();
}
List <string> primaryKeys = new List <string>();
List <DbColumn> res = new List <DbColumn>();
do
{
if (sql.Trim().StartsWith(")"))
{
break;
}
DbColumn col = ParseColumn(ref sql);
if (col != null)
{
res.Add(col);
if (!ScanToken(ref sql, ","))
{
if (ScanToken(ref sql, ")"))
{
break;
}
else
{
DbUpgradeException.SchemaIsNotSupported();
}
} // if
}
else
{
// Try to parse as a PRIMARY KEY section
List <string> keys = ParsePrimaryKeys(ref sql);
if (keys != null)
{
primaryKeys.AddRange(keys);
}
else
{
throw DbUpgradeException.SchemaIsNotSupported();
}
} // else
} while (true);
// Apply any primary keys found
foreach (string pkey in primaryKeys)
{
bool found = false;
foreach (DbColumn col in res)
{
if (col.ColumnName == pkey)
{
col.IsPrimaryKey = true;
found = true;
break;
}
} // foreach
if (!found)
{
throw DbUpgradeException.InvalidPrimaryKeySection(tableName);
}
} // foreach
return(res);
}
/// <summary>
/// Parse the specified CREATE INDEX statement and returned the
/// index representation as a DbIndex instance.
/// </summary>
/// <param name="sql">The CREATE INDEX sql statement</param>
/// <returns>The DbIndex representation of the table.</returns>
private static DbIndex ParseDbIndex(ref string sql)
{
DbIndex index = new DbIndex();
Match m = _indexHeader.Match(sql);
if (m.Success)
{
if (m.Groups[1].Success)
{
index.IsUnique = true;
}
int start = m.Index + m.Length;
index.IndexName = ParsePotentiallyDelimitedToken(sql, ref start);
if (index.IndexName == null)
{
throw DbUpgradeException.SchemaIsNotSupported();
}
// Search for occurence of "ON"
int offset = sql.IndexOf("ON", start);
if (offset == -1)
{
throw DbUpgradeException.SchemaIsNotSupported();
}
start = offset + 2;
index.TableName = ParsePotentiallyDelimitedToken(sql, ref start);
if (index.TableName == null)
{
throw DbUpgradeException.SchemaIsNotSupported();
}
sql = sql.Substring(start);
if (!ScanToken(ref sql, "("))
{
throw DbUpgradeException.SchemaIsNotSupported();
}
string cols = null;
for (int i = 0; i < sql.Length; i++)
{
if (sql[i] == ')')
{
cols = sql.Substring(0, i);
if (i < sql.Length - 1)
{
sql = sql.Substring(i + 1);
}
else
{
sql = string.Empty;
}
break;
}
} // for
if (cols == null)
{
throw DbUpgradeException.SchemaIsNotSupported();
}
string[] parts = cols.Split(',');
Dictionary <string, DbSortOrder> icols =
new Dictionary <string, DbSortOrder>();
foreach (string p in parts)
{
string cn = p.Trim();
string[] cparts = cn.Split(' ', '\t');
DbSortOrder order = DbSortOrder.Ascending;
if (cparts.Length == 2)
{
if (cparts[1].ToUpper() == "DESC")
{
order = DbSortOrder.Descending;
}
else
{
throw DbUpgradeException.SchemaIsNotSupported();
}
}
icols.Add(cparts[0].Trim().Trim('[', ']', '\'', '`', '\"'), order);
}
if (icols.Count == 0)
{
throw DbUpgradeException.SchemaIsNotSupported();
}
index.IndexColumns = icols;
return(index);
}
else
{
throw DbUpgradeException.SchemaIsNotSupported();
}
}