Skip to content

Instantly share code, notes, and snippets.

@troyk

troyk/account_migration.rb

Created May 10, 2012 19:44
Show Gist options
  • Save troyk/2655391 to your computer and use it in GitHub Desktop.
Save troyk/2655391 to your computer and use it in GitHub Desktop.
Download ZIP
Postgres is the snizzle
Raw
account_migration.rb
class CreateAccounts < ActiveRecord::Migration
def up
#return unless connection.schema_search_path == 'public'
#-- create a dummy schema so migrations will run
execute "CREATE SCHEMA crm0;"
#-- citext is cool (I think)
execute "CREATE EXTENSION IF NOT EXISTS citext;"
#-- public/shared types
execute "CREATE TYPE public.contacts_type AS ENUM ('Person', 'Organization');"
execute "CREATE TYPE public.contacts_infos_type AS ENUM ('Contact::Address', 'Contact::Phone', 'Contact::Email', 'Contact::Website');"
#-- public schema
execute <<-SQL
CREATE TABLE IF NOT EXISTS public.accounts (
id serial PRIMARY KEY,
created_at timestamp with time zone NOT NULL DEFAULT NOW(),
name citext NOT NULL
);
SQL
#-- trigger function to clone/delete a schema for accounts table
execute <<-SQL
CREATE OR REPLACE FUNCTION public.accounts_fntrig_schema() RETURNS TRIGGER AS $BODY$
DECLARE
crm0_table_name text;
act_table_name text;
BEGIN
IF TG_OP = 'INSERT' THEN
EXECUTE 'CREATE SCHEMA crm' || NEW.id ;
FOR crm0_table_name IN
SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = 'crm0'
LOOP
act_table_name := 'crm' || NEW.id || '.' || crm0_table_name;
EXECUTE 'CREATE TABLE ' || act_table_name || ' (LIKE ' || 'crm0' || '.' || crm0_table_name || ' INCLUDING ALL)';
END LOOP;
--EXECUTE 'INSERT INTO crm' || NEW.id || '.schema_migrations (SELECT * FROM crm0.schema_migrations)';
ELSIF TG_OP = 'DELETE' THEN
EXECUTE 'DROP SCHEMA crm' || OLD.id || ' CASCADE';
ELSE
RAISE EXCEPTION 'TG_OP % not supported in this trigger bro', TG_OP;
END IF;
RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;
SQL
execute "CREATE TRIGGER accounts_trig_schema AFTER INSERT OR DELETE ON public.accounts FOR EACH ROW EXECUTE PROCEDURE public.accounts_fntrig_schema()"
end
end
Raw
psql
# check out this psql console output showing how transactional ddl, triggers and bodacious
# CREATE TABLE LIKE make postgres rock for multi-tenant!
blit5=# \dn
List of schemas
Name | Owner
--------+-------
crm0 | troy
public | troy
(2 rows)
blit5=# \d accounts
Table "public.accounts"
Column | Type | Modifiers
------------+--------------------------+-------------------------------------------------------
id | integer | not null default nextval('accounts_id_seq'::regclass)
created_at | timestamp with time zone | not null default now()
name | citext | not null
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "account_users" CONSTRAINT "account_users_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id) ON UPDATE CASCADE
Triggers:
accounts_trig_schema AFTER INSERT OR DELETE ON accounts FOR EACH ROW EXECUTE PROCEDURE accounts_fntrig_schema()
blit5=# insert into accounts(name) values('Blit rocks!');
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "contact_infos_pkey" for table "contact_infos"
CONTEXT: SQL statement "CREATE TABLE crm1.contact_infos (LIKE crm0.contact_infos INCLUDING ALL)"
PL/pgSQL function "accounts_fntrig_schema" line 13 at EXECUTE statement
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "contacts_pkey" for table "contacts"
CONTEXT: SQL statement "CREATE TABLE crm1.contacts (LIKE crm0.contacts INCLUDING ALL)"
PL/pgSQL function "accounts_fntrig_schema" line 13 at EXECUTE statement
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function "accounts_fntrig_schema"
blit5=# select * from accounts;
id | created_at | name
----+------------+------
(0 rows)
blit5=# drop FUNCTION public.accounts_fntrig_schema() cascade;
NOTICE: drop cascades to trigger accounts_trig_schema on table accounts
DROP FUNCTION
blit5=# \d accounts
Table "public.accounts"
Column | Type | Modifiers
------------+--------------------------+-------------------------------------------------------
id | integer | not null default nextval('accounts_id_seq'::regclass)
created_at | timestamp with time zone | not null default now()
name | citext | not null
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "account_users" CONSTRAINT "account_users_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id) ON UPDATE CASCADE
blit5=# CREATE OR REPLACE FUNCTION public.accounts_fntrig_schema() RETURNS TRIGGER AS $BODY$
blit5$# DECLARE
blit5$# crm0_table_name text;
blit5$# act_table_name text;
blit5$# BEGIN
blit5$# IF TG_OP = 'INSERT' THEN
blit5$# EXECUTE 'CREATE SCHEMA crm' || NEW.id ;
blit5$#
blit5$# FOR crm0_table_name IN
blit5$# SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = 'crm0'
blit5$# LOOP
blit5$# act_table_name := 'crm' || NEW.id || '.' || crm0_table_name;
blit5$# EXECUTE 'CREATE TABLE ' || act_table_name || ' (LIKE ' || 'crm0' || '.' || crm0_table_name || ' INCLUDING ALL)';
blit5$# END LOOP;
blit5$# --EXECUTE 'INSERT INTO crm' || NEW.id || '.schema_migrations (SELECT * FROM crm0.schema_migrations)';
blit5$# ELSIF TG_OP = 'DELETE' THEN
blit5$# EXECUTE 'DROP SCHEMA crm' || OLD.id || ' CASCADE';
blit5$# ELSE
blit5$# RAISE EXCEPTION 'TG_OP % not supported in this trigger bro', TG_OP;
blit5$# END IF;
blit5$# RETURN NULL;
blit5$# END;
blit5$# $BODY$ LANGUAGE plpgsql;
CREATE FUNCTION
blit5=# CREATE TRIGGER accounts_trig_schema AFTER INSERT OR DELETE ON public.accounts FOR EACH ROW EXECUTE PROCEDURE public.accounts_fntrig_schema();
CREATE TRIGGER
blit5=# \d accounts
Table "public.accounts"
Column | Type | Modifiers
------------+--------------------------+-------------------------------------------------------
id | integer | not null default nextval('accounts_id_seq'::regclass)
created_at | timestamp with time zone | not null default now()
name | citext | not null
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "account_users" CONSTRAINT "account_users_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id) ON UPDATE CASCADE
Triggers:
accounts_trig_schema AFTER INSERT OR DELETE ON accounts FOR EACH ROW EXECUTE PROCEDURE accounts_fntrig_schema()
blit5=# insert into accounts(name) values('Blit rocks!');NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "contact_infos_pkey" for table "contact_infos"CONTEXT: SQL statement "CREATE TABLE crm2.contact_infos (LIKE crm0.contact_infos INCLUDING ALL)"PL/pgSQL function "accounts_fntrig_schema" line 13 at EXECUTE statementNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "contacts_pkey" for table "contacts"CONTEXT: SQL statement "CREATE TABLE crm2.contacts (LIKE crm0.contacts INCLUDING ALL)"PL/pgSQL function "accounts_fntrig_schema" line 13 at EXECUTE statementINSERT 0 1blit5=# select * from accounts; id | created_at | name ----+-------------------------------+------------- 2 | 2012-05-10 12:34:11.519005-07 | Blit rocks!(1 row)blit5=# \dn List of schemas Name | Owner --------+------- crm0 | troy crm2 | troy public | troy(3 rows)blit5=# \dn crm2List of schemas Name | Owner ------+------- crm2 | troy(1 row)blit5=# \dn+ crm2 List of schemas Name | Owner | Access privileges | Description ------+-------+-------------------+------------- crm2 | troy | | (1 row)
blit5=# SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = 'crm2';
table_name
---------------
contact_infos
contacts
(2 rows)
blit5=# \d crm2.contacts;
Table "crm2.contacts"
Column | Type | Modifiers
-------------------------+--------------------------+------------------------------------------------------------
id | integer | not null default nextval('crm0.contacts_id_seq'::regclass)
type | contacts_type | not null
created_at | timestamp with time zone | not null default now()
updated_at | timestamp with time zone | not null default now()
todo_at | timestamp with time zone |
created_by_id | citext | not null
organization_id | integer |
organization_num_people | integer |
name | citext |
first_name | citext |
last_name | citext |
title | citext |
background | citext |
Indexes:
"contacts_pkey" PRIMARY KEY, btree (id)
"contacts_created_by_id_idx" btree (created_by_id)
"contacts_organization_id_idx" btree (organization_id)
blit5=# \d crm2.contact_infos;
Table "crm2.contact_infos"
Column | Type | Modifiers
------------+---------------------+-----------------------------------------------------------------
id | integer | not null default nextval('crm0.contact_infos_id_seq'::regclass)
type | contacts_infos_type | not null
contact_id | integer | not null
label | citext |
address | citext |
city | citext |
state | citext |
zip | citext |
Indexes:
"contact_infos_pkey" PRIMARY KEY, btree (id)
"contact_infos_contact_id_idx" btree (contact_id)
blit5=#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment