# KSQL Query Guidelines

> KSQL query constraints, security rules, and best practices for Kingdee Cosmic data operations.

---

## Scope

These guidelines apply to **Cosmic/BOS metadata-based** KSQL/SQL data fix scenarios (Cangqiong, Xinghan, Flagship). They do NOT apply to Enterprise C#, Python plugins, or non-Cosmic table structures.

---

## Pre-Flight Checks (Mandatory)

Before generating any KSQL:

1. Confirm the product belongs to the **Cosmic** ecosystem
2. Verify entity/form ID, table name, field DB names, field types, and enum/dropdown values via **Agent-guided readonly SQL + LLM parse** (`T_META_OBJECTTYPE.FKERNELXML` / `t_meta_entitydesign.fdata`; see `metadata-db-query.md`). Ask the user for connection info if missing.
3. Verify `dbKey` and `dbName` for cross-database routing
4. If any table, field, enum, or DB routing fact is unverified: **STOP** and list pending items
5. If `dbName` differs across tables: ask user for cross-database strategy before generating

---

## Hard Rules

| Rule | Description |
|------|-------------|
| Verify-before-update | Every `UPDATE` must have a corresponding `SELECT` with the same filter conditions |
| Full backup first | Use `SELECT * INTO` for full table backup (no WHERE), table name: `bak_<table>_<yyyyMMddHHmm>` |
| Time consistency | Backup table name and output file name must use the same timestamp |
| No `SELECT *` | Prohibited in query/verify statements (backup statement is the sole exception) |
| No unconditional UPDATE/DELETE | Every `UPDATE` / `DELETE` must have a `WHERE` clause |
| Postgres syntax | Default to PostgreSQL syntax unless user explicitly specifies a different dialect |
| Readability preference | Prefer `IN` (value list or subquery) over `EXISTS` for semi-join conditions. Only use `EXISTS` when `IN` would change semantics, and explain why |
| No destructive DDL | No DROP, RENAME, or other destructive structural changes unless user explicitly requests and acknowledges risk |
| Parameterized queries | Use KSQL parameters — never string concatenation for filter conditions |
| Pagination | Use pagination or streaming `DataSet` for large datasets — never load all into memory |

---

## SQL Output Structure

Generated KSQL files must follow this structure:

```
-- ============================================
-- Purpose: <what this script does>
-- Product: <product name>
-- Metadata source: <verification source>
-- Generated: <timestamp>
-- ============================================

-- 1. Impact scope query
SELECT ... WHERE ...

-- 2. Full table backup
SELECT * INTO bak_<table>_<yyyyMMddHHmm> FROM <table>

-- 3. Pre-update confirmation query
SELECT ... WHERE ...

-- 4. UPDATE statement
UPDATE <table> SET ... WHERE ...

-- 5. Post-update verification query
SELECT ... WHERE ...

-- 6. Rollback statement
UPDATE <table> SET ... WHERE ...

-- 7. Field mapping summary (from metadata verification)
```

---

## Query Patterns

| Pattern | Rule |
|---------|------|
| Existence check | Primary key is typically `fid`, entry table key is `fentryid` |
| Entry table join | Entry tables typically link via `fid` to the main table |
| NULL check | Use `IS NULL` / `IS NOT NULL` |
| Enum/dropdown values | Always verify actual values via readonly SQL + LLM parse (Ext column) before writing code |
| Multi-field verification | Combine into a single `--fuzzy` call (3+ keywords auto-upgrades to detail mode) |

---

## Security

- Verify metadata before every operation
- No DROP, RENAME, or destructive structural changes without explicit user consent
- Unconditional UPDATE/DELETE is never allowed
- Impact scope, pre-check, update, verify, and rollback queries must use consistent ranges