VAD Do not editSTICKER '/ec2exts'); vhost_define(lpath => '/ec2exts', ppath => '/DAV/VAD/ec2exts/', is_dav => 1, is_brws => 0, vsp_user => 'dba', def_page => 'index.vspx'); vhost_remove(vhost => '*sslini*', lhost => '*sslini*', lpath => '/ec2exts'); vhost_define(vhost => '*sslini*', lhost => '*sslini*', lpath => '/ec2exts', ppath => '/DAV/VAD/ec2exts/', is_dav => 1, is_brws => 0, vsp_user => 'dba', def_page => 'index.vspx'); vad_load_sql_file('/DAV/VAD/ec2exts/sql/tables.sql', 1, 'report', 1); vad_load_sql_file('/DAV/VAD/ec2exts/sql/procedures.sql', 1, 'report', 1); ]]> '/ec2exts'); vhost_remove(vhost => '*sslini*', lhost => '*sslini*', lpath => '/ec2exts'); vad_load_sql_file('/DAV/VAD/ec2exts/sql/delete.sql', 1, 'report', 1); ]]> ec2exts/bts_edit.vspx 'add') self.erecid := cast (get_keyword ('rec', self.vc_event.ve_params) as integer); ]]>

Backup Transfer Location

( )
ec2exts/bts_main.vspx

Automatic Backup Transfers

Here you can instruct Virtuoso to automatically upload your on-line backup files to a remote location.

Your database backup files will be transfered automatically to the following locations:

Proto Host Path Enabled Uploaded Status Action
No locations have been defined.

Your current on-line backup contains segments.
Uploading of segments takes place after the next on-line backup has run. ( )
No database backups have currently been made. ()

ec2exts/bts_restore_status.vspx

Restore Status

Current status:

Time Status Message
There is no status information.

The next phase will re-assemble the database from the downloaded backup files, and restart the server.

This is the last chance to abort.

ec2exts/bts_restore.vspx

Restore a Remote Backup

This procedure will initiate a restore from backup operation.
After filling in this form, the backup files will be transfered from their remote location.
When that has completed, you will be guided through additional steps.

Please specify the location of your remote backup.

ec2exts/dyndns_edit.vspx

Dynamic DNS Registration

Check this if you have a free account.
self.account.ufl_value or _hostnames <> self.hostnames.ufl_value or self.force_update.ufl_selected) { update DB.DBA.S3_DYNDNS_CONFIG set last_updated = null, last_err = '', last_ip = '' where recid = self.erecid; } self.vc_redirect ('dyndns_main.vspx'); return; ]]>
ec2exts/dyndns_main.vspx 0) { db.dba.s3_get_current_ip (self.current_ip); if (_last_ip <> self.current_ip or _last_updated is null or (_auto_update <> 0 and datediff ('day', _last_updated, now ()) >= 28)) { self.require_update := 1; } } ]]>

Dynamic DNS Registration

Here you instruct Virtuoso to update the nameserver at dyndns.com with the
current IP address of your AMI. Visit their site to create a free account for yourself.

There is currently no active dyndns.com account. () Your Dynamic DNS registration is up-to-date. () Your Dynamic DNS registration needs to be updated. ()

The IP address of this instance was detected to be .

ec2exts/dyndns.vspx'

Dynamic DNS Registration

You are not logged in.

Hello . ()

self.account.ufl_value or _hostnames <> self.hostnames.ufl_value) { dbg_printf('RESET TS'); update DB.DBA.S3_DYNDNS_CONFIG set last_updated = null, last_err = '' where recid = self.erecid; } --self.vc_redirect ('bts_main.vspx'); self.vc_data_bind (e); return; ]]>
ec2exts/index.vspx

Extensions for Amazon EC2 Images

You are not logged in.

Hello . ()

This is a collection of utilities that helps you to migrate your
data from one AMI to another.

ec2exts/sql/dele2.sqldrop procedure db.dba.s3_fyi_set_partner; drop procedure db.dba.s3_fyi_parse_name; drop function db.dba.s3_fyi_call; drop procedure db.dba.s3_fyi_register; drop procedure db.dba.s3_fyi_cancel; drop procedure db.dba.s3_fyi_user_list; drop view db.dba.fyi_user_list; drop procedure db.dba.s3_fyi_check; drop view db.dba.fyi_check; drop procedure db.dba.s3_fyi_query_balance; drop procedure db.dba.s3_fyi_txn_list; drop procedure db.dba.s3_fyi_extend; drop procedure db.dba.s3_fyi_manage; drop procedure db.dba.s3_fyi_namespinner; drop view db.dba.fyi_namespinner; drop procedure db.dba.s3_fyi_pagetag_add; drop procedure db.dba.s3_fyi_pagetag_modify; drop procedure db.dba.s3_fyi_pagetag_delete; drop procedure db.dba.test; ec2exts/sql/delete.sqltdrop table db.dba.s3_backup_schedule; drop table db.dba.s3_restore_logmsgs; drop table db.dba.s3_dyndns_config; drop procedure db.dba.s3_login; drop procedure db.dba.s3_logout; drop function db.dba.s3_request; drop procedure db.dba.s3_s3_upload; drop procedure db.dba.s3_s3_download; drop procedure db.dba.s3_ftp_upload; drop procedure db.dba.s3_ftp_download; drop procedure db.dba.s3_http_upload; drop procedure db.dba.s3_http_download; drop procedure db.dba.s3_upload; drop procedure db.dba.s3_download; drop procedure db.dba.s3_backup_run; drop procedure db.dba.s3_restore_status; drop procedure db.dba.s3_restore_run; drop procedure db.dba.s3_sql_user_password; drop procedure db.dba.s3_sql_user_password_check; drop procedure db.dba.backup_completed; drop procedure db.dba.s3_get_current_ip; drop procedure db.dba.s3_send_dyndns_update; drop procedure db.dba.s3_update_dyndns; ec2exts/sql/proc2notes.txt*select * from fyi_namespinner where firstname='John' and lastname='Doe'; STATUS=HTTP/1.1 200 OK CONTENT= OK doe1.jonny.name doe1@jonny.name 0 doe1.jonnie.name doe1@jonnie.name 1 jonnie1.jonnie.name jonnie1@jonnie.name 2 =============================================================================== s3_fyi_register (null, null, u_account => 'tester123.sandboxtest.name', u_credentials => 'sosecret', u_email => 'tester123@yahoo.com', nowelcomeemail => 1, u_firstname=>'John', u_lastname=>'Doe', u_country=>'US', u_address => 'fake address', u_city => 'fake city', u_postcode=>'1324'); STATUS=HTTP/1.1 200 OK CONTENT= OK By registering and using your service with us, you accept the terms and conditions found at http://freeyourid.com/tcs 2008-06-02 14:27:15 =============================================================================== select * from fyi_check where username = 'tester123.sandboxtest.name'; STATUS=HTTP/1.1 200 OK CONTENT= OK tester123.sandboxtest.name tester123@yahoo.com US tester123@sandboxtest.name tester123.sandboxtest.name tester123.sandboxtest.name gnr.com tester123@yahoo.com 2008-03-04 14:27:10 2008-06-02 14:27:10 =============================================================================== s3_fyi_cancel (null, null, u_account => 'tester123.sandboxtest.name'); =============================================================================== s3_fyi_manage ('tester123.sandboxtest.name', 'sosecret', u_firstname => 'John', u_lastname => 'Doe', u_city => 'Amsterdam', u_country => 'nl', u_address => 'Damrak 1'); URL=http://www.freeyourid.com/api.php?command=manage&auth_username=tester123.sandboxtest.name&auth_password=sosecret&firstname=John&lastname=Doe&address1=Damrak%201&city=Amsterdam&country=nl&output_type=xml STATUS=HTTP/1.1 200 OK CONTENT= OK tester123.sandboxtest.name John Doe tester123@yahoo.com nl tester123@sandboxtest.name tester123.sandboxtest.name tester123.sandboxtest.name gnr.com tester123@yahoo.com 2008-03-04 14:27:10 2008-06-02 14:27:10 =============================================================================== s3_fyi_query_balance(null,null); STATUS=HTTP/1.1 200 OK CONTENT= OK 0 FAke, try real acct =============================================================================== s3_fyi_txn_list (null, null, '2008-03-01 12:00:00', '2008-03-31 12:00:00'); STATUS=HTTP/1.1 200 OK CONTENT= OK FAke, try real acct =============================================================================== s3_fyi_user_list (null, null, 0); STATUS=HTTP/1.1 200 OK CONTENT= OK test2.gnrtestfeb27.name 2007-06-26 16:52:57 2007-10-09 16:52:57 gnrtest.toby16.name 2007-07-04 16:01:16 2007-10-17 16:01:16 steve.webb.name 2007-07-05 11:52:04 2007-10-18 11:52:04 hh.haugnes.name 2007-07-18 14:27:02 2007-10-31 14:27:02 toby.ignore.name 2007-07-26 15:36:53 2007-11-08 15:36:53 gnrtest.toby.name 2007-07-27 11:27:39 2007-11-09 11:27:39 test25656.sandboxtest.name 2008-02-05 11:19:32 2008-05-05 11:19:32 test.gnrtest.name 2008-02-29 18:46:36 2008-05-29 18:46:36 testkarthik.sandboxtest.name 2008-03-03 17:21:19 2008-06-01 17:21:19 test4987.gnrtest.name 2008-03-03 23:22:39 2008-06-01 23:22:39 test15687.sandboxtest.name 2008-03-04 13:57:49 2008-06-02 13:57:49 tester123.sandboxtest.name 2008-03-04 14:27:10 2008-06-02 14:27:10 ec2exts/sql/proc2.sqlhcreate procedure db.dba.s3_fyi_login ( in p_account varchar, -- partner username in p_credentials varchar) -- partner password { if (p_account is null or p_credentials is null) { -- use test credentials if no credentials given p_account := 'sandbox'; p_credentials := 'verysecret'; } registry_set ('fyi_id', p_account); registry_set ('fyi_pw', pwd_magic_calc (p_account, p_credentials)); } ; create procedure db.dba.s3_fyi_logout () { registry_remove ('fyi_id'); registry_remove ('fyi_pw'); } ; create procedure db.dba.s3_fyi_set_partner ( out p_account varchar, -- partner username out p_credentials varchar) -- partner password { -- use stored credentials if none specified if (p_account is null and p_credentials is null) { declare account, credentials varchar; account := registry_get ('fyi_id'); credentials := registry_get ('fyi_pw'); if (isstring (account) and isstring (credentials)) { p_account := account; p_credentials := pwd_magic_calc (account, credentials, 1); } } -- make sure they're valid if (p_account is null or p_credentials is null) signal ('FYI08', 'missing credentials'); } ; create procedure db.dba.s3_fyi_parse_name ( in u_account varchar, out username varchar, out dotnameemail varchar) { dotnameemail := u_account; username := u_account; -- first@last.name if (regexp_parse ('^[a-zA-Z0-9\\-]{1,63}@[a-zA-Z0-9\\-]{3,63}\\.name\$', u_account, 0) is not null) { aset (username, strchr (username, '@'), 46); } -- first.last.name else if (regexp_parse ('^[a-zA-Z0-9\\-]{1,63}\\.[a-zA-Z0-9\\-]{3,63}\\.name\$', u_account, 0) is not null) { aset (dotnameemail, strchr (dotnameemail, '.'), 64); } else signal ('FYI04', 'Invalid .name email address'); } ; create function db.dba.s3_fyi_call ( in is_partner integer, in account varchar, in credentials varchar, in arguments vector) returns any { declare n, j integer; declare requrl varchar; declare ret_hdrs varchar; declare status varchar; declare content varchar; declare k, v varchar; declare xmldoc any; log_message ('Freeyourid call'); n := length (arguments); if (n < 1 or mod (n + 1, 2) <> 0) signal ('FYI00', 'Invalid arguments'); v := aref (arguments, 0); requrl := 'http://www.freeyourid.com/api.php?command=' || v; if (account is not null and credentials is not null) { if (is_partner) requrl := requrl || '&auth_un=' || account || '&auth_pw=' || credentials; else requrl := requrl || '&auth_username=' || account || '&auth_password=' || credentials; } j := 1; while (j < n - 1) { k := aref (arguments, j); v := aref (arguments, j + 1); if (v is not null) { v := sprintf ('%U', cast (v as varchar)); requrl := requrl || '&' || k || '=' || v; } j := j + 2; } requrl := requrl || '&output_type=xml'; dbg_printf('URL=%s',requrl); if (1) { status := 'HTTP/1.1 200 OK'; if (aref (arguments, 0) = 'namespinner') content := ' OK doe1.jonny.name doe1@jonny.name 0 doe1.jonnie.name doe1@jonnie.name 1 jonnie1.jonnie.name jonnie1@jonnie.name 2 '; else if (aref (arguments, 0) = 'register') content := ' OK By registering and using your service with us, you accept the terms and conditions found at http://freeyourid.com/tcs 2008-06-02 13:57:49 '; else if (aref (arguments, 0) = 'check' or aref (arguments, 0) = 'manage') content := ' OK test.example.name test7597@example.name UK test@test.name test.gnrtest.name test.gnrtest.name http://google.com test7597@gmail.com 2008-02-05 11:26:32 2008-05-05 11:26:32 '; else if (aref (arguments, 0) = 'query_balance') content := ' OK 0 '; else if (aref (arguments, 0) = 'user_list') content := ' OK test2.gnrtestfeb27.name 2007-06-26 16:52:57 2007-10-09 16:52:57 gnrtest.toby16.name 2007-07-04 16:01:16 2007-10-17 16:01:16 steve.webb.name 2007-07-05 11:52:04 2007-10-18 11:52:04 hh.haugnes.name 2007-07-18 14:27:02 2007-10-31 14:27:02 toby.ignore.name 2007-07-26 15:36:53 2007-11-08 15:36:53 gnrtest.toby.name 2007-07-27 11:27:39 2007-11-09 11:27:39 test25656.sandboxtest.name 2008-02-05 11:19:32 2008-05-05 11:19:32 test.gnrtest.name 2008-02-29 18:46:36 2008-05-29 18:46:36 testkarthik.sandboxtest.name 2008-03-03 17:21:19 2008-06-01 17:21:19 test4987.gnrtest.name 2008-03-03 23:22:39 2008-06-01 23:22:39 test15687.sandboxtest.name 2008-03-04 13:57:49 2008-06-02 13:57:49 tester123.sandboxtest.name 2008-03-04 14:27:10 2008-06-02 14:27:10 '; else content := ' OK Testing error messages '; } else { signal ('FYI01', 'NOT IMPLEMENTED'); content := http_get (requrl, ret_hdrs, 'GET'); status := rtrim (ret_hdrs[0], '\r\n'); dbg_printf('STATUS=%s', status); dbg_printf('CONTENT=%s', content); } if (not status like 'HTTP/1._ %') signal ('FYI01', 'Unexpected http status "' || status || '"'); status := right (status, length (status) - 9); if (not status like '2%') signal ('FYI02', 'Server replied "' || status || '"'); xmldoc := xml_tree_doc (content); v := cast (xpath_eval ('/response/result', xmldoc, 1) as varchar); if (v is null or (v <> 'OK' and v <> 'ERROR')) signal ('FYI03', 'Unexpected xml response'); if (v = 'ERROR') { v := cast (xpath_eval ('/response/error', xmldoc, 1) as varchar); signal ('FYI04', v); } return xmldoc; } ; create procedure db.dba.s3_fyi_register ( in u_account varchar, -- x@y.name or x.y.name in u_credentials varchar, -- user's password in u_email varchar, -- mail forwarded to in u_urlforward varchar := null, -- optional: http forwarded to in u_firstname varchar := null, -- optional: user's first name in u_lastname varchar := null, -- optional: user's last name in u_address varchar := null, -- optional: user's address in u_city varchar := null, -- optional: user's city in u_postcode varchar := null, -- optional: user's postcode/zip in u_country varchar := null, -- optional: user's country in u_mobile varchar := null, -- optional: user's mobile number in nowelcomeemail integer := 0, -- optional: send welcome e-mail? in noopenid integer := 0, -- optional: do OpenID? in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare opts any; declare dotnameemail varchar; declare username varchar; declare i integer; declare tree any; db.dba.s3_fyi_set_partner (p_account, p_credentials); if (u_account is null or u_email is null) signal ('FYI08', 'missing arguments to fyi_register'); db.dba.s3_fyi_parse_name (u_account, username, dotnameemail); -- validate password if (regexp_parse ('^[0-9a-zA-Z\\\$\\.\\/\\-_]{5,}\$', u_credentials, 0) is null) signal ('FYI05', 'Invalid password - too short, or invalid characters'); -- validate e-mail --if (regexp_parse ('^[a-zA-Z0-9\\-]{1,63}@[a-zA-Z0-9\\-]{3,63}[a-zA-Z]{2,6}\$', u_email, 0) is null) -- signal ('FYI06', 'Invalid e-mail adress'); opts := vector ('register', 'dotnameemail', dotnameemail, 'email', u_email, 'password', u_credentials, 'password2', u_credentials, 'country', u_country); -- validate website if (u_urlforward is not null) { if (regexp_parse ('^[0-9a-zA-Z\\-\\.:\\/\\?\\&_=\\~]{3,255}\$', u_urlforward, 0) is null) signal ('FYI07', 'Invalid website url'); opts := vector_concat (opts, vector ('urlforward1', u_urlforward)); } if (nowelcomeemail = 1) opts := vector_concat (opts, vector ('nowelcomeemail', 1)); if (noopenid = 1) opts := vector_concat (opts, vector ('noopenid', 1)); tree := db.dba.s3_fyi_call (1, p_account, p_credentials, opts); if (u_firstname is not null or u_lastname is not null or u_address is not null or u_city is not null or u_postcode is not null or u_mobile is not null) { opts := vector ('manage', 'firstname', u_firstname, 'lastname', u_lastname, 'address1', u_address, 'city', u_city, 'postcode', u_postcode, 'mobile', u_mobile); db.dba.s3_fyi_call (0, username, u_credentials, opts); } declare FREETRIAL_END, MESSAGE varchar; result_names (FREETRIAL_END, MESSAGE); result (cast (xpath_eval ('/response/freetrial_end', tree) as varchar), cast (xpath_eval ('/response/message', tree) as varchar)); } ; create procedure db.dba.s3_fyi_cancel ( in u_account varchar, -- x@y.name or x.y.name in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare dotnameemail varchar; declare username varchar; db.dba.s3_fyi_set_partner (p_account, p_credentials); if (u_account is null) signal ('FYI08', 'missing arguments to fyi_cancel'); db.dba.s3_fyi_parse_name (u_account, username, dotnameemail); db.dba.s3_fyi_call (1, p_account, p_credentials, vector ('cancel', 'username', username)); } ; create procedure db.dba.s3_fyi_user_list ( in start integer := 0, -- optional: start number in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare tree any; declare elems any; db.dba.s3_fyi_set_partner (p_account, p_credentials); tree := db.dba.s3_fyi_call (1, p_account, p_credentials, vector ('user_list', 'start', start)); elems := xpath_eval ('/response/elements/element', tree, 0); declare USERNAME, CREATED_DATE, EXPIRY_DATE varchar; result_names (USERNAME, CREATED_DATE, EXPIRY_DATE); foreach (any e in elems) do { result (cast (xpath_eval ('username', e) as varchar), cast (xpath_eval ('created_date', e) as varchar), cast (xpath_eval ('expiry_date', e) as varchar)); } } ; drop view db.dba.fyi_user_list ; create procedure view db.dba.fyi_user_list as db.dba.s3_fyi_user_list (START, ACCOUNT, CREDENTIALS) (USERNAME varchar, CREATED_DATE varchar, EXPIRY_DATE varchar) ; create procedure db.dba.s3_fyi_check ( in u_account varchar, -- x@y.name or x.y.name in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare _dotnameemail varchar; declare _username varchar; declare tree any; declare elems any; db.dba.s3_fyi_set_partner (p_account, p_credentials); if (u_account is null) signal ('FYI08', 'missing arguments to fyi_check'); db.dba.s3_fyi_parse_name (u_account, _username, _dotnameemail); tree := db.dba.s3_fyi_call (1, p_account, p_credentials, vector ('check', 'username', _username)); declare USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRY, DOTNAMEEMAIL, DOMAIN, OPENID, URLFORWARD, EMAILFORWARD, CREATED_DATE, EXPIRY_DATE varchar; result_names (USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRY, DOTNAMEEMAIL, DOMAIN, OPENID, URLFORWARD, EMAILFORWARD, CREATED_DATE, EXPIRY_DATE); result (cast (xpath_eval ('/response/username', tree) as varchar), cast (xpath_eval ('/response/firstname', tree) as varchar), cast (xpath_eval ('/response/lastname', tree) as varchar), cast (xpath_eval ('/response/email', tree) as varchar), cast (xpath_eval ('/response/country', tree) as varchar), cast (xpath_eval ('/response/dotnameemail', tree) as varchar), cast (xpath_eval ('/response/domain', tree) as varchar), cast (xpath_eval ('/response/openid', tree) as varchar), cast (xpath_eval ('/response/urlforward1', tree) as varchar), cast (xpath_eval ('/response/emailforward1', tree) as varchar), cast (xpath_eval ('/response/created_date', tree) as varchar), cast (xpath_eval ('/response/expiry_date', tree) as varchar)); } ; drop view db.dba.fyi_check ; create procedure view db.dba.fyi_check as db.dba.s3_fyi_check (USERNAME, ACCOUNT, CREDENTIALS) (USERNAME varchar, FIRSTNAME varchar, LASTNAME varchar, EMAIL varchar, COUNTRY varchar, DOTNAMEEMAIL varchar, DOMAIN varchar, OPENID varchar, URLFORWARD varchar, EMAILFORWARD varchar, CREATED_DATE varchar, EXPIRY_DATE varchar) ; create procedure db.dba.s3_fyi_query_balance ( in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare tree any; db.dba.s3_fyi_set_partner (p_account, p_credentials); tree := db.dba.s3_fyi_call (1, p_account, p_credentials, vector ('query_balance')); declare BALANCE varchar; result_names (BALANCE); result (cast (xpath_eval ('/response/balance', tree) as varchar)); } ; create procedure db.dba.s3_fyi_txn_list ( in startdate varchar, -- YYYY-MM-DD HH:MM:SS in enddate varchar, -- YYYY-MM-DD HH:MM:SS in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare tree any; db.dba.s3_fyi_set_partner (p_account, p_credentials); tree := db.dba.s3_fyi_call (1, p_account, p_credentials, vector ('txn_list', 'startdate', startdate, 'enddate', enddate)); -- TODO DECOMPOSE XML DOCUMENT -- sandbox returns just OK; check this later } ; -- TODO PROCEDURE VIEW ON TXN_LIST create procedure db.dba.s3_fyi_extend ( in u_account varchar, -- x@y.name or x.y.name in period varchar, -- see below in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare dotnameemail varchar; declare username varchar; declare item_id integer; db.dba.s3_fyi_set_partner (p_account, p_credentials); if (u_account is null or item_id is null) signal ('FYI08', 'missing arguments to fyi_extend'); if (period = '10 years') item_id := 2; else if (period = '1 year') item_id := 3; else if (period = '3 months') item_id := 4; else if (period = '1 month') item_id := 5; else signal ('FYI08', 'invalid period argument to fyi_extend'); db.dba.s3_fyi_parse_name (u_account, username, dotnameemail); db.dba.s3_fyi_call (1, p_account, p_credentials, vector ('extend', 'username', username, 'item_id', item_id)); } ; create procedure db.dba.s3_fyi_manage ( in u_account varchar, -- username --in u_credentials varchar, -- password in u_email varchar := null, -- optional: e-mail forwarded to in u_urlforward varchar := null, -- optional: http forwarded to in u_firstname varchar := null, -- optional: user's first name in u_lastname varchar := null, -- optional: user's last name in u_address varchar := null, -- optional: user's address in u_city varchar := null, -- optional: user's city in u_postcode varchar := null, -- optional: user's postcode/zip in u_country varchar := null, -- optional: user's country in u_mobile varchar := null, -- optional: user's mobile number in p_account varchar := null, -- partner username in p_credentials varchar := null) -- partner password { declare tree any; /* if (u_account is null or u_credentials is null) signal ('FYI08', 'missing arguments to fyi_manage'); tree := db.dba.s3_fyi_call (0, u_account, u_credentials, vector ('manage', 'newurlforward1', u_urlforward, 'newemailforward1', u_email, 'firstname', u_firstname, 'lastname', u_lastname, 'mobile', u_mobile, 'address1', u_address, 'city', u_city, 'postcode', u_postcode, 'country', u_country)); */ db.dba.s3_fyi_set_partner (p_account, p_credentials); if (u_account is null) signal ('FYI08', 'missing arguments to fyi_manage'); tree := db.dba.s3_fyi_call (1, p_account, p_credentials, vector ('manage', 'username', u_account, 'newurlforward1', u_urlforward, 'newemailforward1', u_email, 'firstname', u_firstname, 'lastname', u_lastname, 'mobile', u_mobile, 'address1', u_address, 'city', u_city, 'postcode', u_postcode, 'country', u_country)); -- same as check declare USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRY, DOTNAMEEMAIL, DOMAIN, OPENID, URLFORWARD, EMAILFORWARD, CREATED_DATE, EXPIRY_DATE varchar; result_names (USERNAME, FIRSTNAME, LASTNAME, EMAIL, COUNTRY, DOTNAMEEMAIL, DOMAIN, OPENID, URLFORWARD, EMAILFORWARD, CREATED_DATE, EXPIRY_DATE); result (cast (xpath_eval ('/response/username', tree) as varchar), cast (xpath_eval ('/response/firstname', tree) as varchar), cast (xpath_eval ('/response/lastname', tree) as varchar), cast (xpath_eval ('/response/email', tree) as varchar), cast (xpath_eval ('/response/country', tree) as varchar), cast (xpath_eval ('/response/dotnameemail', tree) as varchar), cast (xpath_eval ('/response/domain', tree) as varchar), cast (xpath_eval ('/response/openid', tree) as varchar), cast (xpath_eval ('/response/urlforward1', tree) as varchar), cast (xpath_eval ('/response/emailforward1', tree) as varchar), cast (xpath_eval ('/response/created_date', tree) as varchar), cast (xpath_eval ('/response/expiry_date', tree) as varchar)); } ; create procedure db.dba.s3_fyi_namespinner ( in u_firstname varchar, -- user's first name in u_lastname varchar, -- user's last name in num_results integer := 3)-- optional, default num_results = 3 { declare tree any; declare elems any; if (u_firstname is null or u_lastname is null) signal ('FYI08', 'missing arguments to fyi_namespinner'); tree := db.dba.s3_fyi_call (0, null, null, vector ('namespinner', 'firstname', u_firstname, 'lastname', u_lastname, 'num_results', num_results)); declare DOMAIN, EMAIL, RANK varchar; result_names (DOMAIN, EMAIL, RANK); elems := xpath_eval ('/response/elements/element', tree, 0); foreach (any e in elems) do { result (cast (xpath_eval ('domain', e) as varchar), cast (xpath_eval ('email', e) as varchar), cast (xpath_eval ('rank', e) as integer)); } } ; drop view db.dba.fyi_namespinner ; create procedure view db.dba.fyi_namespinner as db.dba.s3_fyi_namespinner (FIRSTNAME, LASTNAME, NUM_RESULTS) (DOMAIN varchar, EMAIL varchar, RANK integer) ; create procedure db.dba.s3_fyi_pagetag_add ( in u_account varchar, -- x@y.name or x.y.name in u_credentials varchar, -- user's password in page varchar, -- Name of the page tag, eg. "blog" in url varchar) -- URL to which the page tag will point { if (u_account is null or u_credentials is null or page is null or url is null) signal ('FYI08', 'missing arguments to fyi_manage'); db.dba.s3_fyi_call (0, u_account, u_credentials, vector ('pagetag_add', 'page', page, 'url', url)); } ; create procedure db.dba.s3_fyi_pagetag_modify ( in u_account varchar, -- x@y.name or x.y.name in u_credentials varchar, -- user's password in page varchar, -- Name of the page tag, eg. "blog" in newpage varchar) -- New name of the page tag { if (u_account is null or u_credentials is null or page is null or newpage is null) signal ('FYI08', 'missing arguments to fyi_pagetag_modify'); db.dba.s3_fyi_call (0, u_account, u_credentials, vector ('pagetag_modify', 'page', page, 'newpage', newpage)); } ; create procedure db.dba.s3_fyi_pagetag_delete ( in u_account varchar, -- x@y.name or x.y.name in u_credentials varchar, -- user's password in page varchar) -- Name of the page tag, eg. "blog" { if (u_account is null or u_credentials is null or page is null) signal ('FYI08', 'missing arguments to fyi_pagetag_delete'); db.dba.s3_fyi_call (0, u_account, u_credentials, vector ('pagetag_delete', 'page', page)); } ; create procedure db.dba.s3_fyi_pagetag_listall ( in u_account varchar, -- x@y.name or x.y.name in u_credentials varchar) -- user's password { declare tree any; if (u_account is null or u_credentials is null) signal ('FYI08', 'missing arguments to fyi_pagetag_delete'); tree := db.dba.s3_fyi_call (0, u_account, u_credentials, vector ('pagetag_listall')); -- TODO figure out result set format } ; create procedure db.dba.test () { return; -- log in on sandbox db.dba.s3_fyi_login (null, null); db.dba.s3_fyi_register ( u_account => 'testacct.sandboxtest.name', u_credentials => 'geheimpje123', u_email => 'testacct@nieuw.xs4all.nl'); -- optional --u_firstname => 'Paul', --u_lastname => 'Nieuwdorp', --u_address => 'Goyckemastraat 3', --u_postcode => '9036 PJ', --u_city => 'Menaldum', --u_country => 'NL', --u_urlforward => 'http://www.openlinksw.com/' --); db.dba.s3_fyi_manage (u_account => 'testacct.sandboxtest.name', u_firstname => 'John', u_lastname => 'Doe'); db.dba.s3_fyi_extend (u_account => 'testacct.sandboxtest.name', period => '1 year'); db.dba.s3_fyi_pagetag_add ( u_account => 'testacct.sandboxtest.name', u_credentials => 'geheimpje123', page => 'blog', url => 'http://openlinksw.com/~kidehen'); db.dba.s3_fyi_user_list (start => 0); db.dba.s3_fyi_check (u_account => 'testacct.sandboxtest.name'); --select * from db.dba.fyi_user_list where start = 0; db.dba.s3_fyi_cancel (u_account => 'testacct.sandboxtest.name'); --select * from db.dba.fyi_check where username = 'tester123.sandboxtest.name'; return; -- NAMESPINNER no auth db.dba.s3_fyi_call (0, '', '', vector ('namespinner', 'firstname', 'paul', 'lastname', 'nieuwdorp', -- optional, default num_results = 3 'num_results', 5)); -- REGISTER partner db.dba.s3_fyi_call (1, 'sandbox', 'verysecret', vector ('register', 'dotnameemail', 'paul@nieuw.name', 'email', 'paul@nieuw.xs4all.nl', 'password', 'sosecret123', 'password2', 'sosecret123', -- optional 'urlforward1', 'http://nieuw.xs4all.nl/~paul', 'country', 'nl', 'nowelcomeemail', 1, 'noopenid', 1)); -- USER_LIST partner -- return up to 1000 reseller accounts db.dba.s3_fyi_call (1, 'sandbox', 'verysecret', vector ('user_list', 'start', 0)); -- TXN_LIST partner -- List all billable transactions deducted from reseller account db.dba.s3_fyi_call (1, 'sandbox', 'verysecret', vector ('txn_list', 'startdate', '2008-01-01 12:00:00', 'enddate', '2008-12-31 23:59:00')); -- EXTEND partner -- Extend a registration with a period The Partners account will be -- debited with the associated price for the transaction. -- item_id == 2 : ten year extension -- item_id == 3 : one year extension -- item_id == 4 : three month extension -- item_id == 5 : one month extension db.dba.s3_fyi_call (1, 'sandbox', 'verysecret', vector ('extend', 'username', 'paul.nieuw.name', 'item_id', 5)); -- CANCEL partner -- Immediately cancel a registration. db.dba.s3_fyi_call (1, 'sandbox', 'verysecret', vector ('cancel', 'username', 'paul.nieuw.name')); -- QUERY_BALANCE partner -- Query the balance of reseller on account. db.dba.s3_fyi_call (1, 'sandbox', 'verysecret', vector ('query_balance')); -- CHECK user/partner db.dba.s3_fyi_call (1, 'sandbox', 'verysecret', vector ('check', 'username', 'paul.nieuw.name')); -- MANAGE user/partner db.dba.s3_fyi_call (0, 'paul.nieuw.name', 'sosecret123', vector ('manage', 'newurlforward1', 'http://openlinksw.com/', 'newemailforward1', 'info@nieuw.xs4all.nl', 'firstname', 'Paul', 'lastname' ,'Nieuwdorp', 'mobile', '06', 'address1', 'Goyckemastraat 3', 'city', 'Menaldum', 'postcode', '9036 PJ', 'country', 'The Netherlands')); -- PAGETAGS -- PAGETAG_ADD user db.dba.s3_fyi_call (0, 'paul.nieuw.name', 'sosecret123', vector ('pagetag_add', 'page', 'blog', 'url', 'http://boards.openlinksw.com/')); -- PAGETAG_MODIFY user db.dba.s3_fyi_call (0, 'paul.nieuw.name', 'sosecret123', vector ('pagetag_modify', 'page', 'blog', 'newpage', 'myblog')); -- PAGETAG_DELETE user db.dba.s3_fyi_call (0, 'paul.nieuw.name', 'sosecret123', vector ('pagetag_delete', 'page', 'blog')); -- PAGETAG_LISTALL user db.dba.s3_fyi_call (0, 'paul.nieuw.name', 'sosecret123', vector ('pagetag_listall')); } ; ec2exts/sql/procedures.sqlgh-- -- AWS Functions -- create procedure db.dba.s3_login (in aws_id varchar, in aws_key varchar) { registry_set ('aws_id', aws_id); if (xenc_key_exists (aws_id)) xenc_key_remove (aws_id); xenc_key_RAW_read (aws_id, encode_base64 (aws_key)); } ; create procedure db.dba.s3_logout () { declare aws_id varchar; aws_id := registry_get ('aws_id'); if (isstring (aws_id)) { if (xenc_key_exists (aws_id)) xenc_key_remove (aws_id); registry_remove ('aws_id'); } } ; --s3_login ('0V71QNJ4A77ZAFHDPY82', ); --s3_request ('GET', '/pnieuw'); --s3_logout (); -- Create bucket --db.dba.s3_request ('PUT', '/test_bucket'); -- Delete bucket --db.dba.s3_request ('DELETE', '/test_bucket'); -- List bucket --db.dba.s3_request ('GET', '/'); -- Get object -- s3_request ('GET', '/pnieuw/hello.txt'); -- Delete object -- s3_request ('DELETE', '/pnieuw/hello.txt'); -- Put object --db.dba.s3_request ('PUT', '/pnieuw/hello.txt', 'hello world\n', vector ('x-amz-acl: public-read')); --db.dba.s3_request ('PUT', '/pnieuw/hello.txt', 'hello world\n', vector ('x-amz-acl: public-read', 'Content-Type: text/plain')); create function db.dba.s3_request ( in method varchar, in uri varchar, in body any := null, in headers vector := null) returns any { declare n, j integer; declare r_date, r_content_type, r_content_md5 varchar; declare aws_id varchar; declare to_sign varchar; declare hdr varchar; declare req_hdrs varchar; declare ret_hdrs varchar; declare page any; declare status varchar; -- make sure S3 access has been configured correctly aws_id := registry_get ('aws_id'); if (not isstring (aws_id)) signal ('AWS01', 'AWS identifier has not been defined.'); if (not xenc_key_exists (aws_id)) signal ('AWS02', 'AWS secret key has not been defined.'); -- extract info from given headers if (headers is null) { r_content_type := ''; r_content_md5 := ''; } else { r_content_type := http_request_header_full (headers, 'Content-Type', ''); r_content_md5 := http_request_header_full (headers, 'Content-MD5', ''); } r_date := date_rfc1123 (now ()); -- fill in fields for SHA1 HMAC signature to_sign := method || '\n' || r_content_md5 || '\n' || r_content_type || '\n' || r_date || '\n'; -- add x- headers as extra headers to sign n := length (headers); j := 0; while (j < n) { hdr := aref (headers, j); if (substring (hdr, 1, 2) = 'x-') { hdr := replace (lcase (hdr), ' ', ''); to_sign := to_sign || hdr || '\n'; } j := j + 1; } -- finally add url to to_sign -- XXX doesn't deal with ?spec - see docu to_sign := to_sign || uri; -- add Date: and Authorization: header headers := vector_concat (vector ( 'Date: ' || r_date, 'Authorization: AWS ' || aws_id || ':' || xenc_hmac_sha1_digest (to_sign, aws_id)), headers); -- concat all headers so http_get can deal with them n := length (headers); j := 0; req_hdrs := ''; while (j < n) { hdr := aref (headers, j); if (j > 0) req_hdrs := req_hdrs || '\r\n' || hdr; else req_hdrs := hdr; j := j + 1; } page := http_get ('http://s3.amazonaws.com' || uri, ret_hdrs, method, req_hdrs, body); status := rtrim (ret_hdrs[0], '\r\n'); if (not status like 'HTTP/1._ %') signal ('AWS03', 'Unexpected HTTP status "' || status || '"'); status := right (status, length (status) - 9); -- If the server replies 3xx, 4xx, 5xx and Content-Type: application/xml, -- then an error document follows. Parse and signal... if (status like '3%' or status like '4%' or status like '5%') { hdr := http_request_header (ret_hdrs, 'Content-Type'); if (isstring (hdr) and hdr = 'application/xml') { declare doc any; declare message varchar; doc := xtree_doc (page); message := cast (xpath_eval ('/Error/Message', doc) as varchar); if (message is not null) signal ('AWS04', message); } } if (not status like '2%') signal ('AWS05', 'Server replied "' || status || '"'); return page; } ; -- -- -- create procedure db.dba.s3_s3_upload ( in src varchar, in dst varchar) { declare content any; log_message ('S3 upload '||dst); content := file_to_string_output (src); -- XXX create bucket db.dba.s3_request ('PUT', dst, content); } ; create procedure db.dba.s3_s3_download ( in dst varchar, in src varchar) { declare content any; s3_restore_status ('INFO', 'Downloading s3:/' || src); content := db.dba.s3_request ('GET', src); string_to_file (dst, content, 0); } ; create procedure db.dba.s3_ftp_upload ( in src varchar, in host varchar, in account varchar, in credentials varchar, in dst varchar) { log_message ('FTP upload '||src||' to '||host); ftp_put (host, account, credentials, src, dst); } ; create procedure db.dba.s3_ftp_download ( in dst varchar, in host varchar, in account varchar, in credentials varchar, in src varchar) { s3_restore_status ('INFO', 'Downloading ftp://' || host || '/' || src); ftp_get (host, account, credentials, src, dst); } ; create procedure db.dba.s3_http_upload ( in src varchar, in host varchar, in account varchar, in credentials varchar, in dst varchar) { declare req_hdrs varchar; declare ret_hdrs varchar; declare page varchar; declare content any; declare status varchar; log_message ('HTTP upload '||src||' to '||host); req_hdrs := 'Content-Type: application/octet-stream'; if (length (account) <> 0) req_hdrs := req_hdrs || '\r\nAuthorization: Basic ' || encode_base64 (account || ':' || credentials); content := file_to_string_output (src); page := http_get ('http://'||host||dst, ret_hdrs, 'PUT', req_hdrs, content); status := rtrim (ret_hdrs[0], '\r\n'); if (not status like 'HTTP/1._ %') signal ('BUP02', 'Unexpected http status "' || status || '"'); status := right (status, length (status) - 9); if (not status like '2%') signal ('BUP03', 'Server replied "' || status || '"'); } ; create procedure db.dba.s3_http_download ( in dst varchar, in host varchar, in account varchar, in credentials varchar, in src varchar) { declare req_hdrs varchar; declare ret_hdrs varchar; declare content any; declare status varchar; s3_restore_status ('INFO', 'Downloading http://' || host || src); if (length (account) <> 0) req_hdrs := 'Authorization: Basic ' || encode_base64 (account || ':' || credentials); else req_hdrs := null; ret_hdrs := null; content := http_get ('http://'||host||src, ret_hdrs, 'GET', req_hdrs, null); status := rtrim (ret_hdrs[0], '\r\n'); if (not status like 'HTTP/1._ %') signal ('BUP02', 'Unexpected http status "' || status || '"'); status := right (status, length (status) - 9); if (not status like '2%') signal ('BUP03', 'Server replied "' || status || '"'); string_to_file (dst, content, 0); } ; create procedure db.dba.s3_upload ( in src varchar, in protocol varchar, in host varchar, in account varchar, in credentials varchar, in dst varchar) { if (protocol = 's3') { dst := '/' || dst; dst := replace (dst, '//', '/'); db.dba.s3_s3_upload (src, dst); } else if (protocol = 'http') { dst := '/' || dst; dst := replace (dst, '//', '/'); db.dba.s3_http_upload (src, host, account, credentials, dst); } else if (protocol = 'ftp') { dst := replace (dst, '//', '/'); db.dba.s3_ftp_upload (src, host, account, credentials, dst); } else { signal ('BUP01', 'Unknown protocol'); } } ; create procedure db.dba.s3_download ( in dst varchar, in protocol varchar, in host varchar, in account varchar, in credentials varchar, in src varchar) { if (protocol = 's3') { src := '/' || src; src := replace (src, '//', '/'); db.dba.s3_s3_download (dst, src); } else if (protocol = 'http') { src := '/' || src; src := replace (src, '//', '/'); db.dba.s3_http_download (dst, host, account, credentials, src); } else if (protocol = 'ftp') { src := replace (src, '//', '/'); db.dba.s3_ftp_download (dst, host, account, credentials, src); } else { signal ('BUP01', 'Unknown protocol'); } } ; create procedure db.dba.s3_backup_run () { declare _protocol, _host varchar; declare _account, _credentials, _path_prefix varchar; declare _sync_num integer; declare _backup_ts varchar; declare _b_prefix varchar; declare _b_num integer; declare _b_run integer; declare _b_dindex integer; declare _b_ts varchar; declare _b_dir varchar; declare _pathv vector; declare _prefix varchar; declare _srcfile varchar; declare _dstfile varchar; declare _descrf varchar; declare s3c cursor for select protocol, host, account, credentials, path_prefix, sync_num, backup_ts from DB.DBA.S3_BACKUP_SCHEDULE where backup_enabled = 1; _b_dindex := backup_context_info_get ('dir_inx'); _b_prefix := backup_context_info_get ('prefix'); _b_num := backup_context_info_get ('num'); _b_run := backup_context_info_get ('run'); _b_ts := backup_context_info_get ('ts'); -- skip if backup not configured, it is currently running, or never run if (_b_run or _b_prefix is null or _b_ts = 'unknown' or _b_num = 0) return; -- calculate _prefix to be the place where the .bp files are _pathv := null; for select bd_dir from DB.DBA.SYS_BACKUP_DIRS order by bd_id do { if (_pathv is null) _pathv := vector (bd_dir); else _pathv := vector_concat (_pathv, vector (bd_dir)); } if (_pathv is null) { _prefix := _b_prefix; _b_dir := '.'; } else { _b_dir := _pathv[_b_dindex]; _prefix := _b_dir || '/' || _b_prefix; } open s3c (exclusive, prefetch 1); whenever not found goto err; _descrf := sprintf ('%s-descriptor.ini', _prefix); cfg_write (_descrf, 'Backup', 'Date', cast (now () as varchar)); cfg_write (_descrf, 'Backup', 'Identifier', _b_ts); cfg_write (_descrf, 'Backup', 'Directory', _b_dir); cfg_write (_descrf, 'Backup', 'Prefix', _b_prefix); cfg_write (_descrf, 'Backup', 'FileCount', cast (_b_num as varchar)); while (1) { fetch s3c into _protocol, _host, _account, _credentials, _path_prefix, _sync_num, _backup_ts; if (_sync_num > _b_num or _backup_ts <> _b_ts) _sync_num := 0; if (_sync_num < _b_num) { declare exit handler for sqlstate '*' { rollback work; log_message ('** S3 backup of ' || _srcfile || ' failed: ' || __SQL_MESSAGE); update DB.DBA.S3_BACKUP_SCHEDULE set status = 'FAILED', last_err = __SQL_MESSAGE||' ('||__SQL_STATE||')', backup_ts = _b_ts, backup_enabled = 0, updated = now () where current of s3c; commit work; goto err; }; update DB.DBA.S3_BACKUP_SCHEDULE set status = 'RUNNING', last_err = '', backup_ts = _b_ts, updated = now () where current of s3c; commit work; _credentials := pwd_magic_calc (_account, _credentials, 1); if (_protocol = 's3') db.dba.s3_login (_account, _credentials); if (_sync_num = 0 and cfg_item_value (virtuoso_ini_path (), 'Parameters', 'DbaExecutables') is not null) { _srcfile := sprintf ('%s-extra.tgz', _prefix); run_executable ('/bin/tar', 1, 'cfz', _srcfile, virtuoso_ini_path (), 'vsp'); _dstfile := sprintf ('%s-extra.tgz', _b_prefix); if (length (_path_prefix) <> 0) _dstfile := _path_prefix || '/' || _dstfile; db.dba.s3_upload (_srcfile, _protocol, _host, _account, _credentials, _dstfile); } while (_sync_num < _b_num) { _sync_num := _sync_num + 1; _srcfile := sprintf ('%s%d.bp', _prefix, _sync_num); _dstfile := sprintf ('%s%d.bp', _b_prefix, _sync_num); if (length (_path_prefix) <> 0) _dstfile := _path_prefix || '/' || _dstfile; db.dba.s3_upload (_srcfile, _protocol, _host, _account, _credentials, _dstfile); update DB.DBA.S3_BACKUP_SCHEDULE set sync_num = _sync_num, updated = now () where current of s3c; commit work; } _dstfile := sprintf ('%s-descriptor.ini', _b_prefix); if (length (_path_prefix) <> 0) _dstfile := _path_prefix || '/' || _dstfile; db.dba.s3_upload (_descrf, _protocol, _host, _account, _credentials, _dstfile); update DB.DBA.S3_BACKUP_SCHEDULE set status = 'READY', updated = now () where current of s3c; commit work; } } err: if (_protocol = 's3') db.dba.s3_logout (); close s3c; } ; create procedure s3_restore_status ( in status varchar, in last_err varchar) { log_message (last_err); insert into DB.DBA.S3_RESTORE_LOGMSGS (status, last_err, updated) values (status, last_err, now ()); commit work; } ; create procedure s3_restore_run ( in protocol varchar, in host varchar, in account varchar, in credentials varchar, in path_prefix varchar, -- bucket in backup_name varchar) -- backup name { declare _srcfile varchar; declare _dstfile varchar; declare _dprefix varchar; declare _sprefix varchar; declare _b_id varchar; declare _b_dir varchar; declare _b_prefix varchar; declare _b_num integer; declare _sync_num integer; declare _descrf varchar; declare _srvid1 varchar; declare _srvid2 varchar; declare _recid integer; -- Make sure no two threads will ever execute this code status (); _srvid1 := sprintf ('%d/%d/%d %d:%d', sys_stat ('st_started_since_year'), sys_stat ('st_started_since_month'), sys_stat ('st_started_since_day'), sys_stat ('st_started_since_hour'), sys_stat ('st_started_since_minute')); --__atomic (1); _srvid2 := registry_get ('_s3_restore_run'); if (isstring (_srvid2) and _srvid1 = _srvid2) { --__atomic (0); signal ('BUP04', 'An on-line restore is currently in progress. Try again later, or restart the server.'); } registry_remove ('_s3_restore_set'); registry_set ('_s3_restore_run', _srvid1); --__atomic (0); -- First clean the log records delete from DB.DBA.S3_RESTORE_LOGMSGS; db.dba.set_identity_column ('DB.DBA.S3_RESTORE_LOGMSGS', 'recid', 1); -- Make sure we clean up on signals declare exit handler for sqlstate '*' { registry_remove ('_s3_restore_run'); registry_remove ('_s3_restore_set'); s3_restore_status ('ERROR', __SQL_MESSAGE || ' (' || __SQL_STATE || ')'); return; }; -- Make sure no unattended backup messes with the download directory. -- This is not fool-proof, but it's better than nothing. if (backup_context_info_get ('run')) signal ('BUP05', 'An on-line backup is currently in progress. Try again later.'); if (exists (select 1 from DB.DBA.SYS_SCHEDULED_EVENT where SE_NAME = DB.DBA.BACKUP_SCHED_NAME ())) { delete from DB.DBA.SYS_SCHEDULED_EVENT where SE_NAME = DB.DBA.BACKUP_SCHED_NAME (); s3_restore_status ('NOTICE', 'The on-line backup schedule has been disabled. You may want to re-enable the backup schedule if this restore operation does not run to completion.'); } s3_restore_status ('INFO', 'Starting restore operation.'); if (protocol = 's3') db.dba.s3_login (account, credentials); -- Download xxx-descriptor.ini and parse it if (length (path_prefix) <> 0) _srcfile := path_prefix || '/' || backup_name || '-descriptor.ini'; else _srcfile := backup_name || '-descriptor.ini'; _descrf := 'restore-tmp'; db.dba.s3_download (_descrf, protocol, host, account, credentials, _srcfile); -- Parse the descriptor file _b_id := cfg_item_value (_descrf, 'Backup', 'Identifier'); _b_dir := cfg_item_value (_descrf, 'Backup', 'Directory'); _b_prefix := cfg_item_value (_descrf, 'Backup', 'Prefix'); _b_num := cast (cfg_item_value (_descrf, 'Backup', 'FileCount') as integer); file_unlink (_descrf); -- Make sure file content is reasonable if (not isstring (_b_id) or length (_b_id) <> 16 or not isstring (_b_dir) or length (_b_dir) = 0 or not isstring (_b_prefix) or length (_b_prefix) = 0 or strchr (_b_prefix, '/') is not null or _b_num < 0) signal ('BUP06', 'The backup descriptor file is invalid'); -- This will trap DirsAllowed violations file_mkpath (_b_dir); -- Remove previous backup set, if any backup_dirs_clear (_b_prefix, vector (_b_dir)); s3_restore_status ('INFO', sprintf ('Downloading set %s in %s/, %d files', _b_id, _b_dir, _b_num)); if (length (path_prefix) <> 0) _sprefix := path_prefix || '/' || _b_prefix; else _sprefix := _b_prefix; _dprefix := _b_dir || '/' || _b_prefix; _sync_num := 0; _recid := 0; while (_sync_num < _b_num) { _sync_num := _sync_num + 1; -- Try to keep the number of records small, or the browser may -- have problems when there are many files and a high refresh rate -- if (_recid = 0) -- { -- insert into DB.DBA.S3_RESTORE_LOGMSGS (status, last_err, updated) -- values ('INFO', '', now ()); -- _recid := identity_value (); -- } -- update DB.DBA.S3_RESTORE_LOGMSGS -- set last_err = sprintf ('Downloading file %d of %d', _sync_num, _b_num), -- updated = now () -- where recid = _recid; -- commit work; _srcfile := sprintf ('%s%d.bp', _sprefix, _sync_num); _dstfile := sprintf ('%s%d.bp', _dprefix, _sync_num); db.dba.s3_download (_dstfile, protocol, host, account, credentials, _srcfile); -- delete from DB.DBA.S3_RESTORE_LOGMSGS where recid > _recid; -- commit work; } _srcfile := sprintf ('%s-extra.tgz', _sprefix); _dstfile := sprintf ('%s-extra.tgz', _dprefix); db.dba.s3_download (_dstfile, protocol, host, account, credentials, _srcfile); _srcfile := sprintf ('%s-descriptor.ini', _sprefix); _descrf := sprintf ('%s-descriptor.ini', _dprefix); db.dba.s3_download (_descrf, protocol, host, account, credentials, _srcfile); s3_restore_status ('INFO', 'Download complete.'); if (protocol = 's3') db.dba.s3_logout (); registry_remove ('_s3_restore_run'); registry_set ('_s3_restore_set', _descrf); return; s3_restore_status ('INFO', 'Executing checkpoint.'); exec ('checkpoint'); raw_exit (); } ; create procedure db.dba.s3_restore_run2 () { declare _srvid1 varchar; declare _srvid2 varchar; declare _descrf varchar; declare _b_dir varchar; declare _b_prefix varchar; declare _status integer; -- Make sure no two threads will ever execute this code status (); _srvid1 := sprintf ('%d/%d/%d %d:%d', sys_stat ('st_started_since_year'), sys_stat ('st_started_since_month'), sys_stat ('st_started_since_day'), sys_stat ('st_started_since_hour'), sys_stat ('st_started_since_minute')); --__atomic (1); _srvid2 := registry_get ('_s3_restore_run'); if (isstring (_srvid2) and _srvid1 = _srvid2) { --__atomic (0); signal ('BUP04', 'An on-line restore is currently in progress. Try again later, or restart the server.'); } registry_set ('_s3_restore_run', _srvid1); --__atomic (0); -- Make sure we clean up on signals declare exit handler for sqlstate '*' { registry_remove ('_s3_restore_run'); s3_restore_status ('ERROR', __SQL_MESSAGE || ' (' || __SQL_STATE || ')'); return; }; -- CODE _descrf := registry_get ('_s3_restore_set'); _b_dir := cfg_item_value (_descrf, 'Backup', 'Directory'); _b_prefix := cfg_item_value (_descrf, 'Backup', 'Prefix'); s3_restore_status('INFO','Reassembling new database from backup files.'); string_to_file ('restore-tmp.ini', '', 0); string_to_file ('restore-tmp.db', '', 0); file_unlink ('restore-tmp.db'); _status := run_executable ('/bin/sh', 1, '-c', sprintf( './virtuoso -r "%s" -B "%s" -c restore-tmp.ini >restore.log 2>&1', _b_prefix, _b_dir)); if (_status) signal ('BUP07', 'Failed to reassemble the database. Please check the file restore.log in the database directory.'); s3_restore_status ('INFO','Reassembly complete - saved as restore-tmp.db'); registry_remove ('_s3_restore_set'); file_unlink ('restore.log'); file_unlink ('restore-tmp.log'); file_unlink ('restore-tmp.ini'); s3_restore_status ('INFO', 'Executing checkpoint.'); exec ('checkpoint'); s3_restore_status ('INFO', 'The server is now restarting using the restored database.'); registry_remove ('_s3_restore_run'); delay (5); _status := run_executable ('/bin/sh', 0, '-c', '/opt/virtuoso/install/restart.sh'); exec ('shutdown'); } ; create procedure db.dba.s3_get_current_ip (out ip varchar) { declare last_ip varchar; declare ret_hdrs varchar; declare status varchar; -- This only works on an EC2 instance if (file_stat('/opt/virtuoso/.ami', 0) = 0) { ip := 'unknown'; return; --signal('DNS01', 'Function implemented only for EC2 instances.'); } signal('DNS02', 'Function implemented only for EC2 instances.'); ip := http_get ('http://169.254.169.254/latest/meta-data/public-ipv4', ret_hdrs, 'GET', null, null); status := rtrim (ret_hdrs[0], '\r\n'); if (not status like 'HTTP/1._ %') signal ('DNS001', 'Could not determine IP Address. Unexpected http status "' || status || '"'); status := right (status, length (status) - 9); if (not status like '2%') signal ('DNS002', 'Could not determine IP Address. Server replied "' || status || '"'); } ; create function db.dba.s3_send_dyndns_update ( in account varchar, in credentials varchar, in hostname varchar, in ip varchar) returns varchar { declare requrl varchar; declare req_hdrs varchar; declare ret_hdrs varchar; declare status varchar; declare content varchar; log_message ('DynDNS Update for hostname(s) ' || hostname || ', IP=' || ip); requrl := 'http://members.dyndns.org/nic/update?hostname=' || hostname || '&myip=' || ip || '&wildcard=NOCHG&mx=NOCHG&backmx=NOCHG'; req_hdrs := 'Authorization: Basic ' || encode_base64 (account || ':' || credentials); content := http_get (requrl, ret_hdrs, 'GET', req_hdrs, null); status := rtrim (ret_hdrs[0], '\r\n'); if (not status like 'HTTP/1._ %') signal ('DNS01', 'Unexpected http status "' || status || '"'); status := right (status, length (status) - 9); if (not status like '2%') signal ('DNS02', 'Server replied "' || status || '"'); if (content = 'badauth') signal ('DNS03', 'The username and password pair do not match a real user.'); if (content = '!donator') signal ('DNS04', 'An option available only to credited users was specified, but the user is not a credited user.'); if (content = 'dnserr') signal ('DNS05', 'DNS error encountered. Please retry later.'); if (content = '911') signal ('DNS06', 'There is a problem or scheduled maintenance. Please retry later.'); if (content = 'notfqdn') signal ('DNS07', 'The hostname specified is not a fully qualified domain name.'); if (content = 'nohost') signal ('DNS08', 'The hostname specified does not exist in this user account.'); if (content = 'numhost') signal ('DNS09', 'Too many hosts specified in an update.'); if (content = 'abuse') signal ('DNS10', 'The hostname specified is blocked for update abuse.'); return content; } ; create procedure db.dba.test_dyndns () { db.dba.s3_send_dyndns_update ('test','test','test.dyndns.org','127.0.0.1'); } ; create function db.dba.test_ip () returns varchar { declare ip varchar; db.dba.s3_get_current_ip (ip); log_message('Detected IP for EC2 instance: ' || ip); return ip; } ; create procedure db.dba.s3_update_dyndns () { declare _account, _credentials, _hostnames varchar; declare _last_ip, _current_ip varchar; declare _last_updated datetime; declare _auto_update integer; declare status varchar; declare dnsc cursor for select account, credentials, hostnames, last_ip, last_updated, auto_update from DB.DBA.S3_DYNDNS_CONFIG where update_enabled = 1; open dnsc (exclusive, prefetch 1); whenever not found goto err; db.dba.s3_get_current_ip (_current_ip); while (1) { fetch dnsc into _account, _credentials, _hostnames, _last_ip, _last_updated, _auto_update; if (_last_ip <> _current_ip or _last_updated is null or (_auto_update <> 0 and datediff ('day', _last_updated, now ()) >= 28)) { declare exit handler for sqlstate '*' { rollback work; log_message ('** DNS update failed: ' || __SQL_MESSAGE); update DB.DBA.S3_DYNDNS_CONFIG set last_err = __SQL_MESSAGE||' ('||__SQL_STATE||')', last_updated = now (), last_ip = '', update_enabled = 0 where current of dnsc; commit work; goto err; }; status := db.dba.s3_send_dyndns_update (_account, _credentials, _hostnames, _current_ip); update DB.DBA.S3_DYNDNS_CONFIG set last_err = status, last_updated = now (), last_ip = _current_ip where current of dnsc; commit work; } } err: close dnsc; } ; -- -- VSPX authentication - code stolen from Yacutia -- create procedure db.dba.s3_sql_user_password ( in name varchar) { declare pass varchar; pass := NULL; whenever not found goto none; select pwd_magic_calc (U_NAME, U_PASSWORD, 1) into pass from SYS_USERS where U_NAME = name and U_SQL_ENABLE = 1 and U_IS_ROLE = 0; none: return pass; } ; create procedure db.dba.s3_sql_user_password_check ( in name varchar, in pass varchar) { declare nonce, pass1 varchar; declare rc int; declare ltm datetime; if (name <> 'dba') return 0; nonce := connection_get ('vspx_nonce'); rc := 0; whenever not found goto nfu; select pwd_magic_calc (U_NAME, U_PASSWORD, 1), U_LOGIN_TIME into pass1, ltm from SYS_USERS where U_NAME = name and U_SQL_ENABLE = 1 and U_IS_ROLE = 0; if (length (nonce) and md5 (nonce||pass1) = pass) rc := 1; else if (not length (nonce) and pass1 = pass) rc := 1; if (rc and (ltm is null or ltm < dateadd ('minute', -2, now ()))) { update SYS_USERS set U_LOGIN_TIME = now () where U_NAME = name; commit work; } nfu: return rc; } ; -- -- Magic hook for online-backup -- create procedure DB.DBA.BACKUP_COMPLETED () { log_message ('BACKUP COMPLETED'); db.dba.s3_backup_run (); } ; ec2exts/sql/tables.sqlcreate procedure DB.DBA.s3_exec_no_error(in expr varchar) { declare state, message, meta, result any; exec(expr, state, message, vector(), 0, meta, result); } ; db.dba.s3_exec_no_error ( 'create table DB.DBA.S3_BACKUP_SCHEDULE ( recid integer identity not null, protocol varchar(12) not null, host varchar(255) not null, account varchar(20) not null, credentials varchar not null, path_prefix varchar(255) not null, sync_num integer not null default 0, backup_enabled integer not null default 1, backup_ts varchar not null default \'\', status varchar not null default \'READY\', last_err varchar not null default \'\', updated datetime not null, primary key (recid) )'); db.dba.s3_exec_no_error ( 'create table DB.DBA.S3_RESTORE_LOGMSGS ( recid integer identity not null, status varchar not null default \'READY\', last_err varchar not null default \'\', updated datetime not null, primary key (recid) )'); db.dba.s3_exec_no_error ( 'create table DB.DBA.S3_DYNDNS_CONFIG ( recid integer identity not null, account varchar(20) not null, credentials varchar not null, hostnames varchar not null, update_enabled integer not null default 1, auto_update integer not null default 1, last_updated datetime, last_ip varchar not null default \'\', last_err varchar not null default \'\', primary key (recid) )'); insert soft db.dba.s3_dyndns_config (recid, account, credentials, hostnames, update_enabled, auto_update, last_updated, last_ip, last_err) values (1, 'test', 'test', 'test.dyndns.org', 0, 0, null, '', ''); drop procedure db.dba.s3_exec_no_error; ec2exts/sql/tables.sql~-- drop table DB.DBA.S3_BACKUP_SCHEDULE; create table DB.DBA.S3_BACKUP_SCHEDULE ( recid integer identity not null, protocol varchar(12) not null, host varchar(255) not null, account varchar(20) not null, credentials varchar not null, path_prefix varchar(255) not null, sync_num integer not null default 0, backup_enabled integer not null default 1, backup_ts varchar not null default '', status varchar not null default 'READY', last_err varchar not null default '', updated datetime not null, primary key (recid) ); db.dba.set_identity_column ('DB.DBA.S3_BACKUP_SCHEDULE', 'recid', 1); create table DB.DBA.S3_RESTORE_LOGMSGS ( recid integer identity not null, status varchar not null default 'READY', last_err varchar not null default '', updated datetime not null, primary key (recid) ); db.dba.set_identity_column ('DB.DBA.S3_RESTORE_LOGMSGS', 'recid', 1); create table DB.DBA.S3_DYNDNS_CONFIG ( recid integer identity not null, account varchar(20) not null, credentials varchar not null, hostnames varchar not null, update_enabled integer not null default 1, auto_update integer not null default 1, last_updated datetime, last_ip varchar not null default '', last_err varchar not null default '', primary key (recid) ); db.dba.set_identity_column ('DB.DBA.S3_DYNDNS_CONFIG', 'recid', 1); insert into db.dba.s3_dyndns_config (recid, account, credentials, hostnames, update_enabled, auto_update, last_updated, last_ip, last_err) values (1, 'test', 'test', 'test.dyndns.org', 0, 0, null, '', ''); MD5 1d614c81dc7962bd0526d9df6745618e