Oomysql NPM | npm.io

OoMySQL

Presentation

I'm not a pro developper, and my english is not very good (but I didn't use google translate :D). In a second time, it's my first ever package in node.js, and he is not finished (version 0.0.4). There are missing functionnalities, but the code is working (uh ... I think ?) but not cleaned, etc ...

Description and key-concepts

OoMySQL (Object Oriented MySQL) (I hadn't found another name :/) is a module that allows you to query a database (like all other modules in fact ... but I think OoMySQL is maybe a little bit simpler, or juste different). One of the most importants concepts of OoMySQL (and the only one in facts) is the concept of the LinkedRow object. A LinkedRow represent a Row of a table in a single moment. You can imagine a LinkedRow like this :

{
    ID: 1,
    field1: "hi",
    anotherField: "how are you ?",
    field42: 42
}

Okay, pretty simple. But, why "Linked" ? Oh, sorry I forgotted one thing : a LinkedRow is Linked with the table. So, modifying any values in a LinkedRow modify it in the database to. Let's see an exemple with the row above :

console.log(lrow.field42) // 42
lrow.field42++; // 43 in the database to !

And, that's it. You've just updated in your table where ID = 1, field1 = "hi", anotherField = "how are you" and field42 = 42.

Getting started

Imagine we have a database called "test" with an empty table called "players". Each players have an ID (auto increment, of course), a name, a level (default value = 0) and money (default value = 1500).

Below is an exemple code of how you connect to a database, add some players, delete one or two and update some datas :

const { Database } = require('oomysql'); // require Database
const db = new Database({
    cacheTables: true, // just for keep clear something, "cacheTables" doesn't mean of course that ALL the content of ALL the tables will be fetched and putted into some variables, arrays or something.
    pingTime: 3600000
});

db.connect({
    host: "<host>",
    user: "<user>",
    password: "<password>",
    database: "test"
});

db.on('ready', async () => {
    const playersTable = db.tables.players; // or 'db.tables["players"]' if you want.

    playersTable.insert({
        name: "WhiteNoise09",
        level: 42
    }); // inserted one row : Whitenoise09, level 42, ID = 1 and money = 1500.

    playersTable.insert([
        {name: "Piper", money: 1000},
        {name: "Pineapple", level: 2000},
        {money: 2000, level: 7, name: "Wasabi"}
        {name: "I will be deleted", money: 0}
    ]); // inserted multiple rows (4)

    playersTable.deleteOne({
        money: {comparator: "<", value: "1000"} // where money < 0.
    }); // "I will be deleted" was delete :/

    const wasabiDatas = await playersTable.fetchOne({name: "Wasabi"}); // fetched Wasabi.
    // let's give 5 levels to Wasabi :
    wasabiDatas.level+=5  // done !

    const pineappleDatas = await playersTable.fetchOne({name: "Pineapple"}); // fetched Pineapple.
    // let's give a lot of money to Pineapple :
    pineappleDatas.money = 100000000000; // done !

    console.log(await playersTable.all()); /*
                                                [
                                                    {ID: 1, name: "WhiteNoise09", level: 42, money: 1500},
                                                    {ID: 2, name: "Piper", level: 0, money: 1000},
                                                    {ID: 3, name: "Pineapple", level: 2000, money: 100000000000},
                                                    {ID: 4, name: "Wasabi", level: 12, money: 2000}
                                                ]
                                            */
});

Voilà !

Documentation

Here is the documentation of the module.

Database

Represent a database. The starting point of this module.

Constructor

new OoMySQL.Database(databaseOptions)

databaseOptions : the options for the database
databaseOptions.cacheTables : optionnal. Determine if the TableManager of the Database must be a TableManager or an AsyncTableManager (default: false)
databaseOptions.pingTime : optionnal. Used to keep the connection alive. Every pingTime ms, a query will be send to the database. If pingTime = 0, there will be no ping. (default: 0)

Properties

options (read only) : the options gived to the constructor.
name (read only) : the name of the database.
connection (read only) : the Connection object (default mysql module connection).
tables (read only) : the TableManager or AsyncTableManager of this Database.

Methods

connect (async)

database.connect(connectionOptions)

Connect to the database.

connectionOptions : like the default mysql module "Connection.connect(options)" options, but host, user, password and database are required.

destroy

database.destroy()

Destroy the connection, clear the keepAlive interval if there is one and emit the destroy event.

on

database.on(eventName, callback)

listen to an event.

eventName : the name of the event?
callback : the callback that will be attached to the event.

query

database.query(query)

Send a query to the database. (in fact it's just a promise wrapper for mysql.Connection.query())

query : the query that will be sended to the database.
return value : the result of the query.

Events

connected : the connection has been created and connected. If cacheTables = true, it doesn't mean that the TableManager is readu to use yet.
ready : the (Async)TableManager is ready to use.
destroy : the connection was destroyed by Database.destroy();
error : there was an error (I don't know if it's working totally, maybe not :/)

TableManager

An object that has one property for each tables that were in the database when the connection started. The property is a Table object.

properties

whateveryouwant (but needs to be the name of a table in the database) : a Table object. exemple :

// if the database has 2 tables called "players" and "$pecial Chars here"
const playersTable = TableManager.players // the "players" table.
const specialTable = TableManager["$pecial Chars here"] // the "$pecial Chars here" table.

AsyncTableManager

An proxy that has one property "emulated" for each tables that is actually in the database. The property is a Table object.

properties

whateveryouwant (but needs to be the name of a table in the database) : a Promise that will resolve with a Table object, or reject if there is no Table objects named like this. exemple :

// if the database has 2 tables called "players" and "$pecial Chars here"
const playersTable = await TableManager.players // the "players" table.
const specialTable = await TableManager["$pecial Chars here"] // the "$pecial Chars here" table.
// note : in the case that it returns promise, use await.

Table

Represent a Table.

properties

name (read only) : the name of the table.

Methods

insert

table.insert(row_or_array_of_rows);

Insert one or multiple rows. The row is an object, with a property for each fields that doesn't have a default value, so if there is a default value, a field is optionnal.

row_or_array_of_rows : a ... row or an array of rows --' exemple :

    playersTable.insert({
        name: "WhiteNoise09",
        level: 42
    }); // inserted one row : Whitenoise09, level 42, ID = 1 and money = 1500.

    playersTable.insert([
        {name: "Piper", money: 1000},
        {name: "Pineapple", level: 2000},
        {money: 2000, level: 7, name: "Wasabi"},
        {name: "I will be deleted", money: 0}
    ]); // inserted multiple rows (4)

fetch (async)

table.fetch(row, limit);

Fetch the rows that match with the row passed in parameter, and returns it into a Promise resolved with an array or undefined.

row : a ... row (--') that will be used to compare.
limit (optional) : the number of rows to fetch. exemple :

    playersTable.fetch({
        level: 42
    }); // fetch all rows where level = 42

fetchOne (async)

table.fetch(row);

Fetch one row that match with the row passed in parameter, and returns a Promise resolved with it or undefined.

row : a ... row (--') that will be used to compare. exemple :

    playersTable.fetchOne({
        name: "WhiteNoise09",
        level: 42
    }); // fetch one row where name = WhiteNoise09 and level = 42

filter (async)

table.fetch(comparatorRow, limit);

Fetch rows that match with the row passed in parameter, and returns a Promise resolved with an array of rows or undefined.

comparatorRow : a comparatorRow (--') that will be used to compare.
limit : the number of rows to fetch. exemple :

    playersTable.filter({
        level: {operator: "<", value: 10}
    }); // fetch all rows where level < 10

all (async)

table.all();

Return a Promise with ALL the rows in an array. exemple :

    playersTable.all() // return all the players in playersTable.

delete (async)

table.delete(comparatorRow, limit);

Delete the rows that match with the comparator row passed in parameter, and return a Promise that will resolve with true if success, or reject with an error if there is one.

comparatorRow : a ... comparatorRow (--') that will be used to compare.
limit (optional) : the number of rows to delete. exemple :

    playersTable.delete({
        level: {comparator: "<", value: 42}
    }); // delete all rows where level < 42.

deleteOne (async)

table.delete(comparatorRow, limit);

Delete one row that match with the comparator row passed in parameter, and return a Promise that will resolve with true if success, or reject with an error if there is one.

comparatorRow : a ... comparatorRow (--') that will be used to compare. exemple :

    playersTable.deleteOne({
        name: {value: "WhiteNoise09"},
        level: {comparator: "<", value: 42}
    }); // delete one row where name = "WhiteNoise09" and level < 42.

Concepts

LinkedRow

A LinkedRow represent a Row of a table in a single moment. A LinkedRow is Linked to the database, so modifying it will modify the database to. exemple:

const linkedRow = playersTable.fetchOne({
    name: "WhiteNoise09"
    level: 42
});

linkedRow.level++; // the level was incremented in linkedRow AND in the database too !

ComparatorRow

A ComparatorRow is used to search rows in the database. Here how a comparator row is builded :

{
    oneFieldName: {comparator: "<", value: 42},
    anotherField: {value: "hey"}
}

the property names of the comparator row object are the name of a field you want to include to your search conditions.
the value of the properties is an object to
- comparator : "<" (strictly lower), ">" (strictly higher) or "=" (equals). If not defined, the "=" operator will be used.
- value : the value that will be used to compare.

Finally, you can understand the code below ...

    playersTable.deleteOne({
        name: {value: "WhiteNoise09"},
        level: {comparator: "<", value: 42}
    });

... like this :

delete one row from the table "players" where the name is exactly "WhiteNoise09" and the level is strictly lower than 42.

Future addings or ideas

auto parsing of json fields types to javascript objects (wip, soon)
maybe a concept of RowsManager with a "keyfield" ? (Table.rows"WhiteNoise09" returns the lrow where name = "WhiteNoise09" if the keyfield is setted to "name" for example)
Possibility to connect to a single table, and to connect to the entire databases and have a DatabaseHandler (like the (Async)TableHandler but for databases)
a lot of other methods, like Database.addTable/removeTable
possibility to delete with a regular row rather than a comparator row

final words

I remind you that this project is in bêta, maybe a little bit buggy (I think not but please tell me if it is), the code is not cleaned yet, my english is not very well (so excuuuuuuuuuuuuuuuuuuuuuuuuuuuuse me, princess. (did somebody has the ref ?)) And, finally, really finally, please contact me for ideas, improvements, or just to tell me your opinion. It's my first ever node.js module, and I hope that you enjoyed use it. So ... bye !

mysql

@infinitebrahmanuniverse/nolb-oo @everything-registry/sub-chunk-2379

3 years ago

3 years ago

3 years ago

3 years ago

oomysql v0.0.4

OoMySQL

Presentation

Description and key-concepts

Getting started

Documentation

Menu

Database

Constructor

Properties

Methods

connect (async)

destroy

on

query

Events

TableManager

properties

AsyncTableManager

properties

Table

properties

Methods

insert

fetch (async)

fetchOne (async)

filter (async)

all (async)

delete (async)

deleteOne (async)

Concepts

LinkedRow

ComparatorRow

Future addings or ideas

final words