View Source

The current db schema can be created as follows:

create a database called berkano (use another name if you want, you'll have to update your jndi configuration accordingly)

create a user for berkano and give it the right permissions.
On mysql, this can be done with the following script:

create database berkano;
grant all privileges on berkano.* to 'berkano'@'localhost' identified by 'some-password';

create the schema itself, using the following script:

This is a generated script, which is why some of the columns are oddly ordered and the constraints have human-unfriendly names. I'm not sure I want to maintain a clean script, although now that might become an option since the model has been stabilizing.

This will work with the Hibernate implementation of Berkano-User. It could probably work for and with other implementations, too, but since we have none for the moment, we can't really say.

use berkano;

create table user (
   user_id bigint not null auto_increment,
   password varchar(255) not null,
   user_name varchar(32) not null,
   email varchar(128),
   full_name varchar(128),
   primary key (user_id)
);
create table group_role (
   group_id bigint not null,
   role varchar(255) not null,
   primary key (group_id, role)
);
create table group_properties (
   group_id bigint not null,
   value text,
   name varchar(255) not null,
   primary key (group_id, name)
);
create table `group` (
   group_id bigint not null auto_increment,
   group_name varchar(32) not null unique,
   primary key (group_id)
);
create table user_properties (
   user_id bigint not null,
   value text,
   name varchar(255) not null,
   primary key (user_id, name)
);
create table role (
   name varchar(255) not null,
   primary key (name)
);
create table user_group (
   group_id bigint not null,
   user_id bigint not null,
   primary key (user_id, group_id)
);
alter table group_role 
  add index FK4C707A36358076 (role), 
  add constraint FK4C707A36358076 foreign key (role) references role (name);
alter table group_role
  add index FK4C707A361E2E76DB (group_id), 
  add constraint FK4C707A361E2E76DB foreign key (group_id) references `group` (group_id);
alter table group_properties 
  add index FK1DD636F31E2E76DB (group_id), 
  add constraint FK1DD636F31E2E76DB foreign key (group_id) references `group` (group_id);
alter table user_properties
  add index FK18623A27F73AEE0F (user_id), 
  add constraint FK18623A27F73AEE0F foreign key (user_id) references user (user_id);
alter table user_group
  add index FK72A9010B1E2E76DB (group_id), 
  add constraint FK72A9010B1E2E76DB foreign key (group_id) references `group` (group_id);
alter table user_group
  add index FK72A9010BF73AEE0F (user_id), 
  add constraint FK72A9010BF73AEE0F foreign key (user_id) references user (user_id);