Grouper Developers Forum

[Simon McLeish <>]

Thanks for the suggestions on this; I've now got grouper to pass all the
tests. Attached are the working postgres schema and test data SQL files.

The delay in responding was because I was trying to do something a bit
more clever with the SQL - postgres doesn't support conditional table
drops, so I was creating a function to implement this. Unfortunately,
having got it to work, I found that it didn't load via the hibernate
interface. It's also not possible to use a script which drops non-
existent tables, as it would be with a directly executed SQL file, as
what would be warnings from the postgres interface become fatal errors.
So the schema file does not include any drop statements.

In case it might be of use (and because I put quite a lot of effort into
it - it was a useful if frustrating learning experience working round
all the things the procedural language won't do) I've also attached the
version of the schema file with the function implementing conditional
drop.

Cheers,
Simon

On Thu, 2005-06-09 at 16:37 +0100, GW Brown, Information Systems and
Computing wrote:
> Hi Simon,
> 
> Have you looked at conf/hibernate.properties?
> 
> There are setttings such as:
> 
> hibernate.dialect= net.sf.hibernate.dialect.OracleDialect
> hibernate.connection.driver_class= oracle.jdbc.driver.OracleDriver
> 
> hibernate.connection.url= jdbc:oracle:thin:@<host>:1521:HDESKT
> 
> These are what Grouper uses when exercising the API.
> 
> Gary
> 
> --On 09 June 2005 16:20 +0100 Simon McLeish 
> <>
>  wrote:
> 
> > Hi,
> >
> > I've been trying to build grouper against the postgres database; I've
> > created working schema and test data files, and ant build and ant db-
> > init run fine to create the database. When I run ant test, though, I get
> > the data insertion OK but then every test fails with errors like:
> >
> >    [junit] 113) testReplaceThenFetchDescNewSession
> > (test.edu.internet2.middleware.grouper.TestGroupsAttrsRep)java.lang.Runti
> > meException: Unable to create connection to
> > jdbc:hsqldb:dist/run/grouper:java.sql.SQLException: The database is
> > already in use by another process: 
> > org.hsqldb.NIOLockFile@77097ff5[file
> > =/usr/local/grouper-0.5.6/dist/run/grouper.lck, exists=true,
> > locked=false, valid=false, fl =null]: java.lang.Exception:
> > checkHeartbeat(): lock file
> > [/usr/local/grouper-0.5.6/dist/run/grouper.lck] is presumably locked by
> > another process.     [junit]     at
> > test.edu.internet2.middleware.grouper.DB._connect (Unknown Source)
> >     [junit]     at
> > test.edu.internet2.middleware.grouper.DB.<init>(Unknown Source)
> >     [junit]     at
> > test.edu.internet2.middleware.grouper.TestGroupsAttrsRep.setUp(Unknown
> > Source)
> >
> > I presume this means that the hsqldb URI is hardcoded somewhere rather
> > than picked up from the build.xml file where I put the equivalent
> > postgres URI (and the lock file part of the error is present because the
> > previous tests didn't complete). If I'm wrong and there's a fix, could
> > you let me know?
> >
> > Cheers,
> > Simon
> > --
> > Simon McLeish 
> > <>
> > London School of Economics
> >
> 
> 
> 
> ----------------------
> GW Brown, Information Systems and Computing
> 
-- 
Simon McLeish 
<>
London School of Economics
-- 
-- Grouper Database Schema
-- 

CREATE TABLE grouper_attribute (
  groupKey        VARCHAR(64) NOT NULL,
  groupField      VARCHAR(64) NOT NULL,
  groupFieldValue VARCHAR(64),
  CONSTRAINT      uniq_ga_gk_gf UNIQUE (groupKey, groupField)
);

CREATE TABLE grouper_field (
  groupField  VARCHAR(64) NOT NULL,
  readPriv    VARCHAR(64),
  writePriv   VARCHAR(64),
  isList      VARCHAR(8),

  PRIMARY KEY (groupField)
);

CREATE TABLE grouper_group (
  groupKey      VARCHAR(64) NOT NULL,
  groupID       VARCHAR(64) NOT NULL,
  classType     VARCHAR(64),
  createTime    VARCHAR(16),
  createSubject VARCHAR(64),
  createSource  VARCHAR(64),
  modifyTime    VARCHAR(16),
  modifySubject VARCHAR(64),
  modifySource  VARCHAR(64),
  groupComment  VARCHAR(256),
  CONSTRAINT    uniq_gg_gid  UNIQUE (groupID),
  PRIMARY KEY (groupKey)
);

CREATE TABLE grouper_list (
  listKey       VARCHAR(64) NOT NULL,
  groupKey      VARCHAR(64) NOT NULL,
  groupField    VARCHAR(64) NOT NULL,
  memberKey     VARCHAR(64) NOT NULL,
  chainKey      VARCHAR(64),
  viaKey        VARCHAR(64),
  PRIMARY KEY (listKey)
);
CREATE UNIQUE INDEX idx_gl_gk_gf_mk_ck ON grouper_list 
  (groupKey, groupField, memberKey, chainKey);

CREATE TABLE grouper_member (
  memberKey     VARCHAR(64) NOT NULL,
  memberID      VARCHAR(64) NOT NULL,
  subjectID     VARCHAR(64) NOT NULL,
  subjectTypeID VARCHAR(64) NOT NULL,
  PRIMARY KEY (memberKey)
);
CREATE UNIQUE INDEX idx_gm_sid_stid ON grouper_member
  (subjectID, subjectTypeID);

CREATE TABLE grouper_memberVia (
  chainKey    VARCHAR(64) NOT NULL,
  chainIdx    INTEGER NOT NULL,
  listKey     VARCHAR(64) NOT NULL,
  CONSTRAINT  uniq_gmv_ck_ci_lk UNIQUE (chainKey, chainIdx, listKey)
);

-- TODO Are these the right indices for this table?
CREATE  INDEX idx_mv_ck ON grouper_memberVia (chainKey);
CREATE  INDEX idx_mv_lk ON grouper_memberVia (listKey);

CREATE TABLE grouper_schema (
  groupKey    VARCHAR(64) NOT NULL,
  groupType   VARCHAR(64) NOT NULL,
  CONSTRAINT  uniq_gsch_gk_gt  UNIQUE (groupKey, groupType)
);

CREATE TABLE grouper_session (
  sessionID  VARCHAR(64) NOT NULL,
  memberID   VARCHAR(64) NOT NULL,
  startTime  VARCHAR(16),
  PRIMARY KEY (sessionID)
);

CREATE TABLE grouper_subject (
  subjectID     VARCHAR(64) NOT NULL,
  subjectTypeID VARCHAR(64) NOT NULL,
  CONSTRAINT    uniq_gsub_sid_stid UNIQUE (subjectID, subjectTypeID),
  PRIMARY KEY (subjectID)
);

CREATE TABLE grouper_subjectAttribute (
  subjectID     VARCHAR(64) NOT NULL, 
  subjectTypeID VARCHAR(64) NOT NULL,
  name          VARCHAR(64) NOT NULL,
  instance      INTEGER,
  value         VARCHAR(64) NOT NULL,
  searchValue   VARCHAR(64),
  CONSTRAINT    uniq_gsubattr_sid_stid UNIQUE (subjectID, subjectTypeID)
);

CREATE TABLE grouper_subjectType (
  subjectTypeID VARCHAR(64) NOT NULL,
  name          VARCHAR(64) NOT NULL,
  adapterClass  VARCHAR(128) NOT NULL,
  PRIMARY KEY (subjectTypeID)
);

CREATE TABLE grouper_typeDef (
  groupType   VARCHAR(64) NOT NULL,
  groupField  VARCHAR(64) NOT NULL,
  CONSTRAINT  uniq_gtypdef_gt_gf  UNIQUE (groupType, groupField)
);

CREATE TABLE grouper_type (
  groupType   VARCHAR(64) NOT NULL PRIMARY KEY
);

-- 
-- Grouper Database Schema
-- 

CREATE OR REPLACE FUNCTION cond_drop(text) RETURNS text AS '
DECLARE 
  tabnametext ALIAS FOR $1;
  tablist RECORD;
BEGIN
  -- checks system tables to see if table exists before attempting to drop it
  -- Postgres doesnt have drop ... if exists syntax
  -- and script execution halts if drop of non-existent table attempted
  -- To run the script requires the creation of the grouper database and 
running
  -- the createlang command line tool:
  -- % createlang plpgsql grouper
  FOR tablist IN SELECT tablename FROM pg_tables LOOP
    IF (tablist.tablename = tabnametext) THEN
      EXECUTE ''SELECT cond_drop('' || quote_ident(tabnametext);
      RETURN ''Table dropped: '' || tabnametext;
    END IF;
  END LOOP;
  RETURN ''Table '' || tabnametext || '' does not exist'';
END;
' LANGUAGE plpgsql;

SELECT cond_drop('grouper_attribute');
CREATE TABLE grouper_attribute (
  groupKey        VARCHAR(64) NOT NULL,
  groupField      VARCHAR(64) NOT NULL,
  groupFieldValue VARCHAR(64),
  CONSTRAINT      uniq_ga_gk_gf UNIQUE (groupKey, groupField)
);

SELECT cond_drop('grouper_field');
CREATE TABLE grouper_field (
  groupField  VARCHAR(64) NOT NULL,
  readPriv    VARCHAR(64),
  writePriv   VARCHAR(64),
  isList      VARCHAR(8),

  PRIMARY KEY (groupField)
);

SELECT cond_drop('grouper_group');
CREATE TABLE grouper_group (
  groupKey      VARCHAR(64) NOT NULL,
  groupID       VARCHAR(64) NOT NULL,
  classType     VARCHAR(64),
  createTime    VARCHAR(16),
  createSubject VARCHAR(64),
  createSource  VARCHAR(64),
  modifyTime    VARCHAR(16),
  modifySubject VARCHAR(64),
  modifySource  VARCHAR(64),
  groupComment  VARCHAR(256),
  CONSTRAINT    uniq_gg_gid  UNIQUE (groupID),
  PRIMARY KEY (groupKey)
);

SELECT cond_drop('grouper_list');
CREATE TABLE grouper_list (
  listKey       VARCHAR(64) NOT NULL,
  groupKey      VARCHAR(64) NOT NULL,
  groupField    VARCHAR(64) NOT NULL,
  memberKey     VARCHAR(64) NOT NULL,
  chainKey      VARCHAR(64),
  viaKey        VARCHAR(64),
  PRIMARY KEY (listKey)
);
CREATE UNIQUE INDEX idx_gl_gk_gf_mk_ck ON grouper_list 
  (groupKey, groupField, memberKey, chainKey);

SELECT cond_drop('grouper_member');
CREATE TABLE grouper_member (
  memberKey     VARCHAR(64) NOT NULL,
  memberID      VARCHAR(64) NOT NULL,
  subjectID     VARCHAR(64) NOT NULL,
  subjectTypeID VARCHAR(64) NOT NULL,
  PRIMARY KEY (memberKey)
);
CREATE UNIQUE INDEX idx_gm_sid_stid ON grouper_member
  (subjectID, subjectTypeID);

SELECT cond_drop('grouper_memberVia');
CREATE TABLE grouper_memberVia (
  chainKey    VARCHAR(64) NOT NULL,
  chainIdx    INTEGER NOT NULL,
  listKey     VARCHAR(64) NOT NULL,
  CONSTRAINT  uniq_gmv_ck_ci_lk UNIQUE (chainKey, chainIdx, listKey)
);

-- TODO Are these the right indices for this table?
CREATE  INDEX idx_mv_ck ON grouper_memberVia (chainKey);
CREATE  INDEX idx_mv_lk ON grouper_memberVia (listKey);

SELECT cond_drop('grouper_schema');
CREATE TABLE grouper_schema (
  groupKey    VARCHAR(64) NOT NULL,
  groupType   VARCHAR(64) NOT NULL,
  CONSTRAINT  uniq_gsch_gk_gt  UNIQUE (groupKey, groupType)
);

SELECT cond_drop('grouper_session');
CREATE TABLE grouper_session (
  sessionID  VARCHAR(64) NOT NULL,
  memberID   VARCHAR(64) NOT NULL,
  startTime  VARCHAR(16),
  PRIMARY KEY (sessionID)
);

SELECT cond_drop('grouper_subject');
CREATE TABLE grouper_subject (
  subjectID     VARCHAR(64) NOT NULL,
  subjectTypeID VARCHAR(64) NOT NULL,
  CONSTRAINT    uniq_gsub_sid_stid UNIQUE (subjectID, subjectTypeID),
  PRIMARY KEY (subjectID)
);

SELECT cond_drop('grouper_subjectAttribute');
CREATE TABLE grouper_subjectAttribute (
  subjectID     VARCHAR(64) NOT NULL, 
  subjectTypeID VARCHAR(64) NOT NULL,
  name          VARCHAR(64) NOT NULL,
  instance      INTEGER,
  value         VARCHAR(64) NOT NULL,
  searchValue   VARCHAR(64),
  CONSTRAINT    uniq_gsubattr_sid_stid UNIQUE (subjectID, subjectTypeID)
);

SELECT cond_drop('grouper_subjectType');
CREATE TABLE grouper_subjectType (
  subjectTypeID VARCHAR(64) NOT NULL,
  name          VARCHAR(64) NOT NULL,
  adapterClass  VARCHAR(128) NOT NULL,
  PRIMARY KEY (subjectTypeID)
);

SELECT cond_drop('grouper_typeDef');
CREATE TABLE grouper_typeDef (
  groupType   VARCHAR(64) NOT NULL,
  groupField  VARCHAR(64) NOT NULL,
  CONSTRAINT  uniq_gtypdef_gt_gf  UNIQUE (groupType, groupField)
);

SELECT cond_drop('grouper_type');
CREATE TABLE grouper_type (
  groupType   VARCHAR(64) NOT NULL PRIMARY KEY
);
-- 
-- Grouper Test Data
-- 

DELETE FROM grouper_subject WHERE subjectID='member 0';
INSERT INTO grouper_subject (subjectID, subjectTypeID)
  VALUES ('member 0', 'person');
DELETE FROM grouper_subject WHERE subjectID='member 1';
INSERT INTO grouper_subject (subjectID, subjectTypeID)
  VALUES ('member 1', 'person');