From: paulr <me@pjr.cc>
Date: Mon, 18 Apr 2011 17:23:35 +0000 (+1000)
Subject: rudementry functioning db functions
X-Git-Url: http://git.pjr.cc/?p=glcas.git;a=commitdiff_plain;h=bdc6417e64fc4a48c1364dc4773d68205f88b015

rudementry functioning db functions
---

diff --git a/lib/config.php b/lib/config.php
index ad4a503..272e641 100644
--- a/lib/config.php
+++ b/lib/config.php
@@ -1,7 +1,8 @@
 <?php
 
-global $configured, $BASE_DIR;
+global $configured, $BASE_DIR, $DB_HANDLE;
 $configured = false;
+$DB_HANDLE = false;
 
 $BASE_DIR = realpath(dirname(__FILE__)."/../");
 
diff --git a/lib/plugins/db.php b/lib/plugins/db.php
index cf8974f..a337718 100644
--- a/lib/plugins/db.php
+++ b/lib/plugins/db.php
@@ -3,20 +3,105 @@
 function db_getDB()
 {
 	global $DB_URL;
+	
+	$dbobject = false;
+    global $BASE_DIR, $DB_HANDLE;
+    if($DB_HANDLE != false) return $DB_HANDLE;
+	try {
+		$dbobject = new PDO("$DB_URL");
+	} catch(PDOException $exep) {
+		error_log("execpt on db open");
+		return false;
+	}
+	
+	$DB_HANDLE = $dbobject;
+
+	return $dbobject;
 }
 
 function db_createDB()
 {
+	// theres not much to do yet with only sqlite support
+}
+
+// all columns end up as text
+function db_createTable($tablename, $ncolumns)
+{
+	$db = db_getDB();
+	
+	$sql = "create table \"$tablename\" (\"".$tablename."_id\" INTEGER PRIMARY KEY AUTOINCREMENT";
+	for($i=1; $i <= $ncolumns; $i++) {
+		$colname = "column_$i";
+		$sql .= ", $colname text";
+	}
+	$sql .= ")";
+	
+	$db->query($sql);
+}
+
+function db_insertData($tablename)
+{
+
+	$db = db_getDB();
 	
+	$sql = "insert into \"$tablename\" values (NULL";
+	for($i=1; $i < func_num_args(); $i++) {
+		$sql .= ",'".func_get_arg($i)."'";
+	}
+	$sql .=")";
+	
+	$db->query($sql);
 }
 
-function db_createTable($tablename)
+function db_selectData($tablename, $column, $value)
 {
+	$db = db_getDB();
+	
+	$sql = "select * from \"$tablename\" where column_$column like '%$value%'";
+	$res = $db->query($sql);
+	$data = $res->fetchAll();
 	
+	return $data;
+}
+
+function db_deleteData($tablename, $column, $value)
+{
+	$db = db_getDB();
+	
+	$sql = "delete from \"$tablename\" where column_$column like '%$value%'";
+	return $db->query($sql);
+	
+}
+
+function db_deleteTable($tablename)
+{
+	$db = db_getDB();
+	
+	$sql = "drop table $tablename";
+	
+	return $db->query($sql);
+}
+
+function db_tableExists($tablename)
+{
+	$db = db_getDB();
+	
+	$sql = "select count(*) from sqlite_master where type='table' and name='$tablename'";
+	$res = $db->query($sql);
+	
+	$value = $res->fetchColumn();
+	
+	if($value == 1) return true;
+	else return false;
 }
 
 function db_getTables()
 {
+	$db = db_getDB();
+	
+	$sql = "select name from sqlite_master where type='table' and name not like 'sqlite_%'";
+	$res = $db->query($sql);
 	
+	return $res->fetchAll();
 }
 ?>
\ No newline at end of file
diff --git a/unittests/dbtest.php b/unittests/dbtest.php
new file mode 100644
index 0000000..20491c9
--- /dev/null
+++ b/unittests/dbtest.php
@@ -0,0 +1,76 @@
+<?php
+
+require_once("../lib/lib.php");
+
+db_createTable("table1", 4);
+db_createTable("table2", 4);
+db_createTable("table3", 4);
+
+if(db_tableExists("table1")) echo "table 1 exists - correct\n";
+else echo "table 1 not exists - wrong\n";
+
+if(db_tableExists("table2")) echo "table 2 exists - correct\n";
+else echo "table 2 not exists - wrong\n";
+
+if(db_tableExists("table3")) echo "table 3 exists - correct\n";
+else echo "table 3 not exists - wrong\n";
+
+if(db_tableExists("table4")) echo "table 4 exists - wrong\n";
+else echo "table 4 not exists - correct\n";
+
+// now insert data test
+db_insertData("table1", "data1", "data2", "data3", "data4");
+
+// now try and do a gettables
+echo "tabs looks like:\n";
+$tabs = db_getTables();
+print_r($tabs);
+
+// now try a select
+$ret = db_selectData("table1", "1", "data1");
+echo "did select 1 on table1 and got:\n";
+print_r($ret);
+if(isset($ret[0])) {
+	echo "looks correct\n";
+} else {
+	echo "looks wrong\n";
+}
+
+// now try a select
+$ret2 = db_selectData("table1", "1", "notdata1");
+echo "did select 2 on table1 and got:\n";
+print_r($ret2);
+if(isset($ret2[0])) {
+	echo "looks wrong\n";
+} else {
+	echo "looks correct\n";
+}
+
+// now do delete
+$ret = db_deleteData("table1", "1", "data1");
+
+// now try a select again
+$ret = db_selectData("table1", "1", "data1");
+echo "did select 1 on table1 and got:\n";
+print_r($ret);
+if(isset($ret[0])) {
+	echo "looks wrong\n";
+} else {
+	echo "looks correct\n";
+}
+
+// now delete the tables
+$ret = db_deleteTable("table1");
+$ret = db_deleteTable("table2");
+$ret = db_deleteTable("table3");
+
+if(db_tableExists("table1")) echo "table 1 exists - wrong\n";
+else echo "table 1 not exists - correct\n";
+
+if(db_tableExists("table2")) echo "table 2 exists - wrong\n";
+else echo "table 2 not exists - correct\n";
+
+if(db_tableExists("table3")) echo "table 3 exists - wrong\n";
+else echo "table 3 not exists - correct\n";
+
+?>
\ No newline at end of file
diff --git a/var/config.php b/var/config.php
index 52bceef..8e63d25 100644
--- a/var/config.php
+++ b/var/config.php
@@ -2,7 +2,7 @@
 
 // statically set for now
 $GLOBAL_BASE_URL="/src/eclipse-workspace/glcas/www/";
-$DB_URL="sqlite:"
+$DB_URL="sqlite:$BASE_DIR/var/ds_store.db";
 
 global $GLOBAL_BASE_URL;