* [gentoo-commits] proj/gentoostats:master commit in: server/sql/
@ 2012-02-05 12:15 Vikraman Choudhury
0 siblings, 0 replies; 5+ messages in thread
From: Vikraman Choudhury @ 2012-02-05 12:15 UTC (permalink / raw
To: gentoo-commits
commit: e8b0447ea39afdf07a5acd1d065a16971fed65d7
Author: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
AuthorDate: Sun Feb 5 12:14:40 2012 +0000
Commit: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
CommitDate: Sun Feb 5 12:14:40 2012 +0000
URL: http://git.overlays.gentoo.org/gitweb/?p=proj/gentoostats.git;a=commit;h=e8b0447e
update CAT field length to handle longer category values
---
server/sql/init.sql | 2 +-
1 files changed, 1 insertions(+), 1 deletions(-)
diff --git a/server/sql/init.sql b/server/sql/init.sql
index 5d7d6ab..cd77433 100644
--- a/server/sql/init.sql
+++ b/server/sql/init.sql
@@ -111,7 +111,7 @@ create table `HOST_MIRRORS` (
create table `PACKAGES` (
`PKEY` serial,
- `CAT` varchar (32),
+ `CAT` varchar (64),
`PKG` varchar (64),
`VER` varchar (32),
primary key (`CAT`, `PKG`, `VER`)
^ permalink raw reply related [flat|nested] 5+ messages in thread
* [gentoo-commits] proj/gentoostats:master commit in: server/sql/
@ 2011-06-14 17:43 Vikraman Choudhury
0 siblings, 0 replies; 5+ messages in thread
From: Vikraman Choudhury @ 2011-06-14 17:43 UTC (permalink / raw
To: gentoo-commits
commit: 0ea546a65caf6ff49b5756a99a8c3c21ac564bc2
Author: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
AuthorDate: Tue Jun 14 17:41:44 2011 +0000
Commit: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
CommitDate: Tue Jun 14 17:41:44 2011 +0000
URL: http://git.overlays.gentoo.org/gitweb/?p=proj/gentoostats.git;a=commit;h=0ea546a6
capitalize sql objects
---
server/sql/init.sql | 264 +++++++++++++++++++++++++-------------------------
1 files changed, 132 insertions(+), 132 deletions(-)
diff --git a/server/sql/init.sql b/server/sql/init.sql
index f177ee9..70b1ba7 100644
--- a/server/sql/init.sql
+++ b/server/sql/init.sql
@@ -1,190 +1,190 @@
--- run as 'gentoo'@'localhost' identified by 'gentoo'
+-- run as 'gentoo'@'localHOST' identified by 'gentoo'
use `gentoostats`;
-drop table if exists `env`;
-drop table if exists `global_keywords`;
-drop table if exists `host_lang`;
-drop table if exists `host_features`;
-drop table if exists `host_mirrors`;
-drop table if exists `global_useflags`;
-drop table if exists `plus_useflags`;
-drop table if exists `minus_useflags`;
-drop table if exists `unset_useflags`;
-drop table if exists `installed_packages`;
-drop table if exists `lang`;
-drop table if exists `features`;
-drop table if exists `gentoo_mirrors`;
-drop table if exists `useflags`;
-drop table if exists `keywords`;
-drop table if exists `packages`;
-drop table if exists `repositories`;
-drop table if exists `hosts`;
-
-create table `hosts` (
- `uuid` binary (16),
- `passwd` varchar (32) not null,
- primary key (`uuid`)
-) engine=innodb;
-
-create table `env` (
- `uuid` binary (16),
- `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 null default null,
- `profile` varchar (64),
- `sync` varchar (128),
- primary key (`uuid`),
- foreign key (`uuid`) references `hosts`(`uuid`)
+drop table if exists `ENV`;
+drop table if exists `GLOBAL_KEYWORDS`;
+drop table if exists `HOST_LANG`;
+drop table if exists `HOST_FEATURES`;
+drop table if exists `HOST_MIRRORS`;
+drop table if exists `GLOBAL_USEFLAGS`;
+drop table if exists `PLUS_USEFLAGS`;
+drop table if exists `MINUS_USEFLAGS`;
+drop table if exists `UNSET_USEFLAGS`;
+drop table if exists `INSTALLED_PACKAGES`;
+drop table if exists `LANG`;
+drop table if exists `FEATURES`;
+drop table if exists `GENTOO_MIRRORS`;
+drop table if exists `USEFLAGS`;
+drop table if exists `KEYWORDS`;
+drop table if exists `PACKAGES`;
+drop table if exists `REPOSITORIES`;
+drop table if exists `HOSTS`;
+
+create table `HOSTS` (
+ `UUID` binary (16),
+ `PASSWD` varchar (32) not null,
+ primary key (`UUID`)
+) engine=innodb;
+
+create table `ENV` (
+ `UUID` binary (16),
+ `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 null default null,
+ `PROFILE` varchar (64),
+ `SYNC` varchar (128),
+ primary key (`UUID`),
+ foreign key (`UUID`) references `HOSTS`(`UUID`)
on delete cascade on update cascade
) engine=innodb;
-create table `keywords` (
- `kwkey` serial,
- `keyword` varchar (16),
- primary key (`keyword`)
+create table `KEYWORDS` (
+ `KWKEY` serial,
+ `KEYWORD` varchar (16),
+ primary key (`KEYWORD`)
) engine=innodb;
-create table `global_keywords` (
- `uuid` binary (16),
- `kwkey` bigint unsigned,
- primary key (`uuid`, `kwkey`),
- foreign key (`uuid`) references `hosts`(`uuid`)
+create table `GLOBAL_KEYWORDS` (
+ `UUID` binary (16),
+ `KWKEY` bigint unsigned,
+ primary key (`UUID`, `KWKEY`),
+ foreign key (`UUID`) references `HOSTS`(`UUID`)
on delete cascade on update cascade,
- foreign key (`kwkey`) references `keywords`(`kwkey`)
+ foreign key (`KWKEY`) references `KEYWORDS`(`KWKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `lang` (
- `lkey` serial,
- `lang` varchar (16),
- primary key (`lang`)
+create table `LANG` (
+ `LKEY` serial,
+ `LANG` varchar (16),
+ primary key (`LANG`)
) engine=innodb;
-create table `host_lang` (
- `uuid` binary (16),
- `lkey` bigint unsigned,
- primary key (`uuid`, `lkey`),
- foreign key (`uuid`) references `hosts`(`uuid`)
+create table `HOST_LANG` (
+ `UUID` binary (16),
+ `LKEY` bigint unsigned,
+ primary key (`UUID`, `LKEY`),
+ foreign key (`UUID`) references `HOSTS`(`UUID`)
on delete cascade on update cascade,
- foreign key (`lkey`) references `lang`(`lkey`)
+ foreign key (`LKEY`) references `LANG`(`LKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `features` (
- `fkey` serial,
- `feature` varchar (64),
- primary key (`feature`)
+create table `FEATURES` (
+ `FKEY` serial,
+ `FEATURE` varchar (64),
+ primary key (`FEATURE`)
) engine=innodb;
-create table `host_features` (
- `uuid` binary (16),
- `fkey` bigint unsigned,
- primary key (`uuid`, `fkey`),
- foreign key (`uuid`) references `hosts`(`uuid`)
+create table `HOST_FEATURES` (
+ `UUID` binary (16),
+ `FKEY` bigint unsigned,
+ primary key (`UUID`, `FKEY`),
+ foreign key (`UUID`) references `HOSTS`(`UUID`)
on delete cascade on update cascade,
- foreign key (`fkey`) references `features`(`fkey`)
+ foreign key (`FKEY`) references `FEATURES`(`FKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `gentoo_mirrors` (
- `mkey` serial,
- `mirror` varchar (128),
- primary key (`mirror`)
+create table `GENTOO_MIRRORS` (
+ `MKEY` serial,
+ `MIRROR` varchar (128),
+ primary key (`MIRROR`)
) engine=innodb;
-create table `host_mirrors` (
- `uuid` binary (16),
- `mkey` bigint unsigned,
- primary key (`uuid`, `mkey`),
- foreign key (`uuid`) references `hosts`(`uuid`)
+create table `HOST_MIRRORS` (
+ `UUID` binary (16),
+ `MKEY` bigint unsigned,
+ primary key (`UUID`, `MKEY`),
+ foreign key (`UUID`) references `HOSTS`(`UUID`)
on delete cascade on update cascade,
- foreign key (`mkey`) references `gentoo_mirrors`(`mkey`)
+ foreign key (`MKEY`) references `GENTOO_MIRRORS`(`MKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `packages` (
- `pkey` serial,
- `cat` varchar (32),
- `pkg` varchar (64),
- `ver` varchar (32),
- primary key (`cat`, `pkg`, `ver`)
+create table `PACKAGES` (
+ `PKEY` serial,
+ `CAT` varchar (32),
+ `PKG` varchar (64),
+ `VER` varchar (32),
+ primary key (`CAT`, `PKG`, `VER`)
) engine=innodb;
-create table `repositories` (
- `rkey` serial,
- `repo` varchar (32),
- primary key (`repo`)
+create table `REPOSITORIES` (
+ `RKEY` serial,
+ `REPO` varchar (32),
+ primary key (`REPO`)
) engine=innodb;
-create table `installed_packages` (
- `ipkey` serial,
- `uuid` binary (16),
- `pkey` bigint unsigned,
+create table `INSTALLED_PACKAGES` (
+ `IPKEY` serial,
+ `UUID` binary (16),
+ `PKEY` bigint unsigned,
`build_time` timestamp null default null,
- `counter` bigint unsigned,
- `kwkey` bigint unsigned,
- `rkey` bigint unsigned,
- `size` bigint unsigned,
- primary key (`uuid`, `pkey`),
- foreign key (`uuid`) references `hosts`(`uuid`)
+ `COUNTER` bigint unsigned,
+ `KWKEY` bigint unsigned,
+ `RKEY` bigint unsigned,
+ `SIZE` bigint unsigned,
+ primary key (`UUID`, `PKEY`),
+ foreign key (`UUID`) references `HOSTS`(`UUID`)
on delete cascade on update cascade,
- foreign key (`pkey`) references `packages`(`pkey`)
+ foreign key (`PKEY`) references `PACKAGES`(`PKEY`)
on delete cascade on update cascade,
- foreign key (`kwkey`) references `keywords`(`kwkey`)
+ foreign key (`KWKEY`) references `KEYWORDS`(`KWKEY`)
on delete cascade on update cascade,
- foreign key (`rkey`) references `repositories`(`rkey`)
+ foreign key (`RKEY`) references `REPOSITORIES`(`RKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `useflags` (
- `ukey` serial,
- `useflag` varchar (64),
- primary key (`useflag`)
+create table `USEFLAGS` (
+ `UKEY` serial,
+ `USEFLAG` varchar (64),
+ primary key (`USEFLAG`)
) engine=innodb;
-create table `global_useflags` (
- `uuid` binary (16),
- `ukey` bigint unsigned,
- primary key (`uuid`, `ukey`),
- foreign key (`uuid`) references `hosts`(`uuid`)
+create table `GLOBAL_USEFLAGS` (
+ `UUID` binary (16),
+ `UKEY` bigint unsigned,
+ primary key (`UUID`, `UKEY`),
+ foreign key (`UUID`) references `HOSTS`(`UUID`)
on delete cascade on update cascade,
- foreign key (`ukey`) references `useflags`(`ukey`)
+ foreign key (`UKEY`) references `USEFLAGS`(`UKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `plus_useflags` (
- `ipkey` bigint unsigned,
- `ukey` bigint unsigned,
- primary key (`ipkey`, `ukey`),
- foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+create table `PLUS_USEFLAGS` (
+ `IPKEY` bigint unsigned,
+ `UKEY` bigint unsigned,
+ primary key (`IPKEY`, `UKEY`),
+ foreign key (`IPKEY`) references `INSTALLED_PACKAGES`(`IPKEY`)
on delete cascade on update cascade,
- foreign key (`ukey`) references `useflags`(`ukey`)
+ foreign key (`UKEY`) references `USEFLAGS`(`UKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `minus_useflags` (
- `ipkey` bigint unsigned,
- `ukey` bigint unsigned,
- primary key (`ipkey`, `ukey`),
- foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+create table `MINUS_USEFLAGS` (
+ `IPKEY` bigint unsigned,
+ `UKEY` bigint unsigned,
+ primary key (`IPKEY`, `UKEY`),
+ foreign key (`IPKEY`) references `INSTALLED_PACKAGES`(`IPKEY`)
on delete cascade on update cascade,
- foreign key (`ukey`) references `useflags`(`ukey`)
+ foreign key (`UKEY`) references `USEFLAGS`(`UKEY`)
on delete cascade on update cascade
) engine=innodb;
-create table `unset_useflags` (
- `ipkey` bigint unsigned,
- `ukey` bigint unsigned,
- primary key (`ipkey`, `ukey`),
- foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+create table `UNSET_USEFLAGS` (
+ `IPKEY` bigint unsigned,
+ `UKEY` bigint unsigned,
+ primary key (`IPKEY`, `UKEY`),
+ foreign key (`IPKEY`) references `INSTALLED_PACKAGES`(`IPKEY`)
on delete cascade on update cascade,
- foreign key (`ukey`) references `useflags`(`ukey`)
+ foreign key (`UKEY`) references `USEFLAGS`(`UKEY`)
on delete cascade on update cascade
) engine=innodb;
^ permalink raw reply related [flat|nested] 5+ messages in thread
* [gentoo-commits] proj/gentoostats:master commit in: server/sql/
@ 2011-06-07 23:58 Vikraman Choudhury
0 siblings, 0 replies; 5+ messages in thread
From: Vikraman Choudhury @ 2011-06-07 23:58 UTC (permalink / raw
To: gentoo-commits
commit: 0e945b10b562afa03c3e8170bba0f12ba03d4584
Author: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
AuthorDate: Tue Jun 7 19:44:23 2011 +0000
Commit: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
CommitDate: Tue Jun 7 19:44:23 2011 +0000
URL: http://git.overlays.gentoo.org/gitweb/?p=proj/gentoostats.git;a=commit;h=0e945b10
fix foreign key constraints
---
server/sql/init.sql | 108 +++++++++++++++++++++++++++++++++++---------------
1 files changed, 75 insertions(+), 33 deletions(-)
diff --git a/server/sql/init.sql b/server/sql/init.sql
index c0c2f96..72bf716 100644
--- a/server/sql/init.sql
+++ b/server/sql/init.sql
@@ -11,7 +11,7 @@ create table `hosts` (
drop table if exists `env`;
create table `env` (
- `uuid` binary (16) references hosts.uuid,
+ `uuid` binary (16),
`platform` varchar (128),
`arch` varchar (16),
`chost` varchar (32),
@@ -20,10 +20,12 @@ create table `env` (
`fflags` varchar (64),
`ldflags` varchar (64),
`makeopts` varchar (8),
- `lastsync` timestamp,
+ `lastsync` timestamp null default null,
`profile` varchar (64),
`sync` varchar (128),
- primary key (`uuid`)
+ primary key (`uuid`),
+ foreign key (`uuid`) references `hosts`(`uuid`)
+ on delete cascade on update cascade
);
drop table if exists `keywords`;
@@ -35,9 +37,13 @@ create table `keywords` (
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`)
+ `uuid` binary (16),
+ `kwkey` bigint unsigned,
+ primary key (`uuid`, `kwkey`),
+ foreign key (`uuid`) references `hosts`(`uuid`)
+ on delete cascade on update cascade,
+ foreign key (`kwkey`) references `keywords`(`kwkey`)
+ on delete cascade on update cascade
);
drop table if exists `lang`;
@@ -49,9 +55,13 @@ create table `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`)
+ `uuid` binary (16),
+ `lkey` bigint unsigned,
+ primary key (`uuid`, `lkey`),
+ foreign key (`uuid`) references `hosts`(`uuid`)
+ on delete cascade on update cascade,
+ foreign key (`lkey`) references `lang`(`lkey`)
+ on delete cascade on update cascade
);
drop table if exists `features`;
@@ -63,9 +73,13 @@ create table `features` (
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`)
+ `uuid` binary (16),
+ `fkey` bigint unsigned,
+ primary key (`uuid`, `fkey`),
+ foreign key (`uuid`) references `hosts`(`uuid`)
+ on delete cascade on update cascade,
+ foreign key (`fkey`) references `features`(`fkey`)
+ on delete cascade on update cascade
);
drop table if exists `gentoo_mirrors`;
@@ -77,9 +91,13 @@ create table `gentoo_mirrors` (
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`)
+ `uuid` binary (16),
+ `mkey` bigint unsigned,
+ primary key (`uuid`, `mkey`),
+ foreign key (`uuid`) references `hosts`(`uuid`)
+ on delete cascade on update cascade,
+ foreign key (`mkey`) references `gentoo_mirrors`(`mkey`)
+ on delete cascade on update cascade
);
drop table if exists `packages`;
@@ -101,14 +119,22 @@ create table `repositories` (
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,
+ `uuid` binary (16),
+ `pkey` bigint unsigned,
+ `build_time` timestamp null default null,
`counter` bigint unsigned,
- `kwkey` bigint unsigned references keywords.kwkey,
- `rkey` varchar (64) references repositories.rkey,
+ `kwkey` bigint unsigned,
+ `rkey` varchar (64),
`size` bigint unsigned,
- primary key (`uuid`, `pkey`)
+ primary key (`uuid`, `pkey`),
+ foreign key (`uuid`) references `hosts`(`uuid`)
+ on delete cascade on update cascade,
+ foreign key (`pkey`) references `packages`(`pkey`)
+ on delete cascade on update cascade,
+ foreign key (`kwkey`) references `keywords`(`kwkey`)
+ on delete cascade on update cascade,
+ foreign key (`rkey`) references `repositories`(`rkey`)
+ on delete cascade on update cascade
);
drop table if exists `useflags`;
@@ -120,28 +146,44 @@ create table `useflags` (
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`)
+ `uuid` binary (16),
+ `ukey` bigint unsigned,
+ primary key (`uuid`, `ukey`),
+ foreign key (`uuid`) references `hosts`(`uuid`)
+ on delete cascade on update cascade,
+ foreign key (`ukey`) references `useflags`(`ukey`)
+ on delete cascade on update cascade
);
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`)
+ `ipkey` bigint unsigned,
+ `ukey` bigint unsigned,
+ primary key (`ipkey`, `ukey`),
+ foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+ on delete cascade on update cascade,
+ foreign key (`ukey`) references `useflags`(`ukey`)
+ on delete cascade on update cascade
);
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`)
+ `ipkey` bigint unsigned,
+ `ukey` bigint unsigned,
+ primary key (`ipkey`, `ukey`),
+ foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+ on delete cascade on update cascade,
+ foreign key (`ukey`) references `useflags`(`ukey`)
+ on delete cascade on update cascade
);
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`)
+ `ipkey` bigint unsigned,
+ `ukey` bigint unsigned,
+ primary key (`ipkey`, `ukey`),
+ foreign key (`ipkey`) references `installed_packages`(`ipkey`)
+ on delete cascade on update cascade,
+ foreign key (`ukey`) references `useflags`(`ukey`)
+ on delete cascade on update cascade
);
^ permalink raw reply related [flat|nested] 5+ messages in thread
* [gentoo-commits] proj/gentoostats:master commit in: server/sql/
@ 2011-06-07 19:07 Vikraman Choudhury
0 siblings, 0 replies; 5+ messages in thread
From: Vikraman Choudhury @ 2011-06-07 19:07 UTC (permalink / raw
To: gentoo-commits
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`)
);
^ permalink raw reply related [flat|nested] 5+ messages in thread
* [gentoo-commits] proj/gentoostats:master commit in: server/sql/
@ 2011-05-06 17:21 Vikraman Choudhury
0 siblings, 0 replies; 5+ messages in thread
From: Vikraman Choudhury @ 2011-05-06 17:21 UTC (permalink / raw
To: gentoo-commits
commit: bd2d27531a873bb1659b6a8d76737083c52c1967
Author: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
AuthorDate: Fri May 6 17:21:04 2011 +0000
Commit: Vikraman Choudhury <vikraman.choudhury <AT> gmail <DOT> com>
CommitDate: Fri May 6 17:21:04 2011 +0000
URL: http://git.overlays.gentoo.org/gitweb/?p=proj/gentoostats.git;a=commit;h=bd2d2753
sql tables created
---
server/sql/init.sql | 32 ++++++++++++++++++++++++++++++++
server/sql/setup.sql | 3 +++
2 files changed, 35 insertions(+), 0 deletions(-)
diff --git a/server/sql/init.sql b/server/sql/init.sql
new file mode 100644
index 0000000..4fa490a
--- /dev/null
+++ b/server/sql/init.sql
@@ -0,0 +1,32 @@
+
+-- run as 'vh4x0r'@'localhost' identified by 'vh4x0r'
+use gentoostats;
+
+drop table if exists hosts;
+create table hosts (
+ uuid varchar (40) primary key,
+ passwd varchar (40) not null
+);
+
+drop table if exists env;
+create table env (
+ uuid varchar (40) references hosts.uuid,
+ var varchar (15) not null,
+ value varchar (100),
+ primary key (uuid, var)
+);
+
+drop table if exists packages;
+create table packages (
+ cat varchar (20) not null,
+ pkg varchar (20) not null,
+ ver varchar (20) not null,
+ pkey serial primary key
+);
+
+drop table if exists useflags;
+create table useflags (
+ uuid varchar (40) references host.uuid,
+ useflag varchar (20) not null,
+ pkey serial references packages.pkey
+);
diff --git a/server/sql/setup.sql b/server/sql/setup.sql
new file mode 100644
index 0000000..98126d7
--- /dev/null
+++ b/server/sql/setup.sql
@@ -0,0 +1,3 @@
+-- run as root
+create database gentoostats;
+grant all on gentoostats.* to 'vh4x0r'@'localhost' identified by 'vh4x0r';
^ permalink raw reply related [flat|nested] 5+ messages in thread
end of thread, other threads:[~2012-02-05 12:16 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
2012-02-05 12:15 [gentoo-commits] proj/gentoostats:master commit in: server/sql/ Vikraman Choudhury
-- strict thread matches above, loose matches on Subject: below --
2011-06-14 17:43 Vikraman Choudhury
2011-06-07 23:58 Vikraman Choudhury
2011-06-07 19:07 Vikraman Choudhury
2011-05-06 17:21 Vikraman Choudhury
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox