Hello, > I have never used github before so yeah, i'm unfamiliar with the workflow > and terminologies used.
OK, sending files is fine with me, too. > I was slogging through with porting the SQLite DDL to Firebird on the > weekend, but i have this nagging feeling that i'm really doing this wrong > due to my unfamiliarity with jOOQ internals. I understand. Adding a new dialect is non-trivial, unfortunately. > Also some of the issues i encountered: > 1. Firebird doesn't support "Identity" column directly. This can be > simulated with "Before Insert" triggers and Generator values (a.k.a. > SEQUENCES) > http://www.firebirdfaq.org/faq29/ Yes, I've noticed that. I'll have to think about how to handle identities in Firebird. I guess some inspiration can be taken from Oracle, which doesn't support identity columns either. > 2. SEQUENCEs are supported in firebird 2.0 onwards, but i'm not certain Yes, they are. The latest version on GitHub uses them already > 3. "DROP TABLE IF EXISTS" not supported. There are work arounds, but it's a > bit icky. Removed those from the script for now. True. jOOQ's integration tests try to run DROP TABLE scripts and fail silently, if the table doesn't exist. I've added a check for that. > 4. AFTER INSERT triggers that modify the just-inserted-records doesn't > really make sense. > etc. True. I think that was the only way I could get a unified behaviour for SQLite's version of the t_triggers test table. > In any case, i've uploaded the files here > https://dl.dropbox.com/u/22507462/jOOQ/jooq_fb2_20120820.7z Thanks! I'll check them out ASAP, this week. > Please let me know what to do next. I will integrate your code generation logic with my test database, and then I'll get in touch with you, should there be any questions. In fact, there is one issue that's causing me some headaches: Firebird is one of those databases that requires about as much bind variable casting as Derby and DB2 (see this post for details: http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness/). At the same time, Firebird has a weird limit of around 64kb for various block sizes, including the total of all involved varchar objects: http://www.firebirdfaq.org/faq299/ This is a dilemma for jOOQ. Casting all varchar bind values to cast(? as varchar(32765)) will quickly cause that limit to be reached. Casting them to something less, such as cast(? as varchar(4000)) will impose limits on client applications. Not casting them will cause syntax errors in various occasions. Do you have any hints for me? How would this be resolved when querying Firebird with JDBC directly? > PS: How does jOOQ plan to deal with different dialects/versions within a > particular database server? > For example, firebird 2.x supports a lot of new constructs that were not > supported by firebird 1.x. There are even differences between 2.0.x vs 2.1.x > vs 2.5.x release. > I don't think many people are still using firebird 1.x and 2.0.x > (end-of-lifed already) these days, so the concern is more about 2.1.x (still > supported) and 2.5.x (current stable release) vs 3.0 (soon to be alpha) I am aware of this problem. Every database has it. I have a pending feature request #552 concerning this topic: https://github.com/jOOQ/jOOQ/issues/552 Essentially, I want to introduce something like a SQLFamily object, which is referenced by SQLDialect. SQLDialect.FIREBIRD would always be the latest version of the SQLFamily.FIREBIRD family, whereas SQLDialect.FIREBIRD_2_5 could be used for backwards-compatibility. However, adding such support to jOOQ would require quite a bit of effort on my side, in order to properly support several versions of a database. Hence, currently, I'm restricting official support to only one version of each database: http://www.jooq.org/#What-databases-are-supported Stay tuned to see if I manage to resolve this in jOOQ 3.0 by the end of 2012... Cheers Lukas
