Archive

Posts Tagged ‘postgresql’

Fixing the upgrade procedure of WebCalendar 1.2 for PostgreSQL 7.4

September 28, 2008 1 comment

When trying to upgrade WebCalendar to 1.2, I fell on a series of errors due to the fact that the upgrade script relies on a dependency to PostgreSQL 8.1, which allows much more ALTER TABLE syntaxes than PostgreSQL 7.4. Nevertheless, there are ways to make it work by updating the upgrade script a little to suit the new syntax.

I’m giving bits of code here, which are directly based on the www/install/sql/update-postgres.sql script, and altered to make it work.

This script can be executed manually as many times as you want with the PostgreSQL user you use for Webcalendar. Something like

psql -U webcalendar -W webcalendar < upgrade-postgres.sql

Please note that using the “postgres” user will inevitably create permissions problems that you might have to fix later using something like:

GRANT ALL PRIVILEGES ON DATABASE webcalendar TO webcalendar;

or something more specific if that doesn’t fix it.

The “TYPE” syntax error

On line 156 of the update-postgres.sql script, you will find the following query:

ALTER TABLE webcal_user ALTER COLUMN cal_passwd TYPE VARCHAR(32);

The ALTER TABLE – ALTER COLUMN with a TYPE constraint, however, is unknown in Postgresql 7.4, so you need to change this line. Here is the code I used to replace it (quite long as you will see)

/* Query in PG 7.4 form */
/*ALTER TABLE webcal_user ALTER COLUMN cal_passwd TYPE VARCHAR(32); */
BEGIN;
ALTER TABLE webcal_user ADD COLUMN new_col VARCHAR(32);
UPDATE webcal_user SET new_col = CAST(cal_passwd AS VARCHAR(32));
ALTER TABLE webcal_user DROP COLUMN cal_passwd;
ALTER TABLE webcal_user RENAME COLUMN new_col TO cal_passwd;
COMMIT;
VACUUM FULL webcal_user;

The VACUUM FULL command at the end allows for a complete memory clean-up which would otherwise not happen.

The UPDATE TABLE error for webcal_user

You will find the following lines around line 305:

ALTER TABLE webcal_user ADD COLUMN cal_enabled CHAR(1);
UPDATE TABLE webcal_user SET cal_enabled = ‘Y’;

This one is easy: the “UPDATE TABLE” syntax is not allowed in PG 7.4. You simply have to remove the “TABLE” keyword:

ALTER TABLE webcal_user ADD COLUMN cal_enabled CHAR(1);
UPDATE webcal_user SET cal_enabled = ‘Y’;

The NOT NULL constraint in webcal_entry_log problem

This one seems to be a real bug somewhere in the creation procedure for PostgreSQL tables. In fact, if you look into the tables-postgres.sql file, you will find that the webcal_entry_log.cal_user_cal column has a NOT NULL constraint, while in the equivalent script for MySQL, NULL values are authorized. This is tricky to fix (and uses the same procedure as the “TYPE” syntax bugfix above. In order to place it more or less in the right place, I have tried to put it around the same line as a query on the webcal_entry_log table in the upgrade-mysql.sql script (which is around line 75, or just before a comment talking about version 1.1.0).

/* Fix not null constraint in webcal_entry_log.cal_user_cal */
BEGIN;
ALTER TABLE webcal_entry_log ADD COLUMN new_col VARCHAR(25) NULL DEFAULT NULL;
UPDATE webcal_entry_log SET new_col = CAST(cal_user_cal AS VARCHAR(25));
ALTER TABLE webcal_entry_log DROP COLUMN cal_user_cal;
ALTER TABLE webcal_entry_log RENAME COLUMN new_col TO cal_user_cal;
COMMIT;
VACUUM FULL webcal_entry_log;

The webcal_nonuser_cals.cal_is_public missing column

For some reason, the cal_is_public field had not been created by the upgrade procedure, so I had to add it manually (I did that around line 200)

ALTER TABLE webcal_nonuser_cals ADD cal_is_public CHAR(1);
ALTER TABLE webcal_nonuser_cals ALTER cal_is_public SET DEFAULT ‘N’;
ALTER TABLE webcal_nonuser_cals ALTER cal_is_public SET NOT NULL;

The webcal_entry_repeats.cal_wkst DEFAULT constraint

Around line 240, you will find the following query

ALTER TABLE webcal_entry_repeats ADD cal_wkst CHAR(2) DEFAULT ‘MO’;

Because default values constraints cannot be assigned directly in an ALTER TABLE query, you have to split this line

/* Update to upgrade in PG 7.4 */
/* ALTER TABLE webcal_entry_repeats ADD cal_wkst CHAR(2) DEFAULT ‘MO’; */
ALTER TABLE webcal_entry_repeats ADD cal_wkst CHAR(2);
ALTER TABLE webcal_entry_repeats ALTER COLUMN cal_wkst SET DEFAULT ‘MO’;

You should now be alright with the SQL upgrade procedure.

Encoding

You might (but this has *nothing* to do with the above) run into an encoding problem whereby the accentuated characters are transformed into little squares with question marks. This is generally due to the fact that Webcalendar only deals with ISO charsets, while your Apache configuration might be set to a default encoding of UTF-8. Make sure there is an Apache’s VirtualHost defined with an encoding of ISO-8859-1 if you are going to use this encoding.

Advertisements

Running PostgreSQL on Solaris 8

July 2, 2008 Leave a comment
This article was first written in February 2004 for
the BeezNest technical website (http://glasnost.beeznest.org/articles/107)

The default values for Shared Memory in Sun’s Solaris (starting from 2.6, at least) are set too low to run.

Here is how to change it (see also PostgreSQL’s Admin Guide, Managing Kernel Ressources):

The relevant settings can be changed in /etc/system, for example:

set shmsys:shminfo_shmmax=0x2000000 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=256 set shmsys:shminfo_shmseg=256 set semsys:seminfo_semmap=256 set semsys:seminfo_semmni=512 set semsys:seminfo_semmns=512 set semsys:seminfo_semmsl=32

You need to reboot to make the changes effective.

See also http://www.sunworld.com/swol-09-1997/swol-09-insidesolaris.html for information on shared memory under Solaris.


								
Categories: English, Tech Crunch Tags: , ,

A few things about CakePHP, PostgreSQL and Xapian

January 18, 2008 20 comments

While on part-time holidays from Dokeos, I’m looking into some of the stuff I will need to be aware of when working on Dokeos for the next year. I think you can call that technology watch.

Anyway, I’m looking into CakePHP, PostgreSQL and most of all Xapian (although I did not yet reach that point).

CakePHP is the closest thing to a “PHP on Rails” (or so I though at the time of writing this article – turns out there are much closest matches – see comments to this post). You can develop ugly but functional web applications really fast, then start working on the things that really matter in terms of graphics. I’ve barely done a few pages but I have read a lot. Although the next version (1.2), integrating l10n and i18n is only in beta at the time of writing, it does look promising and I bet in a year or so it might be quite easy to do a simple e-learning system with that. However, we are going our way in the same direction too, with the preparation of Dokeos 2.0, which will bring object-oriented code which will (hopefully) speed up our developments.

I need a file upload feature (to index text files) and a users and permissions management for a small application I’m working on (to prepare for indexation in Dokeos) and I have found enough websites to get me going… (see references below)

About PostgreSQL now, I just realised there is no known way of retrieving PostgreSQL arrays directly from PHP. You have to fetch them as strings and then “parse” the string to get an array. That sounds quite bad to me. Apparently, there is a very recent version of Perl’s DBI that allows that, but I haven’t been able to confirm that just yet. One solution might be to develop a stored procedure in PostgreSQL to do that, but I don’t really fancy the idea of delving into DBMS-specific internals (although arrays are one specific internal of PostgreSQL).

Xapian seems nice. I’m getting more precise in my questions on the Xapian mailing-list, because I know what I want and need now, but I haven’t been able to put it in practice just yet. That’s going to have to wait for next week, probably. Xapian is comparable in many ways to Lucene, a Java-based indexing and search engine. I’m working on this small app’ with my father, who’s spear-heading the Lucene alternative while I’m trying to get my hands deep into Xapian+PHP

The good thing in all this is that all these tools are easily (all things being equal) combined to provide a powerful application for document indexing, storage and retrieval. CakePHP has a fully-functional PostgreSQL mode (which apparently cannot be said about its ADODB mode). Xapian works well with PHP and has a Debian package (php5-xapian) which eases its integration. Let’s see where that leads us…

Exciting!

References

File upload with CakePHP (although not exactly what I’m looking for)

HasAndBelongsToMany relationship (what I’m looking for for the user-group relationship)

HOWTO Fully Install PostgreSQL on Debian

This article was first written in March 2005 for the BeezNest technical
website (http://glasnost.beeznest.org/articles/218).

This is a short tutorial on how to fully install PostgreSQL (with ready-to-use user accounts and test table) on a simple Debian installation.

When not specified otherwise by the command line prefix or by a full statement, you are supposed to have root permissions.

The packages

To install the PostgreSQL base system as well as a textual client, you need to issue the following command

apt-get install postgresql postgresql-client

This will install a lot of package dependencies as well, which should amount to around 11MB. The version of PostgreSQL installed should be 7.4 on a common Debian Sarge system. Keep the documentation nearby in case you need any info: http://www.postgresql.org/docs/7.4/

A few questions will be asked by the configuration script. Most of them can be simply agreed, but you might want to read them to know what configuration PostgreSQL will use.

Data directory All database data will be kept in /var/lib/postgres/data by default.
Data removal policy Also by default, a PostgreSQL system removal won’t remove the data (database contents).
Locale The locale should be configured to accept the character encoding you are going to use with your database system. Choose something that suits you. The list of available locales comes from the system. If you need more locales, you need to install them on the system first  [1]
Date Choose European or US as your preferred date format.

The install continues with several default parameters, and then creates /etc/postgresql/postgresql.conf, which will hold your configuration settings.

Users

The only (default) way to log into your newly created database system is to log as root, then to log as user postgres, by issuing

su
su postgres
psql template1
template1=# \q
exit

However, you would probably like to be able to login as your unix user, or any other user you choose. There are several ways to set up these accesses, so let’s decomposeAlowing local users to login is done by editing the /etc/postgresql/pg_hba.conf file (a complete – but too cloudy – documentation is available here). There, you have to retrieve a series of lines like the following ones

local   all         postgres                                        ident sameuser
#
# All other connections by UNIX sockets
local   all         all                                             ident sameuser
#
# All IPv4 connections from localhost
host    all         all         127.0.0.1         255.255.255.255   md5
# All IPv6 localhost connections
host    all         all         ::1               ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff        ident sameuser
host    all         all         ::ffff:127.0.0.1/128                ident sameuser
#
# reject all other connection attempts
host    all         all         0.0.0.0           0.0.0.0           reject

This file has limited accesses but we will come back here later.Now what we have told PostgreSQL is that all users on the local machine should be able to access all databases, using their UNIX login and passwords. These changes must be operated, so you must restart PostgreSQL

/etc/init.d/postgresql restart

Now the problem is that you have authorized users to log into the database, but they will only be able to log in if they also exist in the database. So you need to create accounts with their usernames. This is done by issuing the createuser command as user postgres

su postgres
createuser firstuser
exit

The createuser command will ask you if the user should be able to create databases or create new users. Answer truly.

Now you have a new user in the database, and you should be able to connect as this user (let’s call him firstuser) like this:

su - firstuser
firstuser@localhost:~$ psql -W template1
template1=# \q
exit

The -W parameter is to ask for a password prompt. You should enter your system password when asked.

So that’s it, we got the first part of this installation running. Now let’s prepare this for the web…

If some of your web scripts are going to need a connection to the database, you will need to grant them some access via a new user, reserved for this web usage.

Let’s first create this new user (as user postgres). Let’s call him webuser

.

su postgres
createuser webuser
exit

Now you want to give him access to one or several databases (let’s say you will let him access the databases which name is web). To do this, you need to edit once more the pg_hba.conf file. Retrieve the first line below and write the second one just beneath the first.

host    all         all         127.0.0.1         255.255.255.255   md5
host    web         webuser     127.0.0.1         255.255.255.255   md5

Would you like to give this user access from any computer on the sub-network 192.168.0.xxx, you would have to add the following line

host    web         webuser     192.168.0.1         255.255.255.0   md5

You have to grant him access to the host as it will probably use the TCP/IP protocol to get a connection to the database, and that’s what host is for. But as you have given him an md5 authentication type, you need to give him a password as well. In order to do this, you need to connect to the database and issue a special command, all as postgres

user:

su postgres
postgres@localhost:~$ psql template1
template1=# alter user webuser password 'some_password';
ALTER USER
template1=# \q
exit

Now the user should have a nice ‘some_password’ password that you can use after restarting PostgreSQL to make it remember your changes to pg_hba.conf.

/etc/init.d/postgresql restart

And you should be able to create his own database if you gave him the permission…

createdb -h localhost -U webuser -W web

And connect to this newly-created database using

psql -h localhost -U webuser -W web

As you might have noticed, we use -h localhost here. This is to force the PostgreSQL client to use TCP/IP instead of UNIX sockets. If you don’t want is, you need to add a new line into the “local” category of pg_hba.conf for the user webuser to connect via sockets.The line would be something like

local   web         webuser                               md5

SQL playground

The first important command you might want to know is

psql -h localhost -U webuser -W -l

This will give you a list of available databases.

Now connect to the PostgreSQL database using the psql client, and let’s create a small table, just after a few tests…

web=> SELECT version();
web=> SELECT current_date;
web=> SELECT 2+2;
web=> \h
web=> \d
web=> \dt
web=> CREATE TABLE weather(
web=>     city            varchar(80),
web=>     temp_lo         int,           -- low temperature
web=>     temp_hi         int,           -- high temperature
web=>     prcp            real,          -- precipitation
web=>     date            date
web=> );
web=> INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
web=>    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
web=> INSERT INTO weather (date, city, temp_hi, temp_lo)
web=>    VALUES ('1994-11-29', 'Hayward', 54, 37);
web=> SELECT city, temp_lo as 'Lowest Temperature', temp_hi as 'Highest Temperature' FROM weather;
web=> SELECT city FROM weather WHERE temp_lo < 40;
web=> SELECT max(temp_lo) FROM weather;
web=> UPDATE weather SET temp_lo = 41 WHERE city = 'Hayward';
web=> SELECT * FROM weather;
web=> DELETE FROM weather WHERE prcp IS NULL;
web=> SELECT * FROM weather;
web=> \q

Now you’ve had a nice overview of several SQL statements and how to use them in PostgreSQL.

Later, you might want to create a table with a numerical sequence as an index. This link will help you get through this step which I find counter-intuitive and user-unfriendly at will. You might as well know of a shortcut… If you want to create a default auto-incremental key that will be used by your table, you need to define (for every table) a SEQUENCE. This id done, for the weather table above, like this (with the help of user comments here

):

web=> CREATE SEQUENCE my_table_pk_sequence;
web=> CREATE TABLE weather(
web=>     id              int  not null default nextval('my_table_pk_sequence') PRIMARY KEY,
web=>     city            varchar(80),
web=>     temp_lo         int,           -- low temperature
web=>     temp_hi         int,           -- high temperature
web=>     prcp            real,          -- precipitation
web=>     date            date
web=> );

And then insert elements as before, ignoring the id field

web=> INSERT INTO weather (date, city, temp_hi, temp_lo)
web=>    VALUES ('1994-11-29', 'Hayward', 54, 37);
web=> SELECT * FROM weather;

This should have inserted an auto-incremented id.But you want more… You want ODBC support. Woaw! Cool!

ODBC Support

Let’s, once more enjoy the freedom and ease of use of Debian. After a little apt-cache search postgres odbc…

apt-get install odbc-postgresql

This should need about 1 MB on your system’s hard drive.

Well… actually… this should be enough to form a DSN name in order to connect to your table. A good source of information on the topic is on gborg.postgresql.org.

Using the parameters we used here above, you should be able to connect with something like:

Server=localhost{ or the IP address of your PostgreSQL server};Database=web;Uid=webuser;Pwd=some_password"

Note that we use MD5 encryption, so you might need to specify that somewhere (and encrypt your password before you put it in the connection string).
Yannick Warnier – ywarnier [at] beeznest [dot] net

[1] To install locales on your system, use the “dpkg-reconfigure locales” command and then reconfigure PostgreSQL using “dpkg-reconfigure postgres” to reselect the default encoding

PostgreSQL Autodoc

June 8, 2004 Leave a comment
This article was first written in June 2004 for the BeezNest technical
website (http://glasnost.beeznest.org/articles/134).

PostgreSQL Autodoc is a powerful and useful tool to document a PostgreSQL database. It is written in Perl and generates several outputs.

Using it is as simple as launching it with the name of the DB, and user and password to connect to it. See postgresql_autodoc –help for help.

The Dia output is not really useful or practical, because all the tables will get on the same place of the Dia document (ie they will all be on top of each other).

The Dot output is a text output meant to be processed by GraphViz (or a compatible program such as Springgraph) to generate graph from it.

It seemed particularly difficult to get a printed version, so I explain here how I got it to work for a medium-sized graph on an A3 printer, on a Debian system with packages graphviz and mpage and xpp installed.

I used dotty which is a graphical tool to see and edit the .dot file. To print it, I went into the menu available on right-click and print graph. There, it asks if you want to print on a file (PostScript output) or on a printer. I chose printer. There, it asks which command-line to use to print. I replaced the call from lpr to xpp, which is interactive. There, it asks for the size and orientation you want. Choose whatever best suite your needs (ie the size of your DB schema). I chose 11×17 and best fit and it launched XPP, where I changed page size to A3 and clicked on button Print.

And guess what? I have my schema printed as I like!

%d bloggers like this: