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 (4), db
Search
Vadim Pushtaev
October 30, 2016
Programming
0
210
Perl course (4), db
Vadim Pushtaev
October 30, 2016
Tweet
Share
More Decks by Vadim Pushtaev
See All by Vadim Pushtaev
PyCon Russia 2018 — Unit Testing
vadimpushtaev
0
380
Perl course (5), testing
vadimpushtaev
0
97
MR::Go::Admin::Test
vadimpushtaev
0
76
Perl course (4), testing
vadimpushtaev
0
100
Perl course (4), oop
vadimpushtaev
1
180
Perl course (4), mod
vadimpushtaev
0
190
Perl course (3), testing
vadimpushtaev
0
85
Perl course (3), db
vadimpushtaev
0
78
Perl course (3), oop
vadimpushtaev
0
94
Other Decks in Programming
See All in Programming
PicoRuby on Rails
makicamel
2
110
Discover Metal 4
rei315
2
110
20250704_教育事業におけるアジャイルなデータ基盤構築
hanon52_
1
140
ソフトウェア品質を数字で捉える技術。事業成長を支えるシステム品質の マネジメント
takuya542
0
290
git worktree × Claude Code × MCP ~生成AI時代の並列開発フロー~
hisuzuya
1
520
Deep Dive into ~/.claude/projects
hiragram
10
2.1k
今ならAmazon ECSのサービス間通信をどう選ぶか / Selection of ECS Interservice Communication 2025
tkikuc
20
3.8k
関数型まつりレポート for JuliaTokai #22
antimon2
0
160
童醫院敏捷轉型的實踐經驗
cclai999
0
210
GraphRAGの仕組みまるわかり
tosuri13
8
500
Go1.25からのGOMAXPROCS
kuro_kurorrr
1
830
エンジニア向け採用ピッチ資料
inusan
0
180
Featured
See All Featured
The Myth of the Modular Monolith - Day 2 Keynote - Rails World 2024
eileencodes
26
2.9k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
30
2.1k
Balancing Empowerment & Direction
lara
1
390
Git: the NoSQL Database
bkeepers
PRO
430
65k
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
130
19k
Scaling GitHub
holman
459
140k
Adopting Sorbet at Scale
ufuk
77
9.4k
Making the Leap to Tech Lead
cromwellryan
134
9.4k
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
The Art of Delivering Value - GDevCon NA Keynote
reverentgeek
15
1.5k
StorybookのUI Testing Handbookを読んだ
zakiyama
30
5.8k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
44
2.4k
Transcript
Программирование на Perl Работа с базами данных 1 / 44
Отметьтесь на портале! 2 / 44
DBI $dbh = DBI->connect( $dsn, $user, $password, {RaiseError => 1,
AutoCommit => 0} ); $dbh->do($sql); 3 / 44
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 / 44
do my $number_of_rows = $dbh->do( 'DELETE FROM user WHERE age
< 18 '); my $name = <>; $dbh->do("DELETE FROM user WHERE name = '$name'"); 5 / 44
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 / 44
SQL injections 7 / 44
prepare, execute my $sth = $dbh->prepare( 'DELETE FROM user WHERE
name = ?' ); $sth->execute('Vadim'); 8 / 44
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"; } 9 / 44
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, }); 10 / 44
fetchall_hashref $sth->fetchall_hashref('id'); # { 1 => {...}, 2 => {...}
} $sth->fetchall_hashref([ qw(foo bar) ]); { 1 => { a => {...}, b => {...} }, 2 => { a => {...}, b => {...} }, } 11 / 44
selectrow $dbh->selectrow_array( $statement, \%attr, @bind_values ); $dbh->selectrow_arrayref( $statement, \%attr, @bind_values
); $dbh->selectrow_hashref( $statement, \%attr, @bind_values ); 12 / 44
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 => {} } ); 13 / 44
Errors $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { RaiseError =>
1 } ); $dbh->err; $dbh->errstr; 14 / 44
Transactions $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { AutoCommit =>
1 } ); $dbh->begin_work; $dbh->rollback; $dbh->commit; 15 / 44
last_insert_id $dbh->do('INSERT INTO user VALUES(...)'); my $user_id = $dbh->last_insert_id( $catalog,
$schema, $table, $field, \%attr ); 16 / 44
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', }, ); 17 / 44
DBIx::Class __PACKAGE__->set_primary_key('id'); __PACKAGE__->has_many( visits => 'Local::Schema::Visit', 'user_id' ); __PACKAGE__->many_to_many( visited_cities
=> 'visits', 'city' ); 18 / 44
Files package Local::Schema; use base qw/DBIx::Class::Schema/; __PACKAGE__->load_namespaces(); 1; Local::Schema::Result::*; Local::Schema::ResultSet::*;
19 / 44
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(); 20 / 44
search $rs = $rs->search({ age => {'>=' => 18}, parent_id
=> undef, }); @results = $rs->all(); @results = $rs->search(...); $rs = $rs->search(...); $rs = $rs->search_rs(...); 21 / 44
search — attributes $rs = $rs->search( { page => {'>='
=> 18} }, { order_by => { -desc => [qw(a b c)] } }, ); $rs = $rs->search(undef, {rows => 100}); 22 / 44
search — duplicate key # :-( $rs = $rs->search({ age
=> {'>=' => 18}, age => {'<' => 60}, }); # :-) $rs = $rs->search([ { age => {'>=' => 18} }, { age => {'<' => 60} }, ]); 23 / 44
find, single my $rs = $schema->resultset('User'); $user = $rs->find({id =>
81858}); $user = $rs->find(81858); $user = $rs->search({id => 81858})->single(); 24 / 44
count my $count = $schema->resultset('User')->search({ name => 'name', age =>
18, })->count(); 25 / 44
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', ], }); 26 / 44
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' ); 27 / 44
Relations — usage $user = $schema->resultset('User')->find(81858); foreach my $dog ($user->dogs)
{ print join(' ', $dog->id, $dog->user->id); } 28 / 44
join $rs = $schema->resultset('Dog')->search({ 'me.name' => 'Sharik', 'user.name' => 'Vadim',
}, { join => 'user', }); 29 / 44
prefetch foreach my $user ($schema->resultset('User')) { foreach my $dog ($user->dogs)
{ # ... } } $rs = $schema->resultset('User')->search({}, { prefetch => 'dogs', # implies join }); 30 / 44
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', }); } 31 / 44
Custom result methods foreach my $woman (@women) { $woman->log('was selected');
} package Local::Schema::Result::User; sub log { print {$log} @_; } 32 / 44
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 }, ], }, ); 33 / 44
update, delete $result->last_modified(\'NOW()')->update(); # OR $result->update({ last_modified => \'NOW()' });
$user->delete(); 34 / 44
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'); 35 / 44
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; 36 / 44
storage $schema->storage->debug(1); $schema->storage->dbh(); 37 / 44
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 38 / 44
SQL::Translator $schema->deploy(); 39 / 44
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, # ... }); 40 / 44
Memached — operations $memd->add('skey', 'text'); $memd->set('nkey', 5, 60); $memd->incr('nkey'); $memd->get('skey');
41 / 44
NoSQL Redis MongoDB Cassandra Tarantool Octopus 42 / 44
ДЗ https://github.com/Nikolo/Technosfera-perl/ /homeworks/habr 43 / 44
Спасибо за внимание! Оставьте отзыв Вадим Пуштаев
[email protected]
44 /
44