I think this is nice for completeness but is probably not very practical.
I work with the Yii 2.0 Active Record ORM. This ORM has no configuration. It reads a table description from the server and uses it to relate object properties with table column names, datatypes and values. My work attempts to extend this ORM to dynamic columns yii2-dynamic-ar and has three main parts:
- Saving models to table records
- Loading table records into models
- Allowing individual dynamic columns to be used in queries, e.g. in a WHERE condition
2. is the hardest because, unlike normal columns, the ORM doesn't know anything about the content of dyncol_blobs. COLUMN_JSON() is convenient because it can load the whole dyncol_blob at once. But if I rely on it then I have to avoid using BINARY in a dynamic column (JSON can represent only valid Unicode strings) and avoid non-Unicode column names. Let's see how could I use COLUMN_GET_SQL() to overcome this limitation...
First the ORM would use COLUMN_LIST() to get dynamic column names. In the next query, it would use a number of COLUMN_GET_SQL() expressions to load datatypes and values. COLUMN_GET_SQL() would somehow indicate if a dynamic column itself contains a dyncol_blob, in which case the ORM makes two more queries to load first the names and then the types/values, and it keeps iterating like this down the data structure tree until it has everything.
You can imagine how I don't think this is very practical.
Alternatives:
- COLUMN_GET_SQL(dyncol_blob) without the column name to that returns a possibly long, possibly nested COLUMN_CREATE() expression (with casts).
- Follow the example of SHOW CREATE TABLE. I haven't worked out any details but it would return a table of strings which describe everything in the dyncol_blob: structure, column names, datatypes, values. It would use the standard client protocol to return the table.
- Use the same serialization that mysqldump uses for dynamic columns.
- Give up and impelment a new protocol, e.g. MongoDB's BSON, or Google's Protocol Buffers.
I like the 2. best. 1. requires me to write an SQL parser. 3. Requires standardizing (and probably versioning) the internal blob encoding. 4. is radical. 2 uses current protocols and is easy to live with. Perhaps:
> SELECT COLUMN_GET_ALL(`specification`) FROM `product`;
|
+--------------+--------------+------------+
|
| Name | Datatype | Value |
|
+--------------+--------------+------------+
|
| category | CHAR | Transistor |
|
| description | CHAR | TO-91 |
|
| manufacturer | CHAR | Fairchild |
|
| price | BLOB | NULL |
|
| price.unit | DECIMAL(9,5) | 0.03315 |
|
| price.100 | DECIMAL(9,5) | 2.45000 |
|
+--------------+--------------+------------+
|
Task
Major
I think this is nice for completeness but is probably not very practical.
I work with the Yii 2.0 Active Record ORM. This ORM has no configuration. It reads a table description from the server and uses it to relate object properties with table column names, datatypes and values. My work attempts to extend this ORM to dynamic columns yii2-dynamic-ar and has three main parts:
2. is the hardest because, unlike normal columns, the ORM doesn't know anything about the content of dyncol_blobs. COLUMN_JSON() is convenient because it can load the whole dyncol_blob at once. But if I rely on it then I have to avoid using BINARY in a dynamic column (JSON can represent only valid Unicode strings) and avoid non-Unicode column names. Let's see how could I use COLUMN_GET_SQL() to overcome this limitation...
First the ORM would use COLUMN_LIST() to get dynamic column names. In the next query, it would use a number of COLUMN_GET_SQL() expressions to load datatypes and values. COLUMN_GET_SQL() would somehow indicate if a dynamic column itself contains a dyncol_blob, in which case the ORM makes two more queries to load first the names and then the types/values, and it keeps iterating like this down the data structure tree until it has everything.
You can imagine how I don't think this is very practical.
Alternatives:
I like the 2. best. 1. requires me to write an SQL parser. 3. Requires standardizing (and probably versioning) the internal blob encoding. 4. is radical. 2 uses current protocols and is easy to live with. Perhaps:
> SELECT COLUMN_GET_ALL(`specification`) FROM `product`;+--------------+--------------+------------+| Name | Datatype | Value |+--------------+--------------+------------+| category | CHAR | Transistor || description | CHAR | TO-91 || manufacturer | CHAR | Fairchild || price | BLOB | NULL || price.unit | DECIMAL(9,5) | 0.03315 || price.100 | DECIMAL(9,5) | 2.45000 |+--------------+--------------+------------+