Perl course (2), lesson #7

Slide 1

Slide 1 text

Работа с базами данных 1 / 41

Slide 2

Slide 2 text

SQL SELECT name, surname FROM users WHERE age > 18; SELECT balance FROM account WHERE user_id = 81858 SELECT * FROM users u JOIN accounts a ON u.id = a.user_id WHERE account.balance > 0 2 / 41

Slide 3

Slide 3 text

DBI $dbh = DBI->connect( $dsn, $user, $password, {RaiseError => 1, AutoCommit => 0} ); $dbh->do($sql); 3 / 41

Slide 4

Slide 4 text

connect $dbh = DBI->connect($data_source, user, $password, {...}); # DBD::SQLite $dbh = DBI->connect("dbi:SQLite:dbname=dbfile", "",""); # DBD::mysql $dbh = DBI->connect( "DBI:mysql:database=$database;" . "host=$hostname;port=$port", $user, $password ); dbi:DriverName:database_name dbi:DriverName:database_name@hostname:port dbi:DriverName:database=DBNAME;host=HOSTNAME;port=PORT 4 / 41

Slide 5

Slide 5 text

do my $number_of_rows = $dbh->do( 'DELETE FROM user WHERE age < 18 '); my $name = <>; $dbh->do("DELETE FROM user WHERE name = '$name'"); 5 / 41

Slide 6

Slide 6 text

SQL injections my $name = q{' OR (DELETE FROM log) AND '' = '}; $dbh->do("DELETE FROM user WHERE name = '$name'"); DELETE FROM user WHERE name = '' OR (DELETE FROM log) AND '' = '' $name = $dbh->quote($name); 6 / 41

Slide 7

Slide 7 text

prepare, execute my $sth = $dbh->prepare( 'DELETE FROM user WHERE name = ?' ); $sth->execute('Vadim'); 7 / 41

Slide 8

Slide 8 text

fetchrow my $ary_ref = $sth->fetchrow_arrayref(); my @ary = $sth->fetchrow_array(); my $hash = $sth->fetchrow_hashref(); while (@row = $sth->fetchrow_array()) { print "@row\n"; } 8 / 41

Slide 9

Slide 9 text

fetchall_arrayref my $ary = $sth->fetchall_arrayref; # [ [...], [...], [...] ] my $ary = $sth->fetchall_arrayref({}); # [ {...}, {...}, {...} ] $tbl_ary_ref = $sth->fetchall_arrayref( [0] ); $tbl_ary_ref = $sth->fetchall_arrayref( [-2,-1] ); $tbl_ary_ref = $sth->fetchall_arrayref({ foo => 1, BAR => 1, }); 9 / 41

Slide 10

Slide 10 text

fetchall_hashref $sth->fetchall_hashref('id'); # { 1 => {...}, 2 => {...} } $sth->fetchall_hashref([ qw(foo bar) ]); { 1 => { a => {...}, b => {...} }, 2 => { a => {...}, b => {...} }, } 10 / 41

Slide 11

Slide 11 text

selectrow $dbh->selectrow_array( $statement, \%attr, @bind_values ); $dbh->selectrow_arrayref( $statement, \%attr, @bind_values ); $dbh->selectrow_hashref( $statement, \%attr, @bind_values ); 11 / 41

Slide 12

Slide 12 text

selectall $dbh->selectall_arrayref( $statement, \%attr, @bind_values); $dbh->selectall_hashref( $statement, $key_field, \%attr, @bind_values); $dbh->selectall_arrayref( "SELECT ename FROM emp ORDER BY ename", { Slice => {} } ); 12 / 41

Slide 13

Slide 13 text

Errors $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { RaiseError => 1 } ); $dbh->err; $dbh->errstr; 13 / 41

Slide 14

Slide 14 text

Transactions $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { AutoCommit => 1 } ); $dbh->begin_work; $dbh->rollback; $dbh->commit; 14 / 41

Slide 15

Slide 15 text

last_insert_id $dbh->do('INSERT INTO user VALUES(...)'); my $user_id = $dbh->last_insert_id( $catalog, $schema, $table, $field, \%attr ); 15 / 41

Slide 16

Slide 16 text

DBIx::Class package Local::Schema::User; use base qw(DBIx::Class::Core); __PACKAGE__->table('user'); __PACKAGE__->add_columns( id => { data_type => 'integer', is_auto_increment => 1, }, name => { data_type => 'varchar', size => '100', }, superuser => { data_type => 'bool', }, ); 16 / 41

Slide 17

Slide 17 text

DBIx::Class __PACKAGE__->set_primary_key('id'); __PACKAGE__->has_many( visits => 'Local::Schema::Visit', 'user_id' ); __PACKAGE__->many_to_many( visited_cities => 'visits', 'city' ); 17 / 41

Slide 18

Slide 18 text

Files package Local::Schema; use base qw/DBIx::Class::Schema/; __PACKAGE__->load_namespaces(); 1; Local::Schema::Result::*; Local::Schema::ResultSet::*; 18 / 41

Slide 19

Slide 19 text

resultset, result my $resultset = $schema->resultset('User'); my $resultset2 = $resultset->search({age => 25}); while (my $user = $resultset->next) { print $user->name . "\n"; } print join "\n", $resultset2->all(); 19 / 41

Slide 20

Slide 20 text

search $rs = $rs->search({ age => {'>=' => 18}, parent_id => undef, }); @results = $rs->all(); @results = $rs->search(...); $rs = $rs->search(...); $rs = $rs->search_rs(...); 20 / 41

Slide 21

Slide 21 text

search — attributes $rs = $rs->search( { page => {'>=' => 18} }, { order_by => { -desc => [qw(a b c)] } }, ); $rs = $rs->search(undef, {rows => 100}); 21 / 41

Slide 22

Slide 22 text

search — duplicate key # :-( $rs = $rs->search({ age => {'>=' => 18}, age => {'<' => 60}, }); # :-) $rs = $rs->search([ { age => {'>=' => 18} }, { age => {'<' => 60} }, ]); 22 / 41

Slide 23

Slide 23 text

find, single my $rs = $schema->resultset('User'); $user = $rs->find({id => 81858}); $user = $rs->find(81858); $user = $rs->search({id => 81858})->single(); 23 / 41

Slide 24

Slide 24 text

count my $count = $schema->resultset('User')->search({ name => 'name', age => 18, })->count(); 24 / 41

Slide 25

Slide 25 text

select — advanced $resultset->search({ date => { '>' => \'NOW()' }, }); $rs->search( \[ 'YEAR(date_of_birth) = ?', 1979 ] ); my @albums = $schema->resultset('Album')->search({ -or => [ -and => [ artist => { 'like', '%Smashing Pumpkins%' }, title => 'Siamese Dream', ], artist => 'Starchildren', ], }); 25 / 41

Slide 26

Slide 26 text

Relations package Local::Schema::User; use base qw(DBIx::Class::Core); __PACKAGE__->table('user'); __PACKAGE__->has_many( dogs => 'Local::Schema::Dog', 'user_id' ); package Local::Schema::Dog; use base qw(DBIx::Class::Core); __PACKAGE__->table('dog'); __PACKAGE__->belongs_to( user => 'Local::Schema::User', 'user_id' ); 26 / 41

Slide 27

Slide 27 text

Relations — usage $user = $schema->resultset('User')->find(81858); foreach my $dog ($user->dogs) { print join(' ', $dog->id, $dog->user->id); } 27 / 41

Slide 28

Slide 28 text

join $rs = $schema->resultset('Dog')->search({ 'me.name' => 'Sharik', 'user.name' => 'Vadim', }, { join => 'user', }); 28 / 41

Slide 29

Slide 29 text

prefetch foreach my $user ($schema->resultset('User')) { foreach my $dog ($user->dogs) { # ... } } $rs = $schema->resultset('User')->search({}, { prefetch => 'dogs', # implies join }); 29 / 41

Slide 30

Slide 30 text

Custom resultset methods my @women = $schema->resultset('User')-> search_women()->all(); package Local::Schema::ResultSet::User; sub search_women { my ($self) = @_; return $self->search({ gender => 'f', }); } 30 / 41

Slide 31

Slide 31 text

Custom result methods foreach my $woman (@women) { $woman->log('was selected'); } package Local::Schema::Result::User; sub log { print {$log} @_; } 31 / 41

Slide 32

Slide 32 text

new_result, create my $user = $schema->resultset('User')->new_result({ name => 'Vadim', superuser => 1, }); $user->insert(); my $artist = $artist_rs->create( { artistid => 4, name => 'Blah-blah', cds => [ { title => 'My First CD', year => 2006 }, { title => 'e.t.c', year => 2007 }, ], }, ); 32 / 41

Slide 33

Slide 33 text

update, delete $result->last_modified(\'NOW()')->update(); # OR $result->update({ last_modified => \'NOW()' }); $user->delete(); 33 / 41

Slide 34

Slide 34 text

many_to_many package Local::Schema::User; __PACKAGE__->has_many( visits => 'Local::Schema::Visit', 'user_id'); __PACKAGE__->many_to_many( visited_cities => 'visits', 'city'); package Local::Schema::City; __PACKAGE__->has_many( visits => 'Local::Schema::Visit', 'city_id'); __PACKAGE__->many_to_many( visited_by => 'visits', 'user'); 34 / 41

Slide 35

Slide 35 text

many_to_many package Local::Schema::Visit; __PACKAGE__->belongs_to( user => 'Local::Schema::User', 'user_id'); __PACKAGE__->belongs_to( city => 'Local::Schema::City', 'city_id'); my @cities = $schema->resultset('User')-> find(81858)->visited_cities; 35 / 41

Slide 36

Slide 36 text

storage $schema->storage->debug(1); $schema->storage->dbh(); 36 / 41

Slide 37

Slide 37 text

DBIx::Class::Schema::Loader use DBIx::Class::Schema::Loader qw( make_schema_at ); make_schema_at( 'My::Schema', { debug => 1, dump_directory => './lib', }, [ 'dbi:Pg:dbname="foo"', 'user', 'pw' ] ); dbicdump -o dump_directory=./lib \ -o debug=1 \ My::Schema \ 'dbi:Pg:dbname=foo' \ myuser \ mypassword 37 / 41

Slide 38

Slide 38 text

SQL::Translator $schema->deploy(); 38 / 41

Slide 39

Slide 39 text

Memcached use Cache::Memcached::Fast; my $memd = Cache::Memcached::Fast->new({ servers => [ {address => 'localhost:11211', weight => 2.5}, '192.168.254.2:11211', '/path/to/unix.sock' ], namespace => 'my:', connect_timeout => 0.2, # ... }); 39 / 41

Slide 40

Slide 40 text

Memached — operations $memd->add('skey', 'text'); $memd->set('nkey', 5, 60); $memd->incr('nkey'); $memd->get('skey'); 40 / 41