Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: DBI Update dynamically

by Anonymous Monk
on Feb 23, 2018 at 17:44 UTC ( [id://1209850]=note: print w/replies, xml ) Need Help??


in reply to DBI Update dynamically

Thank you for the precious suggestions. As far as I can see from the answers, it seems not to be a common things to have applications with a "flexible" database. What is common practice if the number of table columns may vary from user to user. Let's say user A will need x=5 columns while user B x=10. I wanted to create all my statements (create, insert, update and so on) dynamically on the basis of the choosen x value. Is this way to work deprecated? The alternative is to have always x=10 and let user A use less columns... not very elegant. I know this is no more a Perl question...but I would appreciate any thought on this.

Replies are listed 'Best First'.
Re^2: DBI Update dynamically
by Corion (Patriarch) on Feb 23, 2018 at 17:51 UTC

    I just wrote the program pasted below some days ago which basically takes hashes and makes an INSERT statement out of them.

    Update: Upon re-reading your question, I realize that my program completely misses the point, as you want to do UPDATE statements, not INSERT statements. This would be easy if you have the column names and know which column is to be used as the primary key. My program does not know about that though, sorry.

    In your case, the interesting parts are where the program constructs the INSERT statement from a hash. It uses placeholders and assigns the hash values to a the list of values based on the order of the column names. I recommend to always use column names and never use positional INSERT statements as the column order of a database might change when columns are dropped or renamed and then other columns with the same name are again.

    my $data = [ { col1 => 'data1', col3 => 'data3' }, { col2 => 'data2', col3 => 'data3plus' }, ]; if( ! @columns ) { # Collect all (potential) columns we want to write my %columns; for( @$data ) { $columns{ $_ } = 1 for keys %$_; } @columns = sort keys %columns; }; my $columns = join ",", map { qq("$_") } @columns; my $placeholders = join ",", ("?") x @columns; # Run the insert statement for each row in our list my $sql_insert = qq(insert into "$table" ($columns) values ($placehold +ers));

    The full program is here:

    #!perl -w use strict; use DBI; use JSON; use Getopt::Long; use Pod::Usage; use Path::Class 'file'; GetOptions( 'table:s' => \my $table, 'dsn:s' => \my $dsn, 'user:s' => \my $user, 'password:s' => \my $password, 'file:s' => \my $json_file, 'json:s' => \my $json_text, 'columns:s' => \my @columns, ) or pod2usage(2); my $data; if( $json_file ) { $data = decode_json( file( $json_file )->slurp($json_file, iomode +=> '<:raw' )); } elsif( $json_text ) { $data = decode_json( $json_text ); } else { binmode STDIN, ':raw'; local $/; $data = decode_json( <STDIN> ); }; if( ref $data eq 'HASH' ) { # Convert single row to a set of rows $data = [$data] }; if( ! @columns ) { # Collect all (potential) columns we want to write my %columns; for( @$data ) { $columns{ $_ } = 1 for keys %$_; } @columns = sort keys %columns; }; my $columns = join ",", map { qq("$_") } @columns; my $placeholders = join ",", ("?") x @columns; # Run the insert statement for each row in our list my $sql_insert = qq(insert into "$table" ($columns) values ($placehold +ers)); my $dbh = DBI->connect( $dsn, $user, $password, { RaiseError => 1, Pri +ntError => 0 }); my $sth_insert = $dbh->prepare( $sql_insert ); if( ! $sth_insert->execute_for_fetch( sub { my $item = shift @$data; if( $item ) { my $res = [@{$item}{ @columns }]; return $res } else { return $item } }, \my @results)) { for my $err (grep { ref $_ } @results) { warn $err->[1]; }; };
Re^2: DBI Update dynamically
by poj (Abbot) on Feb 23, 2018 at 18:12 UTC

    The traditional way is to have 3 columns.

    User ColNo Value
    A    1    A1
    A    2    A2
    B    1    B1
    B    2    B2
    B    3    B3
    B    4    B4
    B    5    B5
    

    There are more flexible databases such as mongoDb

    poj
Re^2: DBI Update dynamically
by hippo (Bishop) on Feb 23, 2018 at 22:01 UTC
    What is common practice if the number of table columns may vary from user to user.

    Views

Re^2: DBI Update dynamically
by thanos1983 (Parson) on Feb 24, 2018 at 00:10 UTC

    Hello again Anonymous Monk,

    Since there is no limit on how many tables can SQLite see here Limits In SQLite, why not create different table for each user and then use the REPLACE that fellow Monk poj proposed. By doing so you can have different number of columns for each user based on their requirements.

    Hope this helps. BR / Thanos

    Seeking for Perl wisdom...on the process of learning...not there...yet!

      I feel that having a different table for each user completely removes the entire purpose of a single source of information.

      Now, it feels like I'm missing something here, but to maintain such a structure (especially to simply fetch information across all tables) would be a nightmare.

      I'm not saying its right or wrong, I'm saying it feels like a kludge that actually goes against any efficiencies and scalability.

      The real solution I think is to add a bit more code and do the UPDATE accordingly. That'll keep the DB consistent, and allow OP to still dyanmically do things in a way that would be scalable going forward. I just got to a hotel after a three hour drive so I need more time to digest this (perhaps OP can share more insight into the reason for needing this extreme conciseness) before I have any formal solution.

      Could be trivially easy to add another column to the table, perhaps even comma separated that explains which columns each user can populate by name, then grab that info based on the userid, dump it into a hash, and then you know which columns user X has, and just dump undef, NULL or whatever to the others (again, using UPDATE).

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1209850]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2024-03-28 13:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found