DESCRIBE function

[ummjackson]

Hey there,

Coming from a MySQL / HiveQL background, I'm really missing having a DESCRIBE function (or equivalent) in AlaSQL. (see: http://dev.mysql.com/doc/refman/5.7/en/describe.html / https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Describe)

Two questions: a) is this on the roadmap and b) if not, can you provide some pointers for how I'd go about implementing it myself?

Thanks!

[mathiasrw]

Hi @ummjackson

Great to her about your interest in making things better.

At the moment there is no roadmap - so its just what people feel like to contribute with when they find time.

On http://dev.mysql.com/doc/refman/5.7/en/explain.html I see that DESCRIBE is a shortcut for SHOW COLUMNS. AlaSQL supports SHOW COLUMNS but will only provide information about field and type under the names columnid and Type (see http://jsfiddle.net/v1e4ep5m/). Do you feel the names be changes to reflect the MySQL way of naming things?

I have a feeling that creating an alias for "SHOW COLUMNS" + extend the existing functionality would be the best approach.

To create an alias you need to expand the https://github.com/agershun/alasql/blob/develop/src/alasqlparser.jison file with the definition of the word DESCRIBE at the top and extend https://github.com/agershun/alasql/blob/develop/src/alasqlparser.jison#L2212 to handle the new word. To compile the new parser do a npm run build:jison before testing the new alasql file in dist/.

To expand the data given: have a look at https://github.com/agershun/alasql/blob/develop/src/78show.js#L70 - I have a feeling most of the information we are not providing at the moment is located in alasql.tables.TABLENAME

Are you familiar with git ? if not have a look at https://github.com/agershun/alasql/blob/develop/CONTRIBUTING.md

Let me know how you go.

[ummjackson]

Thanks for these pointers @mathiasrw - this is super helpful and I think an alias might be the right way to go.

I seem to be having issues getting the SHOW COLUMNS function to work properly, however. To give you some context, here's my project where I attach an INDEXEDDB database on load: https://github.com/ummjackson/crunch/blob/master/public/index.html#L111 - but if I try to run SHOW COLUMNS FROM xxyyzz; against any of the tables attached it won't return anything. Any ideas?

Here is where the CSV import actually happens: https://github.com/ummjackson/crunch/blob/master/public/views/editor.html#L87

My project is: https://ummjackson.github.io/crunch/#editor and you can test it for yourself by uploading a CSV and then trying a simple alasql('show columns from mycsv;') in the console (or you can test it from the SQL editor itself)... Nothing happens.

My hunch is that this has something to do with me using INDEXEDDB? Any help appreciated, because I think simply aliasing SHOW COLUMNS once I can get it to work is a perfect solution :)

ps. please excuse the hacky code in my project, it's a WIP!

[mathiasrw]

I see.

Hmm. For CSV we do look into what is in the data.

Have a look at https://github.com/agershun/alasql/blob/2ee7f79c28a89a6342badee875376232f9d2aa5d/src/40select.js#L404-L421

and imagine that res is set to alasql.tables.TABLENAME.data

I have no idea why its not working with indexeddb. If you find out please let me know...

[ummjackson]

I've been testing it over @ https://jsfiddle.net/8233wrb1/3/ and you can do the same by uploading a CSV to the project I linked above. It seems that immediately after doing a CREATE TABLE or IMPORT from CSV that SHOW COLUMNS works for INDEXEDDB-stored databases... the problem is when you ATTACH a previously created / existing INDEXEDDB database - you can run SELECT queries against it, but the SHOW COLUMNS FROM does not work. Additionally, calling alasql.tables.TABLENAME returns an empty array.

Any ideas? I have no idea how SELECT queries are working against an INDEXEDDB table when the alasql.tables.TABLENAME returns nothing. Could this be a bug with the INDEXEDDB persistence implementation?

[ummjackson]

@mathiasrw - I noticed this code is commented out when ATTACHing INDEXEDDB databases: https://github.com/agershun/alasql/blob/2ee7f79c28a89a6342badee875376232f9d2aa5d/src/91indexeddb.js#L211

This looks like it loops through and populates the tables + data upon ATTACH and might explain why things are breaking. Any clues as to why this was commented out? Is this a known bug where the table schema isn't reloaded into memory upon ATTACH of an existing database?

[mathiasrw]

Interesting !

I can see the code was comitted december 2014

https://github.com/agershun/alasql/blame/2ee7f79c28a89a6342badee875376232f9d2aa5d/src/91indexeddb.js#L211

I feel very confident that the code was copied in so it could be implemented + testet later, but never got to that stage.

If you feel inspired, please look into if commenting them in gives any interesting results.