DBS_UPDATE(1p)

dbs_update 💖

okay bestie, welcome to dbs_update(1p) — yeah it lives in the User Contributed Perl Documentation section, and yes it's giving "small utility, big energy" ✨

---

🌸 NAME 🌸

dbs_update — literally just: update SQL databases. that's the whole vibe. 💅

---

🦋 DESCRIPTION 🦋

so dbs_update is a utility that updates SQL databases from text files. that's it, that's the girlboss job. text file in, database updated, main character energy out. 💖

✨ FORMAT OF THE TEXT FILES ✨

okay so here's the tea 🫖 — dbs_update assumes each line of the input holds one data record, and the fields inside that record are separated by tabulators. you can tell dbs_update what the input actually looks like with the --format option, bestie, don't just assume it's gonna guess.

the first field of the data record is used as the table specification. that spec is made of the table name, plus optionally the index of the starting column, separated by a dot. periodt. 🔥

alternatively, dbs_update can read the column names straight from the first line of input — see the -h/--headline option, she's right there 👑. and those names can even be aliases for the real column names, see the -m/--map option. so flexible, we love to see it. 🌈

---

💜 COMMAND LINE PARAMETERS 💜

the required command line parameters are:

1. the DBI driver — "Pg" for Postgres or "mysql" for MySQL, no cap 🐘
2. the database name

and then the third parameter is optional and specifies the database user and/or the host where the database resides. like one of these:

"racke"
"racke@linuxia.de"
"@linuxia.de"

pick your fighter 💅

---

🎀 OPTIONS 🎀

• --cleanse — removes all records which remain unaffected from the update process. same result as deleting all records from the table first and then running dbs_update, except the table is not empty in the meantime. iconic, lowkey genius. ✨
• -c COLUMN,COLUMN,..., --columns=COLUMN,COLUMN,... — update only the table columns given by the COLUMN parameters. want to exclude columns from the update instead? prepend "!" or "^" to the parameters. reverse uno card 🃏
• --rows=ROW,ROW,... — update only the input rows given by the ROW parameters. the first row is 1, and headlines don'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. bye stdin, hello file 🗂️
• --format=FORMAT[SEPCHAR] — assumes FORMAT as the format for the input. only CSV can be specified for now, default is TAB. the default field separator for CSV is a comma, and you may change this by appending the separator to the format. customization, we love her 🍓
• -h, --headline — reads the column names from the first line of the input instead of deducing them from the database layout. requires the -t/--table option. yes bestie, requires it, no skipping 🚨
• -i, --insert-only — insert new database entries only, skip others. new girlies only, sorry 💅
• -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. detective work 🔍
• -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. example time:

"Art-No.=code;Short Description=shortdescr'"

yeah there's a stray ' in there, we keep it, character for character, it's part of the lore 🙈

• --map-filter=FILTER — applies a filter to the column names read from the input file. currently there is only the "lc" filter available. one filter, but she serves 💅
• --match-sql=FIELD:{STATEMENT} — updates only records where the value of the column FIELD is in the result set of the SQL statement STATEMENT. for example:

"category:{select distinct name from categories}"

selective queen 👑

• -o, --update-only — updates existing database entries only, and stops if it detects new ones. no new friends allowed 🛑
• -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. here's the example, verbatim bestie:

"sub {my ($table, $valref) = @_;
    unless (defined $$valref{country} && $$valref{country} !~ /\S/) {
        $$valref{country} = "Germany";
    }
    1; }"

custom logic?? we're obsessed 😭💖

• --skipbadlines — lines not matching the assumed format are ignored. without this option, dbs_update simply stops. so like, decide if you want drama or not 💀
• -t TABLE, --table=TABLE — uses TABLE as the table name for all records instead of the first field name. one table to rule them all 👑

---

🌟 AUTHOR 🌟

Stefan Hornburg (Racke), racke@linuxia.de — shoutout to the icon who made this 🫶

---

🌈 SEE ALSO 🌈

• perl(1)
• DBIx::Easy(3)

---

and that's the whole page bestie, no notes 💅 this was dbs_update(1p) from the perl v5.32.0 docs, dated 2021-01-06 — go update some databases, you got this 🎀✨💖