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¶
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:
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.
SELECT id, username FROM users WHERE {{SEARCH}}
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
SELECT id, username, createdDate FROM users WHERE {{FILTER}}
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.
SELECT id, username FROM users ORDER BY {{ORDER}}
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.
SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}
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:
SELECT id, username FROM users
OFFSET {{PAGESTART}} ROWS FETCH NEXT {{PAGENUM}} ROWS ONLY
SELECT id, username FROM users LIMIT {{PAGENUM}} OFFSET {{PAGESTART}}
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
SELECT * FROM users
INSERT INTO users(username,password) VALUES ("{{data.username}}", "{{data.password}}")
UPDATE users SET username = "{{data.username}}" WHERE id = "{{data.id}}"
DELETE FROM users WHERE id = "{{data.id}}"
LET Portal still support multiple execution steps, so you can read to get more details.
SQL - Chart¶
Unlike SQL - Dynamic List, Chart is supporting two words are {{FILTER}}
and {{REAL_TIME}}
.
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
SELECT id, reportedDate FROM reports WHERE {{FILTER}}
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 `
SELECT id, reportedDate FROM reports WHERE {{REAL_TIME}}
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