Any idea how to implement increments() by creating generators and triggers automatically
Closed this issue · 7 comments
Firebird way for auto increments is using generators and trigger. Applications like Firebird Maestro and Flamerobin can create this automatically. I have been looking up Laravel 5.1 migration classes, but still have no idea how this could be created automatically when running the migration.
Yes, I am aware of using generators and triggers, and have thought of various ways of approaching this. I have not been able to implement a solution that works effectively.
Perhaps creating the generator/trigger inside the migration would be a good solution? Instead of creating it automatically?
Yes, it could be that way, but it will make the migration become database-specific one, not compatible when the same migration used in the different database. Database specific code should be handled in the driver class.
You are correct, I will put some more work into it.
Hey @jacquestvanzuydam , thanks for the Driver's Firebird and in relation to Laravel I see and I could (might not be the best solution, but this was) use generator for model
What did I do:
Firebird/Model.php
<?php
namespace Firebird;
use Illuminate\Database\Eloquent\Model as BaseModel;
use Illuminate\Database\Eloquent\Builder;
class Model extends BaseModel {
/**
* Insert the given attributes and set the ID on the model.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param array $attributes
* @return void
*/
protected function insertAndSetId(Builder $query, $attributes)
{
$nextSequenceId = $this->nextSequenceId();
$query->insert(array_merge( $attributes, [ $this->getKeyName() => $nextSequenceId ] ) );
$this->setAttribute($this->getKeyName(), $nextSequenceId);
}
protected function nextSequenceId(){
$result = $this->getConnection()
->table('RDB$DATABASE')
->select( $this->getConnection()->raw( "COALESCE( GEN_ID( $this->sequenceName, 1 ), 1 ) AS ID" ) )
->get();
return $result[0]->ID;
}
}
// Model
<?php
namespace App;
use Firebird\Model;
class User extends Model
{
protected $table = 'table name if necessary';
protected $primaryKey = 'important and required';
protected $sequenceName = 'what sequence you are using for this table';
}
If you want to follow a pattern and generate generator for all tables created, you can easily ignore the variable $ sequenceName and use something like $ this-> getSequenceId () // return $ tableName. '_G' Or anything else
I tried using the "insertGetId" as mentioned in the Laravel 5.1 documentation, but could not change the Primary Key
@cedamorim I have different approach. Because I design the table directly in the FireBird (not using migration), I have the generator and trigger in place to insert the id automatically. I have add PR #15 for this, based on PostgresGrammar and PostgresProcessor classes as-is.
If the migration could also create the generator and trigger automatically, then this library will be complete and useable.
I will look into how to get migrations to create the generators and triggers, thank you for your feedback regarding this.
<?php namespace Firebird\Schema;
use Illuminate\Database\Schema\Blueprint as BaseBlueprint;
class Blueprint extends BaseBlueprint
{
/**
* Use identity modifier for increment columns
*
* @var bool
*/
public $use_identity = false;
/**
* Indicate that it is necessary to use a identity modifier for increment columns
*
* @return void
*/
public function useIdentity()
{
$this->use_identity = true;
}
/**
* Determine if the blueprint has a create command.
*
* @return bool
*/
protected function droping()
{
foreach ($this->commands as $command) {
if (($command->name == 'drop') || ($command->name == 'dropIfExists')) {
return true;
}
}
return false;
}
/**
* Add the commands that are implied by the blueprint.
*
* @return void
*/
protected function addImpliedCommands()
{
parent::addImpliedCommands();
if (!$this->use_identity) {
$this->addSequence();
$this->addAutoIncrementTrigger();
}
if ($this->droping() && !$this->use_identity) {
$this->dropSequence();
}
}
/**
* Add the command for create sequence for table
*
* @return void
*/
protected function addSequence()
{
foreach ($this->columns as $column) {
if ($column->autoIncrement) {
array_push($this->commands, $this->createCommand('sequenceForTable'));
break;
}
}
}
/**
* Add the command for drop sequence for table
*
* @return void
*/
protected function dropSequence()
{
array_push($this->commands, $this->createCommand('dropSequenceForTable'));
}
/**
* Add the command for create trigger
*
* @return void
*/
protected function addAutoIncrementTrigger()
{
foreach ($this->columns as $column) {
if ($column->autoIncrement) {
array_push($this->commands, $this->createCommand('triggerForAutoincrement', ['columnname' => $column->name]));
break;
}
}
}
}
<?php namespace Firebird\Schema;
use Illuminate\Database\Schema\Builder as BaseBuilder;
use Firebird\Schema\Blueprint;
use Closure;
class Builder extends BaseBuilder
{
/**
* Create a new command set with a Closure.
*
* @param string $table
* @param \Closure|null $callback
* @return \Firebird\Schema\Blueprint
*/
protected function createBlueprint($table, Closure $callback = null)
{
if (isset($this->resolver)) {
return call_user_func($this->resolver, $table, $callback);
}
return new Blueprint($table, $callback);
}
}
<?php namespace Firebird\Schema\Grammars;
use Illuminate\Database\Schema\Grammars\Grammar;
use Illuminate\Support\Fluent;
use Illuminate\Database\Schema\Blueprint;
class FirebirdGrammar extends Grammar
{
// ...... missing code
/**
* Get the SQL for an auto-increment column modifier.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $column
* @return string|null
*/
protected function modifyIncrement(Blueprint $blueprint, Fluent $column)
{
if (in_array($column->type, $this->serials) && $column->autoIncrement) {
// identity columns support beginning Firebird 3.0 and above
return $blueprint->use_identity ? ' GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' : ' PRIMARY KEY';
}
}
/**
* Compile a create sequence command for table.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileSequenceForTable(Blueprint $blueprint, Fluent $command)
{
$sequence = $this->wrap(substr('seq_' . $blueprint->getTable(), 0, 31));
return "CREATE SEQUENCE {$sequence}";
}
/**
* Compile a drop sequence command for table.
*
* @param \Illuminate\Database\Schema\SequenceBlueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileDropSequenceForTable(Blueprint $blueprint, Fluent $command)
{
$sequenceName = substr('seq_' . $blueprint->getTable(), 0, 31);
$sequence = $this->wrap($sequenceName);
$sql = 'EXECUTE BLOCK' . "\n";
$sql .= 'AS' . "\n";
$sql .= 'BEGIN' . "\n";
$sql .= " IF (EXISTS(SELECT * FROM RDB\$GENERATORS WHERE RDB\$GENERATOR_NAME = '{$sequenceName}')) THEN" . "\n";
$sql .= " EXECUTE STATEMENT 'DROP SEQUENCE {$sequence}';" . "\n";
$sql .= 'END';
return $sql;
}
/**
* Compile a create trigger for support autoincrement.
*
* @param \Illuminate\Database\Schema\Blueprint $blueprint
* @param \Illuminate\Support\Fluent $command
* @return string
*/
public function compileTriggerForAutoincrement(Blueprint $blueprint, Fluent $command)
{
$table = $this->wrapTable($blueprint);
$trigger = $this->wrap(substr('tr_' . $blueprint->getTable() . '_bi', 0, 31));
$column = $this->wrap($command->columnname);
$sequence = $this->wrap(substr('seq_' . $blueprint->getTable(), 0, 31));
$sql = "CREATE OR ALTER TRIGGER {$trigger} FOR {$table}\n";
$sql .= "ACTIVE BEFORE INSERT\n";
$sql .= "AS\n";
$sql .= "BEGIN\n";
$sql .= " IF (NEW.{$column} IS NULL) THEN \n";
$sql .= " NEW.{$column} = NEXT VALUE FOR {$sequence};\n";
$sql .= 'END';
return $sql;
}
// ...... missing code
}
I implemented in my branch code, see https://github.com/sim1984/laravel-firebird