view docs/scripts/txt/DBSchemaTablesToTextFiles.txt @ 0:4816e4a8ae95 draft default tip

Uploaded

NAME
    DBSchemaTablesToTextFiles.pl - Export table data from database
    SchemaName(s) into CSV/TSV text files

SYNOPSIS
    DBSchemaTablesToTextFiles.pl SchemaName(s)...

    DBSchemaTablesToTextFiles.pl [-d, --dbdriver mysql | Oracle| Postgres or
    Pg] [--dbhost hostname] [--dbname databasename] [--dbpassword password]
    [--dbusername username] [--exportdatalabels yes | no] [--exportlobs yes
    | no] [-h, --help] [-m, --mode exportdata | describetable] [-n,
    --numoutfilesmode single | multiple] [-o, --overwrite] [--outdelim comma
    | tab | semicolon] [-q, --quote yes | no] [-r, --root rootname]
    [--replacenullstr string] [-w --workingdir dirname] SchemaName(s)...

DESCRIPTION
    Export table data from database SchemaName(s) into CSV/TSV text files.
    Use -n --numoutfiles option to control the number of text files
    generated for a database schema.

OPTIONS
    -d, --dbdriver *mysql | Oracle | Postgres or Pg*
        Database driver name. Possible values: *mysql, Oracle, Postgres or
        Pg*. Default: *MySQL* or value of environment variable DBI_DRIVER.
        This script has only been tested with MySQL, Oracle and PostgreSQL
        drivers.

    --dbhost *hostname*
        Database host name. Default: *127.0.0.1* for both MySQL and Oracle.
        For remote databases, specify complete remote host domain:
        *dbhostname.org* or something like it.

    --dbname *databasename*
        Database name. Default: mysql for MySQL, postgres for PostgreSQL and
        none for Oracle. For connecting to local/remote Oracle databases,
        this value can be left undefined assuming --dbhost is correctly
        specified.

    --dbpassword *password*
        Database user password. Default: *none* and value of environment
        variable DBI_PASS is used for connecting to database.

    --dbusername *username*
        Database user name. Default: *none* and value of environment
        variable DBI_USER is used for connecting to database.

    --exportdatalabels *yes | no*
        This option is mode specific and controls exporting of column data
        labels during exportdata mode. Possible values: *yes or no*.
        Default: *yes*.

    --exportlobs *yes | no*
        This option is mode specific and controls exporting of CLOB/BLOB or
        BYTEA data columns during exportdata mode. Possible values: *yes or
        no*. Default: *no*.

    -h, --help
        Print this help message.

    -m, --mode *exportdata | describetable*
        Data selection criterion from database. Possible values: *exportdata
        or describetable*. Default value: *exportdata*.

    -n, --numoutfilesmode *single | multiple*
        Number of CSV/TSV output files to generate: combine output into one
        file or generate a different file for each table in a schema.
        Possible values: *single or multiple*. Default: *single*.

        In a single output file, data for different tables is separated by a
        blank line.

        Single outfile option in *exportdata* mode is quite useful for
        exporting data from all tables in specifed schemas to one file which
        can be used for migrating data to another database or simply provide
        a backup of data; during *describetable* mode, it provides a means
        to collect information about columns of all schema tables which can
        help in creation of these tables on a different database server.

    -o, --overwrite
        Overwrite existing files.

    --outdelim *comma | tab | semicolon*
        Output text file delimiter. Possible values: *comma, tab, or
        semicolon*. Default value: *comma*

    -q, --quote *yes | no*
        Put quotes around column values in output text file. Possible
        values: *yes or no*. Default value: *yes*.

    -r, --root *rootname*
        New file name is generated using the root:<Root>.<Ext> and
        <Root><TableName>.<Ext> for *single* and *multiple* -n --numoutfiles
        option values. Default file name for *single* -n --numoutfiles
        option value: <Mode>SchemaTables.<Ext>. Default file names for
        *multiple* -n --numoutfiles value:
        <Mode><SchemaName><TableName>.<Ext>. Based on -m --mode option,
        *Export* or *Describe* <Mode> value is used. The csv and tsv <Ext>
        values are used for comma/semicolon, and tab delimited text files
        respectively. This option is ignored for multiple input schema
        names.

    --replacenullstr *string*
        Replace NULL or undefined row values with specified value. Default:
        *none*.

        For importing output text files into MySQL database using "load data
        local infile '<tablename>.tsv' into table <tablename>" command, use
        *--raplacenullstr "NULL"* in conjunction with *--exportdatalabels
        no*, *--quote no*, and *--outdelim tab* options: it'll generate
        files for direct import into MySQL assuming tables already exists.

    -w --workingdir *dirname*
        Location of working directory. Default: current directory.

EXAMPLES
    To export data in all tables from mysql schema on a MySQL server running
    on a local machine using username/password from DBI_USER and DBI_PASS
    environmental variables, type:

        % DBSchemaTablesToTextFiles.pl mysql

    To describe all tables in mysql and test schemas on a MySQL server
    running on a remote machine using explicit username/password and
    capturing the ouput into a DescribeTables.csv file, type:

        % DBSchemaTablesToTextFiles.pl --dbdriver mysql --dbuser <name>
          --dbpassword <pasword> --dbname mysql --dbhost
          <mysqlhostname.org> -r DescribeTable -m describetable
          -o mysql test

    To describe all tables in SCOTT schema in Oracle running on a remote
    machine using explicit username/password and capturing the ouput into a
    DescribeAllTable.tsv file, type:

        % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
          --dbpassword <pasword> --dbhost <oraclehostname.com>
          -r DescribeAllTable -m describetable --outdelim tab --quote no
          -o SCOTT

    To export data in all tables in mysql and test schemas on a MySQL server
    running at a local machine using explicit username/password and
    capturing the data in TSV file for each table with empty values
    substitued with NULL and clob/blob data, type:

        % DBSchemaTablesToTextFiles.pl --dbdriver Oracle --dbuser <name>
          --dbpassword <pasword> -r ExportTables --outdelim tab --quote no
          --replacenullstr "NULL" -m exportdata --exportlobs no  --numoutfiles
          multiple -o user user_info

AUTHOR
    Manish Sud <[e-mail]>

SEE ALSO
    DBSQLToTextFiles.pl, DBTablesToTextFiles.pl

COPYRIGHT
    Copyright (C) 2015 Manish Sud. All rights reserved.

    This file is part of MayaChemTools.

    MayaChemTools is free software; you can redistribute it and/or modify it
    under the terms of the GNU Lesser General Public License as published by
    the Free Software Foundation; either version 3 of the License, or (at
    your option) any later version.