Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Perl course (3), db
Search
Vadim Pushtaev
April 19, 2016
Programming
0
79
Perl course (3), db
Perl DB interaction
Vadim Pushtaev
April 19, 2016
Tweet
Share
More Decks by Vadim Pushtaev
See All by Vadim Pushtaev
PyCon Russia 2018 — Unit Testing
vadimpushtaev
0
390
Perl course (5), testing
vadimpushtaev
0
100
MR::Go::Admin::Test
vadimpushtaev
0
76
Perl course (4), testing
vadimpushtaev
0
110
Perl course (4), db
vadimpushtaev
0
210
Perl course (4), oop
vadimpushtaev
1
180
Perl course (4), mod
vadimpushtaev
0
200
Perl course (3), testing
vadimpushtaev
0
90
Perl course (3), oop
vadimpushtaev
0
98
Other Decks in Programming
See All in Programming
【第4回】関東Kaggler会「Kaggleは執筆に役立つ」
mipypf
0
880
The State of Fluid (2025)
s2b
0
200
なぜ今、Terraformの本を書いたのか? - 著者陣に聞く!『Terraformではじめる実践IaC』登壇資料
fufuhu
4
670
私の後悔をAWS DMSで解決した話
hiramax
4
150
AIコーディングAgentとの向き合い方
eycjur
0
240
MLH State of the League: 2026 Season
theycallmeswift
0
160
AI OCR API on Lambdaを Datadogで可視化してみた
nealle
0
180
State of CSS 2025
benjaminkott
1
120
フロントエンドのmonorepo化と責務分離のリアーキテクト
kajitack
2
140
コーディングエージェント時代のNeovim
key60228
1
110
AIでLINEスタンプを作ってみた
eycjur
1
200
GitHub Copilotの全体像と活用のヒント AI駆動開発の最初の一歩
74th
8
3.2k
Featured
See All Featured
A better future with KSS
kneath
239
17k
Designing for humans not robots
tammielis
253
25k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
31
2.5k
Why You Should Never Use an ORM
jnunemaker
PRO
59
9.5k
Build your cross-platform service in a week with App Engine
jlugia
231
18k
10 Git Anti Patterns You Should be Aware of
lemiorhan
PRO
656
61k
Why Our Code Smells
bkeepers
PRO
338
57k
Building Adaptive Systems
keathley
43
2.7k
Speed Design
sergeychernyshev
32
1.1k
VelocityConf: Rendering Performance Case Studies
addyosmani
332
24k
Bootstrapping a Software Product
garrettdimon
PRO
307
110k
Become a Pro
speakerdeck
PRO
29
5.5k
Transcript
Работа с базами данных 1 / 40
DBI $dbh = DBI->connect( $dsn, $user, $password, {RaiseError => 1,
AutoCommit => 0} ); $dbh->do($sql); 2 / 40
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 3 / 40
do my $number_of_rows = $dbh->do( 'DELETE FROM user WHERE age
< 18 '); my $name = <>; $dbh->do("DELETE FROM user WHERE name = '$name'"); 4 / 40
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); 5 / 40
prepare, execute my $sth = $dbh->prepare( 'DELETE FROM user WHERE
name = ?' ); $sth->execute('Vadim'); 6 / 40
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"; } 7 / 40
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, }); 8 / 40
fetchall_hashref $sth->fetchall_hashref('id'); # { 1 => {...}, 2 => {...}
} $sth->fetchall_hashref([ qw(foo bar) ]); { 1 => { a => {...}, b => {...} }, 2 => { a => {...}, b => {...} }, } 9 / 40
selectrow $dbh->selectrow_array( $statement, \%attr, @bind_values ); $dbh->selectrow_arrayref( $statement, \%attr, @bind_values
); $dbh->selectrow_hashref( $statement, \%attr, @bind_values ); 10 / 40
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 => {} } ); 11 / 40
Errors $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { RaiseError =>
1 } ); $dbh->err; $dbh->errstr; 12 / 40
Transactions $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { AutoCommit =>
1 } ); $dbh->begin_work; $dbh->rollback; $dbh->commit; 13 / 40
last_insert_id $dbh->do('INSERT INTO user VALUES(...)'); my $user_id = $dbh->last_insert_id( $catalog,
$schema, $table, $field, \%attr ); 14 / 40
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', }, ); 15 / 40
DBIx::Class __PACKAGE__->set_primary_key('id'); __PACKAGE__->has_many( visits => 'Local::Schema::Visit', 'user_id' ); __PACKAGE__->many_to_many( visited_cities
=> 'visits', 'city' ); 16 / 40
Files package Local::Schema; use base qw/DBIx::Class::Schema/; __PACKAGE__->load_namespaces(); 1; Local::Schema::Result::*; Local::Schema::ResultSet::*;
17 / 40
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(); 18 / 40
search $rs = $rs->search({ age => {'>=' => 18}, parent_id
=> undef, }); @results = $rs->all(); @results = $rs->search(...); $rs = $rs->search(...); $rs = $rs->search_rs(...); 19 / 40
search — attributes $rs = $rs->search( { page => {'>='
=> 18} }, { order_by => { -desc => [qw(a b c)] } }, ); $rs = $rs->search(undef, {rows => 100}); 20 / 40
search — duplicate key # :-( $rs = $rs->search({ age
=> {'>=' => 18}, age => {'<' => 60}, }); # :-) $rs = $rs->search([ { age => {'>=' => 18} }, { age => {'<' => 60} }, ]); 21 / 40
find, single my $rs = $schema->resultset('User'); $user = $rs->find({id =>
81858}); $user = $rs->find(81858); $user = $rs->search({id => 81858})->single(); 22 / 40
count my $count = $schema->resultset('User')->search({ name => 'name', age =>
18, })->count(); 23 / 40
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', ], }); 24 / 40
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' ); 25 / 40
Relations — usage $user = $schema->resultset('User')->find(81858); foreach my $dog ($user->dogs)
{ print join(' ', $dog->id, $dog->user->id); } 26 / 40
join $rs = $schema->resultset('Dog')->search({ 'me.name' => 'Sharik', 'user.name' => 'Vadim',
}, { join => 'user', }); 27 / 40
prefetch foreach my $user ($schema->resultset('User')) { foreach my $dog ($user->dogs)
{ # ... } } $rs = $schema->resultset('User')->search({}, { prefetch => 'dogs', # implies join }); 28 / 40
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', }); } 29 / 40
Custom result methods foreach my $woman (@women) { $woman->log('was selected');
} package Local::Schema::Result::User; sub log { print {$log} @_; } 30 / 40
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 }, ], }, ); 31 / 40
update, delete $result->last_modified(\'NOW()')->update(); # OR $result->update({ last_modified => \'NOW()' });
$user->delete(); 32 / 40
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'); 33 / 40
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; 34 / 40
storage $schema->storage->debug(1); $schema->storage->dbh(); 35 / 40
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 36 / 40
SQL::Translator $schema->deploy(); 37 / 40
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, # ... }); 38 / 40
Memached — operations $memd->add('skey', 'text'); $memd->set('nkey', 5, 60); $memd->incr('nkey'); $memd->get('skey');
39 / 40
ДЗ https://github.com/Nikolo/T echnosfera-perl/ /homeworks/habr 40 / 40