In VoltDB you define your database schema using SQL data definition language (DDL) statements just like other SQL databases. So, if we want to create a database table for the places where we live, the DDL schema might look like the following:
CREATE TABLE towns ( town VARCHAR(64), county VARCHAR(64), state VARCHAR(2) );
The preceding schema defines a single table with three columns: town, county, and state. We could also set options, such as default values and primary keys. But for now we will keep it as simple as possible.
To create a database using this schema, we take three steps:
Save the schema as a text file.
Compile the schema into an application catalog.
Start the database using the catalog created in step #2.
For now, let's save the preceding schema into a file named
towns.sql. The following sections
explain how to create the application catalog and start the database.
Once you have the schema defined, you can create an application catalog. The application catalog is one way VoltDB achieves the performance it does, by pre-compiling important aspects of the database — such as stored procedures and partitioning information — into a single file.
Although we are not interested in performance right now, we still need to create the application catalog before we can start the database. You create the application catalog by compiling the schema:
$ voltdb compile towns.sql
In this simplest case, the voltdb compile command requires only one argument: the name of the
schema DDL file. By default, it creates the catalog as
catalog.jar. If you want to name your catalog
file, you can use the
--output flag, like so:
$ voltdb compile -o towns.jar towns.sql
Assuming no typos or other errors in the DDL, VoltDB compiles the schema and creates the application catalog
Once you create the application catalog, you are ready to start the database. Again, there are several options available when starting a VoltDB database, which we will discuss later. But for now, we can use the simplest command to start the database using the default options on the current machine with the following command:
$ voltdb create catalog towns.jar
The create keyword tells VoltDB to create a new, empty database. The catalog keyword tells VoltDB which application catalog to use when creating the database.
Congratulations! You have created your first VoltDB database. Of course, an empty database is not terribly useful. So the first thing you will want to do is create and retrieve a few records to prove to yourself that the database is running as you expect.
VoltDB supports all of the standard SQL query statements, such as INSERT, UPDATE, DELETE, and SELECT. You can invoke queries programmatically, through standard interfaces such as JDBC and JSON, or you can include them in stored procedures that are compiled and included in the application catalog.
But for now, we'll just try some ad hoc queries using the sqlcmd command line interface that VoltDB provides. Create a new terminal window and issue the sqlcmd command from the shell prompt:
$ sqlcmd SQL Command :: localhost:21212 1>
The VoltDB interactive SQL command line first reports what database it has connected to and then puts up a numbered prompt. At the prompt, you can enter ad hoc SQL queries, execute stored procedures, or type "exit" to end the program and return to the shell prompt.
Let's start by creating some records using the INSERT statement. The following example creates three records, for the towns of Billerica, Buffalo, and Bay View. Be sure to include the semi-colon after each statement.
1> insert into towns values ('Billerica','Middlesex','MA'); 2> insert into towns values ('Buffalo','Erie','NY'); 3> insert into towns values ('Bay View','Erie','OH');
We can also use ad hoc queries to verify that our inserts worked as expected. The following queries use the SELECT statement to retrieve information about the database records.
4> select count(*) as total from towns; TOTAL ------ 3 (1 row(s) affected) 5> select town, state from towns ORDER BY town; TOWN STATE ------------ ------ Bay Village OH Billerica MA Buffalo NY (3 row(s) affected)
When you are done working with the database, you can type "exit" to end the sqlcmd session and return to the shell command prompt. Then switch back to the terminal session where you started the database and press CTRL-C to end the database process.
This ends Part One of the tutorial.