Thread Datenbank-Model für Webframework (7 answers)
Opened by Kuerbis at 2011-09-07 14:05

Kuerbis
 2011-09-07 14:05
#152184 #152184
User since
2011-03-20
936 Artikel
BenutzerIn
[default_avatar]
Ist das in Ordnung für ein Datenbank-Model für Mojolicious::Lite?

Code (perl): (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package Model_SQLite;
use warnings;
use 5.014;
use utf8;
use DBI;

my $dbname = 'adressen.db';
my $table = 'adressen';

sub new {
   my ( $class ) = @_;
   my $dbh = DBI->connect( "dbi:SQLite:dbname=$dbname", '', '',
   { RaiseError => 1, PrintError => 0, AutoCommit => 1, sqlite_unicode => 1, }
   ) or die $DBI::errstr;
   $dbh->do( "PRAGMA cache_size = 400000" );
   $dbh->do( "PRAGMA synchronous = OFF" );
   $dbh->do( "CREATE TABLE IF NOT EXISTS $table 
      ( vorname STRING NOT NULL, nachname STRING NOT NULL, 
      steuernummer STRING PRIMARY KEY, strasse STRING NOT NULL, 
      nummer STRING NOT NULL, ort STRING NOT NULL, plz STRING NOT NULL )" );
   my $self = bless { dbh => $dbh }, $class;
   return $self;
}

sub insert_db {
   my ( $self, %opts ) = @_;
   my @cols = ( qw( vorname nachname steuernummer strasse nummer ort plz ) );
   for ( @cols ) {
      die "Option '$_' is missing" if not exists $opts{$_};
      die "Value for '$_' is missing" if not $opts{$_};
   }
   my $sth = $self->{dbh}->prepare( 
      "INSERT INTO  $table (" . join( ', ', @cols ) . ') 
      VALUES (' . join( ', ', ( '?' ) x @cols ) . ')' 
   );
   $sth->execute( @opts{@cols} );
}

sub print_db {
   my ( $self ) = @_;
   my $sth = $self->{dbh}->prepare( 
      "SELECT * FROM $table ORDER BY nachname, vorname" 
   ); 
   $sth->execute;
   return $sth;
}

sub search_entry_steuernummer {
   my ( $self, $steuernummer ) = @_;
   my $ref = $self->{dbh}->selectall_hashref( 
      "SELECT * FROM $table  WHERE steuernummer == ?", 'steuernummer', {}, $steuernummer 
   ); 
   my @keys = keys %$ref;
   die if @keys != 1;
   return $ref->{$keys[0]};
}

sub search_entries_nachname {
   my ( $self, $nachname ) = @_;
   my $rows;
   my $sth = $self->{dbh}->prepare( 
      "SELECT vorname, nachname, steuernummer FROM $table WHERE nachname == ?" 
   );
   $sth->execute( $nachname );
   while ( my $row = $sth->fetchrow_hashref() ) {
      push @$rows, $row;
   }
   return $rows;
}

1;

View full thread Datenbank-Model für Webframework