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
67
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
300
Perl course (5), testing
vadimpushtaev
0
82
MR::Go::Admin::Test
vadimpushtaev
0
65
Perl course (4), testing
vadimpushtaev
0
87
Perl course (4), db
vadimpushtaev
0
190
Perl course (4), oop
vadimpushtaev
1
150
Perl course (4), mod
vadimpushtaev
0
180
Perl course (3), testing
vadimpushtaev
0
75
Perl course (3), oop
vadimpushtaev
0
81
Other Decks in Programming
See All in Programming
Elm Form Validation
bkuhlmann
0
510
Milestoner
bkuhlmann
1
410
Azure OpenAI Serviceのプロンプトエンジニアリング入門
tomokusaba
3
620
Ruby Pattern Matching
bkuhlmann
0
920
Rubyでたのしむクリエイティブコーディング/Enjoy Creative coding with Ruby
chobishiba
1
170
VSCodeでのDatabricks開発もお勧めしたい/I would also recommend Databricks development with VSCode.
kazumain
0
250
雑に思考を整理する技術と効能
konifar
58
28k
try! Swift Tokyo 2024 参加報告 / try! Swift Tokyo 2024 Report
hironytic
0
200
スキーマ駆動開発による品質とスピードの両立 - 私達は何故、スキーマを書くのか
kentaroutakeda
0
160
HUIT新歓2024「競技プログラミング、やってみませんか?」
slephy2784
1
260
From Spring Boot 2 to Spring Boot 3 with Java 22 and Jakarta EE
ivargrimstad
0
1.1k
Semantic search with Django and pgvector
pauloxnet
0
240
Featured
See All Featured
Build your cross-platform service in a week with App Engine
jlugia
225
17k
Bootstrapping a Software Product
garrettdimon
PRO
301
110k
A Modern Web Designer's Workflow
chriscoyier
689
190k
Debugging Ruby Performance
tmm1
70
11k
What the flash - Photography Introduction
edds
64
11k
jQuery: Nuts, Bolts and Bling
dougneiner
59
7.1k
Writing Fast Ruby
sferik
620
60k
Art, The Web, and Tiny UX
lynnandtonic
288
19k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
273
13k
Faster Mobile Websites
deanohume
298
30k
BBQ
matthewcrist
80
8.8k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
658
120k
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