Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re: DBI relation between tables tutorial

by daxim (Chaplain)
on Mar 11, 2013 at 15:25 UTC ( #1022813=note: print w/ replies, xml ) Need Help??


in reply to DBI relation between tables tutorial

This is a beginner database question, go to the next library and get an introduction to RDBMS design/SQL book.

You are describing a Junction table.

$ echo ' create table articles (id integer not null, parsed text, primary k +ey (id)); create table information (id integer not null, something text, pri +mary key (id)); create table articles_information (articles_id integer not null, i +nformation_id integer not null, primary key (articles_id, information +_id), foreign key (articles_id) references articles (id), foreign key + (information_id) references information (id)); ' | sqlite3 M15U.sqlite
This example is already complicated enough that you should not bother with raw SQL in DBI, but use a higher-level ORM. DBIx::Class sample code:
use DBIx::Class::Schema::Loader qw(); DBIx::Class::Schema::Loader->naming('preserve'); my $schema = DBIx::Class::Schema::Loader->connect('DBI:SQLite:db=M15U. +sqlite'); my $foobar = $schema->resultset('Articles')->create({ id => 1, parsed +=> 'foo bar' }); $foobar->add_to_informations({ id => 1, something => 'quux' }); $foobar->add_to_informations({ id => 2, something => 'fnord' });
As long as the foreign keys are set up properly, the junction table is filled automatically:
$ echo .dump | sqlite3 M15U.sqlite | grep INSERT INSERT INTO "articles" VALUES(1,'foo bar'); INSERT INTO "information" VALUES(1,'quux'); INSERT INTO "information" VALUES(2,'fnord'); INSERT INTO "articles_information" VALUES(1,1); INSERT INTO "articles_information" VALUES(1,2);


Comment on Re: DBI relation between tables tutorial
Select or Download Code
Re^2: DBI relation between tables tutorial
by M15U (Acolyte) on Mar 11, 2013 at 16:13 UTC

    Thank you so much, this is exactly what I was looking for, and sorry for the fuzzy description

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2015-07-03 20:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (56 votes), past polls