mysql-dynamo

A solution to use the simple-dynamo interface with a MySQL database. So you can create a offline version of a AWS-DynamoDB project.

mysql-dynamo

Build Status

A solution to use the simple-dynamo interface with a MySQL database. So you can create a offline version of a AWS-DynamoDB project.

Written in coffee-script

INFO: all examples are written in coffee-script

Install

  npm install mysql-dynamo

API differences to simple-dynamo

Compared to the simple-dynamo module you only have to make a few smears.

One big difference between the Dynamo and MySQL version is that dynamo can generate undefined attributes on the fly. Th MySQL version will ignore undefined attributes. So all necessary attributes has to be defined within the table definitions

Connection and Table

Initialize

first you have to define the connection and table attributes and get an instance of the simple-dynamo interface.

new MySqlDynamo( connectionSettings, tables )

connection Settings


MySQL connection settings

table Definition

An Object of Tables.
The key you are using will be the key to get the table object.

Every object can have the following keys:

# import module
MySQLDynamo = require "mysql-dynamo"

# define connection settings
connectionSettings =
    host: "localhost"
    user: "root"
    password: "root"
    database: "myDB"

# define tables
tables = 
    "Users":
        name: "users"
        hashKey: "id"

        attributes: [
            { key: "name", type: "string", required: true }
            { key: "email", type: "string" }
        ]

    "Todos":
        name: "todos"
        hashKey: "id"
        rangeKey: "_t"
        rangeKeyType: "N"

        fnCreateHash: ( attributes, cb )=>
            cb( attributes.user_id )
            return

        attributes: [
            { key: "title", type: "string", required: true }
            { key: "done", type: "number" }
        ]

# create instance
sqldManager = new MySQLDynamo( connectionSettings, tables )

# connect
sqldManager.connect ( err )->
    console.log( "mysql-dynamo ready to use" )

First connect to MySQL

The module has to know about the existing SQL tables so you have to read them first.
If you do not run .connect() the module will throw an error everytime

Manager.connect( fnCallback ) Arguments :

Example

sqldManager.connect ( err )->
    if err
        console.error( "connect ERROR", err )
    else
        console.log( "mysql-dynamo ready to use" )

Create all tables

to create all missing tables just call .createAll().

This is not necessary if you know the tables has been created in the past.

Manager.generateAll( fnCallback ) Arguments :

Example

sqldManager.generateAll ( err )->
    if err
        console.error( "connect ERROR", err )
    else
        console.log( "mysql-dynamo ready to use" )

Get a table instance

To interact with a table you have to retrieve the table object. It's defined in the table-definitions

Manager.get( 'tableName' ) Arguments :

Example

tblTodos = sqldManager.get( 'Todos' )

Loop through all tables

Loop trough all table objects

Manager.each( eachFn ) Arguments :

Example

Manager.each ( tableKey, tableObj )=>
    console.log( "SQL table name", tableObj.tableName )
    return

Destroy a table

destroy table in MySQL. This drops a table from MySQL will all the data

Table.destroy( fnCallback ) Arguments :

Example

tblTodos.del ( err )->
    if err
        console.error( "destroy ERROR", err )
    else
        console.log( "table destroyed" )

Item handling

Write a new item (INSERT)

Create a new item in a select table. You can also add some attributes not defined in the table-definition, which will be saved, too.

Table.set( data, options, fnCallback ) Arguments :

Example

data = 
    title: "My First Todo"
    done: 0
    aditionalData: "Foo bar"

tblTodos.set data, ( err, todo )->
    if err
        console.error( "insert ERROR", err )
    else
        console.log( todo )

Get a item (GET)

Get an existing element by id/hash

Table.get( id[, options], fnCallback ) Arguments :

Example

tblTodos.get 'myTodoId', ( err, todo )->
    if err
        console.error( "get ERROR", err )
    else
        console.log( todo )
tblRangeTodos.get [ 'myHash', 'myRange' ], ( err, todo )->
    if err
        console.error( "get ERROR", err )
    else
        console.log( todo )

Get many items (MGET)

Get an many existing elements by id/hash in one request

Table.mget( [ id1, id2, .. ], options, fnCallback ) Arguments :

Example

tblTodos.mget [ 'myTodoIdA', 'myTodoIdB' ], ( err, todos )->
    if err
        console.error( "get ERROR", err )
    else
        console.log( todos )
tblRangeTodos.mget [ [ 'myHash', 1 ], [ 'myHash', 2 ] ], ( err, todos )->
    if err
        console.error( "get ERROR", err )
    else
        console.log( todos )

Update an item (UPDATE)

update an existing item.
To remove a attribute you have to set the value to null

Table.set( id, data, options, fnCallback ) Arguments :

Example

data = 
    title: "My First Update"
    done: 1

tblTodos.set 'myTodoId', data, ( err, todo )->
    if err
        console.error( "update ERROR", err )
    else
        # note. the key 'aditionalData' will be gone
        console.log( todo )

Delete an item (DELETE)

delete an item by id/hash

Table.del( id, fnCallback ) Arguments :

Example

tblTodos.del 'myTodoId', ( err )->
    if err
        console.error( "delete ERROR", err )
    else
        console.log( "delete done" )

Query a table (FIND)

run a query on a table. The module automatically trys to do a Dynamo.db scan or Dynamo query.

Table.find( query, startAt, options, fnCallback ) Arguments :

Example

tblTodos.find {}, ( err, items )->
    if err
        console.error( "delete ERROR", err )
    else
        console.log( "all existend items", items )

Advanced Examples

# create a query to read all todos from last hour
_query = 
    id: { "!=": null }
    _t: { "<": ( Date.now() - ( 1000 * 60 * 60 ) ) }

tblTodos.find , ( err, items )->
    if err
        console.error( "delete ERROR", err )
    else
        console.log( "found items", items )
# read 4 todos from last hour beginning starting with a known id
_query = 
    id: { "!=": null }
    _t: { "<": ( Date.now() - ( 1000 * 60 * 60 ) ) }

_startAt = "myid_todoItem12"

_options = { "limit": 4, "fields": [ "id", "_t", "title" ] }

tblTodos.find _query, _startAt, _options, ( err, items )->
    if err
        console.error( "delete ERROR", err )
    else
        console.log( "4 found items", items )

Working with sets

Dynamo has the ability to work with sets. That means you can save a Set of Strings as an Array.
During an update you have the ability to add or remove a single value out of the set. Or you can reset the whole set.

But you can only perform one action per key and you only can use the functionality if defined through the table-definition ( type:"array" ).

Existing values will be ignored.

The following key variants are available:

Examples

# Source "key: [ "a", "b", "c" ]"

data = 
    key: [ "x", "y", "z" ]

tblSets.set 'mySetsId', data, ( err, setData )->
    # Result "key: [ "x", "y", "z" ]"
    console.log( setData )
# Source "key: [ "a", "b", "c" ]"

data = 
    key: { "$add": [ "a", "d", "e" ] }

tblSets.set 'mySetsId', data, ( err, setData )->
    # Result "key: [ "a", "b", "c", "d", "e" ]"
    console.log( setData )
# Source "key: [ "a", "b", "c" ]"

data = 
    key: { "$rem": [ "a", "b", "x" ] }

tblSets.set 'mySetsId', data, ( err, setData )->
    # Result "key: [ "c" ]"
    console.log( setData )
# Source "key: [ "a", "b", "c" ]"

data = 
    key: { "$reset": [ "x", "y", "z" ] }

tblSets.set 'mySetsId', data, ( err, setData )->
    # Result "key: [ "x", "y", "z" ]"
    console.log( setData )

Events

To provide a API to react on different events you can listen to a bunch of events.

Manager Events

Table Events

Changelogs

0.2.2 7. June 2013

0.2.1 7. June 2013

0.2.0 7. June 2013

0.1.4 6. June 2013

0.1.3 6. June 2013

0.1.2 6. June 2013

0.1.1 6. June 2013

0.1.0 5. June 2013

Todos

Work in progress

mysql-dynamo is work in progress. Your ideas, suggestions etc. are very welcome.

License

(The MIT License)

Copyright (c) 2010 TCS <dev (at) tcs.de>

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the 'Software'), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED 'AS IS', WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.