SQL - Procedural Programming ============================= Overview --------- - Queries and expressions in SQL are ideal for large set-at-a-time operations: - Operations that need to search the whole relation to answer a complex combination of tuples. - Using SQL allows database to use the query optimizer to find the best query execution plan for the query. - However, SQL is not efficient for some programming tasks: - Sequence of code: A block of queries that must be executed in an order - Conditionals: if/then/else statements (some simple ones can be handled, but cannot be combined with sequence of code - Loops - Store values in and use values from program variables - Error handling Example Program --------------- - We will see an example program that is hard to write using an SQL query, but trivial with a procedural language. - Find the top 3 most frequent y for each x in table R(x,y,z). - Not very easy! - However, suppose we have a query: :: SELECT x,y, count(*) num FROM R GROUP BY x,y ORDER BY x,y, num; Now, we can write a simple program to compute this as follows: :: Algorithm Top 3 y for each x: Run query: SELECT x,y, count(*) num FROM R GROUP BY x,y ORDER BY x,y, num; In a loop read each tuple such that: if a new x is found: read the next three y values (error handling if less than three y values are found) skip remaining x values Procedural SQL ---------------- - To enable the use of SQL for costly queries, while making it possible to write code/procedures on top of it, databases support a number of options. - The options belong in two main options: - Server side - Client side Server side ~~~~~~~~~~~~~ - Languages make it possible to define procedures, functions and triggers - These programs are compiled and stored within the database server - They can also be called by queries Client side ~~~~~~~~~~~~ - Languages allow programs to integrate querying of the database with a procedural language - Coding in a host language with db hooks (C, C++, Java, Python, etc.) using the data structures of these languages - Coding in frameworks with their own data models (Java, Python, etc) with similar db hooks as in above. Programming with SQL ----------------------- - All programming paradigms support: - Methods to execute queries and update statements Executing any SQL statement and catching the outcome - Interpret errors returned if any - Input values from variables into queries, output query results into variables - Loop over query results (for queries returning multiple tuples) - Raise exceptions (which results in rollback of transactions) - Store and reuse queries in the shape of cursors - Starting and committing transactions - Client side programs also support: - Opening/closing connections, allocating/releasing database resources for queries - The actual syntax of the methods for doing this vary, but the principle is the same. Server side language examples ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - Generally database specific - Postgresql: - pl/pgsql: generic procedural language for postgresql - pl/pyhton: procedural language that is an extension of python (also see pl/tcl, pl/perl) Client side language examples ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ - Database specific extensions of host languages, for example - libpq: C library for postgresql, uses library calls for specific to postgresql - OCCI: Oracle library for C Write code in C with hooks to the database in the form of functions and libraries, and compile using C compiler - ECPG: embedded programming in SQL, based on the embedded programming standard with a postgresql specific pre-compiler, an the standard C compiler - Standard libraries for connecting to the database - JDBC is a Java standard library, all databases implement the same functions (some non-standard data types will be different) To compile code with JDBC functions, the specific adapter (library) for a database must be used. - No specific standard for libraries based on C, but ODBC is a non-language specific standard, .NET tries to achieve the same - Python DB-API is a database independent framework for Python Similar to Java, a module for each database management system is needed. The postgresql module is called psycopg2. Frameworks ----------- - Frameworks are based on specific design principles for developing database backed applications - Examples: - Object-relational-mapping used by Rails, Hibernate, Django, WebObjects, .NET (different frameworks have different models) - Note that the frameworks can be built on top of other languages (such as Java + JDBC) pl/pgsql ---------- - Note that languages may be different, but the way that communicate with SQL is very similar. - We will go into detail of one language and then show similar concepts in other paradigms. See full documentation here: http://www.postgresql.org/docs/9.5/interactive/plpgsql.html - pl/pgsql supports the same data types as the database - Programs and functions can be compiled and used directly at the db server - Main pl/pgsql block has the form: :: [ <