[PGsql] Column default function value

yassinrais commented 3 years ago

It will be cool if we can add a default function value for the column

new Column(
      'id',
      [
          'type' => Column::TYPE_CHAR,
          'default' => "uuid_generate_v4()",
          'notNull' => true,
          'size' => 36,
          'first' => true
      ]
  ),

INSERT INTO dma.city (  country_id, name, lat, lng, population, status, title) 
            VALUES ( 'FR','france',1.0, 1.0, 100000000, 1 , 'France' );

Beautiful solution

SQL :

ALTER TABLE dma.city ALTER COLUMN id SET DEFAULT uuid_generate_v4()

PHP (expected) :

new Column(
  'id',
  [
      'type' => Column::TYPE_CHAR,
      'notNull' => true,
      'size' => 36,
      'first' => true,
      'function'=>  "uuid_generate_v4()"
  ]
),

BeMySlaveDarlin commented 2 years ago

! Feature can be implemented only in cphalcon repo

List of required changes

Phalcon\Db\Adapter\Pdo\Postgresql::createTable() major behavior changes
Phalcon\Db\Adapter\Pdo\Postgresql::addColumn() major behavior changes
Phalcon\Db\Adapter\Pdo\Postgresql::modifyColumn() major behavior changes
Phalcon\Db\Column adding new property 'function'
Phalcon\Db\Column adding new column type 'Column::TYPE_UUID'

Requirement of possible default functions list

As far i researched, to implement this, you need to know at least list of possible default_value functions.

# Example list of functions (procedures) for uuid
uuid_generate_v1()
uuid_generate_v1mc()
uuid_generate_v3()
uuid_generate_v4()
uuid_generate_v5()
uuid_nil()
uuid_ns_dns()
uuid_ns_oid()
uuid_ns_url()
uuid_ns_x500()

The reason we need possible default functions list is

SELECT * FROM  information_schema.columns WHERE table_name = 'tbl';

Снимок Describe just returns default value without telling us is it a function or just a text. Another approach - some magic to detect whether default value is text or function - to point braces as prefix ()

Example of uuid pkey setup

# Ensure uuid procedures available
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; 

# Creating table with uuid pkey column
CREATE TABLE "tbl" (
    "pkey" UUID NOT NULL DEFAULT 'uuid_generate_v4()',
    PRIMARY KEY ("pkey")
);

# Altering table with uuid pkey column
ALTER TABLE "tbl" ALTER COLUMN "pkey" SET DATA TYPE UUID USING (uuid_generate_v4());

# Migration column example
  new Column(
      'id',
      [
          'type' => Column::TYPE_UUID,
          'default'=>  "uuid_generate_v4()"
          'notNull' => true,
          'first' => true,
      ]
  ),

Jeckerson commented 2 years ago

Currently it is impossible to do this via morph(), only by executing manually the alter query. We need to change a lot in own Phalcon\Db component to achieve desired..

phalcon / migrations