From Stack Overflow
[edit] Structure
-- ---------------------------------------
-- PHPBB TABLES
-- These would normally be copied into the
-- database with a command like (untested):
--
-- mysqldump -uroot -psomething -hmysql.example.com --add-drop-table phpbb phpbb_users phpbb_user_groups phpbb_groups | gzip -c > tables.sql.gz
-- zcat -c tables.sql.gz | mysql -uroot -psomething -hmysql.ufrl.net ufrl
--
-- ---------------------------------------
create table phpbb_users (
user_id int primary key not null,
user_active tinyint not null default 1,
username varchar(25) not null,
user_password varchar(32) not null,
user_website varchar(100) null
);
create table phpbb_groups (
group_id int primary key auto_increment,
group_type int not null,
group_name varchar(40) not null,
group_singleuser tinyint not null
);
create table phpbb_user_groups (
group_id int not null,
user_id int not null,
user_pending tinyint not null,
constraint foreign key (group_id) references phpbb_groups (group_id),
constraint foreign key (user_id) references phpbb_users (user_id)
);
create unique index phpbb_user_groups_pk on phpbb_user_groups (group_id, user_id);
-- ---------------------------------------
-- UFRL TABLES
-- ---------------------------------------
create table ufrl_sessions (
user_id int primary key not null, -- reference to phpbb_users
cookie varchar(100) not null, -- the cookie given to their browser
created timestamp, -- creation timestamp
accessed timestamp, -- last access timestamp
created_ip varchar(16), -- IP address of creation
accessed_ip varchar(16), -- IP address of last access
constraint foreign key (user_id) references phpbb_users (user_id)
);
create index ufrl_sessions_cookie on ufrl_sessions (cookie);
create table ufrl_admin_groups (
group_id int primary key not null, -- reference to phpbb_groups
is_admin tinyint not null, -- set if the group is considered UFRL administrators
is_donor tinyint not null, -- set if the group is considered UF donors
constraint foreign key (group_id) references phpbb_groups (group_id)
);
create table ufrl_urls (
url_id int primary key not null auto_increment,
url text
);
create table ufrl_url_groups (
url_group_id int primary key auto_increment,
short_name varchar(32) not null,
long_name varchar(255) not null
);
create table ufrl_short_urls (
short_url_id int primary key auto_increment,
url_group_id int null,
text varchar(64) not null,
url_id int not null,
user_id int not null,
stamp timestamp,
ip varchar(16),
redirect_count_loggedin int default 0,
redirect_count_anonymous int default 0,
constraint foreign key (short_url_id) references ufrl_urls (url_id),
constraint foreign key (url_group_id) references ufrl_url_groups (url_group_id),
constraint foreign key (user_id) references phpbb_users (user_id)
);
create unique index ufrl_short_urls_uk on ufrl_short_urls (url_group_id, text);
-- so there's only one unique group+text in the table
create index ufrl_short_urls_idx on ufrl_short_urls (url_id);
-- to make access by the short URL quick
[edit] Sample Data
-- First, dump the current tables in cascading order
truncate table ufrl_short_urls;
truncate table ufrl_sessions;
truncate table ufrl_url_groups;
truncate table ufrl_urls;
truncate table ufrl_admin_groups;
truncate table phpbb_user_groups;
truncate table phpbb_groups;
truncate table phpbb_users;
-- Next, add some sample phpbb users
insert into phpbb_users (user_id, user_active, username, user_password, user_website)
values (1, 1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'http://unfiction.com');
-- admin/admin
insert into phpbb_users (user_id, user_active, username, user_password, user_website)
values (2, 1, 'user', 'ee11cbb19052e40b07aac0ca060c23ee', 'http://netninja.com');
-- user/user
insert into phpbb_users (user_id, user_active, username, user_password, user_website)
values (3, 1, 'paid', '76e084771e78c194efd0e2d5b8920bea', 'http://google.com');
-- paid/paid
insert into phpbb_groups (group_id, group_type, group_name, group_singleuser)
values (1, 2, 'UFRL Administrators', 0);
insert into phpbb_groups (group_id, group_type, group_name, group_singleuser)
values (2, 2, 'Donors', 0);
insert into phpbb_user_groups (group_id, user_id, user_pending)
values (1, 1, 0);
insert into phpbb_user_groups (group_id, user_id, user_pending)
values (3, 2, 0);
-- Next, set up security
insert into ufrl_admin_groups (group_id, is_admin, is_donor)
values (1, 1, 0);
insert into ufrl_admin_groups (group_id, is_admin, is_donor)
values (2, 0, 1);
-- Next, set up some URL groups
insert into ufrl_url_groups (url_group_id, short_name, long_name)
values (1, 'pxc', 'Perplex City');
insert into ufrl_url_groups (url_group_id, short_name, long_name)
values (2, 'edoc', 'EDOC Laundry');
insert into ufrl_url_groups (url_group_id, short_name, long_name)
values (3, 'uf', 'Unfiction');
insert into ufrl_url_groups (url_group_id, short_name, long_name)
values (4, 'ctw', 'Catching the Wish');
insert into ufrl_url_groups (url_group_id, short_name, long_name)
values (5, 'lost', 'The Lost Experience');
-- Next, set up some grouped URLs (e.g. edoc/blah pxc/blah)
insert into ufrl_urls (url_id, url)
values (1, 'http://perplexcity.com');
insert into ufrl_short_urls (short_url_id, url_group_id, text, url_id, user_id)
values (1, 1, 'home', 1, 1);
insert into ufrl_urls (url_id, url)
values (2, 'http://thescarlettkite.com');
insert into ufrl_short_urls (short_url_id, url_group_id, text, url_id, user_id)
values (2, 1, 'scarlett', 2, 1);
insert into ufrl_urls (url_id, url)
values (3, 'http://perplexcitywiki.com');
insert into ufrl_short_urls (short_url_id, url_group_id, text, url_id, user_id)
values (3, 1, 'wiki', 3, 1);
insert into ufrl_urls (url_id, url)
values (4, 'http://edoclaundry.com/');
insert into ufrl_short_urls (short_url_id, url_group_id, text, url_id, user_id)
values (4, 2, 'home', 4, 1);
insert into ufrl_urls (url_id, url)
values (5, 'http://www.intellinuts.com/edoc/');
insert into ufrl_short_urls (short_url_id, url_group_id, text, url_id, user_id)
values (5, 2, 'guide', 5, 1);
-- Next, set up some ungrouped URLs (e.g. abcd)
insert into ufrl_urls (url_id, url)
values (6, 'http://netninja.com');
insert into ufrl_short_urls (short_url_id, url_group_id, text, url_id, user_id)
values (6, NULL, 'nn', 6, 3);
