SQL Database Programming

Design Principles

• ​http://it.toolbox.com/blogs/database-soup/wrecking-your-database-33298
• ​https://www.percona.com/blog/2009/03/01/kiss-kiss-kiss/
• Book: ​https://pragprog.com/book/bksqla/sql-antipatterns
• SQL antipatterns : avoiding the pitfalls of database programming ​WorldCat​The Pragmatic Bookshelf
  • Review ​O'Reilly
• SQL design patterns : the expert guide to SQL programming ​WorldCat ​Rampant

Tree or Hierarchical Data

• nested-set, closure-table and materialized-path.
• Tree_(data_structure) ​Wikipedia
• Joe Celkos: Trees and Hierarchies in SQL for Smarties ​WorldCat ​Table of Content (pdf)
• SQL - How to store and navigate hierarchies? ​stackoverflow
• How to represent a data tree in SQL? ​stackoverflow
• Models for hierarchical data ​SlideShare
• How to represent a tree structure numerically in SQL Server ​yellowpencil (short HOWTO)
• Closure Table – Store Hierarchical Data Seamlessly | PostgreSQL ​technobytz (short HOWTO)
• What is the most efficient/elegant way to parse a flat table into a tree? ​stackoverflow
• Storing hierarchical data: Materialized Path ​Blog

Blobs for documents

• FAQ: ​http://stackoverflow.com/questions/211895/storing-documents-as-blobs-in-a-database-any-disadvantages

Serialized Objects

• ​https://www.percona.com/blog/2010/01/21/when-should-you-store-serialized-objects-in-the-database

SQL

• ​https://en.wikibooks.org/wiki/Structured_Query_Language/Introduction_to_SQL
• ​https://en.wikipedia.org/wiki/SQL

Views

• ​http://www.postgresql.org/docs/current/static/sql-createview.html
• ​http://www.doctrine-project.org/2009/06/19/using-views-with-doctrine.html (is it always valid?)
• ​http://www.postgresql.org/docs/current/static/rules-materializedviews.html

sql procedural language

• ​https://en.wikipedia.org/wiki/SQL#Procedural_extensions
• ​https://en.wikipedia.org/wiki/SQL/PSM
• ​https://en.wikipedia.org/wiki/Stored_procedure

Supported features

Data Types

json

• SQLite ​https://www.sqlite.org/json1.html

XML

• Seems not yet to be in Firebird

Embedded Database Programming

• ​https://en.wikipedia.org/wiki/Embedded_database
  • QDBM, BDB, ​http://php.net/manual/en/function.dba-handlers.php
  • ​php-tokyo-tyrant​
  • ​php7.0-interbase (php driver does not include embeded version of interbase) ​libfbembed2.5
    • ​https://wiki.documentfoundation.org/Development/Base/FirebirdSQL
    • ​https://en.wikipedia.org/wiki/InterBase
    • ​https://en.wikipedia.org/wiki/Firebird_%28database_server%29
  • ​http://www.haildb.com/
  • ​php5-midgard2​

sqlite

• ​SQL Features That SQLite Does Not Implement
• ​https://www.sqlite.org/json1.html
• ​https://www.sqlite.org/fts5.html
• ​Please enable FTS5 support ​https://tracker.debian.org/media/packages/s/sqlite3/rules-3.11.0-3

HSQLDB (Hyper SQL Database)

• ​https://en.wikipedia.org/wiki/HSQLDB

Postgres

• ​https://wiki.debian.org/PostgreSql
• ​http://db.cs.berkeley.edu/papers/ERL-M85-95.pdf
• ​http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.133.8073&rep=rep1&type=pdf
• ​https://dl.acm.org/citation.cfm?id=2834495

Performance

• ​http://fr.slideshare.net/pgconf/five-steps-perform2009

Timing

• ​Re: What does \timing measure?
• ​http://www.postgresql.org/docs/9.4/static/runtime-config-statistics.html
• ​http://stackoverflow.com/questions/17653884/postgres-query-execution-time
• ​http://stackoverflow.com/questions/9063402/get-execution-time-of-postgresql-query

Procedural Language

• ​https://wiki.postgresql.org/wiki/PL_Matrix
• PL/pgSQL
  • Included in postgresql~ basic Debian Package
  • ​http://www.postgresql.org/docs/9.3/static/plpgsql.html
  • ​https://en.wikipedia.org/wiki/PL/pgSQL
    • ​http://postgres.cz/wiki/PL/pgSQL_(en)
• ​postgresql-plpython-9.4 more stable and more popular that PL/Lua
• ​postgresql-plpython3-9.4
• ​postgresql-9.4-pllua ​https://github.com/pllua/pllua ​http://wiki.alpinelinux.org/wiki/Pllua
• ​postgresql-pltcl-9.4
• ​postgresql-9.4-plsh

General information and Feature Lists

• ​PostgreSQL: Up and Running
• ​Advertisement by a provider

Release Notes and Documentation

• ​PostgreSQL 9.2 released

MySQL

• ​https://en.wikipedia.org/wiki/MySQL

Stored Procedure

• MySQL stored procedure programming
  • ​http://www.worldcat.org/oclc/70161318
  • ​http://www.worldcat.org/oclc/850445083
• MySQL Cookbook
  • ​http://www.worldcat.org/oclc/76905222

Firebird

• ​https://wiki.documentfoundation.org/Development/Base/FirebirdSQL
• ​http://stackoverflow.com/questions/1635273/postgres-vs-firebird