New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PostgreSQL "create table" sql builder fail #13132

Closed
Urvin opened this Issue Oct 23, 2017 · 1 comment

Comments

Projects
None yet
3 participants
@Urvin

Urvin commented Oct 23, 2017

Hello!
I found two fatal errors when creating tables in database using the $_connection->createTable method.
So, I try to create table 'user' in 'app' schema with the next definition:

[
    'columns' => [
        new Column(
            'id',
            [
                'type' => Column::TYPE_INTEGER,
                'notNull' => true,
                'autoIncrement' => true,
                'first' => true
            ]
        ),
        new Column(
            'created_at',
            [
                'type' => Column::TYPE_TIMESTAMP,
                'default' => "now()",
                'notNull' => true,
                'size' => 1,
                'after' => 'id'
            ]
        ),
        new Column(
            'updated_at',
            [
                'type' => Column::TYPE_TIMESTAMP,
                'default' => "now()",
                'notNull' => true,
                'size' => 1,
                'after' => 'created_at'
            ]
        ),
        new Column(
            'login',
            [
                'type' => Column::TYPE_VARCHAR,
                'notNull' => true,
                'size' => 100,
                'after' => 'updated_at'
            ]
        ),
        new Column(
            'password',
            [
                'type' => Column::TYPE_VARCHAR,
                'notNull' => true,
                'size' => 100,
                'after' => 'login'
            ]
        ),
        new Column(
            'role',
            [
                'type' => Column::TYPE_INTEGER,
                'notNull' => true,
                'after' => 'password'
            ]
        ),
        new Column(
            'force_change',
            [
                'type' => Column::TYPE_BOOLEAN,
                'default' => "false",
                'notNull' => true,
                'after' => 'role'
            ]
        ),
        new Column(
            'active',
            [
                'type' => Column::TYPE_BOOLEAN,
                'default' => "true",
                'notNull' => true,
                'after' => 'force_change'
            ]
        )
    ],
    'indexes' => [
        new Index('idx_search', ['login'], null),
        new Index('unq_login', ['login'], 'UNIQUE'),
        new Index('user_pkey', ['id'], 'PRIMARY')
    ],
    'references' => [
        new Reference(
            'ifx_role',
            [
                'referencedTable' => 'rbac',
                'referencedSchema' => 'encallapp',
                'columns' => ['role'],
                'referencedColumns' => ['id'],
                'onUpdate' => 'CASCADE',
                'onDelete' => 'CASCADE'
            ]
        )
    ],
]

And here's the SQL code Phalcon generated to be run:

CREATE TABLE "app"."user" (   DEFAULT false DEFAULT true"id" SERIAL NOT NULL,  "created_at" TIMESTAMP DEFAULT 'now()' NOT NULL,  "updated_at" TIMESTAMP DEFAULT 'now()' NOT NULL,  "login" CHARACTER VARYING(100) NOT NULL,  "password" CHARACTER VARYING(100) NOT NULL, "role" INT NOT NULL,  "force_change" BOOLEAN NOT NULL,  "active" BOOLEAN NOT NULL,  CONSTRAINT "unq_login" UNIQUE ("login"),  CONSTRAINT "user_pkey" PRIMARY ("id"),  CONSTRAINT "ifx_role" FOREIGN KEY ("role") REFERENCES "encallapp"."rbac" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
  1. DEFAULT false DEFAULT true
    You see' default values for my 'force_change' and 'active' gone to beginning of SQL columns definitions.
    Seems like an error in /blob/master/phalcon/db/dialect/postgresql.zep:380 file. Probably, it sould be
let columnLine .= " DEFAULT " . defaultValue;
  1. CONSTRAINT "user_pkey" PRIMARY ("id")
    'KEY' directive is missing. Should be 'CONSTRAINT "user_pkey" PRIMARY KEY ("id")'

Details

  • Phalcon version: 3.2.4
  • PHP Version: 7.0.22
  • Operating System: Ubuntu 16.04
  • Installation type: installing via package manager
  • Zephir version (if any): no
  • Server: Nginx
  • Postgresql 9.5
@sergeyklay

This comment has been minimized.

Show comment
Hide comment
@sergeyklay

sergeyklay Nov 17, 2017

Member

Fixed in the 3.3.x branch. Feel free to open a new issue if the problem appears again. Thank you for contributing.

Member

sergeyklay commented Nov 17, 2017

Fixed in the 3.3.x branch. Feel free to open a new issue if the problem appears again. Thank you for contributing.

@sergeyklay sergeyklay closed this Nov 17, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment