dbay - npm Package Compare versions

Comparing version 10.123.0 to 11.0.0

lib/errors.js

@@ -180,2 +180,9 @@ (function() {
 
+  this.DBay_argument_missing = class DBay_argument_missing extends this.DBay_error {
+    constructor(ref, name) {
+      super(ref, `expected value for ${name}, got nothing`);
+    }
+
+  };
+
   this.DBay_wrong_type = class DBay_wrong_type extends this.DBay_error {
@@ -182,0 +189,0 @@ constructor(ref, types, type) {

lib/helpers.js

@@ -82,4 +82,15 @@ (function() {
 
+  //-----------------------------------------------------------------------------------------------------------
+  this.SQL = function(parts, ...expressions) {
+    var R, expression, i, idx, len;
+    R = parts[0];
+    for (idx = i = 0, len = expressions.length; i < len; idx = ++i) {
+      expression = expressions[idx];
+      R += expression.toString() + parts[idx + 1];
+    }
+    return R;
+  };
+
 }).call(this);
 
 //# sourceMappingURL=helpers.js.map

lib/main.js

 (function() {
   'use strict';
-  var CND, DBay_ctx, DBay_openclose, DBay_query, DBay_reify, DBay_sqlgen, DBay_stdlib, DBay_udf, E, FS, H, PATH, Random, SQL, Sql, badge, debug, echo, guy, help, info, new_bsqlt3_connection, rpr, types, urge, warn, whisper;
+  var CND, DBay_ctx, DBay_openclose, DBay_query, DBay_sqlgen, DBay_stdlib, DBay_trash, DBay_udf, E, FS, H, PATH, Random, SQL, Sql, badge, debug, echo, guy, help, info, new_bsqlt3_connection, rpr, types, urge, warn, whisper;
 
@@ -58,3 +58,3 @@ //###########################################################################################################
 
-  ({DBay_reify} = require('./reify-mixin'));
+  ({DBay_trash} = require('./trash-mixin'));
 
@@ -65,18 +65,4 @@ ({Sql} = require('./sql'));
   this.DBay = (function() {
-    class DBay extends DBay_query(DBay_ctx(DBay_openclose(DBay_stdlib(DBay_sqlgen(DBay_udf(DBay_reify(Function))))))) {
+    class DBay extends DBay_query(DBay_ctx(DBay_openclose(DBay_stdlib(DBay_sqlgen(DBay_udf(DBay_trash(Function))))))) {
       //---------------------------------------------------------------------------------------------------------
-      /* This function is meant to be used to explicitly mark up SQL literals as in
-       constructs like `for row from db SQL"select * from ..."`. The markup can help text editors to provided
-       syntax hiliting and other language-specific features for embedded SQL strings. */
-      static SQL(parts, ...expressions) {
-        var R, expression, i, idx, len;
-        R = parts[0];
-        for (idx = i = 0, len = expressions.length; i < len; idx = ++i) {
-          expression = expressions[idx];
-          R += expression.toString() + parts[idx + 1];
-        }
-        return R;
-      }
-
-      //---------------------------------------------------------------------------------------------------------
       static cast_sqlt_cfg(me) {
@@ -152,4 +138,4 @@ /* Produce a configuration object for `better-sqlite3` from `me.cfg`. */
         }
-        if (typeof this._$reify_initialize === "function") {
-          this._$reify_initialize();
+        if (typeof this._$trash_initialize === "function") {
+          this._$trash_initialize();
         }
@@ -233,2 +219,8 @@ guy.cfg.configure_with_types(this._me, cfg, types);
     //---------------------------------------------------------------------------------------------------------
+    /* This function is meant to be used to explicitly mark up SQL literals as in
+     constructs like `for row from db SQL"select * from ..."`. The markup can help text editors to provided
+     syntax hiliting and other language-specific features for embedded SQL strings. */
+    DBay.SQL = H.SQL;
+
+    //---------------------------------------------------------------------------------------------------------
     DBay.C = guy.lft.freeze({
@@ -299,2 +291,14 @@ autolocation: H.autolocation,
           returning: null
+        },
+        //.....................................................................................................
+        dbay_trash_to_sql_cfg: {
+          path: false,
+          overwrite: false,
+          walk: false,
+          _use_dot_cmds: true
+        },
+        //.....................................................................................................
+        dbay_trash_to_sqlite_cfg: {
+          path: false,
+          overwrite: false
         }
@@ -308,4 +312,6 @@ }
 
+  guy.props.hide(this.DBay, 'new_bsqlt3_connection', new_bsqlt3_connection);
+
 }).call(this);
 
 //# sourceMappingURL=main.js.map

lib/random.js

@@ -73,6 +73,6 @@ (function() {
       //---------------------------------------------------------------------------------------------------------
-      get_random_filename() {
+      get_random_filename(extension = 'sqlite') {
         var n10;
         n10 = this.get_random_integer(1_000_000_000, 9_999_999_999);
-        return `dbay-${n10}.sqlite`;
+        return `dbay-${n10}.${extension}`;
       }
@@ -79,0 +79,0 @@

lib/stdlib-mixin.js

@@ -67,3 +67,3 @@ (function() {
       create_stdlib() {
-        var prefix, self;
+        var prefix;
         if (this._stdlib_created) {
@@ -188,2 +188,15 @@ return null;
         });
+        //-------------------------------------------------------------------------------------------------------
+        this.create_function({
+          name: prefix + 're_is_match',
+          deterministic: false,
+          varargs: false,
+          call: function(text, pattern) {
+            if ((new RegExp(pattern, 'g')).test(text)) {
+              return 1;
+            } else {
+              return 0;
+            }
+          }
+        });
         //=======================================================================================================
@@ -306,3 +319,2 @@ // ASSERTS AND EXCEPTIONS
         this.variables = {};
-        self = this;
         //-------------------------------------------------------------------------------------------------------
@@ -309,0 +321,0 @@ this.create_function({

lib/types.js

@@ -240,2 +240,42 @@ (function() {
   //===========================================================================================================
+  // TRASH
+  //-----------------------------------------------------------------------------------------------------------
+  this.declare('dbay_trash_to_sql_cfg', function(x) {
+    return {
+      "@isa.object x": function(x) {
+        return this.isa.object(x);
+      },
+      "@type_of x.path in [ 'boolean', 'nonempty_text', ]": function(x) {
+        var ref;
+        return this.type_of((ref = x.path) === 'boolean' || ref === 'nonempty_text');
+      },
+      "@isa.boolean x.overwrite": function(x) {
+        return this.isa.boolean(x.overwrite);
+      },
+      "@isa.boolean x.walk": function(x) {
+        return this.isa.boolean(x.walk);
+      },
+      "@isa.boolean x._use_dot_cmds": function(x) {
+        return this.isa.boolean(x._use_dot_cmds);
+      }
+    };
+  });
+
+  //-----------------------------------------------------------------------------------------------------------
+  this.declare('dbay_trash_to_sqlite_cfg', function(x) {
+    return {
+      "@isa.object x": function(x) {
+        return this.isa.object(x);
+      },
+      "@type_of x.path in [ 'boolean', 'nonempty_text', ]": function(x) {
+        var ref;
+        return this.type_of((ref = x.path) === 'boolean' || ref === 'nonempty_text');
+      },
+      "@isa.boolean x.overwrite": function(x) {
+        return this.isa.boolean(x.overwrite);
+      }
+    };
+  });
+
+  //===========================================================================================================
   // SQLGEN
@@ -242,0 +282,0 @@ //-----------------------------------------------------------------------------------------------------------

package.json

 {
   "name": "dbay",
-  "version": "10.123.0",
+  "version": "11.0.0",
   "description": "In-Process, In-Memory & File-Based Relational Data Processing with SQLite, BetterSQLite3",
@@ -5,0 +5,0 @@ "main": "lib/main.js",

README.md

@@ -28,3 +28,3 @@
       - [List of Functions](#list-of-functions)
-      - [Use Case for DBay Exceptions and Assertions: Enforcing Invariables](#use-case-for-dbay-exceptions-and-assertions-enforcing-invariables)
+      - [Use Case for DBay Exceptions and Assertions: Enforcing Invariants](#use-case-for-dbay-exceptions-and-assertions-enforcing-invariants)
       - [Use Case for DBay Variables: Parametrized Views](#use-case-for-dbay-variables-parametrized-views)
@@ -38,2 +38,6 @@ - [Safe Escaping for SQL Values and Identifiers](#safe-escaping-for-sql-values-and-identifiers)
       - [Insert Statements with a `returning` Clause](#insert-statements-with-a-returning-clause)
+    - [Trash Your DB for Fun and Profit](#trash-your-db-for-fun-and-profit)
+      - [Motivation](#motivation)
+      - [Properties of Trashed DBs](#properties-of-trashed-dbs)
+      - [API](#api)
     - [Random](#random)
@@ -318,3 +322,3 @@ - [Note on Package Structure](#note-on-package-structure)
 
-#### Use Case for DBay Exceptions and Assertions: Enforcing Invariables
+#### Use Case for DBay Exceptions and Assertions: Enforcing Invariants
 
@@ -379,6 +383,6 @@ * `std_assert: ( test, message ) ->` throws error if `test` is false(y)
 
-* **`db.sql.I: ( name ): ->`**: returns a properly quoted and escaped SQL **I**dentifier.
-* **`db.sql.L: ( x ): ->`**: returns a properly quoted and escaped SQL **V**alue. Note that booleans
+* **`db.sql.I: ( name ) ->`**: returns a properly quoted and escaped SQL **I**dentifier.
+* **`db.sql.L: ( x ) ->`**: returns a properly quoted and escaped SQL **V**alue. Note that booleans
   (`true`, `false`) will be converted to `1` and `0`, respectively.
-* **`db.sql.V: ( x ): ->`**: returns a bracketed SQL list of values (using `db.sql.V()` for each list
+* **`db.sql.V: ( x ) ->`**: returns a bracketed SQL list of values (using `db.sql.V()` for each list
   element).
@@ -389,3 +393,3 @@
 
-**`db.interpolate( sql, values ): ->`** accepts a template (a string with placeholder formulas) and a list
+**`db.interpolate( sql, values ) ->`** accepts a template (a string with placeholder formulas) and a list
 or object of values. It returns a string with the placeholder formulas replaced with the escaped values.
@@ -578,2 +582,94 @@
 
+### Trash Your DB for Fun and Profit
+
+#### Motivation
+
+**The Problem**—you have a great SQLite3 database with all the latest features (like `strict` tables,
+`generate`d columns, user-defined function calls in views and so on), and now you would like to use a tool
+like [`visualize-sqlite`](https://lib.rs/crates/visualize-sqlite) or
+[SchemaCrawler](https://www.schemacrawler.com/diagramming.html) to get a nice ER diagram for your many
+tables. Well, now you have two problems.
+
+Thing is, the moment you use UDFs in your DDL (as in, `create view v as select myfunction( x ) as x1 from
+t;`) your `*.sqlite` file stops being viable as a stand-alone DB; because UDFs are declared on the
+connection and defined in the host app's environment, they are not stored inside `*.sqlite` files, nor are
+they present in an SQL dump file. Your database and your application have become an inseparable unit with a
+mutual dependency on each other. But the way the common visualizers work is they require a standalone DB or
+an SQL dump to generate output from, and they will choke on stuff they don't understand (even though the ER
+relationships might not even be affected by the use of a user-defined function).
+
+**The solution** to this conundrum that I've come up with is to prepare a copy of a given DB with all the
+fancy stuff removed but all the essential building blocks—tables, views, primary keys, secondary keys,
+uniqueness constraints—preserved.
+
+I call this functionality `trash` which is both a pun on `dump` (as in 'dump the DB to an SQL file') and a
+warning to the user that this is not a copy. You *do* trash your DB using this feature.
+
+#### Properties of Trashed DBs
+
+The following invariants of trashed DBs hold:
+
+* To trash a DB, an SQL script is computed that replicates the DB's salient structural features.
+* This script is either returned, written to a file, or used to produce a binary representation which is,
+  again, either returned or written to a file.
+* The SQL script runs in a single transaction.
+* It starts by removing all relations, should they exist. This means one can always do `sqlite3 path/to/db <
+  mytrasheddb.sql` even on an existing `path/to/db`.
+* All fields of all relations will be present in the trashed copy.
+* All trashed fields will have the same type declaration as the original DB (in the sense that they will use
+  the same text used in the original DDL). However, depending on meta data as provided by SQLite3's internal
+  tables and pragmas, some views may miss some type information.
+* Empty type declarations and the missing type declaration of view fields will be rendered as `any` in the
+  trash DDL.
+* The trashed DB will contain no data (but see below).
+
+**Discussion and Possible Enhancements**
+
+* It is both trivial to show that, on the one hand, in a properly structured RDB, views can always be
+  materialized to a table, complete with field names, data, and at least partial type information. However,
+  on the other hand, it is also trivial to show that any given view (and any generated field, for that
+  matter) may use arbitrarily complex computations in its definition—imagine a UDF that fetches content from
+  the network as an example.
+  * In SQLite, not all fields of all views have an explicit type (and even fields of tables can lack an
+    explicit type or be of type `any`)
+* There's somewhat of a grey zone between the two extremes of a view just being a join of two tables or an
+  excerpt of a single one—something that would probably be reproducible in a trash DB with some effort
+  towards SQL parsing. Whether this would be worth the effort—tackle SQL parsing with the goal to preserve
+  views as views in a trash DB—is questionable. Observe that not even all built-in functions of SQLite3 are
+  guaranteed to be present in a given compiled library or command line tool because those can be (and often
+  are) configured to be left out; in this area there's also a certain variation across SQLite versions.
+* An alternative to tackling the generally inattainable goal of leaving views as views would be to use
+  user-defined prefixes for views (a view `"comedy_shows"` could be rendered as `"(view) comedy_shows"`).
+  In light of the complications outlined here, this option looks vastly superior.
+
+* The trashed DB will contain no data, but this could conceivably be changed in the future. When
+  implemented, this will allow to pass around DBs 'for your information and pleasure only'. When this
+  feature is implemented, a way to include/exclude specific relations will likely also be implemented.
+
+#### API
+
+**`trash_to_sql: ( { path: false, overwrite: false, walk: false, } ) ->`**
+  * renders DB as SQL text
+  * if `path` is given...
+    * ... and a valid FS path, writes the SQL to that file and returns the path.
+    * ... and `true`, a random path in DBay's `autolocation` will be chosen, written to, and returned.
+    * ... and `false`, it will be treated as not given, see below.
+  * if `path` exists, will fail unless `overwrite: true` is specified
+  * if `path` is not given or `false`,
+    * will return a string if `walk` is not `true`,
+    * otherwise, will return an iterator over the lines of the produced SQL source.
+
+**`trash_to_sqlite: ( { path: false, overwrite: false, } ) ->`**
+  * renders DB as an SQLite3 binary representation
+  * handling of `path`, `overwrite`, and the return value is done as described above for `trash_to_sql()`.
+  * instead of writing or returning an SQL string, this method will write or return a `Buffer` (or a
+    `TypedArray`???)
+
+In any event, parameters that make no sense in the given combination (such as omitting `path` but specifying
+`overwrite: true`) will be silently ignored.
+
+
+
+------------------------------------------------------------------------------------------------------------
+
 ### Random
@@ -672,3 +768,6 @@
   * DB should be readable by tools like `sqlite3` command line, [`visualize-sqlite`](https://lib.rs/crates/visualize-sqlite)
+* **[+]** consider to implement `trash()` as `trash_to_sql()` (`path` optional), `trash_to_sqlite()` (`path`
+  optional)
+* **[–]** consider to implement iterating over statements instead of lines in `trash_to_sql()`
 
 

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

12242277

increased by0.76%

Lines of code

2469

increased by20.73%

Number of lines in readme file

767

increased by14.82%

Worsened metrics

Number of low supply chain risk alerts

5

increased by25%

No dependency changes