Wahoo! I did it! The last part of a WP-DB.PHP for Interbase/Firebird

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!

7 Comments

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.