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", " 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)