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 1QU6Am-0007wy-QU for garchives@archives.gentoo.org; Tue, 07 Jun 2011 23:59:01 +0000 Received: from pigeon.gentoo.org (localhost [127.0.0.1]) by pigeon.gentoo.org (Postfix) with SMTP id 2DCED1C034; Tue, 7 Jun 2011 23:58:48 +0000 (UTC) Received: from smtp.gentoo.org (smtp.gentoo.org [140.211.166.183]) by pigeon.gentoo.org (Postfix) with ESMTP id DA05C1C034 for ; Tue, 7 Jun 2011 23:58:47 +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 367511B401F for ; Tue, 7 Jun 2011 23:58:47 +0000 (UTC) Received: from localhost.localdomain (localhost [127.0.0.1]) by pelican.gentoo.org (Postfix) with ESMTP id 6A27F8003C for ; Tue, 7 Jun 2011 23:58:46 +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: <0e945b10b562afa03c3e8170bba0f12ba03d4584.vikraman@gentoo> 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: 0e945b10b562afa03c3e8170bba0f12ba03d4584 Date: Tue, 7 Jun 2011 23:58:46 +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: 921781d81f8c2fad28dc578e989e8c29 commit: 0e945b10b562afa03c3e8170bba0f12ba03d4584 Author: Vikraman Choudhury gmail com> AuthorDate: Tue Jun 7 19:44:23 2011 +0000 Commit: Vikraman Choudhury gmail com> CommitDate: Tue Jun 7 19:44:23 2011 +0000 URL: http://git.overlays.gentoo.org/gitweb/?p=3Dproj/gentoostats.g= it;a=3Dcommit;h=3D0e945b10 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` ( =20 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 ); =20 drop table if exists `keywords`; @@ -35,9 +37,13 @@ create table `keywords` ( =20 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 ); =20 drop table if exists `lang`; @@ -49,9 +55,13 @@ create table `lang` ( =20 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 ); =20 drop table if exists `features`; @@ -63,9 +73,13 @@ create table `features` ( =20 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 ); =20 drop table if exists `gentoo_mirrors`; @@ -77,9 +91,13 @@ create table `gentoo_mirrors` ( =20 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 ); =20 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 ); =20 drop table if exists `useflags`; @@ -120,28 +146,44 @@ create table `useflags` ( =20 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 ); =20 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 ); =20 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 ); =20 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 );