SQL
The documentation applies to:
v0.8.0
Preparation knowledge¶
We recommend you to read carefully some sections below before you read more steps on this document:
SQL¶
SQL is very popular and easy to learn. However, we point out that is very difficult to add some customs on one SQL command such as SELECT or INSERT. Unlike MongoDB, SQL is flexible when compiling a lot of chaining SELECT (aka sub query). Therefore, we decide to separate some customs based on use-cases.
Interpolation {{}}¶
Same as MongoDB, we use {{data.id}} to translate a value. However, SQL Injection is very easy to cheat so that we always apply SQLParameter instead of concat string.
One important thing that you need to add |{type} in your interpolation for indicating with database type should be bound. There are some primary types of each database:
| Declaration | C# type | MySQL | PostgreSQL | SQL Server |
|---|---|---|---|---|
{{data.id}} |
string | LongText | Text | NVarChar |
{{data.date|date}} |
DateTime | Datetime | Date | DateTime |
{{data.allow|bool}} |
bool | Bit | Boolean | Bit |
{{data.counter|int}} |
int | Int32 | Integer | Int |
{{data.tick|long}} |
long | Int64 | Bigint | BigInt |
{{data.timestamp|timespan}} |
Timespan | Int64 | Bigint | BigInt |
{{data.money|float}} |
float | Float | Double | Float |
{{data.money|double}} |
double | Double | Double | Float |
{{data.money|decimal}} |
decimal | Decimal | Money | Decimal |
Also, it is applied for Built-in methods.
SQL - Dynamic List¶
``` sql tab="Simplest Query" SELECT * FROM table_name
By default, you don't want to add some custom words as **Simplest Query** above because Dynamic List will combine automatically Filters and Pagination. However, in case you want to have a complexity query such as `UNION` or multiple `SELECT`, LET Portal provides some custom words to do flexible.
On Dynamic List, we use this common `SELECT` with some customs keywords below:
``` sql tab="Full custom words"
SELECT * FROM "table_name" WHERE "your_condition"
AND {{SEARCH}} AND {{FILTER}}
ORDER BY {{ORDER}}
LIMIT {{PAGENUM}}
OFFSET {{PAGESTART}}
Interpolation {{ }}
Please remember that you want to bind some data into SQL, you should use {{ }}, also apply for some custom words above
Now back to our customs words for Dynamic List, we will discuss one by one for letting you know what exactly data will be replaced by custom word
Multiple words
You can put many custom words whenever you want. However, you ensure to put correct place.
{{SEARCH}}¶
This word will generate a condition of searching keyword on Dynamic List. For example: you want to find an username on users table, so you enter a keyword admin on Dynamic List.
Default
Default tab means this is a query you need to input on Dynamic List Builder page.
``` sql tab="Default" SELECT id, username FROM users WHERE {{SEARCH}}
``` sql tab="Translated"
SELECT id, username FROM users WHERE username LIKE '%@keyword%'
/* In this, @keyword = 'admin'*/
{{FILTER}}¶
This word will generate a combination of Advanced Filter on Dynamic List. For example: you want to find any user has username contains admin and createdDate must be greater than 2020-02-02
``` sql tab="Default" SELECT id, username, createdDate FROM users WHERE {{FILTER}}
``` sql tab="Translated"
SELECT id, username, createdDate FROM users WHERE username LIKE '%@filter1%' AND createdDate >= @filter2
/* In this, @filter1 = 'admin' and @filter2 = '2020-02-02' */
{{ORDER}}¶
This word will generate a sorting string on Dynamic List. For example: you want to sort username ascending on Dynamic List.
``` sql tab="Default" SELECT id, username FROM users ORDER BY {{ORDER}}
``` sql tab="Translated"
SELECT id, username FROM users ORDER BY username ASC
{{PAGENUM}} and {{PAGESTART}}¶
Two words will replace two numbers number per page and skip rows number. For example: you want to take top 10 rows on page 2 on Dynamic List.
``` sql tab="Default" SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}
``` sql tab="Translated"
SELECT id, username FROM users LIMIT 10 OFFSET 10
/* OFFSET 10 is skipping first 10 rows on result*/
Due to different pagination implementation of each database, we just provide two words for helping you to add a correct syntax. There are pagination implementation of three databases:
``` sql tab="SQL Server" SELECT id, username FROM users OFFSET {{PAGESTART}} ROWS FETCH NEXT {{PAGENUM}} ROWS ONLY
``` sql tab="MySQL"
SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}
``` sql tab="PostgreSQL" SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}
# SQL - Action Button
For using SQL on Action Button (both Standard, Dynamic List and Page), you can run `SQL` for CRUD data
``` sql tab="Query"
SELECT * FROM users
``` sql tab="Insert" INSERT INTO users(username,password) VALUES ("{{data.username}}", "{{data.password}}")
``` sql tab="Update"
UPDATE users SET username = "{{data.username}}" WHERE id = "{{data.id}}"
``` sql tab="Delete" DELETE FROM users WHERE id = "{{data.id}}"
LET Portal still support [multiple execution steps](multiple-execution-steps.md), so you can read to get more details.
# SQL - Chart
Unlike SQL - Dynamic List, Chart is supporting two words are `{{FILTER}}` and `{{REAL_TIME}}`.
!!! warning "Real-time chart"
Unlike MongoDB, `{{REAL_TIME}}` is a mandatory word for real-time chart
## {{FILTER}}
Like as SQL - Dynamic List, this word is used to generate a combination of chart filters.
For example: you want to find a report has `reportedDate` around `NOW` and `NOW - 30 days`
``` sql tab="Default"
SELECT id, reportedDate FROM reports WHERE {{FILTER}}
``` sql tab="Translated" SELECT id, reportedDate FROM reports WHERE reportedDate >= @filter1 && reportedDate < @filter2
/* In this, @filter1 = 'NOW - 30days' and @filter2 = 'NOW' */
## {{REAL_TIME}}
This is a mandatory keyword when you set up a real-time Chart. You need to ensure `comparerealtimefield` is a date column.
For example: you want to compare `reportedDate` between `
``` sql tab="Default"
SELECT id, reportedDate FROM reports WHERE {{REAL_TIME}}
sql tab="Translated"
SELECT id, reportedDate FROM reports
WHERE reportedDate >= '2020-02-02 00:00:00' AND reportedDate < '2020-02-02 00:00:30'
For a reason why a duration is 30 seconds, let check it