You are not logged in. Please login or register.
PunBB Forums → Feature requests → Support for Firebird SQL
Hi,
I was after a forum that supports Firebird SQL so as I can integrate with my website. I was told that PunBB had support for this, however, I have not been able to find it. Is this a planned feature for future versions or is there a mod by a 3rd party that supports Firebird.
rgds
Si
i dunno anything about Firebird SQL but you might be able to add support though the db classes, i've never seen anyone else want to use it or request it though
Thanks for the quick response, I would love to add support but my PHP knowledge is very flaky 
rgds
Si
Adding support for Firebird is something I have thought about before. It all depends on whether it has LIMIT/OFFSET support. If it doesn't, it's a LOT trickier.
Not sure what LIMIT/OFFSET is, if its to return a subset of records then you can use
SELECT FIRST n SKIP n blah blah
Otherwise let me know and I will find an equivalent. I would be more than happy to convert the database for you as well
rgds
Si
That is exactly what I meant. Because it's a different syntax than that of MySQL, PostgreSQL and SQLite, there will need to be some tricks involved, but it shouldn't be too difficult.
If your interested I have converted the database to Firebird (1.5 Dialect 3), there are a couple of changes you need to know about:
~ message is a reserved word so have changed all column names from message to msg
~ TEXT is not a valid FB datatype, have changed to VARCHAR (nnn) took best guess at size. This could have been a BLOB (sub_type TEXT).
~ Where date has been added I have changed data type to TIMESTAMP and default (where applicable) to 'NOW'
~ have re-added all index's and added triggers for Generators (AUTOINCREMENT)
~ Have added Foreign keys
~ Prefix for all objects (Tables, Generators, Index's, Triggers etc) have been set to PUNBB_
The script can be downloaded from http://www.tectsoft.net/download/punbb_fb.zip
rgds
Cool
~ message is a reserved word so have changed all column names from message to msg
Hmm, that I don't like. I was hoping it would be possible to do this without affecting the already existing database support. Bummer.
~ TEXT is not a valid FB datatype, have changed to VARCHAR (nnn) took best guess at size. This could have been a BLOB (sub_type TEXT).
OK. That's no problem.
~ Where date has been added I have changed data type to TIMESTAMP and default (where applicable) to 'NOW'
But isn't a timestamp updated whenever you update the row?
~ have re-added all index's and added triggers for Generators (AUTOINCREMENT)
OK, cool.
~ Have added Foreign keys
Not really needed, but still cool.
~ Prefix for all objects (Tables, Generators, Index's, Triggers etc) have been set to PUNBB_
Why not use the prefix that is used for tables?
keldar wrote:~ message is a reserved word so have changed all column names from message to msg
Hmm, that I don't like. I was hoping it would be possible to do this without affecting the already existing database support. Bummer.
You can get around this by adding dbl quotes to the column when defining, however it is then case sensitive and the case must be preserved in all selects, updates etc.
keldar wrote:~ Where date has been added I have changed data type to TIMESTAMP and default (where applicable) to 'NOW'
But isn't a timestamp updated whenever you update the row?
No, its just a datatype, it *could* be updated easily via the triggers if needed.
keldar wrote:~ Have added Foreign keys
Not really needed, but still cool.
This is done at the very end of the script, if its not necessary it can be easily omitted
keldar wrote:~ Prefix for all objects (Tables, Generators, Index's, Triggers etc) have been set to PUNBB_
Why not use the prefix that is used for tables?
I used that prefix when I created a db, you should be able to do a search/replace on PUNBB_ and all will be ok
I posted a duff script last time (sorry) the real one can be obtained from http://www.tectsoft.net/download/punbb.firebird.zip
rgds
You can get around this by adding dbl quotes to the column when defining, however it is then case sensitive and the case must be preserved in all selects, updates etc.
Case sensitivity is no problem. The fact that it uses doublequotes to escape field names is. I'm not sure MySQL, PostgreSQL and SQLite will buy that.
Rickard wrote:But isn't a timestamp updated whenever you update the row?
No, its just a datatype, it *could* be updated easily via the triggers if needed.
Ah, ok. I'm used to the TIMESTAMP datatype being automatically set on UPDATEs and INSERTs. Just goes to show how large the differences between different SQL implementations are.
You can create DOMIAN in firebired as TEXT
More conflicts
- unbuffered_query not found
- you cant seek result as in mysql or sqllite or pgsql
- rows effected for "select" query not supported in firebird 1.5
- getting insert_id not found becuase there is no AUTO_INCREMENT field type in firebird we generate it before inserting a row by user firebird gen_id function (long story) pgsql has sequence number for each table retrive it by table name is easy but this idea not work because you can use one sequence (generator) for more than one, there is open request for retrive an insert_id when inserting but in firebird 2.0, not released yet.
I stoped here.
You can create DOMIAN in firebired as TEXT
More conflicts
- unbuffered_query not found
- you cant seek result as in mysql or sqllite or pgsql
- rows effected for "select" query not supported in firebird 1.5
- getting insert_id not found becuase there is no AUTO_INCREMENT field type in firebird we generate it before inserting a row by user firebird gen_id function (long story) pgsql has sequence number for each table retrive it by table name is easy but this idea not work because you can use one sequence (generator) for more than one, there is open request for retrive an insert_id when inserting but in firebird 2.0, not released yet.I stoped here.
It might be worth asking some of these question on the FB-PHP list http://groups.yahoo.com/group/firebird-php
The autoinc is easily replicated, you can either grab the id using something like the following
SELECT GEN_ID(GeneratorName, 1) FROM RDB$DATABASE;
Or more typically use a stored procedure where you pass the input data in and it returns the id thats been generated.
rgds
Si
I know, i used in my projects.
SELECT GEN_ID(GeneratorName, 1) FROM RDB$DATABASE;
That mean we must use it in the first query to get a new id (for "post" by example) and then pass it to another query for INSERT a new record, two of queries VS one of it in mysql or sqllite.
https://sourceforge.net/tracker/?func=d … up_id=9028
keldar, In fact i have tried to make a new dblayer for punbb to supprt Firebird by replaceing sqlite layer because it more compatiple metadata, but i stucked with Firebird/Interbase not suppered with same function as like (mysql, pg, sqlite), for that i gived up (bad english 
) and waiting a miracle from firebird developers .
This is where stored procedures come in to play, I am more than willing to help (on the FB side) if you want.
Maybe Firebird SQL support could be added in PunBB 1.3?
Where ?
If punbb does not have support for firebird soon I will have finished a new firebird based forum. I would however prefer to have punbb support FB, but...
WHen makeing a new topic PunBB make INSERT then get the id of topic for INSERT the post messge, in FB we must take the ID then make the 2 INSERTs that mean we must change the code not DBLayer.
WHen makeing a new topic PunBB make INSERT then get the id of topic for INSERT the post messge, in FB we must take the ID then make the 2 INSERTs that mean we must change the code not DBLayer.
Stored procedures, will solve this problem straight off.
Is that mean stored procedure for every table?
Is that mean stored procedure for every table?
Not always, only if you have multiple updates where things like the ID is required for the next update
I was playing with Firebird/Interbase. I've made a punbb driver for it (alfa,alfa) but this DB is quite annoying. punBB in post table has "message" field and Firebird doesn't like such filed name etc. etc. I need to read some more docs and maybe then I'll manage to gef pun working 
very big maybe
Create new domian named as D_TEXT for (domain mean user field type)
CREATE DOMAIN D_TEXT AS BLOB SUB_TYPE TEXT;define message field as D_TEXT
PunBB Forums → Feature requests → Support for Firebird SQL
Powered by PunBB, supported by Informer Technologies, Inc.