Repository: incubator-hawq Updated Branches: refs/heads/master 1363d3ef2 -> 192cff1fc
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/192cff1f/tools/bin/pythonSrc/PyGreSQL-4.0/setup.py ---------------------------------------------------------------------- diff --git a/tools/bin/pythonSrc/PyGreSQL-4.0/setup.py b/tools/bin/pythonSrc/PyGreSQL-4.0/setup.py new file mode 100755 index 0000000..5d28b31 --- /dev/null +++ b/tools/bin/pythonSrc/PyGreSQL-4.0/setup.py @@ -0,0 +1,152 @@ +#!/usr/bin/env python +# $Id: setup.py,v 1.27 2008/11/21 17:08:17 cito Exp $ + +"""Setup script for PyGreSQL version 4.0 + +Authors and history: +* PyGreSQL written 1997 by D'Arcy J.M. Cain <da...@druid.net> +* based on code written 1995 by Pascal Andre <an...@chimay.via.ecp.fr> +* setup script created 2000/04 Mark Alexander <m...@gate.net> +* tweaked 2000/05 Jeremy Hylton <jer...@cnri.reston.va.us> +* win32 support 2001/01 by Gerhard Haering <gerh...@bigfoot.de> +* tweaked 2006/02 and 2008/11 by Christoph Zwerschke <c...@online.de> + +Prerequisites to be installed: +* Python including devel package (header files and distutils) +* PostgreSQL libs and devel packages (header files of client and server) +* PostgreSQL pg_config tool (usually included in the devel package) + (the Windows installer has it as part of the database server feature) + +Tested with Python 2.5.2 and PostGreSQL 8.3.5. Older version should work +as well, but you will need at least Python 2.3 and PostgreSQL 7.4. + +Use as follows: +python setup.py build # to build the module +python setup.py install # to install it + +You should use MinGW (www.mingw.org) for building on Win32: +python setup.py build -c mingw32 install # use MinGW +Note that Python newer than version 2.3 is using msvcr71 instead of msvcrt +as its common runtime library. So, if you are using MinGW to build PyGreSQL, +you should edit the file "%MinGWpath%/lib/gcc/%MinGWversion%/specs" +and change the entry that reads -lmsvcrt to -lmsvcr71. + +See docs.python.org/doc/install/ for more information on +using distutils to install Python programs. + +""" + +version = "4.0" + +import sys + +if not (2, 2) < sys.version_info[:2] < (3, 0): + raise Exception("PyGreSQL %s requires a Python 2 version" + " newer than 2.2." % version) + +import os +from distutils.core import setup +from distutils.extension import Extension + +def pg_config(s): + """Retrieve information about installed version of PostgreSQL.""" + if os.path.exists("../../../../src/bin/pg_config/pg_config"): + f = os.popen("../../../../src/bin/pg_config/pg_config --%s" % s) + else: + """If a VPATH build, it might not be there. Look other places""" + """It should be the one in the path, because the makefile includes greenplum_path.sh """ + f = os.popen("pg_config --%s" % s) + + d = f.readline().strip() + if f.close() is not None: + raise Exception("pg_config tool is not available.") + if not d: + raise Exception("Could not get %s information." % s) + return d + +def mk_include(): + """Create a temporary local include directory. + + The directory will contain a copy of the PostgreSQL server header files, + where all features which are not necessary for PyGreSQL are disabled. + + """ + os.mkdir('include') + for f in os.listdir(pg_include_dir_server): + if not f.endswith('.h'): + continue + d = open(os.path.join(pg_include_dir_server, f)).read() + if f == 'pg_config.h': + d += '\n'.join(('', + '#undef ENABLE_NLS', + '#undef USE_REPL_SNPRINTF', + '#undef USE_SSL', + '#undef USE_ZLIB', + '#undef HAVE_STDINT_H', + '#undef HAVE_SYS_TIME_H', + '#undef HAVE_UNISTD_H', + '#define _CRT_SECURE_NO_WARNINGS 1', + '#define _USE_32BIT_TIME_T 1', + '')) + open(os.path.join('include', f), 'w').write(d) + +def rm_include(): + """Remove the temporary local include directory.""" + if os.path.exists('include'): + for f in os.listdir('include'): + os.remove(os.path.join('include', f)) + os.rmdir('include') + +pg_include_dir = pg_config('includedir') +pg_include_dir_server = pg_config('includedir-server') + +rm_include() +mk_include() + +include_dirs = ['include', pg_include_dir, pg_include_dir_server] + +pg_libdir = pg_config('libdir') +library_dirs = [pg_libdir] + +libraries=['pq'] + +if sys.platform == "win32": + include_dirs.append(os.path.join(pg_include_dir_server, 'port/win32')) + +setup( + name="PyGreSQL", + version=version, + description="Python PostgreSQL Interfaces", + long_description = ("PyGreSQL is an open-source Python module" + " that interfaces to a PostgreSQL database." + " It embeds the PostgreSQL query library to allow easy use" + " of the powerful PostgreSQL features from a Python script."), + keywords="postgresql database api dbapi", + author="D'Arcy J. M. Cain", + author_email="da...@pygresql.org", + url="http://www.pygresql.org";, + download_url = "ftp://ftp.pygresql.org/pub/distrib/";, + platforms = ["any"], + license="Python", + py_modules=['pg', 'pgdb'], + ext_modules=[Extension( + '_pg', ['pgmodule.c'], + include_dirs = include_dirs, + library_dirs = library_dirs, + libraries = libraries, + extra_compile_args = ['-O2'] + )], + classifiers=[ + "Development Status :: 6 - Mature", + "Intended Audience :: Developers", + "License :: OSI Approved :: Python Software Foundation License", + "Operating System :: OS Independent", + "Programming Language :: C", + "Programming Language :: Python", + "Topic :: Database", + "Topic :: Database :: Front-Ends", + "Topic :: Software Development :: Libraries :: Python Modules" + ] +) + +rm_include() http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/192cff1f/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/advanced.py ---------------------------------------------------------------------- diff --git a/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/advanced.py b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/advanced.py new file mode 100755 index 0000000..41a5bc4 --- /dev/null +++ b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/advanced.py @@ -0,0 +1,198 @@ +#! /usr/bin/env python +# advanced.py - demo of advanced features of PostGres. Some may not be ANSI. +# inspired from the Postgres tutorial +# adapted to Python 1995 by Pascal Andre + +print """ +__________________________________________________________________ +MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIAL + +This module is designed for being imported from python prompt + +In order to run the samples included here, first create a connection +using : cnx = advanced.DB(...) + +The "..." should be replaced with whatever arguments you need to open an +existing database. Usually all you need is the name of the database and, +in fact, if it is the same as your login name, you can leave it empty. + +then start the demo with: advanced.demo(cnx) +__________________________________________________________________ +""" + +from pg import DB +import sys + +# waits for a key +def wait_key(): + print "Press <enter>" + sys.stdin.read(1) + +# inheritance features +def inherit_demo(pgcnx): + print "-----------------------------" + print "-- Inheritance:" + print "-- a table can inherit from zero or more tables. A query" + print "-- can reference either all rows of a table or all rows " + print "-- of a table plus all of its descendants." + print "-----------------------------" + print + print "-- For example, the capitals table inherits from cities table." + print "-- (It inherits all data fields from cities.)" + print + print "CREATE TABLE cities (" + print " name text," + print " population float8," + print " altitude int" + print ")" + print + print "CREATE TABLE capitals (" + print " state varchar(2)" + print ") INHERITS (cities)" + pgcnx.query("""CREATE TABLE cities ( + name text, + population float8, + altitude int)""") + pgcnx.query("""CREATE TABLE capitals ( + state varchar(2)) INHERITS (cities)""") + wait_key() + print + print "-- now, let's populate the tables" + print + print "INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)" + print "INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)" + print "INSERT INTO cities VALUES ('Mariposa', 1200, 1953)" + print + print "INSERT INTO capitals VALUES ('Sacramento', 3.694E+5, 30, 'CA')" + print "INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')" + print + pgcnx.query("INSERT INTO cities VALUES ('San Francisco', 7.24E+5, 63)") + pgcnx.query("INSERT INTO cities VALUES ('Las Vegas', 2.583E+5, 2174)") + pgcnx.query("INSERT INTO cities VALUES ('Mariposa', 1200, 1953)") + pgcnx.query("INSERT INTO capitals VALUES ('Sacramento',3.694E+5,30,'CA')") + pgcnx.query("INSERT INTO capitals VALUES ('Madison', 1.913E+5, 845, 'WI')") + print + print "SELECT * FROM cities" + print pgcnx.query("SELECT * FROM cities") + print "SELECT * FROM capitals" + print pgcnx.query("SELECT * FROM capitals") + print + print "-- like before, a regular query references rows of the base" + print "-- table only" + print + print "SELECT name, altitude" + print "FROM cities" + print "WHERE altitude > 500;" + print pgcnx.query("""SELECT name, altitude + FROM cities + WHERE altitude > 500""") + print + print "-- on the other hand, you can find all cities, including " + print "-- capitals, that are located at an altitude of 500 'ft " + print "-- or higher by:" + print + print "SELECT c.name, c.altitude" + print "FROM cities* c" + print "WHERE c.altitude > 500" + print pgcnx.query("""SELECT c.name, c.altitude + FROM cities* c + WHERE c.altitude > 500""") + +# arrays attributes +def array_demo(pgcnx): + print "----------------------" + print "-- Arrays:" + print "-- attributes can be arrays of base types or user-defined " + print "-- types" + print "----------------------" + print + print "CREATE TABLE sal_emp (" + print " name text," + print " pay_by_quarter int4[]," + print " pay_by_extra_quarter int8[]," + print " schedule text[][]" + print ")" + pgcnx.query("""CREATE TABLE sal_emp ( + name text, + pay_by_quarter int4[], + pay_by_extra_quarter int8[], + schedule text[][])""") + wait_key() + print + print "-- insert instances with array attributes. " + print " Note the use of braces" + print + print "INSERT INTO sal_emp VALUES (" + print " 'Bill'," + print " '{10000,10000,10000,10000}'," + print " '{9223372036854775800,9223372036854775800,9223372036854775800}'," + print " '{{\"meeting\", \"lunch\"}, {}}')" + print + print "INSERT INTO sal_emp VALUES (" + print " 'Carol'," + print " '{20000,25000,25000,25000}'," + print " '{9223372036854775807,9223372036854775807,9223372036854775807}'," + print " '{{\"talk\", \"consult\"}, {\"meeting\"}}')" + print + pgcnx.query("""INSERT INTO sal_emp VALUES ( + 'Bill', '{10000,10000,10000,10000}', + '{9223372036854775800,9223372036854775800,9223372036854775800}', + '{{\"meeting\", \"lunch\"}, {}}')""") + pgcnx.query("""INSERT INTO sal_emp VALUES ( + 'Carol', '{20000,25000,25000,25000}', + '{9223372036854775807,9223372036854775807,9223372036854775807}', + '{{\"talk\", \"consult\"}, {\"meeting\"}}')""") + wait_key() + print + print "----------------------" + print "-- queries on array attributes" + print "----------------------" + print + print "SELECT name FROM sal_emp WHERE" + print " sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]" + print + print pgcnx.query("""SELECT name FROM sal_emp WHERE + sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]""") + print + print pgcnx.query("""SELECT name FROM sal_emp WHERE + sal_emp.pay_by_extra_quarter[1] <> sal_emp.pay_by_extra_quarter[2]""") + print + print "-- retrieve third quarter pay of all employees" + print + print "SELECT sal_emp.pay_by_quarter[3] FROM sal_emp" + print + print pgcnx.query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp") + print + print "-- retrieve third quarter extra pay of all employees" + print + print "SELECT sal_emp.pay_by_extra_quarter[3] FROM sal_emp" + print pgcnx.query("SELECT sal_emp.pay_by_extra_quarter[3] FROM sal_emp") + print + print "-- retrieve first two quarters of extra quarter pay of all employees" + print + print "SELECT sal_emp.pay_by_extra_quarter[1:2] FROM sal_emp" + print + print pgcnx.query("SELECT sal_emp.pay_by_extra_quarter[1:2] FROM sal_emp") + print + print "-- select subarrays" + print + print "SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE" + print " sal_emp.name = 'Bill'" + print pgcnx.query("SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " \ + "sal_emp.name = 'Bill'") + +# base cleanup +def demo_cleanup(pgcnx): + print "-- clean up (you must remove the children first)" + print "DROP TABLE sal_emp" + print "DROP TABLE capitals" + print "DROP TABLE cities;" + pgcnx.query("DROP TABLE sal_emp") + pgcnx.query("DROP TABLE capitals") + pgcnx.query("DROP TABLE cities") + +# main demo function +def demo(pgcnx): + inherit_demo(pgcnx) + array_demo(pgcnx) + demo_cleanup(pgcnx) http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/192cff1f/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/basics.py ---------------------------------------------------------------------- diff --git a/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/basics.py b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/basics.py new file mode 100755 index 0000000..98a7f86 --- /dev/null +++ b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/basics.py @@ -0,0 +1,296 @@ +#! /usr/bin/env python +# basics.py - basic SQL commands tutorial +# inspired from the Postgres95 tutorial +# adapted to Python 1995 by Pascal ANDRE + +print """ +__________________________________________________________________ +MODULE BASICS.PY : BASIC POSTGRES SQL COMMANDS TUTORIAL + +This module is designed for being imported from python prompt + +In order to run the samples included here, first create a connection +using : cnx = basics.DB(...) + +The "..." should be replaced with whatever arguments you need to open an +existing database. Usually all you need is the name of the database and, +in fact, if it is the same as your login name, you can leave it empty. + +then start the demo with: basics.demo(cnx) +__________________________________________________________________ +""" + +from pg import DB +import sys + +# waits for a key +def wait_key(): + print "Press <enter>" + sys.stdin.read(1) + +# table creation commands +def create_table(pgcnx): + print "-----------------------------" + print "-- Creating a table:" + print "-- a CREATE TABLE is used to create base tables. POSTGRES" + print "-- SQL has its own set of built-in types. (Note that" + print "-- keywords are case-insensitive but identifiers are " + print "-- case-sensitive.)" + print "-----------------------------" + print + print "Sending query :" + print "CREATE TABLE weather (" + print " city varchar(80)," + print " temp_lo int," + print " temp_hi int," + print " prcp float8," + print " date date" + print ")" + pgcnx.query("""CREATE TABLE weather (city varchar(80), temp_lo int, + temp_hi int, prcp float8, date date)""") + print + print "Sending query :" + print "CREATE TABLE cities (" + print " name varchar(80)," + print " location point" + print ")" + pgcnx.query("""CREATE TABLE cities ( + name varchar(80), + location point)""") + +# data insertion commands +def insert_data(pgcnx): + print "-----------------------------" + print "-- Inserting data:" + print "-- an INSERT statement is used to insert a new row into" + print "-- a table. There are several ways you can specify what" + print "-- columns the data should go to." + print "-----------------------------" + print + print "-- 1. the simplest case is when the list of value correspond to" + print "-- the order of the columns specified in CREATE TABLE." + print + print "Sending query :" + print "INSERT INTO weather " + print " VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')" + pgcnx.query("""INSERT INTO weather + VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')""") + print + print "Sending query :" + print "INSERT INTO cities " + print " VALUES ('San Francisco', '(-194.0, 53.0)')" + pgcnx.query("""INSERT INTO cities + VALUES ('San Francisco', '(-194.0, 53.0)')""") + print + wait_key() + print "-- 2. you can also specify what column the values correspond " + print " to. (The columns can be specified in any order. You may " + print " also omit any number of columns. eg. unknown precipitation" + print " below)" + print "Sending query :" + print "INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)" + print " VALUES ('San Francisco', 43, 57, 0.0, '11/29/1994')" + pgcnx.query("INSERT INTO weather (date, city, temp_hi, temp_lo)" \ + "VALUES ('11/29/1994', 'Hayward', 54, 37)") + +# direct selection commands +def select_data1(pgcnx): + print "-----------------------------" + print "-- Retrieving data:" + print "-- a SELECT statement is used for retrieving data. The " + print "-- basic syntax is:" + print "-- SELECT columns FROM tables WHERE predicates" + print "-----------------------------" + print + print "-- a simple one would be the query:" + print "SELECT * FROM weather" + print + print "The result is :" + q = pgcnx.query("SELECT * FROM weather") + print q + print + print "-- you may also specify expressions in the target list (the " + print "-- 'AS column' specifies the column name of the result. It is " + print "-- optional.)" + print "The query :" + print " SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date " + print " FROM weather" + print "Gives :" + print pgcnx.query("""SELECT city, (temp_hi+temp_lo)/2 + AS temp_avg, date FROM weather""") + print + print "-- if you want to retrieve rows that satisfy certain condition" + print "-- (ie. a restriction), specify the condition in WHERE. The " + print "-- following retrieves the weather of San Francisco on rainy " + print "-- days." + print "SELECT *" + print "FROM weather" + print "WHERE city = 'San Francisco' " + print " and prcp > 0.0" + print pgcnx.query("""SELECT * FROM weather WHERE city = 'San Francisco' + AND prcp > 0.0""") + print + print "-- here is a more complicated one. Duplicates are removed when " + print "-- DISTINCT is specified. ORDER BY specifies the column to sort" + print "-- on. (Just to make sure the following won't confuse you, " + print "-- DISTINCT and ORDER BY can be used separately.)" + print "SELECT DISTINCT city" + print "FROM weather" + print "ORDER BY city;" + print pgcnx.query("SELECT DISTINCT city FROM weather ORDER BY city") + +# selection to a temporary table +def select_data2(pgcnx): + print "-----------------------------" + print "-- Retrieving data into other classes:" + print "-- a SELECT ... INTO statement can be used to retrieve " + print "-- data into another class." + print "-----------------------------" + print + print "The query :" + print "SELECT * INTO TABLE temptab " + print "FROM weather" + print "WHERE city = 'San Francisco' " + print " and prcp > 0.0" + pgcnx.query("""SELECT * INTO TABLE temptab FROM weather + WHERE city = 'San Francisco' and prcp > 0.0""") + print "Fills the table temptab, that can be listed with :" + print "SELECT * from temptab" + print pgcnx.query("SELECT * from temptab") + +# aggregate creation commands +def create_aggregate(pgcnx): + print "-----------------------------" + print "-- Aggregates" + print "-----------------------------" + print + print "Let's consider the query :" + print "SELECT max(temp_lo)" + print "FROM weather;" + print pgcnx.query("SELECT max(temp_lo) FROM weather") + print + print "-- Aggregate with GROUP BY" + print "SELECT city, max(temp_lo)" + print "FROM weather " + print "GROUP BY city;" + print pgcnx.query( """SELECT city, max(temp_lo) + FROM weather GROUP BY city""") + +# table join commands +def join_table(pgcnx): + print "-----------------------------" + print "-- Joining tables:" + print "-- queries can access multiple tables at once or access" + print "-- the same table in such a way that multiple instances" + print "-- of the table are being processed at the same time." + print "-----------------------------" + print + print "-- suppose we want to find all the records that are in the " + print "-- temperature range of other records. W1 and W2 are aliases " + print "--for weather." + print + print "SELECT W1.city, W1.temp_lo, W1.temp_hi, " + print " W2.city, W2.temp_lo, W2.temp_hi" + print "FROM weather W1, weather W2" + print "WHERE W1.temp_lo < W2.temp_lo " + print " and W1.temp_hi > W2.temp_hi" + print + print pgcnx.query("""SELECT W1.city, W1.temp_lo, W1.temp_hi, + W2.city, W2.temp_lo, W2.temp_hi FROM weather W1, weather W2 + WHERE W1.temp_lo < W2.temp_lo and W1.temp_hi > W2.temp_hi""") + print + print "-- let's join two tables. The following joins the weather table" + print "-- and the cities table." + print + print "SELECT city, location, prcp, date" + print "FROM weather, cities" + print "WHERE name = city" + print + print pgcnx.query("""SELECT city, location, prcp, date FROM weather, cities + WHERE name = city""") + print + print "-- since the column names are all different, we don't have to " + print "-- specify the table name. If you want to be clear, you can do " + print "-- the following. They give identical results, of course." + print + print "SELECT w.city, c.location, w.prcp, w.date" + print "FROM weather w, cities c" + print "WHERE c.name = w.city;" + print + print pgcnx.query("""SELECT w.city, c.location, w.prcp, w.date + FROM weather w, cities c WHERE c.name = w.city""") + +# data updating commands +def update_data(pgcnx): + print "-----------------------------" + print "-- Updating data:" + print "-- an UPDATE statement is used for updating data. " + print "-----------------------------" + print + print "-- suppose you discover the temperature readings are all off by" + print "-- 2 degrees as of Nov 28, you may update the data as follow:" + print + print "UPDATE weather" + print " SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2" + print " WHERE date > '11/28/1994'" + print + pgcnx.query("""UPDATE weather + SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 + WHERE date > '11/28/1994'""") + print + print "SELECT * from weather" + print pgcnx.query("SELECT * from weather") + +# data deletion commands +def delete_data(pgcnx): + print "-----------------------------" + print "-- Deleting data:" + print "-- a DELETE statement is used for deleting rows from a " + print "-- table." + print "-----------------------------" + print + print "-- suppose you are no longer interested in the weather of " + print "-- Hayward, you can do the following to delete those rows from" + print "-- the table" + print + print "DELETE FROM weather WHERE city = 'Hayward'" + pgcnx.query("DELETE FROM weather WHERE city = 'Hayward'") + print + print "SELECT * from weather" + print + print pgcnx.query("SELECT * from weather") + print + print "-- you can also delete all the rows in a table by doing the " + print "-- following. (This is different from DROP TABLE which removes " + print "-- the table in addition to the removing the rows.)" + print + print "DELETE FROM weather" + pgcnx.query("DELETE FROM weather") + print + print "SELECT * from weather" + print pgcnx.query("SELECT * from weather") + +# table removal commands +def remove_table(pgcnx): + print "-----------------------------" + print "-- Removing the tables:" + print "-- DROP TABLE is used to remove tables. After you have" + print "-- done this, you can no longer use those tables." + print "-----------------------------" + print + print "DROP TABLE weather, cities, temptab" + pgcnx.query("DROP TABLE weather, cities, temptab") + +# main demo function +def demo(pgcnx): + create_table(pgcnx) + wait_key() + insert_data(pgcnx) + wait_key() + select_data1(pgcnx) + select_data2(pgcnx) + create_aggregate(pgcnx) + join_table(pgcnx) + update_data(pgcnx) + delete_data(pgcnx) + remove_table(pgcnx) http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/192cff1f/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/func.py ---------------------------------------------------------------------- diff --git a/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/func.py b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/func.py new file mode 100755 index 0000000..af2b412 --- /dev/null +++ b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/func.py @@ -0,0 +1,205 @@ +# func.py - demonstrate the use of SQL functions +# inspired from the PostgreSQL tutorial +# adapted to Python 1995 by Pascal ANDRE + +print """ +__________________________________________________________________ +MODULE FUNC.PY : SQL FUNCTION DEFINITION TUTORIAL + +This module is designed for being imported from python prompt + +In order to run the samples included here, first create a connection +using : cnx = func.DB(...) + +The "..." should be replaced with whatever arguments you need to open an +existing database. Usually all you need is the name of the database and, +in fact, if it is the same as your login name, you can leave it empty. + +then start the demo with: func.demo(cnx) +__________________________________________________________________ +""" + +from pg import DB +import sys + +# waits for a key +def wait_key(): + print "Press <enter>" + sys.stdin.read(1) + +# basic functions declaration +def base_func(pgcnx): + print "-----------------------------" + print "-- Creating SQL Functions on Base Types" + print "-- a CREATE FUNCTION statement lets you create a new " + print "-- function that can be used in expressions (in SELECT, " + print "-- INSERT, etc.). We will start with functions that " + print "-- return values of base types." + print "-----------------------------" + print + print "--" + print "-- let's create a simple SQL function that takes no arguments" + print "-- and returns 1" + print + print "CREATE FUNCTION one() RETURNS int4" + print " AS 'SELECT 1 as ONE' LANGUAGE 'sql'" + pgcnx.query("""CREATE FUNCTION one() RETURNS int4 + AS 'SELECT 1 as ONE' LANGUAGE 'sql'""") + wait_key() + print + print "--" + print "-- functions can be used in any expressions (eg. in the target" + print "-- list or qualifications)" + print + print "SELECT one() AS answer" + print pgcnx.query("SELECT one() AS answer") + print + print "--" + print "-- here's how you create a function that takes arguments. The" + print "-- following function returns the sum of its two arguments:" + print + print "CREATE FUNCTION add_em(int4, int4) RETURNS int4" + print " AS 'SELECT $1 + $2' LANGUAGE 'sql'" + pgcnx.query("""CREATE FUNCTION add_em(int4, int4) RETURNS int4 + AS 'SELECT $1 + $2' LANGUAGE 'sql'""") + print + print "SELECT add_em(1, 2) AS answer" + print pgcnx.query("SELECT add_em(1, 2) AS answer") + +# functions on composite types +def comp_func(pgcnx): + print "-----------------------------" + print "-- Creating SQL Functions on Composite Types" + print "-- it is also possible to create functions that return" + print "-- values of composite types." + print "-----------------------------" + print + print "-- before we create more sophisticated functions, let's " + print "-- populate an EMP table" + print + print "CREATE TABLE EMP (" + print " name text," + print " salary int4," + print " age int4," + print " dept varchar(16)" + print ")" + pgcnx.query("""CREATE TABLE EMP ( + name text, + salary int4, + age int4, + dept varchar(16))""") + print + print "INSERT INTO EMP VALUES ('Sam', 1200, 16, 'toy')" + print "INSERT INTO EMP VALUES ('Claire', 5000, 32, 'shoe')" + print "INSERT INTO EMP VALUES ('Andy', -1000, 2, 'candy')" + print "INSERT INTO EMP VALUES ('Bill', 4200, 36, 'shoe')" + print "INSERT INTO EMP VALUES ('Ginger', 4800, 30, 'candy')" + pgcnx.query("INSERT INTO EMP VALUES ('Sam', 1200, 16, 'toy')") + pgcnx.query("INSERT INTO EMP VALUES ('Claire', 5000, 32, 'shoe')") + pgcnx.query("INSERT INTO EMP VALUES ('Andy', -1000, 2, 'candy')") + pgcnx.query("INSERT INTO EMP VALUES ('Bill', 4200, 36, 'shoe')") + pgcnx.query("INSERT INTO EMP VALUES ('Ginger', 4800, 30, 'candy')") + wait_key() + print + print "-- the argument of a function can also be a tuple. For " + print "-- instance, double_salary takes a tuple of the EMP table" + print + print "CREATE FUNCTION double_salary(EMP) RETURNS int4" + print " AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql'" + pgcnx.query("""CREATE FUNCTION double_salary(EMP) RETURNS int4 + AS 'SELECT $1.salary * 2 AS salary' LANGUAGE 'sql'""") + print + print "SELECT name, double_salary(EMP) AS dream" + print "FROM EMP" + print "WHERE EMP.dept = 'toy'" + print pgcnx.query("""SELECT name, double_salary(EMP) AS dream + FROM EMP WHERE EMP.dept = 'toy'""") + print + print "-- the return value of a function can also be a tuple. However," + print "-- make sure that the expressions in the target list is in the " + print "-- same order as the columns of EMP." + print + print "CREATE FUNCTION new_emp() RETURNS EMP" + print " AS 'SELECT \'None\'::text AS name," + print " 1000 AS salary," + print " 25 AS age," + print " \'none\'::varchar(16) AS dept'" + print " LANGUAGE 'sql'" + pgcnx.query("""CREATE FUNCTION new_emp() RETURNS EMP + AS 'SELECT \\\'None\\\'::text AS name, + 1000 AS salary, + 25 AS age, + \\\'none\\\'::varchar(16) AS dept' + LANGUAGE 'sql'""") + wait_key() + print + print "-- you can then project a column out of resulting the tuple by" + print "-- using the \"function notation\" for projection columns. " + print "-- (ie. bar(foo) is equivalent to foo.bar) Note that we don't" + print "-- support new_emp().name at this moment." + print + print "SELECT name(new_emp()) AS nobody" + print pgcnx.query("SELECT name(new_emp()) AS nobody") + print + print "-- let's try one more function that returns tuples" + print "CREATE FUNCTION high_pay() RETURNS setof EMP" + print " AS 'SELECT * FROM EMP where salary > 1500'" + print " LANGUAGE 'sql'" + pgcnx.query("""CREATE FUNCTION high_pay() RETURNS setof EMP + AS 'SELECT * FROM EMP where salary > 1500' + LANGUAGE 'sql'""") + print + print "SELECT name(high_pay()) AS overpaid" + print pgcnx.query("SELECT name(high_pay()) AS overpaid") + +# function with multiple SQL commands +def mult_func(pgcnx): + print "-----------------------------" + print "-- Creating SQL Functions with multiple SQL statements" + print "-- you can also create functions that do more than just a" + print "-- SELECT." + print "-----------------------------" + print + print "-- you may have noticed that Andy has a negative salary. We'll" + print "-- create a function that removes employees with negative " + print "-- salaries." + print + print "SELECT * FROM EMP" + print pgcnx.query("SELECT * FROM EMP") + print + print "CREATE FUNCTION clean_EMP () RETURNS int4" + print " AS 'DELETE FROM EMP WHERE EMP.salary <= 0" + print " SELECT 1 AS ignore_this'" + print " LANGUAGE 'sql'" + pgcnx.query("CREATE FUNCTION clean_EMP () RETURNS int4 AS 'DELETE FROM EMP WHERE EMP.salary <= 0; SELECT 1 AS ignore_this' LANGUAGE 'sql'") + print + print "SELECT clean_EMP()" + print pgcnx.query("SELECT clean_EMP()") + print + print "SELECT * FROM EMP" + print pgcnx.query("SELECT * FROM EMP") + +# base cleanup +def demo_cleanup(pgcnx): + print "-- remove functions that were created in this file" + print + print "DROP FUNCTION clean_EMP()" + print "DROP FUNCTION high_pay()" + print "DROP FUNCTION new_emp()" + print "DROP FUNCTION add_em(int4, int4)" + print "DROP FUNCTION one()" + print + print "DROP TABLE EMP CASCADE" + pgcnx.query("DROP FUNCTION clean_EMP()") + pgcnx.query("DROP FUNCTION high_pay()") + pgcnx.query("DROP FUNCTION new_emp()") + pgcnx.query("DROP FUNCTION add_em(int4, int4)") + pgcnx.query("DROP FUNCTION one()") + pgcnx.query("DROP TABLE EMP CASCADE") + +# main demo function +def demo(pgcnx): + base_func(pgcnx) + comp_func(pgcnx) + mult_func(pgcnx) + demo_cleanup(pgcnx) http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/192cff1f/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/syscat.py ---------------------------------------------------------------------- diff --git a/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/syscat.py b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/syscat.py new file mode 100755 index 0000000..1ab1d58 --- /dev/null +++ b/tools/bin/pythonSrc/PyGreSQL-4.0/tutorial/syscat.py @@ -0,0 +1,149 @@ +# syscat.py - parses some system catalogs +# inspired from the PostgreSQL tutorial +# adapted to Python 1995 by Pascal ANDRE + +print """ +__________________________________________________________________ +MODULE SYSCAT.PY : PARSES SOME POSTGRESQL SYSTEM CATALOGS + +This module is designed for being imported from python prompt + +In order to run the samples included here, first create a connection +using : cnx = syscat.DB(...) + +The "..." should be replaced with whatever arguments you need to open an +existing database. Usually all you need is the name of the database and, +in fact, if it is the same as your login name, you can leave it empty. + +then start the demo with: syscat.demo(cnx) + +Some results may be empty, depending on your base status." + +__________________________________________________________________ +""" + +from pg import DB +import sys + +# waits for a key +def wait_key(): + print "Press <enter>" + sys.stdin.read(1) + +# lists all simple indices +def list_simple_ind(pgcnx): + result = pgcnx.query("""SELECT bc.relname AS class_name, + ic.relname AS index_name, a.attname + FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a + WHERE i.indrelid = bc.oid AND i.indexrelid = bc.oid + AND i.indkey[0] = a.attnum AND a.attrelid = bc.oid + AND i.indproc = '0'::oid AND a.attisdropped = 'f' + ORDER BY class_name, index_name, attname""") + return result + +# list all user defined attributes and their type in user-defined classes +def list_all_attr(pgcnx): + result = pgcnx.query("""SELECT c.relname, a.attname, t.typname + FROM pg_class c, pg_attribute a, pg_type t + WHERE c.relkind = 'r' and c.relname !~ '^pg_' + AND c.relname !~ '^Inv' and a.attnum > 0 + AND a.attrelid = c.oid and a.atttypid = t.oid + AND a.attisdropped = 'f' + ORDER BY relname, attname""") + return result + +# list all user defined base type +def list_user_base_type(pgcnx): + result = pgcnx.query("""SELECT u.usename, t.typname + FROM pg_type t, pg_user u + WHERE u.usesysid = int2in(int4out(t.typowner)) + AND t.typrelid = '0'::oid and t.typelem = '0'::oid + AND u.usename <> 'postgres' order by usename, typname""") + return result + +# list all right-unary operators +def list_right_unary_operator(pgcnx): + result = pgcnx.query("""SELECT o.oprname AS right_unary, + lt.typname AS operand, result.typname AS return_type + FROM pg_operator o, pg_type lt, pg_type result + WHERE o.oprkind='r' and o.oprleft = lt.oid + AND o.oprresult = result.oid + ORDER BY operand""") + return result + +# list all left-unary operators +def list_left_unary_operator(pgcnx): + result = pgcnx.query("""SELECT o.oprname AS left_unary, + rt.typname AS operand, result.typname AS return_type + FROM pg_operator o, pg_type rt, pg_type result + WHERE o.oprkind='l' AND o.oprright = rt.oid + AND o.oprresult = result.oid + ORDER BY operand""") + return result + +# list all binary operators +def list_binary_operator(pgcnx): + result = pgcnx.query("""SELECT o.oprname AS binary_op, + rt.typname AS right_opr, lt.typname AS left_opr, + result.typname AS return_type + FROM pg_operator o, pg_type rt, pg_type lt, pg_type result + WHERE o.oprkind = 'b' AND o.oprright = rt.oid + AND o.oprleft = lt.oid AND o.oprresult = result.oid""") + return result + +# returns the name, args and return type from all function of lang l +def list_lang_func(pgcnx, l): + result = pgcnx.query("""SELECT p.proname, p.pronargs, t.typname + FROM pg_proc p, pg_language l, pg_type t + WHERE p.prolang = l.oid AND p.prorettype = t.oid + AND l.lanname = '%s' + ORDER BY proname""" % l) + return result + +# lists all the aggregate functions and the type to which they can be applied +def list_agg_func(pgcnx): + result = pgcnx.query("""SELECT p.proname, t.typname + FROM pg_aggregate a, pg_proc p, pg_type t + WHERE a.aggfnoid = p.oid + and p.proargtypes[0] = t.oid + ORDER BY proname, typname""") + return result + +# lists all the operator classes that can be used with each access method as +# well as the operators that can be used with the respective operator classes +def list_op_class(pgcnx): + result = pgcnx.query("""SELECT am.amname, opc.opcname, opr.oprname + FROM pg_am am, pg_amop amop, pg_opclass opc, pg_operator opr + WHERE amop.amopid = am.oid and amop.amopclaid = opc.oid + AND amop.amopopr = opr.oid order by amname, opcname, oprname""") + return result + +# demo function - runs all examples +def demo(pgcnx): + import sys, os + save_stdout = sys.stdout + sys.stdout = os.popen("more", "w") + print "Listing simple indices ..." + print list_simple_ind(pgcnx) + print "Listing all attributes ..." + print list_all_attr(pgcnx) + print "Listing all user-defined base types ..." + print list_user_base_type(pgcnx) + print "Listing all left-unary operators defined ..." + print list_left_unary_operator(pgcnx) + print "Listing all right-unary operators defined ..." + print list_right_unary_operator(pgcnx) + print "Listing all binary operators ..." + print list_binary_operator(pgcnx) + print "Listing C external function linked ..." + print list_lang_func(pgcnx, 'C') + print "Listing C internal functions ..." + print list_lang_func(pgcnx, 'internal') + print "Listing SQL functions defined ..." + print list_lang_func(pgcnx, 'sql') + print "Listing 'aggregate functions' ..." + print list_agg_func(pgcnx) + print "Listing 'operator classes' ..." + print list_op_class(pgcnx) + del sys.stdout + sys.stdout = save_stdout
