Converting From postgreSQL to bSQL
Overview​
An explanation of how to migrate postgreSQL to bSQL. This tutorial includes basic differences between the two languages and their functionality. Here's an overview of the key differences:
| postgreSQL | bSQL | |
|---|---|---|
| Immutable | ✓ | |
| Snapshots | ✓ | |
| Time Travel Queries™ | ✓ | |
| Integrity Proofs™ | ✓ |
Data Type Conversions​
Numeric​
The following table provides approximate numeric data type conversions. PostgreSQL doesn't support unsigned numeric types, so it is important to use this table only as general guidelines. For a reference look at the bSQL data types.
| Storage Size | postgreSQL | bSQL |
|---|---|---|
| 2 bytes | smallint | INT16 or UINT16 |
| 4 bytes | integer | INT32 or UINT32 |
| 8 bytes | bigint | INT64 or UINT64 |
| 4 bytes | real | FLOAT32 |
| 8 bytes | double precision | FLOAT64 |
| 2 bytes | smallserial | UINT16 column with AUTO INCREMENT |
| 4 bytes | serial | UINT32 column with AUTO INCREMENT |
| 8 bytes | bigserial | UINT64 column with AUTO INCREMENT |
| variable | numeric | 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 | varchar(n) | STRING PACKED SIZE = n |
| n | character(n) | STRING SIZE = n |
| variable | text | no equivalent |
Other​
The following table provides bSQL equivalents to the character types in postgreSQL. The bSQL equivalents include column additional column level arguments.
| postgreSQL | bSQL |
|---|---|
| bytea | BYTEARRAY PACKED |
| timestamp | TIMESTAMP |
| time | use numeric types |
| date | use TIMESTAMP |
| boolean | BOOL |
Blockchain vs. Table​
Creating a table in postgreSQL is equivalent to creating a blockchain in bSQL. For example creating the following table in postgreSQL:
CREATE TABLE "Batters"
(
Player VARCHAR(101),
Team VARCHAR(50),
League VARCHAR(2),
Yr SMALLINT,
Games DOUBLE PRECISION,
AB DOUBLE PRECISION,
R DOUBLE PRECISION,
H DOUBLE PRECISION,
Doubles DOUBLE PRECISION,
Triples DOUBLE PRECISION,
HR DOUBLE PRECISION,
RBI DOUBLE PRECISION,
SB DOUBLE PRECISION,
CS DOUBLE PRECISION,
BB DOUBLE PRECISION,
SO DOUBLE PRECISION,
IBB DOUBLE PRECISION,
HBP DOUBLE PRECISION,
SH DOUBLE PRECISION,
SF DOUBLE PRECISION,
GIDP DOUBLE PRECISION
);
Would be equivalent to the following bSQL statement:
CREATE BLOCKCHAIN batters HISTORICAL (
player STRING SIZE = 101 PACKED NULLABLE,
team STRING SIZE = 50 PACKED NULLABLE,
league STRING SIZE = 2 PACKED NULLABLE,
yr UINT16 NULLABLE,
games FLOAT64 NULLABLE,
ab FLOAT64 NULLABLE,
r FLOAT64 NULLABLE,
h FLOAT64 NULLABLE,
doubles FLOAT64 NULLABLE,
triples FLOAT64 NULLABLE,
hr FLOAT64 NULLABLE,
rbi FLOAT64 NULLABLE,
sb FLOAT64 NULLABLE,
cs FLOAT64 NULLABLE,
bb FLOAT64 NULLABLE,
so FLOAT64 NULLABLE,
ibb FLOAT64 NULLABLE,
hbp FLOAT64 NULLABLE,
sh FLOAT64 NULLABLE,
sf FLOAT64 NULLABLE,
gidp FLOAT64 NULLABLE
);
A few things to note here:
- Unlike postgreSQL, bSQL columns don't allow null entries by default and NULLABLE must be specified.
- The HISTORICAL blockchain is an immutable log 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.
- The "y" column can be made a UINT16 in bSQL because years are positive.
Mutations​
Insertions​
The following insertion inserts a value into the films postgreSQL table:
INSERT INTO films VALUES
('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
This is equivalent to the following bSQL insertion:
INSERT films VALUES
("UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes");
Note the few differences:
- The INTO keyword is omitted in the bSQL statement.
- STRING values in bSQL require double quotes.
Update​
The equivalent to an update statement in postgreSQL 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:
- Using an AMEND with static values dependent on primary key.
- Using AMEND INTO using a blockchains values and a search constraint.
The first case depends on the primary key and would look like this in postgreSQL:
UPDATE films SET kind = 'Dramatic' WHERE film_id = 101;
In bSQL it would look like the following:
AMEND films (film_id, kind) VALUES (101, "Dramatic");
Assuming that films 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 film_id and kind). This mutation doesn't rely on scanning through values to arrive at a match, and uses the index built on film_id.
Updating a values whose primary key is unknown must be resolved from the existing data. In postgreSQL this statement doesn't change and resembles the following:
UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';
In bSQL we use the AMEND INTO statement to pull data:
AMEND INTO films
SELECT film_id, name, release_date, "Dramatic"
FROM films
WHERE kind = "Dramatic";
Assuming that the column list in the select statement encompasses all columns, this statement can be used for all blockchains that allow amends.
DELETE​
The equivalent to a DELETE statement in postgreSQL 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 FROM films WHERE film_id = 101;
In bSQL this would translate to:
DISCONTINUE films (film_id) VALUES (101);
- 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 postgreSQL 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