<!-- 
/*
Copyright 2017 apHarmony

This file is part of jsHarmony.

jsHarmony is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

jsHarmony is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License
along with this package.  If not, see <http://www.gnu.org/licenses/>.
*/
-->
<script type="text/x-tutorial-info">
{
  "ID": "model_database_sql",
  "Title": "Database / Custom SQL Expressions",
  "Menu": ["Models","Models Overview"],
  "Code": [
    "/models/ModelSQL_Grid.json",
    "/models/ModelSQL_Form.json",
    "/models/ModelSQL_Form_m.json",
    "/models/ModelSQL_Multisel.json",
    "/models/ModelSQL_Exec.json",
    "/models/ModelSQL_Exec_multirecordset.json",
    "/models/ModelSQL_Grid_sqlselect_sort.json",
    "/models/ModelSQL_Field_sqlselect.json"
  ]
}
</script>

<h3>Overview</h3>
One of the key features of jsHarmony is its database integration.  Not only is the SQL automatically generated in most instances, but forms and grids can also have data types and controls automatically chosen based on the database schema.<br/>
<br/>
Enterprise systems, however, often require a high level of customization.  The automatic SQL expressions may need to be customized in different scenarios.  A variety of model and field properties are available for custom SQL behavior.

<h3>Model Datasource</h3>
The model datasource is set using the "table" property.  The value of the "table" property can be either a table or view.  Insert / Update / Delete statements will be automatically generated for that table or view.<br/>
<br/>
Schemas can be used in the table property by setting the value to SCHEMA.TABLE:
<pre>
{
  "table": "public.cust"
}
</pre>

<h4>Selecting from Multiple Tables</h4>
In order to select data from multiple tables, it will be necessary to either:
<ul>
  <li>Create a view based on multiple tables, and set the model's "table" property to the name of the view</li>
  <li>If the fields are read-only, use a "field.sqlselect" expression for each field that selects data from the other table.</li>
  <li>If using a form, create a subform to access the other table</li>
</ul>
In many databases, views are read-only by default.  There are two options for making views updateable:
<ul>
  <li>Use triggers, views can be made updateable at the database-level</li>
  <li>Custom sqlselect, sqlinsert, sqlupdate, and sqldelete statements can be written (see below)</li>
</ul>

<h3>Custom SQL</h3>
Custom SQL expressions can be used to replace any of the generated SQL.  View the raw SQL for any model by turning on database debugging in app.config.local.js:<br/>
<pre>
config.debug_params.db_requests = true;
</pre>

Below is an overview of the SQL expressions that are generated.
<pre>
sqlselect
  Grid: select [i]fields[/i] from [i]table[/i] where %%%SQLWHERE%%% %%%DATALOCKS%%% %%%SEARCH%%% order by %%%SORT%%% limit %%%ROWCOUNT%%% offset %%%ROWSTART%%%
        ** or **
        select %%%SQLFIELDS%%% from [i]table[/i] where %%%SQLWHERE%%% %%%DATALOCKS%%% %%%SEARCH%%% order by %%%SORT%%% limit %%%ROWCOUNT%%% offset %%%ROWSTART%%%
        ** or **
        select [i]fields[/i] from [i]table[/i] %%%SQLSUFFIX%%%
        ** or **
        select %%%SQLFIELDS%%% from [i]table[/i] %%%SQLSUFFIX%%%
  Form: select [i]fields[/i] from [i]table[/i] where %%%SQLWHERE%%% %%%DATALOCKS%%% and [i]key=@key[/i]
  Form-m: select [i]fields[/i] from [i]table[/i] where %%%SQLWHERE%%% %%%DATALOCKS%%% and [i]key=@key[/i] order by %%%SORT%%%
  Multisel: select [i]fields[/i] from [i]table[/i] full outer join (%%%LOVSQL%%%) multiparent ... where %%%SQLWHERE%%% %%%DATALOCKS%%%
sqlrowcount
  select count(*) as cnt from [i]table[/i] where %%%SQLWHERE%%% %%%DATALOCKS%%% %%%SEARCH%%%
  ** or **
  select count(*) as cnt from [i]table[/i] %%%SQLSUFFIX%%%
sqlinsert
  insert into %%%TABLE%%%(%%%FIELDS%%%) values(%%%VALUES%%%);select scope_identity() as CHG_ID;
sqlinsertencrypt
  update [i]table[/i] set [i]field=@field[/i] where 1=1 %%%DATALOCKS%%% and [i]key=@key[/i]
sqlupdate
  Form: $getUpdateCount(update %%%TABLE%%% set %%%FIELDS%%% where (%%%SQLWHERE%%%) %%%DATALOCKS%%% %%%KEYS%%%)
        update [i]table[/i] set [i]field=@field[/i] where (%%%SQLWHERE%%%) %%%DATALOCKS%%% and [i]key=@key[/i]
  Multisel: delete from [i]table[/i] where (%%%SQLWHERE%%%) and [i]key=@key[/i] and [i]field[/i] not in (...) %%%DATALOCKS%%%
            insert into [i]table[/i] select from %%%LOVSQL%%% where [i]field[/i] in (...) and [i]field[/i] not in (... where (%%%SQLWHERE%%%)) %%%DATALOCKS%%%
sqldelete
  delete from [i]table[/i] where (%%%SQLWHERE%%%) %%%DATALOCKS%%% and [i]key=@key[/i]
sqldownloadselect
  select [i]fields[/i] from [i]table[/i] where (%%%SQLWHERE%%%) %%%DATALOCKS%%% and [i]key=@key[/i]
sqlexec
  %%%DATALOCKS%%%

For any models except "exec" models, the %%%SQL%%% variable can be used to prepend or append to the entire generated SQL string
</pre>

<h4>model.sqlselect / model.sqlwhere / model.sqlrowcount</h4>
The "sqlselect" property can be used to override the full select SQL statement.  Since grids implement sorting, search, and paging, the following variables are available:
<ul>
  <li>%%%SQLFIELDS%%% - The list of SQL field expressions based on the model fields</li>
  <li>%%%SQLWHERE%%% - The combined model.sqlwhere and field.sqlwhere statements</li>
  <li>%%%DATALOCKS%%% - Datalocks generated for this model</li>
  <li>%%%SEARCH%%% - The search query based on the Grid search</li>
  <li>%%%SORT%%% - Sort expression</li>
  <li>%%%ROWCOUNT%%% - Number of rows to be returned per page</li>
  <li>%%%ROWSTART%%% - Page offset</li>
  <li>
    %%%SQLSUFFIX%%% - Substitute for all of the above (to simplify custom SQL definition).  Translates to:<br/>
    &nbsp; &nbsp; <b>model.sqlselect:</b> where %%%SQLWHERE%%% %%%DATALOCKS%%% %%%SEARCH%%% order by %%%SORT%%% limit %%%ROWCOUNT%%% offset %%%ROWSTART%%%<br/>
    &nbsp; &nbsp; <b>model.sqlrowcount:</b> where %%%SQLWHERE%%% %%%DATALOCKS%%% %%%SEARCH%%%
  </li>
</ul>
Since "multisel" joins against the List of Values (LOV), the %%%LOVSQL%%% parameter additionally provides the SQL for the List of Values.<br/>
<br/>
Custom "required" search clauses are most easily implemented using the model's "sqlwhere" property:
<pre>
{
  "sqlwhere": "cust_sts='ACTIVE'"
}
</pre>

Simple sort expressions can be built using model.sort, as described in the <a href="/tutorials/grid_basic#sort">Basic Grid</a> tutorial.<br/>
<br/>
Additional permanent prefixed or suffixed sort expressions can be added using "model.sqlselect", for instance to always display "ACTIVE" customers on top:
<pre>
{
  "sqlselect":"select cust_id, cust_name,cust_sts from cust where %%%SQLWHERE%%% %%%DATALOCKS%%% %%%SEARCH%%% order by (case when cust_sts='ACTIVE' then 0 else 1 end),%%%SORT%%% limit %%%ROWCOUNT%%% offset %%%ROWSTART%%%"
}
</pre>

<h4>model.sqlrowcount</h4>
The "sqlrowcount" statement is executed during Grid select statements to return the total number of results.<br/>
<br/>
The <a href="/tutorials/grid_basic#number_of_rows">Basic Grid</a> tutorial has a screenshot of a grid with the "grid_rowcount" property enabled.

<h4>model.sqlinsert / model.sqlinsertencrypt</h4>
When grid or form data is saved, the "sqlinsert" and "sqlinsertencrypt" sql statements are executed.  The "sqlinsert" statement is first executed to create the row.<br/>
<br/>
If encrypted data is being saved, the primary key is used as part of the encryption algorithm, so any encrypted fields need to be saved in a subsequent "sqlinsertencrypt" update statement.<br/>
<br/>
The sqlinsert statement should return the new primary key.  This will be used to reselect the data.<br/>
<br/>
The sqlinsertencrypt statement should return the # of rows that were updated.  This validates that the update completed successfully.
<pre>
  Sample sqlinsert:
    with xrslt as (insert into cust(cust_name,cust_sts)  values(@cust_name,@cust_sts) returning cust_id as cust_id) select cust_id from xrslt;
  Sample sqlinsertencrypt:
    with xrslt as (update cust set cust_ssn=@cust_ssn,cust_ssnhash=@cust_ssnhash where 1=1 and cust_id=@cust_id returning 1) select count(*) xrowcount from xrslt;
</pre>

<h4>model.sqlupdate</h4>
The "sqlupdate" statement is executed for Grid, Form, Form-m, and Multisel updates.  The sqlupdate statement should return the # of rows that were updated.  This validates that the update completed successfully.

<h4>model.sqldelete</h4>
The "sqldelete" statement is executed for Grid, Form, and Form-m deletes.  The sqldelete statement should return the # of rows that were deleted.  This validates that the delete completed successfully.

<h4>model.sqldownloadselect</h4>
The "sqldownloadselect" statement is used for downloading files linked to database records using "file_upload" and "file_download" controls.  The SQL statement should return the file_name and file_extension.  The names of the file_name and file_extension columns are defined in the field.controlparams property.
<pre>
select doc_id,doc_ext,(('D'::text || doc.doc_id::character varying::text) || COALESCE(doc.doc_ext, ''::character varying)::text) as "doc_filename" from jsharmony.doc where 1=1  and doc_id=@doc_id
</pre>

<h4>model.sqlexec</h4>


<h3>SQL Parameters / Order of Execution / sql_params</h3>
There are three ways SQL statements can be executed:
<ul>
  <li>
    <b>Standard Non-transaction</b><br/>
    SQL statements are executed in parallel.  All SQL parameters are passed-in, either via the Query String or Parent / Child Bindings.
  </li>
    <b>Transaction</b><br/>
    SQL statements are executed in serial.  The results from one SQL statement are passed in to be available as parameters to the next SQL statement.<br/>
    Parameters that should be resolved based on SQL results should have their value set to %%%FIELD%%%
  <li>
    <b>Waterfall Non-transaction</b><br/>
    SQL statements can be grouped into batches.  SQL statements in each individual batch will be executed in parallel, and then aggregated and passed to the next batch.
  </li>
</ul>
Save statements are executed in transactions.  In forms with parent / subforms, parents are saved first, and then results of the operation are passed to the subform.  This is done so that primary keys can be passed to the child forms.<br/>
<br/>
Form "select" statements are executed as waterfall non-transaction statements.  The primary "sqlselect" statement is executed first, and the result is then passed to the Title, Breadcrumb, Default Parameter, and LOV sql statements.  This enables results from the initial select to be fed back as parameters into the subsequent statements.<br/>
<br/>
All other statements are executed as standard non-transaction statements.<br/>
<br/>
In parent / child forms, the "select" statements are executed as an asynchronous tree, with the Parent "select" performed first.  Then any bindings from the Parent "select" statement can be applied to the parameters of the Child forms.<br/>

<h4>sql_params</h4>
The model.breadcrumbs, field.lov, and field.default properties additionally have a "sql_params" property.  This is used to keep track of the SQL parameters required for the respective SQL statement.<br/>
<br/>
In most cases, sql_params is automatically generated on system load, by parsing the model fields and checking which fields are present as parameters in the SQL.  If these are not automatically recognized, it may be necessary to manually populate the sql_params array.<br/>
<br/>
The values for sql_params are found from the querystring.  If the value is not passed in the querystring, the resulting values from previous SQL statements in the same request are used.  For instance, in "form" models, SQL parameters for List of Values can be based on values of fields retrieved in the SQL select statement, since the LOV / Default / Breadcrumb queries are executed after the SQL select.

<h3>Primary Key / No Key / Unbound</h3>
Primary keys are pulled from the Querystring or from the Parent Form's bindings.  In insert statements, the Primary Key should be returned from the insert operation, so that the row can be reselected.

<h4>model.nokey</h4>
If no primary key exists for the model, for instance in a Grid without a primary key, the "model.nokey" attribute should be set:
<pre>
{
  "nokey": true
}
</pre>

A model with the "nokey" attribute will still execute the "select" SQL statements.

<h4>model.unbound</h4>
A model with the "unbound" attribute will not execute any SQL statements for Select, Insert, Update, or Delete.  Unbound models will still execute SQL on initial load for List of Values, Default Values, Breadcrumbs, or the Title, if applicable.  An unbound model will automatically also set field.unbound=true for all fields in that model.
<pre>
{
  "unbound": true
}
</pre>

<h3>Sample Grid SQL</h3>
For the model:
<pre>
{
  "table":"cust",
  "layout":"grid",
  "caption":["Customer","Customers"],
  "grid_rowcount":true,
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","caption":"Customer"},
    {"name":"cust_sts","caption":"Status"}
  ]
}
</pre>

<pre>
On Load:
  sqlselect: select cust_id,cust_name,cust_sts,(select code_txt from public.code_cust_sts where code_val=(cust_sts)) as __code_txt__cust_sts from cust where  1=1   order by cust_id asc limit 51 offset 0
  sqlrowcount: select count(*) as cnt from cust where  1=1
  LOV: select code_val,code_txt from public.code_cust_sts where (code_end_dt is null or code_end_dt>current_date) order by code_seq,code_txt

On Search:
  sqlselect: select cust_id,cust_name,cust_sts,(select code_txt from public.code_cust_sts where code_val=(cust_sts)) as __code_txt__cust_sts from cust where  1=1   and ( cust_name ilike @search_0_cust_name) order by cust_id asc limit 51 offset 0
             @search_0_cust_name:"%ACME%"
  sqlrowcount: select count(*) as cnt from cust where  1=1   and ( cust_name ilike @search_0_cust_name)
             @search_0_cust_name:"%ACME%"

On Insert:
  sqlinsert: with xrslt as (insert into cust(cust_name,cust_sts)  values(@cust_name,@cust_sts) returning cust_id as cust_id) select cust_id from xrslt;

On Update:
  sqlupdate: with xrslt as (update cust set cust_name=@cust_name,cust_sts=@cust_sts where 1=1 and cust_id=@cust_id returning 1) select count(*) xrowcount from xrslt;

On Delete:
  sqldelete: with xrslt as (delete from cust where 1=1  and cust_id=@cust_id returning 1) select count(*) xrowcount from xrslt;
</pre>

<h3>Sample Form SQL</h3>
For the model:
<pre>
{
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"}
  ]
}
</pre>

<pre>
On Load:
  Insert:
    LOV: select code_val,code_txt from public.code_cust_sts where (code_end_dt is null or code_end_dt>current_date) order by code_seq,code_txt
  Browse/Update:
    sqlselect: select cust_id,cust_name,cust_sts,(select code_txt from public.code_cust_sts where code_val=(cust_sts)) as __code_txt__cust_sts from cust where  1=1  and cust_id=@cust_id
    LOV: select code_val,code_txt from public.code_cust_sts where (code_end_dt is null or code_end_dt>current_date) order by code_seq,code_txt

On Insert:
  sqlinsert: with xrslt as (insert into cust(cust_name,cust_sts)  values(@cust_name,@cust_sts) returning cust_id as cust_id) select cust_id from xrslt;

On Update:
  sqlupdate: with xrslt as (update cust set cust_name=@cust_name,cust_sts=@cust_sts where 1=1  and cust_id=@cust_id returning 1) select count(*) xrowcount from xrslt;

On Delete:
  sqldelete: with xrslt as (delete from cust where 1=1  and cust_id=@cust_id returning 1) select count(*) xrowcount from xrslt;
</pre>

<h3>Sample Form-m SQL</h3>
For the model:
<pre>
{
  "table":"cust",
  "layout":"form-m",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"}
  ]
}
</pre>

<pre>
On Load:
  sqlselect: select cust_id,cust_name,cust_sts,(select code_txt from public.code_cust_sts where code_val=(cust_sts)) as __code_txt__cust_sts from cust where  1=1  order by cust_id asc
  LOV: select code_val,code_txt from public.code_cust_sts where (code_end_dt is null or code_end_dt>current_date) order by code_seq,code_txt

On Insert:
  sqlinsert: with xrslt as (insert into cust(cust_name,cust_sts)  values(@cust_name,@cust_sts) returning cust_id as cust_id) select cust_id from xrslt;

On Update:
  sqlupdate: with xrslt as (update cust set cust_name=@cust_name,cust_sts=@cust_sts where 1=1  and cust_id=@cust_id returning 1) select count(*) xrowcount from xrslt;

On Delete:
  sqldelete: with xrslt as (delete from cust where 1=1  and cust_id=@cust_id returning 1) select count(*) xrowcount from xrslt;
</pre>

<h3>Sample Multisel SQL</h3>
For the model:
<pre>
{
  "table":"cust_flag",
  "layout":"multisel",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_flag_id"},
    {"name":"cust_flag_type","caption":"Flag"}
  ]
}
</pre>

<pre>
On Load:
  sqlselect: select cust_flag_type ,coalesce(code_val::text,cust_flag_type::text) code_val,coalesce(coalesce(code_txt::text,code_val::text),cust_flag_type::text) code_txt from (select * from cust_flag where 1=1  and cust_id=@cust_id) cust_flag full outer join (select code_val,code_txt,code_seq from code_cust_flag_type where (code_end_dt is null or code_end_dt>current_date)) multiparent on multiparent.code_val = cust_flag.cust_flag_type order by code_seq,code_txt

On Save:
  with xrslt_delete as (delete from cust_flag where 1=1  and cust_id=@cust_id and cust_flag_type::text not in (@multisel0,@multisel1)  returning 1),xrslt_insert as (insert into cust_flag(cust_id,cust_flag_type) select @cust_id,code_val from (select code_val,code_txt,code_seq from code_cust_flag_type where (code_end_dt is null or code_end_dt>current_date)) multiparent where code_val::text in (@multisel0,@multisel1) and code_val not in (select cust_flag_type from cust_flag where 1=1  and cust_id=@cust_id ) returning 1) select (select count(*) from xrslt_delete)+(select count(*) from xrslt_insert) xrowcount;
    @multisel0: "SELECTED_VALUE_0"
    @multisel1: "SELECTED_VALUE_1"
</pre>

<h3>Sample Exec SQL</h3>
For the model:
<pre>
{
  "table":"cust",
  "layout":"exec",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "sqlexec": "select @cust_sts as status", 
  "fields":[
    {"name":"cust_sts","caption":"Status"},
    {"name":"RUN","control":"button","value":"Run","caption":"",
      "onclick":"XPage.RunDBFunc(this,function(rslt){ XExt.Alert(rslt[modelid][0].status); }, { noMessage: true });"}
  ]
}
</pre>

<pre>
On Load:
  LOV: select code_val,code_txt from public.code_cust_sts where (code_end_dt is null or code_end_dt>current_date) order by code_seq,code_txt

On Button Click:
  sqlexec: select @cust_sts as status
</pre>

<h3>SQL Return Type - model.sqltype</h3>
"Exec" models can return either a single table/resultset, or multiple tables/resultsets.  If the query should return multiple tables, change the "sqltype" to "multirecordset" instead of the default "recordset".
<pre>
{
  "layout":"exec",
  "sqlexec": [
    "select * from cust where cust_id=@cust_id;",
    "select * from cust_flag where cust_id=@cust_id;"
  ],
  "sqltype":"multirecordset",
  "table":"cust",
  "fields":[
    {"name":"cust_id","actions":"BIU"}
  ]
}
</pre>

<h3>Field-level Properties</h3>
SQL can be customized on a per-field basis, if a field requires a custom SQL expression for a select, insert, or update operation.

<h4>field.sqlselect</h4>
For select statements, replace FIELD with EXPRESSION
<pre>
{
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_modified","sqlselect":"current_timestamp"}
  ]
}

Resulting SQL
-------------
select cust_id, current_timestamp from cust
</pre>

<h4>field.sqlwhere</h4>
For select statements in grids, if field is in querystring / bindings, use this WHERE expression
<pre>
{
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_name","sqlwhere":"(cust_name like '%'||@cust_name||'%' or cust_desc like '%'||@cust_name||'%')"}
  ]
}

Resulting SQL
-------------
select cust_id,cust_name from cust where (cust_name like '%'||@cust_name||'%' or cust_desc like '%'||@cust_name||'%')
</pre>

<h4>field.sqlinsert</h4>
For insert statements, replace FIELD_VALUE with EXPRESSION
<pre>
{
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_modified","sqlinsert":"current_timestamp"}
  ]
}

Resulting SQL
-------------
insert into cust(cust_modified) values(current_timestamp);
</pre>

<h4>field.sqlupdate</h4>
For update statements, replace FIELD_VALUE with EXPRESSION
<pre>
{
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_modified","sqlupdate":"current_timestamp"}
  ]
}

Resulting SQL
-------------
update cust set cust_modified=current_timestamp where cust_id=@cust_id
</pre>

<h4>field.sqlsearch</h4>
For grids, use the specified search expression.
<pre>
{
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_name","sqlsearch":"(cust_name||' '||cust_desc)"}
  ]
}

Resulting SQL
-------------
If the user searches the cust_name field for any rows containing "ACME":
  select cust_id,cust_name from cust where (cust_name||' '||cust_desc) like '%ACME%'
</pre>

<h4>field.sqlsearchsound</h4>
For grids, use the specified Soundex search expression.  The field supports the following variables:
<ul>
  <li>%%%FIELD%%% - Will be replaced by the search query</li>
  <li>%%%SOUNDEX%%% - Will be replaced by a Soundex of the search query</li>
</ul>
<pre>
{
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_name","sqlsearchsound":"cust_id in (select table_id from soundex where table_name='cust' and field_name='cust_name' and soundex_val=soundex(%%%FIELD%%%))"}
  ]
}

Resulting SQL
-------------
If the user searches the cust_name field for any rows that sound like "John":
  select cust_id,cust_name from cust where cust_id in (select table_id from soundex where table_name='cust' and field_name='cust_name' and soundex_val=soundex('John'))
</pre>

<h4>field.sqlsort</h4>
For grids, use the specified sort expression.
<pre>
{
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_name","sqlsort":"ifnull(cust_name,cust_id)"}
  ]
}

Resulting SQL
-------------
If the user sorts by the cust_name column:
  select cust_id,cust_name from cust order by (ifnull(cust_name,cust_id)) desc
</pre>

<h3>Multiple Databases</h3>
One jsHarmony project can connect to multiple databases.  Each model has a "db" value that can be set to the database connection.<br/>
<br/>
First, define the second database in app.config.local.js:
<pre>
dbconfig['secondary'] = { 
  server: "server.domain.com", 
  database: "DBNAME", 
  user: "DBUSER", 
  password: "DBPASS", 
  _driver: new pgsqlDBDriver()
}
</pre>
Then, set the target model's "db" property to the new database:
<pre>
{
  "db": "secondary"
}
</pre>
The default database is named "default".  If using the jsHarmony Factory, the Factory will be initialized into the "default" database.