Converting From mySQL to bSQL

Overview

This resource provides an explanation of how to migrate mySQL to bSQL. This tutorial includes basic differences between the two languages and their functionality. Here's an overview of the key differences between the systems:

	mySQL	bSQL
Immutable		✓
Snapshots	✓
Time Travel Queries™		✓
Integrity Proofs™		✓

Data Type Conversions

Numeric

The following table provides approximate numeric data type conversions. For a reference on the bSQL data types and their ranges, see the documentation.

Storage Size	mySQL	bSQL
n bits	bit(n)	no equivalent
1 bytes	tinyInt [unsigned]	INT8 or UINT8 if unsigned
1 byte	boolean	BOOL
2 bytes	smallint [unsigned]	INT16 or UINT16 if unsigned
3 bytes	mediumint [unsigned]	no equivalent
4 bytes	int [unsigned]	INT32 or UINT32 if unsigned
8 bytes	bigint [unsigned]	INT32 or UINT32 if unsigned
4 bytes	float	FLOAT32
8 bytes	double	FLOAT64
8 bytes	double precision	FLOAT64
n bytes	decimal	no equivalent

String

The following table provides bSQL equivalents to the character types in postgreSQL. The bSQL equivalents include additional column level arguments such as SIZE and PACKED.

Storage Size	postgreSQL	bSQL
n	char(n)	STRING SIZE = n
<=n+1	varchar(n)	STRING PACKED SIZE = n
n	binary(n)	BYTE ARRAY SIZE = n
<=n+1	varbinary(n)	BYTE ARRAY PACKED SIZE = n
variable	blob	no equivalent
variable	text	no equivalent
variable	enum	no equivalent
variable	set	no equivalent

Date and Time

bSQL's TIMESTAMP type and numeric types encompass the mySQL date and time types.

mySQL	bSQL
datetime	TIMESTAMP
timestamp	TIMESTAMP
time	use numeric types
date	use TIMESTAMP
year	use numeric types

Blockchain vs. Table

Creating a table in mySQL is equivalent to creating a blockchain in bSQL. For example creating the following table in postgreSQL:

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
       species VARCHAR(20), sex CHAR(1), weight TINYINT UNSIGNED, birth DATE, death DATE);

Would be equivalent to the following bSQL statement:

CREATE BLOCKCHAIN batters HISTORICAL (
        pet STRING SIZE = 20 PACKED NULLABLE, 
        owner STRING SIZE = 20 PACKED NULLABLE, 
        species STRING SIZE = 20 PACKED NULLABLE, 
        sex STRING SIZE = 1 NULLABLE, 
        wieght UINT8 NULLABLE,
        birth TIMESTAMP NULLABLE,
        death TIMESTAMP NULLABLE
);

A few things to note here:

• Unlike mySQL, bSQL columns don't allow null entries by default and NULLABLE must be specified.
• The HISTORICAL blockchain is an immutable log, doesn't have a primary key, and DOESN'T allow any mutations other than INSERT.
• Column and blockchain names in bSQL are interpreted as lower case and can't include spaces, use an underscore instead.

Tables with primary keys

Tables with primary keys must be converted to blockchains of type HISTORICAL PLUS, TRADITIONAL, and SPARSE. If unfamiliar with the blockchain types, reference the blockchain documentation.

CREATE TABLE users(
   user_id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(40),
   password VARCHAR(255),
   email VARCHAR(255)
);

Would be equivalent to the following bSQL statement:

CREATE BLOCKCHAIN users SPARSE (
        user_id UINT32 AUTO INCREMENT PRIMARY , 
        username STRING SIZE = 255 PACKED NULLABLE, 
        password STRING SIZE = 255 PACKED NULLABLE, 
        email STRING SIZE = 255 PACKED NULLABLE
);

Blockchain with primary keys only allow pseudo-mutations, there is no way to physically modify or erase existing data. Instead of traditional UPDATE and DELETE statements, they allow AMEND and DISCONTINUE.

Loading data

Loading data is key to migration. In mySQL data is loaded using the LOAD DATA statement. Loading data from a csv in mySQL takes the following form:

LOAD DATA LOCAL INFILE '/path/pet.csv' INTO TABLE pet
FIELDS TERMINTATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

In bSQL files can be read and incorporated into queries, this boils down to an INSERT INTO to be read from the filepath using the schema specified:

INSERT INTO pet (
   pet STRING SIZE = 20 PACKED NULLABLE,
   owner STRING SIZE = 20 PACKED NULLABLE,
   species STRING SIZE = 20 PACKED NULLABLE,
   sex STRING SIZE = 1 NULLABLE,
   wieght UINT8 NULLABLE,
   birth TIMESTAMP NULLABLE,
   death TIMESTAMP NULLABLE
  ) 
  SELECT * 
    FROM IMPORT = "path/pet.csv" ;

Mutations

Mutations in bSQL follow a different format and are considered primary-key dependent.

Insertions

The following insertion inserts a value into the pet mySQL table:

INSERT INTO pet
VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

This is equivalent to the following bSQL insertion:

INSERT pet
VALUES ("Puffball","Diane","hamster","f","1999-03-30 00:00:00.000000000",NULL);

Note the few differences:

• The INTO keyword is omitted in the bSQL statement.
• STRING values in bSQL require double quotes.
• By default, the birth and death columns of type TIMESTAMP have a default format of nanosecond specificity. Different formats can be specified using the FORMAT specification.

Update

The equivalent to an update statement in mySQL is an AMEND statement. The key difference is that the bSQL statement doesn't modify existing data, instead it only appends the new version.

There are two ways of updating data in bSQL:

1. Using an AMEND with static values and a know primary key.
2. Using AMEND INTO using a blockchains values that satisfy a search criteria.

The first case depends on the primary key and would look like this in mySQL:

UPDATE users SET email = 'mr.jones@hotmail.com' WHERE id = 10;

In bSQL it would look like the following:

AMEND users (id, email) VALUES (10, "mr.jones@hotmail.com");

Assuming that users is a SPARSE blockchain, the bSQL statement above inherits the previous values of the original record for all values not contained in the column list (in this case username and password). This mutation doesn't rely on scanning through values to arrive at a match, and uses multi-version index the index built on id.

DELETE

The equivalent to a DELETE statement in mySQL is a DISCONTINUE statement. The key difference is that the bSQL statement doesn't remove existing data, instead it only appends the new version.

DELETE users WHERE id = 10;

In bSQL it would look like the following:

DISCONTINUE users (id) VALUES (10);

• When discontinuing a value based off of a primary key it isn't necessary to provide all columns; the only column required is the primary key.
• When a values are discontinued, a tombstone record is added to the log to mark the primary key as no longer existing in the current system.

QUERIES

Queries in bSQL are very similar to queries in mySQL and follow a nearly identical format. Here are a few of the noticeable differences:

• LIMIT is replaced with FILTER
• Double quotes indicate string values, not column names

See Also

• Data Types
• overview
• postgreSQL to bSQL