[Maria-discuss] getting all name-value pairs from a dynamic column
These docs are excellent -- http://kb.askmonty.org/en/dynamic-columns
How do you fetch all name-value pairs from a dynamic column in a format that can be consumed by a client? The client can select the column as is but then must know how mariadb encoded it. The function column_list returns the column names. If there were a function that encoded all name-value pairs as JSON, then the client could do something like this and be able to decode the result: select column_json(dynamic_column_name) from t where ...
Hi!
"MARK" == MARK CALLAGHAN mdcallag@gmail.com writes:
MARK> These docs are excellent -- http://kb.askmonty.org/en/dynamic-columns
Thanks.
MARK> How do you fetch all name-value pairs from a dynamic column in a MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Yes, even if the column names are numbers for the moment.
MARK> If there were a function that MARK> encoded all name-value pairs as JSON, then the client could do MARK> something like this and be able to decode the result: MARK> select column_json(dynamic_column_name) from t where ...
We just got funding for half of the future planned work for dynamic columns. We will soon start looking at how to encode names for dynamic columns.
Regards, Monty
Monty,
I wrote this function already, and had it working as fast as any query, if you'd like my code I wrote for a previous employer (and was NOT used, so is available) I would gladly turn this over to the project. I called it JSONIFY(QUERY_TEXT).
Jakob Lorberblatt
Hi!
"MARK" == MARK CALLAGHAN mdcallag@gmail.com writes:
MARK> These docs are excellent -- http://kb.askmonty.org/en/dynamic-columns
Thanks.
MARK> How do you fetch all name-value pairs from a dynamic column in a MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Yes, even if the column names are numbers for the moment.
MARK> If there were a function that MARK> encoded all name-value pairs as JSON, then the client could do MARK> something like this and be able to decode the result: MARK> select column_json(dynamic_column_name) from t where ...
We just got funding for half of the future planned work for dynamic columns. We will soon start looking at how to encode names for dynamic columns.
Regards, Monty
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi!
"Jakob" == Jakob Lorberblatt jakob@native-intelligence.net writes:
Jakob> Monty,
Jakob> I wrote this function already, and had it working as fast as any query, if Jakob> you'd like my code I wrote for a previous employer (and was NOT used, so Jakob> is available) I would gladly turn this over to the project. Jakob> I called it JSONIFY(QUERY_TEXT).
Yes, please post it to me and I will take a look at including that into MariaDB.
I assume it's ok that you give us the code under the new BSD license (no attribution)?
Regards, Monty
Jakob> Jakob Lorberblatt
Hi!
> "MARK" == MARK CALLAGHAN mdcallag@gmail.com writes:
MARK> These docs are excellent --
http://kb.askmonty.org/en/dynamic-columns
Thanks.
MARK> How do you fetch all name-value pairs from a dynamic column in a MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Yes, even if the column names are numbers for the moment.
MARK> If there were a function that MARK> encoded all name-value pairs as JSON, then the client could do MARK> something like this and be able to decode the result: MARK> select column_json(dynamic_column_name) from t where ...
We just got funding for half of the future planned work for dynamic columns. We will soon start looking at how to encode names for dynamic columns.
Regards, Monty
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Jakob> _______________________________________________ Jakob> Mailing list: https://launchpad.net/~maria-discuss Jakob> Post to : maria-discuss@lists.launchpad.net Jakob> Unsubscribe : https://launchpad.net/~maria-discuss Jakob> More help : https://help.launchpad.net/ListHelp
Hi!
"Jakob" == Jakob Lorberblatt jakob@native-intelligence.net writes:
Jakob> Monty,
Jakob> I wrote this function already, and had it working as fast as any query, if Jakob> you'd like my code I wrote for a previous employer (and was NOT used, so Jakob> is available) I would gladly turn this over to the project. Jakob> I called it JSONIFY(QUERY_TEXT).
Yes, please post it to me and I will take a look at including that into MariaDB.
I assume it's ok that you give us the code under the new BSD license (no attribution)?
Regards, Monty
On Fri, Dec 2, 2011 at 5:23 AM, Michael Widenius monty@askmonty.org wrote:
MARK> How do you fetch all name-value pairs from a dynamic column in a MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
In the client
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Dynamic columns are dynamic. The client doesn't know which key names are used for each row selected. With the current code there is no way for the client to get all key:value pairs set in a dynamic column for some number of rows. They can select the column but probably won't know how to decode the name:value pairs stored in it.
Apparently Jakob has code for this. So I don't have much more to add for this thread.
I think I want a function that converts the blob into a JSON formatted set of key-value pairs.
On Wed, Dec 7, 2011 at 9:19 PM, MARK CALLAGHAN mdcallag@gmail.com wrote:
On Fri, Dec 2, 2011 at 5:23 AM, Michael Widenius monty@askmonty.org wrote:
MARK> How do you fetch all name-value pairs from a dynamic column in a MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
In the client
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Dynamic columns are dynamic. The client doesn't know which key names are used for each row selected. With the current code there is no way for the client to get all key:value pairs set in a dynamic column for some number of rows. They can select the column but probably won't know how to decode the name:value pairs stored in it.
Apparently Jakob has code for this. So I don't have much more to add for this thread.
-- Mark Callaghan mdcallag@gmail.com
Isn't this request something like this http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html - only not only XML but also JSON should be supported?
I remember when this XML functionality was introduced in early MySQL 5.1 there was quite a lot of *buzz* about it. I never heard anything about it since. I have not even seen a bug report! It seems that nobody knows it and nobody uses it.
I agree that JSON is an important format. But there are lots such. A little polemically I could say that as since some people store .mp3/.mp4 snippets in a BLOB why not add a media streaming functionality to the server ... and playback functionalities to the standard client :-)
I think such stuff belongs in a 'stored functions library' and are not a core server feature. But I realize that as long as stored functions programming only supports SQL (and not Perl like in Postgsql and not C# as in recent SQL Server) it is not really much feasible. Currently it requires coding in the application/client environment (such as a PHP class for instance).
(you may find this an off-topic comment from a non-coder (and you are right) - but I wanted to put a little perspective on this. IMHO this reqeust arises bcos of poor 'stored programs' programming options in MySQL)
-- Peter -- Webyog
On Wed, Apr 11, 2012 at 19:40, MARK CALLAGHAN mdcallag@gmail.com wrote:
I think I want a function that converts the blob into a JSON formatted set of key-value pairs.
On Wed, Dec 7, 2011 at 9:19 PM, MARK CALLAGHAN mdcallag@gmail.com wrote:
On Fri, Dec 2, 2011 at 5:23 AM, Michael Widenius monty@askmonty.org
wrote:
MARK> How do you fetch all name-value pairs from a dynamic column in a MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
In the client
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Dynamic columns are dynamic. The client doesn't know which key names are used for each row selected. With the current code there is no way for the client to get all key:value pairs set in a dynamic column for some number of rows. They can select the column but probably won't know how to decode the name:value pairs stored in it.
Apparently Jakob has code for this. So I don't have much more to add for this thread.
-- Mark Callaghan mdcallag@gmail.com
-- Mark Callaghan mdcallag@gmail.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Isn't this request something like this http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html - only not only XML but also JSON should be supported?
yes! its a more useful format, as Mark points out below, I have code for it. I'll test it on a more recent version of mariadb in the next couple days, and unearth my old design, I'll respond with code in the next few days I am physically separate currently from my archived code. I would love to see someone make use of it.
Jakob
I remember when this XML functionality was introduced in early MySQL 5.1 there was quite a lot of *buzz* about it. I never heard anything about it since. I have not even seen a bug report! It seems that nobody knows it and nobody uses it.
I agree that JSON is an important format. But there are lots such. A little polemically I could say that as since some people store .mp3/.mp4 snippets in a BLOB why not add a media streaming functionality to the server ... and playback functionalities to the standard client :-)
I think such stuff belongs in a 'stored functions library' and are not a core server feature. But I realize that as long as stored functions programming only supports SQL (and not Perl like in Postgsql and not C# as in recent SQL Server) it is not really much feasible. Currently it requires coding in the application/client environment (such as a PHP class for instance).
(you may find this an off-topic comment from a non-coder (and you are right) - but I wanted to put a little perspective on this. IMHO this reqeust arises bcos of poor 'stored programs' programming options in MySQL)
-- Peter -- Webyog
On Wed, Apr 11, 2012 at 19:40, MARK CALLAGHAN mdcallag@gmail.com wrote:
I think I want a function that converts the blob into a JSON formatted set of key-value pairs.
On Wed, Dec 7, 2011 at 9:19 PM, MARK CALLAGHAN mdcallag@gmail.com wrote:
On Fri, Dec 2, 2011 at 5:23 AM, Michael Widenius monty@askmonty.org
wrote:
MARK> How do you fetch all name-value pairs from a dynamic column in
a
MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
In the client
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Dynamic columns are dynamic. The client doesn't know which key names are used for each row selected. With the current code there is no way for the client to get all key:value pairs set in a dynamic column for some number of rows. They can select the column but probably won't know how to decode the name:value pairs stored in it.
Apparently Jakob has code for this. So I don't have much more to add for this thread.
-- Mark Callaghan mdcallag@gmail.com
-- Mark Callaghan mdcallag@gmail.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Isn't this request something like this http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html - only not only XML but also JSON should be supported?
yes! its a more useful format, as Mark points out below, I have code for it. I'll test it on a more recent version of mariadb in the next couple days, and unearth my old design, I'll respond with code in the next few days I am physically separate currently from my archived code. I would love to see someone make use of it.
Jakob
I remember when this XML functionality was introduced in early MySQL 5.1 there was quite a lot of *buzz* about it. I never heard anything about it since. I have not even seen a bug report! It seems that nobody knows it and nobody uses it.
I agree that JSON is an important format. But there are lots such. A little polemically I could say that as since some people store .mp3/.mp4 snippets in a BLOB why not add a media streaming functionality to the server ... and playback functionalities to the standard client :-)
I think such stuff belongs in a 'stored functions library' and are not a core server feature. But I realize that as long as stored functions programming only supports SQL (and not Perl like in Postgsql and not C# as in recent SQL Server) it is not really much feasible. Currently it requires coding in the application/client environment (such as a PHP class for instance).
(you may find this an off-topic comment from a non-coder (and you are right) - but I wanted to put a little perspective on this. IMHO this reqeust arises bcos of poor 'stored programs' programming options in MySQL)
-- Peter -- Webyog
On Wed, Apr 11, 2012 at 19:40, MARK CALLAGHAN mdcallag@gmail.com wrote:
I think I want a function that converts the blob into a JSON formatted set of key-value pairs.
On Wed, Dec 7, 2011 at 9:19 PM, MARK CALLAGHAN mdcallag@gmail.com wrote:
On Fri, Dec 2, 2011 at 5:23 AM, Michael Widenius monty@askmonty.org
wrote:
MARK> How do you fetch all name-value pairs from a dynamic column in
a
MARK> format that can be consumed by a client?
Do you mean from SQL or in the client library?
In the client
MARK> The client can select the MARK> column as is but then must know how mariadb encoded it.
Sorry, don't understand the question.
When asking for a value for a column in the client , you get back a structure that tells how the data is encoded. See the 'struct st_dynamic_column_value'
MARK> The function MARK> column_list returns the column names.
Dynamic columns are dynamic. The client doesn't know which key names are used for each row selected. With the current code there is no way for the client to get all key:value pairs set in a dynamic column for some number of rows. They can select the column but probably won't know how to decode the name:value pairs stored in it.
Apparently Jakob has code for this. So I don't have much more to add for this thread.
-- Mark Callaghan mdcallag@gmail.com
-- Mark Callaghan mdcallag@gmail.com
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Jakob Lorberblatt -
MARK CALLAGHAN -
Michael Widenius -
Peter Laursen