Revision: 2618
https://sourceforge.net/p/mrbs/code/2618/
Author: cimorrison
Date: 2012-12-29 18:39:36 +0000 (Sat, 29 Dec 2012)
Log Message:
-----------
Made deleting a room work with the new tables
Modified Paths:
--------------
mrbs/branches/linked_bookings/web/dbsys.inc
mrbs/branches/linked_bookings/web/mysql.inc
mrbs/branches/linked_bookings/web/mysqli.inc
mrbs/branches/linked_bookings/web/pgsql.inc
Added Paths:
-----------
mrbs/branches/linked_bookings/web/del.php
Removed Paths:
-------------
mrbs/branches/linked_bookings/web/del.php
Modified: mrbs/branches/linked_bookings/web/dbsys.inc
===================================================================
--- mrbs/branches/linked_bookings/web/dbsys.inc 2012-12-27 15:38:31 UTC (rev
2617)
+++ mrbs/branches/linked_bookings/web/dbsys.inc 2012-12-29 18:39:36 UTC (rev
2618)
@@ -446,7 +446,7 @@
// Returns the syntax for a case sensitive string "equals" function
// (By default MySQL is case insensitive, whereas PostgreSQL is not)
// NB: This function is also assumed to do a strict comparison, ie
-// take account of training spaces. (The '=' comparison in MySQL allows
+// take account of trailing spaces. (The '=' comparison in MySQL allows
// trailing spaces, eg 'john' = 'john ').
function sql_syntax_casesensitive_equals($fieldname, $s)
{
@@ -565,6 +565,33 @@
}
+// Returns the syntax for a DELETE FROM $target_table which is
+// joined on $join_table using $join_condition with deleted
+// rows selected by a $where_condition
+function sql_syntax_delete_from_with_join($target_table, $join_table,
+ $join_condition, $where_condition)
+{
+ if (func_num_args() > 4)
+ {
+ $handle = func_get_arg(4);
+ $db_sys = $handle['system'];
+ $db_conn = $handle['connection'];
+ }
+ else
+ {
+ global $dbsys;
+
+ $db_sys = $dbsys;
+ $db_conn = null;
+ }
+
+ $f = "sql_${db_sys}_syntax_delete_from_with_join";
+ return $f($target_table, $join_table,
+ $join_condition, $where_condition,
+ $db_conn);
+}
+
+
// Return the name of a column in a result object
function sql_field_name($result, $index)
{
Copied: mrbs/branches/linked_bookings/web/del.php (from rev 2597,
mrbs/trunk/web/del.php)
===================================================================
--- mrbs/trunk/web/del.php 2012-12-16 14:12:44 UTC (rev 2597)
+++ mrbs/branches/linked_bookings/web/del.php 2012-12-29 18:39:36 UTC (rev
2618)
@@ -20,13 +20,35 @@
if (isset($confirm))
{
// They have confirmed it already, so go blast!
+ $commands = array();
+
+ // First take out all appointments for this room in the entry table
+ // Good for PG
+ $commands[] = sql_syntax_delete_from_with_join($tbl_entry,
+ $tbl_room_entry,
+
"$tbl_entry.id=$tbl_room_entry.entry_id",
+
"$tbl_room_entry.room_id=$room");
+
+ // Then take out all appointments for this room in the repeat table
+ $commands[] = sql_syntax_delete_from_with_join($tbl_repeat,
+ $tbl_room_repeat,
+
"$tbl_repeat.id=$tbl_room_repeat.repeat_id",
+
"$tbl_room_repeat.room_id=$room");
+
+ // Finally take out the room itself (the room_entry and room_repeat
+ // rows will be deleted by a cascade operation when the corresponding
+ // rows from the entry/repeat/room tables are deleted)
+ $commands[] = "DELETE FROM $tbl_room WHERE id=$room";
+
sql_begin();
- // First take out all appointments for this room
- sql_command("delete from $tbl_entry where room_id=$room");
- sql_command("delete from $tbl_repeat where room_id=$room");
-
- // Now take out the room itself
- sql_command("delete from $tbl_room where id=$room");
+ foreach ($commands as $command)
+ {
+ if (sql_command($command) < 0)
+ {
+ trigger_error(sql_error(), E_USER_WARNING);
+ fatal_error(TRUE, get_vocab("fatal_db_error"));
+ }
+ }
sql_commit();
// Go back to the admin page
@@ -39,7 +61,10 @@
// We tell them how bad what they're about to do is
// Find out how many appointments would be deleted
- $sql = "select name, start_time, end_time from $tbl_entry where
room_id=$room";
+ $sql = "SELECT name, start_time, end_time
+ FROM $tbl_entry E, $tbl_room_entry RE
+ WHERE E.id=RE.entry_id
+ AND RE.room_id=$room";
$res = sql_query($sql);
if (! $res)
{
@@ -79,11 +104,11 @@
{
// We are only going to let them delete an area if there are
// no rooms. its easier
- $n = sql_query1("select count(*) from $tbl_room where area_id=$area");
+ $n = sql_query1("SELECT COUNT(*) FROM $tbl_room WHERE area_id=$area");
if ($n == 0)
{
// OK, nothing there, lets blast it away
- sql_command("delete from $tbl_area where id=$area");
+ sql_command("DELETE FROM $tbl_area WHERE id=$area");
// Redirect back to the admin page
header("Location: admin.php");
Modified: mrbs/branches/linked_bookings/web/mysql.inc
===================================================================
--- mrbs/branches/linked_bookings/web/mysql.inc 2012-12-27 15:38:31 UTC (rev
2617)
+++ mrbs/branches/linked_bookings/web/mysql.inc 2012-12-29 18:39:36 UTC (rev
2618)
@@ -335,6 +335,28 @@
}
+// Returns the syntax for a DELETE FROM $target_table which is
+// joined on $join_table using $join_condition with deleted
+// rows selected by a $where_condition
+function sql_mysql_syntax_delete_from_with_join($target_table, $join_table,
+ $join_condition,
$where_condition,
+ $db_conn = null)
+{
+ $conditions = array('join_condition', 'where_condition');
+ foreach ($conditions as $condition)
+ {
+ $$condition = str_replace("$target_table.", "T.", $$condition);
+ $$condition = str_replace("$join_table.", "J.", $$condition);
+ }
+ $sql = "DELETE FROM T
+ USING `$target_table` T
+ INNER JOIN `$join_table` J
+ ON $join_condition
+ WHERE $where_condition";
+ return $sql;
+}
+
+
// Returns the name of a field.
function sql_mysql_field_name($result, $index, $db_conn = null)
{
Modified: mrbs/branches/linked_bookings/web/mysqli.inc
===================================================================
--- mrbs/branches/linked_bookings/web/mysqli.inc 2012-12-27 15:38:31 UTC
(rev 2617)
+++ mrbs/branches/linked_bookings/web/mysqli.inc 2012-12-29 18:39:36 UTC
(rev 2618)
@@ -343,6 +343,28 @@
}
+// Returns the syntax for a DELETE FROM $target_table which is
+// joined on $join_table using $join_condition with deleted
+// rows selected by a $where_condition
+function sql_mysqli_syntax_delete_from_with_join($target_table, $join_table,
+ $join_condition,
$where_condition,
+ $db_conn = null)
+{
+ $conditions = array('join_condition', 'where_condition');
+ foreach ($conditions as $condition)
+ {
+ $$condition = str_replace("$target_table.", "T.", $$condition);
+ $$condition = str_replace("$join_table.", "J.", $$condition);
+ }
+ $sql = "DELETE FROM T
+ USING `$target_table` T
+ INNER JOIN `$join_table` J
+ ON $join_condition
+ WHERE $where_condition";
+ return $sql;
+}
+
+
// Returns the name of a field.
function sql_mysqli_field_name($result, $index, $db_conn = null)
{
Modified: mrbs/branches/linked_bookings/web/pgsql.inc
===================================================================
--- mrbs/branches/linked_bookings/web/pgsql.inc 2012-12-27 15:38:31 UTC (rev
2617)
+++ mrbs/branches/linked_bookings/web/pgsql.inc 2012-12-29 18:39:36 UTC (rev
2618)
@@ -341,6 +341,28 @@
}
+// Returns the syntax for a DELETE FROM $target_table which is
+// joined on $join_table using $join_condition with deleted
+// rows selected by a $where_condition
+function sql_pgsql_syntax_delete_from_with_join($target_table, $join_table,
+ $join_condition,
$where_condition,
+ $db_conn = null)
+{
+ $conditions = array('join_condition', 'where_condition');
+ foreach ($conditions as $condition)
+ {
+ $$condition = str_replace("$target_table.", "T.", $$condition);
+ $$condition = str_replace("$join_table.", "J.", $$condition);
+ }
+ $sql = "DELETE FROM \"$target_table\"
+ USING \"$target_table\" T
+ INNER JOIN $join_table J
+ ON $join_condition
+ WHERE $where_condition";
+ return $sql;
+}
+
+
// Returns the name of a field.
function sql_pgsql_field_name($result, $index, $db_conn = null)
{
------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. SALE $99.99 this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122912
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
