Home » RADICORE » How To » Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern (Do you have any advice about how to structure a program that will be moving data between pgsql and sqlsrv databases?)
| Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4749] |
Fri, 22 May 2015 18:14  |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
I apologize if this is too off-topic, but I am hoping you might have a suggestion for how I might proceed to use your radicore infrastructure for an ETL solution. There is an older sqlsrv database with four sets of tables, each set of tables is similar to the other sets with the addition of some peculiarities/customizations. I have redesigned the schema to consolidate each of the four sets into a single set of tables (that encompasses all of the union of all customizations). I am connecting to the databases and have all of my classes created via the radicore IMPORT DATABASES and TABLES and COLUMNS, etc, then EXPORT PHP. This is all fine.
There are three directions I am considering, as to where to put my code to make the transformations: (1) just a jumble of procedures (functions) in my main script code to transform an array of one type to an array of another type; (2) create a new class called Transformer that takes ($fieldarray, $classname) and returns $fieldarray of the equivalent type (from its opposite database); or (3) to put the transformation code into the std.table.class.inc and extend it in the subclasses.
If (3), then maybe defining:
$fieldarray_transformed = getData_transformed($where);
Do you have any suggestions?
Thanks.
|
|
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4761 is a reply to message #4752] |
Tue, 02 June 2015 22:21  |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
Hi tony,
I am missing some fundamental issue. Can you see if you can explain why my data are not getting updated? The output from updateMultile seems to be correct, however, as you can see there are 0 updates, as seen in the messages array.
<?php
// Batch Test
ini_set('include_path', '.');
require 'std.batch.inc';
batchInit(__FILE__);
$dbobj = RDCsingleton::getInstance('sids');
$dbobj->sql_select = '';
$dbobj->sql_from = '';
$dbobj->sql_where = 'sid_type_id=2';
$dbobj->sql_groupby = '';
$dbobj->sql_orderby = 'sid ASC';
$rows = $dbobj->getData("sid IN (7, 15)");
print "Debug\tgetData:\n";
var_dump($rows);
// neither seem to work...
//$postarray['deployment_status_flags'] = 'deployment_status_flags | 2';
//$postarray[] = 'deployment_status_flags=2';
//$dbobj->startTransaction();
//$rows = $dbobj->updateMultiple($rows, $postarray);
//$dbobj->commit();
// so trying this...also does not update DB
foreach ( $rows as $i => $arr ) {
$rows[$i]['deployment_status_flags'] = $rows[$i]['deployment_status_flags'] | 16;
}
$dbobj->startTransaction();
$rows = $dbobj->updateMultiple($rows, null);
$dbobj->commit();
if ( !empty($dbobj->errors) ) {
print "Debug\tErrors:\n";
var_dump($dbobj->errors);
}
if ( isset($dbobj->messages) ) {
print "Debug\tmessages: \n";
var_dump($dbobj->messages);
}
print "Debug\tAfter update rows:\n";
var_dump($rows);
batchEnd();
?>
output:
<html>
<p>** started at June 3, 2015, 3:58 am</p>
<p>server name : localhost</p>
<p>document root: C:/xampp/htdocs/radicore</p>
<p>script name : C:/xampp/htdocs/radicore/newsigdb/test-batch-idp.php</p>
<p>php_self : /newsigdb/test-batch-idp.php</p>
<p>server API : cli</p>
<p>language : en-us</p>
<p>server_admin : </p>
<p>include_path : .;C:\xampp\radicore\includes;C:\xampp\htdocs\radicore\newsigdb;C:\xampp\htdocs\radicore\idp1;C:\xampp\htdocs\radicore\audit;C:\xampp\htdocs\radicore\audit\text;C:\xampp\htdocs\radicore\dict;C:\xampp\htdocs\radicore\menu;C:\xampp\htdocs\radicore\workflow;C:\xampp\htdocs\radicore\menu;C:\xampp\htdocs\radicore\audit;C:\xampp\htdocs\radicore\workflow</p>
<p>project_code : </p>
<p>user_id : batch</p>
<p>role_id : batch</p>
Debug getData:
array(2) {
[0]=>
array(9) {
["id"]=>
string(1) "2"
["sid_type_id"]=>
string(1) "2"
["sid"]=>
string(1) "7"
["current_signature_id"]=>
string(5) "22825"
["deployment_status_flags"]=>
string(1) "4"
["updated_at"]=>
string(19) "2014-11-04 08:53:17"
["updated_by"]=>
string(3) "222"
["first_deployed_timestamp"]=>
NULL
["last_deployed_timestamp"]=>
NULL
}
[1]=>
array(9) {
["id"]=>
string(1) "2"
["sid_type_id"]=>
string(1) "2"
["sid"]=>
string(2) "15"
["current_signature_id"]=>
NULL
["deployment_status_flags"]=>
string(1) "2"
["updated_at"]=>
NULL
["updated_by"]=>
NULL
["first_deployed_timestamp"]=>
NULL
["last_deployed_timestamp"]=>
NULL
}
}
Debug messages:
array(1) {
[0]=>
string(30) "0 records were updated in SIDS"
}
Debug After update rows:
array(2) {
[0]=>
array(9) {
["id"]=>
string(1) "2"
["sid_type_id"]=>
string(1) "2"
["sid"]=>
string(1) "7"
["current_signature_id"]=>
string(5) "22825"
["deployment_status_flags"]=>
string(2) "20"
["updated_at"]=>
string(19) "2014-11-04 08:53:17"
["updated_by"]=>
string(3) "222"
["first_deployed_timestamp"]=>
NULL
["last_deployed_timestamp"]=>
NULL
}
[1]=>
array(9) {
["id"]=>
string(1) "2"
["sid_type_id"]=>
string(1) "2"
["sid"]=>
string(2) "15"
["current_signature_id"]=>
NULL
["deployment_status_flags"]=>
string(2) "18"
["updated_at"]=>
NULL
["updated_by"]=>
NULL
["first_deployed_timestamp"]=>
NULL
["last_deployed_timestamp"]=>
NULL
}
}
<p>** finished at June 3, 2015, 3:58 am</p>
</html>
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4766 is a reply to message #4765] |
Wed, 03 June 2015 06:32 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
Both the old and new db schemas have two tables parent --< child representing the same concept: an SID. (So, OLDDB/IDP.IDP_ID == NEWDB/SIDS.SID). At any time, all I care is to synchronize only the parents that are enabled, and the current_version of their respective child tables.
OLDDB (MSSQL)
=======
CREATE TABLE idp {
idp_id int PRIMARY KEY, # IDP.IDP_ID = SIDS.SID
current_version int, # IDP.CURRENT_VERSION = IDP_S.IDP_VERSION
idp_status, # bitmap where 0x2 is disable flag
...
}
CREATE TABLE idp_s {
idp_id int,
idp_version int,
...
PRIMARY KEY (idp_id, idp_version) # composite primary key
}
NEWDB (POSTGRESQL)
=======
CREATE TABLE sids {
id int PRIMARY KEY, # not equivalent to idp_id in OLDDB
current_signature_id int, # points to current_verion
sid int, # IDP.IDP_ID = SIDS.SID
deployment_status_flags int, # similar to OLDDB/idp.idp_status
...
}
CREATE TABLE signatures {
id int PRIMARY KEY, # not equivalent to anything in OLDDB
sid_id int, # foreign key, signatures.sid_id = sids.id
...
}
So, OLDDB and NEWDB each have a set of currently-enabled (parent, child) pairs represented by a single SID concept
(OLDDB/IDP.IDP_ID == NEWDB/SIDS.SID). Data could be synchronized in either direction: old to new, or vice versa. When synching
from one to the other, I do set operations to yeild two sets of sids: (1) those that need to have a new child inserted, and then enable parent; (2) those that need to have the parent disabled in the target db.
In case (2), I would to do a simple update on multiple record sets, but one at a time should be fine; In case (1) I think I
need to iterate through each pair to extract, transform, then load, inserting a new child, then setting the child's id to parent.current_version (or parent.current_signature_id).
[Updated on: Wed, 03 June 2015 06:32] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4781 is a reply to message #4774] |
Thu, 18 June 2015 12:52 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
Hi Tony,
Can you give me some pointers? (1) Do you have a description on how to make use of the parent/child relationships defined on a table? Presently, I am only able to use a single instance of a table, and the getData with $where argument to retrieve arrays of data. I want to understand if I can make better use of the relationships between objects.
If a package has many files, and a file has many lines, where the
converse can also be true--that a line can belong to many files, and packages.
packages --< packages_files >-- files --< files_lines >-- lines
The lines already exist, but I want to create new files and packages from lines.
I want to select several sets of lines, then create a new file for each set, then create a package from these files. And, I want all of the cross/joining tables (packages_files, and files_lines) to be updated as well.
$lines = RDCsingleton::getInstance('lines');
$lines->sql_where = "id > 100 AND id < 200";
$lines_data = $lines->getData();
(2) What is the difference between putting my where clause in sql_where, vs into $where?
$files_lines = RDCsingleton::getInstance('files_lines');
$files = RDCsingleton::getInstance('files');
$file_array = array('id'=>null, 'name'=>"file1");
$files_data = $files->insertRecord($file_array)[0];
(3) Can the parent or child table class handle these inserts into the linking tables for me? Or do I need to instantiate them and do it in a loop or something?
foreach ($lines_data as $i => $row) {
$files_lines_data = $files_lines->insertRecord($files_data['id', $row['id'])[0];
...
}
...
$packages = RDCsingleton::getInstance('packages');
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4782 is a reply to message #4781] |
Fri, 19 June 2015 05:20 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
(1) Relationships are used as follows:
1.1 You cannot create a transaction from a transaction pattern with two or more entities (tables) unless thozse tables are related.
1.2 If a table has a parent, and you have defined one or more columns to be retrieved from that parent, when the default SQL statement is created the framework will automatically insert a JOIN to that parent and include the nominated columns in the SELECT list.
1.3 When deleting a record the validateDelete() method will check the status of any records on child tables to see what action needs to be taken.
1.4 If you have a many-to-many relationship you create two one-to-many relationships which relate Parent1 and Parent2 to a Child table (which is somethimes known as a LINK table, an XREF table or a INTERSECTION table). When you create transactions to deal with this relationship will need one version with Parent1 as the "outer" entity and a second version with Parent2 as the "outer" entity.
(2) What is the difference between putting my where clause in sql_where, vs into $where?
In your code you should ONLY be using $where. $object->sql_where is used in some controllers to specify additions to the $where clause which cannot be changed.
(3) Can the parent or child table class handle these inserts into the linking tables for me?
No. The default behaviour for any transaction pattern is that it will only write to a table that is specified in the component script. If you wish to update other tables then you will have to insert custom code in the relevant _cm_post_insertRecord(() or _cm_post_updateRecord() method.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4783 is a reply to message #4782] |
Fri, 19 June 2015 23:48 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
Tony,
Which transaction patterns would I use for the following relationships below where _X_ are the joining tables with only the foreign keys of the two parents' primary keys, and the added wrinkle that lines have many line_versions, but each line has only one "current_line_version"?
packages --< packages_X_files >-- files --< files_X_lines >-- lines --< line_versions
If I were to build the web interface, (just to see how the parts fit togther)...
(0) One interface would be for viewing all lines, showing only the details of the current line_versions. LIST1 with some modified join somehow?
(1) Another view would for listing line_versions for a line: LIST2, I think. With CRUD operations for line_versions. Which page would be appropriate for choosing which line_version to set as the current line_version for the line?
(2) I want to create a package, then create files of lines but only by choosing from the set of currently active versions of the lines. (Any current line can go into any file. Should this be a "Multi 3", or two separate LINK1s?
[I will add that almost all of the data for a line is in line_versions.]
[Updated on: Sat, 20 June 2015 01:53] Report message to a moderator |
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4784 is a reply to message #4783] |
Sat, 20 June 2015 04:15 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
For dealing with many-to-many relationships you have a choice of three patterns - multi2, link1 or List2. If you look at the "List Task (Process)" screen in the MENU system you will see examples of all three for dealing with navigation buttons. They each have slightly different ways of showing and modifying the data, so you need to play with them to see which one fits your particular circumstances.
When it comes to a choice between a single MULTI3 or two LINK1s you have to bear in mind that a single task can only deal with a particular relationship in a particular order, so if you have Parent1, Parent2 and Child it can either work in the sequence Parent1 -> Child (with a JOIN to Parent2) or Parent2 -> Child (with a JOIN to Parent1).
The beauty of Radicore is that you can quickly try one transaction pattern, and if that doesn't quite work you can scrap it and try another.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4787 is a reply to message #4749] |
Sun, 21 June 2015 12:33 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
Connecting to multiple databases part 2.
I am having the same issue as when using the CLI batch script, but now when using web app version.
A var_dump($args) just after entering function pgsql($arg), (and before the error below), shows:
array (size=7)
'dbname' => null
'dbhost' => string 'id********************.com' (length=26)
'dbusername' => string '********' (length=8)
'dbuserpass' => string '***********' (length=11)
'dbport' => string '5432' (length=4)
'dbprefix' => string '' (length=0)
'PGSQL_dbname' => &string 'radicore' (length=8)
Fatal Error: Undefined variable: unbuffered_query
Error in line 78 of file 'C:\xampp\radicore\includes\dml.pgsql.class.inc'.
I ran the debugger against radicore, and have included a screenshot that might help.
Also, including the config.inc file, here:
<?php
// ************************************************************ *****************
// Copyright 2003-2005 by A J Marston <http://www.tonymarston.net>
// Copyright 2006 by Radicore Software Limited <http://www.radicore.org>
// ************************************************************ *****************
// This file contains database access details and the standard connection function
$GLOBALS['dbms'] = 'mysql'; // database engine is MySQL
//$GLOBALS['dbms'] = 'pgsql'; // database engine is PostgreSQL
//$GLOBALS['dbms'] = 'oracle'; // database engine is Oracle
//$GLOBALS['dbms'] = 'sqlsrv'; // database engine is SQL Server (Microsoft)
if ($GLOBALS['dbms'] == 'oracle') {
$GLOBALS['dbhost'] = '//localhost/xe';
$GLOBALS['dbprefix'] = '';
} elseif ($GLOBALS['dbms'] == 'sqlsrv') {
// JOIN clauses in SQL Server require 'dbname.<schema>.tblname'
$GLOBALS['dbhost'] = 'localhost';
$GLOBALS['dbprefix'] = '';
$GLOBALS['SQLSRV_schema'] = 'dbo';
$GLOBALS['serverName'] = '(local)';
$GLOBALS['connectionInfo'] = array('CharacterSet' => 'UTF-8',
'ReturnDatesAsStrings' => true);
} elseif ($GLOBALS['dbms'] == 'pgsql') {
// NOTE: with MYSQL there are tables withing databases, and within a single connection
// it is possible to access tables in any database.
// But with PostgreSQL there are tables within schemas within databases, and within
// a single connection it is only possible to access a single database, but any number
// of schemas within that database.
// When using PostgreSQL you must supply a value for $PGSQL_dbname for the single database
// connection, and what is known as 'dbname' to MySQL becomes 'schema' to PostgreSQL.
$GLOBALS['dbhost'] = 'id*****************.com';
$GLOBALS['dbprefix'] = '';
$GLOBALS['PGSQL_dbname'] = 'radicore';
} else {
$GLOBALS['dbhost'] = 'localhost';
$GLOBALS['dbprefix'] = '';
} // if
// NOTE: $dbprefix is for my web host (shared) where my databases are prefixed
// with my account name to keep them separate from other accounts.
/*
if (preg_match('/^(local\.radicore\.org|127.0.0.1|localhost|des ktop|laptop)$/i', $_SERVER['SERVER_NAME'])) {
// this is for my PC
global $servers;
// server 0
$servers[0]['dbhost'] = $GLOBALS['dbhost'];
$servers[0]['dbengine'] = $GLOBALS['dbms'];
if (isset($GLOBALS['SQLSRV_schema'])) {
$servers[0]['SQLSRV_schema'] = $GLOBALS['SQLSRV_schema'];
$servers[0]['serverName'] = $GLOBALS['serverName'];
$servers[0]['connectionInfo'] = $GLOBALS['connectionInfo'];
} // if
if (isset($GLOBALS['PGSQL_dbname'])) {
$servers[0]['PGSQL_dbname'] = $GLOBALS['PGSQL_dbname'];
} // if
$servers[0]['dbusername'] = 'tony';
$servers[0]['dbuserpass'] = 'tony';
$servers[0]['dbport'] = '';
$servers[0]['dbsocket'] = '';
$servers[0]['dbprefix'] = $GLOBALS['dbprefix'];
$servers[0]['dbnames'] = 'audit,radicore,menu,dict,workflow,classroom,product,survey, xample';
// server 1
$servers[1]['dbhost'] = $GLOBALS['dbhost'];
$servers[1]['dbengine'] = $GLOBALS['dbms'];
$servers[1]['dbusername'] = 'tony';
$servers[1]['dbuserpass'] = 'tony';
$servers[1]['dbport'] = '';
$servers[1]['dbsocket'] = '';
$servers[1]['dbprefix'] = '';
$servers[1]['dbnames'] = '*';
// set these only if secure HTTPS protocol is available on your server
$GLOBALS['http_server'] = 'local.radicore.org';
$GLOBALS['https_server'] = 'local.radicore.org';
$GLOBALS['https_server_suffix'] = '';
//define('HTTPS_NOT_FOR_FILES', true);
} else {
// this is for my shared web host
$GLOBALS['dbusername'] = 'radicore_radicor';
$GLOBALS['dbuserpass'] = 'GrungeBunny';
$GLOBALS['dbprefix'] = 'radicore_';
// set these only if secure HTTPS protocol is available on your server
$GLOBALS['http_server'] = 'www.radicore.org';
//$GLOBALS['https_server'] = 'starburst.secureguards.com/~radicore';
//$GLOBALS['https_server'] = 'neutrino.secureguards.com/~radicore';
//$GLOBALS['https_server'] = 'titan.secureguards.com/~radicore';
//$GLOBALS['https_server_suffix'] = '/~radicore';
// define this only if content such as CSS or JS files are not to be referenced using HTTPS
//define('HTTPS_NOT_FOR_FILES', true);
// converts datetime between client and server timezones (PHP version >= 5.2)
$GLOBALS['server_timezone'] = 'America/New_York';
date_default_timezone_set('America/New_York');
} // if
*/
// this demonstrates the multi-server option (see FAQ92)
if (preg_match('/^(127.0.0.1|localhost|desktop|laptop|id)$/i', $_SERVER['SERVER_NAME'])) {
global $servers;
// server 0
$servers[0]['dbhost'] = 'localhost';
$servers[0]['dbengine'] = 'mysql';
$servers[0]['dbusername'] = 'root';
$servers[0]['dbuserpass'] = '*********';
$servers[0]['dbport'] = '';
$servers[0]['dbsocket'] = '';
$servers[0]['dbprefix'] = '';
$servers[0]['dbnames'] = 'audit,dict,menu,workflow,xample';
$servers[0]['ssl_key'] = '';
$servers[0]['ssl_cert'] = '';
$servers[0]['ssl_ca'] = '';
$servers[0]['ssl_capath'] = '';
$servers[0]['ssl_cipher'] = '';
// server 1
$servers[1]['dbengine'] = 'sqlsrv';
$servers[1]['dbusername'] = '******';
$servers[1]['dbuserpass'] = '*******';
$servers[1]['dbport'] = '';
$servers[1]['dbsocket'] = '';
$servers[1]['dbprefix'] = '';
$servers[1]['dbnames'] = '***';
$servers[1]['serverName'] = 'WIN************'; // test
$servers[1]['SQLSRV_schema'] = 'dbo';
$servers[1]['connectionInfo'] = array('CharacterSet' => 'UTF-8',
'UID' => '*********',
'PWD' => '*********',
'ReturnDatesAsStrings' => true);
// server 2
$servers[2]['dbhost'] = 'id********.com';
$servers[2]['dbengine'] = 'pgsql';
$servers[2]['dbusername'] = 'postgres';
$servers[2]['dbuserpass'] = '************';
$servers[2]['dbport'] = '5432';
$servers[2]['dbsocket'] = '';
$servers[2]['dbprefix'] = '';
$servers[2]['PGSQL_dbname'] = 'radicore';
$servers[2]['dbnames'] = 'newscheme,*';
$servers[2]['ssl_key'] = '';
$servers[2]['ssl_cert'] = '';
$servers[2]['ssl_ca'] = '';
$servers[2]['ssl_capath'] = '';
$servers[2]['ssl_cipher'] = '';
// server
} // if
// set this to true if you want all XSL transformations to be done by the client
// (NOTE: only valid if supported by your client browser)
$GLOBALS['XSLT_client_side'] = false;
// set this to TRUE to write all sql queries to file 'sql/<script_id>.sql' (for debugging)
$GLOBALS['log_sql_query'] = false;
// set date format - choose one of te following
$GLOBALS['date_format'] = 'dmy';
//$GLOBALS['date_format'] = 'mdy';
//$GLOBALS['date_format'] = 'ymd';
// set this to TRUE to write all XML documents to file 'xsl/<script_id>.xml' (for debugging)
if (preg_match('/^(local\.radicore\.org|localhost|desktop|lapto p)$/i', $_SERVER['SERVER_NAME'])
or $_SERVER['REMOTE_ADDR'] == '80.177.120.119') {
$GLOBALS['log_xml_document'] = true;
} else {
$GLOBALS['log_xml_document'] = false;
} // if
// use HTTPS for all web pages
$GLOBALS['use_https'] = false;
// use to access files in the 'classes/custom-processing/<project>/' folder
//$GLOBALS['project_code'] = 'unknown';
// redirect all outgoing emails to this address
//define('MAIL_TO_REDIRECT', 'tony@marston-home.demon.co.uk');
// defines email address(es) to which any error_log will be sent
//define('ERROR_LOG_MAILTO', 'tony@marston-home.demon.co.uk, tonymarston@hotmail.com');
// defines directory into which error logs will be written
define('ERROR_LOG_DIR', $_SERVER['DOCUMENT_ROOT'].'/radicore/error_logs');
/*
if (preg_match('/^(local\.)/i', $_SERVER['SERVER_NAME'])
OR $_SERVER['REMOTE_ADDR'] == '80.177.120.119') {
// causes the logstuff() function to write details to the log file
//define('LOGSTUFF', true);
} // if
*/
?>
[Updated on: Sun, 21 June 2015 12:39] Report message to a moderator |
|
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4789 is a reply to message #4784] |
Sun, 21 June 2015 16:14 |
rafs
Messages: 69
Registered: May 2015
|
Member |
|
|
AJM wrote on Sat, 20 June 2015 04:15For dealing with many-to-many relationships you have a choice of three patterns - multi2, link1 or List2. If you look at the "List Task (Process)" screen in the MENU system you will see examples of all three for dealing with navigation buttons. They each have slightly different ways of showing and modifying the data, so you need to play with them to see which one fits your particular circumstances.
When it comes to a choice between a single MULTI3 or two LINK1s you have to bear in mind that a single task can only deal with a particular relationship in a particular order, so if you have Parent1, Parent2 and Child it can either work in the sequence Parent1 -> Child (with a JOIN to Parent2) or Parent2 -> Child (with a JOIN to Parent1).
The beauty of Radicore is that you can quickly try one transaction pattern, and if that doesn't quite work you can scrap it and try another.
It seems that you can only import a database into one subsystem, so how do I clean out all of the menu and dict entries for this subsystem, if like me, one has messed things up pretty bad. I managed to crash apache from my radicore app. It took me like more hours than I care to admit, to start from scratch and rebuild everything. Many of those hours were spent banging my head against the config.inc file issue (again). And, like the previous time, I ended-up hardcoding my psgsql-schema into the dml class.
Problem signature:
Problem Event Name: APPCRASH
Application Name: httpd.exe
Application Version: 2.4.12.0
Application Timestamp: 54c90386
Fault Module Name: php5ts.dll
Fault Module Version: 5.6.8.0
Fault Module Timestamp: 552ee66a
Exception Code: c00000fd
Exception Offset: 0011cb12
OS Version: 6.1.7601.2.1.0.256.48
Locale ID: 1033
Additional Information 1: d4eb
Additional Information 2: d4eb7cf28a4f910486e91a11f70d294f
Additional Information 3: 2263
Additional Information 4: 2263d97abcebb3530a2e3fb121c56a57
Read our privacy statement online:
http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0409
If the online privacy statement is not available, please read our privacy statement offline:
C:\Windows\system32\en-US\erofflps.txt
[Tue May 26 14:06:41.037032 2015] [ssl:error] [pid 3240:tid 1524] AH02031: Hostname idp-etl provided via SNI, but no hostname provided in HTTP request
[Tue May 26 14:07:15.999694 2015] [mpm_winnt:warn] [pid 3240:tid 356] (OS 64)The specified network name is no longer available. : AH00341: winnt_accept: Asynchronous AcceptEx failed.
[Tue May 26 14:07:16.030894 2015] [mpm_winnt:warn] [pid 3240:tid 356] (OS 64)The specified network name is no longer available. : AH00341: winnt_accept: Asynchronous AcceptEx failed.
[Tue May 26 14:07:30.226919 2015] [ssl:error] [pid 3240:tid 1524] AH02031: Hostname idp-etl provided via SNI, but no hostname provided in HTTP request
[Tue May 26 14:09:42.003351 2015] [mpm_winnt:warn] [pid 3240:tid 356] (OS 64)The specified network name is no longer available. : AH00341: winnt_accept: Asynchronous AcceptEx failed.
[Tue May 26 14:10:00.632783 2015] [mpm_winnt:warn] [pid 3240:tid 356] (OS 64)The specified network name is no longer available. : AH00341: winnt_accept: Asynchronous AcceptEx failed.
[Wed May 27 13:11:50.490209 2015] [ssl:error] [pid 3240:tid 1568] AH02031: Hostname win7pro-x86 provided via SNI, but no hostname provided in HTTP request
Hardcoding works good enough for now. For the record, in this case I had also set config.inc...
[code]
$servers[2]['dbnames'] = 'newscheme'; //this value is not making it to the $args array
// ************************************************************ ****************
// class constructor
// ************************************************************ ****************
function pgsql ($args=null)
{
//var_dump($args);
if (is_string($args)) {
$schema = $args;
} else {
$this->dbname = $args['PGSQL_dbname'];
//$schema =& $args['dbname'];
$schema = 'newscheme'; // hardcoding name
REQUEST_URI: /radicore/dict/database(add1)import.php?session_name=menu13
[code]
|
|
|
|
|
|
| Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4794 is a reply to message #4789] |
Mon, 22 June 2015 04:48 |
AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
1) How do I clean out all of the menu and dict entries for this subsystem, if like me, one has messed things up pretty bad. I managed to crash apache from my radicore app. It took me like more hours than I care to admit, to start from scratch and rebuild everything. Many of those hours were spent banging my head against the config.inc file issue (again). And, like the previous time, I ended-up hardcoding my psgsql-schema into the dml class.
To completely erase a database from the dictionary database, select it and press the "Erase" navigation button.
To completely erase a subsystem from the menu database, select it and press the "Erase" navigation button.
2) It seems that you can only import a database into one subsystem
With the same name yes. If you have the same database name in two servers, and they have exactly the same structure, then you only need one definition in the dictionary.
If they have different structures, then you have to give one of them an alias name by using the "switch_dbnames" option as described in FAQ92.
If you have the same structure in two different servers, and you want to copy data from one server to another using the same class file, then you need to create two objects from the class file, but without using RDCsingleton::getInstance(), such as:
require 'classes/foobar.class.inc';
$object_from = new foobar;
$object_to = new foobar;
Note that both of these objects will have the following properties set to the same value:
$this->dbms_engine
$this->dbname
You can change these values manually in your code before you start using these objects:
$object_from->dbms_engine = 'mysql';
$object_from->dbname = 'alias'; // if there is an alias
You can then read from one object and write to another in order to copy you data from one database on one server to another database on an other server.
Easy Peasy Lemon Squeezy.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Nov 03 09:02:50 EST 2024
Total time taken to generate the page: 0.01752 seconds
|
Radicore Forum
Search
Help
Members
Register
Login
Home
