dbclear - Delete Data

dbclear is a tool to delete data from tables in a database, either directly or by producing an SQL script to be executed later.

By default, all tables in the database are cleared. However, this can be restricted by schema and/or by table.

Prerequisites

Because this script needs to get at metadata for the database, it uses the dbmsjy wrapper and a JDBC driver. See the dbmsjy page for details.

You use the tool like this:

dbmsjy.py dbclear.py [dbOptions] [generalOptions]

or, specifying JDBC details directly:

dbmsjy.py dbclear.py [jdbcOptions] [generalOptions]

`-d dbms`	Name of the DBMS. Required. Values recognised are: postgres7, postgres8, mssql, oracle, mysql, hsql
`-D dbName`	Name of the database to connect to. Required
`-H dbHost`	Name or IP address of the database host to connect to. Optional, defaults to localhost
`-P dbPort`	Port of the database host to connect to. Optional, defaults to the default port for the DBMS

`-c driverClass`	Fully-qualified name of the JDBC driver class, e.g. org.postgresql.Driver. If omitted, will be defaulted based on URL. Must be present in classpath, usually by placing in the `jdbc-drivers` directory
`-j JDBC_URL`	JDBC URL for connecting to the database, in the format expected by the JDBC driver, e.g. `jdbc:postgresql://localhost:5432/PS30` Required

`-e tableName`	Specifically exclude this table (add a -e option for each table you want to exclude)
`-f filepath`	Output file path. If omitted, output to stdout
`-h`	Shows this screen
`-i tableName`	Specifically include this table (add a -i option for each table you want to include)
`-p password`	Password to use to connect to DB (if required)
`-s schema`	Schema name to export (defaults to all schemas)
`-u username`	Username to use to connect to DB (if required)
`-v`	Enable verbose logging (to stderr)
`-x`	Execute - rather than generating a script to clear the database, actually clear it using JDBC.

`--force-delete`	Always delete, rather than truncating
`--version`	Print version number and exit

See the dbmsjy page for details of default JDBC drivers and URLs etc.

Clear (directly) all tables from a local Postgres database called mydb:

$ dbmsjy.py dbclear.py -u fred -p itsasecret -d postgres8 -D mydb -x

Generate an SQL script (to stdout) to clear 2 named tables from a local MySQL database:

$ dbmsjy.py dbclear.py -i tableone -i table2 -u fred -p itsasecret \
    -d mysql -D mydb

Generate an SQL script (to a specified path) to clear all tables in the specified schema from an Oracle database on host bigserver:

$ dbmsjy.py dbclear.py -s theschema -f /tmp/clearItAll.sql \
    -u fred -p itsasecret -d oracle -H bigserver -D mydb