DotSQL: SQL Tool

The DotHRB DotSQL executable is a command-line tool for direct management, inspection, and manipulation of SQL db. It supports command execution via JSON input and direct command options.

Important

Currently, DotSQL exclusively supports the integrated SQLite. Support for additional SQL drivers will be evaluated and introduced in future releases based on community demand and user feedback.

Usage Syntax

dotsql [options]

---

1. ⚙️ Core Options and Configuration

These options define database connectivity, input/output files, and general tool behavior.

Option	Description
-h / -help	Display the help documentation.
-v	Enable Verbose output.
-o <file>	Set the output file path for results.
-s	Open the output file using the system's default application after creation (requires -o).
-dbs <json|file>	Set dotdbs connection parameters (JSON string or file path) for connecting to the database.
-c <file>	Specify the Configuration file to use for database connection details.
-model	Create a model file based on the database or table structure.
-t <table>	Set a default table for subsequent commands (similar to USE in some SQL shells).
-deleted	Set deleted records processing off (behavior may vary by underlying DB).
--license	Display license and copyright information.
--version	Display the tool version.

---

2. ⌨️ Command History

These commands are used for accessing command history.

Command	Description
:	List the history of log entries (previous commands).
:? <search>	List log entries that contain the specified <search> string.
:<n>	Recall and execute log entry number <n>.
:$	Recall and execute the last log entry.
:@<template>	Recall a template** (e.g., @select).

---

3. 📝 Practical Examples

These examples demonstrate common ways to execute SQL queries and commands using DotSql.

Scenario	Command	Purpose
Execute Command File	dotsql myproc.json	Executes a set of commands (e.g., a batch of queries) defined in myproc.json.
Direct select	dotsql -d test.db -t test	Executes 'select * from test' and output a json to stdout.
Inline JSON Execution	dotsql '{"cmd":"execute","dbname":"test.db","query":"select * from movie"}'	Executes a specific SQL query, passing the full command structure as a quoted JSON string.
Export to Spreadsheet	dotsql -o movie.xlsx '{"cmd":"execute","dbname":"test.db","query":"select * from movie"}'	Executes the query and immediately saves the result set into the movie.xlsx file.

---

4. 📝 Import a directory of DBF files into a SQL database

This example demonstrates how to import an entire directory of DBF files into a SQL database. DotSQL automatically reads the DBF structure and generates the corresponding SQL schemas.

Key Features

• Flexible "C" Type Mapping: While standard types are automatically mapped using optimal defaults—such as N (Numeric) fields to NUMERIC(len, dec) and D to DATE—you can explicitly configure how C (Character) fields are handled, choosing between VARCHAR, VARCHAR(len), CHAR(len), or TEXT.
• Automatic Primary Keys: If a CDX production bag contains an index tag named primary with an expression like field1+field2+fieldn, DotSQL automatically generates a primary key constraint named pk_{tablename} using those exact fields. Additionally, an auto-incrementing identity column (e.g., id) can be injected.
• Audit & Concurrency Fields: Upon request, standard tracking columns—such as created_by, updated_by, created_at, and updated_at—will be appended to support built-in audit systems and optimistic locking algorithms.

Usage Example

Execute the following command to process the directory:

dotsql '{"cmd":"loaddir","dir":"/mydbfs", "dbname":"new.db"}'

⏳ loading `/mydbfs`...
⏳ generate `mytab.sql` to load `mytab.dbf` into `new.db` table `mytab` 💡 564 rows.
💡 index `pk_mytab` created
...