.\" -*- mode: troff; coding: utf-8 -*-
.\" Automatically generated by Pod::Man 5.01 (Pod::Simple 3.43)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" \*(C` and \*(C' are quotes in nroff, nothing in troff, for use with C<>.
.ie n \{\
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "DBS_UPDATE 1p"
.TH DBS_UPDATE 1p 2024-03-24 "perl v5.38.2" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH NAME
dbs_update \- Update SQL Databases
.SH DESCRIPTION
.IX Header "DESCRIPTION"
dbs_update is an utility to update SQL databases from text files.
.SS "FORMAT OF THE TEXT FILES"
.IX Subsection "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 \fB\-\-format\fR
option.
.PP
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.
.PP
Alternatively dbs_update can read the column names from the first line
of input (see the \fB\-h\fR/\fB\-\-headline\fR option). These can even be aliases
for the real column names (see the \fB\-m\fR/\fB\-\-map\fR option).
.SH "COMMAND LINE PARAMETERS"
.IX Header "COMMAND LINE PARAMETERS"
Required command line parameters are the DBI driver
(\f(CW\*(C`Pg\*(C'\fR for Postgres or \f(CW\*(C`mysql\*(C'\fR for MySQL)
and the database name. The third parameter is optionally
and specifies the database user and/or the host where the
database resides (\f(CW\*(C`racke\*(C'\fR, \f(CW\*(C`racke@linuxia.de\*(C'\fR or \f(CW\*(C`@linuxia.de\*(C'\fR).
.SH OPTIONS
.IX Header "OPTIONS"
.SS \fB\-\-cleanse\fP
.IX Subsection "--cleanse"
\&\fIRemoves\fR 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.
.SS "\fB\-c\fP \fICOLUMN,COLUMN,...\fP, \fB\-\-columns\fP=\fICOLUMN,COLUMN,...\fP"
.IX Subsection "-c COLUMN,COLUMN,..., --columns=COLUMN,COLUMN,..."
Update only the table columns given by the \fICOLUMN\fR parameters.
To exclude columns from the update prepend \f(CW\*(C`!\*(C'\fR or \f(CW\*(C`^\*(C'\fR to the
parameters.
.SS \fB\-\-rows\fP=\fIROW,ROW,...\fP
.IX Subsection "--rows=ROW,ROW,..."
Update only the input rows given by the \fIROW\fR parameters.
The first row is 1 where headlines doesn't count.
To exclude rows from the update prepend \f(CW\*(C`!\*(C'\fR or \f(CW\*(C`^\*(C'\fR to the
parameters.
.SS "\fB\-f\fP \fIFILE\fP, \fB\-\-file\fP=\fIFILE\fP"
.IX Subsection "-f FILE, --file=FILE"
Reads records from file \fIFILE\fR instead of from standard input.
.SS \fB\-\-format\fP=\fIFORMAT[SEPCHAR]\fP
.IX Subsection "--format=FORMAT[SEPCHAR]"
Assumes \fIFORMAT\fR as format for the input. Only \fBCSV\fR can be
specified for now, default is \fBTAB\fR. The default field
separator for \fBCSV\fR is a comma, you may change this by
appending the separator to the format.
.SS "\fB\-h\fP, \fB\-\-headline\fP"
.IX Subsection "-h, --headline"
Reads the column names from the first line of the input instead
of dedicting them from the database layout. Requires the
\&\fB\-t/\-\-table\fR option.
.SS "\fB\-i\fP, \fB\-\-insert\-only\fP"
.IX Subsection "-i, --insert-only"
Insert new database entries only, skip others.
.SS "\fB\-k\fP \fICOUNT\fP, \fB\-k\fP \fIKEY,KEY,...\fP, \fB\-\-keys\fP=\fICOUNT\fP, \fB\-\-keys\fP=\fIKEY,KEY,...\fP"
.IX Subsection "-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.
.SS "\fB\-m\fP \fIALIASDEF\fP, \fB\-\-map\fP=\fIALIASDEF\fP"
.IX Subsection "-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 \f(CW\*(C`=\*(C'\fR signs and the different entries are separated by \f(CW\*(C`;\*(C'\fR signs,
e.g. \f(CW\*(C`Art\-No.=code;Short Description=shortdescr\*(Aq\*(C'\fR.
.SS \fB\-\-map\-filter\fP=\fIFILTER\fP
.IX Subsection "--map-filter=FILTER"
Applies a filter to the column names read from the input file.
Currently there is only the \f(CW\*(C`lc\*(C'\fR filter available.
.SS \fB\-\-match\-sql\fP=\fIFIELD:{STATEMENT}\fP
.IX Subsection "--match-sql=FIELD:{STATEMENT}"
Updates only records where the value of the column \fIFIELD\fR is in
the result set of the SQL statement \fISTATEMENT\fR,
e.g. \f(CW\*(C`category:{select distinct name from categories}\*(C'\fR.
.SS "\fB\-o\fP, \fB\-\-update\-only\fP"
.IX Subsection "-o, --update-only"
Updates existing database entries only, stops if it detects
new ones.
.SS "\fB\-r\fP \fIROUTINE\fP, \fB\-\-routine\fP=\fIROUTINE\fP"
.IX Subsection "-r ROUTINE, --routine=ROUTINE"
Applies \fIROUTINE\fR to any data record. \fIROUTINE\fR 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 \fIROUTINE\fR is not
a truth value, the data record will be skipped.
.PP
\&\f(CW\*(C`sub {my ($table, $valref) = @_;
    unless (defined $$valref{country} && $$valref{country} !~ /\eS/) {
        $$valref{country} = "Germany";
    }
    1;
}\*(C'\fR
.SS \fB\-\-skipbadlines\fP
.IX Subsection "--skipbadlines"
Lines not matching the assumed format are ignored. Without this
option, dbs_update simply stops.
.SS "\fB\-t\fP \fITABLE\fP, \fB\-\-table\fP=\fITABLE\fP"
.IX Subsection "-t TABLE, --table=TABLE"
Uses \fITABLE\fR as table name for all records instead of the
first field name.
.SH AUTHOR
.IX Header "AUTHOR"
Stefan Hornburg (Racke), racke@linuxia.de
.SH "SEE ALSO"
.IX Header "SEE ALSO"
\&\fBperl\fR\|(1), \fBDBIx::Easy\fR\|(3)