Mercurial > hg > dbutils / file revision

create table Person(
	person_id integer primary key autoincrement,
	name text,
	email text,
	age integer,
	iscustomer integer,
	hash integer,
	address_id integer
);

create table Country(
	country_id integer primary key autoincrement,
	name text
);

create table Address(
	address_id integer primary key autoincrement,
	street text,
	zip text,
	city text,
	country_id integer references Country(country_id)
);

create table Role(
	role_id integer primary key autoincrement,
	person_id integer,
	name text
);

insert into country (name) values ('Germany');

insert into address (street, zip, city, country_id) 
values 
('street 1', '12343', 'city 17', 1), 
('street 2', '23456', 'city 18', 1);

insert into person (name, email, age, iscustomer, hash, address_id) values
('alice', 'alice@example.com', 30, 0, 123456789, (select address_id from address where street = 'street 1')),
('bob', 'bob@example.com', 25, 1, 987654321,  (select address_id from address where street = 'street 2'));

insert into role (person_id, name) 
values 
(1, 'finance'), 
(1, 'dev'), 
(1, 'manager'), 
(2, 'extern');