Intershop NPM | npm.io

InterShop

An incipient application foundation built on Postgres, with sprinkles of JavaScript and plPython3u

Table of Contents generated with DocToc

Installation

Dependencies

Postgres

sudo apt install wget ca-certificates psmisc
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt install postgresql-12
sudo apt install postgresql-server-dev-12
sudo apt install postgresql-plpython3-12
sudo apt install postgresql-contrib-12
sudo apt install postgresql-12-unit
sudo apt install postgresql-12-plsh
# sudo apt install postgresql-12-pgtap
# sudo apt install postgresql-12-pldebugger
# sudo apt install postgresql-9.6-plv8
# sudo apt install postgresql-plperl-9.6

Configure Postgres

Find Configuration File Locations

In the below, adjust port; the first Postgres installation will likely listen on port 5432, the next one on port 5433 and so on; this, of course, will vary depending on whether one installed a newer PG version along an older one and so.

sudo -u postgres psql --port 5433 -c "                                                \
  select                                                                  \
      name                                                    as key,     \
      setting                                                 as value,   \
      case setting when reset_val then '' else reset_val end  as changed  \
    from pg_settings                                                      \
    where true                                                            \
      and ( category = 'File Locations' )                                 \
      order by name;"

This will output a table similar to this one:

        key        |                  value                  | changed
-------------------+-----------------------------------------+---------
 config_file       | /etc/postgresql/12/main/postgresql.conf |
 data_directory    | /var/lib/postgresql/12/main             |
 external_pid_file | /var/run/postgresql/12-main.pid         |
 hba_file          | /etc/postgresql/12/main/pg_hba.conf     |
 ident_file        | /etc/postgresql/12/main/pg_ident.conf   |

Personally, I prefer to create a git-versioned project so I can track (and, when necessary, undo) my changes to the PG configuration. Inside that project, I create one directory for each version and use hardlinks (not symlinks) so I get mirrored local versions of the pertinent files that will always be identical to the configurations as seen by Postgres.

In pg_hba.conf, add these lines below the one that reads

# Database administrative login by Unix domain socket
local   all             postgres                                peer

Do not change the above, just add these lines to indicate that connections from localhost should always be trusted:

### in pg_hba.conf ###
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             localhost               trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

You must restart Postgres after changing the configuration, for example with one of these lines:

sudo /etc/init.d/postgresql restart ; echo $?
sudo /etc/init.d/postgresql restart 12 ; echo $?

Statement-Level Statistics

In postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'     # (change requires restart)

(must restart with sudo /etc/init.d/postgresql restart 12 or similar after change; note that errors in this settings might not lead to visible error messages, but still prevent the server from restarting).

Otherwise, comment this out in 020-extensions.sql:

create extension if not exists pg_stat_statements;

Also see here.

Python

sudo pip install pipenv
pipenv install tap.py pytest

cp ~/jzr/hengist/intershop.ptv .
ln -s ../intershop
intershop rebuild
pip3 install setuptools
pip3 install --user pipenv
pipenv install uharfbuzz
pipenv shell
intershop psql -f db/100-harfbuzz.sql

InterShop Initialization and (Re-) Building

mkdir myapp
cd myapp
wget https://raw.githubusercontent.com/loveencounterflow/intershop/master/copy-to-host-app/rakefile
wget https://raw.githubusercontent.com/loveencounterflow/intershop/master/copy-to-host-app/peru.yaml
wget https://raw.githubusercontent.com/loveencounterflow/intershop/master/intershop.ptv

If you don't already have a .gitignore file, you may want to copy (or merge) the one from InterShop; this is to make sure your git repo won't version a gazillion dependencies under node_modules (although for some use cases this is actually the recommended way):

wget https://raw.githubusercontent.com/loveencounterflow/intershop/master/.gitignore

You probably do not want to add the directories .peru and intershop to your git repo, so make sure that both entries do appear in your .gitignore file.

Edit intershop.ptv so the line intershop/host/name spells out the name of your app (let's call it myapp here), which will also become the name of the database and the Postgres user:

intershop/host/name                             ::text=               myapp
intershop/db/port                               ::integer=            5432
intershop/db/name                               ::text=               ${intershop/host/name}
intershop/db/user                               ::text=               ${intershop/host/name}

You are now ready to start installation: peru sync will pull the latest InterShop sources; rake intershop_npm_install will run npm install inside the newly established intershop folder, and intershop rebuild will create a Postgres DB (named myapp or whatever name you chose) and a user by the same name and run all the *.sql files in intershop/db:

peru reup && peru sync rake intershop_npm_install

intershop rebuild

To get an idea what we have by now, take a gander at the catalog:

intershop psql -c "select * from CATALOG.catalog order by schema, name;"

The intershop psql invocation is essentially nothing but psql -U $intershop_db_user -d $intershop_db_name -p $intershop_db_port ... "@$" where ... denotes a bunch of configuration values.

It's probably a good idea to add your configuration to git:

git add intershop.ptv && git commit -m'add intershop.ptv'
git add peru.yaml && git commit -m'update by peru'

Whether or not to add the intershop submodule to git is a matter of taste:

git add intershop && git commit -m'updates from upstream'

Some Queries of Interest

Using PTV Configuration Variables in SQL

In order to show a table of all current variables, run

intershop psql -c "select * from U.variables order by key;"

Later on, you may want to add your own options into intershop.ptv so you can access those configuration settings from SQL; it's customary to prefix those options with the name of your app (but anything will work so long as names don't start with intershop):

myapp/fudge/use                                 ::boolean=          true
myapp/fudge/factor                              ::float=            3.14
myapp/fudge/delta                               ::integer=          12

The type annotations are currently not documented and not used programmatically; they serve at present merely as a handy reference for the type casting one has to perform explicitly when retrieving values; so, in your SQL you might want to do this:

select ¶( 'myapp/fudge/use'    )::boolean;
select ¶( 'myapp/fudge/factor' )::float;
select ¶( 'myapp/fudge/delta'  )::integer;

Variables can be used as compilation parameters:

do $$ begin
  if ¶( 'myapp/fudge/use' )::boolean then
    create function ...;
  else
    create function ...;
    end if;
  $$

InterShop Commands

Built-In Commands

`intershop node` and `intershop nodexh`

`intershop psql`

`intershop rebuild`

User-Defined Commands

InterShop AddOns

first example of this: InterShop RPC, a package to enable Inter-Process Communication (IPC), including Remote Procedure Calls (RPCs) to be executed by NodeJS
generally, a way to extend functionalities of the DB
may consist of
- *.sql files containing table definitions and so on that should be read during DB rebuilds
- *.py files that are visible to the plPython3u subsystem
- *.js files to be run by NodeJS
- other files to be ignored by the ISAO subsystem

Format of `intershop-package.json`

decribes what to do with the files in the package
outermost values must be a JSON object
with one entry intershop-package-version that specifies the version of the format itself; must currently be 1.0.0
another entry "files": {...} that describes how to treat the source files
files maps from filenames (relative to package root) to purposes
purpose may be either one of
- "ignore"—do nothing; used e.g. for source files that have to be transpiled. This is the default and may be left out
- "app"—intended for the InterShop host application; as far as InterShop is concerned, equivalent to "ignore"
- "support"—will be imported by the InterShop plpython3u subsystem ('support' meaning 'supporting plPython3u library')
- "rebuild"—to be executed when the DB is rebuilt from scratch with the intershop rebuild command
intershop-package.json files that do not meet the above criteria will cause an error

Example:

{
  "intershop-package-version": "1.0.0",
  "files": {
    "ipc.sql":                                "rebuild",
    "intershop-rpc-server-secondary.js":      "app",
    "intershop-rpc-server-secondary.coffee":  "ignore",
    "ipc.py":                                 "support"
  }
}

Running Tests

py.test --tap-files

The InterShop Dev Cycle

In the normal course of events where one just wants to use InterShop for the benefits it offers, it is sufficient to just do either

peru reup to update to the newest version as available on https://github.com, or
peru sync to pull in either the newest version (if no current version has been pulled so far) or the current version (that is, make sure there are no changes in your local copy that deviate from the current version).

In this setup (where the intershop directory is essentially a clone of a remote repo), in order to implement some new InterShop features and have them immediately available in the host app, one could work inside a local copy of the remote repo, say path/to/local/clone/of/intershop; however, that would entail having to push all changes to the remote prior to updating the local copy, which gets tedious very soon.

(how to use peru override)https://github.com/buildinspace/peru/issues/187

In your host app, register an 'override' for your local intershop repo with peru:

Setup 1: 'Regular' InterShop setup

intershop dependency managed by peru

peru override add intershop path/to/local/clone/of/intershop

# ... edit some InterShop source file, then ...
cd path/to/local/clone/of/intershop                     # ... CD into intershop repo
coffee --map -o intershop_modules -c intershop_modules  # ... do whatever to build your code
cd -                                                    # ... CD back to project folder
peru sync                                               # ... make Peru pull changes from local InterShop repo
intershop refresh-mirage-datasources                    # ... issue suitable commands to see whether
intershop psql -c "select * from MIRAGE.mirror;"        #     everything works OK.

Putting everything on a single line:

( cd path/to/local/clone/of/intershop && coffee --map -o intershop_modules -c intershop_modules ) && peru sync && intershop refresh-mirage-datasources && intershop psql -c "select * from MIRAGE.mirror order by dsk, dsnr, linenr;"

Setup 2: Clone InterShop from Within App Folder

TBW

Setup 3: Put Symlink to Local InterShop Repo into App Folder

TBW

Future

drop stand-alone mode of InterShop
make npm installable
dev cycle will become standard NodeJS module dev cycle, no more peru

No More FDWs FTW

See documentation/no-more-fdws-ftw.md.

The MIRAGE File Mirror Module

The Mirage module is responsible for handling all read-only linewise file access. Have a look at the docs and the demo.

VNRs

See documentation/vnrs.md.

Invariants

See documentation/invariants.md.

Catalog

See documentation/catalog.md.

To Do

Remove npm dependency squel, replace by other query builder b/c of npm audit: Failure to sanitize quotes which can lead to sql injection, Package: squel, No patch available, see https://npmjs.com/advisories/575
Better docs, esp. section with useful command lines, queries
fix add-ons, documentation
remove stand-alone capabilities as they are not needed (dev inside hengist)
document dev cycle (i.e. how to develop features in local copy w/out having to re-published for each change) updating datasources on rebuild; documentation
replace most bash files with a sane language (any of JS, ?Ruby?, ?Python?)
replace rake, rakefile with other language to get rid of Ruby?
confusingly, rake --tasks does not display tasks, but dumps intershop.ptv configuration; rake -P (rake ----prereqs) does list tasks and their prerequisits, neither is not documented, nor are tasks documented. Observe that Only tasks with descriptions set will appear in rake -T
confusingly, rake ≡ rake default ≡ rake update ≡ rake peru_update; ideally there should only be 1 way to do it, so get rid of other 3 ways
replace Peru?
PTV readers: do not allow empty substitutions (${}) but throw explit error
intershop commands:
- intershop list-databases
- intershop reconstruct (in addition to or as expansion of rebuild)
Mirage documentation
Mirage: local file paths like ./README.md do not work although ¶( 'os/env/PWD' ) correctly points to local directory
make InterShop Mirage more useful by providing out-of-the-box configuration directives, default to
Mirage declarations must use keys intershop/mirage/*
simplify Mirage implementation:
- separate into layers:
  - lowest level: only file content caching
  - intermediate level: file concatenation
  - higher level: format application (320 lines = 50% of mirage.sql, sections Modes, Readers)
- each DSK either identifies one path and a format, or else a series of DSKs for either concatenation (independent lines) or composition (interdependent lines)
- format is really a layering in itself: bits -(*utf8)-> text -(*nls)-> lines -(blanks)-> compact lines -(comments)-> compact comment-less lines -(tsv)-> fields; first two steps are implicit and non-configurable (i.e. we only deal with line-oriented text files); as soon as fields have been derived, a recurring task is to cast them to known data types
053-immutable.sql: implement generalized DynSQL trigger functions to make immutable records easier
U.variables: references in variable values are not resolved