bazdig

From 005acd88dcf1bbbe30e92dc0a849cc92cc32ed0a Mon Sep 17 00:00:00 2001 From: Slim Amamou Date: Wed, 30 Jan 2008 20:31:09 +0100 Subject: [PATCH] classe BDB et sa methode listTables() j'ai corrige le bug de l'ecape des quotes --- bazdig/bazdig.css | 5 ++ bazdig/console/index.php | 13 ++-- bazdig/db/set/index.php | 1 + bazdig/history/index.php | 15 ++++- bazdig/sql/exec/index.php | 4 +- lib/code.php | 4 +- lib/database.php | 149 ++++++++++++++++++++-------------------------- test/SqlCodeTest.php | 31 +++++++--- test/TableTest.php | 37 ++++++++++++ test/bazdig-test.db | Bin 4096 -> 5120 bytes 10 files changed, 156 insertions(+), 103 deletions(-) create mode 100644 bazdig/bazdig.css rewrite lib/database.php (88%) create mode 100644 test/TableTest.php diff --git a/bazdig/bazdig.css b/bazdig/bazdig.css new file mode 100644 index 0000000..e0068cc --- /dev/null +++ b/bazdig/bazdig.css @@ -0,0 +1,5 @@ +.button { border: 1px outset silver ; padding: 5px 10px; color: black; background-color: lightgrey; text-decoration:none} +.query { display: block; padding: 10px; text-decoration: none; color: black } +a:hover.query { background-color: yellow } +#ok { width: 100%; } +#browser { width: 30%; padding: 10px; vertical-align: top} diff --git a/bazdig/console/index.php b/bazdig/console/index.php index a4f046c..e0f8bb5 100644 --- a/bazdig/console/index.php +++ b/bazdig/console/index.php @@ -1,16 +1,19 @@ + bazdig + +

history database

history | database | save

diff --git a/bazdig/db/set/index.php b/bazdig/db/set/index.php index e2c183e..3ea73a4 100644 --- a/bazdig/db/set/index.php +++ b/bazdig/db/set/index.php @@ -1,4 +1,5 @@ get("/console"); SqlCode::set_db("sqlite:". $dbFile->get_file()); - $queries = SqlCode::select('order by date desc limit 10'); + if ($_GET['q']) { + $queries = SqlCode::search($_GET['q']); + } else { + $queries = SqlCode::select('order by date desc limit 10'); + } +?> + +

console save

+ +get_url() .'?q='. $q->code .'" >'. $q->code .''; + echo ''. $q->code .''; } diff --git a/bazdig/sql/exec/index.php b/bazdig/sql/exec/index.php index e300045..6fee79f 100644 --- a/bazdig/sql/exec/index.php +++ b/bazdig/sql/exec/index.php @@ -22,7 +22,8 @@ } $query->save(); - $columns = columnNames($result); + $rows = $result->fetchAll(PDO::FETCH_ASSOC); + $columns = columnNames($rows[0]); ?> @@ -40,7 +41,6 @@ echo "$c"; } echo ""; - $rows = $result->fetchAll(PDO::FETCH_NUM); foreach ($rows as $r) { echo ""; foreach ($r as $value) { diff --git a/lib/code.php b/lib/code.php index 7c898a6..d4aaa64 100644 --- a/lib/code.php +++ b/lib/code.php @@ -40,7 +40,7 @@ { $table = $this->get_table_name(); $id = $this->id; - $code = $this->code; + $code = str_replace("'", "''",$this->code); $date = $this->date; $query = "insert into $table (id, code, date) values ('$id', '$code', '$date')"; return $query; @@ -89,6 +89,8 @@ if ($db->errorCode() == "23000") { // l'id existe dans la table $db->exec("delete from $table where id='$id'"); $db->exec($query); + } else { + die($e->getMessage()); } } return $this; diff --git a/lib/database.php b/lib/database.php dissimilarity index 88% index 584c0eb..66b0a7a 100755 --- a/lib/database.php +++ b/lib/database.php @@ -1,86 +1,63 @@ -data = $db->getAll($this->string); - } - - function select() - { - $this->string = "select"; - } - - function from($tables) - { - $this->tables = $tables; - $this->string .= " ". implode(',', $tables) ." "; - } - - function where($conditions) - { - $this->conditions = $conditions; - foreach ($conditions as $column => $value) { - } - } - } - - class DataBase extends DB - { - var $db; - - function DataBase($dsn) - { - $this->db = DB::Connect($dsn); - } - - function insert($table, $data) - { - $query = "insert $table set ". DataBase::serialize_data($data); - return $this->query($query); - } - - function select($data) - { - $tables = DataBase::extract_tables($data); - $query = "select * from ". implode(',', $tables); - $query .= " where ". DataBase::serialize_data($data, 'and'); - return $this->query($query); - } - - function serialize_data($data, $separator = ',') - { - $tuples = array(); - foreach ($data as $key => $value) { - $tuples[] = " $key='$value' "; - } - return implode(" $separator ", $tuples); - } - - function extract_tables($data) - { - $tables = array(); - foreach (array_keys($data) as $column) { - list($tableName, $columnName) = split('.', $column); - $tables[] = $tableName; - } - return $tables; - } - } +getAttribute(PDO::ATTR_DRIVER_NAME); + if ('sqlite' == $dbType || 'sqlite2' == $dbType) { + $query = "select name from sqlite_master where type='table'"; + } else { + $query = "show tables"; + } + $result = $this->query($query); + $tables = array(); + foreach ($result as $r) { + $tables []= new Table($r['name']); + } + + return $tables; + } + } + + class Table + { + var $name; + var $columns; + + function __construct($name, $columns = array()) + { + $this->name = $name; + $this->columns = $columns; + } + + function loadColumns($db) + { + $table = $this->name; + $dbType = $db->getAttribute(PDO::ATTR_DRIVER_NAME); + if ('sqlite' == $dbType || 'sqlite2' == $dbType) { + $result = $db->fetchColumnTypes($table); + foreach ($result as $column => $type) { + $this->columns []= new Column($column, $type); + } + } else { + $columns = $db->query("show columns from $table"); + } + + } + } + + + class Column + { + var $name; + var $type; + + function __construct($name, $type = NULL) + { + $this->name = $name; + $this->type = $type; + } + } diff --git a/test/SqlCodeTest.php b/test/SqlCodeTest.php index 8974ab7..7d4dee5 100644 --- a/test/SqlCodeTest.php +++ b/test/SqlCodeTest.php @@ -17,6 +17,7 @@ class SqlCodeTest extends UnitTestCase { $this->sqlCode2->id = 'test:001'; $this->sqlCode2->date = '2008-01-25'; $this->markkitDB =& new PDO("sqlite2:markkit-test.db"); + $this->markkitQuery3 = new SqlCode("select * from marks where pageUrl like '%x'"); } function tearDown() @@ -24,6 +25,8 @@ class SqlCodeTest extends UnitTestCase { $tableName = SqlCode::get_table_name(); $id = $this->sqlCode->id; $this->db->exec("delete from $tableName where id='$id'"); + $id = $this->markkitQuery3->id; + $this->db->exec("delete from $tableName where id='$id'"); } function test_search() @@ -51,8 +54,7 @@ class SqlCodeTest extends UnitTestCase { $result = $markkitQuery2->exec($this->markkitDB)->fetchAll(); $expected = 'http://localhost/test/x'; $this->assertEqual($expected, $result[0]['pageUrl']); - $markkitQuery3 = new SqlCode("select * from marks where pageUrl like '%x'"); - $result = $markkitQuery3->exec($this->markkitDB)->fetchAll(); + $result = $this->markkitQuery3->exec($this->markkitDB)->fetchAll(); $expected = 'http://localhost/test/x'; $this->assertEqual($expected, $result[1]['pageUrl']); } @@ -101,14 +103,29 @@ class SqlCodeTest extends UnitTestCase { $this->sqlCode->save(); $md5 = $this->sqlCode->id; $result = $this->db->query("select code from sql where id='$md5'"); - $result = $result->fetch(); + $rows = $result->fetchAll(PDO::FETCH_ASSOC); + $count = count($rows); + $expected = 1; + $this->assertEqual($expected, $count); $expected = "select * from test"; - $this->assertEqual($expected, $result['code']); - $this->sqlCode->save(); + $this->assertEqual($expected, $rows[0]['code']); + $this->sqlCode->save(); // save a second time $result = $this->db->query("select * from sql where id='$md5'"); - $result = $result->fetchAll(); + $rows = $result->fetchAll(PDO::FETCH_ASSOC); + $count = count($rows); $expected = 1; - $this->assertEqual($expected, count($result)); + $this->assertEqual($expected, $count); + $this->markkitQuery3->save(); + $id = $this->markkitQuery3->id; + $result = $this->db->query("select * from sql where id='$id'"); + $rows = $result->fetchAll(PDO::FETCH_ASSOC); + $count = count($rows); + $expected = 1; + $this->assertEqual($expected, $count); + $expected = "/'%x'/"; + $this->assertWantedPattern($expected, $rows[0]['code']); + $expected = $id; + $this->assertEqual($expected, $rows[0]['id']); } } diff --git a/test/TableTest.php b/test/TableTest.php new file mode 100644 index 0000000..f803fc6 --- /dev/null +++ b/test/TableTest.php @@ -0,0 +1,37 @@ +db =& new BDB("sqlite:bazdig-test.db"); + $this->table = new Table('sql'); + } + + function tearDown() + { + } + + function test_listTables() + { + $tables = $this->db->listTables(); + $expected = array($this->table); + $this->assertEqual($expected, $tables); + } + + function test_loadColumns() + { + $this->table->loadColumns($this->db); + $expected = array(new Column('id'), new Column('date'), new Column('code')); + $this->assertEqual($expected, $this->table->columns); + } + +} +// Running the test. +$test =& new TableTest; +$test->run(new HtmlReporter()); +?> diff --git a/test/bazdig-test.db b/test/bazdig-test.db index 8d3c0ac9a5fa37162d0226a6171e00663b2d049a..75d5d91b42bf4a869b9afee6131410c6e40c3ce1 100644 GIT binary patch delta 372 zcwTEzXwaA-CBzuVfC^Y9>e%qGFsm~#Phq~wtj?Uhv2Zh!x)U>-xTq*&W=UdFPHIwO zaRvypI0v~phPWz(I6C>bDu6^L?_ieIRA*rmw-#k&$jnPgttc+c$t+2YPb@9T2lL}W z%Hj<{BmAbH^tP-$imXXIL$mc*(k-x!oVyw)hIE|EZNw^ z#M08p%qYd8I5j6VxkN!rA+0DsS0OjCD7#ppJR`LzRiPj;JvFo_M_=ux-XkNy$b=1{OxflMnD2av>B>Udo=y l#QbctAV)j1S`aY~W0|~{+nJ3Q9ELz`>zOAuc(ZY#Z~=KPWeNZQ delta 269 zcwTGJXi%6SCB!h90RmWnm=TDXC+gU6FfmVIV4lK!b+aIc3iISC%oR+`VL-MwGjph+ z{p5q(Dw7-7rMb*P42`V}OstHICog5s+$_kmiFxt?ZU-R-1YibYCZ_O>jjl{=!GbJE wy2VUG3=OS-$iPV307x1H3*eXL$CNg(v@$e+O7r2D=0%nkfJj?dnLrH(0Gbmzd;kCd -- 2.11.4.GIT