Get last order number with PostgreSQL trigger

postgresqlIn multi user applications it is not easy to serve a unique order or invoice numbers for each save process. You should check carefully several times on every database insert. But with database triggers you can get last order or invoice number easily as described below.

I have used PostgreSQL 9.3.5 version on Ubuntu for this trigger.

Create order table:

-- order table sequence
DROP SEQUENCE IF EXISTS order_order_id_seq CASCADE;
CREATE SEQUENCE order_order_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

ALTER TABLE public.order_order_id_seq OWNER TO postgres;

-- order table
DROP TABLE IF EXISTS order CASCADE;
CREATE TABLE order (
  order_id bigint DEFAULT nextval('order_order_id_seq'::regclass) NOT NULL,
  order_number bigint,
  order_date timestamp without time zone NOT NULL
  -- add other fields
);

ALTER TABLE public.order OWNER TO postgres;

Create params table. The parameters table has last order number;

-- params table sequence
DROP SEQUENCE IF EXISTS params_params_id_seq CASCADE;
CREATE SEQUENCE params_params_id_seq
  INCREMENT BY 1
  NO MAXVALUE
  NO MINVALUE
  CACHE 1;

ALTER TABLE public.params_params_id_seq OWNER TO postgres;

-- params table
DROP TABLE IF EXISTS params CASCADE;
CREATE TABLE params (
  params_id bigint DEFAULT nextval('params_params_id_seq'::regclass) NOT NULL,
  last_order_number bigint
  -- add other fields
);

ALTER TABLE public.params OWNER TO postgres;

Create trigger:

-- get last order number function
CREATE OR REPLACE FUNCTION get_last_order_number()
    RETURNS TRIGGER AS
$BODY$
DECLARE
    v_last_order_number bigint;
BEGIN
  SELECT last_order_number INTO v_last_order_number FROM params WHERE params_id = 1;
  UPDATE params set last_order_number = v_last_order_number + 1 WHERE params_id = 1;
  NEW.order_number = v_last_order_number + 1;
  RETURN NEW;
END $BODY$
    LANGUAGE plpgsql;

ALTER FUNCTION public.get_last_order_number() OWNER TO postgres;

-- last order number trigger
CREATE TRIGGER last_order_number_trigger BEFORE INSERT ON order
  FOR EACH ROW EXECUTE PROCEDURE get_last_order_number();

PostgreSQL installation and settings on Ubuntu 12.04

postgresql

Updated for Ubuntu GNU/Linux 13.04 (Raring Ringtail).

  • Install latest PostgreSQL 9.1 in console:
sudo apt-get install postgresql libpq-dev
  • PostgreSQL has a super user is called postgres. Change user to the PostgreSQL user:
sudo su - postgres
  • Change password of postgres user:
psql -d postgres -U postgres
psql (9.1.3) Type "help" for help
postgres=# alter user postgres with password 'YOUR_NEW_PASSWORD';
ALTER ROLE
postgres=# \q
#logout postgres user
logout
  • Restart the PostgreSQL server:
sudo /etc/init.d/postgresql restart

pgAdmin III: PostgreSQL administration and management tools

  • If pgAdminIII is not installed, the installation is easy:
sudo apt-get install pgadmin3
  • Open pgAdminIII and add new localhost server. Go to menu File > Add Server as the following screenshot:

pgAdmin

Set up pgAdmin III server instrumentation:
When connecting to a PostgreSQL database using pgAdmin you may receive an error letting you know that the server instrumentation is not installed.

  • Install postgresql-contrib package:
sudo apt-get install postgresql-contrib
  • Install adminpack extension:
sudo -u postgres psql
postgres=# CREATE EXTENSION "adminpack";
postgres=# \q

SQLAlchemy Tutorial 1 : Declarative MySQL Tables

SQLAlchemy Tutorial 1 : Declarative MySQL Tables

SQLAlchemy is awesome ORM and SQL Toolkit for Python. I am using it for MySQL 5.1 with MySQL specific settings.

How can i define and create MySQL tables with SQLAlchemy ORM?

I have 3 Tables :

--
-- customer db schema
-- version 1.0
--

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS customerdb;
CREATE SCHEMA customerdb;
USE customerdb;

--
-- Table structure for table country
--
DROP TABLE IF EXISTS country;

CREATE TABLE country (
	country_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	code VARCHAR(10) NOT NULL,
	name VARCHAR(50) NOT NULL,
	created_by_user VARCHAR(50) DEFAULT NULL,
	created_at DATETIME NOT NULL,
	updated_by_user VARCHAR(50) DEFAULT NULL,
	updated_at DATETIME DEFAULT NULL,
	PRIMARY KEY (country_id),
	UNIQUE KEY (code),
	UNIQUE KEY (name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table customer category
--
DROP TABLE IF EXISTS category;

CREATE TABLE category (
	category_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(50) NOT NULL,
	created_by_user VARCHAR(50) DEFAULT NULL,
	created_at DATETIME NOT NULL,
	updated_by_user VARCHAR(50) DEFAULT NULL,
	updated_at DATETIME DEFAULT NULL,
	PRIMARY KEY (category_id),
	UNIQUE KEY (name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table customers
--
DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
	customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	company_name VARCHAR(100) NOT NULL,
	category_id BIGINT UNSIGNED NOT NULL,
	contact_title VARCHAR(50) DEFAULT NULL,
	contact_first_name VARCHAR(50) DEFAULT NULL,
	contact_last_name VARCHAR(50) DEFAULT NULL,
	address TEXT DEFAULT NULL,
	city VARCHAR(50) DEFAULT NULL,
	region VARCHAR(50) DEFAULT NULL,
	postal_code VARCHAR(50) DEFAULT NULL,
	country_id BIGINT UNSIGNED DEFAULT NULL,
	phone VARCHAR(50) DEFAULT NULL,
	fax VARCHAR(50) DEFAULT NULL,
	mobile VARCHAR(50) DEFAULT NULL,
	email VARCHAR(50) DEFAULT NULL,
	homepage VARCHAR(50) DEFAULT NULL,
	skype VARCHAR(50) DEFAULT NULL,
	active BOOLEAN NOT NULL DEFAULT TRUE,
	notes TEXT DEFAULT NULL,
	created_by_user VARCHAR(50) DEFAULT NULL,
	created_at DATETIME NOT NULL,
	updated_by_user VARCHAR(50) DEFAULT NULL,
	updated_at DATETIME DEFAULT NULL,
	PRIMARY KEY (customer_id),
	UNIQUE KEY (company_name),
	KEY idx_fk_category_id (category_id),
	CONSTRAINT fk_customer_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE,
	KEY idx_fk_country_id (country_id),
	CONSTRAINT fk_customer_country FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

and SQLAlchemy Classes :

"""
db entites, mappers and db table settings
"""
from datetime import datetime
from sqlalchemy import Column, ForeignKey, create_engine
from sqlalchemy.dialects.mysql import BIGINT, DATETIME, TEXT, TINYINT, VARCHAR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm.interfaces import MapperExtension

Base = declarative_base()

class Country(Base):
    """ Country entity class """
    __tablename__ = 'country'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    } 

    # this field is in model "id", in db "country_id"
    # not null and auto_increment settings with "primary_key"
    id = Column('country_id', BIGINT(unsigned=True), primary_key=True)
    code = Column(VARCHAR(10), nullable=False, unique=True)
    name = Column(VARCHAR(100), nullable=False, unique=True)
    created_by_user = Column(VARCHAR(50))
    created_at = Column(DATETIME)
    updated_by_user = Column(VARCHAR(50))
    updated_at = Column(DATETIME)

    def __init__(self, code, name):
        self.code = code
        self.name = name

class Category(Base):
    """ Category entity class """
    __tablename__ = 'category'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    } 

    id = Column('category_id', BIGINT(unsigned=True), primary_key=True)
    name = Column(VARCHAR(100), nullable=False, unique=True)
    created_by_user = Column(VARCHAR(50))
    created_at = Column(DATETIME)
    updated_by_user = Column(VARCHAR(50))
    updated_at = Column(DATETIME)

    def __init__(self, name):
        self.name = name

class Customer(Base):
    """ Customer entity class """
    __tablename__ = 'customer'
    __table_args__ = {
        'mysql_engine': 'InnoDB',
        'mysql_charset': 'utf8'
    } 

    id = Column('customer_id', BIGINT(unsigned=True), primary_key=True)
    company_name = Column(VARCHAR(100), nullable=False, unique=True)
    # declarative foreing key setting
    # nullable = false, the customer must have a category
    category_id = Column(BIGINT(unsigned=True), ForeignKey('category.category_id',
        name="fk_customer_category", onupdate='CASCADE', ondelete='RESTRICT'), nullable=False)
    contact_title = Column(VARCHAR(50))
    contact_first_name = Column(VARCHAR(50))
    contact_last_name = Column(VARCHAR(50))
    address = Column(TEXT)
    city = Column(VARCHAR(50))
    region = Column(VARCHAR(50))
    postal_code = Column(VARCHAR(50))
	# declarative foreing key setting
    country_id = Column(BIGINT(unsigned=True), ForeignKey('country.country_id',
        name='fk_customer_country', onupdate='CASCADE', ondelete='RESTRICT'))
    phone = Column(VARCHAR(50))
    fax = Column(VARCHAR(50))
    mobile = Column(VARCHAR(50))
    email = Column(VARCHAR(50))
    homepage = Column(VARCHAR(50))
    skype = Column(VARCHAR(50))
	# this customer active or inactive?
    active = Column(TINYINT(1), nullable=False, default=1)
    notes = Column(TEXT)
    created_by_user = Column(VARCHAR(50))
    created_at = Column(DATETIME)
    updated_by_user = Column(VARCHAR(50))
    updated_at = Column(DATETIME)

    def __init__(self, company_name, category_id):
        self.company_name = company_name
        self.category_id = category_id