Any idea how to implement increments() by creating generators and triggers automatically

[donnykurnia]

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.

[jacquestvanzuydam]

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?

[donnykurnia]

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.

[jacquestvanzuydam]

You are correct, I will put some more work into it.

[cedamorim]

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

[donnykurnia]

@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.

[jacquestvanzuydam]

I will look into how to get migrations to create the generators and triggers, thank you for your feedback regarding this.

[sim1984]

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