hamsql

Interpreter for SQL-structure definitions in YAML (YamSql) https://git.hemio.de/hemio/hamsql

Latest on Hackage:0.9.0.0

This package is not currently in any snapshots. If you're interested in using it, we recommend adding it to Stackage Nightly. Doing so will make builds more reliable, and allow stackage.org to host generated Haddocks.

GPL-3 licensed and maintained by Michael Herold

HamSql

An interpreter for SQL structure definitions in YAML (YamSql)

build status Hackage-Deps Hackage

About HamSql

HamSql is a software that parses SQL structures defined in a YAML based language and deploys them on PostgreSQL servers. It allows to maintain PostgreSQL projects in a form more similar to other programming languages.

In contrast to the CREATE OR REPLACE FUNCTION approaches, residual structures are deleted, column properties are deleted without explicit definition of the migration and the ordering imposed by dependencies is resolved automatically.

HamSql is on hackage, which means you can build it with cabal install hamsql. Please note the build requirements and details at the bottom. HamSql binaries for Linux amd64 are available as build artifacts from our build server.

HamSql can be used for

  • Neat SQL development with clearer versioning via "one object one file" principle
  • More flexible development with features like function and table templates
  • On site deployments of SQL structures
  • Off-line computation of upgrade strategies for known status quo
  • Documentation generation of SQL structures and APIs

What is there

  • hamsql install command that deploys the defined structure
  • hamsql upgrade command that updates existing structures - Per default no data are at risk during upgrade (no column/table deletion) - Overwrite via --permit-data-deletion possible - SQL command ordering that avoids dependency conflicts - Complicated dependencies are resolved via trial and error
  • hamsql doc creates a documentation of the complete sql structure - Custom templates can be provided using doctemplates known from pandoc - The build-in template creates Sphinx and Read the Docs ready .rst files
  • Code basis tailored for the support of many SQL features
  • Basic documentation of the YamSql Language

Coming soon

  • Support for views and triggers
  • Warn about name conflicts for SQL objects before deployment
  • Output defining YamSql file for each type of error

What is missing

Those are all things on the radar but the exact requirements are unclear or the workforce is missing.

  • Support for several other PostgreSQL features
  • Stable definition of YamSql
  • Support for renaming tables and columns
  • Covering other strategies required for upgrade
  • Unit and integration test as part of YamSql - Define scenarios that are loaded into the database - Define test and expected result for a function for a scenario - Define operations with expected outcome (to tests triggers etc.)

Example Project

The example project below could be deployed via

hamsql install -c postgresql:///dbname

Later changes can be pushed via

hamsql upgrade -c postgresql:///dbname

The default documentation can be written to docs/ via

hamsql doc

You can have a look at the output rendered via Sphinx.

Those are the YamSql files for the project:

# setup.yml
schemas:
 - math
schema_dirs:
 - schemas
# schema/math/schema.yml
name: math
description: |
 Some basic math in SQL
---
# schema/math/function.d/factorial.plsql
name: factorial
description: |
 Factorial function using the
 ``WITH RECURSIVE`` SQL feature.

 Logical definition::

     f(0) = 1
     f(n) = n * f (n - 1)

parameters:
 - name: p_n
   type: int
returns: int
---
RETURN (
  WITH RECURSIVE t AS (
      SELECT 1 AS f, 0 AS n
    UNION ALL
      SELECT f * (n + 1), n + 1 FROM t
  )
  SELECT f FROM t WHERE n=p_n LIMIT 1
);
---
# schema/math/function.d/erf.py
name: erf 
description: |
 Gauss error function

 This function is a wrapper for the
 Python 3 implementation.

parameters:
 - name: x
   type: float
returns: float

language: plpython3u
---
import math
return math.erf(x)

Building HamSql on Debian Stretch

To completely build HamSql from source

apt install make ghc cabal-install libpq-dev happy
make
make install

To avoid compiling all the dependencies you can use the following set of debian packages instead of the above ones

apt install \
 make \
 ghc \
 cabal-install \
 libghc-aeson-dev \
 libghc-file-embed-dev \
 libghc-network-uri-dev \
 libghc-optparse-applicative-dev \
 libghc-pandoc-dev \
 libghc-postgresql-simple-dev \
 libghc-text-dev \
 libghc-unordered-containers-dev \
 libghc-yaml-dev

Changes

v0.9.0.0
- Fixes DELETE BASE before schema definitions are read (#64)
- Fixes incorrect naming of sequences for SERIAL emulation
- Fixes bugs in table constraint statements (#59)
- Adds listing of blocking connections if database can't be deleted
- Adds project status description to README
- Adds link to example project to README
- Adds bash completion install (closes #63)
- Adds revocation of role memberships (fixes #37)
- Better internal code structure
v0.8.0.0
- Replaces pandoc with doctemplates to halve dependency compile time
Big thanks to @jgm for implementing doctemplates right away!
<https://github.com/jgm/pandoc/issues/3134>
- Adds --delete-residual-roles option (closes #30)
- Adds --sql-log-hide-rollbacks option (closes #55)
- Adds Internal libraries to exposed modules
- Adds dropping of basic role privileges (#37)
- Changes YamSql objects to Internal.Obj.* (closes #58)
- Changes to a better SqlObj and SqlContext structure (closes #60)
- Changes to treat columns as independent objects (closes #33)
- Updates README
v0.7.0.0
- Fixes all compiler warnings
- Fixes several bugs related to ROLE upgrades
- Fixes table checks missing after upgrade
- Adds --sql-log flag
- Adds --version flag
- Better SQL-Error presentation
- Better test coverage, now using pytest3
- Better usage information for CLI
- Code coverage reports
- Refactoring of many code parts
- Removes support for column templates
- Splits library from binary (cabal)
- Started API and implementation docs
v0.6.0.0
- Adds resolve complicated dependency issues
- Adds resolve change of function return type (if function can be dropped)
- Changes connection options to suppress SQL NOTICE messages
- Fixes fundamental logic error in pgsqlUpdateFragile
Without --perimit-data-deletion upgrades would not create objects etc.
- Fixes constraints in schema public are not deleted
- Renames Module to Schema
- Copletely new Library structure
- Reworked Documentation feature
- Removes avoidable dependencies
- Mostly uses Data.Text instead of String
- Renames modules to schemas
v0.5.0
- Adds proper deletion of obsolete functions
- Changes to no data deletion as default for upgrades
v0.4.1
- First release used in production
comments powered byDisqus