Create table with engine=OQGRAPH should check that the DATA_TABLE exists with the given origid, destid and if specified, weightid fields.

[Andrew McDonnell]

Currently it is possible to CREATE a ENGINE=OQGRAPH with a non-existent DATA_TABLE, or with ORIGID, DESTID or WEIGH attribute values for which a column does not exist in DATA_TABLE.

The 'table' created with ENGINE=OQGRAPH should be considered analogous to a VIEW, such that when it is created the relevant underlying table and columns should exist.

Example:

> describe tol;
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | |
| parent | int(10) unsigned | YES | | NULL | |
| position | int(11) | YES | | NULL | |
| name | varchar(100) | YES | MUL | NULL | |
| description | varchar(200) | YES | | NULL | |
+-------------+------------------+------+-----+---------+-------+

> CREATE TABLE tol_tree (
  wrong SMALLINT
  ) ENGINE=OQGRAPH data_table='bad' origid='id' destid='id';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ENGINE=OQGRAPH data_table='tol' origid='id' destid='id'' at line 5

The above is the correct behaviour, which already happens if the structure is invalid.

> CREATE TABLE tol_tree (
    latch SMALLINT UNSIGNED NULL,
    origid BIGINT UNSIGNED NULL,
    destid BIGINT UNSIGNED NULL,
    weight DOUBLE NULL,
    seq BIGINT UNSIGNED NULL,
    linkid BIGINT UNSIGNED NULL,
    KEY (latch, origid, destid) USING HASH,
    KEY (latch, destid, origid) USING HASH
  ) ENGINE=OQGRAPH data_table='bad' origid='id' destid='id';
Query OK, 0 rows affected (0.05 sec)

However, having a wrong attribute is not currently detected.

(CONFIRMED --> As per https://lists.launchpad.net/oqgraph-dev/msg00074.html )

[Andrew McDonnell (andymc73)]

This is reflected by errors when attempting to SELECT from the OQGRAPH table, but as discussed, should be avoided in the first place by checking at construction.

[Andrew McDonnell (andymc73)]

To be properly consistent with VIEW behaviour, somehow OQGRAPH needs to detect attempts to change the underlying table structure and attributes and act accordingly.

We have to choices:
1) go through and make sure that if the user does do this, it cant cause a crash somewhere - this should be checked anyway
2) and, detect if ALTER TABLE changing the referenced attributes in the same way as for a VIEW

For example, if a table referenced by a view is renamed, then the following happens:
ERROR 1356 (HY000): View 'test.x' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

But if DATA_TABLE is renamed, then SELECT * from oqgraph_table appears to still work as before...
If the column referenced is renamed, SELECT * from oqgraph_table appears to still work as before...

Need to decide correct behaviour in this instance.

[Andrew McDonnell (andymc73)]

Interestingly, in postgres enforces referential integrity - it is not possible to modify (specifically, drop) a table referenced by a view without first removing the view (unless CASCADE is specified)

[Andrew McDonnell (andymc73)]

Fixes primarily between commits 3643-3660 of http://bazaar.launchpad.net/~andymc73/oqgraph/10.0-oqgraph3-varchar/

Primarily in #3651with test cases in other commits

We cand detect invalid values or ORIGID DESTID WEIGHT until the first operation on the engine table, but at that stage
select * from graph; (etc) will fail with error - test coverage in create_attr.test