from IPython.display import HTML
HTML('<iframe src="http://www.datomic.com" width=1024 height=768></iframe>')

HTML('<iframe src="http://www.postgresql.org/docs/6.3/static/c0503.htm" width=1024 height=768></iframe>')

HTML('<iframe src="http://www.postgresql.org/docs/current/static/contrib-spi.html" width=1024 height=768></iframe>')

HTML('<iframe src="http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=print_post&node=1961716" width=800 height=600></iframe>')

%%script psql -d tmc -U norman
\d price

%load_ext sql
%sql postgresql://norman@localhost/tmc
cid=432

%%script psql -d tmc -U norman
\sf+ price_block_update_or_delete_or_truncate

%%script psql -d tmc -U norman
update price 
set settle = 2 
where cid = 432
and dte = '2013-12-02';

delete from price
where cid = 432
and dte = '2013-12-02';

truncate table price;

%%sql
SELECT min(date_entered),max(date_entered),count(*)
FROM price
WHERE cid = :cid
AND dte = '2013-12-13'

%%sql 
select * 
from price 
where cid = :cid 
and dte = '2013-12-13' 
order by date_entered desc 
limit 10

%%sql 
-- Get price for Dec 13 2013 as of 12:30 pm Dec 31st
select distinct on (cid,dte) cid,dte,date_entered, settle 
from price
where cid = :cid
and dte = '2013-12-13'
and date_entered <= '2013-12-31 12:30:00'
order by 1,2,3 desc

%%sql 
-- Get price for Dec 13 2013 as of 12:45 pm Dec 31st
select distinct on (cid,dte) cid,dte,date_entered, settle 
from price
where cid = :cid
and dte = '2013-12-13'
and date_entered <= '2013-12-31 12:45:00'
order by 1,2,3 desc

%%sql
-- or we can use windowing example
select distinct cid,dte, 
first_value(date_entered) over date_window as date_entered,
first_value(settle) over date_window as settle
FROM price
where cid = :cid
and dte = '2013-12-13'
and date_entered <= '2013-12-31 12:45:00'
window date_window as
   (partition by cid, dte
   order by cid, dte, date_entered desc
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
order by 1,2,3 desc;


%%script psql -d tmc -U norman
-- create extension btree_gist ;
-- need this to mix btree with gist 
\d price_range

%%script psql -d tmc -U norman
-- insert into price_range
-- (cid,dte,settle,date_range)
-- select cid,dte,settle,
--    tstzrange(date_entered,lead(date_entered) 
--     over (partition by cid,dte order by cid,dte,date_entered)
--     ) as date_range
-- from price
-- order by cid,dte,date_entered;
select * 
from price_range 
where cid = 432
and dte = '2013-12-13' 
order by date_range desc limit 10;


%%sql
select cid,dte, round(settle,2) as settle
from price_range 
where cid = :cid 
and dte between '2010-12-01' and '2010-12-13' 
-- now is within date_range
and current_timestamp <@ date_range
order by 2

HTML('<iframe src="http://arstechnica.com/information-technology/2014/01/why-facebook-thinks-blu-ray-discs-are-perfect-for-the-data-center/" width=1024 height=768></iframe>')

%%script psql -d tmc -U norman
-- create schema partman;
-- create extension pg_partman schema partman;
-- set search_path to public,partman;
-- create table price_parent(like price including constraints including defaults including indexes)

-- create partition based index please...
-- create index price_parent_dte on price_parent(dte);


-- select partman.create_parent(p_parent_table:='public.price_parent', p_control:='dte', 
--       p_type:='time-dynamic',p_interval:='yearly',p_start_partition:='2009-01-01 00:00:00');
\sf+ price_parent_part_trig_func

%%script psql -d tmc -U norman
\d price_parent_p2009

%%sql
explain analyze
select distinct on (cid, dte) cid, dte, date_entered,settle
FROM price_parent
where cid = :cid
and dte between '2014-02-01' and '2014-03-30'
and date_entered < current_timestamp
ORDER BY 1,2,3 desc

HTML('<iframe src="http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=print_post&node=5778285" width=800 height=600></iframe>')