Revision: 1307
http://mrbs.svn.sourceforge.net/mrbs/?rev=1307&view=rev
Author: cimorrison
Date: 2010-03-21 08:54:33 +0000 (Sun, 21 Mar 2010)
Log Message:
-----------
The room editing pages (admin.php and edit_area_room.php) will now recognise
user defined columns in the room table, displaying them in the room editing
form as appropriate. This enables information about the rooms to be added to
the system (eg coffee machine? whiteboard? etc.). At the moment only text,
varchar, int, smallint and tinyint are supported, displayed as textarea, text
or checkbox fields as appropriate. Smaallints and tinyints are assumed to be
booleans and are displayed as checkboxes. Text descriptions are looked for in
the lang files with the tag room.column_name, eg room.coffee_machine, enabling
translations to be provided. If not present, the column name will be used for
labels etc.
Modified Paths:
--------------
mrbs/trunk/web/admin.php
mrbs/trunk/web/dbsys.inc
mrbs/trunk/web/edit_area_room.php
mrbs/trunk/web/mrbs-ielte6.css
mrbs/trunk/web/mrbs.css.php
mrbs/trunk/web/mysql.inc
mrbs/trunk/web/mysqli.inc
mrbs/trunk/web/pgsql.inc
Added Paths:
-----------
mrbs/trunk/web/images/check.png
Modified: mrbs/trunk/web/admin.php
===================================================================
--- mrbs/trunk/web/admin.php 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/admin.php 2010-03-21 08:54:33 UTC (rev 1307)
@@ -2,6 +2,8 @@
// $Id$
+define ('MAX_TEXT_LENGTH', 20); // the maximum number of characters to
display in the room table
+
require_once "defaultincludes.inc";
// Get form variables
@@ -186,12 +188,16 @@
}
else
{
+ // Get the information about the fields in the room table
+ $fields = sql_field_info($tbl_room);
+
// Build an array with the room info
$rooms = array();
for ($i = 0; ($row = sql_row_keyed($res, $i)); $i++)
{
$rooms[] = $row;
}
+
// Display it in a table [Actually two tables side by side so that we can
// achieve a "Freeze Panes" effect: there doesn't seem to be a good way of
// getting a colgroup to scroll, so we have to distort the mark-up a
little]
@@ -248,9 +254,33 @@
echo "<table>\n";
echo "<thead>\n";
echo "<tr>\n";
- echo "<th><div>" . get_vocab("description") . "</div></th>\n";
- echo "<th><div>" . get_vocab("capacity") . "</div></th>\n";
- echo "<th><div>" . get_vocab("room_admin_email") . "</div></th>\n";
+ // ignore these columns, either because we don't want to display them,
+ // or because we have already displayed them in the header column
+ $ignore = array('id', 'area_id', 'room_name', 'sort_key');
+ foreach($fields as $field)
+ {
+ if (!in_array($field['name'], $ignore))
+ {
+ switch ($field['name'])
+ {
+ // the standard MRBS fields
+ case 'description':
+ case 'capacity':
+ case 'room_admin_email':
+ $text = get_vocab($field['name']);
+ break;
+ // any user defined fields
+ default:
+ $text = substr($tbl_room, strlen($db_tbl_prefix)); // strip the
prefix off the table name
+ $text .= "." . $field['name']; // add on the fieldname
+ // then if there's a string in the vocab array for $tag use that
+ // otherwise just use the fieldname
+ $text = (isset($vocab[$text])) ? get_vocab($text) : $field['name'];
+ break;
+ }
+ echo "<th><div>" . htmlspecialchars($text) . "</div></th>\n";
+ }
+ }
echo "</tr>\n";
echo "</thead>\n";
echo "<tbody>\n";
@@ -259,9 +289,48 @@
{
$row_class = ($row_class == "even_row") ? "odd_row" : "even_row";
echo "<tr class=\"$row_class\">\n";
- echo "<td><div>" . htmlspecialchars($r['description']) . "</div></td>\n";
- echo "<td class=\"int\"><div>" . $r['capacity'] . "</div></td>\n";
- echo "<td><div>" . htmlspecialchars($r['room_admin_email']) .
"</div></td>\n";
+ foreach($fields as $field)
+ {
+ if (!in_array($field['name'], $ignore))
+ {
+ switch ($field['name'])
+ {
+ // the standard MRBS fields
+ case 'description':
+ case 'room_admin_email':
+ echo "<td><div>" . htmlspecialchars($r[$field['name']]) .
"</div></td>\n";
+ break;
+ case 'capacity':
+ echo "<td class=\"int\"><div>" . $r[$field['name']] .
"</div></td>\n";
+ break;
+ // any user defined fields
+ default:
+ if (($field['nature'] == 'boolean') ||
+ (($field['nature'] == 'integer') && isset($field['length'])
&& ($field['length'] <= 2)) )
+ {
+ // booleans: represent by a checkmark
+ echo "<td class=\"int\"><div>";
+ echo (!empty($r[$field['name']])) ? "<img
src=\"images/check.png\" alt=\"check mark\" width=\"16\" height=\"16\">" :
" ";
+ echo "</div></td>\n";
+ }
+ elseif (($field['nature'] == 'integer') &&
isset($field['length']) && ($field['length'] > 2))
+ {
+ // integer values
+ echo "<td class=\"int\"><div>" . $r[$field['name']] .
"</div></td>\n";
+ }
+ else
+ {
+ // strings
+ $text = htmlspecialchars($r[$field['name']]);
+ echo "<td title=\"$text\"><div>";
+ echo substr($text, 0, MAX_TEXT_LENGTH);
+ echo (strlen($text) > MAX_TEXT_LENGTH) ? " ..." : "";
+ echo "</div></td>\n";
+ }
+ break;
+ }
+ }
+ }
echo "</tr>\n";
}
echo "</tbody>\n";
@@ -314,4 +383,4 @@
require_once "trailer.inc"
-?>
+?>
\ No newline at end of file
Modified: mrbs/trunk/web/dbsys.inc
===================================================================
--- mrbs/trunk/web/dbsys.inc 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/dbsys.inc 2010-03-21 08:54:33 UTC (rev 1307)
@@ -548,6 +548,60 @@
}
+// This function returns information about columns in the database in a manner
+// that is independent of the underlying database. It was originally
designed
+// for use by MRBS pages that have user defined columns in the relevant table
+// and to enable them to decide how to represent the column on a page. For
example
+// a smallint is often used to represent a boolean, which should be
represented by a
+// checkbox. (Smallints are used by MRBS for booleans in PostgreSQL because
booleans return
+// a PHP boolean type, rather than a 0 or 1 which MySQL does, and so this
makes testing
+// of the results difficult).
+//
+// Returns an array with the following indices for each column
+//
+// 'name' the column name
+// 'type' the type as reported by MySQL or PostgreSQL
+// 'nature' the type mapped onto one of a generic set of types
+// (boolean, integer, real, character, binary). This enables
+// the nature to be used by MRBS code when deciding how to
+// display fields, without MRBS having to worry about the
+// differences between MySQL and PostgreSQL type names.
+// 'length' the maximum length of the field in bytes, octets or
characters
+// (Note: this could be NULL)
+//
+// NOTE: the type mapping is incomplete and just covers the types commonly
+// used by MRBS
+//
+// The difficulty is that MySQL and PostgreSQL return different formats
+// of answer for the type of field when using sql_field_type. For example
+// for a smallint, PostgreSQL will just return int, and there is no exact
equivalent
+// of the mysql_ field_ len() function. Although pg_field_size is a close
+// approximation and returns 2 for a smallint, enabling it to be distinguished
+// from an int, when used with variable length character types such as text or
+// varying character it returns -1.
+//
+// [There must be a better way of doing all this???!]
+function sql_field_info($table)
+{
+ 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}_field_info";
+ return $f($table, $db_conn);
+}
+
+
// Connect to a database host and select the supplied database
function sql_connect($system, $host, $username, $password, $db_name,
$persist = 0)
Modified: mrbs/trunk/web/edit_area_room.php
===================================================================
--- mrbs/trunk/web/edit_area_room.php 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/edit_area_room.php 2010-03-21 08:54:33 UTC (rev 1307)
@@ -1,6 +1,51 @@
<?php
// $Id$
+// If you want to add some extra columns to the room table to describe the room
+// then you can do so and this page should automatically recognise them and
handle
+// them. At the moment support is limited to the following column types:
+//
+// MySQL PostgreSQL Form input type
+// ----- ---------- ---------------
+// bigint bigint text
+// int integer text
+// mediumint text
+// smallint smallint checkbox
+// tinyint checkbox
+// text text textarea
+// tinytext textarea
+// character varying textarea
+// varchar(n) character varying(n) text/textarea, depending on the value of
n
+// character text
+// char(n) character(n) text/textarea, depending on the value of
n
+//
+// NOTE 1: For char(n) and varchar(n) fields, a text input will be presented if
+// n is less than or equal to TEXT_INPUT_MAX, otherwise a textarea box will be
+// presented.
+//
+// NOTE 2: PostgreSQL booleans are not supported, due to difficulties in
+// handling the fields in a database independent way (a PostgreSQL boolean
+// will return a PHP boolean type when read by a PHP query, whereas a MySQL
+// tinyint returns an int). In order to have a boolean field in the room
+// table you should use a smallint in PostgreSQL or a smallint or a tinyint
+// in MySQL.
+//
+// You can put a description of the column that will be used as the label in
+// the form in the appropriate lang file(s) using the tag 'room.[columnname]'.
+// For example if you want to add a column specifying whether or not a room
+// has a coffee machine you could add a column to the room table called
+// 'coffee_machine' of type tinyint(1), in MySQL, or smallint in PostgreSQL.
+// Then in the appropriate lang file(s) you would add the line
+//
+// vocab["room.coffee_machine"] = "Coffee machine"; // or appropriate
translation
+//
+// If MRBS can't find an entry for the field in the lang file, then it will use
+// the fieldname, eg 'coffee_machine'.
+
+define('TEXT_INPUT_MAX', 70); // Maximum size in characters of a user defined
+ // column for a text input to be used.
Greater than
+ // this and a textarea will be used
+
require_once "defaultincludes.inc";
require_once "mrbs_sql.inc";
@@ -44,6 +89,33 @@
$change_room = get_form_var('change_room', 'string');
$change_area = get_form_var('change_area', 'string');
+// Get the information about the fields in the room table
+$fields = sql_field_info($tbl_room);
+
+// Get any user defined form variables
+foreach($fields as $field)
+{
+ switch($field['nature'])
+ {
+ case 'character':
+ $type = 'string';
+ break;
+ case 'integer':
+ $type = 'int';
+ break;
+ // We can only really deal with the types above at the moment
+ default:
+ $type = 'string';
+ break;
+ }
+ $var = "f_" . $field['name'];
+ $$var = get_form_var($var, $type);
+ if (($type == 'int') && ($$var === ''))
+ {
+ unset($$var);
+ }
+}
+
// If we dont know the right date then make it up
if (!isset($day) or !isset($month) or !isset($year))
{
@@ -82,6 +154,7 @@
$valid_room_name = TRUE;
+
// PHASE 2
// -------
// Unauthorised users shouldn't normally be able to reach Phase 2, but just in
case
@@ -131,11 +204,63 @@
// If everything is still OK, update the databasae
else
{
- $sql = "UPDATE $tbl_room SET room_name='" . addslashes($room_name)
- . "', sort_key='" . addslashes($sort_key)
- . "', description='" . addslashes($description)
- . "', capacity=$capacity, area_id=$new_area, room_admin_email='"
- . addslashes($room_admin_email) . "' WHERE id=$room";
+ $sql = "UPDATE $tbl_room SET ";
+ $n_fields = count($fields);
+ $first_field = TRUE;
+ foreach ($fields as $field)
+ {
+ if ($field['name'] != 'id') // don't do anything with the id field
+ {
+ if (!$first_field)
+ {
+ $sql .= ", ";
+ }
+ else
+ {
+ $first_field = FALSE;
+ }
+ switch ($field['name'])
+ {
+ // first of all deal with the standard MRBS fields
+ case 'area_id':
+ $sql .= "area_id=$new_area";
+ break;
+ case 'room_name':
+ $sql .= "room_name='" . addslashes($room_name) . "'";
+ break;
+ case 'sort_key':
+ $sql .= "sort_key='" . addslashes($sort_key) . "'";
+ break;
+ case 'description':
+ $sql .= "description='" . addslashes($description) . "'";
+ break;
+ case 'capacity':
+ $sql .= "capacity=$capacity";
+ break;
+ case 'room_admin_email':
+ $sql .= "room_admin_email='" . addslashes($room_admin_email) .
"'";
+ break;
+ // then look at any user defined fields
+ default:
+ $var = "f_" . $field['name'];
+ switch ($field['nature'])
+ {
+ case 'integer':
+ if (!isset($$var))
+ {
+ $$var = 'NULL';
+ }
+ break;
+ default:
+ $$var = "'" . addslashes($$var) . "'";
+ break;
+ }
+ $sql .= $field['name'] . "=" . $$var;
+ break;
+ }
+ }
+ }
+ $sql .= " WHERE id=$room";
if (sql_command($sql) < 0)
{
fatal_error(0, get_vocab("update_room_failed") . sql_error());
@@ -287,13 +412,12 @@
// THE ROOM FORM
if (!empty($room))
{
- $res = sql_query("SELECT * FROM $tbl_room WHERE id=$room");
+ $res = sql_query("SELECT * FROM $tbl_room WHERE id=$room LIMIT 1");
if (! $res)
{
fatal_error(0, get_vocab("error_room") . $room . get_vocab("not_found"));
}
$row = sql_row_keyed($res, 0);
- sql_free($res);
?>
<form class="form_general" id="edit_room" action="edit_area_room.php"
method="post">
@@ -345,37 +469,81 @@
echo "<input type=\"hidden\" name=\"old_area\" value=\"" .
$row['area_id'] . "\">\n";
echo "</div>\n";
- // Room name
- echo "<div>\n";
- echo "<label for=\"room_name\">" . get_vocab("name") . ":</label>\n";
- echo "<input type=\"text\" id=\"room_name\" name=\"room_name\" value=\""
. htmlspecialchars($row["room_name"]) . "\"$disabled>\n";
- echo "<input type=\"hidden\" name=\"old_room_name\" value=\"" .
htmlspecialchars($row["room_name"]) . "\">\n";
- echo "</div>\n";
+ foreach ($fields as $field)
+ {
+ if (!in_array($field['name'], array('id', 'area_id'))) // Ignore
certain fields
+ {
+ echo "<div>\n";
+ switch($field['name'])
+ {
+ // first of all deal with the standard MRBS fields
+ case 'room_name':
+ echo "<label for=\"room_name\">" . get_vocab("name") .
":</label>\n";
+ echo "<input type=\"text\" id=\"room_name\" name=\"room_name\"
value=\"" . htmlspecialchars($row["room_name"]) . "\"$disabled>\n";
+ echo "<input type=\"hidden\" name=\"old_room_name\" value=\"" .
htmlspecialchars($row["room_name"]) . "\">\n";
+ break;
+ case 'sort_key':
+ echo "<label for=\"sort_key\" title=\"" .
get_vocab("sort_key_note") . "\">" . get_vocab("sort_key") . ":</label>\n";
+ echo "<input type=\"text\" id=\"sort_key\" name=\"sort_key\"
value=\"" . htmlspecialchars($row["sort_key"]) . "\"$disabled>\n";
+ break;
+ case 'description':
+ echo "<label for=\"description\">" . get_vocab("description") .
":</label>\n";
+ echo "<input type=\"text\" id=\"description\"
name=\"description\" value=\"" . htmlspecialchars($row["description"]) .
"\"$disabled>\n";
+ break;
+ case 'capacity':
+ echo "<label for=\"capacity\">" . get_vocab("capacity") .
":</label>\n";
+ echo "<input type=\"text\" id=\"capacity\" name=\"capacity\"
value=\"" . $row["capacity"] . "\"$disabled>\n";
+ break;
+ case 'room_admin_email':
+ echo "<label for=\"room_admin_email\">" .
get_vocab("room_admin_email") . ":</label>\n";
+ echo "<input type=\"text\" id=\"room_admin_email\"
name=\"room_admin_email\" maxlength=\"75\" value=\"" .
htmlspecialchars($row["room_admin_email"]) . "\"$disabled>\n";
+ break;
+ // then look at any user defined fields
+ default:
+ $tag = substr($tbl_room, strlen($db_tbl_prefix)); // strip the
prefix off the table name
+ $tag .= "." . $field['name']; // add on the fieldname
+ // then if there's a string in the vocab array for $tag use that
+ // otherwise just use the fieldname
+ $label_text = (isset($vocab[$tag])) ? get_vocab($tag) :
$field['name'];
+ echo "<label for=\"f_" . $field['name'] .
"\">$label_text:</label>\n";
+ // Output a checkbox if it's a boolean or integer <= 2 bytes
(which we will
+ // assume are intended to be booleans)
+ if (($field['nature'] == 'boolean') ||
+ (($field['nature'] == 'integer') && isset($field['length'])
&& ($field['length'] <= 2)) )
+ {
+ echo "<input type=\"checkbox\" class=\"checkbox\" " .
+ "id=\"f_" . $field['name'] . "\" " .
+ "name=\"f_" . $field['name'] . "\" " .
+ "value=\"1\" " .
+ ((!empty($row[$field['name']])) ? " checked=\"checked\""
: "") .
+ "$disabled>\n";
+ }
+ // Output a textarea if it's a character string longer than the
limit for a
+ // text input
+ elseif (($field['nature'] == 'character') &&
isset($field['length']) && ($field['length'] > TEXT_INPUT_MAX))
+ {
+ echo "<textarea rows=\"8\" cols=\"40\" " .
+ "id=\"f_" . $field['name'] . "\" " .
+ "name=\"f_" . $field['name'] . "\" " .
+ "$disabled>\n";
+ echo htmlspecialchars($row[$field['name']]);
+ echo "</textarea>\n";
+ }
+ // Otherwise output a text input
+ else
+ {
+ echo "<input type=\"text\" " .
+ "id=\"f_" . $field['name'] . "\" " .
+ "name=\"f_" . $field['name'] . "\" " .
+ "value=\"" . htmlspecialchars($row[$field['name']]) .
"\"" .
+ "$disabled>\n";
+ }
+ break;
+ }
+ echo "</div>\n";
+ }
+ }
- // Sort key
- echo "<div>\n";
- echo "<label for=\"sort_key\" title=\"" . get_vocab("sort_key_note") .
"\">" . get_vocab("sort_key") . ":</label>\n";
- echo "<input type=\"text\" id=\"sort_key\" name=\"sort_key\" value=\"" .
htmlspecialchars($row["sort_key"]) . "\"$disabled>\n";
- echo "</div>\n";
-
- // Description
- echo "<div>\n";
- echo "<label for=\"description\">" . get_vocab("description") .
":</label>\n";
- echo "<input type=\"text\" id=\"description\" name=\"description\"
value=\"" . htmlspecialchars($row["description"]) . "\"$disabled>\n";
- echo "</div>\n";
-
- // Capacity
- echo "<div>\n";
- echo "<label for=\"capacity\">" . get_vocab("capacity") . ":</label>\n";
- echo "<input type=\"text\" id=\"capacity\" name=\"capacity\" value=\"" .
$row["capacity"] . "\"$disabled>\n";
- echo "</div>\n";
-
- // Room admin email
- echo "<div>\n";
- echo "<label for=\"room_admin_email\">" . get_vocab("room_admin_email")
. ":</label>\n";
- echo "<input type=\"text\" id=\"room_admin_email\"
name=\"room_admin_email\" maxlength=\"75\" value=\"" .
htmlspecialchars($row["room_admin_email"]) . "\"$disabled>\n";
- echo "</div>\n";
-
// Submit and Back buttons (Submit only if they're an admin)
echo "<fieldset class=\"submit_buttons\">\n";
echo "<legend></legend>\n";
Added: mrbs/trunk/web/images/check.png
===================================================================
(Binary files differ)
Property changes on: mrbs/trunk/web/images/check.png
___________________________________________________________________
Added: svn:mime-type
+ image/png
Modified: mrbs/trunk/web/mrbs-ielte6.css
===================================================================
--- mrbs/trunk/web/mrbs-ielte6.css 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/mrbs-ielte6.css 2010-03-21 08:54:33 UTC (rev 1307)
@@ -8,6 +8,9 @@
.form_admin label {height: 2.0em} /* min-height not recognised by
IE6 and below */
.form_admin input {margin-top: 0} /* negative margins remove the
input border in IE6 */
.form_admin input.submit {display: inline} /* stops IE6 "double margin float
bug" appearing */
+div#header_column {width: 20%} /* IE6 doesn't understand
max-width */
+#header_column table {width: 100%} /* so force the table to fill all
of the 20% */
+div#body_columns {width: 80%}
/* ------------ DAY/WEEK/MONTH.PHP ------------------*/
div.cell_container {height: 100px} /* NOTE: if you change the value
of height, make sure you */
Modified: mrbs/trunk/web/mrbs.css.php
===================================================================
--- mrbs/trunk/web/mrbs.css.php 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/mrbs.css.php 2010-03-21 08:54:33 UTC (rev 1307)
@@ -99,7 +99,7 @@
width: auto; margin-top: 1.2em; margin-left: <?php echo
number_format(($admin_form_gap + $admin_form_label_width), 1, '.', '')?>em
}
.admin h2 {clear: left}
-div#area_form, div#room_form {float: left; margin: 0 0 2em 1em}
+div#area_form, div#room_form {float: left; width: 95%; padding: 0 0 2em 1em}
#area_form form {float: left; margin-right: 1em}
#area_form label#area_label {display: block; float: left; font-weight: bold;
margin-right: <?php echo $admin_form_gap ?>em}
#areaChangeForm select {display: block; float: left; margin: -0.1em 1.5em 0 0}
@@ -108,7 +108,10 @@
background-color: transparent; border: 0; padding: 0}
#areaChangeForm button img {vertical-align: middle}
-div#header_column, div#body_columns {float: left}
+div#room_info {width: 100%; float: left}
+div#header_column, div#body_columns {position: relative; float: left;
overflow-x: scroll; overflow-y: hidden}
+div#header_column {max-width: 20%}
+div#body_columns {max-width: 80%}
#room_info table {border-spacing: 0px; border-collapse: collapse;
border-color: <?php echo $admin_table_border_color ?>; border-style: solid;
border-top-width: 0; border-right-width: 1px; border-bottom-width: 1px;
border-left-width: 0}
@@ -129,7 +132,6 @@
#room_info #header_column th:first-child {border-left-color: <?php echo
$admin_table_header_back_color ?>}
-
/* ------------ DAY/WEEK/MONTH.PHP ------------------*/
<?php
Modified: mrbs/trunk/web/mysql.inc
===================================================================
--- mrbs/trunk/web/mysql.inc 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/mysql.inc 2010-03-21 08:54:33 UTC (rev 1307)
@@ -326,6 +326,92 @@
}
+// Get information about the columns in a table
+// Returns an array with the following indices for each column
+//
+// 'name' the column name
+// 'type' the type as reported by MySQL
+// 'nature' the type mapped onto one of a generic set of types
+// (boolean, integer, real, character, binary). This enables
+// the nature to be used by MRBS code when deciding how to
+// display fields, without MRBS having to worry about the
+// differences between MySQL and PostgreSQL type names.
+// 'length' the maximum length of the field in bytes, octets or
characters
+// (Note: this could be NULL)
+//
+// NOTE: the type mapping is incomplete and just covers the types commonly
+// used by MRBS
+function sql_mysql_field_info($table, $db_conn = null)
+{
+ sql_mysql_ensure_handle($db_conn);
+
+ // Map MySQL types on to a set of generic types
+ $nature_map = array('bigint' => 'integer',
+ 'char' => 'character',
+ 'double' => 'real',
+ 'float' => 'real',
+ 'int' => 'integer',
+ 'mediumint' => 'integer',
+ 'smallint' => 'integer',
+ 'text' => 'character',
+ 'tinyint' => 'integer',
+ 'tinytext' => 'character',
+ 'varchar' => 'character');
+
+ // Length in bytes of MySQL integer types
+ $int_bytes = array('bigint' => 8, // bytes
+ 'int' => 4,
+ 'mediumint' => 3,
+ 'smallint' => 2,
+ 'tinyint' => 1);
+
+ $fields = array();
+ $res = sql_mysql_query("SHOW COLUMNS FROM $table");
+ if ($res && (sql_mysql_count($res) > 0))
+ {
+ for ($i = 0; ($row = sql_mysql_row_keyed($res, $i)); $i++)
+ {
+ $name = $row['Field'];
+ $type = $row['Type'];
+ // split the type (eg 'varchar(25)') around the opening '('
+ $parts = explode('(', $type);
+ // map the type onto one of the generic natures, if a mapping exists
+ $nature = (array_key_exists($parts[0], $nature_map)) ?
$nature_map[$parts[0]] : $parts[0];
+ // now work out the length
+ if ($nature == 'integer')
+ {
+ // if it's one of the ints, then look up the length in bytes
+ $length = (array_key_exists($parts[0], $int_bytes)) ?
$int_bytes[$parts[0]] : 0;
+ }
+ elseif ($nature == 'character')
+ {
+ // if it's a character type then use the length that was in parentheses
+ // eg if it was a varchar(25), we want the 25
+ if (isset($parts[1]))
+ {
+ $length = preg_replace('/\)/', '', $parts[1]); // strip off the
closing ')'
+ }
+ // otherwise it could be any length (eg if it was a 'text')
+ else
+ {
+ $length = defined('PHP_INT_MAX') ? PHP_INT_MAX : 9999;
+ }
+ }
+ else // we're only dealing with a few simple cases at the moment
+ {
+ $length = NULL;
+ }
+
+ $fields[$i]['name'] = $name;
+ $fields[$i]['type'] = $type;
+ $fields[$i]['nature'] = $nature;
+ $fields[$i]['length'] = $length;
+ }
+ }
+ return $fields;
+}
+
+
// Connect to a database server and select a database, optionally using
// persistent connections
function sql_mysql_connect($host, $username, $password, $db_name, $persist = 0)
Modified: mrbs/trunk/web/mysqli.inc
===================================================================
--- mrbs/trunk/web/mysqli.inc 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/mysqli.inc 2010-03-21 08:54:33 UTC (rev 1307)
@@ -355,6 +355,92 @@
}
+// Get information about the columns in a table
+// Returns an array with the following indices for each column
+//
+// 'name' the column name
+// 'type' the type as reported by MySQL
+// 'nature' the type mapped onto one of a generic set of types
+// (boolean, integer, real, character, binary). This enables
+// the nature to be used by MRBS code when deciding how to
+// display fields, without MRBS having to worry about the
+// differences between MySQL and PostgreSQL type names.
+// 'length' the maximum length of the field in bytes, octets or
characters
+// (Note: this could be NULL)
+//
+// NOTE: the type mapping is incomplete and just covers the types commonly
+// used by MRBS
+function sql_mysqli_field_info($table, $db_conn = null)
+{
+ sql_mysqli_ensure_handle($db_conn);
+
+ // Map MySQL types on to a set of generic types
+ $nature_map = array('bigint' => 'integer',
+ 'char' => 'character',
+ 'double' => 'real',
+ 'float' => 'real',
+ 'int' => 'integer',
+ 'mediumint' => 'integer',
+ 'smallint' => 'integer',
+ 'text' => 'character',
+ 'tinyint' => 'integer',
+ 'tinytext' => 'character',
+ 'varchar' => 'character');
+
+ // Length in bytes of MySQL integer types
+ $int_bytes = array('bigint' => 8, // bytes
+ 'int' => 4,
+ 'mediumint' => 3,
+ 'smallint' => 2,
+ 'tinyint' => 1);
+
+ $fields = array();
+ $res = sql_mysqli_query("SHOW COLUMNS FROM $table");
+ if ($res && (sql_mysqli_count($res) > 0))
+ {
+ for ($i = 0; ($row = sql_mysqli_row_keyed($res, $i)); $i++)
+ {
+ $name = $row['Field'];
+ $type = $row['Type'];
+ // split the type (eg 'varchar(25)') around the opening '('
+ $parts = explode('(', $type);
+ // map the type onto one of the generic natures, if a mapping exists
+ $nature = (array_key_exists($parts[0], $nature_map)) ?
$nature_map[$parts[0]] : $parts[0];
+ // now work out the length
+ if ($nature == 'integer')
+ {
+ // if it's one of the ints, then look up the length in bytes
+ $length = (array_key_exists($parts[0], $int_bytes)) ?
$int_bytes[$parts[0]] : 0;
+ }
+ elseif ($nature == 'character')
+ {
+ // if it's a character type then use the length that was in parentheses
+ // eg if it was a varchar(25), we want the 25
+ if (isset($parts[1]))
+ {
+ $length = preg_replace('/\)/', '', $parts[1]); // strip off the
closing ')'
+ }
+ // otherwise it could be any length (eg if it was a 'text')
+ else
+ {
+ $length = defined('PHP_INT_MAX') ? PHP_INT_MAX : 9999;
+ }
+ }
+ else // we're only dealing with a few simple cases at the moment
+ {
+ $length = NULL;
+ }
+
+ $fields[$i]['name'] = $name;
+ $fields[$i]['type'] = $type;
+ $fields[$i]['nature'] = $nature;
+ $fields[$i]['length'] = $length;
+ }
+ }
+ return $fields;
+}
+
+
// Connect to a database server and select a database, optionally using
// persistent connections
function sql_mysqli_connect($host, $username, $password,
Modified: mrbs/trunk/web/pgsql.inc
===================================================================
--- mrbs/trunk/web/pgsql.inc 2010-03-19 09:40:34 UTC (rev 1306)
+++ mrbs/trunk/web/pgsql.inc 2010-03-21 08:54:33 UTC (rev 1307)
@@ -362,6 +362,75 @@
}
+// Get information about the columns in a table
+// Returns an array with the following indices for each column
+//
+// 'name' the column name
+// 'type' the type as reported by PostgreSQL
+// 'nature' the type mapped onto one of a generic set of types
+// (boolean, integer, real, character, binary). This enables
+// the nature to be used by MRBS code when deciding how to
+// display fields, without MRBS having to worry about the
+// differences between MySQL and PostgreSQL type names.
+// 'length' the maximum length of the field in bytes, octets or
characters
+// (Note: this could be NULL)
+//
+// NOTE: the type mapping is incomplete and just covers the types commonly
+// used by MRBS
+function sql_pgsql_field_info($table, $db_conn = null)
+{
+ sql_pgsql_ensure_handle($db_conn);
+
+ // Map PostgreSQL types on to a set of generic types
+ $nature_map = array('bigint' => 'integer',
+ 'boolean' => 'boolean',
+ 'bytea' => 'binary',
+ 'character' => 'character',
+ 'character varying' => 'character',
+ 'double precision' => 'real',
+ 'integer' => 'integer',
+ 'real' => 'real',
+ 'smallint' => 'integer',
+ 'text' => 'character');
+
+ $fields = array();
+ $res = sql_pgsql_query("SELECT column_name, data_type, numeric_precision,
+ character_maximum_length,
character_octet_length
+ FROM information_schema.columns
+ WHERE table_name ='$table'
+ ORDER BY ordinal_position");
+ if ($res && (sql_pgsql_count($res) > 0))
+ {
+ for ($i = 0; ($row = sql_pgsql_row_keyed($res, $i)); $i++)
+ {
+ $name = $row['column_name'];
+ $type = $row['data_type'];
+ // map the type onto one of the generic natures, if a mapping exists
+ $nature = (array_key_exists($type, $nature_map)) ? $nature_map[$type] :
$type;
+ // Get a length value; one of these values should be set
+ if (isset($row['numeric_precision']))
+ {
+ $length = (int) floor($row['numeric_precision'] / 8); // precision is
in bits
+ }
+ elseif (isset($row['character_maximum_length']))
+ {
+ $length = $row['character_maximum_length'];
+ }
+ elseif (isset($row['character_octet_length']))
+ {
+ $length = $row['character_octet_length'];
+ }
+
+ $fields[$i]['name'] = $name;
+ $fields[$i]['type'] = $type;
+ $fields[$i]['nature'] = $nature;
+ $fields[$i]['length'] = $length;
+ }
+ }
+ return $fields;
+}
+
+
// Connect to a database server and select a database, optionally using
// persistent connections
function sql_pgsql_connect($host, $username, $password, $db_name,
This was sent by the SourceForge.net collaborative development platform, the
world's largest Open Source development site.
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Mrbs-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mrbs-commits
