public AutomaticMigration(IAccessDb db, IManageMigrations migrations, ILogWriter logger)
{
_db = db;
_migrations = migrations;
_runner = new MigrationTaskRunner(db, logger);
UpdateSelf();
}
/// <summary>
/// Gets one object, selected by id and an optional predicate
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="additionalPredicate"></param>
/// <param name="args"></param>
/// <returns></returns>
public static T Get <T>(this IAccessDb db, object id, string additionalPredicate = null, params object[] args)
{
id.MustNotBeNull("id");
var tp = typeof(T);
if (typeof(ExpandoObject) == tp || typeof(object) == tp)
{
throw new InvalidOperationException("Can't work with System.Object or dynamic types");
}
var ti = TableInfo.ForType(tp);
if (ti.SelectSingleSql == null)
{
var sb = new StringBuilder("select ");
var p = tp.GetProperties().Where(pr => !pr.PropertyType.IsCustomObjectType()).Select(pr => pr.Name);
foreach (var name in p)
{
sb.AppendFormat("{0},", db.Provider.EscapeName(name));
}
sb.Remove(sb.Length - 1, 1);
sb.AppendFormat(" from {0}", db.Provider.EscapeName(ti.Name));
sb.AppendFormat(" where {0}=@0", db.Provider.EscapeName(ti.PrimaryKey));
if (!string.IsNullOrEmpty(additionalPredicate))
{
sb.AppendFormat(" and {0}", additionalPredicate);
}
ti.SelectSingleSql = sb.ToString();
}
var fargs = new List <object>(args.Length + 1);
fargs.Add(id);
fargs.AddRange(args);
return(db.FirstOrDefault <T>(ti.SelectSingleSql, fargs.ToArray()));
}
public AutomaticMigration(IAccessDb db, IManageMigrations migrations, ILogWriter logger)
{
_db = db;
_migrations = migrations;
_runner=new MigrationTaskRunner(db,logger);
UpdateSelf();
}
public CommonDDLWriter(IAccessDb db, DbEngine engine)
{
db.MustNotBeNull();
Db = db;
Builder = new StringBuilder();
_engine = engine;
}
public CommonDDLWriter(IAccessDb db,DbEngine engine)
{
db.MustNotBeNull();
Db = db;
Builder = new StringBuilder();
_engine = engine;
}
public static int UpdateWhereColumn(this IAccessDb db, string tableName, object data, string colName, object columnValue)
{
tableName.MustNotBeEmpty();
colName.MustNotBeEmpty();
columnValue.MustNotBeNull();
return(Update(db, new TableInfo(tableName), data, columnValue, colName));
}
public static int DeleteFrom <T>(this IAccessDb db, string condition, params object[] args)
{
var ti = TableInfo.ForType(typeof(T));
return
(db.ExecuteCommand(
string.Format("delete from {0} where {1}", db.Provider.EscapeName(ti.Name), condition), args));
}
public UpdateTableBuilder(IAccessDb db)
{
db.MustNotBeNull();
_db = db;
_builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
_builder.Append("update ").WriteTableName();
_builder.Append(" set");
}
public CreateTableBuilder(IAccessDb db,IGenerateDDL generator,TableSchema schema)
{
_db = db;
_generator = generator;
_table = schema;
_columns = new ColumnsCreator(Table);
_constraints = new ConstraintsCreator(Table.Constraints);
_indexes = new IndexCreator(Table.Indexes);
}
public CreateTableBuilder(IAccessDb db, IGenerateDDL generator, TableSchema schema)
{
_db = db;
_generator = generator;
_table = schema;
_columns = new ColumnsCreator(Table);
_constraints = new ConstraintsCreator(Table.Constraints);
_indexes = new IndexCreator(Table.Indexes);
}
//[Obsolete("Use QuerySingle")]
//public static T FirstOrDefault<T>(this IAccessDb db, string sql, params object[] args)
//{
// return db.QuerySingle<T>(sql, args);
//}
#region Insert
/// <summary>
/// Inserts into database
/// </summary>
/// <param name="db"></param>
/// <param name="table">Table name</param>
/// <param name="data">Column and Values</param>
/// <param name="idIsIdentity">By default if the object has an Id property, it's considered to be autoincremented</param>
/// <returns></returns>
public static LastInsertId Insert(this IAccessDb db, string table, object data, bool idIsIdentity = true)
{
table.MustNotBeEmpty();
data.MustNotBeNull();
var ti = new TableInfo(table);
ti.AutoGenerated = idIsIdentity;
return(Insert(db, ti, data));
}
private static int Update(IAccessDb db, TableInfo ti, object data, object id = null, string filterColumn = null)
{
var sb = new StringBuilder();
sb.AppendFormat("update {0} set", db.Provider.EscapeName(ti.Name));
var d = data.ToDictionary();
bool hasId = false;
int i = 0;
var args = new List <object>();
foreach (var k in d)
{
if (k.Key == ti.PrimaryKey)
{
hasId = true;
continue;
}
if (ti.Excludes.Any(c => c == k.Key))
{
continue;
}
sb.AppendFormat(" {0}={1},", db.Provider.EscapeName(k.Key), db.Provider.ParamPrefix + i);
if (ti.ConvertToString.Any(s => s == k.Key))
{
args.Add(k.Value.ToString());
}
else
{
args.Add(k.Value);
}
i++;
}
sb.Remove(sb.Length - 1, 1);
if (filterColumn.IsNullOrEmpty())
{
filterColumn = ti.PrimaryKey;
}
if (id != null || hasId)
{
sb.AppendFormat(" where {0}={1}", db.Provider.EscapeName(filterColumn), db.Provider.ParamPrefix + i);
hasId = true;
if (id == null)
{
id = d[ti.PrimaryKey];
}
}
if (hasId)
{
args.Add(id);
}
return(db.ExecuteCommand(sb.ToString(), args.ToArray()));
}
/// <summary>
/// Task is executed automatically in a transaction
/// </summary>
/// <param name="db"/>
public override void Execute(IAccessDb db)
{
var tbl = db.DatabaseTools.GetCreateTableBuilder(AutomaticMigration.TableName, IfTableExists.Ignore);
tbl.Columns
.Add("Id", DbType.Int32, isNullable: false, autoIncrement: true).AsPrimaryKey()
.Add("SchemaName", DbType.String, "50")
.Add("Version", DbType.AnsiString, size: "25", isNullable: false)
.Add("TimeOfUpdate", DbType.DateTime, isNullable: false);
tbl.ExecuteDDL();
}
public ModifyTableBuilder(IAccessDb db, IGenerateDDL generator, string name)
{
_db = db;
_generator = generator;
name.MustNotBeEmpty();
_table = new TableSchema(name);
_columns = new ColumnsEditor(Table, this);
_constraints = new ConstraintsEditor(Table.Constraints, this);
_indexes = new IndexEditor(Table.Indexes, this);
}
/// <summary>
/// Selects first row matching criteria and maps it to poco
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static IEnumerable <T> Query <T>(this IAccessDb db, Expression <Func <T, bool> > condition)
{
var builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
builder
.WriteSelect()
.WriteSelectAllColumns()
.WriteFrom()
.Where(condition);
return(db.Query <T>(builder.ToString(), builder.Parameters.ToArray()));
}
public static long Count <T>(this IAccessDb db, Expression <Func <T, bool> > criteria = null)
{
var builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
builder.Append("select count(*) from ").WriteTableName();
if (criteria != null)
{
builder.Where(criteria);
}
return(db.GetValue <long>(builder.ToString(), builder.Parameters.ToArray()));
}
public static int DeleteFrom <T>(this IAccessDb db, Expression <Func <T, bool> > criteria = null)
{
var builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
builder.WriteDelete();
if (criteria != null)
{
builder.Where(criteria);
}
return(db.ExecuteCommand(builder.ToString(), builder.Parameters.ToArray()));
}
public ModifyTableBuilder(IAccessDb db, IGenerateDDL generator, string name)
{
_db = db;
_generator = generator;
name.MustNotBeEmpty();
_table = new TableSchema(name);
_columns = new ColumnsEditor(Table, this);
_constraints = new ConstraintsEditor(Table.Constraints, this);
_indexes = new IndexEditor(Table.Indexes, this);
}
/// <summary>
/// Task is executed automatically in a transaction
/// </summary>
/// <param name="db"/>
public override void Execute(IAccessDb db)
{
var tbl = db.DatabaseTools.GetCreateTableBuilder(AutomaticMigration.TableName, IfTableExists.Ignore);
tbl.Columns
.Add("Id", DbType.Int32, isNullable: false, autoIncrement: true).AsPrimaryKey()
.Add("SchemaName", DbType.String, "50")
.Add("Version", DbType.AnsiString, size: "25", isNullable: false)
.Add("TimeOfUpdate", DbType.DateTime, isNullable: false);
tbl.ExecuteDDL();
}
public CreateTableBuilder(IAccessDb db, IGenerateDDL generator, string tableName, IfTableExists option)
{
_db = db;
_generator = generator;
tableName.MustNotBeEmpty();
_table = new TableSchema(tableName);
_columns = new ColumnsCreator(Table);
_constraints = new ConstraintsCreator(Table.Constraints);
_indexes = new IndexCreator(Table.Indexes);
Table.Name = tableName;
Table.CreationOption = option;
}
public CreateTableBuilder(IAccessDb db, IGenerateDDL generator, string tableName, IfTableExists option)
{
_db = db;
_generator = generator;
tableName.MustNotBeEmpty();
_table = new TableSchema(tableName);
_columns = new ColumnsCreator(Table);
_constraints = new ConstraintsCreator(Table.Constraints);
_indexes = new IndexCreator(Table.Indexes);
Table.Name = tableName;
Table.CreationOption = option;
}
/// <summary>
/// Returns only the specified column value
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="R"></typeparam>
/// <param name="db"></param>
/// <param name="selector">Column selector</param>
/// <param name="criteria">Selection criteria</param>
/// <returns></returns>
public static R GetColumnValue <T, R>(this IAccessDb db, Expression <Func <T, R> > selector,
Expression <Func <T, bool> > criteria)
{
selector.MustNotBeNull();
var builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
builder
.WriteSelect()
.WriteSelectColumn(selector)
.WriteFrom()
.Where(criteria);
return(db.GetValue <R>(builder.ToString(), builder.Parameters.ToArray()));
}
/// <summary>
/// Task is executed automatically in a transaction
/// </summary>
/// <param name="db"/>
public override void Execute(IAccessDb db)
{
var store = db.DatabaseTools.GetCreateTableBuilder(TableName, IfTableExists.DropIt);
store.Columns
.Add("Id", DbType.Int64, isNullable: false, autoIncrement: true).AsPrimaryKey()
.Add("MessageId", DbType.Guid, isNullable: false)
.Add("Body", DbType.Binary)
.Add("CommittedAt", DbType.DateTime)
.Add("State", DbType.Int16, isNullable: false)
.Add("Hash", DbType.AnsiStringFixedLength, "40", false).AsUnique("uc_message")
.Add("Failures", DbType.Int16, isNullable: false, defaultValue: "0");
;
store.ExecuteDDL();
}
private static LastInsertId Insert(IAccessDb db, TableInfo ti, object data)
{
var p = db.Provider;
List <object> args = null;
var d = data.ToDictionary();
if (ti.InsertSql == null)
{
var sb = new StringBuilder("Insert into");
sb.AppendFormat(" {0} (", p.EscapeName(ti.Name));
args = FillArgs(d, ti, p, sb);
sb.Remove(sb.Length - 1, 1);
sb.Append(") values(");
for (var i = 0; i < args.Count; i++)
{
sb.Append("@" + i + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append(")");
ti.InsertSql = sb.ToString();
}
if (args == null)
{
args = FillArgs(d, ti, p);
}
var st = db.WithSql(ti.InsertSql, args.ToArray()) as SqlStatement;
st.ReuseCommand = true;
LastInsertId rez;
try
{
rez = db.Provider.ExecuteInsert(st, ti.PrimaryKey);
}
finally
{
db.CloseConnection();
}
return(rez);
}
public static int Update <T>(this IAccessDb db, object data, Expression <Func <T, bool> > criteria)
{
var args = data.ToDictionary();
var ti = TableInfo.ForType(typeof(T));
var updater = db.Update <T>();
foreach (var kv in args)
{
if (ti.PrimaryKey == kv.Value)
{
continue;
}
if (ti.Excludes.Any(d => d == kv.Key))
{
continue;
}
updater.Set(kv.Key, kv.Value);
}
return(updater.Where(criteria).Execute());
}
/// <summary>
/// Checks if the table has any rows with optional criteria
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public static bool HasAnyRows <T>(this IAccessDb db, Expression <Func <T, bool> > criteria = null)
{
var builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
builder
.Append("select 1 from ")
.WriteTableName()
.Append(" where exists(select * from ")
.WriteTableName();
if (criteria != null)
{
builder.Where(criteria);
}
builder.Append(")");
var r = db.GetValue <int?>(builder.ToString(), builder.Parameters.ToArray());
if (r.HasValue)
{
return(true);
}
return(false);
}
public SqlServerCompactModifiedColumnsWriter(StringBuilder builder, IAccessDb db)
{
Builder = builder;
Db = db;
}
/// <summary>
/// If both poco has id property and the Id arg is specified, the arg is used
/// </summary>
/// <param name="db"></param>
/// <param name="table"></param>
/// <param name="data"></param>
/// <param name="id"></param>
/// <returns></returns>
public static int Update(this IAccessDb db, string table, object data, object id = null)
{
var ti = new TableInfo(table);
return(Update(db, ti, data, id));
}
/// <summary>
/// If both poco has id property and the Id arg is specified, the arg is used
/// </summary>
public static int Update <T>(this IAccessDb db, object data, object id = null)
{
var ti = TableInfo.ForType(typeof(T));
return(Update(db, ti, data, id));
}
/// <summary>
/// Task is executed automatically in a transaction
/// </summary>
/// <param name="db"/>
public override void Execute(IAccessDb db)
{
var table = db.DatabaseTools.GetCreateTableBuilder(TableName, IfTableExists.DropIt);
table.Columns
.Add("Id", DbType.Guid, isNullable: false).AsPrimaryKey()
.Add("Body", DbType.Binary, isNullable: false)
.Add("QueuedAt", DbType.DateTime, isNullable: false)
.Add("ShouldRunAt", DbType.DateTime, isNullable: false)
.Add("CompletedAt", DbType.DateTime)
.Add("Failures",DbType.Int16,isNullable:false,defaultValue:"0")
;
table.ExecuteDDL();
}
public MySqlDDLWriter(IAccessDb db)
: base(db, DbEngine.MySql)
{
}
public abstract void Execute(IAccessDb db);
public SqliteDDLWriter(IAccessDb db)
: base(db, DbEngine.SQLite)
{
ColonBeforeConstraints = true;
}
public override void Execute(IAccessDb db)
{
_builder.Append("2");
}
public PostgresqlDDLWriter(IAccessDb db)
: base(db,DbEngine.PostgreSQL)
{
}
public override void Execute(IAccessDb db)
{
_sb.Append("1.0.2");
}
public SqlServerDDLWriter(IAccessDb db)
: base(db, DbEngine.SqlServer)
{
}
public static void CreateTable <T>(this IAccessDb db)
{
db.DatabaseTools.GetCreateTableBuilder <T>().ExecuteDDL();
}
public MigrationTaskRunner(IAccessDb db, ILogWriter logger)
{
_db = db;
_logger = logger;
}
public override void Execute(IAccessDb db)
{
_builder.Append("1");
}
/// <summary>
/// Gets update table builder
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <returns></returns>
public static IBuildUpdateTable <T> Update <T>(this IAccessDb db)
{
return(new UpdateTableBuilder <T>(db));
}
public PostgresqlDDLWriter(IAccessDb db)
: base(db, DbEngine.PostgreSQL)
{
}
public static int Delete <T>(this IAccessDb db, string condition, params object[] args)
{
return(DeleteFrom <T>(db, condition, args));
}
public abstract void Execute(IAccessDb db);
public SqlServerDDLWriter(IAccessDb db) : base(db, DbEngine.SqlServer)
{
}
public SqlServerChangedColumnsManager(StringBuilder builder, IAccessDb db)
: base(builder, DbEngine.SqlServer)
{
_db = db;
}
public static LastInsertId Insert <T>(this IAccessDb db, T data) where T : class
{
data.MustNotBeNull();
return(Insert(db, TableInfo.ForType(typeof(T)), data));
}
public SqlServerChangedColumnsManager(StringBuilder builder, IAccessDb db) : base(builder, DbEngine.SqlServer)
{
_db = db;
}
/// <summary>
/// Task is executed automatically in a transaction
/// </summary>
/// <param name="db"/>
public override void Execute(IAccessDb db)
{
}
public MysqlDropConstraintWriter(StringBuilder builder, IAccessDb db)
: base(builder, DbEngine.MySql, db.DatabaseTools)
{
db.MustNotBeNull();
_db = db;
}
