// Inserted identity is returned by extra select; command postprocessor IdentityReader gets it from DataReader
// and puts it into EntityRecord
private void AppendIdentityReturn(SqlStatement sql, DbTableInfo table)
{
sql.Append(_myDialect.SqlSelectIdentity); // returned value is decimal!
sql.Append(SqlTerms.NewLine);
sql.ExecutionType = DbExecutionType.Reader;
sql.ResultProcessor = this._identityReader;
}
public void GenerateDeleteTest1()
{
var gen = new SqlBuilder();
var type = typeof(TestModel);
var tableInfo = new DbTableInfo(type).SetFields(
new DbFieldInfo("IntProperty", "IntField"),
new DbFieldInfo("NotField").SetNotDbField());
try
{
var test1 = gen.GetDelete <TestModel>(tableInfo);
Assert.Fail();
}
catch (Exception e)
{
Assert.AreEqual("can't find primary key when generate delete command!", e.Message);
}
try
{
var test2 = gen.GetDelete <TestModel>(null);
Assert.Fail();
}
catch (Exception e)
{
Assert.AreEqual("can't find primary key when generate delete command!", e.Message);
}
}
internal TableDescription(DbTableInfo dbTableInfo, string schema, string name, IEnumerable <ColumnDescription> columns)
{
this.TableInfo = dbTableInfo;
this.Name = name;
this.Schema = schema;
this.Columns = columns.ToList().AsReadOnly();
}
public virtual bool ViewsMatch(DbTableInfo oldView, DbTableInfo newView)
{
if (oldView.IsMaterializedView != newView.IsMaterializedView)
{
return(false);
}
var oldS = oldView.ViewSql;
var newS = newView.ViewSql;
if (oldS == null || newS == null)
{
return(false);
}
if (oldS == newS)
{
return(true);
}
oldS = NormalizeViewScript(oldS);
newS = NormalizeViewScript(newS);
if (oldS == newS)
{
return(true);
}
return(false);
}
public RazorModel(IFreeSql fsql, TaskBuild task, List <DbTableInfo> tables, DbTableInfo table)
{
this.fsql = fsql;
this.task = task;
this.tables = tables;
this.table = table;
}
private DataTable ScrambleDataSet(DbTableInfo destination, DataSet dataSet)
{
var scrambledResult = dataSet.Tables[0].Copy();
scrambledResult.Clear();
var scrambleColumns = destination.Columns.Where(c => !c.IsGroupColumn).ToList();
if (destination.Columns.Any(c => c.IsGroupColumn))
{
var groupColumns = destination.Columns.Where(c => c.IsGroupColumn).ToList();
var distinctGroups = GetColumns(dataSet.Tables[0], groupColumns)
.AsEnumerable()
.Distinct(new DataRowComparer());
foreach (var group in distinctGroups)
{
var filter = string.Empty;
for (int i = 0; i < groupColumns.Count(); i++)
{
filter += $"AND {groupColumns[i].Name}={ParseForDataSetFilter(group[i])} ";
}
var filteredDataTable = dataSet.Tables[0].Select(filter.Substring(3)).CopyToDataTable();
ScrambleColumnValues(scrambleColumns, filteredDataTable, scrambledResult);
}
}
else
{
ScrambleColumnValues(scrambleColumns, dataSet.Tables[0], scrambledResult);
}
return(scrambledResult);
}
public void GenerateUpdateTest()
{
var gen = new SqlBuilder();
var type = typeof(TestModel);
var tableInfo = new DbTableInfo(type).SetFields(
new DbFieldInfo("ID").SetAutoIncrement().SetPrimaryKey(),
new DbFieldInfo("IntProperty", "IntField"),
new DbFieldInfo("NotField").SetNotDbField());
var test1 = gen.GetUpdate <TestModel>(tableInfo);
var test2 = gen.GetUpdate <TestModel>(tableInfo, "StringProperty=@StringProperty", "ID>9");
var test3 = gen.GetUpdate <TestModel>(tableInfo, "IntProperty");
var expected1 = "UPDATE TestModel SET StringProperty=@StringProperty,IntField=@IntProperty WHERE ID=@ID";
var expected2 = "UPDATE TestModel SET StringProperty=@StringProperty WHERE ID>9";
var expected3 = "UPDATE TestModel SET IntField=@IntProperty WHERE ID=@ID";
Assert.AreEqual(expected1, test1);
Assert.AreEqual(expected2, test2);
Assert.AreEqual(expected3, test3);
Assert.AreEqual(3, gen.CacheCount);
var test4 = gen.GetUpdate <TestModel>(tableInfo);
Assert.ReferenceEquals(test1, test4);
Assert.AreEqual(3, gen.CacheCount);
}
public void GenerateSelectTest()
{
var sqlGen = new SqlBuilder();
var type = typeof(TestModel);
var tableInfo = new DbTableInfo(type).SetFields(
new DbFieldInfo("IntProperty", "IntField"),
new DbFieldInfo("NotField").SetNotDbField());
var test1 = sqlGen.GetSelect <TestModel>(tableInfo);
var test2 = sqlGen.GetSelect <TestModel>(tableInfo, "");
var test3 = sqlGen.GetSelect <TestModel>(tableInfo, "StringProperty", "IntField>34 AND StringProperty=@StringProperty ORDER BY IntField");
var expected1 = "SELECT * FROM TestModel";
var expected2 = "SELECT ID,StringProperty,IntField AS IntProperty FROM TestModel";
var expected3 = "SELECT StringProperty FROM TestModel WHERE IntField>34 AND StringProperty=@StringProperty ORDER BY IntField";
Assert.AreEqual(expected1, test1);
Assert.AreEqual(expected2, test2);
Assert.AreEqual(expected3, test3);
Assert.AreEqual(3, sqlGen.CacheCount);
var test4 = sqlGen.GetSelect <TestModel>(tableInfo, "StringProperty", "IntField>34 AND StringProperty=@StringProperty ORDER BY IntField");
Assert.ReferenceEquals(test3, test4);
Assert.AreEqual(3, sqlGen.CacheCount);
}
}//method
public virtual void BuildTableAddSql(DbObjectChange change, DbTableInfo table)
{
const string SqlTemplate = @"CREATE TABLE {0} (" + "\r\n {1} \r\n); ";
var specs = table.Columns.Select(c => GetColumnSpec(c));
var columnSpecs = string.Join("," + Environment.NewLine, specs);
change.AddScript(DbScriptType.TableAdd, SqlTemplate, table.FullName, columnSpecs);
}//method
private bool ViewsMatch(DbTableInfo existing, DbTableInfo newView)
{
if (existing.IsMaterializedView != newView.IsMaterializedView)
{
return(false);
}
return(newView.ViewHash == existing.ViewHash);
}
public virtual void BuildViewAddSql(DbObjectChange change, DbTableInfo view)
{
const string sqlTemplate =
@"CREATE VIEW {0} AS
{1}"; //no ';' at the end, SQL must have it already
change.AddScript(DbScriptType.ViewAdd, sqlTemplate, view.FullName, view.ViewSql);
}
void InitTableInfo()
{
dbTableInfos = _node.Parent.Nodes.Cast <Node>()
.Select(a => a.Tag as DbTableInfo).ToList();
//G.GetTablesByDatabase(_node.Parent.DataKey, _node.Parent.Text);
dbTableInfo = dbTableInfos.FirstOrDefault(a => a.Name == _node.Text);
dataGridViewX1.DataSource = dbTableInfo?.Columns;
}
public virtual bool TableExists(DbSettings settings, DbTableInfo table)
{
var dialect = settings.Driver.SqlDialect;
var sql = dialect.GetTableExistsSql(table);
var result = settings.Driver.ExecuteRawSelect(settings.SchemaManagementConnectionString, sql);
return(result.Rows.Count > 0);
}
public virtual InfoTable SelectAllFrom(DbSettings settings, DbTableInfo table)
{
var dialect = settings.Driver.SqlDialect;
var sql = dialect.GetTableSelectAllSql(table);
var result = settings.Driver.ExecuteRawSelect(settings.SchemaManagementConnectionString, sql);
return(result);
}
// Inject hash value, some servers preserve it, some don't
public virtual void BuildViewAddSql(DbObjectChange change, DbTableInfo view)
{
var viewAddSql =
$@"CREATE VIEW {view.FullName} AS
{view.ViewSql}";
change.AddScript(DbScriptType.ViewAdd, viewAddSql);
}
// Used by DbInfo module
public virtual string GetTableExistsSql(DbTableInfo table)
{
var template = @"
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='{0}' AND TABLE_SCHEMA = '{1}'";
var sql = string.Format(template, table.TableName, table.Schema);
return(sql);
}
public DbTableChangeGroup(DbTableInfo oldTable, DbTableInfo newTable)
{
OldTable = oldTable;
NewTable = newTable;
var tbl = NewTable ?? OldTable;
TableName = tbl.FullName;
}
public TableExpression(DbTableInfo tableInfo, Type type, string name, LockOptions lockOptions = LockOptions.None)
: base(SqlExpressionType.Table, type)
{
Vita.Common.Util.Check(type != null, "TableExpression (name: {0}) - type may not be null.", name);
TableInfo = tableInfo;
Name = name;
LockOptions = lockOptions;
}
// SQLite docs do NOT recommend using AUTOINCREMENT explicitly; for any new row an uninitialized PK column
// will be auto set to RowID, so it is Auto-inc automatically
// http://sqlite.org/autoinc.html
private void CreateAutoIncPrimaryKey(DbTableInfo table, IList <DbColumnInfo> columns)
{
table.PrimaryKey = new DbKeyInfo("PK_" + table.TableName, table, KeyType.PrimaryKey);
foreach (var col in columns)
{
col.Flags |= DbColumnFlags.PrimaryKey | DbColumnFlags.Identity;
table.PrimaryKey.KeyColumns.Add(new DbKeyColumnInfo(col));
} //foreach
} //method
public void CheckDbFieldTest()
{
var tableInfo1 = new DbTableInfo(typeof(TestModel));
var tableInfo2 = new DbTableInfo(typeof(TestModel)).SetFields(
new DbFieldInfo("NotField").SetNotDbField());
Assert.IsTrue(tableInfo1.CheckDbField("NotField"));
Assert.IsFalse(tableInfo2.CheckDbField("NotField"));
}
public TableModel(string _namespace, DbTableInfo table, List <DbColumnInfo> cols)
{
Namespace = _namespace;
TableName = table.Name;
Description = table.Description;
DbObjectType = table.DbObjectType;
Columns = new List <ColumnModel>();
cols.ForEach(col => { Columns.Add(new ColumnModel(col)); });
}
public void CheckPrimaryKeyTest()
{
var tableInfo1 = new DbTableInfo(typeof(TestModel));
var tableInfo2 = new DbTableInfo(typeof(TestModel)).SetFields(
new DbFieldInfo("ID").SetPrimaryKey());
Assert.IsFalse(tableInfo1.CheckPrimaryKey("ID"));
Assert.IsTrue(tableInfo2.CheckPrimaryKey("ID"));
}
public TableExpression(DbTableInfo tableInfo, Type type, string name, LockType lockType = LockType.None, string joinId = null)
: base(SqlExpressionType.Table, type)
{
Util.Check(type != null, "TableExpression (name: {0}) - type may not be null.", name);
TableInfo = tableInfo;
Name = name;
LockType = lockType;
JoinID = joinId;
}
public void CheckAutoIncrementTest()
{
var tableInfo1 = new DbTableInfo(typeof(TestModel));
var tableInfo2 = new DbTableInfo(typeof(TestModel)).SetFields(
new DbFieldInfo("ID").SetAutoIncrement());
Assert.IsFalse(tableInfo1.CheckAutoIncrement("ID"));
Assert.IsTrue(tableInfo2.CheckAutoIncrement("ID"));
}
public override string GetTableExistsSql(DbTableInfo table)
{
var sql = $@"
SELECT Table_name
FROM all_tables
WHERE owner='{table.Schema}' AND table_name = '{table.TableName}'
";
return(sql);
}
public virtual SqlStatement BuildCrudDeleteOne(DbTableInfo table)
{
var placeHolders = new SqlPlaceHolderList();
var whereCond = BuildWhereConditonForUpdateDeleteOne(table, placeHolders);
var whereClause = new CompositeSqlFragment(SqlTerms.Where, whereCond);
var sql = SqlDialect.SqlCrudTemplateDelete.Format(table.SqlFullName, whereClause);
var stmt = new SqlStatement(SqlKind.DeleteOne, sql, placeHolders, DbExecutionType.NonQuery);
return(stmt);
}
public override SqlStatement BuildCrudInsertOne(DbTableInfo table, EntityRecord record)
{
var sql = base.BuildCrudInsertOne(table, record);
if (table.Entity.Flags.IsSet(EntityFlags.HasIdentity))
{
sql.ResultProcessor = this._identityReader;
}
return(sql);
}
//Drop table - supported, but will fail if there's an old foreign key to this table. Dropping FKs is not supported,
// so we do not delete the table if there are foreign keys
public override void BuildTableDropSql(DbObjectChange change, DbTableInfo table)
{
var refs = table.GetIncomingReferences();
if (refs.Count > 0)
{
return;
}
base.BuildTableDropSql(change, table);
}
public virtual EntityMaterializer CreateEntityMaterializer(DbTableInfo table, IList <DbColumnInfo> columns)
{
var matzr = new EntityMaterializer(table);
foreach (var col in columns)
{
matzr.AddColumn(col);
}
return(matzr);
}
public override SqlStatement BuildCrudUpdateOne(DbTableInfo table, EntityRecord rec)
{
var sql = base.BuildCrudUpdateOne(table, rec);
if (table.Entity.Flags.IsSet(EntityFlags.HasRowVersion))
{
AppendRowVersionCheckReturn(sql, table, rec);
}
return(sql);
}
