Frontier Java
Saturday, September 13, 2003
PostgreSQL recursion
I am having trouble setting up a recursive relationship in PostgreSQL. In the following table, updated_by will contain the person_id of whoever performed the update.
CREATE TABLE public.person (
person_id serial,
person_name varchar(40),
updated_by integer DEFAULT 0,
CONSTRAINT person_pk PRIMARY KEY ("person_id")
) WITH OIDS;
The problem is: how do I create the first entry?
The following statement will create the record, but I need the updated_by field to contain the same value as what the implied sequence provided for persion_id.
INSERT INTO public.person (person_name) VALUES ('loader');
The plan is that after the first record is in place, I can apply the recursive constraint and then add all the remaining records, using the loader's id in the updated_by field.
CREATE TABLE public.person (
person_id serial,
person_name varchar(40),
updated_by integer DEFAULT 0,
CONSTRAINT person_pk PRIMARY KEY ("person_id")
) WITH OIDS;
The problem is: how do I create the first entry?
The following statement will create the record, but I need the updated_by field to contain the same value as what the implied sequence provided for persion_id.
INSERT INTO public.person (person_name) VALUES ('loader');
The plan is that after the first record is in place, I can apply the recursive constraint and then add all the remaining records, using the loader's id in the updated_by field.
Friday, September 12, 2003
PGAdmin problems
The pgAdmin II client has bugs. If I try to add too many fields at a time, it gives me some error about 'default values' even though I don't have any set. I think I'll revert back to creating a sql file that drops and creates everything. I'll use pgAdmin for tweaking and learning PostgreSQL, then funnel it back to the sql file.
Thursday, September 11, 2003
PostgreSQL install
I've got PostgreSQL running on the dev server. To manage it I installed pgAdmin II on the desktop. Got it working easily, but had to make a few configuration changes on dev.
Added two lines to /var/lib/pgsql/data/postgresql.con:
tcpip_socket = true
port = 5432
Added one line to /var/lib/pgsql/data/pg_hba.conf:
host all all my_desktop_ip 255.255.255.255 trust
Not very secure, but hey, these aren't the production servers.
Added two lines to /var/lib/pgsql/data/postgresql.con:
tcpip_socket = true
port = 5432
Added one line to /var/lib/pgsql/data/pg_hba.conf:
host all all my_desktop_ip 255.255.255.255 trust
Not very secure, but hey, these aren't the production servers.
Wednesday, September 10, 2003
DBDesigner4
I installed DBDesigner4 on my desktop, a Dell 4300 running Win2000, but couldn't get it to connect to the MySQL database on my dev server. Probably something in a configuration file somewhere.
That is probably a good thing because I need PostgreSQL for my project anyway.
That is probably a good thing because I need PostgreSQL for my project anyway.
Tuesday, September 09, 2003
phpMyAdmin and MySQL
I work from my home near Vancouver, BC for an American company. Gotta love it. No more commute, office politics or dress code!
Today I prototyped the database in MySQL. A nice easy database to work with, especially since I have phpMyAdmin installed on the dev server.
Today I prototyped the database in MySQL. A nice easy database to work with, especially since I have phpMyAdmin installed on the dev server.
Monday, September 08, 2003
LAMP server
I've got my dev server up and running. It is a LAMP server, which is an acronym for Red Hat Linux, Apache, MySQL and PHP. It is a 600MHz Pentium2 box with 128 MB and a 13 Gig drive.
