fos1/sf-s-q-l-tools-plugin

Doctrine SQL Tools Plugin for Symfony1. Task to execute SQL files with triggers, procedures and events in order. Compatible with any kind of database engines.

sfSQLToolsPlugin

The sfSQLToolsPlugin is a symfony1 plugin that provides easy way to execute database specific features like stored procedures, triggers, events and any other SQL commands., _(*1)

Contents

It gives you one task to execute SQL files with additional options., _(*2)

Installation

• Download the plugin

Add the plugin your composer.json requirements:, _(*3)

  "require": {
    ...
    "fos1/sf-s-q-l-tools-plugin": "1.0.*",
    ...

• Install the plugin: edit your ProjectConfiguration.class.php file

  $this->enablePlugins(array('sSQLToolsPlugin');

• Clear you cache symfony cc

Usage

symfony sql:execute [--application[="..."]] [--env[="..."]] [--dir[="..."]] [--dir-depth[="..."]] [--file[="..."]] [--exclude[="..."]] [--delimiter[="..."]]

Options:

• --application The application name (default: 1)
• --env The environment (default: dev)
• --dir The directory where to look for *.sql file (default: data/sql/tasks)
• --dir-depth Search directory depth (default: 0)
• --file One file to be executed
• --exclude Exclude file pattern or file list separated by commas
• --delimiter Query delimiter (default: ~)

Description:

The sql:execute task reads *.sql files in search directory and then runs them in order, _(*4)

• Call it with:, _(*5)

  $ ./symfony sql:execute

• To work in certain environment run this command with --env option, _(*6)

  $ ./symfony sql:execute --env=prod

• To use certain application`s database settings use --application option, _(*7)

  $ ./symfony sql:execute --application=frontend

• If you need to customize the *.sql location dirname (default is "data/sql/tasks"), you can pass a --dir option:, _(*8)

  $ ./symfony sql:execute --dir=data/my/folder

• To exclude one or more files from --dir folder use --exclude option. In order to exclude "00-misc.sql" file from "data/my/folder" directory use:, _(*9)

  $ ./symfony sql:execute --dir=data/my/folder --exclude="00-misc.sql"

• In order to exclude many files from "data/my/folder" directory, separate is by commas:, _(*10)

  $ ./symfony sql:execute --dir=data/my/folder --exclude="00-misc.sql, 10-triggers.sql, 20-events.sql"

• Or you can use glob patterns (exclude all filename which contains words: U_"old"_ and "backup"):, _(*11)

  $ ./symfony sql:execute --dir=data/my/folder --exclude="*old*,*backup*"

• To run only one specific SQL file use --file:, _(*12)

  $ ./symfony sql:execute --file=data/sql/tasks_1/alter-tables.sql

• or to run one specific file in directory "data/sql/tasks_1", _(*13)

  $ ./symfony sql:execute --dir=data/sql/tasks_1 --file=alter-tables.sql

• To search for *.sql file until sub folder certain level use --dir-depth option:, _(*14)

  $ ./symfony sql:execute --dir-depth=5

• To search for *.sql file recursively pass "*" to --dir-depth option:, _(*15)

  $ ./symfony sql:execute --dir-depth=*

Example

This is your file "00-procedures.sql" content (MySQL)

        CREATE PROCEDURE `simpleproc`(OUT param1 INT)
        BEGIN
            SELECT COUNT(*) INTO param1 FROM t;
        END
        ~
        CREATE FUNCTION `hello`(s CHAR(20)) RETURNS CHAR(50)  RETURN CONCAT('Hello, ',s,'!');
        ~
        CREATE PROCEDURE molo() SELECT 'Molo';

• Now, setup your DBMS conntecion in config/databases.yml (if you haven't done this yet)
• And execute this procedures in development environment: symfony sql:execute --env=dev --file=data/sql/00-procedures.sql
• After you run this task, you should get the following output:

>> sql:execute start
>> sql:execute [00-procedures.sql] CREATE PROC...OUNT(*) INTO param1 FROM t; END
>> sql:execute [00-procedures.sql] CREATE FUNC...RETURN CONCAT('Hello, ',s,'!');
>> sql:execute [00-procedures.sql] CREATE PROCEDURE molo() SELECT 'Molo';
>> sql:execute end

Unit test

• Unit tests (14 of 14) successfully completed.
• Tested with:
  • MySQL 5.0.84
  • MySQL 5.1.40
  • PostgreSQL 8.3.8