NAME¶
dbs_update - Update SQL Databases
DESCRIPTION¶
dbs_update is an utility to update SQL databases from text files.
FORMAT OF THE TEXT FILES
dbs_update assumes that each line of the input contains a data record and that
the field within the records are separated by tabulators. You can tell
dbs_update about the input format with the
--format option.
The first field of the data record is used as table specification. These
consists of the table name and optionally the index of starting column,
separated by a dot.
Alternatively dbs_update can read the column names from the first line of input
(see the
-h/
--headline option). These can even be aliases for
the real column names (see the
-m/
--map option).
COMMAND LINE PARAMETERS¶
Required command line parameters are the DBI driver ("Pg" for Postgres
or "mysql" for MySQL) and the database name. The third parameter is
optionally and specifies the database user and/or the host where the database
resides ("racke", "racke@linuxia.de" or
"@linuxia.de").
OPTIONS¶
--cleanse
Removes all records which remain unaffected from the update process. The
same result as deleting all records from the table first and then running
dbs_update, but the table is not empty in the meantime.
-c COLUMN,COLUMN,...,
--columns= COLUMN,COLUMN,...
Update only the table columns given by the
COLUMN parameters. To exclude
columns from the update prepend "!" or "^" to the
parameters.
--rows=ROW,ROW,...
Update only the input rows given by the
ROW parameters. The first row is
1 where headlines doesn't count. To exclude rows from the update prepend
"!" or "^" to the parameters.
-f FILE,
--file=FILE
Reads records from file
FILE instead of from standard input.
--format=FORMAT[SEPCHAR]
Assumes
FORMAT as format for the input. Only
CSV can be specified
for now, default is
TAB. The default field separator for
CSV is
a comma, you may change this by appending the separator to the format.
-h, --headline
Reads the column names from the first line of the input instead of dedicting
them from the database layout. Requires the
-t/--table option.
-k COUNT, -k
KEY,KEY,..., --keys=COUNT,
--keys =KEY,KEY,...
Specifies the keys for the table(s) either as the number of columns used as keys
or by specifying them explicitly as comma separated arguments to the option.
This is used for the detection of existing records.
-m ALIASDEF,
--map=ALIASDEF
Maps the names found in the first line of input to the actual column names in
the database. The alias and the column name are separated with "="
signs and the different entries are separated by ";" signs, e.g.
"Art-No.=code;Short Description=shortdescr'".
--map-filter=FILTER
Applies a filter to the column names read from the input file. Currently there
is only the "lc" filter available.
--match-sql=FIELD:{STATEMENT}
Updates only records where the value of the column
FIELD is in the result
set of the SQL statement
STATEMENT, e.g. "category:{select
distinct name from categories}".
-o, --update-only
Updates existing database entries only, stops if it detects new ones.
-r ROUTINE,
--routine=ROUTINE
Applies
ROUTINE to any data record.
ROUTINE must be a subroutine.
dbs_update passes the table name and a hash reference to this subroutine. The
keys of the hash are the column names and the values are the corresponding
field values. If the return value of
ROUTINE is not a truth value, the
data record will be skipped.
"sub {my ($table, $valref) = @_;
unless (defined $$valref{country} && $$valref{country} !~ /\S/) {
$$valref{country} = "Germany";
}
1; }"
--skipbadlines
Lines not matching the assumed format are ignored. Without this option,
dbs_update simply stops.
-t TABLE,
--table=TABLE
Uses
TABLE as table name for all records instead of the first field name.
AUTHOR¶
Stefan Hornburg (Racke), racke@linuxia.de
SEE ALSO¶
perl(1),
DBIx::Easy(3)