X-Git-Url: https://git.pterodactylus.net/?p=fms.git;a=blobdiff_plain;f=src%2Fdbsetup.cpp;h=1add253df1812a03a3af4dee395a820906f6330e;hp=5f4c1290bae24f44ece8ce46d3c5d7844d82a6c8;hb=HEAD;hpb=d5c9f7e6c1dd263dfc85a3cb5941a378a5ddd923 diff --git a/src/dbsetup.cpp b/src/dbsetup.cpp index 5f4c129..1add253 100644 --- a/src/dbsetup.cpp +++ b/src/dbsetup.cpp @@ -7,16 +7,11 @@ #include #include -void SetupDB() +void SetupDB(SQLite3DB::DB *db) { Poco::DateTime date; std::string tempval=""; - SQLite3DB::DB *db=SQLite3DB::DB::Instance(); - - db->Open("fms.db3"); - db->SetBusyTimeout(20000); // set timeout to 20 seconds - db->Execute("PRAGMA synchronous = FULL;"); db->Execute("CREATE TABLE IF NOT EXISTS tblDBVersion(\ Major INTEGER,\ @@ -34,83 +29,107 @@ void SetupDB() st.Finalize(); if(major==1 && minor==0) { - ConvertDB0100To0101(); + ConvertDB0100To0101(db); major=1; minor=1; } if(major==1 && (minor==1 || minor==2)) { - ConvertDB0101To0103(); + ConvertDB0101To0103(db); major=1; minor=3; } if(major==1 && minor==3) { - ConvertDB0103To0104(); + ConvertDB0103To0104(db); major=1; minor=4; } if(major==1 && minor==4) { - ConvertDB0104To0105(); + ConvertDB0104To0105(db); major=1; minor=5; } if(major==1 && minor==5) { - ConvertDB0105To0106(); + ConvertDB0105To0106(db); major=1; minor=6; } if(major==1 && minor==6) { - ConvertDB0106To0107(); + ConvertDB0106To0107(db); major=1; minor=7; } if(major==1 && minor==7) { - ConvertDB0107To0108(); + ConvertDB0107To0108(db); major=1; minor=8; } if(major==1 && minor==8) { - ConvertDB0108To0109(); + ConvertDB0108To0109(db); major=1; minor=9; } if(major==1 && minor==9) { - ConvertDB0109To0110(); + ConvertDB0109To0110(db); major=1; minor=10; } if(major==1 && minor==10) { - ConvertDB0110To0111(); + ConvertDB0110To0111(db); major=1; minor=11; } if(major==1 && minor==11) { - ConvertDB0111To0112(); + ConvertDB0111To0112(db); major=1; minor=12; } if(major==1 && minor==12) { - ConvertDB0112To0113(); + ConvertDB0112To0113(db); major=1; minor=13; } + if(major==1 && minor==13) + { + ConvertDB0113To0114(db); + major=1; + minor=14; + } + if(major==1 && minor==14) + { + ConvertDB0114To0115(db); + major=1; + minor=15; + } + if(major==1 && minor==15) + { + ConvertDB0115To0116(db); + major=1; + minor=16; + } + if(major==1 && minor==16) + { + ConvertDB0116To0117(db); + major=1; + minor=17; + } } else { - db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,13);"); + db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,17);"); } - db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;"); + db->Execute("UPDATE tblDBVersion SET Major=1, Minor=17;"); db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\ Major INTEGER,\ @@ -130,7 +149,11 @@ void SetupDB() OptionDescription TEXT,\ Section TEXT,\ SortOrder INTEGER,\ - ValidValues TEXT\ + ValidValues TEXT,\ + DisplayType TEXT CHECK (DisplayType IN ('textbox','textarea','select','multiselect')) DEFAULT 'textbox',\ + DisplayParam1 TEXT,\ + DisplayParam2 TEXT,\ + Mode TEXT CHECK (Mode IN ('simple','advanced')) DEFAULT 'simple'\ );"); db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentity(\ @@ -207,7 +230,8 @@ void SetupDB() PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\ AddedMethod TEXT,\ Hidden BOOL CHECK(Hidden IN('true','false')) DEFAULT 'false',\ - PurgeDate DATETIME\ + PurgeDate DATETIME,\ + FailureCount INTEGER CHECK(FailureCount>=0) DEFAULT 0\ );"); db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\ @@ -283,13 +307,14 @@ void SetupDB() BoardDescription TEXT,\ DateAdded DATETIME,\ SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true',\ - AddedMethod TEXT\ + AddedMethod TEXT,\ + Forum TEXT CHECK(Forum IN('true','false')) DEFAULT 'false'\ );"); - db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Initial Board');"); - db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board');"); - db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board');"); - db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board');"); + db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('fms','Freenet Message System','2007-12-01 12:00:00','Seed Board','true');"); + db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Seed Board','true');"); + db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Seed Board','true');"); + db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Seed Board','true');"); db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\ MessageID INTEGER PRIMARY KEY,\ @@ -301,7 +326,9 @@ void SetupDB() MessageUUID TEXT UNIQUE,\ ReplyBoardID INTEGER,\ Body TEXT,\ - MessageIndex INTEGER\ + InsertDate DATE,\ + MessageIndex INTEGER,\ + Read INTEGER CHECK(Read IN(0,1)) DEFAULT 0\ );"); db->Execute("CREATE INDEX IF NOT EXISTS idxMessage_IdentityID ON tblMessage (IdentityID);"); @@ -313,6 +340,7 @@ void SetupDB() );"); db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_MessageID ON tblMessageReplyTo (MessageID);"); + db->Execute("CREATE INDEX IF NOT EXISTS idxMessageReplyTo_ReplyToMessageUUID ON tblMessageReplyTo (ReplyToMessageUUID);"); db->Execute("CREATE TABLE IF NOT EXISTS tblMessageBoard(\ MessageID INTEGER,\ @@ -339,7 +367,8 @@ void SetupDB() FromMessageList BOOL CHECK(FromMessageList IN('true','false')) DEFAULT 'false',\ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\ Tries INTEGER DEFAULT 0,\ - Key TEXT\ + Key TEXT,\ + FromIdentityID INTEGER\ );"); db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);"); @@ -389,25 +418,50 @@ void SetupDB() Day DATE,\ RequestIndex INTEGER,\ Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\ - );"); + );"); - // MessageInserter will insert a record into this temp table which the MessageListInserter will query for and insert a MessageList when needed - db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpMessageListInsert(\ - LocalIdentityID INTEGER,\ - Date DATETIME\ +#ifdef FROST_SUPPORT + + db->Execute("CREATE TABLE IF NOT EXISTS tblFrostMessageRequests(\ + BoardID INTEGER,\ + Day DATE,\ + RequestIndex INTEGER,\ + Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false',\ + Tries INTEGER DEFAULT 0\ );"); - // A temporary table that will hold a local identity id of the last identity who was loaded in the trust list page - db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpLocalIdentityPeerTrustPage(\ - LocalIdentityID INTEGER\ + db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxFrostMessageRequest ON tblFrostMessageRequests(BoardID,Day,RequestIndex);"); + +#endif + + // begin thread db schema + db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\ + ThreadID INTEGER PRIMARY KEY,\ + BoardID INTEGER,\ + FirstMessageID INTEGER,\ + LastMessageID INTEGER\ );"); - // Temporary table for form passwords - db->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS tmpFormPassword(\ - Date DATETIME,\ - Password TEXT\ + db->Execute("CREATE INDEX IF NOT EXISTS idxThread_BoardID ON tblThread(BoardID);"); + db->Execute("CREATE INDEX IF NOT EXISTS idxThread_FirstMessageID ON tblThread(FirstMessageID);"); + db->Execute("CREATE INDEX IF NOT EXISTS idxThread_LastMessageID ON tblThread(LastMessageID);"); + + db->Execute("CREATE TABLE IF NOT EXISTS tblThreadPost(\ + ThreadID INTEGER,\ + MessageID INTEGER,\ + PostOrder INTEGER\ );"); + db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxThreadPost_ThreadMessage ON tblThreadPost(ThreadID,MessageID);"); + db->Execute("CREATE INDEX IF NOT EXISTS idxThreadPost_MessageID ON tblThreadPost(MessageID);"); + + db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteOnThread AFTER DELETE ON tblThread\ + FOR EACH ROW\ + BEGIN\ + DELETE FROM tblThreadPost WHERE ThreadID=old.ThreadID;\ + END;"); + // end thread db schema + // low / high / message count for each board db->Execute("CREATE VIEW IF NOT EXISTS vwBoardStats AS \ SELECT tblBoard.BoardID AS 'BoardID', IFNULL(MIN(MessageID),0) AS 'LowMessageID', IFNULL(MAX(MessageID),0) AS 'HighMessageID', COUNT(MessageID) AS 'MessageCount' \ @@ -522,37 +576,36 @@ void SetupDB() date=Poco::Timestamp(); // insert SomeDude's public key - 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');"); + 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');"); // insert Shadow Panther's public key - haven't seen in a while - disabling for now - //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');"); + //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');"); // insert garfield's public key -haven't seen in a while - disabling for now - //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');"); + //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');"); // insert alek's public key - haven't seen in a while - disabling for now - //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');"); + //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');"); // insert Luke771's public key - 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');"); + 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');"); // insert falafel's public key - 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');"); + 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');"); // insert cptn_insano's public key - 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');"); - // insert Flink's public key - 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');"); + 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');"); + // insert Flink's public key - haven't seen in a while - disabling for now + //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');"); // insert Kane's public key - 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');"); + 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');"); // inserts boardstat's public key - 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');"); + 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');"); // TODO remove sometime after 0.1.17 - FixCapitalBoardNames(); + FixCapitalBoardNames(db); // run analyze - may speed up some queries db->Execute("ANALYZE;"); } -const bool VerifyDB() +const bool VerifyDB(SQLite3DB::DB *db) { - SQLite3DB::DB *db=SQLite3DB::DB::Instance(); SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;"); st.Step(); if(st.RowReturned()) @@ -565,7 +618,24 @@ const bool VerifyDB() } else { - return false; + // try to reindex and vacuum database in case of index corruption + st=db->Prepare("REINDEX;"); + st.Step(); + st=db->Prepare("VACUUM;"); + st.Step(); + + // check integrity again + st=db->Prepare("PRAGMA integrity_check;"); + st.Step(); + st.ResultText(0,res); + if(res=="ok") + { + return true; + } + else + { + return false; + } } } else @@ -573,3 +643,19 @@ const bool VerifyDB() return false; } } + +const std::string TestDBIntegrity(SQLite3DB::DB *db) +{ + std::string result=""; + + SQLite3DB::Statement st=db->Prepare("PRAGMA integrity_check;"); + st.Step(); + while(st.RowReturned()) + { + std::string text=""; + st.ResultText(0,text); + result+=text; + st.Step(); + } + return result; +}