4 users Sign-in to change your status

Task to execute SQL files with triggers, procedures and events in order. Compatible with any kind of database engines. Development

• Homepage
• Repository
• Report a bug
• Download package
• Browse source

The sfSQLToolsPlugin is a symfony plugin that provides easy way to execute database specific features like stored procedures, triggers, events and any other SQL commands.

Developers

Name	Status	Email
Ilya Sabelnikov	lead	moc.liamg <<ta>> ved.tiurf

License

Copyright (c) 2008-2009 Ilya Sabelnikov <fruit dot dev at gmail dot com>

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and
associateddocumentation files (the "Software"), to deal in the Software without restriction,
including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense,
and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial
portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT
LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTIONOF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Releases for sf 1.4

Version	License	API	Released
1.0.3stable	MIT	1.0.0stable	30/05/2010
1.0.2stable	MIT	1.0.0stable	30/05/2010
1.0.1stable	MIT	1.0.0stable	19/12/2009

Releases for sf 1.3

Version	License	API	Released
1.0.3stable	MIT	1.0.0stable	30/05/2010
1.0.2stable	MIT	1.0.0stable	30/05/2010
1.0.1stable	MIT	1.0.0stable	19/12/2009

Releases for sf 1.2

Version	License	API	Released
1.0.3stable	MIT	1.0.0stable	30/05/2010
1.0.2stable	MIT	1.0.0stable	30/05/2010
1.0.1stable	MIT	1.0.0stable	19/12/2009
1.0.0stable	MIT	1.0.0stable	20/09/2009
0.1.0beta	MIT license	0.1.0beta	19/09/2009
0.0.5alpha	MIT license	0.0.1alpha	18/09/2009
0.0.4alpha	MIT license	0.0.1alpha	18/09/2009
0.0.2alpha	MIT license	0.0.1alpha	18/09/2009

Join this plugin team

Other releases

Release 1.0.3 - 30/05/2010

Release 1.0.2 - 30/05/2010

Release 1.0.1 - 19/12/2009

Release 1.0.0 - 20/09/2009

Release 0.1.0 - 19/09/2009

Release 0.0.5 - 18/09/2009

Release 0.0.4 - 18/09/2009

Release 0.0.2 - 18/09/2009

sfSQLToolsPlugin

The sfSQLToolsPlugin is a symfony plugin that provides easy way to execute database specific features like stored procedures, triggers, events and any other SQL commands.

Contents

It gives you one task to execute SQL files with additional options to exclude extra files.

Installation

• Install the plugin

  $ ./symfony plugin:install sfSQLToolsPlugin
  

• 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 environement (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

• Call it with:

  $ ./symfony sql:execute
  

• To work in certain environment run this command with --env option

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

• To use certain application`s database settings use --application option

  $ ./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:

  $ ./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:

  $ ./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:

  $ ./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"):

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

• To run only one specific SQL file use --file:

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

• or

  $ ./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:

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

• To search for .sql file recursively pass "" to --dir-depth option:

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

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 recieve 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
  

Repository

• plugin repository http://github.com/fruit/sfSQLToolsPlugin
• plugin tickets http://github.com/fruit/sfSQLToolsPlugin/issues

Unit test

• Unit tests (14 of 14) successfully completed (tested with MySQL 5.0.84 and PostgreSQL 8.3.8)

TODO

• check the spelling in English [priority 2]