CREATE TABLE user (
    id                      SERIAL PRIMRAY KEY,
    username                VARCHAR(200) UNIQUE,
    password                VARCHAR(200),
    locked                  BOOLEAN DEFAULT 1,
    email                   VARCHAR(200)
);

CREATE TABLE group (
    id                      SERIAL PRIMARY KEY,
    groupname               VARCHAR(24)  UNIQUE,
    description             VARCHAR(200)
);

CREATE TABLE user_group (
    user_id                 INTEGER REFERENCES user(id),
    group_id                INTEGER REFERENCES group(id),
    PRIMARY KEY(user_id, group_id)
);

package BloonixDB::User;

use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('user');
__PACKAGE__->add_columns(qw/id username password locked email/);
__PACKAGE__->set_primary_key(qw/id/);
__PACKAGE__->has_many('user_groups', 'BloonixDB::UserGroup', { 'foreign.user_id' => 'self.id' });

package BloonixDB::Group;

use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('group');
__PACKAGE__->add_columns(qw/id groupname description/);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many('user_groups', 'BloonixDB::UserGroup', { 'foreign.group_id' => 'self.id' });

package BloonixDB::UserGroup;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('user_group');
__PACKAGE__->add_columns(qw/user_id group_id/);
__PACKAGE__->belongs_to('users', 'BloonixDB::User', { 'foreign.user_id' => 'self.id' });
__PACKAGE__->belongs_to('groups', 'BloonixDB::Group', { 'foreign.group_id' => 'self.id' });

1;