public override int AlterTableTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable Fixtable, ClassTable NewTable)
{
try
{
//Update column
for (int i = 0; i < Fixtable.listColumn.Count; i++)
{
//Tim column tuong ung trong NewTable
var foundColumn = NewTable.listColumn.FirstOrDefault(p => p.ColumnName.ToLower() == Fixtable.listColumn[i].ColumnName.ToLower());
if (foundColumn == null)
{
StringBuilder sql = new StringBuilder();
sql.Append(" ALTER TABLE " + Fixtable.Table + " ");
sql.Append(" ADD COLUMN " + Fixtable.listColumn[i].ColumnName + " " + Fixtable.listColumn[i].ColumnDefine + Fixtable.listColumn[i].ColumnAfter + " ");
int ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
if (ret < 0)
{
return(ret);
}
SanitaLogEx.e(TAG, " >>Add column '" + Fixtable.listColumn[i].ColumnName + "'");
}
}
return(0);
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
return(-100);
}
}
private void bwAsync_DoWork(object sender, DoWorkEventArgs e)
{
Stopwatch mStopWatch = new Stopwatch();
mStopWatch.Start();
SanitaLogEx.d(TAG, "Start database cache...");
PostMessage("Start database cache ...");
IsCacheCompleted = false;
try
{
//Update timer
SystemInfo.NOW = SoftUpdatePresenter.GetCurrentTime(null, null);
//Kiểm tra và nâng cấp cấu trúc database
Splasher.Status = "Check and update database...";
SoftUpdatePresenter.DoUpdateDatabaseSQL();
using (IDbConnection connection = SoftUpdatePresenter.GetConnection())
{
//Open connection
connection.Open();
//Begin transtation
using (IDbTransaction trans = connection.BeginTransaction())
{
Splasher.Status = "Đang xử lý : Danh sách danh mục...".Translate();
DM_Intent_Type.InitDefaultList(DM_Intent_TypePresenter.GetDM_Intent_Types(connection, trans));
MyVar.mListDM_Intent_Type = DM_Intent_Type.GetDefaultList(0).OrderBy(p => p.DM_Intent_TypeID).ToList();
DM_Entity_Type.InitDefaultList(DM_Entity_TypePresenter.GetDM_Entity_Types(connection, trans));
MyVar.mListDM_Entity_Type = DM_Entity_Type.GetDefaultList(0).OrderBy(p => p.DM_Entity_TypeID).ToList();
MyVar.mListUser = UserPresenter.GetUsers(connection, trans);
MyVar.mListHome = HomePresenter.GetHomes(connection, trans);
MyVar.mListRoom = RoomPresenter.GetRooms(connection, trans);
MyVar.mListDevice = DevicePresenter.GetDevices(connection, trans);
//-----------------------------------------------------------------------------
//Commit transtation
trans.Commit();
//Close connection
connection.Close();
}
}
SanitaLogEx.d(TAG, "End database cache...");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, "bwAsync_DoWork error !", ex);
}
IsCacheCompleted = true;
}
private void bwAsync_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
try
{
PostMessage("End database cache !");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
}
private void bwAsync_Start()
{
if (!m_BackgroundWorker.IsBusy)
{
m_BackgroundWorker.RunWorkerAsync();
}
else
{
SanitaLogEx.e(TAG, "Task busy...");
}
}
private void bwAsync_DoWork(object sender, DoWorkEventArgs e)
{
Stopwatch mStopWatch = new Stopwatch();
mStopWatch.Start();
SanitaLogEx.d(TAG, "Start database cache...");
PostMessage("Start database cache ...");
IsCacheCompleted = false;
try
{
//Update timer
SystemInfo.NOW = SoftUpdatePresenter.GetCurrentTime(null, null);
//Kiểm tra và nâng cấp cấu trúc database
Splasher.Status = "Check and update database...";
SoftUpdatePresenter.DoUpdateDatabaseSQL();
using (IDbConnection connection = SoftUpdatePresenter.GetConnection())
{
//Open connection
connection.Open();
//Begin transtation
using (IDbTransaction trans = connection.BeginTransaction())
{
MyVar.mListLoaiPhong = LoaiPhongPresenter.GetLoaiPhongs();
//-----------------------------------------------------------------------------
//Commit transtation
trans.Commit();
//Close connection
connection.Close();
}
}
SanitaLogEx.d(TAG, "End database cache...");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, "bwAsync_DoWork error !", ex);
}
IsCacheCompleted = true;
}
public override int CreateTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable table)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(" CREATE TABLE " + table.Table + " ( ");
for (int i = 0; i < table.listColumn.Count; i++)
{
sql.Append(" " + table.listColumn[i].ColumnName + " " + table.listColumn[i].ColumnDefine);
if (table.listColumn[i].isPRIMARY)
{
sql.Append(" PRIMARY KEY ");
}
if (i < table.listColumn.Count - 1)
{
sql.Append(" , ");
}
}
sql.Append(" ) WITH OIDS ; ");
//sql.Append(" ); ");
//Update index
for (int i = 0; i < table.listColumn.Count; i++)
{
if (table.listColumn[i].isIndex)
{
sql.Append(String.Format("CREATE INDEX {0}_{1}_idx ON {0} USING btree ({2});", table.Table.ToLower(), table.listColumn[i].ColumnName.ToLower().Replace(table.Table.ToLower().Replace("tb_", ""), ""), table.listColumn[i].ColumnName));
}
}
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
catch { return(-100); }
}
public DateTime GetCurrentTime(IDbConnection connection, IDbTransaction trans)
{
StringBuilder sql = new StringBuilder();
if (GetDatabaseType() == DATABASE_TYPE.POSTGRESQL)
{
sql.Append(" SELECT NOW()::TIMESTAMP WITHOUT TIME ZONE AS TIME_NOW ");
}
else if (GetDatabaseType() == DATABASE_TYPE.MYSQL)
{
sql.Append(" SELECT NOW() AS TIME_NOW ");
}
else
{
return(DateTime.Now);
}
DataRow row = GetDatabaseDAO().DoGetDataRow(connection, trans, sql.ToString());
DateTime dt = new DateTime();
if (row != null)
{
if (row["TIME_NOW"] != System.DBNull.Value)
{
DateTime.TryParse(row["TIME_NOW"].ToString(), out dt);
}
else
{
SanitaLogEx.e("X100", "GetCurrentTime.row.TIME = null");
}
}
else
{
SanitaLogEx.e("X100", "GetCurrentTime.row = null");
}
#if false
//Update local time
if (dt.Year > 2001)
{
UtilityDate.SetLocalTime(dt);
}
#endif
return(dt);
}
public void StopTask()
{
SanitaLogEx.e(TAG, "Stop task...");
try
{
if (m_BackgroundWorker.CurrentThread != null)
{
m_BackgroundWorker.CurrentThread.Suspend();
m_BackgroundWorker.StopImmediately();
}
}
catch (Exception ex)
{
SanitaLog.e(TAG, ex);
}
}
public int AlterTableTable_DeleteColumn(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable NewTable, ClassTable Fixtable)
{
StringBuilder sql = new StringBuilder();
bool isNeedDeleteColumn = false;
try
{
//Update column
for (int i = 0; i < NewTable.listColumn.Count; i++)
{
//Tim column tuong ung trong NewTable
var foundColumn = Fixtable.listColumn.FirstOrDefault(p => p.ColumnName.ToLower() == NewTable.listColumn[i].ColumnName.ToLower());
if (foundColumn == null)
{
sql.Append(" ALTER TABLE " + NewTable.Table + " DROP COLUMN " + NewTable.listColumn[i].ColumnName + "; ");
isNeedDeleteColumn = true;
SanitaLogEx.e(TAG, " >>Drop column '" + NewTable.listColumn[i].ColumnName + "'");
}
}
sql.Replace(", ; ", "; ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
if (isNeedDeleteColumn)
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
else
{
return(0);
}
}
catch { return(-100); }
}
public override int CreateTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable table)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(" CREATE TABLE " + table.Table + " ( ");
for (int i = 0; i < table.listColumn.Count; i++)
{
sql.Append(" " + table.listColumn[i].ColumnName + " " + table.listColumn[i].ColumnDefine);
if (table.listColumn[i].isPRIMARY)
{
sql.Append(" PRIMARY KEY ");
}
if (i < table.listColumn.Count - 1)
{
sql.Append(" , ");
}
}
sql.Append(" ); ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
catch { return(-100); }
}
public int DeleteTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable table)
{
StringBuilder sql = new StringBuilder();
try
{
sql.Append(" DROP TABLE " + table.Table + " ; ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
catch { return(-100); }
}
private void bwAsync_Worker(object sender, DoWorkEventArgs e)
{
SanitaLogEx.e(TAG, "Start get timer...");
try
{
using (IDbConnection connection = SoftUpdatePresenter.GetConnection())
{
//Open connection
connection.Open();
//Get time
DateTime dt = SoftUpdatePresenter.GetCurrentTime(null, null);
//Close connection
connection.Close();
}
}
catch (Exception ex)
{
SanitaLog.e(TAG, ex);
}
}
public override bool SynchDatabase(IDbConnection connection, IDbTransaction trans, ExBackgroundWorker worker, int number_hosobenhan, bool IsCapNhatTrigger)
{
synch_worker = worker;
//Get database name
String DatabaseName = myBaseDao.GetDatabaseName();
String file_name = "Database\\" + DatabaseName + ".db";
SanitaLogEx.d(TAG, "[SynchDatabase] Database = [" + DatabaseName + "]");
//If have no database -> create datbase
if (!File.Exists("Database\\" + DatabaseName + ".db"))
{
SanitaLog.e(TAG, "[SynchDatabase] Database chưa tồn tại");
CreateDatabase(DatabaseName);
}
//Get all base table
IList <ClassTable> listTable = GetListTable(connection, trans, DatabaseName);
//Synch database
if (synch_worker != null)
{
synch_worker.ReportProgress(0, listFixTable.Count);
}
for (int i = 0; i < listFixTable.Count; i++)
{
if (synch_worker != null)
{
synch_worker.ReportProgress(i + 1, "Nâng cấp table '" + listFixTable[i].Table + "...");
}
//Convert mysql to sqlite
for (int j = 0; j < listFixTable[i].listColumn.Count; j++)
{
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("int(10) unsigned NOT NULL auto_increment", "INTEGER");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("int(10) unsigned", "INTEGER");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("int(10)", "INTEGER");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("int(11)", "INTEGER");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("text CHARACTER SET utf8 COLLATE utf8_unicode_ci", "text");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("longblob", "BLOB");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("double", "REAL");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("Datetime DEFAULT '1-1-1'", "TEXT");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("datetime DEFAULT '1-1-1'", "TEXT");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("Datetime", "TEXT");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("datetime", "TEXT");
listFixTable[i].listColumn[j].ColumnDefine = listFixTable[i].listColumn[j].ColumnDefine.Replace("timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP", "TEXT");
}
//Tim xem co table ton tai chua
var foundTable = listTable.FirstOrDefault(p => p.Table.Equals(listFixTable[i].Table, StringComparison.CurrentCultureIgnoreCase));
if (foundTable == null)
{
//Create table
SanitaLogEx.d(TAG, "[SynchDatabase] Create table '" + listFixTable[i].Table + "'");
if (CreateTable(connection, trans, DatabaseName, listFixTable[i]) <= -100)
{
SanitaLogEx.e(TAG, "[SynchDatabase] Create table lỗi");
if (synch_worker != null)
{
synch_worker.ReportProgress(-1, "Tạo table '" + listFixTable[i].Table + "' lỗi !");
}
return(false);
}
}
else
{
//Alter table
SanitaLogEx.e(TAG, "[SynchDatabase] Alter table '" + listFixTable[i].Table + "'");
if (AlterTableTable(connection, trans, DatabaseName, listFixTable[i], foundTable) <= -100)
{
SanitaLogEx.e(TAG, "[SynchDatabase] Alter table lỗi");
if (synch_worker != null)
{
synch_worker.ReportProgress(-1, "Update table '" + listFixTable[i].Table + "' lỗi !");
}
return(false);
}
}
if (synch_worker != null)
{
synch_worker.ReportProgress(i + 1, "Nâng cấp table '" + listFixTable[i].Table + "' OK !");
}
}
return(true);
}
public override int AlterTableTable(IDbConnection connection, IDbTransaction trans, String DatabaseName, ClassTable Fixtable, ClassTable NewTable)
{
StringBuilder sql = new StringBuilder();
bool isNeedAddColumn = false;
bool isNeedAddIndex = false;
string strPRIMARY = "";
try
{
sql.Append(" ALTER TABLE " + Fixtable.Table + " ");
//Update column
for (int i = 0; i < Fixtable.listColumn.Count; i++)
{
//Add PRIMARY KEY
if (Fixtable.listColumn[i].isPRIMARY)
{
if (string.IsNullOrEmpty(strPRIMARY))
{
strPRIMARY = "'" + Fixtable.listColumn[i].ColumnName + "'";
}
else
{
strPRIMARY += " , " + "'" + Fixtable.listColumn[i].ColumnName + "'";
}
}
//Tim column tuong ung trong NewTable
bool isupdateSQL = false;
var foundColumn = NewTable.listColumn.FirstOrDefault(p => p.ColumnName.ToLower() == Fixtable.listColumn[i].ColumnName.ToLower());
if (foundColumn == null)
{
//AliboboLog.e("[SynchSchema] Add column '" + Fixtable.listColumn[i].ColumnName + "'");
sql.Append(" ADD COLUMN " + Fixtable.listColumn[i].ColumnName + " " + Fixtable.listColumn[i].ColumnDefine + " ");
isNeedAddColumn = true;
isupdateSQL = true;
SanitaLogEx.e(TAG, " >>Add column '" + Fixtable.listColumn[i].ColumnName + "'");
}
if (i < Fixtable.listColumn.Count - 1)
{
if (isupdateSQL)
{
sql.Append(" , ");
}
}
else //Da check den column cuoi cung
{
//Them dau ket thuc lenh
sql.Append(" ; ");
}
}
//Reset
if (!isNeedAddColumn)
{
sql = new StringBuilder();
}
//Update index
for (int i = 0; i < Fixtable.listColumn.Count; i++)
{
if (Fixtable.listColumn[i].isIndex)
{
String index_name = String.Format("{0}_{1}_idx", Fixtable.Table.ToLower(), Fixtable.listColumn[i].ColumnName.ToLower().Replace(Fixtable.Table.ToLower().Replace("tb_", ""), ""));
var foundIndex = NewTable.listIndex.FirstOrDefault(p => p.ColumnName.Equals(index_name, StringComparison.CurrentCultureIgnoreCase));
if (foundIndex == null)
{
sql.Append(String.Format("CREATE INDEX {2} ON {0} USING btree ({1});", Fixtable.Table, Fixtable.listColumn[i].ColumnName, index_name));
isNeedAddIndex = true;
SanitaLogEx.e(TAG, " >>Add index '" + Fixtable.listColumn[i].ColumnName + "'");
}
}
}
sql.Replace(", ; ", "; ");
}
catch (Exception ex)
{
SanitaLogEx.e(TAG, ex);
}
int ret = -100;
try
{
if (isNeedAddColumn || isNeedAddIndex)
{
ret = myBaseDao.DoUpdate(connection, trans, sql.ToString());
return(ret);
}
else
{
return(0);
}
}
catch { return(-100); }
}
static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
SanitaLog.SetLogName("Medibox");
SanitaLogEx.SetLogName("Medibox");
//Create temp
if (!Directory.Exists(Path.Combine(Application.StartupPath, "Data")))
{
Directory.CreateDirectory(Path.Combine(Application.StartupPath, "Data"));
}
if (!Directory.Exists(Path.Combine(Application.StartupPath, "Log")))
{
Directory.CreateDirectory(Path.Combine(Application.StartupPath, "Log"));
}
//Get info
object[] customAttributes = Assembly.GetExecutingAssembly().GetCustomAttributes(false);
foreach (object attribute in customAttributes)
{
if (attribute.GetType() == typeof(AssemblyDescriptionAttribute))
{
MyVar.mAppDescription = ((AssemblyDescriptionAttribute)attribute).Description;
SanitaLog.d(TAG, "Description:" + MyVar.mAppDescription);
}
if (attribute.GetType() == typeof(AssemblyTitleAttribute))
{
MyVar.mAppName = ((AssemblyTitleAttribute)attribute).Title;
SanitaLog.d(TAG, "Name:" + MyVar.mAppName);
}
if (attribute.GetType() == typeof(AssemblyFileVersionAttribute))
{
MyVar.mAppVersion = ((AssemblyFileVersionAttribute)attribute).Version;
SanitaLog.d(TAG, "Version:" + MyVar.mAppVersion);
}
}
//Init Toast
ToastNotification.DefaultToastGlowColor = eToastGlowColor.Red;
ToastNotification.DefaultToastPosition = eToastPosition.MiddleCenter;
ToastNotification.ToastFont = new System.Drawing.Font("Tahoma", 10);
//Load setting
try
{
MyVar.mMediboxSetting = JsonConvert.DeserializeObject <MediboxSetting>(File.ReadAllText(MyVar.SettingsFileName));
if (MyVar.mMediboxSetting.DB_ENCRIPT)
{
MyVar.mMediboxSetting.DB_ENCRIPT = false;
MyVar.mMediboxSetting.DB_SERVER = CryptorEngine.Decrypt(MyVar.mMediboxSetting.DB_SERVER, true);
MyVar.mMediboxSetting.DB_USERID = CryptorEngine.Decrypt(MyVar.mMediboxSetting.DB_USERID, true);
MyVar.mMediboxSetting.DB_PORT = CryptorEngine.Decrypt(MyVar.mMediboxSetting.DB_PORT, true);
MyVar.mMediboxSetting.DB_USERPASSWORD = CryptorEngine.Decrypt(MyVar.mMediboxSetting.DB_USERPASSWORD, true);
MyVar.mMediboxSetting.DB_NAME = CryptorEngine.Decrypt(MyVar.mMediboxSetting.DB_NAME, true);
}
}
catch (Exception ex)
{
SanitaLog.e(TAG, ex);
}
//Start application
{
//Check database
while (true)
{
String Localserver = MyVar.mMediboxSetting.DB_SERVER;
String Localdatabase = MyVar.mMediboxSetting.DB_NAME;
String Localuserid = MyVar.mMediboxSetting.DB_USERID;
String Localpassword = MyVar.mMediboxSetting.DB_USERPASSWORD;
SystemInfo.DatabaseType = Sanita.Utility.Database.Utility.DatabaseUtility.DATABASE_TYPE.POSTGRESQL;
SoftUpdatePresenter.SetConnectionConfig(Localserver, Localuserid, Localpassword, Localdatabase, MyVar.DEFAULT_PORT);
//Init database
SoftUpdatePresenter.InitDatabase();
//Check database OK
if (!SoftUpdatePresenter.IsDatabaseOK())
{
using (FormConfigDatabase form = new FormConfigDatabase())
{
if (form.ShowDialog() == DialogResult.OK)
{
continue;
}
else
{
SanitaMessageBox.Show("Lỗi kết nối cơ sở dữ liệu !", "Thông Báo");
return;
}
}
}
break;
}
//Start main form
FormLogin mFormLogin = new FormLogin();
Application.Run(mFormLogin);
}
}
