pg-diff-cli - npm Package Compare versions

Comparing version 1.0.4 to 1.1.0

CHANGELOG.md

+# Changelog
+
+#### ver 1.1.0
+
+- Fixed a bug comparing data between tables
+- Improved sql patch generator evaluating objects dependencies
+- Small code refactoring
+- Improved data type recognition
+- Refactored sql path generator to divide commands between DROP and CREATE, removed CHANGE script generator
+- Added MIGRATION STRATEGY for patch execution
+- Added USING expression for casting on ALTER COLUMN data type
+- Improved and re-organized configuration file
+- Improved data compare script generator, now with a single statement is possible to merge existing records on same table but in different database
+
+#### ver 1.0.4
+
+- Improved package information for NPM repository
+
+#### ver 1.0.3
+
+- Added option to generate idempotent sql code
+
+#### ver 1.0.2
+
+- Fixed small bugs
+- Added records comparing and relative patch generator
+
+#### ver 1.0.1
+
+- Fix issue when publish on NPM repository
+
+#### ver 1.0.0
+
+- First working version to compare e generate patch file for (TABLES, INDEXES, VIEWS, MATERIALIZED VIEWS, FUNCTIONS)

src/migratePatch.js

+const sql = require('./sqlScriptGenerator')
+const path = require('path');
+const textReader = require('line-by-line');
+const fs = require('fs');
+const chalk = require('chalk');
+
+var helper = {
+    __status: {
+        TO_APPLY: "TODO",
+        IN_PROGRESS: "WIP",
+        DONE: "DONE",
+        ERROR: "ERROR"
+    },
+    __migrationsHistoryTableExists: false,
+    __fullMigrationsHistoryTableName: `"${global.config.options.migration.tableSchema}"."${global.config.options.migration.tableName}"`,
+    __migrationsHistoryTableConstraintName: `"${global.config.options.migration.tableName}_pkey"`,
+    __migrationsHistoryTableSchema: {
+        columns: {
+            "version": {
+                nullable: false,
+                datatype: "varchar",
+                dataTypeID: 1043,
+                default: '',
+                precision: 17,
+                scale: null
+            },
+            "name": {
+                nullable: false,
+                datatype: "varchar",
+                dataTypeID: 1043,
+                default: null,
+                precision: null,
+                scale: null
+            },
+            "status": {
+                nullable: false,
+                datatype: "varchar",
+                dataTypeID: 1043,
+                default: "''",
+                precision: 5,
+                scale: null
+            },
+            "last_message": {
+                nullable: true,
+                datatype: "varchar",
+                dataTypeID: 1043,
+                default: null,
+                precision: null,
+                scale: null
+            },
+            "script": {
+                nullable: false,
+                datatype: "varchar",
+                dataTypeID: 1043,
+                default: "''",
+                precision: null,
+                scale: null
+            },
+            "applied_on": {
+                nullable: true,
+                datatype: "timestamp",
+                dataTypeID: 1114,
+                default: null,
+                precision: null,
+                scale: null
+            }
+        },
+        constraints: {},
+        options: {
+            withOids: false
+        },
+        indexes: {},
+        privileges: {},
+        owner: global.config.target.user
+    },
+    applyPatch: async function(patchFileInfo) {
+        await helper.__applyPatchFile(patchFileInfo);
+    },
+    getLatestPatchApplied: async function() {
+        let sql = `SELECT "version" FROM ${helper.__fullMigrationsHistoryTableName} ORDER BY "version" DESC LIMIT 1;`
+        let result = await global.targetClient.query(sql);
+        let lastVersionApplied = 0;
+
+        if (result.rows.length > 0)
+            lastVersionApplied = (result).rows[0].version;
+
+        return bigInt(lastVersionApplied);
+    },
+    migrate: async function() {
+        await helper.__prepareMigrationsHistoryTable();
+
+        let scriptsFolder = path.resolve(process.cwd(), global.config.options.outputDirectory);
+        let scriptFiles = fs.readdirSync(scriptsFolder).sort().filter((file) => { return file.match(/.*\.(sql)/ig) });
+
+        for (let index in scriptFiles) {
+            let patchFileInfo = helper.__getPatchFileInfo(scriptFiles[index], scriptsFolder);
+            let patchStatus = await helper.__checkPatchStatus(patchFileInfo);
+
+            switch (patchStatus) {
+                case helper.__status.IN_PROGRESS:
+                    {
+                        if (!global.replayMigration)
+                            throw new Error(`The patch version={${patchFileInfo.version}} and name={${patchFileInfo.name}} is still in progress! Use command argument "-mr" to replay this script.`);
+
+                        await helper.applyPatch(patchFileInfo);
+                    }
+                    break;
+                case helper.__status.ERROR:
+                    {
+                        if (!global.replayMigration)
+                            throw new Error(`The patch version={${patchFileInfo.version}} and name={${patchFileInfo.name}} encountered an error! Use command argument "-mr" to replay this script.`);
+
+                        await helper.applyPatch(patchFileInfo);
+                    }
+                    break;
+                case helper.__status.DONE:
+                    console.log(chalk.yellow(`The patch version={${patchFileInfo.version}} and name={${patchFileInfo.name}} has been already applied, it will be skipped.`));
+                    break;
+                case helper.__status.TO_APPLY:
+                    await helper.applyPatch(patchFileInfo);
+                    break;
+                default:
+                    throw new Error(`The status "${args[0]}" not recognized! Impossible to apply patch version={${patchFileInfo.version}} and name={${patchFileInfo.name}}.`);
+            }
+        }
+    },
+    async __checkPatchStatus(patchFileInfo) {
+        let sql = `SELECT "status" FROM ${helper.__fullMigrationsHistoryTableName} WHERE "version" = '${patchFileInfo.version}' AND "name" = '${patchFileInfo.name}'`;
+        let response = await global.targetClient.query(sql);
+
+        if (response.rows.length > 1)
+            throw new Error(`Too many patches found on migrations history table "${helper.__fullMigrationsHistoryTableName}" for patch version=${patchFileInfo.version} and name=${patchFileInfo.name}!`);
+
+        if (response.rows.length < 1)
+            return helper.__status.TO_APPLY;
+        else
+            return response.rows[0].status;
+
+    },
+    __getPatchFileInfo(filename, filepath) {
+        let splittedPatchFileName = filename.split(/_(.+)/);
+
+        let patchInfo = {
+            version: splittedPatchFileName[0],
+            name: splittedPatchFileName[1].replace('.sql', ''),
+            fileName: filename,
+            filePath: filepath
+        }
+
+        return patchInfo;
+    },
+    __applyPatchFile: function(patchFileInfo) {
+        return new Promise(async(resolve, reject) => {
+            try {
+
+                let scriptPatch = patchFileInfo;
+                scriptPatch.command = '';
+                scriptPatch.message = '';
+
+                await helper.__addRecordToHistoryTable(scriptPatch.version, scriptPatch.name);
+
+                let reader = new textReader(path.resolve(scriptPatch.filePath, scriptPatch.fileName));
+
+                reader.on('error', (err) => {
+                    reject(err);
+                });
+
+                let readingBlock = false;
+                let patchError = null;
+
+                reader.on('line', function(line) {
+                    if (readingBlock) {
+                        if (line.startsWith('--- END')) {
+                            readingBlock = false;
+                            reader.pause();
+                            helper.__executePatchScript(scriptPatch)
+                                .then(() => {
+                                    reader.resume();
+                                })
+                                .catch((err) => {
+                                    patchError = err;
+                                    reader.close();
+                                    reader.resume();
+                                });
+                        } else {
+                            scriptPatch.command += `${line}\n`;
+                        }
+                    }
+
+                    if (!readingBlock && line.startsWith('--- BEGIN')) {
+                        readingBlock = true;
+                        scriptPatch.command = '';
+                        scriptPatch.message = line;
+                    }
+                });
+
+                reader.on('end', function() {
+                    if (patchError)
+                        helper.__updateRecordToHistoryTable(helper.__status.ERROR, patchError, scriptPatch.command, scriptPatch.version)
+                        .then(() => {
+                            reject(patchError);
+                        }).catch((err) => {
+                            reject(err);
+                        })
+                    else
+                        helper.__updateRecordToHistoryTable(helper.__status.DONE, '', '', scriptPatch.version)
+                        .then(() => {
+                            resolve();
+                        }).catch((err) => {
+                            reject(err);
+                        })
+                });
+
+            } catch (e) {
+                reject(e);
+            }
+        });
+    },
+    __executePatchScript: async function(scriptPatch) {
+        await helper.__updateRecordToHistoryTable(helper.__status.IN_PROGRESS, scriptPatch.message, scriptPatch.command, scriptPatch.version);
+        await global.targetClient.query(scriptPatch.command);
+    },
+    __updateRecordToHistoryTable: async function(status, message, script, patchVersion) {
+        let changes = {
+            "status": status,
+            "last_message": message,
+            "script": script.replace(/'/g, "''"),
+            "applied_on": new Date().toUTCString()
+        }
+
+        let filterConditions = {
+            "version": patchVersion
+        }
+
+        let command = sql.generateUpdateTableRecordScript(helper.__fullMigrationsHistoryTableName, helper.__getFieldDataTypeIDs(), filterConditions, changes);
+        await global.targetClient.query(command);
+    },
+    __addRecordToHistoryTable: async function(patchVersion, patchName) {
+        let changes = {
+            "version": patchVersion,
+            "name": patchName,
+            "status": helper.__status.TO_APPLY,
+            "last_message": '',
+            "script": '',
+            "applied_on": null
+        }
+
+        let options = {
+            constraintName: helper.__migrationsHistoryTableConstraintName
+        }
+
+        let command = sql.generateMergeTableRecord(helper.__fullMigrationsHistoryTableName, helper.__getFieldDataTypeIDs(), changes, options);
+        await global.targetClient.query(command);
+    },
+    __getFieldDataTypeIDs: function() {
+        let fields = [];
+        for (let column in helper.__migrationsHistoryTableSchema.columns) {
+            fields.push({
+                name: column,
+                dataTypeID: helper.__migrationsHistoryTableSchema.columns[column].dataTypeID
+            });
+        }
+        return fields;
+    },
+    __prepareMigrationsHistoryTable: async function() {
+        if (!helper.__migrationsHistoryTableExists) {
+
+            helper.__migrationsHistoryTableSchema.constraints[helper.__migrationsHistoryTableConstraintName] = {
+                type: 'p',
+                definition: 'PRIMARY KEY ("version")'
+            };
+
+            helper.__migrationsHistoryTableSchema.privileges[global.config.target.user] = {
+                select: true,
+                insert: true,
+                update: true,
+                delete: true,
+                truncate: true,
+                references: true,
+                trigger: true
+            };
+
+            let saveIdempotentSetting = global.config.options.schemaCompare.idempotentScript;
+            global.config.options.schemaCompare.idempotentScript = true;
+
+            let sqlScript = sql.generateCreateTableScript(helper.__fullMigrationsHistoryTableName, helper.__migrationsHistoryTableSchema);
+            await global.targetClient.query(sqlScript);
+
+            helper.__migrationsHistoryTableExists = true;
+            global.config.options.schemaCompare.idempotentScript = saveIdempotentSetting;
+        }
+    }
+};
+
+module.exports = helper;

src/retrieveRecords.js

+const Exception = require('./error')
+const { Progress } = require('clui');
+const chalk = require('chalk');
+
+var helper = {
+    __progressBar: new Progress(20),
+    __progressBarValue: 0.0,
+    __updateProgressbar: function(value, label) {
+        this.__progressBarValue = value;
+        process.stdout.clearLine();
+        process.stdout.cursorTo(0);
+        process.stdout.write(this.__progressBar.update(this.__progressBarValue) + ' - ' + chalk.whiteBright(label));
+    },
+    collectTablesRecords: function(client, tables) {
+        return new Promise(async(resolve, reject) => {
+            try {
+                helper.__updateProgressbar(0.0, 'Collecting tables records ...');
+                const progressBarStep = 1.0 / Object.keys(tables).length;
+
+                var tableRecords = {};
+                for (let table in tables) {
+                    helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting RECORDS for table ${table}`);
+
+                    tableRecords[table] = {
+                        records: [],
+                        exists: false,
+                        sequences: []
+                    };
+
+                    if (await helper.__checkIfTableExists(client, table, tables[table].schema)) {
+                        tableRecords[table].records = await helper.__collectTableRecords(client, table, tables[table]);
+                        tableRecords[table].exists = true;
+                        tableRecords[table].sequences = await helper.__collectTableSequences(client, table, tables[table].schema);
+                    }
+                }
+
+                helper.__updateProgressbar(1.0, 'Table records collected!');
+
+                resolve(tableRecords);
+            } catch (e) {
+                reject(e);
+            }
+        });
+    },
+    __checkIfTableExists: async function(client, table, schema) {
+        let response = await client.query(`SELECT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = '${table}' AND schemaname = '${schema||'public'}')`);
+        return response.rows[0].exists;
+    },
+    __collectTableRecords: async function(client, table, config) {
+        let result = {
+            fields: null,
+            rows: null
+        };
+        let response = await client.query(`SELECT MD5(ROW(${config.keyFields.join(',')})::text) AS "rowHash", * FROM ${table}`);
+        result.fields = response.fields;
+        result.rows = response.rows;
+
+        return result;
+    },
+    __collectTableSequences: async function(client, table, schema) {
+        let response = await client.query(`
+            SELECT * FROM (
+                SELECT 
+                    pg_get_serial_sequence(a.attrelid::regclass::name, a.attname) AS seqname,
+                    a.attname,
+                    CASE 
+                    	WHEN COALESCE(a.attidentity,'') = '' THEN 'SERIAL'
+                    	WHEN a.attidentity = 'a' THEN 'ALWAYS'
+                    	WHEN a.attidentity = 'd' THEN 'BY DEFAULT'
+                    END AS identitytype
+                FROM pg_attribute a
+                WHERE a.attrelid = '"${schema||'public'}"."${table}"'::regclass
+                AND a.attnum > 0
+                AND a.attisdropped = false
+            ) T WHERE T.seqname IS NOT NULL`);
+
+        return response.rows;
+    }
+}
+
+module.exports = helper;

main.js

@@ -11,3 +11,3 @@ #!/usr/bin/env node
 const compareSchema = require('./src/compareSchema');
-const data = require('./src/retrieveData');
+const data = require('./src/retrieveRecords');
 const compareRecords = require('./src/compareRecords');
@@ -20,11 +20,11 @@ const { Client } = require('pg');
 global.config = null;
+global.replayMigration = false;
 
 __printIntro();
-__readArguments();
-__loadConfig();
-__printOptions();
-__initDbConnections();
-__run();
+__readArguments().catch((err) => {
+    __handleError(err);
+    process.exitCode = -1;
+    process.exit();
+});
 
-
 function __printHelp() {
@@ -37,10 +37,21 @@ log();
     log();
-    log(chalk.gray('OPTION     \t\tDESCRIPTION'));
-    log(chalk.green('-h, --help\t\t') + chalk.blue('To show this help.'));
+    log(chalk.gray('OPTION                \t\tDESCRIPTION'));
+    log(chalk.green('-h,  --help           \t\t') + chalk.blue('To show this help.'));
+    log(chalk.green('-c,  --compare        \t\t') + chalk.blue('To run compare and generate a patch file.'));
+    log(chalk.green('-m,  --migrate        \t\t') + chalk.blue('To run migration applying all missing patch files.'));
+    log(chalk.green('-mu, --migrate-upto   \t\t') + chalk.blue('To run migration applying all patch files till the specified patch file.'));
+    log(chalk.green('-mr, --migrate-replay \t\t') + chalk.blue('To run migration applying all missing or failed or stuck patch files.'));
     log();
     log();
-    log(chalk.gray("USAGE:   ") + chalk.yellow("pg-diff ") + chalk.cyan("{configuration name | or valid option} {script name}"));
-    log(chalk.gray("EXAMPLE: ") + chalk.yellow("pg-diff ") + chalk.cyan("development my-script"));
+    log(chalk.gray("TO COMPARE: ") + chalk.yellow("pg-diff ") + chalk.gray("-c ") + chalk.cyan("configuration-name script-name"));
+    log(chalk.gray("   EXAMPLE: ") + chalk.yellow("pg-diff ") + chalk.gray("-c ") + chalk.cyan("development my-script"));
     log();
+    log(chalk.gray("TO MIGRATE: ") + chalk.yellow("pg-diff ") + chalk.gray("[-m | -mr] ") + chalk.cyan("configuration-name"));
+    log(chalk.gray("   EXAMPLE: ") + chalk.yellow("pg-diff ") + chalk.gray("-m ") + chalk.cyan("development"));
+    log(chalk.gray("   EXAMPLE: ") + chalk.yellow("pg-diff ") + chalk.gray("-mr ") + chalk.cyan("development"));
     log();
+    log(chalk.gray("TO MIGRATE: ") + chalk.yellow("pg-diff ") + chalk.gray("-mu ") + chalk.cyan("configuration-name patch-file-name"));
+    log(chalk.gray("   EXAMPLE: ") + chalk.yellow("pg-diff ") + chalk.gray("-mu ") + chalk.cyan("development 20182808103040999_my-script.sql"));
+    log();
+    log();
 }
@@ -62,11 +73,11 @@
     log(chalk.gray('CONFIGURED OPTIONS'))
-    log(chalk.yellow("    Script Author: ") + chalk.green(global.config.options.author));
-    log(chalk.yellow(" Output Directory: ") + chalk.green(path.resolve(process.cwd(), global.config.options.outputDirectory)));
-    log(chalk.yellow("Schema Namespaces: ") + chalk.green(global.config.options.schemaNamespace));
-    log(chalk.yellow("Idempotent Script: ") + chalk.green(global.config.options.idempotent ? 'ENABLED' : 'DISABLED'));
-    log(chalk.yellow("     Data Compare: ") + chalk.green(global.config.options.dataCompare.enable ? 'ENABLED' : 'DISABLED'));
+    log(chalk.yellow("         Script Author: ") + chalk.green(global.config.options.author));
+    log(chalk.yellow("      Output Directory: ") + chalk.green(path.resolve(process.cwd(), global.config.options.outputDirectory)));
+    log(chalk.yellow("     Schema Namespaces: ") + chalk.green(global.config.options.schemaCompare.namespaces));
+    log(chalk.yellow("     Idempotent Script: ") + chalk.green(global.config.options.schemaCompare.idempotentScript ? 'ENABLED' : 'DISABLED'));
+    log(chalk.yellow("          Data Compare: ") + chalk.green(global.config.options.dataCompare.enable ? 'ENABLED' : 'DISABLED'));
     log();
 }
 
-function __readArguments() {
+async function __readArguments() {
     var args = process.argv.slice(2);
@@ -79,13 +90,54 @@ if (args.length <= 0) {
 
-    if (args.length == 1)
-        switch (args[0]) {
-            case '-h':
-            case '--help':
+    switch (args[0]) {
+        case '-h':
+        case '--help':
+            {
                 __printHelp();
                 process.exit();
-        }
+            }
+        case '-c':
+        case '--compare':
+            {
+                if (args.length != 3) {
+                    log(chalk.red('Missing arguments!'));
+                    __printHelp();
+                    process.exit();
+                }
+                global.configName = args[1];
+                global.scriptName = args[2];
+                __loadConfig();
+                __validateCompareConfig();
+                __printOptions();
+                await __initDbConnections();
+                await __runComparison();
+            }
+            break;
+        case '-m':
+        case '--migrate':
+        case '-mr':
+        case '--migrate-replay':
+            {
+                if (args.length != 2) {
+                    log(chalk.red('Missing arguments!'));
+                    __printHelp();
+                    process.exit();
+                }
 
-    if (args.length == 2) {
-        global.configName = args[0];
-        global.scriptName = args[1];
+                if (args[0] == '-mr' || args[0] == '--migrate-replay')
+                    global.replayMigration = true;
+
+                global.configName = args[1];
+                __loadConfig();
+                __validateMigrationConfig();
+                __printOptions();
+                await __initDbConnections();
+                await __runMigration();
+            }
+            break;
+        default:
+            {
+                log(chalk.red('Missing arguments!'));
+                __printHelp();
+                process.exit();
+            }
     }
@@ -105,11 +157,29 @@ }
 
+        if (!global.config.source)
+            throw new Error('The configuration doesn\'t contains the section "source (object)" !');
+
+        if (!global.config.target)
+            throw new Error('The configuration doesn\'t contains the section "target (object)" !');
+
+    } catch (e) {
+        __handleError(e);
+        process.exitCode = -1;
+        process.exit();
+    }
+}
+
+function __validateCompareConfig() {
+    try {
         if (!global.config.options.outputDirectory)
             throw new Error('The configuration section "options" must contains property "outputDirectory (string)" !');
 
-        if (!global.config.options.schemaNamespace)
-            throw new Error('The configuration section "options" must contains property "schemaNamespace (array of strings)" !');
+        if (!global.config.options.schemaCompare)
+            throw new Error('The configuration section "options" must contains property "schemaCompare (object)" !');
 
-        if (!global.config.options.hasOwnProperty('idempotent'))
-            throw new Error('The configuration section "options" must contains property "idempotent (boolean)" !');
+        if (!global.config.options.schemaCompare.hasOwnProperty("namespaces"))
+            throw new Error('The configuration section "options.schemaCompare" must contains property "namespaces (array of strings)" !');
 
+        if (!global.config.options.schemaCompare.hasOwnProperty('idempotentScript'))
+            throw new Error('The configuration section "options.schemaCompare" must contains property "idempotentScript (boolean)" !');
+
         if (!global.config.options.dataCompare)
@@ -121,8 +191,21 @@ throw new Error('The configuration section "options" must contains property "dataCompare (object)" !');
 
-        if (!global.config.source)
-            throw new Error('The configuration doesn\'t contains the section "source (object)" !');
+    } catch (e) {
+        __handleError(e);
+        process.exitCode = -1;
+        process.exit();
+    }
+}
 
-        if (!global.config.target)
-            throw new Error('The configuration doesn\'t contains the section "target (object)" !');
+function __validateMigrationConfig() {
+    try {
 
+        if (!global.config.options.migration)
+            throw new Error('The configuration section "options" must contains property "migration (object)" !');
+
+        if (!global.config.options.migration.hasOwnProperty("tableSchema"))
+            throw new Error('The configuration section "options.migration" must contains property "tableSchema (string)" !');
+
+        if (!global.config.options.migration.hasOwnProperty('tableName'))
+            throw new Error('The configuration section "options.migration" must contains property "tableName (string)" !');
+
     } catch (e) {
@@ -135,3 +218,3 @@ __handleError(e);
 
-function __initDbConnections() {
+async function __initDbConnections() {
     var spinner = new Spinner(chalk.blue('Connecting to databases ...'), ['⣾', '⣽', '⣻', '⢿', '⡿', '⣟', '⣯', '⣷']);
@@ -146,4 +229,5 @@ spinner.start();
         port: global.config.source.port,
-    })
-    global.sourceClient.connect();
+    });
+
+    await global.sourceClient.connect();
     log(chalk.whiteBright(`Connected to [${global.config.source.host}:${global.config.source.port}/${global.config.source.database}] `) + chalk.green('✓'));
@@ -158,3 +242,4 @@
     });
-    global.targetClient.connect();
+
+    await global.targetClient.connect();
     log(chalk.whiteBright(`Connected to [${global.config.target.host}:${global.config.target.port}/${global.config.target.database}] `) + chalk.green('✓'));
@@ -165,60 +250,53 @@
 
-async function __run() {
-    try {
-        log();
-        log(chalk.yellow("Collect SOURCE database objects"));
-        let sourceSchema = await schema.collectSchemaObjects(sourceClient, global.config.options.schemaNamespace);
+async function __runComparison() {
+    log();
+    log(chalk.yellow("Collect SOURCE database objects"));
+    let sourceSchema = await schema.collectSchemaObjects(sourceClient, global.config.options.schemaCompare.namespaces);
 
+    log();
+    log();
+    log(chalk.yellow("Collect TARGET database objects"));
+    let targetSchema = await schema.collectSchemaObjects(targetClient, global.config.options.schemaCompare.namespaces);
+
+    log();
+    log();
+    log(chalk.yellow("Compare SOURCE with TARGET database objects"));
+    let scripts = compareSchema.compareDatabaseObjects(sourceSchema, targetSchema);
+
+    //console.dir(scripts, { depth: null });
+
+    if (global.config.options.dataCompare.enable) {
+        global.dataTypes = (await sourceClient.query(`SELECT oid, typcategory FROM pg_type`)).rows;
+
         log();
         log();
-        log(chalk.yellow("Collect TARGET database objects"));
-        let targetSchema = await schema.collectSchemaObjects(targetClient, global.config.options.schemaNamespace);
+        log(chalk.yellow("Collect SOURCE tables records"));
+        let sourceTablesRecords = await data.collectTablesRecords(sourceClient, global.config.options.dataCompare.tables);
 
         log();
         log();
-        log(chalk.yellow("Compare SOURCE with TARGET database objects"));
-        let scripts = compareSchema.compareDatabaseObjects(sourceSchema, targetSchema);
+        log(chalk.yellow("Collect TARGET tables records"));
+        let targetTablesRecords = await data.collectTablesRecords(targetClient, global.config.options.dataCompare.tables);
 
-        //console.dir(scripts, { depth: null });
-
-        if (global.config.options.dataCompare.enable) {
-            global.dataTypes = (await sourceClient.query(`SELECT oid, typcategory FROM pg_type`)).rows;
-
-            log();
-            log();
-            log(chalk.yellow("Collect SOURCE tables records"));
-            let sourceTablesRecords = await data.collectTablesRecords(sourceClient, global.config.options.dataCompare.tables);
-
-            log();
-            log();
-            log(chalk.yellow("Collect TARGET tables records"));
-            let targetTablesRecords = await data.collectTablesRecords(targetClient, global.config.options.dataCompare.tables);
-
-            log();
-            log();
-            log(chalk.yellow("Compare SOURCE with TARGET database table records"));
-            scripts = scripts.concat(compareRecords.compareTablesRecords(global.config.options.dataCompare.tables, sourceTablesRecords, targetTablesRecords));
-        } else {
-            log();
-            log();
-            log(chalk.yellow("Data compare not enabled!"));
-        }
-
-        let scriptFilePath = await __saveSqlScript(scripts);
-
         log();
         log();
-        log(chalk.whiteBright("SQL patch file has been created succesfully at: ") + chalk.green(scriptFilePath));
+        log(chalk.yellow("Compare SOURCE with TARGET database table records"));
+        scripts = scripts.concat(compareRecords.compareTablesRecords(global.config.options.dataCompare.tables, sourceTablesRecords, targetTablesRecords));
+    } else {
+        log();
+        log();
+        log(chalk.yellow("Data compare not enabled!"));
+    }
 
-        process.exit();
+    let scriptFilePath = await __saveSqlScript(scripts);
 
-    } catch (e) {
-        __handleError(e);
+    log();
+    log();
+    log(chalk.whiteBright("SQL patch file has been created succesfully at: ") + chalk.green(scriptFilePath));
 
-        process.exitCode = -1;
-        process.exit();
-    }
+    process.exit();
 }
 
 function __handleError(e) {
+    log();
     log(chalk.red(e));
@@ -259,2 +337,10 @@ log(chalk.magenta(e.stack));
     });
+}
+
+async function __runMigration() {
+    global.dataTypes = (await sourceClient.query(`SELECT oid, typcategory FROM pg_type`)).rows;
+    const migratePatch = require('./src/migratePatch');
+    await migratePatch.migrate();
+
+    process.exit();
 }

package.json

 {
     "name": "pg-diff-cli",
-    "version": "1.0.4",
+    "version": "1.1.0",
     "description": "PostgreSQL schema and data comparing tool",
@@ -45,4 +45,5 @@ "pgver": "9.6+",
         "figlet": "^1.2.0",
+        "line-by-line": "^0.1.6",
         "pg": "^7.4.3"
     }
 }

pg-diff-config.json

@@ -13,3 +13,3 @@ {
             "port": 5432,
-            "database": "hkdev",
+            "database": "hukocash_dev",
             "user": "postgres",
@@ -19,21 +19,27 @@ "password": "postgres"
         "options": {
-            "outputDirectory": "sqlscripts",
-            "schemaNamespace": ["public"],
             "author": "@MSO - Michael Sogos",
-            "idempotent": false,
+            "outputDirectory": "db_migration",
+            "schemaCompare": {
+                "namespaces": ["public"],
+                "idempotentScript": true
+            },
             "dataCompare": {
                 "enable": true,
                 "tables": {
-                    "pippo": {
-                        "keyFields": ["id_reason"],
+                    "settings": {
+                        "keyFields": ["id_setting"],
                         "schema": "public"
                     },
-                    "reason_enum": {
-                        "keyFields": ["id_reason"]
+                    "tax_regulations": {
+                        "keyFields": ["id_tax_regulation"]
                     },
-                    "test1": {
-                        "keyFields": ["id_test"],
+                    "vat_rates": {
+                        "keyFields": ["id_vat_rate"],
                         "schema": "public"
                     }
                 }
+            },
+            "migration": {
+                "tableSchema": "public",
+                "tableName": "migrations"
             }
@@ -40,0 +46,0 @@ }

src/compareRecords.js

@@ -29,3 +29,3 @@ const sql = require('./sqlScriptGenerator')
             let tableName = `"${options[table].schema||'public'}"."${table}"`
-            this.__updateProgressbar(this.__progressBarValue + progressBarStep, `Comparing table ${tableName} records`);
+            this.__updateProgressbar(this.__progressBarValue, `Comparing table ${tableName} records`);
             this.__tempScripts = [];
@@ -35,9 +35,15 @@
                 this.__tempScripts.push(`\n--ERROR: Table ${tableName} not found on SOURCE database for comparison!\n`);
-            } else if (!targetTablesRecords[table] || !targetTablesRecords[table].exists) {
-                this.__tempScripts.push(`\n--ERROR: Table ${tableName} not found on TARGET database for comparison!\n`);
             } else {
-                this.__compareTableRecords(tableName, options[table].keyFields, sourceTablesRecords[table].records, targetTablesRecords[table].records);
+                if (!targetTablesRecords[table] || !targetTablesRecords[table].exists)
+                    this.__tempScripts.push(`\n--ERROR: Table ${tableName} not found on TARGET database for comparison!\n`);
+
+                //Check if at least one sequence is for an ALWAYS IDENTITY in case the OVERRIDING SYSTEM VALUE must be issued
+                let isIdentityUserValuesAllowed = this.__checkIdentityAllowUserValues(targetTablesRecords[table].sequences);
+                this.__compareTableRecords(tableName, options[table].keyFields, sourceTablesRecords[table], targetTablesRecords[table], isIdentityUserValuesAllowed);
+                //Reset sequences to avoid PKEY or UNIQUE CONSTRAINTS conflicts
+                this.__rebaseSequences(tableName, sourceTablesRecords[table].sequences);
             }
 
             this.__appendScripts(`SYNCHRONIZE TABLE ${tableName} RECORDS`);
+            this.__progressBarValue += progressBarStep;
         }
@@ -49,5 +55,14 @@
     },
-    __compareTableRecords: function(table, keyFields, sourceTableRecords, targetTableRecords) {
+    __rebaseSequences: function(tableName, tableSequences) {
+        tableSequences.forEach(sequence => {
+            this.__tempScripts.push(sql.generateSetSequenceValueScript(tableName, sequence));
+        });
+    },
+    __checkIdentityAllowUserValues: function(tableSequences) {
+        return !tableSequences.some((sequence) => sequence.identitytype === 'ALWAYS');
+    },
+    __compareTableRecords: function(table, keyFields, sourceTableRecords, targetTableRecords, isIdentityUserValuesAllowed) {
         let ignoredRowHash = [];
-        sourceTableRecords.rows.forEach((record, index) => {
+
+        sourceTableRecords.records.rows.forEach((record, index) => {
             let keyFieldsMap = this.__getKeyFieldsMap(keyFields, record);
@@ -60,3 +75,3 @@
             //Check if record is duplicated in source
-            if (this.__checkDuplicatedRowHash(sourceTableRecords.rows, record.rowHash, index)) {
+            if (this.__checkDuplicatedRowHash(sourceTableRecords.records.rows, record.rowHash, index)) {
                 ignoredRowHash.push(record.rowHash);
@@ -68,5 +83,7 @@ this.__tempScripts.push(`\n--ERROR: Too many record found in SOURCE database for table {${table}} and key fields ${JSON.stringify(keyFieldsMap)} !\n`);
             //Check if record is duplicated in target
-            let targetRecord = targetTableRecords.rows.filter(function(r) {
-                return r.rowHash === record.rowHash;
-            });
+            let targetRecord = [];
+            if (targetTableRecords.exists)
+                targetRecord = targetTableRecords.records.rows.filter(function(r) {
+                    return r.rowHash === record.rowHash;
+                });
 
@@ -83,5 +100,5 @@ if (targetRecord.length > 1) {
                 delete record.rowHash;
-                this.__tempScripts.push(sql.generateInsertTableRecordScript(table, record, sourceTableRecords.fields));
-            } else { //A record with same KEY FIELDS VALEUS has been found, then update not matching fieds only
-                this.__compareTableRecordFields(table, keyFieldsMap, sourceTableRecords.fields, record, targetRecord[0])
+                this.__tempScripts.push(sql.generateInsertTableRecordScript(table, record, sourceTableRecords.records.fields, isIdentityUserValuesAllowed));
+            } else { //A record with same KEY FIELDS VALUES has been found, then update not matching fieds only
+                this.__compareTableRecordFields(table, keyFieldsMap, sourceTableRecords.records.fields, record, targetRecord[0])
             }
@@ -91,19 +108,20 @@
 
-        targetTableRecords.rows.forEach((record, index) => {
-            //Check if row hash has been ignored because duplicated or already processed from source
-            if (ignoredRowHash.some((hash) => hash === record.rowHash))
-                return;
+        if (targetTableRecords.exists)
+            targetTableRecords.records.rows.forEach((record, index) => {
+                //Check if row hash has been ignored because duplicated or already processed from source
+                if (ignoredRowHash.some((hash) => hash === record.rowHash))
+                    return;
 
-            //Check if record is duplicated in target
-            if (this.__checkDuplicatedRowHash(targetTableRecords.rows, record.rowHash, index)) {
-                ignoredRowHash.push(record.rowHash);
-                this.__tempScripts.push(`\n--ERROR: Too many record found in TARGET database for table {${table}} and key fields ${JSON.stringify(keyFieldsMap)} !\n`);
-                return;
-            };
+                //Check if record is duplicated in target
+                if (this.__checkDuplicatedRowHash(targetTableRecords.records.rows, record.rowHash, index)) {
+                    ignoredRowHash.push(record.rowHash);
+                    this.__tempScripts.push(`\n--ERROR: Too many record found in TARGET database for table {${table}} and key fields ${JSON.stringify(keyFieldsMap)} !\n`);
+                    return;
+                };
 
-            let keyFieldsMap = this.__getKeyFieldsMap(keyFields, record);
+                let keyFieldsMap = this.__getKeyFieldsMap(keyFields, record);
 
-            //Generate sql script to delete record because not exists on source database table
-            this.__tempScripts.push(sql.generateDeleteTableRecordScript(table, sourceTableRecords.fields, keyFieldsMap));
-        });
+                //Generate sql script to delete record because not exists on source database table
+                this.__tempScripts.push(sql.generateDeleteTableRecordScript(table, sourceTableRecords.records.fields, keyFieldsMap));
+            });
     },
@@ -110,0 +128,0 @@ __compareTableRecordFields: function(table, keyFieldsMap, fields, sourceRecord, targetRecord) {

src/compareSchema.js

@@ -8,4 +8,9 @@ const sql = require('./sqlScriptGenerator')
     __tempScripts: [],
+    __droppedConstraints: [],
+    __droppedIndexes: [],
+    __droppedViews: [],
     __progressBar: new Progress(20),
     __progressBarValue: 0.0,
+    __sourceSchema: {},
+    __targetSchema: {},
     __updateProgressbar: function(value, label) {
@@ -24,12 +29,12 @@ this.__progressBarValue = value;
     },
-    __compareSchemas: function(sourceSchemas, targetSchemas) {
+    __compareSchemas: function() {
         this.__updateProgressbar(this.__progressBarValue + 0.0001, 'Comparing schemas');
-        const progressBarStep = 0.1999 / Object.keys(sourceSchemas).length;
+        const progressBarStep = 0.1999 / Object.keys(this.__sourceSchema.schemas).length;
 
-        for (let schema in sourceSchemas) { //Get missing schemas on target
+        for (let schema in this.__sourceSchema.schemas) { //Get missing schemas on target
             this.__updateProgressbar(this.__progressBarValue + progressBarStep, `Comparing SCHEMA ${schema}`);
             this.__tempScripts = [];
 
-            if (!targetSchemas[schema]) { //Schema not exists on target database, then generate script to create schema
-                this.__tempScripts.push(sql.generateCreateSchemaScript(schema, sourceSchemas[schema].owner));
+            if (!this.__targetSchema.schemas[schema]) { //Schema not exists on target database, then generate script to create schema
+                this.__tempScripts.push(sql.generateCreateSchemaScript(schema, this.__sourceSchema.schemas[schema].owner));
             }
@@ -40,25 +45,27 @@
     },
-    __compareTables: function(sourceTables, targetTables) {
+    __compareTables: function() {
         this.__updateProgressbar(this.__progressBarValue + 0.0001, 'Comparing tables');
-        const progressBarStep = 0.1999 / Object.keys(sourceTables).length;
+        const progressBarStep = 0.1999 / Object.keys(this.__sourceSchema.tables).length;
 
-        for (let table in sourceTables) { //Get new or changed tablestable
+        for (let table in this.__sourceSchema.tables) { //Get new or changed tablestable
             this.__updateProgressbar(this.__progressBarValue + progressBarStep, `Comparing TABLE ${table}`);
             this.__tempScripts = [];
+            this.__droppedConstraints = [];
+            this.__droppedIndexes = [];
             let actionLabel = '';
 
-            if (targetTables[table]) { //Table exists on both database, then compare table schema  
+            if (this.__targetSchema.tables[table]) { //Table exists on both database, then compare table schema  
                 actionLabel = 'ALTER';
 
-                this.__compareTableOptions(table, sourceTables[table].options, targetTables[table].options);
-                this.__compareTableColumns(table, sourceTables[table].columns, targetTables[table].columns);
-                this.__compareTableConstraints(table, sourceTables[table].constraints, targetTables[table].constraints);
-                this.__compareTableIndexes(sourceTables[table].indexes, targetTables[table].indexes);
-                this.__compareTablePrivileges(table, sourceTables[table].privileges, targetTables[table].privileges);
-                if (sourceTables[table].owner != targetTables[table].owner)
-                    this.__tempScripts.push(sql.generateChangeTableOwnerScript(table, sourceTables[table].owner));
+                this.__compareTableOptions(table, this.__sourceSchema.tables[table].options, this.__targetSchema.tables[table].options);
+                this.__compareTableColumns(table, this.__sourceSchema.tables[table].columns, this.__targetSchema.tables[table].columns, this.__targetSchema.tables[table].constraints, this.__targetSchema.tables[table].indexes);
+                this.__compareTableConstraints(table, this.__sourceSchema.tables[table].constraints, this.__targetSchema.tables[table].constraints);
+                this.__compareTableIndexes(this.__sourceSchema.tables[table].indexes, this.__targetSchema.tables[table].indexes);
+                this.__compareTablePrivileges(table, this.__sourceSchema.tables[table].privileges, this.__targetSchema.tables[table].privileges);
+                if (this.__sourceSchema.tables[table].owner != this.__targetSchema.tables[table].owner)
+                    this.__tempScripts.push(sql.generateChangeTableOwnerScript(table, this.__sourceSchema.tables[table].owner));
             } else { //Table not exists on target database, then generate the script to create table
                 actionLabel = 'CREATE';
 
-                this.__tempScripts.push(sql.generateCreateTableScript(table, sourceTables[table]));
+                this.__tempScripts.push(sql.generateCreateTableScript(table, this.__sourceSchema.tables[table]));
             }
@@ -73,6 +80,6 @@
     },
-    __compareTableColumns: function(table, sourceTableColumns, targetTableColumns) {
+    __compareTableColumns: function(table, sourceTableColumns, targetTableColumns, targetTableConstraints, targetTableIndexes) {
         for (let column in sourceTableColumns) { //Get new or changed columns
             if (targetTableColumns[column]) { //Table column exists on both database, then compare column schema
-                this.__compareTableColumn(table, column, sourceTableColumns[column], targetTableColumns[column]);
+                this.__compareTableColumn(table, column, sourceTableColumns[column], targetTableColumns[column], targetTableConstraints, targetTableIndexes);
             } else { //Table column not exists on target database, then generate script to add column
@@ -87,3 +94,3 @@ this.__tempScripts.push(sql.generateAddTableColumnScript(table, column, sourceTableColumns[column]));
     },
-    __compareTableColumn: function(table, column, sourceTableColumn, targetTableColumn) {
+    __compareTableColumn: function(table, column, sourceTableColumn, targetTableColumn, targetTableConstraints, targetTableIndexes) {
         let changes = {};
@@ -98,2 +105,4 @@
             changes.datatype = sourceTableColumn.datatype;
+            changes.dataTypeID = sourceTableColumn.dataTypeID;
+            changes.dataTypeCategory = sourceTableColumn.dataTypeCategory;
             changes.precision = sourceTableColumn.precision;
@@ -106,5 +115,67 @@ changes.scale = sourceTableColumn.scale;
 
-        if (Object.keys(changes).length > 0)
+        if (sourceTableColumn.identity != targetTableColumn.identity) {
+            changes.identity = sourceTableColumn.identity;
+
+            if (targetTableColumn.identity == null)
+                changes.isNewIdentity = true;
+            else
+                changes.isNewIdentity = false;
+        }
+
+        if (Object.keys(changes).length > 0) {
+            let rawColumnName = column.substring(1).slice(0, -1);
+
+            //Check if the column is under constrains
+            for (let constraint in targetTableConstraints) {
+                if (this.__droppedConstraints.includes(constraint))
+                    continue;
+
+                let constraintDefinition = targetTableConstraints[constraint].definition;
+                let serachStartingIndex = constraintDefinition.indexOf('(');
+
+                if (constraintDefinition.includes(`${rawColumnName},`, serachStartingIndex) ||
+                    constraintDefinition.includes(`${rawColumnName})`, serachStartingIndex) ||
+                    constraintDefinition.includes(`${column}`, serachStartingIndex)) {
+                    this.__tempScripts.push(sql.generateDropTableConstraintScript(table, constraint));
+                    this.__droppedConstraints.push(constraint);
+                }
+            }
+
+            //Check if the column is part of indexes
+            for (let index in targetTableIndexes) {
+                let indexDefinition = targetTableIndexes[index].definition;
+                let serachStartingIndex = indexDefinition.indexOf('(');
+
+                if (indexDefinition.includes(`${rawColumnName},`, serachStartingIndex) ||
+                    indexDefinition.includes(`${rawColumnName})`, serachStartingIndex) ||
+                    indexDefinition.includes(`${column}`, serachStartingIndex)) {
+                    this.__tempScripts.push(sql.generateDropIndexScript(index))
+                    this.__droppedIndexes.push(index);
+                }
+            }
+
+            //Check if the column is used into view
+            for (let view in this.__targetSchema.views) {
+                this.__targetSchema.views[view].dependencies.forEach(dependency => {
+                    let fullDependencyName = `"${dependency.schemaName}"."${dependency.tableName}"`;
+                    if (fullDependencyName == table && dependency.columnName == column) {
+                        this.__tempScripts.push(sql.generateDropViewScript(index))
+                        this.__droppedViews.push(view);
+                    }
+                });
+            }
+
+            //Check if the column is used into materialized view
+            for (let view in this.__targetSchema.materializedViews) {
+                this.__targetSchema.materializedViews[view].dependencies.forEach(dependency => {
+                    let fullDependencyName = `"${dependency.schemaName}"."${dependency.tableName}"`;
+                    if (fullDependencyName == table && dependency.columnName == column) {
+                        this.__tempScripts.push(sql.generateDropMaterializedViewScript(index))
+                        this.__droppedViews.push(view);
+                    }
+                });
+            }
+
             this.__tempScripts.push(sql.generateChangeTableColumnScript(table, column, changes));
-
+        }
     },
@@ -114,4 +185,10 @@ __compareTableConstraints: function(table, sourceTableConstraints, targetTableConstraints) {
             if (targetTableConstraints[constraint]) { //Table constraint exists on both database, then compare column schema
-                if (sourceTableConstraints[constraint] != targetTableConstraints[constraint])
-                    this.__tempScripts.push(sql.generateChangeTableConstraintScript(table, constraint, sourceTableConstraints[constraint]));
+                if (sourceTableConstraints[constraint].definition != targetTableConstraints[constraint].definition) {
+                    if (!this.__droppedConstraints.includes(constraint))
+                        this.__tempScripts.push(sql.generateDropTableConstraintScript(table, constraint));
+                    this.__tempScripts.push(sql.generateAddTableConstraintScript(table, constraint, sourceTableConstraints[constraint]));
+                } else {
+                    if (this.__droppedConstraints.includes(constraint)) //It will recreate a dropped constraints because changes happens on involved columns
+                        this.__tempScripts.push(sql.generateAddTableConstraintScript(table, constraint, sourceTableConstraints[constraint]));
+                }
             } else { //Table constraint not exists on target database, then generate script to add constraint
@@ -122,4 +199,4 @@ this.__tempScripts.push(sql.generateAddTableConstraintScript(table, constraint, sourceTableConstraints[constraint]));
         for (let constraint in targetTableConstraints) { //Get dropped constraints
-            if (!sourceTableConstraints[constraint]) //Table constraint not exists on source, then generate script to drop constraint
-                this.__tempScripts.push(sql.generateDropTableConstraintScript(table, constraint))
+            if (!sourceTableConstraints[constraint] && !this.__droppedConstraints.includes(constraint)) //Table constraint not exists on source, then generate script to drop constraint
+                this.__tempScripts.push(sql.generateDropTableConstraintScript(table, constraint));
         }
@@ -130,4 +207,10 @@ },
             if (targetTableIndexes[index]) { //Table index exists on both database, then compare index definition
-                if (sourceTableIndexes[index] != targetTableIndexes[index])
-                    this.__tempScripts.push(sql.generateChangeIndexScript(index, sourceTableIndexes[index].definition));
+                if (sourceTableIndexes[index].definition != targetTableIndexes[index].definition) {
+                    if (!this.__droppedIndexes.includes(index))
+                        this.__tempScripts.push(sql.generateDropIndexScript(index));
+                    this.__tempScripts.push(`\n${sourceTableIndexes[index].definition};\n`);
+                } else {
+                    if (this.__droppedIndexes.includes(index)) //It will recreate a dropped index because changes happens on involved columns
+                        this.__tempScripts.push(`\n${sourceTableIndexes[index].definition};\n`);
+                }
             } else { //Table index not exists on target database, then generate script to add index                
@@ -138,3 +221,3 @@ this.__tempScripts.push(`\n${sourceTableIndexes[index].definition};\n`);
         for (let index in targetTableIndexes) { //Get dropped indexes
-            if (!sourceTableIndexes[index]) //Table index not exists on source, then generate script to drop index
+            if (!sourceTableIndexes[index] && !this.__droppedIndexes.includes(index)) //Table index not exists on source, then generate script to drop index
                 this.__tempScripts.push(sql.generateDropIndexScript(index))
@@ -176,7 +259,7 @@ }
     },
-    __compareViews: function(sourceViews, targetViews) {
+    __compareViews: function() {
         this.__updateProgressbar(this.__progressBarValue + 0.0001, 'Comparing views');
-        const progressBarStep = 0.1999 / Object.keys(sourceViews).length;
+        const progressBarStep = 0.1999 / Object.keys(this.__sourceSchema.views).length;
 
-        for (let view in sourceViews) { //Get new or changed views
+        for (let view in this.__sourceSchema.views) { //Get new or changed views
             this.__updateProgressbar(this.__progressBarValue + progressBarStep, `Comparing VIEW ${view}`);
@@ -186,11 +269,16 @@ this.__tempScripts = [];
 
-            if (targetViews[view]) { //View exists on both database, then compare view schema                 
+            if (this.__targetSchema.views[view]) { //View exists on both database, then compare view schema                 
                 actionLabel = 'ALTER';
 
-                if (sourceViews[view].definition != targetViews[view].definition)
-                    this.__tempScripts.push(sql.generateChangeViewScript(view, sourceViews[view]));
-                else {
-                    this.__compareTablePrivileges(view, sourceViews[view].privileges, targetViews[view].privileges);
-                    if (sourceViews[view].owner != targetViews[view].owner)
-                        this.__tempScripts.push(sql.generateChangeTableOwnerScript(view, sourceViews[view].owner));
+                if (this.__sourceSchema.views[view].definition != this.__targetSchema.views[view].definition) {
+                    if (!this.__droppedViews.includes(view))
+                        this.__tempScripts.push(sql.generateDropViewScript(view));
+                    this.__tempScripts.push(sql.generateCreateViewScript(view, this.__sourceSchema.views[view]));
+                } else {
+                    if (this.__droppedViews.includes(view)) //It will recreate a dropped view because changes happens on involved columns
+                        this.__tempScripts.push(sql.generateCreateViewScript(view, this.__sourceSchema.views[view]));
+
+                    this.__compareTablePrivileges(view, this.__sourceSchema.views[view].privileges, this.__targetSchema.views[view].privileges);
+                    if (this.__sourceSchema.views[view].owner != this.__targetSchema.views[view].owner)
+                        this.__tempScripts.push(sql.generateChangeTableOwnerScript(view, this.__sourceSchema.views[view].owner));
                 }
@@ -200,3 +288,3 @@ } else { //View not exists on target database, then generate the script to create view
 
-                this.__tempScripts.push(sql.generateCreateViewScript(view, sourceViews[view]));
+                this.__tempScripts.push(sql.generateCreateViewScript(view, this.__sourceSchema.views[view]));
             }
@@ -207,7 +295,7 @@
     },
-    __compareMaterializedViews: function(sourceMaterializedViews, targetMaterializedViews) {
+    __compareMaterializedViews: function() {
         this.__updateProgressbar(this.__progressBarValue + 0.0001, 'Comparing materialized views');
-        const progressBarStep = 0.1999 / Object.keys(sourceMaterializedViews).length;
+        const progressBarStep = 0.1999 / Object.keys(this.__sourceSchema.materializedViews).length;
 
-        for (let view in sourceMaterializedViews) { //Get new or changed materialized views
+        for (let view in this.__sourceSchema.materializedViews) { //Get new or changed materialized views
             this.__updateProgressbar(this.__progressBarValue + progressBarStep, `Comparing MATERIALIZED VIEW ${view}`);
@@ -217,12 +305,17 @@ this.__tempScripts = [];
 
-            if (targetMaterializedViews[view]) { //Materialized view exists on both database, then compare materialized view schema     
+            if (this.__targetSchema.materializedViews[view]) { //Materialized view exists on both database, then compare materialized view schema     
                 actionLabel = 'ALTER';
 
-                if (sourceMaterializedViews[view].definition != targetMaterializedViews[view].definition)
-                    this.__tempScripts.push(sql.generateChangeMaterializedViewScript(view, sourceMaterializedViews[view]));
-                else {
-                    this.__compareTableIndexes(sourceMaterializedViews[view].indexes, targetMaterializedViews[view].indexes);
-                    this.__compareTablePrivileges(view, sourceMaterializedViews[view].privileges, targetMaterializedViews[view].privileges);
-                    if (sourceMaterializedViews[view].owner != targetMaterializedViews[view].owner)
-                        this.__tempScripts.push(sql.generateChangeTableOwnerScript(view, sourceMaterializedViews[view].owner));
+                if (this.__sourceSchema.materializedViews[view].definition != this.__targetSchema.materializedViews[view].definition) {
+                    if (!this.__droppedViews.includes(view))
+                        this.__tempScripts.push(sql.generateDropMaterializedViewScript(view));
+                    this.__tempScripts.push(sql.generateCreateMaterializedViewScript(view, this.__sourceSchema.materializedViews[view]));
+                } else {
+                    if (this.__droppedViews.includes(view)) //It will recreate a dropped materialized view because changes happens on involved columns
+                        this.__tempScripts.push(sql.generateCreateMaterializedViewScript(view, this.__sourceSchema.views[view]));
+
+                    this.__compareTableIndexes(this.__sourceSchema.materializedViews[view].indexes, this.__targetSchema.materializedViews[view].indexes);
+                    this.__compareTablePrivileges(view, this.__sourceSchema.materializedViews[view].privileges, this.__targetSchema.materializedViews[view].privileges);
+                    if (this.__sourceSchema.materializedViews[view].owner != this.__targetSchema.materializedViews[view].owner)
+                        this.__tempScripts.push(sql.generateChangeTableOwnerScript(view, this.__sourceSchema.materializedViews[view].owner));
                 }
@@ -232,3 +325,3 @@ } else { //Materialized view not exists on target database, then generate the script to create materialized view
 
-                this.__tempScripts.push(sql.generateCreateMaterializedViewScript(view, sourceMaterializedViews[view]));
+                this.__tempScripts.push(sql.generateCreateMaterializedViewScript(view, this.__sourceSchema.materializedViews[view]));
             }
@@ -239,7 +332,7 @@
     },
-    __compareProcedures: function(sourceProcedures, targetProcedures) {
+    __compareProcedures: function(targetProcedures) {
         this.__updateProgressbar(this.__progressBarValue + 0.0001, 'Comparing functions');
-        const progressBarStep = 0.1999 / Object.keys(sourceProcedures).length;
+        const progressBarStep = 0.1999 / Object.keys(this.__sourceSchema.functions).length;
 
-        for (let procedure in sourceProcedures) { //Get new or changed procedures
+        for (let procedure in this.__sourceSchema.functions) { //Get new or changed procedures
             this.__updateProgressbar(this.__progressBarValue + progressBarStep, `Comparing FUNCTION ${procedure}`);
@@ -249,11 +342,11 @@ this.__tempScripts = [];
 
-            if (targetProcedures[procedure]) { //Procedure exists on both database, then compare procedure definition                     
+            if (this.__targetSchema.functions[procedure]) { //Procedure exists on both database, then compare procedure definition                     
                 actionLabel = 'ALTER';
 
-                if (sourceProcedures[procedure].definition != targetProcedures[procedure].definition) {
-                    this.__tempScripts.push(sql.generateChangeProcedureScript(procedure, sourceProcedures[procedure]));
+                if (this.__sourceSchema.functions[procedure].definition != this.__targetSchema.functions[procedure].definition) {
+                    this.__tempScripts.push(sql.generateChangeProcedureScript(procedure, this.__sourceSchema.functions[procedure]));
                 } else {
-                    this.__compareProcedurePrivileges(procedure, sourceProcedures[procedure].argTypes, sourceProcedures[procedure].privileges, targetProcedures[procedure].privileges);
-                    if (sourceProcedures[procedure].owner != targetProcedures[procedure].owner)
-                        this.__tempScripts.push(sql.generateChangeProcedureOwnerScript(procedure, sourceProcedures[procedure].argTypes, sourceViews[view].owner));
+                    this.__compareProcedurePrivileges(procedure, this.__sourceSchema.functions[procedure].argTypes, this.__sourceSchema.functions[procedure].privileges, this.__targetSchema.functions[procedure].privileges);
+                    if (this.__sourceSchema.functions[procedure].owner != this.__targetSchema.functions[procedure].owner)
+                        this.__tempScripts.push(sql.generateChangeProcedureOwnerScript(procedure, this.__sourceSchema.functions[procedure].argTypes, sourceViews[view].owner));
                 }
@@ -263,3 +356,3 @@ } else { //Procedure not exists on target database, then generate the script to create procedure
 
-                this.__tempScripts.push(sql.generateCreateProcedureScript(procedure, sourceProcedures[procedure]));
+                this.__tempScripts.push(sql.generateCreateProcedureScript(procedure, this.__sourceSchema.functions[procedure]));
             }
@@ -287,8 +380,11 @@
 
-        this.__compareSchemas(sourceSchema.schemas, targetSchema.schemas);
-        this.__compareTables(sourceSchema.tables, targetSchema.tables);
-        this.__compareViews(sourceSchema.views, targetSchema.views);
-        this.__compareMaterializedViews(sourceSchema.materializedViews, targetSchema.materializedViews);
-        this.__compareProcedures(sourceSchema.functions, targetSchema.functions);
+        this.__sourceSchema = sourceSchema;
+        this.__targetSchema = targetSchema;
 
+        this.__compareSchemas();
+        this.__compareTables();
+        this.__compareViews();
+        this.__compareMaterializedViews();
+        this.__compareProcedures();
+
         this.__updateProgressbar(1.0, 'Database objects compared!');
@@ -295,0 +391,0 @@

src/retrieveSchema.js

@@ -16,10 +16,10 @@ const Exception = require('./error')
     "getTableColumns": function(tableName) {
-        return `SELECT a.attname, a.attnotnull, t.typname, ad.adsrc,
+        return `SELECT a.attname, a.attnotnull, t.typname, t.oid as typeid, t.typcategory, ad.adsrc, a.attidentity,
                 CASE 
-                    WHEN t.typname = 'numeric' THEN (a.atttypmod-4) >> 16
-                    WHEN t.typname = 'bpchar' or t.typname = 'varchar' THEN a.atttypmod-4
+                    WHEN t.typname = 'numeric' AND a.atttypmod > 0 THEN (a.atttypmod-4) >> 16
+                    WHEN (t.typname = 'bpchar' or t.typname = 'varchar') AND a.atttypmod > 0 THEN a.atttypmod-4
                     ELSE null
                 END AS precision,
                 CASE
-                    WHEN t.typname = 'numeric' THEN (a.atttypmod-4) & 65535
+                    WHEN t.typname = 'numeric' AND a.atttypmod > 0 THEN (a.atttypmod-4) & 65535
                     ELSE null
@@ -86,2 +86,14 @@ END AS scale
     },
+    "getViewDependencies": function(schemaName, viewName) {
+        return `SELECT                 
+                n.nspname AS schemaname,
+                c.relname AS tablename,
+                a.attname AS columnname
+                FROM pg_rewrite AS r
+                INNER JOIN pg_depend AS d ON r.oid=d.objid
+                INNER JOIN pg_attribute a ON a.attnum = d.refobjsubid AND a.attrelid = d.refobjid AND a.attisdropped = false
+                INNER JOIN pg_class c ON c.oid = d.refobjid
+                INNER JOIN pg_namespace n ON n.oid = c.relnamespace                
+                WHERE r.ev_class='"${schemaName}"."${viewName}"'::regclass::oid AND d.refobjid <> '"${schemaName}"."${viewName}"'::regclass::oid`
+    },
     "getFunctions": function(schemas) {
@@ -113,3 +125,2 @@ return `SELECT p.proname, n.nspname, pg_get_functiondef(p.oid) as definition, p.proowner::regrole::name as owner, oidvectortypes(proargtypes) as argtypes
             try {
-
                 helper.__updateProgressbar(0.0, 'Collecting database objects ...');
@@ -144,3 +155,3 @@
         //Get schemas
-        const namespaces = await client.query(query.getSchemas(schemas))
+        const namespaces = await client.query(query.getSchemas(schemas));
         const progressBarStep = 0.1999 / namespaces.rows.length;
@@ -165,7 +176,5 @@
         const tables = await client.query(query.getTables(schemas))
-        const progressBarStep = 0.1999 / tables.rows.length;
+        const progressBarStep = (0.1999 / tables.rows.length) / 5.0;
 
         await Promise.all(tables.rows.map(async(table) => {
-            const progressBarSubStep = progressBarStep / 5;
-
             let fullTableName = `"${table.schemaname}"."${table.tablename}"`;
@@ -181,3 +190,3 @@ result[fullTableName] = {
 
-            helper.__updateProgressbar(helper.__progressBarValue + progressBarSubStep, `Collecting COLUMNS for table ${fullTableName}`);
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting COLUMNS for table ${fullTableName}`);
 
@@ -188,15 +197,36 @@ //Get table columns
                 let columnName = `"${column.attname}"`;
-                let isAutoIncrement = (column.adsrc && column.adsrc.startsWith('nextval') && column.adsrc.includes('_seq')) || false;
-                let defaultValue = isAutoIncrement ? '' : column.adsrc
-                let dataType = isAutoIncrement ? 'serial' : column.typname
+                let columnIdentity = null;
+                let defaultValue = column.adsrc;
+                let dataType = column.typname;
+
+                switch (column.attidentity) {
+                    case 'a':
+                        columnIdentity = 'ALWAYS';
+                        defaultValue = '';
+                        break;
+                    case 'd':
+                        columnIdentity = 'BY DEFAULT';
+                        defaultValue = '';
+                        break;
+                    default:
+                        if (column.adsrc && column.adsrc.startsWith('nextval') && column.adsrc.includes('_seq')) {
+                            defaultValue = '';
+                            dataType = 'serial';
+                        };
+                        break;
+                }
+
                 result[fullTableName].columns[columnName] = {
                     nullable: !column.attnotnull,
                     datatype: dataType,
+                    dataTypeID: column.typeid,
+                    dataTypeCategory: column.typcategory,
                     default: defaultValue,
                     precision: column.precision,
-                    scale: column.scale
+                    scale: column.scale,
+                    identity: columnIdentity
                 }
             });
 
-            helper.__updateProgressbar(helper.__progressBarValue + progressBarSubStep, `Collecting CONSTRAINTS for table ${fullTableName}`);
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting CONSTRAINTS for table ${fullTableName}`);
 
@@ -213,3 +243,3 @@ //Get table constraints
 
-            helper.__updateProgressbar(helper.__progressBarValue + progressBarSubStep, `Collecting OPTIONS for table ${fullTableName}`);
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting OPTIONS for table ${fullTableName}`);
 
@@ -224,3 +254,3 @@ //Get table options
 
-            helper.__updateProgressbar(helper.__progressBarValue + progressBarSubStep, `Collecting INDEXES for table ${fullTableName}`);
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting INDEXES for table ${fullTableName}`);
 
@@ -235,3 +265,3 @@ //Get table indexes
 
-            helper.__updateProgressbar(helper.__progressBarValue + progressBarSubStep, `Collecting PRIVILEGES for table ${fullTableName}`);
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting PRIVILEGES for table ${fullTableName}`);
 
@@ -268,3 +298,3 @@ //Get table privileges
         const views = await client.query(query.getViews(schemas))
-        const progressBarStep = 0.1999 / views.rows.length;
+        const progressBarStep = (0.1999 / views.rows.length) / 2.0;
 
@@ -276,3 +306,4 @@ await Promise.all(views.rows.map(async(view) => {
                 owner: view.viewowner,
-                privileges: {}
+                privileges: {},
+                dependencies: []
             };
@@ -283,3 +314,3 @@
             //Get view privileges
-            let privileges = await client.query(query.getViewPrivileges(view.schemaname, view.viewname))
+            let privileges = await client.query(query.getViewPrivileges(view.schemaname, view.viewname));
             privileges.rows.forEach(privilege => {
@@ -296,2 +327,14 @@ result[fullViewName].privileges[privilege.usename] = {
             });
+
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting DEPENDENCIES for view ${fullViewName}`);
+
+            //Get view dependencies
+            let dependencies = await client.query(query.getViewDependencies(view.schemaname, view.viewname));
+            dependencies.rows.forEach(dependency => {
+                result[fullViewName].dependencies.push({
+                    schemaName: dependency.schemaname,
+                    tableName: dependency.tablename,
+                    columnName: dependency.columnname
+                });
+            })
         }));
@@ -312,7 +355,5 @@
         const views = await client.query(query.getMaterializedViews(schemas))
-        const progressBarStep = 0.1999 / views.rows.length;
+        const progressBarStep = (0.1999 / views.rows.length) / 3.0;
 
         await Promise.all(views.rows.map(async(view) => {
-            const progressBarSubStep = progressBarStep / 2;
-
             let fullViewName = `"${view.schemaname}"."${view.matviewname}"`;
@@ -323,6 +364,7 @@ result[fullViewName] = {
                 owner: view.matviewowner,
-                privileges: {}
+                privileges: {},
+                dependencies: []
             };
 
-            helper.__updateProgressbar(helper.__progressBarValue + progressBarSubStep, `Collecting INDEXES for materialized view ${fullViewName}`);
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting INDEXES for materialized view ${fullViewName}`);
 
@@ -337,3 +379,3 @@ //Get view indexes
 
-            helper.__updateProgressbar(helper.__progressBarValue + progressBarSubStep, `Collecting PRIVILEGES for materialized view ${fullViewName}`);
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting PRIVILEGES for materialized view ${fullViewName}`);
 
@@ -353,2 +395,14 @@ //Get view privileges
             });
+
+            helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting DEPENDENCIES for materialized view ${fullViewName}`);
+
+            //Get view dependencies
+            let dependencies = await client.query(query.getViewDependencies(view.schemaname, view.matviewname));
+            dependencies.rows.forEach(dependency => {
+                result[fullViewName].dependencies.push({
+                    schemaName: dependency.schemaname,
+                    tableName: dependency.tablename,
+                    columnName: dependency.columnname
+                });
+            })
         }));
@@ -355,0 +409,0 @@

src/sqlScriptGenerator.js

 const hints = {
     addColumnNotNullableWithoutDefaultValue: " --WARN: Add a new column not nullable without a default value can occure in a sql error during execution!",
-    changeColumnDataType: " --WARN: Change column data type can occure in a auto-casting sql error during execution, is recommended to use the keyword USING to include a casting logic!",
+    changeColumnDataType: " --WARN: Change column data type can occure in a casting error, the suggested casting expression is the default one and may not fit your needs!",
     dropColumn: " --WARN: Drop column can occure in data loss!",
-    potentialRoleMissing: " --WARN: Grant\\Revoke privileges to a role can occure in a sql error during execution if role is missing to the target database!"
+    potentialRoleMissing: " --WARN: Grant\\Revoke privileges to a role can occure in a sql error during execution if role is missing to the target database!",
+    identityColumnDetected: " --WARN: Identity column has been detected, an error can occure because constraints violation!"
 }
@@ -23,5 +24,9 @@
 
+        let identityValue = '';
+        if (columnSchema.identity)
+            identityValue = `GENERATED ${columnSchema.identity} AS IDENTITY`;
+
         let dataType = this.__generateColumnDataTypeDefinition(columnSchema);
 
-        return `${column} ${dataType} ${columnSchema.nullable?'NULL':'NOT NULL'} ${defaultValue}`
+        return `${column} ${dataType} ${columnSchema.nullable?'NULL':'NOT NULL'} ${defaultValue} ${identityValue}`;
     },
@@ -63,3 +68,3 @@ __generateTableGrantsDefinition: function(table, role, privileges) {
     generateCreateSchemaScript: function(schema, owner) {
-        let script = `\nCREATE ${global.config.options.idempotent?'SCHEMA IF NOT EXISTS':'SCHEMA'} ${schema} AUTHORIZATION ${owner};\n`;
+        let script = `\nCREATE ${global.config.options.schemaCompare.idempotentScript?'SCHEMA IF NOT EXISTS':'SCHEMA'} ${schema} AUTHORIZATION ${owner};\n`;
         //console.log(script);
@@ -87,3 +92,3 @@ return script;
             let definition = schema.indexes[index].definition;
-            if (global.config.options.idempotent) {
+            if (global.config.options.schemaCompare.idempotentScript) {
                 definition = definition.replace('CREATE INDEX', 'CREATE INDEX IF NOT EXISTS');
@@ -98,3 +103,3 @@ definition = definition.replace('CREATE UNIQUE INDEX', 'CREATE UNIQUE INDEX IF NOT EXISTS');
         let privileges = [];
-        privileges.push(`ALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} OWNER TO ${schema.owner};\n`);
+        privileges.push(`ALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table} OWNER TO ${schema.owner};\n`);
         for (let role in schema.privileges) {
@@ -104,8 +109,8 @@ privileges = privileges.concat(this.__generateTableGrantsDefinition(table, role, schema.privileges[role]))
 
-        let script = `\nCREATE ${global.config.options.idempotent?'TABLE IF NOT EXISTS ':'TABLE'} ${table} (\n\t${columns.join(',\n\t')}\n)\n${options};\n${indexes.join('\n')}\n${privileges.join('\n')}\n`
-            //console.log(script)
+        let script = `\nCREATE ${global.config.options.schemaCompare.idempotentScript?'TABLE IF NOT EXISTS':'TABLE'} ${table} (\n\t${columns.join(',\n\t')}\n)\n${options};\n${indexes.join('\n')}\n${privileges.join('\n')}\n`;
+        //console.log(script)
         return script;
     },
     generateAddTableColumnScript: function(table, column, schema) {
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} ADD ${global.config.options.idempotent?'COLUMN IF NOT EXISTS':'COLUMN'} ${this.__generateColumnDefinition(column, schema)};`
+        let script = `\nALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table} ADD ${global.config.options.schemaCompare.idempotentScript?'COLUMN IF NOT EXISTS':'COLUMN'} ${this.__generateColumnDefinition(column, schema)};`
         if (script.includes('NOT NULL') && !script.includes('DEFAULT'))
@@ -124,4 +129,5 @@ script += hints.addColumnNotNullableWithoutDefaultValue;
         if (changes.hasOwnProperty('datatype')) {
-            definitions.push(`${hints.changeColumnDataType}`)
-            definitions.push(`ALTER COLUMN ${column} SET DATA TYPE ${this.__generateColumnDataTypeDefinition(changes)}`);
+            definitions.push(`${hints.changeColumnDataType}`);
+            let dataTypeDefinition = this.__generateColumnDataTypeDefinition(changes);
+            definitions.push(`ALTER COLUMN ${column} SET DATA TYPE ${dataTypeDefinition} USING ${column}::${dataTypeDefinition}`);
         }
@@ -133,4 +139,14 @@
 
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table}\n\t${definitions.join(',\n\t')};\n`
+        if (changes.hasOwnProperty('identity') && changes.hasOwnProperty('isNewIdentity')) {
+            let identityDefinition = '';
+            if (changes.identity) { //truly values
+                identityDefinition = `${changes.isNewIdentity?'ADD':'SET'} GENERATED ${changes.identity} ${changes.isNewIdentity?'AS IDENTITY':''}`;
+            } else { //falsy values
+                identityDefinition = `DROP IDENTITY ${global.config.options.schemaCompare.idempotentScript?'IF EXISTS':''}`;
+            }
+            definitions.push(`ALTER COLUMN ${column} ${identityDefinition}`);
+        }
 
+        let script = `\nALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table}\n\t${definitions.join(',\n\t')};\n`
+
         //console.log(script);
@@ -143,3 +159,3 @@
     generateDropTableColumnScript: function(table, column) {
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} DROP ${global.config.options.idempotent?'COLUMN IF EXISTS':'COLUMN'} ${column} CASCADE;${hints.dropColumn}\n`;
+        let script = `\nALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table} DROP ${global.config.options.schemaCompare.idempotentScript?'COLUMN IF EXISTS':'COLUMN'} ${column} CASCADE;${hints.dropColumn}\n`;
         //console.log(script);
@@ -149,13 +165,8 @@ return script;
     generateAddTableConstraintScript: function(table, constraint, schema) {
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} ADD CONSTRAINT ${constraint} ${schema.definition};\n`;
+        let script = `\nALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table} ADD CONSTRAINT ${constraint} ${schema.definition};\n`;
         //console.log(script);
         return script;
     },
-    generateChangeTableConstraintScript: function(table, constraint, schema) {
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} DROP ${global.config.options.idempotent?'CONSTRAINT IF EXISTS':'CONSTRAINT'} ${constraint}, ADD CONSTRAINT ${constraint} ${schema.definition};\n`;
-        //console.log(script);
-        return script;
-    },
     generateDropTableConstraintScript: function(table, constraint) {
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} DROP ${global.config.options.idempotent?'CONSTRAINT IF EXISTS':'CONSTRAINT'} ${constraint};\n`;
+        let script = `\nALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table} DROP ${global.config.options.schemaCompare.idempotentScript?'CONSTRAINT IF EXISTS':'CONSTRAINT'} ${constraint};\n`;
         //console.log(script);
@@ -165,3 +176,3 @@ return script;
     generateChangeTableOptionsScript: function(table, options) {
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} SET ${options.withOids?'WITH':'WITHOUT'} OIDS;\n`;
+        let script = `\nALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table} SET ${options.withOids?'WITH':'WITHOUT'} OIDS;\n`;
         //console.log(script);
@@ -171,3 +182,3 @@ return script;
     generateChangeIndexScript: function(index, definition) {
-        let script = `\nDROP ${global.config.options.idempotent?'INDEX IF EXISTS':'INDEX'} ${index};\n${definition};\n`;
+        let script = `\nDROP ${global.config.options.schemaCompare.idempotentScript?'INDEX IF EXISTS':'INDEX'} ${index};\n${definition};\n`;
         //console.log(script);
@@ -177,3 +188,3 @@ return script;
     generateDropIndexScript: function(index) {
-        let script = `\nDROP ${global.config.options.idempotent?'INDEX IF EXISTS':'INDEX'} ${index};\n`;
+        let script = `\nDROP ${global.config.options.schemaCompare.idempotentScript?'INDEX IF EXISTS':'INDEX'} ${index};\n`;
         //console.log(script);
@@ -216,3 +227,3 @@ return script;
     generateChangeTableOwnerScript: function(table, owner) {
-        let script = `\nALTER ${global.config.options.idempotent?'TABLE IF EXISTS':'TABLE'} ${table} OWNER TO ${owner};\n`;
+        let script = `\nALTER ${global.config.options.schemaCompare.idempotentScript?'TABLE IF EXISTS':'TABLE'} ${table} OWNER TO ${owner};\n`;
         //console.log(script);
@@ -224,3 +235,3 @@ return script;
         let privileges = [];
-        privileges.push(`ALTER ${global.config.options.idempotent?'VIEW IF EXISTS':'VIEW'} ${view} OWNER TO ${schema.owner};`);
+        privileges.push(`ALTER ${global.config.options.schemaCompare.idempotentScript?'VIEW IF EXISTS':'VIEW'} ${view} OWNER TO ${schema.owner};`);
         for (let role in schema.privileges) {
@@ -230,8 +241,8 @@ privileges = privileges.concat(this.__generateTableGrantsDefinition(view, role, schema.privileges[role]))
 
-        let script = `\nCREATE ${global.config.options.idempotent? 'OR REPLACE VIEW':'VIEW'} ${view} AS ${schema.definition}\n${privileges.join('\n')}\n`;
+        let script = `\nCREATE ${global.config.options.schemaCompare.idempotentScript? 'OR REPLACE VIEW':'VIEW'} ${view} AS ${schema.definition}\n${privileges.join('\n')}\n`;
         //console.log(script)
         return script;
     },
-    generateChangeViewScript: function(view, schema) {
-        let script = `\nDROP ${global.config.options.idempotent?'VIEW IF EXISTS':'VIEW'} ${view};\n${this.generateCreateViewScript(view,schema)}`;
+    generateDropViewScript: function(view) {
+        let script = `\nDROP ${global.config.options.schemaCompare.idempotentScript?'VIEW IF EXISTS':'VIEW'} ${view};`;
         //console.log(script)
@@ -250,3 +261,3 @@ return script;
         let privileges = [];
-        privileges.push(`ALTER ${global.config.options.idempotent?'MATERIALIZED VIEW IF EXISTS':'MATERIALIZED VIEW'} ${view} OWNER TO ${schema.owner};\n`);
+        privileges.push(`ALTER ${global.config.options.schemaCompare.idempotentScript?'MATERIALIZED VIEW IF EXISTS':'MATERIALIZED VIEW'} ${view} OWNER TO ${schema.owner};\n`);
         for (let role in schema.privileges) {
@@ -256,8 +267,8 @@ privileges = privileges.concat(this.__generateTableGrantsDefinition(view, role, schema.privileges[role]))
 
-        let script = `\nCREATE ${global.config.options.idempotent?'MATERIALIZED VIEW IF NOT EXISTS':'MATERIALIZED VIEW'} ${view} AS ${schema.definition}\n${indexes.join('\n')}\n${privileges.join('\n')}\n`;
+        let script = `\nCREATE ${global.config.options.schemaCompare.idempotentScript?'MATERIALIZED VIEW IF NOT EXISTS':'MATERIALIZED VIEW'} ${view} AS ${schema.definition}\n${indexes.join('\n')}\n${privileges.join('\n')}\n`;
         //console.log(script)
         return script;
     },
-    generateChangeMaterializedViewScript: function(view, schema) {
-        let script = `\nDROP ${global.config.options.idempotent?'MATERIALIZED VIEW IF EXISTS':'MATERIALIZED VIEW'} ${view};\n${this.generateCreateMaterializedViewScript(view,schema)}`;
+    generateDropMaterializedViewScript: function(view) {
+        let script = `\nDROP ${global.config.options.schemaCompare.idempotentScript?'MATERIALIZED VIEW IF EXISTS':'MATERIALIZED VIEW'} ${view};`;
         //console.log(script)
@@ -279,3 +290,3 @@ return script;
     generateChangeProcedureScript: function(procedure, schema) {
-        let script = `\nDROP ${global.config.options.idempotent?'FUNCTION IF EXISTS':'FUNCTION'} ${procedure}(${schema.argTypes});\n${this.generateCreateProcedureScript(procedure,schema)}`;
+        let script = `\nDROP ${global.config.options.schemaCompare.idempotentScript?'FUNCTION IF EXISTS':'FUNCTION'} ${procedure}(${schema.argTypes});\n${this.generateCreateProcedureScript(procedure,schema)}`;
         //console.log(script)
@@ -304,3 +315,3 @@ return script;
     },
-    generateUpdateTableRecordScript: function(table, fields, keyFieldsMap, changes) {
+    generateUpdateTableRecordScript: function(table, fields, filterConditions, changes) {
         let updates = [];
@@ -312,4 +323,4 @@ for (let field in changes) {
         let conditions = [];
-        for (let condition in keyFieldsMap) {
-            conditions.push(`"${condition}" = ${this.__generateSqlFormattedValue(condition, fields, keyFieldsMap[condition])}`);
+        for (let condition in filterConditions) {
+            conditions.push(`"${condition}" = ${this.__generateSqlFormattedValue(condition, fields, filterConditions[condition])}`);
         }
@@ -320,3 +331,3 @@
     },
-    generateInsertTableRecordScript: function(table, record, fields) {
+    generateInsertTableRecordScript: function(table, record, fields, isIdentityUserValuesAllowed) {
         let fieldNames = [];
@@ -329,3 +340,5 @@ let fieldValues = [];
 
-        let script = `\nINSERT INTO ${table} (${fieldNames.join(', ')}) VALUES (${fieldValues.join(', ')});\n`;
+        let script = `\nINSERT INTO ${table} (${fieldNames.join(', ')}) ${isIdentityUserValuesAllowed?'': 'OVERRIDING SYSTEM VALUE'} VALUES (${fieldValues.join(', ')});\n`;
+        if (!isIdentityUserValuesAllowed)
+            script = `\n${hints.identityColumnDetected}` + script;
         return script;
@@ -343,2 +356,6 @@ },
     __generateSqlFormattedValue: function(fieldName, fields, value) {
+
+        if (value === null)
+            return 'NULL';
+
         let dataTypeId = fields.find((field) => {
@@ -373,2 +390,27 @@ return fieldName === field.name
         }
+    },
+    generateMergeTableRecord(table, fields, changes, options) {
+        let fieldNames = [];
+        let fieldValues = [];
+        let updates = [];
+        for (let field in changes) {
+            fieldNames.push(`"${field}"`);
+            fieldValues.push(this.__generateSqlFormattedValue(field, fields, changes[field]));
+            updates.push(`"${field}" = ${this.__generateSqlFormattedValue(field,fields,changes[field])}`);
+        }
+
+        let conflictDefinition = "";
+        if (options.constraintName)
+            conflictDefinition = `ON CONSTRAINT ${options.constraintName}`;
+        else if (options.uniqueFields && options.uniqueFields.length > 0)
+            conflictDefinition = `("${options.uniqueFields.join('", "')}")`;
+        else
+            throw new Error(`Impossible to generate conflict definition for table ${table} record to merge!`);
+
+        let script = `\nINSERT INTO ${table} (${fieldNames.join(', ')}) VALUES (${fieldValues.join(', ')})\nON CONFLICT ${conflictDefinition}\nDO UPDATE SET ${updates.join(', ')}`;
+        return script;
+    },
+    generateSetSequenceValueScript(tableName, sequence) {
+        let script = `\nSELECT setval('${sequence.seqname}', max("${sequence.attname}"), true) FROM ${tableName};\n`;
+        return script;
     }
@@ -375,0 +417,0 @@ }

src/retrieveData.js

-const Exception = require('./error')
-const { Progress } = require('clui');
-const chalk = require('chalk');
-
-var helper = {
-    __progressBar: new Progress(20),
-    __progressBarValue: 0.0,
-    __updateProgressbar: function(value, label) {
-        this.__progressBarValue = value;
-        process.stdout.clearLine();
-        process.stdout.cursorTo(0);
-        process.stdout.write(this.__progressBar.update(this.__progressBarValue) + ' - ' + chalk.whiteBright(label));
-    },
-    collectTablesRecords: function(client, tables) {
-        return new Promise(async(resolve, reject) => {
-            try {
-                helper.__updateProgressbar(0.0, 'Collecting tables records ...');
-                const progressBarStep = 1.0 / Object.keys(tables).length;
-
-                var tableRecords = {};
-                for (let table in tables) {
-                    helper.__updateProgressbar(helper.__progressBarValue + progressBarStep, `Collecting RECORDS for table ${table}`);
-
-                    tableRecords[table] = {
-                        records: [],
-                        exists: false
-                    };
-
-                    if (await helper.__checkIfTableExists(client, table, tables[table].schema)) {
-                        tableRecords[table].records = await helper.__collectTableRecords(client, table, tables[table]);
-                        tableRecords[table].exists = true;
-                    }
-                }
-
-                helper.__updateProgressbar(1.0, 'Table records collected!');
-
-                resolve(tableRecords);
-            } catch (e) {
-                reject(e);
-            }
-        });
-    },
-    __checkIfTableExists: async function(client, table, schema) {
-        let response = await client.query(`SELECT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = '${table}' AND schemaname = '${schema||'public'}')`);
-        return response.rows[0].exists;
-    },
-    __collectTableRecords: async function(client, table, config) {
-        let result = {
-            fields: null,
-            rows: null
-        };
-        let response = await client.query(`SELECT MD5(ROW(${config.keyFields.join(',')})::text) AS "rowHash", * FROM ${table}`);
-        result.fields = response.fields;
-        result.rows = response.rows;
-
-        return result;
-    }
-}
-
-module.exports = helper;

New alerts

Dynamic require

Supply chain risk

Dynamic require can indicate the package is performing dangerous or unsafe dynamic code execution.

Found 1 instance in 1 package

Filesystem access

Supply chain risk

Accesses the file system, and could potentially read sensitive data.

Found 1 instance in 1 package

Fixed alerts

Dynamic require

Supply chain risk

Dynamic require can indicate the package is performing dangerous or unsafe dynamic code execution.

Found 1 instance in 1 package

Filesystem access

Supply chain risk

Accesses the file system, and could potentially read sensitive data.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

105420

40.91%

Number of package files

15

15.38%

Lines of code

1835

43.02%

Worsened metrics

Dependency count

5

25%

Number of low supply chain risk alerts

3

50%

Dependency changes

• + Added

  line-by-line@^0.1.6

• + Added

  line-by-line@0.1.6
  (transitive)