testdata.sql


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
);

create table Resource(
	resource_id integer primary key autoincrement,
	parent_id integer references Resource(resource_id),
	nodename text,
	content text,
	iscollection integer default 0
);

create table Note(
	note_id integer primary key autoincrement,
	resource_id integer references Resource(resource_id),
	tags text,
	type int
);

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');

insert into Resource(nodename, iscollection)
values
('root', 1);

insert into Resource(parent_id, nodename, iscollection)
values
((select resource_id from Resource where nodename = 'root'), 'Collection1', 1);

insert into Resource(parent_id, nodename, iscollection)
values
((select resource_id from Resource where nodename = 'root'), 'Collection2', 1);

insert into Resource(parent_id, nodename, content)
values
((select resource_id from Resource where nodename = 'Collection1'), 'note1', 'Hello World!');

insert into Resource(parent_id, nodename, content)
values
((select resource_id from Resource where nodename = 'Collection1'), 'note2', 'Test String');

insert into Resource(parent_id, nodename, content)
values
((select resource_id from Resource where nodename = 'Collection2'), 'note3', 'Content Text');

insert into Note(resource_id, tags, type)
values
((select resource_id from Resource where nodename = 'note1'), 'todo, test', 1);

insert into Note(resource_id, tags, type)
values
((select resource_id from Resource where nodename = 'note2'), 'work, project2501, ai', 2);

insert into Note(resource_id, tags, type)
values
((select resource_id from Resource where nodename = 'note3'), 'finance', 3);