private string GetConstraintType()
{
var tp =
_db.GetValue <string>(
@"select CONSTRAINT_TYPE as Type from information_schema.`TABLE_CONSTRAINTS` where CONSTRAINT_SCHEMA=@0 and CONSTRAINT_NAME=@1 and TABLE_NAME=@2 limit 1",
_db.Connection.Database, Item.Name ?? "PRIMARY", Item.TableName);
if (tp.IsNullOrEmpty())
{
throw new InvalidOperationException("Constraint does not exist");
}
switch (tp)
{
case PrimaryKey:
return(PrimaryKey);
case "UNIQUE":
return(Item.Name);
case "FOREIGN KEY":
return(tp + " " + Item.Name);
}
throw new NotSupportedException();
}
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()));
}
/// <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>
/// 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);
}
private string GetInstalledVersion(string schema)
{
return(_db.GetValue <string>("select Version from " + TableName + " where SchemaName=@0 order by Id desc",
schema));
}