<?php
/**
* dbscript for PHP 4 & 5 - restful crud framework
* @version 0.3.0 -- 10-Jun-2007
* @author Brian Hendrickson <hide@address.com>
* @link http://dbscript.net/
* @copyright Copyright 2007 Brian Hendrickson
* @license http://www.opensource.org/licenses/mit-license.php MIT License
* @package dbscript
*/
/**
* MySQL
*
* adapter for the MySQL database system
*
* Usage:
* <code>
* $db = new MySQL ( 'hostname', 'database_name', 'username', 'password' );
* </code>
*
* More info...
* {@link http://dbscript.net/mysql}
*
* @package dbscript
* @author Brian Hendrickson <hide@address.com>
* @access public
* @version 0.3.0
* @todo support array datatypes
*/
class MySQL extends Database {
var $host;
var $user;
var $pass;
var $opt1;
var $opt2;
var $dbname;
function MySQL() {
$this->db_open = false;
$this->models = array();
$this->recordsets = array();
$this->max_blob_length = 6144000; // default max blob file size is 6MB
$this->max_string_length = 1024000; // default max string length is 1MB
$this->datatype_map = array(
'float' => 'float', // precise to 23 digits
'double' => 'float', // 24-53 digits
'decimal' => 'float', // double stored as string
'int' => 'int',
'tinyint' => 'int',
'smallint' => 'int',
'mediumint' => 'int',
'bigint' => 'int',
'char' => 'char',
'varchar' => 'char',
'tinytext' => 'char',
'text' => 'text',
'mediumtext' => 'text',
'longtext' => 'text',
'bigtext' => 'text',
'time' => 'time',
'timestamp' => 'time',
'datetime' => 'time',
'date' => 'date',
'boolean' => 'bool',
'bool' => 'bool',
'blob' => 'blob',
'mediumblob' => 'blob',
'longblob' => 'blob'
);
$args = func_get_args();
$argnames = array('host','dbname','user','pass','opt1','opt2'); for ($i = 0; $i < count($args); $i++) {
$this->$argnames[$i] = $args[$i];
}
$this->true_values = array('t','true','1',true);
$this->alias_array = array();
$this->connect();
}
function connect() { /* function to re/establish the DB connection */
$this->conn = mysql_connect($this->host,$this->user,$this->pass,$this->opt1,$this->opt2);
if (!$this->conn) {
$this->db_open = false;
trigger_error("error in connect function of class MySQL in mysql.php "hide@address.com($this->conn), E_USER_ERROR );
} else {
$this->db_open = mysql_select_db($this->dbname);
if (!$this->db_open)
trigger_error(@mysql_error($this->conn), E_USER_ERROR );
}
return $this->db_open;
}
function escape_string( $string ) { /* watch for bad characters in each SQL query */
if (!(strlen($string) > 0)) { return $string; }
$result = @mysql_escape_string($string);
if (!$result && !(is_numeric($string))) {
trigger_error("error in escape_string in mysql.php "hide@address.com($this->conn), E_USER_ERROR );
} else {
return $result;
}
}
function get_result( $sql, $returnfalse = NULL ) { /* run an SQL query */
$request =& request_object();
if (isset($request->params)) {
trigger_before( 'get_result', $request, $this );
}
$result = @mysql_query( $sql, $this->conn );
if (!$result && $returnfalse == NULL) {
trigger_error("error in get_result in mysql.php "hide@address.com($this->conn)." ".$sql, E_USER_ERROR );
exit;
} elseif (!$result && $returnfalse) {
return false;
} else {
return $result;
}
}
function next_primary_key($table,$pkfield,$sequence_name=NULL) {
return "";
}
function last_insert_id( &$result, $pk, $table ) { /* returns the id of the most recently modified record */
$res = @mysql_insert_id($this->conn);
if (!$res) {
trigger_error("unable to determine last_insert_id in mysql.php "hide@address.com($this->conn), E_USER_ERROR );
} else {
return $res;
}
}
function result_value( &$result, $resultindex, $field ) { /* get a single value from a result set */
$res = mysql_result( $result, $resultindex, $field );
if (!$res && $res != 0) {
trigger_error("error in result_value in mysql.php"hide@address.com($this->conn), E_USER_ERROR );
} else {
return $res;
}
}
function close() {
$args = func_get_args();
mysql_close( $this->conn );
if ( isset( $args[0] ) ) {
if ( strlen($args[0]) > 0 ) {
header( "Location:" . $args[0] );
exit;
}
}
}
function &get_table($table) {
if ( isset( $this->models[$table] ) )
return $this->models[$table];
$data_model = model_path() . classify($table) . '.php';
if (file_exists($data_model))
require_once $data_model;
$this->models[$table] = new MySQLTable( $table, $this );
return $this->models[$table];
}
function &model($model) {
return $this->get_table(tableize($model));
}
function fetch_array(&$result,$row=NULL) {
if (is_numeric($row)) {
$this->seek_row( $result, $row );
}
return mysql_fetch_array( $result, MYSQL_ASSOC );
}
function fetch_row(&$result,$row=NULL) {
if (is_numeric($row)) {
$this->seek_row( $result, $row );
}
return mysql_fetch_row( $result );
}
function seek_row(&$result,$row) {
return mysql_data_seek( $result, $row );
}
function query_limit($limit,$offset) {
return " LIMIT " . $offset . "," . $limit;
}
function blob_value( &$rec, $field, &$value ) {
$ret = array();
$ret['t'] = $rec->table;
$ret['f'] = $field;
$ret['k'] = $rec->primary_key;
$ret['i'] = $rec->attributes[$rec->primary_key];
return $ret;
}
function sql_insert_for( &$rec ) {
$sql = "INSERT INTO " . $rec->table . " (";
$comma = '';
$fields = '';
$values = '';
foreach (array_unique($rec->modified_fields) AS $modified_field) {
$datatype = $this->get_mapped_datatype($this->models[$rec->table]->field_array[$modified_field]);
$this->pre_insert( $rec, $modified_field, $datatype );
if ( !( $datatype == 'blob' && ( !(strlen( $rec->attributes[$modified_field] ) > 0 ) ) ) ) {
$fields .= $comma . $modified_field;
$values .= $comma . $this->quoted_insert_value( $rec, $modified_field );;
$comma = ',';
}
}
$sql .= $fields . ") VALUES (" . $values . ")";
return $sql;
}
function sql_update_for( &$rec ) {
$sql = "UPDATE ";
$sql .= $rec->table . ' SET ';
$comma = '';
foreach (array_unique($rec->modified_fields) AS $modified_field) {
$datatype = $this->get_mapped_datatype($this->models[$rec->table]->field_array[$modified_field]);
$this->pre_update( $rec, $modified_field, $datatype );
if ( !( $datatype == 'blob' && ( !(strlen( $rec->attributes[$modified_field] ) > 0 ) ) ) ) {
$sql .= $comma . $this->quoted_update_value( $rec, $modified_field );
$comma = ',';
}
}
$sql .= " WHERE " . $rec->primary_key . "='" . $rec->attributes[$rec->primary_key] . "'";
return $sql;
}
function sql_select_for( &$rec, $id ) {
return "SELECT ".$rec->selecttext." FROM ".$rec->table." WHERE ".$rec->primary_key." = '".$id."'";
}
function sql_delete_for( &$rec ) {
$sql = 'DELETE FROM ' . $rec->table . ' WHERE ' . $rec->primary_key . ' = ' . $rec->$pkfield;
return $sql;
}
function select_distinct( $field, $table, $orderby ) {
return "SELECT DISTINCT $field, " . $this->models[$table]->primary_key . " FROM $table ORDER BY $orderby DESC";
}
function quoted_update_value( &$rec, $modified_field ) {
return $modified_field . "='" . $this->escape_string($rec->attributes[$modified_field]) . "'";
}
function quoted_insert_value( &$rec, $modified_field ) {
return "'" . $this->escape_string($rec->attributes[$modified_field]) . "'";
}
function pre_insert( &$rec, $modified_field, $datatype ) {
if (isset($this->models[$rec->table]->field_attrs[$modified_field]['required'])) {
if (!(strlen( $rec->attributes[$modified_field] ) > 0))
trigger_error( "$modified_field is a required field", E_USER_ERROR );
}
if (isset($this->models[$rec->table]->field_attrs[$modified_field]['unique'])) {
$result = $this->get_result("select ".$modified_field." from ".$rec->table." where ".$modified_field." = '".$rec->attributes[$modified_field]."'");
if ($result && $this->num_rows($result) > 0)
trigger_error( "$modified_field must be unique!", E_USER_ERROR );
}
if ($datatype == 'time' && !(strlen($rec->attributes[$modified_field]) > 0))
$rec->attributes[$modified_field] = date("Y-m-d H:i:s",strtotime("now"));
if ($datatype == 'blob')
$rec->attributes[$modified_field] =& $this->large_object_create( $rec->table, $rec->attributes[$modified_field] );
if ($datatype == 'bool') {
if ( in_array( $rec->attributes[$modified_field], $this->true_values, true ) ) {
$rec->attributes[$modified_field] = "1";
} else {
$rec->attributes[$modified_field] = "false";
}
}
}
function pre_update( &$rec, $modified_field, $datatype ) {
if (isset($this->models[$rec->table]->field_attrs[$modified_field]['required'])) {
if (!(strlen( $rec->attributes[$modified_field] ) > 0))
trigger_error( "$modified_field is a required field", E_USER_ERROR );
}
if (isset($this->models[$rec->table]->field_attrs[$modified_field]['unique'])) {
$result = $this->get_result("select ".$modified_field." from ".$rec->table." where ".$modified_field." = '".$rec->attributes[$modified_field]."' and ".$rec->primary_key." != '".$rec->attributes[$rec->primary_key]."'");
if ($this->num_rows($result) > 0)
trigger_error( "$modified_field must be unique!", E_USER_ERROR );
}
if ($datatype == 'bool') {
if ( in_array( $rec->attributes[$modified_field], $this->true_values, true ) ) {
$rec->attributes[$modified_field] = "1";
} else {
$rec->attributes[$modified_field] = "false";
}
}
if ( ($datatype == 'blob') && (strlen( $rec->attributes[$modified_field] ) > 0) ) {
if ( strlen( $rec->attributes[$modified_field] ) > 0 ) {
$data =& $this->large_object_create($rec->table,$rec->attributes[$modified_field]);
$rec->attributes[$modified_field] =& $data;
}
}
}
function post_insert( &$rec, &$result ) {
if (!$result) { trigger_error("Sorry, the record could not be saved due to a database error.", E_USER_ERROR ); }
$pkvalue = $this->last_insert_id($result,NULL,NULL);
$pkfield = $rec->primary_key;
$rec->attributes[$pkfield] = $pkvalue;
$rec->$pkfield =& $rec->attributes[$pkfield];
}
function num_rows(&$result) {
return @mysql_num_rows($result);
}
function num_fields(&$result) {
return @mysql_num_fields($result);
}
function field_name(&$result, $index) {
return @mysql_field_name($result, $index);
}
function large_object_create($table,$file) {
$return = false;
if (!(file_exists($file))) { trigger_error("temporary file could not be found", E_USER_ERROR ); }
$handle = fopen($file,"r");
if (!$handle) { trigger_error("Error creating large object in fopen", E_USER_ERROR ); }
$buffer = fread($handle,filesize($file));
if (!$buffer) { trigger_error("Error creating large object in fread", E_USER_ERROR ); }
$result = fclose($handle);
if (!$result) { trigger_error("Error creating large object in fclose", E_USER_ERROR ); }
else {
$return =& $buffer;
}
return $return;
}
function large_object_fetch($table,$blobcol,$pkfield,$pkvalue, $return=false) {
// t f k i
$sql = "SELECT $blobcol FROM $table WHERE $pkfield = '$pkvalue'";
$result = $this->get_result($sql);
if ($return)
$return = $this->result_value($result,0,$blobcol);
else
print $this->result_value($result,0,$blobcol);
return $return;
}
function large_object_delete($oid) {
return true;
}
function add_table( $table, $field_array ) {
if (!(count($field_array)>0)) trigger_error( "Error creating table, no fields are defined. Use \$model->auto_field and \$model->text_field etc.", E_USER_ERROR );
$sql = "CREATE TABLE $table (";
$comma = "";
foreach ( $field_array as $field => $data_type ) {
$sql .= "$comma $field $data_type";
$comma = ",";
}
$sql .= ")";
$result = $this->get_result($sql);
if ($result)
$this->tables[] = $table;
}
function add_field( $table, $field, $data_type ) {
$sql = "ALTER TABLE $table ADD COLUMN $field $data_type";
$result = $this->get_result($sql);
}
function has_table($t) {
return in_array( $t, $this->get_tables(), true );
}
function get_tables() {
$tables = array();
$sql = "SHOW tables FROM ".$this->dbname;
$result = $this->get_result($sql);
while ($arr = $this->fetch_array($result)) {
foreach($arr as $key=>$value) {
if (!(in_array($value, array('db_sessions','categories_entries','entries_reviews'))))
$tables[] = $value;
}
}
return $tables;
}
function get_fields($table) {
$datatypes = array();
$sql = "SHOW columns FROM $table";
$result = $this->get_result($sql, true);
if (!$result) return $datatypes;
while ($arr = $this->fetch_array($result)) {
foreach($arr as $key=>$value) {
if ($key == "Field") {
$field = $value;
} elseif ($key == "Type") {
$type = $value;
} elseif ($key == "Key") {
if ($value == "PRI") {
$datatypes[$table."_primary_key"] = $field; // yuck
}
}
}
$datatypes[$field] = $type;
}
return $datatypes;
}
}
/**
* MySQL Table
*
* data model for a single MySQL table
*
* Usage:
* <code>
* $people =& $db->model( 'people' );
* </code>
*
* More info...
* {@link http://dbscript.net/mysqltable}
*
* @package dbscript
* @author Brian Hendrickson <hide@address.com>
* @access public
* @param string $table
* @param object $db
* @version 0.3.0
*/
class MySQLTable extends Model {
function MySQLTable( $table, &$db ) {
$this->table = $table;
$db->models[$table] =& $this;
$this->params = array('resource'=>$table);
$this->access_list = array();
$this->relations = array();
$this->allowed_methods = array( 'get', 'post', 'put', 'delete' );
$this->field_array = $db->get_fields( $this->table );
$this->hidden = false;
if ( count( $this->field_array ) > 0 )
$this->exists = true;
else
$this->exists = false;
if (isset($this->field_array[$this->table."_primary_key"])) {
$this->set_primary_key( $this->field_array[$this->table."_primary_key"] );
$this->field_array = drop_array_element($this->field_array,$this->table."_primary_key");
}
if ( class_exists( classify( $table )))
$this->register( classify( $table ));
}
function auto_field( $field ) {
$this->set_field( $field, "int(11) not null auto_increment primary key" );
$this->set_primary_key( $field );
}
function enum_field( $field, $values ) {
$this->set_field( $field, "enum", $values );
}
function float_field( $field ) {
$this->set_field( $field, "double" );
}
function bool_field( $field ) {
$this->set_field( $field, "bool" );
}
function char_field( $field ) {
$args = func_get_args();
if (count($args)>1) {
$len = $args[1];
} else {
$len = "255";
}
$this->set_field( $field, "varchar($len)" );
}
function date_field( $field ) {
$this->set_field( $field, "date" );
}
function file_field( $field ) {
$this->set_field( $field, "longblob" );
}
function int_field( $field ) {
$this->set_field( $field, "int(11)" );
}
function text_field( $field ) {
$this->set_field( $field, "text" );
}
function time_field( $field ) {
$this->set_field( $field, "datetime" );
}
function get_query( $id=NULL, $find_by=NULL ) {
$db =& db_object();
trigger_before( 'get_query', $this, $db );
$pkfield = $this->primary_key;
if ($find_by == NULL)
$find_by = $this->primary_key;
$relfields = array();
$relfields = $this->relations;
$table = $this->table;
$fieldstring = '';
$sql = "SELECT " . "\n";
if (!array_key_exists($pkfield,$this->field_array))
$sql .= "$table.$pkfield as \"$table.$pkfield\", " . "\n";
foreach ($this->field_array as $fieldname=>$datatypename) {
if (!(!(strpos($fieldname,".") === false)))
$fieldname = $table . "." . $fieldname;
$fieldstring .= "$fieldname as \"$fieldname\", " . "\n";
}
$leftsql = "";
$first = true;
if (count($relfields) > 0) {
foreach ($relfields as $key=>$val) {
$spl = split("\.",$val["fkey"]);
if (!(isset($db->models[$spl[0]])))
$$spl[0] =& $db->get_table($spl[0]);
if (($val["type"] != 'child-many'))
$leftsql .= "(";
}
$skippedrel = false;
foreach ($relfields as $key=>$val) {
$spl = split("\.",$val["fkey"]);
if (($val["type"] == 'child-many')) {
if ($first)
$skippedrel = true;
continue;
}
foreach ($db->models[$spl[0]]->field_array as $fieldname=>$datatypename) {
$fieldstring .= $spl[0].".".$fieldname." as \"".$spl[0].".".$fieldname."\", " . "\n";
}
if ($first)
$leftsql .= $table;
$leftsql .= " left join " . $spl[0] . " on ".$table.".".$val["col"]." = " . $val["fkey"];
$leftsql .= ")";
$first = false;
}
}
$fieldstring = substr($fieldstring,0,-3) . " " . "\n";
$sql .= $fieldstring;
$sql .= "FROM ";
$sql .= $leftsql;
if (!(strlen($leftsql) > 1))
$sql .= $table;
if (is_array($find_by)) {
$findfirst = true;
foreach( $find_by as $col=>$val ) {
$op = "AND";
if ($col == 'op') {
$op = $val;
} else {
if ($findfirst) {
$sql .= " WHERE $table.$col = '$val' ";
} else {
$sql .= " $op $table.$col = '$val' ";
}
$findfirst = false;
}
}
} elseif ($id != NULL) {
$sql .= " WHERE $table.$find_by = '$id' ";
}
if (!(isset($this->orderby))) {
$this->orderby = $table . "." . $pkfield;
}
if (!(isset($this->order))) {
$this->order = "DESC";
}
if (!(isset($this->offset))) {
$this->offset = 0;
}
if (!(isset($this->limit))) {
$this->limit = 20;
}
$sql .= " ORDER BY " . $this->orderby . " ";
$sql .= $this->order . $db->query_limit($this->limit,$this->offset);
if (class_exists(classify($table))) {
$this->custom_class = classify($table);
}
trigger_after( 'get_query', $this, $db );
return $sql;
}
}
?>