izyware-sqlconsole-rekey

    1.0.8 • Public • Published

    Izy SQL-Console Connector

    This package is the main SQL service consumption layer for Izyware apps. It is written in JavaScript, does not require compiling.

    Features

    • Use the rekey command to reassign the primry key values for rows within a particular table in your relational database. The mapping for newIds and oldIds must be defined in batchTrackingTable. The workflow consists of:
      • for each row, create a record in batchTrackingTable with (originalId, newId, tbl) triplet set to the desired values
      • run the rekey command
      • The feature automatically builds the dependency graph for Primary and Foreign keys in the database and will update all the neccessary records automaticaly.
    • Record the timestamp for when the changes to rekeying are made: This will allow tracability and rollback
    • supports stringified cross platform command pattern chains to simplify components that need to delegate, sequence or execute database interactions in hetergenous cloud environment. In addition, this allows bookkeeping and auditing to be conveniently performed transparently.
    • escapes and cleans up parameters. This will protect your components against SQL injection attacks.

    Establishing connections

    The library is compatible with standard mysql node configuration files, i.e.:

    const sqlLib = require('izyware-sqlconsole-rekey').basePath;
    const proxyLib = require('izy-proxy').basePath;
    
    ['chain.importProcessor', `${sqlLib}/chain`, {
        verbose: {
            logConnectionAttempt: false,
            logQuery: false
        }
    }],
    [`//inline/${proxyLib}/json?loadById`, { id:
        {
          "host":"example.org"
        }
    }],
    chain => chain(['sql.connect', chain.get('outcome').data]),
    ['sql.query', 'select 1']
    

    You can consult the nodejs mysql library for detailed options regarding connection pooling, clustering and SSL management. In addition to all the standard options the library supports custom adapters fot SOCKS5.

    [`//inline/${proxyLib}/json?loadById`, { id:
        {
          "adapterservice":"//service/rel:adapter/socks5"
        }
    }]
    

    For embedded applications that require unusual networking environments, you may customize the adapter. Refer to the knowledge center on Izyware website for more details and white papers on this topic.

    Commands Syntax

    • getUpdate

    • getInsert

        ['sql.getInsert', {
            table: 'table1',
            map: [
                { field1: 'value1', ... }
            ]
        ]
      
    • getInsertSelect

    • select

        ['sql.select', {
            verbose: {
                logQuery: false
            },
            map: {
                jsonField1: 'table1.field1',
                owners: 'NOQUOTE__GROUP_CONCAT(DISTINCT CONCAT(accesscontrol.ownerType , "_", accesscontrol.ownerId) SEPARATOR "__sep__")'
            },
            // // useful for parsing joined groups, i.e owners above
            deserializeGroupConcats: ['owners'],
            from: 'FROM table1 left join table2 ....',
            condition: 'WHERE id = 1 limit 1 group by name'
        }]
      
    • query (if chain verbose.logQuery is set it will log)

        /* returns JSON records */
        ['sql.query', 'string'], 
        /* returns Array records */
        ['sql.query', { queryStr: 'select 1', recordFormat: 'array' }]
      

    CLI Schema

    For command line access use:

    npm run query queryObject.dbConfigId xxxx-xxxx queryObject.sqlStr "SELECT 1"
    npm run rekey queryObject.dbConfigId xxxx-xxxx queryObject.batchTrackingTable batchTrackingTable_izyware_sqldashboard_rekey queryObject.limit 20000 queryObject.batchSize 20 queryObject.schema dbname queryObject.tbl tablename queryObject.commit false
    

    Test

    npm run test
    

    Known Issues

    • support for ? characters as placeholders for values you would like to have escaped.
    • implement chain handlers for
      • escapeRegexpCharsForSingleQoutedRegExpSearch
      • encodeStringToSQLStrSingleQuoted

    Notes

    • make sure that NO_BACKSLASH_ESCAPES mode is disabled (this is the default state for MySQL servers).

    External Resources

    Changelog

    V1

    • 5300033: add command line test interface
    • 5300032: seperate the mysql adapter from the chain processor
      • enables merging this with frame_getnode and replacing the legacy interface
      • essential for mixed embedded app development
    • convert JSON null and undefined values to SQL NULL
    • refactor code and remove dead code. Add more documentation.
    • support sql.select
    • support dynamic query generation for sql.getInsert.
    • add index.js and expose basePath
      • cleaner syntax for referencing in chains by require
    • add support for live JSON object config
      • enables direct launch from Proxy Projects
    • improve formatting for output
    • add query command
    • changed lookupConfigFromFile to loadConfigJSONFromID

    Install

    npm i izyware-sqlconsole-rekey

    DownloadsWeekly Downloads

    4

    Version

    1.0.8

    License

    MIT

    Unpacked Size

    30.6 kB

    Total Files

    14

    Last publish

    Collaborators

    • alex.wong