X-Git-Url: https://git.pterodactylus.net/?a=blobdiff_plain;f=src%2Fdbsetup.cpp;h=1316bb28c1533e5d8dec09dd7a83763fb1c5c75d;hb=e662ea47fba8715474851ceebacba400984ee433;hp=2e0a460ea7824065a3fe9d72cd34d502b3243ac5;hpb=278ee758050cb7772cd95946688c5b40104f4d8b;p=fms.git

diff --git a/src/dbsetup.cpp b/src/dbsetup.cpp
index 2e0a460..1316bb2 100644
--- a/src/dbsetup.cpp
+++ b/src/dbsetup.cpp
@@ -104,13 +104,19 @@ void SetupDB()
 			major=1;
 			minor=13;
 		}
+		if(major==1 && minor==13)
+		{
+			ConvertDB0113To0114();
+			major=1;
+			minor=14;
+		}
 	}
 	else
 	{
-		db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,13);");
+		db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,14);");
 	}
 
-	db->Execute("UPDATE tblDBVersion SET Major=1, Minor=13;");
+	db->Execute("UPDATE tblDBVersion SET Major=1, Minor=14;");
 
 	db->Execute("CREATE TABLE IF NOT EXISTS tblFMSVersion(\
 				Major				INTEGER,\
@@ -283,13 +289,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','Initial Board','true');");
+	db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('freenet','Discussion about Freenet','2007-12-01 12:00:00','Initial Board','true');");
+	db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('public','Public discussion','2007-12-01 12:00:00','Initial Board','true');");
+	db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded,AddedMethod,Forum) VALUES('test','Test board','2007-12-01 12:00:00','Initial Board','true');");
 
 	db->Execute("CREATE TABLE IF NOT EXISTS tblMessage(\
 				MessageID			INTEGER PRIMARY KEY,\
@@ -301,7 +308,8 @@ void SetupDB()
 				MessageUUID			TEXT UNIQUE,\
 				ReplyBoardID		INTEGER,\
 				Body				TEXT,\
-				MessageIndex		INTEGER\
+				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 +321,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,\
@@ -389,10 +398,39 @@ void SetupDB()
 				Day					DATE,\
 				RequestIndex		INTEGER,\
 				Found				BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\
-				);");	
+				);");
+
+	// begin thread db schema
+	db->Execute("CREATE TABLE IF NOT EXISTS tblThread(\
+				ThreadID		INTEGER PRIMARY KEY,\
+				BoardID			INTEGER,\
+				FirstMessageID	INTEGER,\
+				LastMessageID	INTEGER\
+				);");
+
+	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 INDEX IF NOT EXISTS idxThreadPost_ThreadID ON tblThreadPost(ThreadID);");
+	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
 
 	// 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(\
+				MessageListInsertID	INTEGER PRIMARY KEY,\
 				LocalIdentityID		INTEGER,\
 				Date				DATETIME\
 				);");
@@ -525,10 +563,10 @@ void SetupDB()
 	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');");
 	// 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');");
-	// insert garfield's public key
-	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');");
-	// insert alek's public key
-	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');");
+	// 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');");
+	// 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');");
 	// 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');");
 	// insert falafel's public key
@@ -565,7 +603,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 +628,20 @@ const bool VerifyDB()
 		return false;
 	}
 }
+
+const std::string TestDBIntegrity()
+{
+	std::string result="";
+
+	SQLite3DB::DB *db=SQLite3DB::DB::Instance();
+	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;
+}