install/update_sqlite_from_0_to_1.php

   1 <?php
   2 # import script to upgrade from poche 0.3
   3 $db_path = 'sqlite:../db/poche.sqlite';
   4 $handle = new PDO($db_path);
   5 $handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   6
   7 # Requêtes à exécuter pour mettre à jour poche.sqlite en 1.x
   8
   9 # ajout d'un champ user_id sur la table entries
  10 $sql = 'ALTER TABLE entries RENAME TO tempEntries;';
  11 $query = $handle->prepare($sql);
  12 $query->execute();
  13
  14 $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);';
  15 $query = $handle->prepare($sql);
  16 $query->execute();
  17
  18 $sql = 'INSERT INTO entries (id, title, url, is_read, is_fav, content) SELECT id, title, url, is_read, is_fav, content FROM tempEntries;';
  19 $query = $handle->prepare($sql);
  20 $query->execute();
  21
  22 # Update tout pour mettre user_id = 1
  23 $sql = 'UPDATE entries SET user_id = 1;';
  24 $query = $handle->prepare($sql);
  25 $query->execute();
  26
  27 # Changement des flags pour les lus / favoris
  28 $sql = 'UPDATE entries SET is_read = 1 WHERE is_read = -1;';
  29 $query = $handle->prepare($sql);
  30 $query->execute();
  31
  32 $sql = 'UPDATE entries SET is_fav = 1 WHERE is_fav = -1;';
  33 $query = $handle->prepare($sql);
  34 $query->execute();
  35
  36 # Création de la table users
  37 $sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, name TEXT, email TEXT);';
  38 $query = $handle->prepare($sql);
  39 $query->execute();
  40
  41 $sql = 'INSERT INTO users (username) SELECT value FROM config WHERE name = "login";';
  42 $query = $handle->prepare($sql);
  43 $query->execute();
  44
  45 $sql = "UPDATE users SET password = (SELECT value FROM config WHERE name = 'password')";
  46 $query = $handle->prepare($sql);
  47 $query->execute();
  48
  49 # Création de la table users_config
  50 $sql = 'CREATE TABLE users_config (id INTEGER PRIMARY KEY, user_id NUMERIC, name TEXT, value TEXT);';
  51 $query = $handle->prepare($sql);
  52 $query->execute();
  53
  54 $sql = 'INSERT INTO users_config (user_id, name, value) VALUES (1, "pager", "10");';
  55 $query = $handle->prepare($sql);
  56 $query->execute();
  57
  58 $sql = 'INSERT INTO users_config (user_id, name, value) VALUES (1, "language", "en_EN.UTF8");';
  59 $query = $handle->prepare($sql);
  60 $query->execute();
  61
  62 # Suppression de la table temporaire
  63 $sql = 'DROP TABLE tempEntries;';
  64 $query = $handle->prepare($sql);
  65 $query->execute();
  66
  67 # Vidage de la table de config
  68 $sql = 'DELETE FROM config;';
  69 $query = $handle->prepare($sql);
  70 $query->execute();
  71
  72 echo 'welcome to poche 1.0 !';