Split databases by pairs

[PhunkyBob]

• I'm submitting a ... [ ] bug report [x] feature request [ ] question about the decisions made in the repository

• Action taken (what you did) Import 5 different pairs in the same market.

• Expected result (what you hoped would happen) Have 5 databases "marketname_USD_ETH.db", "marketname_USD_BTC.db", "marketname_USD_XRP.db", ...

• Actual result (unexpected outcome) Have 1 database for whole market "marketname.db".

• Other information (e.g. detailed explanation, stacktraces, related issues, suggestions how to fix, links for us to have context, eg. stackoverflow, etc)

It's would be easier to manage databases if they were stored in "1 file by market by pair" instead of "1 file by market".

[askmike]

great idea!

[askmike]

Though keep in mind this is pretty hard to do:

updating the code is only half the work, the other half is providing people ways to convert the old style database into the new one (via migrations and documentation or so).

[PhunkyBob]

I understand.

[tommiehansen]

I thought about this too. Would be much simpler to handle the data this way (e.g. delete/update/merge/whatever).

[PhunkyBob]

On my side, in the file plugins/sqlite/handle.js

I replaced var dbName = config.watch.exchange.toLowerCase() + '_' + version + '.db';

by var dbName = config.watch.exchange.toLowerCase() + '_' + config.watch.currency.toLowerCase() + '_' + config.watch.asset.toLowerCase() + '_' + version + '.db';

To convert the history, I copied / pasted my "exchange_0.1.db" into as many files as I have pairs inside, and kept only 1 table / file. kraken_0.1.db → kraken_eur_eth_0.1.db, kraken_eur_xbt_0.1.db, kraken_usd_ltc_0.1.db, ... To reduce the file, I ran the SQL command "VACUUM;".

[GO1984]

@Phunky Bob: I like your way very much.

[PhunkyBob]

Okay, I've found a simple way to be compatible with previous config:

In the file plugins/sqlite/handle.js

Replace

var dbName = config.watch.exchange.toLowerCase() + '_' + version + '.db';
var dir = dirs.gekko + adapter.dataDirectory;

var fullPath = [dir, dbName].join('/');

with

var dbName = config.watch.exchange.toLowerCase() + '_' + version + '.db';
var dir = dirs.gekko + adapter.dataDirectory;

var fullPath = [dir, dbName].join('/');

if (!fs.existsSync(fullPath)) {
  var dbName = config.watch.exchange.toLowerCase() + '_' + config.watch.currency.toLowerCase() + '_' + config.watch.asset.toLowerCase() + '_' + version + '.db';
  var dir = dirs.gekko + adapter.dataDirectory;

  var fullPath = [dir, dbName].join('/');
}

When you retrieve data from an exchange you already have data, they will be placed in a single file (like the default behavior). But when you retrieve data from a new exchange, they will be placed in 1 file / pair.

I don't know how to submit this evolution in the main repo...

[PhunkyBob]

I've made a script to convert the databases. Sorry, I don't know how to create a pull request (@askmike, please help me if you are interested).

In the attached folder gekko.zip:

• node_modules/aa-sqlite: a synchronous lib to make SQLite queries (source: https://www.scriptol.fr/sql/sqlite-async-await.php).
• plugins/sqlite/handle.js: the modified handle that knows how to use "full" databases and "splitted" databases (but you cannot have the 2 systems at once for the same exchange).
• plugins/sqlite/db_split.js: the script that convert a "full" database to 1 database / pair.

Usage:

cd plugins/sqlite
node db_split.js [exchange]

Exemple:

node db_split.js binance

will rename "binance_0.1.db" into "binance_0.1.db_splitted" (will not be used by the core anymore) and will create:

• binance_usdt_ada_0.1.db
• binance_usdt_bcc_0.1.db
• binance_usdt_bnb_0.1.db
• binance_usdt_btc_0.1.db
• binance_usdt_eos_0.1.db
• binance_usdt_etc_0.1.db
• binance_usdt_eth_0.1.db
• binance_usdt_icx_0.1.db
• binance_usdt_iota_0.1.db
• binance_usdt_ltc_0.1.db
• binance_usdt_neo_0.1.db
• binance_usdt_nuls_0.1.db
• binance_usdt_ont_0.1.db
• binance_usdt_qtum_0.1.db
• binance_usdt_trx_0.1.db
• binance_usdt_tusd_0.1.db
• binance_usdt_ven_0.1.db
• binance_usdt_vet_0.1.db
• binance_usdt_xlm_0.1.db
• binance_usdt_xrp_0.1.db

db_split.js: [edit] new version in next post [/edit]

[PhunkyBob]

New version, faster:

/*
db_split.js (version 2.01)

This script splits a Gekko database containing several pairs into several databases containing 1 pair.

Usage:
- Copy this file into "plugins/sqlite/".
- Run
node db_split.js [excahnge_name]

*/

// https://www.scriptol.fr/sql/sqlite-async-await.php
var sqlite = require('aa-sqlite');

const fs = require('fs');

// Main.

var exchange = process.argv[2];
if (!exchange)
{
    exchange = "";
}

var fullPath = "../../history/" + exchange + "_0.1.db";

if (exchange.length > 0 && fs.existsSync(fullPath))
{
    console.log("Splitting database \"" + fullPath + "\"...");
    split_tables();
} else {
    console.log("Database \"" + fullPath + "\" not found.");
    console.log("Usage:");
    console.log("node db_split.js [exchange]");
}

async function split_tables()
{

    await sqlite.open(fullPath);

    sql = "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'candles_%'";
    r = await sqlite.all(sql, []);
    await sqlite.close();

    var arrPairs = [];
    r.forEach(function(row) {
        arrPairs.push(row.name);

        //console.log(row.name);
    });

    for (i = 0; i < arrPairs.length; i++)
    {
        console.log("Current pair: " + arrPairs[i] + " (" + (i+1) + "/" + arrPairs.length + ")");
        var pair = arrPairs[i].replace("candles_", "");
        var newPath = "../../history/" + exchange + "_" + pair.toLowerCase() + "_0.1.db";

        // Create new database.
        sql = "SELECT sql FROM sqlite_master WHERE name='" + arrPairs[i] + "'";
        r = await sqlite.get(sql);
        create_statement = r['sql'];

        console.log("Copying table \"" + arrPairs[i] + "\" from database \"" + fullPath + "\" to \"" + newPath + "\"...");
        sql = "ATTACH '" + newPath  + "' AS new";
        r = await sqlite.run(sql);

        sql = "DROP TABLE IF EXISTS new." + arrPairs[i];
        r = await sqlite.run(sql);

        sql = create_statement.replace(arrPairs[i], "new." + arrPairs[i]);
        r = await sqlite.run(sql);

        sql = "INSERT INTO new." + arrPairs[i] + " SELECT * FROM " + arrPairs[i] + "";
        r = await sqlite.run(sql);

        sql = "VACUUM new";
        r = await sqlite.run(sql);

        sql = "DETACH new";
        r = await sqlite.run(sql);

    }

    console.log("Rename database \"" + fullPath + "\" to \"" + fullPath + "_splitted\"...");
    fs.renameSync(fullPath, fullPath + "_splitted");

    console.log("Done!");
}

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. If you feel this is very a important issue please reach out the maintainer of this project directly via e-mail: gekko at mvr dot me.