Why custom query syntax, and not e.g. sqlite support?

Question

question

Thijs Damsma asked Feb 8, '17 Brian commented Sep 26, '17

Why custom query syntax, and not e.g. sqlite support?

As an experienced SQL user, but new to Flexsim, I was wondering why did Flexsim decide to build a custom implementation and not go for a well known and well documented solution as e.g. sqlite? Simple queries like below are already beyond the capabilities of the query parser, which makes it much more difficult to me how to apply it.

query("labelName - max(labelName) FROM List")

Software Version:

FlexSim 17.0.2

query

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2017-02-08T17:51:49Z

anthony.johnson answered Feb 8, '17 Brian commented Sep 26, '17

The following are some of the reasons we do not use SQLite backend for our query functionality.

FlexSim's memory/class structure does not work with standard SQLite formats, so we would have had to implement customized data sources for the SQLite backend.
FlexSim supports most FlexScript commands so that you can put FlexScript expressions into your query, and we hope to extend that to using FlexScript dot syntax expressions in the future. I assume this would have been very difficult to do using the SQLite engine, especially with our intention to add FlexScript dot syntax, which will break with standard sql syntax.
The query command also supports lambda-like expressions that can be passed as additional parameters, using the $ syntax. This allows users to query data that is not even structured as table data, as if it were table data. I don't know if this would have been possible using the SQLite engine.
When pulling from lists, users often implement queries that do not require certain sql clauses. Usually a list pull will only have a WHERE and/or ORDER BY clause, without a SELECT or FROM clause. This would likely be invalid sql causing an error if SQLite were the sql backend.

Additionally, as far as I can tell, the query you provided as an example is not a valid query, and even if it were, I don't believe it does what you think it does. It does not include the SELECT clause, and if it did, the MAX() function is an aggregation function that will aggregate everything into one row in the result table. As an example, if you got to w3schools and use the Try It Yourself button, then enter the query:

SELECT Price - MAX(Price) AS HighestPrice FROM Products;

The result is a single row that happens to be the price of the last entry in the table minus the maximum of all prices, which is 0. Unless SQLite works differently than other sql engines, I don't think that is what you want.

I believe the result you likely would want is the result of an inner join with a nested query:

SELECT List.labelName - Max.maxLabelName FROM List INNER JOIN (SELECT MAX(labelName) AS maxLabelName FROM List) Max

We plan on adding support for nested queries in the next release of FlexSim, in which case you will be able do this.

We will continue to improve our sql engine to bring it more inline with how other sql parsers work, and to add our own "special sauce". Perhaps we could have been able to implement SQLite as the backend for FlexSim's query() command. However, given the requirements that we had, we decided to implement it ourselves, and I do not regret that decision. Had we implemented a SQLite backend, we might have spent as much time fighting with their implementation to get what we need as spending time adding new features.

· 4

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Thijs Damsma commented · Feb 09, 2017 at 08:40 AM

@anthony.johnson, thank you for your elaborate reply. You are completely right about my fake example not being valid SQL. I guess I as a bit frustrated trying to decipher which part of the SQL syntax is supported, and how. Let me share some of my findings after my first few days with Flexsim, to illustrate this:

I often just get error messages like below which are fairly unhelpful:

exception: Invalid SqlQuery object

I wanted to query a partitioned list, but couldn't find any examples/documentation on this. Finally figured out to do this with dot syntax, if partition_by is a string

query("SELECT value FROM List1.1");

If partition_by is a number, then you must do this

query("SELECT value FROM List1.[1]");

You note the Flexsim dot syntax is not supported, however this query works as I expected (with the Script console, so=a token on a list, partitioned by token.stack)

query("SELECT value FROM List1.$1", so.stack);

This returns nothing however (in custom code):

query("SELECT value FROM List1.$1", token.stack)

But this does:

query("SELECT value FROM List1.$1", getlabel(token, "stack"))

And then I am surprised this query returns anything (using Script console)

query("SELECT value FROM List1.$1 WHERE non_existing_field = $2", so.stack, so.non_existing_field);

Maybe it's just me, but I find that the combination of edge cases, limited documentation, few examples, custom sql-like syntax, custom scripting language really of limits my productivity.

I feel that building on existing tools could provide one with a well tested, battle proven implementation in combination with extensive documentation, and thousands of answered Stack Overflow questions for every edge case. But as you explained, this might not fit the Flexsim use case

0 ·

anthony.johnson ♦♦ Thijs Damsma commented · Feb 09, 2017 at 05:05 PM

@Thijs Damsma, thanks for this feedback. Your complaints are noted, but none of the issues you cite would be made better by making SQLite the backend for FlexSim. The ability to query a specific list partition by using dot syntax in the FROM clause is itself special to FlexSim. I don't think you would be benefited by looking up SQLite's thorough documentation on this issue, because there would be no documentation on it. In SQLite, specifying a dot in the table specifier in a FROM clause would likely render a syntax error. Had we limited ourselves to the more confined functionality that SQLite provides, we likely would not have been able to let people query a specific partition of a list. In my opinion, I'd rather give people the ability to query specific partitions, and deal with the documentation/bug issues that come from that, than have the answer be just, sorry, you can't do that because SQLite doesn't support it.

I should say that, where there are standard SQL mechanisms for a given feature, we have always tried to just use what's already there, and not reinvent the wheel. For those, we would hope that you can use standard documentation like SQLite's to figure out how to write your query. It is for the exception cases where we have to diverge from the standard SQL model, e.g. when you want certain values in the query to be dynamically determined using FlexScript expressions, or in this case where you want to query a specific partition of a list. For those cases, we just need to have good documentation so people can figure out how to do it.

Specific to your issues, all of the examples you cited should technically work, so really, it's not as much a documentation issue as it is just that it needs to work right (although the nebulous "Invalid SqlQuery object" error could also be improved). Please be patient with us as the ability to query list partitions is a relatively new feature. Obviously, it still has a few rough edges. We'll try to get this fixed soon.

Thanks for your feedback.

3 ·

Brian commented · May 26, 2017 at 04:04 PM

Anthony,
is it possible to pull multiple values for a query and if so, do you have any script examples?

I've done a lot of scripting in VB, Python and jscript and I don't see any good examples online for flexsim.
Here are a couple examples I've used in the past.
mydata = runQuery("SELECT * WHERE ID = 1")
for myrow in newdata:
prodName = str(myrow[0])
prodDescription = str(myrow[1])

Set RS = AD.Execute(sql_query)
Do While Not RS.EOF
for i = 0 to RS.rowCount
columnName=RS(i).Name
value=RS(i)
next i
RS.MoveNext
Loop

0 ·

Ben Wilson ♦♦ Brian commented · May 26, 2017 at 07:40 PM

Hi @Brian F2,

Please repost your question as a new question. That way it can have an appropriate title related to your actual question, and it will not be marked as answered.

Piggybacking on an old, answered question will probably not get you the visibility you need for a good answer. Many people view this forum through a filter which shows only unanswered questions, and so adding your new question to this that already has an accepted answer would prevent your addition from even showing up on their feeds.

Please see the community best practices for more info. Points 14 and 15 are directly related.

We understand that Q&A sites are new for many users, and a different format than a normal conversational forum, but the best practices will help us keep the site more organized, searchable, useful going forward.

0 ·

question