-- OpenGroupware.org database schema creation script -- (C) 2000-2005 SKYRIX Software AG -- CREATE DATABASE OGo; USE OGo; CREATE TABLE staff ( staff_id int not null, company_id int not null, description varchar(255), login varchar(255), is_team smallint, is_account smallint, db_status varchar(50), constraint pk_staff primary key (staff_id), constraint unique_company_id unique(company_id)); CREATE TABLE company ( company_id int not null, object_version int, owner_id int, contact_id int, template_user_id int, is_private smallint, is_person smallint, is_readonly smallint, is_enterprise smallint, is_account smallint, is_intra_account smallint, is_extra_account smallint, is_trust smallint, is_team smallint, is_location_team smallint, is_customer smallint, number varchar(100), description varchar(255), priority varchar(50), keywords varchar(255), url varchar(255), email varchar(100), type varchar(50), bank varchar(100), bank_code varchar(50), account varchar(50), payment varchar(50), is_locked smallint, is_template_user smallint, can_change_password smallint, login varchar(50), password varchar(255), pop3_account varchar(50), name varchar(50), middlename varchar(50), firstname varchar(50), salutation varchar(50), degree varchar(50), birthday datetime, sex varchar(10), source_url varchar(255), db_status varchar(50), sensitivity smallint, -- sensitivity boss_name varchar(255), -- boss' name partner_name varchar(255), -- partners' name assistant_name varchar(255), -- assistants' name department varchar(255), -- department office varchar(255), -- office (office number) occupation varchar(255), -- occupation anniversary datetime, -- anniversary dir_server varchar(255), -- directory serv. (NetMeeting) email_alias varchar(255), -- email alias (NetMeeting) freebusy_url varchar(255), -- free/busy URL fileas varchar(255), -- file as/save as name_title varchar(255), -- name title (e.g. Prof.) name_affix varchar(255), -- name affix (e.g. jun.) im_address varchar(255), -- Instant Messenger address associated_contacts varchar(255), -- associated contacts associated_categories varchar(255), -- associated categories associated_company varchar(255), -- associated company show_email_as varchar(255), -- email show as attr show_email2_as varchar(255), -- email show as attr show_email3_as varchar(255), -- email show as attr constraint pk_company primary key (company_id), constraint unique_company_number unique(number), constraint unique_company_login unique(login) ); CREATE TABLE company_info ( company_info_id int not null, company_id int not null, comment text , db_status varchar(50), constraint pk_company_info primary key (company_info_id)); CREATE TABLE company_value ( company_value_id int not null, company_id int , attribute varchar(255) , attribute_type varchar(50), value_string varchar(255) , value_date datetime , value_int int , is_enum smallint , category varchar(255) , uid int , label varchar(255) , type int , is_label_localized smallint , db_status varchar(50), constraint pk_company_value primary key (company_value_id)); CREATE TABLE company_category ( company_category_id int not null, object_version int , category varchar(255) , db_status varchar(50), constraint pk_company_category primary key (company_category_id)); CREATE TABLE company_assignment ( company_assignment_id int not null, company_id int , sub_company_id int , is_headquarter smallint , is_chief smallint , function varchar(255) , db_status varchar(50), constraint pk_company_assignment primary key (company_assignment_id)); CREATE TABLE appointment_resource ( appointment_resource_id int not null, object_version int , name varchar(255) not null, email varchar(255) , email_subject varchar(255) , category varchar(255) , notification_time int , db_status varchar(50), constraint pkx_company_category primary key (appointment_resource_id), constraint unique_aptresname_idx unique(name)); CREATE TABLE appointment ( date_id int not null, object_version int, owner_id int, access_team_id int, parent_date_id int, start_date datetime not null, end_date datetime not null, cycle_end_date datetime, type varchar(50), -- repetition type title varchar(255) not null, location varchar(255), absence varchar(255), resource_names varchar(255), write_access_list varchar(255), is_absence smallint, is_attendance smallint, is_conflict_disabled smallint, travel_duration_before int, travel_duration_after int, notification_time int, db_status varchar(50), apt_type varchar(100), -- appointment type (birthday...) calendar_name varchar(255), source_url varchar(255), fbtype varchar(50), -- free/busy type sensitivity smallint, -- sensitivity busy_type smallint, -- busy type importance smallint, -- importance last_modified int, -- timestamp of last modification evo_reminder varchar(255), -- Evolution reminder settings ol_reminder varchar(255), -- Outlook reminder settings online_meeting varchar(255), -- CSV for online meeting values associated_contacts varchar(255), -- Outlook associated_contacts keywords varchar(255), -- Outlook keywords constraint pk_date primary key (date_id)); CREATE TABLE date_info ( date_info_id int not null, date_id int not null, comment text, db_status varchar(50), constraint pk_date_info primary key (date_info_id)); CREATE TABLE date_company_assignment ( date_company_assignment_id int not null, company_id int, date_id int, is_staff smallint, is_new smallint, partstatus varchar(50), role varchar(50), comment varchar(255), rsvp smallint, db_status varchar(50), outlook_key varchar(255), constraint pk_date_company_assignment primary key (date_company_assignment_id)); CREATE TABLE project ( project_id int not null, object_version int, owner_id int not null, team_id int, number varchar(100), name varchar(255), start_date datetime, end_date datetime, status varchar(255), is_fake smallint, db_status varchar(50), kind varchar(50), url varchar(100), constraint pk_project primary key (project_id), constraint unique_project_number unique(number)); CREATE TABLE object_acl ( object_acl_id int not null, sort_key int not null, action varchar(10) not null, object_id varchar(255) not null, auth_id varchar(255), permissions varchar(50)); CREATE TABLE project_company_assignment ( project_company_assignment_id int not null, company_id int, project_id int, info varchar(255), has_access smallint, access_right varchar(50), db_status varchar(50), constraint pk_project_company_assignment primary key (project_company_assignment_id)); CREATE TABLE document ( document_id int not null, object_version int, project_id int, parent_document_id int, date_id int, first_owner_id int, current_owner_id int, version_count int, file_size int, is_note smallint, is_folder smallint, is_object_link smallint, is_index_doc smallint, title varchar(255), abstract varchar(255), file_type varchar(255), object_link varchar(255), creation_date datetime, lastmodified_date datetime, status varchar(50), db_status varchar(50), contact varchar(255), constraint pk_document primary key (document_id)); CREATE TABLE document_version ( document_version_id int not null, object_version int, document_id int, last_owner_id int, title varchar(255), abstract varchar(255), file_type varchar(255), version int, file_size int, creation_date datetime, archive_date datetime, is_packed smallint, change_text text, db_status varchar(50), contact varchar(255), constraint pk_document_version primary key (document_version_id)); CREATE TABLE document_editing ( document_editing_id int not null, object_version int, document_id int, current_owner_id int, title varchar(255), abstract varchar(255), file_type varchar(255), file_size int, version int, is_attach_changed smallint, checkout_date datetime, status varchar(50), db_status varchar(50), contact varchar(255), constraint pk_document_editing primary key (document_editing_id)); CREATE TABLE address ( address_id int not null, object_version int, company_id int, name1 varchar(255), name2 varchar(255), name3 varchar(255), street varchar(255), zip varchar(50), zipcity varchar(255), country varchar(100), state varchar(100), type varchar(50) not null, db_status varchar(50), source_url varchar(255), constraint pk_address primary key (address_id)); CREATE TABLE telephone ( telephone_id int not null, object_version int, company_id int, number varchar(255), real_number varchar(255), type varchar(50) not null, info varchar(255), url varchar(255), db_status varchar(50), constraint pk_telephone primary key (telephone_id)); CREATE TABLE job ( job_id int not null, object_version int, parent_job_id int, project_id int, creator_id int, executant_id int, name varchar(255) not null, start_date datetime not null, end_date datetime not null, notify_x int, is_control_job smallint, is_team_job smallint, is_new smallint, job_status varchar(255), category varchar(255), priority int, db_status varchar(50), kind varchar(50), keywords varchar(255), source_url varchar(255), sensitivity smallint, job_comment text, completion_date datetime, percent_complete smallint, actual_work smallint, total_work smallint, last_modified int, accounting_info varchar(255), kilometers varchar(255), associated_companies varchar(255), associated_contacts varchar(255), timer_date datetime, constraint pk_job primary key (job_id)); CREATE TABLE job_history ( job_history_id int not null, object_version int, job_id int not null, actor_id int, action varchar(50), action_date datetime, job_status varchar(50), db_status varchar(50), constraint pk_job_history primary key (job_history_id)); CREATE TABLE job_history_info ( job_history_info_id int not null, job_history_id int not null, comment text, db_status varchar(50), constraint pk_job_history_info primary key (job_history_info_id)); CREATE TABLE resource ( resource_id int not null, resource_name varchar(255) not null, token varchar(255), object_id int, quantity int, comment text, standard_costs numeric(19,2), type int not null, db_status varchar(50), object_version int, constraint pk_resource_id primary key (resource_id)); CREATE TABLE job_resource_assignment ( job_resource_assignment_id int not null, resource_id int, job_id int, operative_part int, db_status varchar(50), constraint pk_job_resource_id primary key (job_resource_assignment_id)); CREATE TABLE news_article ( news_article_id int not null, object_version int, name varchar(255), caption varchar(255), is_index_article smallint, creation_date datetime, db_status varchar(50), constraint pk_news_article primary key (news_article_id)); CREATE TABLE news_article_link ( news_article_link_id int not null, object_version int, news_article_id int, sub_news_article_id int, constraint pk_article_link primary key (news_article_link_id)); CREATE TABLE job_assignment ( job_assignment_id int not null, parent_job_id int not null, child_job_id int not null, position_x int, assignment_kind varchar(50), db_status varchar(50), constraint pk_job_assignment primary key (job_assignment_id)); CREATE TABLE project_info ( project_info_id int not null, project_id int not null, comment text, db_status varchar(50), constraint pk_project_info primary key (project_info_id)); CREATE TABLE log ( log_id int not null, creation_date datetime not null, object_id int not null, log_text text not null, action varchar(100) not null, account_id int, constraint pk_log primary key (log_id)); CREATE TABLE session_log ( session_log_id int not null, account_id int not null, log_date datetime not null, action varchar(255) not null, constraint pk_session_log primary key (session_log_id)); CREATE TABLE obj_info ( obj_id int not null, obj_type varchar(255) not null, constraint pk_obj_info primary key (obj_id)); CREATE TABLE obj_property ( obj_property_id int not null, obj_id int not null, obj_type varchar(255), access_key int, value_key varchar(255) not null, namespace_prefix varchar(255), preferred_type varchar(255) not null, value_string varchar(255), value_int int, value_float numeric(19,8), value_date datetime, value_oid varchar(255), blob_size int, value_blob text, constraint pk_obj_property primary key (obj_property_id)); CREATE TABLE obj_link ( obj_link_id int not null, source_id int not null, source_type varchar(50) null, target varchar(255) not null, target_id int null, target_type varchar(50) null, link_type varchar(50) null, label varchar(255) null); CREATE TABLE object_model ( db_version int not null, model_name varchar(255) not null); INSERT INTO object_model(db_version, model_name) VALUES ( 3, 'OpenGroupware.org_MySQL5'); CREATE TABLE table_version ( table_name varchar(255) not null, table_version int not null, constraint pk_table_version primary key (table_name)); CREATE VIEW person AS SELECT company_id, owner_id, contact_id, template_user_id, number, description, priority, keywords, name, middlename, firstname, salutation, degree, is_private, is_readonly, birthday, url, sex, is_person, login, password, is_locked, pop3_account, is_account, is_intra_account, is_extra_account, is_customer, db_status, object_version, is_template_user, can_change_password, source_url, sensitivity, boss_name, partner_name, assistant_name, department, office, occupation, anniversary, dir_server, email_alias, freebusy_url, fileas, name_title, name_affix, im_address, associated_contacts, associated_categories, associated_company, show_email_as, show_email2_as, show_email3_as FROM company WHERE is_person = 1 AND is_enterprise IS NULL AND is_trust IS NULL AND is_team IS NULL; CREATE VIEW enterprise AS SELECT company_id, owner_id, contact_id, number, description, priority, keywords, url, email, login, bank, bank_code, account, is_enterprise, db_status, is_customer, is_private, is_readonly, object_version, source_url, sensitivity, boss_name, partner_name, assistant_name, department, office, occupation, anniversary, dir_server, email_alias, freebusy_url, fileas, name_title, name_affix, im_address, associated_contacts, associated_categories, associated_company, show_email_as, show_email2_as, show_email3_as, birthday, firstname FROM company WHERE is_person IS NULL AND is_enterprise = 1 AND is_trust IS NULL AND is_team IS NULL; CREATE VIEW trust AS SELECT company_id, owner_id, contact_id, number, is_private, is_readonly, description, priority, keywords, url, email, is_trust, db_status, object_version FROM company WHERE is_person IS NULL AND is_enterprise IS NULL AND is_trust = 1 AND is_team IS NULL; CREATE VIEW team AS SELECT company_id, owner_id, contact_id, number, login, email, description, is_team, is_location_team, db_status, object_version, sensitivity, boss_name, partner_name, assistant_name, department, office, occupation, anniversary, dir_server, email_alias, freebusy_url, fileas, name_title, name_affix, im_address, associated_contacts, associated_categories, associated_company FROM company WHERE is_person IS NULL AND is_enterprise IS NULL AND is_trust IS NULL AND is_team = 1; CREATE VIEW note AS SELECT * FROM document WHERE is_note = 1; CREATE VIEW doc AS SELECT * FROM document WHERE is_note = 0; -- PDA CREATE TABLE palm_address ( company_id int not null, device_id varchar(50) not null, palm_address_id int not null, palm_id int, category_index int, is_deleted smallint not null, is_modified smallint not null, is_archived smallint not null, is_new smallint not null, is_private smallint not null, md5hash varchar(50) not null, address varchar(255), city varchar(255), company varchar(255), country varchar(255), display_phone int not null, firstname varchar(255), lastname varchar(255), note text, phone0 varchar(255), phone1 varchar(255), phone2 varchar(255), phone3 varchar(255), phone4 varchar(255), phone_label_id0 int not null, phone_label_id1 int not null, phone_label_id2 int not null, phone_label_id3 int not null, phone_label_id4 int not null, state varchar(255), title varchar(255), zipcode varchar(255), custom1 varchar(255), custom2 varchar(255), custom3 varchar(255), custom4 varchar(255), skyrix_id int, skyrix_sync int, skyrix_version int, skyrix_type varchar(10), object_version int, skyrix_palm_version int, constraint pk_palm_address primary key (palm_address_id) ); CREATE TABLE palm_date ( company_id int not null, device_id varchar(50) not null, palm_date_id int not null, palm_id int, category_index int, is_deleted smallint not null, is_modified smallint not null, is_archived smallint not null, is_new smallint not null, is_private smallint not null, md5hash varchar(50) not null, alarm_advance_time int not null, alarm_advance_unit int not null, description varchar(255) not null, enddate datetime, is_alarmed smallint not null, is_untimed smallint not null, note text, repeat_enddate datetime, repeat_frequency int, repeat_on int, repeat_start_week int, repeat_type int, startdate datetime, exceptions text, skyrix_id int, skyrix_sync int, skyrix_version int, object_version int, skyrix_palm_version int, constraint pk_palm_date primary key (palm_date_id) ); CREATE TABLE palm_memo ( company_id int not null, device_id varchar(50) not null, palm_memo_id int not null, palm_id int, category_index int, is_deleted smallint not null, is_modified smallint not null, is_archived smallint not null, is_new smallint not null, is_private smallint not null, md5hash varchar(50) not null, memo text not null, skyrix_id int, skyrix_sync int, skyrix_version int, object_version int, skyrix_palm_version int, constraint pk_palm_memo primary key (palm_memo_id) ); CREATE TABLE palm_todo ( company_id int not null, device_id varchar(50) not null, palm_todo_id int not null, palm_id int, category_index int, is_deleted smallint not null, is_modified smallint not null, is_archived smallint not null, is_new smallint not null, is_private smallint not null, md5hash varchar(50) not null, description varchar(255) not null, duedate datetime, note text, priority int not null, is_completed smallint not null, skyrix_id int, skyrix_sync int, skyrix_version int, object_version int, skyrix_palm_version int, constraint pk_palm_todo primary key (palm_todo_id) ); CREATE TABLE palm_category ( company_id int not null, device_id varchar(50) not null, palm_category_id int not null, palm_id int, palm_table varchar(10) not null, is_modified smallint not null, md5hash varchar(50) not null, category_index int not null, category_name varchar(255) not null, constraint pk_palm_category primary key (palm_category_id) ); -- End PDA -- TODO: depends on the views INSERT INTO person (company_id, login, name, description, is_account, is_intra_account, is_extra_account, is_person, number, is_private, is_readonly, db_status, object_version) VALUES (10000, 'root', 'root', 'Administrator', 1, 1, 0, 1, 'LS10000', 0, 1, 'inserted', 1); INSERT INTO staff (staff_id, company_id, is_account) VALUES (10001, 10000, 1); INSERT INTO company_info (company_info_id, company_id) VALUES (10002, 10000); INSERT INTO person (company_id, owner_id, login, name, description, is_account, is_intra_account, is_extra_account, is_person, number, is_private, is_readonly, is_locked, is_template_user, db_status, object_version) VALUES (9999, 10000, 'template', 'template', 'Template', 1, 1, 0, 1, 'LS9999', 1, 1, 1, 1, 'inserted', 1); INSERT INTO staff (staff_id, company_id, is_account) VALUES (9998, 9999, 1); INSERT INTO company_info (company_info_id, company_id) VALUES (9997, 9999); INSERT INTO team (company_id, description, is_team, number, login, db_status, object_version, owner_id, is_readonly, is_private) VALUES (10003, 'all intranet', 1, 'LS10003', 'all intranet', 'inserted', 1, 10000, 1, 0); INSERT INTO staff (staff_id, company_id, is_team) VALUES (10004, 10003, 1); INSERT INTO company_info (company_info_id, company_id) VALUES (10005, 10003); INSERT INTO team (company_id, description, is_team, number, login, db_status, object_version, owner_id, is_readonly, is_private) VALUES (9991, 'news editors', 1, 'LS9991', 'newseditors', 'inserted', 1, 10000, 1, 0); INSERT INTO staff (staff_id, company_id, is_team) VALUES (9992, 9991, 1); INSERT INTO company_info (company_info_id, company_id) VALUES (9993, 9991); INSERT INTO news_article (news_article_id, name, caption, is_index_article, object_version) VALUES (10006, 'index', 'Index', 1, 1); -- TODO: do we need an increment of 10? Or is that handled by LAST_INSERT_ID? CREATE TABLE key_generator (id INT NOT NULL); INSERT INTO key_generator VALUES (10100); UPDATE project_company_assignment SET access_right = 'r' WHERE has_access = 1 AND access_right IS NULL; -- table versions ------------------------------------------------------------- INSERT INTO table_version (table_name, table_version) VALUES ('_model', 41); INSERT INTO table_version (table_name, table_version) VALUES ('staff', 41); INSERT INTO table_version (table_name, table_version) VALUES ('company', 45); INSERT INTO table_version (table_name, table_version) VALUES ('company_info', 42); INSERT INTO table_version (table_name, table_version) VALUES ('company_value', 41); INSERT INTO table_version (table_name, table_version) VALUES ('company_category', 41); INSERT INTO table_version (table_name, table_version) VALUES ('company_assignment', 41); INSERT INTO table_version (table_name, table_version) VALUES ('appointment_resource', 41); INSERT INTO table_version (table_name, table_version) VALUES ('appointment', 47); INSERT INTO table_version (table_name, table_version) VALUES ('date', 47); INSERT INTO table_version (table_name, table_version) VALUES ('date_info', 42); INSERT INTO table_version (table_name, table_version) VALUES ('date_company_assignment', 43); INSERT INTO table_version (table_name, table_version) VALUES ('project', 41); INSERT INTO table_version (table_name, table_version) VALUES ('project_info', 42); INSERT INTO table_version (table_name, table_version) VALUES ('project_company_assignment', 41); INSERT INTO table_version (table_name, table_version) VALUES ('document', 41); INSERT INTO table_version (table_name, table_version) VALUES ('document_version', 42); INSERT INTO table_version (table_name, table_version) VALUES ('document_editing', 41); INSERT INTO table_version (table_name, table_version) VALUES ('address', 42); INSERT INTO table_version (table_name, table_version) VALUES ('telephone', 41); INSERT INTO table_version (table_name, table_version) VALUES ('job', 46); INSERT INTO table_version (table_name, table_version) VALUES ('job_history', 41); INSERT INTO table_version (table_name, table_version) VALUES ('job_history_info', 42); INSERT INTO table_version (table_name, table_version) VALUES ('news_article', 41); INSERT INTO table_version (table_name, table_version) VALUES ('news_article_link', 41); INSERT INTO table_version (table_name, table_version) VALUES ('key_generator', 41); INSERT INTO table_version (table_name, table_version) VALUES ('key_lock', 41); INSERT INTO table_version (table_name, table_version) VALUES ('person', 45); INSERT INTO table_version (table_name, table_version) VALUES ('enterprise', 46); INSERT INTO table_version (table_name, table_version) VALUES ('trust', 45); INSERT INTO table_version (table_name, table_version) VALUES ('team', 45); INSERT INTO table_version (table_name, table_version) VALUES ('note', 41); INSERT INTO table_version (table_name, table_version) VALUES ('doc', 41); INSERT INTO table_version (table_name, table_version) VALUES ('log', 42); INSERT INTO table_version (table_name, table_version) VALUES ('invoice', 42); INSERT INTO table_version (table_name, table_version) VALUES ('invoice_action', 42); INSERT INTO table_version (table_name, table_version) VALUES ('article_category', 41); INSERT INTO table_version (table_name, table_version) VALUES ('article_unit', 41); INSERT INTO table_version (table_name, table_version) VALUES ('article', 42); INSERT INTO table_version (table_name, table_version) VALUES ('invoice_article_assignment', 42); INSERT INTO table_version (table_name, table_version) VALUES ('palm_address', 43); INSERT INTO table_version (table_name, table_version) VALUES ('palm_date', 43); INSERT INTO table_version (table_name, table_version) VALUES ('palm_memo', 43); INSERT INTO table_version (table_name, table_version) VALUES ('palm_todo', 43); INSERT INTO table_version (table_name, table_version) VALUES ('palm_category', 41); INSERT INTO table_version (table_name, table_version) VALUES ('resource', 42); INSERT INTO table_version (table_name, table_version) VALUES ('obj_property', 42); INSERT INTO table_version (table_name, table_version) VALUES ('obj_link', 42);