[gentoo-commits] proj/gentoostats:master commit in: server/sql/

["Vikraman Choudhury" <vikraman.choudhury@gmail.com>]

commit:     e98abe0a7a2b012c08d728e68900384052f453d7
Author:     Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
AuthorDate: Tue Jun  7 19:06:44 2011 +0000
Commit:     Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
CommitDate: Tue Jun  7 19:06:44 2011 +0000
URL:        http://git.overlays.gentoo.org/gitweb/?p=proj/gentoostats.git;a=commit;h=e98abe0a

updated and fixed sql code

---
 server/sql/init.sql |  161 +++++++++++++++++++++++++++++++++++++++++++--------
 1 files changed, 137 insertions(+), 24 deletions(-)

diff --git a/server/sql/init.sql b/server/sql/init.sql
index c0588cd..c0c2f96 100644
--- a/server/sql/init.sql
+++ b/server/sql/init.sql
@@ -1,34 +1,147 @@
 
 -- run as 'gentoo'@'localhost' identified by 'gentoo'
-use gentoostats;
+use `gentoostats`;
 
-drop table if exists hosts;
-create table hosts (
-  uuid varchar (40) primary key,
-  passwd varchar (40) not null
+drop table if exists `hosts`;
+create table `hosts` (
+  `uuid` binary (16),
+  `passwd` varchar (32) not null,
+  primary key (`uuid`)
 );
 
-drop table if exists env;
-create table env (
-  uuid varchar (40) references hosts.uuid,
-  var varchar (20) not null,
-  value varchar (512),
-  primary key (uuid, var)
+drop table if exists `env`;
+create table `env` (
+  `uuid` binary (16) references hosts.uuid,
+  `platform` varchar (128),
+  `arch` varchar (16),
+  `chost` varchar (32),
+  `cflags` varchar (64),
+  `cxxflags` varchar (64),
+  `fflags` varchar (64),
+  `ldflags` varchar (64),
+  `makeopts` varchar (8),
+  `lastsync` timestamp,
+  `profile` varchar (64),
+  `sync` varchar (128),
+  primary key (`uuid`)
 );
 
-drop table if exists packages;
-create table packages (
-  cat varchar (40) not null,
-  pkg varchar (40) not null,
-  ver varchar (40) not null,
-  pkey serial,
-  primary key (cat, pkg, ver, pkey)
+drop table if exists `keywords`;
+create table `keywords` (
+  `kwkey` serial,
+  `keyword` varchar (16),
+  primary key (`keyword`)
 );
 
-drop table if exists useflags;
-create table useflags (
-  uuid varchar (40) references host.uuid,
-  useflag varchar (40) not null,
-  pkey bigint unsigned references packages.pkey,
-  primary key (uuid, useflag, pkey)
+drop table if exists `global_keywords`;
+create table `global_keywords` (
+  `uuid` binary (16) references hosts.uuid,
+  `kwkey` bigint unsigned references keywords.kwkey,
+  primary key (`uuid`, `kwkey`)
+);
+
+drop table if exists `lang`;
+create table `lang` (
+  `lkey` serial,
+  `lang` varchar (16),
+  primary key (`lang`)
+);
+
+drop table if exists `host_lang`;
+create table `host_lang` (
+  `uuid` binary (16) references hosts.uuid,
+  `lkey` bigint unsigned references lang.lkey,
+  primary key (`uuid`, `lkey`)
+);
+
+drop table if exists `features`;
+create table `features` (
+  `fkey` serial,
+  `feature` varchar (64),
+  primary key (`feature`)
+);
+
+drop table if exists `host_features`;
+create table `host_features` (
+  `uuid` binary (16) references hosts.uuid,
+  `fkey` bigint unsigned references features.fkey,
+  primary key (`uuid`, `fkey`)
+);
+
+drop table if exists `gentoo_mirrors`;
+create table `gentoo_mirrors` (
+  `mkey` serial,
+  `mirror` varchar (128),
+  primary key (`mirror`)
+);
+
+drop table if exists `host_mirrors`;
+create table `host_mirrors` (
+  `uuid` binary (16) references hosts.uuid,
+  `mkey` bigint unsigned references gentoo_mirrors.mkey,
+  primary key (`uuid`, `mkey`)
+);
+
+drop table if exists `packages`;
+create table `packages` (
+  `pkey` serial,
+  `cat` varchar (32),
+  `pkg` varchar (64),
+  `ver` varchar (32),
+  primary key (`cat`, `pkg`, `ver`)
+);
+
+drop table if exists `repositories`;
+create table `repositories` (
+  `rkey` serial,
+  `repo` varchar (32),
+  primary key (`repo`)
+);
+
+drop table if exists `installed_packages`;
+create table `installed_packages` (
+  `ipkey` serial,
+  `uuid` binary (16) references hosts.uuid,
+  `pkey` bigint unsigned references packages.pkey,
+  `build_time` timestamp,
+  `counter` bigint unsigned,
+  `kwkey` bigint unsigned references keywords.kwkey,
+  `rkey` varchar (64) references repositories.rkey,
+  `size` bigint unsigned,
+  primary key (`uuid`, `pkey`)
+);
+
+drop table if exists `useflags`;
+create table `useflags` (
+  `ukey` serial,
+  `useflag` varchar (64),
+  primary key (`useflag`)
+);
+
+drop table if exists `global_useflags`;
+create table `global_useflags` (
+  `uuid` binary (16) references hosts.uuid,
+  `ukey` bigint unsigned references useflags.ukey,
+  primary key (`uuid`, `ukey`)
+);
+
+drop table if exists `plus_useflags`;
+create table `plus_useflags` (
+  `ipkey` bigint unsigned references installed_packages.ipkey,
+  `ukey` bigint unsigned references useflags.ukey,
+  primary key (`ipkey`, `ukey`)
+);
+
+drop table if exists `minus_useflags`;
+create table `minus_useflags` (
+  `ipkey` bigint unsigned references installed_packages.ipkey,
+  `ukey` bigint unsigned references useflags.ukey,
+  primary key (`ipkey`, `ukey`)
+);
+
+drop table if exists `unset_useflags`;
+create table `unset_useflags` (
+  `ipkey` bigint unsigned references installed_packages.ipkey,
+  `ukey` bigint unsigned references useflags.ukey,
+  primary key (`ipkey`, `ukey`)
 );