Wahoo! I did it! The last part of a WP-DB.PHP for Interbase/Firebird
April 1, 2007
I knew 9 years of IB/FB experience would come in handy.
wp-db is the mini database abstraction layer for WordPress. It allows you to run WordPress on various database. Right now MySQL is pretty much it, but some folks have been doing a PostgreSQL version as well.
Well, I wanted it to connect to an Interbase db for the .TEXT blog conversion I’ve been doing. I’m not going to run WordPress off of Interbase yet, but it simplified the import for me to use WP-DB for my database import connection.
It’s interesting to note that I’ve come to the point where the other editors that I’ve been using for PHP code just don’t feel right anymore. Delphi for PHP just feels better now for editing. I wasn’t doing any visual designing in it, but it was still a nice quick editor with the additional help of hints and parameters. Maybe if I had Zend, I would stick with that for non visual stuff, if I were used to it, I don’t know. I don’t have it. I tried it once and found it too bulky for my needs. D4PHP with all of the design boxes closed, still feels light and has a quick open time.
Anyway, most of the conversion was pretty easy. I just had to substitute ibase_ php calls for mysql_ calls. I had to add a little cleanup for handling of empty strings and quotes and some reserved IB words like type, role, password, and etc. But the really problem was that WordPress relies heavily on the InsertID feature that many databases have. It returns the last value of the autoinc field in the table referenced by the last insert command. IB/FB has no equivelent. PHP documentation did not indicate any solution was provided. And all of the database abstration layers out there don’t support it either.
So, I’ve done a work around. It is possibly not 100% accurate for all databases configurations out there, but it should work for WordPress and .TEXT.
See, the trick was to find the primary key for the table you are working on. So, first I had to find what table I was working. I had the SQL INSERT statement, so all I needed to do was find the table name after the words “INSERT INTO “. Fine. There are a bunch of ways to do that.
Next, armed with the table name, I had to find the primary key. How do I do that with just the commands available to PHP? That was the tricky part.
Luckily, IB/FB is a relational database. That in short means the tables are all stored in bits and pieces throughout the database table and then linked back together according to the structure information stored in “hidden” tables.
The long and short of it is that this statement will give you what is needed:
[SQL]
select RDB$RELATION_CONSTRAINTS.RDB$RELATION_NAME, RDB$INDEX_SEGMENTS.RDB$FIELD_NAME, RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION
from RDB$RELATION_CONSTRAINTS, RDB$INDEX_SEGMENTS
where
RDB$RELATION_CONSTRAINTS.RDB$CONSTRAINT_TYPE = ‘PRIMARY KEY’ and
RDB$INDEX_SEGMENTS.RDB$INDEX_NAME=RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME
order by RDB$RELATION_CONSTRAINTS.RDB$RELATION_NAME, RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION
[/SQL]
In WordPress and .TEXT every AutoInc field I looked at was the first field in the table. So, now armed with the table name, and autoinc field, it is a simple query to get the highest value, which should be the most recent insert unless there’s been some kind of corruption.
So that’s just a select Max(fieldname) from tablename statment and I return the value I’ve found in field one (handling an empty result set of course).
Hey – It’s not pretty, but it should work 100% of the time for my uses. If it does, I’ll submit it to ADOdb and see what they think. Since ADOdb is a core part of the Delphi for PHP project, I might get my little bit of code included! Actually, I hope to contribute to the VCL for PHP long before then.
OK, everyone’s eyes have glazed over but it’s still pretty cool honest!
Comments
7 Responses to “Wahoo! I did it! The last part of a WP-DB.PHP for Interbase/Firebird”
Got something to say?
Brian,
1. Can you go over this statement again? “So that’s just a select Max(fieldname) from tablename statment and I return the value I’ve found in field one (handling an empty result set of course).” I’m not quite sure I follow you here.
2. Is there are way to find the GENERATOR for a specific primary key (and table) and then use “SELECT GEN_ID(name, increment) FROM RDB$DATABASE”? I believe that this is the preferred way to fetch an “autoincrement” field in InterBase and Firebird, as app.
BTW, I thought that this link on Lorenzo Alberton site was interesting => http://www.alberton.info/firebird_sql_meta_info.html
Tom
I’ll go from the bottom up in answering that.
First that’s a great link. Thanks! It is definately on my book mark list.
And YES, going after the generators is the right way to go. However, the generators themselves are not associated with the tables in any way I could find outside of the trigger code. Generators are not even required to be named after the field or table. Nor ar the triggers. Read here: http://www.delphi32.com/info_facts/faq/faq_987.asp
So for Interbase, the primary key is simply an interger field and a trigger of some name is added associated the insert action. That meant I would have had search the memo text for each trigger on the table and look for a generator reference. I probably could isolate the insert triggers… somehow and just search those. I’d have to assume the first Generator reference was indeed the one I wanted, but that would probably be a fair bet.
So, instead of doing that, I went after the primary key, which is what you see me doing in this example. So, what the query statement does is go after max value in the primary key field of the table I am working with. That should work most of the time but it is not guaranteed. Afterall the auto inc field COULD be the third field in the table.
Well, today is another day and I’ve thought on this further. I think the most correct way IS to retrieve the MEMO text and and parse it.
So what I’ll need to do is find select from RDB$TRIGGERS where RDB$RELATION_NAME=[The table name from the insert] and RDB$TRIGGER_TYPE=1. That shouldn’t be hard. Then I locate the first GEN_ID( and trim off up to the end of that. Then I need to look for a comma and cut off it and there after. Then I should have the name of the trigger. Then I can do a select statment, retrieving the trigger and I will have to specify that I want to Increment by 0.
Yeah, that actually should do it… Maybe I’ll get to work on that some more tomorrow… Thanks for asking the question! I aparently needed to revisit this after a break.
Why? For Delphi for PHP?
If your’e using the (new) Firebird 2, you can do this using the new returning clause for insert statements. For more info, read the page 39 of the release notes of the 2.0.0 or 2.0.1 release.
jachguate! Thank you! I will actually make a second conversion module for FB2+. I’d totally missed that feature.
Unfortunately, the company I am doing this for has rather strong feelings on the IB/FB debate!