\r
bool wantshutdown=false;\r
\r
+std::string CreateShortIdentityName(const std::string &name, const std::string &publickey)\r
+{\r
+ std::string result="";\r
+ std::vector<std::string> keyparts;\r
+\r
+ StringFunctions::SplitMultiple(publickey,"@,",keyparts);\r
+\r
+ result+=name;\r
+ if(keyparts.size()>1 && keyparts[1].size()>8)\r
+ {\r
+ result+="@"+keyparts[1].substr(0,4)+"...";\r
+ }\r
+\r
+ return result;\r
+}\r
+\r
void SetupDB()\r
{\r
\r
\r
db->Open("fms.db3");\r
db->SetBusyTimeout(10000); // set timeout to 10 seconds\r
- db->Execute("VACUUM;");\r
+ //db->Execute("VACUUM;"); // not needed every startup\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
Major INTEGER,\\r
major=1;\r
minor=7;\r
}\r
+ if(major==1 && minor==7)\r
+ {\r
+ ConvertDB0107To0108();\r
+ major=1;\r
+ minor=8;\r
+ }\r
+ if(major==1 && minor==8)\r
+ {\r
+ ConvertDB0108To0109();\r
+ major=1;\r
+ minor=9;\r
+ }\r
}\r
else\r
{\r
- db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,7);");\r
+ db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,9);");\r
}\r
\r
- db->Execute("UPDATE tblDBVersion SET Major=1, Minor=7;");\r
+ db->Execute("UPDATE tblDBVersion SET Major=1, Minor=9;");\r
\r
db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\\r
Option TEXT UNIQUE,\\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
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
Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
);");\r
\r
+ db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityTrust(\\r
+ LocalIdentityID INTEGER,\\r
+ IdentityID INTEGER,\\r
+ LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ MessageTrustComment TEXT,\\r
+ LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
+ TrustListTrustComment TEXT\\r
+ );");\r
+\r
+ db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxIdentityTrust_IDs ON tblIdentityTrust(LocalIdentityID,IdentityID);");\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgInsertOnIdentityTrust AFTER INSERT ON tblIdentityTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \\r
+ END;");\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgUpdateOnIdentityTrust AFTER UPDATE OF LocalMessageTrust,LocalTrustListTrust ON tblIdentityTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=new.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=new.IdentityID; \\r
+ END;");\r
+\r
+ db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnIdentityTrust AFTER DELETE ON tblIdentityTrust \\r
+ FOR EACH ROW \\r
+ BEGIN \\r
+ UPDATE tblIdentity SET LocalMessageTrust=(SELECT MAX(LocalMessageTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID), LocalTrustListTrust=(SELECT MAX(LocalTrustListTrust) FROM tblIdentityTrust WHERE tblIdentityTrust.IdentityID=old.IdentityID GROUP BY tblIdentityTrust.IdentityID) WHERE tblIdentity.IdentityID=old.IdentityID; \\r
+ END;");\r
+\r
db->Execute("CREATE TABLE IF NOT EXISTS tblPeerTrust(\\r
IdentityID INTEGER,\\r
TargetIdentityID INTEGER,\\r
Inserted BOOL CHECK(Inserted IN('true','false')) DEFAULT 'false'\\r
);");\r
\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
+\r
db->Execute("CREATE TABLE IF NOT EXISTS tblMessageListInserts(\\r
LocalIdentityID INTEGER,\\r
Day DATE,\\r
DELETE FROM tblMessageReplyTo WHERE tblMessageReplyTo.MessageID=old.MessageID;\\r
END;");\r
\r
+ db->Execute("DROP TRIGGER IF EXISTS trgDeleteIdentity;");\r
db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteIdentity AFTER DELETE ON tblIdentity \\r
FOR EACH ROW \\r
BEGIN \\r
DELETE FROM tblMessageRequests WHERE IdentityID=old.IdentityID;\\r
DELETE FROM tblPeerTrust WHERE IdentityID=old.IdentityID;\\r
DELETE FROM tblTrustListRequests WHERE IdentityID=old.IdentityID;\\r
+ DELETE FROM tblIdentityTrust WHERE IdentityID=old.IdentityID;\\r
END;");\r
\r
+ db->Execute("DROP TRIGGER IF EXISTS trgDeleteLocalIdentity;");\r
db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteLocalIdentity AFTER DELETE ON tblLocalIdentity \\r
FOR EACH ROW \\r
BEGIN \\r
DELETE FROM tblMessageInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
DELETE FROM tblMessageListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\\r
+ DELETE FROM tblIdentityTrust WHERE LocalIdentityID=old.LocalIdentityID;\\r
END;");\r
\r
db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \\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
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 SetupDefaultOptions()\r
{\r
// OptionValue should always be inserted as a string, even if the option really isn't a string - just to keep the field data type consistent\r