Re: [PHP] PHP PostgreSQL
Michael Sims wrote: o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. True, changing schema is a major PITA with Postgres. My only real complaint about it, in fact... Have you installed the recent PostgreSQL version 7.3 already? This new version allows the use of ALTER TABLE much like you'd want it. Vincent -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 18:13, Boget, Chris wrote: I'm switching from a MySQL environment to PGSQL and I'm going through and trying to learn the differences between the two. I've come across some issues that I can't seem to find the answers to in the docs or on the web. I'm hoping someone can help me out. * MySQL has a function to reset the result set so that it can be iterated through again - mysql_data_seek(). I don't see that there is a similar function for PGSQL. Is that true? If so, in order to reset the result set you have to perform the query again? Isn't that a waste of resources? pg_result_seek() should perform a similar function. In most cases you wouldn't need to use that because if you're going to be using the results more than once you could store them in an array. * For PGSQL, you can get the database name, the field name even the *host name* but you can't get the table name from a particular query? Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* In Hollywood, all marriages are happy. It's trying to live together afterwards that causes the problems. -- Shelley Winters */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP PostgreSQL
pg_result_seek() should perform a similar function. In most cases you wouldn't need to use that because if you're going to be using the results more than once you could store them in an array. Sometimes yes, sometimes no. But a valid point nonetheless. * For PGSQL, you can get the database name, the field name even the *host name* but you can't get the table name from a particular query? Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information (user, pw, host and perhaps a dbname that may or may not be the dbname that gets queried later on). So sure, you can access those member variables (except maybe dbname; I don't think that gets stored) which is also information you can get programatically using built in functions. But let's say you pass a query to the query() method that doesn't use the dbname that you used when instantiating the object. That's certainly valid. But now, there's no way (ok, there's a way but it isn't necessarily accurate) to get the dbname for the query that was just run... apart from requiring the programmer to pass the dbname as another paramter to the query() method You can do this in mysql. I just don't know why you can't do this in pgsql. Chris
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 18:41, Boget, Chris wrote: * For PGSQL, you can get the database name, the field name even the *host name* but you can't get the table name from a particular query? Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information (user, pw, host and perhaps a dbname that may or may not be the dbname that gets queried later on). So sure, you can access those member variables (except maybe dbname; I don't think that gets stored) which is also information you can get programatically using built in functions. But let's say you pass a query to the query() method that doesn't use the dbname that you used when instantiating the object. That's certainly valid. But now, there's no way (ok, there's a way but it isn't necessarily accurate) to get the dbname for the query that was just run... apart from requiring the programmer to pass the dbname as another paramter to the query() method You can do this in mysql. I just don't know why you can't do this in pgsql. Sorry, you've lost me. In your OP you say you wanted the table name, but now you're talking about the dbname? For dbname, you can use (oddly enough) pg_dbname(). -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* [Babe] Ruth made a big mistake when he gave up pitching. -- Tris Speaker, 1921 */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
At 06:20 PM 12/30/02 +0800, Jason Wong wrote: On Monday 30 December 2002 18:13, Boget, Chris wrote: I'm switching from a MySQL environment to PGSQL and I'm going through and trying to learn the differences between the two. The things that bothered me the most: o Pg doesn't have DATE_FORMAT(), or the types SET and ENUM. o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. o The command line program's exit command starts with a \. It took me a long time to find that... The command line interface for pg is much better. o Large result sets are returned in less, and the command editor feels better. o There are lots of cool things you can look at with the \ commands in the command line program.try \help o You can see the source code of the queries that make up the \ commands. I don't remember exactly how, but it is either in \help or the man page. * MySQL has a function to reset the result set so that it can be iterated through again - mysql_data_seek(). pg_fetch_array() has a third parameter that specifies which record to return. In most cases you wouldn't need to use that because if you're going to be using the results more than once you could store them in an array. I disagree... You have easy access to the entire result set, in any order, with either database. You are wasting time and memory making another copy of the data. Rick -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP PostgreSQL
At 04:41 AM 12/30/02 -0600, Boget, Chris wrote: You can do this in mysql. I just don't know why you can't do this in pgsql. The basic answer to why the interfaces to the databases differ is because the PHP interfaces reflects the C interface provided by the database authors. Most PHP extensions are just wrappers that allow you to call existing libraries. Rick -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 19:11, Rick Widmer wrote: At 06:20 PM 12/30/02 +0800, Jason Wong wrote: On Monday 30 December 2002 18:13, Boget, Chris wrote: I'm switching from a MySQL environment to PGSQL and I'm going through and trying to learn the differences between the two. The things that bothered me the most: o Pg doesn't have DATE_FORMAT(), pg's date functions are pretty abysmal compared to mysql's. or the types SET and ENUM. These aren't standard and handling these in PHP does require a bit more code. So you're probably better off using an extra table instead. o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. pg's ALTER TABLE doesn't really let you alter much at all which makes prototyping a pain. o The command line program's exit command starts with a \. It took me a long time to find that... Use CTRL-d, it's a very commonly used exit command which gets you out of almost anything -- mysql, psql, ftp, telnet, ssh etc. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Ambiguity: Telling the truth when you don't mean to. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP PostgreSQL
Sorry, you've lost me. In your OP you say you wanted the table name, but now you're talking about the dbname? For dbname, you can use (oddly enough) pg_dbname(). Eeep, I'm ever so sorry. That's what I get for responding at 4:30a when trying to battle insomnia. Yes, I am looking for the table name, not dbname. My apologies. Chris
Re: [PHP] PHP PostgreSQL
Due to writing this email *way* too early in the morning, I got everything mixed up and didn't say what I meant. The below is what I really meant. Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information. When you pass a query to the query() there's no way (ok, there's a way but it isn't necessarily accurate) to get the table name for the query that was just run... apart from requiring the programmer to pass the table name as another paramter to the query() method You can do this in mysql using the mysql_tablename() function. I just don't know why you can't do this in pgsql. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Mon, 30 Dec 2002 04:11:02 -0700, you wrote:
The things that bothered me the most:
o Pg doesn't have DATE_FORMAT()
to_char() accomplishes the same thing, for example:
select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf
from users;
, or the types SET and ENUM.
I'm not sure what SET is, never used it, but ENUM is totally
unnecessary in Postgres because it supports foreign keys. If you want
a particular field to only allow ENUM('red', 'blue', 'green') then
create a lookup table, insert these values into it, and then set up a
foreign key. This is more standard anyway, and if you need to update
the set there is no need to touch the database schema.
o Changing database structure is harder. With PG, I usually found it
easier to dump, edit, then reload the database to make changes I did
in MySQL with ALTER TABLE.
True, changing schema is a major PITA with Postgres. My only real
complaint about it, in fact...
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
o Pg doesn't have DATE_FORMAT() to_char() accomplishes the same thing, for example: select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf from users; Unless I missed something, that function doesn't work with unix timestamps. Is there anythin internal to PG that allows you to convert to and from unix timestamps? , or the types SET and ENUM. o Changing database structure is harder. With PG, I usually found it easier to dump, edit, then reload the database to make changes I did in MySQL with ALTER TABLE. True, changing schema is a major PITA with Postgres. My only real complaint about it, in fact... That's how I did it and it was, indeed, a pain in the butt... But there was some good that came out of it. When I created the MySQL tables, I didn't have foreign keys (wasn't using InnoDB), constraints or anything like that so in recreating the tables, I was able to implement those things to make life easier down the road when reworking the code and the logic. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Mon, 30 Dec 2002 07:57:04 -0600, you wrote:
o Pg doesn't have DATE_FORMAT()
to_char() accomplishes the same thing, for example:
select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf
from users;
Unless I missed something, that function doesn't work with unix timestamps.
Is there anythin internal to PG that allows you to convert to and from unix
timestamps?
To convert TO a unix timestamp:
select date_part('epoch', lastlogin) as lastloginf;
or
select extract(epoch from lastlogin) as lastloginf;
To convert FROM a unix timestamp:
select to_char(timestamp 'epoch' + unix_timestamp, 'FMMM-DD-
FMHH:MI AM');
That's how I did it and it was, indeed, a pain in the butt... But there was
some good that came out of it. When I created the MySQL tables, I didn't
have foreign keys (wasn't using InnoDB), constraints or anything like that
so in recreating the tables, I was able to implement those things to make
life easier down the road when reworking the code and the logic.
And there is the major benefit of using Postgres...not to mention
sub-selects, views, unions, etc. Once you get used to these things
it's very hard to give them up.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 21:33, Chris Boget wrote: Due to writing this email *way* too early in the morning, I got everything mixed up and didn't say what I meant. The below is what I really meant. Not sure what you're getting at here. Surely for any particular query, _you_ would know what table(s) is/are being used? That's not necessarily true. Certainly not if you are using an abstraction layer to access the database. Take PEAR for example. When instantiation on object, you give it the relevant connection information. When you pass a query to the query() there's no way (ok, there's a way but it isn't necessarily accurate) to get the table name for the query that was just run... apart from requiring the programmer to pass the table name as another paramter to the query() method You can do this in mysql using the mysql_tablename() function. I just don't know why you can't do this in pgsql. Either you're confused or I'm confused :) mysql_tablename() is used in conjunction with mysql_list_tables() to get the names of the tables defined in the specified database. It cannot get the table name for the query that was just run And another thing, you _know_ the query that was just run, and presumably the tables used is part of the query itself, so why would you need another function to tell you should already know? Can you give an example of what you're trying to do? -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Q: How did you get into artificial intelligence? A: Seemed logical -- I didn't have any real intelligence. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
To convert TO a unix timestamp: To convert FROM a unix timestamp: Excellent. Thank you very much for the info! And there is the major benefit of using Postgres... Why do you think I decided to move to Postgres? :P not to mention sub-selects, views, unions, etc. Indeed. And I'm going to take every advantage in using them in my code. I figure when all is said and done, the scripts will run so much faster because it won't have to be doing those things programatically. Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Monday 30 December 2002 21:57, Chris Boget wrote: o Pg doesn't have DATE_FORMAT() to_char() accomplishes the same thing, for example: select to_char(lastlogin, 'FMMM-DD- FMHH:MI AM') as lastloginf from users; Unless I missed something, that function doesn't work with unix timestamps. No, it only works on pg's date/time formats. Is there anythin internal to PG that allows you to convert to and from unix timestamps? Best to store date/time in pg's format rather than unix timestamp. If you've got legacy data which are stored as unix timestamps then you can do one of the following: 1) Do a one time conversion from unix timestamp to pg date/time 2) Manipulate the dates in PHP instead 3) Write your own function in pg to convert them on the fly -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Griffin's Thought: When you starve with a tiger, the tiger starves last. */ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
Either you're confused or I'm confused :) It's me. This just hasn't been a good week for me. :p One time I'll get it right! mysql_tablename() is used in conjunction with mysql_list_tables() to get the names of the tables defined in the specified database. It cannot get the table name for the query that was just run You are right. The function I was thinking of was mysql_field_table() I knew I should have consulted the documentation and not relied on my memory. :p And another thing, you _know_ the query that was just run, and presumably the tables used is part of the query itself, That's not true in all cases. so why would you need another function to tell you should already know? Can you give an example of what you're trying to do? Sure. And this is what prompted my message initially. PEAR's sql2xml only works with mysql because there's no way to get the table name programatically. When you are using an abstraction such as this, unless you pass another argument to the primary function, you have to go through hoops within the abstraction just to pull it out of the query. The code for sql2xml can be found here: http://pear.php.net/package-info.php?pacid=18 Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
At 07:59 AM 12/30/02 -0600, Michael Sims wrote: On Mon, 30 Dec 2002 04:11:02 -0700, you wrote: , or the types SET and ENUM. I'm not sure what SET is, never used it, Color set( 'red', 'green', 'blue' ) can contain only the following values: NULL;'blue'; 'green'; 'green,blue'; 'red'; 'red,blue'; 'red,green'; 'red,green,blue'; You can enter the color names in any order, but they always come out like this. Only words contained in the set are allowed. You can also look at a set as a number, and do bit manipulation on it. The list of values above are 0 to 7 when used in a numeric context. but ENUM is totally unnecessary in Postgres because it supports foreign keys. The big difference using set and enum is in data storage. If you look on the disk you will find both types are stored in the smallest integer they fit in. Even 'red,green,blue' fits in a single byte each time it is stored. This is more standard anyway, and if you need to update the set there is no need to touch the database schema. I like being able to define some things in the schema. I have widgets in my toolkit that build arrays of checkboxes, radio buttons or drop down lists based on the values allowed in an enum or set. They get used quite a bit, in fact losing these widgets may be the biggest thing keeping me using MySQL. (Harder prototyping is a close second.) Rick. P.S. Thanks for the info on handling dates! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP PostgreSQL
On Sun, 4 Mar 2001, Marcelo Pereira wrote: Hi, All. I have to build a web site and I was studying postgreSQL, but, whether I am quite wrong or this database does not support Foreign Key. Does anyone use postgreSQL and know how handle Foreign Keys ??? Yes, it supports Foreign Keys ... how to use them is another story, as its something I keep meaning to dive into and just never seem to find the time for :( Check out: http://postgresql.readysetnet.com/users-lounge/docs/7.0/user/sql-createtable.htm which talks about using CONSTAINTS on tables, where 'REFERENCES' is discussed ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] PHP PostgreSQL
The following is an example demonstrating how to set up a foreign key
constraint in PostgreSQL 7:
CREATE SEQUENCE school_id_seq;
CREATE TABLE school (
school_id INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'),
school_name VARCHAR(80) NOT NULL
);
CREATE SEQUENCE student_id_seq;
CREATE TABLE student (
student_id INT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'),
student_name VARCHAR(80) NOT NULL,
school_idINT NOT NULL,
CONSTRAINT school_exists
FOREIGN KEY(school_id) REFERENCES school
ON DELETE RESTRICT
);
INSERT INTO school (school_name) VALUES ('Alice''s School of Truck
Driving');
INSERT INTO school (school_name) VALUES ('Bob''s School of Underwater
Knitting');
INSERT INTO student (student_name, school_id) VALUES ('Charlie', 1);
INSERT INTO student (student_name, school_id) VALUES ('Doug', 1);
INSERT INTO student (student_name, school_id) VALUES ('Ernie', 2);
Note the 'ON DELETE RESTRICT' which will prevent you from deleting a school
if there is a student going to that school. First look at what's in the
tables:
SELECT * FROM school;
SELECT * FROM student;
Now attempt to delete the school:
DELETE FROM school WHERE school_id = 1;
If you try the above, you should see 'ERROR: school_exists referential
integrity violation - key in school still referenced from student' and
notice that nothing was deleted.
If you want to try the above more than once, the following may be handy:
DROP SEQUENCE school_id_seq;
DROP TABLE school;
DROP SEQUENCE student_id_seq;
DROP TABLE student;
Cascade Update
Instead of 'ON DELETE RESTRICT' you could specify 'ON DELETE CASCADE'. This
would allow the delete (instead of preventing it like in the example above),
but it would 'cascade' the delete to the student table so that any students
going to the school you deleted would also be deleted.
As well as the 'ON DELETE ...' clause you can also specify what is to happen
on an update of the foreign key with either:
a.. ON UPDATE RESTRICT or
b.. ON UPDATE CASCADE
ON UPDATE RESTRICT would prevent UPDATE school SET school_id = 20 WHERE
school_id = 1 from proceeding if there were any students with a school_id of
1.
You might be able to guess that ON UPDATE CASCADE would allow the UPDATE
school SET school_id = 20 WHERE school_id = 1 to proceed, but it would also
update the school_id field in the student table appropriately.
Foreign Keys where the field names are different
Consider the following table setup:
CREATE SEQUENCE school_id_seq;
CREATE TABLE school (
id INT NOT NULL PRIMARY KEY DEFAULT nextval('school_id_seq'),
name VARCHAR(80) NOT NULL
);
CREATE SEQUENCE student_id_seq;
CREATE TABLE student (
idINT NOT NULL PRIMARY KEY DEFAULT nextval('student_id_seq'),
name VARCHAR(80) NOT NULL,
school_id INT NOT NULL,
CONSTRAINT school_exists
FOREIGN KEY(school_id) REFERENCES school(id)
ON DELETE RESTRICT
);
INSERT INTO school (name) VALUES ('Alice''s School of Truck Driving');
INSERT INTO school (name) VALUES ('Bob''s School of Underwater Knitting');
INSERT INTO student (name, school_id) VALUES ('Charlie', 1);
INSERT INTO student (name, school_id) VALUES ('Doug', 1);
INSERT INTO student (name, school_id) VALUES ('Ernie', 2);
Note how the field names don't match (school.id as opposed to
student.school_id); in this case we can put the field name in brackets after
the table name.
As before, the following will fail:
DELETE FROM school WHERE id = 1;
- AndrewH
- Original Message -
From: "Marcelo Pereira" [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, March 05, 2001 1:00 AM
Subject: [PHP] PHP PostgreSQL
Hi, All.
I have to build a web site and I was
studying postgreSQL, but, whether I am
quite wrong or this database does not
support Foreign Key.
Does anyone use postgreSQL and know
how handle Foreign Keys ???
Thanks,
Marcelo Pereira
Computer Programmer
__
O BOL Top10 no iBest! Vote j para torn-lo Top3!
http://www.bol.com.br/ibest.html
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
