Frey/DBI/Sponge.pm

package Frey::DBI::Sponge;
use Moose;

extends 'Frey';

use lib 'lib';
use Frey::Types;

has sponge => (
        is => 'rw',
        isa => 'Sponge',
        required => 1,
        default => sub { {} },
);

has 'dsn' => (
        is => 'ro',
        isa => 'dsn',
        default => 'dbi:Pg:dbname=frey',
        required => 1,
);

sub to_database_as_markup {
        my ($self) = @_;

        my $table = $self->sponge->{table} || die "no table in sponge ", $self->dump( $self->sponge );

        warn "# dsn: ", $self->dsn;
        my $dbh = DBI->connect( $self->dsn, '', '', { RaiseError => 1 } ) || die $DBI::errstr;
        $dbh->do( qq{ set client_encoding='utf-8' } );

        my @columns = @{ $self->sponge->{NAME} };


        my $create_sql = qq{
                create table $table (
        } . join( ",\n",
                map {
                        s/%//;  # stip invalid characters from column names
                        my $type = 'text';
                        $type = 'timestamp' if $_ =~ m{(date|time)};
                        "$_ $type"
                } @columns ) . qq{
                );
        };
        eval { $dbh->do( $create_sql ); };
        if ( $@ ) {
                die "$create_sql\n$@" if $@ !~ m{$table.*already exists};
                warn "INFO: using existing table $table because of $@";
        } else {
                warn "INFO: created $table using\n$create_sql";
        }

        my $sth = $dbh->prepare( qq{
                insert into $table values (
        } . join( ",", map { '?' } @columns ) . qq{
                );
        });

        my $sponge_rows = $#{ $self->sponge->{rows} };
        warn "# convert ", $sponge_rows + 1 ," rows from spunge into $table in ", $self->dsn;

        foreach my $row_nr ( 0 .. $sponge_rows ) {
                $sth->execute( @{ $self->sponge->{rows}->[$row_nr] } );
        }

        $sponge_rows++;

        my $html = 
                        ( $sponge_rows
                                ? qq|Inserted <b>$sponge_rows</b> rows|
                                : qq|No rows inserted into $table|
                        )
                        . qq| to table <tt>$table</tt> at <tt>| . $self->dsn . qq|</tt>|
                        ;

        return $html;
}

1;
1	dpavlin	935	package Frey::DBI::Sponge;
2			use Moose;
3
4			extends 'Frey';
5
6	dpavlin	936	use lib 'lib';
7			use Frey::Types;
8	dpavlin	935
9			has sponge => (
10			is => 'rw',
11			isa => 'Sponge',
12			required => 1,
13	dpavlin	936	default => sub { {} },
14	dpavlin	935	);
15
16			has 'dsn' => (
17			is => 'ro',
18			isa => 'dsn',
19			default => 'dbi:Pg:dbname=frey',
20			required => 1,
21			);
22
23			sub to_database_as_markup {
24			my ($self) = @_;
25
26			my $table = $self->sponge->{table} \|\| die "no table in sponge ", $self->dump( $self->sponge );
27
28			warn "# dsn: ", $self->dsn;
29			my $dbh = DBI->connect( $self->dsn, '', '', { RaiseError => 1 } ) \|\| die $DBI::errstr;
30			$dbh->do( qq{ set client_encoding='utf-8' } );
31
32			my @columns = @{ $self->sponge->{NAME} };
33
34
35			my $create_sql = qq{
36			create table $table (
37	dpavlin	951	} . join( ",\n",
38			map {
39			s/%//; # stip invalid characters from column names
40			my $type = 'text';
41			$type = 'timestamp' if $_ =~ m{(date\|time)};
42			"$_ $type"
43			} @columns ) . qq{
44	dpavlin	935	);
45			};
46			eval { $dbh->do( $create_sql ); };
47			if ( $@ ) {
48			die "$create_sql\n$@" if $@ !~ m{$table.*already exists};
49			warn "INFO: using existing table $table because of $@";
50			} else {
51			warn "INFO: created $table using\n$create_sql";
52			}
53
54			my $sth = $dbh->prepare( qq{
55			insert into $table values (
56			} . join( ",", map { '?' } @columns ) . qq{
57			);
58			});
59
60			my $sponge_rows = $#{ $self->sponge->{rows} };
61			warn "# convert ", $sponge_rows + 1 ," rows from spunge into $table in ", $self->dsn;
62
63			foreach my $row_nr ( 0 .. $sponge_rows ) {
64			$sth->execute( @{ $self->sponge->{rows}->[$row_nr] } );
65			}
66
67			$sponge_rows++;
68
69			my $html =
70			( $sponge_rows
71			? qq\|Inserted <b>$sponge_rows</b> rows\|
72			: qq\|No rows inserted into $table\|
73			)
74			. qq\| to table <tt>$table</tt> at <tt>\| . $self->dsn . qq\|</tt>\|
75			;
76
77			return $html;
78			}
79
80			1;