public bool CheckCanExecute(DAL database)
{
long numDups = (long)database.ExecuteScalar(
@"SELECT count(1) FROM (
SELECT * FROM (
SELECT count(1) as cnt FROM
CountTree
GROUP BY CuttingUnit_CN, SampleGroup_CN, ifnull(TreeDefaultValue_CN,0), ifnull(Component_CN,0)
)
WHERE cnt > 1
);");
return numDups > 0;
}
private static void RebuildTable(DAL db, String tableName, String newTableDef, String columnList)
{
//get all triggers associated with table so we can recreate them later
var getTriggers = String.Format("SELECT group_concat(sql,';\r\n') FROM sqlite_master WHERE tbl_name LIKE '{0}' and type LIKE 'trigger';", tableName);
var triggers = db.ExecuteScalar(getTriggers) as string;
db.BeginTransaction();
try
{
db.Execute("PRAGMA foreign_keys = off;");
db.Execute("ALTER TABLE " + tableName + " RENAME TO " + tableName + "temp;");
//create rebuilt table
db.Execute(newTableDef + ";");
//copy data from existing table to rebuilt table
db.Execute(
"INSERT INTO " + tableName +
" ( " + columnList + ") " +
"SELECT " + columnList + " FROM " + tableName + "temp;");
db.Execute("DROP TABLE " + tableName + "temp;");
//recreate triggers
if (triggers != null)
{
db.Execute(triggers);
}
db.Execute("PRAGMA foreign_keys = on;");
db.CommitTransaction();
}
catch
{
db.RollbackTransaction();
throw;
}
}
private static String[] ListTriggers(DAL db)
{
var result = db.ExecuteScalar("SELECT group_concat(name,',') FROM sqlite_master WHERE type LIKE 'trigger';") as string;
if (string.IsNullOrEmpty(result)) { return new string[0]; }
else
{
return result.Split(',');
}
}