markw@xxxxxxxxxxxxxx wrote:
markw@xxxxxxxxxxxxxx wrote:
markw@xxxxxxxxxxxxxx wrote:
On Sat, 2007-03-17 at 12:19 -0400, Mark wrote:
There in lies the biggest problem with LAMP, and that's MySQL. The
architecture of your methodology *only* works with MySQL, and not more
capable databases like Oracle, DB2, or even PostgreSQL.
I too thought the same thing, but was corrected when I starting working
on a redesign for the billing system for the company that I am working
for. They use PostgreSQL and we do have a auto incremented unique id
field. It isn't call AUTO_INCREMENT, but rather a sequence value. It
is a value that is controlled by the default value entry.
It then runs this:
nextval('customers_customer_id_seq'::regclass)
and uses this as the value of the id field for the newly inserted row.
Yea, I've been using PG for over 10 years now. Sequences are awesome,
but
there have an "auto number" column type as well. It simply creates a
sequence but it's there.
I guess maybe most databases support the notion of "auto number" in some
form. I haven't used MSSQL recently, but I think their solution is a
GUID.
Then instead of running a command like SELECT LAST_INSERT_ID(); you
have
to do some other stuff like this:
$SQL = "SELECT currval('{$table}_{$column}_seq');";
Again, this is absolutely wrong unless it is wrapped in a transaction.
You
will need a "begin" and a "commit" in an MVCC type system. If you don't
wrap it in a transaction you don't know who got the last "nextval" of
the
sequence.
Check the documentation - currval returns the last one *for that
session* - it does not return the last global change.
http://www.postgresql.org/docs/current/static/functions-sequence.html
It is perfectly safe to use this.
In theory that may be true, but can the application developer make any
assumption about the underlying architecture? Might you be familiar with
connection pooling? Where multiple threads or processes share a database
connection or "session?"
If the developers of the database say it's safe, then I'm happy to take
their word on it. If you can prove otherwise, please do so and post a
bug report to them. In this case, tons of people are using connection
pooling with postgres. If a problem had been discovered, then they would
have complained and it would have been fixed.
Do I need to evaluate every section of code that a 3rd party provides to
make sure the code does what the docs say it should? I'd never get
anything done. At some point I just need to know how to use it and trust
it works as it should. If it's a super critical application (eg
financial information) where integrity is 100% required, then I'd test
it. If I found a problem then I'd complain *shrug*.
As Richard mentioned in another reply, the developers (whether it's
mysql or postgres or "other") have taken a lot of pain to make sure
these things work properly.
Why would I try and do exactly the same thing myself and most likely get
it completely wrong?
The rest of your rant I agree with in theory, but making something
"flexible" is extremely difficult unless you understand what you're
trying to accomplish from the beginning - and know each system well
enough to know how each one works and the differences between them. Not
even "LIMIT" is standard across databases let alone anything else like
data types ('int(11)' compared to 'int', 'datetime' compared to
'timestamp' etc etc etc) or even date formats that they store.
Taking all of that into account from the very beginning is pretty tough.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
