Opened 18 years ago

Closed 16 years ago

Last modified 16 years ago

#1261 closed enhancement (invalid)

[patch] Firebird database backend

Reported by: anykey@… Owned by: Ivan Illarionov
Component: Database layer (models, ORM) Version: dev
Severity: normal Keywords: firebird database backend feature
Cc: ivan.illarionov@… Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (19)

firebird_backend.zip (4.3 KB ) - added by david@… 18 years ago.
just unzip this on django/db/backeds
django_firebird_rev3077.patch (10.9 KB ) - added by david@… 18 years ago.
boulder-oracle-sprint-firebird.zip (18.1 KB ) - added by david@… 17 years ago.
boulder-oracle-sprint-firebird-rev4212.patch (3.8 KB ) - added by david@… 17 years ago.
boulder-oracle-sprint-firebird-rev4309.patch (3.9 KB ) - added by david@… 17 years ago.
updated patch
boulder-oracle-sprint-firebird-rev4456.patch (5.7 KB ) - added by david@… 17 years ago.
New patch
boulder-oracle-sprint-firebird-rev4456.diff (6.5 KB ) - added by david@… 17 years ago.
Updated patch
release-0.96-firebird.diff (31.8 KB ) - added by david@… 17 years ago.
Patch against 0.96 with backend included
firebird-port-svn-trunk-6186-not-completed.diff (30.2 KB ) - added by Almad 16 years ago.
Partially ported patch to django trunk
firebird_backend.tar.gz (9.1 KB ) - added by Ivan Illarionov 16 years ago.
updated firevird backend
firebird_backend_patch_6650.diff (9.9 KB ) - added by Ivan Illarionov 16 years ago.
updated firevird backend patch
firebird-svn6652-full.diff (42.5 KB ) - added by Ivan Illarionov 16 years ago.
Firebird database backend working with svn django full patch
firebird-svn6652-full-updated.diff (45.8 KB ) - added by Ivan Illarionov 16 years ago.
some more fixes
firebird-svn6654.diff (54.3 KB ) - added by Ivan Illarionov 16 years ago.
a lot of fixes and improvements for firebird backend
firebird-6660.diff (74.9 KB ) - added by Ivan Illarionov 16 years ago.
All tests passed
firebird-6668.diff (74.8 KB ) - added by Ivan Illarionov 16 years ago.
small fixes
firebird-6669.diff (86.3 KB ) - added by Ivan Illarionov 16 years ago.
code clean up and support for stored procedures and computed columns
firebird-6669-update.diff (98.2 KB ) - added by Ivan Illarionov 16 years ago.
django-on-fire patch against django svn 6669
firebird_backend_fixcase.zip (31.1 KB ) - added by cristofaro,campagna@… 16 years ago.
fix bug into introspection.py. Now this script fetch field in upper case(in line with standard firebird tables schema)

Download all attachments as: .zip

Change History (71)

by david@…, 18 years ago

Attachment: firebird_backend.zip added

just unzip this on django/db/backeds

by david@…, 18 years ago

comment:1 by david@…, 18 years ago

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.

comment:2 by Adrian Holovaty, 18 years ago

Summary: backend for firebird[patch] Firebird database backend

by david@…, 17 years ago

comment:3 by david@…, 17 years ago

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.

by david@…, 17 years ago

updated patch

comment:4 by Chris Beaven, 17 years ago

Patch needs improvement: set
Triage Stage: UnreviewedAccepted

by david@…, 17 years ago

New patch

comment:5 by david@…, 17 years ago

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 _

comment:6 by bugs@…, 17 years ago

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

by david@…, 17 years ago

Updated patch

comment:7 by david@…, 17 years ago

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 :)

comment:8 by sydneyfong@…, 17 years ago

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

I'm using:

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

comment:9 by sydneyfong@…, 17 years ago

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):
     """

}}}

comment:10 by david@…, 17 years ago

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.

comment:11 by sydneyfong@…, 17 years ago

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

by david@…, 17 years ago

Attachment: release-0.96-firebird.diff added

Patch against 0.96 with backend included

comment:12 by david@…, 17 years ago

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.

in reply to:  12 comment:13 by anonymous, 17 years ago

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.

comment:14 by Almad <bugs@…>, 17 years ago

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)

in reply to:  14 comment:15 by david@…, 17 years ago

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.

comment:16 by Ashley, 17 years ago

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

comment:17 by David Larlet, 17 years ago

Cc: larlet@… added

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.)

in reply to:  17 ; comment:18 by Almad, 17 years ago

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.

in reply to:  18 ; comment:19 by anonymous, 17 years ago

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.

in reply to:  19 comment:20 by David Elias, 17 years ago

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 :)

comment:21 by anonymous, 17 years ago

Owner: changed from nobody to anonymous
Status: newassigned

comment:22 by Almad, 17 years ago

Owner: changed from anonymous to Almad
Status: assignednew

comment:23 by Almad, 17 years ago

Status: newassigned

comment:24 by Almad, 17 years ago

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

comment:25 by Philippe Raoult, 17 years ago

Keywords: feature added

by Almad, 16 years ago

Partially ported patch to django trunk

comment:26 by Almad, 16 years ago

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.

comment:27 by Ivan Illarionov, 16 years ago

Needs tests: set
Patch needs improvement: unset
Version: SVN

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.

by Ivan Illarionov, 16 years ago

Attachment: firebird_backend.tar.gz added

updated firevird backend

by Ivan Illarionov, 16 years ago

updated firevird backend patch

comment:28 by Brian Rosner, 16 years ago

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!

by Ivan Illarionov, 16 years ago

Attachment: firebird-svn6652-full.diff added

Firebird database backend working with svn django full patch

by Ivan Illarionov, 16 years ago

some more fixes

comment:29 by David Elias, 16 years ago

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 :)

comment:30 by David Elias, 16 years ago

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.

by Ivan Illarionov, 16 years ago

Attachment: firebird-svn6654.diff added

a lot of fixes and improvements for firebird backend

comment:31 by Ivan Illarionov, 16 years ago

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"

comment:32 by Ivan Illarionov, 16 years ago

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

comment:33 by David Elias, 16 years ago

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>

by Ivan Illarionov, 16 years ago

Attachment: firebird-6660.diff added

All tests passed

comment:34 by Ivan Illarionov, 16 years ago

Cc: ivan.illarionov@… added; larlet@… removed
Needs documentation: set
Needs tests: unset
Resolution: fixed
Status: assignedclosed

comment:35 by Ivan Illarionov, 16 years ago

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 :)

comment:36 by James Bennett, 16 years ago

Resolution: fixed
Status: closedreopened

comment:37 by James Bennett, 16 years ago

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

by Ivan Illarionov, 16 years ago

Attachment: firebird-6668.diff added

small fixes

comment:38 by Ivan Illarionov, 16 years ago

ok

by Ivan Illarionov, 16 years ago

Attachment: firebird-6669.diff added

code clean up and support for stored procedures and computed columns

comment:39 by Ivan Illarionov, 16 years ago

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)

comment:40 by Ivan Illarionov, 16 years ago

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

comment:41 by Ivan Illarionov, 16 years ago

Owner: changed from Almad to Ivan Illarionov
Status: reopenednew

comment:42 by Ivan Illarionov, 16 years ago

Status: newassigned

comment:43 by Jacob, 16 years ago

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!

comment:44 by Jacob, 16 years ago

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.

comment:45 by Ivan Illarionov, 16 years ago

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.

by Ivan Illarionov, 16 years ago

Attachment: firebird-6669-update.diff added

django-on-fire patch against django svn 6669

comment:46 by Almad <bugs@…>, 16 years ago

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

But thanks for the patch _

comment:46 by Almad <bugs@…>, 16 years ago

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

But thanks for the patch _

comment:47 by Almad <bugs@…>, 16 years ago

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).

comment:48 by Jacob, 16 years ago

Resolution: invalid
Status: assignedclosed

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

in reply to:  45 ; comment:49 by Almad, 16 years ago

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.

in reply to:  49 comment:50 by Ivan Illarionov, 16 years ago

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.

comment:51 by Ivan Illarionov, 16 years ago

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

by cristofaro,campagna@…, 16 years ago

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

Note: See TracTickets for help on using tickets.
Back to Top