[patch] Firebird database backend

[anykey@…]

I would really like to see a firebird backend for django.

If I have the time I do it myself, but at the moment I have no spare time for it.

Jürgen

[david@…]

just unzip this on django/db/backeds

[david@…]

Just attached the firebird backend and a patch file.
Firebird has a problem, names can't bigger than 31 chars for tables, fields, constraints, etc.
In auth app needed to change user_premissions to permissions so the many to many table name doens't get too big... must be a way we can workaround this...

Another thing, i just got a testing project working installing the apps one by one, with syncdb, somehow, not all the generators/sequences are being created.

It passed the majority of tests, again one by one as i needed to change the tables names in Meta class for some models. A relevant test failure was in lookups tests with 'startswith'.

This should not be used for production, please test.

[david@…]

Added a revised firebird backend and a patch to work with the oracle branch.
I had to disable the redirects app from tests because the index key size was exceeding the 256 bytes allowed in Firebird 1.5, it failed 5 tests.

I'm using the oracle branch in a production ready web application with no problems, so far, but test it.

Also I need help with the creation module, some how during tests the test db doesn't get deleted.

[david@…]

updated patch

[david@…]

New patch

[david@…]

I was trying to upload the new revised firebird backend but trac throws an error... it was an zip file.
You can grab it ​here

Now it only fails 3 tests, in the modeltests/basic

Failed example:
    Article.objects.all()[0:5][5:]
Expected:
    []
Got:
    [<Article: Fourth article>, <Article: Article 7>, <Article: Updated article 8>]

And 2 errors in many to many tests, with messages like this

ProgrammingError: (-803, 'isc_dsql_execute: \n  violation of PRIMARY or UNIQUE KEY constraint "INTEG_328" on table "M2M_RECURSIVE_PERSON_FRIENDS"')

If anyone is using this patch and backend please test and help me on this errors ^_

[bugs@…]

just +1, there are really people outside crying for seeing Firebird support in trunk

[david@…]

Updated patch

[david@…]

Updated the patch against oracle branch rev.4456 and improved a little bit introspection, you can grab the backend module ​here, trac just throws an error if try to atttach an .zip or .tar file.

Now all tests pass. It seems that kinterbasdb cursor.rowcount is a little quirk, you can check the ​docs, that's why m2m tests were failling rowcount was always returning 0.

I'm using in a production server and so far is very stable, please test :)

[sydneyfong@…]

I had to make the following tweaks to make things work.

I'm using:

• firebird from Debian Etch (1.5.3.4870-12)
• kinterbasdb-3.2 from official upstream source
• django oracle branch rev.4456
• ​http://media.david-elias.net/django/firebird-backend.zip

The following patch aims to fix the problem that firebird barfs when it receives a timestamp of subsecond precision greater than 4, and when a column has the "NULL" constraint during a CREATE TABLE.

Disclaimer: I'm an absolute newbie to firebird and have just started looking at it today.

Sidney.

​http://files.sidney.hk/shameless_plug/django-firebird.0.patch

[sydneyfong@…]

Whoops. missing is also a patch to the author's firebird backend patch:

diff -urN firebird/base.py firebird-orig/base.py
--- firebird/base.py    2007-04-16 17:33:23.000000000 +0800
+++ firebird-orig/base.py       2007-04-16 17:33:59.000000000 +0800
@@ -71,7 +71,6 @@
 needs_datetime_string_cast = False
 supports_constraints = True
 uses_case_insensitive_names = True
-allow_null_column_constraint = False
 
 class FormatPlaceholderStylerCursor(Database.Cursor):
     """

}}}

[david@…]

Hi, Sydney

Glad you're using and testing.

I'm working in a new patch against 0.96 release not the oracle branch.
The problem with timestamp subsecond greater than 4 i've resolved with kinterbasdb type_converters, so i removed all the "if settings.DATABASE_ENGINE == 'firebird'" from "db.models.fields".
Regarding the NULL constraint in the above patch is resolved with "'%sNULL' % (not f.null and 'NOT ' or 'DEFAULT ')", or am i missing something?

I'll post the new patch in a couple of days.

[sydneyfong@…]

Hi David,

Regarding the NULL constraint, shouldn't "DEFAULT" belong to somewhere else? What if a column can be null, but has a non-null default value?

eg.

CREATE TABLE test (a INT DEFAULT 1);

would your code generate something like this?

CREATE TABLE test (a INT DEFAULT 1 DEFAULT NULL);

Sidney

[david@…]

Patch against 0.96 with backend included

[david@…]

Sidney,

You absolutely right about, i've changed the new patch to reflect that.

The new patch works with the 0.96 release, i could remove all "if settings.DATABASE_ENGINE == 'firebird'" if the oracle branch was used, but right now the trunk is not recommendable according to django devs.

The test suite reports 1 failure and 1 error:

======================================================================
ERROR: Doctest: regressiontests.forms.tests
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/elias/Projectos/FirebirdDjangoSupport/test/django/test/doctest.py", line 2151, in runTest
    test, out=new.write, clear_globs=False)
  File "/home/elias/Projectos/FirebirdDjangoSupport/test/django/test/doctest.py", line 1379, in run
    return self.__run(test, compileflags, out)
  File "/home/elias/Projectos/FirebirdDjangoSupport/test/django/test/doctest.py", line 1292, in __run
    self.report_failure(out, test, example, got)
  File "/home/elias/Projectos/FirebirdDjangoSupport/test/django/test/doctest.py", line 1160, in report_failure
    self._checker.output_difference(example, got, self.optionflags))
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc5 in position 245: ordinal not in range(128)

======================================================================
FAIL: test_templates (regressiontests.templates.tests.Templates)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/elias/Projectos/FirebirdDjangoSupport/test/tests/regressiontests/templates/tests.py", line 754, in test_templates
    self.assertEqual(failures, [], '\n'.join(failures))
AssertionError: Template test (TEMPLATE_STRING_IF_INVALID=''): basic-syntax37 -- FAILED. Got <type 'exceptions.UnicodeEncodeError'>, exception: 'latin-1' codec can't encode characters in position 0-7: ordinal not in range(256)
Template test (TEMPLATE_STRING_IF_INVALID='INVALID'): basic-syntax37 -- FAILED. Got <type 'exceptions.UnicodeEncodeError'>, exception: 'latin-1' codec can't encode characters in position 0-7: ordinal not in range(256)

----------------------------------------------------------------------
Ran 103 tests in 58.960s

FAILED (failures=1, errors=1)

But don't know where too look to resolve these.

Another thing, TextField/BLOB are not searchable in Firebird 1.5 and 2.0. If we use "CONTAINING" the column index is not used and we loose case sensitive. I've tried to indicate the field type so we could do a special search for TextField's, but i'll wait for the query.py refactoring.
In Firebird 2.1 BLOB's TEXT will work like VARCHARS, but it's still in alpha testing.

If anyone has any suggestion about the test errors and textfield's, please help out.

[anonymous]

Let me correct myself:

Another thing, TextField/BLOB are not searchable in Firebird 1.5 and 2.0.

should be:
Another thing, TextField/BLOB are not case insentive searchable with Firebird 1.5 and 2.0.

[Almad <bugs@…>]

About test errors, try

import kinterbasdb
kinterbasdb.init(concurrency_level=2, type_conv=200)

About searchables, are they required for making it in trunk?

(BTW, if there is help needed, I'd like to contribute on this patch as firebird support in trunk is beginning to be important for me)

[david@…]

Replying to Almad <bugs@almad.net>:

About test errors, try

import kinterbasdb
kinterbasdb.init(concurrency_level=2, type_conv=200)

Thanks :)

Initially it was like that, but type_conv=200 converts fixed point values to Decimal, given an error in serialization on 0.96.
But now in trunk that the Decimal is supported and we have now a new DecimalField, it should be type_conv=200.

About searchables, are they required for making it in trunk?

Well, I guess that now the oracle branch has been merged to trunk is the right time write down what features Firebird has and what Django needs and the needed workarounds.

(BTW, if there is help needed, I'd like to contribute on this patch as firebird support in trunk is beginning to be important for me)

Thanks, for the next couple weeks I'm occupied with work, if you wanna you could try apply this patch to the trunk.

[Ashley]

Hi, I've tried using the latest firebird backend, just following the django tutorial at the moment (​http://www.djangoproject.com/documentation/tutorial01/) - and I get an error when attempting to sync the db. I've not created anything custom whatsoever. It fails with a kinterbasdb Programming Error "Name longer than database column size". Has anyone encountered this before?

Cheers
Ashley

[David Larlet]

Added myself as cc, btw, what is the status of this two years old (!) enhancement? Maybe the sprint could be a good day to end that? (Disclaimer: I didn't know Firebird until today when a client ask me about this database.)

[Almad]

Replying to david:

Added myself as cc, btw, what is the status of this two years old (!) enhancement? Maybe the sprint could be a good day to end that? (Disclaimer: I didn't know Firebird until today when a client ask me about this database.)

If noone else, I'm planning to update patch and rich trunk in the sprint.

[anonymous]

Replying to Almad:

If noone else, I'm planning to update patch and rich trunk in the sprint.

I'm with you, will you be online most likely friday nigth and saturday.

[David Elias]

Replying to anonymous:

Replying to Almad:

If noone else, I'm planning to update patch and rich trunk in the sprint.

I'm with you, will you be online most likely friday nigth and saturday.

I'm the anonymous :)

[Almad]

Have to go early :( If noone will complete it, i'll work on it next week, but for now, I must go :]

[Almad]

Partially ported patch to django trunk

[Almad]

Sorry for long response, but I ran out of spare time :]

Posting patch I ported now. Tests are running, but lot's are failing and I haven't time to inspect API changes deep enough to determine why.

If someone would take a look, it will be fine.

[Ivan Illarionov]

Based on previous patches. Some code is completely rewritten.
Seems to work fine with Firebird 2.0 and 1.5 and latest SVN.
Needs latest kinterbasdb (3.2)
Changed contrib.auth and contrib.contenttypes models to make indexes < 252 bytes
Uses custom QuerySet to handle limits.

[Ivan Illarionov]

updated firevird backend

[Ivan Illarionov]

updated firevird backend patch

[Brian Rosner]

i_i,

Please attach just one diff that includes all the files. To include a file in a diff that is not versioned use svn add. Thanks!

[Ivan Illarionov]

Firebird database backend working with svn django full patch

[Ivan Illarionov]

some more fixes

[David Elias]

Nice patch i_i,

Regarding NULL fields, like sydney pointed above if you want a null column in firebird you simply ommit the declaration. But i could be missing something :)

[David Elias]

Another thing,

# Trancate and quote once. No need for uppercase since
# we quote autofields too

Is this because in models/base.py autofields aren't being quoted? Maybe is some fetaure/bug from django. Now if you declare "some_field" you can't write sql with this field unquoted because it will default to uppercase.

[Ivan Illarionov]

a lot of fixes and improvements for firebird backend

[Ivan Illarionov]

David,

As I know, omitting the declaration and declaring DEFAULT NULL does the same. DEFAULT NULL just looks a bit clearer and "djangy".
As for quotes, it looked very ugly when all table names was UPPERCASE (as it is now in oracle backend). Firebird allows table and column names to be in any case, but requires quotes everywhere. So I added quote_autofields feature, and patched models.base. Now it works nice.

Even more, workaround with uppercasing in quote_name (as it was in previous attends in firebird and is in oracle backend now) is not only ugly, but is really a BUG.

e.g. try to create something like this in official tutorial:

pub_date = models.DateTimeField('date published')
Pub_Date = models.DateTimeField('date published test')

Backend SHOULD create two coloumns, like firebird does now:

CREATE TABLE "polls_poll" (
    "id" integer NOT NULL PRIMARY KEY,
    "question" varchar(200) NOT NULL,
    "Pub_Date" timestamp NOT NULL,
    "pub_date" timestamp NOT NULL
);

but with uppercase it will try to create:

CREATE TABLE "POLLS_POLL" (
    "ID" integer NOT NULL PRIMARY KEY,
    "QUESTION" varchar(200) NOT NULL,
    "PUB_DATE" timestamp NOT NULL,
    "PUB_DATE" timestamp NOT NULL
);

that brings the error like:

unsuccessful metadata update

STORE RDB$RELATION_FIELDS failed
attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_15"

[Ivan Illarionov]

In my patches following solutions/enhancements are working:

1. Table and column names now don't have to be in UPPERCASE (while generators and triggers are -- and that's right -- just like in any GUI admin tool for FB)
   
2. CHECKs are working with Boolean and Positive fields
   
3. Native FB lookups with STARTING WITH for Django's startswith and istartswith and CONTAINIG for icontains
   
4. Patched missing quotes throughout the Django code
   
5. Solved foreign key problems with ops.sql_flush and other delete/update related issues.
   
6. ops.sequence_reset_sql now works as expected with custom stored procedure
   
7. Added support for UTF8 charset (FB2.0+). UNICODE_FSS works good with any version and uses less memory. This backend looks to be more stable with non-ASCII characters (I tried different charsets as experiment) than current MySQL backend. At least on my shared hosing MySQL/Django don't work with cyrillic, while FB/Django works fine.
   
8. Custom QuerySet class handles limits.
   
9. Backend will check for 252 bytes index limit (FB1.5) (or 1/4 page size limit in FB2.0+) in core.management.sql.sql_model_create

and change indexed columns to 1-byte charset and limit the maximum field length to fit current limitations. Only if needed. Nice WARNING will be printed if it happen. It won't in FB2.0 with page size > 4096. Anyway, it's not good idea to have non-ASCII large text indexes.

Most of the tests are running. Few are still failing. I hope that I can fix that soon. And maybe add something else to make this backend the fastest and most feature-rich of Django DB backends.

I think it should be in the trunk. It's more stable than some other backends already.

Cheers,
Ivan

[David Elias]

That's really a nice improvement Ivan.

<mini-rant>
Only one thing that's seems hackish to me is how django backend's are organised. We must always do "if this backend", "if connection.ops.supports_this". I know some efforts to ease this were made and Malcolm is working on queryset-refactor, that, i think, only evolves SQL and no DDL.
SQLALchemy have a good aproach with visitors for all SQL/DDL creation.
</mini-rant>

[Ivan Illarionov]

All tests passed

[Ivan Illarionov]

It works great. And it's really different from other backends.
Now I am going to add support for some advanced stuff like fields using (COMPUTED BY) and model lookups based on stored procs :)

[James Bennett]

Please don't close as "fixed" unless the code has actually been committed to Django trunk.

[Ivan Illarionov]

small fixes

[Ivan Illarionov]

ok

[Ivan Illarionov]

code clean up and support for stored procedures and computed columns

[Ivan Illarionov]

Added support for stored procedures (you can use them as normal model methods)

demonstration in tests/modeltests/custom_methods/models.py

and computed columns:

class Article(models.Model):
    headline = models.CharField(max_length=100)
    rating_one = models.IntegerField()
    rating_two = models.IntegerField()
    rating_all = models.ComputedField(expression='%s + %s', params=('rating_one', 'rating_two'))
    rating_avg = models.ComputedField(expression='(%s + %s)/2', params=('rating_one', 'rating_two'))

# Create an article
>>> art = Article(headline='Django lets you build web apps easily', rating_one=2, rating_two=3)
>>> art.save()

# Get an article from database to get calculated values
>>> art = Article.objects.get(pk=1)
>>> art.rating_all
5
>>> art.rating_avg
2

# Change values
>>> art.rating_one = 10
>>> art.rating_two = 20
>>> art.save()
# Need to read values again to get calculated fields
>>> art = Article.objects.get(pk=1)
>>> art.rating_avg
15
>>> art.rating_all
30

You can also set custom encoding of Char and Text fields if you want to (with encoding= keyword argument)

[Ivan Illarionov]

Please use 'FB_DJANGO_TEST_MODE=2 python runtests.py' to turn off strict FK constraints and turn on ON DELETE CASCADE while testing

[Jacob]

This is far too big a patch/change for our existing infrastructure to handle.

Luckily, there's a better way: turn this firebird backend into an external project (hosted on Google Code, perhaps). Once it's bulletproof, clearly stable, and has active maintainers you can propose addition into Django proper (do so on django-dev).

See ​http://www.pointy-stick.com/blog/2007/11/11/django-tip-external-database-backends/ and ​http://www.djangoproject.com/documentation/settings/#database-engine for information about how to turn this backend into an external project.

Once you've moved this externally, please close the ticket with a link to the new home for the firebird backend.

Thanks!

[Jacob]

Oh, and one more thing:

Things like the ComputedField do not belong in a database backend and will prevent a change like this from ever getting in. We try to make the smallest change possible when introducing new features like this. {{{ComputedField}} should be a separate feature/patch.

[Ivan Illarionov]

Thanks! I will follow your advise and create separate Django/Firebird project. There are already many new features like full integration of stored procedures and triggers with Django models and I want even more. I'll post the last patch here for interested people and close this ticket as soon as my project will have its home.

[Ivan Illarionov]

django-on-fire patch against django svn 6669

[Almad <bugs@…>]

Just to report, latest diff is missing db/backends/firebird/__init__.py, so it won't get imported.

But thanks for the patch ^_

[Almad <bugs@…>]

Just to report, latest diff is missing db/backends/firebird/__init__.py, so it won't get imported.

But thanks for the patch ^_

[Almad <bugs@…>]

Don't know why, but varchar limit is 8191 on my system (Gentoo Linux with Firebird 2.0.3.12981.0-r2), instead of provided 10922 (= 32767/4 instead of 32767/3, which probably makes sense for utf8).

[Jacob]

I'm marking this closed so it can be moved externally -- see my comment above.

[Almad]

Replying to i_i:

Thanks! I will follow your advise and create separate Django/Firebird project. There are already many new features like full integration of stored procedures and triggers with Django models and I want even more. I'll post the last patch here for interested people and close this ticket as soon as my project will have its home.

Hi, how is it looking with external project creation?

I'd like to see it working ;) If needed, I can support it by providing trac/mercurial/svn hosting.

[Ivan Illarionov]

Hi, how is it looking with external project creation?

I'd like to see it working ;) If needed, I can support it by providing trac/mercurial/svn hosting.

I'm working on it. I don't want to put serious efforts on this project until queryset-refactor branch become official. And, as soon as I have a working external project, I'll post about it here or on the Django group mailing-list.

[Ivan Illarionov]

moved to ​http://code.google.com/p/django-firebird/
Switched to future-proof queryset-refactor branch.

[cristofaro,campagna@…]

fix bug into introspection.py. Now this script fetch field in upper case(in line with standard firebird tables schema)