search

OrchardCMS / OrchardCore

Orchard Core is an open-source modular and multi-tenant application framework built with ASP.NET Core, and a content management system (CMS) built on top of that framework.
https://orchardcore.net
BSD 3-Clause "New" or "Revised" License
7.23k stars 2.34k forks source link

Enable SQL queries specific to RDBMS #8625

Open tropcicstefan opened 3 years ago

tropcicstefan commented 3 years ago

Is your feature request related to a problem?

I have headless app and queries are pretty cool and easy solution for most of the app but current sql parser is somewhat limited. For instance you cannot combine 'distinct' and 'limit' because ordering isn't right after parsing. And I cannot do joins with lesser and greater signs. Plus why not provide developers with full ability of RDBMS.

Describe the solution you'd like

Maybe add checkbox in query create form to not parse query and visualize that a query is RDBMS specific in list of all queries.

Describe alternatives you've considered

Fix sql parser, but again why not give all functionalities of RDBMS.

Skrypt commented 3 years ago

I cannot say I don't agree on that one. I think it's a simple line to comment out if you don't want these Queries to be parsed. But, the intent is to make them work with any of our supported database. This is probably why we use a parser to create the equivalent "functions" for each of the RDBMS.

Though, it could be intentional that you just want to use a specific version of SQL Server and want to call some custom function that you made for it. So, yeah, this is limiting and probably can become counter productive at some point.

Please provide a PR 😉

deanmarcussen commented 3 years ago

Yes, and No.

The parsing is also required to provide the correct table prefixes for tenants, so if you just expose sql as sql, without any parsing, you open up a large can of worms.

Better to improve the parser with any missing functionality.

Plus also if you expose SQL directly, then I suspect this becomes possible. DROP ...

tropcicstefan commented 3 years ago

Ok, I hear your reasoning for tenancy. Hypothetically could that be solved with liquid? If not improving parser it is :)

On second thought, I could just make custom query source for my usecase right?

Skrypt commented 3 years ago

Or simply from SQL Server use a different account for each tenants. But that would not secure things for SQLite though. And yes, for custom query source, I guess one SQL query source without parsing could work.

hyzx86 commented 2 years ago

@tropcicstefan Take a look at this module https://github.com/EasyOC/EasyOC#%E6%95%B0%E6%8D%AE%E5%90%8C%E6%AD%A5

sebastienros commented 2 years ago

If the query you are trying to write can be done in all RDBMS then we can extend the grammar. If not then I think it's reasonable to add an option to not translate the query and use it as-is.

Obviously you can already write the query directly from code or custom modules, but I assume you are only interested in this feature in the context of the Queries feature.