dbschema.sql

来自「Sun公司Dream项目」· SQL 代码 · 共 150 行

create table mastersubscribers (
masterid	number,
login		varchar(16),
pin		varchar(16),
firstname	varchar(32),
lastname	varchar(32),
address		varchar(64),
city		varchar(32),
zip		varchar(16),
email		varchar(32),
phone		varchar(16)
);

create table subsubscribers (
masterid 	number,
id		integer,
login		varchar(16),
pin		varchar(16),
firstname	varchar(32),
lastname	varchar(32)
);

create table ratings (
id		integer,
name		varchar(16),
descr		varchar(255)
);

create table moviegenres (
id		integer,
genre		varchar(32),
assetcount	integer
);

create table moviecatalog (
id		number,
lysisid		varchar(32),
fulltitle	varchar(255),
origtitle	varchar(255),
shorttitle	varchar(255),
shortdescr	varchar(255),
longdescr	varchar(255),
version		integer,
genre		integer,
prodcompany	varchar(255),
proddate	date,
releasedate	varchar(32),
country		varchar(32),
screenformat	integer,
showing		varchar(32),
boxofficerating	integer,
parentrating	integer,
duration	integer,
color		integer,
stereo		integer,
surround	integer,
dolby		integer,
dvbcontent	varchar(255),
startdate	date,
enddate		date,
stdprice	number,
minprice	number,
movieurl	varchar(255),
posterurl	varchar(255)
);

create table moviedetail (
movieid		number,
detailtype	integer,
detail		varchar(255)
);
// where detailtype is 1 actor, 2 producer, 3 director, 4 composer, 5 imageurl, 6 trailerurl

create table subscrstb (
id		varchar(32),
subscrid	number,
type		integer,
macaddress	varchar(17),
ipaddress	varchar(15),
regionalhe	varchar(32)
);

create table stbtypes (
id		integer,
brand		varchar(32),
type		varchar(32)
);

create table subscrsessions (
id		number,
subscrid	number,
starttimest	timestamp,
lastactivityts	timestamp,
currentmovieid	number,
bookmark	varchar(255)
);

create table subscracct (
subscrid	number,
balance		number
);

create table billingcdr (
subscrid	number,
movieid		number,
purchasedate	timestamp,
movieprice	number
);

create table subscrgenres (
subscrid	number,
genreid		integer
);

create table subscrentals (
subscrid	number,
movieid		number,
starttimest	timestamp,
elapsedviewtime	number,
bookmark	varchar(255)
);

create table auditlog (
timest		timestamp,
subscrid	number,
action		varchar(255),
params		varchar(255),
errorstatus	varchar(255)
);

create table qamresources (
cpeid		varchar(32),
qamip		varchar(32),
qamport		integer,
tsid		varchar(8),
rfchannel	varchar(8),
timest		timestamp,
free		integer
);