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.







5 comments:

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 https://hackerone.com/shots

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