//***********************************************************************************
static public void SQLITE_FORMAT_QUERY()
{
using (XwDbCommand sql = new XwDbCommand("DBCONN_LITETEST"))
{
if (!sql.TableExists("Tester"))
{
sql.ExecuteTX(@"CREATE TABLE [Tester] (
[id] TEXT,
[client] INTEGER,
[objid] INTEGER,
[objtype] TEXT,
[mediatype] TEXT,
[mediaext] TEXT,
PRIMARY KEY (id,objtype,mediatype,client,objid)
);");
}
/*
* sql.AddParameter("@Name", "%A%");
* sql.ExecuteTX("SELECT * FROM Tester WHERE Name LIKE @Name");
* Console.WriteLine(sql.GetTextCommand());
* while (sql.Read())
* {
* Console.WriteLine(sql.Value("Name").ToString());
* }
*/
}
}
//**************************************************************************************************
//This could be condensed in fewer commands, but would breake compatibility with previous versions
private void UpgradeConfigDB()
{
//Get password ?!?
if (File.Exists(GetDBPath()))
{
using (XwDbCommand tryOne = new XwDbCommand(GetConnectionString(), "Data.SQLite"))
{
try
{
tryOne.ExecuteTX("SELECT * FROM System");
}
catch (Exception ex1)
{
if (ex1.Message.Contains("file is not a database"))
{
AskPassword ask = new AskPassword();
ask.ShowDialog();
Config.MasterPassword = ask.PasswordBox.Text;
using (XwDbCommand tryTwo = new XwDbCommand(GetConnectionString(), "Data.SQLite"))
{
try
{
tryTwo.ExecuteTX("SELECT * FROM System");
}
catch (Exception ex2)
{
if (ex2.Message.Contains("file is not a database"))
{
MessageBox.Show("Bad password or config is corrupted.", "XwRemote", MessageBoxButtons.OK, MessageBoxIcon.Stop);
Environment.Exit(1);
}
}
}
}
}
}
}
using (XwDbCommand sql = new XwDbCommand(GetConnectionString(), "Data.SQLite"))
{
if (!sql.TableExists("System"))
{
sql.ExecuteTX(@"CREATE TABLE [System] (
[SystemKey] TEXT,
[SystemValue] TEXT,
CONSTRAINT [] PRIMARY KEY ([SystemKey]));");
}
if (!sql.TableExists("Groups"))
{
sql.ExecuteTX(@"CREATE TABLE [Groups] (
[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[Name] TEXT,
[Icon] INTEGER);");
}
if (!sql.TableExists("Servers"))
{
sql.ExecuteTX(@"CREATE TABLE [Servers] (
[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[Name] TEXT,
[Host] TEXT,
[Port] INTEGER,
[X] INTEGER,
[Y] INTEGER,
[Username] TEXT,
[Password] TEXT,
[Color] INTEGER,
[SendKeys] BOOLEAN DEFAULT (1),
[GroupID] INTEGER CONSTRAINT [Servers_GroupID] REFERENCES [Groups]([ID]),
[Sound] BOOLEAN,
[Drives] BOOLEAN,
[ServerType] INTEGER,
[Favorite] BOOLEAN,
[Console] BOOLEAN DEFAULT (0),
[AutoScale] BOOLEAN DEFAULT (1),
[SSH1] BOOLEAN DEFAULT (0));");
}
if (!sql.TableExists("Pins"))
{
sql.ExecuteTX(@"CREATE TABLE [Pins] (
[ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[ServerID] INTEGER CONSTRAINT [Pins_ServerID] REFERENCES [Servers]([ID]),
[Local] BOOLEAN DEFAULT (0),
[Path] TEXT);");
}
if (!sql.ColumnExists("Pins", "LinkTo"))
{
sql.ExecuteTX("ALTER TABLE Pins ADD COLUMN LinkTo INTEGER;");
}
if (!sql.ColumnExists("Servers", "Passive"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Passive BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Passive=1 WHERE ServerType=3;");
}
if (!sql.ColumnExists("Servers", "Themes"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Themes BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Themes=0");
}
if (!sql.ColumnExists("Servers", "Certificates"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Certificates BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Certificates=0");
}
if (!sql.ColumnExists("Servers", "UseHtmlLogin"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN UseHtmlLogin BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET UseHtmlLogin=0");
}
if (!sql.ColumnExists("Servers", "HtmlUserBox"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN HtmlUserBox TEXT;");
sql.ExecuteTX("UPDATE Servers SET HtmlUserBox=''");
}
if (!sql.ColumnExists("Servers", "HtmlPassBox"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN HtmlPassBox TEXT;");
sql.ExecuteTX("UPDATE Servers SET HtmlPassBox=''");
}
if (!sql.ColumnExists("Servers", "HtmlLoginBtn"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN HtmlLoginBtn TEXT;");
sql.ExecuteTX("UPDATE Servers SET HtmlLoginBtn=''");
}
if (!sql.ColumnExists("Servers", "TabColor"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN TabColor INTEGER;");
sql.ExecuteTX("UPDATE Servers SET TabColor=-4144960");
}
if (!sql.ColumnExists("Servers", "TabColorTmp"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN TabColorTmp INTEGER;");
sql.ExecuteTX("UPDATE Servers SET TabColor=-4144960 WHERE TabColor=-5192482");
}
if (!sql.ColumnExists("Servers", "Notes"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Notes TEXT;");
sql.ExecuteTX("UPDATE Servers SET Notes=''");
}
if (!sql.ColumnExists("Servers", "SshTerminal"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN SshTerminal INTEGER;");
sql.ExecuteTX("UPDATE Servers SET SshTerminal=1");
}
if (!sql.ColumnExists("Groups", "Expanded"))
{
sql.ExecuteTX("ALTER TABLE Groups ADD COLUMN Expanded BOOLEAN;");
sql.ExecuteTX("UPDATE Groups SET Expanded=0");
}
if (!sql.ColumnExists("Servers", "Encryption"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN Encryption BOOLEAN;");
sql.ExecuteTX("UPDATE Servers SET Encryption=0");
}
if (!sql.ColumnExists("Servers", "SshKey"))
{
sql.ExecuteTX("ALTER TABLE Servers ADD COLUMN SshKey TEXT;");
sql.ExecuteTX("UPDATE Servers SET SshKey=''");
}
}
}