Neurosis Prevention Mechanism

    slite-change
    TypeScript icon, indicating that this package has built-in type declarations

    1.2.15 • Public • Published

    slite-change

    A simple change management system for SQLite3 written in pure JavaScript/TypeScript. This is for on server programs like backend services or utilities.

    Getting started

    Installing

    From within your project directory were package.json is found, simply run this.

    npm install slite-change

    This library reads a sequence of SQL files and applies those as changes to an SQLite3 database.

    Only new changes are applied. A change log table maintains the tracking of what changes are already applied.

    Change files are simple SQL, because XML sucks!


    Formating Of Change Sets

    Each change must begin with a comment line starting with --changeset <Author Name>:<change ID>

    Each change set file is read in order of directory listing. Change ID values are integers. They must never repeat. They are applied in order as they are found with the changeset file.

    NOTE: Always start the SQL with BEGIN; and end it with COMMIT;. This provides transactions so that no changes are made if there is an error in the SQL.

    This is an example of a simple change set.

    --changeset Royce:1
    BEGIN;
    CREATE TABLE 'users' (
      'id' int NOT NULL,
      'firstname' VARCHAR(50),
      'lastname' VARCHAR(50),
      'username' VARCHAR(50),
      'passwd' VARCHAR(50),
      'emailAddress' VARCHAR(50),
      PRIMARY KEY ('id'));
    
    -- Coment lines are supported
    INSERT INTO 'users' (id, firstname, lastname, username, passwd, emailAddress) VALUES (0, 'admin', 'admin', 'admin', 'password', 'roo@localhost');
    
    CREATE TABLE 'config' (
      'parameter' VARCHAR(45) NULL,
      'value' VARCHAR(45) NULL,
      PRIMARY KEY ('parameter'));
    
    INSERT INTO 'config' ('parameter','value') VALUES ('db_version', '1');
    COMMIT;

    Once a change set has been appled to a database, no more changes should be made to that change ID, additional changes should be added to a new changeset file with the next value of change ID.

    Example changes can be found in db-changes


    Usage

    Put SQL files into a resources directory and create an instance of slite-change with the arguments of the DB instance and change directory.

    import SliteChange from 'slite-change';
    ...
      this.Sql = new sqlite.Database('my_database.db', (err) => {
        if (err) {
          console.log('Could not connect to database', err)
        } else {
          console.log('Connected to database');
          new SliteChange(this.Sql, './src/resources/db-changes/', (ReturnMsg) => {
            if(ReturnMsg != '') {
              console.log('Error: %s', ReturnMsg);
            }
          });
        }
      });

    Using Stored Procedures

    Sqlite does not support stored procedures but slite-change does. Stored procedures are written to the SQL files and can be called using the tag @Procedure followed by the function name and arguments to be passed to the function.

    The function called is responsible for parsing the argument string.

    -- Sample SQL to call "AddUser" funtion with a string of arguments.
    @Procedure AddUser guest guest guest guest guest
    import SliteChange from 'slite-change';
    ...
      this.Sql = new sqlite.Database('my_database.db', (err) => {
        if (err) {
          console.log('Could not connect to database', err)
        } else {
          console.log('Connected to database');
          new SliteChange(this.Sql, './src/resources/db-changes/', (ReturnMsg) => {
            if(ReturnMsg != '') {
              console.log('Error: %s', ReturnMsg);
            }
          });
        }
      }, [
        { procName: 'AddUser', procFunc: (args: string) => { this.AddUser(args); } }
      ]);
    ...
    
    	private AddUser(args: string) {
    		let argsArray = args.split(' ');
    		this.Sql.all('INSERT INTO users (firstname, lastname, username, password, emailAddress) \
    			VALUES (?, ?, ?, ?, ?)', argsArray, (error) => {
    				if(error) {
    					console.error(error.message);
    				}
    		});
    	}

    NOTE: Procedure calls are made after the commit.


    Support

    Send emails to osgnuru@gmail.com

    Twitter URL

    Discord

    Buttons generated by Shields IO


    Contributing

    Anyone is welcome to fork this project on GitLab slite-change

    Submit a pull request and it will be reviewed.


    Authors and acknowledgment

    SQLite3 team!!! 🍺🍺🍺


    License

    Copyright (C) 2021 Silicon Tao Technology Systems

    This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; GPL-2.0-only.

    This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

    You should have received a copy of the GNU General Public License along with this program; if not, write to

    Free Software Foundation
    51 Franklin Street, Fifth Floor
    Boston, MA 02110
    USA
    

    Project status

    This is a new project and the authors first time posting to NPM. If something is not working please send an email to the contact above.

    Unit tests have been added to validate features.


    Developing This Project

    One time only install and setup.

    npm install --global np
    npm i -g typescript -D
    npm i -g typings -D
    tsc --init

    tsc is configured to compile in package.json in scripts.

    To compile

    tsc

    Commit to GitLab.com

    git commit
    git push

    Publishing is done by np

    np

    To publish a beta version for testing a branch in development.

    npm run beta

    Testing testing with Mocha. Installing gloabally because the library user will have their own tests.

    npm install mocha -g
    npm install ts-mocha -g
    npm install --save-dev @types/mocha
    

    To run the tests.

    npm run tests
    
    # Or as defined in package.json
    ts-mocha test/*.spec.ts
    

    How to Unit Test with NodeJS? TypeScript wrapper for Mocha

    Install

    npm i slite-change

    DownloadsWeekly Downloads

    1

    Version

    1.2.15

    License

    GPL-2.0-only

    Unpacked Size

    51.7 kB

    Total Files

    12

    Last publish

    Collaborators

    • roycethebiker