From mboxrd@z Thu Jan 1 00:00:00 1970 Received: from pigeon.gentoo.org ([208.92.234.80] helo=lists.gentoo.org) by finch.gentoo.org with esmtp (Exim 4.60) (envelope-from ) id 1QU1cP-0005QK-TT for garchives@archives.gentoo.org; Tue, 07 Jun 2011 19:07:14 +0000 Received: from pigeon.gentoo.org (localhost [127.0.0.1]) by pigeon.gentoo.org (Postfix) with SMTP id 1444E1C0E0; Tue, 7 Jun 2011 19:07:06 +0000 (UTC) Received: from smtp.gentoo.org (smtp.gentoo.org [140.211.166.183]) by pigeon.gentoo.org (Postfix) with ESMTP id C88341C0E0 for ; Tue, 7 Jun 2011 19:07:05 +0000 (UTC) Received: from pelican.gentoo.org (unknown [66.219.59.40]) (using TLSv1 with cipher ADH-AES256-SHA (256/256 bits)) (No client certificate requested) by smtp.gentoo.org (Postfix) with ESMTPS id 2BF531B401C for ; Tue, 7 Jun 2011 19:07:05 +0000 (UTC) Received: from localhost.localdomain (localhost [127.0.0.1]) by pelican.gentoo.org (Postfix) with ESMTP id 9451A8003F for ; Tue, 7 Jun 2011 19:07:04 +0000 (UTC) From: "Vikraman Choudhury" To: gentoo-commits@lists.gentoo.org Content-type: text/plain; charset=UTF-8 Reply-To: gentoo-dev@lists.gentoo.org, "Vikraman Choudhury" Message-ID: Subject: [gentoo-commits] proj/gentoostats:master commit in: server/sql/ X-VCS-Repository: proj/gentoostats X-VCS-Files: server/sql/init.sql X-VCS-Directories: server/sql/ X-VCS-Committer: vikraman X-VCS-Committer-Name: Vikraman Choudhury X-VCS-Revision: e98abe0a7a2b012c08d728e68900384052f453d7 Date: Tue, 7 Jun 2011 19:07:04 +0000 (UTC) Precedence: bulk List-Post: List-Help: List-Unsubscribe: List-Subscribe: List-Id: Gentoo Linux mail X-BeenThere: gentoo-commits@lists.gentoo.org Content-Transfer-Encoding: quoted-printable X-Archives-Salt: X-Archives-Hash: 83969b14418aead877dca330f16ab832 commit: e98abe0a7a2b012c08d728e68900384052f453d7 Author: Vikraman Choudhury gmail com> AuthorDate: Tue Jun 7 19:06:44 2011 +0000 Commit: Vikraman Choudhury gmail com> CommitDate: Tue Jun 7 19:06:44 2011 +0000 URL: http://git.overlays.gentoo.org/gitweb/?p=3Dproj/gentoostats.g= it;a=3Dcommit;h=3De98abe0a 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 @@ =20 -- run as 'gentoo'@'localhost' identified by 'gentoo' -use gentoostats; +use `gentoostats`; =20 -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`) ); =20 -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`) ); =20 -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`) ); =20 -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`) );