installunipubs2

sybase数据库ct library的开发,使用了所以有函数

/* installunipubs2 - scripts to install the unichar16 pubs2 database */
/* unipubs2 which is used for running open client unichar16 examples */

/* (6/15/00 created by steng) modified installpubs2 for installunipubs2 */
/* installunipubs2 1.0 6/15/00 */
/* change some char/varchar to unichar/univarchar from installpubs2 */
use master
go
set nocount on

if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	drop database unipubs2
end
go
print 'Creating the "unipubs2" database'

if (@@maxpagesize = 1024 * 2)
	create database unipubs2 on default = 3
else
	create database unipubs2 on default
go

set dateformat mdy
go
sp_dboption unipubs2, "trunc log on chkpt", true
go
use unipubs2
go
checkpoint
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	execute sp_addtype id, "varchar(11)", "not null"
	execute sp_addtype tid, "varchar(6)", "not null"
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table authors
	(au_id id not null,
	au_lname univarchar(40) not null,
	au_fname univarchar(20) not null,
	phone unichar(12) not null,
	address univarchar(40) null,
	city univarchar(20) null,
	state unichar(2) null,
	country univarchar(12) null,
	postalcode unichar(10) null)
end
go
grant select on authors to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table publishers
	(pub_id char(4) not null,
	pub_name univarchar(40) null,
	city univarchar(20) null,
	state unichar(2) null)
end
go
grant select on publishers to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table roysched
	(title_id tid not null,
	lorange int null,
	hirange int null,
	royalty int null)
end
go
grant select on roysched to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table sales
	(stor_id char(4) not null,
	ord_num varchar(20) not null,
	date datetime not null)
end
go
grant select on sales to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table salesdetail
	(stor_id char(4) not null,
	ord_num varchar(20) not null,
	title_id tid not null,
	qty smallint not null,
	discount float not null)
end
go
grant select on salesdetail to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table titleauthor
	(au_id id not null,
	title_id tid not null,
	au_ord tinyint null,
	royaltyper int null)
end
go
grant select on titleauthor to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table titles
	(title_id tid not null,
	title univarchar(80) not null,
	type unichar(12) not null,
	pub_id char(4) null,
	price money null,
	advance money null,
	total_sales int null,
	notes univarchar(200) null,
	pubdate datetime not null,
	contract bit not null )
end
go
grant select on titles to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table stores
	(stor_id char(4) not null,
	stor_name univarchar(40) null,
	stor_address univarchar(40) null,
	city univarchar(20) null,
	state unichar(2) null,
	country univarchar(12) null,
	postalcode unichar(10) null,
	payterms univarchar(12) null)
end
go
grant select on stores to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "unipubs2")
begin
	create table discounts
	(discounttype   varchar(40) not null,
	stor_id         char(4) null,
	lowqty          smallint null,
	highqty         smallint null,
	discount        float not null)
end
go
grant select on discounts to public
go
if exists (select * from master.dbo.sysdatabases
                where name = "unipubs2")
begin
	create table au_pix
	(au_id		char(11) not null,
	pic		image null,
	format_type	unichar(11) null,
	bytesize	int null,
	pixwidth_hor	unichar(14) null,
	pixwidth_vert	unichar(14) null)
end
go
grant select on au_pix to public
go
if exists (select * from master.dbo.sysdatabases 
                where name = "unipubs2") 
begin 
	create table blurbs
	(au_id	id not null,
	 copy	text null)
end
go 
grant select on blurbs to public 
go 
execute sp_primarykey titles, title_id
execute sp_primarykey titleauthor, au_id, title_id
execute sp_primarykey authors, au_id
execute sp_primarykey publishers, pub_id
execute sp_primarykey roysched, title_id
execute sp_primarykey sales, stor_id, ord_num
execute sp_primarykey salesdetail, stor_id, ord_num, title_id
execute sp_primarykey stores, stor_id
execute sp_primarykey discounts, discounttype, stor_id	 /* problem? */
execute sp_primarykey au_pix, au_id
execute sp_primarykey blurbs, au_id
go
execute sp_foreignkey titleauthor, titles, title_id
execute sp_foreignkey titleauthor, authors, au_id
execute sp_foreignkey roysched, titles, title_id
execute sp_foreignkey sales, stores, stor_id
execute sp_foreignkey salesdetail, titles, title_id
execute sp_foreignkey salesdetail, sales, stor_id, ord_num
execute sp_foreignkey titles, publishers, pub_id
execute sp_foreignkey discounts, stores, stor_id
execute sp_foreignkey au_pix, authors, au_id
execute sp_foreignkey blurbs, authors, au_id
go
create default typedflt
as "UNDECIDED"
go
sp_bindefault typedflt, "titles.type"
go
create default datedflt
as getdate()
go
sp_bindefault datedflt, "titles.pubdate"
go
create default phonedflt
as "UNKNOWN"
go
sp_bindefault phonedflt, "authors.phone"
go
insert authors
values('409-56-7008', 'Bennet', 'Abraham',
'415 658-9932', '6223 Bateman St.', 'Berkeley', 'CA', 'USA', '94705')
go
insert authors
values ('213-46-8915', 'Green', 'Marjorie',
'415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', 'USA', '94618')
go
insert authors
values('238-95-7766', 'Carson', 'Cheryl',
'415 548-7723', '589 Darwin Ln.', 'Berkeley', 'CA', 'USA', '94705')
go
insert authors
values('998-72-3567', 'Ringer', 'Albert',
'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', 'USA', '84152')
go
insert authors
values('899-46-2035', 'Ringer', 'Anne',
'801 826-0752', '67 Seventh Av.', 'Salt Lake City', 'UT', 'USA', '84152')
go
insert authors
values('722-51-5454', 'DeFrance', 'Michel',
'219 547-9982', '3 Balding Pl.', 'Gary', 'IN', 'USA', '46403')
go
insert authors
values('807-91-6654', 'Panteley', 'Sylvia',
'301 946-8853', '1956 Arlington Pl.', 'Rockville', 'MD', 'USA', '20853')
go
insert authors
values('893-72-1158', 'McBadden', 'Heather',
'707 448-4982', '301 Putnam', 'Vacaville', 'CA', 'USA', '95688')
go
insert authors
values('724-08-9931', 'Stringer', 'Dirk',
'415 843-2991', '5420 Telegraph Av.', 'Oakland', 'CA', 'USA', '94609')
go
insert authors
values('274-80-9391', 'Straight', 'Dick',
'415 834-2919', '5420 College Av.', 'Oakland', 'CA', 'USA', '94609')
go
insert authors
values('756-30-7391', 'Karsen', 'Livia',
'415 534-9219', '5720 McAuley St.', 'Oakland', 'CA', 'USA', '94609')
go
insert authors
values('724-80-9391', 'MacFeather', 'Stearns',
'415 354-7128', '44 Upland Hts.', 'Oakland', 'CA', 'USA', '94612')
go
insert authors
values('427-17-2319', 'Dull', 'Ann',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', 'USA', '94301')
go
insert authors
values('672-71-3249', 'Yokomoto', 'Akiko',
'415 935-4228', '3 Silver Ct.', 'Walnut Creek', 'CA', 'USA', '94595')
go
insert authors
values('267-41-2394', "O'Leary", 'Michael',
'408 286-2428', '22 Cleveland Av. #14', 'San Jose', 'CA', 'USA', '95128')
go
insert authors
values('472-27-2349', 'Gringlesby', 'Burt',
'707 938-6445', 'PO Box 792', 'Covelo', 'CA', 'USA', '95428')
go
insert authors
values('527-72-3246', 'Greene', 'Morningstar',
'615 297-2723', '22 Graybar House Rd.', 'Nashville', 'TN', 'USA', '37215')
go
insert authors
values('172-32-1176', 'White', 'Johnson',
'408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', 'USA', '94025')
go
insert authors
values('712-45-1867', 'del Castillo', 'Innes',
'615 996-8275', '2286 Cram Pl. #86', 'Ann Arbor', 'MI', 'USA', '48105')
go
insert authors
values('846-92-7186', 'Hunter', 'Sheryl',
'415 836-7128', '3410 Blonde St.', 'Palo Alto', 'CA', 'USA', '94301')
go
insert authors
values('486-29-1786', 'Locksley', 'Chastity',
'415 585-4620', '18 Broadway Av.', 'San Francisco', 'CA', 'USA', '94130')
go
insert authors
values('648-92-1872', 'Blotchet-Halls', 'Reginald',
'503 745-6402', '55 Hillsdale Bl.', 'Corvallis', 'OR', 'USA', '97330')
go
insert authors
values('341-22-1782', 'Smith', 'Meander',
'913 843-0462', '10 Mississippi Dr.', 'Lawrence', 'KS', 'USA', '66044')
go
insert publishers
values('0736', 'New Age Books', 'Boston', 'MA')
go
insert publishers
values('0877', 'Binnet & Hardley', 'Washington', 'DC')
go
insert publishers
values('1389', 'Algodata Infosystems', 'Berkeley', 'CA')
go
insert roysched
values('BU1032', 0, 5000, 10)
go
insert roysched
values('BU1032', 5001, 50000, 12)
go
insert roysched
values('PC1035', 0, 2000, 10)
go
insert roysched
values('PC1035', 2001, 3000, 12)
go
insert roysched
values('PC1035', 3001, 4000, 14)
go
insert roysched
values('PC1035', 4001, 10000, 16)
go
insert roysched
values('PC1035', 10001, 50000, 18)
go
insert roysched
values('BU2075', 0, 1000, 10)
go
insert roysched
values('BU2075', 1001, 3000, 12)
go
insert roysched
values('BU2075', 3001, 5000, 14)
go
insert roysched
values('BU2075', 5001, 7000, 16)
go
insert roysched
values('BU2075', 7001, 10000, 18)
go
insert roysched
values('BU2075', 10001, 12000, 20)
go
insert roysched
values('BU2075', 12001, 14000, 22)
go
insert roysched
values('BU2075', 14001, 50000, 24)
go
insert roysched
values('PS2091', 0, 1000, 10)
go
insert roysched
values('PS2091', 1001, 5000, 12)
go
insert roysched
values('PS2091', 5001, 10000, 14)
go
insert roysched
values('PS2091', 10001, 50000, 16)
go
insert roysched
values('PS2106', 0, 2000, 10)
go
insert roysched
values('PS2106', 2001, 5000, 12)
go
insert roysched
values('PS2106', 5001, 10000, 14)
go
insert roysched
values('PS2106', 10001, 50000, 16)
go
insert roysched
values('MC3021', 0, 1000, 10)
go
insert roysched
values('MC3021', 1001, 2000, 12)
go
insert roysched
values('MC3021', 2001, 4000, 14)
go
insert roysched
values('MC3021', 4001, 6000, 16)
go
insert roysched
values('MC3021', 6001, 8000, 18)
go
insert roysched
values('MC3021', 8001, 10000, 20)
go
insert roysched
values('MC3021', 10001, 12000, 22)
go
insert roysched
values('MC3021', 12001, 50000, 24)
go
insert roysched
values('TC3218', 0, 2000, 10)
go
insert roysched
values('TC3218', 2001, 4000, 12)
go
insert roysched
values('TC3218', 4001, 6000, 14)
go
insert roysched
values('TC3218', 6001, 8000, 16)
go
insert roysched
values('TC3218', 8001, 10000, 18)
go
insert roysched
values('TC3218', 10001, 12000, 20)
go
insert roysched
values('TC3218', 12001, 14000, 22)
go
insert roysched
values('TC3218', 14001, 50000, 24)
go
insert roysched
values('PC8888', 0, 5000, 10)
go
insert roysched
values('PC8888', 5001, 10000, 12)
go
insert roysched
values('PC8888', 10001, 15000, 14)
go
insert roysched
values('PC8888', 15001, 50000, 16)
go
insert roysched
values('PS7777', 0, 5000, 10)
go
insert roysched
values('PS7777', 5001, 50000, 12)
go
insert roysched
values('PS3333', 0, 5000, 10)
go
insert roysched
values('PS3333', 5001, 10000, 12)
go
insert roysched
values('PS3333', 10001, 15000, 14)
go
insert roysched
values('PS3333', 15001, 50000, 16)
go
insert roysched
values('BU1111', 0, 4000, 10)
go
insert roysched
values('BU1111', 4001, 8000, 12)
go
insert roysched
values('BU1111', 8001, 10000, 14)
go