I spent some time on this early this morning because I used to do this sort of thing for a living, and though I learned something interesting it didn't reveal any solutions. I turned on debugging in the ODBC connector for MySQL, and also monitored the TCP stream in Wireshark, just to see what was going on and when. When you link a table, Manifold makes this sequence of SQL statements: -- Query logging -- -- Driver name: MySQL ODBC 5.3 Driver Version: 05.03.0007 -- Timestamp: 170130 5:31:00 1485783060:SELECT * FROM `Foo`; 1485783060:Using direct execution; 1485783060:query has been executed; 1485783060:SHOW KEYS FROM `Foo`; 1485783060:SHOW KEYS FROM `Foo`; Now, the obvious question is, since a Drawing component will link read/write but a Table component won't, what's the difference? Well, first, when linking a Drawing Manifold will query MFD_META for information about the spatial index. That's how you get the Area of Interest prompt before it brings in the table: 1485783672:SELECT `PROP`, `VAL` FROM `MFD_META` WHERE `OBJ` = 'POI'; 1485783672:Using direct execution; 1485783672:query has been executed; 1485783672:Using prepared statement; 1485783672:SELECT `VAL` FROM `MFD_META` WHERE `OBJ` = 'POI' AND `PROP` = 'SpatialIndex'; 1485783672:Using direct execution; 1485783672:query has been executed; 1485783672:Using prepared statement; 1485783672:SELECT `VAL` FROM `MFD_META` WHERE `OBJ` = 'POI' AND `PROP` = 'SpatialIndexXMin'; [rest omitted] Then, when you link, it fetches the table contents, just as if you were linking a Table component: 1485783672:SELECT * FROM `POI`; 1485783672:Using direct execution; 1485783672:query has been executed; 1485783672:SHOW KEYS FROM `POI`; 1485783672:SHOW KEYS FROM `POI`; But what you don't see in the ODBC debug log is that Manifold is doing this to get just one row of the table using SELECT * (it shows up in the TCP stream): set @@sql_select_limit=1 SELECT * FROM `POI`; Then it fetches the entire table by SELECTING using explicit column names: 1485783672:SELECT `POI`.`OID`, `POI`.`Version`, `POI`.`SourceID`, `POI`.`Geometry`, `POI`.`Cat`, `POI`.`Name`, `POI`.`Country` FROM `POI`; It doesn't do it this way when you link a Table component. Presumably, it selects one row to get the list of columns, but I don't know. Anyway, after this, it then preparses a bunch of SQL statements on the server for use during update, with OID as the primary key. What happens if I select the POI drawing as a Table instead of as a Drawing? Sure enough, it uses the "table" execution sequence and not the "drawing" execution sequence. From the raw TCP stream: set @@sql_select_limit=DEFAULT SELECT * FROM `POI` SHOW KEYS FROM `POI` SHOW KEYS FROM `POI` So Manifold really does act differently if you are fetching a table as a Table component vs. as a Drawing component. And I don't mean the spatial indexing: how it actually fetches the table is different. And whether or not that table has an index doesn't seem to matter since the POI table definitely has a primary key: the OID column, which is used when it's linked as a Drawing component. So something is amiss here. But I don't know if it's worth pursuing, with the next major release just around the corner. Dimitri? What do you think? Is it worth reporting this as a potential bug in 8 or not?
|