query-builder

Name: query-builder
Author: totvs

108stars50forks

How to install query-builder?

totvs/engpro-advpl-tlpp-skills/query-builder

$npx -y skills add totvs/engpro-advpl-tlpp-skills --skill query-builder

Installs into the current project.

Summary

Build optimized and safe SQL queries for Protheus tables. Automatically includes mandatory filters (D_E_L_E_T_, branch), suggests appropriate indexes from SIX patterns, generates both Embedded SQL (preferring FWExecStatement) and Workarea (DBSelectArea/DBSeek) versions, and warns

Files · 1

View on GitHub

SKILL.md

1	# Protheus Query Builder
2
3	## Overview
4
5	Build correct, safe, and optimized SQL queries for Protheus ERP tables. Protheus has unique database conventions — mandatory soft-delete filters, multi-branch filtering, Hungarian notation for fields, data dictionary-driven schemas, and specific index patterns — that every query must respect. This skill generates queries that follow these conventions and helps choose between Workarea access and Embedded SQL.
6
7	## When to Use
8
9	Use this skill when:
10
11	- Writing SQL queries against Protheus tables (SA1, SD1, SF2, etc.)
12	- Building parameterized SQL queries (`FWExecStatement`, `TCSqlExec`) in AdvPL/TLPP
13	- Optimizing existing queries for Protheus-specific patterns
14	- Deciding between Workarea access and Embedded SQL
15	- Ensuring mandatory filters are not missing
16	- Generating safe queries that prevent SQL injection
17
18	---
19
20	## Bundled Reference Files
21
22	This skill uses progressive disclosure. The SKILL.md body covers conventions, decision logic, the checklist, and anti-patterns. Detailed code templates and cross-database reference tables are in the `references/` directory — read them on demand based on the scenario:
23
24	\| Reference File \| When to Read \| Content \|
25	\| --- \| --- \| --- \|
26	\| [references/query-patterns-and-examples.md](references/query-patterns-and-examples.md) \| Generating query code — Workarea, Embedded SQL, multi-table JOINs, TCSqlExec updates, counting, or reviewing SQL injection prevention examples \| Full code templates for all 5 query patterns, safe/unsafe FWExecStatement examples, LIKE clause parameterization \|
27	\| [references/cross-database-compatibility.md](references/cross-database-compatibility.md) \| Handling cross-database concerns — ChangeQuery(), TCGetDB(), DBAccess macros, or translating functions between MSSQL / PostgreSQL / Oracle \| ChangeQuery and TCGetDB code examples, DBAccess macros table, cross-database function equivalents (9 operations × 4 dialects) \|
28
29	> Also refer to [references/sonarqube-rules-reference.md](../references/sonarqube-rules-reference.md) for the complete SonarQube rules reference shared across skills.
30
31	---
32
33	## Protheus Database Conventions
34
35	### Table Naming
36
37	Protheus table aliases follow the pattern `XXN` where `XX` is the module prefix and `N` is a sequence:
38
39	\| Prefix \| Module \| Example Tables \|
40	\| ------ \| ----------------- \| ------------------------------------------------------------------------------------------------------------------------- \|
41	\| SA \| Customers/Vendors \| SA1 (Customers), SA2 (Vendors), SA3 (Salespeople) \|
42	\| SB \| Products \| SB1 (Products), SB2 (Stock Balances), SB5 (Supplements) \|
43	\| SC \| Purchasing \| SC1 (Purchase Requests), SC5 (Sales Orders Header), SC6 (Sales Orders Items), SC7 (Purchase Orders) \|
44	\| SD \| Documents \| SD1 (Incoming Invoices Items), SD2 (Outgoing Invoices Items), SD3 (Internal Movements) \|
45	\| SE \| Financial \| SE1 (Accounts Receivable), SE2 (Accounts Payable), SE5 (Cash Movements) \|
46	\| SF \| Invoices \| SF1 (Incoming Invoices Header), SF2 (Outgoing Invoices Header) \|
47	\| SX \| Data Dictionary \| SX1 (Parameters), SX2 (Tables), SX3 (Fields), SX5 (Lookup Tables), SX6 (System Parameters), SX7 (Triggers), SIX (Indexes) \|
48	\| ZZ \| Custom \| ZZ1-ZZZ (Custom tables created by customer, also Z01-Z99, ZA0-ZAZ, etc.) \|
49
50	### Physical Table Names
51
52	The physical table name in the database appends the company code:
53
54	\| Alias \| Physical Name \| Rule \|
55	\| ----- \| ------------- \| --------------------------------------------- \|
56	\| SA1 \| SA1010 \| Alias + Company ("01") + Branch padding ("0") \|
57	\| SD1 \| SD1010 \| Same pattern \|
58
59	Use `RetSQLName("SA1")` to get the correct physical table name dynamically.
60
61	### Field Naming
62
63	Fields follow the pattern `XX_FIELD` where `XX` matches the table a

People who liked this also viewed

6 picks

Type

query-builder

108stars50forks

View on GitHub

How to install query-builder?

totvs/engpro-advpl-tlpp-skills/query-builder

$npx -y skills add totvs/engpro-advpl-tlpp-skills --skill query-builder

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Protheus Query Builder
2
3	## Overview
4
5	Build correct, safe, and optimized SQL queries for Protheus ERP tables. Protheus has unique database conventions — mandatory soft-delete filters, multi-branch filtering, Hungarian notation for fields, data dictionary-driven schemas, and specific index patterns — that every query must respect. This skill generates queries that follow these conventions and helps choose between Workarea access and Embedded SQL.
6
7	## When to Use
8
9	Use this skill when:
10
11	- Writing SQL queries against Protheus tables (SA1, SD1, SF2, etc.)
12	- Building parameterized SQL queries (`FWExecStatement`, `TCSqlExec`) in AdvPL/TLPP
13	- Optimizing existing queries for Protheus-specific patterns
14	- Deciding between Workarea access and Embedded SQL
15	- Ensuring mandatory filters are not missing
16	- Generating safe queries that prevent SQL injection
17
18	---
19
20	## Bundled Reference Files
21
22	This skill uses progressive disclosure. The SKILL.md body covers conventions, decision logic, the checklist, and anti-patterns. Detailed code templates and cross-database reference tables are in the `references/` directory — read them on demand based on the scenario:
23
24	\| Reference File \| When to Read \| Content \|
25	\| --- \| --- \| --- \|
26	\| [references/query-patterns-and-examples.md](references/query-patterns-and-examples.md) \| Generating query code — Workarea, Embedded SQL, multi-table JOINs, TCSqlExec updates, counting, or reviewing SQL injection prevention examples \| Full code templates for all 5 query patterns, safe/unsafe FWExecStatement examples, LIKE clause parameterization \|
27	\| [references/cross-database-compatibility.md](references/cross-database-compatibility.md) \| Handling cross-database concerns — ChangeQuery(), TCGetDB(), DBAccess macros, or translating functions between MSSQL / PostgreSQL / Oracle \| ChangeQuery and TCGetDB code examples, DBAccess macros table, cross-database function equivalents (9 operations × 4 dialects) \|
28
29	> Also refer to [references/sonarqube-rules-reference.md](../references/sonarqube-rules-reference.md) for the complete SonarQube rules reference shared across skills.
30
31	---
32
33	## Protheus Database Conventions
34
35	### Table Naming
36
37	Protheus table aliases follow the pattern `XXN` where `XX` is the module prefix and `N` is a sequence:
38
39	\| Prefix \| Module \| Example Tables \|
40	\| ------ \| ----------------- \| ------------------------------------------------------------------------------------------------------------------------- \|
41	\| SA \| Customers/Vendors \| SA1 (Customers), SA2 (Vendors), SA3 (Salespeople) \|
42	\| SB \| Products \| SB1 (Products), SB2 (Stock Balances), SB5 (Supplements) \|
43	\| SC \| Purchasing \| SC1 (Purchase Requests), SC5 (Sales Orders Header), SC6 (Sales Orders Items), SC7 (Purchase Orders) \|
44	\| SD \| Documents \| SD1 (Incoming Invoices Items), SD2 (Outgoing Invoices Items), SD3 (Internal Movements) \|
45	\| SE \| Financial \| SE1 (Accounts Receivable), SE2 (Accounts Payable), SE5 (Cash Movements) \|
46	\| SF \| Invoices \| SF1 (Incoming Invoices Header), SF2 (Outgoing Invoices Header) \|
47	\| SX \| Data Dictionary \| SX1 (Parameters), SX2 (Tables), SX3 (Fields), SX5 (Lookup Tables), SX6 (System Parameters), SX7 (Triggers), SIX (Indexes) \|
48	\| ZZ \| Custom \| ZZ1-ZZZ (Custom tables created by customer, also Z01-Z99, ZA0-ZAZ, etc.) \|
49
50	### Physical Table Names
51
52	The physical table name in the database appends the company code:
53
54	\| Alias \| Physical Name \| Rule \|
55	\| ----- \| ------------- \| --------------------------------------------- \|
56	\| SA1 \| SA1010 \| Alias + Company ("01") + Branch padding ("0") \|
57	\| SD1 \| SD1010 \| Same pattern \|
58
59	Use `RetSQLName("SA1")` to get the correct physical table name dynamically.
60
61	### Field Naming
62
63	Fields follow the pattern `XX_FIELD` where `XX` matches the table a

People who liked this also viewed

6 picks

Type