The current version is #ident "@(#)$Format:LocalFoodAI_lanfr144:SKILL.md:%an:%ae:%ad:%cn:%ce:%cd:%H:%D:%N$"

name: sql-optimizer

description: Optimizes and secures SQL for MySQL, Oracle, and PostgreSQL, enforcing strict DBA standards.

SQL Optimizer & DBA Skill

When reviewing, optimizing, or generating SQL code (MySQL, Oracle, PostgreSQL), enforce these strict database guidelines:

1. Performance & Concurrency

Locking: Avoid row locking issues and design queries to prevent deadlocks.
Indexing: Suggest optimal indexes, including B-Tree, full-text, spatial, or composite indexes where appropriate.
Testing: When testing SQL in non-production environments, utilize all available database optimizer tools (e.g., EXPLAIN PLAN, tkprof).

2. Security & Access Control

No Hardcoded Users: NEVER hardcode usernames in scripts.
Proxy/Restricted Access: Ensure the program accesses objects through proxy users or restricted views (objects must be owned by one or more dedicated owner schemas, not the application user).
Audit Policies: Recommend the setup of appropriate audit policies for sensitive tables/actions (Do not write the scripts to set them up, only advise).
Bind Variables: SQL statements MUST use bind variables to pass and receive values. No dynamic concatenation of user inputs.
Grants & Synonyms: Whenever new objects are created or accessed, you MUST provide all the necessary GRANT statements and SYNONYM creations required for the application to function securely.

3. Transaction Management
No Auto-Commit: Disable auto-commit. Explicitly manage transactions with COMMIT and ROLLBACK blocks.

4. Syntax & DDL Standards

Quoted Identifiers: All object and column names must be double-quoted (") or back-quoted (`) to avoid collisions with reserved words. Warn if a name matches a V$RESERVED_WORDS in Oracle.
Reserved Words Warning: Issue a warning if an object or column name matches an Oracle reserved word (from V$RESERVED_WORDS).
DDL Changes: All Data Definition Language (DDL) changes must be generated using DBMS_METADATA and DBMS_METADATA_DIFF to calculate and apply exact differences.
Exception Management: Always implement robust exception handling (e.g., EXCEPTION blocks in PL/SQL) to capture, manage, and log database errors gracefully. Do not allow silent failures.

5. Mandatory File Header

Header Tag Requirement: Every source code, scripting, config, or text file (including ignored scratch files) must include the exact identity format at the top of the file:
```
i d e n t   " @ ( # ) $ F o r m a t : { p r o j e c t _ n a m e } : { f i l e _ n a m e } : % a n : % a e : % a d : % c n : % c e : % c d : % H : % D : % N $ "
```
Note: In the template above, the character sequence has been intentionally formatted with spaces between each character (representing the sed transformation s/./& /g). This prevents Git's clean/smudge filters from matching, interpreting, and modifying this rule documentation file itself. For tracked files, the Git smudge filter (ident-dynamic) will automatically expand the placeholder variables with real Git commit and author/committer data during checkouts. Untracked or ignored scratch files must still physically carry this header comment as a repository consistency requirement. The comment syntax must match the file's language (e.g., # for Python/Shell/YAML/Markdown/Dockerfiles, -- for SQL, :: for Batch). For tracked files, the git smudge filter expands this dynamically. Ignore-listed/scratch files must carry the comment statically for structure. Adapt the comment syntax (e.g., "//", "#", "--", "`", "!", "REM", "/* */") to the specific language. Exception: For executable scripts requiring a shebang (e.g., #!/bin/bashor#!/usr/bin/env python), the shebang must remain on the first line, and the Identity Tag MUST be placed on the second line.

To initialize a new file, place the clean version at the top of your file (legible examples are listed below in spaced-out format to prevent active smudge filter matching):

For Python/Shell files: # i d e n t " @ ( # ) $ F o r m a t : G i t p r o j e c t n a m e : f i l e n a m e : % a n : % a e : % a d : % c n : % c e : % c d : % H : % D : % N $ "
For SQL files: - - i d e n t " @ ( # ) $ F o r m a t : G i t p r o j e c t n a m e : f i l e n a m e : % a n : % a e : % a d : % c n : % c e : % c d : % H : % D : % N $ "
For Batch files: : : i d e n t " @ ( # ) $ F o r m a t : G i t p r o j e c t n a m e : f i l e n a m e : % a n : % a e : % a d : % c n : % c e : % c d : % H : % D : % N $ "
For Markdown/YAML/Dockerfiles/XML: # i d e n t " @ ( # ) $ F o r m a t : G i t p r o j e c t n a m e : f i l e n a m e : % a n : % a e : % a d : % c n : % c e : % c d : % H : % D : % N $ "

SKILL.md 4.8 KB Lịch sử Raw