PDO sqlite backend
Christof Meerwald via Phpwiki-talk <[email protected]> Sat, 30 Dec 2023 20:15:09 +0100
| Newsgroups | gmane.comp.web.wiki.phpwiki.talk |
|---|---|
| Message-ID | <[email protected]> |
--xezAqql7qoNQUbaB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
So far I have been using phpwiki with the dba backend, but web hosters
don't always make dba available, so been investigating using SQLite as
the backend. Anyway, tried to use PDO, but ran into a few issues - I
have attached a patch that makes it work (at least for me).
BTW, not really sure what that "$date > 1588437560" check was all
about (1588437560 corresponds to "Sat May 2 18:39:20 2020").
Also not entirely sure what that "else" branch in PDO.php was trying
to do with the database dsn - I am just passing the DATABASE_DSN to
PDO there now (e.g. DATABASE_DSN = "sqlite:/path/to/file.db")
Christof
--
https://cmeerw.org sip:cmeerw at cmeerw.org
mailto:cmeerw at cmeerw.org xmpp:cmeerw at cmeerw.org
--xezAqql7qoNQUbaB
Content-Type: text/x-diff; charset=iso-8859-1
Content-Disposition: attachment; filename="pdo-sqlite.diff"
Content-Transfer-Encoding: quoted-printable
Index: lib/DbSession/PDO.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- lib/DbSession/PDO.php (revision 11069)
+++ lib/DbSession/PDO.php (working copy)
@@ -32,9 +32,9 @@
{
public $_backend_type =3D "PDO";
=20
- public function __construct($dbh, $table)
+ public function __construct($backend, $table)
{
- $this->_dbh =3D $dbh;
+ $this->_backend =3D $backend;
$this->_table =3D $table;
=20
session_set_save_handler(
@@ -49,11 +49,7 @@
=20
public function & _connect()
{
- $dbh =3D &$this->_dbh;
- global $DBParams;
- $db =3D new WikiDB_backend_PDO($DBParams);
- $this->_dbh =3D& $db->_dbh;
- $this->_backend =3D& $db;
+ $dbh =3D &$this->_backend->_dbh;
return $dbh;
}
=20
@@ -70,9 +66,6 @@
=20
public function _disconnect()
{
- if (0 and $this->_dbh) {
- unset($this->_dbh);
- }
}
=20
/**
@@ -118,6 +111,9 @@
$sth->bindParam(1, $id, PDO::PARAM_STR, 32);
if ($sth->execute()) {
$res =3D $sth->fetchColumn();
+ if (empty($res)) {
+ $res =3D '';
+ }
} else {
$res =3D '';
}
@@ -165,10 +161,19 @@
$remote_addr =3D $request->get('REMOTE_ADDR');
=20
$this->_backend->beginTransaction();
- $delete =3D $dbh->prepare("DELETE FROM $table WHERE sess_id=3D?");
- $delete->bindParam(1, $id, PDO::PARAM_STR, 32);
- $delete->execute();
- $sth =3D $dbh->prepare("INSERT INTO $table"
+ $backend_type =3D $this->_backend->backendType();
+ if (substr($backend_type, 0, 5) =3D=3D 'mysql' or
+ $backend_type =3D=3D 'sqlite') {
+ // mysql/sqlite can do it as a single replace command
+ $insert =3D "REPLACE INTO";
+ } else {
+ // otherwise need to do it as a delete/insert
+ $delete =3D $dbh->prepare("DELETE FROM $table WHERE sess_id=3D=
?");
+ $delete->bindParam(1, $id, PDO::PARAM_STR, 32);
+ $delete->execute();
+ $insert =3D "INSERT INTO";
+ }
+ $sth =3D $dbh->prepare("$insert $table"
. " (sess_id, sess_data, sess_date, sess_ip)"
. " VALUES (?, ?, ?, ?)");
$sth->bindParam(1, $id, PDO::PARAM_STR, 32);
@@ -239,7 +244,7 @@
if (preg_match('|^[a-zA-Z0-9/+=3D]+$|', $data)) {
$data =3D base64_decode($data);
}
- if ($date < 908437560 or $date > 1588437560) {
+ if ($date < 908437560) {
$date =3D 0;
}
// session_data contains the <variable name> + "|" + <packed s=
tring>
Index: lib/DbSession/SQL.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- lib/DbSession/SQL.php (revision 11069)
+++ lib/DbSession/SQL.php (working copy)
@@ -36,9 +36,9 @@
{
public $_backend_type =3D "SQL";
=20
- public function __construct($dbh, $table)
+ public function __construct($backend, $table)
{
- $this->_dbh =3D $dbh;
+ $this->_dbh =3D $backend->_dbh;
$this->_table =3D $table;
=20
session_set_save_handler(
@@ -248,7 +248,7 @@
if (preg_match('|^[a-zA-Z0-9/+=3D]+$|', $data)) {
$data =3D base64_decode($data);
}
- if ($date < 908437560 or $date > 1588437560) {
+ if ($date < 908437560) {
$date =3D 0;
}
// session_data contains the <variable name> + "|" + <packed s=
tring>
Index: lib/DbSession/dba.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- lib/DbSession/dba.php (revision 11069)
+++ lib/DbSession/dba.php (working copy)
@@ -37,9 +37,9 @@
{
public $_backend_type =3D "dba";
=20
- public function __construct($dbh, $table)
+ public function __construct($backend, $table)
{
- $this->_dbh =3D $dbh;
+ $this->_dbh =3D $backend->_dbh;
session_set_save_handler(
array(&$this, 'open'),
array(&$this, 'close'),
@@ -221,7 +221,7 @@
}
// session_data contains the <variable name> + "|" + <packed s=
tring>
// we need just the wiki_user object (might be array as well)
- if ($date < 908437560 or $date > 1588437560) {
+ if ($date < 908437560) {
$date =3D 0;
}
$user =3D strstr($packed, "wiki_user|");
Index: lib/DbSession.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- lib/DbSession.php (revision 11069)
+++ lib/DbSession.php (working copy)
@@ -52,8 +52,7 @@
=20
$class =3D "DbSession_" . $db_type;
if (class_exists($class)) {
- // dba has no ->_dbh, so this is used for the session link
- $this->_backend =3D new $class($dbh->_backend->_dbh, $tabl=
e);
+ $this->_backend =3D new $class($dbh->_backend, $table);
return;
}
}
Index: lib/WikiDB/backend/PDO.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- lib/WikiDB/backend/PDO.php (revision 11069)
+++ lib/WikiDB/backend/PDO.php (working copy)
@@ -82,16 +82,11 @@
}
} else {
list($driver, $dsn) =3D explode(":", $dbparams['dsn'], 2);
- foreach (explode(";", trim($dsn)) as $pair) {
- if ($pair) {
- list($option, $value) =3D explode("=3D", $pair, 2);
- $this->_parsedDSN[$option] =3D $value;
- }
- }
- $this->_dbh->database =3D isset($this->_parsedDSN['database'])
- ? $this->_parsedDSN['database']
- : $this->_parsedDSN['dbname'];
+ $this->_parsedDSN =3D array();
}
+ if (empty($this->_parsedDSN['username'])) {
+ $this->_parsedDSN['username'] =3D '';
+ }
if (empty($this->_parsedDSN['password'])) {
$this->_parsedDSN['password'] =3D '';
}
@@ -129,7 +124,7 @@
$this->_hasTransactions =3D true;
try {
$this->_dbh->beginTransaction();
- $this->commit();
+ $this->_dbh->commit();
} catch (PDOException $e) {
$this->_hasTransactions =3D false;
}
@@ -404,7 +399,8 @@
}
if (empty($id)) {
//mysql, mysqli or mysqlt
- if (substr($dbh->databaseType, 0, 5) =3D=3D 'mysql') {
+ if (substr($dbh->databaseType, 0, 5) =3D=3D 'mysql' or
+ $dbh->databaseType =3D=3D 'sqlite') {
// have auto-incrementing, atomic version
$sth =3D $dbh->prepare("INSERT INTO $page_tbl"
. " (id,pagename)"
@@ -576,7 +572,8 @@
$id =3D $this->_get_pageid($pagename, true);
$backend_type =3D $this->backendType();
// optimize: mysql can do this with one REPLACE INTO.
- if (substr($backend_type, 0, 5) =3D=3D 'mysql') {
+ if (substr($backend_type, 0, 5) =3D=3D 'mysql' or
+ $backend_type =3D=3D 'sqlite') {
$sth =3D $dbh->prepare("REPLACE INTO $version_tbl"
. " (id,version,mtime,minor_edit,content,versiondata)"
. " VALUES(?,?,?,?,?,?)");
@@ -715,7 +712,7 @@
if ($sth->fetchColumn()) {
// We're still in the link table (dangling link) so we can=
't delete this
// altogether.
- $dbh->query("UPDATE $page_tbl SET hits=3D0, pagedata=3D'' =
WHERE id=3D$id");
+ $dbh->query("UPDATE $page_tbl SET hits=3D0, pagedata=3D'',=
cached_html=3DNULL WHERE id=3D$id");
$result =3D 0;
} else {
$dbh->query("DELETE FROM $page_tbl WHERE id=3D$id");
@@ -1186,7 +1183,8 @@
=20
// optimize: mysql can do this with one REPLACE INTO.
$backend_type =3D $this->backendType();
- if (substr($backend_type, 0, 5) =3D=3D 'mysql') {
+ if (substr($backend_type, 0, 5) =3D=3D 'mysql' or
+ $backend_type =3D=3D 'sqlite') {
$sth =3D $dbh->prepare("REPLACE INTO $recent_tbl"
. " (id, latestversion, latestmajor, latestminor)"
. " SELECT id, $maxversion, $maxmajor, $maxminor"
Index: lib/WikiDB/backend/PDO_sqlite.php
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- lib/WikiDB/backend/PDO_sqlite.php (nonexistent)
+++ lib/WikiDB/backend/PDO_sqlite.php (working copy)
@@ -0,0 +1,60 @@
+<?php
+/**
+ * Copyright =A9 2023 $ThePhpWikiProgrammingTeam
+ *
+ * This file is part of PhpWiki.
+ *
+ * PhpWiki 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.
+ *
+ * PhpWiki 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 PhpWiki; if not, write to the Free Software Foundation, Inc.,
+ * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
+ *
+ * SPDX-License-Identifier: GPL-2.0-or-later
+ *
+ */
+
+/**
+ * @author: Christof Meerwald
+ */
+require_once 'lib/WikiDB/backend/PDO.php';
+
+class WikiDB_backend_PDO_sqlite extends WikiDB_backend_PDO
+{
+ public function __construct($dbparams)
+ {
+ parent::__construct($dbparams);
+ }
+
+ public function backendType()
+ {
+ return 'sqlite';
+ }
+
+ /*
+ * offset specific syntax within sqlite
+ * convert from,count to SQL "LIMIT $count OFFSET $from"
+ */
+ public function _limit_sql($limit =3D false)
+ {
+ if ($limit) {
+ list($from, $count) =3D $this->limit($limit);
+ if ($from) {
+ $limit =3D " LIMIT $count OFFSET $from";
+ } else {
+ $limit =3D " LIMIT $count";
+ }
+ } else {
+ $limit =3D '';
+ }
+ return $limit;
+ }
+}
Index: schemas/sqlite-initialize.sql
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
--- schemas/sqlite-initialize.sql (revision 11069)
+++ schemas/sqlite-initialize.sql (working copy)
@@ -34,7 +34,8 @@
=20
CREATE TABLE link (
linkfrom INTEGER NOT NULL,
- linkto INTEGER NOT NULL
+ linkto INTEGER NOT NULL,
+ relation INTEGER
);
CREATE INDEX linkfrom_index ON link (linkfrom);
CREATE INDEX linkto_index ON link (linkto);
--xezAqql7qoNQUbaB
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--xezAqql7qoNQUbaB
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
Phpwiki-talk mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/phpwiki-talk
--xezAqql7qoNQUbaB--