PostgreSQL

Aus Limbas Wiki

Wechseln zu: Navigation, Suche
Diese Seite ist eine übersetzte Version der Seite PostgreSQL und die Übersetzung ist zu 100 % abgeschlossen sowie aktuell.

<-- backt to Main Page


PostgreSQL Update

For some distributions, the PostgreSQL database and in particular the ODBC driver needs to be updated.
Limbas has been tested on PostgreSQL from version 8.3.7 upwards .
For CentOS, the database can be updated by the following commands. wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm rpm -i pgdg-centos-8.4-1.noarch.rpm yum update

PostgreSQL Installation for Limbas

From Version 2.0.22 onwards, LIMBAS supports UTF8. If UTF8 is not required, ISO encoding is recommended. LIMBAS converts the date and time formats independently and requires the date format "DD.MM.YYYY". Since PostgreSQL is installed with localization as standard, either the record cluster or the record without localization has to be created.


Create/Renew Database Cluster

- Stop postgres server (root)

/etc/init.d/postgresql stop

- Delete old cluster:

Directory content (cluster) of "/var/lib/pgsql/data" has to be deleted (Please notice: Existing databases will be deleted !)

- Create cluster without localisation:
Basically you should not use localisation because of less performance. Be aware that Limbas expect the dateformat in 'YY-MM-DD'. If you have trouble with special digits you can use the ctype setting.

su - postgres
initdb --local=C
[ initdb --local=C --lc-ctype=de_DE ]
exit


Start Server (root)

systemctl start postgresql.service

Create Database

Without localization. Possible encondings are for example LATIN1 or SQL_ASCII or UTF8

# su - postgres
# psql
# create user limbasuser password 'limbaspass';
# create database limbas WITH ENCODING 'SQL_ASCII' LC_COLLATE 'C' LC_CTYPE 'C' OWNER limbasuser TEMPLATE template0;
# \q
# createlang plpgsql limbas

Unix ODBC Setup

unixODBC

Additional Information

Version 9.1

Since version 9.1 backslashes are handled differently (http://www.postgresql.org/docs/9.2/static/release-9-1.html). This is a problem with the previous versions of Limbas. In order to use these versions, the constant LMB_DBFUNC_UMASCB in the file "lib/db/db_postgres.lib" has to be adapted:

#define("LMB_DBFUNC_UMASCB","\\\\");
define("LMB_DBFUNC_UMASCB","\\");

Additionally, the functions "dbf_6" and "dbf_7" have to be patched:

function dbf_6($p){
	return str_replace("'","''",$p);
}

Adding required functions

This functionality is performed automatically during installation and is used only for documentation.

LIMBAS requires a defined function that can be executed by triggers. It deals with the values ​​displayed when adding or removing links. Limbas generates the associated trigger itself and can be renewed if necessary in the LIMBAS Admin section under "tools- > system -> trigger" . The function can only be created if the required language "plpgsql" has been added to the database.

CREATE OR REPLACE FUNCTION lmb_vkn() RETURNS trigger AS '
DECLARE

statement VARCHAR(200);
nid INTEGER;
 
BEGIN

IF TG_ARGV[2] = ''+'' THEN
	 nid = new.id;
END IF;
IF TG_ARGV[2] = ''-'' THEN
	nid = old.id;
END IF;

statement = ''UPDATE '' || TG_ARGV[0] || '' SET '' || TG_ARGV[1] || '' = '' || TG_ARGV[1] || '' '' || TG_ARGV[2] || '' 1 WHERE id = '' || nid;
EXECUTE statement;


IF TG_ARGV[3] = '''' THEN
	return new;
END IF;

IF TG_ARGV[2] = ''+'' THEN
	 nid = new.verkn_id;
END IF;
IF TG_ARGV[2] = ''-'' THEN
	nid = old.verkn_id;
END IF;


statement = ''UPDATE '' || TG_ARGV[3] || '' SET '' || TG_ARGV[4] || '' = '' || TG_ARGV[4] || '' '' || TG_ARGV[2] || '' 1 WHERE id = '' || nid;
EXECUTE statement;


return new;

END; 
'  LANGUAGE 'plpgsql';

General Tools

Overview of all Databases

psql -l

Create User

createuser username

or with SQL Befehl: create user username password 'secret'

Delete USer

dropuser username

or with SQL Command: DROP USER name;

Database Creation over Console

su - postgres createdb -O username limbas

Delete Database

su - postgres
dropdb dbname

add plpgsql languge

createlang plpgsql limbas

Restart Server

pg_ctl reload




To dump a database:

$ pg_dump mydb > db.out

To reload this database:

$ psql -d database -f db.out

To dump a database called mydb that contains large objects to a tar file:

$ pg_dump -Ft -b mydb > db.tar

To reload this database (with large objects) to an existing database called newdb:

$ pg_restore -d newdb db.tar