projects / fms.git / blobdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | inline | side by side
version 0.3.33
[fms.git] / src / dbsetup.cpp
diff --git a/src/dbsetup.cpp b/src/dbsetup.cpp
index b9f30be..1add253 100644 (file)
--- a/src/dbsetup.cpp
+++ b/src/dbsetup.cpp
@@ -7,15 +7,11 @@
 #include <Poco/Timespan.h>\r
 #include <Poco/DateTimeFormatter.h>\r
 \r
 #include <Poco/Timespan.h>\r
 #include <Poco/DateTimeFormatter.h>\r
 \r
-void SetupDB()\r
+void SetupDB(SQLite3DB::DB *db)\r
 {\r
 \r
        Poco::DateTime date;\r
        std::string tempval="";\r
 {\r
 \r
        Poco::DateTime date;\r
        std::string tempval="";\r
-       SQLite3DB::DB *db=SQLite3DB::DB::Instance();\r
-\r
-       db->Open("fms.db3");\r
-       db->SetBusyTimeout(20000);              // set timeout to 20 seconds\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
                                Major                           INTEGER,\\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\\r
                                Major                           INTEGER,\\r
@@ -33,83 +29,107 @@ void SetupDB()
                st.Finalize();\r
                if(major==1 && minor==0)\r
                {\r
                st.Finalize();\r
                if(major==1 && minor==0)\r
                {\r
-                       ConvertDB0100To0101();\r
+                       ConvertDB0100To0101(db);\r
                        major=1;\r
                        minor=1;\r
                }\r
                if(major==1 && (minor==1 || minor==2))\r
                {\r
                        major=1;\r
                        minor=1;\r
                }\r
                if(major==1 && (minor==1 || minor==2))\r
                {\r
-                       ConvertDB0101To0103();\r
+                       ConvertDB0101To0103(db);\r
                        major=1;\r
                        minor=3;\r
                }\r
                if(major==1 && minor==3)\r
                {\r
                        major=1;\r
                        minor=3;\r
                }\r
                if(major==1 && minor==3)\r
                {\r
-                       ConvertDB0103To0104();\r
+                       ConvertDB0103To0104(db);\r
                        major=1;\r
                        minor=4;\r
                }\r
                if(major==1 && minor==4)\r
                {\r
                        major=1;\r
                        minor=4;\r
                }\r
                if(major==1 && minor==4)\r
                {\r
-                       ConvertDB0104To0105();\r
+                       ConvertDB0104To0105(db);\r
                        major=1;\r
                        minor=5;\r
                }\r
                if(major==1 && minor==5)\r
                {\r
                        major=1;\r
                        minor=5;\r
                }\r
                if(major==1 && minor==5)\r
                {\r
-                       ConvertDB0105To0106();\r
+                       ConvertDB0105To0106(db);\r
                        major=1;\r
                        minor=6;\r
                }\r
                if(major==1 && minor==6)\r
                {\r
                        major=1;\r
                        minor=6;\r
                }\r
                if(major==1 && minor==6)\r
                {\r
-                       ConvertDB0106To0107();\r
+                       ConvertDB0106To0107(db);\r
                        major=1;\r
                        minor=7;\r
                }\r
                if(major==1 && minor==7)\r
                {\r
                        major=1;\r
                        minor=7;\r
                }\r
                if(major==1 && minor==7)\r
                {\r
-                       ConvertDB0107To0108();\r
+                       ConvertDB0107To0108(db);\r
                        major=1;\r
                        minor=8;\r
                }\r
                if(major==1 && minor==8)\r
                {\r
                        major=1;\r
                        minor=8;\r
                }\r
                if(major==1 && minor==8)\r
                {\r
-                       ConvertDB0108To0109();\r
+                       ConvertDB0108To0109(db);\r
                        major=1;\r
                        minor=9;\r
                }\r
                if(major==1 && minor==9)\r
                {\r
                        major=1;\r
                        minor=9;\r
                }\r
                if(major==1 && minor==9)\r
                {\r
-                       ConvertDB0109To0110();\r
+                       ConvertDB0109To0110(db);\r
                        major=1;\r
                        minor=10;\r
                }\r
                if(major==1 && minor==10)\r
                {\r
                        major=1;\r
                        minor=10;\r
                }\r
                if(major==1 && minor==10)\r
                {\r
-                       ConvertDB0110To0111();\r
+                       ConvertDB0110To0111(db);\r
                        major=1;\r
                        minor=11;\r
                }\r
                if(major==1 && minor==11)\r
                {\r
                        major=1;\r
                        minor=11;\r
                }\r
                if(major==1 && minor==11)\r
                {\r
-                       ConvertDB0111To0112();\r
+                       ConvertDB0111To0112(db);\r
                        major=1;\r
                        minor=12;\r
                }\r
                if(major==1 && minor==12)\r
                {\r
                        major=1;\r
                        minor=12;\r
                }\r
                if(major==1 && minor==12)\r
                {\r
-                       ConvertDB0112To0113();\r
+                       ConvertDB0112To0113(db);\r
                        major=1;\r
                        minor=13;\r
                }\r
                        major=1;\r
                        minor=13;\r
                }\r
+               if(major==1 && minor==13)\r
+               {\r
+                       ConvertDB0113To0114(db);\r
+                       major=1;\r
+                       minor=14;\r
+               }\r
+               if(major==1 && minor==14)\r
+               {\r
+                       ConvertDB0114To0115(db);\r
+                       major=1;\r
+                       minor=15;\r
+               }\r
+               if(major==1 && minor==15)\r
+               {\r
+                       ConvertDB0115To0116(db);\r
+                       major=1;\r
+                       minor=16;\r
+               }\r
+               if(major==1 && minor==16)\r
+               {\r
+                       ConvertDB0116To0117(db);\r
+                       major=1;\r
+                       minor=17;\r
+               }\r
        }\r
        else\r
        {\r
        }\r
        else\r
        {\r
-               db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,13);");\r
+               db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,17);");\r
        }\r
 \r
        }\r
 \r
-       db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");\r
+       db->Execute("UPDATE tblDBVersion SET Major=1, Minor=17;");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\\r
                                Major                           INTEGER,\\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\\r
                                Major                           INTEGER,\\r
@@ -129,7 +149,11 @@ void SetupDB()
                                OptionDescription       TEXT,\\r
                                Section                         TEXT,\\r
                                SortOrder                       INTEGER,\\r
                                OptionDescription       TEXT,\\r
                                Section                         TEXT,\\r
                                SortOrder                       INTEGER,\\r
-                               ValidValues                     TEXT\\r
+                               ValidValues                     TEXT,\\r
+                               DisplayType                     TEXT CHECK (DisplayType IN ('textbox','textarea','select','multiselect')) DEFAULT 'textbox',\\r
+                               DisplayParam1           TEXT,\\r
+                               DisplayParam2           TEXT,\\r
+                               Mode                            TEXT CHECK (Mode IN ('simple','advanced')) DEFAULT 'simple'\\r
                                );");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
                                );");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\\r
@@ -206,7 +230,8 @@ void SetupDB()
                                PeerTrustListTrust              INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\\r
                                AddedMethod                             TEXT,\\r
                                Hidden                                  BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\\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
-                               PurgeDate                               DATETIME\\r
+                               PurgeDate                               DATETIME,\\r
+                               FailureCount                    INTEGER CHECK(FailureCount>=0) DEFAULT 0\\r
                                );");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
                                );");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\\r
@@ -282,13 +307,14 @@ void SetupDB()
                                BoardDescription                TEXT,\\r
                                DateAdded                               DATETIME,\\r
                                SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\\r
                                BoardDescription                TEXT,\\r
                                DateAdded                               DATETIME,\\r
                                SaveReceivedMessages    BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\\r
-                               AddedMethod                             TEXT\\r
+                               AddedMethod                             TEXT,\\r
+                               Forum                                   TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\\r
                                );");\r
 \r
                                );");\r
 \r
-       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');");\r
-       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');");\r
-       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');");\r
-       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');");\r
+       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Seed Board','true');");\r
+       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Seed Board','true');");\r
+       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Seed Board','true');");\r
+       db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Seed Board','true');");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
                                MessageID                       INTEGER PRIMARY KEY,\\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\\r
                                MessageID                       INTEGER PRIMARY KEY,\\r
@@ -300,7 +326,9 @@ void SetupDB()
                                MessageUUID                     TEXT UNIQUE,\\r
                                ReplyBoardID            INTEGER,\\r
                                Body                            TEXT,\\r
                                MessageUUID                     TEXT UNIQUE,\\r
                                ReplyBoardID            INTEGER,\\r
                                Body                            TEXT,\\r
-                               MessageIndex            INTEGER\\r
+                               InsertDate                      DATE,\\r
+                               MessageIndex            INTEGER,\\r
+                               Read                            INTEGER CHECK(Read IN(0,1)) DEFAULT 0\\r
                                );");\r
 \r
        db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");\r
                                );");\r
 \r
        db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);");\r
@@ -312,6 +340,7 @@ void SetupDB()
                                );");\r
 \r
        db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\r
                                );");\r
 \r
        db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);");\r
+       db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);");\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
                                MessageID                       INTEGER,\\r
 \r
        db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\\r
                                MessageID                       INTEGER,\\r
@@ -338,7 +367,8 @@ void SetupDB()
                                FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
                                Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
                                Tries                           INTEGER DEFAULT 0,\\r
                                FromMessageList         BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\\r
                                Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
                                Tries                           INTEGER DEFAULT 0,\\r
-                               Key                                     TEXT\\r
+                               Key                                     TEXT,\\r
+                               FromIdentityID          INTEGER\\r
                                );");\r
 \r
        db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
                                );");\r
 \r
        db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);");\r
@@ -388,25 +418,50 @@ void SetupDB()
                                Day                                     DATE,\\r
                                RequestIndex            INTEGER,\\r
                                Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
                                Day                                     DATE,\\r
                                RequestIndex            INTEGER,\\r
                                Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\\r
-                               );");   \r
+                               );");\r
 \r
 \r
-       // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed\r
-       db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\\r
-                               LocalIdentityID         INTEGER,\\r
-                               Date                            DATETIME\\r
+#ifdef FROST_SUPPORT\r
+\r
+       db->Execute("CREATE TABLE IF NOT EXISTS tblFrostMessageRequests(\\r
+                               BoardID                         INTEGER,\\r
+                               Day                                     DATE,\\r
+                               RequestIndex            INTEGER,\\r
+                               Found                           BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\\r
+                               Tries                           INTEGER DEFAULT 0\\r
                                );");\r
 \r
                                );");\r
 \r
-       // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page\r
-       db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\\r
-                               LocalIdentityID         INTEGER\\r
+       db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFrostMessageRequest ON tblFrostMessageRequests(BoardID,Day,RequestIndex);");\r
+\r
+#endif\r
+\r
+       // begin thread db schema\r
+       db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\\r
+                               ThreadID                INTEGER PRIMARY KEY,\\r
+                               BoardID                 INTEGER,\\r
+                               FirstMessageID  INTEGER,\\r
+                               LastMessageID   INTEGER\\r
                                );");\r
 \r
                                );");\r
 \r
-       // Temporary table for form passwords\r
-       db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpFormPassword(\\r
-                               Date                    DATETIME,\\r
-                               Password                TEXT\\r
+       db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);");\r
+       db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);");\r
+       db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);");\r
+\r
+       db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\\r
+                               ThreadID                INTEGER,\\r
+                               MessageID               INTEGER,\\r
+                               PostOrder               INTEGER\\r
                                );");\r
 \r
                                );");\r
 \r
+       db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxThreadPost_ThreadMessage ON tblThreadPost(ThreadID,MessageID);");\r
+       db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);");\r
+\r
+       db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\\r
+                               FOR EACH ROW\\r
+                               BEGIN\\r
+                                       DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\\r
+                               END;");\r
+       // end thread db schema\r
+\r
        // low / high / message count for each board\r
        db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
                                SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
        // low / high / message count for each board\r
        db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \\r
                                SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \\r
@@ -521,30 +576,86 @@ void SetupDB()
 \r
        date=Poco::Timestamp();\r
        // insert SomeDude's public key\r
 \r
        date=Poco::Timestamp();\r
        // insert SomeDude's public key\r
-       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust,AddedMethod) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"',50,'Initial Identity');");\r
+       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust,AddedMethod) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"',50,'Seed Identity');");\r
        // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
        // insert Shadow Panther's public key - haven't seen in a while - disabling for now\r
-       //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%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/','"+Poco::DateTimeFormatter::format(date,"%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/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+       //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
+       // insert garfield's public key -haven't seen in a while - disabling for now\r
+       //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
+       // insert alek's public key - haven't seen in a while - disabling for now\r
+       //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@lTjeI6V0lQsktXqaqJ6Iwk4TdsHduQI54rdUpHfhGbg,0oTYfrxxx8OmdU1~60gqpf3781qzEicM4Sz97mJsBM4,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
        // insert Luke771's public key\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/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@mdXK~ZVlfTZhF1SLBrvZ--i0vOsOpa~w9wv~~psQ-04,gXonsXKc7aexKSO8Gt8Fwre4Qgmmbt2WueO7VzxNKkk,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
        // insert falafel's public key\r
        // insert falafel's public key\r
-       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@IxVqeqM0LyYdTmYAf5z49SJZUxr7NtQkOqVYG0hvITw,RM2wnMn5zAufCMt5upkkgq25B1elfBAxc7htapIWg1c,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
        // insert cptn_insano's public key\r
        // insert cptn_insano's public key\r
-       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@bloE1LJ~qzSYUkU2nt7sB9kq060D4HTQC66pk5Q8NpA,DOOASUnp0kj6tOdhZJ-h5Tk7Ka50FSrUgsH7tCG1usU,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%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/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@bloE1LJ~qzSYUkU2nt7sB9kq060D4HTQC66pk5Q8NpA,DOOASUnp0kj6tOdhZJ-h5Tk7Ka50FSrUgsH7tCG1usU,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
+       // insert Flink's public key - haven't seen in a while - disabling for now\r
+       //db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@q2TtkNBOuuniyJ56~8NSopCs3ttwe5KlB31ugZtWmXA,6~PzIupS8YK7L6oFNpXGKJmHT2kBMDfwTg73nHdNur8,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
        // insert Kane's public key\r
        // insert Kane's public key\r
-       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@Ofm~yZivDJ5Z2fSzZbMiLEUUQaIc0KHRdZMBTaPLO6I,WLm4s4hNbOOurJ6ijfOq4odz7-dN7uTUvYxJRwWnlMI,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@Ofm~yZivDJ5Z2fSzZbMiLEUUQaIc0KHRdZMBTaPLO6I,WLm4s4hNbOOurJ6ijfOq4odz7-dN7uTUvYxJRwWnlMI,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
        // inserts boardstat's public key\r
        // inserts boardstat's public key\r
-       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@aYWBb6zo2AM13XCNhsmmRKMANEx6PG~C15CWjdZziKA,X1pAG4EIqR1gAiyGFVZ1iiw-uTlh460~rFACJ7ZHQXk,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Initial Identity');");\r
+       db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,AddedMethod) VALUES('SSK@aYWBb6zo2AM13XCNhsmmRKMANEx6PG~C15CWjdZziKA,X1pAG4EIqR1gAiyGFVZ1iiw-uTlh460~rFACJ7ZHQXk,AQACAAE/','"+Poco::DateTimeFormatter::format(date,"%Y-%m-%d %H:%M:%S")+"','Seed Identity');");\r
 \r
        // TODO remove sometime after 0.1.17\r
 \r
        // TODO remove sometime after 0.1.17\r
-       FixCapitalBoardNames();\r
+       FixCapitalBoardNames(db);\r
 \r
        // run analyze - may speed up some queries\r
        db->Execute("ANALYZE;");\r
 \r
 }\r
 \r
        // run analyze - may speed up some queries\r
        db->Execute("ANALYZE;");\r
 \r
 }\r
+\r
+const bool VerifyDB(SQLite3DB::DB *db)\r
+{\r
+       SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
+       st.Step();\r
+       if(st.RowReturned())\r
+       {\r
+               std::string res="";\r
+               st.ResultText(0,res);\r
+               if(res=="ok")\r
+               {\r
+                       return true;\r
+               }\r
+               else\r
+               {\r
+                       // try to reindex and vacuum database in case of index corruption\r
+                       st=db->Prepare("REINDEX;");\r
+                       st.Step();\r
+                       st=db->Prepare("VACUUM;");\r
+                       st.Step();\r
+\r
+                       // check integrity again\r
+                       st=db->Prepare("PRAGMA integrity_check;");\r
+                       st.Step();\r
+                       st.ResultText(0,res);\r
+                       if(res=="ok")\r
+                       {\r
+                               return true;\r
+                       }\r
+                       else\r
+                       {\r
+                               return false;\r
+                       }\r
+               }\r
+       }\r
+       else\r
+       {\r
+               return false;\r
+       }\r
+}\r
+\r
+const std::string TestDBIntegrity(SQLite3DB::DB *db)\r
+{\r
+       std::string result="";\r
+\r
+       SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;");\r
+       st.Step();\r
+       while(st.RowReturned())\r
+       {\r
+               std::string text="";\r
+               st.ResultText(0,text);\r
+               result+=text;\r
+               st.Step();\r
+       }\r
+       return result;\r
+}\r
Freenet Message System (unofficial Git repository)