Mercurial > repos > deepakjadmin > mayatool3_test2
diff docs/scripts/txt/DBSchemaTablesToTextFiles.txt @ 0:4816e4a8ae95 draft default tip
Uploaded
author | deepakjadmin |
---|---|
date | Wed, 20 Jan 2016 09:23:18 -0500 |
parents | |
children |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/docs/scripts/txt/DBSchemaTablesToTextFiles.txt Wed Jan 20 09:23:18 2016 -0500 @@ -0,0 +1,165 @@ +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 <msud@san.rr.com> + +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. +