#226 Versioning broken in 0.9.1

closed
None
5
2007-09-11
2007-08-06
azav
No

The versions table uses the same constraints as the original table, so having for instance a unique column in a table will raise a DuplicateEntryError when a second version with the same name is created.

Discussion

  • Nobody/Anonymous

    Logged In: NO

    I stumbled across the same problem. As constraints are directly copied to version tables, each version of a row needs to hold new values in all unique columns. This may not be acceptable to all applications.

    In order to fix this a minor change can be made to the file /sqlobject/versioning/__init__.py. You merely need to add a few lines. Heres the result from a diff between the original and my changed version:

    48c48,51
    < columns[column] = defi.__class__(**defi._kw)
    ---
    > kwds = dict(defi._kw)
    > for kw in ["alternateID", "unique"]:
    > if kw in kwds: del kwds[kw]
    > columns[column] = defi.__class__(**kwds)

    I dont know if this is the way the sqlobject authors would do it, but it seems to work. Hopefully it will result in a faster patch :-)

     
  • azav

    azav - 2007-09-10

    Logged In: YES
    user_id=1861531
    Originator: YES

    This only fixes part of the problem. For instance, version tables will still be created with the foreign key constraints of the original table.

     
  • David Turner

    David Turner - 2007-09-10

    Logged In: YES
    user_id=83137
    Originator: NO

    I think the old versions ought to have the same foreign key constraints. Why does this cause problems?

     
  • David Turner

    David Turner - 2007-09-10

    Logged In: YES
    user_id=83137
    Originator: NO

    I fixed this in SVN (unless the foreign key thing turns out to be a problem)

     
  • Oleg Broytman

    Oleg Broytman - 2007-09-11
    • assigned_to: nobody --> novalis_dt
     
  • David Turner

    David Turner - 2007-09-11
    • status: open --> closed
     
  • Nobody/Anonymous

    Logged In: NO

    Ahh.. now i see why my first solution only solves half the problem. azav is right in his example with foreign keys...

    So forth a versioned row holds a foreign key to another table, that constraint is still present. When using a database engine enforcing foreign key constraints a foreign key of an old row cannot be deleted as it might still be referenced with a no delete policy. For this reason rows may be stuck in a table due to old versions of rows from other tables still reference them.

    A simple solution would be to convert foreign key columns to simple int columns, thereby having no constraints. This way older versions of a table holds no foreign key constraints on other tables. However this may prevent versioned tables from being restored to previous versions so forth the referenced field has been deleted, as this could break a notNone constraint.

    As i see it the possibilities is to store all foreign key data in the version table too (impractical and possibly impossible), or accept this flaw in the system... Any good ideas? If you can suggest a solution ill gladly post an implementation example.

     
  • David Turner

    David Turner - 2007-09-15

    Logged In: YES
    user_id=83137
    Originator: NO

    I actually think the correct solution is more complex still.

    Consider a situation where there are employees and jobs, where employee has a foreign key to job. Both are versioned.

    If we want to look at the historical salary record of an employee, we need to know not just what job they had at time T, but also what the salary of that job was at time T.

    So ideally, when we version an employee, we ought to link their job to the appropriate version of the job.

    That would require a bit of work to implement -- although I cannot see any theoretical reason that it is not possible, at least when limited to cases where the user has added the appropriate MultipleJoins. The only problem is if there are cases where this is not the correct behavior. I don't think of any, but I don't know for sure.

    Look up "ON DELETE". Delete can restrict (as you note) -- but it can also cascade, deleting the older versions that reference the deleted row, or set null.

    If I'm in a situation where I care about history, I have a deleted column in each table, and never use actual deletes. Ideally, SQLObject would provide support for this.

     
  • David Turner

    David Turner - 2007-09-15

    Logged In: YES
    user_id=83137
    Originator: NO

    If you write this patch, please make it optional per-versioned-table.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks