Installing a Database Schema from a JSON format - Scripd package blog

By Samuel Adeshina

samshal.github.io

<email contact>

Contents

Introduction

How Does it Work?

Installing

A quick example: Generating your First SQL

How to contribute

Conclusion

Introduction

Scripd is a PHP package that uses files in JSON format to define SQL queries that can run in databases from different vendors.

It makes things like writing your database structure specification in one file and generating valid SQL scripts supported by different vendors such as MySQL, Oracle and SQL server much simpler.

It also makes it easy to transfer the structure of a database or objects within a database such as a table or view from one environment to another or from one database vendor to another.

This package uses the JSON format to describe database structures, database modifications and so on, thereby making it easy to manipulate a complete database with a JavaScript syntax or transfer database object specifications via API as JSON objects.

How Does it Work?

From a much more technical point of view, Scripd comprises of a class that parses JSON while comparing it to the definition of a database structure supplied in a jsyn file in order to generate SQL.

Now we have three things going on here:

1. Specifying the structure of a database or a database object in JSON format
2. Parsing the JSON file while comparing it to an already defined jsyn file
3. Generating valid SQL based on the vendor specified during parsing

JSYN is a custom file format for defining a database and all of its objects in a JSON compatible form. It is the main layer in the Scripd library that specifies how a supplied structure should be parsed.

While making sure the JSON format is completely supported by jsyn files, it uses strings with special characters such as braces, brackets and square brackets to determine if an SQL keyword should be omitted, compulsory or optional in various scenarios.

A special article will be dedicated to better understanding the jsyn specification and even writing database definitions in jsyn format but the example below gives a brief illustration of what jsyn is all about.

{
 "mysql":[
  "CREATE DATABASE",
  "[{{IF NOT EXISTS}}]",
  "{{NAME}}",
  "[DEFAULT CHARACTER SET]",
  "[{{DEFAULT CHARACTER SET}}]",
  "[DEFAULT COLLATE]",
  "[{{COLLATE}}]",
  "[CHARACTER SET]",
  "[{{CHARACTER SET}}]",
  "[COLLATE]",
  "[{{COLLATE}}]"
 ]
}

The snippet above represents a valid jsyn definition of the CREATE DATABASE keyword in MySQL. From the official MySQL reference manual, the SQL statement syntax for creating a database is:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

The jsyn snippet above is an adaptation of this syntax. It uses the same keywords and the same optional symbols. The only difference is, it is in JSON fomat. This helps to improve the end goal of the whole scripd library which is to provide a language agnostic format for database independent structures. JSON is agnostic because it is supported by many languages and is therefore the most suitable candidate for defining these structures.

So while jsyn is mainly the representation of the keywords of a DDL syntax, the main structure to be converted is specified in completely valid JSON with no special symbols.

For instance, to generate SQL for the creation of a database called "repositories" and at the same time other existing databases of the same name if they existed, the following JSON should be written:

{
 ":create-database":{
  ":crud-action":"create",
  "if-not-exists":true,
  "name":"respositories"
 }
}

Very simple right? That's not all. With the parser from the Scripd library, this JSON can be converted to valid SQL compatible with MySQL, SQL Server, SQLite, PostgreSQL and so on, all at once.

At this point, it is worth knowing that Scripd is meant for "defining" database structures, not for specifying or "generating" other SQL that does not affect the structure of a database or any of its objects.

In other words, Scripd only generates DDL queries. It is not meant for manipulating the data in a database, you should use a query builder for that, Doctrine DBAL is a very good candidate.

Installing Scripd

The Scripd library is available on packagist, therefore it can be installed via composer. Running the command below on a terminal downloads it into your project and as long as you have an autoloader, you're good to go

composer require samshal/scripd ^1.0

or add the following in composer.json

"samshal/scripd":"^1.0"

This library is also hosted on the PHPClasses site. You can install it from there by adding the following to your composer.json file:

{
  "require":
  {
    "phpclasses/scripd": ">=1.0.3"
  },
  "repositories":
  [
    {
      "type": "composer",
      "url": "https:\/\/www.phpclasses.org\/"
    },
    {
      "packagist": false
    }
  ]
}

Read  this article to learn more about installing packages from the PHPClasses site with composer.

If you don't use composer, you can download a zipped file of the most recent release from the PHPClasses or from GitHub.

For a basic setup, no configuration is needed. It works out of the box. An upcoming article will explain how to extend this library and perform some configuration tasks.

Generating your first SQL

It's time to put the library to a good use. Let's continue with our previous example of creating a "repositories" database, and actually generate real SQL that defines the structure of a database.

In this example, we would create a database called "repositories" with two tables "contributors" and "code". The contributors table has two columns "contrib_id" and "contrib_name" while the code table has three, "code_id", "code_url", "code_contributor".

To begin, we need to create a JSON file that looks like this:

{
 ":create-database":{
  ":crud-action":"create",
  "name":"repositories",
  ":table-group":[
   {
    ":crud-action":"create",
    "name":"contributors",
    "columns":[
     {
      "name":"contrib_id",
      "data-type":"int",
      "auto_increment":true,
      "primary-key":true
     },
     {
      "name":"contrib_name",
      "data-type":"varchar(20)",
      "unique":true,
      "default":"'John Doe'",
      "not-null":true
     }
    ]
   },
   {
    ":crud-action":"create",
    "name":"code",
    "columns":[
     {
      "name":"code_id",
      "data-type":"int",
      "auto_increment":true,
      "primary-key":true
     },
     {
      "name":"code_url",
      "data-type":"varchar(255)",
      "not-null":true
     },
     {
      "name":"code_contributor",
      "data-type":"int",
      "not-null":true,
      "foreign-key":"(code_contributor)",
      "references":"contributors(id)",
      "on-update":"cascade",
      "on-delete":"set null"
     }
    ]
   }
  ]
 }
}

Now, we have our database structure defined in JSON. It's time to generate SQL.

<?php
 require 'vendor/autoload.php';

 $jsonDBStructure = new Samshal\Scripd\JsonDbStructure( './structure.json', 'mysql'); //structure.json is name of json file

 $jsonDBStructure->parseStructure();

 $sql = $jsonDBStructure->getGeneratedSql();

 echo $sql;

Executing the code above generates the following sql

 CREATE DATABASE repositories;
 CREATE TABLE contributors( contrib_id int auto_increment primary key, contrib_name varchar(20) unique not null default 'John Doe');
 CREATE TABLE code( code_id int auto_increment primary key, code_url varchar(255) not null, code_contributor int not null foreign key (code_contributor) references contributors(id) on update cascade on delete set null);

That's it. We've generated the SQL query needed to solve our problem in MySQL, passing SQLlite as second parameter to the JsonDBStructure constructor method generates SQL compatible with SQLite. The same thing goes for every other supported vendors.

How to contribute to the development of Scripd

The Scripd library is in active development on GitHub. If you find a bug in the source code and want to report it, you can submit an issue to its GitHub repository. Even better, you can submit a pull request with a fix. If you want to request a new feature, you can also open a new issue in the repository and mark it with the appropriate tags.

Conclusion

We've discussed how Scripd works, how to install it and an example of how to use it.

The next article will expand more on the usage. We would discuss the special keywords used in the JSON files, how to solve common errors and exceptions that occur during the usage and other complex ways to utilize this library to generate SQL for manipulating database structures.

If you liked this article please share it with other developers. If you have a question about Scripd or the jsyn format, post a comment here, so I will reply to you.