joevandyk

begin;                                                                                              
                                                                                                    
                                                                                                    
  -- orders have many line_items, refunds have many line_items, but a refund can't be associated with line items from different orders.
  create table orders(id integer primary key);                                                      
  create table line_items(id integer primary key, order_id integer references orders);              
  create table refunds(id integer primary key);                                                     
  create table line_items_refunds(line_item_id integer references line_items, refund_id integer references refunds);
                                                                                                    
  insert into orders values (1);                                

joevandyk

begin;                                                                                              
                                                                                                    
  -- Could make this a temporary table, right?                                                      
  CREATE TABLE current_user_ids (id SERIAL PRIMARY KEY, user_id integer);                           
  create function current_user_id() returns integer language sql as $f$                             
    select user_id from current_user_ids where id = currval('current_user_ids_id_seq');             
  $f$;                                                                                              
                                                                                                    
  create table products(id integer primary key, price numeric);                                     
  insert into products values (1, 1.99), (2, 2.50);                                                 

joevandyk

  create sequence current_user_id;                                                                  
                                                                                                    
  create function set_current_user_id(user_id integer) returns integer language sql as $f$          
    select setval('current_user_id', $1::integer)::integer;                                         
  $f$;                                                                                              
                                                                                                    
  create function current_user_id() returns integer language sql as $f$                             
    select currval('current_user_id')::integer;                                                     
  $f$;                                                                                              
                                                                                                    

joevandyk

begin;
create schema test;
set search_path=test;

create table products (
  id serial primary key,
  quantity_in_stock integer
);

create table inventory_movements (

joevandyk

CREATE OR REPLACE FUNCTION escape_json (text) RETURNS text AS $$                                    
SELECT replace($1, '"', '"'); $$ LANGUAGE SQL IMMUTABLE;                                            
                                                                                                    
                                                                                                    
CREATE OR REPLACE FUNCTION to_json(text) RETURNS text AS $$                                         
SELECT escape_json($1) $$ LANGUAGE SQL IMMUTABLE;                                                   
                                                                                                    
                                                                                                    
CREATE OR REPLACE FUNCTION to_json(KEY text, value text) RETURNS text AS $$                         
SELECT '"' || to_json($1) || '" : "' || to_json($2) || '"'; $$ LANGUAGE SQL IMMUTABLE;              

joevandyk

/* This demonstrates a trigger for denormalizing a join table into an array.
   Hasn't really been tested much, but seems to work.

   Will put it into production tomorrow and see!

   Maybe PostgreSQL 9.4 will come with arrays of foreign keys. That will remove a lot of the
   need for simple join tables.

   http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys didn't
   make it into 9.3.

joevandyk

BEGIN;

create table test_table (id serial primary key, name text);
insert into test_table (name) values ('joe'), ('bob');

create schema database_reports;

create table database_reports.queries (
  query_id bigserial primary key,
  parent_id bigint references database_reports.queries,

joevandyk

begin;                                                                                              
                                                                                                    
create table user_ratings (                                                                         
  id serial primary key,                                                                            
  user_id integer not null,                                                                         
  rating integer not null check (rating >= 0 and rating <= 5),                                      
  ratable_id integer not null                                                                       
);                                                                                                  
                                                                                                    
create function random_int(max integer) returns integer as $$                                       

joevandyk

require 'active_support'
require 'active_support/core_ext'
require 'ruby-prof'
require "minitest/autorun"

class Array
  def rest
    self[1..-1]
  end
end

joevandyk

interface BingoStatus {
  won: boolean
}

interface BingoCell {
  number: string,
  freebie?: boolean,
  completed?: boolean
}