-- MySQL commands to populate the kplug-library database, with test data
-- vim:ts=4:syn=sql
-- john h. robinson, iv 
-- $Id: psql_data.txt,v 1.3 2002/01/22 06:34:30 jaqque Exp $

truncate Addresses;
insert into Addresses (street, city, country, zip) values
	('111 Main St', '1town', '1US', '11111');
insert into Addresses (street, city, country, zip) values
	('222 Main St', '2town', '2US', '22222');
insert into Addresses (street, city, country, zip) values
	('333 Main St', '3town', '3US', '33333');
insert into Addresses (street, city, country, zip) values
	('444 Main St', '4town', '4US', '44444');
insert into Addresses (street, city, country, zip) values
	('555 Main St', '5town', '5US', '55555');

truncate AddressTypes;
insert into AddressTypes (type) values
	('1-Home(addr)');
insert into AddressTypes (type) values
	('2-Work(addr)');
insert into AddressTypes (type) values
	('3-Play(addr)');
insert into AddressTypes (type) values
	('4-Other(addr)');
insert into AddressTypes (type) values
	('5-Misc(addr)');

truncate Emails;
insert into Emails (name,domain) values
	('1','1.1');
insert into Emails (name,domain) values
	('2','2.2');
insert into Emails (name,domain) values
	('3','3.3');
insert into Emails (name,domain) values
	('4','4.4');
insert into Emails (name,domain) values
	('5','5.5');

truncate EmailTypes;
insert into EmailTypes (type) values
	('1-Home(email)');
insert into EmailTypes (type) values
	('2-Work(email)');
insert into EmailTypes (type) values
	('3-Play(email)');
insert into EmailTypes (type) values
	('4-Other(email)');
insert into EmailTypes (type) values
	('5-Misc(email)');

truncate Offices;
insert into Offices (title) values
	('1-President');
insert into Offices (title) values
	('2-Treasurer');
insert into Offices (title) values
	('3-Librarian');
insert into Offices (title) values
	('4-Vice President');
insert into Offices (title) values
	('5-Public Relations');

truncate Phones;
insert into Phones (country_code,city_code,line) values
	('1','1','1');
insert into Phones (country_code,city_code,line) values
	('2','2','2');
insert into Phones (country_code,city_code,line) values
	('3','3','3');
insert into Phones (country_code,city_code,line) values
	('4','4','4');
insert into Phones (country_code,city_code,line) values
	('5','5','5');

truncate PhoneTypes;
insert into PhoneTypes (type) values
	('1-Home(phone)');
insert into PhoneTypes (type) values
	('2-Work(phone)');
insert into PhoneTypes (type) values
	('3-Play(phone)');
insert into PhoneTypes (type) values
	('4-Other(phone)');
insert into PhoneTypes (type) values
	('5-Misc(phone)');

truncate Prefixes;
insert into Prefixes (prefix) values
	('1- (prefix)');
insert into Prefixes (prefix) values
	('2-Mr.(prefix)');
insert into Prefixes (prefix) values
	('3-Ms.(prefix)');
insert into Prefixes (prefix) values
	('4-Mrs.(prefix)');
insert into Prefixes (prefix) values
	('5-Miss(prefix)');

truncate Publishers;
insert into Publishers (name) values
	('1 Publisher');
insert into Publishers (name) values
	('2 Publisher');
insert into Publishers (name) values
	('3 Publisher');
insert into Publishers (name) values
	('4 Publisher');
insert into Publishers (name) values
	('5 Publisher');

truncate Suffixes;
insert into Suffixes (suffix) values
	('1- (suffix)');
insert into Suffixes (suffix) values
	('2-Jr.(suffix)');
insert into Suffixes (suffix) values
	('3-II(suffix)');
insert into Suffixes (suffix) values
	('4-III(suffix)');
insert into Suffixes (suffix) values
	('5-IV(suffix)');

truncate TokenTypes;
insert into TokenTypes (type) values
	('1-password(token)');
insert into TokenTypes (type) values
	('2-gpg(token)');
insert into TokenTypes (type) values
	('3-pgp(token)');
insert into TokenTypes (type) values
	('4-email(token)');
insert into TokenTypes (type) values
	('5-other(token)');

truncate People;
insert into People (prefix,f_name,m_name,l_name,suffix) select
	p.id, '1f-name','1-m-name','1-l-name',s.id from Prefixes p, Suffixes s
	where p.prefix like '1%' and s.suffix like '1%';
insert into People (prefix,f_name,m_name,l_name,suffix) select
	p.id, '2f-name','2-m-name','2-l-name',s.id from Prefixes p, Suffixes s
	where p.prefix like '2%' and s.suffix like '2%';
insert into People (prefix,f_name,m_name,l_name,suffix) select
	p.id, '3f-name','3-m-name','3-l-name',s.id from Prefixes p, Suffixes s
	where p.prefix like '3%' and s.suffix like '3%';
insert into People (prefix,f_name,m_name,l_name,suffix) select
	p.id, '4f-name','4-m-name','4-l-name',s.id from Prefixes p, Suffixes s
	where p.prefix like '4%' and s.suffix like '4%';
insert into People (prefix,f_name,m_name,l_name,suffix) select
	p.id, '5f-name','5-m-name','5-l-name',s.id from Prefixes p, Suffixes s
	where p.prefix like '5%' and s.suffix like '5%';

truncate Books;
insert into Books (title,author,publisher,published,ISBN,value) select
	'1-title','1-author',p.id,NULL,'1-isbn','1.11' from Publishers p
	where p.name like '1%';
insert into Books (title,author,publisher,published,ISBN,value) select
	'2-title','2-author',p.id,NULL,'2-isbn','2.22' from Publishers p
	where p.name like '2%';
insert into Books (title,author,publisher,published,ISBN,value) select
	'3-title','3-author',p.id,NULL,'3-isbn','3.33' from Publishers p
	where p.name like '3%';
insert into Books (title,author,publisher,published,ISBN,value) select
	'4-title','4-author',p.id,NULL,'4-isbn','4.44' from Publishers p
	where p.name like '4%';
insert into Books (title,author,publisher,published,ISBN,value) select
	'5-title','5-author',p.id,NULL,'5-isbn','5.55' from Publishers p
	where p.name like '5%';

truncate BookReviews;
insert into BookReviews (book,reviewer,review) select
	b.id,p.id,'1-review' from Books b, People p
	where b.title like '1%' and p.f_name like '1%';
insert into BookReviews (book,reviewer,review) select
	b.id,p.id,'2-review' from Books b, People p
	where b.title like '2%' and p.f_name like '2%';
insert into BookReviews (book,reviewer,review) select
	b.id,p.id,'3-review' from Books b, People p
	where b.title like '3%' and p.f_name like '3%';
insert into BookReviews (book,reviewer,review) select
	b.id,p.id,'4-review' from Books b, People p
	where b.title like '4%' and p.f_name like '4%';
insert into BookReviews (book,reviewer,review) select
	b.id,p.id,'5-review' from Books b, People p
	where b.title like '5%' and p.f_name like '5%';

truncate Officers;
insert into Officers (office,person,date_in,date_out) select
	o.id,p.id,NULL,NULL from Offices o, People p
	where o.title like '1%' and p.f_name like '1%';
insert into Officers (office,person,date_in,date_out) select
	o.id,p.id,NULL,NULL from Offices o, People p
	where o.title like '2%' and p.f_name like '2%';
insert into Officers (office,person,date_in,date_out) select
	o.id,p.id,NULL,NULL from Offices o, People p
	where o.title like '3%' and p.f_name like '3%';
insert into Officers (office,person,date_in,date_out) select
	o.id,p.id,NULL,NULL from Offices o, People p
	where o.title like '4%' and p.f_name like '4%';
insert into Officers (office,person,date_in,date_out) select
	o.id,p.id,NULL,NULL from Offices o, People p
	where o.title like '5%' and p.f_name like '5%';

truncate People_Addresses;
insert into People_Addresses (person,address,type) select
	p.id,a.id,t.id from People p, Addresses a, AddressTypes t
	where p.f_name like '1%' and a.street like '1%' and t.type like '1%';
insert into People_Addresses (person,address,type) select
	p.id,a.id,t.id from People p, Addresses a, AddressTypes t
	where p.f_name like '2%' and a.street like '2%' and t.type like '2%';
insert into People_Addresses (person,address,type) select
	p.id,a.id,t.id from People p, Addresses a, AddressTypes t
	where p.f_name like '3%' and a.street like '3%' and t.type like '3%';
insert into People_Addresses (person,address,type) select
	p.id,a.id,t.id from People p, Addresses a, AddressTypes t
	where p.f_name like '4%' and a.street like '4%' and t.type like '4%';
insert into People_Addresses (person,address,type) select
	p.id,a.id,t.id from People p, Addresses a, AddressTypes t
	where p.f_name like '5%' and a.street like '5%' and t.type like '5%';

truncate People_Emails;
insert into People_Emails (person,email,type) select
	p.id,e.id,t.id from People p, Emails e, EmailTypes t
	where p.f_name like '1%' and e.name like '1%' and t.type like '1%';
insert into People_Emails (person,email,type) select
	p.id,e.id,t.id from People p, Emails e, EmailTypes t
	where p.f_name like '2%' and e.name like '2%' and t.type like '2%';
insert into People_Emails (person,email,type) select
	p.id,e.id,t.id from People p, Emails e, EmailTypes t
	where p.f_name like '3%' and e.name like '3%' and t.type like '3%';
insert into People_Emails (person,email,type) select
	p.id,e.id,t.id from People p, Emails e, EmailTypes t
	where p.f_name like '4%' and e.name like '4%' and t.type like '4%';
insert into People_Emails (person,email,type) select
	p.id,e.id,t.id from People p, Emails e, EmailTypes t
	where p.f_name like '5%' and e.name like '5%' and t.type like '5%';

truncate People_Phones;
insert into People_Phones (person,phone) select
	p.id,n.id from People p, Phones n
	where p.f_name like '1%' and n.line like '1%';
insert into People_Phones (person,phone) select
	p.id,n.id from People p, Phones n
	where p.f_name like '2%' and n.line like '2%';
insert into People_Phones (person,phone) select
	p.id,n.id from People p, Phones n
	where p.f_name like '3%' and n.line like '3%';
insert into People_Phones (person,phone) select
	p.id,n.id from People p, Phones n
	where p.f_name like '4%' and n.line like '4%';
insert into People_Phones (person,phone) select
	p.id,n.id from People p, Phones n
	where p.f_name like '5%' and n.line like '5%';

truncate Publisher_Addresses;
insert into Publisher_Addresses (publisher,address) select
	p.id,a.id from Publishers p, Addresses a
	where p.name like '1%' and a.street like '1%';
insert into Publisher_Addresses (publisher,address) select
	p.id,a.id from Publishers p, Addresses a
	where p.name like '2%' and a.street like '2%';
insert into Publisher_Addresses (publisher,address) select
	p.id,a.id from Publishers p, Addresses a
	where p.name like '3%' and a.street like '3%';
insert into Publisher_Addresses (publisher,address) select
	p.id,a.id from Publishers p, Addresses a
	where p.name like '4%' and a.street like '4%';
insert into Publisher_Addresses (publisher,address) select
	p.id,a.id from Publishers p, Addresses a
	where p.name like '5%' and a.street like '5%';

truncate Publishers_Phones;
insert into Publishers_Phones (publisher,phone) select
	p.id,n.id from Publishers p, Phones n
	where p.name like '1%' and n.line like '1%';
insert into Publishers_Phones (publisher,phone) select
	p.id,n.id from Publishers p, Phones n
	where p.name like '2%' and n.line like '2%';
insert into Publishers_Phones (publisher,phone) select
	p.id,n.id from Publishers p, Phones n
	where p.name like '3%' and n.line like '3%';
insert into Publishers_Phones (publisher,phone) select
	p.id,n.id from Publishers p, Phones n
	where p.name like '4%' and n.line like '4%';
insert into Publishers_Phones (publisher,phone) select
	p.id,n.id from Publishers p, Phones n
	where p.name like '5%' and n.line like '5%';

truncate Tokens;
insert into Tokens (person,type,value) select
	p.id,t.id,'1-value' from People p, TokenTypes t
	where p.f_name like '1%' and t.type like '1%';
insert into Tokens (person,type,value) select
	p.id,t.id,'2-value' from People p, TokenTypes t
	where p.f_name like '2%' and t.type like '2%';
insert into Tokens (person,type,value) select
	p.id,t.id,'3-value' from People p, TokenTypes t
	where p.f_name like '3%' and t.type like '3%';
insert into Tokens (person,type,value) select
	p.id,t.id,'4-value' from People p, TokenTypes t
	where p.f_name like '4%' and t.type like '4%';
insert into Tokens (person,type,value) select
	p.id,t.id,'5-value' from People p, TokenTypes t
	where p.f_name like '5%' and t.type like '5%';

truncate Transactions;
insert into Transactions (book,person,date_out,date_due,date_in,fee) select
	b.id,p.id,NULL,NULL,NULL,'1.11' from Books b, People p
	where b.title like '1%' and p.f_name like '1%';
insert into Transactions (book,person,date_out,date_due,date_in,fee) select
	b.id,p.id,NULL,NULL,NULL,'2.22' from Books b, People p
	where b.title like '2%' and p.f_name like '2%';
insert into Transactions (book,person,date_out,date_due,date_in,fee) select
	b.id,p.id,NULL,NULL,NULL,'3.33' from Books b, People p
	where b.title like '3%' and p.f_name like '3%';
insert into Transactions (book,person,date_out,date_due,date_in,fee) select
	b.id,p.id,NULL,NULL,NULL,'4.44' from Books b, People p
	where b.title like '4%' and p.f_name like '4%';
insert into Transactions (book,person,date_out,date_due,date_in,fee) select
	b.id,p.id,NULL,NULL,NULL,'5.55' from Books b, People p
	where b.title like '5%' and p.f_name like '5%';