Hi!
I'd like to contribute install scripts for Interbase. Tested on IB 6.0
Interbase_default_roles_permissions.sql only differs by comment style
because IB does not support "--" comments. It would make sense to change
main version to use /**/-style comments since all DBs seem to support
it.
WBR, Fedor
------------- Interbase_id_table.sql ---------------------------------
/* ------------------------------------
** This table provides the primary keys for all
** other tables in the system. Should be used
** with util.db.IDBroker.
** -----------------------------------
*/
drop table id_table;
create table id_table
(
id_table_id integer NOT NULL,
table_name varchar(255) NOT NULL,
next_id integer,
quantity integer,
PRIMARY KEY (ID_TABLE_ID)
);
create unique index id_table_id_table_id on id_table(id_table_id);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (1, 'Permission', 20, 10);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (2, 'UserRole', 20, 10);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (3, 'Visitor', 20, 10);
insert into ID_TABLE (id_table_id, table_name, next_id, quantity)
values (4, 'Jobentry', 20, 10);
----------------------------------------------------------------------
------------- Interbase_users_roles_permissions.sql ------------------
/* Turbine, a Servlet Framework for building Dynamic Websites
** Copyright (C) 1999 Jon S. Stevens, Brett McLaughlin, Fedor
Karpelevitch
**
** For licensing and use, please see the LICENSE.txt in this directory.
**
** This is a schema for Interbase for
** the concept of Users/Roles/Permissions
**
*/
/********************************************************************/
/*
** do not execute (comment-out) the drop part when performing
** the initial setup or it will break. The drop part is needed for
re-build only.
*/
alter table RolePermission
drop constraint FK_ROLEPERM_PERM;
alter table RolePermission
drop constraint FK_ROLEPERM_USERROLE;
alter table VisitorRole
drop constraint FK_VISROLE_USERROLE;
alter table VisitorRole
drop constraint FK_VISROLE_VISITOR;
drop table Permission;
drop table UserRole;
drop table RolePermission;
drop table Visitor;
drop table VisitorRole;
drop table Jobentry;
DELETE FROM RDB$GENERATORS
WHERE RDB$GENERATOR_NAME in ('PERMISSIONID_GEN
','ROLEID_GEN ', 'VISITORID_GEN ');
commit work;
create table Permission
(
PERMISSIONID integer not null,
PERMISSION varchar (99) NOT NULL,
PRIMARY KEY (PERMISSIONID)
);
create unique index Permission_permission on Permission (PERMISSION);
create generator permissionid_gen;
SET TERM ^ ;
CREATE TRIGGER SET_permissionid FOR Permission
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.permissionid = gen_id(permissionid_gen, 1);
END ^
set term ;^
/********************************************************************/
create table UserRole
(
ROLEID integer not null,
ROLENAME varchar (99) NOT NULL,
PRIMARY KEY (ROLEID)
);
create unique index UserRole_rolename on UserRole (ROLENAME);
create generator ROLEID_gen;
SET TERM ^ ;
CREATE TRIGGER SET_ROLEID FOR UserRole
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ROLEID = gen_id(ROLEID_gen, 1);
END ^
set term ;^
/********************************************************************/
create table RolePermission
(
ROLEID integer NOT NULL,
PERMISSIONID integer NOT NULL,
PRIMARY KEY (ROLEID, PERMISSIONID)
);
create index RoPerm_rid_permid_index on RolePermission ( ROLEID,
PERMISSIONID );
/********************************************************************/
create table Visitor
(
VISITORID INTEGER NOT NULL,
LOGINID varchar (32) NOT NULL,
PASSWORD_VALUE varchar (32) NOT NULL,
FIRST_NAME varchar (99) NOT NULL,
LAST_NAME varchar (99) NOT NULL,
ADDRESS1 varchar (255),
ADDRESS2 varchar (255),
CITY varchar (255),
STATE varchar (32),
POSTALCODE varchar (32),
COUNTRY varchar (99),
CITIZENSHIP varchar (32),
PHONE varchar (32),
ALTPHONE varchar (32),
FAX varchar (32),
CELL varchar (32),
PAGER varchar (32),
EMAIL varchar (99),
MODIFIED timestamp,
CREATED timestamp,
LASTLOGIN timestamp,
OBJECTDATA BLOB,
PREFIX_NAME varchar (16),
MIDDLE_NAME varchar (99),
SUFFIX_NAME varchar (16),
COMPANY varchar (255),
CONFIRM_VALUE varchar (99),
PRIMARY KEY (VISITORID)
);
create unique index Visitor_loginid on Visitor (LOGINID);
create generator VISITORID_gen;
SET TERM ^ ;
CREATE TRIGGER SET_VISITORID FOR Visitor
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.VISITORID = gen_id(VISITORID_gen, 1);
END ^
set term ;^
/********************************************************************/
create table VisitorRole
(
VISITORID integer NOT NULL,
ROLEID integer NOT NULL,
PRIMARY KEY (VISITORID, ROLEID)
);
create unique index VisRo_visid_roid_index on VisitorRole ( VISITORID,
ROLEID );
alter table RolePermission
add constraint FK_ROLEPERM_PERM foreign key (PERMISSIONID)
references Permission (PERMISSIONID);
alter table RolePermission
add constraint FK_ROLEPERM_USERROLE foreign key (ROLEID)
references UserRole (ROLEID);
alter table VisitorRole
add constraint FK_VISROLE_USERROLE foreign key (ROLEID)
references UserRole (ROLEID);
alter table VisitorRole
add constraint FK_VISROLE_VISITOR foreign key (VISITORID)
references Visitor (VISITORID);
/********************************************************************/
create table Jobentry (
JOB_ID integer NOT NULL,
"MINUTE" integer default -1 NOT NULL,
"HOUR" integer default -1 NOT NULL,
"WEEKDAY" integer default -1 NOT NULL,
DAY_OF_MONTH integer default -1 NOT NULL,
TASK varchar(99) NOT NULL,
EMAIL varchar(99),
PRIMARY KEY (JOB_ID)
);
----------------------------------------------------------------------
------------- Interbase_default_roles_permissions.sql ----------------
/*
** Script to fill the tables with default roles and permissions for
Interbase
** The only difference is comment style
*/
/* Add some default permissions */
insert into Permission (PERMISSIONID, PERMISSION) values (1,
'view_user');
insert into Permission (PERMISSIONID, PERMISSION) values (2,
'add_user');
insert into Permission (PERMISSIONID, PERMISSION) values (3,
'modify_user');
insert into Permission (PERMISSIONID, PERMISSION) values (4,
'view_group');
insert into Permission (PERMISSIONID, PERMISSION) values (5,
'add_group');
insert into Permission (PERMISSIONID, PERMISSION) values (6,
'modify_group');
insert into Permission (PERMISSIONID, PERMISSION) values (7,
'view_permission');
insert into Permission (PERMISSIONID, PERMISSION) values (8,
'add_permission');
insert into Permission (PERMISSIONID, PERMISSION) values (9,
'modify_permission');
insert into Permission (PERMISSIONID, PERMISSION) values (10,
'view_role');
insert into Permission (PERMISSIONID, PERMISSION) values (11,
'add_role');
insert into Permission (PERMISSIONID, PERMISSION) values (12,
'modify_role');
/* Create a Role or Group in this case turbine root */
insert into UserRole (ROLEID, ROLENAME) values (1, 'turbine_root');
/* Add some permissions for turbine root */
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'view_user' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'add_user' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'modify_user' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'view_group' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'add_group' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'modify_group' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'view_permission' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'add_permission' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'modify_permission' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'view_role' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'add_role' and UserRole.ROLENAME =
'turbine_root';
insert into RolePermission (ROLEID,PERMISSIONID) select UserRole.ROLEID,
Permission.PERMISSIONID from UserRole, Permission where
Permission.PERMISSION = 'modify_role' and UserRole.ROLENAME =
'turbine_root';
/*
** Create an account 'turbine' for system administartor
** Remeber to set a good password for this user in a production system!
*/
insert into Visitor (LOGINID, PASSWORD_VALUE, FIRST_NAME, LAST_NAME,
MODIFIED, CREATED ) values ('turbine', 'turbine', 'turbine', 'turbine',
null, null );
/* Finally, add the user turbine to the turbine_root group */
insert into VisitorRole ( VISITORID, ROLEID ) select Visitor.VISITORID,
UserRole.ROLEID from Visitor, UserRole where Visitor.LOGINID = 'turbine'
AND UserRole.ROLENAME = 'turbine_root';
------------------------------------------------------------------------------------
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
