public void SQLiteCreate(string sFilePath = "")
{
Globals g = new Globals();
SetSession(SqlProviders.SQLite, sFilePath);
sqlConnection = (SQLiteConnection)sqlFactory.CreateConnection();
sqlCommand = (SQLiteCommand)sqlFactory.CreateCommand();
sqlConnection.ConnectionString = sConnection;
sqlCommand.Connection = sqlConnection;
sqlConnection.Open();
bool bExists = false;
sqlCommand.CommandText = @"SELECT name FROM sqlite_master WHERE type='table' AND name='android_metadata';";
SQLiteDataReader dr = sqlCommand.ExecuteReader();
if (dr.HasRows)
{
bExists = true;
}
dr.Close();
if (!bExists)
{
string[] sTables = new string[] {
@"CREATE TABLE android_metadata (locale TEXT NOT NULL PRIMARY KEY);",
@"CREATE TABLE settings (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, skey TEXT, sval TEXT);",
@"CREATE TABLE users (_id INTEGER, username TEXT, password TEXT, usergroup TEXT, status TEXT, name_first TEXT, name_last TEXT, supercode TEXT);",
@"CREATE TABLE schedules (_id INTEGER, name TEXT, grade TEXT, questions TEXT, notes TEXT);",
@"CREATE TABLE schedules_sections (_id INTEGER, name TEXT);",
@"CREATE TABLE schedules_notes (_id INTEGER, note TEXT);",
@"CREATE TABLE schedules_questions (_id INTEGER, section TEXT, letter TEXT, number TEXT, question TEXT, parts TEXT);",
@"CREATE TABLE lists_faults (_id INTEGER, fault TEXT);",
@"CREATE TABLE locations_sites (_id INTEGER, name TEXT);",
@"CREATE TABLE inspections (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, workorder TEXT, inspector TEXT, item TEXT, schedule TEXT, electrical TEXT, mechanical TEXT, priority TEXT, comments TEXT, entered TEXT);",
@"CREATE TABLE inspections_answers (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, inspection TEXT, question TEXT, part TEXT, answer TEXT);",
@"CREATE TABLE inspections_faults (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, inspection TEXT, question TEXT, part TEXT, fault TEXT, priority TEXT);",
@"CREATE TABLE items (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, schedule TEXT, locationsite TEXT, locationarea TEXT, locationvessel TEXT, locationfloor TEXT, locationgrid TEXT, type_equipment TEXT, tag TEXT, barcode TEXT, cableid TEXT, serial TEXT, description TEXT, manufacturer TEXT, type_model TEXT, drawing TEXT, drawing_hac TEXT, drawing_device_loop TEXT, cert_equipment TEXT, barrier TEXT, type_device TEXT, type_protection TEXT, group_equipment TEXT, trating TEXT, atex_group TEXT, atex_category TEXT, atex_protection TEXT, epl TEXT, ip_rating TEXT, ce_number TEXT, temp_range TEXT, area_zone TEXT, area_group TEXT, area_trating TEXT, access_req TEXT, suitable TEXT, entered TEXT, tracehc TEXT, cpref TEXT);",
@"CREATE TABLE items_faults (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, item TEXT, fault TEXT, priority TEXT);",
@"CREATE TABLE images (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, type TEXT, typeid TEXT, filename TEXT);",
@"CREATE TABLE locations_areas (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, site TEXT);",
@"CREATE TABLE locations_vessels (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, site TEXT, area TEXT);",
@"CREATE TABLE locations_floors (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, site TEXT, area TEXT, vessel TEXT);",
@"CREATE TABLE locations_grids (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, site TEXT, area TEXT, vessel TEXT, floor TEXT);",
@"CREATE TABLE lists_drawings (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, revision TEXT, date TEXT);",
@"CREATE TABLE lists_drawings_hac (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, revision TEXT, date TEXT);",
@"CREATE TABLE lists_manufacturers (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT);",
@"CREATE TABLE lists_types_device (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT);",
@"CREATE TABLE lists_types_protection (_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT);"
};
foreach (string s in sTables)
{
sqlCommand.CommandText = s;
sqlCommand.ExecuteNonQuery();
}
sqlCommand.Parameters.Add(new SQLiteParameter("locale", "en_US"));
sqlCommand.CommandText = @"INSERT INTO android_metadata (locale) VALUES (@locale);";
sqlCommand.ExecuteNonQuery();
sqlCommand.Parameters.Add(new SQLiteParameter("id", 1));
sqlCommand.Parameters.Add(new SQLiteParameter("username", "fes"));
sqlCommand.Parameters.Add(new SQLiteParameter("usergroup", "1"));
sqlCommand.Parameters.Add(new SQLiteParameter("status", "1"));
sqlCommand.Parameters.Add(new SQLiteParameter("name_first", "F.E.S."));
sqlCommand.Parameters.Add(new SQLiteParameter("name_last", "(EX) Ltd"));
sqlCommand.Parameters.Add(new SQLiteParameter("password", g.EncodePassword("fes")));
sqlCommand.CommandText = @"INSERT INTO users (_id,username,usergroup,status,name_first,name_last,password) VALUES (@id,@username,@usergroup,@status,@name_first,@name_last,@password)";
sqlCommand.ExecuteNonQuery();
}
}