Author: spadkins Date: Tue Dec 1 06:06:31 2009 New Revision: 13620 Added: p5ee/trunk/App-Repository/db/ p5ee/trunk/App-Repository/db/mysql/ p5ee/trunk/App-Repository/db/mysql/README (contents, props changed) p5ee/trunk/App-Repository/db/mysql/app.sql p5ee/trunk/App-Repository/db/mysql/app_partner.sql p5ee/trunk/App-Repository/db/mysql/app_partnership.sql p5ee/trunk/App-Repository/db/mysql/app_profiler_log.sql p5ee/trunk/App-Repository/db/mysql/org.sql p5ee/trunk/App-Repository/db/mysql/org_app_access.sql p5ee/trunk/App-Repository/db/mysql/org_role.sql p5ee/trunk/App-Repository/db/mysql/org_usr_memb.sql p5ee/trunk/App-Repository/db/mysql/perm.sql p5ee/trunk/App-Repository/db/mysql/perm_role_grant.sql p5ee/trunk/App-Repository/db/mysql/role_usr_grant.sql p5ee/trunk/App-Repository/db/mysql/usr.sql
Log: added App-Repository related tables Added: p5ee/trunk/App-Repository/db/mysql/README ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/README Tue Dec 1 06:06:31 2009 @@ -0,0 +1,123 @@ +=========================== +ENTITY-RELATIONSHIP DIAGRAM +=========================== + + =========== ============= ========= + usr app_partner app + =========== ============= ========= + | | | o | | | | + | | +--------+ +---+ | +---+ | | + | | | | | | | | + | | M o M M | | + | | ============ =============== | | + | | org app_partnership | | + | | ============ =============== | | + | | | | o | | | | + | +----+ +--+ | | | +---------+ +----+ | + | | | | | +-----+ | | | + | M M | M | M M M + | ============ | ======== | ============== ======== + | org_usr_memb | role | org_app_access perm + | ============ | ======== | ============== ======== + | | | | | | + +------+ +-----+ | | | | + | | +---+ | | +--------------+ + | | | | | | + M M M M M M + ================ ================= + role_usr_grant perm_role_grant + ================ ================= + +======================================== +Definitions, Uniqueness, and Cardinality +======================================== + +Definitions for all the tables are given below, and statements +are made between the relationships to other tables. +In addition to an ID used for a primary key, +certaint alternate keys are also guaranteed unique. + +usr - An independent user of the suite of application + services. Anyone may become a user, and he immediately has + access to all public applications. He may be granted a membership + in one or more organizations. He then gains access + to all the applications of the organization with permissions + on those applications in keeping with the roles he has been + granted in the organization. + * a user sponsors zero-or-more organizations + * a user is a member of zero-or-more organizations + * a user is granted a role in zero-or-more organizations + o uniquely identified by (usr_name) + +org - An organization is sponsored by a particular user and is a + billable entity. The sponsor is automatically an administrator + of the organization and grants memberships to users, defines + roles, grants available permissions in applications to those + roles, and grants roles to users. + * an organization is sponsored by exactly-one user + * an organization has zero-or-more member users + * an organization has zero-or-more roles within it + * an organization grants zero-or-more permissions to each role + * an organization grants zero-or-more roles to each member user + o uniquely identified by (org_auth_cd) + o uniquely identified by (org_name) + +app - An application is a piece of software implementing business + functions which users will want to use. + * an application requires zero-or-more permissions to operate it + * an application is granted access to zero-or-more organizations + * an application is hosted in conjunction with zero-or-more partners + o uniquely identified by (app_cd) + +app_partner - An application partner is a party who will benefit when users + use the application. It is the recipient of revenue sharing, + usage information, or whatever is specified in the partnership. + Information about an application partner includes the details + on how to transfer funds or information. + * an application partner may be an organization + * an application partner is a partner on zero-or-more applications + o uniquely identified by (app_partner_cd) + +app_partnership - An application partnership is an agreement between the + application service provider (ASP) and the application partner + that describes the details of the relationship (i.e. revenue + and information sharing). + * an application partnership is valid for a single partner + * an application partnership is valid for a single application + o uniquely identified by (app_id, app_partner_id) + +perm - A permission is a feature of an application which can be granted + or restricted from use to a role. This allows different users + to have access to different permissions within an application + based on the role(s) they play in an organization. + * a permission is a feature of a single application + * a permission is granted to zero-or-more roles in various orgs + o uniquely identified by (perm_cd) + o uniquely identified by (perm_name) + +role - A role is defined in an organization in order to selectively + assign permissions to users. + o uniquely identified by (role_name, org_id) + +org_app_access - Each row describes that access to an application has been + granted to the organization by the ASP or an Application + partner. + o uniquely identified by (org_id, app_id) + +org_usr_memb - Each row describes a membership that a user has in + an organization, granted by an administrator of the + organization. + o uniquely identified by (usr_id, org_id) + o uniquely identified by (org_id, usr_id) + o uniquely identified by (usr_name, org_auth_cd) + +perm_role_grant - Each row describes that a particular permission has been + granted to a role in an organization. + o uniquely identified by (role_id, perm_id) + o uniquely identified by (org_id, perm_id, role_id) + +role_usr_grant - Each row describes that a role in an organization has been + granted to a user. + o uniquely identified by (usr_id, role_id) + o uniquely identified by (org_id, role_id, usr_id) + Added: p5ee/trunk/App-Repository/db/mysql/app.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/app.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,9 @@ + +create table app ( + app_id integer not null auto_increment primary key, + app_cd varchar (12) not null, + app_name varchar (99) not null, + public_ind char(1) not null default 'N', + unique app_ak1 (app_cd) +); + Added: p5ee/trunk/App-Repository/db/mysql/app_partner.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/app_partner.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,23 @@ + +create table app_partner ( + app_partner_id integer not null auto_increment primary key, + app_partner_cd varchar (12) not null, + app_partner_name varchar (99) not null, + org_id integer null, + admin_email varchar (255) null, + info_email varchar (255) null, + bank_name varchar (99) null, + bank_address1 varchar (99) null, + bank_address2 varchar (99) null, + bank_city varchar (99) null, + bank_state varchar (99) null, + bank_postal_cd varchar (99) null, + bank_aba_routing varchar (99) null, + bank_account_nbr varchar (99) null, + bank_account_name varchar (99) null, + bank_contact_name varchar (99) null, + bank_tel_nbr varchar (99) null, + bank_fax_nbr varchar (99) null, + unique app_partner_ak1 (app_partner_cd) +); + Added: p5ee/trunk/App-Repository/db/mysql/app_partnership.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/app_partnership.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,11 @@ + +create table app_partnership ( + app_partnership_id integer not null auto_increment primary key, + app_id integer not null, + app_partner_id integer not null, + revenue_share float not null default 0, + grant_access_ind char(1) not null default 'N', + usage_email_ind char(1) not null default 'N', + unique app_partnershi_ak1 (app_id, app_partner_id) +); + Added: p5ee/trunk/App-Repository/db/mysql/app_profiler_log.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/app_profiler_log.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,51 @@ + +DROP TABLE IF EXISTS app_profiler_log; + +CREATE TABLE app_profiler_log ( + profiler_log_id integer NOT NULL AUTO_INCREMENT, + + -- values that are known at the start + context varchar(18) NOT NULL, -- HTTP, ClusterController + host varchar(32) NOT NULL, -- i.e. 'po63' + username varchar(99) NOT NULL, -- i.e. 'rmartin' + app varchar(64) NOT NULL, -- i.e. 'hotel' + events varchar(64) NOT NULL, -- i.e. + start_dttm datetime NOT NULL, -- i.e. '2007-09-24 12:03:34' + end_dttm datetime NULL, -- i.e. '2007-09-24 12:05:03' + end_cd varchar(1) NULL, -- i.e. F=Finish, D=Destroy + start_mem_mb float NULL, + end_mem_mb float NULL, + + app_scope varchar(255) NULL, -- i.e. 'hotel-TRV' + content_name varchar(64) NULL, -- i.e. + app_scope_id_type varchar(8) NULL, -- i.e. 'HSRID', 'HSSRID', 'HPID' + app_scope_id integer NULL, -- i.e. shop_request_id, subrequest_id, profile_id + + cpu_time float NULL, + run_time float NULL, + run_main_time float NULL, + run_event_time float NULL, + run_db_time float NULL, + run_file_time float NULL, + run_net_time float NULL, + run_aux1_label varchar(16) NULL, + run_aux1_time float NULL, + run_aux2_label varchar(16) NULL, + run_aux2_time float NULL, + run_xfer_time float NULL, + run_other_time float NULL, + num_db_calls integer NULL, + num_db_rows_read integer NULL, + num_db_rows_write integer NULL, + num_net_calls integer NULL, + content_length integer NULL, + + modify_dttm timestamp NOT NULL DEFAULT current_timestamp on update current_timestamp, + + PRIMARY KEY (profiler_log_id), + INDEX app_profiler_log_ie1 (modify_dttm), + INDEX app_profiler_log_ie2 (start_dttm), + INDEX app_profiler_log_ie3 (app_scope_id) + +) ENGINE=InnoDB; + Added: p5ee/trunk/App-Repository/db/mysql/org.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/org.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,15 @@ + +drop table if exists org; +create table org ( + org_id integer not null auto_increment primary key, + org_auth_cd varchar(25) not null, -- used for Apache authentication + org_name varchar(255) not null, + org_long_name varchar(255) not null, + owner_usr_id integer not null, + auth_org_email varchar(255) null, + pri_org_email varchar(255) not null, + create_dttm datetime not null, + unique org_ak1 (org_auth_cd), + unique org_ak2 (org_name) +); + Added: p5ee/trunk/App-Repository/db/mysql/org_app_access.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/org_app_access.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,11 @@ + +create table org_app_access ( + org_app_access_id integer not null auto_increment primary key, + org_id integer not null, + app_id integer not null, + all_memb_user_ind char(1) not null default 'Y', + org_use_status char(1) not null default 'A', + asp_use_status char(1) not null default 'A', + unique org_app_access_ak1 (org_id, app_id) +); + Added: p5ee/trunk/App-Repository/db/mysql/org_role.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/org_role.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,8 @@ + +create table org_role ( + org_role_id integer not null auto_increment primary key, + org_id integer not null default 1, + org_role_name varchar (99) not null, + unique org_role_ak1 (org_role_name, org_id) +); + Added: p5ee/trunk/App-Repository/db/mysql/org_usr_memb.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/org_usr_memb.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,14 @@ + +create table org_usr_memb ( + org_usr_memb_id integer not null auto_increment primary key, + org_id integer not null, + usr_id integer not null, + org_auth_cd varchar(25) not null, + usr_name varchar(25) not null, + all_perm_ind char(1) not null default 'Y', + status char(1) not null default 'A', + unique org_usr_memb_ak1 (usr_id, org_id), + unique org_usr_memb_ak2 (org_id, usr_id), + unique org_usr_memb_ak3 (usr_name, org_auth_cd) +); + Added: p5ee/trunk/App-Repository/db/mysql/perm.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/perm.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,12 @@ + +create table perm ( + perm_id integer not null auto_increment primary key, + app_id integer not null, + perm_cd varchar(12) not null, + perm_name varchar(99) not null, + display_order integer not null default 999, + create_dttm datetime not null, + unique perm_ak1 (app_id, perm_cd), + unique perm_ak2 (app_id, perm_name) +); + Added: p5ee/trunk/App-Repository/db/mysql/perm_role_grant.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/perm_role_grant.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,10 @@ + +create table perm_role_grant ( + role_perm_grant_id integer not null auto_increment primary key, + org_id integer not null default 1, + role_id integer not null, + perm_id integer not null, + unique perm_role_gran_ak1 (role_id, perm_id), + unique perm_role_gran_ak2 (org_id, perm_id, role_id) +); + Added: p5ee/trunk/App-Repository/db/mysql/role_usr_grant.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/role_usr_grant.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,11 @@ + +create table role_usr_grant ( + role_usr_grant_id integer not null auto_increment primary key, + org_id integer not null, + usr_id integer not null, + role_id integer not null, + status char(1) not null default 'A', + unique role_usr_grant_ak1 (usr_id, role_id), + unique role_usr_grant_ak2 (org_id, role_id, usr_id) +); + Added: p5ee/trunk/App-Repository/db/mysql/usr.sql ============================================================================== --- (empty file) +++ p5ee/trunk/App-Repository/db/mysql/usr.sql Tue Dec 1 06:06:31 2009 @@ -0,0 +1,32 @@ + +create table usr ( + usr_id integer not null auto_increment primary key, + usr_name varchar(25) not null, + password1 varchar(25) not null, + password2 varchar(99) null, + pri_email varchar(255) not null, + create_dttm datetime not null, + first_name varchar (99), + last_name varchar (99), + prefix_name varchar (16), + middle_name varchar (99), + suffix_name varchar (16), + company varchar (255), + address1 varchar (255), + address2 varchar (255), + city varchar (255), + state varchar (32), + postal_code varchar (32), + country varchar (99), + citizenship varchar (32), + phone varchar (32), + altphone varchar (32), + fax varchar (32), + cell varchar (32), + pager varchar (32), + confirm_value varchar (99), + last_login datetime, + object_data mediumblob, + unique usr_ak1 (usr_name) +); +
