DbSchemaCLI is a universal database command-line client. DbSchemaCLI is part of the standard installation kit.
Connect to the database using:
connection mysql -dbms MySql -h localhost -u dbschema -p dbschema12 -d sakila |
Before creating database connections, you have to download the JDBC drivers for the databases we are working with. For this execute in DbSchemaCLI the command:
download driver |
Example: download driver PostgreSQL or download driver MySql. The drivers will be downloaded in C:\Users\YourUser\.DbSchema\drivers. Alternatively, download the JDBC driver .jar files by yourself and place them in the /home/users/.DbSchema/drivers/<rdbms>
In the User Home Directory (C:\Users\YourUser or /home/YourUser ) DbSchemaCLI is creating a folder .DbSchema/cli. with a file init.sql. DbSchemaCLI is executing this script at startup. So is easy to define the database connections right in this file.
Example:
register driver PostgreSql org.postgresql.Driver jdbc:postgresql://{HOST}:{PORT}/{DB} "port=5432" |
The register driver command defines for each RDBMS (MySql,Postgres,Oracle...) the:
The connection command defines the connection by setting the real values for each URL parameter. The RDBMS from the register driver command should match the RDBMS from the connection command.
The complete list of known drivers is the same as for DbSchema.
This operation assumes that the queried tables exists on each of the connected databases. The connections 'pos1' and 'pos2' should be already defined.
connect pos1,pos2 |
Create connection groups.
connection group production pos1,pos2 |
connect production exclude my3 |
vset cli.spool.separator=| |
spool /tmp/export_&myid.csv |
connect pos1 |
The transfer is running on a configurable number of threads.
vset cli.settings.transfer_threads=4
Execute 'help' to see all configurable parameters.
There is one reader thread and multiple writer threads. Each writer thread is writing a bunch of rows, then it commits the transaction. The writer threads are writing between 500 and 100.000 records, depending on the number of transferred columns ( 500 for more than 50 columns, 100.000 for 1 column ).
Many database tasks require more logic than simple SQLs. We do this using Groovy, a pure Java scripting language with closures. Groovy code can be started with the keyword groovy and ends with the first line with only a slash '/'.
Everything which works in Java is working in Groovy, with these improvements:
connect db1 |
Groovy scripts receive two objects :
Updating or deleting large amounts of data may require splitting the operation into smaller chunks. This because the large operation may cause disk or memory issues and may lead to database locks.
groovy |
groovy |
def cli = new CliBuilder(usage:'disk usage ') |
There are cases when you have to manage large database clusters where you have primaries and standby databases. I used to manage a cluster where the primary database hostnames were like 'pos1.db', 'pos2.db', ... and standbys were 'pos1.sbydb', 'pos2.sbydb'...
For this I let this script to create the standby database connections:
groovy |
vset cli.mail.server.host=internal.mailserver |
01 * * * * /usr/local/bin/dbschemacli -cron |
sales_report.schedule_hour(13)_dayOfWeek(1).sql |
upload sftp://[email protected]/intoFolder report.csv |
For many reasons, you may need to store passwords outside the init.sql file, where the database connections are being defined. This would allow saving the init.sql file in GIT without making passwords public. Therefore is possible to store passwords in a separate file ~/.DbSchema/dbshell/pass.txt or ~/.pgpass (for compatibility with PostgreSQL standards). The file format is the same as the PostgreSQL pgpass
hostname:port:database:username:password |
*:1521:sakila:postgres:secret |
The Floating License Server is used to manage and distribute floating licenses for DbSchema within an organization. It allows multiple users to share access to DbSchema while ensuring that the total number of concurrent users does not exceed the number of floating licenses purchased.
The Floating License Server can be started from DbSchemaCLI, which is part of the standard installation kit.
On the client computer, configure the Floating License Server in the Help / Registration Dialog / Floating License tab.
Automatically restart the Floating License Server by storing this into a file and adding DbSchemaCLI <path-to-file> to the Windows Task Scheduler
license server -s |
The floating license server host name and port can be set on the computer where the DbSchema execute as environment variables DBSCHEMA_FLS_HOST and DBSCHEMA_FLS_PORT.
DbSchemaCLI can be started with the following parameters:
Run this commands to create the docker container:
docker build -f Dockerfile.DbSchema2 -t dbschema-docker4 .
docker run --rm -it -v /tmp/.X11-unix:/tmp/.X11-unix -e DISPLAY=:0 --name my-dbschema4 dbschema-docker4:latest ${PWD}/scripts/test.groovy#
# DESCRIPTION: Image with DbSchema
# TO_BUILD: docker build -t amcorreia/docker-dbschema .
# TO_RUN: docker run -d --rm -it -v /tmp/.X11-unix:/tmp/.X11-unix -e DISPLAY=unix$DISPLAY --name dbschema amcorreia/docker-dbschema
#
# NOTE: THIS DOCKERFILE IS GENERATED VIA "apply-templates.sh"
#
# PLEASE DO NOT EDIT IT DIRECTLY.
#
FROM buildpack-deps:buster-scm
RUN set -eux; \
apt-get update; \
apt-get install -y --no-install-recommends \
bzip2 \
unzip \
xz-utils \
\
# jlink --strip-debug on 13+ needs objcopy: https://github.com/docker-library/openjdk/issues/351
# Error: java.io.IOException: Cannot run program "objcopy": error=2, No such file or directory
binutils \
\
# java.lang.UnsatisfiedLinkError: /usr/local/openjdk-11/lib/libfontmanager.so: libfreetype.so.6: cannot open shared object file: No such file or
directory
# java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11FontManager
# https://github.com/docker-library/openjdk/pull/235#issuecomment-424466077
fontconfig libfreetype6 \
\
# utilities for keeping Debian and OpenJDK CA certificates in sync
ca-certificates p11-kit \
libx11-6 libgl1 libxrender1 libxi6 libxtst6 libfreetype6 x11-xserver-utils
ENV JAVA_HOME /usr/local/openjdk-17
ENV PATH $JAVA_HOME/bin:$PATH
# Default to UTF-8 file.encoding
ENV LANG C.UTF-8
# https://jdk.java.net/
# >
# > Java Development Kit builds, from Oracle
# >
ENV JAVA_VERSION 17.0.2
RUN set -eux; \
\
arch="$(dpkg --print-architecture)"; \
case "$arch" in \
'amd64') \
downloadUrl='https://download.java.net/java/GA/jdk17.0.2/dfd4a8d0985749f896bed50d7138ee7f/8/GPL/openjdk-17.0.2_linux-x64_bin.tar.gz'; \
downloadSha256='0022753d0cceecacdd3a795dd4cea2bd7ffdf9dc06e22ffd1be98411742fbb44'; \
;; \
'arm64') \
downloadUrl='https://download.java.net/java/GA/jdk17.0.2/dfd4a8d0985749f896bed50d7138ee7f/8/GPL/openjdk-17.0.2_linux-aarch64_bin.tar.gz'; \
downloadSha256='13bfd976acf8803f862e82c7113fb0e9311ca5458b1decaef8a09ffd91119fa4'; \
;; \
*) echo >&2 "error: unsupported architecture: '$arch'"; exit 1 ;; \
esac; \
\
wget --progress=dot:giga -O openjdk.tgz "$downloadUrl"; \
echo "$downloadSha256 *openjdk.tgz" | sha256sum --strict --check -; \
\
mkdir -p "$JAVA_HOME"; \
tar --extract \
--file openjdk.tgz \
--directory "$JAVA_HOME" \
--strip-components 1 \
--no-same-owner \
; \
rm openjdk.tgz*; \
\
# update "cacerts" bundle to use Debian's CA certificates (and make sure it stays up-to-date with changes to Debian's store)
# see https://github.com/docker-library/openjdk/issues/327
# http://rabexc.org/posts/certificates-not-working-java#comment-4099504075
# https://salsa.debian.org/java-team/ca-certificates-java/blob/3e51a84e9104823319abeb31f880580e46f45a98/debian/jks-keystore.hook.in
# https://git.alpinelinux.org/aports/tree/community/java-cacerts/APKBUILD?id=761af65f38b4570093461e6546dcf6b179d2b624#n29
{ \
echo '#!/usr/bin/env bash'; \
echo 'set -Eeuo pipefail'; \
echo 'trust extract --overwrite --format=java-cacerts --filter=ca-anchors --purpose=server-auth "$JAVA_HOME/lib/security/cacerts"'; \
} > /etc/ca-certificates/update.d/docker-openjdk; \
chmod +x /etc/ca-certificates/update.d/docker-openjdk; \
/etc/ca-certificates/update.d/docker-openjdk; \
\
# https://github.com/docker-library/openjdk/issues/331#issuecomment-498834472
find "$JAVA_HOME/lib" -name '*.so' -exec dirname '{}' ';' | sort -u > /etc/ld.so.conf.d/docker-openjdk.conf; \
ldconfig; \
\
# https://github.com/docker-library/openjdk/issues/212#issuecomment-420979840
# https://openjdk.java.net/jeps/341
java -Xshare:dump; \
\
# basic smoke test
fileEncoding="$(echo 'System.out.println(System.getProperty("file.encoding"))' | jshell -s -)"; [ "$fileEncoding" = 'UTF-8' ]; rm -rf ~/.java; \
javac --version; \
java --version
# # "jshell" is an interactive REPL for Java (see https://en.wikipedia.org/wiki/JShell)
# CMD ["jshell"]
# # RUN apt-get update --yes --quiet && \
# # apt-get install --yes --quiet --no-install-recommends wget apt-utils ca-certificates && \
# RUN wget --no-verbose -O /tmp/dbschema.deb https://dbschema.com/download/DbSchema_linux_9_0_0.deb && \
# dpkg -i /tmp/dbschema.deb && \
# rm /tmp/dbschema.deb && \
# rm -rf -- /var/lib/apt/lists/*
COPY DbSchema_linux_9_0_0.deb .
RUN dpkg --install DbSchema_linux_9_0_0.deb
RUN mkdir -p /app/scripts
COPY ./scripts/test.groovy /app/scripts/
RUN useradd --create-home --shell /bin/bash user
USER user
ENTRYPOINT /opt/DbSchema/DbSchema -x /app/scripts/test.groovyjre/bin/java -Djava.awt.headless=true -Dglass.platform=Monocle -Dmonocle.platform=Headless -Dprism.order=sw -cp "lib/*" com.wisecoders.dbs.DbSchema -x
sample.groovy