Page 3 of 3
Posted: Sat Aug 26, 2006 11:35 am
by Ollie Saunders
AKA Panama Jack wrote:You do know that your TillingDatabaseAccess.php is really a simple database abstraction layer.

And its simplicity means it won't...[quoteme]slow down the application, and prevent you from making database specific optimizations.[/quoteme]. When I want to add a new query I have to write the query myself only the real basics are being abstracted.
Its not claiming to work across loads of different database technologies, that is what makes things slow. Although actually my feeling on this are changing as I'm planning on supporting
PDO in OsisForms. PDO is highly recommended though much better than PEAR DB or even Zend_Db (when its done).
Posted: Sat Aug 26, 2006 11:39 am
by Ambush Commander
Code: Select all
select @category := LAST_INSERT_ID();
Doesn't this create a race condition?
Posted: Sat Aug 26, 2006 12:28 pm
by onion2k
Ambush Commander wrote:Code: Select all
select @category := LAST_INSERT_ID();
Doesn't this create a race condition?
No. Why would it?
Posted: Sat Aug 26, 2006 12:30 pm
by Ambush Commander
It's an extremely small one, I agree, but there's always the slight possibility that in between the time you performed the insert and then you make the variable assignment another insert took place, causing LAST_INSERT_ID() to give an invalid ID.
Transactions, of course, would make this a non-issue.
Posted: Sat Aug 26, 2006 12:34 pm
by Ollie Saunders
Is a race condition just a fancy term for poor multi-user support?
Posted: Sat Aug 26, 2006 12:35 pm
by timvw
If i understand well, the OP notices that SQL always have the same form (just like each html page that has a head, title, body, etc...). Just like you can build templates for html you can build them for sql...
Instead of building a wrapper around a database api you would implement a SQL Builder class... Eg: a SELECT query has the following properties: from, where, group by, having, selections... I've seen SQL Builders that are smart enough to 'qualify' (place user and table name before the elements in the selections property)... Lot's of nice stuff to do
At work i usually write the basic CRUD operations as a stored procedure... And in php i simply use variable binding and call the procedure.. For the more complex operations i could write more specialized stored procedures or use the SQL Builders that live in our database as other stored procedures....
Posted: Sat Aug 26, 2006 12:38 pm
by timvw
I don't think php uses the same connection at the time for multiple request (one connection per request)...
So basically, in most common usages it's impossible to run into concurrency issues because last_insert_id returns the most recently generated id on a per connection basis... (i don't consider multithreading with php 'common use')
Posted: Sat Aug 26, 2006 1:14 pm
by Ambush Commander
Posted: Sat Aug 26, 2006 2:42 pm
by Weirdan
Ambush Commander wrote:It's an extremely small one, I agree, but there's always the slight possibility that in between the time you performed the insert and then you make the variable assignment another insert took place, causing LAST_INSERT_ID() to give an invalid ID.
Transactions, of course, would make this a non-issue.
last insert id is stored in connection thread context, last_insert_id() just returns that value. Thus it does not create race condition.
Posted: Sat Aug 26, 2006 2:43 pm
by Weirdan
ole wrote:Is a race condition just a fancy term for poor multi-user support?
http://en.wikipedia.org/wiki/Race_condition
Posted: Sat Aug 26, 2006 4:30 pm
by timvw
All pconnect says is that the connection will be re-used..
(As long as a request is being handled by php.. php will use that connection for the request it's handling... Only when the request has been handled, the connection will become available again for other requests...)
Posted: Sat Aug 26, 2006 5:07 pm
by Ambush Commander
Ahhh... I stand corrected then.
Hmm... that probably would mean that I can abandon my `key` table! Whee!
Posted: Thu Aug 31, 2006 4:38 pm
by onion2k
I've been playing around with some SQL stuff this evening, and I've decided that prepared statements are better than simple bound parametres. Rather than doing:
Code: Select all
select @title := 'Product title';
select @description := 'Product description';
insert into product (title, description) values (@title, @description);
it's actually a bit quicker (excution time, not typing

), and safer regarding SQL injection to do
Code: Select all
prepare insert_product from "insert into product (title, description) values (?, ?)";
select @title := 'Product title';
select @description := 'Product description';
execute insert_product using @title, @description
deallocate prepare insert_product;
It's another level of difficulty for a hacker to get past, and for servers without stored procedures it's a pretty good step in the right direction. Coupled with mysql_pconnect() it would actually be virtually the same as using stored procedures (assuming of course that there's a way to check if a prepared statement exists).
Obviously mysqli has a nicer interface to prepared statements than multiple mysql_query() calls, but I'm just bothered with the SQL at the moment.
Posted: Thu Aug 31, 2006 4:46 pm
by Ollie Saunders
Great post onion2k, just tried that myself very cool. I never actually knew what prepaired statements looked like and did till now.