Wiki
Clone wikiShampoo / SQLDump
Shampoo database structure
MySQL 5 InnoDB SQL dialect, for Shampoo 0.9.6
create table animatorright (
programmeid varchar(255) not null,
restricteduserid varchar(255) not null,
primary key (restricteduserid, programmeid)
) ENGINE=InnoDB
create table archive (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
starttimestamp bigint not null,
channelid varchar(255) not null,
playlist varchar(255),
timetableslot varchar(255),
duration bigint not null,
playlistcaption varchar(255),
programmecaption varchar(255),
track varchar(255),
authorcaption varchar(255),
titlecaption varchar(255),
albumcaption varchar(255),
publishercaption varchar(255),
copyrightcaption varchar(255),
requester varchar(255),
broadcastercaption varchar(255),
primary key (refid),
unique (starttimestamp, channelid)
) ENGINE=InnoDB
create table channel (
label varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
description varchar(255),
timezone varchar(255) not null,
maxdailyrequestlimitperuser integer,
url varchar(255),
tag varchar(255),
openregistration bit not null,
streamitem varchar(255) unique,
seatnumber bigint,
primary key (label)
) ENGINE=InnoDB
create table channeladministratorright (
channelid varchar(255) not null,
restricteduserid varchar(255) not null,
primary key (restricteduserid, channelid)
) ENGINE=InnoDB
create table channeljournal (
channelid varchar(255) not null,
logid varchar(255) not null,
primary key (channelid, logid)
) ENGINE=InnoDB
create table contributorright (
programmeid varchar(255) not null,
restricteduserid varchar(255) not null,
primary key (restricteduserid, programmeid)
) ENGINE=InnoDB
create table curatorright (
programmeid varchar(255) not null,
restricteduserid varchar(255) not null,
primary key (restricteduserid, programmeid)
) ENGINE=InnoDB
create table filter (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
slotid varchar(255),
include bit not null,
mixedfeature varchar(255),
contains varchar(255),
numericfeature varchar(255),
minimum smallint,
maximum smallint,
categoryfeature varchar(255),
primary key (refid)
) ENGINE=InnoDB
create table hit (
apikey varchar(255) not null,
timestamp bigint not null,
version integer not null,
primary key (apikey, timestamp)
) ENGINE=InnoDB
create table journal (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
logtimestamp bigint not null,
actor varchar(255),
actee varchar(255),
action varchar(255),
friendlyplaylistlabel varchar(255),
slottimestamp bigint,
channellabel varchar(255),
friendlytrackauthorlabel varchar(255),
friendlytracktitlelabel varchar(255),
archivetimestamp bigint,
reportstarttimestamp bigint,
reportendtimestamp bigint,
friendlyexceptionlabel varchar(255),
primary key (refid)
) ENGINE=InnoDB
create table listenerright (
channelid varchar(255) not null,
restricteduserid varchar(255) not null,
primary key (restricteduserid, channelid)
) ENGINE=InnoDB
create table notification (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
logtimestamp bigint not null,
actor varchar(255),
freetext varchar(255),
recipientnotified bit not null,
recipientid varchar(255) not null,
sourceid tinyblob,
destinationid tinyblob,
action varchar(255),
slottimestamp bigint,
channellabel varchar(255),
archivetimestamp bigint,
reportstarttimestamp bigint,
reportendtimestamp bigint,
queueitemtimestamp bigint,
entityid tinyblob,
friendlytrackauthorlabel varchar(255),
friendlytracktitlelabel varchar(255),
friendlyplaylistlabel varchar(255),
primary key (refid)
) ENGINE=InnoDB
create table people (
login varchar(255) not null,
type varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
realname varchar(255),
password varchar(255) not null,
email varchar(255) not null,
enabled bit not null,
timezone varchar(255) not null,
email_notification bit not null,
primary key (login)
) ENGINE=InnoDB
create table playlist (
refid varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
label varchar(255),
description varchar(255),
maxuserrequestlimit integer,
norequestreplaydelay bigint,
norequestreplayinplaylist bit not null,
ready bit not null,
picture_format varchar(255),
picture_size bigint,
programmeid varchar(255) not null,
live_broadcaster varchar(255),
live_login varchar(255),
live_password varchar(255),
advisory_age varchar(255),
advisory_violence bit,
advisory_profanity bit,
advisory_fear bit,
advisory_sex bit,
advisory_drugs bit,
advisory_discrimination bit,
author_tag varchar(255),
title_tag varchar(255),
album_tag varchar(255),
genre_tag varchar(255),
dateofrelease_tag smallint,
description_tag varchar(255),
copyright_tag varchar(255),
advisory_age_tag varchar(255),
advisory_violence_tag bit,
advisory_profanity_tag bit,
advisory_fear_tag bit,
advisory_sex_tag bit,
advisory_drugs_tag bit,
advisory_discrimination_tag bit,
primary key (refid)
) ENGINE=InnoDB
create table programme (
label varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
description varchar(255),
metainfoPattern varchar(255),
overlappingplaylistallowed bit not null,
primary key (label)
) ENGINE=InnoDB
create table programmechannel (
channelid varchar(255) not null,
programmeid varchar(255) not null,
primary key (channelid, programmeid)
) ENGINE=InnoDB
create table programmemanagerright (
channelid varchar(255) not null,
restricteduserid varchar(255) not null,
primary key (restricteduserid, channelid)
) ENGINE=InnoDB
create table queue (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
channelid varchar(255) not null,
sequenceindex bigint not null,
pooled bit not null,
likelystarttimestamp bigint not null,
likelyduration bigint not null,
timemarkeratend bigint,
playlistfadein integer,
timetableid varchar(255),
trackid varchar(255),
playlistsequenceindex bigint,
playlistloopindex bigint,
naturalduration bigint,
primary key (refid)
) ENGINE=InnoDB
create table report (
refid varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
starttimestamp bigint not null,
stoptimestamp bigint not null,
channelid varchar(255) not null,
text_format varchar(255),
text_size bigint,
ready bit not null,
primary key (refid),
unique (starttimestamp, stoptimestamp, channelid)
) ENGINE=InnoDB
create table request (
refid varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
songid varchar(255),
channelid varchar(255) not null,
requesterid varchar(255),
streamitem varchar(255) unique,
queueitem varchar(255) unique,
message varchar(255),
primary key (refid),
unique (songid, requesterid),
unique (songid, channelid)
) ENGINE=InnoDB
create table slot (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
playlistid varchar(255) not null,
sequenceindex bigint not null,
fadein integer,
repeatloop bigint,
request bit not null,
trackid varchar(255),
selection varchar(255),
sort varchar(255),
limitstart bigint,
limitend bigint,
noreplaydelay bigint,
noreplayinplaylist bit,
primary key (refid)
) ENGINE=InnoDB
create table stream (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
starttimestamp bigint not null,
likelyduration bigint not null,
queueid varchar(255),
trackid varchar(255),
primary key (refid)
) ENGINE=InnoDB
create table timetableslot (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
starttimestamp bigint not null,
channelid varchar(255) not null,
enabled bit not null,
streamitem varchar(255) unique,
programmeid varchar(255) not null,
playlistid varchar(255),
endtimestamp bigint,
decommissioningtimestamp bigint,
startmillisecondfromperiod bigint,
endmillisecondfromperiod bigint,
primary key (refid),
unique (starttimestamp, channelid)
) ENGINE=InnoDB
create table track (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
author varchar(255) not null,
title varchar(255) not null,
ready bit not null,
album varchar(255),
genre varchar(255),
dateofrelease smallint,
description varchar(255),
publisher varchar(255),
copyright varchar(255),
tag varchar(255),
advisory_age varchar(255),
advisory_violence bit,
advisory_profanity bit,
advisory_fear bit,
advisory_sex bit,
advisory_drugs bit,
advisory_discrimination bit,
audio_format varchar(255),
audio_size bigint,
audio_duration float,
audio_bitrate bigint,
audio_samplerate integer,
audio_channels tinyint,
audio_vbr bit,
picture_format varchar(255),
picture_size bigint,
enabled bit,
primary key (refid)
) ENGINE=InnoDB
create table trackprogramme (
refid varchar(255) not null,
type varchar(255) not null,
version integer not null,
rotation bigint,
trackid varchar(255) not null,
programmeid varchar(255) not null,
primary key (refid)
) ENGINE=InnoDB
create table voting (
refid varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
songid varchar(255) not null,
restricteduserid varchar(255) not null,
value smallint not null,
primary key (refid),
unique (songid, restricteduserid)
) ENGINE=InnoDB
create table webservice (
apikey varchar(255) not null,
version integer not null,
creation datetime not null,
creator varchar(255),
latestmodification datetime,
latesteditor varchar(255),
channelid varchar(255) not null,
maxfirerate bigint,
maxdailylimit bigint,
privatekey varchar(255) not null,
whitelistregexp varchar(255),
nowplayingmodule bit not null,
comingnextmodule bit not null,
timetablemodule bit not null,
votemodule bit not null,
archivemodule bit not null,
requestmodule bit not null,
primary key (apikey)
) ENGINE=InnoDB
alter table animatorright
add index FKBBD74F958810107F (programmeid),
add constraint FKBBD74F958810107F
foreign key (programmeid)
references programme (label)
alter table animatorright
add index FKBBD74F954F94DE48 (restricteduserid),
add constraint FKBBD74F954F94DE48
foreign key (restricteduserid)
references people (login)
alter table animatorright
add index FKBBD74F95B4418FAD (restricteduserid),
add constraint FKBBD74F95B4418FAD
foreign key (restricteduserid)
references people (login)
alter table archive
add index FKD368E0C2AC74AA78 (creator),
add constraint FKD368E0C2AC74AA78
foreign key (creator)
references people (login)
alter table archive
add index FKD368E0C29D8CDA00 (latesteditor),
add constraint FKD368E0C29D8CDA00
foreign key (latesteditor)
references people (login)
alter table archive
add index FKD368E0C2C528BF4 (channelid),
add constraint FKD368E0C2C528BF4
foreign key (channelid)
references channel (label)
on delete cascade
alter table channel
add index FK2C0B7D038659E265 (streamitem),
add constraint FK2C0B7D038659E265
foreign key (streamitem)
references stream (refid)
alter table channel
add index FK2C0B7D03AC74AA78 (creator),
add constraint FK2C0B7D03AC74AA78
foreign key (creator)
references people (login)
alter table channel
add index FK2C0B7D039D8CDA00 (latesteditor),
add constraint FK2C0B7D039D8CDA00
foreign key (latesteditor)
references people (login)
alter table channeladministratorright
add index FK6CB819B24F94DE48 (restricteduserid),
add constraint FK6CB819B24F94DE48
foreign key (restricteduserid)
references people (login)
alter table channeladministratorright
add index FK6CB819B2B4418FAD (restricteduserid),
add constraint FK6CB819B2B4418FAD
foreign key (restricteduserid)
references people (login)
alter table channeladministratorright
add index FK6CB819B2C528BF4 (channelid),
add constraint FK6CB819B2C528BF4
foreign key (channelid)
references channel (label)
alter table channeljournal
add index FK178618D49F4505EA (logid),
add constraint FK178618D49F4505EA
foreign key (logid)
references journal (refid)
alter table channeljournal
add index FK178618D4C528BF4 (channelid),
add constraint FK178618D4C528BF4
foreign key (channelid)
references channel (label)
alter table contributorright
add index FKE360B2218810107F (programmeid),
add constraint FKE360B2218810107F
foreign key (programmeid)
references programme (label)
alter table contributorright
add index FKE360B2214F94DE48 (restricteduserid),
add constraint FKE360B2214F94DE48
foreign key (restricteduserid)
references people (login)
alter table contributorright
add index FKE360B221B4418FAD (restricteduserid),
add constraint FKE360B221B4418FAD
foreign key (restricteduserid)
references people (login)
alter table curatorright
add index FKC7B754468810107F (programmeid),
add constraint FKC7B754468810107F
foreign key (programmeid)
references programme (label)
alter table curatorright
add index FKC7B754464F94DE48 (restricteduserid),
add constraint FKC7B754464F94DE48
foreign key (restricteduserid)
references people (login)
alter table curatorright
add index FKC7B75446B4418FAD (restricteduserid),
add constraint FKC7B75446B4418FAD
foreign key (restricteduserid)
references people (login)
alter table filter
add index FKB408CB7861F1E934 (slotid),
add constraint FKB408CB7861F1E934
foreign key (slotid)
references slot (refid)
on delete cascade
alter table hit
add index FK19393AFE52351 (apikey),
add constraint FK19393AFE52351
foreign key (apikey)
references webservice (apikey)
on delete cascade
alter table listenerright
add index FK24BDCDE84F94DE48 (restricteduserid),
add constraint FK24BDCDE84F94DE48
foreign key (restricteduserid)
references people (login)
alter table listenerright
add index FK24BDCDE8B4418FAD (restricteduserid),
add constraint FK24BDCDE8B4418FAD
foreign key (restricteduserid)
references people (login)
alter table listenerright
add index FK24BDCDE8C528BF4 (channelid),
add constraint FK24BDCDE8C528BF4
foreign key (channelid)
references channel (label)
alter table notification
add index FK237A88EBC318F0FB (recipientid),
add constraint FK237A88EBC318F0FB
foreign key (recipientid)
references people (login)
on delete cascade
alter table people
add index FKC4E2328FAC74AA78 (creator),
add constraint FKC4E2328FAC74AA78
foreign key (creator)
references people (login)
alter table people
add index FKC4E2328F9D8CDA00 (latesteditor),
add constraint FKC4E2328F9D8CDA00
foreign key (latesteditor)
references people (login)
alter table playlist
add index FK700681D28810107F (programmeid),
add constraint FK700681D28810107F
foreign key (programmeid)
references programme (label)
on delete cascade
alter table playlist
add index FK700681D2AC74AA78 (creator),
add constraint FK700681D2AC74AA78
foreign key (creator)
references people (login)
alter table playlist
add index FK700681D29D8CDA00 (latesteditor),
add constraint FK700681D29D8CDA00
foreign key (latesteditor)
references people (login)
alter table programme
add index FKC6419B1CAC74AA78 (creator),
add constraint FKC6419B1CAC74AA78
foreign key (creator)
references people (login)
alter table programme
add index FKC6419B1C9D8CDA00 (latesteditor),
add constraint FKC6419B1C9D8CDA00
foreign key (latesteditor)
references people (login)
alter table programmechannel
add index FKBD766A678810107F (programmeid),
add constraint FKBD766A678810107F
foreign key (programmeid)
references programme (label)
alter table programmechannel
add index FKBD766A67C528BF4 (channelid),
add constraint FKBD766A67C528BF4
foreign key (channelid)
references channel (label)
alter table programmemanagerright
add index FK223C47AB4F94DE48 (restricteduserid),
add constraint FK223C47AB4F94DE48
foreign key (restricteduserid)
references people (login)
alter table programmemanagerright
add index FK223C47ABB4418FAD (restricteduserid),
add constraint FK223C47ABB4418FAD
foreign key (restricteduserid)
references people (login)
alter table programmemanagerright
add index FK223C47ABC528BF4 (channelid),
add constraint FK223C47ABC528BF4
foreign key (channelid)
references channel (label)
alter table queue
add index FK66F1911250E382C (timetableid),
add constraint FK66F1911250E382C
foreign key (timetableid)
references timetableslot (refid)
on delete cascade
alter table queue
add index FK66F1911CFBDDFDB (trackid),
add constraint FK66F1911CFBDDFDB
foreign key (trackid)
references track (refid)
on delete cascade
alter table queue
add index FK66F1911C528BF4 (channelid),
add constraint FK66F1911C528BF4
foreign key (channelid)
references channel (label)
on delete cascade
alter table report
add index FKC84C5534AC74AA78 (creator),
add constraint FKC84C5534AC74AA78
foreign key (creator)
references people (login)
alter table report
add index FKC84C55349D8CDA00 (latesteditor),
add constraint FKC84C55349D8CDA00
foreign key (latesteditor)
references people (login)
alter table report
add index FKC84C5534C528BF4 (channelid),
add constraint FKC84C5534C528BF4
foreign key (channelid)
references channel (label)
alter table request
add index FK414EF28F7B5C3547 (streamitem),
add constraint FK414EF28F7B5C3547
foreign key (streamitem)
references stream (refid)
alter table request
add index FK414EF28FAC74AA78 (creator),
add constraint FK414EF28FAC74AA78
foreign key (creator)
references people (login)
alter table request
add index FK414EF28F9D8CDA00 (latesteditor),
add constraint FK414EF28F9D8CDA00
foreign key (latesteditor)
references people (login)
alter table request
add index FK414EF28F8960499E (requesterid),
add constraint FK414EF28F8960499E
foreign key (requesterid)
references people (login)
alter table request
add index FK414EF28F46EA69DB (songid),
add constraint FK414EF28F46EA69DB
foreign key (songid)
references track (refid)
alter table request
add index FK414EF28F85A9471A (songid),
add constraint FK414EF28F85A9471A
foreign key (songid)
references track (refid)
alter table request
add index FK414EF28F9547BDF7 (songid),
add constraint FK414EF28F9547BDF7
foreign key (songid)
references track (refid)
alter table request
add index FK414EF28FC528BF4 (channelid),
add constraint FK414EF28FC528BF4
foreign key (channelid)
references channel (label)
alter table request
add index FK414EF28FFA821798 (queueitem),
add constraint FK414EF28FFA821798
foreign key (queueitem)
references queue (refid)
alter table slot
add index FK35E9FE770C7D19 (playlistid),
add constraint FK35E9FE770C7D19
foreign key (playlistid)
references playlist (refid)
on delete cascade
alter table slot
add index FK35E9FECFBDDFDB (trackid),
add constraint FK35E9FECFBDDFDB
foreign key (trackid)
references track (refid)
on delete cascade
alter table stream
add index FKCAD54F80CFBDDFDB (trackid),
add constraint FKCAD54F80CFBDDFDB
foreign key (trackid)
references track (refid)
on delete cascade
alter table timetableslot
add index FK8B1D129F8659E265 (streamitem),
add constraint FK8B1D129F8659E265
foreign key (streamitem)
references stream (refid)
alter table timetableslot
add index FK8B1D129F8810107F (programmeid),
add constraint FK8B1D129F8810107F
foreign key (programmeid)
references programme (label)
on delete cascade
alter table timetableslot
add index FK8B1D129F770C7D19 (playlistid),
add constraint FK8B1D129F770C7D19
foreign key (playlistid)
references playlist (refid)
alter table timetableslot
add index FK8B1D129FAC74AA78 (creator),
add constraint FK8B1D129FAC74AA78
foreign key (creator)
references people (login)
alter table timetableslot
add index FK8B1D129F9D8CDA00 (latesteditor),
add constraint FK8B1D129F9D8CDA00
foreign key (latesteditor)
references people (login)
alter table timetableslot
add index FK8B1D129FC528BF4 (channelid),
add constraint FK8B1D129FC528BF4
foreign key (channelid)
references channel (label)
on delete cascade
alter table track
add index FK697F14BAC74AA78 (creator),
add constraint FK697F14BAC74AA78
foreign key (creator)
references people (login)
alter table track
add index FK697F14B9D8CDA00 (latesteditor),
add constraint FK697F14B9D8CDA00
foreign key (latesteditor)
references people (login)
alter table trackprogramme
add index FKEE8CEE318810107F (programmeid),
add constraint FKEE8CEE318810107F
foreign key (programmeid)
references programme (label)
alter table trackprogramme
add index FKEE8CEE3164D4190C (trackid),
add constraint FKEE8CEE3164D4190C
foreign key (trackid)
references track (refid)
alter table voting
add index FKCFAE5D27AC74AA78 (creator),
add constraint FKCFAE5D27AC74AA78
foreign key (creator)
references people (login)
alter table voting
add index FKCFAE5D279D8CDA00 (latesteditor),
add constraint FKCFAE5D279D8CDA00
foreign key (latesteditor)
references people (login)
alter table voting
add index FKCFAE5D274F94DE48 (restricteduserid),
add constraint FKCFAE5D274F94DE48
foreign key (restricteduserid)
references people (login)
on delete cascade
alter table voting
add index FKCFAE5D2746EA69DB (songid),
add constraint FKCFAE5D2746EA69DB
foreign key (songid)
references track (refid)
alter table voting
add index FKCFAE5D2785A9471A (songid),
add constraint FKCFAE5D2785A9471A
foreign key (songid)
references track (refid)
alter table voting
add index FKCFAE5D279547BDF7 (songid),
add constraint FKCFAE5D279547BDF7
foreign key (songid)
references track (refid)
alter table webservice
add index FK479A54E1AC74AA78 (creator),
add constraint FK479A54E1AC74AA78
foreign key (creator)
references people (login)
alter table webservice
add index FK479A54E19D8CDA00 (latesteditor),
add constraint FK479A54E19D8CDA00
foreign key (latesteditor)
references people (login)
alter table webservice
add index FK479A54E1C528BF4 (channelid),
add constraint FK479A54E1C528BF4
foreign key (channelid)
references channel (label)
on delete cascade
Updated