-- eratta/addition file for ida database 
-- author: Adrian Pop
-- last date: 2003-08-28



----------------------------------
-- global.room modifications
----------------------------------
-- drop location column
alter table global.room drop location;
-- create unique index on code
create unique index ndxroom0 on global.room(code);
-- create other indexes we might need
create index ndxroom1 on global.room(building_code);
create index ndxroom2 on global.room(map_name);
create index ndxroom3 on global.room(roomtype_code);


----------------------------------
-- global.address additions
----------------------------------
create index ndxaddress0 on global.address(addresstype_code);
create index ndxaddress1 on global.address(city);
create index ndxaddress2 on global.address(country_code);

----------------------------------
-- global.object additions
----------------------------------
create index ndxobject0 on global.object(objecttype_code);
create index ndxobject1 on global.object(comments);


----------------------------------
-- global.o2address additions
----------------------------------
create index ndxo2address0 on global.o2address(object_code);
create index ndxo2address1 on global.o2address(address_id);

----------------------------------
-- global.o2login additions
----------------------------------
create unique index ndxo2login0 on global.o2login(object_code, login, domain_code);
create index ndxo2login1 on global.o2login(object_code);
create index ndxo2login2 on global.o2login(login);
create index ndxo2login3 on global.o2login(domain_code);


----------------------------------
-- global.o2email additions
----------------------------------
create unique index ndxo2email0 on global.o2email(object_code, email, emailtype_code);
create index ndxo2email1 on global.o2email(object_code);
create index ndxo2email2 on global.o2email(email);
create index ndxo2email3 on global.o2email(emailtype_code);


----------------------------------
-- global.o2phone additions
----------------------------------
create unique index ndxo2phone0 on global.o2phone(object_code, phone, phonetype_code);
create index ndxo2phone1 on global.o2phone(object_code);
create index ndxo2phone2 on global.o2phone(phone);
create index ndxo2phone3 on global.o2phone(phonetype_code);

----------------------------------
-- global.o2url additions
----------------------------------
create unique index ndxo2url0 on global.o2url(object_code, url);
create index ndxo2url1 on global.o2url(object_code);
create index ndxo2url2 on global.o2url(url);

----------------------------------
-- global.o2freetext additions
----------------------------------
create index ndxo2freetext0 on global.o2freetext(object_code);

----------------------------------
-- global.tree additions
----------------------------------
create index ndxtree1 on global.tree(nchildren);


----------------------------------
-- global.r2object additions
----------------------------------
create index ndxr2object0 on global.r2object(room_id);
create index ndxr2object1 on global.r2object(object_code);

----------------------------------
-- person.person additions
----------------------------------
create index ndxperson3 on person.person(statustype_code);
create index ndxperson4 on person.person(schedule_code);
create index ndxperson5 on person.person(doorkey);
create index ndxperson6 on person.person(sdate);
create index ndxperson7 on person.person(edate);
create index ndxperson8 on person.person(comments);


----------------------------------
-- person.job additions
----------------------------------
create index ndxjob0 on person.job(jobcategory_code);
create index ndxjob1 on person.job(code);
create index ndxjob2 on person.job(code_name);


----------------------------------
-- person.p2history additions
----------------------------------
create index ndxp2history0 on person.p2history(person_code);
create index ndxp2history1 on person.p2history(statustype_code);
create index ndxp2history2 on person.p2history(udate);

----------------------------------
-- person.p2title additions
----------------------------------
create index ndxp2title0 on person.p2title(person_code);
create index ndxp2title1 on person.p2title(titletype_code);

----------------------------------
-- person.p2phone additions
----------------------------------
create unique index ndxp2phone0 on person.p2phone(person_code, phone, phonetype_code);
create index ndxp2phone1 on person.p2phone(person_code);
create index ndxp2phone2 on person.p2phone(phone);
create index ndxp2phone3 on person.p2phone(phonetype_code);

----------------------------------
-- person.p2room additions
----------------------------------
create index ndxp2room0 on person.p2room(person_code);
create index ndxp2room1 on person.p2room(room_id);


----------------------------------
-- person.p2faculty additions
----------------------------------
create index ndxp2faculty0 on person.p2faculty(person_code);
create index ndxp2faculty1 on person.p2faculty(faculty_code);

----------------------------------
-- person.p2language additions
----------------------------------
create index ndxp2language0 on person.p2language(person_code);
create index ndxp2language1 on person.p2language(l_code);

----------------------------------
-- person.p2job additions
----------------------------------
create index ndxp2job0 on person.p2job(person_code);
create index ndxp2job1 on person.p2job(job_id);

----------------------------------
-- person.p2jobtype additions
----------------------------------
create index ndxp2jobtype0 on person.p2jobtype(person_code);
create index ndxp2jobtype1 on person.p2jobtype(jobtype_code);

----------------------------------
-- person.p2subject additions
----------------------------------
create index ndxp2subject0 on person.p2subject(person_code);
create index ndxp2subject1 on person.p2subject(faculty_code);

----------------------------------
-- person.p2freetext additions
----------------------------------
create index ndxp2freetext0 on person.p2freetext(person_code);

----------------------------------
-- person.p2knowledge additions
----------------------------------
create index ndxp2knowledge0 on person.p2knowledge(person_code);


----------------------------------
-- person.p2login additions
----------------------------------
create unique index ndxp2login0 on person.p2login(person_code, login, domain_code);
create index ndxp2login1 on person.p2login(person_code);
create index ndxp2login2 on person.p2login(login);
create index ndxp2login3 on person.p2login(domain_code);


----------------------------------
-- person.p2email additions
----------------------------------
create unique index ndxp2email0 on person.p2email(person_code, email, emailtype_code);
create index ndxp2email1 on person.p2email(person_code);
create index ndxp2email2 on person.p2email(email);
create index ndxp2email3 on person.p2email(emailtype_code);

----------------------------------
-- person.p2url additions
----------------------------------
create unique index ndxp2url0 on person.p2url(person_code, url);
create index ndxp2url1 on person.p2url(person_code);
create index ndxp2url2 on person.p2url(url);

----------------------------------
-- person.p2address additions
----------------------------------
create index ndxp2address0 on person.p2address(person_code);
create index ndxp2address1 on person.p2address(address_id);

----------------------------------
-- person.p2object additions
----------------------------------
create index ndxp2object0 on person.p2object(person_code);
create index ndxp2object1 on person.p2object(object_code);
create index ndxp2object2 on person.p2object(relationtype_code);
















