Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

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 Go to next message
rafs is currently offline  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 #4752 is a reply to message #4749] Sat, 23 May 2015 09:46 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
I personally would not create a separate class to perform the transformation. Basically what you are trying to do is this:
(1) Read a record from tableA with one structure
(2) Add a record to tableB with a different structure

You can either put the transformation code between these two steps, or in the _cm_pre_insertRecord method of table tableB. I personally have used both of these methods, and as they both work it is just a matter of personal preference.


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 Go to previous messageGo to next message
rafs is currently offline  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 #4762 is a reply to message #4761] Tue, 02 June 2015 23:28 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
Here is another, more simple test. Here it appears that I am trying to insert, rather than update...basically I am missing the key idea.

You can see the errors:
38 Debug Errors:
39 array(2) {
40 ["sid_type_id"]=>
41 string(38) "A record already exists with this key."
42 ["sid"]=>
43 string(38) "A record already exists with this key."

test-batch-simple.php
  1 <?php
  2 // Batch Test
  3
  4 ini_set('include_path', '.');
  5 require 'std.batch.inc';
  6
  7 batchInit(__FILE__);
  8
  9 $dbobj = RDCsingleton::getInstance('sids');
 10 $dbobj->sql_select  = '';
 11 $dbobj->sql_from    = '';
 12 $dbobj->sql_where   = 'sid_type_id=2';
 13 $dbobj->sql_groupby = '';
 14 $dbobj->sql_orderby = 'sid ASC';
 15
 16 $fieldarray = $dbobj->getData("sid=15");
 17 print "Debug\tgetData:\n";
 18 var_dump($fieldarray);
 19
 20 // simple modification
 21 $fieldaray['deployment_status_flags'] = $fieldarray['deployment_status_flags'] | 2;
 22
 23 $dbobj->startTransaction();
 24 $rowdata = $dbobj->updateRecord($fieldarray);
 25
 26 if ( !empty($dbobj->errors) ) {
 27     print "Debug\tErrors:\n";
 28     var_dump($dbobj->errors);
 29     $dbobj->rollback();
 30 } else {
 31     $dbobj->commit();
 32 }
 33
 34 if ( isset($dbobj->messages) ) {
 35     print "Debug\tmessages: \n";
 36     var_dump($dbobj->messages);
 37 }
 38
 39 print "Debug\tAfter update rows:\n";
 40 var_dump($rowdata);
 41
 42 batchEnd();
 43
 44 ?>


out
     7  <p>server API   : cli</p>
     8  <p>language     : en-us</p>
     9  <p>server_admin : </p>
    10  <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>
    11  <p>project_code : </p>
    12  <p>user_id : batch</p>
    13  <p>role_id : batch</p>
    14  Debug   getData:
    15  array(1) {
    16    [0]=>
    17    array(9) {
    18      ["id"]=>
    19      string(1) "2"
    20      ["sid_type_id"]=>
    21      string(1) "2"
    22      ["sid"]=>
    23      string(2) "15"
    24      ["current_signature_id"]=>
    25      NULL
    26      ["deployment_status_flags"]=>
    27      string(1) "2"
    28      ["updated_at"]=>
    29      NULL
    30      ["updated_by"]=>
    31      NULL
    32      ["first_deployed_timestamp"]=>
    33      NULL
    34      ["last_deployed_timestamp"]=>
    35      NULL
    36    }
    37  }
    38  Debug   Errors:
    39  array(2) {
    40    ["sid_type_id"]=>
    41    string(38) "A record already exists with this key."
    42    ["sid"]=>
    43    string(38) "A record already exists with this key."
    44  }
    45  Debug   messages:
    46  array(0) {
    47  }
    48  Debug   After update rows:
    49  array(9) {
    50    ["id"]=>
    51    string(1) "2"
    52    ["sid_type_id"]=>
    53    string(1) "2"
    54    ["sid"]=>
    55    string(2) "15"
    56    ["current_signature_id"]=>
    57    NULL
    58    ["deployment_status_flags"]=>
    59    string(1) "2"
    60    ["updated_at"]=>
    61    NULL
    62    ["updated_by"]=>
    63    NULL
    64    ["first_deployed_timestamp"]=>
    65    NULL
    66    ["last_deployed_timestamp"]=>
    67    NULL
    68  }
    69  <p>** finished at June 3, 2015, 5:16 am</p>
    70  </html>
Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4763 is a reply to message #4762] Wed, 03 June 2015 05:09 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
The title of this thread suggests that you are copying from one database to another, yet your sample code shows that you are reading and writing to the same table 'sids'. If the update is not working you need to step through with your debugger to see what values are actually being passed to the update method as what is being received may be different from what you think you are sending. If none of the table's columns have actually been changed then nothing will be updated.

Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4764 is a reply to message #4763] Wed, 03 June 2015 05:31 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
Both test posted above are simple tests to see if I can updateMultiple/updateRecord from a single table, the 'sids' table.
You can see line 41, 43 above, the $dbobj->message(s).
Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4765 is a reply to message #4764] Wed, 03 June 2015 05:32 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
I am just trying to build an intuition for how to use these tables classes.
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 Go to previous messageGo to next message
rafs is currently offline  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 #4770 is a reply to message #4766] Wed, 03 June 2015 19:16 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
In one case it was a typo, '21 $fieldaray...'.
In the other case, I had a mistaken assumption about the format of $postarray in updateMultiple($rows, $postarray).
I think I am back on track.
Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4771 is a reply to message #4770] Thu, 04 June 2015 04:37 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
This is why I always use a debugger to step through my scripts while they are running as this shows up such errors very quickly.

Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4772 is a reply to message #4771] Thu, 04 June 2015 18:39 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
Which debugger do you use? (I see your section faq#51 which shows how to log the XML and SQL...)
I am most comfortable using linux/vim, so I have made a share out of my C:\xampp (this is not accessible from internet), and mounted, so I can just vi from linux command line.

[I have setup Eclipse + PHP (PDT) with Xdebug. Seems pretty good by the look of it so far.]

[Updated on: Thu, 04 June 2015 21:05]

Report message to a moderator

Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4773 is a reply to message #4772] Fri, 05 June 2015 03:08 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
My programming development efficiency just took several orders of magnitude increase. thanks for the suggestion.
Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4774 is a reply to message #4773] Fri, 05 June 2015 04:47 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
I'm glad that you have discovered the benefits of a debugger.

I started off by using a free IDE which came with a debugger, but then I switched to a commercial IDE (Zend Sudio) as it had better support. Then Zend Studio got bloated and slow, so I decided to switch to PhpED from http://www.nusphere.com. I could not imagine trying to do development without a debugger any more as it shows up any problems immediately without you having to guess where they are.


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 Go to previous messageGo to next message
rafs is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


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 Go to previous messageGo to next message
rafs is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


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 Go to previous messageGo to next message
rafs is currently offline  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 #4788 is a reply to message #4787] Sun, 21 June 2015 13:16 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
opps, wrong thread.

[Updated on: Sun, 21 June 2015 13:29]

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 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
AJM wrote on Sat, 20 June 2015 04:15
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.


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 #4793 is a reply to message #4787] Mon, 22 June 2015 04:20 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
I do not know how you are getting this error:

Fatal Error: Undefined variable: unbuffered_query
Error in line 78 of file 'C:\xampp\radicore\includes\dml.pgsql.class.inc'.

as there is no reference anywhere in dml.pgsql.class.inc to a variable called "unbuffered_query"


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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
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.


Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4808 is a reply to message #4749] Wed, 08 July 2015 03:28 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
Just when I thought I was nearing the surface...wirlpool!...and back under water. I am probably going to need to learn about Petri Nets now too to make some workflows, and figure out which transaction patterns to use for them.

I am overwhelmed, but also completely in awe at the complexity of this system you have put together. I only hope that I will can hang in there to get something working.
Re: Batch Script connecting two databases in an Extract Tranform Load (ETL) Design Pattern [message #4809 is a reply to message #4808] Wed, 08 July 2015 05:17 Go to previous message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
If you click on /workflow/docs/index.html you will see some documentation on the sample workflows which I have set up in the demonstration system. You can make any one of these active simply by removing its end_date, then simply by using the "Add Person" task in the "Example" susbsystem you will then start a workflow case. Note that you cannot have more than one workflow active at the same time with the same starting task.

Previous Topic: How to use actions(submit,submitstay,copy,quit)
Next Topic: tutorial4 Viewing the Tree structure
Goto Forum:
  


Current Time: Thu Mar 28 15:21:28 EDT 2024

Total time taken to generate the page: 0.01494 seconds