Code

Opened 3 years ago

Closed 3 years ago

#16448 closed New feature (duplicate)

Single sequence for every table

Reported by: wiesiek@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Design decision needed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by aaugustin)

Hi,
I am a newbie in Django,
but IMHO in database layer design Django does not follow DRY principle.
I do not understand, why Django uses different sequence for every table.
Having single sequence for all tables is much better than having
sequences for different tables regarding
maintenance, sharding, security et.c. I give you and example package,
that can greatly simplify many mentioned tasks.
Just use django_seq as id generator on any django table

CREATE OR REPLACE PACKAGE django_seq IS

  FUNCTION NEXTVAL RETURN NUMBER;
  FUNCTION CURRVAL RETURN NUMBER;
END django_seq;
/
CREATE OR REPLACE PACKAGE BODY django_seq IS

  last_val NUMBER;
  shard_no NUMBER;

  FUNCTION NEXTVAL RETURN NUMBER IS
  BEGIN
    SELECT to_number(to_char(mr_inne_seq.nextval) || lpad(shard_no, 4, '0')) ||
           ltrim(to_char(MOD(abs(hsecs), 1000000), '000000'))
      INTO last_val
      FROM sys.v_$timer;
    RETURN last_val;
  END;

  FUNCTION CURRVAL RETURN NUMBER IS
    no_current_value EXCEPTION;
    PRAGMA EXCEPTION_INIT(no_current_value, -8002);
  BEGIN
    IF (last_val IS NULL) THEN
      RAISE no_current_value;
    END IF;
    RETURN last_val;
  END CURRVAL;

BEGIN
  --- the code below can use database table lookup
  shard_no := 5;

END django_seq;
/

Attachments (1)

django_seq.pck (839 bytes) - added by wiesiek@… 3 years ago.

Download all attachments as: .zip

Change History (5)

Changed 3 years ago by wiesiek@…

comment:1 Changed 3 years ago by aaugustin

  • Description modified (diff)
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Triage Stage changed from Unreviewed to Design decision needed

Fixed formatting (please use preview).

As is, I can't tell where we'd use your block of code, what are its advantages and drawbacks, and whether it is backwards-compatible. We're web developers, not DBAs :) All I can say is that it is far from trivial.

As a consequence, this ticket is likely to fall into the "yes, Django could have been designed differently, but it isn't" bucket, but I'll leave it open for now in case someone's interested.

If you want to see this in Django, you will have to 1) produce a patch 2) explain why it is useful — your explanation is mostly hand-waving at this point.

comment:2 Changed 3 years ago by aaugustin

Also, I believe #13295 would resolve this to some extend: it would allow you to use whatever sequence you want. So you could use the same sequence for all tables in your project if you wanted to.

comment:3 Changed 3 years ago by anonymous

I forgot to mention, that this code works only for Oracle and
it needs larger Id column (e.g. Oracle number(38)). I am also not a DBA,
but I am a database application developer and beginning web developer.
I think that using many sequences in a database application is very common
design flaw and nuissance in allmost all database applications I met
so far :-)

comment:4 Changed 3 years ago by jacob

  • Resolution set to duplicate
  • Status changed from new to closed

As aaugustin says, this was just a decision we made years ago. I don't see that there's a great advantage either way: each option has its pros and cons. I think #13295 is the answer here: let users decide if they feel strongly. Thus, I'm marking this closed as a duplicate of #13295.

Thanks!

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.