How to edit tables without primary key? (e.g. xref ) about dancer-plugin-simplecrud HOT 8 CLOSED

On Thu, 21 Feb 2013 00:11:46 -0800
Andrew Grangaard [email protected] wrote:

Thanks for your module. I'm using it during a hackathon at work, so
I can quickly import and edit some data in my project.

Glad to hear it's been useful to you!

Use case: three small tables: projects, users, and
project_user_xref. project_user_xref has two fields, project_id
and user_id, which are foreign keys pointing to projects.id and
users.id. I am currently stumped on how to modify this table via
D-P-SimpleCRUD.

Similarly, I had a problem with my original users table, which had a
primary key of "users.name". Since that didn't work well, I added a
synthetic id field (autoinc integer primary key), and I was able to
use SimpleCRUD for my users and my projects.

Yeah, D::P::SimpleCRUD is written with the perhaps slightly
naive) expectation that all tables will have an id column to identify
rows uniquely.

The easiest way to make it work for your project_user_xref table would
be to simply add an id column as a unique ID - then things will Just
Work.

That might seem a bit of a kludge, though, as a compound key on
project_id and user_id is perfectly sufficient database wise; in fact,
if you add a unique id column to allow D::P::SimpleCRUD to identify
rows, you'd likely still want a UNIQUE constraint on the other two
anyway, so you couldn't end up with multiple rows with the same
project_id and user_id.

The main reason I made the (perhaps naive) choice to require an id
column is so that records can be uniquely identified easily when
producing links to view/edit/delete etc. I'll have a think how
difficult it would be to add support for compound keys at some point.

Off the top of my head, I could make the 'key_column' option accept an
arrayref of column names, and have the resulting SQL build a suitable
WHERE clause using each one - but I'd have to think how that would be
represented in URLs to view/edit/delete records. It might be simple
enough to just add each column name as a param to the URL, so in your
case, say, you'd have said:

simple_crud(
    prefix       => '/project_user_xref',
    db_table     => 'project_user_xref',
    record_title => 'Project <-> User mapping',
    key_column   => [qw( project_id user_id )],

    foreign_keys => {
        user_id => {
            table        => 'user',
            key_column   => 'id',
            label_column => 'name',
        },
        project_id => {
            table        => 'project',
            key_column   => 'id',
            label_column => 'name',
        },
    },
);

Then, URLs to view/edit records would look like e.g.:

/project_user_xref?user_id=42&project_id=24

That would be reasonable, I think.

Does that sound useful to you?

I'll look in to how much effort would be needed.

EDIT: GitHub appears to be ignoring my attempts to make it format the code example above properly.

Cheers,

Dave P

from dancer-plugin-simplecrud.

Incidentally, http://stackoverflow.com/questions/350950/multi-column-primary-keys/351190#351190 sums up my feelings on id columns fairly well.

from dancer-plugin-simplecrud.

Thanks for the great response. Thinking this over.

from dancer-plugin-simplecrud.

I just realised what I suggested is pretty much exactly what was requested in Issue #11 a long long time ago.

from dancer-plugin-simplecrud.

... this has sat for some time!

I'm going to close this, as I think "tables must have a unique identifier column" is a reasonable choice, and I don't forsee having the time available (or, indeed, a burning desire) to do the work to support tables that don't have an ID column. Sorry!

from dancer-plugin-simplecrud.

closing was a good idea.

FWIW: I'd totally forgotten about this :)
And also forgotten what hackathon project I was working on in 2013.

from dancer-plugin-simplecrud.

@bigpresh I don't agree with your statement " "tables must have a unique identifier column" is a reasonable choice".
Reason: I am using PostgreSQL Citus in which I want to shard my multi-tenant tables with tenant_id. Now in order for citus to work, the shard key tenant_id needs to be a part of the primary key set mandatorily. If we do a Get operation or any other operation without the tenant_id, it will run the query in all the shards.

I would argue, this is a very reasonable choice for Citus DB to force the distrubution column to be a part of primary key. So that it can tell from the where condition itself which shard to run this query on.

For this reason, it is super hard for me to get any benefit of this library.

from dancer-plugin-simplecrud.

Fair point. I think then umber of people using sharding like that, but still wanting to use a "::Simple*" CRUD library like these is probably fairly small, though. My thoughts on handling multiple column primary keys earlier in the ticket (for e.g. how to represent that reliably in the URLs - at the moment, you know easily that /$prefix/42 will get you an editing form for the row whose identifier column has the value 42 - that all gets more complex if there can be multiple columns making up the identifier) still stand, I think.

If you have some cunning ideas on how it should work I'm more than happy to hear them, but I don't want to move away from the "simple, works for the majority of people likely to use this" to support relatively unusual cases like yours if it causes extra complexity for the typical user.

My initial thought is the same as before, that the key_column could be an arrayref of columns, e.g. key_column => [ 'tenant_id', 'thing_id' ], and generate URLs to represent objects as e.g. /prefix/12/34, /prefix/12/34/edit - where the values for each column within key_column are in the URL path... but would need to be careful for cases like "what if one of the values should contain an actual /... I suppose as long as the values get URL-encoded carefully that should be OK?

from dancer-plugin-simplecrud.