From: <au...@us...> - 2008-12-12 14:35:33
|
Revision: 85 http://quoterschoice.svn.sourceforge.net/quoterschoice/?rev=85&view=rev Author: auron_x Date: 2008-12-12 14:35:23 +0000 (Fri, 12 Dec 2008) Log Message: ----------- - added FOREIGN KEY CONSTRAINTS to Database - made real TRANSACTIONS of some mysql-queries - improved SQL-Code (no "php-caching") ATTENTION: BEFORE uploading, CHANGEs have to be applied to the DATABASE Modified Paths: -------------- trunk/adminfunctions.inc.php trunk/db-layout.sql trunk/modquotes.php Modified: trunk/adminfunctions.inc.php =================================================================== --- trunk/adminfunctions.inc.php 2008-12-12 09:10:20 UTC (rev 84) +++ trunk/adminfunctions.inc.php 2008-12-12 14:35:23 UTC (rev 85) @@ -12,24 +12,26 @@ $decision = mysql_real_escape_string($decision_array[0]) == "approve"; $quote_id = mysql_real_escape_string($decision_array[1]); - $result = true; + if(!mysql_query("BEGIN")) + return false; + if($decision) - $result = mysql_query("INSERT INTO quotes (quote, author, date) SELECT quote, author, date FROM queue WHERE id ='$quote_id'"); + if(!mysql_query("INSERT INTO quotes (quote, author, date) SELECT quote, author, date FROM queue WHERE id ='$quote_id'")) + return false; - if($result) - $result = mysql_query("DELETE FROM queue WHERE id='".$quote_id."' LIMIT 1;"); - - if($result) { - echo "Quote $quote_id wurde "; - if($decision) - echo "akzeptiert."; - else - echo "zurückgewiesen."; - return true; - } else { - echo "Datenbankfehler beim Moderieren des Quotes: ".mysql_error(); + if(mysql_query("DELETE FROM queue WHERE id='".$quote_id."' LIMIT 1")) { + if(!mysql_query("COMMIT")) + return false; + } else return false; - } + + + echo "Quote $quote_id wurde "; + if($decision) + echo "akzeptiert."; + else + echo "zurückgewiesen."; + return true; } function delete_quote($quote_id) { @@ -39,35 +41,16 @@ startsql(); $quote_id = mysql_real_escape_string($quote_id); - if(!log_delete_quote($quote_id)) - return false; + //return true if the complete transaction worked (one fail breaks the chain -> return false) + if(mysql_query("BEGIN")) + if(mysql_query("INSERT INTO deletelog (`quote_id`, `quote`, `author`, `rating`, `date`, `deletor`, `del_date`) SELECT q.id, q.quote, q.author, q.rating, q.date, m.id, ".time()." FROM quotes q, members m WHERE q.id = '".$quote_id."' AND m.id ='".$_SESSION['userid']."' LIMIT 1")) + if(mysql_query("DELETE FROM quotes WHERE id = '$quote_id' LIMIT 1")) + if(mysql_query("COMMIT")) + return true; - $sql = "DELETE FROM quotes WHERE id = '$quote_id' LIMIT 1"; - if(!mysql_query($sql)) - return false; - - return true; + return false; } -function log_delete_quote($quote_id) { - $sql = "INSERT INTO deletelog (`id`, `quote`, `author`, `rating`, `date`) SELECT id, quote, author, rating, date FROM quotes WHERE id = '".$quote_id."';"; - $result = mysql_query($sql); - echo mysql_error(); - - if($result) { - $sql = "UPDATE deletelog SET deletor = '".$_SESSION['userid']."', del_date = '".time()."' WHERE id = '".$quote_id."' LIMIT 1;"; - $result = mysql_query($sql); - echo mysql_error(); - } - - if($result) - $result = mysql_query("DELETE FROM quotes WHERE id='".$quote_id."' LIMIT 1;"); - - if(!$result) - return false; - return true; -} - function edit_quote($quote_id, $quote) { if(!canEditQuotes()) return false; @@ -76,27 +59,16 @@ $quote_id = mysql_real_escape_string($quote_id); $quote = mysql_real_escape_string($quote); - if(!log_edit_quote($quote_id, $quote)) - return false; + //return true if the complete transaction worked (one fail breaks the chain -> return false) + if(mysql_query("BEGIN")) + if(mysql_query("INSERT INTO editlog (`quote_id`, `old_quote`, `new_quote`, `editor`, `date`) SELECT q.id, q.quote, '$quote', m.id, ".time()." FROM quotes q, members m WHERE q.id = $quote_id AND m.id = ".$_SESSION['userid']." LIMIT 1")) + if(mysql_query("UPDATE quotes SET quote = '".$quote."' WHERE quotes.id = '".$quote_id."' LIMIT 1;")) + if(mysql_query("COMMIT")) + return true; - $sql = "UPDATE quotes SET quote = '".$quote."' WHERE quotes.id = '".$quote_id."' LIMIT 1;"; - if(!mysql_query($sql)) - return false; - return true; + return false; } -function log_edit_quote($quote_id, $new_quote) { - $sql = "SELECT quote FROM quotes WHERE id = '".$quote_id."' LIMIT 1;"; - $result = mysql_query($sql); - while($row = mysql_fetch_row($result)) { - $old_quote = mysql_real_escape_string($row[0]); - } - $sql = "INSERT INTO editlog (`id`, `old_quote`, `new_quote`, `editor`, `date`) VALUES('".$quote_id."', '".$old_quote."', '".$new_quote."', '".$_SESSION['userid']."', '".time()."');"; - if(!mysql_query($sql)) - return false; - return true; -} - function edit_user_permissions($user_id, $permissions) { if(!(isUserAdmin() || isSuperAdmin())) return false; Modified: trunk/db-layout.sql =================================================================== --- trunk/db-layout.sql 2008-12-12 09:10:20 UTC (rev 84) +++ trunk/db-layout.sql 2008-12-12 14:35:23 UTC (rev 85) @@ -1,9 +1,9 @@ CREATE TABLE IF NOT EXISTS `members` ( - `id` int NOT NULL auto_increment, + `id` int(11) NOT NULL auto_increment, `username` varchar(65) NOT NULL default '', `password` varchar(32) NOT NULL default '', `permissions` int(11) NOT NULL default 0, - PRIMARY KEY (`id`) + PRIMARY KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `queue` ( @@ -25,29 +25,33 @@ CREATE TABLE IF NOT EXISTS `votes` ( `vote_id` int NOT NULL auto_increment, - `quote_id` int(11) NOT NULL, + `quote_id` int(11), `cookie_id` int(11) NOT NULL, `time` int(11) NOT NULL, UNIQUE KEY `vote_id` (`vote_id`), - KEY `quote_id` (`quote_id`) + FOREIGN KEY `quote_id` (`quote_id`) REFERENCES quotes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `editlog` ( - `id` int NOT NULL, + `id` int NOT NULL auto_increment, + `quote_id` int NOT NULL, `old_quote` text NOT NULL, `new_quote` text NOT NULL, - `editor` int(11) NOT NULL, + `editor` int(11), `date` int(11) NOT NULL, - KEY `id` (`id`) + PRIMARY KEY `id` (`id`), + FOREIGN KEY `editor` (`editor`) REFERENCES members(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `deletelog` ( - `id` int NOT NULL, + `id` int NOT NULL auto_increment, + `quote_id` int NOT NULL, `quote` text NOT NULL, `author` varchar(32) NOT NULL, `rating` int NOT NULL default 0, `date` int(11) NOT NULL, - `deletor` int(11) NOT NULL default 0, + `deletor` int(11), `del_date` int(11) NOT NULL default 0, - KEY `id` (`id`) + PRIMARY KEY `id` (`id`), + FOREIGN KEY `deletor` (`deletor`) REFERENCES members(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; \ No newline at end of file Modified: trunk/modquotes.php =================================================================== --- trunk/modquotes.php 2008-12-12 09:10:20 UTC (rev 84) +++ trunk/modquotes.php 2008-12-12 14:35:23 UTC (rev 85) @@ -21,7 +21,8 @@ if(isset($_POST['modbutton'])) { echo '<div class="textbox">'; - moderate_quote($_POST['modbutton']); + if(!moderate_quote($_POST['modbutton'])) + echo "Datenbankfehler: ".mysql_error(); echo "</div>\r\n"; } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |