diff options
Diffstat (limited to 'src/interfaces/python')
21 files changed, 0 insertions, 5160 deletions
diff --git a/src/interfaces/python/Announce b/src/interfaces/python/Announce deleted file mode 100644 index c5ecd8294fb..00000000000 --- a/src/interfaces/python/Announce +++ /dev/null @@ -1,66 +0,0 @@ - -Announce: Release of PyGreSQL version 2.2 -=============================================== - -PyGreSQL v2.2 has been released. -It is available at: ftp://ftp.druid.net/pub/distrib/PyGreSQL-2.2.tgz. - -PostgreSQL is a database system derived from Postgres4.2. It conforms -to (most of) ANSI SQL and offers many interesting capabilities (C -dynamic linking for functions or type definition, etc.). This package -is copyright by the Regents of the University of California, and is -freely distributable. - -Python is an interpreted programming language. It is object oriented, -simple to use (light syntax, simple and straightforward statements), and -has many extensions for building GUIs, interfacing with WWW, etc. An -intelligent web browser (HotJava like) is currently under development -(November 1995), and this should open programmers many doors. Python is -copyrighted by Stichting S Mathematisch Centrum, Amsterdam, The -Netherlands, and is freely distributable. - -PyGreSQL is a 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. - -PyGreSQL 2.2 was developed and tested on a NetBSD 1.3_BETA system. It -is based on the PyGres95 code written by Pascal Andre, -andre@chimay.via.ecp.fr. I changed the version to 2.0 and updated the -code for Python 1.5 and PostgreSQL 6.2.1. While I was at it I upgraded -the code to use full ANSI style prototypes and changed the order of -arguments to connect. Later versions are fixes and enhancements to that. - -Important changes from PyGreSQL 2.1 to PyGreSQL 2.2: - - Added user and password support thanks to Ng Pheng Siong <ngps@post1.com> - - Insert queries return the inserted oid - - Add new pg wrapper (C module renamed to _pg) - - Wrapped database connection in a class. - - Cleaned up some of the tutorial. (More work needed.) - - Added version and __version__. Thanks to thilo@eevolute.com for - the suggestion. - -Important changes from PyGreSQL 2.0 to PyGreSQL 2.1: - - return fields as proper Python objects for field type - - Cleaned up pgext.py - - Added dictresult method - -Important changes from Pygres95 1.0b to PyGreSQL 2.0: - - Updated code for PostgreSQL 6.2.1 and Python 1.5. - - Reformatted code and converted to ANSI . - - Changed name to PyGreSQL (from PyGres95.) - - Changed order of arguments to connect function. - - Created new type pgqueryobject and moved certain methods to it. - - Added a print function for pgqueryobject - - Various code changes - mostly stylistic. - -For more information about each package, please have a look to their -web pages: - - Python : http://www.python.org/ - - PostgreSQL : http://www.PostgreSQL.org/ - - PyGreSQL : http://www.druid.net/pygresql/ - - -D'Arcy J.M. Cain -darcy@druid.net - - diff --git a/src/interfaces/python/ChangeLog b/src/interfaces/python/ChangeLog deleted file mode 100644 index 3d08b87e309..00000000000 --- a/src/interfaces/python/ChangeLog +++ /dev/null @@ -1,49 +0,0 @@ -PyGreSQL changelog. -=================== - -This software is copyright (c) 1995, Pascal Andre (andre@via.ecp.fr) -Further copyright 1997, 1998 by D'Arcy J.M. Cain (darcy@druid.net) -See file README for copyright information. - -Version 2.2 - - Added user and password support thanks to Ng Pheng Siong <ngps@post1.com> - - Insert queries return the inserted oid - - Add new pg wrapper (C modile renamed to _pg) - - Wrapped database connection in a class. - - Cleaned up some of the tutorial. (More work needed.) - - Added version and __version__. Thanks to thilo@eevolute.com for - the suggestion. - -Version 2.1 - - return fields as proper Python objects for field type - - Cleaned up pgext.py - - Added dictresult method - -Version 2.0 (23/12/1997): - - updated code for PostgreSQL 6.2.1 and Python 1.5 - - reformatted code and converted to ANSI - - Changed name to PyGreSQL (from PyGres95) - - changed order of arguments to connect function - - Created new type pgqueryobject and moved certain methods to it. - - Added a print function for pgqueryobject - -Version 1.0b (4/11/1995): - - keyword support for connect function moved from library file to C code - and taken away from library. - - rewrote documentation - - bug fix in connect function - - enhancements in large objects interface methods - -Version 1.0a (30/10/1995) (limited release): - - module adapted to standard Python syntax - - keyword support for connect function in library file - - rewrote default parameters interface (internal use of strings) - - fixed minor bugs in module interface - - redefinition of error messages - -Version 0.9b (10/10/1995) (first public release): - - large objects implementation - - many bug fixes, enhancements, ... - -Version 0.1a (7/10/1995): - - basic libpq functions (SQL access) diff --git a/src/interfaces/python/README b/src/interfaces/python/README deleted file mode 100644 index 4d3bfcfa603..00000000000 --- a/src/interfaces/python/README +++ /dev/null @@ -1,927 +0,0 @@ - -PyGreSQL - v2.2: PostgreSQL module for Python -============================================== - -0. Copyright notice -=================== - - PyGreSQL, version 2.2 - A Python interface for PostgreSQL database. - Written by D'Arcy J.M. Cain, darcy@druid.net<BR> - Based heavily on code written by Pascal Andre, andre@chimay.via.ecp.fr. - Copyright (c) 1995, Pascal ANDRE (andre@via.ecp.fr) - - Permission to use, copy, modify, and distribute this software and its - documentation for any purpose, without fee, and without a written agreement - is hereby granted, provided that the above copyright notice and this - paragraph and the following two paragraphs appear in all copies or in any - new file that contains a substantial portion of this file. - - IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, - SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, - ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE - AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - - THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED - TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR - PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE - AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, - ENHANCEMENTS, OR MODIFICATIONS. - - Further modifications copyright 1997 by D'Arcy J.M. Cain (darcy@druid.net) - subject to the same terms and conditions as above. - -1. Presentation -=============== - -1.1. Introduction ------------------ - -PostgreSQL is a database system derived from Postgres4.2. It conforms to -(most of) ANSI SQL and offers many interesting capabilities (C dynamic linking -for functions or type definition, etc.). This package is copyright by the -Regents of the University of California, and is freely distributable. - -Python is an interpreted programming language. It is object oriented, simple -to use (light syntax, simple and straightforward statements), and has many -extensions for building GUIs, interfacing with WWW, etc. An intelligent web -browser (HotJava like) is currently under development (November 1995), and -this should open programmers many doors. Python is copyrighted by Stichting S -Mathematisch Centrum, Amsterdam, The Netherlands, and is freely distributable. - -PyGreSQL is a 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. - -PyGreSQL 2.0 was developed and tested on a NetBSD 1.3_BETA system. It is -based on the PyGres95 code written by Pascal Andre, andre@chimay.via.ecp.fr. -I changed the version to 2.0 and updated the code for Python 1.5 and -PostgreSQL 6.2.1. While I was at it I upgraded the code to use full ANSI -style prototypes and changed the order of arguments to connect. - - -1.2. Distribution files ------------------------ - - README - this file - Announce - announcement of this release - ChangeLog - changes that affected this package during its history - pgmodule.c - the C python module - pgext.py - PyGreSQL library - This file should go in your Python library directory. It - contains some interesting functions for pg use. All pg - function are imported in this file. - pg.py - PyGreSQL DB class. - tutorial/ - demos directory - Content: basics.py, syscat.py, advanced.py, func.py and - pgtools.py. The samples here have been taken from the - PostgreSQL manual and were used for module testing. They - demonstrate some PostgreSQL features. Pgtools.py is an - add-in used for demonstration. - -1.3. Installation ------------------ - -* You first have to get and build Python and PostgreSQL. - -* PyGreSQL is implemented as two parts, a C module labeled _pg and a - Python wrapper called pg.py. This changed between 2.1 and 2.2. This - should not affect any existing programs but the installation is slightly - different. - -* Find the directory where your 'Setup' file lives (usually ??/Modules) and - copy the 'pgmodule.c' file there. - -* Add the following line to your Setup file - _pg pgmodule.c -I[pgInc] -L[pgLib] -lpq # -lcrypt # needed on some systems - where: - [pgInc] = path of the PostgreSQL include - [pgLib] = path of the PostgreSQL libraries - Some options may be added to this line: - -DNO_DEF_VAR - no default variables support - -DNO_DIRECT - no direct access methods - -DNO_LARGE - no large object support - These options will be described in the next sections. - -* If you want a shared module, make sure that the "*shared*" keyword is - uncommented and add the above line below it. You then need to install - your shared modules with "make sharedinstall." - -* Copy pg.py to the lib directory where the rest of your modules are. For - example, that's /usr/local/lib/Python on my system. - -* Do 'make -f Makefile.pre.in boot' and do 'make && make install' - -* For more details read the documentation at the top of Makefile.pre.in - -* For Linux installation look at README.linux - - -1.4. Where to get ... ? ------------------------ - -The home sites of the different packages are: - - - Python: ftp://ftp.python.org:/pub/python - - PosgreSQL: ftp://ftp.PostgreSQL.org/pub/postgresql-6.4.tar.gz - - PyGreSQL: ftp://ftp.druid.net/pub/distrib/pygresql-2.2.tgz - -A Linux RPM can be picked up from ftp://www.eevolute.com/pub/python/. - - -1.5. Information and support ----------------------------- - -If you need information about these packages please check their web sites: - - - Python: http://www.python.org/ - - PostgreSQL: http://www.postgresql.org/ - - PyGres95: http://www.via.ecp.fr/via/products/pygres.html - - PyGreSQL: http://www.druid.net/pygresql/ - -For support: - - - Python: newgroup comp.lang.python - - PostgreSQL: mailing list (see package documentation for information) - - PyGres95: contact me (andre@via.ecp.fr) for bug reports, ideas, remarks - I will try to answer as long as my free time allow me to do - that. - - PyGreSQL: contact me (darcy@druid.net) concerning the changes to 2.x. - - -2. Programming information -========================== - -This module defines three objects: the pgobject that handles the connection -and all the requests to the database, the pglargeobject that handles -all the accesses to Postgres large objects and pgqueryobject that handles -query results. - -2.1. pg module description ----------------------------- - -The module defines only a few methods that allow to connect to a database and -to allow to define "default variables" that override the environment variables -used by PostgreSQL. - -These "default variables" were designed to allow you to handle general -connections parameters without heavy code in your programs. You can prompt the -user for a value, put it in the default variable, and forget it, without -having to modify environment. The support for default variables can be disabled -by setting the -DNO_DEF_VAR option in the Python Setup file. Methods relative -to this are specified by te tag [DV]. - -All variables are set to None at module initialization, specifying that -standard environment variables should be used. - - 2.1.1. connect - opens a pg connection - ---------------------------------------- - - Syntax: - connect(dbname, host, port, opt, tty, user, passwd) - Parameters: - dbname - name of connected database (string/None) - host - name of the server host (string/None) - port - port used by the database server (integer/-1) - opt - connection options (string/None) - tty - debug terminal (string/None) - user - PostgreSQL user (string/None) - passwd - password for user (string/None) - Return type: - pgobject - the object handling the connection - Exceptions raised: - TypeError - bad argument type, or too many arguments - SyntaxError - duplicate argument definition - pg.error - some error occurred during pg connection definition - (+ all exceptions relative to object allocation) - Description: - This method opens a connection to a specified database on a given - PostgreSQL server. You can use keywords here, as described in the - Python tutorial; - the names of the keywords are the name of the parameters given in the - syntax line. For a precise description of the parameters, please refer to - the PostgreSQL user manual. - - 2.1.2. get_defhost, set_defhost - default server host name handling [DV] - ------------------------------------------------------------------------ - - Syntax: get_defhost() - Parameters: - none - Return type: - string, None - default host specification - Exceptions raised: - SyntaxError - too many arguments - Description: - This method returns the current default host specification, or None if the - environment variables should be used. Environment variables won't be looked - up. - - Syntax: set_defhost(host) - Parameters: - host - new default host (string/None) - Return type: - string, None - previous default host specification - Exceptions raised: - TypeError - bad argument type, or too many arguments - Description: - This methods sets the default host value for new connections. If None is - supplied as parameter, environment variables will be used in future - connections. It returns the previous setting for default host. - - 2.1.3. get_defport, set_defport - default server port handling [DV] - ------------------------------------------------------------------- - - Syntax: get_defport() - Parameters: none - Return type: - integer, None - default port specification - Exceptions raised: - SyntaxError - too many arguments - Description: - This method returns the current default port specification, or None if - the environment variables should be used. Environment variables won't - be looked up. - - Syntax: set_defport(port) - Parameters: - port - new default port (integer/-1) - Return type: - integer, None - previous default port specification - Description: - This methods sets the default port value for new connections. If -1 is - supplied as parameter, environment variables will be used in future - connections. It returns the previous setting for default port. - - 2.1.4. get_defopt, set_defopt - default connection options handling [DV] - ------------------------------------------------------------------------ - - Syntax: get_defopt() - Parameters: none - Return type: - string, None - default options specification - Exceptions raised: - SyntaxError - too many arguments - Description: - This method returns the current default connection options specification, - or None if the environment variables should be used. Environment variables - won't be looked up. - - Syntax: set_defopt(options) - Parameters: - options - new default connection options (string/None) - Return type: - string, None - previous default options specification - Exceptions raised: - TypeError - bad argument type, or too many arguments - Description: - This methods sets the default connection options value for new connections. - If None is supplied as parameter, environment variables will be used in - future connections. It returns the previous setting for default options. - - 2.1.5. get_deftty, set_deftty - default connection debug tty handling [DV] - -------------------------------------------------------------------------- - - Syntax: get_deftty() - Parameters: none - Return type: - string, None - default debug terminal specification - Exceptions raised: - SyntaxError - too many arguments - Description: - This method returns the current default debug terminal specification, or - None if the environment variables should be used. Environment variables - won't be looked up. - - Syntax: set_deftty(terminal) - Parameters: - terminal - new default debug terminal (string/None) - Return type: - string, None - previous default debug terminal specification - Exceptions raised: - TypeError - bad argument type, or too many arguments - Description: - This methods sets the default debug terminal value for new connections. If - None is supplied as parameter, environment variables will be used in future - connections. It returns the previous setting for default terminal. - - 2.1.6. get_defbase, set_defbase - default database name handling [DV] - --------------------------------------------------------------------- - - Syntax: get_defbase() - Parameters: none - Return type: - string, None - default database name specification - Exceptions raised: - SyntaxError - too many arguments - Description: - This method returns the current default database name specification, or - None if the environment variables should be used. Environment variables - won't be looked up. - - Syntax: set_defbase(base) - Parameters: - base - new default base name (string/None) - Return type: - string, None - previous default database name specification - Exceptions raised: - TypeError - bad argument type, or too many arguments - Description: - This methods sets the default database name value for new connections. If - None is supplied as parameter, environment variables will be used in - future connections. It returns the previous setting for default host. - - 2.1.7. Module constants - ----------------------- - - Some constants are defined in the module dictionary. They are intended to be -used as parameters for methods calls. You should refer to PostgreSQL user -manual for more information about them. These constants are: - - - large objects access modes, used by (pgobject.)locreate and - (pglarge.)open: (pg.)INV_READ, (pg.)INV_WRITE, (pg.)INV_ARCHIVE - - positional flags, used by (pglarge.)seek: (pg.)SEEK_SET, - (pg.)SEEK_CUR, (pg.)SEEK_END. - - version and __version__ constants that give the current version. - -2.2. pgobject description ---------------------------- - - This object handle a connection to a PostgreSQL database. It embeds and -hides all the parameters that define this connection, thus just leaving really -significant parameters in function calls. - Some methods give direct access to the connection socket. They are specified -by the tag [DA]. DO NOT USE THEM UNLESS YOU REALLY KNOW WHAT YOU ARE DOING. If -you prefer disabling them, set the -DNO_DIRECT option in the Python Setup file. - Some other methods give access to large objects (refer to PostgreSQL user -manual for more information about these). if you want to forbid access to these -from the module, set the -DNO_LARGE option in the Python Setup file. These -methods are specified by the tag [LO]. - - 2.2.1. query - executes a SQL command string - -------------------------------------------- - - Syntax: query(command) - Parameters: - command - SQL command (string) - Return type: - pgqueryobject, None - result values - Exceptions raised: - TypeError - bad argument type, or too many arguments. - ValueError - empty SQL query - pg.error - error during query processing, or invalid connection - Description: - This method simply sends a SQL query to the database. If the query is - an insert statement, the return value is the OID of the newly - inserted row. If it is otherwise a query that does not return a result - (ie. is not a some kind of SELECT statement), it returns None. - Otherwise, it returns a pgqueryobject that can be accessed via the - getresult method or printed. - - pgqueryobject methods - --------------------- - - 2.2.1.1. getresult - gets the values returned by the query - ------------------------------------------------------------- - - Syntax: getresult() - Parameters: none - Return type: - list - result values - Exceptions raised: - SyntaxError - too many parameters - pg.error - invalid previous result - Description: - This method returns the list of the values returned by the query. - More information about this result may be get using listfields, - fieldname and fiednum methods. - - 2.2.1.2. dictresult - like getresult but returns list of dictionaries - --------------------------------------------------------------------- - - Syntax: dictresult() - Parameters: none - Return type: - list - result values as a dictionary - Exceptions raised: - SyntaxError - too many parameters - pg.error - invalid previous result - Description: - This method returns the list of the values returned by the query - with each tuple returned as a dictionary with the field names - used as the dictionary index. - - - 2.2.3. listfields - lists the fields names of the previous query result - ----------------------------------------------------------------------- - - Syntax: listfields() - Parameters: none - Return type: - list - fields names - Exceptions raised: - SyntaxError - too many parameters - pg.error - invalid previous result, or invalid connection - Description: - This method returns the list of names of the fields defined for the - query result. The fields are in the same order as the result values. - - 2.2.4. fieldname, fieldnum - field name-number conversion - --------------------------------------------------------- - - Syntax: fieldname(i) - Parameters: - i - field number (integer) - Return type: - string - field name - Exceptions raised: - TypeError - bad parameter type, or too many parameters - ValueError - invalid field number - pg.error - invalid previous result, or invalid connection - Description: - This method allows to find a field name from its rank number. It can be - useful for displaying a result. The fields are in the same order than the - result values. - - Syntax: fieldnum(name) - Parameters: - name - field name (string) - Return type: - integer - field number - Exceptions raised: - TypeError - bad parameter type, or too many parameters - ValueError - unknown field name - pg.error - invalid previous result, or invalid connection - Description: - This method returns a field number from its name. It can be used to - build a function that converts result list strings to their correct - type, using a hardcoded table definition. The number returned is the - field rank in the result values list. - - 2.2.5. getnotify - gets the last notify from the server - ------------------------------------------------------- - - Syntax: getnotify() - Parameters: none - Return type: - tuple, None - last notify from server - Exceptions raised: - SyntaxError - too many parameters - pg.error - invalid connection - Description: - This methods try to get a notify from the server (from the SQL statement - NOTIFY). If the server returns no notify, the methods returns None. - Otherwise, it returns a tuple (couple) (relname, pid), where relname is the - name of the notify and pid the process id of the connection that triggered - the notify. - - 2.2.6. inserttable - insert a list into a table - ----------------------------------------------- - - Syntax: inserttable(table, values) - Parameters: - table - the table name (string) - values - list of rows values (list) - Return type: - None - Exception raised: - pg.error - invalid connection - TypeError - bad argument type, or too many arguments - Description: - This method allow to quickly insert large blocks of data in a table: it - inserts the whole values list into the given table. The list is a list of - tuples/lists that define the values for each inserted row. The rows values - may contain string, integer, long or double (real) values. - BE VERY CAREFUL: this method doesn't typecheck the fields according to the - table definition; it just look whether or not it knows how to handle such - types. - - 2.2.7. putline - writes a line to the server socket [DA] - -------------------------------------------------------- - - Syntax: putline(line) - Parameters: - line - line to be written (string) - Return type: - None - Exceptions raised: - pg.error - invalid connection - TypeError - bad parameter type, or too many parameters - Description: - This method allows to directly write a string to the server socket. - - 2.2.8. getline - gets a line from server socket [DA] - ---------------------------------------------------- - - Syntax: getline() - Parameters: none - Return type: - string - the line read - Exceptions raised: - pg.error - invalid connection - SyntaxError - too many parameters - Description: - This method allows to directly read a string from the server socket. - - 2.2.9. endcopy - synchronizes client and server [DA] - ---------------------------------------------------- - - Syntax: endcopy() - Parameters: none - Return type: - None - Exceptions raised: - pg.error - invalid connection - SyntaxError - too many parameters - Description: - The use of direct access methods may desynchonize client and server. This - method ensure that client and server will be synchronized. - - 2.2.10. locreate - creates of large object in the database [LO] - --------------------------------------------------------------- - - Syntax: locreate(mode) - Parameters: - mode - large object create mode - Return type: - pglarge - object handling the postgres large object - Exceptions raised: - pg.error - invalid connection, or creation error - TypeError - bad parameter type, or too many parameters - Description: - This method creates a large object in the database. The mode can be defined - by OR-ing the constants defined in the pg module (INV_READ, INV_WRITE and - INV_ARCHIVE). Please refer to PostgreSQL user manual for a description of - the mode values. - - 2.2.11. getlo - builds a large object from given oid [LO] - --------------------------------------------------------- - - Syntax: getlo(oid) - Parameters: - oid - oid of the existing large object (integer) - Return type: - pglarge - object handling the postgres large object - Exceptions raised: - pg.error - invalid connection - TypeError - bad parameter type, or too many parameters - ValueError - bad oid value (0 is invalid_oid) - Description: - This method allows to reuse a formerly created large object through the - pglarge interface, providing the user have its oid. - - 2.2.12. loimport - import a file to a postgres large object [LO] - ---------------------------------------------------------------- - - Syntax: loimport(name) - Parameters: - name - the name of the file to be imported (string) - Return type: - pglarge - object handling the postgres large object - Exceptions raised: - pg.error - invalid connection, or error during file import - TypeError - bad argument type, or too many arguments - Description: - This methods allows to create large objects in a very simple way. You just - give the name of a file containing the data to be use. - - 2.2.13. pgobject attributes - ----------------------------- - - Every pgobject defines a set of read-only attributes that describe the -connection and its status. These attributes are: - host - the hostname of the server (string) - port - the port of the server (integer) - db - the selected database (string) - options - the connection options (string) - tty - the connection debug terminal (string) - user - the username on the database system (string) - status - the status of the connection (integer: 1 - OK, 0 - BAD) - error - the last warning/error message from the server (string) - -2.3. pglarge description --------------------------- - - This object handles all the request concerning a postgres large object. It -embeds and hides all the 'recurrent' variables (object oid and connection), -exactly in the same way pgobjects do, thus only keeping significant -parameters in function calls. It keeps a reference to the pgobject used for -its creation, sending requests though with its parameters. Any modification but -dereferencing the pgobject will thus affect the pglarge object. -Dereferencing the initial pgobject is not a problem since Python won't -deallocate it before the large object dereference it. - All functions return a generic error message on call error, whatever the -exact error was. The 'error' attribute of the object allow to get the exact -error message. - - 2.3.1. open - opens a large object - ---------------------------------- - - Syntax: open(mode) - Parameters: - mode - open mode definition (integer) - Return type: - None - Exceptions raised: - pg.error - invalid connection - TypeError - bad parameter type, or too many parameters - IOError - already opened object, or open error - Description: - This method opens a large object for reading/writing, in the same way than - the UNIX open() function. The mode value can be obtained by OR-ing the - constants defined in the pgmodule (INV_READ, INV_WRITE). - - 2.3.2. close - closes a large object - ------------------------------------ - - Syntax: close() - Parameters: none - Return type: - None - Exceptions raised: - pg.error - invalid connection - SyntaxError - too many parameters - IOError - object is not opened, or close error - Description: - This method closes a previously opened large object, in the same way than - the UNIX close() function. - - 2.3.4. read, write, tell, seek, unlink - file like large object handling - ------------------------------------------------------------------------ - - Syntax: read(size) - Parameters: - size - maximal size of the buffer to be read - Return type: - sized string - the read buffer - Exceptions raised: - pg.error - invalid connection or invalid object - TypeError - bad parameter type, or too many parameters - IOError - object is not opened, or read error - Description: - This function allows to read data from a large object, starting at current - position. - - Syntax: write(string) - Parameters: - (sized) string - buffer to be written - Return type: - None - Exceptions raised: - pg.error - invalid connection or invalid object - TypeError - bad parameter type, or too many parameters - IOError - object is not opened, or write error - Description: - This function allows to write data to a large object, starting at current - position. - - Syntax: seek(offset, whence) - Parameters: - offset - position offset - whence - positional parameter - Return type: - integer - new position in object - Exception raised: - pg.error - invalid connection or invalid object - TypeError - bad parameter type, or too many parameters - IOError - object is not opened, or seek error - Description: - This method allows to move the position cursor in the large object. The - whence parameter can be obtained by OR-ing the constants defined in the - pg module (SEEK_SET, SEEK_CUR, SEEK_END). - - Syntax: tell() - Parameters: none - Return type: - integer - current position in large object - Exception raised: - pg.error - invalid connection or invalid object - SyntaxError - too many parameters - IOError - object is not opened, or seek error - Description: - This method allows to get the current position in the large object. - - Syntax: unlink() - Parameter: none - Return type: - None - Exception raised: - pg.error - invalid connection or invalid object - SyntaxError - too many parameters - IOError - object is not closed, or unlink error - Description: - This methods unlinks (deletes) the postgres large object. - - 2.3.5. size - gives the large object size - ----------------------------------------- - - Syntax: size() - Parameters: none - Return type: - integer - large object size - Exceptions raised: - pg.error - invalid connection or invalid object - SyntaxError - too many parameters - IOError - object is not opened, or seek/tell error - Description: - This (composite) method allows to get the size of a large object. Currently - the large object needs to be opened. It was implemented because this - function is very useful for a WWW interfaced database. - - 2.3.6. export - saves a large object to a file - ---------------------------------------------- - - Syntax: export(name) - Parameters: - name - file to be created - Return type: - None - Exception raised: - pg.error - invalid connection or invalid object - TypeError - bad parameter type, or too many parameters - IOError - object is not closed, or export error - Description: - This methods allows to dump the content of a large object in a very simple - way. The exported file is created on the host of the program, not the - server host. - - 2.3.7. Object attributes - ------------------------ - - pglarge objects define a read-only set of attributes that allow to get some -information about it. These attributes are: - oid - the oid associated with the object - pgcnx - the pgobject associated with the object - error - the last warning/error message of the connection -BE CAREFUL: in multithreaded environments, 'error' may be modified by another -thread using the same pgobject. Remember these object are shared, not -duplicated. You should provide some locking to be able if you want to check -this. - The oid attribute is very interesting because it allow you reuse the oid -later, creating the pglarge object with a pgobject getlo() method call. - - -3. The pg wrapper -================ - -The previous functions are wrapped in a module called pg. The module -has a class called DB. The above functions are also included in the -name space so it isn't necessary to import both modules. The preferred -way to use this module is as follows. - -from pg import DB -db = DB(...) # See description of the initialization method below. - -The following describes the methods and variables of this class. - - - 3.1. Initialization - ------------------- - The DB class is initialized with the same arguments as the connect - method described in section 2. It also initializes a few internal - variables. The statement 'db = DB()' will open the local database - with the name of the user just like connect() does. - - 3.2. pkey - --------- - Syntax: - pkey(table) - Parameters: - table - name of table - Returns: - Name of field which is the primary key of the table. - Description: - This method returns the primary key of a table. Note that this raises - an exception if the table doesn't have a primary key. Further, in the - current implementation of PostgreSQL the 'PRIMARY KEY' syntax doesn't - actually fill in the necessary tables to determine primary keys. You - can do this yourself with the following query. Replace $0 with the - table name and $1 with the attribute that is the primary key. - - UPDATE pg_index SET indisprimary = 't' - WHERE pg_index.oid in (SELECT pg_index.oid - FROM pg_class, pg_attribute, pg_index - WHERE pg_class.oid = pg_attribute.attrelid AND - pg_class.oid = pg_index.indrelid AND - pg_index.indkey[0] = pg_attribute.attnum AND - pg_class.relname = '$0' AND - pg_attribute.attname = '$1'); - - 3.3. get_attnames - ----------------- - Syntax: - get_attnames(table) - Parameters: - table - name of table - Returns: - List of attribute names - Description: - Given the name of a table, digs out the list of attribute names. - - 3.4. get - -------- - Syntax: - get(table, arg, [keyname]) - Parameters: - table - name of table - arg - either a dictionary or the value to be looked up - keyname - name of field to use as key (optional) - Returns: - A dictionary mapping attribute names to row values. - Description: - This method is the basic mechanism to get a single row. It assumes - that the key specifies a unique row. If keyname is not specified - then the primary key for the table is used. If arg is a dictionary - then the value for the key is taken from it and it is modified to - include the new values, replacing existing values where necessary. - The oid is also put into the dictionary but in order to allow the - caller to work with multiple tables, the attribute name is munged - to make it unique. It consists of the string "oid_" followed by - the name of the table. - - - 3.5. insert - ----------- - Syntax: - insert(table, a) - Parameters: - table - name of table - a - a dictionary of values - Returns: - The OID of the newly inserted row. - Description: - This method inserts values into the table specified filling in the - values from the dictionary. - - - 3.6. update - ----------- - Syntax: - update(table, a) - Parameters: - table - name of table - a - a dictionary of values - Returns: - A dictionary with the new row - Description: - Similar to insert but updates an existing row. The update is based - on the OID value as munged by get. The array returned is the - one sent modified to reflect any changes caused by the update due - to triggers, rules, defaults, etc. - - 3.7. clear - ---------- - Syntax: - clear(table, [a]) - Parameters: - table - name of table - a - a dictionary of values - Returns: - A dictionary with an empty row - Description: - This method clears all the attributes to values determined by the types. - Numeric types are set to 0, dates are set to 'TODAY' and everything - else is set to the empty string. If the array argument is present, - it is used as the array and any entries matching attribute names - are cleared with everything else left unchanged. - - 3.8. delete - ----------- - Syntax: - delete(table, a) - Parameters: - table - name of table - a - a dictionary of values - Returns: - None - Description: - This method deletes the row from a table. It deletes based on the OID - as munged as described above. - - 3.9. Convenience methods - ------------------------ - In order to allow all access to a connection to be done through the DB - class, the following methods wrap the basic functions. - - query - reset - getnotify - inserttable - - The following depend on being activated in the underlying C code - - putline - getline - endcopy - locreate - getlo - loimport - - -4. Future directions -==================== - -The large object and direct access functions need much more attention. - -I want to add a DB-SIG API wrapper around the underlying module. - diff --git a/src/interfaces/python/advanced.py b/src/interfaces/python/advanced.py deleted file mode 100755 index 13d1037e13e..00000000000 --- a/src/interfaces/python/advanced.py +++ /dev/null @@ -1,171 +0,0 @@ -#! /usr/local/bin/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 "__________________________________________________________________" -print "MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIAL" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = advanced.connect(...)" -print "then start the demo with: advanced.demo(cnx)" -print "__________________________________________________________________" - -from pgtools import * -from pgext import * - -# 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 " schedule text[][]" - print ")" - pgcnx.query("CREATE TABLE sal_emp (" \ - "name text," \ - "pay_by_quarter int4[]," \ - "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 " '{{\"meeting\", \"lunch\"}, {}}')" - print - print "INSERT INTO sal_emp VALUES (" - print " 'Carol'," - print " '{20000,25000,25000,25000}'," - print " '{{\"talk\", \"consult\"}, {\"meeting\"}}')" - print - pgcnx.query("INSERT INTO sal_emp VALUES (" \ - "'Bill', '{10000,10000,10000,10000}'," \ - "'{{\"meeting\", \"lunch\"}, {}}')") - pgcnx.query("INSERT INTO sal_emp VALUES (" \ - "'Carol', '{20000,25000,25000,25000}'," \ - "'{{\"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 "-- 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 "-- 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) diff --git a/src/interfaces/python/basics.py b/src/interfaces/python/basics.py deleted file mode 100755 index a65f16911d3..00000000000 --- a/src/interfaces/python/basics.py +++ /dev/null @@ -1,284 +0,0 @@ -#! /usr/local/bin/python -# basics.py - basic SQL commands tutorial -# inspired from the Postgres95 tutorial -# adapted to Python 1995 by Pascal ANDRE - -print "__________________________________________________________________" -print "MODULE BASICS.PY : BASIC SQL COMMANDS TUTORIAL" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = basics.connect(...)" -print "then start the demo with: basics.demo(cnx)" -print "__________________________________________________________________" - -from pgext import * -from pgtools import * - -# 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 temp " - print "FROM weather" - print "WHERE city = 'San Francisco' " - print " and prcp > 0.0" - pgcnx.query("SELECT * INTO TABLE temp FROM weather " \ - "WHERE city = 'San Francisco' and prcp > 0.0") - print "Fills the table temp, that can be listed with :" - print "SELECT * from temp" - print pgcnx.query("SELECT * from temp") - -# 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, temp" - pgcnx.query("DROP TABLE weather, cities, temp") - -# 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) diff --git a/src/interfaces/python/func.py b/src/interfaces/python/func.py deleted file mode 100755 index 90f012a0f9d..00000000000 --- a/src/interfaces/python/func.py +++ /dev/null @@ -1,193 +0,0 @@ -# func.py - demonstrate the use of SQL functions -# inspired from the PostgreSQL tutorial -# adapted to Python 1995 by Pascal ANDRE - -print "__________________________________________________________________" -print "MODULE FUNC.PY : SQL FUNCTION DEFINITION TUTORIAL" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = func.connect(...)" -print "then start the demo with: func.demo(cnx)" -print "__________________________________________________________________" - -from pgtools import * -from pgext import * - -# 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" - 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") - -# main demo function -def demo(pgcnx): - base_func(pgcnx) - comp_func(pgcnx) - mult_func(pgcnx) - demo_cleanup(pgcnx) diff --git a/src/interfaces/python/mkdefines b/src/interfaces/python/mkdefines deleted file mode 100755 index fd5eb6f4cd7..00000000000 --- a/src/interfaces/python/mkdefines +++ /dev/null @@ -1,11 +0,0 @@ -#! /usr/local/bin/python - -import string - -# change this if you have it somewhere else -for l in open("/usr/local/pgsql/src/include/catalog/pg_type.h").readlines(): - tokens = string.split(l) - if len(tokens) == 0 or tokens[0] != "#define": continue - - if tokens[1] in ('CASHOID', 'INT2OID', 'INT4OID', 'OIDOID', 'FLOAT4OID', 'FLOAT8OID'): - print l, diff --git a/src/interfaces/python/pg.py b/src/interfaces/python/pg.py deleted file mode 100644 index b463f5ee7ba..00000000000 --- a/src/interfaces/python/pg.py +++ /dev/null @@ -1,223 +0,0 @@ -# pgutil.py -# Written by D'Arcy J.M. Cain - -# This library implements some basic database management stuff -# It includes the pg module and builds on it - -from _pg import * -import string, re, sys - -# utility function -# We expect int, seq, decimal, text or date (more later) -def _quote(d, t): - if t in ['int', 'decimal', 'seq']: - if d == "": return 0 - return "%s" % d - - if t == 'bool': - if string.upper(d) in ['T', 'TRUE', 'Y', 'YES', 1, '1', 'ON']: - return "'t'" - else: - return "'f'" - - if d == "": return "null" - return "'%s'" % string.strip(re.sub('\'', '\'\'', "%s" % d)) - -class DB: - """This class wraps the pg connection type""" - - def __init__(self, *args): - self.db = apply(connect, args) - self.attnames = {} - self.pkeys = {} - self.debug = None # For debugging scripts, set to output format - # that takes a single string arg. For example - # in a CGI set to "%s<BR>" - - # Get all the primary keys at once - for rel, att in self.db.query("""SELECT - pg_class.relname, pg_attribute.attname - FROM pg_class, pg_attribute, pg_index - WHERE pg_class.oid = pg_attribute.attrelid AND - pg_class.oid = pg_index.indrelid AND - pg_index.indkey[0] = pg_attribute.attnum AND - pg_index.indisprimary = 't'""").getresult(): - self.pkeys[rel] = att - - def pkey(self, cl): - # will raise an exception if primary key doesn't exist - return self.pkeys[cl] - - def get_attnames(self, cl): - # May as well cache them - if self.attnames.has_key(cl): - return self.attnames[cl] - - query = """SELECT pg_attribute.attname, pg_type.typname - FROM pg_class, pg_attribute, pg_type - WHERE pg_class.relname = '%s' AND - pg_attribute.attnum > 0 AND - pg_attribute.attrelid = pg_class.oid AND - pg_attribute.atttypid = pg_type.oid""" - - l = {} - for attname, typname in self.db.query(query % cl).getresult(): - if re.match("^int", typname): - l[attname] = 'int' - elif re.match("^oid", typname): - l[attname] = 'int' - elif re.match("^text", typname): - l[attname] = 'text' - elif re.match("^char", typname): - l[attname] = 'text' - elif re.match("^name", typname): - l[attname] = 'text' - elif re.match("^abstime", typname): - l[attname] = 'date' - elif re.match("^date", typname): - l[attname] = 'date' - elif re.match("^bool", typname): - l[attname] = 'bool' - elif re.match("^float", typname): - l[attname] = 'decimal' - elif re.match("^money", typname): - l[attname] = 'money' - else: - l[attname] = 'text' - - self.attnames[cl] = l - return self.attnames[cl] - - # return a tuple from a database - def get(self, cl, arg, keyname = None): - if keyname == None: # use the primary key by default - keyname = self.pkeys[cl] - - fnames = self.get_attnames(cl) - - if type(arg) == type({}): - # To allow users to work with multiple tables we munge the - # name when the key is "oid" - if keyname == 'oid': k = arg['oid_%s' % cl] - else: k = arg[keyname] - else: - k = arg - arg = {} - - # We want the oid for later updates if that isn't the key - if keyname == 'oid': - q = "SELECT * FROM %s WHERE oid = %s" % (cl, k) - else: - q = "SELECT oid AS oid_%s, %s FROM %s WHERE %s = %s" % \ - (cl, string.join(fnames.keys(), ','),\ - cl, keyname, _quote(k, fnames[keyname])) - - if self.debug != None: print self.debug % q - res = self.db.query(q).dictresult() - if res == []: - raise error, \ - "No such record in %s where %s is %s" % \ - (cl, keyname, _quote(k, fnames[keyname])) - return None - - for k in res[0].keys(): - arg[k] = res[0][k] - - return arg - - # Inserts a new tuple into a table - def insert(self, cl, a): - fnames = self.get_attnames(cl) - l = [] - n = [] - for f in fnames.keys(): - if a.has_key(f): - if a[f] == "": l.append("null") - else: l.append(_quote(a[f], fnames[f])) - n.append(f) - - try: - q = "INSERT INTO %s (%s) VALUES (%s)" % \ - (cl, string.join(n, ','), string.join(l, ',')) - if self.debug != None: print self.debug % q - a['oid_%s' % cl] = self.db.query(q) - except: - raise error, "Error inserting into %s: %s" % (cl, sys.exc_value) - - # reload the dictionary to catch things modified by engine - return self.get(cl, a, 'oid') - - # update always works on the oid which get returns - def update(self, cl, a): - q = "SELECT oid FROM %s WHERE oid = %s" % (cl, a['oid_%s' % cl]) - if self.debug != None: print self.debug % q - res = self.db.query(q).getresult() - if len(res) < 1: - raise error, "No record in %s where oid = %s (%s)" % \ - (cl, a['oid_%s' % cl], sys.exc_value) - - v = [] - k = 0 - fnames = self.get_attnames(cl) - - for ff in fnames.keys(): - if a.has_key(ff) and a[ff] != res[0][k]: - v.append("%s = %s" % (ff, _quote(a[ff], fnames[ff]))) - - if v == []: - return None - - try: - q = "UPDATE %s SET %s WHERE oid = %s" % \ - (cl, string.join(v, ','), a['oid_%s' % cl]) - if self.debug != None: print self.debug % q - self.db.query(q) - except: - raise error, "Can't update %s: %s" % (cl, sys.exc_value) - - # reload the dictionary to catch things modified by engine - return self.get(cl, a, 'oid') - - # At some point we will need a way to get defaults from a table - def clear(self, cl, a = {}): - fnames = self.get_attnames(cl) - for ff in fnames.keys(): - if fnames[ff] in ['int', 'decimal', 'seq', 'money']: - a[ff] = 0 - elif fnames[ff] == 'date': - a[ff] = 'TODAY' - else: - a[ff] = "" - - a['oid'] = 0 - return a - - # Like update, delete works on the oid - # one day we will be testing that the record to be deleted - # isn't referenced somewhere (or else PostgreSQL will) - def delete(self, cl, a): - try: - q = "DELETE FROM %s WHERE oid = %s" % (cl, a['oid_%s' % cl]) - if self.debug != None: print self.debug % q - self.db.query(q) - except: - return "Can't delete %s: %s" % (cl, sys.exc_value) - - return None - - - # The rest of these methods are for convenience. Note that X.method() - # and X.db.method() are equivalent - def query(self, query): return self.db.query(query) - def reset(self): self.db.reset() - def getnotify(self): self.db.getnotify() - def inserttable(self): self.db.inserttable() - - # The following depend on being activated in the underlying C code - def putline(self): self.db.putline() - def getline(self): self.db.getline() - def endcopy(self): self.db.endcopy() - def locreate(self): self.db.locreate() - def getlo(self): self.db.getlo() - def loimport(self): self.db.loimport() - diff --git a/src/interfaces/python/pgext.py b/src/interfaces/python/pgext.py deleted file mode 100644 index f1a7cfa6062..00000000000 --- a/src/interfaces/python/pgext.py +++ /dev/null @@ -1,39 +0,0 @@ -from pg import * - -# This library file contains some common functions not directly provided by the -# PostGres C library. It offers too a keyword interface for pgmodule connect -# function. - -# encapsulate pg connect function for keywords enabling -def doconnect(dbname = None, host = None, port = None, opt = None, tty = None): - return connect(dbname, host, port, opt, tty) - -# list all databases on the server -def ListDB(pgcnx): - list = [] - for node in pgcnx.query("SELECT datname FROM pg_database").getresult(): - list.append(node[0]) - return list - -# list all tables (classes) in the selected database -def ListTables(pgcnx): - list = [] - for node in pgcnx.query("""SELECT relname FROM pg_class - WHERE relkind = 'r' AND - relname !~ '^Inv' AND - relname !~ '^pg_'""").getresult(): - list.append(node[0]) - return list - -# list table fields (attribute) in given table -def ListAllFields(pgcnx, table): - list = [] - for node in pgcnx.query("""SELECT c.relname, a.attname, t.typname - FROM pg_class c, pg_attribute a, pg_type t - WHERE c.relname = '%s' AND - a.attnum > 0 AND - a.attrelid = c.oid AND - a.atttypid = t.oid - ORDER BY relname, attname""" % table).getresult(): - list.append(node[1], node[2]) - return list diff --git a/src/interfaces/python/pgmodule.c b/src/interfaces/python/pgmodule.c deleted file mode 100644 index 34c534e5ce8..00000000000 --- a/src/interfaces/python/pgmodule.c +++ /dev/null @@ -1,2115 +0,0 @@ -/* - * PyGres, version 2.2 A Python interface for PostgreSQL database. Written by - * D'Arcy J.M. Cain, (darcy@druid.net). Based heavily on code written by - * Pascal Andre, andre@chimay.via.ecp.fr. Copyright (c) 1995, Pascal Andre - * (andre@via.ecp.fr). - * - * Permission to use, copy, modify, and distribute this software and its - * documentation for any purpose, without fee, and without a written - * agreement is hereby granted, provided that the above copyright notice and - * this paragraph and the following two paragraphs appear in all copies or in - * any new file that contains a substantial portion of this file. - * - * IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, - * SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, - * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE - * AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. - * - * THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED - * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR - * PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE - * AUTHOR HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, - * ENHANCEMENTS, OR MODIFICATIONS. - * - * Further modifications copyright 1997 by D'Arcy J.M. Cain (darcy@druid.net) - * subject to the same terms and conditions as above. - * - */ - -#include <Python.h> -#include <libpq-fe.h> -#include <libpq/libpq-fs.h> -#include <stdio.h> -#include <string.h> - -/* really bad stuff here - I'm so naughty */ -/* If you need to you can run mkdefines to get */ -/* current defines but it should not have changed */ -#define INT2OID 21 -#define INT4OID 23 -#define OIDOID 26 -#define FLOAT4OID 700 -#define FLOAT8OID 701 -#define CASHOID 790 - -static PyObject *PGError; -static char *PyPgVersion = "2.2"; - -/* taken from fileobject.c */ -#define BUF(v) PyString_AS_STRING((PyStringObject *)(v)) - -#define CHECK_OPEN 1 -#define CHECK_CLOSE 2 - -#define MAX_BUFFER_SIZE MAX_QUERY_SIZE /* maximum transaction size */ - -#ifndef NO_DIRECT -#define DIRECT_ACCESS 1 /* enables direct access functions */ -#endif /* NO_DIRECT */ - -#ifndef NO_LARGE -#define LARGE_OBJECTS 1 /* enables large objects support */ -#endif /* NO_LARGE */ - -#ifndef NO_DEF_VAR -#define DEFAULT_VARS 1 /* enables default variables use */ -#endif /* NO_DEF_VAR */ - -/* --------------------------------------------------------------------- */ - -/* MODULE GLOBAL VARIABLES */ - -#ifdef DEFAULT_VARS - -PyObject *pg_default_host; /* default database host */ -PyObject *pg_default_base; /* default database name */ -PyObject *pg_default_opt; /* default connection options */ -PyObject *pg_default_tty; /* default debug tty */ -PyObject *pg_default_port; /* default connection port */ -PyObject *pg_default_user; /* default username */ -PyObject *pg_default_passwd; /* default password */ - -#endif /* DEFAULT_VARS */ - -/* --------------------------------------------------------------------- */ - -/* OBJECTS DECLARATION */ - -/* pg connection object */ - -typedef struct -{ - PyObject_HEAD - int valid; /* validity flag */ - PGconn *cnx; /* PostGres connection handle */ -} pgobject; - -staticforward PyTypeObject PgType; - -#define is_pgobject(v) ((v)->ob_type == &PgType) - -/* pg query object */ - -typedef struct -{ - PyObject_HEAD - PGresult *last_result; /* last result content */ -} pgqueryobject; - -staticforward PyTypeObject PgQueryType; - -#define is_pgqueryobject(v) ((v)->ob_type == &PgQueryType) - -#ifdef LARGE_OBJECTS -/* pg large object */ - -typedef struct -{ - PyObject_HEAD - pgobject * pgcnx; - Oid lo_oid; - int lo_fd; -} pglargeobject; - -staticforward PyTypeObject PglargeType; - -#define is_pglargeobject(v) ((v)->ob_type == &PglargeType) -#endif /* LARGE_OBJECTS */ - -/* --------------------------------------------------------------------- */ - -/* INTERNAL FUNCTIONS */ - -#ifdef LARGE_OBJECTS -/* validity check (large object) */ -static int -check_lo(pglargeobject * self, int level) -{ - if (!self->lo_oid) - { - PyErr_SetString(PGError, "object is not valid (null oid)."); - return 0; - } - - if (level & CHECK_OPEN) - { - if (self->lo_fd < 0) - { - PyErr_SetString(PyExc_IOError, "object is not opened."); - return 0; - } - } - - if (level & CHECK_CLOSE) - { - if (self->lo_fd >= 0) - { - PyErr_SetString(PyExc_IOError, "object is already opened."); - return 0; - } - } - - return 1; -} - -#endif /* LARGE_OBJECTS */ - -/* --------------------------------------------------------------------- */ - -#ifdef LARGE_OBJECTS -/* PG CONNECTION OBJECT IMPLEMENTATION */ - -/* pglargeobject initialisation (from pgobject) */ - -/* creates large object */ -static PyObject * -pg_locreate(pgobject * self, PyObject * args) -{ - int mode; - pglargeobject *npglo; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "i", &mode)) - { - PyErr_SetString(PyExc_TypeError, - "locreate(mode), with mode (integer)."); - return NULL; - } - - if ((npglo = PyObject_NEW(pglargeobject, &PglargeType)) == NULL) - return NULL; - - npglo->pgcnx = self; - Py_XINCREF(self); - npglo->lo_fd = -1; - npglo->lo_oid = lo_creat(self->cnx, mode); - - /* checks result validity */ - if (npglo->lo_oid == 0) - { - PyErr_SetString(PGError, "can't create large object."); - Py_XDECREF(npglo); - return NULL; - } - - return (PyObject *) npglo; -} - -/* init from already known oid */ -static PyObject * -pg_getlo(pgobject * self, PyObject * args) -{ - int lo_oid; - pglargeobject *npglo; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "i", &lo_oid)) - { - PyErr_SetString(PyExc_TypeError, "loopen(oid), with oid (integer)."); - return NULL; - } - - if (!lo_oid) - { - PyErr_SetString(PyExc_ValueError, "the object oid can't be null."); - return NULL; - } - - /* creates object */ - if ((npglo = PyObject_NEW(pglargeobject, &PglargeType)) == NULL) - return NULL; - - npglo->pgcnx = self; - Py_XINCREF(self); - npglo->lo_fd = -1; - npglo->lo_oid = lo_oid; - - return (PyObject *) npglo; -} - -/* import unix file */ -static PyObject * -pg_loimport(pgobject * self, PyObject * args) -{ - char *name; - pglargeobject *npglo; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "s", &name)) - { - PyErr_SetString(PyExc_TypeError, "loimport(name), with name (string)."); - return NULL; - } - - if ((npglo = PyObject_NEW(pglargeobject, &PglargeType)) == NULL) - return NULL; - - npglo->pgcnx = self; - Py_XINCREF(self); - npglo->lo_fd = -1; - npglo->lo_oid = lo_import(self->cnx, name); - - /* checks result validity */ - if (npglo->lo_oid == 0) - { - PyErr_SetString(PGError, "can't create large object."); - Py_XDECREF(npglo); - return NULL; - } - - return (PyObject *) npglo; -} - -/* pglargeobject methods */ - -/* destructor */ -static void -pglarge_dealloc(pglargeobject * self) -{ - if (self->lo_fd >= 0 && self->pgcnx->valid == 1) - lo_close(self->pgcnx->cnx, self->lo_fd); - - Py_XDECREF(self->pgcnx); - PyMem_DEL(self); -} - -/* opens large object */ -static PyObject * -pglarge_open(pglargeobject * self, PyObject * args) -{ - int mode, fd; - - /* check validity */ - if (!check_lo(self, CHECK_CLOSE)) - return NULL; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "i", &mode)) - { - PyErr_SetString(PyExc_TypeError, "open(mode), with mode(integer)."); - return NULL; - } - - /* opens large object */ - if ((fd = lo_open(self->pgcnx->cnx, self->lo_oid, mode)) < 0) - { - PyErr_SetString(PyExc_IOError, "can't open large object."); - return NULL; - } - self->lo_fd = fd; - - /* no error : returns Py_None */ - Py_INCREF(Py_None); - return Py_None; -} - -/* close large object */ -static PyObject * -pglarge_close(pglargeobject * self, PyObject * args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method close() takes no parameters."); - return NULL; - } - - /* checks validity */ - if (!check_lo(self, CHECK_OPEN)) - return NULL; - - /* closes large object */ - if (lo_close(self->pgcnx->cnx, self->lo_fd)) - { - PyErr_SetString(PyExc_IOError, "error while closing large object fd."); - return NULL; - } - self->lo_fd = -1; - - /* no error : returns Py_None */ - Py_INCREF(Py_None); - return Py_None; -} - -/* reads from large object */ -static PyObject * -pglarge_read(pglargeobject * self, PyObject * args) -{ - int size; - PyObject *buffer; - - /* checks validity */ - if (!check_lo(self, CHECK_OPEN)) - return NULL; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "i", &size)) - { - PyErr_SetString(PyExc_TypeError, "read(size), wih size (integer)."); - return NULL; - } - - if (size <= 0) - { - PyErr_SetString(PyExc_ValueError, "size must be positive."); - return NULL; - } - - /* allocate buffer and runs read */ - buffer = PyString_FromStringAndSize((char *) NULL, size); - - if ((size = lo_read(self->pgcnx->cnx, self->lo_fd, BUF(buffer), size)) < 0) - { - PyErr_SetString(PyExc_IOError, "error while reading."); - Py_XDECREF(buffer); - return NULL; - } - - /* resize buffer and returns it */ - _PyString_Resize(&buffer, size); - return buffer; -} - -/* write to large object */ -static PyObject * -pglarge_write(pglargeobject * self, PyObject * args) -{ - char *buffer; - int size; - - /* checks validity */ - if (!check_lo(self, CHECK_OPEN)) - return NULL; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "s", &buffer)) - { - PyErr_SetString(PyExc_TypeError, - "write(buffer), with buffer (sized string)."); - return NULL; - } - - /* sends query */ - if ((size = lo_write(self->pgcnx->cnx, self->lo_fd, buffer, - strlen(buffer))) < strlen(buffer)) - { - PyErr_SetString(PyExc_IOError, "buffer truncated during write."); - return NULL; - } - - /* no error : returns Py_None */ - Py_INCREF(Py_None); - return Py_None; -} - -/* go to position in large object */ -static PyObject * -pglarge_lseek(pglargeobject * self, PyObject * args) -{ - int ret, offset, whence; - - /* checks validity */ - if (!check_lo(self, CHECK_OPEN)) - return NULL; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "ii", &offset, &whence)) - { - PyErr_SetString(PyExc_TypeError, - "lseek(offset, whence), with offset and whence (integers)."); - return NULL; - } - - /* sends query */ - if ((ret = lo_lseek(self->pgcnx->cnx, self->lo_fd, offset, whence)) == -1) - { - PyErr_SetString(PyExc_IOError, "error while moving cursor."); - return NULL; - } - - /* returns position */ - return PyInt_FromLong(ret); -} - -/* gets large object size */ -static PyObject * -pglarge_size(pglargeobject * self, PyObject * args) -{ - int start, end; - - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method size() takes no parameters."); - return NULL; - } - - /* checks validity */ - if (!check_lo(self, CHECK_OPEN)) - return NULL; - - /* gets current position */ - if ((start = lo_tell(self->pgcnx->cnx, self->lo_fd)) == -1) - { - PyErr_SetString(PyExc_IOError, "error while getting current position."); - return NULL; - } - - /* gets end position */ - if ((end = lo_lseek(self->pgcnx->cnx, self->lo_fd, 0, SEEK_END)) == -1) - { - PyErr_SetString(PyExc_IOError, "error while getting end position."); - return NULL; - } - - /* move back to start position */ - if ((start = lo_lseek(self->pgcnx->cnx,self->lo_fd,start,SEEK_SET)) == -1) - { - PyErr_SetString(PyExc_IOError, - "error while moving back to first position."); - return NULL; - } - - /* returns size */ - return PyInt_FromLong(end); -} - -/* gets large object cursor position */ -static PyObject * -pglarge_tell(pglargeobject * self, PyObject * args) -{ - int start; - - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method tell() takes no parameters."); - return NULL; - } - - /* checks validity */ - if (!check_lo(self, CHECK_OPEN)) - return NULL; - - /* gets current position */ - if ((start = lo_tell(self->pgcnx->cnx, self->lo_fd)) == -1) - { - PyErr_SetString(PyExc_IOError, "error while getting position."); - return NULL; - } - - /* returns size */ - return PyInt_FromLong(start); -} - -/* exports large object as unix file */ -static PyObject * -pglarge_export(pglargeobject * self, PyObject * args) -{ - char *name; - - /* checks validity */ - if (!check_lo(self, CHECK_CLOSE)) - return NULL; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "s", &name)) - { - PyErr_SetString(PyExc_TypeError, - "export(filename), with filename (string)."); - return NULL; - } - - /* runs command */ - if (!lo_export(self->pgcnx->cnx, self->lo_oid, name)) - { - PyErr_SetString(PyExc_IOError, "error while exporting large object."); - return NULL; - } - - Py_INCREF(Py_None); - return Py_None; -} - -/* deletes a large object */ -static PyObject * -pglarge_unlink(pglargeobject * self, PyObject * args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method unlink() takes no parameters."); - return NULL; - } - - /* checks validity */ - if (!check_lo(self, CHECK_CLOSE)) - return NULL; - - /* deletes the object, invalidate it on success */ - if (!lo_unlink(self->pgcnx->cnx, self->lo_oid)) - { - PyErr_SetString(PyExc_IOError, "error while unlinking large object"); - return NULL; - } - self->lo_oid = 0; - - Py_INCREF(Py_None); - return Py_None; -} - -/* large object methods */ -static struct PyMethodDef pglarge_methods[] = { - {"open", (PyCFunction) pglarge_open, 1}, /* opens large object */ - {"close", (PyCFunction) pglarge_close, 1},/* closes large object */ - {"read", (PyCFunction) pglarge_read, 1}, /* reads from large object */ - {"write", (PyCFunction) pglarge_write, 1},/* writes to large object */ - {"seek", (PyCFunction) pglarge_lseek, 1},/* seeks position */ - {"size", (PyCFunction) pglarge_size, 1}, /* gives object size */ - {"tell", (PyCFunction) pglarge_tell, 1}, /* gives position in lobj */ - {"export", (PyCFunction) pglarge_export, 1},/* exports to unix file */ - {"unlink", (PyCFunction) pglarge_unlink, 1},/* deletes a large object */ - {NULL, NULL} /* sentinel */ -}; - -/* get attribute */ -static PyObject * -pglarge_getattr(pglargeobject * self, char *name) -{ - /* list postgreSQL large object fields */ - - /* associated pg connection object */ - if (!strcmp(name, "pgcnx")) - { - if (check_lo(self, 0)) - { - Py_INCREF(self->pgcnx); - return (PyObject *) (self->pgcnx); - } - - Py_INCREF(Py_None); - return Py_None; - } - - /* large object oid */ - if (!strcmp(name, "oid")) - { - if (check_lo(self, 0)) - return PyInt_FromLong(self->lo_oid); - - Py_INCREF(Py_None); - return Py_None; - } - - /* error (status) message */ - if (!strcmp(name, "error")) - return PyString_FromString(PQerrorMessage(self->pgcnx->cnx)); - - /* attributes list */ - if (!strcmp(name, "__members__")) - { - PyObject *list = PyList_New(3); - - if (list) - { - PyList_SetItem(list, 0, PyString_FromString("oid")); - PyList_SetItem(list, 1, PyString_FromString("pgcnx")); - PyList_SetItem(list, 2, PyString_FromString("error")); - } - - return list; - } - - return Py_FindMethod(pglarge_methods, (PyObject *) self, name); -} - -/* object type definition */ -staticforward PyTypeObject PglargeType = { - PyObject_HEAD_INIT(&PyType_Type) - 0, /* ob_size */ - "pglarge", /* tp_name */ - sizeof(pglargeobject), /* tp_basicsize */ - 0, /* tp_itemsize */ - - /* methods */ - (destructor) pglarge_dealloc, /* tp_dealloc */ - 0, /* tp_print */ - (getattrfunc) pglarge_getattr, /* tp_getattr */ - 0, /* tp_setattr */ - 0, /* tp_compare */ - 0, /* tp_repr */ - 0, /* tp_as_number */ - 0, /* tp_as_sequence */ - 0, /* tp_as_mapping */ - 0, /* tp_hash */ -}; - -#endif /* LARGE_OBJECTS */ - - -/* --------------------------------------------------------------------- */ - -/* PG CONNECTION OBJECT IMPLEMENTATION */ - -/* pgobject initialisation (from module) */ - -static PyObject * -pgconnect(pgobject * self, PyObject * args, PyObject * dict) -{ - char *pghost, *pgopt, *pgtty, *pgdbname, *pguser, *pgpasswd; - int pgport; - char port_buffer[20]; - PyObject *temp; - pgobject *npgobj; - PGconn *test_cnx; - - pghost = pgopt = pgtty = pgdbname = pguser = pgpasswd = NULL; - pgport = -1; - - /* parses standard arguments */ - if (!PyArg_ParseTuple(args, "|zzlzzzz", - &pgdbname, &pghost, &pgport, &pgopt, &pgtty, &pguser, &pgpasswd)) - { - PyErr_Clear(); - - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_TypeError, - "connect(dbname, host, port, opt, tty, user, password), " - "with args (opt., strings or None)."); - return NULL; - } - } - - /* looks for keywords arguments */ - if (PyMapping_Check(dict)) - { - /* server host */ - if (PyMapping_HasKeyString(dict, "host")) - { - if ((temp = PyMapping_GetItemString(dict, "host")) != NULL) - { - if (pghost) - { - PyErr_SetString(PyExc_SyntaxError, - "Duplicate argument definition."); - return NULL; - } - - if (!PyString_Check(temp)) - { - PyErr_SetString(PyExc_TypeError, - "'Host' argument must be a string."); - return NULL; - } - - pghost = PyString_AsString(temp); - } - } - - /* server port */ - if (PyMapping_HasKeyString(dict, "port")) - { - if ((temp = PyMapping_GetItemString(dict, "port")) != NULL) - { - if (pgport != -1) - { - PyErr_SetString(PyExc_SyntaxError, - "Duplicate argument definition."); - return NULL; - } - - if (!PyInt_Check(temp)) - { - PyErr_SetString(PyExc_TypeError, - "'Port' argument must be an integer."); - return NULL; - } - - pgport = PyInt_AsLong(temp); - } - } - - /* connection options */ - if (PyMapping_HasKeyString(dict, "opt")) - { - if ((temp = PyMapping_GetItemString(dict, "opt")) != NULL) - { - if (pgtty) - { - PyErr_SetString(PyExc_SyntaxError, - "Duplicate argument definition."); - return NULL; - } - - if (!PyString_Check(temp)) - { - PyErr_SetString(PyExc_TypeError, - "'opt' argument must be a string."); - return NULL; - } - - pgopt = PyString_AsString(temp); - } - } - - /* debug terminal */ - if (PyMapping_HasKeyString(dict, "tty")) - { - if ((temp = PyMapping_GetItemString(dict, "tty")) != NULL) - { - if (pgtty) - { - PyErr_SetString(PyExc_SyntaxError, - "Duplicate argument definition."); - return NULL; - } - - if (!PyString_Check(temp)) - { - PyErr_SetString(PyExc_TypeError, - "'tty' argument must be a string."); - return NULL; - } - - pgtty = PyString_AsString(temp); - } - } - - /* database name */ - if (PyMapping_HasKeyString(dict, "dbname")) - { - if ((temp = PyMapping_GetItemString(dict, "dbname")) != NULL) - { - if (pgdbname) - { - PyErr_SetString(PyExc_SyntaxError, - "Duplicate argument definition."); - return NULL; - } - - if (!PyString_Check(temp)) - { - PyErr_SetString(PyExc_TypeError, - "'dbname' argument must be a string."); - return NULL; - } - - pgdbname = PyString_AsString(temp); - } - } - - /* username */ - if (PyMapping_HasKeyString(dict, "user")) - { - if ((temp = PyMapping_GetItemString(dict, "user")) != NULL) - { - if (pguser) - { - PyErr_SetString(PyExc_SyntaxError, - "Duplicate argument definition."); - - return NULL; - } - - if (!PyString_Check(temp)) - { - PyErr_SetString(PyExc_TypeError, - "'user' argument must be a string."); - - return NULL; - } - - pguser = PyString_AsString(temp); - } - } - - /* password */ - if (PyMapping_HasKeyString(dict, "passwd")) - { - if ((temp = PyMapping_GetItemString(dict, "passwd")) != NULL) - { - if (pgpasswd) - { - PyErr_SetString(PyExc_SyntaxError, - "Duplicate argument definition."); - - return NULL; - } - - if (!PyString_Check(temp)) - { - PyErr_SetString(PyExc_TypeError, - "'passwd' argument must be a string."); - - return NULL; - } - - pgpasswd = PyString_AsString(temp); - } - } - } - -#ifdef DEFAULT_VARS - /* handles defaults variables (for unintialised vars) */ - if ((!pghost) && (pg_default_host != Py_None)) - pghost = PyString_AsString(pg_default_host); - - if ((pgport == -1) && (pg_default_port != Py_None)) - pgport = PyInt_AsLong(pg_default_port); - - if ((!pgopt) && (pg_default_opt != Py_None)) - pgopt = PyString_AsString(pg_default_opt); - - if ((!pgtty) && (pg_default_tty != Py_None)) - pgtty = PyString_AsString(pg_default_tty); - - if ((!pgdbname) && (pg_default_base != Py_None)) - pgdbname = PyString_AsString(pg_default_base); - - if ((!pguser) && (pg_default_user != Py_None)) - pguser = PyString_AsString(pg_default_user); - - if ((!pgpasswd) && (pg_default_passwd != Py_None)) - pgpasswd = PyString_AsString(pg_default_passwd); -#endif /* DEFAULT_VARS */ - - if ((npgobj = PyObject_NEW(pgobject, &PgType)) == NULL) - return NULL; - - if (pgport != -1) - { - bzero(port_buffer, sizeof(port_buffer)); - sprintf(port_buffer, "%d", pgport); - npgobj->cnx = PQsetdbLogin(pghost, port_buffer, pgopt, pgtty, pgdbname, - pguser, pgpasswd); - } - else - npgobj->cnx = PQsetdbLogin(pghost, NULL, pgopt, pgtty, pgdbname, - pguser, pgpasswd); - - if (PQstatus(npgobj->cnx) == CONNECTION_BAD) - { - PyErr_SetString(PGError, PQerrorMessage(npgobj->cnx)); - Py_XDECREF(npgobj); - return NULL; - } - - return (PyObject *) npgobj; -} - -/* pgobject methods */ - -/* destructor */ -static void -pg_dealloc(pgobject * self) -{ - PQfinish(self->cnx); - PyMem_DEL(self); -} - -static void -pg_querydealloc(pgqueryobject * self) -{ - if (self->last_result) - PQclear(self->last_result); - - PyMem_DEL(self); -} - -/* resets connection */ -static PyObject * -pg_reset(pgobject * self, PyObject * args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method reset() takes no parameters."); - return NULL; - } - - /* resets the connection */ - PQreset(self->cnx); - Py_INCREF(Py_None); - return Py_None; -} - -/* list fields names from query result */ -static PyObject * -pg_listfields(pgqueryobject * self, PyObject * args) -{ - int i, n; - char *name; - PyObject *fieldstuple, *str; - - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method listfields() takes no parameters."); - return NULL; - } - - /* builds tuple */ - n = PQnfields(self->last_result); - fieldstuple = PyTuple_New(n); - - for (i = 0; i < n; i++) - { - name = PQfname(self->last_result, i); - str = PyString_FromString(name); - PyTuple_SetItem(fieldstuple, i, str); - } - - return fieldstuple; -} - -/* get field name from last result */ -static PyObject * -pg_fieldname(pgqueryobject * self, PyObject * args) -{ - int i; - char *name; - - /* gets args */ - if (!PyArg_ParseTuple(args, "i", &i)) - { - PyErr_SetString(PyExc_TypeError, - "fieldname(number), with number(integer)."); - return NULL; - } - - /* checks number validity */ - if (i >= PQnfields(self->last_result)) - { - PyErr_SetString(PyExc_ValueError, "invalid field number."); - return NULL; - } - - /* gets fields name and builds object */ - name = PQfname(self->last_result, i); - return PyString_FromString(name); -} - -/* gets fields number from name in last result */ -static PyObject * -pg_fieldnum(pgqueryobject * self, PyObject * args) -{ - char *name; - int num; - - /* gets args */ - if (!PyArg_ParseTuple(args, "s", &name)) - { - PyErr_SetString(PyExc_TypeError, "fieldnum(name), with name (string)."); - return NULL; - } - - /* gets field number */ - if ((num = PQfnumber(self->last_result, name)) == -1) - { - PyErr_SetString(PyExc_ValueError, "Unknown field."); - return NULL; - } - - return PyInt_FromLong(num); -} - -/* retrieves last result */ -static PyObject * -pg_getresult(pgqueryobject * self, PyObject * args) -{ - PyObject *rowtuple, *reslist, *val; - int i, j, m, n, *typ; - - /* checks args (args == NULL for an internal call) */ - if ((args != NULL) && (!PyArg_ParseTuple(args, ""))) - { - PyErr_SetString(PyExc_SyntaxError, - "method getresult() takes no parameters."); - return NULL; - } - - /* stores result in tuple */ - reslist = PyList_New(0); - m = PQntuples(self->last_result); - n = PQnfields(self->last_result); - - if ((typ = malloc(sizeof(int) * n)) == NULL) - { - PyErr_SetString(PyExc_SyntaxError, "memory error in getresult()."); - return NULL; - } - - for (j = 0; j < n; j++) - { - switch (PQftype(self->last_result, j)) - { - case INT2OID: - case INT4OID: - case OIDOID: - typ[j] = 1; - break; - - case FLOAT4OID: - case FLOAT8OID: - typ[j] = 2; - break; - - case CASHOID: - typ[j] = 3; - break; - - default: - typ[j] = 4; - break; - } - } - - for (i = 0; i < m; i++) - { - rowtuple = PyTuple_New(n); - for (j = 0; j < n; j++) - { - char *s = PQgetvalue(self->last_result, i, j); - char cashbuf[64]; - int i; - - switch (typ[j]) - { - case 1: - val = PyInt_FromLong(strtol(s, NULL, 10)); - break; - - case 2: - val = PyFloat_FromDouble(strtod(s, NULL)); - break; - - case 3: /* get rid of the '$' and commas */ - if (*s == '$') /* there's talk of getting rid of it */ - s++; - - for (i = 0; *s; s++) - if (*s != ',') - cashbuf[i++] = *s; - - cashbuf[i] = 0; - val = PyFloat_FromDouble(strtod(cashbuf, NULL)); - break; - - default: - val = PyString_FromString(s); - break; - } - - PyTuple_SetItem(rowtuple, j, val); - } - - PyList_Append(reslist, rowtuple); - Py_XDECREF(rowtuple); - } - - free(typ); - - /* returns list */ - return reslist; -} - -/* retrieves last result as a list of dictionaries*/ -static PyObject * -pg_dictresult(pgqueryobject * self, PyObject * args) -{ - PyObject *dict, *reslist, *val; - int i, j, m, n, *typ; - - /* checks args (args == NULL for an internal call) */ - if ((args != NULL) && (!PyArg_ParseTuple(args, ""))) - { - PyErr_SetString(PyExc_SyntaxError, - "method getresult() takes no parameters."); - return NULL; - } - - /* stores result in list */ - reslist = PyList_New(0); - m = PQntuples(self->last_result); - n = PQnfields(self->last_result); - - if ((typ = malloc(sizeof(int) * n)) == NULL) - { - PyErr_SetString(PyExc_SyntaxError, "memory error in getresult()."); - return NULL; - } - - for (j = 0; j < n; j++) - { - switch (PQftype(self->last_result, j)) - { - case INT2OID: - case INT4OID: - case OIDOID: - typ[j] = 1; - break; - - case FLOAT4OID: - case FLOAT8OID: - typ[j] = 2; - break; - - case CASHOID: - typ[j] = 3; - break; - - default: - typ[j] = 4; - break; - } - } - - for (i = 0; i < m; i++) - { - dict = PyDict_New(); - for (j = 0; j < n; j++) - { - char *s = PQgetvalue(self->last_result, i, j); - char cashbuf[64]; - int i; - - switch (typ[j]) - { - case 1: - val = PyInt_FromLong(strtol(s, NULL, 10)); - break; - - case 2: - val = PyFloat_FromDouble(strtod(s, NULL)); - break; - - case 3: /* get rid of the '$' and commas */ - if (*s == '$') /* there's talk of getting rid of it */ - s++; - - for (i = 0; *s; s++) - if (*s != ',') - cashbuf[i++] = *s; - - cashbuf[i] = 0; - val = PyFloat_FromDouble(strtod(cashbuf, NULL)); - break; - - default: - val = PyString_FromString(s); - break; - } - - PyDict_SetItemString(dict, PQfname(self->last_result, j), val); - } - - PyList_Append(reslist, dict); - Py_XDECREF(dict); - } - - free(typ); - - /* returns list */ - return reslist; -} - -/* getq asynchronous notify */ -static PyObject * -pg_getnotify(pgobject * self, PyObject * args) -{ - PGnotify *notify; - PGresult *result; - PyObject *notify_result, *temp; - - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method getnotify() takes no parameters."); - return NULL; - } - - /* gets notify and builds result */ - /* notifies only come back as result of a query, so I send an empty query */ - result = PQexec(self->cnx, " "); - - if ((notify = PQnotifies(self->cnx)) != NULL) - { - notify_result = PyTuple_New(2); - temp = PyString_FromString(notify->relname); - PyTuple_SetItem(notify_result, 0, temp); - temp = PyInt_FromLong(notify->be_pid); - PyTuple_SetItem(notify_result, 1, temp); - free(notify); - } - else - { - Py_INCREF(Py_None); - notify_result = Py_None; - } - - PQclear(result); - - /* returns result */ - return notify_result; -} - -/* database query */ -static PyObject * -pg_query(pgobject * self, PyObject * args) -{ - char *query; - PGresult *result; - PyObject *rowtuple, *reslist, *str; - pgqueryobject *npgobj; - int i, j, m, n, status; - - /* get query args */ - if (!PyArg_ParseTuple(args, "s", &query)) - { - PyErr_SetString(PyExc_TypeError, "query(sql), with sql (string)."); - return NULL; - } - - /* gets result */ - result = PQexec(self->cnx, query); - - /* checks result validity */ - if (!result) - { - PyErr_SetString(PyExc_ValueError, PQerrorMessage(self->cnx)); - return NULL; - } - - /* checks result status */ - if ((status = PQresultStatus(result)) != PGRES_TUPLES_OK) - { - const char *str; - - PQclear(result); - - switch (status) - { - case PGRES_EMPTY_QUERY: - PyErr_SetString(PyExc_ValueError, "empty query."); - break; - case PGRES_BAD_RESPONSE: - PyErr_SetString(PGError, - "unexpected responsed received from server."); - break; - case PGRES_FATAL_ERROR: - PyErr_SetString(PGError, - "server fatal error. " - "Please report to your db administrator."); - break; - case PGRES_NONFATAL_ERROR: - PyErr_SetString(PGError, "server (non fatal) error."); - break; - case PGRES_COMMAND_OK: /* could be an INSERT */ - if (*(str = PQoidStatus(result)) == 0) /* nope */ - { - Py_INCREF(Py_None); - return Py_None; - } - - /* otherwise, return the oid */ - return PyInt_FromLong(strtol(str, NULL, 10)); - - case PGRES_COPY_OUT: /* no data will be received */ - case PGRES_COPY_IN: - Py_INCREF(Py_None); - return Py_None; - default: - PyErr_SetString(PGError, "internal error: " - "unknown result status."); - break; - } - - return NULL; /* error detected on query */ - } - - if ((npgobj = PyObject_NEW(pgqueryobject, &PgQueryType)) == NULL) - return NULL; - - /* stores result and returns object */ - npgobj->last_result = result; - return (PyObject *) npgobj; -} - -#ifdef DIRECT_ACCESS -/* direct acces function : putline */ -static PyObject * -pg_putline(pgobject * self, PyObject * args) -{ - char *line; - - /* reads args */ - if (!PyArg_ParseTuple(args, "s", &line)) - { - PyErr_SetString(PyExc_TypeError, "putline(line), with line (string)."); - return NULL; - } - - /* sends line to backend */ - PQputline(self->cnx, line); - Py_INCREF(Py_None); - return Py_None; -} - -/* direct access function : getline */ -static PyObject * -pg_getline(pgobject * self, PyObject * args) -{ - char *line; - PyObject *str; - int ret; - - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method getline() takes no parameters."); - return NULL; - } - - /* allocate buffer */ - if ((line = malloc(MAX_BUFFER_SIZE)) == NULL) - { - PyErr_SetString(PyExc_MemoryError, "can't allocate getline buffer"); - return NULL; - } - - /* gets line */ - switch (PQgetline(self->cnx, line, MAX_BUFFER_SIZE)) - { - case 0: - str = PyString_FromString(line); - break; - case 1: - PyErr_SetString(PyExc_MemoryError, "buffer overflow"); - str = NULL; - break; - case EOF: - Py_INCREF(Py_None); - str = Py_None; - break; - } - - free(line); - return str; -} - -/* direct access function : end copy */ -static PyObject * -pg_endcopy(pgobject * self, PyObject * args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method endcopy() takes no parameters."); - return NULL; - } - - /* ends direct copy */ - PQendcopy(self->cnx); - Py_INCREF(Py_None); - return Py_None; -} -#endif /* DIRECT_ACCESS */ - - -static PyObject * -pg_print(pgqueryobject *self, FILE *fp, int flags) -{ - PQprintOpt op; - - memset(&op, 0, sizeof(op)); - op.align = 1; - op.header = 1; - op.fieldSep = "|"; - op.pager = 1; - PQprint(fp, self->last_result, &op); - return 0; -} - -/* insert table */ -static PyObject * -pg_inserttable(pgobject * self, PyObject * args) -{ - PGresult *result; - char *table, *buffer, *temp; - char temp_buffer[256]; - PyObject *list, *sublist, *item; - PyObject *(*getitem) (PyObject *, int); - PyObject *(*getsubitem) (PyObject *, int); - int i, j; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "sO:filter", &table, &list)) - { - PyErr_SetString(PyExc_TypeError, - "tableinsert(table, content), with table (string) " - "and content (list)."); - return NULL; - } - - /* checks list type */ - if (PyTuple_Check(list)) - getitem = PyTuple_GetItem; - else if (PyList_Check(list)) - getitem = PyList_GetItem; - else - { - PyErr_SetString(PyExc_TypeError, - "second arg must be some kind of array."); - return NULL; - } - - /* checks sublists type */ - for (i = 0; sublist = getitem(list, i); i++) - { - if (!PyTuple_Check(sublist) && !PyList_Check(sublist)) - { - PyErr_SetString(PyExc_TypeError, - "second arg must contain some kind of arrays."); - return NULL; - } - } - - /* allocate buffer */ - if (!(buffer = malloc(MAX_BUFFER_SIZE))) - { - PyErr_SetString(PyExc_MemoryError, "can't allocate insert buffer."); - return NULL; - } - - /* starts query */ - sprintf(buffer, "copy %s from stdin", table); - - if (!(result = PQexec(self->cnx, buffer))) - { - free(buffer); - PyErr_SetString(PyExc_ValueError, PQerrorMessage(self->cnx)); - return NULL; - } - - PQclear(result); - - /* feeds table */ - for (i = 0; sublist = getitem(list, i); i++) - { - if (PyTuple_Check(sublist)) - getsubitem = PyTuple_GetItem; - else - getsubitem = PyList_GetItem; - - /* builds insert line */ - buffer[0] = 0; - - for (j = 0; item = getsubitem(sublist, j); j++) - { - /* converts item to string */ - if (PyString_Check(item)) - PyArg_ParseTuple(item, "s", &temp); - else if (PyInt_Check(item)) - { - int k; - - PyArg_ParseTuple(item, "i", &k); - sprintf(temp_buffer, "%d", k); - temp = temp_buffer; - } - else if (PyLong_Check(item)) - { - long k; - - PyArg_ParseTuple(item, "l", &k); - sprintf(temp_buffer, "%ld", k); - temp = temp_buffer; - } - else if (PyFloat_Check(item)) - { - double k; - - PyArg_ParseTuple(item, "d", &k); - sprintf(temp_buffer, "%g", k); - temp = temp_buffer; - } - else - { - free(buffer); - PyErr_SetString(PyExc_ValueError, - "items must be strings, integers, " - "longs or double (real)."); - return NULL; - } - - /* concats buffer */ - if (strlen(buffer)) - strncat(buffer, "\t", MAX_BUFFER_SIZE - strlen(buffer)); - - fprintf(stderr, "Buffer: '%s', Temp: '%s'\n", buffer, temp); - strncat(buffer, temp, MAX_BUFFER_SIZE - strlen(buffer)); - } - - strncat(buffer, "\n", MAX_BUFFER_SIZE - strlen(buffer)); - - /* sends data */ - PQputline(self->cnx, buffer); - } - - /* ends query */ - PQputline(self->cnx, ".\n"); - PQendcopy(self->cnx); - free(buffer); - - /* no error : returns nothing */ - Py_INCREF(Py_None); - return Py_None; -} - -/* connection object methods */ -static struct PyMethodDef pgobj_methods[] = { - {"query", (PyCFunction) pg_query, 1}, /* query method */ - {"reset", (PyCFunction) pg_reset, 1}, /* connection reset */ - {"getnotify", (PyCFunction) pg_getnotify, 1}, /* checks for notify */ - {"inserttable", (PyCFunction) pg_inserttable, 1}, /* table insert */ - -#ifdef DIRECT_ACCESS - {"putline", (PyCFunction) pg_putline, 1}, /* direct access: putline */ - {"getline", (PyCFunction) pg_getline, 1}, /* direct access: getline */ - {"endcopy", (PyCFunction) pg_endcopy, 1}, /* direct access: endcopy */ -#endif /* DIRECT_ACCESS */ - -#ifdef LARGE_OBJECTS - {"locreate", (PyCFunction) pg_locreate, 1}, /* creates large object */ - {"getlo", (PyCFunction) pg_getlo, 1}, /* get lo from oid */ - {"loimport", (PyCFunction) pg_loimport, 1}, /* imports lo from file */ -#endif /* LARGE_OBJECTS */ - - {NULL, NULL} /* sentinel */ -}; - -/* get attribute */ -static PyObject * -pg_getattr(pgobject * self, char *name) -{ - /* list postgreSQL connection fields */ - - /* postmaster host */ - if (!strcmp(name, "host")) - return PyString_FromString(PQhost(self->cnx)); - - /* postmaster port */ - if (!strcmp(name, "port")) - return PyInt_FromLong(atol(PQport(self->cnx))); - - /* selected database */ - if (!strcmp(name, "db")) - return PyString_FromString(PQdb(self->cnx)); - - /* selected options */ - if (!strcmp(name, "options")) - return PyString_FromString(PQoptions(self->cnx)); - - /* selected postgres tty */ - if (!strcmp(name, "tty")) - return PyString_FromString(PQtty(self->cnx)); - - /* error (status) message */ - if (!strcmp(name, "error")) - return PyString_FromString(PQerrorMessage(self->cnx)); - - /* connection status : 1 - OK, 0 - BAD */ - if (!strcmp(name, "status")) - return PyInt_FromLong(PQstatus(self->cnx) == CONNECTION_OK ? 1 : 0); - - /* provided user name */ - if (!strcmp(name, "user")) - return PyString_FromString("Deprecated facility"); - /* return PyString_FromString(fe_getauthname("<unknown user>")); */ - - /* attributes list */ - if (!strcmp(name, "__members__")) - { - PyObject *list = PyList_New(8); - - if (list) - { - PyList_SetItem(list, 0, PyString_FromString("host")); - PyList_SetItem(list, 1, PyString_FromString("port")); - PyList_SetItem(list, 2, PyString_FromString("db")); - PyList_SetItem(list, 3, PyString_FromString("options")); - PyList_SetItem(list, 4, PyString_FromString("tty")); - PyList_SetItem(list, 5, PyString_FromString("error")); - PyList_SetItem(list, 6, PyString_FromString("status")); - PyList_SetItem(list, 7, PyString_FromString("user")); - } - - return list; - } - - return Py_FindMethod(pgobj_methods, (PyObject *) self, name); -} - -/* object type definition */ -staticforward PyTypeObject PgType = { - PyObject_HEAD_INIT(&PyType_Type) - 0, /* ob_size */ - "pgobject", /* tp_name */ - sizeof(pgobject), /* tp_basicsize */ - 0, /* tp_itemsize */ - /* methods */ - (destructor) pg_dealloc, /* tp_dealloc */ - 0, /* tp_print */ - (getattrfunc) pg_getattr, /* tp_getattr */ - 0, /* tp_setattr */ - 0, /* tp_compare */ - 0, /* tp_repr */ - 0, /* tp_as_number */ - 0, /* tp_as_sequence */ - 0, /* tp_as_mapping */ - 0, /* tp_hash */ -}; - - -/* query object methods */ -static struct PyMethodDef pgquery_methods[] = { - {"getresult", (PyCFunction) pg_getresult, 1}, /* get last result */ - {"dictresult", (PyCFunction) pg_dictresult, 1}, /* get result as dict*/ - {"fieldname", (PyCFunction) pg_fieldname, 1}, /* get field name */ - {"fieldnum", (PyCFunction) pg_fieldnum, 1}, /* get field number */ - {"listfields", (PyCFunction) pg_listfields, 1}, /* list fields names */ - {NULL, NULL} /* sentinel */ -}; - -static PyObject * -pg_querygetattr(pgqueryobject * self, char *name) -{ - /* list postgreSQL connection fields */ - return Py_FindMethod(pgquery_methods, (PyObject *) self, name); -} - -/* query type definition */ -staticforward PyTypeObject PgQueryType = { - PyObject_HEAD_INIT(&PyType_Type) - 0, /* ob_size */ - "pgqueryobject", /* tp_name */ - sizeof(pgqueryobject), /* tp_basicsize */ - 0, /* tp_itemsize */ - /* methods */ - (destructor) pg_querydealloc,/* tp_dealloc */ - (printfunc) pg_print, /* tp_print */ - (getattrfunc) pg_querygetattr,/* tp_getattr */ - 0, /* tp_setattr */ - 0, /* tp_compare */ - 0, /* tp_repr */ - 0, /* tp_as_number */ - 0, /* tp_as_sequence */ - 0, /* tp_as_mapping */ - 0, /* tp_hash */ -}; - - - -/* --------------------------------------------------------------------- */ - -/* MODULE FUNCTIONS */ - -#ifdef DEFAULT_VARS - -/* gets default host */ -PyObject * -pggetdefhost(PyObject *self, PyObject *args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method get_defhost() takes no parameter."); - return NULL; - } - - Py_XINCREF(pg_default_host); - return pg_default_host; -} - -/* sets default host */ -PyObject * -pgsetdefhost(PyObject * self, PyObject *args) -{ - char *temp = NULL; - PyObject *old; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "z", &temp)) - { - PyErr_SetString(PyExc_TypeError, - "set_defhost(name), with name (string/None)."); - return NULL; - } - - /* adjusts value */ - old = pg_default_host; - - if (temp) - pg_default_host = PyString_FromString(temp); - else - { - Py_INCREF(Py_None); - pg_default_host = Py_None; - } - - return old; -} - -/* gets default base */ -PyObject * -pggetdefbase(PyObject * self, PyObject *args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method get_defbase() takes no parameter."); - return NULL; - } - - Py_XINCREF(pg_default_base); - return pg_default_base; -} - -/* sets default base */ -PyObject * -pgsetdefbase(PyObject * self, PyObject *args) -{ - char *temp = NULL; - PyObject *old; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "z", &temp)) - { - PyErr_SetString(PyExc_TypeError, - "set_defbase(name), with name (string/None)."); - return NULL; - } - - /* adjusts value */ - old = pg_default_base; - - if (temp) - pg_default_base = PyString_FromString(temp); - else - { - Py_INCREF(Py_None); - pg_default_base = Py_None; - } - - return old; -} - -/* gets default options */ -PyObject * -pggetdefopt(PyObject * self, PyObject *args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method get_defopt() takes no parameter."); - return NULL; - } - - Py_XINCREF(pg_default_opt); - return pg_default_opt; -} - -/* sets default opt */ -PyObject * -pgsetdefopt(PyObject * self, PyObject *args) -{ - char *temp = NULL; - PyObject *old; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "z", &temp)) - { - PyErr_SetString(PyExc_TypeError, - "set_defopt(name), with name (string/None)."); - return NULL; - } - - /* adjusts value */ - old = pg_default_opt; - - if (temp) - pg_default_opt = PyString_FromString(temp); - else - { - Py_INCREF(Py_None); - pg_default_opt = Py_None; - } - - return old; -} - -/* gets default tty */ -PyObject * -pggetdeftty(PyObject * self, PyObject *args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method get_deftty() takes no parameter."); - return NULL; - } - - Py_XINCREF(pg_default_tty); - return pg_default_tty; -} - -/* sets default tty */ -PyObject * -pgsetdeftty(PyObject * self, PyObject *args) -{ - char *temp = NULL; - PyObject *old; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "z", &temp)) - { - PyErr_SetString(PyExc_TypeError, - "set_deftty(name), with name (string/None)."); - return NULL; - } - - /* adjusts value */ - old = pg_default_tty; - - if (temp) - pg_default_tty = PyString_FromString(temp); - else - { - Py_INCREF(Py_None); - pg_default_tty = Py_None; - } - - return old; -} - -/* gets default username */ -PyObject * -pggetdefuser(PyObject * self, PyObject *args) -{ - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method get_defuser() takes no parameter."); - - return NULL; - } - - Py_XINCREF(pg_default_user); - return pg_default_user; -} - -/* sets default username */ -PyObject * -pgsetdefuser(PyObject * self, PyObject *args) -{ - char *temp = NULL; - PyObject *old; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "z", &temp)) - { - PyErr_SetString(PyExc_TypeError, - "set_defuser(name), with name (string/None)."); - return NULL; - } - - /* adjusts value */ - old = pg_default_user; - - if (temp) - pg_default_user = PyString_FromString(temp); - else - { - Py_INCREF(Py_None); - pg_default_user = Py_None; - } - - return old; -} - -/* sets default password */ -PyObject * -pgsetdefpasswd(PyObject * self, PyObject *args) -{ - char *temp = NULL; - PyObject *old; - - /* gets arguments */ - if (!PyArg_ParseTuple(args, "z", &temp)) - { - PyErr_SetString(PyExc_TypeError, - "set_defpasswd(password), with password (string/ -None)."); - return NULL; - } - - /* adjusts value */ - old = pg_default_passwd; - - if (temp) - pg_default_passwd = PyString_FromString(temp); - else - { - Py_INCREF(Py_None); - pg_default_passwd = Py_None; - } - - return old; -} - -/* gets default port */ -PyObject * -pggetdefport(PyObject * self, PyObject *args) -{ - char *temp; - - /* checks args */ - if (!PyArg_ParseTuple(args, "")) - { - PyErr_SetString(PyExc_SyntaxError, - "method get_defport() takes no parameter."); - return NULL; - } - - Py_XINCREF(pg_default_port); - return pg_default_port; -} - -/* sets default port */ -PyObject * -pgsetdefport(PyObject * self, PyObject *args) -{ - long int port = -2; - char buffer[64], *temp; - PyObject *old; - - /* gets arguments */ - if ((!PyArg_ParseTuple(args, "l", &port)) || (port < -1)) - { - PyErr_SetString(PyExc_TypeError, "set_defport(port), with port " - "(positive integer/-1)."); - return NULL; - } - - /* adjusts value */ - old = pg_default_port; - - if (port != -1) - pg_default_port = PyLong_FromLong(port); - else - { - Py_INCREF(Py_None); - pg_default_port = Py_None; - } - - return old; -} - -#endif /* DEFAULT_VARS */ - -/* List of functions defined in the module */ - -static struct PyMethodDef pg_methods[] = { - {"connect", (PyCFunction) pgconnect, 3},/* connect to a postgres database */ - {"__VERSION__", (PyCFunction) pgconnect, 3}, - -#ifdef DEFAULT_VARS - {"get_defhost", pggetdefhost, 1}, /* gets default host */ - {"set_defhost", pgsetdefhost, 1}, /* sets default host */ - {"get_defbase", pggetdefbase, 1}, /* gets default base */ - {"set_defbase", pgsetdefbase, 1}, /* sets default base */ - {"get_defopt", pggetdefopt, 1}, /* gets default options */ - {"set_defopt", pgsetdefopt, 1}, /* sets default options */ - {"get_deftty", pggetdeftty, 1}, /* gets default debug tty */ - {"set_deftty", pgsetdeftty, 1}, /* sets default debug tty */ - {"get_defport", pggetdefport, 1}, /* gets default port */ - {"set_defport", pgsetdefport, 1}, /* sets default port */ - {"set_defuser", pgsetdefuser, 1}, /* sets default user */ - {"set_defpasswd", pgsetdefpasswd, 1}, /* sets default passwd */ -#endif /* DEFAULT_VARS */ - {NULL, NULL} /* sentinel */ -}; - -static char pg__doc__[] = "Python interface to PostgreSQL DB"; - -/* Initialization function for the module */ -void -init_pg(void) -{ - PyObject *mod, *dict, *v; - - /* Create the module and add the functions */ - mod = Py_InitModule4("_pg", pg_methods, pg__doc__, NULL, PYTHON_API_VERSION); - dict = PyModule_GetDict(mod); - - /* Add some symbolic constants to the module */ - PGError = PyString_FromString("pg.error"); - PyDict_SetItemString(dict, "error", PGError); - - /* Make the version available */ - v = PyString_FromString(PyPgVersion); - PyDict_SetItemString(dict, "version", v); - PyDict_SetItemString(dict, "__version__", v); - Py_DECREF(v); - -#ifdef LARGE_OBJECTS - /* create mode for large objects */ - PyDict_SetItemString(dict, "INV_READ", PyInt_FromLong(INV_READ)); - PyDict_SetItemString(dict, "INV_WRITE", PyInt_FromLong(INV_WRITE)); - - /* position flags for lo_lseek */ - PyDict_SetItemString(dict, "SEEK_SET", PyInt_FromLong(SEEK_SET)); - PyDict_SetItemString(dict, "SEEK_CUR", PyInt_FromLong(SEEK_CUR)); - PyDict_SetItemString(dict, "SEEK_END", PyInt_FromLong(SEEK_END)); -#endif /* LARGE_OBJECTS */ - -#ifdef DEFAULT_VARS - /* prepares default values */ - Py_INCREF(Py_None); pg_default_host = Py_None; - Py_INCREF(Py_None); pg_default_base = Py_None; - Py_INCREF(Py_None); pg_default_opt = Py_None; - Py_INCREF(Py_None); pg_default_port = Py_None; - Py_INCREF(Py_None); pg_default_tty = Py_None; - Py_INCREF(Py_None); pg_default_user = Py_None; - Py_INCREF(Py_None); pg_default_passwd = Py_None; -#endif /* DEFAULT_VARS */ - - /* Check for errors */ - if (PyErr_Occurred()) - Py_FatalError("can't initialize module _pg"); -} diff --git a/src/interfaces/python/pgtools.py b/src/interfaces/python/pgtools.py deleted file mode 100755 index 5355c584d72..00000000000 --- a/src/interfaces/python/pgtools.py +++ /dev/null @@ -1,48 +0,0 @@ -#! /usr/local/bin/python -# pgtools.py - valuable functions for PostGreSQL tutorial -# written 1995 by Pascal ANDRE - -import sys - -# number of rows -scr_size = 24 - -# waits for a key -def wait_key(): - print "Press <enter>" - sys.stdin.read(1) - -# displays a table for a select query result -def display(fields, result): - print result - # gets cols width - fmt = [] - sep = '+' - head = '|' - for i in range(0, len(fields)): - max = len(fields[i]) - for j in range(0, len(result)): - if i < len(result[j]): - if len(result[j][i]) > max: - max = len(result[j][i]) - fmt.append(" %%%ds |" % max) - for j in range(0, max): - sep = sep + '-' - sep = sep + '--+' - for i in range(0, len(fields)): - head = head + fmt[i] % fields[i] - print sep + '\n' + head + '\n' + sep - pos = 6 - for i in range(0, len(result)): - str = '|' - for j in range(0, len(result[i])): - str = str + fmt[j] % result[i][j] - print str - pos = pos + 1 - if pos == scr_size: - print sep - wait_key() - print sep + '\n' + head + '\n' + sep - pos = 6 - print sep - wait_key() diff --git a/src/interfaces/python/syscat.py b/src/interfaces/python/syscat.py deleted file mode 100755 index bf27bd75fe5..00000000000 --- a/src/interfaces/python/syscat.py +++ /dev/null @@ -1,133 +0,0 @@ -# syscat.py - parses some system catalogs -# inspired from the PostgreSQL tutorial -# adapted to Python 1995 by Pascal ANDRE - -print "____________________________________________________________________" -print -print "MODULE SYSCAT.PY : PARSES SOME POSTGRESQL SYSTEM CATALOGS" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = syscat.connect(...)" -print "then start the demo with: syscat.demo(cnx)" -print -print "Some results may be empty, depending on your base status." -print -print "____________________________________________________________________" -print - -from pgext import * -from pgtools import * - -# 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 " \ - "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 " \ - "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 a.aggname, t.typname " \ - "from pg_aggregate a, pg_type t " \ - "where a.aggbasetype = t.oid order by aggname, 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 diff --git a/src/interfaces/python/tutorial/advanced.py b/src/interfaces/python/tutorial/advanced.py deleted file mode 100755 index 758525b5216..00000000000 --- a/src/interfaces/python/tutorial/advanced.py +++ /dev/null @@ -1,214 +0,0 @@ -#! /usr/local/bin/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 "__________________________________________________________________" -print "MODULE ADVANCED.PY : ADVANCED POSTGRES SQL COMMANDS TUTORIAL" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = advanced.connect(...)" -print "then start the demo with: advanced.demo(cnx)" -print "__________________________________________________________________" - -from pgtools import * -from pgext import * - -# 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 char2" - print ") INHERITS (cities)" - pgcnx.query("CREATE TABLE cities (" \ - "name text," \ - "population float8," \ - "altitude int)") - pgcnx.query("CREATE TABLE capitals (" \ - "state char2) 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" - q = pgcnx.query("SELECT * FROM cities") - display(q.listfields(), q.getresult()) - print "SELECT * FROM capitals" - q = pgcnx.query("SELECT * FROM capitals") - display(q.listfields(), q.getresult()) - 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;" - q = pgcnx.query("SELECT name, altitude " \ - "FROM cities " \ - "WHERE altitude > 500") - display(q.listfields(), q.getresult()) - 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" - q = pgcnx.query("SELECT c.name, c.altitude " \ - "FROM cities* c " \ - "WHERE c.altitude > 500") - display(q.listfields(), q.getresult()) - -# time travel features -def time_travel(pgcnx): - print "-----------------------------" - print "-- Time Travel:" - print "-- this feature allows you to run historical queries. " - print "-----------------------------" - print - print "-- first, let's make some changes to the cities table (suppose" - print "-- Mariposa's population grows 10% this year)" - print - print "UPDATE cities" - print "SET population = population * 1.1" - print "WHERE name = 'Mariposa';" - pgcnx.query("UPDATE cities " \ - "SET population = population * 1.1" \ - "WHERE name = 'Mariposa'") - wait_key() - print - print "-- the default time is the current time ('now'):" - print - print "SELECT * FROM cities WHERE name = 'Mariposa';" - q = pgcnx.query("SELECT * FROM cities WHERE name = 'Mariposa'") - display(q.listfields(), q.getresult()) - print - print "-- we can also retrieve the population of Mariposa ever has. " - print "-- ('epoch' is the earliest time representable by the system)" - print - print "SELECT name, population" - print "FROM cities['epoch', 'now'] -- can be abbreviated to cities[,]" - print "WHERE name = 'Mariposa';" - q = pgcnx.query("SELECT name, population " - "FROM cities['epoch', 'now'] " - "WHERE name = 'Mariposa'") - display(q.listfields(), q.getresult()) - -# 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 " schedule char16[][]" - print ")" - pgcnx.query("CREATE TABLE sal_emp (" \ - "name text," \ - "pay_by_quarter int4[]," \ - "schedule char16[][])") - 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 " '{{\"meeting\", \"lunch\"}, {}}')" - print - print "INSERT INTO sal_emp VALUES (" - print " 'Carol'," - print " '{20000,25000,25000,25000}'," - print " '{{\"talk\", \"consult\"}, {\"meeting\"}}')" - print - pgcnx.query("INSERT INTO sal_emp VALUES (" \ - "'Bill', '{10000,10000,10000,10000}'," \ - "'{{\"meeting\", \"lunch\"}, {}}')") - pgcnx.query("INSERT INTO sal_emp VALUES (" \ - "'Carol', '{20000,25000,25000,25000}'," \ - "'{{\"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 - q = pgcnx.query("SELECT name FROM sal_emp WHERE " \ - "sal_emp.pay_by_quarter[1] <> sal_emp.pay_by_quarter[2]") - display(q.listfields(), q.getresult()) - print - print "-- retrieve third quarter pay of all employees" - print - print "SELECT sal_emp.pay_by_quarter[3] FROM sal_emp" - print - q = pgcnx.query("SELECT sal_emp.pay_by_quarter[3] FROM sal_emp") - display(q.listfields(), q.getresult()) - print - print "-- select subarrays" - print - print "SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " - print " sal_emp.name = 'Bill'" - q = pgcnx.query("SELECT sal_emp.schedule[1:2][1:1] FROM sal_emp WHERE " \ - "sal_emp.name = 'Bill'") - display(q.listfields(), q.getresult()) - -# 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) - time_travel(pgcnx) - array_demo(pgcnx) - demo_cleanup(pgcnx) diff --git a/src/interfaces/python/tutorial/advanced.pyc b/src/interfaces/python/tutorial/advanced.pyc Binary files differdeleted file mode 100644 index 91bdec9430f..00000000000 --- a/src/interfaces/python/tutorial/advanced.pyc +++ /dev/null diff --git a/src/interfaces/python/tutorial/basics.py b/src/interfaces/python/tutorial/basics.py deleted file mode 100755 index bd47611488e..00000000000 --- a/src/interfaces/python/tutorial/basics.py +++ /dev/null @@ -1,298 +0,0 @@ -#! /usr/local/bin/python -# basics.py - basic SQL commands tutorial -# inspired from the PostgreSQL tutorial -# adapted to Python 1995 by Pascal ANDRE - -print "__________________________________________________________________" -print "MODULE BASICS.PY : BASIC SQL COMMANDS TUTORIAL" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = basics.connect(...)" -print "then start the demo with: basics.demo(cnx)" -print "__________________________________________________________________" - -from pgext import * -from pgtools import * - -# 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 "But that can be easily reformated to :" - display(q.listfields(), q.getresult()) - 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 :" - q = pgcnx.query("SELECT city, (temp_hi+temp_lo)/2 " \ - "AS temp_avg, date FROM weather") - display(q.listfields(), q.getresult()) - 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" - q = pgcnx.query("SELECT * FROM weather WHERE city = 'San Francisco'" \ - " AND prcp > 0.0") - display(q.listfields(), q.getresult()) - 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;" - q = pgcnx.query("SELECT DISTINCT city FROM weather ORDER BY city") - display(q.listfields(), q.getresult()) - -# 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 temp " - print "FROM weather" - print "WHERE city = 'San Francisco' " - print " and prcp > 0.0" - pgcnx.query("SELECT * INTO TABLE temp FROM weather " \ - "WHERE city = 'San Francisco' and prcp > 0.0") - print "Fills the table temp, that can be listed with :" - print "SELECT * from temp" - q = pgcnx.query("SELECT * from temp") - display(q.listfields(), q.getresult()) - -# 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;" - q = pgcnx.query("SELECT max(temp_lo) FROM weather") - display(q.listfields(), q.getresult()) - print - print "-- Aggregate with GROUP BY" - print "SELECT city, max(temp_lo)" - print "FROM weather " - print "GROUP BY city;" - q = pgcnx.query( "SELECT city, max(temp_lo)" \ - "FROM weather GROUP BY city") - display(q.listfields(), q.getresult()) - -# 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 - q = 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") - display(q.listfields(), q.getresult()) - 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 - q = pgcnx.query("SELECT city, location, prcp, date FROM weather, cities"\ - " WHERE name = city") - display(q.listfields(), q.getresult()) - 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 - q = pgcnx.query("SELECT w.city, c.location, w.prcp, w.date " \ - "FROM weather w, cities c WHERE c.name = w.city") - display(q.listfields(), q.getresult()) - -# 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" - q = pgcnx.query("SELECT * from weather") - display(q.listfields(), q.getresult()) - -# 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 - q = pgcnx.query("SELECT * from weather") - display(q.listfields(), q.getresult()) - 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" - q = pgcnx.query("SELECT * from weather") - display(q.listfields(), q.getresult()) - -# 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, temp" - pgcnx.query("DROP TABLE weather, cities, temp") - -# 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) diff --git a/src/interfaces/python/tutorial/func.py b/src/interfaces/python/tutorial/func.py deleted file mode 100755 index 5123d26cd8a..00000000000 --- a/src/interfaces/python/tutorial/func.py +++ /dev/null @@ -1,201 +0,0 @@ -# func.py - demonstrate the use of SQL functions -# inspired from the PostgreSQL tutorial -# adapted to Python 1995 by Pascal ANDRE - -print "__________________________________________________________________" -print "MODULE FUNC.PY : SQL FUNCTION DEFINITION TUTORIAL" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = func.connect(...)" -print "then start the demo with: func.demo(cnx)" -print "__________________________________________________________________" - -from pgtools import * -from pgext import * - -# 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" - q = pgcnx.query("SELECT one() AS answer") - display(q.listfields(), q.getresult()) - 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" - q = pgcnx.query("SELECT add_em(1, 2) AS answer") - display(q.listfields(), q.getresult()) - -# 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 char16" - print ")" - pgcnx.query("CREATE TABLE EMP (" \ - "name text," \ - "salary int4," \ - "age int4," \ - "dept char16)") - 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'" - q = pgcnx.query("SELECT name, double_salary(EMP) AS dream " \ - "FROM EMP WHERE EMP.dept = 'toy'") - display(q.listfields(), q.getresult()) - 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\'::char16 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\\\'::char16 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" - q = pgcnx.query("SELECT name(new_emp()) AS nobody") - display(q.listfields(), q.getresult()) - 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" - q = pgcnx.query("SELECT name(high_pay()) AS overpaid") - display(q.listfields(), q.getresult()) - -# 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" - q = pgcnx.query("SELECT * FROM EMP") - display(q.listfields(), q.getresult()) - 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()" - q = pgcnx.query("SELECT clean_EMP()") - display(q.listfields(), q.getresult()) - print - print "SELECT * FROM EMP" - q = pgcnx.query("SELECT * FROM EMP") - display(q.listfields(), q.getresult()) - -# 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" - 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") - -# main demo function -def demo(pgcnx): - base_func(pgcnx) - comp_func(pgcnx) - mult_func(pgcnx) - demo_cleanup(pgcnx) diff --git a/src/interfaces/python/tutorial/func.pyc b/src/interfaces/python/tutorial/func.pyc Binary files differdeleted file mode 100644 index 246736b008a..00000000000 --- a/src/interfaces/python/tutorial/func.pyc +++ /dev/null diff --git a/src/interfaces/python/tutorial/pgtools.py b/src/interfaces/python/tutorial/pgtools.py deleted file mode 100755 index 738d221c1c5..00000000000 --- a/src/interfaces/python/tutorial/pgtools.py +++ /dev/null @@ -1,47 +0,0 @@ -#! /usr/local/bin/python -# pgtools.py - valuable functions for PostGreSQL tutorial -# written 1995 by Pascal ANDRE - -import sys - -# number of rows -scr_size = 24 - -# waits for a key -def wait_key(): - print "Press <enter>" - sys.stdin.read(1) - -# displays a table for a select query result -def display(fields, result): - # gets cols width - fmt = [] - sep = '+' - head = '|' - for i in range(0, len(fields)): - max = len(fields[i]) - for j in range(0, len(result)): - if i < len(result[j]): - if len(result[j][i]) > max: - max = len(result[j][i]) - fmt.append(" %%%ds |" % max) - for j in range(0, max): - sep = sep + '-' - sep = sep + '--+' - for i in range(0, len(fields)): - head = head + fmt[i] % fields[i] - print sep + '\n' + head + '\n' + sep - pos = 6 - for i in range(0, len(result)): - str = '|' - for j in range(0, len(result[i])): - str = str + fmt[j] % result[i][j] - print str - pos = pos + 1 - if pos == scr_size: - print sep - wait_key() - print sep + '\n' + head + '\n' + sep - pos = 6 - print sep - wait_key() diff --git a/src/interfaces/python/tutorial/pgtools.pyc b/src/interfaces/python/tutorial/pgtools.pyc Binary files differdeleted file mode 100644 index 50ff796df7b..00000000000 --- a/src/interfaces/python/tutorial/pgtools.pyc +++ /dev/null diff --git a/src/interfaces/python/tutorial/syscat.py b/src/interfaces/python/tutorial/syscat.py deleted file mode 100755 index 060b595d07a..00000000000 --- a/src/interfaces/python/tutorial/syscat.py +++ /dev/null @@ -1,141 +0,0 @@ -# syscat.py - parses some system catalogs -# inspired from the PostgreSQL tutorial -# adapted to Python 1995 by Pascal ANDRE - -print "____________________________________________________________________" -print -print "MODULE SYSCAT.PY : PARSES SOME POSTGRESQL SYSTEM CATALOGS" -print -print "This module is designed for being imported from python prompt" -print -print "In order to run the samples included here, first create a connection" -print "using : cnx = syscat.connect(...)" -print "then start the demo with: syscat.demo(cnx)" -print -print "Some results may be empty, depending on your base status." -print -print "If you want to adjust the display to your screen size (rows), you" -print "can type: syscat.src_size = [rows]" -print "____________________________________________________________________" -print - -from pgext import * -from pgtools import * - -# 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 " \ - "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 " \ - "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 a.aggname, t.typname " \ - "from pg_aggregate a, pg_type t " \ - "where a.aggbasetype = t.oid order by aggname, 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): - print "Listing simple indices ..." - temp = list_simple_ind(pgcnx) - display(temp.listfields(), temp.getresult()) - print "Listing all attributes ..." - temp = list_all_attr(pgcnx) - display(temp.listfields(), temp.getresult()) - print "Listing all user-defined base types ..." - temp = list_user_base_type(pgcnx) - display(temp.listfields(), temp.getresult()) - print "Listing all left-unary operators defined ..." - temp = list_left_unary_operator(pgcnx) - display(temp.listfields(), temp.getresult()) - print "Listing all right-unary operators defined ..." - temp = list_right_unary_operator(pgcnx) - display(temp.listfields(), temp.getresult()) - print "Listing all binary operators ..." - temp = list_binary_operator(pgcnx) - display(temp.listfields(), temp.getresult()) - print "Listing C external function linked ..." - temp = list_lang_func(pgcnx, 'C') - display(temp.listfields(), temp.getresult()) - print "Listing C internal functions ..." - temp = list_lang_func(pgcnx, 'internal') - display(temp.listfields(), temp.getresult()) - print "Listing SQL functions defined ..." - temp = list_lang_func(pgcnx, 'sql') - display(temp.listfields(), temp.getresult()) - print "Listing 'aggregate functions' ..." - temp = list_agg_func(pgcnx) - display(temp.listfields(), temp.getresult()) - print "Listing 'operator classes' ..." - temp = list_op_class(pgcnx) - display(temp.listfields(), temp.getresult()) diff --git a/src/interfaces/python/tutorial/syscat.pyc b/src/interfaces/python/tutorial/syscat.pyc Binary files differdeleted file mode 100644 index 6d477761cf5..00000000000 --- a/src/interfaces/python/tutorial/syscat.pyc +++ /dev/null |