From 37b641966f4a550637421c345e5013cf24645226 Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Sun, 18 Aug 2013 14:59:03 +0200 Subject: Issue #100 : base pour utilisation SQLite MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Ajout de la structure pour l'utilisation de SQLite. Tout n'est pas fonctionnel, loin de là, certaines requêtes SQL ne passent pas. Pour essayer tout de même, décommenter le bloc TODO dans le fichier public/install.php --- app/models/Entry.php | 2 +- lib/minz/Configuration.php | 1 + lib/minz/dao/Model_pdo.php | 17 +++++-- public/data/.gitignore | 1 + public/install.php | 110 +++++++++++++++++++++++++++++---------------- 5 files changed, 87 insertions(+), 44 deletions(-) diff --git a/app/models/Entry.php b/app/models/Entry.php index ae70bb58e..1d08f09da 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -350,7 +350,7 @@ class EntryDAO extends Model_pdo { public function cleanOldEntries ($nb_month) { $date = 60 * 60 * 24 * 30 * $nb_month; - $sql = 'DELETE FROM ' . $this->prefix . 'entry WHERE date <= ? AND is_favorite = 0 AND annotation = ""'; + $sql = 'DELETE FROM ' . $this->prefix . 'entry WHERE date <= ? AND is_favorite = 0'; $stm = $this->bd->prepare ($sql); $values = array ( diff --git a/lib/minz/Configuration.php b/lib/minz/Configuration.php index e670a5b21..ad6df1bcf 100755 --- a/lib/minz/Configuration.php +++ b/lib/minz/Configuration.php @@ -217,6 +217,7 @@ class Configuration { ); } + self::$db['type'] = isset ($db['type']) ? $db['type'] : 'mysql'; self::$db['host'] = $db['host']; self::$db['user'] = $db['user']; self::$db['password'] = $db['password']; diff --git a/lib/minz/dao/Model_pdo.php b/lib/minz/dao/Model_pdo.php index b3a0569c1..a101887d1 100755 --- a/lib/minz/dao/Model_pdo.php +++ b/lib/minz/dao/Model_pdo.php @@ -20,12 +20,21 @@ class Model_pdo { * Créé la connexion à la base de données à l'aide des variables * HOST, BASE, USER et PASS définies dans le fichier de configuration */ - public function __construct ($type = 'mysql') { + public function __construct () { $db = Configuration::dataBase (); + try { - $string = $type - . ':host=' . $db['host'] - . ';dbname=' . $db['base']; + $type = $db['type']; + if($type == 'mysql') { + $string = $type + . ':host=' . $db['host'] + . ';dbname=' . $db['base']; + } elseif($type == 'sqlite') { + $string = $type + . ':/' . PUBLIC_PATH + . '/data/' . $db['base'] . '.sqlite'; + } + $this->bd = new PDO ( $string, $db['user'], diff --git a/public/data/.gitignore b/public/data/.gitignore index 81154d14f..315458098 100644 --- a/public/data/.gitignore +++ b/public/data/.gitignore @@ -1,2 +1,3 @@ favicons Configuration.array.php +*.sqlite diff --git a/public/install.php b/public/install.php index 97bed4eac..f5801acc6 100644 --- a/public/install.php +++ b/public/install.php @@ -8,14 +8,30 @@ if (isset ($_GET['step'])) { define ('STEP', 1); } -define ('SQL_REQ', 'CREATE TABLE IF NOT EXISTS `%scategory` ( +define ('SQL_REQ_CAT', 'CREATE TABLE IF NOT EXISTS `%scategory` ( `id` varchar(6) NOT NULL, `name` varchar(255) NOT NULL, `color` varchar(7) NOT NULL, PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +);'); -CREATE TABLE IF NOT EXISTS `%sentry` ( +define ('SQL_REQ_FEED', 'CREATE TABLE IF NOT EXISTS `%sfeed` ( + `id` varchar(6) NOT NULL, + `url` text NOT NULL, + `category` varchar(6) DEFAULT \'000000\', + `name` varchar(255) NOT NULL, + `website` text NOT NULL, + `description` text NOT NULL, + `lastUpdate` int(11) NOT NULL, + `priority` int(2) NOT NULL DEFAULT \'10\', + `pathEntries` varchar(500) DEFAULT NULL, + `httpAuth` varchar(500) DEFAULT NULL, + `error` int(1) NOT NULL DEFAULT \'0\', + PRIMARY KEY (`id`), + FOREIGN KEY (`category`) REFERENCES %scategory(id) ON DELETE SET NULL ON UPDATE CASCADE +);'); + +define ('SQL_REQ_ENTRY', 'CREATE TABLE IF NOT EXISTS `%sentry` ( `id` varchar(6) NOT NULL, `guid` text NOT NULL, `title` varchar(255) NOT NULL, @@ -27,33 +43,12 @@ CREATE TABLE IF NOT EXISTS `%sentry` ( `is_favorite` int(11) NOT NULL, `is_public` int(1) NOT NULL, `id_feed` varchar(6) NOT NULL, - `annotation` text NOT NULL, `tags` text NOT NULL, `lastUpdate` int(11) NOT NULL, PRIMARY KEY (`id`), - KEY `id_feed` (`id_feed`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; + FOREIGN KEY (`id_feed`) REFERENCES %sfeed(id) ON DELETE CASCADE ON UPDATE CASCADE +);'); -CREATE TABLE IF NOT EXISTS `%sfeed` ( - `id` varchar(6) NOT NULL, - `url` text NOT NULL, - `category` varchar(6) DEFAULT \'000000\', - `name` varchar(255) NOT NULL, - `website` text NOT NULL, - `description` text NOT NULL, - `lastUpdate` int(11) NOT NULL, - `priority` int(2) NOT NULL DEFAULT \'10\', - `pathEntries` varchar(500) DEFAULT NULL, - `httpAuth` varchar(500) DEFAULT NULL, - `error` int(1) NOT NULL DEFAULT \'0\', - PRIMARY KEY (`id`), - KEY `category` (`category`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -ALTER TABLE `%sentry` - ADD CONSTRAINT `entry_ibfk_1` FOREIGN KEY (`id_feed`) REFERENCES `%sfeed` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE `%sfeed` - ADD CONSTRAINT `feed_ibfk_4` FOREIGN KEY (`category`) REFERENCES `%scategory` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;'); function writeLine ($f, $line) { fwrite ($f, $line . "\n"); @@ -166,12 +161,14 @@ function saveStep2 () { } function saveStep3 () { if (!empty ($_POST)) { - if (empty ($_POST['host']) || + if (empty ($_POST['type']) || + empty ($_POST['host']) || empty ($_POST['user']) || empty ($_POST['base'])) { $_SESSION['bd_error'] = true; } + $_SESSION['bd_type'] = isset ($_POST['type']) ? $_POST['type'] : 'mysql'; $_SESSION['bd_host'] = addslashes ($_POST['host']); $_SESSION['bd_user'] = addslashes ($_POST['user']); $_SESSION['bd_pass'] = addslashes ($_POST['pass']); @@ -187,6 +184,7 @@ function saveStep3 () { writeLine ($f, 'base_url = "' . $_SESSION['base_url'] . '"'); writeLine ($f, 'title = "' . $_SESSION['title'] . '"'); writeLine ($f, '[db]'); + writeLine ($f, 'type = "' . $_SESSION['bd_type'] . '"'); writeLine ($f, 'host = "' . $_SESSION['bd_host'] . '"'); writeLine ($f, 'user = "' . $_SESSION['bd_user'] . '"'); writeLine ($f, 'password = "' . $_SESSION['bd_pass'] . '"'); @@ -277,7 +275,8 @@ function checkStep2 () { } function checkStep3 () { $conf = file_exists (APP_PATH . '/configuration/application.ini'); - $bd = isset ($_SESSION['bd_host']) && + $bd = isset ($_SESSION['bd_type']) && + isset ($_SESSION['bd_host']) && isset ($_SESSION['bd_user']) && isset ($_SESSION['bd_pass']) && isset ($_SESSION['bd_name']); @@ -294,19 +293,33 @@ function checkBD () { $error = false; try { - $c = new PDO ('mysql:host=' . $_SESSION['bd_host'] . ';dbname=' . $_SESSION['bd_name'], + $str = ''; + if($_SESSION['bd_type'] == 'mysql') { + $str = 'mysql:host=' . $_SESSION['bd_host'] . ';dbname=' . $_SESSION['bd_name']; + } elseif($_SESSION['bd_type'] == 'sqlite') { + $str = 'sqlite:' . PUBLIC_PATH + . '/data/' . $_SESSION['bd_name'] . '.sqlite'; + } + + $c = new PDO ($str, $_SESSION['bd_user'], $_SESSION['bd_pass']); - $sql = sprintf ( - SQL_REQ, - $_SESSION['bd_prefix'], - $_SESSION['bd_prefix'], - $_SESSION['bd_prefix'], - $_SESSION['bd_prefix'], - $_SESSION['bd_prefix'], - $_SESSION['bd_prefix'], - $_SESSION['bd_prefix'] - ); + + $sql = sprintf (SQL_REQ_CAT, $_SESSION['bd_prefix']); + $res = $c->query ($sql); + + if (!$res) { + $error = true; + } + + $sql = sprintf (SQL_REQ_FEED, $_SESSION['bd_prefix'], $_SESSION['bd_prefix']); + $res = $c->query ($sql); + + if (!$res) { + $error = true; + } + + $sql = sprintf (SQL_REQ_ENTRY, $_SESSION['bd_prefix'], $_SESSION['bd_prefix']); $res = $c->query ($sql); if (!$res) { @@ -496,6 +509,25 @@ function printStep3 () {
+ +
-- cgit v1.2.3