pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: John Tregea <john@debraneys.com>
To: Aaron Bono <postgresql@aranya.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Joining a result set from four (4) tables
Date: Tue, 01 Aug 2006 08:12:19 +0800
Message-ID: <44CE9C63.7010409@debraneys.com> (raw)
In-Reply-To: <bf05e51c0607310112wbc75fedkb21773c5bb64d11e@mail.gmail.com>
References: <44CDB1EF.6000508@debraneys.com>
	<bf05e51c0607310112wbc75fedkb21773c5bb64d11e@mail.gmail.com>

Hi aaron,

Here are the 'create table' statements. I have indicated what are the 
primary and foreign keys with trailing comments.

Thanks

John

Aaron Bono wrote:
> Can you include the table create statements with primary and foreign 
> keys?  That would help a lot.

CREATE TABLE resources
(
  serial_id numeric NOT NULL, -- << Primary Key
  related_id numeric, -- << Foreign Key
  host_id int4,
  created timestamptz DEFAULT now(),
  modified timestamptz,
  valid_from timestamp,
  valid_to timestamp,
  schema_name varchar(32),
  grid_loc varchar(32),
  name varchar(32),
  status varchar(16),
  description text,
  comments text,
  sort_order int2,
  user_id int4 DEFAULT 0,
  located text,
  classification varchar(32),
  sequence_id int4,
)

CREATE TABLE actions
(
  serial_id numeric NOT NULL, -- primary key
  related_id numeric, -- foreign key on resources.serial_id
  host_id int4,
  created timestamptz DEFAULT now(),
  modified timestamptz,
  valid_from timestamp,
  valid_to timestamp,
  name varchar(32),
  status varchar(16) DEFAULT 'Active'::character varying,
  description text,
  comments text,
  sort_order int2 DEFAULT 0,
  user_id int4 DEFAULT 0, -- User_ID of the creator
  located text,
  classification varchar(32),
  sequence_id int4,
  in_box varchar(32),
  display_group varchar(2),
)

CREATE TABLE policies
(
  serial_id numeric NOT NULL, -- primary key
  related_id numeric, -- foreign key on actions.serial_id
  resource_id numeric, -- foreign key on resources.serial_id
  owner_id numeric,
  authority_id int4,
  created timestamptz DEFAULT now(),
  modified timestamptz,
  valid_from timestamp,
  valid_to timestamp,
  status varchar(16) DEFAULT 'Active'::character varying,
  description text,
  comments text,
  classification varchar(32),
  user_id int4,
  sequence_id int4,
  inheritance text,
)

CREATE TABLE permissions
(
  serial_id numeric NOT NULL, -- primary key
  related_id numeric, -- foreign key on policies.serial_id
  user_id int4, -- foreign key on users.serial_id
  owner_id int4,
  authority_id int4,
  resource_id int4,
  created timestamptz DEFAULT now(),
  modified timestamptz,
  valid_from timestamp,
  valid_to timestamp,
  name varchar(32),
  acronym varchar(6),
  status varchar(16) DEFAULT 'Active'::character varying,
  inheritance text,
  description text,
  comments text,
  sort_order int2,
  user_id int4 DEFAULT 0,
  located text,
  classification varchar(32),
  sequence_id int4,
)

CREATE TABLE users
(
  serial_id numeric NOT NULL, -- primary key
  created timestamptz DEFAULT now(),
  modified timestamptz,
  valid_from timestamp,
  valid_to timestamp,
  name varchar(64) NOT NULL,
  acronym varchar(6),
  status varchar(16),
  inheritance text,
  description text NOT NULL,
  comments text NOT NULL,
  sort_order int2 NOT NULL,
  clearance varchar(32) NOT NULL,
  administrator bool DEFAULT false,
  user_id int4 DEFAULT 0,
  next_serial_id int4 DEFAULT 1,
  classification varchar(32),
)

view thread (7+ messages)  latest in thread

Message-ID: <44CE9C63.7010409@debraneys.com>
Permalink:  ../44CE9C63.7010409@debraneys.com/
Also on:    postgresql.org/message-id/44CE9C63.7010409@debraneys.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: john@debraneys.com, postgresql@aranya.com
  Subject: Re: Joining a result set from four (4) tables
  In-Reply-To: <44CE9C63.7010409@debraneys.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox