Security News
tea.xyz Spam Plagues npm and RubyGems Package Registries
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
gopherdata
Advanced tools
Changelog
Gopher v0.1.4 (11 June 2017)
Updates gopher
:
Gopher.run
function now uses Swap Values and will recognize a swapValues property with the Transaction PlanReadme
Develop and organize your recycled database statements and queries to be executed from within a Node.js environment against any one (or all) of your Oracle Database environments from a centralized location using your own named/aliased Connections and Transactions.
Jump to a section | Section description |
---|---|
Oracle Instant Client Setup | Get up and going with Oracle Instant Client |
Gopher Concepts | A quick reference guide for application concepts |
Setup | Setup a basic demo app |
Configure | Configure some example libraries |
Build a Gopher | Create a basic Gopher |
Create a Gopher Schema | Create a family of Gopher Types |
Create Gopher Calls | Put your Gophers to use |
~/oracle
unzip instantclient-basic-macos.x64-12.1.0.2.0.zip -d ~/oracle
unzip instantclient-sdk-macos.x64-12.1.0.2.0.zip -d ~/oracle
##### Oracle Instant Client 12.1 #####
export OCI_HOME=~/oracle/instantclient_12_1
export OCI_LIB_DIR=$OCI_HOME
export OCI_INC_DIR=$OCI_HOME/sdk/include
export OCI_INCLUDE_DIR=$OCI_HOME/sdk/include
export DYLD_LIBRARY_PATH=$OCI_LIB_DIR
ln -s ~/oracle/instantclient_12_1/libclntsh.dylib.12.1 ~/oracle/instantclient_12_1/libclntsh.dylib
ln -s ~/oracle/instantclient_12_1/libocci.dylib.12.1 ~/oracle/instantclient_12_1/libocci.dylib
source ~/.bashrc
The following instruction has been written to help you set up a basic model for usage called "gopher-demo". Please note that (for simplicity sake) this demo is non-functional as it will not connect you to any real database. It is meant to cover core concepts for usage providing examples that demonstrate how to get started, and how to get the most from this software.
npm init
npm install gopherdata
mkdir -p libraries/connection
mkdir libraries/transaction
touch libraries/connection/finance-connections.json
touch libraries/transaction/oracle-dictionary.json
touch libraries/transaction/finance-reports-2016-DEV.json
touch libraries/transaction/finance-reports-2016-ETL-DEV.json
./libraries/connection/finance-connections.json
:[
{"finance-Prod" :{
"user" : "me",
"password" : "myProdPassword",
"host" : "databases.arecool.com",
"port" : 12345,
"service" : "databases.arecool.com"
}},
{"finance-QA" :{
"user" : "me",
"password" : "myQAPassword",
"host" : "QAdatabases.arecool.com",
"port" : 12345,
"service" : "QAdatabases.arecool.com"
}},
{"finance-Dev" :{
"user" : "me",
"password" : "myDevPassword",
"host" : "financedatabases.arecooltoo.com",
"port" : 12345,
"SID" : "financedatabases.arecooltoo.com"
}}
]
./libraries/transaction/oracle-dictionary.json
:[
{"get-db-tables" :{
"dbStatement" : "SELECT a.object_name AS \"TABLE\" FROM sys.user_objects a INNER JOIN sys.user_all_tables b ON a.object_name = b.table_name WHERE a.object_type = 'TABLE' ORDER BY b.table_name",
"zeroRowMessage" : "No Tables found"
}},
{"get-db-table-columns" :{
"dbStatement" : "SELECT column_name AS \"COLUMN\" FROM sys.user_tab_columns WHERE lower(table_name) = lower(:tableName)",
"bindVariables" : {"tableName":"dual"},
"zeroRowMessage" : "Table not found"
}},
{"get-db-views" :{
"dbStatement" : "SELECT object_name AS \"VIEW\", substr(status, 1, 1) AS status,last_ddl_time,created FROM sys.user_objects WHERE object_type = 'VIEW' ORDER BY object_name",
"zeroRowMessage" : "No Views found"
}}
]
./libraries/transaction/finance-reports-2016-DEV.json
:[
{"get-quarterly-report-2016" :{
"dbStatement" : "SELECT region, division, storeID, year, quarter, total_sales FROM quarterly_sales_2016 WHERE lower(storeID) = lower(:storeID) AND lower(quarter) = lower(:quarter)) ORDER BY year, quarter",
"bindVariables" :{"storeID":null, "quarter":null},
"zeroRowMessage" : "Not enough info entered"
}},
{"get-annual-report-2016" :{
"dbStatement" : "SELECT region, division, storeID, year, quarter, total_sales FROM annual_sales_2014-2016 WHERE lower(storeID) = lower(:storeID) AND lower(year) = lower(:year)",
"bindVariables" : {"storeID":null, "year":null},
"zeroRowMessage" : "Not enough info entered"
}}
]
./libraries/transaction/finance-reports-2016-ETL-DEV.json
:[
{"drop-quarterly-sales-2016" :{
"dbStatement" : "DROP MATERIALIZED VIEW quarterly_sales_2016"
}},
{"build-quarterly-sales-2016" :{
"dbStatement" : "CREATE MATERIALIZED VIEW quarterly_sales_2016 BUILD IMMEDIATE REFRESH FORCE ON DEMAND AS SELECT region, division, storeID, year, quarter, total_sales FROM quarterly_sales WHERE year = 2016"
}},
{"drop-annual-sales-2014-2016" :{
"dbStatement" : "DROP MATERIALIZED VIEW quarterly_sales_2016"
}},
{"build-annual-sales-2014-2016" :{
"dbStatement" : "CREATE MATERIALIZED VIEW annual_sales_2014-2016 BUILD IMMEDIATE REFRESH FORCE ON DEMAND AS SELECT region, division, storeID, year, quarter, total_sales FROM annual_sales WHERE year IN (2014,2015,2016)"
}}
]
./libraries/connection/finance-connections.json
to include links to Transaction Libraries:[
{"finance-Prod" :{
"user" : "me",
"password" : "myProdPassword",
"host" : "databases.arecool.com",
"port" : 12345,
"service" : "databases.arecool.com",
"transactionLibraries" : ["./libraries/transaction/oracle-dictionary.json",
"./libraries/transaction/finance-reports-2015.json",
"./libraries/transaction/finance-reports-2015-ETL.json"]
}},
{"finance-QA" :{
"user" : "me",
"password" : "myQAPassword",
"host" : "QAdatabases.arecool.com",
"port" : 12345,
"service" : "QAdatabases.arecool.com",
"transactionLibraries" : ["./libraries/transaction/oracle-dictionary.json",
"./libraries/transaction/finance-reports-2015.json",
"./libraries/transaction/finance-reports-2015-ETL.json"
"./libraries/transaction/finance-reports-2016-DEV.json",
"./libraries/transaction/finance-reports-2016-ETL-DEV.json"]
}},
{"finance-Dev" :{
"user" : "me",
"password" : "myDevPassword",
"host" : "financedatabases.arecooltoo.com",
"port" : 12345,
"SID" : "financedatabases.arecooltoo.com",
"transactionLibraries" : ["./libraries/transaction/oracle-dictionary.json",
"./libraries/transaction/financeSandbox.json",
"./libraries/transaction/finance-reports-2015.json",
"./libraries/transaction/finance-reports-2015-ETL.json"
"./libraries/transaction/finance-reports-2016-DEV.json",
"./libraries/transaction/finance-reports-2016-ETL-DEV.json"]
}}
]
Create a simple Gopher that uses the same stored Transaction against two different databases (from the same common library) to get lists of all tables listed in the Finance Production and Finance Development Databases.
touch gopher.js
"use strict";
const Gopher = require('gopherdata');
//Assign Gardens (Gopher vernacular for Db Connection Configurations)
const GARDENS = ['./libraries/connection/finance-connections.json',
'./libraries/connection/myDatabase-connections.json'];
/*******************************************************************************
Create a generic Gopher
********************************************************************************/
var runGopher = function(dbConnection,transactionName,callback){
// Add Transaction Properties here:
let transactionPlan = {transaction : transactionName,
outputFormat : 'array',
responseOutput : ['dbResponse','metaData','metrics']};
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {
return callback(err,res);
}
return callback(err,res);
}
);
}
/*******************************************************************************
Send Gopher on it's way
********************************************************************************/
//get a production db table listing
runGopher('finance-Prod','get-db-tables',
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
//get a development db table listing
runGopher('finance-Dev','get-db-tables',
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
Create a gopher-schema.js file and add links to your Connection Libraries, and then build/configure your Transaction types
touch gopher-schema.js
"use strict";
const Gopher = require('gopherdata');
//Assign Gardens (Gopher vernacular for Db Connection Configurations)
const GARDENS = ['./libraries/connection/myDatabase-connections.json',
'./libraries/connection/corporate-connections.json',
'./libraries/connection/finance-connections.json'];
/*******************************************************************************
Create Different Gophers (i.e. a Gopher Schema) and Configure their
Transaction Properties
********************************************************************************/
//--------------------Without Bind Variables--------------------
exports.run = function(dbConnection,transactionName,callback){
let transactionPlan = {transaction : transactionName};
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {return callback(err,res);}
return callback(err,res);
}
);
}
//----------------------With Bind Variables---------------------
exports.runWBindVariables = function(dbConnection,transactionName,bindVariables,callback){
let transactionPlan = {
transaction : transactionName,
bindVariables : bindVariables
};
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {return callback(err,res);}
return callback(err,res);
}
);
}
//---------------------------Show SQL---------------------------
exports.showSql = function(dbConnection,transactionName,callback){
let transactionPlan = {
transaction : transactionName,
responseOutput : ['sqlOnly']
};
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {return callback(err,res);}
return callback(err,res);
}
);
}
//---------------------------Verbose----------------------------
exports.runVerbose = function(dbConnection,transactionName,callback){
let transactionPlan = {
transaction : transactionName,
responseOutput : ['verbose']
};
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {return callback(err,res);}
return callback(err,res);
}
);
}
//------------------------Get Db Tables-------------------------
exports.getTables = function(dbConnection,callback){
let transactionPlan = {transaction : 'get-db-tables'};
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {return callback(err,res);}
return callback(err,res);
}
);
}
//------------------------Get Db Columns------------------------
exports.getColumns = function(dbConnection,table,callback){
let transactionPlan = {
transaction : 'get-db-table-columns',
bindVariables : {tableName:table}
};
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {return callback(err,res);}
return callback(err,res);
}
);
}
//--------------------------Modifiable--------------------------
exports.runModifiable = function(dbConnection,transactionPlan,callback){
new Gopher({"connection":dbConnection,"connectionLibraries":GARDENS})
.run(transactionPlan,
function(err,res){
if (err) {return callback(err,res);}
return callback(err,res);
}
);
}
touch myGopherCalls.js
"use strict";
const gopher = require('./gopher-schema.js');
let connection = '', // a named connection (configured from within a Connection Library)
transaction = '', // a named transaction (i.e. a canned DDL/DML statement configured from within a Transaction Library)
bindVariables = {}, // unique bind variables associated with a transaction (set/configured from within a Transaction Library)
transactionPlan = {}; // a means by which to override a stored Transaction's default settings
connection = 'finance-Dev';
transaction = 'get-db-tables';
gopher.run(connection,transaction,
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
connection = 'finance-Prod';
transaction = 'get-quarterly-report-2016';
bindVariables = {
storeID : '1234',
quarter : 3
};
gopher.runWBindVariables(connection,transaction, bindVariables,
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
connection = 'finance-Dev';
transaction = 'get-quarterly-report-2016';
bindVariables = {
storeID : '1234',
quarter : 3
};
gopher.showSql(connection,transaction,
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
connection = 'finance-Prod';
gopher.getTables(connection,
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
connection = 'finance-Prod';
var table = 'quarterly_metrics';
gopher.getColumns(connection,table,
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
connection = 'finance-Prod';
transaction = 'get-db-tables';
gopher.runVerbose(connection,transaction,
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
connection = 'finance-QA';
transactionPlan = {
transaction :'get-quarterly-report-2016',
bindVariables :{
storeID : '1234',
quarter : 3
}
outputFormat : 'object', // format used for the database output. choices are "array", "object", or "json". if not set/configured, the application default is "json"
maxRowsReturned : 200 ,// the number of rows returned from database output. if or not set/configured, the application default is 2000
zeroRowMessage : 'No information found for North America Region', // the message returned when nothing is returned. if not set/configured, the application default is "0 rows returned"
responseOutput : ['host','network','connection','dbStatement','error','dbResponse','metaData','metrics'], // use any of the following choices, or use ONLY one of the following special commands: "dataOnly", "sqlOnly", or "verbose". if not set/configured, the application default is "dataOnly"
timeZone : 'local'// sets the time zone for timestamps returned in the response output information (this will NOT modify times/dates/timestamps within the returned dataset)
};
gopher.runModifiable(connectTo,transactionPlan,
function(gophErr, gophRes){
if(gophErr){console.log(gophRes);}
console.log(gophRes)
}
);
FAQs
Develop, Maintain, Organize, and Send your recycled database statements from outside of an Oracle Database. Develop and execute canned or dynamic DDL/DML statements that are stored within library files (in json format) executed from a Node.js configured e
The npm package gopherdata receives a total of 4 weekly downloads. As such, gopherdata popularity was classified as not popular.
We found that gopherdata demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Security News
As cyber threats become more autonomous, AI-powered defenses are crucial for businesses to stay ahead of attackers who can exploit software vulnerabilities at scale.
Security News
UnitedHealth Group disclosed that the ransomware attack on Change Healthcare compromised protected health information for millions in the U.S., with estimated costs to the company expected to reach $1 billion.