Code

Opened 8 years ago

Closed 6 years ago

Last modified 6 years ago

#1261 closed enhancement (invalid)

[patch] Firebird database backend

Reported by: anykey@… Owned by: i_i
Component: Database layer (models, ORM) Version: master
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: UI/UX:

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@… 8 years ago.
just unzip this on django/db/backeds
django_firebird_rev3077.patch (10.9 KB) - added by david@… 8 years ago.
boulder-oracle-sprint-firebird.zip (18.1 KB) - added by david@… 7 years ago.
boulder-oracle-sprint-firebird-rev4212.patch (3.8 KB) - added by david@… 7 years ago.
boulder-oracle-sprint-firebird-rev4309.patch (3.9 KB) - added by david@… 7 years ago.
updated patch
boulder-oracle-sprint-firebird-rev4456.patch (5.7 KB) - added by david@… 7 years ago.
New patch
boulder-oracle-sprint-firebird-rev4456.diff (6.5 KB) - added by david@… 7 years ago.
Updated patch
release-0.96-firebird.diff (31.8 KB) - added by david@… 7 years ago.
Patch against 0.96 with backend included
firebird-port-svn-trunk-6186-not-completed.diff (30.2 KB) - added by Almad 7 years ago.
Partially ported patch to django trunk
firebird_backend.tar.gz (9.1 KB) - added by i_i 6 years ago.
updated firevird backend
firebird_backend_patch_6650.diff (9.9 KB) - added by i_i 6 years ago.
updated firevird backend patch
firebird-svn6652-full.diff (42.5 KB) - added by i_i 6 years ago.
Firebird database backend working with svn django full patch
firebird-svn6652-full-updated.diff (45.8 KB) - added by i_i 6 years ago.
some more fixes
firebird-svn6654.diff (54.3 KB) - added by i_i 6 years ago.
a lot of fixes and improvements for firebird backend
firebird-6660.diff (74.9 KB) - added by i_i 6 years ago.
All tests passed
firebird-6668.diff (74.8 KB) - added by i_i 6 years ago.
small fixes
firebird-6669.diff (86.3 KB) - added by i_i 6 years ago.
code clean up and support for stored procedures and computed columns
firebird-6669-update.diff (98.2 KB) - added by i_i 6 years ago.
django-on-fire patch against django svn 6669
firebird_backend_fixcase.zip (31.1 KB) - added by cristofaro,campagna@… 6 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)

Changed 8 years ago by david@…

just unzip this on django/db/backeds

Changed 8 years ago by david@…

comment:1 Changed 8 years ago by 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.

comment:2 Changed 8 years ago by adrian

  • Summary changed from backend for firebird to [patch] Firebird database backend

Changed 7 years ago by david@…

Changed 7 years ago by david@…

comment:3 Changed 7 years ago by 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.

Changed 7 years ago by david@…

updated patch

comment:4 Changed 7 years ago by SmileyChris

  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to Accepted

Changed 7 years ago by david@…

New patch

comment:5 Changed 7 years ago by 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 _

comment:6 Changed 7 years ago by bugs@…

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

Changed 7 years ago by david@…

Updated patch

comment:7 Changed 7 years ago by 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 :)

comment:8 Changed 7 years ago by sydneyfong@…

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 Changed 7 years ago by 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):
     """

}}}

comment:10 Changed 7 years ago by 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.

comment:11 Changed 7 years ago by 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

Changed 7 years ago by david@…

Patch against 0.96 with backend included

comment:12 follow-up: Changed 7 years ago by 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.

comment:13 in reply to: ↑ 12 Changed 7 years ago by 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.

comment:14 follow-up: Changed 7 years ago by 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)

comment:15 in reply to: ↑ 14 Changed 7 years ago by 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.

comment:16 Changed 7 years ago by 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

comment:17 follow-up: Changed 7 years ago by david

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

comment:18 in reply to: ↑ 17 ; follow-up: Changed 7 years ago by 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.

comment:19 in reply to: ↑ 18 ; follow-up: Changed 7 years ago by 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.

comment:20 in reply to: ↑ 19 Changed 7 years ago by davidelias

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 Changed 7 years ago by anonymous

  • Owner changed from nobody to anonymous
  • Status changed from new to assigned

comment:22 Changed 7 years ago by Almad

  • Owner changed from anonymous to Almad
  • Status changed from assigned to new

comment:23 Changed 7 years ago by Almad

  • Status changed from new to assigned

comment:24 Changed 7 years ago by Almad

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

comment:25 Changed 7 years ago by PhiR

  • Keywords feature added

Changed 7 years ago by Almad

Partially ported patch to django trunk

comment:26 Changed 7 years ago by 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.

comment:27 Changed 6 years ago by i_i

  • Needs tests set
  • Patch needs improvement unset
  • Version set to 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.

Changed 6 years ago by i_i

updated firevird backend

Changed 6 years ago by i_i

updated firevird backend patch

comment:28 Changed 6 years ago by brosner

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!

Changed 6 years ago by i_i

Firebird database backend working with svn django full patch

Changed 6 years ago by i_i

some more fixes

comment:29 Changed 6 years ago by davidelias

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 Changed 6 years ago by davidelias

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.

Changed 6 years ago by i_i

a lot of fixes and improvements for firebird backend

comment:31 Changed 6 years ago by i_i

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 Changed 6 years ago by i_i

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 Changed 6 years ago by davidelias

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>

Changed 6 years ago by i_i

All tests passed

comment:34 Changed 6 years ago by i_i

  • Cc ivan.illarionov@… added; larlet@… removed
  • Needs documentation set
  • Needs tests unset
  • Resolution set to fixed
  • Status changed from assigned to closed

comment:35 Changed 6 years ago by i_i

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 Changed 6 years ago by ubernostrum

  • Resolution fixed deleted
  • Status changed from closed to reopened

comment:37 Changed 6 years ago by ubernostrum

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

Changed 6 years ago by i_i

small fixes

comment:38 Changed 6 years ago by i_i

ok

Changed 6 years ago by i_i

code clean up and support for stored procedures and computed columns

comment:39 Changed 6 years ago by i_i

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 Changed 6 years ago by i_i

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 Changed 6 years ago by i_i

  • Owner changed from Almad to i_i
  • Status changed from reopened to new

comment:42 Changed 6 years ago by i_i

  • Status changed from new to assigned

comment:43 Changed 6 years ago by 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!

comment:44 Changed 6 years ago by 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.

comment:45 follow-up: Changed 6 years ago by 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.

Changed 6 years ago by i_i

django-on-fire patch against django svn 6669

comment:46 Changed 6 years ago by 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 _

comment:46 Changed 6 years ago by 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 _

comment:47 Changed 6 years ago by 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).

comment:48 Changed 6 years ago by jacob

  • Resolution set to invalid
  • Status changed from assigned to closed

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

comment:49 in reply to: ↑ 45 ; follow-up: Changed 6 years ago by 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.

comment:50 in reply to: ↑ 49 Changed 6 years ago by i_i

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 Changed 6 years ago by i_i

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

Changed 6 years ago by cristofaro,campagna@…

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.