<?php

  class sml_data_install extends mw_data{

    // ---------------------------------------------------------------------------------
    //                                                                           install
    //

    public function mw_sourceml_install_mysql($plugin, $env){
      $sgbd = $this->sgbd();
      try{
        $EXISTS =
            $sgbd->table_exists("#--sml_authors")
        ||  $sgbd->table_exists("#--sml_classes")
        ||  $sgbd->table_exists("#--sml_licences")
        ||  $sgbd->table_exists("#--sml_sources")
        ||  $sgbd->table_exists("#--sml_sources_access")
        ||  $sgbd->table_exists("#--sml_sources_authors")
        ||  $sgbd->table_exists("#--sml_sources_infos")
        ||  $sgbd->table_exists("#--sml_source_cache")
        ||  $sgbd->table_exists("#--sml_source_compositions")
        ||  $sgbd->table_exists("#--sml_source_derivations")
        ||  $sgbd->table_exists("#--sml_source_documents")
        ||  $sgbd->table_exists("#--sml_sources_invitations");
      }
      catch(Exception $e){
        return "impossible de savoir si les tables existent deja";
      }
      if($EXISTS){
        return "des tables existent deja en base. installation annulee";
      }
      if(!($version = $env->version("mw_sourceml"))){
        return "impossible de lire la version de sourceml";
      }
      try{
        $sql =
         "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\"";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_authors`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_user` int(11) NOT NULL,"
        ."  `nom` varchar(255) NOT NULL,"
        ."  `image` varchar(255) DEFAULT NULL,"
        ."  `description` text,"
        ."  `email` varchar(255) NOT NULL,"
        ."  `contact_form` tinyint(4) NOT NULL,"
        ."  `captcha` tinyint(4) NOT NULL,"
        ."  PRIMARY KEY (`id`),"
        ."  KEY `id_user` (`id_user`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_classes`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `nom` varchar(255) NOT NULL,"
        ."  PRIMARY KEY (`id`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_licences`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `nom` varchar(255) NOT NULL,"
        ."  `url` varchar(255) NOT NULL,"
        ."  PRIMARY KEY (`id`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_sources`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_class` int(11) NOT NULL,"
        ."  `reference` varchar(255) DEFAULT NULL,"
        ."  `titre` varchar(255) DEFAULT NULL,"
        ."  `licence` int(11) DEFAULT NULL,"
        ."  `date_creation` date DEFAULT NULL,"
        ."  `date_inscription` datetime NOT NULL,"
        ."  PRIMARY KEY (`id`),"
        ."  KEY `id_class` (`id_class`),"
        ."  KEY `licence` (`licence`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_sources_access`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `nom` varchar(255) NOT NULL,"
        ."  `intitule` varchar(255) NOT NULL,"
        ."  PRIMARY KEY (`id`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_sources_authors`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_source` int(11) NOT NULL,"
        ."  `id_author` int(11) NOT NULL,"
        ."  `id_sources_access` int(11) NOT NULL,"
        ."  PRIMARY KEY (`id`),"
        ."  KEY `id_object` (`id_source`),"
        ."  KEY `id_author` (`id_author`),"
        ."  KEY `id_sources_access` (`id_sources_access`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_sources_infos`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_source` int(11) NOT NULL,"
        ."  `key` varchar(255) NOT NULL,"
        ."  `value` text NOT NULL,"
        ."  PRIMARY KEY (`id`),"
        ."  KEY `id_source` (`id_source`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_source_cache`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `url` varchar(255) NOT NULL,"
        ."  `id_source` int(11) NOT NULL,"
        ."  `last_update` datetime NOT NULL,"
        ."  PRIMARY KEY (`id`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_source_compositions`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_source` int(11) NOT NULL,"
        ."  `id_composition` int(11) NOT NULL,"
        ."  PRIMARY KEY (`id`),"
        ."  KEY `id_source` (`id_source`),"
        ."  KEY `id_composition` (`id_composition`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_source_derivations`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_source` int(11) NOT NULL,"
        ."  `derivation` varchar(255) NOT NULL,"
        ."  PRIMARY KEY (`id`),"
        ."  KEY `derivation` (`derivation`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_source_documents`("
        ."  `id` int(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_source` int(11) NOT NULL,"
        ."  `nom` varchar(255) NOT NULL,"
        ."  `url` varchar(255) NOT NULL,"
        ."  PRIMARY KEY (`id`),"
        ."  KEY `id_source` (`id_source`)"
        .") DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE `#--sml_sources_invitations`("
        ."  `id` INT(11) NOT NULL AUTO_INCREMENT,"
        ."  `id_user` INT(11) NOT NULL,"
        ."  `id_source` INT(11) NOT NULL,"
        ."  `id_author` INT(11) NOT NULL,"
        ."  `id_sources_access` INT(11) NOT NULL,"
        ."  `date_invitation` datetime NOT NULL,"
        ."  PRIMARY KEY (`id`)"
        .")";
        $sgbd->query($sql);

      }
      catch(Exception $e){
        return "imposible de creer les tables en base";
      }
      try{
        $sql =
         "INSERT INTO `#--sml_licences` (`id`, `nom`, `url`) VALUES"
        ."(1, 'Creative commons by-sa 2.0', 'http://creativecommons.org/licenses/by-sa/2.0/deed.fr'),"
        ."(2, 'Creative Commons by-nc-nd 2.5', 'http://creativecommons.org/licenses/by-nc-nd/2.5/'),"
        ."(3, 'Creative Commons by-nc-sa 2.5', 'http://creativecommons.org/licenses/by-nc-sa/2.5/'),"
        ."(4, 'Creative Commons by-nc 2.5', 'http://creativecommons.org/licenses/by-nc/2.5/'),"
        ."(5, 'Creative Commons by-nd 2.5', 'http://creativecommons.org/licenses/by-nd/2.5/'),"
        ."(6, 'Creative Commons by-sa 2.5', 'http://creativecommons.org/licenses/by-sa/2.5/'),"
        ."(7, 'Creative Commons by 2.5', 'http://creativecommons.org/licenses/by/2.5/'),"
        ."(8, 'Licence Art Libre', 'http://artlibre.org/licence/lal/'),"
        ."(9, 'Licence C Reaction', 'http://morne.free.fr/Necktar7/creactionfr.htm'),"
        ."(10, 'Yellow OpenMusic License', 'http://openmusic.linuxtag.org/yellow.html'),"
        ."(11, 'Green OpenMusic License', 'http://openmusic.linuxtag.org/green.html')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_classes` (`id`, `nom`) VALUES"
        ."(1, 'album'),"
        ."(2, 'morceau'),"
        ."(3, 'piste')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_sources_access` (`id`, `nom`, `intitule`) VALUES"
        ."(1, 'admin', 'administrateur'),"
        ."(2, 'editeur', 'éditeur'),"
        ."(3, 'contributeur', 'contributeur')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--versions` (`application`, `version`) VALUES"
        ."('mw_sourceml', ".$this->eq($version).")";
        $sgbd->query($sql);

      }
      catch(Exception $e){
        return "les tables ont ete ajoutees en base mais impossible d'y enregistrer les valeurs par defaut.";
      }
      return true;
    }

    public function mw_sourceml_install_sqlite($plugin, $env){
      $sgbd = $this->sgbd();
      try{
        $EXISTS =
            $sgbd->table_exists("#--sml_authors")
        ||  $sgbd->table_exists("#--sml_classes")
        ||  $sgbd->table_exists("#--sml_licences")
        ||  $sgbd->table_exists("#--sml_sources")
        ||  $sgbd->table_exists("#--sml_sources_access")
        ||  $sgbd->table_exists("#--sml_sources_authors")
        ||  $sgbd->table_exists("#--sml_sources_infos")
        ||  $sgbd->table_exists("#--sml_source_cache")
        ||  $sgbd->table_exists("#--sml_source_compositions")
        ||  $sgbd->table_exists("#--sml_source_derivations")
        ||  $sgbd->table_exists("#--sml_source_documents")
        ||  $sgbd->table_exists("#--sml_sources_invitations");
      }
      catch(Exception $e){
        return "impossible de savoir si les tables existent deja";
      }
      if($EXISTS){
        return "des tables existent deja en base. installation annulee";
      }
      if(!($version = $env->version("mw_sourceml"))){
        return "impossible de lire la version de sourceml";
      }
      try{

        $sql =
         "CREATE TABLE #--sml_authors("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_user` INTEGER NOT NULL,"
        ."  `nom` TEXT NOT NULL,"
        ."  `image` TEXT DEFAULT NULL,"
        ."  `description` TEXT,"
        ."  `email` TEXT NOT NULL,"
        ."  `contact_form` INTEGER NOT NULL,"
        ."  `captcha` INTEGER NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_classes("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `nom` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_licences("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `nom` TEXT NOT NULL,"
        ."  `url` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_sources("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_class` INTEGER NOT NULL,"
        ."  `reference` TEXT DEFAULT NULL,"
        ."  `titre` TEXT DEFAULT NULL,"
        ."  `licence` INTEGER DEFAULT NULL,"
        ."  `date_creation` TEXT DEFAULT NULL,"
        ."  `date_inscription` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_sources_access("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `nom` TEXT NOT NULL,"
        ."  `intitule` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_sources_authors("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_source` INTEGER NOT NULL,"
        ."  `id_author` INTEGER NOT NULL,"
        ."  `id_sources_access` INTEGER NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_sources_infos("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_source` INTEGER NOT NULL,"
        ."  `key` TEXT NOT NULL,"
        ."  `value` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_source_cache("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `url` TEXT NOT NULL,"
        ."  `id_source` INTEGER NOT NULL,"
        ."  `last_update` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_source_compositions("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_source` INTEGER NOT NULL,"
        ."  `id_composition` INTEGER NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_source_derivations("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_source` INTEGER NOT NULL,"
        ."  `derivation` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_source_documents("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_source` INTEGER NOT NULL,"
        ."  `nom` TEXT NOT NULL,"
        ."  `url` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

        $sql =
         "CREATE TABLE #--sml_sources_invitations("
        ."  `id` INTEGER PRIMARY KEY AUTOINCREMENT,"
        ."  `id_user` INTEGER NOT NULL,"
        ."  `id_source` INTEGER NOT NULL,"
        ."  `id_author` INTEGER NOT NULL,"
        ."  `id_sources_access` INTEGER NOT NULL,"
        ."  `date_invitation` TEXT NOT NULL"
        .")";
        $sgbd->query($sql);

      }
      catch(Exception $e){
        return "imposible de creer les tables en base";
      }
      try{
        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES(1, 'Creative commons by-sa 2.0', 'http://creativecommons.org/licenses/by-sa/2.0/deed.fr')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (2, 'Creative Commons by-nc-nd 2.5', 'http://creativecommons.org/licenses/by-nc-nd/2.5/')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (3, 'Creative Commons by-nc-sa 2.5', 'http://creativecommons.org/licenses/by-nc-sa/2.5/')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (4, 'Creative Commons by-nc 2.5', 'http://creativecommons.org/licenses/by-nc/2.5/')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (5, 'Creative Commons by-nd 2.5', 'http://creativecommons.org/licenses/by-nd/2.5/')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (6, 'Creative Commons by-sa 2.5', 'http://creativecommons.org/licenses/by-sa/2.5/')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (7, 'Creative Commons by 2.5', 'http://creativecommons.org/licenses/by/2.5/')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (8, 'Licence Art Libre', 'http://artlibre.org/licence/lal/')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (9, 'Licence C Reaction', 'http://morne.free.fr/Necktar7/creactionfr.htm')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (10, 'Yellow OpenMusic License', 'http://openmusic.linuxtag.org/yellow.html')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO #--sml_licences(`id`, `nom`, `url`)"
        ." VALUES (11, 'Green OpenMusic License', 'http://openmusic.linuxtag.org/green.html')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_classes` (`id`, `nom`) VALUES (1, 'album')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_classes` (`id`, `nom`) VALUES (2, 'morceau')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_classes` (`id`, `nom`) VALUES (3, 'piste')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_sources_access` (`id`, `nom`, `intitule`) VALUES (1, 'admin', 'administrateur')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_sources_access` (`id`, `nom`, `intitule`) VALUES (2, 'editeur', 'éditeur')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--sml_sources_access` (`id`, `nom`, `intitule`) VALUES (3, 'contributeur', 'contributeur')";
        $sgbd->query($sql);

        $sql =
         "INSERT INTO `#--versions` (`application`, `version`) VALUES ('mw_sourceml', ".$this->eq($version).")";
        $sgbd->query($sql);

      }
      catch(Exception $e){
        return "les tables ont ete ajoutees en base mais impossible d'y enregistrer les valeurs par defaut.";
      }
      return true;
    }

    // ---------------------------------------------------------------------------------
    //                                                                         uninstall
    //

    public function mw_sourceml_uninstall_sql($plugin, $env){
      $sgbd = $this->sgbd();
      if(!$plugin->disable($env)) return "impossible de desactiver le plugin";
      if(!($version = $env->version("mw_sourceml"))){
        return "impossible de lire la version de sourceml";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_authors");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_authors";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_classes");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_classes";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_licences");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_licences";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_sources");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_sources";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_sources_access");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_sources_access";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_sources_authors");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_sources_authors";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_sources_infos");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_sources_infos";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_source_cache");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_source_cache";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_source_compositions");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_source_compositions";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_source_derivations");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_source_derivations";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_source_documents");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_source_documents";
      }
      try{
        $sgbd->query("DROP TABLE #--sml_sources_invitations");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_sources_invitations";
      }
      try{
        $sgbd->query("DELETE FROM #--versions WHERE application='mw_sourceml'");
      }
      catch(Exception $e){
        return "impossible de supprimer la table #--sml_sources_invitations";
      }
      return true;
    }

  }