---

phpPgWeb Documentation

What this library can do

This PHP library is intended to make quickly a user fiendly web interface to a postgresql database. It is thinked to be configurable, manage automaticly references betwhin tables, has multilanguage support, and could be extended quite easly. It is not intended as admin tool (see phpPgAdmin), but as an intuitive web mask to a database. It supports also file upload that could be stored in your file system or directly in the database as a postgres large object. Creates automaticly thumbnail if a image is uploaded and read exif information (metadata headers of image generated by digital cameras)

What requires

• It was tested on apache server on linux, but there are no reason to don't work with other systems.
• It requires PHP >= 4.0 but >= 4.2 it is better.
  It is important that php enviroment variable magic_quotes_gpc is set to Off. Set this in your php.ini file or in apache web server configuration file write php_admin_value magic_quotes_gpc Off
• It was tested with postgres >= 7.1 Postgres versions < 7.3 dont manage automaticly relatuions betwin tables, but it is possible to link manually them.
• Thumbnail are made whit convert program of imagemagic if is installed, othervise with php internal function if exist(if php is compiled with them).
• Exif info are read with php internal function if exist(if php is compiled with them).

It works with all tested browser. With text browser links some non essential javascript function don't works, but it is usable.

How use this library

1. You must to include all ./include/libs libraries (see ./include/init/init.php for an example)
2. Than you have to open a postgresql connection with standard pg_connect() function.
3. Now you can initialize a new Pgtable object, specifing as parameters postgresql connection object and configuration file (see ./conf/pgtbconf.ini for an example). See Pgtable Class Reference for more details (or ./html/tables.php).
4. So you have to add a new table to the object Pgtable with function add_table() specifing as parameters the schema name, the table name and if you want the primary key column name. See Pgtable Class Reference for more details (or ./html/tables.php).
5. Now you can set some proprieties, configure visualization parameters, limit permission on the table and others features.
   See How upload files, How change perms or how links tables sections for examples.
6. Than you must to run the user query calling switch_action()method, specifing as parameters the schema name and the table name. This return a hash with keys:
   • text: text to display at the bottom of the table
   • search_bar: search bar
   • submit: submit buttons
   • table: html table

   See Pgtable Class Reference for more details (or ./html/tables.php).
7. So if you print search_bar, table, text and submit into a form tag that calls the same page, your script have to work. Note that the form must be a post thype form, with enctype="multipart/form-data" if you want submit files (see ./html/tables.php). It could be similar to this:
   

   <form  name="pgform" method="POST" enctype="multipart/form-data" action="<?=$_SERVER["PHP_SELF"]>"?>
   
   

   Note that it is important to specify form name that corespond with formName specified in the configuration file of your Pgclass object (see ./conf/pgtbconf.ini). It is used by javascript for tables references.

How uplod files and store into the database

There are two mode to store files with this tool:

1. save as postgres large object. In this case files was stored directly into the database. This is the better way. There is a problem if database and php dont see the same filesystem. This could be resolved with a not implemented trigger procedure in plperlu or other postgres procedural language.

   Here is an example:
   SQL code to create table:

   CREATE TABLE lobjecttable(
   	note text,
   	file_size bigint,
   	file_name varchar,
   	file_type varchar,
   	file_thumbnail oid,
   	file_file oid,
   	file_exif varchar
   	);
   

   now you have to call alterType() method of object Pgtable to change the visualization mode as specified at point 5 of section How use this library:

   $pgtable->alterType('public', 'lobjecttable','file_file', "lobject", 
                        array( "type"=>'',
   		            'mime_type_col'=>'file_type',
   			    'name_col'=>'file_name', 
   			    'size_col'=>'file_size', 
   			    'thumbnail_col'=>'file_thumbnail', 
   			    'exif_col'=>'file_exif', 
   			    "thumbnail_size"=>120
   			    )
   		     );
   

   "type" could be an array of mime_type allowed or of a part of mime_type allowed (for example "type"=>array("image", "application/pdf") if you want to store only images and pdf)

   See alterType() function references for details or more features and .html/tables.php for an example.

2. save in a directory of the filesystem visible from web server. Into the database is saved only the file name. The directory is specified in the configuration file that you must specify when a new Pgclass object is initialized. See ./conf/pgtbconf.ini for this detail.

   Here is an example:
   SQL code to create table:

   CREATE TABLE filetable(
   	note text,
   	file_size bigint,
   	file_name varchar,
   	file_type varchar,
    	file_thumbnail varchar,
   	file_file varchar,
   	file_exif varchar
   	);
   

   now you have to call alterType() method of object Pgtable to change the visualization mode as specified at point 5 of section How use this library:

   $pgtable->alterType('public', 'filetable','file_file', "file", 
                        array( "type"=>'',
   		            'mime_type_col'=>'file_type',
   			    'name_col'=>'file_name', 
   			    'size_col'=>'file_size', 
   			    'thumbnail_col'=>'file_thumbnail', 
   			    'exif_col'=>'file_exif', 
   			    "thumbnail_size"=>120
   			    )
   		     );
   

   "type" could be an array of mime_type allowed or of a part of mime_type allowed (for example "type"=>array("image", "application/pdf") if you want to store only images and pdf)

   See alterType() function references for details or more features and .html/tables.php for an example.

For thise two ways, there is a small poblem of integrity: if your table in wich there is stored the large object or the file name, could be modified from other interfaces different form phppgweb or has a references on another table, created with "on delete cascade" clause, there is the possibility to remove only the record without deleting file or large object or the possibility to insert inexistents files or large object. So only in thise particula cases it is better to create a control trigger that delete large object or file when a delete occurs or when an update change the refer to the file.

How change language

Change language is simple: you hace to modify de file specified by strings parameter into the configuration file of your Pgclass configuration file(specified whan the class is initialized). (see ./conf/pgtbconf.ini for an example)

If your language not exist create a copy of existent file and translate the few strings presents in it. You can so send me this file to add support for other languages.

How link tables

Tables are automaticaly linked if are created with correctly postgresql syntax.

In other case you can join explicitly tables with join_parent() and join_child() methods of Pgtable class. If your rferenced table is quite small and you want a select input against the default popup window, you could do this with alter_parent() method of Pgtable class. This is an example to be called as specified at point 5 of section How use this library:

$pgtable->alter_parent('child_table_schema_name', 'child_table_name', 'child_table_column_name', array("type"=>"select"));

If you will display another column of referenced table or a mix of its column you can call the function in this way:

$pgtable->alter_parent('child_table_schema_name', 'child_table_name', 'child_table_column_name',
                        array("type"=>"select",
			      "query"=>"SELECT ref_table_column as \"OID\", surname ||' '|| name as child_table_column_name from ref_table_schema.ref_table_name"
                             )
		      );

Note that it is important to rename appopriatly the query results as in the example, and that OID corespond to referenced column name of child table.
The cuery must not have semicolon.

How manage perms

It is possible to change dinamically perms to a table (if view, insert, update...).
You have to redefine public attribute actions of Pgtable class.
By defaut it allows all actions on the selected tables table_name of schema table_schema_name: it is set to

$pgtable->actions[table_schema_name][table_name]=array('search','edit','insert','delete','view', 'preMultipleInsert', 'copy', 'modifySearch', 'multipleInsert');

You can reset this attribute to only some values of the above in the part specified at point 5 of section How use this library.

---