InterShop
An incipient application foundation built on Postgres, with sprinkles of JavaScript and plPython3u
Table of Contents generated with DocToc
- Installation
- InterShop Commands
- InterShop AddOns
- The InterShop Dev Cycle
- No More FDWs FTW
- The MIRAGE File Mirror Module
- VNRs
- Invariants
- Catalog
- To Do
Installation
Dependencies
Postgres
- https://wiki.postgresql.org/wiki/Apt
- https://askubuntu.com/questions/445487/what-debian-version-are-the-different-ubuntu-versions-based-on
- https://www.linuxmint.com/download_all.php
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
-
intershop-package.json
Format of - 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 be1.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 InterShopplpython3u
subsystem ('support' meaning 'supporting plPython3u library') -
"rebuild"
—to be executed when the DB is rebuilt from scratch with theintershop 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 install
able - 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
Invariants
See documentation/invariants.md.
Catalog
To Do
-
[ ] Remove npm dependency
squel
, replace by other query builder b/c ofnpm 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? -
[X] confusingly,
rake --tasks
does not display tasks, but dumpsintershop.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 inrake -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
- [X]
intershop reconstruct
(in addition to or as expansion ofrebuild
)
- [ ]
-
[ ] Mirage documentation
-
[X] Mirage: local file paths like
./README.md
do not work although¶( 'os/env/PWD' )
correctly points to local directory -
[X] 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
- [ ] separate into layers:
-
[ ]
053-immutable.sql
: implement generalized DynSQL trigger functions to make immutable records easier -
[ ]
U.variables
: references in variable values are not resolved