<?php
/**********************************************************************
Php Textfile DB Access API
Copyright 2002 by c-worker.ch
http://www.c-worker.ch
***********************************************************************/
/**********************************************************************
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***********************************************************************/
include_once(API_HOME_DIR . "const.php");
include_once(API_HOME_DIR . "util.php");
include_once(API_HOME_DIR . "resultset.php");
include_once(API_HOME_DIR . "sql.php");
include_once(API_HOME_DIR . "expression.php");
/**********************************************************************
Database
***********************************************************************/
// Represents a Database and has functions to execute Sql-Queries on it
class Database {
var $dbFolder;
/***********************************
Constructor
************************************/
function Database($dbFolder="defaultDb/") {
$this->dbFolder= DB_DIR . $dbFolder;
if(last_char($this->dbFolder) != "/")
$this->dbFolder .= "/";
}
/***********************************
Table Lock functions
************************************/
// Lock's a Table
// returns true if the table could be locked successfull
function lockTable($tableName, $lockTimeout=LOCK_TIMEOUT) {
$filename=$this->dbFolder . $tableName . LOCK_FILE_EXT;
while($this->isLocked($tableName)) {
sleep(1);
$lockTimeout--;
if($lockTimeout==0) {
print_error("Table $tableName is still Locked");
return false;
}
}
$fp=fopen ($filename, "w");
fputs($fp,"lock file");
fclose($fp);
return true;
}
// Unlock's a Table
// returns true or false
function unlockTable($tableName) {
clearstatcache();
$filename=$this->dbFolder . $tableName . LOCK_FILE_EXT;
if(file_exists($filename)) {
$rc=unlink($filename);
if(!$rc)
print_error("unlink failed on File $filename");
return $rc;
} else {
print_error("Cannot unlock Table, the File $filename does not exist");
return false;
}
}
// Returns true if the Table $tableName is locked
function isLocked($tableName) {
clearstatcache();
$filename=$this->dbFolder . $tableName . LOCK_FILE_EXT;
if(file_exists($filename)) {
if(!($ts=filemtime($filename))) {
print_error("filemtime failed on $filename");
return true;
}
if( (time()-$ts)>LOCKFILE_TIMEOUT) {
debug_print("Deleting Lock File (Timeout)<br>");
if(!unlink($filename)) {
print_error("unlink $filename failed");
return true;
}
return false;
}
return true;
}
return false;
}
/***********************************
Table open/close Functions
************************************/
// Does open a locked Table for writing
// Do only open a table, with this function, you have locked before !!!
// openLockedTableWrite() does not check if the table is locked
// you must call lockTable() first, and only if this succeeds
// you should call openLockedTableWrite()
function openLockedTableWrite($tableName, $openTimeout=OPEN_TIMEOUT) {
debug_print("openLockedTableWrite<br>");
$filename=$this->dbFolder . $tableName . TABLE_FILE_EXT;
return fopen($filename,"w");
}
// Does open a locked Table for reading
// Do only open a table, with this function, you have locked before !!!
// openLockedTableRead() does not check if the table is locked
// you must call lockTable() first, and only if this succeeds
// you should call openLockedTableRead()
function openLockedTableRead($tableName, $openTimeout=OPEN_TIMEOUT) {
debug_print("openLockedTableRead<br>");
$filename=$this->dbFolder . $tableName . TABLE_FILE_EXT;
return fopen($filename,"r");
}
// Opens a Table for reading, this function does not open locked tables
// This function is used by SELECT
// returns a FilePointer or false
function openTableRead($tableName, $openTimeout=OPEN_TIMEOUT) {
debug_print("openTableRead<br>");
$filename=$this->dbFolder . $tableName . TABLE_FILE_EXT;
while($this->isLocked($tableName)) {
sleep(1);
debug_print("next try<br>");
$openTimeout--;
if($openTimeout==0) {
print_error("Table $tableName is still Locked");
return false;
}
}
debug_print("Table $tableName opened (READ)<br>");
return fopen($filename,"r");
}
// Closes a Table
// (do not forget to unlock with unlockTable() after the Table is closed)
function closeTable($fp) {
debug_print("Table $fp closed<br>");
return fclose($fp);
}
/***********************************
Table read/write Functions
************************************/
// Reads a Table into a ResultSet
// Returns a ResultSet or null (the function opens an closes the file itself)
// This functions can only read tables which are not locked (used in SELECT)
function readTable($tableName) {
debug_print("readTable<br>");
$parser= new ResultSetParser();
if(!($fd=$this->openTableRead($tableName))) {
print_error("readTable(): Cannot open Table $tableName");
return null;
}
$rs=$parser->parseResultSetFromFile($fd);
$this->closeTable($fd);
return $rs;
}
// Reads a locked Table into a ResultSet
// Returns a ResultSet or null (the function opens an closes the file itself)
// Do only call this funtion if you have locked the table before
function readLockedTable($tableName) {
debug_print("readLockedTable<br>");
$parser= new ResultSetParser();
if(!($fd=$this->openLockedTableRead($tableName))) {
print_error("readLockedTable(): Cannot open Table $tableName");
return null;
}
$rs=$parser->parseResultSetFromFile($fd);
$this->closeTable($fd);
return $rs;
}
// writes the table by using the FilePointer $fd
// $fd has to be opened an closed by the caller
// lock the table first !
function writeLockedTable($fd, $resultSet) {
debug_print("writeLockedTable<br>");
$parser= new ResultSetParser();
return $parser->parseResultSetIntoFile($fd, $resultSet);
}
// sql_query the data into assositive array of arrays.
//-------------------------------------------------
function sql_query($sql){
return $this->executeQuery($sql);
}
// insert the data into assositive array of arrays.
//-------------------------------------------------
function insert($sql){
return $this->executeQuery($sql);
}
// select the data into assositive array of arrays.
//-------------------------------------------------
function select($sql){
$sql_split = split(" LIMIT",$sql);
$startrow = 0;
$currentrow = 0;
$endrow = 99999999;
if($sql_split[1] != ""){
$mylimit = ereg_replace(" ","",$sql_split[1]);
$mylimit = ereg_replace("LIMIT","",$mylimit);
$sql_split2 = split(",",$mylimit);
$startrow = $sql_split2[0];
$endrow = $sql_split2[1] + $startrow;
}
$rs = $this->executeQuery($sql_split[0]);
$fieldnames = $rs->getColumnNames();
$data_array = array();
$dataarr = array();
$dataarr_index = 0;
while($rs->next()) {
$tmp_array = $rs->getCurrentValues();
for($i=0;$i<count($tmp_array); $i++){
$myfield = $fieldnames[$i];
if ($myfield != ""){
// print "myfield = $myfield value = $tmp_array[$i] ";
$data_array[$myfield] = $tmp_array[$i];
}
}
if( ($currentrow >= $startrow) && ($currentrow <= $endrow) ){
$dataarr[$dataarr_index] = $data_array;
$dataarr_index++;
}
$currentrow++;
}
return $dataarr;
}
/***********************************
Query dispatcher
************************************/
// $sql_query_str is an unparsed SQL Query String
// Return Values:
// SELECT Queries: Returns a ResultSet Object or null
// CREATE TABLE: Returns true or false
// All other types: Returns the number of rows affected
function executeQuery($sql_query_str) {
// if this is an alter
if(ereg("^ALTER TABLE",$sql_query_str)){
return $this->executeAlterQuery($sql_query_str);
} else {
// Parse Query
$sqlParser= &new SqlParser($sql_query_str);
$sqlQuery=$sqlParser->parseSqlQuery();
// Test Query
if((!$sqlQuery) || (!$sqlQuery->test()))
return null;
// Dispatch
switch($sqlQuery->type) {
case "SELECT":
$rc=$this->executeSelectQuery($sqlQuery);
break;
case "INSERT":
$rc= $this->executeInsertQuery($sqlQuery);
break;
case "DELETE":
$rc= $this->executeDeleteQuery($sqlQuery);
break;
case "UPDATE":
$rc= $this->executeUpdateQuery($sqlQuery);
break;
case "CREATE TABLE":
$rc= $this->executeCreateTableQuery($sqlQuery);
break;
default:
print_error("Invalid or unsupported Query Type: " . $sqlQuery->type);
return false;
}
}
return $rc;
}
/***********************************
Delete Query
************************************/
function executeDeleteQuery(&$sqlQuery) {
// Lock Table
if(!$this->lockTable($sqlQuery->tables[0])) {
print_error("Locking the Table " . $sqlQuery->tables[0] . " failed");
return null;
}
// Read Table
$rs=$this->readLockedTable($sqlQuery->tables[0]);
if(!$rs) {
print_error("Reading the Table " . $sqlQuery->tables[0] . " failed");
$this->unlockTable($sqlQuery->tables[0]);
return null;
}
$rowsAffected=0;
if(!$sqlQuery->where_expr || $sqlQuery->where_expr=="") {
$rowsAffected=$rs->getRowCount();
$rs->deleteAllRows();
} else {
// set row ids
$rId=-1;
$rs->reset();
while($rs->next())
$rs->setCurrentRowId(++$rId);
$rs->reset();
// apply WHERE Statement
$ep=&new ExpressionParser();
$rsFiltered=$ep->getFilteredResultSet($rs, $sqlQuery);
if(!$rsFiltered) {
$this->unlockTable($sqlQuery->tables[0]);
return 0;
}
// Delete rows..
$rsFiltered->reset();
while($rsFiltered->next()) {
$rowId=$rsFiltered->getCurrentRowId();
$rs->deleteRow($rs->searchRowById($rowId));
}
$rowsAffected=$rsFiltered->getRowCount();
}
// Open Table
$fp=$this->openLockedTableWrite($sqlQuery->tables[0]);
if(!$fp) {
print_error("Open the Table " . $sqlQuery->tables[0] . " (for WRITE) failed");
$this->unlockTable($sqlQuery->tables[0]);
return null;
}
// Write Table
$this->writeLockedTable($fp,$rs);
$this->closeTable($fp);
$this->unlockTable($sqlQuery->tables[0]);
return $rowsAffected;
}
/***********************************
Insert Query
************************************/
// returns the affected Row count or false
function executeInsertQuery(&$sqlQuery) {
// Lock Table
if(!$this->lockTable($sqlQuery->tables[0])) {
print_error("Locking the Table " . $sqlQuery->tables[0] . " failed");
return null;
}
// Read Table
$rs=$this->readLockedTable($sqlQuery->tables[0]);
if(!$rs) {
print_error("Reading the Table " . $sqlQuery->tables[0] . " failed");
$this->unlockTable($sqlQuery->tables[0]);
return null;
}
// Open Table
$fp=$this->openLockedTableWrite($sqlQuery->tables[0]);
if(!$fp) {
print_error("Open the Table " . $sqlQuery->tables[0] . " (for WRITE) failed");
$this->unlockTable($sqlQuery->tables[0]);
return null;
}
array_walk($sqlQuery->fieldValues,"array_walk_remove_quotes");
switch(count($sqlQuery->fields)) {
case 0:
$rs->appendRow($sqlQuery->fieldValues);
$this->writeLockedTable($fp,$rs);
$this->closeTable($fp);
$this->unlockTable($sqlQuery->tables[0]);
return 1; // Error Handling ??
break;
default:
$rs->append();
for($i=0;$i<count($sqlQuery->fields);$i++) {
$rs->setCurrentValueByName($sqlQuery->fields[$i],$sqlQuery->fieldValues[$i]);
}
$this->writeLockedTable($fp,$rs);
$this->closeTable($fp);
$this->unlockTable($sqlQuery->tables[0]);
return 1; // Error Handling ??
break;
}
}
/***********************************
Alter Query
************************************/
// this function needs a lot of work this is just a work around
// for now .
function executeAlterQuery(&$sqlQuery) {
global $DB_DIR;
//print "running: $sqlQuery<br><br>";
//ALTER TABLE gallery_feature ADD/DROP what
$sqlQuery = ereg_replace(" "," ",$sqlQuery);
$detail_query = split(" ",$sqlQuery);
$filename = $DB_DIR . "csgallery/" . $detail_query[2] . ".txt";
$filename_w = $DB_DIR . "csgallery/" . $detail_query[2] . ".new";
$fp = fopen ("$filename", "r");
$fp_w = fopen ("$filename_w", "w+");
$linenumber = 0;
if($detail_query[3] == "ADD"){
while (!feof($fp)){
$currentline = fgets($fp,99999);
$currentline = ereg_replace("\r\n","",$currentline);
$currentline = ereg_replace("\n","",$currentline);
if($linenumber == 0){ $currentline .= $detail_query[4] . "#"; }
if($linenumber == 1){ $currentline .= "str#"; }
if($linenumber > 1){ $currentline .= "#"; }
$currentline .= "\n";
$linenumber++;
fwrite($fp_w, $currentline, strlen($currentline));
}
}
if($detail_query[3] == "DROP"){
while (!feof($fp)){
$currentline = fgets($fp,99999);
$currentline_n = "";
if($linenumber == 0){
$fields = split("#",$currentline);
for($i=0;$i<count($fields); $i++){
if($fields[$i] == $detail_query[4]){
$index_rm = $i;
} else {
$currentline_n .= $fields[$i] . "#";
}
} // for loop
} // if line == 0
if($linenumber > 0){
$fields = split("#",$currentline);
for($i=0;$i<count($fields); $i++){
if( $i == $index_rm){
// skipping..
} else {
$currentline_n .= $fields[$i] . "#";
}
} // for loop
} // if line > 0
$linenumber++;
fwrite($fp_w, $currentline_n, strlen($currentline_n));
}
}
fclose($fp);
fclose($fp_w);
unlink($filename);
copy($filename_w,$filename);
unlink($filename_w);
}
/***********************************
Update Query
************************************/
// returns the affected Row count or false
function executeUpdateQuery(&$sqlQuery) {
// Lock Table
if(!$this->lockTable($sqlQuery->tables[0])) {
print_error("Locking the Table " . $sqlQuery->tables[0] . " failed");
return null;
}
// Read Table
$rs=$this->readLockedTable($sqlQuery->tables[0]);
if(!$rs) {
print_error("Reading the Table " . $sqlQuery->tables[0] . " failed");
$this->unlockTable($sqlQuery->tables[0]);
return null;
}
array_walk($sqlQuery->fieldValues,"array_walk_remove_quotes");
// No where_expr ? update all
if( (!isset($sqlQuery->where_expr)) || (!$sqlQuery->where_expr) ) {
// update
$rs->reset();
while($rs->next()) {
for($i=0;$i<count($sqlQuery->fields);$i++) {
$rs->setCurrentValueByName($sqlQuery->fields[$i],$sqlQuery->fieldValues[$i]);
}
}
// Open Table
$fp=$this->openLockedTableWrite($sqlQuery->tables[0]);
if(!$fp) {
print_error("Open the Table " . $sqlQuery->tables[0] . " (for WRITE) failed");
$this->unlockTable($sqlQuery->tables[0]);
return null;
}
// Write Table
$this->writeLockedTable($fp,$rs);
$this->closeTable($fp);
$this->unlockTable($sqlQuery->tables[0]);
return true;
} else {
// set row id's
$rs->reset();
$rId=-1;
while($rs->next())
$rs->setCurrentRowId(++$rId);
// create a copy
$rsFiltered=$rs;
// filter by where expression
$ep=&new ExpressionParser();
$rsFiltered=$ep->getFilteredResultSet($rsFiltered, $sqlQuery);
if($rsFiltered->getRowCount()<1) {
$this->unlockTable($sqlQuery->tables[0]);
return 0;
}
// update
$rsFiltered->reset();
while($rsFiltered->next()) {
for($i=0;$i<count($sqlQuery->fields);$i++) {
$rsFiltered->setCurrentValueByName($sqlQuery->fields[$i],$sqlQuery->fieldValues[$i]);
}
}
// put filtered part back in the original ResultSet
$rowNr=0;
$putBack=0;
$rs->reset();
$rsFiltered->reset();
while($rs->next()) {
if(($rowNr=$rsFiltered->searchRowById($rs->getCurrentRowId())) !=NOT_FOUND) {
$rs->setCurrentValues($rsFiltered->getValues($rowNr));
$putBack++;
}
}
if($putBack<$rsFiltered->getRowCount()) {
print_error("UPDATE: Could not put Back all filtered Values");
$this->unlockTable($sqlQuery->tables[0]);
return 0;
}
// Open Table
$fp=$this->openLockedTableWrite($sqlQuery->tables[0]);
if(!$fp) {
print_error("Open the Table " . $sqlQuery->tables[0] . " (for WRITE) failed");
$this->unlockTable($sqlQuery->tables[0]);
return null;
}
$this->writeLockedTable($fp,$rs);
$this->closeTable($fp);
$this->unlockTable($sqlQuery->tables[0]);
return $rsFiltered->getRowCount();
}
}
/***********************************
Create Table Query
************************************/
// executes a SQL CREATE TABLE STATEMENT and returns a ResultSet
// param: SqlQuery Object
// returns True or False
function executeCreateTableQuery(&$sqlQuery) {
clearstatcache();
$filename=$this->dbFolder . $sqlQuery->tables[0] . TABLE_FILE_EXT;
// checks
if(!$sqlQuery->tables[0]) {
print_error("Invalid Table " . $sqlQuery->tables[0]);
return false;
}
if(file_exists($filename)) {
print_error("Table " . $sqlQuery->tables[0] . " allready exists");
return false;
}
if(count($sqlQuery->fields)!=count($sqlQuery->fieldTypes)) {
print_error("There's not for each Field a Type defined");
return false;
}
for($i=0;$i<count($sqlQuery->fieldTypes);$i++) {
$tmp= ($sqlQuery->fieldTypes[$i]=strtolower($sqlQuery->fieldTypes[$i]));
if( !($tmp == COL_TYPE_INT || $tmp == COL_TYPE_STRING || $tmp==COL_TYPE_INC)) {
print_error("Column Type " . $tmp . " not supported");
return false;
}
}
// write file
$fp=fopen ($filename, "w");
$rsParser=&new ResultSetParser();
fwrite($fp, $rsParser->parseLineFromRow($sqlQuery->fields));
fwrite($fp, "\n");
fwrite($fp, $rsParser->parseLineFromRow($sqlQuery->fieldTypes));
fclose($fp);
chmod($filename,0777);
return true;
}
/***********************************
Select Query
************************************/
// executes a SQL SELECT STATEMENT and returns a ResultSet
// param: SqlQuery Object
function executeSelectQuery(&$sqlQuery) {
$resultSets=array();
// create a copy
$aliases=$sqlQuery->fieldAlias;
// Read all Tables
for($i=0;$i<count($sqlQuery->tables);$i++) {
debug_print ("<br>reading table " . $sqlQuery->tables[$i] ."<br>");
if(!($resultSets[$i]=$this->readTable($sqlQuery->tables[$i]))) {
print_error("Reading Table " . $sqlQuery->tables[$i]. " failed");
return null;
}
$resultSets[$i]->setColumnTableForAll($sqlQuery->tables[$i]);
// set all aliases where table and field name matches
for($j=0;$j<count($aliases);$j++) {
if(!$aliases[$j])
continue;
if($sqlQuery->fieldTables[$j]==$sqlQuery->tables[$i]) {
$colNr=$resultSets[$i]->findColNr($sqlQuery->fields[$j]);
if($colNr!=-1) {
$resultSets[$i]->setColumnAliasName($colNr,$aliases[$j]);
$aliases[$j]="";
}
}
}
if(DEBUG) $resultSets[$i]->dump();
}
// set remaining aliases where field name matches
for($i=0;$i<count($resultSets);$i++) {
for($j=0;$j<count($aliases);$j++) {
if(!$aliases[$j])
continue;
if( ($colNr=$resultSets[$i]->findColNr($sqlQuery->fields[$j])) !=-1) {
$resultSets[$i]->setColumnAliasName($colNr,$aliases[$j]);
$aliases[$j]="";
}
}
}
// check if all aliases are used
for($i=0;$i<count($aliases);$i++) {
if($aliases[$i])
print_error("Could not attach alias " . $aliases[$i] . " contact developer");
}
// join the ResultSet's
$rsMaster=$resultSets[0];
for($i=1;$i<count($resultSets);$i++) {
$rsMaster=$rsMaster->joinWithResultSet($resultSets[$i]);
}
// set row id's
$rsMaster->reset();
$rId=-1;
while($rsMaster->next())
$rsMaster->setCurrentRowId(++$rId);
debug_print ("<br>Master ResultSet:<br>");
if(DEBUG) $rsMaster->dump();
// apply WHERE Statement
if($sqlQuery->where_expr) {
$ep=&new ExpressionParser();
$rsMaster=$ep->getFilteredResultSet($rsMaster, $sqlQuery);
}
// return only the requested columns
$rsMaster->filterByColumnNamesInSqlQuery($sqlQuery);
// order columns (no their data)
if(!$rsMaster->orderColumnsBySqlQuery($sqlQuery)) {
print_error("Ordering the Columns (themself) failed");
return null;
}
// Order ResultSet
if(count($sqlQuery->orderFields)>0) {
$rsMaster->orderRows($sqlQuery->orderFields,$sqlQuery->orderTypes);
}
$rsMaster->reset();
return $rsMaster;
}
}
?>