Revision: 1030
http://mrbs.svn.sourceforge.net/mrbs/?rev=1030&view=rev
Author: jberanek
Date: 2009-02-24 22:27:19 +0000 (Tue, 24 Feb 2009)
Log Message:
-----------
Fixes for utilising 'db' auth method with a Postgresql database:
* The new upgrade code used "ALTER TABLE ADD col AFTER othercol" syntax,
which isn't available in Postgresql. Added a new DB abstraction call,
sql_syntax_addcolumn_after($col), which returns an empty string in
any DB abstraction (like Postgresql) that doesn't have the AFTER
syntax.
* Created another DB abstraction call,
sql_syntax_createtable_autoincrementcolumn(), which returns the correct
SQL to specify a column as being an auto-incrementing integer. This
is required because the syntax for this is quite different between
MySQL and Postgresql. edit_users.php now uses this to create the
mrbs_users table.
* Removed the use of the "REPLACE INTO" SQL in edit_users.php, which
Postgresql doesn't support. Based on the patches received from
Taryn East.
Modified Paths:
--------------
mrbs/trunk/web/dbsys.inc
mrbs/trunk/web/edit_users.php
mrbs/trunk/web/mysql.inc
mrbs/trunk/web/mysqli.inc
mrbs/trunk/web/pgsql.inc
Modified: mrbs/trunk/web/dbsys.inc
===================================================================
--- mrbs/trunk/web/dbsys.inc 2009-02-24 19:26:53 UTC (rev 1029)
+++ mrbs/trunk/web/dbsys.inc 2009-02-24 22:27:19 UTC (rev 1030)
@@ -392,7 +392,58 @@
return $f($fieldname, $s, $db_conn);
}
+// Returns the syntax for a caseless "contains" function
+function sql_syntax_addcolumn_after($fieldname)
+{
+ if (func_num_args() > 1)
+ {
+ $handle = func_get_arg(1);
+ $db_sys = $handle['system'];
+ $db_conn = $handle['connection'];
+ }
+ else
+ {
+ global $dbsys;
+ $db_sys = $dbsys;
+ $db_conn = null;
+ }
+
+ $f = "sql_${db_sys}_syntax_addcolumn_after";
+
+ if (function_exists($f))
+ {
+ return $f($fieldname, $db_conn);
+ }
+ else
+ {
+ return "";
+ }
+}
+
+
+// Returns the syntax for a caseless "contains" function
+function sql_syntax_createtable_autoincrementcolumn()
+{
+ if (func_num_args() > 0)
+ {
+ $handle = func_get_arg(0);
+ $db_sys = $handle['system'];
+ $db_conn = $handle['connection'];
+ }
+ else
+ {
+ global $dbsys;
+
+ $db_sys = $dbsys;
+ $db_conn = null;
+ }
+
+ $f = "sql_${db_sys}_syntax_createtable_autoincrementcolumn";
+ return $f($db_conn);
+}
+
+
// Return the name of a column in a result object
function sql_field_name($result, $index)
{
Modified: mrbs/trunk/web/edit_users.php
===================================================================
--- mrbs/trunk/web/edit_users.php 2009-02-24 19:26:53 UTC (rev 1029)
+++ mrbs/trunk/web/edit_users.php 2009-02-24 22:27:19 UTC (rev 1030)
@@ -61,6 +61,7 @@
$field_props[$field_name]['type'] = sql_field_type($result, $i);
$field_props[$field_name]['istext'] = ($field_props[$field_name]['type']
== 'string') ? true : false;
$field_props[$field_name]['isnum'] =
preg_match('/(int|real)/',$field_props[$field_name]['type']) ? true : false;
+ $field_props[$field_name]['isbool'] = ($field_props[$field_name]['type']
== 'boolean') ? true : false;
}
sql_free($result);
}
@@ -78,7 +79,7 @@
CREATE TABLE $tbl_users
(
/* The first four fields are required. Don't remove. */
- id int NOT NULL auto_increment,
+ id ".sql_syntax_createtable_autoincrementcolumn().",
level smallint DEFAULT '0' NOT NULL, /* play safe and give no rights */
name varchar(30),
password varchar(40),
@@ -109,7 +110,7 @@
{
// Default is '1' because we will assume all existing entries in the
database are ordinary
// users. In a moment we will go through the admins and upgrade them.
- $r = sql_command("ALTER TABLE $tbl_users ADD level smallint DEFAULT '1' NOT
NULL AFTER id");
+ $r = sql_command("ALTER TABLE $tbl_users ADD COLUMN level smallint DEFAULT
'1' NOT NULL ".sql_syntax_addcolumn_after("id"));
if ($r == -1)
{
// No need to localize this: Only the admin running this for the first
time would see it.
@@ -445,31 +446,25 @@
exit;
}
-
- if ($Id >= 0)
- {
- $operation = "replace into $tbl_users values (";
- }
- else
- {
- $operation = "insert into $tbl_users values (";
- $Id = sql_query1("select max(id) from $tbl_users;") + 1; /* Use the last
index + 1 */
- /* Note: If the table is empty, sql_query1 returns -1. So use index 0. */
- }
+ $sql_fields = array();
- $i = 0;
+ // For each db column, try to fetch out an appropriate form field value
foreach ($fields as $fieldname)
{
if ($fieldname=="id")
{
- $value = $Id;
+ // We don't add or update the id - that's autoincremented in the db
+ // so move onto the next value
+ continue;
}
else if ($fieldname=="name")
{
+ // convert to lowercase so that authentication will be case insensitive
$value = strtolower(get_form_var('Field_name', 'string'));
}
else if (($fieldname=="password") && ($password0!=""))
{
+ // Hash the password for security
$value=md5($password0);
}
else if ($fieldname=="level")
@@ -489,31 +484,84 @@
}
else
{
- $value = get_form_var("Field_$fieldname",
$field_props[$fieldname]['istext'] ? 'string' : 'int');
+ $value = get_form_var("Field_$fieldname",
$field_props[$fieldname]['type']);
}
- if ($i > 0)
+ // pre-process the field value for SQL
+ if ($field_props[$fieldname]['istext'])
{
- $operation = $operation . ", ";
+ $value = "'" . addslashes($value) . "'";
}
- if ($field_props[$fieldname]['istext'])
+ else if ($field_props[$fieldname]['isbool'])
{
- $operation .= "'" . addslashes($value) . "'";
+ if ($value && $value == true)
+ {
+ $value = "TRUE";
+ }
+ else
+ {
+ $value = "FALSE";
+ }
}
else
{
- if ($field_props[$fieldname]['isnum'] && ($value == ""))
+ // put in a sensible default for a missing field
+ if (($value == null) || ($value == ''))
{
- $value = "0";
+ if ($field_props[$fieldname]['isnum'])
+ {
+ $value = "0";
+ }
+ else
+ {
+ $value = "NULL";
+ }
}
- $operation = $operation . $value;
}
- $i++;
+
+ /* If we got here, we have a valid, sql-ified value for this field,
+ * so save it for later */
+ $sql_fields[$fieldname] = $value;
+
+ } /* end for each column of user database */
+
+ /* Now generate the SQL operation based on the given array of fields */
+ if ($Id >= 0)
+ {
+ /* if the Id exists - then we are editing an existing user, rather th
+ * creating a new one */
+
+ $assign_array = array();
+ $operation = "UPDATE $tbl_users SET ";
+
+ foreach ($sql_fields as $fieldname => $value)
+ {
+ array_push($assign_array,"$fieldname=$value");
+ }
+ $operation .= implode(",", $assign_array) . " WHERE id=$Id;";
}
- $operation = $operation . ");";
+ else
+ {
+ /* The id field doesn't exist, so we're adding a new user */
-// print $operation . "<br>\n";
-// exit;
+ $fields_list = array();
+ $values_list = array();
+
+ foreach ($sql_fields as $fieldname => $value)
+ {
+ array_push($fields_list,$fieldname);
+ array_push($values_list,$value);
+ }
+
+ $operation = "INSERT INTO $tbl_users " .
+ "(". implode(",",$fields_list) . ")" .
+ " VALUES " . "(" . implode(",",$values_list) . ");";
+ }
+
+ /* DEBUG lines - check the actual sql statement going into the db */
+ //echo "Final SQL string: <code>$operation</code>";
+ //exit;
+
$r = sql_command($operation);
if ($r == -1)
{
Modified: mrbs/trunk/web/mysql.inc
===================================================================
--- mrbs/trunk/web/mysql.inc 2009-02-24 19:26:53 UTC (rev 1029)
+++ mrbs/trunk/web/mysql.inc 2009-02-24 22:27:19 UTC (rev 1030)
@@ -266,6 +266,26 @@
}
+// Generate non-standard SQL to add a table column after another specified
+// column
+function sql_mysql_addcolumn_after($fieldname, $db_conn = null)
+{
+ sql_mysql_ensure_handle($db_conn);
+
+ return "AFTER $fieldname";
+}
+
+
+// Generate non-standard SQL to specify a column as an auto-incrementing
+// integer while doing a CREATE TABLE
+function sql_mysql_syntax_createtable_autoincrementcolumn($db_conn = null)
+{
+ sql_mysql_ensure_handle($db_conn);
+
+ return "int NOT NULL auto_increment";
+}
+
+
// Returns the name of a field.
function sql_mysql_field_name($result, $index, $db_conn = null)
{
Modified: mrbs/trunk/web/mysqli.inc
===================================================================
--- mrbs/trunk/web/mysqli.inc 2009-02-24 19:26:53 UTC (rev 1029)
+++ mrbs/trunk/web/mysqli.inc 2009-02-24 22:27:19 UTC (rev 1030)
@@ -274,6 +274,25 @@
}
+// Generate non-standard SQL to add a table column after another specified
+// column
+function sql_mysqli_addcolumn_after($fieldname, $db_conn = null)
+{
+ sql_mysqli_ensure_handle($db_conn);
+
+ return "AFTER $fieldname";
+}
+
+
+// Generate non-standard SQL to specify a column as an auto-incrementing
+// integer while doing a CREATE TABLE
+function sql_mysqli_syntax_createtable_autoincrementcolumn($db_conn = null)
+{
+ sql_mysqli_ensure_handle($db_conn);
+
+ return "int NOT NULL auto_increment";
+}
+
// Returns the name of a field.
function sql_mysqli_field_name($result, $index, $db_conn = null)
{
Modified: mrbs/trunk/web/pgsql.inc
===================================================================
--- mrbs/trunk/web/pgsql.inc 2009-02-24 19:26:53 UTC (rev 1029)
+++ mrbs/trunk/web/pgsql.inc 2009-02-24 22:27:19 UTC (rev 1030)
@@ -292,6 +292,16 @@
}
+// Generate non-standard SQL to specify a column as an auto-incrementing
+// integer while doing a CREATE TABLE
+function sql_pgsql_syntax_createtable_autoincrementcolumn($db_conn = null)
+{
+ sql_pgsql_ensure_handle($db_conn);
+
+ return "serial";
+}
+
+
// Returns the name of a field.
function sql_pgsql_field_name($result, $index, $db_conn = null)
{
This was sent by the SourceForge.net collaborative development platform, the
world's largest Open Source development site.
------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
