Tutorial 2 - Create A Standalone Database And Tables

Tutorial 2 - Create A Standalone Database And Tables#

Let’s use SQL Current to create a new, standalone database and add some tables to it. A standalone database is a database that does not belong to a branch. Before proceeding, ensure you’ve followed the steps in the Setup / Install section.

Get a database server up and running#

This is probably the most difficult step if it’s not already done. Make sure you have a database server, such as PostgreSQL or SQL Server, up and running. In this tutorial, we are going to use SQL Server, but the process is essentially the same.

Write the SQL files#

Write a SQL script that creates the physical database my_db. Save the following script to create-database.sql.

create database my_db on
(
        name = my_db_dat,
        filename = 'C:\\Data\\my_db.mdf',
        size = 64MB,
        maxsize = 512MB,
        filegrowth = 100%
)
log on
(
        name = my_db_log,
        filename = 'C:\\Data\\my_db.ldf',
        size = 8MB,
        filegrowth = 0%
);

alter database heavywork_athena set recovery simple;

Write a SQL script that creates tables table1 and table2. Save the following script to create-tables.sql.

create table table1
(
        table1_id int primary key
);

create table table2
(
        table2_id int primary key
);

Note that you could run the above script directly in SSMS (SQL Server Management Studio) without any changes.

Write the SCS script.txt file#

SQL Current’s built-in scripting language is called Current Script. Create the following SCS file script.txt and save it in the same directory as create-database.sql and create-tables.sql.

Let’s incrementally define our standalone database, starting with the following database definition statement.

database my_db
{
        driver: 'sqlserver';
        connString: 'server=myserver.sqlcurrent.com;user=sa;password=sandy;database=my_db;autocommit=1';
        create: './create-database.sql';
        create: './create-tables.sql';
}

The current script above defines a database without a branch property, making it a standalone database.

  • driver: SQL Current will use the best SQL Server Python driver (pymssql) to connect to the database.

  • connString: The connection string for the driver. Note the database=my_db component. This is the name of the database we are creating.

  • create: There are two (2) create scripts defined. They will be executed in the order they are specified.

Let’s add a create database statement to script.txt.

database my_db
{
        driver: 'sqlserver';
        connString: 'server=myserver.sqlcurrent.com;user=sa;password=sandy;database=my_db;autocommit=1';
        create: './create-database.sql';
        create: './create-tables.sql';
}

create database mydb;

Execute script.txt.

% sqlcurrent script.txt

The current script should fail with an error similar to this:

my_db: Creating database.
my_db: Running '/Projects/Database_Migrations/create-database.sql'.
my_db: Error. (15007, b"'sa' is not a valid login or you do not have permission.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 11:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 11:\nGeneral SQL Server error: Check messages from the SQL Server\n")

There can be several reasons why the login failed. One of the reasons is we are trying to connect to a database that does not exist. If you connect to SQL Server, and you specify a database that does not exist, then you can NEVER login, even as an administrator.

The solution to this problem is to use a server connection string (serverConnString) with a script hint as follows:

Note how serverConnString has no database. The first create script will use the connection string in serverConnString instead of the default connString.

Execute script.txt again.

% sqlcurrent script.txt

The current script should run successfully with output similar to this:

my_db: Creating database.
my_db: Running '/Projects/Database_Migrations/create-database.sql'.
my_db: Success.
my_db: Running '/Projects/Database_Migrations/create-tables.sql'.
my_db: Success.
my_db: Create database complete.

For more information, see Creating Databases and Script Hints.

Verify the tables were created#

Use psql or pgadmin to verify the table was created in your database.

Verify the database version#

select databases;

Verify the update tracking file was created#

SQL Current keeps track of each data in an update tracking file. There is one (1) update tracking file per database definition.

Look for directory sqlcurrent_updatingtracking and find the update tracking file for this database.

Contents