From 580d60b9416b3445300f37fc0ecc160254ed0676 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 15:46:17 +0200 Subject: file to update from 0.x to 1.x \o/ --- install/mysql.sql | 34 ++++++++++++++++++ install/poche.sqlite.in | Bin 0 -> 294912 bytes install/postgres.sql | 30 ++++++++++++++++ install/update_sqlite_from_0_to_1.php | 64 ++++++++++++++++++++++++++++++++++ 4 files changed, 128 insertions(+) create mode 100644 install/mysql.sql create mode 100755 install/poche.sqlite.in create mode 100644 install/postgres.sql create mode 100644 install/update_sqlite_from_0_to_1.php (limited to 'install') diff --git a/install/mysql.sql b/install/mysql.sql new file mode 100644 index 00000000..cb232a84 --- /dev/null +++ b/install/mysql.sql @@ -0,0 +1,34 @@ +CREATE TABLE IF NOT EXISTS `config` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `value` varchar(255) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `entries` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(255) NOT NULL, + `url` varchar(255) NOT NULL, + `is_read` tinyint(1) NOT NULL, + `is_fav` tinyint(1) NOT NULL, + `content` blob NOT NULL, + `user_id` int(11) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `users` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `username` varchar(255) NOT NULL, + `password` varchar(255) NOT NULL, + `name` int(255) NOT NULL, + `email` varchar(255) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `users_config` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `name` varchar(255) NOT NULL, + `value` varchar(255) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; \ No newline at end of file diff --git a/install/poche.sqlite.in b/install/poche.sqlite.in new file mode 100755 index 00000000..45add0d7 Binary files /dev/null and b/install/poche.sqlite.in differ diff --git a/install/postgres.sql b/install/postgres.sql new file mode 100644 index 00000000..9e0e8276 --- /dev/null +++ b/install/postgres.sql @@ -0,0 +1,30 @@ +CREATE TABLE config ( + id bigserial primary key, + name varchar(255) NOT NULL, + value varchar(255) NOT NULL +); + +CREATE TABLE entries ( + id bigserial primary key, + title varchar(255) NOT NULL, + url varchar(255) NOT NULL, + is_read boolean DEFAULT false, + is_fav boolean DEFAULT false, + content TEXT, + user_id integer NOT NULL +); + +CREATE TABLE users ( + id bigserial primary key, + username varchar(255) NOT NULL, + password varchar(255) NOT NULL, + name varchar(255) NOT NULL, + email varchar(255) NOT NULL +); + +CREATE TABLE users_config ( + id bigserial primary key, + user_id integer NOT NULL, + name varchar(255) NOT NULL, + value varchar(255) NOT NULL +); \ No newline at end of file diff --git a/install/update_sqlite_from_0_to_1.php b/install/update_sqlite_from_0_to_1.php new file mode 100644 index 00000000..2ee65522 --- /dev/null +++ b/install/update_sqlite_from_0_to_1.php @@ -0,0 +1,64 @@ +setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + +# Requêtes à exécuter pour mettre à jour poche.sqlite en 1.x + +# ajout d'un champ user_id sur la table entries +$sql = 'ALTER TABLE entries RENAME TO tempEntries;'; +$query = $handle->prepare($sql); +$query->execute(); + +$sql = 'CREATE TABLE entries (id INTEGER PRIMARY KEY, title TEXT, url TEXT, is_read NUMERIC, is_fav NUMERIC, content BLOB, user_id NUMERIC);'; +$query = $handle->prepare($sql); +$query->execute(); + +$sql = 'INSERT INTO entries (id, title, url, is_read, is_fav, content) SELECT id, title, url, is_read, is_fav, content FROM tempEntries;'; +$query = $handle->prepare($sql); +$query->execute(); + +# Update tout pour mettre user_id = 1 +$sql = 'UPDATE entries SET user_id = 1;'; +$query = $handle->prepare($sql); +$query->execute(); + +# Changement des flags pour les lus / favoris +$sql = 'UPDATE entries SET is_read = 1 WHERE is_read = -1;'; +$query = $handle->prepare($sql); +$query->execute(); + +$sql = 'UPDATE entries SET is_fav = 1 WHERE is_fav = -1;'; +$query = $handle->prepare($sql); +$query->execute(); + +# Création de la table users +$sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, name TEXT, email TEXT);'; +$query = $handle->prepare($sql); +$query->execute(); + +$sql = 'INSERT INTO users (username) SELECT value FROM config WHERE name = "login";'; +$query = $handle->prepare($sql); +$query->execute(); + +$sql = "UPDATE users SET password = (SELECT value FROM config WHERE name = 'password')"; +$query = $handle->prepare($sql); +$query->execute(); + +# Création de la table users_config +$sql = 'CREATE TABLE users_config (id INTEGER PRIMARY KEY, user_id NUMERIC, name TEXT, value TEXT);'; +$query = $handle->prepare($sql); +$query->execute(); + +# Suppression de la table temporaire +$sql = 'DROP TABLE tempEntries;'; +$query = $handle->prepare($sql); +$query->execute(); + +# Vidage de la table de config +$sql = 'DELETE FROM config;'; +$query = $handle->prepare($sql); +$query->execute(); + +echo 'welcome to poche 1.0 !'; \ No newline at end of file -- cgit v1.2.3 From b916bcfccc5a8b1c1852c0bf39bb6f9837296a4e Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 19:14:28 +0200 Subject: fixes for 1.0-beta --- install/poche.sqlite | Bin 0 -> 294912 bytes install/poche.sqlite.in | Bin 294912 -> 0 bytes install/update_sqlite_from_0_to_1.php | 2 +- 3 files changed, 1 insertion(+), 1 deletion(-) create mode 100755 install/poche.sqlite delete mode 100755 install/poche.sqlite.in (limited to 'install') diff --git a/install/poche.sqlite b/install/poche.sqlite new file mode 100755 index 00000000..45add0d7 Binary files /dev/null and b/install/poche.sqlite differ diff --git a/install/poche.sqlite.in b/install/poche.sqlite.in deleted file mode 100755 index 45add0d7..00000000 Binary files a/install/poche.sqlite.in and /dev/null differ diff --git a/install/update_sqlite_from_0_to_1.php b/install/update_sqlite_from_0_to_1.php index 2ee65522..f3801eb1 100644 --- a/install/update_sqlite_from_0_to_1.php +++ b/install/update_sqlite_from_0_to_1.php @@ -11,7 +11,7 @@ $sql = 'ALTER TABLE entries RENAME TO tempEntries;'; $query = $handle->prepare($sql); $query->execute(); -$sql = 'CREATE TABLE entries (id INTEGER PRIMARY KEY, title TEXT, url TEXT, is_read NUMERIC, is_fav NUMERIC, content BLOB, user_id NUMERIC);'; +$sql = 'CREATE TABLE entries (id INTEGER PRIMARY KEY, title TEXT, url TEXT, is_read NUMERIC DEFAULT 0, is_fav NUMERIC DEFAULT 0, content BLOB, user_id NUMERIC);'; $query = $handle->prepare($sql); $query->execute(); -- cgit v1.2.3 From 76e9ca2acf55731929e3d83c0af7d7ae78732a24 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 21:00:06 +0200 Subject: good version of poche.sqlite --- install/poche.sqlite | Bin 294912 -> 360448 bytes 1 file changed, 0 insertions(+), 0 deletions(-) (limited to 'install') diff --git a/install/poche.sqlite b/install/poche.sqlite index 45add0d7..c268223d 100755 Binary files a/install/poche.sqlite and b/install/poche.sqlite differ -- cgit v1.2.3 From 04b55e97deb5f32d112f7a03839787bc6afbb2b9 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Wed, 7 Aug 2013 21:16:12 +0200 Subject: populate user_config --- install/update_sqlite_from_0_to_1.php | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'install') diff --git a/install/update_sqlite_from_0_to_1.php b/install/update_sqlite_from_0_to_1.php index f3801eb1..c88af540 100644 --- a/install/update_sqlite_from_0_to_1.php +++ b/install/update_sqlite_from_0_to_1.php @@ -51,6 +51,14 @@ $sql = 'CREATE TABLE users_config (id INTEGER PRIMARY KEY, user_id NUMERIC, name $query = $handle->prepare($sql); $query->execute(); +$sql = 'INSERT INTO users_config (user_id, name, value) VALUES (1, "pager", "10");'; +$query = $handle->prepare($sql); +$query->execute(); + +$sql = 'INSERT INTO users_config (user_id, name, value) VALUES (1, "language", "en_EN.UTF8");'; +$query = $handle->prepare($sql); +$query->execute(); + # Suppression de la table temporaire $sql = 'DROP TABLE tempEntries;'; $query = $handle->prepare($sql); -- cgit v1.2.3 From ed06f040776d5e2f38b938005ebc2b09ddd41bd4 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Nicolas=20L=C5=93uillet?= Date: Thu, 8 Aug 2013 09:11:12 +0200 Subject: test if /install exists --- install/update_sqlite_from_0_to_1.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'install') diff --git a/install/update_sqlite_from_0_to_1.php b/install/update_sqlite_from_0_to_1.php index c88af540..299abf48 100644 --- a/install/update_sqlite_from_0_to_1.php +++ b/install/update_sqlite_from_0_to_1.php @@ -1,5 +1,5 @@ setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); -- cgit v1.2.3