$ trdsql -version
trdsql version v1.2.3

$ trdsql -help
trdsql - Execute SQL queries on CSV, LTSV, JSON, YAML and TBLN.

Usage
	trdsql [OPTIONS] [SQL(SELECT...)]

Options:
  -A string           analyze the file but only suggest SQL.
  -a string           analyze the file and suggest SQL.
  -config string      configuration file location.
  -db string          specify db name of the setting.
  -dblist             display db information.
  -debug              debug print.
  -driver string      database driver.  [ mysql | postgres | sqlite3 | sqlite3_ext ]
  -dsn string         database driver specific data source name.
  -help               display usage information.
  -q string           read query from the specified file.
  -t string           read table name from the specified file.
  -version            display version information.

Input Formats:
  -icsv               CSV format for input.
  -ig                 guess format from extension. (default "true")
  -ijson              JSON format for input.
  -iltsv              LTSV format for input.
  -itbln              TBLN format for input.
  -itext              text format for input.
  -iwidth             width specification format for input.
  -iyaml              YAML format for input.

Input options:
  -id string          field delimiter for input. (default ",")
  -ih                 the first line is interpreted as column names(CSV only).
  -ijq string         jq expression string for input(JSON/JSONL only).
  -ilr int            limited number of rows to read. (default 0)
  -inull value        value(string) to convert to null on input.
  -inum               add row number column.
  -ir int             number of rows to preread. (default 1)
  -is int             skip header row. (default 0)

Output Formats:
  -oat                ASCII Table format for output.
  -ocsv               CSV format for output.
  -ojson              JSON format for output.
  -ojsonl             JSON lines format for output.
  -oltsv              LTSV format for output.
  -omd                Markdown format for output.
  -oraw               Raw format for output.
  -otbln              TBLN format for input.
  -otsv               TSV format for output.
  -ovf                Vertical format for output.
  -oyaml              YAML format for output.

Output options:
  -oaq                enclose all fields in quotes for output.
  -ocrlf              use CRLF for output. End each output line with '\r\n' instead of '\n'.
  -od string          field delimiter for output. (default ",")
  -oh                 output column name as header.
  -onowrap            do not wrap long lines(at/md only).
  -onull value        value(string) to convert from null on output.
  -oq string          quote character for output. (default "\"")
  -out string         output file name.
  -out-without-guess  output without guessing (when using -out).
  -oz string          output compression format. [ gz | bz2 | zstd | lz4 | xz ]

Examples:
  $ trdsql "SELECT c1,c2 FROM test.csv"
  $ trdsql -oltsv "SELECT c1,c2 FROM test.json::.items"
  $ cat test.csv | trdsql -icsv -oltsv "SELECT c1,c2 FROM -"

# 查询已有 SQLite 文件（表 fruits 由 sqlite3 CLI 预建）
$ trdsql -driver sqlite3 -dsn testdata/fruits.db "SELECT name, price FROM fruits WHERE price > 200 ORDER BY price DESC"
Orange,380
Apple,250

# 把 CSV 导出为全新 SQLite 库（建表导入）
$ rm -f testdata/sales.db
$ trdsql -ih -driver sqlite3 -dsn testdata/sales.db "CREATE TABLE sales AS SELECT * FROM testdata/sales.csv"

$ sqlite3 testdata/sales.db ".tables"; sqlite3 testdata/sales.db "SELECT COUNT(*) FROM sales;"
sales
8

$ trdsql -driver postgres -dsn "host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=disable" "SELECT count(*) FROM testdata/sales.csv"
9

$ trdsql -driver postgres -dsn "host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=disable" "CREATE TABLE fruits (id, name) AS SELECT c1::int, c2 FROM testdata/test.csv"

$ psql "host=db-server user=root password=q1w2e3r4 dbname=trdsql" -c "SELECT * FROM fruits;"
 id |  name
----+--------
  1 | Orange
  2 | Melon
  3 | Apple
(3 rows)

# 先建带类型的表，再用 INSERT INTO 导入（price 落 int 列；-db pdb 即 5.2 配置的 postgres 连接）
$ psql "host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=disable" -c "CREATE TABLE sales(id int, name text, price int, city text);"
CREATE TABLE
$ trdsql -db pdb -ih "INSERT INTO sales SELECT id::int, name, price::int, city FROM testdata/sales.csv"
$ psql "host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=disable" -c "SELECT city, sum(price) FROM sales GROUP BY city ORDER BY 2 DESC;"
   city    | sum
-----------+------
 Guangzhou | 1080
 Shanghai  | 1010
 Beijing   |  780
(3 rows)

{
  "db": "pdb",
  "database": {
    "sdb": {
      "driver": "sqlite3",
      "dsn": "/opt/work/testdata/fruits.db"
    },
    "pdb": {
      "driver": "postgres",
      "dsn": "host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=disable"
    },
    "ldb": {
      "driver": "sqlite3",
      "dsn": "/opt/work/testdata/sales.db"
    }
  }
}

$ trdsql -dblist
pdb:postgres
ldb:sqlite3
sdb:sqlite3

$ trdsql -db pdb "SELECT name FROM fruits ORDER BY id"
Orange
Melon
Apple

$ trdsql -db sdb "SELECT name, price FROM fruits WHERE price > 200"
Orange,380
Apple,250

$ trdsql -db ldb "SELECT city, SUM(CAST(price AS INT)) AS total FROM sales GROUP BY city"
Beijing,780
Guangzhou,1080
Shanghai,1010

$ cp ~/.config/trdsql/config.json ./myconf.json
$ trdsql -config myconf.json -db sdb "SELECT * FROM fruits"
1,Orange,380
2,Melon,120
3,Apple,250

$ trdsql -debug -db pdb "SELECT count(*) FROM testdata/sales.csv" 2>&1 | head -8
2026/11/03 04:29:28 config found: /root/.config/trdsql/config.json
2026/11/03 04:29:28  [driver: sdb:sqlite3:/opt/work/testdata/fruits.db]
2026/11/03 04:29:28 >[driver: pdb:postgres:host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=disable]
2026/11/03 04:29:28  [driver: ldb:sqlite3:/opt/work/testdata/sales.db]
2026/11/03 04:29:28 driver: postgres, dsn: host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=disable
2026/11/03 04:29:28 [SELECT][ ][count][(][*][)][ ][FROM][ ][testdata/sales.csv]
2026/11/03 04:29:28 Guess file type as CSV: [testdata/sales.csv]
2026/11/03 04:29:28 Column Names: [c1,c2,c3,c4]

# -ih：首行作列名（对比 5.1 的 count=9 → 8）
$ trdsql -ih "SELECT city, count(*) AS cnt, sum(price) AS total FROM testdata/sales.csv GROUP BY city ORDER BY total DESC"
Guangzhou,2,1080
Shanghai,3,1010
Beijing,3,780

# -ilr 3：只读前 3 行数据
$ trdsql -ih -ilr 3 "SELECT * FROM testdata/sales.csv"
1,Orange,380,Beijing
2,Melon,120,Shanghai
3,Apple,250,Beijing

# -is 1：跳过第 1 行（表头行按数据跳掉，效果近似 -ih 但不建列名）
$ trdsql -is 1 "SELECT * FROM testdata/sales.csv" | head -3
1,Orange,380,Beijing
2,Melon,120,Shanghai
3,Apple,250,Beijing

# -id ":"：自定义输入分隔符
$ printf "a:b\n1:2\n3:4\n" | trdsql -id ":" -ih "SELECT * FROM -"
1,2
3,4

# -inum：追加行号列
$ trdsql -inum "SELECT * FROM testdata/test.csv LIMIT 2"
1,1,Orange
2,2,Melon

# -ijq：JSON 层级取数
$ trdsql -ijson -ijq ".items" "SELECT * FROM -" < testdata/test.json
1,Orange,380
2,Melon,120
3,Apple,250

# -inull/-onull：NULL 语义
$ echo "1,,v" | trdsql -inull "" -onull "(NULL)" "SELECT * FROM -"
1,(NULL),v

# -out 按后缀猜格式（json）
$ trdsql -ih -out out_sales.json "SELECT name,price FROM testdata/sales.csv LIMIT 2"
$ cat out_sales.json
[
  {
    "name": "Orange",
    "price": "380"
  },
  {
    "name": "Melon",
    "price": "120"
  }
]

# -oz 压缩输出（zstd，文件 26 字节）
$ trdsql -ih -out out_sales.csv.zst -oz zstd "SELECT name FROM testdata/sales.csv LIMIT 2"
$ ls -la out_sales.csv.zst
-rw-r--r-- 1 root root 26 Nov  3 04:34 out_sales.csv.zst

# -oh 输出表头 / -od 换分隔符 / -oaq 全字段引号
$ trdsql -otsv -oh "SELECT * FROM testdata/test.csv"
c1	c2
1	Orange
2	Melon
3	Apple
$ trdsql -ih -od ";" "SELECT name,city FROM testdata/sales.csv LIMIT 2"
Orange;Beijing
Melon;Shanghai
$ trdsql -ih -oaq "SELECT name,city FROM testdata/sales.csv LIMIT 2"
"Orange","Beijing"
"Melon","Shanghai"

# 表格类输出
$ trdsql -ih -omd "SELECT name,price FROM testdata/sales.csv LIMIT 3"
|  name  | price |
|--------|-------|
| Orange |   380 |
| Melon  |   120 |
| Apple  |   250 |

$ trdsql -ih -ovf "SELECT name,price FROM testdata/sales.csv WHERE city='Beijing' LIMIT 2"
---[ 1]------------------------
   name | Orange
  price | 380
---[ 2]------------------------
   name | Apple
  price | 250

mkdir -p ~/.config/trdsql && cat > ~/.config/trdsql/config.json <<'EOF'
{
  "db": "local",
  "database": {
    "local": {
      "driver": "sqlite3",
      "dsn": "/opt/work/testdata/sales.db"
    },
    "pg-prod": {
      "driver": "postgres",
      "dsn": "host=db-server user=readonly password=q1w2e3r4 dbname=trdsql sslmode=require connect_timeout=5"
    },
    "pg-etl": {
      "driver": "postgres",
      "dsn": "host=db-server user=root password=q1w2e3r4 dbname=trdsql sslmode=require"
    }
  }
}
EOF
chmod 600 ~/.config/trdsql/config.json

$ trdsql "SELECT * FROM testdata/test.csv" -otsv
2026/11/03 04:35:27 export: near "-": syntax error [SELECT * FROM `testdata/test.csv` -otsv]

$ echo '{"db": "nodb"}' > bad.json
$ trdsql -config bad.json "SELECT 1"
2026/11/03 04:29:28 ERROR: db[nodb] does not found
1

# 无配置（默认 sqlite3）
$ trdsql "SELECT c1 FROM nonexistent.csv"
2026/11/03 04:35:27 export: no such table: nonexistent.csv [SELECT c1 FROM nonexistent.csv]

# 有配置 db=pdb（postgres 引擎）
$ trdsql -db pdb -ih "SELECT name, price FROM fruits WHERE id > 1"
2026/11/03 04:29:28 export: pq: column "price" does not exist at column 14 (42703) [SELECT name, price FROM fruits WHERE id > 1]