internal static void LoadGridColumns(GridView grd, List<ColumnProps> cols, List<GRDSort> sortColumns, string gridName)
{
try
{
cols.Clear();
var sqlList = new List<string>();
var tableName = gridName == "grdNotes" ? "GRD_NOTES_COLUMNS" : "GRD_BACK_COLUMNS";
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
if (oData.TableExists(tableName))
{
// check for "encrypted" column
if (gridName == "grdNotes")
{
var obj =
oData.GetScalar("SELECT CN_KEY FROM GRD_NOTES_COLUMNS WHERE CN_KEY = 'Note_Encrypted'");
if (obj == null || IsDBNull(obj))
{
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_DISPLAY_INDEX = CN_ORIGINAL_INDEX");
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_ORIGINAL_INDEX = CN_ORIGINAL_INDEX + 1, CN_DISPLAY_INDEX = CN_DISPLAY_INDEX + 1 WHERE CN_ORIGINAL_INDEX >= 9");
oData.Execute(
"INSERT INTO GRD_NOTES_COLUMNS VALUES('Note_Encrypted', 1, 9, 9, 32, 32, NULL, NULL)");
}
obj =
oData.GetScalar("SELECT CN_KEY FROM GRD_NOTES_COLUMNS WHERE CN_KEY = 'Note_PrevGroup'");
if (obj == null || IsDBNull(obj))
{
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_DISPLAY_INDEX = CN_ORIGINAL_INDEX");
oData.Execute(
"UPDATE GRD_NOTES_COLUMNS SET CN_ORIGINAL_INDEX = CN_ORIGINAL_INDEX + 1, CN_DISPLAY_INDEX = CN_DISPLAY_INDEX + 1 WHERE CN_ORIGINAL_INDEX >= 11");
oData.Execute(
"INSERT INTO GRD_NOTES_COLUMNS VALUES('Note_Encrypted', 1, 11, 11, 32, 32, NULL, NULL)");
}
}
using (var t = oData.FillDataTable("SELECT * FROM " + tableName))
{
foreach (DataRow r in t.Rows)
{
var column =
grd.Columns.Where(t1 => PNGridViewHelper.GetColumnName(t1) == (string)r["CN_KEY"])
.Cast<FixedWidthColumn>()
.FirstOrDefault();
if (column == null) continue;
if (!Convert.ToBoolean(r["CN_VISIBILITY"]))
{
column.Visibility = Visibility.Hidden;
column.Width = column.ActualWidth;
}
else
{
column.FixedWidth = Convert.ToInt32(r["CN_DISPLAY_WIDTH"]);
column.Width = column.FixedWidth;
}
column.DisplayIndex = Convert.ToInt32(r["CN_DISPLAY_INDEX"]);
column.OriginalIndex = Convert.ToInt32(r["CN_ORIGINAL_INDEX"]);
cols.Add(new ColumnProps
{
Name = (string)r["CN_KEY"],
Visibility = column.Visibility,
Width = Convert.ToInt32(r["CN_DISPLAY_WIDTH"])
});
var sort = new GRDSort { Key = (string)r["CN_KEY"] };
if (!IsDBNull(r["CN_SORT_ORDER"]))
{
sort.SortOrder = (ListSortDirection)Convert.ToInt32(r["CN_SORT_ORDER"]);
}
if (!IsDBNull(r["CN_LAST_SORTED"]))
{
sort.LastSorted = Convert.ToBoolean(r["CN_LAST_SORTED"]);
}
sortColumns.Add(sort);
}
}
}
else
{
sqlList.Add(
"CREATE TABLE [" + tableName + "] ([CN_KEY] TEXT PRIMARY KEY NOT NULL UNIQUE, [CN_VISIBILITY] BOOLEAN NOT NULL, [CN_ORIGINAL_INDEX] INT NOT NULL, [CN_DISPLAY_INDEX] INT NOT NULL, [CN_ORIGINAL_WIDTH] INT NOT NULL, [CN_DISPLAY_WIDTH] INT NOT NULL, [CN_SORT_ORDER] INT, [CN_LAST_SORTED] BOOLEAN)");
for (var i = 0; i < grd.Columns.Count; i++)
{
var c = (FixedWidthColumn)grd.Columns[i];
var sb = new StringBuilder();
sb.Append(
"INSERT INTO " + tableName + " (CN_KEY, CN_VISIBILITY, CN_ORIGINAL_INDEX, CN_DISPLAY_INDEX, CN_ORIGINAL_WIDTH, CN_DISPLAY_WIDTH, CN_SORT_ORDER, CN_LAST_SORTED) VALUES(");
sb.Append("'");
sb.Append(PNGridViewHelper.GetColumnName(c));
sb.Append("', ");
sb.Append(1);
sb.Append(", ");
sb.Append(i);
sb.Append(", ");
sb.Append(i);
sb.Append(", ");
sb.Append(c.FixedWidth);
sb.Append(", ");
sb.Append(c.FixedWidth);
sb.Append(", NULL, NULL)");
sqlList.Add(sb.ToString());
cols.Add(new ColumnProps
{
Name = PNGridViewHelper.GetColumnName(c),
Visibility = Visibility.Visible,
Width = c.FixedWidth
});
}
ExecuteTransactionForList(sqlList, SettingsConnectionString);
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
internal static void LoadDBSettings()
{
try
{
var mediaConverter = new ColorConverter();
var drawingConverter = new System.Drawing.ColorConverter();
var sc = new SizeConverter();
var pc = new PointConverter();
var wpfc = new WPFFontConverter();
//var fc = new FontConverter();
var v = Assembly.GetExecutingAssembly().GetName().Version;
PNStatic.Settings = new PNSettings();
SettingsConnectionString = SQLiteDataObject.CheckAndCreateDatabase(PNPaths.Instance.SettingsDBPath);
using (var oData = new SQLiteDataObject(SettingsConnectionString))
{
string sqlQuery;
//config
var pnc = PNStatic.Settings.Config;
if (!oData.TableExists("CONFIG"))
{
sqlQuery = "CREATE TABLE [CONFIG] ([LAST_PAGE] TEXT, [EXIT_FLAG] INT, [CP_LAST_GROUP] INT, [SKINNABLE] BOOLEAN, [CP_PVW_COLOR] TEXT, [CP_USE_CUST_PVW_COLOR] BOOLEAN, [CP_SIZE] TEXT, [CP_LOCATION] TEXT, [CONTROLS_STYLE] TEXT, [CP_PVW_RIGHT] BOOLEAN, [UI_FONT] TEXT, [PROGRAM_VERSION] TEXT, [CP_PVW_SHOW] BOOLEAN, [CP_GROUPS_SHOW] BOOLEAN, [NOTES_WITH_SHORTCUTS] TEXT, [SEARCH_NOTES_SETT] TEXT)";
oData.Execute(sqlQuery);
sqlQuery =
"INSERT INTO CONFIG VALUES(NULL, -1, NULL, NULL, NULL, NULL, '1000,600', NULL, NULL, NULL, NULL, '" +
v.ToString(3) + "', NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
PNSingleton.Instance.FontUser = new PNFont();
pnc.CPSize = new Size(1000, 600);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'PROGRAM_VERSION' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
PNSingleton.Instance.PlatformChanged = true;
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [PROGRAM_VERSION] TEXT";
oData.Execute(sqlQuery);
//save previous edition files
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("back_prev",
"Backing up files from previous edition...");
savePreviousFiles();
}
rows = t.Select("COLUMN_NAME = 'CP_PVW_RIGHT' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_PVW_RIGHT] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CP_PVW_SHOW' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_PVW_SHOW] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CP_GROUPS_SHOW' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_GROUPS_SHOW] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'UI_FONT' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [UI_FONT] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'NOTES_WITH_SHORTCUTS' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [NOTES_WITH_SHORTCUTS] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'SEARCH_NOTES_SETT' AND TABLE_NAME = 'CONFIG'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [SEARCH_NOTES_SETT] TEXT";
oData.Execute(sqlQuery);
}
}
//store version
sqlQuery = "UPDATE CONFIG SET PROGRAM_VERSION = '" + v.ToString(3) + "'";
oData.Execute(sqlQuery);
//upgrade colors an fonts
if (PNSingleton.Instance.PlatformChanged)
{
var obj = oData.GetScalar("SELECT CP_PVW_COLOR FROM CONFIG");
if (obj != null && !IsDBNull(obj))
{
obj = drawingConverter.ConvertFromString(null, PNStatic.CultureInvariant, (string)obj);
if (obj != null)
{
var clrD = (System.Drawing.Color)obj;
var clrM = Color.FromArgb(clrD.A, clrD.R, clrD.G, clrD.B);
var clrText = mediaConverter.ConvertToString(clrM);
oData.Execute("UPDATE CONFIG SET CP_PVW_COLOR = '" + clrText + "'");
}
}
obj = oData.GetScalar("SELECT UI_FONT FROM CONFIG");
if (obj != null && !IsDBNull(obj))
{
var pnFonf = PNStatic.FromDrawingFont((string)obj);
var fontText = wpfc.ConvertToString(pnFonf);
oData.Execute("UPDATE CONFIG SET UI_FONT = '" + fontText + "'");
}
}
sqlQuery = "SELECT * FROM CONFIG";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
if (!IsDBNull(r["LAST_PAGE"]))
{
pnc.LastPage = Convert.ToInt32(r["LAST_PAGE"]);
}
if (!IsDBNull(r["EXIT_FLAG"]))
{
pnc.ExitFlag = (int)r["EXIT_FLAG"];
}
if (!IsDBNull(r["CP_LAST_GROUP"]))
{
pnc.CPLastGroup = (int)r["CP_LAST_GROUP"];
}
if (!IsDBNull(r["SKINNABLE"]))
{
pnc.Skinnable = Convert.ToBoolean(r["SKINNABLE"]);
}
if (!IsDBNull(r["CP_PVW_COLOR"]))
{
var convertFromString = mediaConverter.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CP_PVW_COLOR"]);
if (
convertFromString != null)
pnc.CPPvwColor = (Color)convertFromString;
}
if (!IsDBNull(r["CP_USE_CUST_PVW_COLOR"]))
{
pnc.CPUseCustPvwColor = Convert.ToBoolean(r["CP_USE_CUST_PVW_COLOR"]);
}
if (!IsDBNull(r["CP_SIZE"]))
{
var str = Convert.ToString(r["CP_LOCATION"]);
if (!str.Contains('-'))
{
str = Convert.ToString(r["CP_SIZE"]);
var convertFromString = sc.ConvertFromString(null, PNStatic.CultureInvariant,
str);
if (convertFromString != null)
pnc.CPSize = (Size)convertFromString;
}
}
if (!IsDBNull(r["CP_LOCATION"]))
{
var convertFromString = pc.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CP_LOCATION"]);
if (
convertFromString != null)
pnc.CPLocation = (Point)convertFromString;
}
if (!IsDBNull(r["CONTROLS_STYLE"]))
{
pnc.ControlsStyle = (string)r["CONTROLS_STYLE"];
}
if (!IsDBNull(r["CP_PVW_RIGHT"]))
{
pnc.CPPvwRight = Convert.ToBoolean(r["CP_PVW_RIGHT"]);
}
if (!IsDBNull(r["CP_PVW_SHOW"]))
{
pnc.CPPvwShow = Convert.ToBoolean(r["CP_PVW_SHOW"]);
}
if (!IsDBNull(r["CP_GROUPS_SHOW"]))
{
pnc.CPGroupsShow = Convert.ToBoolean(r["CP_GROUPS_SHOW"]);
}
if (!IsDBNull(r["UI_FONT"]))
{
var temp = (string)(r["UI_FONT"]);
if (temp != "")
{
PNSingleton.Instance.FontUser = (PNFont)wpfc.ConvertFromString(temp);
}
else
{
PNSingleton.Instance.FontUser = new PNFont();
}
}
else
{
PNSingleton.Instance.FontUser = new PNFont();
}
if (!IsDBNull(r["SEARCH_NOTES_SETT"]))
{
var arr = Convert.ToString(r["SEARCH_NOTES_SETT"]).Split('|');
pnc.SearchNotesSettings.WholewWord = Convert.ToBoolean(arr[0]);
pnc.SearchNotesSettings.MatchCase = Convert.ToBoolean(arr[1]);
pnc.SearchNotesSettings.IncludeHidden = Convert.ToBoolean(arr[2]);
pnc.SearchNotesSettings.Criteria = Convert.ToInt32(arr[3]);
pnc.SearchNotesSettings.Scope = Convert.ToInt32(arr[4]);
}
SaveExitFlag(-1);
}
else
{
sqlQuery =
"INSERT INTO CONFIG VALUES(NULL, -1, NULL, NULL, NULL, NULL, '1000,600', NULL, NULL, NULL, NULL, '" +
v.ToString(3) + "', NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
PNSingleton.Instance.FontUser = new PNFont();
pnc.CPSize = new Size(1000, 600);
}
}
}
//general setting
var pngeneral = PNStatic.Settings.GeneralSettings;
if (!oData.TableExists("GENERAL_SETTINGS"))
{
sqlQuery = "CREATE TABLE [GENERAL_SETTINGS] ([LANGUAGE] TEXT, [RUN_ON_START] BOOLEAN, [SHOW_CP_ON_START] BOOLEAN, [CHECK_NEW_VERSION_ON_START] BOOLEAN, [HIDE_TOOLBAR] BOOLEAN, [USE_CUSTOM_FONTS] BOOLEAN, [SHOW_SCROLLBAR] BOOLEAN, [HIDE_DELETE_BUTTON] BOOLEAN, [CHANGE_HIDE_TO_DELETE] BOOLEAN, [HIDE_HIDE_BUTTON] BOOLEAN, [BULLETS_INDENT] INT, [MARGIN_WIDTH] INT, [SAVE_ON_EXIT] BOOLEAN, [CONFIRM_SAVING] BOOLEAN, [CONFIRM_BEFORE_DELETION] BOOLEAN, [SAVE_WITHOUT_CONFIRM_ON_HIDE] BOOLEAN, [WARN_ON_AUTOMATICAL_DELETE] BOOLEAN, [AUTO_SAVE] BOOLEAN, [AUTO_SAVE_PERIOD] INT, [REMOVE_FROM_BIN_PERIOD] INT, [DATE_FORMAT] TEXT, [TIME_FORMAT] TEXT, [SKINLESS_WIDTH] INT, [SKINLESS_HEIGHT] INT, [SPELL_COLOR] TEXT, [USE_SKINS] BOOLEAN, [SPELL_MODE] INT, [SPELL_DICT] TEXT, [DOCK_WIDTH] INT, [DOCK_HEIGHT] INT, [SHOW_PRIORITY_ON_START] BOOLEAN, [BUTTONS_SIZE] INT, [AUTOMATIC_SMILIES] BOOLEAN, [SPACE_POINTS] INT, [RESTORE_AUTO] BOOLEAN, [PARAGRAPH_INDENT] INT, [AUTO_HEIGHT] BOOLEAN, [CRITICAL_ON_START] BOOLEAN, [CRITICAL_PERIODICALLY] BOOLEAN, [DELETE_SHORTCUTS_ON_EXIT] BOOLEAN, [RESTORE_SHORTCUTS_ON_START] BOOLEAN, [CLOSE_ON_SHORTCUT] BOOLEAN)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO GENERAL_SETTINGS VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
//store default english language
var langPath = Path.Combine(PNPaths.Instance.LangDir, pngeneral.Language);
PNLang.Instance.LoadLanguage(langPath);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'BUTTONS_SIZE' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [BUTTONS_SIZE] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'AUTOMATIC_SMILIES' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [AUTOMATIC_SMILIES] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'SPACE_POINTS' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [SPACE_POINTS] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RESTORE_AUTO' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [RESTORE_AUTO] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PARAGRAPH_INDENT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [PARAGRAPH_INDENT] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'AUTO_HEIGHT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [AUTO_HEIGHT] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CRITICAL_ON_START' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CRITICAL_ON_START] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CRITICAL_PERIODICALLY' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CRITICAL_PERIODICALLY] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'DELETE_SHORTCUTS_ON_EXIT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [DELETE_SHORTCUTS_ON_EXIT] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RESTORE_SHORTCUTS_ON_START' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [RESTORE_SHORTCUTS_ON_START] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CLOSE_ON_SHORTCUT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CLOSE_ON_SHORTCUT] BOOLEAN";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM GENERAL_SETTINGS";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
if (!IsDBNull(r["LANGUAGE"]))
{
pngeneral.Language = (string) r["LANGUAGE"];
}
var langPath = Path.Combine(PNPaths.Instance.LangDir, pngeneral.Language);
PNLang.Instance.LoadLanguage(langPath);
if (!IsDBNull(r["RUN_ON_START"]))
{
pngeneral.RunOnStart = (bool)r["RUN_ON_START"];
}
if (!IsDBNull(r["HIDE_TOOLBAR"]))
{
pngeneral.HideToolbar = (bool)r["HIDE_TOOLBAR"];
}
if (!IsDBNull(r["SHOW_CP_ON_START"]))
{
pngeneral.ShowCPOnStart = (bool)r["SHOW_CP_ON_START"];
}
if (!IsDBNull(r["CHECK_NEW_VERSION_ON_START"]))
{
pngeneral.CheckNewVersionOnStart = (bool)r["CHECK_NEW_VERSION_ON_START"];
}
if (!IsDBNull(r["USE_CUSTOM_FONTS"]))
{
pngeneral.UseCustomFonts = (bool)r["USE_CUSTOM_FONTS"];
}
if (!IsDBNull(r["SHOW_SCROLLBAR"]))
{
pngeneral.ShowScrollbar =
(System.Windows.Forms.RichTextBoxScrollBars)
Convert.ToInt32(r["SHOW_SCROLLBAR"]);
}
if (!IsDBNull(r["HIDE_DELETE_BUTTON"]))
{
pngeneral.HideDeleteButton = (bool)r["HIDE_DELETE_BUTTON"];
}
if (!IsDBNull(r["CHANGE_HIDE_TO_DELETE"]))
{
pngeneral.ChangeHideToDelete = (bool)r["CHANGE_HIDE_TO_DELETE"];
}
if (!IsDBNull(r["HIDE_HIDE_BUTTON"]))
{
pngeneral.HideHideButton = (bool)r["HIDE_HIDE_BUTTON"];
}
if (!IsDBNull(r["BULLETS_INDENT"]))
{
pngeneral.BulletsIndent = (short)(int)r["BULLETS_INDENT"];
}
if (!IsDBNull(r["MARGIN_WIDTH"]))
{
pngeneral.MarginWidth = (short)(int)r["MARGIN_WIDTH"];
}
if (!IsDBNull(r["DATE_FORMAT"]))
{
pngeneral.DateFormat =
((string)r["DATE_FORMAT"]).Replace("H", "")
.Replace("h", "")
.Replace("m", "")
.Replace(":", "")
.Trim();
Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern = pngeneral.DateFormat;
}
if (!IsDBNull(r["TIME_FORMAT"]))
{
pngeneral.TimeFormat = (string)r["TIME_FORMAT"];
Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongTimePattern = pngeneral.TimeFormat;
}
if (!IsDBNull(r["SAVE_ON_EXIT"]))
{
pngeneral.SaveOnExit = (bool)r["SAVE_ON_EXIT"];
}
if (!IsDBNull(r["CONFIRM_SAVING"]))
{
pngeneral.ConfirmSaving = (bool)r["CONFIRM_SAVING"];
}
if (!IsDBNull(r["CONFIRM_BEFORE_DELETION"]))
{
pngeneral.ConfirmBeforeDeletion = (bool)r["CONFIRM_BEFORE_DELETION"];
}
if (!IsDBNull(r["SAVE_WITHOUT_CONFIRM_ON_HIDE"]))
{
pngeneral.SaveWithoutConfirmOnHide = (bool)r["SAVE_WITHOUT_CONFIRM_ON_HIDE"];
}
if (!IsDBNull(r["WARN_ON_AUTOMATICAL_DELETE"]))
{
pngeneral.WarnOnAutomaticalDelete = (bool)r["WARN_ON_AUTOMATICAL_DELETE"];
}
if (!IsDBNull(r["REMOVE_FROM_BIN_PERIOD"]))
{
pngeneral.RemoveFromBinPeriod = (int)r["REMOVE_FROM_BIN_PERIOD"];
}
if (!IsDBNull(r["AUTO_SAVE"]))
{
pngeneral.Autosave = (bool)r["AUTO_SAVE"];
}
if (!IsDBNull(r["AUTO_SAVE_PERIOD"]))
{
pngeneral.AutosavePeriod = (int)r["AUTO_SAVE_PERIOD"];
}
if (!IsDBNull(r["SKINLESS_WIDTH"]))
{
pngeneral.Width = (int)r["SKINLESS_WIDTH"];
}
if (!IsDBNull(r["SKINLESS_HEIGHT"]))
{
pngeneral.Height = (int)r["SKINLESS_HEIGHT"];
}
if (!IsDBNull(r["SPELL_COLOR"]))
{
pngeneral.SpellColor = (System.Drawing.Color)
drawingConverter.ConvertFromString(null, PNStatic.CultureInvariant,
(string)r["SPELL_COLOR"]);
}
if (!IsDBNull(r["USE_SKINS"]))
{
pngeneral.UseSkins = (bool)r["USE_SKINS"];
}
if (!IsDBNull(r["SPELL_MODE"]))
{
pngeneral.SpellMode = (int)r["SPELL_MODE"];
}
if (!IsDBNull(r["SPELL_DICT"]))
{
pngeneral.SpellDict = (string)r["SPELL_DICT"];
}
if (!IsDBNull(r["DOCK_WIDTH"]))
{
pngeneral.DockWidth = (int)r["DOCK_WIDTH"];
}
if (!IsDBNull(r["DOCK_HEIGHT"]))
{
pngeneral.DockHeight = (int)r["DOCK_HEIGHT"];
}
if (!IsDBNull(r["SHOW_PRIORITY_ON_START"]))
{
pngeneral.ShowPriorityOnStart = (bool)r["SHOW_PRIORITY_ON_START"];
}
if (!IsDBNull(r["BUTTONS_SIZE"]))
{
pngeneral.ButtonsSize = (ToolStripButtonSize)((int)r["BUTTONS_SIZE"]);
}
if (!IsDBNull(r["AUTOMATIC_SMILIES"]))
{
pngeneral.AutomaticSmilies = (bool)r["AUTOMATIC_SMILIES"];
}
if (!IsDBNull(r["SPACE_POINTS"]))
{
pngeneral.SpacePoints = (int)r["SPACE_POINTS"];
}
if (!IsDBNull(r["RESTORE_AUTO"]))
{
pngeneral.RestoreAuto = (bool)r["RESTORE_AUTO"];
}
if (!IsDBNull(r["PARAGRAPH_INDENT"]))
{
pngeneral.ParagraphIndent = (int)r["PARAGRAPH_INDENT"];
}
if (!IsDBNull(r["AUTO_HEIGHT"]))
{
pngeneral.AutoHeight = (bool)r["AUTO_HEIGHT"];
}
if (!IsDBNull(r["CRITICAL_ON_START"]))
{
pngeneral.CheckCriticalOnStart = (bool)r["CRITICAL_ON_START"];
}
if (!IsDBNull(r["CRITICAL_PERIODICALLY"]))
{
pngeneral.CheckCriticalPeriodically = (bool)r["CRITICAL_PERIODICALLY"];
}
if (!IsDBNull(r["DELETE_SHORTCUTS_ON_EXIT"]))
{
pngeneral.DeleteShortcutsOnExit = (bool)r["DELETE_SHORTCUTS_ON_EXIT"];
}
if (!IsDBNull(r["RESTORE_SHORTCUTS_ON_START"]))
{
pngeneral.RestoreShortcutsOnStart = (bool)r["RESTORE_SHORTCUTS_ON_START"];
}
if (!IsDBNull(r["CLOSE_ON_SHORTCUT"]))
{
pngeneral.CloseOnShortcut = (bool)r["CLOSE_ON_SHORTCUT"];
}
}
else
{
sqlQuery = "INSERT INTO GENERAL_SETTINGS VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("load_program_settings", "Loading program settings");
//schedule
if (!oData.TableExists("SCHEDULE"))
{
sqlQuery = "CREATE TABLE [SCHEDULE] ([SOUND] TEXT, [DATE_FORMAT] TEXT, [TIME_FORMAT] TEXT, [VOICE] TEXT, [ALLOW_SOUND] BOOLEAN, [TRACK_OVERDUE] BOOLEAN, [VISUAL_NOTIFY] BOOLEAN, [CENTER_SCREEN] BOOLEAN, [VOICE_VOLUME] INT, [VOICE_SPEED] INT, [VOICE_PITCH] INT)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO SCHEDULE VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
sqlQuery = "SELECT * FROM SCHEDULE";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnsc = PNStatic.Settings.Schedule;
if (!IsDBNull(r["SOUND"]))
{
pnsc.Sound = (string)r["SOUND"];
}
if (!IsDBNull(r["VOICE"]))
{
pnsc.Voice = (string)r["VOICE"];
}
if (!IsDBNull(r["ALLOW_SOUND"]))
{
pnsc.AllowSoundAlert = (bool)r["ALLOW_SOUND"];
}
if (!IsDBNull(r["TRACK_OVERDUE"]))
{
pnsc.TrackOverdue = (bool)r["TRACK_OVERDUE"];
}
if (!IsDBNull(r["VISUAL_NOTIFY"]))
{
pnsc.VisualNotification = (bool)r["VISUAL_NOTIFY"];
}
if (!IsDBNull(r["CENTER_SCREEN"]))
{
pnsc.CenterScreen = (bool)r["CENTER_SCREEN"];
}
if (!IsDBNull(r["VOICE_VOLUME"]))
{
pnsc.VoiceVolume = (int)r["VOICE_VOLUME"];
}
if (!IsDBNull(r["VOICE_SPEED"]))
{
pnsc.VoiceSpeed = (int)r["VOICE_SPEED"];
}
if (!IsDBNull(r["VOICE_PITCH"]))
{
pnsc.VoicePitch = (int)r["VOICE_PITCH"];
}
}
else
{
sqlQuery = "INSERT INTO SCHEDULE VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//behavior
if (!oData.TableExists("BEHAVIOR"))
{
sqlQuery = "CREATE TABLE [BEHAVIOR] ([NEW_ALWAYS_ON_TOP] BOOLEAN, [RELATIONAL_POSITION] BOOLEAN, [HIDE_COMPLETED] BOOLEAN, [BIG_ICONS_ON_CP] BOOLEAN, [DO_NOT_SHOW_IN_LIST] BOOLEAN, [KEEP_VISIBLE_ON_SHOW_DESKTOP] BOOLEAN, [DBL_CLICK_ACTION] INT, [SINGLE_CLICK_ACTION] INT, [DEFAULT_NAMING] INT, [DEFAULT_NAME_LENGHT] INT, [CONTENT_COLUMN_LENGTH] INT, [HIDE_FLUENTLY] BOOLEAN, [PLAY_SOUND_ON_HIDE] BOOLEAN, [OPACITY] REAL, [RANDOM_COLOR] BOOLEAN, [INVERT_TEXT_COLOR] BOOLEAN, [ROLL_ON_DBLCLICK] BOOLEAN, [FIT_WHEN_ROLLED] BOOLEAN, [SHOW_SEPARATE_NOTES] BOOLEAN, [PIN_CLICK_ACTION] INT, [NOTE_START_POSITION] INT, [HIDE_MAIN_WINDOW] BOOLEAN, [THEME] TEXT, [PREVENT_RESIZING] BOOLEAN, [SHOW_PANEL] BOOLEAN, [PANEL_DOCK] INT, [PANEL_AUTO_HIDE] BOOLEAN, [PANEL_REMOVE_MODE] INT, [PANEL_SWITCH_OFF_ANIMATION] BOOLEAN, [PANEL_ENTER_DELAY] INT)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO BEHAVIOR VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'NOTE_START_POSITION' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [NOTE_START_POSITION] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'HIDE_MAIN_WINDOW' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [HIDE_MAIN_WINDOW] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'THEME' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [THEME] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PREVENT_RESIZING' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PREVENT_RESIZING] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'SHOW_PANEL' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [SHOW_PANEL] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_DOCK' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_DOCK] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_AUTO_HIDE' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_AUTO_HIDE] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_REMOVE_MODE' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_REMOVE_MODE] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_SWITCH_OFF_ANIMATION' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_SWITCH_OFF_ANIMATION] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PANEL_ENTER_DELAY' AND TABLE_NAME = 'BEHAVIOR'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_ENTER_DELAY] INT";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM BEHAVIOR";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnb = PNStatic.Settings.Behavior;
if (!IsDBNull(r["NEW_ALWAYS_ON_TOP"]))
{
pnb.NewNoteAlwaysOnTop = (bool)r["NEW_ALWAYS_ON_TOP"];
}
if (!IsDBNull(r["RELATIONAL_POSITION"]))
{
pnb.RelationalPositioning = (bool)r["RELATIONAL_POSITION"];
}
if (!IsDBNull(r["HIDE_COMPLETED"]))
{
pnb.HideCompleted = (bool)r["HIDE_COMPLETED"];
}
if (!IsDBNull(r["BIG_ICONS_ON_CP"]))
{
pnb.BigIconsOnCP = (bool)r["BIG_ICONS_ON_CP"];
}
if (!IsDBNull(r["DO_NOT_SHOW_IN_LIST"]))
{
pnb.DoNotShowNotesInList = (bool)r["DO_NOT_SHOW_IN_LIST"];
}
if (!IsDBNull(r["KEEP_VISIBLE_ON_SHOW_DESKTOP"]))
{
pnb.KeepVisibleOnShowDesktop = (bool)r["KEEP_VISIBLE_ON_SHOW_DESKTOP"];
}
if (!IsDBNull(r["DBL_CLICK_ACTION"]))
{
var index = (int) r["DBL_CLICK_ACTION"];
if (index < Enum.GetValues(typeof (TrayMouseAction)).Length)
pnb.DoubleClickAction = (TrayMouseAction) index;
}
if (!IsDBNull(r["SINGLE_CLICK_ACTION"]))
{
var index = (int)r["SINGLE_CLICK_ACTION"];
if (index < Enum.GetValues(typeof (TrayMouseAction)).Length)
pnb.SingleClickAction = (TrayMouseAction) index;
}
if (!IsDBNull(r["DEFAULT_NAMING"]))
{
var index = (int)r["DEFAULT_NAMING"];
if (index < Enum.GetValues(typeof(DefaultNaming)).Length)
pnb.DefaultNaming = (DefaultNaming)index;
}
if (!IsDBNull(r["DEFAULT_NAME_LENGHT"]))
{
pnb.DefaultNameLength = (int)r["DEFAULT_NAME_LENGHT"];
}
if (!IsDBNull(r["CONTENT_COLUMN_LENGTH"]))
{
pnb.ContentColumnLength = (int)r["CONTENT_COLUMN_LENGTH"];
}
if (!IsDBNull(r["HIDE_FLUENTLY"]))
{
pnb.HideFluently = (bool)r["HIDE_FLUENTLY"];
}
if (!IsDBNull(r["PLAY_SOUND_ON_HIDE"]))
{
pnb.PlaySoundOnHide = (bool)r["PLAY_SOUND_ON_HIDE"];
}
if (!IsDBNull(r["OPACITY"]))
{
pnb.Opacity = (double)r["OPACITY"];
}
if (!IsDBNull(r["RANDOM_COLOR"]))
{
pnb.RandomBackColor = (bool)r["RANDOM_COLOR"];
}
if (!IsDBNull(r["INVERT_TEXT_COLOR"]))
{
pnb.InvertTextColor = (bool)r["INVERT_TEXT_COLOR"];
}
if (!IsDBNull(r["ROLL_ON_DBLCLICK"]))
{
pnb.RollOnDblClick = (bool)r["ROLL_ON_DBLCLICK"];
}
if (!IsDBNull(r["FIT_WHEN_ROLLED"]))
{
pnb.FitWhenRolled = (bool)r["FIT_WHEN_ROLLED"];
}
if (!IsDBNull(r["SHOW_SEPARATE_NOTES"]))
{
pnb.ShowSeparateNotes = (bool)r["SHOW_SEPARATE_NOTES"];
}
if (!IsDBNull(r["PIN_CLICK_ACTION"]))
{
var index = (int)r["PIN_CLICK_ACTION"];
if (index < Enum.GetValues(typeof(PinClickAction)).Length)
pnb.PinClickAction = (PinClickAction)index;
}
if (!IsDBNull(r["NOTE_START_POSITION"]))
{
var index = (int)r["NOTE_START_POSITION"];
if (index < Enum.GetValues(typeof(NoteStartPosition)).Length)
pnb.StartPosition = (NoteStartPosition)index;
}
if (!IsDBNull(r["HIDE_MAIN_WINDOW"]))
{
pnb.HideMainWindow = (bool)r["HIDE_MAIN_WINDOW"];
}
if (!IsDBNull(r["THEME"]))
{
pnb.Theme = (string) r["THEME"];
}
else
{
pnb.Theme = PNStrings.DEF_THEME;
}
if (!IsDBNull(r["PREVENT_RESIZING"]))
{
pnb.PreventAutomaticResizing = (bool)r["PREVENT_RESIZING"];
}
if (!IsDBNull(r["SHOW_PANEL"]))
{
pnb.ShowNotesPanel = (bool)r["SHOW_PANEL"];
}
if (!IsDBNull(r["PANEL_DOCK"]))
{
pnb.NotesPanelOrientation = (NotesPanelOrientation)r["PANEL_DOCK"];
}
if (!IsDBNull(r["PANEL_AUTO_HIDE"]))
{
pnb.PanelAutoHide = (bool)r["PANEL_AUTO_HIDE"];
}
if (!IsDBNull(r["PANEL_REMOVE_MODE"]))
{
pnb.PanelRemoveMode = (PanelRemoveMode)r["PANEL_REMOVE_MODE"];
}
if (!IsDBNull(r["PANEL_SWITCH_OFF_ANIMATION"]))
{
pnb.PanelSwitchOffAnimation = (bool)r["PANEL_SWITCH_OFF_ANIMATION"];
}
if (!IsDBNull(r["PANEL_ENTER_DELAY"]))
{
pnb.PanelEnterDelay = (int)r["PANEL_ENTER_DELAY"];
}
}
else
{
sqlQuery = "INSERT INTO BEHAVIOR VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//protection
if (!oData.TableExists("PROTECTION"))
{
sqlQuery = "CREATE TABLE [PROTECTION] ([STORE_AS_ENCRYPTED] BOOLEAN, [HIDE_TRAY_ICON] BOOLEAN, [BACKUP_BEFORE_SAVING] BOOLEAN, [SILENT_FULL_BACKUP] BOOLEAN, [BACKUP_DEEPNESS] INT, [DO_NOT_SHOW_CONTENT] BOOLEAN, [INCLUDE_BIN_IN_SYNC] BOOLEAN, [PASSWORD_STRING] TEXT, [FULL_BACKUP_DAYS] TEXT, [FULL_BACKUP_TIME] TEXT, [FULL_BACKUP_DATE] TEXT, [PROMPT_PASSWORD] BOOLEAN)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO PROTECTION VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_DAYS' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_DAYS] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_TIME' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_TIME] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_DATE' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_DATE] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'PROMPT_PASSWORD' AND TABLE_NAME = 'PROTECTION'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [PROMPT_PASSWORD] BOOLEAN";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM PROTECTION";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnp = PNStatic.Settings.Protection;
if (!IsDBNull(r["STORE_AS_ENCRYPTED"]))
{
pnp.StoreAsEncrypted = (bool)r["STORE_AS_ENCRYPTED"];
}
if (!IsDBNull(r["HIDE_TRAY_ICON"]))
{
pnp.HideTrayIcon = (bool)r["HIDE_TRAY_ICON"];
}
if (!IsDBNull(r["BACKUP_BEFORE_SAVING"]))
{
pnp.BackupBeforeSaving = (bool)r["BACKUP_BEFORE_SAVING"];
}
if (!IsDBNull(r["SILENT_FULL_BACKUP"]))
{
pnp.SilentFullBackup = (bool)r["SILENT_FULL_BACKUP"];
}
if (!IsDBNull(r["BACKUP_DEEPNESS"]))
{
pnp.BackupDeepness = (int)r["BACKUP_DEEPNESS"];
}
if (!IsDBNull(r["DO_NOT_SHOW_CONTENT"]))
{
pnp.DontShowContent = (bool)r["DO_NOT_SHOW_CONTENT"];
}
if (!IsDBNull(r["INCLUDE_BIN_IN_SYNC"]))
{
pnp.IncludeBinInSync = (bool)r["INCLUDE_BIN_IN_SYNC"];
}
if (!IsDBNull(r["PASSWORD_STRING"]))
{
pnp.PasswordString = (string)r["PASSWORD_STRING"];
}
if (!IsDBNull(r["FULL_BACKUP_DAYS"]))
{
var temp = Convert.ToString(r["FULL_BACKUP_DAYS"]);
if (!string.IsNullOrEmpty(temp))
{
var days = temp.Split(',');
foreach (var d in days)
{
pnp.FullBackupDays.Add((DayOfWeek)Convert.ToInt32(d));
}
}
}
if (!IsDBNull(r["FULL_BACKUP_TIME"]))
{
pnp.FullBackupTime = DateTime.Parse((string)r["FULL_BACKUP_TIME"], PNStatic.CultureInvariant);
}
if (!IsDBNull(r["FULL_BACKUP_DATE"]))
{
pnp.FullBackupDate = DateTime.Parse((string)r["FULL_BACKUP_DATE"], PNStatic.CultureInvariant);
}
if (!IsDBNull(r["PROMPT_PASSWORD"]))
{
pnp.PromptForPassword = (bool)r["PROMPT_PASSWORD"];
}
}
else
{
sqlQuery = "INSERT INTO PROTECTION VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//diary
if (!oData.TableExists("DIARY"))
{
sqlQuery = "CREATE TABLE [DIARY] ([CUSTOM_SETTINGS] BOOLEAN, [ADD_WEEKDAY] BOOLEAN, [FULL_WEEKDAY_NAME] BOOLEAN, [WEEKDAY_AT_THE_END] BOOLEAN, [DO_NOT_SHOW_PREVIOUS] BOOLEAN, [ASC_ORDER] BOOLEAN, [NUMBER_OF_PAGES] INT, [DATE_FORMAT] TEXT)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO DIARY VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
sqlQuery = "SELECT * FROM DIARY";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pndr = PNStatic.Settings.Diary;
if (!IsDBNull(r["CUSTOM_SETTINGS"]))
{
pndr.CustomSettings = (bool)r["CUSTOM_SETTINGS"];
}
if (!IsDBNull(r["ADD_WEEKDAY"]))
{
pndr.AddWeekday = (bool)r["ADD_WEEKDAY"];
}
if (!IsDBNull(r["FULL_WEEKDAY_NAME"]))
{
pndr.FullWeekdayName = (bool)r["FULL_WEEKDAY_NAME"];
}
if (!IsDBNull(r["WEEKDAY_AT_THE_END"]))
{
pndr.WeekdayAtTheEnd = (bool)r["WEEKDAY_AT_THE_END"];
}
if (!IsDBNull(r["DO_NOT_SHOW_PREVIOUS"]))
{
pndr.DoNotShowPrevious = (bool)r["DO_NOT_SHOW_PREVIOUS"];
}
if (!IsDBNull(r["ASC_ORDER"]))
{
pndr.AscendingOrder = (bool)r["ASC_ORDER"];
}
if (!IsDBNull(r["NUMBER_OF_PAGES"]))
{
pndr.NumberOfPages = (int)r["NUMBER_OF_PAGES"];
}
if (!IsDBNull(r["DATE_FORMAT"]))
{
pndr.DateFormat = (string)r["DATE_FORMAT"];
}
}
else
{
sqlQuery = "INSERT INTO DIARY VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
//network
if (!oData.TableExists("NETWORK"))
{
sqlQuery = "CREATE TABLE [NETWORK] ([INCLUDE_BIN_IN_SYNC] BOOLEAN, [SYNC_ON_START] BOOLEAN, [SAVE_BEFORE_SYNC] BOOLEAN, [ENABLE_EXCHANGE] BOOLEAN, [SAVE_BEFORE_SEND] BOOLEAN, [NO_NOTIFY_ON_ARRIVE] BOOLEAN, [SHOW_RECEIVED_ON_CLICK] BOOLEAN, [SHOW_INCOMING_ON_CLICK] BOOLEAN, [NO_SOUND_ON_ARRIVE] BOOLEAN, [NO_NOTIFY_ON_SEND] BOOLEAN, [SHOW_AFTER_ARRIVE] BOOLEAN, [HIDE_AFTER_SEND] BOOLEAN, [NO_CONTACTS_IN_CONTEXT_MENU] BOOLEAN, [EXCHANGE_PORT] INT, [POST_COUNT] INT, [ALLOW_PING] BOOLEAN, [RECEIVED_ON_TOP] BOOLEAN)";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO NETWORK VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'POST_COUNT' AND TABLE_NAME = 'NETWORK'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [POST_COUNT] INT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'ALLOW_PING' AND TABLE_NAME = 'NETWORK'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [ALLOW_PING] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RECEIVED_ON_TOP' AND TABLE_NAME = 'NETWORK'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [RECEIVED_ON_TOP] BOOLEAN";
oData.Execute(sqlQuery);
}
}
sqlQuery = "SELECT * FROM NETWORK";
using (var t = oData.FillDataTable(sqlQuery))
{
if (t.Rows.Count > 0)
{
var r = t.Rows[0];
var pnw = PNStatic.Settings.Network;
if (!IsDBNull(r["INCLUDE_BIN_IN_SYNC"]))
{
pnw.IncludeBinInSync = (bool)r["INCLUDE_BIN_IN_SYNC"];
}
if (!IsDBNull(r["SYNC_ON_START"]))
{
pnw.SyncOnStart = (bool)r["SYNC_ON_START"];
}
if (!IsDBNull(r["SAVE_BEFORE_SYNC"]))
{
pnw.SaveBeforeSync = (bool)r["SAVE_BEFORE_SYNC"];
}
if (!IsDBNull(r["ENABLE_EXCHANGE"]))
{
pnw.EnableExchange = (bool)r["ENABLE_EXCHANGE"];
}
if (!IsDBNull(r["SAVE_BEFORE_SEND"]))
{
pnw.SaveBeforeSending = (bool)r["SAVE_BEFORE_SEND"];
}
if (!IsDBNull(r["NO_NOTIFY_ON_ARRIVE"]))
{
pnw.NoNotificationOnArrive = (bool)r["NO_NOTIFY_ON_ARRIVE"];
}
if (!IsDBNull(r["SHOW_RECEIVED_ON_CLICK"]))
{
pnw.ShowReceivedOnClick = (bool)r["SHOW_RECEIVED_ON_CLICK"];
}
if (!IsDBNull(r["SHOW_INCOMING_ON_CLICK"]))
{
pnw.ShowIncomingOnClick = (bool)r["SHOW_INCOMING_ON_CLICK"];
}
if (!IsDBNull(r["NO_SOUND_ON_ARRIVE"]))
{
pnw.NoSoundOnArrive = (bool)r["NO_SOUND_ON_ARRIVE"];
}
if (!IsDBNull(r["NO_NOTIFY_ON_SEND"]))
{
pnw.NoNotificationOnSend = (bool)r["NO_NOTIFY_ON_SEND"];
}
if (!IsDBNull(r["SHOW_AFTER_ARRIVE"]))
{
pnw.ShowAfterArrive = (bool)r["SHOW_AFTER_ARRIVE"];
}
if (!IsDBNull(r["HIDE_AFTER_SEND"]))
{
pnw.HideAfterSending = (bool)r["HIDE_AFTER_SEND"];
}
if (!IsDBNull(r["NO_CONTACTS_IN_CONTEXT_MENU"]))
{
pnw.NoContactsInContextMenu = (bool)r["NO_CONTACTS_IN_CONTEXT_MENU"];
}
if (!IsDBNull(r["EXCHANGE_PORT"]))
{
pnw.ExchangePort = (int)r["EXCHANGE_PORT"];
}
if (!IsDBNull(r["POST_COUNT"]))
{
pnw.PostCount = (int)r["POST_COUNT"];
}
if (!IsDBNull(r["ALLOW_PING"]))
{
pnw.AllowPing = (bool)r["ALLOW_PING"];
}
if (!IsDBNull(r["RECEIVED_ON_TOP"]))
{
pnw.ReceivedOnTop = (bool)r["RECEIVED_ON_TOP"];
}
}
else
{
sqlQuery = "INSERT INTO NETWORK VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
oData.Execute(sqlQuery);
}
}
}
}
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}
private void loadDatabase()
{
try
{
if (!File.Exists(PNPaths.Instance.DBPath))
{
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("create_database", "Creating database");
_FirstRun = true;
}
else
{
PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("load_database", "Loading database");
}
PNData.ConnectionString = SQLiteDataObject.CheckAndCreateDatabase(PNPaths.Instance.DBPath);
if (PNData.ConnectionString != "")
{
using (var oData = new SQLiteDataObject(PNData.ConnectionString))
{
//notes
var sqlQuery = "CREATE TABLE IF NOT EXISTS [NOTES] ([ID] TEXT PRIMARY KEY NOT NULL, [NAME] TEXT NOT NULL, [GROUP_ID] INT NOT NULL, [PREV_GROUP_ID] INT, [OPACITY] REAL, [VISIBLE] BOOLEAN, [FAVORITE] BOOLEAN, [PROTECTED] BOOLEAN, [COMPLETED] BOOLEAN, [PRIORITY] BOOLEAN, [PASSWORD_STRING] TEXT, [PINNED] BOOLEAN, [TOPMOST] BOOLEAN, [ROLLED] BOOLEAN, [DOCK_STATUS] INT, [DOCK_ORDER] INT, [SEND_RECEIVE_STATUS] INT, [DATE_CREATED] TEXT, [DATE_SAVED] TEXT, [DATE_SENT] TEXT, [DATE_RECEIVED] TEXT, [DATE_DELETED] TEXT, [SIZE] TEXT, [LOCATION] TEXT, [EDIT_SIZE] TEXT, [REL_X] REAL, [REL_Y] REAL, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )), [SENT_TO] TEXT, [RECEIVED_FROM] TEXT, [PIN_CLASS] TEXT, [PIN_TEXT] TEXT, [SCRAMBLED] BOOLEAN DEFAULT (0), [THUMBNAIL] BOOLEAN DEFAULT (0), [RECEIVED_IP] TEXT)";
oData.Execute(sqlQuery);
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'SCRAMBLED' AND TABLE_NAME = 'NOTES'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES ADD COLUMN [SCRAMBLED] BOOLEAN DEFAULT (0)";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE NOTES SET SCRAMBLED = 0";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'THUMBNAIL' AND TABLE_NAME = 'NOTES'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES ADD COLUMN [THUMBNAIL] BOOLEAN DEFAULT (0)";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE NOTES SET THUMBNAIL = 0";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'RECEIVED_IP' AND TABLE_NAME = 'NOTES'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES ADD COLUMN [RECEIVED_IP] TEXT";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE NOTES SET THUMBNAIL = 0";
oData.Execute(sqlQuery);
}
}
if (!PNSingleton.Instance.PlatformChanged)
{
//custom notes settings
sqlQuery =
"CREATE TABLE IF NOT EXISTS [CUSTOM_NOTES_SETTINGS] ([NOTE_ID] TEXT NOT NULL, [BACK_COLOR] TEXT, [CAPTION_FONT_COLOR] TEXT, [CAPTION_FONT] TEXT, [SKIN_NAME] TEXT, [CUSTOM_OPACITY] BOOLEAN, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
}
else
{
if (oData.TableExists("CUSTOM_NOTES_SETTINGS"))
{
PNData.NormalizeCustomNotesTable(oData);
}
else
{
sqlQuery =
"CREATE TABLE [CUSTOM_NOTES_SETTINGS] ([NOTE_ID] TEXT NOT NULL, [BACK_COLOR] TEXT, [CAPTION_FONT_COLOR] TEXT, [CAPTION_FONT] TEXT, [SKIN_NAME] TEXT, [CUSTOM_OPACITY] BOOLEAN, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
}
}
//notes tags
sqlQuery = "CREATE TABLE IF NOT EXISTS [NOTES_TAGS] ([NOTE_ID] TEXT NOT NULL, [TAG] TEXT NOT NULL, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
//notes schedule
sqlQuery = "CREATE TABLE IF NOT EXISTS [NOTES_SCHEDULE] ([NOTE_ID] TEXT NOT NULL, [SCHEDULE_TYPE] INT, [ALARM_DATE] TEXT, [START_DATE] TEXT, [LAST_RUN] TEXT, [SOUND] TEXT, [STOP_AFTER] INT, [TRACK] BOOLEAN, [REPEAT_COUNT] INT, [SOUND_IN_LOOP] BOOLEAN, [USE_TTS] BOOLEAN, [START_FROM] INT, [MONTH_DAY] TEXT, [ALARM_AFTER] TEXT, [WEEKDAYS] TEXT, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )), [PROG_TO_RUN] TEXT, [CLOSE_ON_NOTIFICATION] BOOLEAN)";
oData.Execute(sqlQuery);
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'PROG_TO_RUN' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [PROG_TO_RUN] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'CLOSE_ON_NOTIFICATION' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [CLOSE_ON_NOTIFICATION] BOOLEAN";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'MULTI_ALERTS' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [MULTI_ALERTS] TEXT";
oData.Execute(sqlQuery);
}
rows = t.Select("COLUMN_NAME = 'TIME_ZONE' AND TABLE_NAME = 'NOTES_SCHEDULE'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE NOTES_SCHEDULE ADD COLUMN [TIME_ZONE] TEXT";
oData.Execute(sqlQuery);
}
}
//linked notes
sqlQuery = "CREATE TABLE IF NOT EXISTS [LINKED_NOTES] ([NOTE_ID] TEXT NOT NULL, [LINK_ID] TEXT NOT NULL, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )))";
oData.Execute(sqlQuery);
//groups
if (!oData.TableExists("GROUPS"))
{
sqlQuery =
"CREATE TABLE [GROUPS] ([GROUP_ID] INT PRIMARY KEY NOT NULL UNIQUE, [PARENT_ID] INT, [GROUP_NAME] TEXT NOT NULL, [ICON] TEXT, [BACK_COLOR] TEXT NOT NULL, [CAPTION_FONT_COLOR] TEXT NOT NULL, [CAPTION_FONT] TEXT NOT NULL, [SKIN_NAME] TEXT NOT NULL, [PASSWORD_STRING] TEXT, [FONT] TEXT, [FONT_COLOR] TEXT, [UPD_DATE] TEXT DEFAULT(strftime ( '%Y%m%d%H%M%S' , 'now' , 'localtime' )), [IS_DEFAULT_IMAGE] BOOLEAN)";
oData.Execute(sqlQuery);
var sb = new StringBuilder();
sb.Append(prepareGroupInsert((int)SpecialGroups.AllGroups, (int)SpecialGroups.DummyGroup,
"All groups", "gr_all.png"));
sb.Append(prepareGroupInsert(0, (int)SpecialGroups.AllGroups, "General", "gr.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.RecycleBin, (int)SpecialGroups.DummyGroup,
"Recycle Bin", "gr_bin.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Diary, (int)SpecialGroups.DummyGroup,
"Diary", "gr_diary.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.SearchResults,
(int)SpecialGroups.DummyGroup, "Search Results", "gr_search.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Backup, (int)SpecialGroups.DummyGroup,
"Backup", "gr_back.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Favorites, (int)SpecialGroups.DummyGroup,
"Favorites", "gr_fav.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Incoming, (int)SpecialGroups.DummyGroup,
"Incoming", "gr_inc.png"));
sb.Append(prepareGroupInsert((int)SpecialGroups.Docking, (int)SpecialGroups.DummyGroup,
"Docking", "dockall.png"));
sqlQuery = sb.ToString();
oData.Execute(sqlQuery);
}
else
{
using (var t = oData.GetSchema("Columns"))
{
var rows = t.Select("COLUMN_NAME = 'IS_DEFAULT_IMAGE' AND TABLE_NAME = 'GROUPS'");
if (rows.Length == 0)
{
sqlQuery = "ALTER TABLE GROUPS ADD COLUMN [IS_DEFAULT_IMAGE] BOOLEAN";
oData.Execute(sqlQuery);
sqlQuery = "UPDATE GROUPS SET IS_DEFAULT_IMAGE = 1";
oData.Execute(sqlQuery);
}
}
if (PNSingleton.Instance.PlatformChanged)
{
PNData.NormalizeGroupsTable(oData, false);
upgradeGroups(oData);
}
}
var groupsWithoutParent = new List<PNGroup>();
sqlQuery = "SELECT GROUP_ID, PARENT_ID, GROUP_NAME, ICON, BACK_COLOR, CAPTION_FONT_COLOR, CAPTION_FONT, SKIN_NAME, PASSWORD_STRING, FONT, FONT_COLOR, IS_DEFAULT_IMAGE FROM GROUPS ORDER BY GROUP_ID ASC";
using (var t = oData.FillDataTable(sqlQuery))
{
foreach (DataRow r in t.Rows)
{
if ((int)r["GROUP_ID"] < 0)
{
if ((int)r["GROUP_ID"] != (int)SpecialGroups.Docking)
{
var group = new PNGroup();
fillGroup(group, r);
PNStatic.Groups.Insert(0, group);
}
else
{
fillGroup(PNStatic.Docking, r);
}
}
else
{
var gr = new PNGroup();
fillGroup(gr, r);
var parentExists = PNStatic.Groups.Select(grp => grp.GetGroupById(gr.ParentID)).Any(pg => pg != null);
if (!parentExists)
{
groupsWithoutParent.Add(gr);
continue;
}
foreach (var parent in PNStatic.Groups.Select(g => g.GetGroupById(gr.ParentID)).Where(parent => parent != null))
{
parent.Subgroups.Add(gr);
break;
}
}
}
}
while (groupsWithoutParent.Count > 0)
{
for (var i = groupsWithoutParent.Count - 1; i >= 0; i--)
{
if (PNStatic.Groups.Select(grp => grp.GetGroupById(groupsWithoutParent[i].ParentID)).All(pg => pg == null))
continue;
var i1 = i;
foreach (
var parent in
PNStatic.Groups.Select(g => g.GetGroupById(groupsWithoutParent[i1].ParentID))
.Where(parent => parent != null))
{
parent.Subgroups.Add(groupsWithoutParent[i]);
groupsWithoutParent.RemoveAt(i);
break;
}
}
}
applyStandardGroupsNames();
//sync comps
sqlQuery = "CREATE TABLE IF NOT EXISTS [SYNC_COMPS] ([COMP_NAME] TEXT NOT NULL, [DATA_DIR] TEXT NOT NULL, [DB_DIR] TEXT NOT NULL, [USE_DATA_DIR] TEXT NOT NULL)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM SYNC_COMPS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SyncComps.Add(new PNSyncComp
{
CompName = (string)r["COMP_NAME"],
DataDir = (string)r["DATA_DIR"],
DBDir = (string)r["DB_DIR"],
UseDataDir = bool.Parse((string)r["USE_DATA_DIR"])
});
}
}
//contact groups
sqlQuery = "CREATE TABLE IF NOT EXISTS [CONTACT_GROUPS] ([ID] INT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [GROUP_NAME] TEXT NOT NULL UNIQUE ON CONFLICT REPLACE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM CONTACT_GROUPS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.ContactGroups.Add(new PNContactGroup { ID = (int)r["ID"], Name = (string)r["GROUP_NAME"] });
}
}
//contacts
sqlQuery = "CREATE TABLE IF NOT EXISTS [CONTACTS] ([ID] INT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [GROUP_ID] INT NOT NULL, [CONTACT_NAME] TEXT NOT NULL UNIQUE ON CONFLICT REPLACE, [COMP_NAME] TEXT NOT NULL, [IP_ADDRESS] TEXT NOT NULL, [USE_COMP_NAME] BOOLEAN NOT NULL)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT ID, GROUP_ID, CONTACT_NAME, COMP_NAME, IP_ADDRESS, USE_COMP_NAME FROM CONTACTS"))
{
foreach (var cont in from DataRow r in t.Rows
select new PNContact
{
Name = (string)r["CONTACT_NAME"],
ComputerName = (string)r["COMP_NAME"],
IpAddress = (string)r["IP_ADDRESS"],
GroupID = (int)r["GROUP_ID"],
UseComputerName = (bool)r["USE_COMP_NAME"],
ID = (int)r["ID"]
})
{
PNStatic.Contacts.Add(cont);
}
Task.Factory.StartNew(updateContactsWithoutIp);
}
//externals
sqlQuery = "CREATE TABLE IF NOT EXISTS [EXTERNALS] ([EXT_NAME] TEXT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [PROGRAM] TEXT NOT NULL, [COMMAND_LINE] TEXT NOT NULL)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT EXT_NAME, PROGRAM, COMMAND_LINE FROM EXTERNALS "))
{
foreach (DataRow r in t.Rows)
{
PNStatic.Externals.Add(new PNExternal
{
Name = (string)r["EXT_NAME"],
Program = (string)r["PROGRAM"],
CommandLine = (string)r["COMMAND_LINE"]
});
}
}
//search providers
if (!oData.TableExists("SEARCH_PROVIDERS"))
{
sqlQuery = "CREATE TABLE [SEARCH_PROVIDERS] ([SP_NAME] TEXT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE, [SP_QUERY] TEXT NOT NULL)";
oData.Execute(sqlQuery);
//insert two default provider for the first time
sqlQuery = "INSERT INTO SEARCH_PROVIDERS VALUES('Google', 'http://www.google.com/search?q=')";
oData.Execute(sqlQuery);
sqlQuery = "INSERT INTO SEARCH_PROVIDERS VALUES('Yahoo', 'http://search.yahoo.com/search?p=')";
oData.Execute(sqlQuery);
}
using (var t = oData.FillDataTable("SELECT * FROM SEARCH_PROVIDERS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SearchProviders.Add(new PNSearchProvider
{
Name = (string)r["SP_NAME"],
QueryString = (string)r["SP_QUERY"]
});
}
}
//tags
sqlQuery = "CREATE TABLE IF NOT EXISTS [TAGS] ([TAG] TEXT PRIMARY KEY ON CONFLICT REPLACE NOT NULL UNIQUE ON CONFLICT REPLACE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM TAGS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.Tags.Add((string)r["TAG"]);
}
}
//plugins
sqlQuery = "CREATE TABLE IF NOT EXISTS [POST_PLUGINS] ([PLUGIN] TEXT PRIMARY KEY NOT NULL UNIQUE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM POST_PLUGINS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.PostPlugins.Add((string)r["PLUGIN"]);
}
}
sqlQuery = "CREATE TABLE IF NOT EXISTS [SYNC_PLUGINS] ([PLUGIN] TEXT PRIMARY KEY NOT NULL UNIQUE)";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM SYNC_PLUGINS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SyncPlugins.Add((string)r["PLUGIN"]);
}
}
//hotkeys
sqlQuery = "CREATE TABLE IF NOT EXISTS [HOT_KEYS] ([HK_TYPE] INT NOT NULL, [MENU_NAME] TEXT NOT NULL, [ID] INT NOT NULL DEFAULT(0), [MODIFIERS] INT NOT NULL DEFAULT(0), [VK] INT NOT NULL DEFAULT(0), [SHORTCUT] TEXT NOT NULL DEFAULT(''), PRIMARY KEY ([HK_TYPE], [MENU_NAME]))";
oData.Execute(sqlQuery);
prepareHotkeysTable(oData);
//find/replace
if (!oData.TableExists("FIND_REPLACE"))
{
sqlQuery = "CREATE TABLE [FIND_REPLACE] ([FIND] TEXT, [REPLACE] TEXT); ";
sqlQuery += "INSERT INTO FIND_REPLACE VALUES(NULL, NULL)";
}
oData.Execute(sqlQuery);
//remove possible program version table from previous versions
if (oData.TableExists("PROGRAM_VERSION"))
{
sqlQuery = "DROP TABLE [PROGRAM_VERSION]; ";
oData.Execute(sqlQuery);
}
//hidden menus
sqlQuery = "CREATE TABLE IF NOT EXISTS [HIDDEN_MENUS] ([MENU_NAME] TEXT NOT NULL, [MENU_TYPE] INT NOT NULL, PRIMARY KEY ([MENU_NAME], [MENU_TYPE]))";
oData.Execute(sqlQuery);
PNData.LoadHiddenMenus();
//menus order
if (!oData.TableExists("MENUS_ORDER"))
{
sqlQuery =
"CREATE TABLE [MENUS_ORDER] ([CONTEXT_NAME] TEXT NOT NULL, [MENU_NAME] TEXT NOT NULL, [ORDER_ORIGINAL] INT NOT NULL, [ORDER_NEW] INT NOT NULL, [PARENT_NAME] TEXT, PRIMARY KEY ( [CONTEXT_NAME], [MENU_NAME]));";
oData.Execute(sqlQuery);
createMenusOrder(oData, true);
}
else
{
createMenusOrder(oData, false);
}
//SMTP profiles
sqlQuery =
"CREATE TABLE IF NOT EXISTS [SMTP_PROFILES] ([ID] INT UNIQUE ON CONFLICT REPLACE, [ACTIVE] BOOLEAN, [HOST_NAME] TEXT, [DISPLAY_NAME] TEXT, [SENDER_ADDRESS] TEXT PRIMARY KEY UNIQUE, [PASSWORD] TEXT, [PORT] INT )";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM SMTP_PROFILES"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.SmtpProfiles.Add(new PNSmtpProfile
{
Active = Convert.ToBoolean(r["ACTIVE"]),
Id = Convert.ToInt32(r["ID"]),
HostName = Convert.ToString(r["HOST_NAME"]),
SenderAddress = Convert.ToString(r["SENDER_ADDRESS"]),
Password = Convert.ToString(r["PASSWORD"]),
Port = Convert.ToInt32(r["PORT"]),
DisplayName = Convert.ToString(r["DISPLAY_NAME"])
});
}
}
//mail contacts
sqlQuery = "CREATE TABLE IF NOT EXISTS [MAIL_CONTACTS] ([ID] INT UNIQUE ON CONFLICT REPLACE, [DISPLAY_NAME] TEXT, [ADDRESS] TEXT )";
oData.Execute(sqlQuery);
using (var t = oData.FillDataTable("SELECT * FROM MAIL_CONTACTS"))
{
foreach (DataRow r in t.Rows)
{
PNStatic.MailContacts.Add(new PNMailContact
{
DisplayName = Convert.ToString(r["DISPLAY_NAME"]),
Address = Convert.ToString(r["ADDRESS"]),
Id = Convert.ToInt32(r["ID"]),
});
}
}
//services
sqlQuery =
"CREATE TABLE IF NOT EXISTS SERVICES ( APP_NAME TEXT PRIMARY KEY, CLIENT_ID TEXT, CLIENT_SECRET TEXT, ACCESS_TOKEN TEXT, REFRESH_TOKEN TEXT, TOKEN_EXPIRY TEXT )";
oData.Execute(sqlQuery);
prepareServices(oData);
//triggers
sqlQuery = PNStrings.CREATE_TRIGGERS;
oData.Execute(sqlQuery);
}
}
//load plugins
PNPlugins.Instance.LoadPlugins(this, PNPaths.Instance.PluginsDir);
}
catch (Exception ex)
{
PNStatic.LogException(ex);
}
}