@linked-db/linked-ql - npm Package Compare versions

Comparing version 0.5.2 to 0.5.3

package.json

@@ -14,3 +14,3 @@ {
   "icon": "https://webqit.io/icon.svg",
-  "version": "0.5.2",
+  "version": "0.5.3",
   "license": "MIT",
@@ -17,0 +17,0 @@ "repository": {

src/api/abstracts/AbstractClient.js

@@ -219,2 +219,26 @@
     /**
+	 * Returns all databases' current savepoint.
+	 * 
+     * @param Object params
+	 * 
+	 * @returns Object
+     */
+    async getSavepoints(params = {}) {
+        const OBJ_INFOSCHEMA_DB = this.constructor.OBJ_INFOSCHEMA_DB;
+        if (!(await this.hasDatabase(OBJ_INFOSCHEMA_DB))) return [];
+        const tblName = [OBJ_INFOSCHEMA_DB,'database_savepoints'].join('.');
+        const result = await this.query(`
+            SELECT id, database_tag, name, "$name", status, version_tag, version_max, rank_for_cursor || '/' || total AS cursor, savepoint_description, tables, savepoint_date, rollback_date FROM (
+                SELECT
+                ROW_NUMBER() OVER (PARTITION BY database_tag ORDER BY rollback_date IS NOT NULL ${ params.direction === 'forward' ? 'DESC' : 'ASC' }, version_tag ${ params.direction === 'forward' ? 'ASC' : 'DESC' }) AS rank_for_target,
+                ROW_NUMBER() OVER (PARTITION BY database_tag ORDER BY version_tag ASC) AS rank_for_cursor,
+                MAX(version_tag) OVER (PARTITION BY database_tag) AS version_max,
+                COUNT(version_tag) OVER (PARTITION BY database_tag) AS total,
+                * FROM ${ tblName }${ params.name ? (params.direction === 'forward' ? `WHERE name = '${ params.name }'` : `WHERE COALESCE("$name", name) = '${ params.name }'`) : '' }
+            ) AS savepoint WHERE rollback_date IS ${ params.direction === 'forward' ? 'NOT NULL' : 'NULL' } AND rank_for_target = 1
+        `);
+        return result.map(savepoint => new Savepoint(this, savepoint, params.direction))
+    }
+
+    /**
      * Method for saving snapshots to internal OBJ_INFOSCHEMA db.
@@ -266,3 +290,3 @@ *
             savepointJson.database_tag = currentSavepoint.databaseTag;
-            savepointJson.version_tag = (await this.query(`SELECT max(version_tag) + 1 AS next_tag FROM ${ tblName } WHERE database_tag = '${ currentSavepoint.databaseTag }'`))[0].next_tag;
+            savepointJson.version_tag = (await this.query(`SELECT max(version_tag) + 1 AS version_next FROM ${ tblName } WHERE database_tag = '${ currentSavepoint.databaseTag }'`))[0].version_next;
             // -- Delete forward records
@@ -279,26 +303,4 @@ if (savepointJson.version_tag - 1 !== currentSavepoint.versionTag) {
         const insertResult = await this.database(OBJ_INFOSCHEMA_DB).table('database_savepoints').insert(savepointJson);
-        return new Savepoint(this, { ...insertResult[0], id_following: null });
+        return new Savepoint(this, { ...insertResult[0], version_max: insertResult[0].version_tag, cursor: null });
     }
-
-    /**
-	 * Returns all databases' current savepoint.
-	 * 
-     * @param Object params
-	 * 
-	 * @returns Object
-     */
-    async getSavepoints(params = {}) {
-        const OBJ_INFOSCHEMA_DB = this.constructor.OBJ_INFOSCHEMA_DB;
-        if (!(await this.hasDatabase(OBJ_INFOSCHEMA_DB))) return [];
-        const tblName = [OBJ_INFOSCHEMA_DB,'database_savepoints'].join('.');
-        return await this.query(`
-            SELECT name, "$name", rn || '/' || total AS pos, version_tag, version_max, savepoint_description, savepoint_date, rollback_date FROM (
-                SELECT ROW_NUMBER() OVER (PARTITION BY database_tag ORDER BY version_tag ASC) AS rn,
-                ROW_NUMBER() OVER (PARTITION BY database_tag ORDER BY rollback_date IS NOT NULL ${ params.direction === 'forward' ? 'DESC' : 'ASC' }, version_tag ${ params.direction === 'forward' ? 'ASC' : 'DESC' }) AS k,
-                COUNT(version_tag) OVER (PARTITION BY database_tag) AS total,
-                MAX(version_tag) OVER (PARTITION BY database_tag) AS version_max,
-                * FROM ${ tblName }
-            ) AS savepoint WHERE k = 1
-        `);
-    }
 }

src/api/abstracts/AbstractDatabase.js

@@ -137,2 +137,6 @@
     async savepoint(params = {}) {
+        const savepoints = await this.client.getSavepoints({ ...params, name: this.name });
+        return savepoints[0];
+    }
+    async _savepoint(params = {}) {
         const OBJ_INFOSCHEMA_DB = this.client.constructor.OBJ_INFOSCHEMA_DB;
@@ -139,0 +143,0 @@ if (!(await this.client.hasDatabase(OBJ_INFOSCHEMA_DB))) return;

src/api/abstracts/Savepoint.js

@@ -35,8 +35,8 @@
      */
-    get description() { return this.$.json.savepoint_description; }
+    get databaseTag() { return this.$.json.database_tag; }
 
     /**
-     * @returns String
+     * @returns Number
      */
-    get databaseTag() { return this.$.json.database_tag; }
+    get versionTag() { return this.$.json.version_tag; }
 
@@ -46,5 +46,15 @@ /**
      */
-    get versionTag() { return this.$.json.version_tag; }
+    get versionMax() { return this.$.json.version_max; }
 
     /**
+     * @returns Number
+     */
+    get cursor() { return this.$.json.cursor; }
+
+    /**
+     * @returns String
+     */
+    get description() { return this.$.json.savepoint_description; }
+
+    /**
      * @returns Date
@@ -60,2 +70,18 @@ */
     /**
+     * @returns String
+     */
+    get rollbackOutcome() {
+        const $outcome = !this.$.json.status ? ['DROPPED','CREATED'] : (this.$.json.status === 'DOWN' ? ['CREATED','DROPPED'] : ['ALTERED']);
+        return this.direction === 'forward' ? $outcome.reverse()[0] : $outcome[0];
+    }
+
+    /**
+     * @returns String
+     */
+    name(postRollback = false) {
+        if (postRollback) return this.direction === 'forward' && this.$.json.$name || this.$.json.name;
+        return this.direction !== 'forward' && this.$.json.$name || this.$.json.name;
+    }
+
+    /**
      * @returns Object
@@ -71,3 +97,6 @@ */
      */
-    toJson() { return { ...this.$.json }; }
+    toJson() {
+        const { id, database_tag, version_tag, version_max, cursor, savepoint_description: description, savepoint_date, rollback_date } = this.$.json;
+        return { id, name: this.name(), database_tag, version_tag, version_max, cursor, description, savepoint_date, rollback_date };
+    }
 
@@ -78,3 +107,3 @@ /**
     async canRollback() {
-        const dbName = this.$.json.rollback_date ? this.$.json.name : this.$.json.$name || this.$.json.name;
+        const dbName = this.direction === 'forward' ? this.$.json.name : this.$.json.$name || this.$.json.name;
         const currentSavepoint = (await this.client.database(dbName).savepoint({ direction: this.direction })) || {};
@@ -92,3 +121,3 @@ return currentSavepoint.id === this.$.json.id;
         const schemaInstance = CreateDatabase.fromJson(this.client, this.schema());
-        if (!this.$.json.rollback_date) {
+        if (this.direction !== 'forward') {
             schemaInstance.reverseAlt(true);
@@ -106,6 +135,6 @@ schemaInstance.status(schemaInstance.status(), true);
         const tblName = [this.client.constructor.OBJ_INFOSCHEMA_DB,'database_savepoints'].join('.');
-        await this.client.query(`UPDATE ${ tblName } SET rollback_date = ${ this.$.json.rollback_date ? 'NULL' : 'now()' } WHERE id = '${ this.$.json.id }'`);
-        this.$.json.rollback_date = this.$.json.rollback_date ? null : Date.now();
+        await this.client.query(`UPDATE ${ tblName } SET rollback_date = ${ this.direction === 'forward' ? 'NULL' : 'now()' } WHERE id = '${ this.$.json.id }'`);
+        this.$.json.rollback_date = this.direction === 'forward' ? null : Date.now();
         return true;
     }
 }

src/migration/index.js

@@ -14,4 +14,5 @@ #!/usr/bin/env node
 // flags: --preview, --desc, --force --db, --schema, --driver, --force-new
+if (flags.direction && !['forward','backward'].includes(flags.direction)) throw new Error(`Invalid --direction. Expected: forward|backward`);
 
-// -------------
+// ------
 // Load schema file
@@ -24,3 +25,3 @@ let schema, schemaFile = path.resolve(flags['schema'] || './database/schema.json');
 
-// -------------
+// ------
 // Load driver
@@ -33,32 +34,18 @@ let driver, driverFile = path.resolve(flags['driver'] || './database/driver.js');
 
-// -------------
+// ------
 // Show?
-if (command === 'status') {
-    console.table(await driver.getSavepoints(), ['name', '$name', 'pos', 'version_tag', 'version_max', 'savepoint_description', 'savepoint_date', 'rollback_date']);
+if (command === 'savepoints') {
+    //TEMP:console.log('DATABASES:', await driver.databases());
+    //TEMP:console.log('SAVEPOINTS:', await driver.database(SQLClient.OBJ_INFOSCHEMA_DB).table('database_savepoints').select());
+    const savepointSummaries = await driver.getSavepoints({ direction: flags.direction });
+    console.table(savepointSummaries.map(sv => sv.toJson()), ['name', 'database_tag', 'version_tag', 'version_max', 'cursor', 'description', 'savepoint_date', 'rollback_date']);
     process.exit();
 }
 
-// -------------
-// Reset?
-if (command === 'reset') {
-    console.log(`\nThis will permanently delete all savepoint records$.`);
-    if (flags.db) console.log(`\nThis will also drop the database: ${ flags.db }.`); // For testing purposes only
-    const proceed = flags.force || (await enquirer.prompt({
-        type: 'confirm',
-        name: 'proceed',
-        message: 'Proceed?'
-    })).proceed;
-    if (proceed) {
-        if (flags.db) await driver.query(`DROP DATABASE IF EXISTS ${ flags.db } CASCADE`, { noCreateSavepoint: true });
-        await driver.query(`DROP DATABASE IF EXISTS obj_information_schema CASCADE`, { noCreateSavepoint: true });
-    }
-    process.exit();
-}
-
-// -------------
+// ------
 // Schemas before and after
-const dbSchemas = [].concat(schema), newDbSchemas = [];
+const dbSchemas = [].concat(schema), resultDbSchemas = [];
 
-// -------------
-// Run migrations
+// ------
+// Run migrations or rollbacks
 if (command === 'migrate') {
@@ -68,10 +55,11 @@ for (const dbSchema of dbSchemas) {
         if (flags.db && flags.db !== dbSchema.name) {
-            newDbSchemas.push(dbSchema);
+            resultDbSchemas.push(dbSchema);
             continue;
         }
-        const scope = { dbName: dbSchema.name, returnValue: null };
+        const postMigration = { name: dbSchema.name, outcome: null, returnValue: undefined };
         const dbInstance = CreateDatabase.fromJson(driver, dbSchema);
 
         if (dbInstance.status() === 'DOWN' && !flags['force-new']) {
-            console.log(`\nDropping database: ${ scope.dbName }`);
+            console.log(`\nDropping database: ${ dbSchema.name }`);
+            if (flags.preview !== false) console.log(`\nSQL preview:\nDROP SCHEMA ${ dbSchema.name } CASCADE\n`);
             const proceed = flags.force || (await enquirer.prompt({
@@ -83,4 +71,4 @@ type: 'confirm',
             if (proceed) {
-                scope.returnValue = await driver.dropDatabase(scope.dbName, { savepointDesc: flags.desc });
-                scope.isDrop = true;
+                postMigration.returnValue = await driver.dropDatabase(dbSchema.name, { cascade: true, savepointDesc: flags.desc });
+                postMigration.outcome = 'DROPPED';
             }
@@ -92,4 +80,4 @@ }
             if (alt.ACTIONS.length) {
-                console.log(`\nAltering database: ${ scope.dbName }`);
-                if (flags.preview !== false) console.log(`\nThe following SQL will now be run:\n${ alt }\n`);
+                console.log(`\nAltering database: ${ dbSchema.name }`);
+                if (flags.preview !== false) console.log(`\nSQL preview:\n${ alt }\n`);
                 const proceed = flags.force || (await enquirer.prompt({
@@ -101,6 +89,7 @@ type: 'confirm',
                 if (proceed) {
-                    scope.returnValue = await driver.query(alt, { savepointDesc: flags.desc });
-                    scope.postName = dbSchema.$name || dbSchema.name;
+                    postMigration.returnValue = await driver.query(alt, { savepointDesc: flags.desc });
+                    postMigration.name = dbSchema.$name || dbSchema.name;
+                    postMigration.outcome = 'ALTERED';
                 }
-            } else console.log(`\nNo alterations have been made to schema: ${ scope.dbName }. Skipping.`);
+            } else console.log(`\nNo alterations have been made to schema: ${ dbSchema.name }. Skipping.`);
         }
@@ -110,4 +99,4 @@
             if (dbInstance.status() && flags['force-new']) dbInstance.status(undefined, true); // Force status to new?
-            console.log(`\nCreating database: ${ scope.dbName }`);
-            if (flags.preview !== false) console.log(`\nThe following SQL will now be run:\n${ dbInstance }\n`);
+            console.log(`\nCreating database: ${ dbSchema.name }`);
+            if (flags.preview !== false) console.log(`\nSQL preview:\n${ dbInstance }\n`);
             const proceed = flags.force || (await enquirer.prompt({
@@ -119,29 +108,26 @@ type: 'confirm',
             if (proceed) {
-                scope.returnValue = await driver.query(dbInstance, { savepointDesc: flags.desc });
-                scope.postName = dbSchema.name;
+                postMigration.returnValue = await driver.query(dbInstance, { savepointDesc: flags.desc });
+                postMigration.outcome = 'CREATED';
             }
         }
 
-        if (scope.postName) {
-            const newSchema = await driver.describeDatabase(scope.postName, '*');
+        if (['CREATED', 'ALTERED'].includes(postMigration.outcome)) {
+            const newSchema = await driver.describeDatabase(postMigration.name, '*');
             const $newSchema = CreateDatabase.fromJson(driver, newSchema).status('UP', 'UP').toJson();
-            newDbSchemas.push($newSchema);
-        } else if (!scope.isDrop) newDbSchemas.push(dbSchema);
+            resultDbSchemas.push($newSchema);
+        } else if (postMigration.outcome !== 'DROPPED') resultDbSchemas.push(dbSchema);
     }
 }
 
-// -------------
 // Do rollbacks
-if (['rollback', 'rollforward'].includes(command)) {
-    newDbSchemas.push(...dbSchemas);
+if (command === 'rollback') {
+    resultDbSchemas.push(...dbSchemas);
 
-    const savepointSummaries = await driver.getSavepoints({ direction: command === 'rollforward' ? 'forward' : null });
-    for (const targetSchema of savepointSummaries) {
-        const scope = { dbName: !targetSchema.rollback_date && targetSchema.$name || targetSchema.name };
-        
-        console.log(`\nRolling ${ command === 'rollforward' ? 'forward' : 'back' } database: ${ scope.dbName }`);
-        scope.isDrop = (!targetSchema.rollback_date && !targetSchema.status) || (targetSchema.rollback_date && targetSchema.status === 'DOWN');
+    const savepointSummaries = await driver.getSavepoints({ direction: flags.direction });
+    for (const savepoint of savepointSummaries) {
+        const postRollback = { returnValue: undefined };
+        console.log(`\nRolling ${ flags.direction === 'forward' ? 'forward' : 'back' } database: ${ savepoint.name() }. (This database will now be ${ savepoint.rollbackOutcome.toLowerCase() })`);
         if (flags.preview !== false) {
-            console.log(`\nThe following structure will now be ${ scope.isDrop ? 'dropped' : 'restored' }:\n`);
-            console.table(targetSchema);
+            console.log(`\nSavepoint details:\n`);
+            console.table(savepoint.toJson());
         }
@@ -153,14 +139,14 @@ const proceed = flags.force || (await enquirer.prompt({
         })).proceed;
-        if (proceed) {
-            const savepoint = await driver.database(scope.dbName).savepoint({ direction: command === 'rollforward' ? 'forward' : null });
-            scope.returnValue = await savepoint?.rollback();
-            if (!scope.isDrop) scope.postName = targetSchema.rollback_date && targetSchema.$name || scope.dbName;
+        if (proceed) { postRollback.returnValue = await savepoint.rollback(); }
+
+        if (savepoint.rollbackOutcome === 'DROPPED') {
+            const existing = resultDbSchemas.findIndex(sch => sch.name === savepoint.name());
+            if (existing > -1) resultDbSchemas.splice(existing, 1);
+         } else {
+           const newSchema = await driver.describeDatabase(savepoint.name(true), '*');
+           const $newSchema = CreateDatabase.fromJson(driver, newSchema).status('UP', 'UP').toJson();
+            const existing = resultDbSchemas.findIndex(sch => sch.name === savepoint.name());
+            if (existing > -1) resultDbSchemas[existing] = $newSchema;
+            else resultDbSchemas.push($newSchema);
         }
-        if (scope.postName) {
-            const newSchema = await driver.describeDatabase(scope.postName, '*');
-            const $newSchema = CreateDatabase.fromJson(driver, newSchema).status('UP', 'UP').toJson();
-            const existing = newDbSchemas.findIndex(sch => sch.name === scope.dbName);
-            if (existing > -1) newDbSchemas[existing] = $newSchema;
-            else newDbSchemas.push($newSchema);
-        }
     }
@@ -170,6 +156,24 @@ }
 // Updating schema
-fs.writeFileSync(schemaFile, JSON.stringify(newDbSchemas, null, 3));
-console.log(`\nDone.`);
-console.log(`\nLocal schema updated: ${ driverFile }`);
+if (['migrate', 'rollback'].includes(command)) {
+    fs.writeFileSync(schemaFile, JSON.stringify(resultDbSchemas, null, 3));
+    console.log(`\nDone.`);
+    console.log(`\nLocal schema updated: ${ driverFile }`);
+    process.exit();
+}
 
-process.exit();
+// ------
+// Reset?
+if (command === 'reset-savepoints') {
+    console.log(`\nThis will permanently delete all savepoint records$.`);
+    if (flags.db) console.log(`\nThis will also drop the database: ${ flags.db }.`); // For testing purposes only
+    const proceed = flags.force || (await enquirer.prompt({
+        type: 'confirm',
+        name: 'proceed',
+        message: 'Proceed?'
+    })).proceed;
+    if (proceed) {
+        if (flags.db) await driver.query(`DROP DATABASE IF EXISTS ${ flags.db } CASCADE`, { noCreateSavepoint: true });
+        await driver.query(`DROP DATABASE IF EXISTS obj_information_schema CASCADE`, { noCreateSavepoint: true });
+    }
+    process.exit();
+}

No alert changes

Improved metrics

Total package byte prevSize

1594825

increased by0.69%

Number of package files

125

increased by1.63%

Lines of code

12002

increased by0.75%

No dependency changes