diff options
author | Vikraman Choudhury <vikraman.choudhury@gmail.com> | 2011-06-08 01:14:23 +0530 |
---|---|---|
committer | Vikraman Choudhury <vikraman.choudhury@gmail.com> | 2011-06-08 01:14:23 +0530 |
commit | 0e945b10b562afa03c3e8170bba0f12ba03d4584 (patch) | |
tree | 62e3d510a114bb29e63a989ed384e907f16a59f3 /server | |
parent | updated and fixed sql code (diff) | |
download | gentoostats-0e945b10b562afa03c3e8170bba0f12ba03d4584.tar.gz gentoostats-0e945b10b562afa03c3e8170bba0f12ba03d4584.tar.bz2 gentoostats-0e945b10b562afa03c3e8170bba0f12ba03d4584.zip |
fix foreign key constraints
Diffstat (limited to 'server')
-rw-r--r-- | 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 ); |