/*****************************************************************************/
/* @(#) generic/localization/scripts/iso_1/%M%	%I%	%G%	*/
/* installinterpubs - script to install the international pubs database      */
/*****************************************************************************/
/*******                                                               *******/
/*******CAUTION:  Do not edit this file with an editor like /usr/ucb/vi!! ****/
/*******          It will strip off the 8th bit and you will no longer    ****/
/*******          have any of the accented characters.                    ****/
/*******                                                                  ****/
/*******          If you have already begun to edit this file, you should ****/
/*******          quit without saving (:q! in vi); otherwise, the data    ****/
/*******          will not reflect any of the international characters.   ****/
/*******                                                                  ****/
/*****************************************************************************/

/*
** raiserror Messages for installintpubs [Total 3]
**
** 19527, "The '%1!' database cannot be created. Terminating the installation."
** 19528, "The '%1!' database does not exist. Terminating the installation."
** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
*/

set nocount on

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

if not exists (select name from master.dbo.sysdatabases 
		where name = "interpubs")
begin
	/*
	** 19527, "The '%1!' database cannot be created. Terminating the installation."
	*/
	raiserror 19527, "interpubs"
	select syb_quit()
end
go

sp_dboption interpubs, "trunc log on chkpt", true
go
set dateformat mdy
go
use interpubs
go
if (db_name() != "interpubs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "interpubs"
	select syb_quit()
end
go
checkpoint
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	execute sp_addtype id, "varchar(11)", "not null"
	execute sp_addtype tid, "varchar(6)", "not null"
end
else
begin
	/*
	** 19528, "The '%1!' database does not exist. Terminating the installation."
	*/
	raiserror 19528, "interpubs"
	select syb_quit()
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table authors
	(au_id id,
	au_lname varchar(40) not null,
	au_fname varchar(20) not null,
	phone char(14),
	address varchar(40) null,
	city varchar(20) null,
	state char(2) null,
	country varchar(12) null,
	postalcode char(10) null)
end
go
grant select on authors to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table titleauthor
	(au_id id,
	title_id tid,
	au_ord tinyint null,
	royaltyper int null)
end
go
grant select on titleauthor to public
go
create unique clustered index taind
on titleauthor (au_id, title_id)
go
create nonclustered index auidind
on titleauthor (au_id)
go
create nonclustered index titleidind
on titleauthor (title_id)
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table sales
	(stor_id char(4),
	ord_num varchar(20),
	date datetime,
	qty smallint,
	payterms varchar(12),
	title_id tid)
end
go
grant select on sales to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table titles
	(title_id tid,
	title varchar(80) not null,
	type char(12),
	price money null,
	advance money null,
	royalty int null,
	ytd_sales int null,
	notes varchar(255) null,
	pubdate datetime)
end
go
grant select on titles to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table stores
	(stor_id char(4),
	stor_name varchar(40) null,
	stor_address varchar(40) null,
	city varchar(20) null,
	state char(2) null,
	country varchar(12) null,
	postalcode char(10) null)
end
go
grant select on stores to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	create table discounts
	(discounttype   varchar(40) not null,
	stor_id         char(4) null,
	lowqty          smallint null,
	highqty         smallint null,
	discount        float)
end
go
grant select on discounts 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 sales, stor_id, title_id, ord_num
execute sp_primarykey stores, stor_id
execute sp_primarykey discounts, discounttype, stor_id  
go
execute sp_foreignkey titleauthor, titles, title_id
execute sp_foreignkey titleauthor, authors, au_id
execute sp_foreignkey sales, titles, title_id
execute sp_foreignkey sales, stores, stor_id
execute sp_foreignkey discounts, stores, stor_id
go
create unique clustered index auidind
on authors (au_id)
go
create nonclustered index aunmind
on authors (au_lname, au_fname)
go
create unique clustered index titleidind
on titles (title_id)
go
create nonclustered index titleind
on titles (title)
go
create nonclustered index titleidind
on sales (title_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', '94703')
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 Ave.', 'Salt Lake City', 'UT', 'USA', '84152')
go
insert authors
values('899-46-2035', 'Ringer', 'Anne',
'801 826-0752', '67 Seventh Ave.', 'Salt Lake City', 'UT', 'USA', '84152')
go
insert authors
values('020-83-5948', 'Dblin', 'Alfred',
'010 223 2455', '12, Goethestrae', 'Berlin', 'Berlin','BRD','P-1124')
go
insert authors
values('011-112-1211', 'Drrenmatt','Friedrich',
'011 255 29 24', '24, Schnplatz','Bern','Bern','Schweiz','S-114')
go
insert authors
values('018-913-9422', 'Bll', 'Heinrich',
'011 442 23 90', '18, Einsteingae', 'Bonn', 'Bonn', 'BRD', 'V-222')
go
insert authors 
values('080-92-2323', 'Borchert', 'Wolfgang',
'020 828 09 93', '24, Tringenstrae', 'Wien', 'Wien', 'sterreich', 'A-1253')
go
insert authors
values('011-35-8631', 'Dutschke', 'Rudi',
'399-09-8380', '811, Lindenstrae', 'Mnchen', 'Mnchen','BRD', 'B-8263')
go
insert authors
values('136-85-3857', 'Csaire', 'Aim',
'010 244 38 95','8, Rue de la Campagne','Nice','Nice','France','N-1382')
go
insert authors
values('852-22-3431', 'Cline', 'Louis-Ferdinand',
'010 344 24 28', '9, Avenue Foch', 'Paris', 'Paris', 'France', 'X-1124')
go
insert authors
values('258-82-1314', 'Cendrars', 'Blaise',
'023 392 29 40', '12, Rue de Gaulle', 'Lyons', 'Lyons', 'France', 'Y-1388')
go
insert authors
values('010-08-1946', 'Lvi-Strauss', 'Claude',
'099 998 23 84', '10, Avenue Saint Martin', 'Paris', 'Paris', 'France', 'P-2832')
go
insert authors
values('330-29-9328', 'Prvost', 'Antoine-Franois',
'011 203 83 27', "82, Rue de l'glise", 'Nancy', 'Nancy', 'France', 'N-3289')
go
insert authors
values('828-39-2382', 'Sgalen', 'Victor',
'011 210 28 21', '104, Avenue Saint Michel', 'Nernier','Haute-Savoie', 'France', 'H-1383')
go
insert authors
values('831-11-9825', 'Aym', 'Marcel',
'011 223 28 22', '112, Rue Micheline', 'Le Mans', 'Le Mans', 'France', 'A-1211')
go
insert authors
values('030-23-1432', 'Aris', 'Philippe',
'012 822 28 02', '84, Rue Charpentier', 'Paris', 'Paris', 'France', 'P-8224')
insert sales
values('7066', 'QA7442.3', '09/13/85', 75, 'On invoice','PS2091')
go
insert sales
values('7067', 'D4482', '09/14/85', 10, 'Net 60','PS2091')
go
insert sales
values('7131', 'N914008', '09/14/85', 20, 'Net 30','PS2091')
go
insert sales
values('7131', 'N914014', '09/14/85', 25, 'Net 30','MC3021')
go
insert sales
values('8042', '423LL922', '09/14/85', 15, 'On invoice','MC3021')
go
insert sales
values('8042', '423LL930', '09/14/85', 10, 'On invoice','BU1032')
go
insert sales
values('6380', '722a', '09/13/85', 3, 'Net 60','PS2091')
go
insert sales
values('6380', '6871', '09/14/85', 5, 'Net 60','BU1032')
go
insert sales 
values('8042','P723', '03/11/88', 25, 'Net 30', 'BU1111')
go
insert sales 
values('7896','X999', '02/21/88', 35, 'On invoice', 'BU2075')
go
insert sales 
values('7896','QQ2299', '10/28/87', 15, 'Net 60', 'BU7832')
go
insert sales 
values('7896','TQ456', '12/12/87', 10, 'Net 60', 'MC2222')
go
insert sales 
values('8042','QA879.1', '5/22/87', 30, 'Net 30', 'PC1035')
go
insert sales 
values('7066','A2976', '5/24/87', 50, 'Net 30', 'PC8888')
go
insert sales 
values('7131','P3087a', '5/29/87', 20, 'Net 60', 'PS1372')
go
insert sales 
values('7131','P3087a', '5/29/87', 25, 'Net 60', 'PS2106')
go
insert sales 
values('7131','P3087a', '5/29/87', 15, 'Net 60', 'PS3333')
go
insert sales 
values('7131','P3087a', '5/29/87', 25, 'Net 60', 'PS7777')
go
insert sales 
values('7067','P2121', '6/15/87', 40, 'Net 30', 'TC3218')
go
insert sales 
values('7067','P2121', '6/15/87', 20, 'Net 30', 'TC4203')
go
insert sales 
values('7067','P2121', '6/15/87', 20, 'Net 30', 'TC7777')
go
insert sales
values('7067','P2123', '6/20/87', 20, 'Net 30', 'HI1789')
go
insert sales
values('7067','P2123', '6/20/87', 10, 'Net 30', 'HI1897')
go
insert sales
values('7067','P2123', '6/20/87', 5, 'Net 30', 'HI1983')
go
insert sales
values('7067','P2123', '6/20/87', 50, 'Net 30', 'PH8103')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','MY1012')
go
insert sales
values('8042', '483LL821', '09/14/87', 20, 'On invoice','MY1011')
go
insert sales
values('8042', '483LL821', '09/14/87', 20, 'On invoice','MY8332')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','SO1832')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','HI1897')
go
insert sales
values('8042', '483LL821', '09/14/87', 10, 'On invoice','DR1818')
go
insert sales
values('7067', 'R4482', '10/17/87', 10, 'Net 60','PH1024')
go
insert sales
values('7067', 'R4482', '10/17/87', 10, 'Net 60','MY1012')
go
insert sales
values('7067', 'R4482', '10/17/87', 50, 'Net 60','PO1813')
go
insert sales
values('7067', 'R4482', '10/17/87', 20, 'Net 60','PH9823')
go
insert sales
values('7067', 'R4482', '10/17/87', 20, 'Net 60','MY1011')
go
insert titleauthor
values('409-56-7008', 'BU1032', 1, 60)
go
insert titleauthor
values('213-46-8915', 'BU1032', 2, 40)
go
insert titleauthor
values('238-95-7766', 'PC1035', 1, 100)
go
insert titleauthor
values('213-46-8915', 'BU2075', 1, 100)
go
insert titleauthor
values('998-72-3567', 'PS2091', 1, 50)
go
insert titleauthor
values('899-46-2035', 'PS2091', 2, 50)
go
insert titleauthor
values('998-72-3567', 'PS2106', 1, 100)
go
insert titleauthor
values('899-46-2035', 'MC3021', 2, 25)
go
insert titleauthor
values('020-83-5948', 'MY1011', 1, 100)
go
insert titleauthor
values('011-112-1211', 'MY1012', 1, 100)
go
insert titleauthor
values('018-913-9422', 'PH1024', 1, 100)
go
insert titleauthor
values('080-92-2323', 'DR1818', 1, 100)
go
insert titleauthor
values('011-35-8631', 'PO1813', 1, 100)
go
insert titleauthor
values('136-85-3857', 'DR0223', 1, 100)
go
insert titleauthor
values('852-22-3431', 'PH9823', 1, 100)
go
insert titleauthor
values('258-82-1314', 'HI1789', 1, 100)
go
insert titleauthor
values('010-08-1946', 'PH8103', 1, 100)
go
insert titleauthor
values('330-29-9328', 'HI1897', 1, 100)
go
insert titleauthor
values('828-39-2382', 'SO1832', 1, 100)
go
insert titleauthor
values('831-11-9825', 'MY8332', 1, 100)
go
insert titleauthor
values('030-23-1432', 'HI1983', 1, 100)
go
insert titles
values ('BU1032', "The Busy Executive's Database Guide",
'business', $19.99, $5000.00, 10, 4095,
"An overview of available database systems with emphasis on common business applications.  Illustrated.",
'06/12/85')
go
insert titles
values ('PC1035', 'But Is It User Friendly?',
'popular_comp', $22.95, $7000.00, 16, 8780,
"A survey of software for the naive user, focusing on the 'friendliness' of each.",
'06/30/85')
go
insert titles
values('BU2075', 'You Can Combat Computer Stress!',
'business', $2.99, $10125.00, 24, 18722,
'The latest medical and psychological techniques for living with the electronic office.  Easy-to-understand explanations.',
'06/30/85')
go
insert titles
values('PS2091', 'Is Anger the Enemy?',
'psychology', $10.95, $2275.00, 12, 2045,
'Carefully researched study of the effects of strong emotions on the body.  Metabolic charts included.',
'06/15/85')
go
insert titles
values('PS2106', 'Life Without Fear',
'psychology', $7.00, $6000.00, 10, 111,
'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience.  Sample menus included, exercise video available separately.',
'10/05/85')
go
insert titles
values('MC3021', 'The Gourmet Microwave',
'mod_cook', $2.99, $15000.00, 24, 22246,
'Traditional French gourmet recipes adapted for modern microwave cooking.',
'06/18/85')
go
insert titles
values('MY1011', 'Berlin Alexanderplatz',
'mystery', $8.95, $10000.00, 24, 22246,
'Die Geschichte des Transportarbeiters Franz Biberkopf, der erste deutsche Grostadtroman von literarischen Rang.',
'06/18/85')
go
insert titles
values('MY1012', 'Der Auftrag',
'mystery', $8.95, $10000.00, 24, 22329,
'Eine Kriminalstory, aber eine von der metaphysischen Sorte, ein Gedankengebude.',
'05/23/71')
go
insert titles 
values('PH1024', 'Vermintes Gelnde',
'philosophy', $12.95, $10000.00, 12, 12829, 
'Dieser jngste Band mit Schriften Heinrich Blls aus den Jahren 1977-1981 zeigt eine deutliche Verschiebung des Interesses von Literatur zur zeitkritischen Analyse und Stellungnahme.',
'03/21/82') 
go 
insert titles  
values('DR1818', 'Drauen vor der Tur', 
'drama', $18.95, $15000.00, 14, 52829,  
'Das einzige Drama des frh verstorbenen Dichters ist ein verzweifelter Protestschrei gegen die zerstrische und verderbnistrchtige Macht des Krieges.',
'12/18/85')  
go
insert titles   
values('PO1813', 'Die Revolte',  
'politics', $8.95, $5000.00, 10, 52923,   
'Dutschke denkt, da sich heute derjenige als Revolutionr begreifen m, der durch intellektuelle Arbeit und sinnvolle Erfahrung zu der Erkenntnis kommt:  Diese Gesellschaft kann und soll verndert werden.',
'2/8/82')   
go 
insert titles    
values('DR0223', 'Une tempte',  
'drama', $12.95, $7500.00, 12, 51823,    
"Adapte pour un thtre ngre, <<La Tempte>> de Shakespeare donne un relief accru aux rapports de Prospro et de Caliban; le matre est blanc, l'esclave est noir.",
'10/18/86')      
go  
insert titles     
values('PH9823', "D'un chteau l'autre",  
'philosophy', $22.95, $7500.00, 12, 81201,     
'Les chteaux dont parle le titre sont en effet douloureux, agits de spectres qui se nomment la guerre, la haine, la misre.',
'8/12/76')       
go  
insert titles
values('HI1789', "L'or",
'history', $7.95, $7500.00, 10, 8201,
'La merveilleuse histoire du gnral Johann August Suter.',
'5/10/70')
go    
insert titles 
values('PH8103', 'La pense sauvage', 
'philosophy', $27.95, $17500.00, 12, 18201, 
"La pense sauvage a trouv la matire et l'inspiration d'une logique dont les lois se bornent  transposer les proprits du rel, et qui, pour cette raison mme, a pu permettre aux hommes d'avoir prise sur lui.",
'10/20/86') 
go     
insert titles  
values('HI1897', 'Manon Lescaut',  
'history', $12.95, $7500.00, 12, 1800,  
'Histoire du chevalier des Grieux.',
'10/12/70')  
go      
insert titles   
values('HI1983', "L'homme devant la mort",   
'history', $32.95, $15000.00, 12, 2800,   
"Aris a contribu magistralement au renouvellement de l'historiographie franaise.",
'3/22/87')   
go       
insert titles    
values('SO1832', 'Les immmoriaux',    
'sociology', $22.95, $5000.00, 10, 8110,    
'Le seul ouvrage sur les les du Pacifique.  Le rcit du drame que provoque le choc de deux civilisations.',
'4/12/85')    
go        
insert titles     
values('MY8332', 'Le moulin de la Sourdine',    
'mystery', $7.95, $5000.00, 12, 8800,     
'Une crime rvoltant vien de mettre en moi la population de notre paisible cit.',
'2/2/82')     
go         
dump transaction interpubs with truncate_only
go
insert stores
values('7066',"Barnum's",'567 Pasadena Ave.','Tustin','CA', 'USA','92789')
go
insert stores
values('7067','News & Brews','577 First St.','Los Gatos','CA','USA','96745')
go
insert stores
values('7131','Doc-U-Mat: Quality Laundry and Books','24-A Avrogado Way','Remulade','WA','USA','98014')
go
insert stores
values('8042','Bookbeat','679 Carson St.','Portland','OR','USA','89076')
go
insert stores
values('6380',"Eric the Read Books",'788 Catamaugus Ave.','Seattle','WA','USA','98056')
go
insert stores
values('7896','Fricative Bookshop','89 Madison St.','Fremont','CA','USA','90019')
go
insert discounts
values('Initial Customer', NULL, NULL, NULL, 10.5)
go
insert discounts 
values('Volume Discount', NULL, 100, 1000, 6.7)
go
insert discounts 
values('Customer Discount', '8042', NULL, NULL, 5.0)
go
create trigger deltitle
on titles
for delete
as
if (select count(*) from deleted, sales
where sales.title_id = deleted.title_id) >0
begin
	rollback transaction
	print "You can't delete a title with sales."
end
go
create view titleview
as
select title, au_ord, au_lname,
price, ytd_sales
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
and titles.title_id = titleauthor.title_id
go
create procedure byroyalty @percentage int
as
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
go
grant execute on byroyalty to public
go
grant create procedure to public
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	execute sp_adduser guest
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "interpubs")
begin
	grant all on titles to guest
	grant all on authors to guest
	grant all on titleauthor to guest
	grant all on sales to guest
	grant all on stores to guest
	grant all on discounts to guest
	grant exec on byroyalty to guest
	grant create table to guest
	grant create view to guest
	grant create rule to guest
	grant create default to guest
	grant create procedure to guest
end
go
if exists (select * from master.dbo.sysdatabases
		where name = "sybsystemprocs")
begin
	use sybsystemprocs
end
else
begin
	/*
	** 19528, "The '%1!' database does not exist. Terminating the installation."
	*/
	raiserror 19528, "sybsystemprocs"
	select syb_quit()
end
go
if (db_name() != "sybsystemprocs")
begin
	/*
	** 19529, "Cannot open database '%1!'. Check the availability of this database and retry the installation."
	*/
	raiserror 19529, "sybsystemprocs"
	select syb_quit()
end
go
grant exec on sp_bindefault to guest
grant exec on sp_unbindefault to guest
grant exec on sp_bindrule to guest
grant exec on sp_unbindrule to guest
grant exec on sp_addtype to guest
grant exec on sp_droptype to guest
grant exec on sp_spaceused to guest
grant exec on sp_help to guest
grant exec on sp_helpgroup to guest
grant exec on sp_helpindex to guest
grant exec on sp_helprotect to guest
go
dump transaction interpubs with truncate_only
go
