PHP Classes
elePHPant
Icontem

Scripd: Generate database creation SQL queries from JSON

Recommend this page to a friend!
  Info   View files Documentation   View files View files (23)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog (2)    
Last Updated Ratings Unique User Downloads Download Rankings
2016-05-31 (4 months ago) RSS 2.0 feedStarStarStarStar 70%Total: 309 All time: 6,907 This week: 1,113Up
Version License PHP version Categories
scripd 1.0.7MIT/X Consortium ...5PHP 5, Databases, Parsers
Description Author

This package can generate database creation SQL queries from JSON.

It takes a file in JSYN format (JSON) and parses it to extract a list of commands for database creation.

The main class can generate SQL queries for creating or modifying database structures like databases, tables, views, indexes, trigger, functions, stored procedures, etc..

The generated SQL is compatible with several databases like MySQL, SQLite, Microsoft SQL server, etc..

Innovation Award
PHP Programming Innovation award nominee
May 2016
Number 7


Prize: SourceGuarding PHP encoder tool
Every SQL database driven application needs to update the database schema once in a while to support new features.

This class can perform that process of generating SQL for database updates with elegance, as it allows developers to define database schema additions in a JSON based file format, so it can work with different types of databases.

Since the format is based in JSON it can be used eventually by similar packages written in other languages.

Not only it supports the creation and modification of databases, tables and indexes, but it also supports triggers, views, stored procedures and functions.

Manuel Lemos
Picture of Samuel Adeshina
  Performance   Level  
Name: Samuel Adeshina <contact>
Classes: 19 packages by
Country: Nigeria Nigeria
Innovation award
Innovation award
Nominee: 7x

Winner: 1x

Details

Scripd Build Status Latest Stable Version Total Downloads Latest Unstable Version License Scrutinizer Code Quality StyleCI

A robust SQL Generator. Parses database structures defined in json based on a custom jsyn file format and generates corresponding sql queries.

Class Features

  • A json like file format to define database structure
  • Support for multiple sql vendors / dialects
  • Compatible with PHP 5.0+
  • Support for UTF-8 content and 8bit, base64, binary, and quoted-printable encodings
  • Much More!

Why you might need it

This project was birthed as a result of the need to give users an opportunity to create their own custom database structure in a PHP Application. I wanted users to be able to modify database structures while there is support for multiple db vendors such as mysql, sqlite and sql server.

This library offers the ability to create database structures in a json-like format and generate sql compatible with several database vendors.

It is also very easy to use and integrate into your php based projects

License

This software is distributed under the MIT license. Please read LICENSE for information on the software availability and distribution.

Installation & loading

Scripd is available via Composer/Packagist, so just add this line to your composer.json file:

"samshal/scripd": "~1.0"

or

composer require samshal/scripd

A Simple Example

JSON DB Structure (structure.json)

{
	":database":{
		":crud-action":"create",
		"name":"dbname",

		":table":[
			{
				":crud-action":"create",
				"name":"students",
				"columns":[
					{
						"name":"id",
						"data-type":"int",
						"primary-key":true
					},
					{
						"name":"first_name",
						"data-type":"varchar(20)",
						"default":"'samuel'"
					},
					{
						"name":"last_name",
						"data-type":"varchar(20)"
					},
					{
						"name":"class",
						"data-type":"varchar(10)"
					}
				]
			}
		]
	}
}

PHP (index.php)

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

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

    $jsonDBStructure->parseStructure();

    $sql = $jsonDBStructure->getGeneratedSql();
    
    echo $sql;
  Files folder image Files  
File Role Description
Files folder imagesrc (2 files, 1 directory)
Files folder imagetests (1 file, 1 directory)
Accessible without login Plain text file .gitignore Data Lists development-only files
Accessible without login Plain text file .travis.yml Data Travis configuration fil
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  src  
File Role Description
Files folder imagebin (10 files)
  Plain text file JsonDbStructure.php Class Class source
  Plain text file JsynExtractor.php Class Class source

  Files folder image Files  /  src  /  bin  
File Role Description
  Accessible without login Plain text file add-column.jsyn Data Auxiliary data
  Accessible without login Plain text file alter-table.jsyn Data Auxiliary data
  Accessible without login Plain text file columns.jsyn Data Auxiliary data
  Accessible without login Plain text file create-database.jsyn Data Auxiliary data
  Accessible without login Plain text file create-table.jsyn Data Auxiliary data
  Accessible without login Plain text file create-view.jsyn Data Auxiliary data
  Accessible without login Plain text file drop-database.jsyn Data Auxiliary data
  Accessible without login Plain text file drop-table.jsyn Data Auxiliary data
  Accessible without login Plain text file drop-view.jsyn Data Auxiliary data
  Accessible without login Plain text file foreign-key.jsyn Data Foreign Key jsyn Definition

  Files folder image Files  /  tests  
File Role Description
Files folder imagejson (5 files)
  Plain text file JsonDbStructureTest.php Class Class source

  Files folder image Files  /  tests  /  json  
File Role Description
  Accessible without login Plain text file 1.json Data Auxiliary data
  Accessible without login Plain text file 2.json Data Auxiliary data
  Accessible without login Plain text file 3.json Data Auxiliary data
  Accessible without login Plain text file 4.json Data Auxiliary data
  Accessible without login Plain text file 5.json Data Auxiliary data

 Version Control Unique User Downloads Download Rankings  
 100%
Total:309
This week:0
All time:6,907
This week:1,113Up
 User Ratings  
 
 All time
Utility:100%StarStarStarStarStarStar
Consistency:100%StarStarStarStarStarStar
Documentation:100%StarStarStarStarStarStar
Examples:-
Tests:-
Videos:-
Overall:70%StarStarStarStar
Rank:336