0
# CLI Interface
1

2
Interactive SQL shell with syntax highlighting, completion, and configuration support for querying data sources from the command line. Provides a user-friendly interface for exploring and analyzing data using SQL.
3

4
## Capabilities
5

6
### Command Line Application
7

8
Interactive SQL shell accessible via the `shillelagh` command with rich terminal features.
9

10
```python { .api }
11
def main():
12
    """
13
    Main CLI entry point.
14
    
15
    Starts interactive SQL shell with:
16
    - Syntax highlighting for SQL queries
17
    - Auto-completion for SQL keywords and functions
18
    - Command history with persistent storage
19
    - Configuration file support
20
    - Tabular result formatting
21
    """
22
```
23

24
### Configuration Management
25

26
YAML-based configuration system for adapter settings and connection parameters.
27

28
```python { .api }
29
# Configuration file: shillelagh.yaml
30
# Location: User config directory (varies by OS)
31
#   - Linux: ~/.config/shillelagh/shillelagh.yaml
32
#   - macOS: ~/Library/Application Support/shillelagh/shillelagh.yaml  
33
#   - Windows: %APPDATA%\shillelagh\shillelagh.yaml
34

35
# Configuration structure:
36
# adapter_name:
37
#   setting1: value1
38
#   setting2: value2
39
#   catalog:
40
#     alias1: url1
41
#     alias2: url2
42
```
43

44
### Interactive Features
45

46
Rich terminal interface with advanced features for SQL query development and data exploration.
47

48
```python { .api }
49
# Interactive features:
50
# - SQL syntax highlighting using Pygments
51
# - Keyword auto-completion  
52
# - Command history with search
53
# - Multi-line query support
54
# - Result pagination and formatting
55
# - Error handling and display
56
```
57

58
## Usage Examples
59

60
### Basic CLI Usage
61

62
```bash
63
# Start interactive shell
64
$ shillelagh
65

66
# Shell prompt with SQL highlighting
67
shillelagh> SELECT * FROM 'data.csv' LIMIT 5;
68

69
# Results displayed in table format
70
┌────┬─────────┬─────┬────────┐
71
│ id │ name    │ age │ city   │
72
├────┼─────────┼─────┼────────┤
73
│ 1  │ Alice   │ 25  │ NYC    │
74
│ 2  │ Bob     │ 30  │ LA     │
75
│ 3  │ Charlie │ 35  │ Chicago│
76
└────┴─────────┴─────┴────────┘
77

78
shillelagh> 
79
```
80

81
### Configuration File Setup
82

83
Create configuration file for adapter settings:
84

85
```yaml
86
# ~/.config/shillelagh/shillelagh.yaml
87

88
# Google Sheets API configuration
89
gsheetsapi:
90
  service_account_file: /path/to/credentials.json
91
  subject: user@example.com
92
  catalog:
93
    # Define aliases for commonly used sheets
94
    sales_data: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
95
    inventory: https://docs.google.com/spreadsheets/d/1AbCdEfGhIjKlMnOpQrStUvWxYz/edit
96

97
# GitHub API configuration  
98
githubapi:
99
  token: ghp_your_github_token_here
100
  catalog:
101
    my_repos: https://api.github.com/users/myusername/repos
102
    org_repos: https://api.github.com/orgs/myorg/repos
103

104
# Weather API configuration
105
weatherapi:
106
  api_key: your_weather_api_key_here
107

108
# Generic JSON API configuration
109
genericjsonapi:
110
  headers:
111
    User-Agent: "ShillelaghCLI/1.0"
112
    Accept: "application/json"
113
```
114

115
### Using Aliases
116

117
```bash
118
# With configuration file aliases, use short names instead of full URLs
119
shillelagh> SELECT * FROM sales_data WHERE amount > 1000;
120

121
shillelagh> SELECT name, stargazers_count FROM my_repos ORDER BY stargazers_count DESC;
122

123
shillelagh> SELECT city, temperature FROM weather WHERE temperature > 30;
124
```
125

126
### Advanced Query Examples
127

128
```bash
129
# Multi-source join query
130
shillelagh> SELECT 
131
    u.name,
132
    d.department_name,
133
    s.total_sales
134
FROM 'https://api.company.com/users' u
135
JOIN './departments.csv' d ON u.dept_id = d.id  
136
JOIN sales_data s ON u.id = s.user_id
137
WHERE s.total_sales > 10000
138
ORDER BY s.total_sales DESC;
139

140
# Aggregate analysis
141
shillelagh> SELECT 
142
    DATE(created_at) as date,
143
    COUNT(*) as issues_created,
144
    AVG(CASE WHEN state = 'closed' THEN 1 ELSE 0 END) as close_rate
145
FROM 'https://api.github.com/repos/owner/repo/issues'
146
WHERE created_at >= '2023-01-01'
147
GROUP BY DATE(created_at)
148
ORDER BY date;
149

150
# Data export to CSV
151
shillelagh> SELECT * FROM 'https://api.example.com/data' 
152
WHERE category = 'important'
153
-- Results can be piped to file: shillelagh < query.sql > output.csv
154
```
155

156
### Error Handling and Help
157

158
```bash
159
# SQL syntax errors are highlighted
160
shillelagh> SELCT * FROM data.csv;
161
Error: SQL syntax error near 'SELCT'
162

163
# Adapter-specific errors
164
shillelagh> SELECT * FROM 'https://invalid-url.com/data.json';
165
Error: Failed to connect to data source
166
Details: Connection timeout after 30 seconds
167

168
# Built-in help and information
169
shillelagh> .help
170
Available commands:
171
  .help     - Show this help message
172
  .exit     - Exit the shell
173
  .version  - Show version information
174
  .adapters - List available adapters
175

176
shillelagh> .adapters
177
Available adapters:
178
  - csvfile: CSV file adapter
179
  - gsheetsapi: Google Sheets API adapter  
180
  - githubapi: GitHub API adapter
181
  - genericjsonapi: Generic JSON API adapter
182
  - htmltableapi: HTML table scraper
183
  [... more adapters ...]
184
```
185

186
### Command History and Navigation
187

188
```bash
189
# Command history persisted between sessions
190
shillelagh> SELECT COUNT(*) FROM data.csv;
191
# Press Up arrow to recall previous commands
192
# Press Ctrl+R to search command history
193

194
# Multi-line query support
195
shillelagh> SELECT 
196
         >   name,
197
         >   age,
198
         >   city
199
         > FROM users
200
         > WHERE age > 25;
201
```
202

203
### Performance and Debugging
204

205
```bash
206
# Query timing information
207
shillelagh> SELECT COUNT(*) FROM 'https://large-api.com/data';
208
┌──────────┐
209
│ COUNT(*) │
210
├──────────┤
211
│ 150000   │
212
└──────────┘
213
Query executed in 2.34 seconds
214

215
# Verbose mode for debugging
216
shillelagh> .verbose on
217
Verbose mode enabled
218

219
shillelagh> SELECT * FROM data.csv LIMIT 1;
220
[DEBUG] Loading adapter: CSVFile
221
[DEBUG] Parsing URI: data.csv  
222
[DEBUG] Executing query: SELECT * FROM data.csv LIMIT 1
223
[DEBUG] Adapter returned 1 rows in 0.05 seconds
224
┌────┬──────┬─────┐
225
│ id │ name │ age │
226
├────┼──────┼─────┤
227
│ 1  │ John │ 30  │
228
└────┴──────┴─────┘
229
```
230

231
## Installation and Setup
232

233
### Installing CLI Dependencies
234

235
```bash
236
# Install shillelagh with CLI support
237
pip install shillelagh[console]
238

239
# Or install all optional dependencies
240
pip install shillelagh[all]
241
```
242

243
### First Run Setup
244

245
```bash
246
# Create initial configuration
247
$ shillelagh --setup
248

249
# This creates:
250
# - Configuration directory
251
# - Default configuration file
252
# - Command history file
253
# - Cache directory for adapter data
254

255
Configuration directory created at: ~/.config/shillelagh/
256
Edit shillelagh.yaml to configure adapters.
257

258
# Start using the CLI
259
$ shillelagh
260
Welcome to Shillelagh SQL shell
261
Type .help for help, .exit to quit
262

263
shillelagh> 
264
```
265

266
### Environment Variables
267

268
```bash
269
# Override configuration file location
270
export SHILLELAGH_CONFIG=/path/to/custom/config.yaml
271

272
# Set cache directory
273
export SHILLELAGH_CACHE_DIR=/path/to/cache
274

275
# Enable debug logging
276
export SHILLELAGH_LOG_LEVEL=DEBUG
277

278
# Run with environment settings
279
shillelagh
280
```