4 This plugin provides the database abstraction layer for SQL-compliant
5 relational databases, for which interfaces in either ADOdb, PEAR::DB
6 or PHP's dbx extension exist. It can access MySQL and Postgres without
7 any of these wrappers, btw.
8 You could establish a database connection via one of these db wrappers
9 yourself and put it into the global $db var, but it is sometimes better
10 to use the "anydb_connect()" function.
12 Currently this plugin is mainly used (and only tested with) the
13 PostgreSQL database. You should rather not use this with MySQL before
14 4.1 (even if it still works with 3.x versions).
17 - you should use the anydb_connect() when possible or else assign
18 your PEAR::DB, ADOdb or dbx connection handle to the global '$db'
19 - this interface also accepts native MY or PG connection handles
20 - IMPORTANT: this newer OO-interface requires that the database
21 connection is already established when you load this plugin - else
22 you should put the anydb_connect() call herein
23 - sqlite is only supported by PEAR::DB currently (but not tested)
24 - dbx is rather memory exhaustive ("emalloc() unable to allocate
25 1.7 gigabytes"...) - but maybe just a bug in my version(?)
26 - dbx is otherwise a very good thing, but now not very suitable
27 for the newer ewiki database layer
28 - ADOdb does not work with PHP5
29 - ewiki uses the Latin-1 charset exclusively, your database needs
30 to know this (createdb -E LATIN1 wikidb for PostgreSQL)
31 - else you could enable EWIKI_DB_UTF8 for Postgres "UNICODE" databases,
32 where "SET NAMES" doesn't work
33 - there is no _DB_F_BINARY support with PostgreSQL, so please use
34 db/binary_store meanwhile or enable the EWIKI_DB_BIN64 workaround
35 (minimally slower, most features remain, only irreal drawback is
36 that _BINARY entries cannot be ::SEARCHed then)
39 - [http://php.weblogs.com/adodb] for ADOdb
40 - [http://pear.php.net/] for PEAR::DB
41 - [http://www.php.net/manual/en/ref.dbx.html] for dbx()
45 #-- open db link here, if not already done, example:
47 include(".../adodb/adodb.inc.php")
51 $db = anydb_connect("localhost", "root", "$password", "test", "mysql");
56 define("EWIKI_DB_UTF8", false);
57 define("EWIKI_DB_BIN64", false); // cipher any _BINARY entry
60 #-- plugin registration
61 $ewiki_plugins["database"][0] = "ewiki_database_anydb";
66 class ewiki_database_anydb {
68 var $table = EWIKI_DB_TABLE_NAME;
70 function ewiki_database_anydb() {
71 anydb_query("SELECT 1;", $GLOBALS["db"]); // saves connection handle
75 function GET($id, $version) {
76 if (EWIKI_DB_UTF8) $this->UTF8_ENCODE($id);
77 $id = anydb_escape_string($id);
79 $AND_VERSION = "AND (version=$version)";
81 $result = anydb_query("
82 SELECT * FROM $this->table
83 WHERE (pagename='$id') $AND_VERSION
84 ORDER BY version DESC LIMIT 1
86 if ($result && ($r = anydb_fetch_array($result, "_ASSOC_ONLY=1"))) {
87 $r["id"] = $r["pagename"];
88 unset($r["pagename"]);
90 if (EWIKI_DB_UTF8) $this->UTF8_DECODE($r);
91 if (EWIKI_DB_BIN64) $this->BIN64_DECODE($r);
97 function WRITE($hash, $overwrite=0) {
98 if (EWIKI_DB_BIN64) $this->BIN64_ENCODE($hash);
99 if (EWIKI_DB_UTF8) $this->UTF8_ENCODE($hash);
102 $id = anydb_escape_string($hash["id"]);
103 $ver = $hash["version"];
104 $current = "FROM $this->table WHERE (pagename='$id') AND (version=$ver)";
105 if (($r = anydb_query("SELECT flags $current"))
106 and anydb_fetch_array($r)) {
108 anydb_query("DELETE $current");
114 #-- build INSERT command
115 $hash["pagename"] = $hash["id"];
118 foreach ($hash as $index => $value) {
119 if (is_int($index)) {
122 $a = ($sql1 ? ', ' : '');
123 $sql1 .= $a . $index;
124 $sql2 .= $a . "'" . anydb_escape_string($value) . "'";
127 $result = anydb_query(
128 "INSERT INTO $this->table ($sql1) VALUES ($sql2)"
130 return($result ?1:0);
135 if (EWIKI_DB_UTF8) $this->UTF8_ENCODE($id);
136 $id = anydb_escape_string($id);
137 anydb_query("UPDATE $this->table SET hits=(hits+1) WHERE pagename='$id'");
141 function FIND($list) {
142 if (EWIKI_DB_UTF8) $this->UTF8_ENCODE($list);
144 foreach ($list as $id) {
147 $where[] = "(pagename='".anydb_escape_string($id)."')";
150 $where = implode(" OR ", $where);
151 if (strlen($where)) { $where = "WHERE $where"; }
152 $result = anydb_query(
153 "SELECT pagename AS id, meta, flags FROM $this->table $where"
156 while ($result && ($row = anydb_fetch_array($result))) {
157 $id = EWIKI_DB_UTF8 ? utf8_decode($row[0]) : $row[0];
159 $r[$id] = $row["meta"];
160 $r[$id]["flags"] = $row["flags"];
162 $r[$id] = $row["flags"];
165 if (EWIKI_DB_UTF8) $this->UTF8_DECODE($r);
170 function GETALL($fields, $mask=0, $filter=0) {
171 $result = anydb_query("SELECT pagename AS id, flags, version, ".
172 implode(", ", $fields) .
173 " FROM $this->table " .
174 " ORDER BY id, version DESC"
176 return $this->AS_DBQUERY_RESULT($result, $fields);
180 function AS_DBQUERY_RESULT(&$result, $fields) {
181 $r = new ewiki_dbquery_result($fields);
183 if ($result) while ($row = anydb_fetch_array($result)) {
184 if (EWIKI_DB_UTF8) $this->UTF8_DECODE($row);
185 $drop = EWIKI_CASE_INSENSITIVE ? strtolower($row["id"]) : $row["id"];
186 if (($last != $drop) && ($last = $drop)) {
187 if (EWIKI_DB_UTF8) $this->UTF8_DECODE($row);
188 if (EWIKI_DB_BIN64) $this->BIN64_DECODE($row);
196 function SEARCH($field, $content, $ci="i", $regex=0, $mask=0, $filter=0) {
198 if (EWIKI_DB_UTF8) $this->UTF8_ENCODE($content);
199 // if (EWIKI_DB_BIN64 && ($field=="content") && ($flags&EWIKI_DB_F_BINARY)) $this->BIN64_ENCODE($content);
200 if ($field != "id") {
201 $sqlfield = ", $field";
204 if ($GLOBALS["anydb_type"] == ANYDB_MY) {
207 $regex = ($ci ? "~": "~*");
209 $WHERE = "$field $regex '$content'";
212 $content = strtolower($content);
213 if ($anydb_type == ANYDB_PG && $field == 'id')
214 $WHERE = "POSITION('$content' IN LOWER(pagename)) > 0";
216 $WHERE = "POSITION('$content' IN LOWER($field)) > 0";
219 $WHERE="POSITION('$content' IN $field) > 0";
221 $content = anydb_escape_string($content);
222 $result = anydb_query("
223 SELECT pagename AS id, version, flags $sqlfield
226 ORDER BY id, version DESC
228 return $this->AS_DBQUERY_RESULT($result, array($field, "version", "flags"));
232 function DELETE($id, $version) {
233 if (EWIKI_DB_UTF8) $this->UTF8_ENCODE($id);
234 $id = anydb_escape_string($id);
235 anydb_query("DELETE FROM $this->table WHERE pagename='$id' AND version=$version");
240 anydb_query("CREATE TABLE $this->table
241 ( pagename VARCHAR(160) NOT NULL,
242 version INTEGER DEFAULT 0 NOT NULL,
243 flags INTEGER DEFAULT 0,
244 content TEXT DEFAULT '',
245 refs TEXT DEFAULT '',
246 meta TEXT DEFAULT '',
247 author VARCHAR(100) DEFAULT 'ewiki',
248 created INTEGER DEFAULT ".time().",
249 lastmodified INTEGER DEFAULT 0,
250 hits INTEGER DEFAULT 0
253 ALTER TABLE ONLY $this->table
254 ADD CONSTRAINT internal_id PRIMARY KEY (pagename, version);
259 #-- for charset-aware databases
260 function UTF8_ENCODE(&$a) {
261 if (is_array($a)) foreach ($a as $i=>$v) {
262 $a[$i] = is_array($v) ? $this->UTF8_ENCODE($v) : utf8_encode($v);
265 $a = utf8_encode($a);
268 function UTF8_DECODE(&$a) {
269 if (is_array($a)) foreach ($a as $i=>$v) {
270 $a[$i] = is_array($v) ? $this->UTF8_DECODE($v) : utf8_decode($v);
273 $a = utf8_decode($a);
278 #-- only engages if the EWIKI_DB_F_BINARY flag is set
279 function BIN64_ENCODE(&$a) {
281 $a = base64_encode($a);
283 elseif ($a["flags"] & EWIKI_DB_F_BINARY) {
284 $a["content"] = base64_encode($a["content"]);
287 function BIN64_DECODE(&$a) {
288 if (isset($a["content"]) && ($a["flags"] & EWIKI_DB_F_BINARY)) {
289 $a["content"] = base64_decode($a["content"]);
302 #----------------------------------------------------------------------------
306 if (!function_exists("anydb_connect")) {
307 #############################################################################
309 ### anydb access wrapper wrapper ###
311 #############################################################################
314 define("ANYDB_PEAR", 21);
315 define("ANYDB_ADO", 22);
316 define("ANYDB_DBX", 23);
317 define("ANYDB_PG", 51); // Postgres
318 define("ANYDB_MY", 52); // MySQL3.x
319 define("ANYDB_LI", 53); // SQLite
320 define("ANYDB_MI", 54); // MySQLi/4
323 function anydb_connect($host="localhost", $user="", $pw="", $dbname="test", $dbtype="mysql") {
324 global $anydb_handle;
326 and ($db = DB::connect("$dbtype://$user:$pw@$host/$dbname"))
327 and (is_a($db, "db_common"))
328 and ($db->setFetchMode(DB_FETCHMODE_ASSOC) or true)
329 or function_exists("newadoconnection")
330 and ($db = NewAdoConnection($dbtype))
331 and ($db->connect($host, $user, $pw, $dbname))
332 and ($db->setFetchMode(ADODB_FETCH_ASSOC) or true)
333 or ($dbtype[0]=="p") and function_exists("pg_connect")
334 and ($db = pg_connect("dbname=$dbname user=$user password=$pw"))
335 or function_exists("mysql_connect")
336 and ($db = mysql_connect($host, $user, $pw))
337 and (mysql_query("USE $dbname"))
338 or function_exists("dbx_connect")
339 and ($db = dbx_connect($dbtype, $host, $dbname, $user, $pw))
342 if ($anydb_handle = $db) {
343 $charset = EWIKI_DB_UTF8 ? "UTF8" : "ISO-8859-1";
344 @anydb_query("SET NAMES '$charset'"); #-- not all databases support this
350 function anydb_handle($db=NULL) {
351 global $anydb_handle, $anydb_type;
353 $anydb_handle = & $db;
354 $anydb_type = anydb_type($anydb_handle);
356 return($anydb_handle);
360 function anydb_type(&$obj) {
361 if (is_object($obj)) {
362 if (is_a($obj, "db_common") || is_a($obj, "db_result")) {
365 elseif (is_a($obj, "adoconnection") || is_a($obj, "adorecordset")) {
368 elseif (is_a($obj, "stdclass")) {
372 elseif (is_resource($obj) && ($type = strtok(get_resource_type($obj), " "))) {
373 if ($type == "pgsql") {
376 elseif ($type == "mysql") {
383 function anydb_query($sql, $db="") {
385 $db = anydb_handle($db);
387 if ($anydb_type == ANYDB_PEAR) {
388 $res = $db->query($sql);
389 if (DB::isError($res)) { $res = false; }
391 elseif ($anydb_type == ANYDB_ADO) {
392 $res = $db->Execute($sql);
394 elseif ($anydb_type == ANYDB_DBX) {
395 $res = dbx_query($db, $sql, DBX_RESULT_ASSOC);
397 elseif ($anydb_type == ANYDB_PG) {
398 $res = pg_query($db, $sql);
400 elseif ($anydb_type == ANYDB_MY) {
401 $res = mysql_query($sql, $db, MYSQL_ASSOC);
408 function anydb_fetch_array(&$res, $assoc_only=0) {
410 $anydb_type = anydb_type($res);
412 if ($anydb_type == ANYDB_PEAR) {
413 $r = $res->fetchRow(DB_FETCHMODE_ASSOC);
418 elseif ($anydb_type == ANYDB_ADO) {
419 $r = $res->FetchRow();
420 #<ok> $r = obj || false
422 elseif ($anydb_type == ANYDB_DBX) {
423 $r = array_shift($res->data);
424 #<ok># $r == obj || 1 || false
426 elseif ($anydb_type == ANYDB_PG) {
427 $r = pg_fetch_assoc($res);
429 elseif ($anydb_type == ANYDB_MY) {
430 $r = mysql_fetch_array($res, $db);
432 #-- make numeric indicies, if wanted
434 if (!$assoc_only && is_array($r) && count($r)) {
435 foreach ($r as $i=>$d) {
446 function anydb_escape_string($s, $db="") {
447 $db = anydb_handle($db);
448 $type = anydb_type($db);
449 if ($type == ANYDB_PEAR) {
450 $s = $db->quoteString($s);
452 elseif ($type == ANYDB_ADO) {
455 $s = substr($s, 1, strlen($s) - 2);
458 elseif ($type == ANYDB_DBX) {
459 $s = dbx_escape_string($db, (string)$s);
461 elseif ($type == ANYDB_PG) {
462 $s = pg_escape_string((string)$s);
464 elseif ($type == ANYDB_MY) {
465 $s = mysql_escape_string((string)$s);
474 #############################################################################
476 #############################################################################