+ date.SetToGMTime();\r
+ // insert SomeDude's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust,AddedMethod) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"',50,'Initial Identity');");\r
+ // insert Shadow Panther's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // insert garfield's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // insert alek's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@lTjeI6V0lQsktXqaqJ6Iwk4TdsHduQI54rdUpHfhGbg,0oTYfrxxx8OmdU1~60gqpf3781qzEicM4Sz97mJsBM4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // insert Luke771's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@mdXK~ZVlfTZhF1SLBrvZ--i0vOsOpa~w9wv~~psQ-04,gXonsXKc7aexKSO8Gt8Fwre4Qgmmbt2WueO7VzxNKkk,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // insert falafel's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // insert cptn_insano's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@bloE1LJ~qzSYUkU2nt7sB9kq060D4HTQC66pk5Q8NpA,DOOASUnp0kj6tOdhZJ-h5Tk7Ka50FSrUgsH7tCG1usU,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // insert Flink's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@q2TtkNBOuuniyJ56~8NSopCs3ttwe5KlB31ugZtWmXA,6~PzIupS8YK7L6oFNpXGKJmHT2kBMDfwTg73nHdNur8,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // insert Kane's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@Ofm~yZivDJ5Z2fSzZbMiLEUUQaIc0KHRdZMBTaPLO6I,WLm4s4hNbOOurJ6ijfOq4odz7-dN7uTUvYxJRwWnlMI,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+ // inserts boardstat's public key\r
+ db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@aYWBb6zo2AM13XCNhsmmRKMANEx6PG~C15CWjdZziKA,X1pAG4EIqR1gAiyGFVZ1iiw-uTlh460~rFACJ7ZHQXk,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+\r
+ // TODO remove sometime after 0.1.17\r
+ FixCapitalBoardNames();\r
+\r
+ // run analyze - may speed up some queries\r
+ db->Execute("ANALYZE;");\r
+\r
+}\r
+\r
+void ConvertDB0100To0101()\r
+{\r
+ // added unique constraint to public and private key\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
+ db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
+ LocalIdentityID INTEGER PRIMARY KEY,\\r
+ Name TEXT,\\r
+ PublicKey TEXT UNIQUE,\\r
+ PrivateKey TEXT UNIQUE,\\r
+ SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
+ PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
+ PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
+ InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedIdentity DATETIME,\\r
+ InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedPuzzle DATETIME,\\r
+ InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedTrustList DATETIME,\\r
+ InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedBoardList DATETIME,\\r
+ InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedMessageList DATETIME\\r
+ );");\r
+ db->Execute("INSERT INTO tblLocalIdentity SELECT * FROM tblLocalIdentityTemp;");\r
+ db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;");\r
+}\r
+\r
+void ConvertDB0101To0103()\r
+{\r
+ // remove default 50 from trust fields and set default to NULL\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
+ db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
+ IdentityID INTEGER PRIMARY KEY,\\r
+ PublicKey TEXT UNIQUE,\\r
+ Name TEXT,\\r
+ SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
+ PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
+ PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
+ DateAdded DATETIME,\\r
+ LastSeen DATETIME,\\r
+ LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\\r
+ );");\r
+ db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;");\r
+ db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
+\r
+ // add SaveReceivedMessages field to tblBoard\r
+ db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';");\r
+\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;");\r
+}\r
+\r
+void ConvertDB0103To0104()\r
+{\r
+ // add MessageIndex to tblMessage\r
+ DateTime date;\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex INTEGER;");\r
+ db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
+ db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;");\r
+ date.SetToGMTime();\r
+ db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;");\r
+}\r
+\r
+void ConvertDB0104To0105()\r
+{\r
+ // add AddedMethod, MessageTrustComment, TrustListTrustComment to tblIdentity\r
+ // add MessageTrustComment,TrustListTrustComment to tblPeerTrust\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("ALTER TABLE tblIdentity ADD COLUMN AddedMethod TEXT;");\r
+ db->Execute("ALTER TABLE tblIdentity ADD COLUMN MessageTrustComment TEXT;");\r
+ db->Execute("ALTER TABLE tblIdentity ADD COLUMN TrustListTrustComment TEXT;");\r
+ db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN MessageTrustComment TEXT;");\r
+ db->Execute("ALTER TABLE tblPeerTrust ADD COLUMN TrustListTrustComment TEXT;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=5;");\r
+}\r
+\r
+void ConvertDB0105To0106()\r
+{\r
+ // add Publish Freesite\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false';");\r
+ db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN LastInsertedFreesite DATETIME;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=6;");\r
+}\r
+\r
+void ConvertDB0106To0107()\r
+{\r
+ // add AddedMethod to tblBoard\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("ALTER TABLE tblBoard ADD COLUMN AddedMethod TEXT;");\r
+ db->Execute("ALTER TABLE tblIdentity ADD COLUMN Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false';");\r
+ db->Execute("UPDATE tblIdentity SET Hidden='false' WHERE Hidden IS NULL;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");\r
+}\r
+\r
+void ConvertDB0107To0108()\r
+{\r
+ // add FreesiteEdition to tblLocalIdentity and tblIdentity\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
+ db->Execute("ALTER TABLE tblIdentity ADD COLUMN FreesiteEdition INTEGER;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=8;");\r
+}\r
+\r
+void ConvertDB0108To0109()\r
+{\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblFileInserts(\\r
+ FileInsertID INTEGER PRIMARY KEY,\\r
+ MessageUUID TEXT,\\r
+ FileName TEXT,\\r
+ Key TEXT,\\r
+ Size INTEGER,\\r
+ Data BLOB\\r
+ );");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");\r
+}\r
+\r
+void ConvertDB0109To0110()\r
+{\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+ db->Execute("ALTER TABLE tblFileInserts ADD COLUMN MimeType TEXT;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=10;");\r
+}\r
+\r
+void ConvertDB0110To0111()\r
+{\r
+ /*\r
+ Drop MessageTrustComment, TrustListTrustComment FROM tblIdentity\r
+\r
+ Drop InsertingMessageList, InsertingBoardList FROM tblLocalIdentity\r
+ Add MinMessageDelay, MaxMessageDelay to tblLocalIdentity Default 0\r
+\r
+ Add SendDate to tblMessageInserts\r
+ */\r
+ SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
+\r
+ db->Execute("ALTER TABLE tblMessageInserts ADD COLUMN SendDate DATETIME;");\r
+\r
+ db->Execute("CREATE TEMPORARY TABLE tblLocalIdentityTemp AS SELECT * FROM tblLocalIdentity;");\r
+ db->Execute("DROP TABLE IF EXISTS tblLocalIdentity;");\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
+ LocalIdentityID INTEGER PRIMARY KEY,\\r
+ Name TEXT,\\r
+ PublicKey TEXT UNIQUE,\\r
+ PrivateKey TEXT UNIQUE,\\r
+ SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
+ PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
+ PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
+ PublishFreesite BOOL CHECK(PublishFreesite IN('true','false')) DEFAULT 'false',\\r
+ FreesiteEdition INTEGER,\\r
+ InsertingIdentity BOOL CHECK(InsertingIdentity IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedIdentity DATETIME,\\r
+ InsertingPuzzle BOOL CHECK(InsertingPuzzle IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedPuzzle DATETIME,\\r
+ InsertingTrustList BOOL CHECK(InsertingTrustList IN('true','false')) DEFAULT 'false',\\r
+ LastInsertedTrustList DATETIME,\\r
+ LastInsertedBoardList DATETIME,\\r
+ LastInsertedMessageList DATETIME,\\r
+ LastInsertedFreesite DATETIME,\\r
+ DateCreated DATETIME,\\r
+ MinMessageDelay INTEGER DEFAULT 0,\\r
+ MaxMessageDelay INTEGER DEFAULT 0\\r
+ );");\r
+ db->Execute("INSERT INTO tblLocalIdentity SELECT LocalIdentityID,Name,PublicKey,PrivateKey,SingleUse,PublishTrustList,PublishBoardList,PublishFreesite,FreesiteEdition,InsertingIdentity,LastInsertedIdentity,InsertingPuzzle,LastInsertedPuzzle,InsertingTrustList,LastInsertedTrustList,LastInsertedBoardList,LastInsertedMessageList,LastInsertedFreesite,DateCreated,0,0 FROM tblLocalIdentityTemp;");\r
+ db->Execute("DROP TABLE IF EXISTS tblLocalIdentityTemp;");\r
+\r
+ db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;");\r
+ db->Execute("DROP TABLE IF EXISTS tblIdentity;");\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\\r
+ IdentityID INTEGER PRIMARY KEY,\\r
+ PublicKey TEXT UNIQUE,\\r
+ Name TEXT,\\r
+ SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\\r
+ PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\\r
+ PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\\r
+ FreesiteEdition INTEGER,\\r
+ DateAdded DATETIME,\\r
+ LastSeen DATETIME,\\r
+ LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ AddedMethod TEXT,\\r
+ Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false'\\r
+ );");\r
+ db->Execute("INSERT INTO tblIdentity SELECT IdentityID,PublicKey,Name,SingleUse,PublishTrustList,PublishBoardList,FreesiteEdition,DateAdded,LastSeen,LocalMessageTrust,PeerMessageTrust,LocalTrustListTrust,PeerTrustListTrust,AddedMethod,Hidden FROM tblIdentityTemp;");\r
+ db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;");\r
+\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=11;");\r
projects / fms.git / blobdiff