view release on metacpan or  search on metacpan

lib/App/Repository/MySQL.pm  view on Meta::CPAN

#+    my $desc = $dbh->selectall_arrayref($desc_sth, { Columns=>{} });
#+    my %keys;
#+    foreach my $row (@$desc) {
#+        if ($row->{key_name} eq 'PRIMARY') {
#+            $keys{$row->{column_name}} = $row->{seq_in_index};
#+        }                          
#+     }
#+     my (@keys) = sort { $keys{$a} <=> $keys{$b} } keys %keys;
#+     return (@keys);
#+}
#+      
#sub column_info {
#    my ($dbh, $catalog, $schema, $table, $column) = @_;
#    return $dbh->set_err(1, "column_info doesn't support table wildcard")

#############################################################################
# METHODS
#############################################################################

=head1 Methods: Import/Export Data From File

=cut

#############################################################################
# import_rows()
#############################################################################

=head2 import_rows()

    * Signature: $rep->import_rows($table, $file);
    * Signature: $rep->import_rows($table, $file, $options);
    * Param:     $table        string
    * Param:     $file         string
    * Param:     $options      named
    * Param:     columns       ARRAY     names of columns of the fields in the file
    * Param:     import_method string    [basic=invokes generic superclass to do work,
                                          insert=loads with multiple-row inserts,
                                          <otherwise>=use "load data infile"]
    * Param:     local         boolean   file is on client machine rather than database server
    * Param:     replace       boolean   rows should replace existing rows based on unique indexes
    * Param:     field_sep     char      character which separates the fields in the file (can by "\t")
    * Param:     field_quote   char      character which optionally encloses the fields in the file (i.e. '"')
    * Param:     field_escape  char      character which escapes the quote chars within quotes (i.e. "\")
    * Return:    void
    * Throws:    App::Exception::Repository
    * Since:     0.01

    Note: If you want to call this with $options->{local}, you will probably
    need to make sure that mysql_local_infile=1 is in your DSN.  This might
    require a line like the following in your "app.conf" file.

      dbioptions = mysql_local_infile=1

    Sample Usage: 

    $rep->import_rows("usr","usr.dat");

    # root:x:0:0:root:/root:/bin/bash
    $rep->import_rows("usr", "/etc/passwd" ,{
        field_sep => ":",
        columns => [ "username", "password", "uid", "gid", "comment", "home_directory", "shell" ],
    });

=cut

#SYNTAX:
#LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
#    [REPLACE | IGNORE]
#    INTO TABLE tbl_name
#    [FIELDS
#        [TERMINATED BY 'string']
#        [[OPTIONALLY] ENCLOSED BY 'char']
#        [ESCAPED BY 'char' ]
#    ]
#    [LINES
#        [STARTING BY 'string']
#        [TERMINATED BY 'string']
#    ]
#    [IGNORE number LINES]
#    [(col_name_or_user_var,...)]
#    [SET col_name = expr,...)]

sub import_rows {
    &App::sub_entry if ($App::trace);
    my ($self, $table, $columns, $file, $options) = @_;
    $columns = $self->_get_default_columns($table) if (!$columns);

    my $nrows = 0;
    my $import_method = $options->{import_method} || $self->{import_method} || "";
    if ($import_method eq "basic") {
        $nrows = $self->SUPER::import_rows($table, $columns, $file, $options);
    }
    elsif ($import_method eq "insert") {
        $nrows = $self->insert_rows($table, $columns, $file, $options);
    }
    elsif ($import_method eq "insert_mysql") {
        $nrows = $self->insert_rows_mysql($table, $columns, $file, $options);
    }
    else {
        my $local = $options->{local};
        $local = 1 if (!defined $local);
        my $local_modifier = $local ? " local" : "";
        my $sql = "load data$local_modifier infile '$file' into table $table";
        if ($options->{field_sep} || $options->{field_quote} || $options->{field_escape}) {
            $sql .= "\nfields";
            $sql .= "\n   terminated by '$options->{field_sep}'" if ($options->{field_sep});
            $sql .= "\n   optionally enclosed by '$options->{field_quote}'" if ($options->{field_quote});
            $sql .= "\n   escaped by '$options->{field_escape}'" if ($options->{field_escape});
        }
        $sql .= "\n(" . join(",", @$columns) . ")\n";
        my $context_options = $self->{context}{options};
        my $debug_sql = $context_options->{debug_sql};
        my ($timer, $elapsed_time);
        if ($debug_sql) {
            $timer = $self->_get_timer();
            print $App::DEBUG_FILE "DEBUG_SQL: import_rows()\n";
            print $App::DEBUG_FILE $sql;
        }
        eval {
            $nrows = $self->{dbh}->do($sql);
        };
        if ($debug_sql) {
            $elapsed_time = $self->_read_timer($timer);
            print $App::DEBUG_FILE "DEBUG_SQL: import_rows=[$nrows] ($elapsed_time sec) $DBI::errstr : $@\n";
        }
        die $@ if ($@);
    }

    &App::sub_exit($nrows) if ($App::trace);
    return($nrows);
}

#############################################################################
# export_rows()
#############################################################################

=head2 export_rows()

    * Signature: $rep->export_rows($table, $file);
    * Signature: $rep->export_rows($table, $file, $options);
    * Param:     $table        string
    * Param:     $file         string
    * Param:     $options      named
    * Param:     columns       ARRAY     names of columns of the fields in the file
    * Param:     export_method string    [basic=invokes generic superclass to do work]
    * Param:     field_sep     char      character which separates the fields in the file (can by "\t")
    * Param:     field_quote   char      character which optionally encloses the fields in the file (i.e. '"')
    * Param:     field_escape  char      character which escapes the quote chars within quotes (i.e. "\")
    * Return:    void
    * Throws:    App::Exception::Repository
    * Since:     0.01

    Sample Usage: 

    $rep->export_rows("usr","usr.dat");

    # root:x:0:0:root:/root:/bin/bash
    $rep->export_rows("usr", "passwd.dat" ,{
        field_sep => ":",
        columns => [ "username", "password", "uid", "gid", "comment", "home_directory", "shell" ],
    });

=cut

#SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the
#export_options part of the statement consists of the same FIELDS and LINES clauses
#that are used with the LOAD DATA INFILE statement.
#See Section 13.2.5, .LOAD DATA INFILE Syntax..

#SELECT
#    [ALL | DISTINCT | DISTINCTROW ]
#      [HIGH_PRIORITY]
#      [STRAIGHT_JOIN]
#      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
#      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
#    select_expr, ...
#    [INTO OUTFILE 'file_name' export_options
#      | INTO DUMPFILE 'file_name']
#    [FROM table_references
#      [WHERE where_definition]
#      [GROUP BY {col_name | expr | position}
#        [ASC | DESC], ... [WITH ROLLUP]]
#      [HAVING where_definition]
#      [ORDER BY {col_name | expr | position}
#        [ASC | DESC] , ...]
#      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
#      [PROCEDURE procedure_name(argument_list)]
#      [FOR UPDATE | LOCK IN SHARE MODE]]

sub export_rows {
    &App::sub_entry if ($App::trace);
    my ($self, $table, $params, $file, $options) = @_;

    if ($options->{export_method} && $options->{export_method} eq "basic") {
        $self->SUPER::export_rows($table, $file, $options);
    }
    else {
        my $columns = $options->{columns} || $self->{table}{$table}{columns};
        my $where_clause = $self->_mk_where_clause($table, $params, $options);
        my $sql = "select\n   " . join(",\n   ", @$columns);
        $sql .= "\n$where_clause" if ($where_clause); 
        $sql .= "\ninto outfile '$file'";
        if ($options->{field_sep} || $options->{field_quote} || $options->{field_escape}) {
            $sql .= "\nfields";
            $sql .= "\n   terminated by '$options->{field_sep}'" if ($options->{field_sep});
            $sql .= "\n   optionally enclosed by '$options->{field_quote}'" if ($options->{field_quote});
            $sql .= "\n   escaped by '$options->{field_escape}'" if ($options->{field_escape});
        }
        $sql .= "\n";
        my $context_options = $self->{context}{options};
        my $debug_sql = $context_options->{debug_sql};
        my ($timer, $elapsed_time);
        if ($debug_sql) {
            $timer = $self->_get_timer();
            print $App::DEBUG_FILE "DEBUG_SQL: export_rows()\n";
            print $App::DEBUG_FILE $sql;
        }
        my ($retval);
        eval {
            $retval = $self->{dbh}->do($sql);