Tutorial

1. Introduction
2. Syntax
3. Connecting to a Database with code generator
4. Templates
   1. Creating a Basic SQL Template
   2. Creating a Basic PHP SELECT
   3. Creating a Basic PHP INSERT

Introduction

If you are like me and many others that don't enjoy doing the same thing over and over again, then this program is for you. I use to spend like 20-30 minutes creating stored procedures, insert, update, deletes, object/relational mappings, every time with the same format. This was so until I decided to create this tool. Now I spend 30 seconds doing what I used to do in 30 minutes and now I know that it will run without error from the first time if my templates are made correctly.

Are you interested?
Let's see how you can accomplish the same.

Syntax

Every template is created taking as its source a table from a database; then the mapping is created using the following database and table elements.

To obtain the:

• Database name we use

{DATABASE.NAME}

• Table name we use

  {TABLE.NAME}

• Table

All columns

{TABLE.COLUMNS}...{/TABLE.COLUMNS}

Primary key column

{TABLE.COLUMNS PRIMARY}...{/TABLE.COLUMNS}

All columns except the primary key

{TABLE.COLUMNS NOPRIMARY}...{/TABLE.COLUMNS}

• Column name

{COLUMN.NAME}

• Column type

{COLUMN.TYPE}

• Column length

{COLUMN.LENGTH}

• Column mapping type for a programming language.

{MAP COLUMN.TYPE}

• Conditionals

To validate a code depending on the column type

{IF COLUMN.TYPE EQ 'int'}...{/IF}

Connecting to a Database with code generator

To be able to connect the code generator to MySQL database you need to have a connection string with the following format:

"Data Source=mydb;Password=secret;User ID=user;Location=hostname;"

To connect to a SQLServer the connection string would be the following:

"SERVER=(local);DATABASE=mydb;UID=user;PWD=secret;"

Creating a basic SQL template

Template Example for SQL INSERT

CREATE PROCEDURE sp{TABLE.NAME}_Insert (
{TABLE.COLUMNS NOPRIMARY}
@{COLUMN.NAME} {COLUMN.TYPE} {IF COLUMN.TYPE EQ 'varchar'}({COLUMN.LENGTH}){/IF}{IF COLUMN.TYPE EQ 'nvarchar'}({COLUMN.LENGTH}){/IF},{/TABLE.COLUMNS}
{TABLE.COLUMNS PRIMARY}
@{COLUMN.NAME} {COLUMN.TYPE} OUTPUT {/TABLE.COLUMNS}
)
AS
INSERT INTO {TABLE.NAME} ({TABLE.COLUMNS NOPRIMARY}{COLUMN.NAME}{IF NOT LAST},{/IF}{/TABLE.COLUMNS})
VALUES ({TABLE.COLUMNS NOPRIMARY}@{COLUMN.NAME}{IF NOT LAST},{/IF}{/TABLE.COLUMNS})
{TABLE.COLUMNS PRIMARY}
SET @{COLUMN.NAME} = SCOPE_IDENTITY() {/TABLE.COLUMNS}

Will generate something like this:

CREATE PROCEDURE spCategories_Insert (

@CategoryName nvarchar(30),
@Description ntext,
@Picture image,

@CategoryID int OUTPUT
)
AS
INSERT INTO Categories (CategoryName,Description,Picture)
VALUES (@CategoryName,@Description,@Picture)

SET @CategoryID = SCOPE_IDENTITY()

1. {TABLE.NAME} is being replace for the name of the table (Categories)
2. {TABLE.COLUMNS}...{/TABLE.COLUMNS} iterates through the columns of that table, and replaces:
   1. {COLUMN.NAME} for the name of the current column,
      (i.e, CategoryName,Description,Picture )
   2. {COLUMN.TYPE} for the type of the current column,
      (i.e., nvarchar(30), ntext, image)
   3. {COLUMN.LENGTH} for the length value of the current column,
      (i.e.,nvarchar(30))
   4. {MAP COLUMN.TYPE}, not used in this example.
      

Creating a Basic PHP SELECT

<?php
mysql_connect("hostname", "user", "password");
mysql_select_db("mydb");
$result = mysql_query("select {TABLE.COLUMNS}{COLUMN.NAME}{IF NOT LAST}, {/IF}{/TABLE.COLUMNS} from {TABLE.NAME}");
while ($row = mysql_fetch_object($result)) {
{TABLE.COLUMNS}
echo $row->{COLUMN.NAME};{/TABLE.COLUMNS}
}
mysql_free_result($result);
?>

Will generate something like this:

<?php
mysql_connect("hostname", "user", "password");
mysql_select_db("mydb");
$result = mysql_query("select bookmarkId, name, url, description from items");
while ($row = mysql_fetch_object($result)) {

echo $row->bookmarkId;
echo $row->name;
echo $row->url;
echo $row->description;
}
mysql_free_result($result);
?>

Creating a Basic PHP INSERT

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('{DATABASE.NAME}');

mysql_query("INSERT INTO {TABLE.NAME}
({TABLE.COLUMNS NOPRIMARY}{COLUMN.NAME}{IF NOT LAST}, {/IF}{/TABLE.COLUMNS})
VALUES ({TABLE.COLUMNS NOPRIMARY}${COLUMN.NAME}{IF NOT LAST}, {/IF}{/TABLE.COLUMNS})");

{TABLE.COLUMNS PRIMARY}${COLUMN.NAME} = mysql_insert_id();{/TABLE.COLUMNS}
{TABLE.COLUMNS PRIMARY}printf("Last inserted record has id %d\n", ${COLUMN.NAME});{/TABLE.COLUMNS}
?>

Will generate something like this:

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('bookmark');

mysql_query("INSERT INTO items
(name, url, description)
VALUES ($name, $url, $description)");

$bookmarkId = mysql_insert_id();
printf("Last inserted record has id %d\n", $bookmarkId);
?>