X-Git-Url: https://git.pterodactylus.net/?a=blobdiff_plain;f=src%2Fglobal.cpp;h=2dbfcdf9a3148f26a3e8a56e13c6fe4ef03e4160;hb=3dc3ac3cfe10b7196a7977e9c041c29fa141c35e;hp=7f635791088beb7281125b2110f27a67c3e7539e;hpb=df316253862dc50e8e5a790d9634ef90be37badb;p=fms.git diff --git a/src/global.cpp b/src/global.cpp index 7f63579..2dbfcdf 100644 --- a/src/global.cpp +++ b/src/global.cpp @@ -52,13 +52,25 @@ void SetupDB() major=1; minor=1; } + if(major==1 && (minor==1 || minor==2)) + { + ConvertDB0101To0103(); + major=1; + minor=3; + } + if(major==1 && minor==3) + { + ConvertDB0103To0104(); + major=1; + minor=4; + } } else { - db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,1);"); + db->Execute("INSERT INTO tblDBVersion(Major,Minor) VALUES(1,4);"); } - db->Execute("UPDATE tblDBVersion SET Major=1, Minor=2;"); + db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;"); db->Execute("CREATE TABLE IF NOT EXISTS tblOption(\ Option TEXT UNIQUE,\ @@ -83,7 +95,8 @@ void SetupDB() InsertingBoardList BOOL CHECK(InsertingBoardList IN('true','false')) DEFAULT 'false',\ LastInsertedBoardList DATETIME,\ InsertingMessageList BOOL CHECK(InsertingMessageList IN('true','false')) DEFAULT 'false',\ - LastInsertedMessageList DATETIME\ + LastInsertedMessageList DATETIME,\ + DateCreated DATETIME\ );"); db->Execute("CREATE TABLE IF NOT EXISTS tblLocalIdentityInserts(\ @@ -126,10 +139,10 @@ void SetupDB() PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\ DateAdded DATETIME,\ LastSeen DATETIME,\ - LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT 50,\ - PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT 50,\ - LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT 50,\ - PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT 50\ + LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\ + PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\ + LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\ + PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\ );"); db->Execute("CREATE TABLE IF NOT EXISTS tblIdentityRequests(\ @@ -166,10 +179,11 @@ void SetupDB() );"); db->Execute("CREATE TABLE IF NOT EXISTS tblBoard(\ - BoardID INTEGER PRIMARY KEY,\ - BoardName TEXT UNIQUE,\ - BoardDescription TEXT,\ - DateAdded DATETIME\ + BoardID INTEGER PRIMARY KEY,\ + BoardName TEXT UNIQUE,\ + BoardDescription TEXT,\ + DateAdded DATETIME,\ + SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true'\ );"); db->Execute("INSERT INTO tblBoard(BoardName,BoardDescription,DateAdded) VALUES('fms','Freenet Message System','2007-12-01 12:00:00');"); @@ -186,7 +200,8 @@ void SetupDB() Subject TEXT,\ MessageUUID TEXT UNIQUE,\ ReplyBoardID INTEGER,\ - Body TEXT\ + Body TEXT,\ + MessageIndex INTEGER\ );"); db->Execute("CREATE TABLE IF NOT EXISTS tblMessageReplyTo(\ @@ -215,6 +230,8 @@ void SetupDB() Found BOOL CHECK(Found IN('true','false')) DEFAULT 'false'\ );"); + db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);"); + db->Execute("CREATE TABLE IF NOT EXISTS tblMessageInserts(\ LocalIdentityID INTEGER,\ Day DATE,\ @@ -265,10 +282,13 @@ void SetupDB() GROUP BY tblBoard.BoardID;"); // calculates peer trust + // do the (MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1)/100.0 - so it MessageTrust or TrustListTrust is NULL, the calc will be NULL and it won't be included at all in the average + // need the +1 so that when the values are 0 the result is not 0 + db->Execute("DROP VIEW IF EXISTS vwCalculatedPeerTrust;"); db->Execute("CREATE VIEW IF NOT EXISTS vwCalculatedPeerTrust AS \ SELECT TargetIdentityID, \ - ROUND(SUM(MessageTrust*(LocalMessageTrust/100.0))/SUM(LocalMessageTrust/100.0),0) AS 'PeerMessageTrust', \ - ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(LocalTrustListTrust/100.0),0) AS 'PeerTrustListTrust' \ + ROUND(SUM(MessageTrust*(LocalTrustListTrust/100.0))/SUM(((MessageTrust+1)*LocalTrustListTrust/(MessageTrust+1))/100.0),0) AS 'PeerMessageTrust', \ + ROUND(SUM(TrustListTrust*(LocalTrustListTrust/100.0))/SUM(((TrustListTrust+1)*LocalTrustListTrust/(TrustListTrust+1))/100.0),0) AS 'PeerTrustListTrust' \ FROM tblPeerTrust INNER JOIN tblIdentity ON tblPeerTrust.IdentityID=tblIdentity.IdentityID \ WHERE LocalTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinLocalTrustListTrust') \ AND ( PeerTrustListTrust IS NULL OR PeerTrustListTrust>=(SELECT OptionValue FROM tblOption WHERE Option='MinPeerTrustListTrust') ) \ @@ -343,6 +363,12 @@ void SetupDB() DELETE FROM tblTrustListInserts WHERE LocalIdentityID=old.LocalIdentityID;\ END;"); + db->Execute("CREATE TRIGGER IF NOT EXISTS trgDeleteBoard AFTER DELETE ON tblBoard \ + FOR EACH ROW \ + BEGIN \ + DELETE FROM tblMessageBoard WHERE BoardID=old.BoardID;\ + END;"); + // delete introduction puzzles that were half-way inserted db->Execute("DELETE FROM tblIntroductionPuzzleInserts WHERE Day IS NULL AND InsertIndex IS NULL;"); @@ -354,12 +380,15 @@ void SetupDB() date.SetToGMTime(); // insert SomeDude's public key - db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');"); + db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded,LocalTrustListTrust) VALUES('SSK@NuBL7aaJ6Cn4fB7GXFb9Zfi8w1FhPyW3oKgU9TweZMw,iXez4j3qCpd596TxXiJgZyTq9o-CElEuJxm~jNNZAuA,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"',51);"); // insert Shadow Panther's public key db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@~mimyB1kmH4f7Cgsd2wM2Qv2NxrZHRMM6IY8~7EWRVQ,fxTKkR0TYhgMYb-vEGAv55sMOxCGD2xhE4ZxWHxdPz4,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');"); // insert garfield's public key db->Execute("INSERT INTO tblIdentity(PublicKey,DateAdded) VALUES('SSK@T8l1IEGU4-PoASFzgc2GYhIgRzUvZsKdoQWeuLHuTmM,QLxAPfkGis8l5NafNpSCdbxzXhBlu9WL8svcqJw9Mpo,AQACAAE/','"+date.Format("%Y-%m-%d %H:%M:%S")+"');"); + // TODO remove sometime after 0.1.17 + FixCapitalBoardNames(); + } void ConvertDB0100To0101() @@ -392,6 +421,48 @@ void ConvertDB0100To0101() db->Execute("UPDATE tblDBVersion SET Major=1, Minor=1;"); } +void ConvertDB0101To0103() +{ + // remove default 50 from trust fields and set default to NULL + SQLite3DB::DB *db=SQLite3DB::DB::Instance(); + db->Execute("CREATE TEMPORARY TABLE tblIdentityTemp AS SELECT * FROM tblIdentity;"); + db->Execute("DROP TABLE IF EXISTS tblIdentity;"); + db->Execute("CREATE TABLE IF NOT EXISTS tblIdentity(\ + IdentityID INTEGER PRIMARY KEY,\ + PublicKey TEXT UNIQUE,\ + Name TEXT,\ + SingleUse BOOL CHECK(SingleUse IN('true','false')) DEFAULT 'false',\ + PublishTrustList BOOL CHECK(PublishTrustList IN('true','false')) DEFAULT 'false',\ + PublishBoardList BOOL CHECK(PublishBoardList IN('true','false')) DEFAULT 'false',\ + DateAdded DATETIME,\ + LastSeen DATETIME,\ + LocalMessageTrust INTEGER CHECK(LocalMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\ + PeerMessageTrust INTEGER CHECK(PeerMessageTrust BETWEEN 0 AND 100) DEFAULT NULL,\ + LocalTrustListTrust INTEGER CHECK(LocalTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL,\ + PeerTrustListTrust INTEGER CHECK(PeerTrustListTrust BETWEEN 0 AND 100) DEFAULT NULL\ + );"); + db->Execute("INSERT INTO tblIdentity SELECT * FROM tblIdentityTemp;"); + db->Execute("DROP TABLE IF EXISTS tblIdentityTemp;"); + + // add SaveReceivedMessages field to tblBoard + db->Execute("ALTER TABLE tblBoard ADD COLUMN SaveReceivedMessages BOOL CHECK(SaveReceivedMessages IN('true','false')) DEFAULT 'true';"); + + db->Execute("UPDATE tblDBVersion SET Major=1, Minor=3;"); +} + +void ConvertDB0103To0104() +{ + // add MessageIndex to tblMessage + DateTime date; + SQLite3DB::DB *db=SQLite3DB::DB::Instance(); + db->Execute("ALTER TABLE tblMessage ADD COLUMN MessageIndex INTEGER;"); + db->Execute("CREATE UNIQUE INDEX IF NOT EXISTS idxMessageRequest ON tblMessageRequests(IdentityID,Day,RequestIndex);"); + db->Execute("ALTER TABLE tblLocalIdentity ADD COLUMN DateCreated DATETIME;"); + date.SetToGMTime(); + db->Execute("UPDATE tblLocalIdentity SET DateCreated='"+date.Format("%Y-%m-%d %H:%M:%S")+"' WHERE DateCreated IS NULL;"); + db->Execute("UPDATE tblDBVersion SET Major=1, Minor=4;"); +} + void SetupDefaultOptions() { // 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 @@ -565,6 +636,24 @@ void SetupDefaultOptions() st.Step(); st.Reset(); + st.Bind(0,"ChangeMessageTrustOnReply"); + st.Bind(1,"0"); + st.Bind(2,"How much the local message trust level of an identity should change when you reply to one of their messages."); + st.Step(); + st.Reset(); + + st.Bind(0,"AddNewPostFromIdentities"); + st.Bind(1,"false"); + st.Bind(2,"Set to true to automatically create new identities when you send a message using a new name. If you set this to false, posting messages will fail until you manually create the identity."); + st.Step(); + st.Reset(); + + st.Bind(0,"DeleteMessagesOlderThan"); + st.Bind(1,"180"); + st.Bind(2,"Automatically delete messages older than this many days."); + st.Step(); + st.Reset(); + } void SetupLogFile() @@ -690,3 +779,67 @@ void StartThreads(std::vector &threads) } */ + +void FixCapitalBoardNames() +{ + SQLite3DB::DB *db=SQLite3DB::DB::Instance(); + + SQLite3DB::Statement st=db->Prepare("SELECT BoardID,BoardName FROM tblBoard WHERE BoardID NOT IN (SELECT BoardID FROM tblAdministrationBoard);"); + SQLite3DB::Statement st2=db->Prepare("SELECT BoardID FROM tblBoard WHERE BoardName=?;"); + SQLite3DB::Statement del=db->Prepare("DELTE FROM tblBoard WHERE BoardID=?;"); + SQLite3DB::Statement upd=db->Prepare("UPDATE tblBoard SET BoardName=? WHERE BoardID=?;"); + SQLite3DB::Statement upd2=db->Prepare("UPDATE tblMessage SET ReplyBoardID=? WHERE ReplyBoardID=?;"); + SQLite3DB::Statement upd3=db->Prepare("UPDATE tblMessageBoard SET BoardID=? WHERE BoardID=?;"); + + st.Step(); + while(st.RowReturned()) + { + int boardid=0; + int newboardid=0; + std::string name=""; + std::string lowername=""; + + st.ResultInt(0,boardid); + st.ResultText(1,name); + + lowername=name; + StringFunctions::LowerCase(lowername,lowername); + + if(name!=lowername) + { + st2.Bind(0,lowername); + st2.Step(); + + if(st2.RowReturned()) + { + st2.ResultInt(0,newboardid); + + upd2.Bind(0,newboardid); + upd2.Bind(1,boardid); + upd2.Step(); + upd2.Reset(); + + upd3.Bind(0,newboardid); + upd3.Bind(1,boardid); + upd3.Step(); + upd3.Reset(); + + del.Bind(0,boardid); + del.Step(); + del.Reset(); + } + else + { + upd.Bind(0,lowername); + upd.Bind(1,boardid); + upd.Step(); + upd.Reset(); + } + + st2.Reset(); + } + + st.Step(); + } + +}