Table relationships question

[samlevin]

How can I set 'foreign key' constraints in MySQL using JSONModel/SQLAbstract stack? These are important for my schema. Can I do it?

[laurentszyster]

There is nothing (yet) in JSONModel nor SQLAbstract to set foreign key constraints.

To add support for foreign keys would imply two modifications:

a) Add a constraints argument to SQLAbstract->createTableStatement
b) Add a foreign property to JSONModel and support for a foreign option in its constructor.

Instead of:

SQLAbstract->createTableStatement ($name, $columns, $primary)

We would have:

SQLAbstract->createTableStatement ($name, $columns, $constraints)

Where $constraints is a simple list of SQL constraints.

The definition of PRIMARY KEY constraint is moved outside of SQLAbstract.

In JSONModel.

Were a foreign option is added, as a mapping of column name(s) to the foreign JSONModel(s):

function tagsTable (SQLAbstract $sql) {
    return new JSONModel($sql, array(
        'name' => 'tags',
        'primary' => array(
            'tag_task', 'tag_label'
        ),
        'foreign' => array(
            'tag_task' => taskTable($sql)
        ),
        'types' => array(
            'tag_task' => 'intval'
        )
    ));
}

Besides generating FOREIGN KEY constraints, this foreign option can also be used to query related models (without having to specify the relation outside of the models definitions).

[laurentszyster]

see: unframed/SQLAbstract.php#4

[laurentszyster]

Having a foreign option (that defines foreign key constraints) also enables models to relate selections automatically.

With the above example, for instance to relate tasks to tags selected by safe options:

$tagsTable->relate($options);