@evs-chris/ts-pg-dao - npm Package Compare versions

Comparing version 0.9.0 to 0.10.0

package.json

 {
   "name": "@evs-chris/ts-pg-dao",
-  "version": "0.9.0",
+  "version": "0.10.0",
   "main": "runtime/main.js",
@@ -5,0 +5,0 @@ "typings": "runtime/main.d.ts",

runtime/cli.js

@@ -73,2 +73,4 @@ #!/usr/bin/env node
             res.functions = (await client.query(main_1.functionQuery)).rows;
+            res.indexes = (await client.query(main_1.indexQuery)).rows;
+            res.views = (await client.query(main_1.viewQuery)).rows;
         }
@@ -90,2 +92,4 @@ finally {
     .option('-f, --functions <list>', 'Only target the named function(s)', str => str.split(','))
+    .option('-i, --indexes <list>', 'Only target the named index(es)', str => str.split(','))
+    .option('-v, --views <list>', 'Only target the named view(s)', str => str.split(','))
     .option('-u, --update', 'Update schema cache from the database')
@@ -171,8 +175,32 @@ .option('-l, --list', 'List all the entries in the cache')
             }
+            if (cmd.indexes) {
+                cache.indexes = (cache.indexes || []).filter(i => {
+                    const found = cmd.indexes.includes(i.name);
+                    if (found)
+                        console.log(`Removing index "${i.schema}"."${i.name}"...`);
+                    return !found;
+                });
+            }
+            if (cmd.views) {
+                cache.views = (cache.views || []).filter(v => {
+                    const found = cmd.views.includes(v.name);
+                    if (found)
+                        console.log(`Removing view "${v.schema}"."${v.name}"...`);
+                    return !found;
+                });
+            }
         }
         if (cmd.update) {
+            if (!cache.tables)
+                cache.tables = [];
             if (!cache.functions)
                 cache.functions = [];
-            const tables = cmd.tables ? cache.tables.filter(t => cmd.tables.includes(t.name)) : cache.tables;
-            const functions = cmd.functions ? cache.functions.filter(f => cmd.functions.includes(f.name)) : cache.functions;
+            if (!cache.indexes)
+                cache.indexes = [];
+            if (!cache.views)
+                cache.views = [];
+            const tables = cmd.tables ? cache.tables.filter(t => cmd.tables.includes(t.name)) : (!cmd.functions && !cmd.indexes && !cmd.views) ? cache.tables : [];
+            const functions = cmd.functions ? cache.functions.filter(f => cmd.functions.includes(f.name)) : (!cmd.tables && !cmd.indexes && !cmd.views) ? cache.functions : [];
+            const indexes = cmd.indexes ? cache.indexes.filter(i => cmd.indexes.includes(i.name)) : (!cmd.tables && !cmd.functions && !cmd.views) ? cache.indexes : [];
+            const views = cmd.views ? cache.views.filter(v => cmd.views.includes(v.name)) : (!cmd.tables && !cmd.functions && !cmd.indexes) ? cache.views : [];
             const schema = await readSchema(connect);
@@ -199,2 +227,22 @@ // update current tables
             }
+            // update current indexes
+            for (const idx of indexes) {
+                const i = schema.indexes.find(i => i.schema === idx.schema && i.name === idx.name && i.table === idx.table);
+                if (i) {
+                    cache.indexes[cache.indexes.indexOf(idx)] = i;
+                    console.log(`Updating index "${i.schema}"."${i.name}"...`);
+                }
+                else
+                    console.log(`Index "${i.schema}"."${i.name}" not found in target database`);
+            }
+            // update current views
+            for (const view of views) {
+                const i = schema.views.find(i => i.schema === view.schema && i.name === view.name);
+                if (i) {
+                    cache.views[cache.views.indexOf(view)] = i;
+                    console.log(`Updating view "${i.schema}"."${i.name}"...`);
+                }
+                else
+                    console.log(`View "${i.schema}"."${i.name}" not found in target database`);
+            }
             // look for new tables
@@ -210,2 +258,3 @@ if (cmd.tables) {
             }
+            // look for new functions
             if (cmd.functions) {
@@ -222,2 +271,26 @@ for (const n of cmd.functions) {
             }
+            // look for new indexes
+            if (cmd.indexes) {
+                for (const n of cmd.indexes) {
+                    const idxs = schema.indexes.filter(i => i.name === n);
+                    for (const idx of idxs) {
+                        if (!cache.indexes.find(i => i.schema === idx.schema && i.name === idx.name && i.table === idx.table)) {
+                            console.log(`Adding index "${idx.schema}"."${idx.name}"...`);
+                            cache.indexes.push(idx);
+                        }
+                    }
+                }
+            }
+            // look for new views
+            if (cmd.views) {
+                for (const n of cmd.views) {
+                    const vs = schema.views.filter(i => i.name === n);
+                    for (const v of vs) {
+                        if (!cache.views.find(v => v.schema === v.schema && v.name === v.name)) {
+                            console.log(`Adding view "${v.schema}"."${v.name}"...`);
+                            cache.views.push(v);
+                        }
+                    }
+                }
+            }
             // check for *
@@ -240,2 +313,18 @@ if (cmd.tables && cmd.tables.length === 1 && cmd.tables[0] === '*') {
             }
+            if (cmd.indexes && cmd.indexes.length === 1 && cmd.indexes[0] === '*') {
+                for (const idx of schema.indexes || []) {
+                    if (!cache.indexes.find(i => i.schema === idx.schema && i.name === idx.name && i.table === idx.table)) {
+                        console.log(`Adding index "${idx.schema}"."${idx.name}...`);
+                        cache.indexes.push(idx);
+                    }
+                }
+            }
+            if (cmd.views && cmd.views.length === 1 && cmd.views[0] === '*') {
+                for (const view of schema.views || []) {
+                    if (!cache.views.find(v => v.schema === view.schema && v.name === view.name)) {
+                        console.log(`Adding view "${view.schema}"."${view.name}...`);
+                        cache.views.push(view);
+                    }
+                }
+            }
         }
@@ -250,2 +339,4 @@ if (cmd.update || cmd.remove) {
             });
+            cache.indexes.sort((l, r) => l.name < r.name ? -1 : l.name > r.name ? 1 : 0);
+            cache.views.sort((l, r) => l.name < r.name ? -1 : l.name > r.name ? 1 : 0);
             await fs.writeFile(config.schemaCacheFile, JSON.stringify(cache, null, ' '), 'utf8');
@@ -263,2 +354,4 @@ console.log(`Wrote ${config.schemaCacheFile}`);
     .option('-f, --functions <list>', 'Only target the named function(s)', str => str.split(','))
+    .option('-i, --indexes <list>', 'Only target the named index(es)', str => str.split(','))
+    .option('-v, --views <list>', 'Only target the named view(s)', str => str.split(','))
     .option('-H, --host <host>', 'Override the target connection host for the named config.')
@@ -305,2 +398,6 @@ .option('-U, --user <user>', 'Override the target connection user for the named config.')
                     opts.functions = cmd.functions;
+                if (cmd.indexes)
+                    opts.indexes = cmd.indexes;
+                if (cmd.views)
+                    opts.views = cmd.views;
             }
@@ -307,0 +404,0 @@ await patch_1.patchConfig(config.config, opts);

runtime/main.d.ts

@@ -168,2 +168,4 @@ import * as pg from 'pg';
 export declare const functionQuery = "SELECT n.nspname as \"schema\",\n  p.proname as \"name\",\n  pg_catalog.pg_get_function_result(p.oid) as \"result\",\n  pg_catalog.pg_get_function_arguments(p.oid) as \"args\",\n  pg_catalog.pg_get_functiondef(p.oid) as \"def\"\nFROM pg_catalog.pg_proc p\n  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\nWHERE pg_catalog.pg_function_is_visible(p.oid)\n  AND n.nspname <> 'pg_catalog'\n  AND n.nspname <> 'information_schema'\nORDER BY 1, 2, 4;";
+export declare const indexQuery = "select schemaname as \"schema\", indexname as \"name\", tablename as \"table\", indexdef as def from pg_indexes where schemaname <> 'pg_catalog' and schemaname <> 'information_schema';";
+export declare const viewQuery = "select schemaname as \"schema\", viewname as \"name\", definition as \"def\", false as \"materialized\" from pg_views where schemaname <> 'pg_catalog' and schemaname <> 'information_schema' union all select schemaname as \"schema\", matviewname as \"name\", definition as \"def\", true as \"materialized\" from pg_matviews where schemaname <> 'pg_catalog' and schemaname <> 'information_schema';";
 export declare function config(config: BuilderConfig, fn: (builder: Builder) => Promise<Config>): BuildConfig;
@@ -236,5 +238,19 @@ export interface Table {
 }
+export interface ViewSchema {
+    schema: string;
+    name: string;
+    materialized: boolean;
+    def: string;
+}
+export interface IndexSchema {
+    schema: string;
+    name: string;
+    table: string;
+    def: string;
+}
 export interface SchemaCache {
     tables: TableSchema[];
     functions?: FunctionSchema[];
+    indexes?: IndexSchema[];
+    views?: ViewSchema[];
 }
@@ -241,0 +257,0 @@ export declare const BuilderOptions: {

runtime/main.js

@@ -198,2 +198,4 @@ "use strict";
 ORDER BY 1, 2, 4;`;
+exports.indexQuery = `select schemaname as "schema", indexname as "name", tablename as "table", indexdef as def from pg_indexes where schemaname <> 'pg_catalog' and schemaname <> 'information_schema';`;
+exports.viewQuery = `select schemaname as "schema", viewname as "name", definition as "def", false as "materialized" from pg_views where schemaname <> 'pg_catalog' and schemaname <> 'information_schema' union all select schemaname as "schema", matviewname as "name", definition as "def", true as "materialized" from pg_matviews where schemaname <> 'pg_catalog' and schemaname <> 'information_schema';`;
 function config(config, fn) {
@@ -200,0 +202,0 @@ const builder = new PrivateBuilder(config);

runtime/patch.d.ts

@@ -8,2 +8,4 @@ import * as pg from 'pg';
     functions?: string[];
+    indexes?: string[];
+    views?: string[];
     connect?: pg.ClientConfig & {
@@ -24,2 +26,8 @@ schemaCacheFile?: string;
     };
+    indexes: {
+        [name: string]: string;
+    };
+    views: {
+        [name: string]: string;
+    };
     statements: string[];
@@ -26,0 +34,0 @@ }

runtime/patch.js

@@ -25,2 +25,4 @@ "use strict";
         functions: {},
+        indexes: {},
+        views: {},
         statements: [],
@@ -34,4 +36,10 @@ };
         const schema = { tables: [] };
+        if (!cache.tables)
+            cache.tables = [];
         if (!cache.functions)
             cache.functions = [];
+        if (!cache.indexes)
+            cache.indexes = [];
+        if (!cache.views)
+            cache.views = [];
         const name = config.name ? `${config.name} (${connect.user || process.env.USER}@${connect.host || 'localhost'}:${connect.port || 5432}/${connect.database || process.env.USER})` : `${connect.user || process.env.USER}@${connect.host || 'localhost'}:${connect.port || 5432}/${connect.database || process.env.USER})`;
@@ -50,2 +58,4 @@ log(`Patching ${name}...`);
             schema.functions = (await client.query(main_1.functionQuery)).rows;
+            schema.indexes = (await client.query(main_1.indexQuery)).rows;
+            schema.views = (await client.query(main_1.viewQuery)).rows;
             for (const ct of cache.tables) {
@@ -56,3 +66,3 @@ if (opts.tables && !opts.tables.includes(ct.name))
                 if (!t) {
-                    const q = `create table "${ct.name}" (${ct.columns.map(createColumn).join(', ')});`;
+                    const q = `create table "${ct.schema}"."${ct.name}" (${ct.columns.map(createColumn).join(', ')});`;
                     qs.push(q);
@@ -65,3 +75,3 @@ res.tables[ct.name] = [q];
                         if (!c) {
-                            const q = `alter table "${ct.name}" add column "${col.name}" ${colType(col)}${col.nullable ? '' : ' not null'}${col.default ? ` default ${col.default}` : ''};`;
+                            const q = `alter table "${ct.schema}"."${ct.name}" add column "${col.name}" ${colType(col)}${col.nullable ? '' : ' not null'}${col.default ? ` default ${col.default}` : ''};`;
                             qs.push(q);
@@ -78,3 +88,3 @@ (res.tables[ct.name] || (res.tables[ct.name] = [])).push(q);
                             if (c.default !== col.default) {
-                                const q = `alter table "${ct.name}" alter column "${col.name}" ${col.default ? 'set' : 'drop'} default${col.default ? ` ${col.default}` : ''};`;
+                                const q = `alter table "${ct.schema}"."${ct.name}" alter column "${col.name}" ${col.default ? 'set' : 'drop'} default${col.default ? ` ${col.default}` : ''};`;
                                 qs.push(q);
@@ -84,3 +94,3 @@ t.push(q);
                             if (c.nullable !== col.nullable) {
-                                const q = `alter table "${ct.name}" alter column "${col.name}" ${col.nullable ? 'drop' : 'set'} not null;`;
+                                const q = `alter table "${ct.schema}"."${ct.name}" alter column "${col.name}" ${col.nullable ? 'drop' : 'set'} not null;`;
                                 qs.push(q);
@@ -102,2 +112,22 @@ t.push(q);
             }
+            for (const cv of cache.views) {
+                if (opts.views && !opts.views.includes(cv.name))
+                    continue;
+                const v = schema.views.find(e => e.schema === cv.schema && e.name === cv.name);
+                if (!v || v.def !== cv.def) {
+                    const def = `DROP ${cv.materialized ? 'MATERIALIZED ' : ''}VIEW IF EXISTS "${cv.schema}"."${cv.name}"; CREATE ${cv.materialized ? 'MATERIALIZED ' : ''}VIEW "${cv.schema}"."${cv.name}" AS ${cv.def};`;
+                    qs.push(def);
+                    res.views[`${cv.name}`] = def;
+                }
+            }
+            for (const ci of cache.indexes) {
+                if (opts.indexes && !opts.indexes.includes(ci.name))
+                    continue;
+                const i = schema.indexes.find(e => e.schema === ci.schema && e.name === ci.name && e.table === ci.table);
+                if (!i || i.def !== ci.def) {
+                    const def = `DROP INDEX IF EXISTS "${ci.schema}"."${ci.name}"; ${ci.def};`;
+                    qs.push(def);
+                    res.indexes[`${ci.table}.${ci.name}`] = def;
+                }
+            }
             if (qs.length) {
@@ -104,0 +134,0 @@ log(`\nPatches for ${name}`);

No alert changes

Improved metrics

Total package byte prevSize

116202

7.88%

Lines of code

2419

6.85%

No dependency changes