Useful projects to check out (2017 collection)

I have been building a list of projects and packages to check out throughout last year and I decided to consolidate them as a post.

Laravel

• Laravel Auditing will track changes to Eloquent models
• Ziggy is a laravel package that outputs router information in JSON form
• Ardent, a Laravel Model extension

PHP

• churn-php analyses your codebase for potential refactoring candidates
• Behat, for testing code - https://github.com/Behat/Behat

Vue.js

• vue-good-wizard is a simple step-by-step wizard plugin
• vue-directive-tooltip is a simple tooltip plugin

SSL

• SSL security suite check

Postgresql

• pgBackRest - Back-up
• Plogical - replication 

PostgreSQL Full Text Search

PostgreSQL has a decent Full Text Search capability. And it is quite simple to implement too; just a few SQL statements and you are done.

You want to implement your search function using Full Text Search because this method is comparatively faster than pattern matching with '%'. The 'tsvector' and 'gin' processes indexes your values and makes it easier for your DBMS to retrieve them.

To the SQL code (assuming you have a system schema with a clients table):

 1 - Add a search field to the table:

ALTER TABLE system.clients ADD COLUMN search TSVECTOR;

 2 - Convert the existing 'name' and 'email' fields into a TSVECTOR stored in the new search field:

UPDATE system.clients SET search = to_tsvector(name || email);

 3 - Index the search field:

CREATE INDEX search_client_index ON system.clients USING gin(search);

 4 - Create a trigger so that whenever the fields are updated then the search field is updated too:

CREATE TRIGGER update_ticket_search BEFORE INSERT OR UPDATE ON system.clients FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(search,"pg_catalog.english",title,email);

 5 - To search for entries you run the following code:

SELECT * FROM system.tickets WHERE search @@ to_tsquery('scrubber');

Resources:

•  Linux Gazette article on full text search features in PostgreSQL

PERL script to connect and test PostgreSQL database

I decided to follow up my last getopts script about PERL scripting (which greatly improved upon my original PERL efforts) with a script that will attempt to connect to a PostgreSQL database and test if some tables exist or not.

I won't go into too much details and just paste the code here...

#!/usr/bin/perl -w
#
# This is a perl script that uses warnings (hence the -w flag)
#
# This perl scripts aims to test the database connections
# and ensure the schema conforms to the standard.

# Use strict perl
use strict;

# Use the Getopt library
use Getopt::Std;

# Creates a hash variable where we can store our command line options
my %options=();

# Grab the list of command line options (some with optional arguments,
#   denoted by a ':')
#   -u {username}   : Sets the username
#   -p {password}   : Sets the password
#   -H {hostname}   : Sets the hostname
#   -D {database}   : Sets the database
#   -h              : Displays the help
#   -d              : Debugging
getopts("hdu:p:H:D:", \%options);

# Sets some variables based on command line options, otherwise use defaults
my $username = (defined $options{u}) ? $options{u} : 'test';
my $password = (defined $options{p}) ? $options{p} : 'test';
my $hostname = (defined $options{H}) ? $options{H} : 'localhost';
my $database = (defined $options{D}) ? $options{D} : 'testdb';

# Set some environment variables
$ENV{PGPASSWORD}=$password;

# Output the display menu if asked for
if (defined $options{h}) {
    print "Test the database connections and ensure the schema conforms to the standard\n\n";
    print "usage: test.pl [-u <username] [-p <password>] [-H <hostname>]\n\n";
}

# Display the passed variables for debugging purposes
print "USERNAME: " .$username . "\nPASSWORD: " . $password . "\nHOSTNAME: " . $hostname . "\nDATABASE: " . $database . "\n\n" if defined $options{d};

# The auth tables
my @tables = qw(system.session system.logs system.users);

# Loop through all our declared tables
foreach (@tables)
{
  psql_command($_);
}

# Clear some environmental variables
delete $ENV{PGPASSWORD};

# Exit from the system
exit 0;

# This subroutine runs a psql command
sub psql_command
{
  # Grab some parameters
  my ($table) = @_;
  if(!( defined($table)))
  {
    die "psql_command() was passed some bad arguments!\n";
  }

  my $output = `/usr/bin/psql -U $username -h $hostname -w -d $database -c 'SELECT * FROM $table' 2>&1`;
  if ( $? == -1 )
  {
    print "!!ERROR!! : $!\n";
  } else {
    if (($? >> 8) > 0)
    {
      print "!!ERROR!! : $table does not exist\n";
    } else {
      print "$table exists\n";
    }
  }

  return 0;
}

References:

• Perl Primer by Pat Gunn
• The Perl Tutorial by tiztag.com
•  The getopts howto by Alex Batko
• http://www.perldoc.com is a great documentation source
• O'Reilly's Programming Perl is a good book on the subject

PostgreSQL: Basic Database User Schema

The following is a basic schema for creating a user authentication system in PostgreSQL. Adjust to your particular use case:

CREATE FUNCTION update_modified() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.modified = now();
RETURN NEW;
END;
$$;

CREATE TABLE users (
user_id integer NOT NULL,
username character varying(64) NOT NULL,
password character varying(255) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL,
activated smallint DEFAULT 0 NOT NULL,
banned smallint DEFAULT 1 NOT NULL
);

CREATE SEQUENCE users_user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;

SELECT pg_catalog.setval('users_user_id_seq', 1, false);

CREATE TABLE person (
person_id integer NOT NULL,
firstname character varying(64) NOT NULL,
lastname character varying(64) NOT NULL,
othername character varying(64) NOT NULL,
date_of_birth date NOT NULL,
gender character(1) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE person_address (
address_id integer NOT NULL,
tag character varying(24) NOT NULL,
address character varying(128) NOT NULL,
locality character varying(64) NOT NULL,
region character varying(32) NOT NULL,
country character varying(32) NOT NULL,
code character varying(8),
person_id integer NOT NULL
);

CREATE SEQUENCE person_address_address_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_address_address_id_seq OWNED BY person_address.address_id;

SELECT pg_catalog.setval('person_address_address_id_seq', 1, false);

CREATE TABLE person_email (
email_id integer NOT NULL,
tag character varying(24) NOT NULL,
email character varying(128),
person_id integer NOT NULL,
CONSTRAINT proper_email CHECK (((email)::text ~* '^[A-Za-z0-9._%-]+@(?:[A-Za-z0-9.-]+\.)+[A-Za-z]+::text'))
);

CREATE SEQUENCE person_email_email_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_email_email_id_seq OWNED BY person_email.email_id;

SELECT pg_catalog.setval('person_email_email_id_seq', 1, false);

CREATE SEQUENCE person_person_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_person_id_seq OWNED BY person.person_id;

SELECT pg_catalog.setval('person_person_id_seq', 1, false);

CREATE TABLE person_phone (
phone_id integer NOT NULL,
tag character varying(24) NOT NULL,
phone character varying(32),
person_id integer NOT NULL
);

CREATE SEQUENCE person_phone_phone_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_phone_phone_id_seq OWNED BY person_phone.phone_id;

SELECT pg_catalog.setval('person_phone_phone_id_seq', 1, false);

ALTER TABLE ONLY users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass);

ALTER TABLE ONLY person ALTER COLUMN person_id SET DEFAULT nextval('person_person_id_seq'::regclass);

ALTER TABLE ONLY person_address ALTER COLUMN address_id SET DEFAULT nextval('person_address_address_id_seq'::regclass);

ALTER TABLE ONLY person_email ALTER COLUMN email_id SET DEFAULT nextval('person_email_email_id_seq'::regclass);

ALTER TABLE ONLY person_phone ALTER COLUMN phone_id SET DEFAULT nextval('person_phone_phone_id_seq'::regclass);

ALTER TABLE ONLY users
ADD CONSTRAINT user_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY users
ADD CONSTRAINT username_key UNIQUE (username);

ALTER TABLE ONLY person_email
ADD CONSTRAINT email_pkey PRIMARY KEY (email_id);

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_pkey PRIMARY KEY (address_id);

ALTER TABLE ONLY person
ADD CONSTRAINT person_pkey PRIMARY KEY (person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT phone_pkey PRIMARY KEY (phone_id);

CREATE TRIGGER update_users_modified BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_modified();

CREATE TRIGGER update_person_modified BEFORE UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE update_modified();

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_email
ADD CONSTRAINT person_email_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT person_phone_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

You can check out the other parts of my PostgreSQL series including an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases.

References:

• Library of free database models from databaseanswers.org
• Moodle's user schema
• Schema for an invoice system by stackoverflow.com

CodeIgniter web app authentication with PostgreSQL and TankAuth

Some of you may note that I had earlier created my own simple authentication system in CodeIgniter. However there comes a time when you just need something fully featured by you don't have the time create it yourself; step into TankAuth (it is also a hosted project on GitHub).

This post will focus on how to get TankAuth working with PostgreSQL .

Some problems....

TankAuth was designed around a MySQL database, and hence some SQL commands won't work. This means that you will get some odd behavior; unfortunately fixing all of these issues is outside the scope of this post.

Some Prerequisite knowledge...

I am going to dive right into the deep end here so if you don't know anything about PostgreSQL, PHP, CodeIgniter or Relational Database theory I strongly suggest you go and do some quick research.

I have a series on PostgreSQL that include an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases. As for PHP, you can check out my blog posts such as my AJAX with JQuery and PHP tricks. You can also check out this list of useful CodeIgniter Tutorials.

Setting up PostgreSQL

We are going to assume that you have already installed PostgreSQL and have correctly download and setup CodeIgniter (and it is correctly serving the default web page). Make sure you have installed the PHP PostgreSQL plug-ins (php5-pgsql and php5-odbc).

Use this script to check if you have correctly set-up PostgreSQL and PHP.

Open up a terminal and create a user and database for CodeIgniter and TankAuth like we did in this earlier post:

createuser -h localhost -U postgres tankauth -W -S -D -R -P

createdb -h localhost -U postgres -O tankauth -W  tankauthdb

NOTE: These commands assume that you have password protected the postgres user; if you have a default install you can ditch the -W trigger.


Once we have created a user and a database, connect to the database server via the terminal command:

psql -h localhost -d tankauthdb -U tankauth -W

Now create a schema for our project to use. In the psql terminal run the following:

tankauthdb=> CREATE SCHEMA codeigniter;

PostgreSQL is now prepped and ready for CodeIgniter...

Setting up CodeIgniter

To get CodeIgniter working with PostgreSQL you will simply need to edit two configuration files. First, edit ./application/config/database.php with the following:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'tankauth';
$db['default']['password'] = 'tankauth';
$db['default']['database'] = 'tankauthdb';
$db['default']['dbdriver'] = 'postgre';
$db['default']['dbprefix'] = 'codeigniter.';

Now we will autoload the database (just because it is easier than calling it for every controller); edit ./application/config/autoload.php with:

$autoload['libraries'] = array('view', 'database');

And that's it! CodeIgniter will now connect to PostgreSQL.

Setting up TankAuth

1. Download and extract TankAuth.
2. Copy the application folder content to your CI application folder.
3. Copy the captcha folder to your CI folder. Make sure this folder is writable by web server.
4. Open the application/config/config.php file in your CI installation and change $config['sess_use_database'] value to TRUE.
5. Create an encryption key in application/config/config.php and editing $config['encryption_key']
6. Finally turn off captcha's in by editing application/config/tank_auth.php with $config['captcha_registration'] = FALSE;

Now we will need to create the databases that TankAuth requires. First we will create a table for storing CodeIgniter sessions:

CREATE TABLE codeigniter.ci_sessions
(
  session_id character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL DEFAULT '0',
  ip_address character varying(16) COLLATE pg_catalog."en_AU.utf8" NOT NULL DEFAULT '0',
  user_agent character varying(150) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_activity integer NOT NULL DEFAULT 0,
  user_data text COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  CONSTRAINT ci_sessions_pkey PRIMARY KEY (session_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.ci_sessions
  OWNER TO tankauth; 

This will create a table for recording login attempts AND a function and trigger for automatically updating the timestamp:

CREATE TABLE codeigniter.login_attempts
(
  id serial NOT NULL,
  ip_address character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  login character varying(50) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  "time" timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT login_attempts_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.login_attempts
  OWNER TO tankauth;

CREATE OR REPLACE FUNCTION codeigniter.update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.time = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_login_attempts_modtime BEFORE UPDATE ON codeigniter.login_attempts FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_column();

This will create a table for tracking users who choose to use the autologin feature (and the requisite triggers):

CREATE TABLE codeigniter.user_autologin
(
  key_id character(32) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  user_id integer NOT NULL DEFAULT 0,
  user_agent character varying(150) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_ip character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_login timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT user_autologin_pkey PRIMARY KEY (key_id, user_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.user_autologin
  OWNER TO tankauth;

CREATE OR REPLACE FUNCTION codeigniter.update_modified_login()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_login = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_autologin_modtime BEFORE UPDATE ON codeigniter.user_autologin FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_login();

This will create our user profiles:

CREATE TABLE codeigniter.user_profiles
(
  id serial NOT NULL,
  user_id integer NOT NULL,
  country character varying(20) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  website character varying(255) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  CONSTRAINT user_profiles_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.user_profiles
  OWNER TO tankauth;

And finally this is our users login table:

CREATE TABLE codeigniter.users
(
  id serial NOT NULL,
  username character varying(50) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  password character varying(255) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  email character varying(100) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  activated smallint NOT NULL DEFAULT 1,
  banned smallint NOT NULL DEFAULT 1,
  ban_reason character varying(255) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_password_key character varying(50) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_password_requested timestamp without time zone DEFAULT NULL,
  new_email character varying(100) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_email_key character varying(50) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  last_ip character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_login timestamp without time zone NOT NULL DEFAULT now(),
  created timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT user_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.users
  OWNER TO tankauth;

CREATE TRIGGER update_users_login BEFORE UPDATE ON codeigniter.users FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_login();

CREATE OR REPLACE FUNCTION codeigniter.update_modified()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_modtime BEFORE UPDATE ON codeigniter.users FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified();

And that is TankAuth set up!!!

One more thing...

For those keeping track, they may have noticed my warning about TankAuth using MySQL specific functions. To get the basic stuff working, you will need to do edit application/models/tank_auth/login_attempts.php and replace:

$this->db->or_where('UNIX_TIMESTAMP(time) <', time() - $expire_period);

with:

$this->db->or_where('extract(epoch FROM time) <', time() - $expire_period);

And you are good to go!

PostgreSQL: Creating Users and Databases

This is a very, very quick blog post regarding how to create a user and database in PostgreSQL through the command line prompt.

To do this you just need to run two commands:

createuser -h localhost -U postgres testuser -W -S -D -R -P

createdb -h localhost -U postgres -O testuser -W  test_db

These commands assume you have password protected the local postgres user (if not, you can drop the -W trigger).

A quick overview of the createuser command:

• -U tells the command which user to perform the command with (NOT the user to create)
• -S tells us that the new user is NOT have superuser privileges (as opposed to the lower case -s, which do have superuser privileges)
• -D tells us that the new user is NOT have create database privileges (as opposed to the lower case -d, which do have superuser privileges)
• -R tells us that the new user is NOT have create roles privileges (as opposed to the lower case -r, which do have superuser privileges)
• -P prompts the command to immediately prompt for a password for the new user.
• -W will prompt the password for the user that will run the command (i.e. the username passed with the -U trigger)
• -h tells the commands which host to connect to.

The createdb command only differs with the -O trigger, which tells the command which user will own the new database.

And that's it!

Other parts of PostgreSQL series include an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases.

PostgreSQL: Improving performance

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, useful functions and operators, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases.

• Although PostgreSQL does a good job of maintenance of tuning. However, it is useful to analyses the systems real-time query use of indexes. There are plenty of tools available to profile your server, but the usefulness depends on the requirements of your particular system.
  
  NOTE: Always performing tuning on real data. Test data will just tell you what will be optimal for the test data only.


• EXPLAIN is a tool that shows the execution plan of a statement and the associated costs (measured in disk page fetches). You can add the options ANALYZE (actually executes the statement; no longer uses estimates), and VERBOSE (display additional information).
  
  A good explanation on how to use EXPLAIN is provided by the documentation.
  

  EXPLAIN [ANALYZE] [VERBOSE] statement



• ANALYZE collects statistics about the contents of tables and stores the results in a system catalog. These statistics are then used by the planner in selecting an appropriate query plan.
  
  It is useless to conduct performance tuning without first running the ANALYZE command. Otherwise, any results gathered will be generating using default values that are sure to be wrong.
  

  ANALYZE [VERBOSE] [table]



• There are a number of useful views to display statistical information about the server:
  

  View	Description
  pg_stat_activity	One row per server process
  pg_stat_database	One row per database
  pg_stat_database_conflicts	Show standby server database conflicts due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers and deadlocks.
  pg_stat_all_tables	Show all tables in the current database
  pg_stat_all_indexes	Show all indexes in the current database
  pg_statio_all_tables	Show all the tables' disk io statistics
  pg_statio_all_indexes	Show all the indexes' disk io statistics



• The default configuration of PostgreSQL is designed to work on a wide variety of hardware and software installations and is most definitely not optimal for your system. You should edit the postgresql.conf file to increase the values of shared_buffers, effective_cache_size, sort_mem, max_fsm_pages and max_fsm_relations.


• The physical memory and disks will most probably be the slowest thing in your system. You should always tweak your hardware to generate the optimal performance in your system.

References:

• Examining indexes to improve performance from the PostgreSQL documentation
• Performance tips chapter from the PostgreSQL documentation
• 5-minute introduction to PostgreSQL performance tuning by Gregory Smith
• PostgreSQL performance tuning by Christopher Browne
• Performance tuning PostgreSQL by Frank Wiles
• Performance checklist by Selena Deckelman

PostgreSQL: Check if Procedural Language is installed

To check if a procedural language is installed on PostgreSQL, just run the following:

SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql';

Values for lanname can be one of the following:

• plsql
• pltcl
• plperl
• plpython

References:

• PostgreSQL documentation on procedural languages
• This SQL code snippet from polak.ro

PostgreSQL: Functions & Operators

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases

• The usual logical operators of AND, OR, and NOT are available. However, SQL uses a three-valued logic system of TRUE, FALSE and NULL (which represents "unknown"). You should therefore design your system to handle a NULL result.


• The usual comparison operators of >, <, >=, <=, = and <> (or !=, but the parser resolves this to <> anyway) are available.


• The BETWEEN construct allows you to test a value between a range (NOT BETWEEN tests if a value is not within a range)
  

  a BETWEEN x AND y

  is equivalent to
  

  a >= x AND a <= y



• The following extra comparison operators will always resolve to a TRUE or FALSE value:
  

  a IS NULL
  a IS NOT NULL
  a IS TRUE
  a IS NOT TRUE
  a IS FALSE
  a IS NOT FALSE
  a IS UNKNOWN
  a IS NOT UNKNOWN



• Pattern matching can be achieved through LIKE, SIMILAR TO, or POSIX style regular expressions.
  
  LIKE is the simplest pattern matching technique. The _ represents any single character, while a % represents a sequence of characters.
  
  SIMILAR TO is a hybrid approach between LIKE and regular expressions. It still uses the _ and %, but additionally uses symbols borrowed from regular expressions like *, ?, {} and [] (see the documentation for an in-depth explanation).
  
  Regular expression support is also provided, but is an extensive topic that would be too lengthy to include here. A great reference can be found at regular-expressions.info


• PostgreSQL provides a wide array of date & time functions. These include current_date, current_time, and current_timestamp.
  
  Another useful function is age(timestamp), which will subtract the timestamp from the current_date and output a result in years, months and days.


• A CASE expression exists for conditional statements. expression is the SQL expression, value is the matching result of the expression, and result is the value to return
  

  CASE expression
  

  WHEN value THEN result

  [WHEN....]

  [ELSE result]

  END



• The general purpose window functions will only work if invoked via the window function syntax (that is, the OVER clause is required). In addition to in-built aggregate functions, the following general purpose functions will work:
• row_number(): number of the row within the partition
• first_value(field): returns the value of the field from the first row of the window frame
• last_value(field): returns the value of the field from the last row of the window frame
• nth_value(field): returns the value of the field from the nth row of the window frame


• There are many functions to get the current system state, but here are a few of the useful ones:
• version(): Version info
• current_user: Username of the current execution context
• current_database(): Name of the database in use
• current_schema[()]: Name of the current schema
• inet_client_addr(): The address of the remote connection
• inet_client_port(): The port of the remote connection
• pg_conf_load_time(): Configuration loading time


• There also exists many administration functions that allow to configure PostgreSQL at runtime. You can also get other running information from the system such as the physical size of databases, initiate back-ups and recoveries, and signal actions to the server.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• Regular Expressions reference

PostgreSQL: Advanced Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu and an overview of some basic concepts). Here I will briefly outline some more concepts about the database.

You can also check out my notes on designing relational databases

• The purpose of an index is to allow the RDBMS to locate the row efficiently. However maintaining an index requires some overhead, so it is not worthwhile to index all fields in a table. In addition, since the index is synchronized when the table is updated it is recommended to remove indexes that are seldom or never used.


• Although a multi-column index can be specified (up to 32 columns), it is generally not recommended to do so unless in certain circumstances. For instance, a multi-column index is useful if you are constantly making queries that references the two or more fields. This is because multi-column indexes are larger and slower than querying a single column index.


• Unique indexes with NULL values are not considered equal in PostgreSQL (you will need to specify the NOT NULL clause to restrict NULL values for this field)


• Partial indexes allow you to index only the field values that you search frequently, and not the whole table. It offers some benefits as your index will not be as big as if you indexed the whole table, but lost out if you every need to query something outside of the index parameters.


•  Data consistency is maintained by following the MVCC approach (MultiVersion Concurrency Control). Each transaction will see a snapshot of the data as it was, regardless of the current state of the underlying data. This isolates the transaction, preventing it from seeing inconsistent data that is produced from concurrent transactions.
  
  MVCC's major advantage is that locks for read transactions do not conflict with locks for write transactions, and vice versa.


• You can explicitly lock tables or rows when the MVCC approach does not produce desirable behavior. However by explicitly locking resources you increase the risk of producing deadlocks in the system.


• Messages generated by the server are assigned a five-character error code that follows the SQL standard for SQLSTATE codes. This standard states that the first two characters denote the class of the message, while the last three characters denote the specific condition.


• The followin class codes have been defined:
  
  

  CODE	DESCRIPTOR
  00	Successful completion
  01	Warning
  02	No Data (also serves as a warning)
  03	SQL Statement not yet complete
  08	Connection exception
  09	Triggered action exception
  0A	Feature not supported
  0B	Invalid transaction initiation
  0F	Locator Exception
  0L	Invalid Grantor
  0P	Invalid Role specification
  20	Case not found
  21	Cardinality Exception
  22	Data Exception
  23	Integrity Constraint Violation
  24	Invalid Cursor state
  25	Invalid Transaction State
  26	Invalid SQL Statement Name
  27	Triggered Data Change Violation
  28	Invalid Authorization Specification
  2B	Dependant Privileges Descriptors still exist
  2D	Invalid Transaction Termination
  2F	SQL Routine Exception
  34	Invalid Cursor Name
  38	External Routine Exception
  39	External Routine Invocation Exception
  3B	Savepoint Exception
  3D	Invalid Catalog name
  40	Transaction rollback
  42	Syntax Error or Access Role Violation
  44	WITH CHECK OPTION Violation
  53	Insufficient Resources
  54	Program Limit Exceeded
  55	Object not in prerequisite state
  57	Operator Intervention
  58	System Error (external to PostgreSQL)
  F0	Configuration File Error
  HV	Foreign Data Wrapper Error
  P0	PL/pgSQL Error
  XX	Internal Error

  
  


• Unfortunately you cannot raise exceptions directly within the PostgreSQL server. You could use a procedural language within the server (such as PL/pgSQL), but this will limit the portability and compatibility of your code with other RDBMS systems.


• In PL/pgSQL, you would use the RAISE statement to report exceptions and messages.
  

  RAISE [level] 'message' [, value] USING ERRCODE = 'SQLSTATE';

  where:
  level = DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION
  message = String (% denotes a placeholder for an optional value
  value = field value
  SQLSTATE = Error code conforming to the SQLSTATE convention


• A SQL query will undergo the following process in order to generate a result:
  1. A connection from the application to the server is established. The application then sends a query to the server and waits for a result
  2. The parser checks the query for correct syntax and creates a query tree.
  3. The rewrite system takes the query tree and looks for any rules (stored in the system catalog) to apply to the tree. It performs the transformations given in the rule bodies.
  4. The planner/optimizer takes the rewritten query tree and creates a query plan. It does this by creating all possible paths leading to the same result, and then calculates the estimated cost of each plan. It will then select the cheapest path and prepare a query plan tree for the executor.
  5. The executor recursively steps through the plan tree and retrieves rows in the way represented by the plan. When it is complete it hands the result back to the application.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• ISO/IEC 9075-1:2011, or the SQL standard (it costs money, though) 
• PL/pgSQL exception handling by stackoverflow.com

PostgreSQL: Basic Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu can be found here). Here I will briefly outline some basic concepts about the database.

You can also check out my notes on designing relational databases.

• Views are a concept that is common to almost all relational database (RDBMS) systems. The best way to think of a view is akin to an object-orientated interface; an abstraction that takes away all the underlying structural details and provides a generic way to access data and functions.


• Foreign keys help maintain the referential integrity of your data. It links a record to another record stored in a different relation.You can additionally define the constraints and associations for the link.


• Transactions are atomic operations where all the steps happen or none of the steps happen. A transaction also guarantees the the result of the operation has been logged to physical storage so that no data is lost in the case of a system crash.


• In PostgreSQL, a transaction block starts at the BEGIN command and completes when it reaches the COMMIT command. You can place as many SQL commands as you wish between these two block commands. If you want to cancel the update and reset the database, you just issue a ROLLBACK command.
  
  An example of a transaction block is as follows:
  

  BEGIN;
  UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
  UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
  UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
  UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
  COMMIT;

• Window functions perform a calculation across a relational table. It is similar to aggregate functions like count() and min(), however where these functions return a single output row a window function retains the identities of all rows in the relation. PostgreSQL defines some in-built aggregate functions that can be used within a window function.


• The OVER clause in a window function specifies how the rows are split up and processed. For instance, you can specify a PARTITION BY list that groups records by a field value. The aggregate function then processes these records by the group rather than all records in the relation. This is a relatively complex topic, so it is strongly suggested that you read the PostgreSQL documentation on this topic.


• Inheritance is a concept that has carried over from object-orientated databases. However, it is not feature-complete and has some serious caveats that severely limits the functionality of this feature. You should read the documentation to see if the inheritance feature is suitable for your needs.


• You can alternatively use views, foreign key constraints and other database concepts to mimic inheritance in your database.


• PostgreSQL supports the idea of constraints for table fields. This allows us to fine-tune the allowable data that can be stored beyond just defining data types. The following constraints have been defined:
  
  

  Type	Description
  CHECK	The value for a column must satisfy a Boolean condition. You can also specify a name by using the optional CONSTRAINT clause. CREATE TABLE products( price numeric CONSTRAINT positive_price CHECK (price >0) );
  NOT NULL	Simply states that a value cannot be NULL
  UNIQUE	Simply states that the value is unique across all record rows
  PRIMARY KEY	Similar to a combination of UNIQUE and NOT NULL constraints, however it specifies that the field can be used as a unique identifier for the row. There can only be one PRIMARY KEY entry for each table.
  REFERENCES (Foreign Key)	The REFERENCES clause is used to specify a foreign key relationship between two tables, which will maintain referential integrity of the data. When this clause is used, it will become impossible to create a row when the foreign key does not exist in the referenced table. You can also specify what happens to the row when the referenced field is updated or deleted by using the ON UPDATE and ON DELETE clauses, respectively. The actions you can define are RESTRICT, CASCADE, NO ACTION, SET NULL and SET DEFAULT. See the documentation for more information on how this works.

  
  


• Each table is created with several system columns. These are reserved and cannot be used as names for user-defined columns. System columns store information such as the unique id of the table, the unique id of the record in the table, and the physical location of the row.
• The ALTER TABLE command allows you to modify an existing table. This is useful if the table already contains data or is being referenced by other tables.
• PostgreSQL Schemas can be thought of as table namespaces. You can logically group tables in a database by schema, and you can have identically named tables existing in two different schemas.
  By Default, a new database will automatically have a 'public' schema that is accessible by all users with privileges on the database.
• Partitioning a table is done via table inheritance (so read up on how PostgreSQL does inheritance before using this feature!). By physically partitioning a table you can increase query times (since the server only needs to search the partition and not the whole table), and you can move less-used data to cheaper disks.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• A decent explanation as to why there is a public schema created by default from stackoverflow.com

PostgreSQL 9.1 and Ubuntu 12.04

This is just a quick guide on how to get started using PostgreSQL on Ubuntu.

Installing

1. Install the PostgreSQL server using apt:
   

   sudo apt-get install postgresql

2. Install the contrib add-on package. This provides additional tools and features, such as improved logging and administration functions.
   

   sudo apt-get install postgresql-contrib

3. Install the GUI admin interface pgadmin3:

   sudo apt-get install pgadmin3

4.  Edit the file /etc/postgresql/9.1/main/postgresql.conf to allow TCP/IP connections to the server. Just uncomment the following line in the file:
   

   listen_addresses = 'localhost'

5. Start the database
   

   sudo service postgresql start

6.  To automatically start the server run the following command:
   

   sudo update-rc.d postgresql defaults

7. Put a password on the default postgresql user by opening up a connection to the database:
   

   sudo -u postgres psql template1

   And then running the following SQL command:

   ALTER USER postgres WITH PASSWORD 'password';

8. Set up the same password for the system postgres user:
   

   # Delete the existing postgres user password
   sudo passwd -d postgres
   
   # Set the user password
   sudo su postgres -c passwd

    

Interfacing with PostgreSQL

• You can create a database directly from the command line (this assumes that your linux user has a corresponding account to use PostgreSQL):
  

  createdb newdb

• To create a new database with a user that has full rights on that database:

  # Create the user
  sudo -u postgres createuser -D -A -P newuser# Create the database
  sudo -u postgres createdb -O newuser newdb

• The corresponding command to delete a database is:
  

  dropdb olddb

• Accessing the database through the commandline:
  

  psql newdb
  #OR you could use
  sudo -u postgres psql newdb#OR you could use
  psql -h localhost -U postgres -W newdb

  
  NOTE: If the command prompt shows "=#", then you are using an admin account which bypasses normal access controls. This is potentially dangerous. Your average account should be displaying "=>" as part of the prompt.
• In-built psql commands are prefaced with a '\':
  
  • \h displays the help for SQL commands
  • \? displays the help for psql-specific commands
  • \q exits the terminal interface for psql
• Some useful in-built SQL statements:
• SELECT version(); will display the current PostgreSQL version
• SELECT current_date; will display the current date of the system
• SELECT now(); will display the date and time
• SELECT now()::date; will only display the date portion
• SELECT now()::time; will only display the time portion
• count(), sum(), avg(), max(), and min() are special aggregate functions you can perform on fields in your SQL statement.

References:

• How to set up PostgreSQL in Ubuntu by ubuntugeek.com
• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• Automatically starting services in Debian