Skip to content
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

[BUG]: SQLite adapter does not support NOT NULL columns with default values #15180

Closed
zsilbi opened this issue Oct 18, 2020 · 1 comment
Closed
Assignees
Labels
bug A bug report

Comments

@zsilbi
Copy link
Member

zsilbi commented Oct 18, 2020

Describe the bug

When creating a new Model the Adapter will throw an Exception like the following, if:

  • the schema has columns with default values
  • these fields are not defined/null in the inserted model
[PDOException] SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: 
co_customers_defaults.cst_status_flag

The SQLite adapter will create the following statement:

INSERT INTO "co_customers_defaults" ("cst_status_flag", "cst_name_last", "cst_name_first", "cst_id") 
VALUES (NULL, NULL, NULL, null)

NULL is inserted for the 3 unset fields, because Db\Adapter\Pdo\Sqlite::getDefaultValue() returns new RawValue('NULL') for any unset fields that have default values in MetaData (This works well for MySQL with RawValue('DEFAULT'))

To Reproduce

create table co_customers_defaults
(
    cst_id          integer constraint co_customers_defaults_pk primary key autoincrement,
    cst_status_flag integer      not null DEFAULT 1,
    cst_name_last   text         not null DEFAULT 'cst_default_lastName',
    cst_name_first  text         not null DEFAULT 'cst_default_firstName'
);
class CustomersDefaults extends Model
{
    public $cst_id;
    public $cst_status_flag;
    public $cst_name_last;
    public $cst_name_first;

    public function initialize()
    {
        $this->setSource('co_customers_defaults');
    }
}

$customer = new CustomersDefaults();
$customer->create(); // Should return true

Tested here: https://github.com/phalcon/cphalcon/pull/15179/checks?check_run_id=1271542325

Proposed solution:

Modify Db\Adapter::getDefaultValue() to return false when it's not supported by the adapter. (Breaks BC, maybe a new method could be added until 6.0)
Modify Mvc\Model not to include these fields in the statements when the adapter doesn't support it:

INSERT INTO "co_customers_defaults" ("cst_id") VALUES (null)

cc @phalcon/core-team

@zsilbi zsilbi added discussion Request for comments and discussion bug A bug report labels Oct 18, 2020
@zsilbi zsilbi added 4.1.0 and removed discussion Request for comments and discussion labels Oct 20, 2020
@zsilbi zsilbi self-assigned this Oct 20, 2020
@zsilbi
Copy link
Member Author

zsilbi commented Oct 20, 2020

Resolved in #15182

@zsilbi zsilbi closed this as completed Oct 20, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug A bug report
Projects
None yet
Development

No branches or pull requests

1 participant