SmallSQL

SmallSQL

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

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>