Metadata-Version: 2.4
Name: dbflib
Version: 1.0
Summary: Create, mutate, and index Visual FoxPro DBF tables (DBF + FPT memo + CDX index), byte-compatible with files written by Visual FoxPro Advanced (32-bit)
Author-email: Sean Kaat <skaat@kdsmoses.com>
License-Expression: MIT
Keywords: dbf,cdx,fpt,foxpro,visual-foxpro,vfp,vfpa
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: dev
Requires-Dist: pytest; extra == "dev"
Requires-Dist: pyyaml; extra == "dev"
Dynamic: license-file

# dbflib

Create, mutate, and index **Visual FoxPro** DBF tables — the `.dbf` table, its
`.fpt` memo file, and its `.cdx` compound index — from pure Python, with no
dependencies outside the standard library.

The correctness bar is not "a reader can parse it": files produced by dbflib
are **byte-compatible with files written by Visual FoxPro Advanced (VFPA,
32-bit)** — the target environment — verified against a corpus of several
hundred VFPA-created production tables. That includes the quirky parts —
VFP's numeric overflow "squeeze", cp1252 with undefined code points
preserved, x87 extended-precision arithmetic in index key expressions,
machine collation, and deleted records remaining indexed. The files should
also be compatible with classic Visual FoxPro, but 32-bit VFPA is what the
output is verified against.

## Installation

```sh
pip install dbflib
```

Requires Python 3.9+. No runtime dependencies.

## Quick start

```python
from dbflib import create_dbf, insert_rows, delete_rows, add_dbf_column

create_dbf('out/items.dbf',
    columns=[('name', 'C (30)'), ('qty', 'N (8, 2)'), ('note', 'M')],
    rows=[{'name': 'widget', 'qty': 1.5}],
    indexes={'BYNAME': 'UPPER(name)'})

insert_rows('out/items.dbf', [{'name': 'gadget'}])
delete_rows('out/items.dbf', lambda record: record['QTY'] == 0)
add_dbf_column('out/items.dbf', 'price', 'N (10, 2)', fill=0)
```

Every mutating call automatically rebuilds the table's structural `.cdx` from
the index expressions stored inside it, so the index never goes stale.

### Column types

Columns are `(name, type)` pairs using compact schema notation:

| Notation   | VFP type                          |
| ---------- | --------------------------------- |
| `C (n)`    | Character, width *n*              |
| `N (n, d)` | Numeric, width *n*, *d* decimals  |
| `F (n, d)` | Float (stored like Numeric)       |
| `M`        | Memo (creates the `.fpt`)         |
| `L`        | Logical                           |
| `I`        | Integer (binary int32)            |
| `D`        | Date                              |
| `T`        | DateTime                          |
| `Y`        | Currency                          |
| `B`        | Double                            |

### Indexes

`indexes` maps CDX tag names to a FoxPro key expression string, or to a dict
with `key` and optional `filter` / `unique` / `descending`:

```python
from dbflib import build_cdx

build_cdx('out/items.dbf', {
    'BYNAME':  'UPPER(name)',
    'POSONLY': {'key': 'UPPER(name)', 'filter': 'qty > 0'},
    'UNAME':   {'key': 'UPPER(name)', 'unique': True},
})
```

Key and filter expressions are real FoxPro expressions, compiled to Python
and evaluated with VFPA's exact semantics so the resulting key bytes match
what VFPA would write.

**The expression compiler supports a subset of FoxPro syntax, not the full
language.** Supported: field references, string/numeric/date/logical
literals, comparison and logical operators (`.AND.`/`.OR.`/`.NOT.` and
symbol forms), string concatenation, numeric arithmetic, and these
functions:

> `UPPER LOWER TRIM RTRIM LTRIM ALLTRIM SPACE PADR PADL SUBSTR LEFT RIGHT
> STR STRTRAN CHRTRAN CHR LEN AT VAL ABS MOD MAX MIN MLINE RECNO DELETED
> DTOS DTOC CTOD CTOT DTOT TTOD TTOC TIME YEAR MONTH DAY HOUR MINUTE SEC
> EMPTY IIF ICASE EVL`

Anything outside this subset raises `FoxProExpressionError` when the index
is built — nothing is silently miscomputed. Support for more of the language
can be added as needed; it just isn't feature-complete today. One structural
constraint carried over from how VFP sizes index keys: every character-typed
expression must have a statically known width, so e.g. `STRTRAN(...)` must
be wrapped in `PADR(...)` to be usable as a key.

### Reading

```python
from dbflib import open_dbf_table, read_cdx, read_cdx_definitions

table = open_dbf_table('out/items.dbf')
try:
    rows = table.read_rows()        # list of dicts of Python values
finally:
    table.close()

tags, order = read_cdx('out/items.cdx')            # tag properties + ordered (key, recno) entries
definitions = read_cdx_definitions('out/items.cdx')  # recover index definitions from the CDX itself
```

## API summary

| Function | Purpose |
| -------- | ------- |
| `create_dbf(path, columns, rows=(), indexes=None)` | Create a table (replacing existing files), optionally with rows and a structural CDX |
| `insert_rows(path, rows, truncate=False)` | Append row dicts; `truncate=True` empties the table first |
| `delete_rows(path, where)` | Soft-delete by 1-based record numbers or a row predicate |
| `add_dbf_column` / `drop_dbf_column` / `alter_dbf_column` | Column operations; rebuild the table in place, preserving deleted flags and the CDX |
| `build_cdx(path, indexes)` | Build the structural compound index |
| `build_cdx_per_tag(path, definitions)` | Build one single-tag CDX file per index |
| `refresh_cdx(path)` | Rebuild the structural CDX from its own stored expressions |
| `read_cdx` / `read_cdx_definitions` | Read an existing CDX back into tags/entries or index definitions |
| `open_dbf_table(path)` | Open a table read-write (`VfpTable`) |
| `DbfTableData(path)` | Low-level binary reader used for fast index builds |

## Fidelity notes

Where Visual FoxPro's behavior conflicts with Python convention, dbflib sides
with VFP (as observed in 32-bit VFPA):

- Oversized numeric values silently "squeeze" (drop decimals, then scientific
  notation, then asterisks) instead of raising.
- `****` numeric overflow reads back as `+inf` and re-encodes as asterisks.
- Deleted records stay in index tags, exactly as VFP keeps them.
- Text is cp1252 with `surrogateescape`, so undefined bytes round-trip.
- Index keys use VFP machine collation and its exact numeric/date/datetime
  key encodings.

## License

MIT
