Monday, September 28, 2015

Wish there is another String DataType called LIST but there is not

I believe the future of SQL is to take a lot of primitives that are Computer Science fundamentals and add them as datatypes to expand on the allowed columns today. The idea is of the ilk of a merging of noSQL and SQL  for solving problems to make it easier for a new person to develop.

For instance, what would be awesome is a LIST type, where the list contains a distinct number of string items mapped to a bit, much like SET yet you don't need to predefine all the items in a set.

Here is a good example as how I would use a list type:

Imagine you need permissions on a per row basis. Some rows are public, some are private, some are viewable by a small set of people. (Less than 64).

Let's take the example of Finding all rows that are public or are viewable by only me.

When creating a row

INSERT INTO resource_permissions (resource_id, perm_bit, list_dt) VALUES(1, 2, "dathan, sam, fred")

perm_bit is 0 private, 1 = public, 2 public to a list of people

When selecting rows that I "dathan" can see

SELECT resource_id FROM resource_permissions WHERE perm_bit = 1 UNION SELECT resource_id FROM resource_permissiongs WHERE perm_bit = 2 AND FIND_IN_LIST(list_dt, "dathan");

What the above statement says is give me all the public resource_ids and resource_ids that I "dathan" can see.

Right now I can't do this, I have to use a medium_blob and a LIKE

SELECT resource_id FROM resource_permissions WHERE perm_bit = 1 UNION SELECT resource_id FROM resource_permissions WHERE perm_bit = 2 AND list_dt LIKE "%:dathan:%"

As you can see I'm able to simulate the desired behavior but I can't use an index, I don't want to use a FULLTEXT_INDEX due to overhead and other issues that out of scope for this post. Nor do I want to manage UDF's or Stored procedures. The last two are not desirable yet can also simulate the behavior I am looking for.

Some primitives from REDIS or other noSQL solutions would be awesome additions for SQL as a hole IMHO.

My two cents.

Also in 5.7 maybe the JSON Column Type might be of some use.


Federico Razzoli said...

You want arrays, like in PostgreSQL. My guess is that implementing that feature in MySQL would be hard. Not because adding a datatype is hard (well, I don't know) but because MySQL doesn't have GIN indexes. And I don't think this is a simple feature - it is basically a many-to-many relation between index entries and table rows.

MariaDB has dynamic columns, that can be used as lists (add each new item as column_name=1).

But I wouldn't do that. Relational databases are perfectly ok for a task like this. Just add 2 tables and use JOINs - you will use indexes, and performance will be ok.

Bytesmiths said...

MariaDB has user-defined columns that can behave like arrays.

akshay jain said...

Found some security loopholes in your shots application. Where can i report them?

Dathan Pattishall said...

we have a bounty system I can add you to my hackerone group

If your security issue is about not having a capta on SMS sends, or "incorrect headers" we know. If it something with the application itself or a problem in the API please send us the report.

akshay jain said...

add me its something different. akshaythe at gmail dot com