SQL

SQL

Scripts

-- Create table
CREATE TABLE BOOK (
  ID INT AUTO_INCREMENT,
  TITLE VARCHAR(200) NOT NULL,
  CONSTRAINT BOOK_PK PRIMARY KEY (ID)
);

-- Insert data
INSERT INTO BOOK(TITLE) VALUES ('MySQL in action');

The trailing semicolon is the default marker for statement end. It can be changed with endOfLineMarker or endOfLineRegex settings.

Command line

$ bin/beepbeep.sh  -t sql -d jdbc:mysql://localhost:3306/beepbeep_db -u beep_user -p beep_pswd -s beepbeep_table -f 'script/*.sql'

• jdbc:mysql://localhost:3306/beepbeep_db: JDBC URL where is located the database server
• beep_user/beep_pswd: user/password used to connect to database
• beepbeep_table: Table used to store ran script and not execute them twice
• script/*.sql: File glob to select which scripts should be ran. File system is scanned. Scripts are ran in lexical order.

Java API

This API can be used on Java application startup or unit test startup

SqlPipelineBuilder pipelineBuilder = new SqlPipelineBuilder()
  .withUrl("jdbc:mysql://localhost:3306/beepbeep_db")
  .withUsername("beep_user")
  .withPassword("beep_pswd")
  .withScriptStore("beepbeep_table")
  .withManualCommit()
  .withVariable("replicas", 1)
  .withResourcesScriptScanner(Thread.currentThread().getContextClassLoader(), "script/*.sql");
pipelineBuilder.scan();

• Url: JDBC URL where is located the database server
• Username/Password: user/password used to connect to database
• ScriptStore: Table used to store ran script and not execute them twice
• ResourcesScriptScanner: File glob to select which scripts should be ran. Classpath is scanned. Scripts are ran in lexical order.
• AutoCommit/ManualCommit: auto commit after each statement or do manual commit/rollback after each script
• Variable: will replace the string ${replicas} by 1 before executing the query.
• SqlConnectionProvider: implement your own strategy to get/release database connection: authentication, pooling...

Configuration file

The optional configuration file

---
type: sql
charset: UTF-8
url: jdbc:h2:mem:test
username: sa
password:
autoCommit: false
scriptStore: beepbeep
scripts:
  - sql/**/*.sql
variables:
  tablespace.main: "MAIN_TBS"