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