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.
Installs: 267
Dependents: 0
Suggesters: 0
Security: 0
Stars: 0
Watchers: 7
Forks: 0
Open Issues: 0
Type:symfony1-plugin
Requires
- composer/installers: ~1.0
README
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.
Contents
It gives you one task to execute SQL
files with additional options.
Installation
- Download the plugin
Add the plugin your composer.json
requirements:
"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
-
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 to run one specific file in directory
"data/sql/tasks_1"
$ ./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=*
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