%VOSWARNING% ---++ODS Specification Components ---+++ODS database back end ---++++Tables * WA_SETTINGS 1 Contains list of ODS-specific settings. 1 Typically, this table contains one row only. CREATE TABLE WA_SETTINGS ( WS_ID INTEGER IDENTITY PRIMARY KEY, WS_REGISTER INT, WS_MAIL_VERIFY INT, WS_VERIFY_TIP INT, WS_REGISTRATION_EMAIL_EXPIRY INT DEFAULT 24, WS_JOIN_EXPIRY INT DEFAULT 72, WS_DOMAINS VARCHAR, WS_SMTP VARCHAR, WS_USE_DEFAULT_SMTP INTEGER, WS_BRAND_NAME VARCHAR, WS_WEB_BANNER VARCHAR, WS_WEB_TITLE VARCHAR, WS_WEB_DESCRIPTION VARCHAR, WS_WELCOME_MESSAGE VARCHAR, WS_COPYRIGHT VARCHAR, WS_DISCLAIMER VARCHAR, WS_DEFAULT_MAIL_DOMAIN VARCHAR, WS_SHOW_SYSTEM_ERRORS INTEGER, WS_MEMBER_MODEL INTEGER, WS_REGISTRATION_XML LONG XML, WS_GENERAL_AGREEMENT VARCHAR, WS_MEMBER_AGREEMENT VARCHAR, ); * WA_USERS 1 Contains list of registered ODS users together with some additional information like secret question and answer for password recovery. 1 Each user in this table have to be exists in SYS_USERS table too. 1 This table automatically filled during user registration through ODS interface CREATE TABLE WA_USERS ( WAU_U_ID INT, WAU_QUESTION VARCHAR, WAU_ANSWER VARCHAR, WAU_LAST_IP VARCHAR, WAU_TEMPLATE VARCHAR, WAU_LOGON_DISABLE_UNTIL DATETIME, WAU_PWD_RECOVER_DISABLE_UNTIL DATETIME, PRIMARY KEY (WAU_U_ID) ); * WA_USER_SETTINGS CREATE TABLE WA_USER_SETTINGS ( WAUS_U_ID INT, WAUS_KEY VARCHAR(50), WAUS_DATA LONG VARBINARY, PRIMARY KEY (WAUS_U_ID,WAUS_KEY) ); ALTER TABLE WA_USER_SETTINGS ADD FOREIGN KEY (WAUS_U_ID) REFERENCES SYS_USERS (U_ID) ON DELETE CASCADE; * WA_USER_INFO CREATE TABLE WA_USER_INFO ( WAUI_U_ID INT, WAUI_VISIBLE VARCHAR(50), -- concatenation of all fields flags. -- by default each is 1: 11111111... -- 1: public, -- 2: friend, -- 3: private WAUI_TITLE VARCHAR(3), -- 0 WAUI_FIRST_NAME VARCHAR(50), -- 1 WAUI_LAST_NAME VARCHAR(50), -- 2 WAUI_FULL_NAME VARCHAR(100), -- 3 WAUI_GENDER VARCHAR(10), -- 5 WAUI_BIRTHDAY DATETIME, -- 6 WAUI_WEBPAGE VARCHAR(50), -- 7 WAUI_FOAF LONG VARCHAR, -- 8 column type changed below WAUI_MSIGNATURE VARCHAR(255), -- 9 WAUI_ICQ VARCHAR(50), -- 10 WAUI_SKYPE VARCHAR(50), -- 11 WAUI_AIM VARCHAR(50), -- 12 WAUI_YAHOO VARCHAR(50), -- 13 WAUI_MSN VARCHAR(50), -- 14 WAUI_HADDRESS1 VARCHAR(50), -- 15 WAUI_HADDRESS2 VARCHAR(50), -- 15 WAUI_HCODE VARCHAR(50), -- 15 WAUI_HCITY VARCHAR(50), -- 16 WAUI_HSTATE VARCHAR(50), -- 16 WAUI_HCOUNTRY VARCHAR(50), -- 16 WAUI_HTZONE VARCHAR(50), -- 17 WAUI_HPHONE VARCHAR(50), -- 18 WAUI_HMOBILE VARCHAR(50), -- 18 WAUI_BINDUSTRY VARCHAR(50), -- 19 WAUI_BORG VARCHAR(50), -- 20 WAUI_BJOB VARCHAR(50), -- 21 WAUI_BADDRESS1 VARCHAR(50), -- 22 WAUI_BADDRESS2 VARCHAR(50), -- 22 WAUI_BCODE VARCHAR(50), -- 22 WAUI_BCITY VARCHAR(50), -- 23 WAUI_BSTATE VARCHAR(50), -- 23 WAUI_BCOUNTRY VARCHAR(50), -- 23 WAUI_BTZONE VARCHAR(50), -- 24 WAUI_BLAT REAL, -- 47 WAUI_BLNG REAL, -- 47 WAUI_BPHONE VARCHAR(50), -- 25 WAUI_BMOBILE VARCHAR(50), -- 25 WAUI_BREGNO VARCHAR(50), -- 26 WAUI_BCAREER VARCHAR(50), -- 27 WAUI_BEMPTOTAL VARCHAR(50), -- 28 WAUI_BVENDOR VARCHAR(50), -- 29 WAUI_BSERVICE VARCHAR(50), -- 30 WAUI_BOTHER VARCHAR(50), -- 31 WAUI_BNETWORK VARCHAR(50), -- 32 WAUI_SUMMARY LONG VARCHAR, -- 33 WAUI_RESUME LONG VARCHAR, -- 34 WAUI_SEC_QUESTION VARCHAR(20), -- 35 WAUI_SEC_ANSWER VARCHAR(20), -- 36 WAUI_PHOTO_URL LONG VARCHAR, -- 37 WAUI_TEMPLATE VARCHAR(20), -- 38 WAUI_LAT REAL, -- 39 WAUI_LNG REAL, -- 40 WAUI_LATLNG_VISIBLE SMALLINT, -- 41 WAUI_USER_SEARCHABLE SMALLINT, -- 42 - new fields WAUI_AUDIO_CLIP LONG VARCHAR, -- 43 WAUI_FAVORITE_BOOKS LONG VARCHAR, -- 44 WAUI_FAVORITE_MUSIC LONG VARCHAR, -- 45 WAUI_FAVORITE_MOVIES LONG VARCHAR, -- 46 WAUI_SEARCHABLE INT DEFAULT 1, WAUI_LATLNG_HBDEF SMALLINT DEFAULT 0, WAUI_SITE_NAME LONG VARCHAR, WAUI_INTERESTS LONG VARCHAR, -- 48 WAUI_BORG_HOMEPAGE LONG VARCHAR, -- 20 same as BORG, PRIMARY KEY (WAUI_U_ID) ); ALTER TABLE DB.DBA.WA_USER_INFO ADD COLUMN WAUI_JOIN_DATE DATETIME; UPDATE DB.DBA.WA_USER_INFO SET WAUI_JOIN_DATE = NOW()'); CREATE TRIGGER WA_USER_INFO_I AFTER INSERT ON WA_USER_INFO REFERENCING NEW AS N { IF (N.WAUI_JOIN_DATE IS NULL) { SET TRIGGERS OFF; UPDATE WA_USER_INFO SET WAUI_JOIN_DATE = NOW() WHERE WAUI_U_ID = N.WAUI_U_ID; SET TRIGGERS ON; } RETURN; } ; CREATE INDEX WA_GEO ON WA_USER_INFO (WAUI_LNG, WAUI_LAT, WAUI_LATLNG_VISIBLE); * WA_USER_TEXT CREATE TABLE WA_USER_TEXT ( WAUT_U_ID INT, WAUT_TEXT LONG VARCHAR, PRIMARY KEY (WAUT_U_ID) ); CREATE TEXT INDEX ON WA_USER_TEXT (WAUT_TEXT) WITH KEY WAUT_U_ID; * WA_USER_TAG CREATE TABLE WA_USER_TAG ( WAUTG_U_ID INTEGER NOT NULL, -- the id of the user of whose tag it is WAUTG_TAG_ID INTEGER NOT NULL, -- the id of the user who gives the tags WAUTG_FT_ID INTEGER NOT NULL, WAUTG_TAGS VARCHAR NOT NULL, PRIMARY KEY (WAUTG_U_ID, WAUTG_TAG_ID) ); CREATE UNIQUE INDEX SYS_WA_USER_TAG_FT_ID ON WA_USER_TAG (WAUTG_FT_ID); CREATE INDEX WA_USER_TAG_TAG_ID ON WA_USER_TAG (WAUTG_TAG_ID); * WA_TYPES 1 Contains list of registered ODS's application types. 1 Based on this table information ODS can create instances of each application. 1 Typically, each application should store necessary information in this table during installation. For example: INSERT REPLACING WA_TYPES(WAT_NAME, WAT_DESCRIPTION, WAT_TYPE, WAT_REALM) VALUES ('WEBLOG2', 'Blog', 'db.dba.wa_blog2', 'blog2') CREATE TABLE WA_TYPES ( WAT_NAME VARCHAR, WAT_TYPE VARCHAR, WAT_REALM VARCHAR, WAT_DESCRIPTION VARCHAR, WAT_MAXINST INTEGER, PRIMARY KEY (WAT_NAME) ); * WA_MEMBER_MODEL 1 Contains list of available ODS application member models. 1 Now, it's: * *Open* - each ODS user may became application member without any restrictions. Application owner will not be informed. * *Closed* - no-one may become new member * *Invite only* - only application owner can invite ODS users to become application member * *Approval based* - ODS user can became application member only after application owner approval * *Notify owner via E-mail* - each ODS user may became application member without any restrictions. Application owner will be informed automatically by email. CREATE TABLE WA_MEMBER_MODEL ( WMM_ID INT PRIMARY KEY, WMM_NAME VARCHAR NOT NULL ); * WA_MEMBER_TYPE 1 Contains list of available application-specific (for each registered application) membership types. 1 For example: author, reader. Owner is not a member type, and is provided by another ODS mechanism. CREATE TABLE WA_MEMBER_TYPE ( WMT_APP VARCHAR, WMT_NAME VARCHAR, WMT_ID INT, WMT_IS_DEFAULT INT, PRIMARY KEY (WMT_APP, WMT_ID)) ); * WA_INSTANCE 1 Contains detailed information about each of created application instances. 1 WAI_NAME field values *MUST BE EQUAL* to corresponding application_object.wa_name value. If any application wants to change values provided by ODS - it should check equality. 1 WAI_NAME should be unique through the whole table. CREATE TABLE WA_INSTANCE ( WAI_ID INT IDENTITY, WAI_TYPE_NAME VARCHAR REFERENCES WA_TYPES ON DELETE CASCADE, WAI_NAME VARCHAR, WAI_INST WEB_APP, WAI_MEMBER_MODEL INT REFERENCES WA_MEMBER_MODEL, WAI_IS_PUBLIC INT DEFAULT 1, WAI_MEMBERS_VISIBLE INT DEFAULT 1, WAI_DESCRIPTION VARCHAR, WAI_MODIFIED TIMESTAMP, WAI_IS_FROZEN INT, WAI_FREEZE_REDIRECT VARCHAR, WAI_LICENSE LONG VARCHAR, PRIMARY KEY (WAI_NAME) ); CREATE TEXT INDEX ON WA_INSTANCE (WAI_DESCRIPTION) WITH KEY WAI_ID USING FUNCTION; CREATE INDEX WAI_TYPE_NAME_IDX1 ON WA_INSTANCE (WAI_TYPE_NAME) * WA_MEMBER 1 Contains detailed information about each of member of each application instance. 1 WAM_MEMBER_TYPE - corresponds (as foreign key) to WA_MEMBER_TYPE table. 1 WAM_STATUS contains ODS-specific (not application-specific) application member status * 1 - owner * 2 - approved * 3 - awaiting approval from owner * 4 - awaiting approval from user 1 ODS application *SHOULD NOT* work with this table directly (except for READ operation to determine member type and status) 1 All necessary action with this table will be done by ODS itself automatically. CREATE TABLE WA_MEMBER ( WAM_USER INT, WAM_INST VARCHAR REFERENCES WA_INSTANCE ON DELETE CASCADE ON UPDATE CASCADE, WAM_MEMBER_TYPE INT, -- 1=owner, 2=admin, 3=regular, -1=waiting approval, etc. WAM_MEMBER_SINCE DATETIME, WAM_EXPIRES DATETIME, WAM_IS_PUBLIC INT DEFAULT 1, -- Duplicate WAI_IS_PUBLIC WAM_MEMBERS_VISIBLE INT DEFAULT 1, -- Duplicate WAI_MEMBERS_VISIBLE WAM_HOME_PAGE VARCHAR, WAM_APP_TYPE VARCHAR, WAM_DATA ANY, -- app dependent, e.g., last payment info, other. WAM_STATUS INT, PRIMARY KEY (WAM_USER, WAM_INST, WAM_MEMBER_TYPE) ); CREATE INDEX WA_MEMBER_WAM_INST ON WA_MEMBER (WAM_INST); * WA_MEMBER_INSTCOUNT CREATE TABLE WA_MEMBER_INSTCOUNT ( WMIC_TYPE_NAME VARCHAR REFERENCES WA_TYPES ON DELETE CASCADE, WMIC_UID INT REFERENCES SYS_USERS (U_ID) ON DELETE CASCADE, WMIC_INSTCOUNT INTEGER DEFAULT NULL, PRIMARY KEY (WMIC_TYPE_NAME, WMIC_UID) ); * WA_INVITATIONS CREATE TABLE WA_INVITATIONS ( WI_U_ID INT, -- U_ID WI_TO_MAIL VARCHAR, -- email WI_INSTANCE VARCHAR, -- WAI_NAME WI_SID VARCHAR, -- VS_SID WI_STATUS VARCHAR, -- pending, or rejected PRIMARY KEY (WI_U_ID, WI_TO_MAIL, WI_INSTANCE) ); CREATE UNIQUE INDEX WA_INVITATIONS_SID ON WA_INVITATIONS (WI_SID); * WA_DOMAINS CREATE TABLE WA_DOMAINS ( WD_DOMAIN VARCHAR, -- domain name WD_HOST VARCHAR, -- this and rest are the endpoint to access wa via that domain WD_LISTEN_HOST VARCHAR, WD_LPATH VARCHAR, WD_MODEL INT, PRIMARY KEY (WD_DOMAIN) ); * WA_MAP_HOSTS CREATE TABLE WA_MAP_HOSTS ( WMH_HOST VARCHAR, WMH_SVC VARCHAR, WMH_KEY VARCHAR, WMH_ID INTEGER IDENTITY, PRIMARY KEY (WMH_HOST, WMH_SVC) ); * WA_VIRTUAL_HOSTS CREATE TABLE WA_VIRTUAL_HOSTS ( VH_INST INTEGER REFERENCES WA_INSTANCE (WAI_ID) ON DELETE CASCADE, VH_HOST VARCHAR, -- this and rest are the endpoint to access wa via that domain VH_LISTEN_HOST VARCHAR, VH_LPATH VARCHAR, VH_PAGE VARCHAR, PRIMARY KEY (VH_INST,VH_HOST,VH_LISTEN_HOST,VH_LPATH) ); * WA_BLOCKED_IP 1 Used internally by ODS to prevent unlimited login (failed) attempts from the same IP address. CREATE TABLE WA_BLOCKED_IP ( WAB_IP VARCHAR, WAB_DISABLE_UNTIL DATETIME, PRIMARY KEY (WAB_IP) ); * WA_INDUSTRY CREATE TABLE WA_INDUSTRY ( WI_NAME VARCHAR NOT NULL PRIMARY KEY ); * WA_COUNTRY CREATE TABLE WA_COUNTRY ( WC_NAME VARCHAR NOT NULL PRIMARY KEY, WC_CODE VARCHAR, WC_LAT REAL, WC_LNG REAL, WC_CODE VARCHAR ); * WA_PROVINCE CREATE TABLE WA_PROVINCE ( WP_COUNTRY VARCHAR, WP_PROVINCE VARCHAR, PRIMARY KEY (WP_COUNTRY, WP_PROVINCE) ); ---++++ ODS base class web_app CREATE TYPE WEB_APP AS ( WA_NAME VARCHAR, -- i.e., blog WA_MEMBER_MODEL INT -- how registration can be made ) METHOD wa_id_string () RETURNS any, -- string in memberships list METHOD wa_new_inst (login varchar) RETURNS any, -- registering METHOD wa_join_request (login varchar) RETURNS any, -- registering METHOD wa_leave_notify (login varchar) RETURNS any, -- cancel join METHOD wa_state_edit_form (stream any) RETURNS any, -- emit a state edit form into the stream present this to owner for setting the state METHOD wa_membership_edit_form (stream any) RETURNS any, -- emit a membership edit form into the stream present this to owner for setting the state METHOD wa_front_page (stream any) RETURNS any, -- emit a front page into the stream present this to owner for setting the state METHOD wa_state_posted (post any, stream any) RETURNS any, -- process a post, updating state and writing a reply into the stream for web interface METHOD wa_periodic_activity () RETURNS any, -- send reminders, invoices, refresh content whatever is regularly done. METHOD wa_drop_instance () RETURNS any, METHOD wa_private_url () RETURNS any, METHOD wa_notify_member_changed (account int, otype int, ntype int, odata any, ndata any, ostatus any, nstatus any) RETURNS any, METHOD wa_member_data (u_id int, stream any) RETURNS any, -- application specific membership attributes METHOD wa_member_data_edit_form (u_id int, stream any) RETURNS any, -- application specific membership attributes edit form METHOD wa_class_details () RETURNS varchar, -- returns details about the nature of the instance class METHOD wa_https_supported () RETURNS int, METHOD wa_dashboard () RETURNS any, METHOD wa_home_url () RETURNS varchar, METHOD wa_dashboard () RETURNS any, METHOD wa_addition_urls () RETURNS any, METHOD wa_addition_instance_urls () RETURNS any, METHOD wa_addition_instance_urls (in lpath any) RETURNS any, METHOD wa_domain_set (in domain varchar) RETURNS any, METHOD wa_size () RETURNS int, METHOD wa_front_page_as_user (in stream any, in user_name varchar) RETURNS any, METHOD wa_rdf_url (in vhost varchar, in lhost varchar) RETURNS varchar, METHOD wa_post_url (in vhost varchar, in lhost varchar, in inst_name varchar, in post any) RETURNS varchar, METHOD wa_domain_set(in domain varchar) RETURNS any, wa_new_instance_url() RETURNS any, wa_edit_instance_url() RETURNS any ) ; ---++++ ODS methods for base class web_app CREATE METHOD wa_id_string () for web_app { return ''; }; CREATE METHOD wa_dashboard () for web_app { return ''; }; CREATE METHOD wa_member_data (in u_id int, inout stream any) for web_app { return 'N/A'; }; CREATE METHOD wa_member_data_edit_form (in u_id int, inout stream any) for web_app { return; }; CREATE METHOD wa_membership_edit_form (inout stream any) for web_app { return; }; CREATE METHOD wa_front_page (inout stream any) for web_app { return; }; CREATE METHOD wa_front_page_as_user (inout stream any, in user_name varchar) for web_app { return; }; CREATE METHOD wa_size () for web_app { return 0; }; CREATE METHOD wa_join_request (in login varchar) for web_app { return; }; CREATE METHOD wa_class_details() for web_app { return null; }; CREATE METHOD wa_state_edit_form (inout stream any) for web_app { return; }; CREATE METHOD wa_state_posted (in post any, inout stream any) for web_app { return; }; CREATE METHOD wa_home_url () for web_app { return null; }; CREATE METHOD wa_rdf_url (in vhost varchar, in lhost varchar) for web_app { return null; }; CREATE METHOD wa_post_url (in vhost varchar, in lhost varchar, in inst_name varchar, in post any) for web_app { return null; }; CREATE METHOD wa_addition_urls () for web_app { return null; }; CREATE METHOD wa_addition_instance_urls () for web_app { return null; }; CREATE METHOD wa_addition_instance_urls (in lpath any) for web_app { return null; }; CREATE METHOD wa_domain_set (in domain varchar) for web_app { return self; }; CREATE METHOD wa_private_url () for web_app { return null; }; CREATE METHOD wa_https_supported () for web_app { return; }; CREATE METHOD wa_drop_instance () for web_app { for select VH_HOST as _host, VH_LISTEN_HOST as _lhost, VH_LPATH as _path, WAI_INST as _inst from WA_INSTANCE, WA_VIRTUAL_HOSTS where WAI_NAME = self.wa_name and WAI_ID = VH_INST and VH_HOST not like '%ini%' do { declare inst web_app; inst := _inst; -- Application additional URL declare len, i, ssl_port integer; declare cur_add_url, addons any; addons := inst.wa_addition_urls(); len := length(addons); i := 0; while (i < len) { cur_add_url := addons [i]; VHOST_REMOVE( vhost=>_host, lhost=>_lhost, lpath=>cur_add_url[2]); i := i + 1; } -- Instance additional URL addons := inst.wa_addition_instance_urls(_path); len := length(addons); i := 0; while (i < len) { cur_add_url := addons[i]; VHOST_REMOVE( vhost=>_host, lhost=>_lhost, lpath=>cur_add_url[2]); i := i + 1; } -- Home URL VHOST_REMOVE(vhost=>_host, lhost=>_lhost, lpath=>_path); } delete from WA_MEMBER where WAM_INST = self.wa_name; delete from WA_INSTANCE where WAI_NAME = self.wa_name; }; CREATE METHOD wa_periodic_activity () for web_app { return; }; CREATE METHOD wa_new_inst (in login varchar) for web_app { declare uid, id, tn, is_pub, is_memb_visb any; uid := (select U_ID from SYS_USERS where U_NAME = login); select WAI_ID, WAI_TYPE_NAME, WAI_IS_PUBLIC, WAI_MEMBERS_VISIBLE into id, tn, is_pub, is_memb_visb from WA_INSTANCE where WAI_NAME = self.wa_name; -- WAM_STATUS = 1 means OWNER -- XXX: check this why is off --set triggers off; insert into WA_MEMBER (WAM_USER, WAM_INST, WAM_MEMBER_TYPE, WAM_STATUS, WAM_HOME_PAGE, WAM_APP_TYPE, WAM_IS_PUBLIC, WAM_MEMBERS_VISIBLE) values (uid, self.wa_name, 1, 1, wa_set_url_t (self), tn, is_pub, is_memb_visb); --set triggers on; return id; }; CREATE METHOD wa_new_instance_url () for web_app{ return 'new_inst.vspx'; }; CREATE METHOD wa_edit_instance_url () for web_app{ return 'edit_inst.vspx'; }; ---++++App membership processing ---+++PL APIs ---++++OpenID APIs Location: openid.sql | OPENID_INIT |Creates user "OpenID" | | | | | | yadis | Automatic creation of user yadis doc format.| | | |in uname varchar, |User name | | |RESULT is XML composed yadis doc for the user| | | | | | | server | Depending on the openid mode performs: associates, checkid_immediate, checkid_setup, check_authentication | | | |in "openid.mode" varchar := 'unknown', | OpenID of the Server | | |RESULT depends on the openid mode| | | | | | | associate | Creates xenc_key_3DES key and inserts it into SERVER_SESSIONS | | | |in "openid.mode" varchar := 'unknown', | | | |in assoc_type varchar := 'HMAC-SHA1', | | | |in session_type varchar := '', | | | |in dh_modulus varchar := null, | | | |in dh_gen varchar := null, | | | |in dh_consumer_public varchar := null | | | |RESULT is string| | | | | | | checkid_immediate | Check OpenID verification| | | |in _identity varchar, | | | |in assoc_handle varchar := null, | | | |in return_to varchar, | | | |in trust_root varchar := null, | | | |in sid varchar, | | | |in flag int := 0, -- called via checkid_setup | | | |in sreg_required varchar := null, | | | |in sreg_optional varchar := null, | | | |in policy_url varchar := null | | | |RESULT is empty string| | | | | | | checkid_setup | Setup OpenID verification and perform check| | | |in _identity varchar, | | | |in assoc_handle varchar := null, | | | |in return_to varchar, | | | |in trust_root varchar := null, | | | |in sid varchar, | | | |in sreg_required varchar := null, | | | |in sreg_optional varchar := null, | | | |in policy_url varchar := null | | | |RESULT is empty string| | | | | | | check_authentication | Check for OpenID authentication| | | |in assoc_handle varchar, | | | |in sig varchar, | | | |in signed varchar, | | | |in invalidate_handle varchar := null, | | | |in params any := null, | | | |in sid varchar | | | |RESULT is empty string| | | | | | | check_signature | Check OpenID authentication comparing the xenc_key-s| | | |in params varchar | | | |RESULT is integer, 1 if the signature matches, 0 if not| | CategoryODS CategoryOpenSource CategoryAPI