Skip to content

DBFxSQL Wiki

Jump to bottom
Joel Abreu Rojas edited this page Jul 3, 2025 · 1 revision

Home (Overview)

DBFxSQL is a CLI tool that enables seamless data consistency between legacy DBF (dBase) files and modern SQL databases during migration projects.

Key Features

  • Multi-Engine Support: dBase, SQLite, and MSSQL
  • Bidirectional Sync: Real-time synchronization between databases
  • CLI Interface: Comprehensive command-line operations with embedded examples
  • Configuration-Driven: TOML-based setup and relation mapping

Architecture Overview

The system uses a modular architecture with separate controllers for each database engine, unified through a common CLI interface that automatically detects target engines based on file extensions.

Installation Guide

System Requirements

  • Python: 3.12 or higher
  • Operating System: Linux, Windows
  • Dependencies: Managed via Poetry

Installation Steps

  1. Clone the repository:
git clone https://github.com/joelabreurojas/DBFxSQL.git
  1. Install the source code:
pip install DBFxSQL/
  1. Verify installation:
dbfxsql

First Run Configuration

On first execution, DBFxSQL automatically creates a default configuration file at ~/.config/DBFxSQL/config.toml. The system creates the directory structure if it doesn't exist.

Alternative Entry Points

The tool can be run both as dbfxsql and python -m dbfxsql.

Command Line Interface

The CLI provides unified access to all database operations through a consistent command structure with automatic engine detection and parameter validation.

Available Commands

CRUD Operations

  • create: Create DBF files or SQL databases/tables
  • insert: Add records to databases
  • read: Query data with optional conditions
  • update: Modify existing records
  • delete: Remove records
  • drop: Delete files, databases, or tables (with confirmation prompts)

Synchronization Commands

  • migrate: One-time data migration
  • sync: Continuous synchronization with file monitoring

Command Structure

All commands follow a consistent parameter pattern:

  • -s, --source: Target file (required)
  • -t, --table: Table name (SQL engines only)
  • -f, --fields: Field definitions or values
  • -c, --condition: Query/filter conditions (requires three separate values: field, operator, value)
  • -n, --notify: Enable operation notifications

Special Condition Fields

The system supports row_number as a virtual condition field for row-based operations. Use quoted operators for better shell compatibility:

dbfxsql read -s users.dbf -c row_number "<=" 10

Embedded Examples System

The CLI automatically includes relevant examples in help text using the @embed_examples decorator for all commands.

Configuration Guide

Configuration File Structure

DBFxSQL uses a TOML configuration file with three main sections:

Engine Definitions

[engines.dBase]
folderpaths = ["."]
extensions = [".dbf", ".DBF"]

[engines.SQLite]
folderpaths = ["."]
extensions = [".sql", ".SQL", ".sqlite3", ".SQLite3", ".db", ".DB"]

[engines.MSSQL]
folderpaths = ["/var/opt/mssql/data/"]
extensions = [".mdf", ".MDF"]
db_server = "SERVER\\INSTANCE"
db_user = "username"
db_password = "password"

Relation Mappings

[[relations]]
sources = ["users.dbf", "company.sql"]
tables = ["", "users"]
fields = [["id", "name"], ["id", "name"]]
priority = "users.dbf"

Configuration Management

The system automatically loads configuration using the file manager, creating default settings and directory structure if none exist.

Database Engine Support

Supported Engines

dBase Engine

  • Extensions: .dbf, .DBF
  • Connection: Direct file access
  • Operations: Full CRUD support
  • Data Types: Character (C), Numeric (N), Date (D), Logical (L), Memo (M)

SQLite Engine

  • Extensions: .sql, .sqlite3, .db
  • Connection: Local database file
  • Operations: Full CRUD + Database management
  • Data Types: INTEGER, REAL, TEXT, BLOB, NULL

MSSQL Engine

  • Extensions: .mdf
  • Connection: Network database server
  • Operations: Full CRUD + Database management
  • Data Types: Comprehensive SQL Server type support
  • Known Issues: Connection issues in database manipulation commands

Engine Detection

The system automatically detects target engines based on file extensions, throwing a click.UsageError for unknown extensions.

Synchronization Guide

Synchronization Modes

One-Time Migration

Transfers data based on configured priorities using the migrate command.

dbfxsql migrate --notify

Continuous Synchronization

Monitors file system changes and applies updates in real-time using the sync command.

dbfxsql sync --notify

Synchronization Process Implementation

The synchronization follows a structured pipeline:

  1. formatters.package_changes() - Package changes from source files
  2. _assign_rows() - Assign rows to sync tables
  3. formatters.compare_tables() - Compare tables to identify differences
  4. formatters.classify_operations() - Classify operations (insert, update, delete)
  5. _execute_operations() - Execute operations on destination databases

Examples and Tutorials

DBF Operations

# Create DBF file
dbfxsql create -s users.dbf -f id "N(20,0)" -f name "C(20)"

# Insert data
dbfxsql insert -s users.dbf -f id 1 -f name "John Doe"

# Query data (condition format: field operator value)
dbfxsql read -s users.dbf -c id == 1

# Query by row number (use quotes for operators)
dbfxsql read -s users.dbf -c row_number "<=" 10

SQL Operations

# Create database and table
dbfxsql create -s company.sql -t users -f id 'integer primary key' -f name text

# Insert data
dbfxsql insert -s company.sql -t users -f id 1 -f name "John Doe"

# Update records (condition format: field operator value)
dbfxsql update -s company.sql -t users -f name "Jane Doe" -c id == 1

# Query by row number
dbfxsql read -s company.sql -t users -c row_number "<=" 5

The system includes embedded examples through the @embed_examples decorator for all CLI commands.

API Reference

CLI Command Reference

The CLI implements comprehensive database operations through a unified interface with automatic engine detection and parameter validation.

Core Functions

  • Engine Detection: Automatic database engine identification
  • Parameter Validation: Engine-specific parameter checking with detailed error messages
  • Operation Routing: Command routing to appropriate controllers
  • Error Handling: Comprehensive exception management with confirmation prompts for destructive operations

Condition System

The condition system supports all standard comparison operators and special fields:

  • Standard operators: ==, !=, <, >, <=, >=
  • Special field: row_number (virtual field for row position)
  • Value quoting: row_number conditions bypass automatic type quoting

Troubleshooting

Common Issues

Engine Detection Problems

Issue: "Unknown extension for 'filename' source" Solution: Ensure file extensions match configured engine extensions.

Parameter Validation Errors

Issue: "No such option '-t' / '--table' for DBF" Solution: Remove table parameter when working with DBF files

Issue: "Missing option '-t' / '--table' for SQL" Solution: Always specify table name for SQL operations

Issue: Condition parameter format errors Solution: Use three separate values for conditions: field, operator, value

Known Limitations

  • MSSQL connection issues in database manipulation commands
  • Requires local file system access
  • Some advanced features are still under development

Testing and Validation

The project includes comprehensive test suites for both DBF and SQL operations, validating CLI operations and database functionality across all supported engines. Tests require the default config file and use subprocess calls to test CLI commands end-to-end.

Contributing Guide

Development Setup

  1. Fork and clone the repository
  2. Install development dependencies with Poetry
  3. Run tests to verify setup (requires default config file)
  4. Follow the project's coding standards

Testing Framework

The project uses pytest for comprehensive testing with separate test suites for DBF and SQL operations. Tests validate CLI commands through subprocess calls and verify database operations across all supported engines.

FAQ

General Questions

Q: When should I use migration vs continuous sync? A: Use migration for one-time data transfers; use sync for ongoing synchronization needs.

Q: What databases are supported? A: dBase (.dbf), SQLite (.sql, .sqlite3, .db), and MSSQL (.mdf) databases.

Q: How does conflict resolution work? A: Priority-based resolution uses the priority field in relation configurations.

Q: How do I format condition parameters? A: Use three separate values: field, operator, value (e.g., -c id == 1). For operators with special characters, use quotes (e.g., -c row_number "<=" 10).

Q: What is the row_number field? A: A virtual field that represents the row position (1-based) in the data, useful for row-based operations.

Q: What are the known limitations? A: MSSQL connection issues in database manipulation commands, requires local file system access, and some advanced features are under development.

Clone this wiki locally