N
aก4c       s      d  GH d GH H	 d GH
 H d GH d GH d GH d  GH k  l  k l  d   Z c d   Z  d   Z ศ d	   Z า d
   Z d S(   sB   __________________________________________________________________s<   MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIALs=   This module is designed for being imported from python promptsD   In order to run the samples included here, first create a connections:   using :                        cnx = advanced.connect(...)s1   then start the demo with:      advanced.demo(cnx)c    s    d GH d GH d GH d GH d GH d GH H d GH d GH H d GH  d	 GH! d
 GH" d GH# d GH$ H% d GH& d GH' d GH( |  i d  , |  i d  . t   / H0 d GH1 H2 d GH3 d GH4 d GH5 H6 d GH7 d GH8 H9 |  i d  ; |  i d  = |  i d  ? |  i d  A |  i d  C HD d GHE |  i d  } F t | i   | i    G d GHH |  i d  } I t | i   | i    J HK d GHL d GHM HN d GHO d GHP d GHQ |  i d  } T t | i   | i    U HV d  GHW d! GHX d" GHY HZ d# GH[ d$ GH\ d% GH] |  i d&  } ` t | i   | i    d  S('   Ns   -----------------------------s   -- Inheritance:s8   --	a table can inherit from zero or more tables. A querys8   --	can reference either all rows of a table or all rows s*   --	of a table plus all of its descendants.s>   -- For example, the capitals table inherits from cities table.s.   -- (It inherits  all data fields from cities.)s   CREATE TABLE cities (s       name		text,s   	   population	float8,s       altitude	ints   )s   CREATE TABLE capitals (s       state	char2s   ) INHERITS (cities)s?   CREATE TABLE cities (name		text,population	float8,altitude	int)s6   CREATE TABLE capitals (state		char2) INHERITS (cities)s!   -- now, let's populate the tabless8   INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)s7   INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)s2   INSERT INTO cities VALUES ('Mariposa', 1200, 1953)s>   INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')s<   INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')s   SELECT * FROM citiess   SELECT * FROM capitalss;   -- like before, a regular query references rows of the bases   -- table onlys   SELECT name, altitudes   FROM citiess   WHERE altitude > 500;s6   SELECT name, altitude FROM cities WHERE altitude > 500s9   -- on the other hand, you can find all cities, including s8   -- capitals, that are located at an altitude of 500 'ft s   -- or higher by:s   SELECT c.name, c.altitudes   FROM cities* cs   WHERE c.altitude > 500s?   SELECT c.name, c.altitude FROM cities* c WHERE c.altitude > 500(   s   pgcnxs   querys   wait_keys   qs   displays
   listfieldss	   getresult(   s   pgcnxs   qs   advanced.pys   inherit_demo s~   
c    s  c d d GHe d GHf d GHg d GHh Hi d GHj d GHk Hl d GHm d GHn d GHo |  i d	  r t   s Ht d
 GHu Hv d GHw |  i d  } x t | i   | i    y Hz d GH{ d GH| H} d GH~ d GH d GH |  i d  }  t | i   | i    d  S(   Ns   -----------------------------s   -- Time Travel:s6   --	this feature allows you to run historical queries. s>   -- first, let's make some changes to the cities table (supposes-   -- Mariposa's population grows 10% this year)s   UPDATE citiess!   SET population = population * 1.1s   WHERE name = 'Mariposa';sF   UPDATE cities SET population = population * 1.1WHERE name = 'Mariposa's0   -- the default time is the current time ('now'):s-   SELECT * FROM cities WHERE name = 'Mariposa';s,   SELECT * FROM cities WHERE name = 'Mariposa's=   -- we can also retrieve the population of Mariposa ever has. s=   -- ('epoch' is the earliest time representable by the system)s   SELECT name, populations?   FROM cities['epoch', 'now']  -- can be abbreviated to cities[,]sK   SELECT name, population FROM cities['epoch', 'now'] WHERE name = 'Mariposa'(   s   pgcnxs   querys   wait_keys   qs   displays
   listfieldss	   getresult(   s   pgcnxs   qs   advanced.pys   time_travelc s8   
c    s๙    d GH d GH d GH d GH d GH H d GH d GH d GH d GH d	 GH |  i d
   t    H d GH d GH H d GH d GH d GH d GH H  d GHก d GHข d GHฃ d GHค Hฅ |  i d  จ |  i d  ซ t   ฌ Hญ d GHฎ d GHฏ d GHฐ Hฑ d GHฒ d GHณ Hด |  i d  } ถ t | i   | i    ท Hธ d GHน Hบ d GHป Hผ |  i d  } ฝ t | i   | i    พ Hฟ d GHภ Hม d GHย d GHร |  i d  } ล t | i   | i    d  S(    Ns   ----------------------s
   -- Arrays:s?   --      attributes can be arrays of base types or user-defined s   --      typess   CREATE TABLE sal_emp (s       name			text,s       pay_by_quarter	int4[],s       schedule		char16[][]s   )sJ   CREATE TABLE sal_emp (name	text,pay_by_quarter	int4[],schedule	char16[][])s,   -- insert instances with array attributes.  s      Note the use of bracess   INSERT INTO sal_emp VALUES (s       'Bill',s        '{10000,10000,10000,10000}',s!       '{{"meeting", "lunch"}, {}}')s       'Carol',s        '{20000,25000,25000,25000}',s)       '{{"talk", "consult"}, {"meeting"}}')s]   INSERT INTO sal_emp VALUES ('Bill', '{10000,10000,10000,10000}','{{"meeting", "lunch"}, {}}')sf   INSERT INTO sal_emp VALUES ('Carol', '{20000,25000,25000,25000}','{{"talk", "consult"}, {"meeting"}}')s   -- queries on array attributess   SELECT name FROM sal_emp WHEREs8     sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]sU   SELECT name FROM sal_emp WHERE sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]s.   -- retrieve third quarter pay of all employeess-   SELECT sal_emp.pay_by_quarter[3] FROM sal_emps   -- select subarrayss5   SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE	s        sal_emp.name = 'Bill'sJ   SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE sal_emp.name = 'Bill'(   s   pgcnxs   querys   wait_keys   qs   displays
   listfieldss	   getresult(   s   pgcnxs   qs   advanced.pys
   array_demo sl   

c    sW   ศ ษ d GHส d GHห d GHฬ d GHอ |  i d  ฮ |  i d  ฯ |  i d  d  S(   Ns0   -- clean up (you must remove the children first)s   DROP TABLE sal_emps   DROP TABLE capitalss   DROP TABLE cities;s   DROP TABLE cities(   s   pgcnxs   query(   s   pgcnxs   advanced.pys   demo_cleanupศ s   c    s;   า ำ t  |   ิ t |   ี t |   ึ t |   d  S(   N(   s   inherit_demos   pgcnxs   time_travels
   array_demos   demo_cleanup(   s   pgcnxs   advanced.pys   demoา s   N(   s   pgtoolss   *s   pgexts   inherit_demos   time_travels
   array_demos   demo_cleanups   demo(    s   advanced.pys   ? s   

O#B
