Skip to content

README.md

Latest commit

 

History

History
233 lines (177 loc) · 9.02 KB

README.md

File metadata and controls

233 lines (177 loc) · 9.02 KB
Raw

Database Utility

Beangle Database Development Utility — JDBC-based tools for schema work, data transport, and an interactive SQL shell.

Launch

Download the launcher and run it against a config XML (real terminal recommended for history / Tab):

wget https://raw.githubusercontent.com/beangle/sqlplus/main/src/main/scripts/sqlplus.sh
chmod +x sqlplus.sh

./sqlplus.sh /path/to/db.xml
./sqlplus.sh transport /path/to/conversion.xml
./sqlplus.sh validate /path/to/basis.xml
Mode Example Description
Interactive shell ./sqlplus.sh db.xml Connect and run SQL / meta commands
Transport ./sqlplus.sh transport conversion.xml Copy data between databases
Validate ./sqlplus.sh validate basis.xml Diff live schema against a basis XML

Database config is an XML with a <source> (driver, url, user, password). See test resources under src/test/resources/ for samples.

What sqlplus.sh does

The script is a zero-install style launcher (similar to other Beangle boot scripts):

  1. Bootstrap jars — downloads Scala runtime, beangle-boot, beangle-commons, logging, and beangle-sqlplus into $M2_REPO (default ~/.m2/repository) from $M2_REMOTE_REPO (default Aliyun Maven).

  2. Resolve dependencies — runs beangle-boot AppResolver so transitive JDBC drivers and libraries are fetched into the local Maven repo.

  3. Build classpath & start — resolves the main class and full classpath, then executes:

    java -cp <classpath> org.beangle.sqlplus.shell.Main <your args...>

All arguments after the script are passed through unchanged. Optional env vars:

Variable Default Meaning
M2_REMOTE_REPO https://maven.aliyun.com/repository/public Remote Maven repository
M2_REPO $HOME/.m2/repository Local Maven cache

The script pins beangle-sqlplus to a release version (see beangle_sqlplus_ver in the script). For development from source use sbt "run /path/to/db.xml" instead.

Interactive shell

Built on JLine 4 (JNI terminal backend). Prefer a real TTY: system terminal, Windows Terminal, or IDEA “Emulate terminal”. Plain IDE consoles may fall back to dumb mode (no arrow/Tab).

Line editing & history

  • ↑ / ↓ browse history (persisted in ~/.sqlplus_history)
  • ← / → move cursor; Ctrl+R search history
  • Ctrl+C clear current line; Ctrl+D or exit / quit / q leave
  • Wrong / failed commands are still stored in history (same as bash)

Multi-line SQL (psql-style)

If the first significant line looks like SQL (select / insert / …) and there is no terminator yet, JLine keeps reading with secondary prompt -> until you end with ;, /, or \G. The whole statement is one history entry (↑ recalls all lines together). Non-SQL meta commands still complete on a single Enter.

db> select id, name
   -> from users
   -> where active = true;

Meta commands

Command Description
help Show help
info Test connection / show DB info
list schema List schemas
use schema Switch schema
find pattern Find tables/views (optional table / view prefix)
desc name Describe table/view (see below)
list tmp / drop tmp List or drop temporary-like tables
dump schema Dump schema to XML
report schema HTML schema report
validate schema Validate against basis.xml
dump data Dump data into local H2
@file.sql / source file.sql Run a SQL script (;-separated)

desc

  • Table columns: primary-key columns first (PK definition order), then other columns alphabetically (case-insensitive).
  • Views: columns sorted alphabetically.
  • If a primary key exists, the footer line is marked: 🔑 primary key: ...

SQL

  • Supported starters: select / insert / update / delete / alter / create / drop / grant
  • End a statement with ; or /
  • Once-off vertical layout: end with \G (psql-like expanded / mysql \G)
  • Each statement prints elapsed time; DML shows affected row counts

Result display & settings

Default table format is psql aligned (display-width aware for CJK so Chinese columns stay aligned).

set                  # show settings
set limit 50         # max rows on console (0 = unlimited, default 10; ignored while spooling)
set width 40         # max column display width (default 50)
set format table     # console: psql-style aligned table
set format vertical  # console: expanded records (psql \x style)
spool /tmp/out.csv   # SELECT → full CSV (no set limit); console shows summary only
spool off            # stop spooling; console format/limit unchanged

With spool on, SELECT rows are streamed as CSV to the file — the full result set (SQL LIMIT / filters still apply; console set limit does not). Console format is only table | vertical (plus once-off \G) and is left unchanged across spool / spool off. The console only shows summaries such as (N rows) and timing; status lines are never written into the spool file.

Tab completion

Press Tab to complete. Scope:

Context Completes
Start of line Shell commands (help, dump schema, set format …, …)
Mid-line (generic) Fixed SQL keywords (select, from, where, join, …) — not dialect-specific
After use Schema names from the current connection
After find / desc / from / join / update / into / table Table/view names from cached JDBC metadata (same cache as find / desc)

Notes:

  • Keywords are a static ANSI-ish list, shared for all databases (not parsed SQL grammar).
  • Object names come from the current database metadata (lazy-loaded, shared with find).
  • At most 100 name candidates; if more: ... (N more) at the end of the list.
  • First metadata load may take a moment (same cost as first find).

Not supported (by design for now):

  • Column-name completion (e.g. after select / where)
  • Engine-specific keywords (ILIKE, RETURNING, dual, …)
  • Context-aware SQL parsing (keywords may still appear where they are not valid)

Transport data from db1 to db2

Edit config file (oracle to postgresql etc.)

<?xml version="1.0" encoding="UTF-8"?>
<transport maxthreads="10">
  <source>
    <driver>oracle</driver>
    <url>jdbc:oracle:thin:@//192.168.100.1:1521/public</url>
    <user>user</user>
    <password>password</password>
  </source>
  <target>
    <driver>postgresql</driver>
    <url>jdbc:postgresql://192.168.100.2:5432/urp</url>
    <user>user</user>
    <password>password</password>
  </target>

  <task from="user" to="user">
    <tables to-case="lower" index="true" constraint="true" unlogged="true">
      <includes>*</includes>
      <excludes></excludes>
    </tables>
  </task>

  <actions>
     <before>
       <sql file="/path/to/sql/file/do/something/in/oracle.sql"/>
     </before>
     <after>
       <sql file="/path/to/sql/file/do/something/in/postgresql.sql"/>
     </after>
  </actions>
</transport>

Large convergent INSERT ... SELECT actions can opt into committed batches with an sqlplus directive. Sqlplus appends the configured LIMIT automatically, and rows inserted by one batch must no longer be selected by the next batch:

-- @loop batch-size=10000 max-batches=500 import edu.course_takers
insert into edu.course_takers(id, clazz_id)
select ct.id, ct.lesson_id
from jw.course_takers ct
where not exists (
  select 1 from edu.course_takers t where t.id = ct.id
);

Each batch is committed separately. Processing stops when a batch affects fewer than batch-size rows. batch-size defaults to 100000, while max-batches defaults to 50 and prevents a non-convergent statement from looping forever. Do not use this directive when the inserted rows remain eligible for the next execution. Loop statements must not contain LIMIT, ON CONFLICT, or RETURNING.

Run with:

./sqlplus.sh transport /path/to/your.xml

PostgreSQL unlogged target tables

For PostgreSQL targets, set unlogged="true" on a task's tables element to create the selected target tables as UNLOGGED:

<task from="USER" to="user">
  <tables unlogged="true" index="true" constraint="true">
    <includes>*</includes>
  </tables>
</task>

The option defaults to false. It is part of the desired target schema, not a temporary loading optimization:

  • A missing target table is created with CREATE UNLOGGED TABLE.
  • An existing logged table differs from the requested target structure and is dropped and recreated as unlogged.
  • The table remains unlogged after transport; sqlplus does not run ALTER TABLE ... SET LOGGED.
  • Indexes created on the table follow PostgreSQL's unlogged-table behavior.

Use this option only when the final table is intentionally unlogged. PostgreSQL does not write unlogged-table changes to WAL: the table is not crash-safe, may be truncated after an unclean shutdown, and is not replicated to standby servers.