Generating SQL to Execute the Installation of a Database schema from JSON definition - Scripd package blog

Contents

Understanding DDL, database objects and crud-actions

Creating and manipulating database objects

Database objects attributes.

The *-group database objects

Introduction

Understanding DDL, database objects and crud-actions

DDL stands for data definition language. In SQL, DDL statements are used to manipulate the structure of a database and any of its objects, unlike the regular DML statements which is used to manipulate the data in a database.

CREATE, ALTER and DROP are the most important DDL statements available in SQL which are supported by most database vendors.

The Scripd library purpose is to generate DDL statements. Those are referred to as crud-actions.

Database objects are databases, tables, views, stored procedures, triggers and so on. They are objects that can be created, modified and dropped by the DDL statement syntax. Table columns, table constraints such as foreign keys and so on are also database objects since they can be manipulated by these keywords.

Scripd understands database objects in two forms:

• The top level objects
• Object definers

Top level objects are databases, tables, views and so on, while object definers are database objects which can't be created independently of the top level objects. These are the columns and constraints and every other objects that depends on a table, view, trigger or stored procedure for their existence.

crud-actions are all of the major DDL statements for manipulating database objects. These include Create, Drop and Alter.

Creating Database Objects

By bringing together the concepts of crud-actions, top level objects and object definers, it is fairly easy to specify the definition of a database object structure in JSON.

To create a top level object like a database, for instance, you write the name of the top level object with a full colon (:), prepended to it as the key of an object in json. In this case, we have:

{
	":database":{

	}
}

The next thing is to specify the crud-action that specifies what DDL statement is going to be generated. For instance if we want to create a database, we'd write:

{
	":database":{
		":crud-action":"create"
	}
}

Similarly, if we want to alter or drop a database we would replace "create" with "alter" or "drop" as the value of the ":crud-action" key.

Remember, a database is just one database object. You can do the same thing for a table by replacing ":database" with ":table".

While creating object definers, you do not need to prepend the keyword with a colon. Just write it as is.

Currently, columns, add-column and foreign-key are the only supported object definers. You can add more by extending the Scripd library.

Database objects attributes

To create a database we need to specify the name of the database and other information depending on the vendor we are creating the database for. For instance in MySQL you can specify the character set and collation options for the database.

Name, Character Set, Collation options are all referred to as attributes and creating them for database objects is as easy as adding a key-value pair to a JSON object. To continue with our example of creating a database, we wouldd add the name and default-collate option for the database in the JSON structure.

{
	":database":{
		":crud-action":"create",
		"if-not-exists":true,
		"name":"my-database-name",
		"collate":"utf8"
	}	
}

If we were creating for SQL Server, there's support for the FILE GROUP and CONTAINMENT keywords, adding this to the structure is a matter of adding an extra key-value pair to the JSON string.

{
	":database":{
		":crud-action":"create",
		"name":"my-database-name",
		"collate":"utf8",
		"file-group":"my-filegroup-name",
		"containment":"none"
	}	
}

The *-group database objects

Due to the nature of the indexing used in JSON, it is practically impossible to create more than one top level objects of the same type under one object. For instance, to create two tables under a database, we'd do this:

{
	":database":{
		":crud-action":"create",
		":table":{
			":crud-action":"create"
		},
		":table":{
			":crud-action":"create"
		}
	}
}

But doing that will return only one table since the second table will override the first one during parsing as they have the same key. This lead to the use of *-group database objects. They are used to group more than one top level objects the same type using an array.

Using the *-group format, to create a table group, that is, more than one table, replace the asterisk with the object name (table in this case).

For example, to create two tables just like we have in the example above, we'd write:

{
	":database":{
		":crud-action":"create",
		":table-group":[
			{
				":crud-action":"create"
			},
			{
				":crud-action":"create"
			}
		]
	}
}

Conclusion

The Scripd library allows you to install database schemata in a database independent manner using a JSON format.

This way he installation of a database schema based on the DDL SQL that this package generates became easier and more error prone.

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