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 (2), lesson #7
Search
Vadim Pushtaev
November 18, 2015
Programming
0
74
Perl course (2), lesson #7
Perl DB
Vadim Pushtaev
November 18, 2015
Tweet
Share
More Decks by Vadim Pushtaev
See All by Vadim Pushtaev
PyCon Russia 2018 — Unit Testing
vadimpushtaev
0
400
Perl course (5), testing
vadimpushtaev
0
110
MR::Go::Admin::Test
vadimpushtaev
0
82
Perl course (4), testing
vadimpushtaev
0
110
Perl course (4), db
vadimpushtaev
0
220
Perl course (4), oop
vadimpushtaev
1
180
Perl course (4), mod
vadimpushtaev
0
200
Perl course (3), testing
vadimpushtaev
0
94
Perl course (3), db
vadimpushtaev
0
79
Other Decks in Programming
See All in Programming
なぜGoのジェネリクスはこの形なのか? - Featherweight Goが明かす設計の核心
qualiarts
0
240
What Spring Developers Should Know About Jakarta EE
ivargrimstad
0
430
Foundation Modelsを実装日本語学習アプリを作ってみた!
hypebeans
1
120
Go言語の特性を活かした公式MCP SDKの設計
hond0413
1
480
AI 駆動開発におけるコミュニティと AWS CDK の価値
konokenj
5
190
3年ぶりにコードを書いた元CTOが Claude Codeと30分でMVPを作った話
maikokojima
0
630
CSC509 Lecture 08
javiergs
PRO
0
240
contribution to astral-sh/uv
shunsock
0
510
タスクの特性や不確実性に応じた最適な作業スタイルの選択(ペアプロ・モブプロ・ソロプロ)と実践 / Optimal Work Style Selection: Pair, Mob, or Solo Programming.
honyanya
3
190
Leading Effective Engineering Teams in the AI Era
addyosmani
7
580
Things You Thought You Didn’t Need To Care About That Have a Big Impact On Your Job
hollycummins
0
250
バッチ処理を「状態の記録」から「事実の記録」へ
panda728
PRO
0
190
Featured
See All Featured
Imperfection Machines: The Place of Print at Facebook
scottboms
269
13k
The Art of Delivering Value - GDevCon NA Keynote
reverentgeek
16
1.7k
The Illustrated Children's Guide to Kubernetes
chrisshort
49
51k
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
9
990
Fashionably flexible responsive web design (full day workshop)
malarkey
407
66k
How STYLIGHT went responsive
nonsquared
100
5.8k
Speed Design
sergeychernyshev
32
1.2k
The Invisible Side of Design
smashingmag
302
51k
Building an army of robots
kneath
305
46k
Automating Front-end Workflow
addyosmani
1371
200k
Music & Morning Musume
bryan
46
6.9k
[RailsConf 2023] Rails as a piece of cake
palkan
57
5.9k
Transcript
Работа с базами данных 1 / 41
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
DBI $dbh = DBI->connect( $dsn, $user, $password, {RaiseError => 1,
AutoCommit => 0} ); $dbh->do($sql); 3 / 41
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
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
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
prepare, execute my $sth = $dbh->prepare( 'DELETE FROM user WHERE
name = ?' ); $sth->execute('Vadim'); 7 / 41
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
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
fetchall_hashref $sth->fetchall_hashref('id'); # { 1 => {...}, 2 => {...}
} $sth->fetchall_hashref([ qw(foo bar) ]); { 1 => { a => {...}, b => {...} }, 2 => { a => {...}, b => {...} }, } 10 / 41
selectrow $dbh->selectrow_array( $statement, \%attr, @bind_values ); $dbh->selectrow_arrayref( $statement, \%attr, @bind_values
); $dbh->selectrow_hashref( $statement, \%attr, @bind_values ); 11 / 41
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
Errors $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { RaiseError =>
1 } ); $dbh->err; $dbh->errstr; 13 / 41
Transactions $dbh = DBI->connect( "dbi:DriverName:db_name", $user, $password, { AutoCommit =>
1 } ); $dbh->begin_work; $dbh->rollback; $dbh->commit; 14 / 41
last_insert_id $dbh->do('INSERT INTO user VALUES(...)'); my $user_id = $dbh->last_insert_id( $catalog,
$schema, $table, $field, \%attr ); 15 / 41
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
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
Files package Local::Schema; use base qw/DBIx::Class::Schema/; __PACKAGE__->load_namespaces(); 1; Local::Schema::Result::*; Local::Schema::ResultSet::*;
18 / 41
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
search $rs = $rs->search({ age => {'>=' => 18}, parent_id
=> undef, }); @results = $rs->all(); @results = $rs->search(...); $rs = $rs->search(...); $rs = $rs->search_rs(...); 20 / 41
search — attributes $rs = $rs->search( { page => {'>='
=> 18} }, { order_by => { -desc => [qw(a b c)] } }, ); $rs = $rs->search(undef, {rows => 100}); 21 / 41
search — duplicate key # :-( $rs = $rs->search({ age
=> {'>=' => 18}, age => {'<' => 60}, }); # :-) $rs = $rs->search([ { age => {'>=' => 18} }, { age => {'<' => 60} }, ]); 22 / 41
find, single my $rs = $schema->resultset('User'); $user = $rs->find({id =>
81858}); $user = $rs->find(81858); $user = $rs->search({id => 81858})->single(); 23 / 41
count my $count = $schema->resultset('User')->search({ name => 'name', age =>
18, })->count(); 24 / 41
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
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
Relations — usage $user = $schema->resultset('User')->find(81858); foreach my $dog ($user->dogs)
{ print join(' ', $dog->id, $dog->user->id); } 27 / 41
join $rs = $schema->resultset('Dog')->search({ 'me.name' => 'Sharik', 'user.name' => 'Vadim',
}, { join => 'user', }); 28 / 41
prefetch foreach my $user ($schema->resultset('User')) { foreach my $dog ($user->dogs)
{ # ... } } $rs = $schema->resultset('User')->search({}, { prefetch => 'dogs', # implies join }); 29 / 41
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
Custom result methods foreach my $woman (@women) { $woman->log('was selected');
} package Local::Schema::Result::User; sub log { print {$log} @_; } 31 / 41
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
update, delete $result->last_modified(\'NOW()')->update(); # OR $result->update({ last_modified => \'NOW()' });
$user->delete(); 33 / 41
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
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
storage $schema->storage->debug(1); $schema->storage->dbh(); 36 / 41
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
SQL::Translator $schema->deploy(); 38 / 41
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
Memached — operations $memd->add('skey', 'text'); $memd->set('nkey', 5, 60); $memd->incr('nkey'); $memd->get('skey');
40 / 41
ДЗ https://github.com/Nikolo/Technosfera-perl/ /homeworks/grades 41 / 41
None