tests/mysql_06.sql

   1 DROP TABLE IF EXISTS adherents;
   2 CREATE TABLE adherents (
   3   id_adh int(10) unsigned NOT NULL auto_increment,
   4   id_statut int(10) unsigned NOT NULL default '4',
   5   nom_adh varchar(20) NOT NULL default '',
   6   prenom_adh varchar(20) default NULL,
   7   pseudo_adh varchar(20) default NULL,
   8   titre_adh tinyint(3) unsigned NOT NULL default '0',
   9   ddn_adh date default NULL,
  10   adresse_adh varchar(150) NOT NULL default '',
  11   adresse2_adh varchar(150) default NULL,
  12   cp_adh varchar(10) NOT NULL default '',
  13   ville_adh varchar(50) NOT NULL default '',
  14   pays_adh varchar(50) default NULL,
  15   tel_adh varchar(20) default NULL,
  16   gsm_adh varchar(20) default NULL,
  17   email_adh varchar(150) default NULL,
  18   url_adh varchar(200) default NULL,
  19   icq_adh varchar(20) default NULL,
  20   msn_adh varchar(150) default NULL,
  21   jabber_adh varchar(150) default NULL,
  22   info_adh text,
  23   info_public_adh text,
  24   prof_adh varchar(150) default NULL,
  25   login_adh varchar(20) NOT NULL default '',
  26   mdp_adh varchar(20) NOT NULL default '',
  27   date_crea_adh date NOT NULL default '1901-01-01',
  28   activite_adh enum('0','1') NOT NULL default '0',
  29   bool_admin_adh enum('1') default NULL,
  30   bool_exempt_adh enum('1') default NULL,
  31   bool_display_info enum('1') default NULL,
  32   date_echeance date default NULL,
  33   PRIMARY KEY  (id_adh)
  34 ) ENGINE=MyISAM;
  35
  36 DROP TABLE IF EXISTS cotisations;
  37 CREATE TABLE cotisations (
  38   id_cotis int(10) unsigned NOT NULL auto_increment,
  39   id_adh int(10) unsigned NOT NULL default '0',
  40   id_type_cotis int(10) unsigned NOT NULL default '0',
  41   montant_cotis float unsigned default '0',
  42   info_cotis text,
  43   duree_mois_cotis tinyint(3) unsigned NOT NULL default '12',
  44   date_cotis date NOT NULL default '1901-01-01',
  45   PRIMARY KEY  (id_cotis)
  46 ) ENGINE=MyISAM;
  47
  48 DROP TABLE IF EXISTS statuts;
  49 CREATE TABLE statuts (
  50   id_statut int(10) unsigned NOT NULL auto_increment,
  51   libelle_statut varchar(20) NOT NULL default '',
  52   priorite_statut tinyint(4) NOT NULL default '0',
  53   PRIMARY KEY  (id_statut)
  54 ) ENGINE=MyISAM;
  55
  56
  57 INSERT INTO statuts VALUES (1,'President',0);
  58 INSERT INTO statuts VALUES (10,'Vice-president',5);
  59 INSERT INTO statuts VALUES (2,'Tresorier',10);
  60 INSERT INTO statuts VALUES (4,'Membre actif',30);
  61 INSERT INTO statuts VALUES (5,'Membre bienfaiteur',40);
  62 INSERT INTO statuts VALUES (6,'Membre fondateur',50);
  63 INSERT INTO statuts VALUES (3,'Secretaire',20);
  64 INSERT INTO statuts VALUES (7,'Ancien',60);
  65 INSERT INTO statuts VALUES (8,'Personne morale',70);
  66 INSERT INTO statuts VALUES (9,'Non membre',80);
  67
  68 DROP TABLE IF EXISTS types_cotisation;
  69 CREATE TABLE types_cotisation (
  70   id_type_cotis int(10) unsigned NOT NULL auto_increment,
  71   libelle_type_cotis varchar(30) NOT NULL default '',
  72   PRIMARY KEY  (id_type_cotis)
  73 ) ENGINE=MyISAM;
  74
  75
  76 INSERT INTO types_cotisation VALUES (1,'Cotisation annuelle normale');
  77 INSERT INTO types_cotisation VALUES (2,'Cotisation annuelle reduite');
  78 INSERT INTO types_cotisation VALUES (3,'Cotisation entreprise');
  79 INSERT INTO types_cotisation VALUES (4,'Donation en nature');
  80 INSERT INTO types_cotisation VALUES (5,'Donation pecuniere');
  81 INSERT INTO types_cotisation VALUES (6,'Partenariat');
  82
  83 DROP TABLE IF EXISTS preferences;
  84 CREATE TABLE preferences (
  85   pref_nom varchar(40) NOT NULL default '',
  86   pref_adresse varchar(150) NOT NULL default '',
  87   pref_adresse2 varchar(150) default NULL,
  88   pref_cp varchar(10) NOT NULL default '',
  89   pref_ville varchar(50) NOT NULL default '',
  90   pref_pays varchar(50) default NULL,
  91   pref_lang varchar(20) NOT NULL default '',
  92   pref_numrows int(10) unsigned NOT NULL default '30',
  93   pref_log enum('0','1','2') NOT NULL default '1',
  94   pref_email_nom varchar(20) NOT NULL default '',
  95   pref_email varchar(150) NOT NULL default '',
  96   pref_etiq_marges int(10) unsigned NOT NULL default '0',
  97   pref_etiq_hspace int(10) unsigned NOT NULL default '0',
  98   pref_etiq_vspace int(10) unsigned NOT NULL default '0',
  99   pref_etiq_hsize int(10) unsigned NOT NULL default '0',
 100   pref_etiq_vsize int(10) unsigned NOT NULL default '0',
 101   pref_etiq_cols int(10) unsigned NOT NULL default '0',
 102   pref_etiq_rows int(10) unsigned NOT NULL default '0',
 103   pref_etiq_corps int(10) unsigned NOT NULL default '0',
 104   pref_admin_login varchar(20) NOT NULL default '',
 105   pref_admin_pass varchar(20) NOT NULL default ''
 106 ) ENGINE=MyISAM;
 107
 108 DROP TABLE IF EXISTS logs;
 109 CREATE TABLE logs (
 110   id_log int(10) unsigned NOT NULL auto_increment,
 111   date_log datetime NOT NULL,
 112   ip_log varchar(30) NOT NULL default '',
 113   adh_log varchar(41) NOT NULL default '',
 114   text_log text,
 115   PRIMARY KEY  (id_log)
 116 ) ENGINE=MyISAM;
 117