public void CreateFromDir(bool overwrite, Action<TraceLevel, string> log = null) {
if (log == null) log = (tl, s) => { };
if (DBHelper.DbExists(Connection)) {
log(TraceLevel.Verbose, "Dropping existing database...");
DBHelper.DropDb(Connection);
log(TraceLevel.Verbose, "Existing database dropped.");
}
log(TraceLevel.Info, "Creating database...");
//create database
DBHelper.CreateDb(Connection);
//run scripts
if (File.Exists(Dir + "/props.sql")) {
log(TraceLevel.Verbose, "Setting database properties...");
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/props.sql"));
} catch (SqlBatchException ex) {
throw new SqlFileException(Dir + "/props.sql", ex);
}
// COLLATE can cause connection to be reset
// so clear the pool so we get a new connection
DBHelper.ClearPool(Connection);
}
if (File.Exists(Dir + "/schemas.sql")) {
log(TraceLevel.Verbose, "Creating database schemas...");
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/schemas.sql"));
} catch (SqlBatchException ex) {
throw new SqlFileException(Dir + "/schemas.sql", ex);
}
}
log(TraceLevel.Info, "Creating database objects...");
// create db objects
// resolve dependencies by trying over and over
// if the number of failures stops decreasing then give up
var scripts = GetScripts();
var errors = new List<SqlFileException>();
var prevCount = -1;
while (scripts.Count > 0 && (prevCount == -1 || errors.Count < prevCount)) {
if (errors.Count > 0) {
prevCount = errors.Count;
log(TraceLevel.Info, string.Format(
"{0} errors occurred, retrying...", errors.Count));
}
errors.Clear();
var index = 0;
var total = scripts.Count;
foreach (var f in scripts.ToArray()) {
log(TraceLevel.Verbose, string.Format("Executing script {0} of {1}...{2}", ++index, total, index < total ? "\r" : string.Empty));
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(f));
scripts.Remove(f);
} catch (SqlBatchException ex) {
errors.Add(new SqlFileException(f, ex));
//Console.WriteLine("Error occurred in {0}: {1}", f, ex);
}
}
}
if (prevCount > 0)
log(TraceLevel.Info, errors.Any() ? string.Format("{0} errors unresolved. Details will follow later.", prevCount) : "All errors resolved, were probably dependency issues...");
log(TraceLevel.Info, string.Empty);
ImportData(log); // load data
if (Directory.Exists(Dir + "/after_data")) {
log(TraceLevel.Verbose, "Executing after-data scripts...");
foreach (var f in Directory.GetFiles(Dir + "/after_data", "*.sql")) {
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(f));
} catch (SqlBatchException ex) {
errors.Add(new SqlFileException(f, ex));
}
}
}
// foreign keys
if (Directory.Exists(Dir + "/foreign_keys")) {
log(TraceLevel.Info, "Adding foreign key constraints...");
foreach (var f in Directory.GetFiles(Dir + "/foreign_keys", "*.sql")) {
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(f));
} catch (SqlBatchException ex) {
//throw new SqlFileException(f, ex);
errors.Add(new SqlFileException(f, ex));
}
}
}
if (errors.Count > 0) {
var ex = new BatchSqlFileException();
ex.Exceptions = errors;
throw ex;
}
}
public void CreateFromDir(bool overwrite)
{
if (DBHelper.DbExists(Connection)) {
DBHelper.DropDb(Connection);
}
Console.WriteLine("Creating database...");
//create database
DBHelper.CreateDb(Connection);
//run scripts
if (File.Exists(Dir + "/props.sql")) {
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/props.sql"));
} catch (SqlBatchException ex) {
throw new SqlFileException(Dir + "/props.sql", ex);
}
// COLLATE can cause connection to be reset
// so clear the pool so we get a new connection
DBHelper.ClearPool(Connection);
}
if (File.Exists(Dir + "/schemas.sql")) {
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(Dir + "/schemas.sql"));
} catch (SqlBatchException ex) {
throw new SqlFileException(Dir + "/schemas.sql", ex);
}
}
Console.WriteLine("Creating database objects...");
// create db objects
// resolve dependencies by trying over and over
// if the number of failures stops decreasing then give up
List<string> scripts = GetScripts();
var errors = new List<SqlFileException>();
int prevCount = -1;
while (scripts.Count > 0 && (prevCount == -1 || errors.Count < prevCount)) {
if (errors.Count > 0) {
prevCount = errors.Count;
Console.WriteLine(
"{0} errors occurred, retrying...", errors.Count);
}
errors.Clear();
foreach (string f in scripts.ToArray()) {
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(f));
scripts.Remove(f);
} catch (SqlBatchException ex) {
errors.Add(new SqlFileException(f, ex));
//Console.WriteLine("Error occurred in {0}: {1}", f, ex);
}
}
}
if (!errors.Any() && prevCount > 0)
Console.WriteLine("All errors resolved, were probably dependency issues...");
Console.WriteLine();
Load(); // load the schema first so we can import data
Console.WriteLine("Importing data...");
ImportData(); // load data
Console.WriteLine("Data imported successfully.");
if (Directory.Exists(Dir + "/after_data")) {
foreach (string f in Directory.GetFiles(Dir + "/after_data", "*.sql")) {
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(f));
} catch (SqlBatchException ex) {
errors.Add(new SqlFileException(f, ex));
}
}
}
Console.WriteLine("Adding foreign key constraints...");
// foreign keys
if (Directory.Exists(Dir + "/foreign_keys")) {
foreach (string f in Directory.GetFiles(Dir + "/foreign_keys", "*.sql")) {
try {
DBHelper.ExecBatchSql(Connection, File.ReadAllText(f));
} catch (SqlBatchException ex) {
//throw new SqlFileException(f, ex);
errors.Add(new SqlFileException(f, ex));
}
}
}
if (errors.Count > 0) {
var ex = new BatchSqlFileException();
ex.Exceptions = errors;
throw ex;
}
}