SmallSQL

From Bauman National Library
This page was last modified on 25 May 2016, at 16:21.
SmallSQL
fraimed
Initial release 28 March 2005
Repository {{#property:P1324}}
Written in Java
Operating system Any Java supported
Platform x32, x64
Available in English, German, Italian
Type DBMS
License GNU LGPL
Website http://www.smallsql.de

SmallSQL is a 100% pure Java DBMS, a relational database for Java desktop applications. It has a JDBC 3.0 interface and offering many ANSI SQL 92 and ANSI SQL 99 features. It is very small and fast Java library. It does not have a network interface.


Getting Started [1]

There are 2 solutions to start with SmallSQL DBMS:

From an existing Database

You can convert an existing Database with JDbConverter. This is an open source tool to convert a database from one DBMS to another DBMS via JDBC. You can download it and convert your existing JDBC database.

Create a database with a GUI tool

We does not have such tool but we recommended one of the follow:

  • JDBC Navigator. This is a free graphical Java tool. After you have download it
    • Select the menu "Open JDBC Data Source"
      Name: Small SQL Database
      Driver Class: smallsql.database.SSDriver
      URL: jdbc:smallsql
      Save and Connect
    • Open the menu "Connection | SQL Window"
    • Enter "CREATE DATABASE db1" and execute it with ENTER
    • Close the Connection
    • Select the menu "Open JDBC Data Source" and change the URL to jdbc:smallsql:db1
      Save and Connect
  • SQuirreL SQL Client. This is an open source graphical Java tool. After you have download and install it
    • Create a new JDBC Driver
      Name: Small SQL Database
      Example URL: jdbc:smallsql:<db name>
    • Create a new Alias
      Name: Small SQL db1
      JDBC Driver: Small SQL
      URL: jdbc:smallsql
      Auto Logon: Yes
    • Connect to the alias "Small SQL db1"
    • Change to the tab "SQL"
    • Enter "CREATE DATABASE db1" and execute it
    • Close the window
    • Change the alias
      JDBC URL: jdbc:smallsql:db1
    • Connect to the alias "Small SQL db1"

Configuration of the JDBC Driver

JDBC Driver class name: smallsql.database.SSDriver
JDBC URL: jdbc:smallsql:<database>, where <database> is a absolute or relative directory name

In the Java code for a directory "db1" this look like:

Class.forName( "smallsql.database.SSDriver" );
java.sql.Connection con = java.sql.DriverManager.getConnection( "jdbc:smallsql:db1" );

JDBC URL properties

A property can be set with a Property Object on the method call DriverManager.getConnection. Or it can add after the base JDBC with a question mark. Multiple properties are delimited with a semicolon.

Property Default value Description

create


dbpath

false


If the flag has the value "true" then the database in the JDBC URL will create if it does not exist.


This property override the database in the JDBC URL

JDBC URL Samples

jdbc:smallsql:MyDb?create=true


Create an empty Database

At beginning of the work with the SmallSQL database you have the problem to create the first database. If you want create database there are 3 solutions:

  1. You can convert an existing database. See the Getting Started.
  2. Use the flag JDBC URL create=true. Then the database is creating if not exist. This look like: jdbc:smallsql:NewDatabase?create=true
  3. Create a connection to a nonexistent database. Then execute a CREATE DATABASE command. With the follow JDBC URL you can create a connection to no database: jdbc:smallsql


SQL Syntax Reference

Constant Expressions

Constant expressions can be used on any place where values allowed. You can use following syntax:

data type samples description
String 'any string value'
'O Brien'
Strings a quoted with a single quote. If string include a quote then you need to duplicate the quote
Binary 0xAB
0x12ad
0x (empty binary)
Binaries have the prefix 0x. Every byte is displayed with 2 hexadecimal digit. You can use lower and upper letter.
Bit true
false
Timestamp {ts '2003-07-16 12:30:15.000'} The format is yyyy-mm-dd hh:mm:ss.nnn.
Time {t '12:30:15'} The format is hh:mm:ss.
Date {d '2003-07-16'} The format is yyyy-mm-dd.
Integer 1234
67
Integer are numbers with digits only without decimal point.
Decimal 12.34 Decimal are numbers with a decimal point.
Double 1.234E1 Double are numbers with a exponent.
Money $12
$12.34
Money have the currency symbol as the prefix.
uniqueidentifier '618859EE-7B89-C122-2ED3-12AA54B6FF22'
0xee598861897b22c12ed312aa54b6ff22
GUID can be written as quoted String or as 16-byte binary code.
any NULL A NULL value.


CREATE DATABASE

Create a new database. A database in SmallSQL is saving as a directory with a master control file. If you want create the first database then you can connect to no database to execute this command.

Syntax

CREATE DATABASE database_name

Parameters

  • database_name: The name of the database. The name is identical to the name of the directory. The directory is created as sub directory to the current working directory. You can also specify a absolute or relative path. If the directory name include spaces then you need to quote the identifier.

Samples

CREATE DATABASE c:\MyDatabase
CREATE DATABASE ../MyDatabase
CREATE DATABASE '../My Database'


DROP DATABASE

Delete a database.

Syntax

DELETE DATABASE database_name

Parameters

  • database_name: The name of the database. SmallSQL is verify if the directory and the master file exists. Then it delete the directory with all files in it. Directory search works relatively to the current directory. You can also specify an absolute path.


USE

Change the current database context. This is equals to the JDBC API method connection.setCatalog(x).

Syntax

USE database_name

Parameters

  • database_name: The name of the new database. The name is identical to the name of CREATE DATABASE.


CREATE TABLE

Create a new table.

Syntax

CREATE TABLE table_name (
  <column_def> [,...n]
)
<column_def> ::= { column_name data type } [ DEFAULT constant_expression ] [ IDENTITY ] [ NULL | NOT NULL ]

Parameters

  • table_name: The name of the new table.


DROP TABLE

Delete a table definition and all data.

Syntax

DROP TABLE table_name

Parameters

  • table_name: The name of the table.


SELECT

This command requests data from the database. The selection can include one or more columns or rows.

Syntax

SELECT <column_def> [,...n]
FROM <from list> 
[WHERE <where expression>] 
[[GROUP BY <group list> 
[HAVING <having expression>]] 
[ORDER BY <order list>]


INSERT

Add a row to a table or view.

Syntax

INSERT INTO <tablename> [(col1,coll2[,...n])] VALUES(val1,val2[,...n])


DELETE

Remove rows from a table.

Syntax

DELETE <tablename> WHERE <where expression>


References

  1. http://www.smallsql.de/doc/index.html