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
220
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
390
Perl course (5), testing
vadimpushtaev
0
110
MR::Go::Admin::Test
vadimpushtaev
0
82
Perl course (4), testing
vadimpushtaev
0
110
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
Perl course (3), oop
vadimpushtaev
0
100
Other Decks in Programming
See All in Programming
Le côté obscur des IA génératives
pascallemerrer
0
150
20251016_Rails News ~Rails 8.1の足音を聴く~
morimorihoge
1
280
Foundation Modelsを実装日本語学習アプリを作ってみた!
hypebeans
0
110
ALL CODE BASE ARE BELONG TO STUDY
uzulla
19
5k
品質ワークショップをやってみた
nealle
0
210
Range on Rails ―「多重範囲型」という新たな選択肢が、複雑ロジックを劇的にシンプルにしたワケ
rizap_tech
0
130
Pull-Requestの内容を1クリックで動作確認可能にするワークフロー
natmark
2
510
bootcamp2025_バックエンド研修_WebAPIサーバ作成.pdf
geniee_inc
0
110
Devoxx BE - Local Development in the AI Era
kdubois
0
130
Devvox Belgium - Agentic AI Patterns
kdubois
1
120
登壇は dynamic! な営みである / speech is dynamic
da1chi
0
330
CSC509 Lecture 06
javiergs
PRO
0
260
Featured
See All Featured
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
115
20k
Building Applications with DynamoDB
mza
96
6.7k
Site-Speed That Sticks
csswizardry
12
900
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
162
15k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
48
9.7k
Making the Leap to Tech Lead
cromwellryan
135
9.6k
Agile that works and the tools we love
rasmusluckow
331
21k
Why You Should Never Use an ORM
jnunemaker
PRO
59
9.6k
Chrome DevTools: State of the Union 2024 - Debugging React & Beyond
addyosmani
8
910
Intergalactic Javascript Robots from Outer Space
tanoku
273
27k
Build your cross-platform service in a week with App Engine
jlugia
232
18k
Fantastic passwords and where to find them - at NoRuKo
philnash
52
3.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