public static void Compare(SqlTableDefinition definition, ExpectedTable expectedTable)
{
Assert.Equal(expectedTable.Name, definition.TableName);
Assert.Equal(expectedTable.Columns.Count, definition.Columns.Count);
if (expectedTable.Columns.All(s => !s.IsRowId))
{
Assert.Null(definition.RowIdColumn);
}
for (int i = 0; i < expectedTable.Columns.Count; i++)
{
SqlTableColumn actual = definition.Columns.ElementAt(i);
ExpectedColumn expected = expectedTable.Columns[i];
Assert.Equal(expected.ClrType, actual.DetectedType);
Assert.Equal(expected.Name, actual.Name);
Assert.Equal(expected.SqlType, actual.TypeName);
Assert.Equal(expected.IsPrimaryKey, actual.IsPartOfPrimaryKey);
if (expected.IsRowId)
{
Assert.Same(actual, definition.RowIdColumn);
}
}
}
private static int RunDescribe(DescribeCommand opts)
{
if (!TryReadFile(opts.DatabaseFile, out Stream stream, out Sqlite3Database db,
out List <Sqlite3SchemaRow> tables))
{
return(1);
}
Console.WriteLine($"Database: pagesize {db.Header.PageSize:N0}B, encoding {db.Header.TextEncoding}");
Console.WriteLine($" size {db.Header.DatabaseSizeInPages:N0} pages/{db.Header.DatabaseSizeInPages * db.Header.PageSize:N0}B - actual size: {stream.Length:N0}B");
Console.WriteLine();
foreach (Sqlite3SchemaRow row in tables)
{
if (row.Type != "table")
{
continue;
}
Console.WriteLine($"{row.Type} {row.Name}");
Console.WriteLine($" Schema: {InlineSql(row.Sql)}");
Console.WriteLine($" Root Page: {row.RootPage}");
try
{
SqlTableDefinition definition = row.GetTableDefinition();
Console.WriteLine($" Columns ({definition.Columns.Count})");
foreach (SqlTableColumn column in definition.Columns)
{
Console.WriteLine($" {column.Name} ({column.TypeName}) => {column.DetectedType.Name}");
}
}
catch (Exception)
{
Console.WriteLine(" Error: Unable to parse SQL");
}
List <Sqlite3SchemaRow> indexes = tables.Where(s => s.Type == "index" && s.TableName == row.TableName).ToList();
Console.WriteLine($" Indexes ({indexes.Count})");
foreach (Sqlite3SchemaRow index in indexes)
{
Console.WriteLine($" {index.Name}");
Console.WriteLine($" Schema: {InlineSql(index.Sql)}");
}
if (!opts.DoSizeStatistics)
{
Console.WriteLine("Unable to do statistics");
}
Console.WriteLine();
}
return(0);
}
private void AddConstraints(SqlTableDefinition createTableDefinition)
{
foreach (var constraint in createTableDefinition.Constraints)
{
switch (constraint.Type)
{
case SqlConstraintType.PrimaryKey: AddPrimaryConstraint(constraint); break;
case SqlConstraintType.ForeignKey: AddForeignKeyConstraint((SqlForeignKeyConstraint)constraint); break;
}
}
}
public static void AppendActual(StreamWriter sw, string sql, SqlTableDefinition definition)
{
sw.WriteLine(sql);
sw.WriteLine("\t" + definition.TableName);
foreach (var sqlTableColumn in definition.Columns)
{
sw.Write("\t");
sw.Write(sqlTableColumn.Name);
sw.Write("\t");
switch (sqlTableColumn.DetectedType.Name)
{
case "Int64":
sw.Write("INTEGER");
break;
case "Byte[]":
sw.Write("BYTES");
break;
case "String":
sw.Write("STRING");
break;
case "Double":
sw.Write("DOUBLE");
break;
default:
throw new Exception();
}
sw.Write("\t");
sw.Write(sqlTableColumn.TypeName);
sw.Write("\t");
if (sqlTableColumn.IsPartOfPrimaryKey)
{
sw.Write("PRIMARY");
}
if (definition.RowIdColumn.Name == sqlTableColumn.Name)
{
sw.Write(" ROWID");
}
sw.WriteLine();
}
sw.WriteLine();
}
private void AddColumns(SqlTableDefinition tableDefinition, MemoryDatabase database)
{
foreach (var columnDefinition in tableDefinition.ColumnDefinitions)
{
Column column;
if (columnDefinition is SqlComputedColumnDefinition computedColumn)
{
var expression = computedColumn.Expression as SqlBuiltinScalarFunctionCallExpression;
var dataType = GuessDataType(expression, null, database);
column = new Column(_Table, columnDefinition.Name.Value, _Table.Columns.Count, expression, dataType);
}
else
{
column = new Column(_Table, columnDefinition.Name.Value, columnDefinition.DataType, database.UserDataTypes, _Table.Columns.Count);
}
AddColumnConstrains(columnDefinition, column);
_Table.Columns.Add(column);
}
}
public void ReadCreateStatementsFromDbTests(string dbFile, SqlTestData data)
{
Assembly assembly = typeof(SqlCreateStatements).Assembly;
using (MemoryStream ms = new MemoryStream())
{
using (Stream fs = assembly.GetManifestResourceStream(dbFile))
using (GZipStream gz = new GZipStream(fs, CompressionMode.Decompress))
{
gz.CopyTo(ms);
ms.Seek(0, SeekOrigin.Begin);
}
// Ensure that the DB matches the expectations
using (Sqlite3Database db = new Sqlite3Database(ms))
{
List <Sqlite3SchemaRow> dbTables = db.GetTables().Where(s => !s.TableName.StartsWith("sqlite_", StringComparison.Ordinal)).Where(s => s.Type == "table").ToList();
Assert.Equal(data.Statements.Count, dbTables.Count);
List <string> dbTableNames = dbTables.Select(s => s.TableName).ToList();
List <string> expectedTabledNames = data.Statements.Select(s => s.Name).ToList();
Assert.All(dbTableNames, x => Assert.Contains(x, expectedTabledNames));
Assert.All(expectedTabledNames, x => Assert.Contains(x, dbTableNames));
Assert.All(dbTables, tbl =>
{
SqlTestData.ExpectedTable expected = data.Statements.First(s => s.Name == tbl.TableName);
SqlTableDefinition definition = tbl.GetTableDefinition();
SqlTestData.Compare(definition, expected);
});
}
}
}
public override void Visit(SqlTableDefinition codeObject)
{
}
public SqlColumnDefinition(SqlTableDefinition owner, string name, SqlColumnType type)
{
Owner = owner;
Name = name;
Type = type;
}
public DbTableQuery(DbSchema schema, SqlTableDefinition def) : base(schema, null, def.ReflectedType, def.Columns)
{
_query = new SqlQuerySelect(this);
}
