topspeed 0.3.0
topspeed: ^0.3.0 copied to clipboard
Pure Dart reader and writer for Clarion TopSpeed (.tps) database files: tables, keys, MEMO/BLOB, code pages and encrypted files. No native code.
topspeed #
Read and write Clarion TopSpeed (.tps) database files in pure Dart.
It needs no native code, no ODBC driver and no Clarion runtime, and it
runs on the Dart VM, in Flutter (mobile and desktop) and on the web.
import 'dart:io';
import 'package:topspeed/topspeed.dart';
void main() {
final file = TpsFile.parse(File('CUSTOMER.TPS').readAsBytesSync());
final customers = file.table();
for (final row in customers.rows) {
print('${row.recordNumber}: ${row['NAME']} ${row['BALANCE']}');
}
}
Features #
| Area | Support |
|---|---|
| File structure | header, B+tree pages, RLE page compression, several tables per file |
| Field types | BYTE SHORT USHORT LONG ULONG SREAL REAL DECIMAL PDECIMAL BFLOAT4 BFLOAT8 DATE TIME STRING CSTRING PSTRING PICTURE GROUP, DIM arrays |
| Unknown types | returned as raw bytes; the rest of the file still loads |
| MEMO / BLOB | read (row.memo, row.blob); kept on update, removed on delete |
| Text | windows1252 (default), cp850, cp437, or any Encoding |
| Encryption | files with an OWNER password: read, and written back encrypted |
| Keys | read rows in key order, seek, ranges and lookups (byKey, seek, range, matching, get); DUP, OPT, NOCASE, descending parts; rebuilt on every change |
| Writing | insert, update, delete, then toBytes() |
| Damaged files | recovery mode scans leaf pages when the page tree is broken, and sorts trees that hold records out of order (read-only) |
| Large files | TpsReader reads a few pages at a time and skips index pages (read-only) |
| Live files | readTps reads again when a Clarion program writes meanwhile; no locks are taken |
| Dates and times | Clarion standard dates in LONG fields: convert, and format with patterns or Clarion pictures (@D6, @T3) |
Installing #
dart pub add topspeed
# or, in a Flutter app
flutter pub add topspeed
Reading #
final file = TpsFile.parse(bytes);
// Most files hold one table; files with several need a name.
final table = file.tables.length == 1 ? file.table() : file.table('ORDERS');
// The layout.
for (final f in table.definition.valueFields) {
print('${f.label}: ${f.type.name}, ${f.length} bytes');
}
// One row by record number, or all of them.
final row = table.row(42);
print(row?['NAME']); // a field by label ('NAME') or full name ('CUS:NAME')
print(row?.values); // every field: {NAME: ..., BALANCE: ...}
Values come back as these Dart types:
| Clarion | Dart |
|---|---|
BYTE, SHORT, USHORT, LONG, ULONG |
int |
SREAL, REAL, BFLOAT4, BFLOAT8 |
double |
DECIMAL, PDECIMAL |
String, such as "-12.50" (no precision loss) |
STRING, PICTURE |
String, trailing spaces removed |
CSTRING, PSTRING |
String |
DATE |
DateTime? (null when blank) |
TIME |
Duration? (null when blank) |
DIM(n) |
List of the element type |
| unknown type | Uint8List (raw bytes) |
row.toJson() gives the same values as a map jsonEncode accepts: dates
as "2024-02-29", times as "13:45:30.12", BLOBs as base64.
Reading by key #
Every table, from TpsFile or TpsReader, reads rows in the order of a
KEY or INDEX, the way Clarion's SET, NEXT, PREVIOUS and GET do:
final inv = file.table();
// The keys of the table and the fields they are built from.
for (final k in inv.definition.indexes) {
final fields = [for (final p in k.parts) inv.definition.fields[p.field].label];
print('${k.label} $fields dup=${k.allowsDuplicates} nocase=${k.ignoresCase}');
}
inv.byKey('ITEM_KEY'); // every row in key order
inv.byKey('ITEM_KEY', reverse: true); // backwards
inv.get('ITEM_KEY', {'ITEM_NBR': '12345'}); // first match, or null
inv.matching('DEPT_KEY', {'DEPT': 7}); // every row with DEPT 7
inv.seek('NAME_KEY', {'NAME': 'M'}); // SET(key, key) then NEXT
inv.seek('NAME_KEY', {'NAME': 'M'}, reverse: true); // ... then PREVIOUS
inv.range('DATE_KEY',
from: {'DATE': DateTime(2024, 1, 1)},
to: {'DATE': DateTime(2024, 12, 31)}); // both ends included
- A key is named by label (
ITEM_KEY) or full name (INV:ITEM_KEY). - Values may give only the leading parts of a key: for a key on
(DEPT, ITEM_NBR),{'DEPT': 7}matches every item of department 7. - Text compares the way the key stores it: padded with spaces to the field
length, and without case on
NOCASEkeys.{'NAME': 'M'}is a position forseek, not a prefix match. - Positions follow key order, so on a descending part "after" means a smaller value.
- To find a value no Dart value reproduces, such as a text field Clarion
left filled with zero bytes, pass the field's raw bytes as a
Uint8List. - With
TpsReader, a lookup reads only the few pages on the way to the key entry and the row. - To sort by fields no key covers, collect the rows and sort the list:
table.rows.toList()..sort((a, b) => ...).
Dates stored in LONG fields #
Many Clarion programs keep dates and times in LONG fields as "standard"
dates (days since 28 December 1800) and times (hundredths of a second
since midnight, plus one). Convert them like this:
final shipped = clarionDateToDateTime(row['SHIPDATE'] as int); // DateTime?
final at = clarionTimeToDuration(row['SHIPTIME'] as int); // Duration?
table.update(row.recordNumber, {'SHIPDATE': dateTimeToClarionDate(DateTime.now())});
To show them as text, format with a pattern or with a Clarion picture.
Both accept the LONG value, or the DateTime/Duration of a DATE
or TIME field, and give '' for a blank date:
formatClarionDate(row['SHIPDATE']); // 31/10/1959
formatClarionDate(row['SHIPDATE'], pattern: 'dd-MM-yyyy'); // 31-10-1959
formatClarionDate(row['SHIPDATE'],
pattern: "EEEE d 'de' MMMM 'de' yyyy",
names: ClarionDateNames.spanish); // Sábado 31 de Octubre de 1959
formatClarionTime(row['SHIPTIME'], pattern: 'h:mm a'); // 5:30 PM
formatClarionPicture(row['SHIPDATE'], '@D6'); // 31/10/1959
formatClarionPicture(row['SHIPDATE'], '@D4'); // October 31, 1959
formatClarionPicture(row['SHIPTIME'], '@T3'); // 5:30PM
formatClarionPicture knows the predefined pictures @D1 to @D16 and
@T1 to @T6, with Clarion's separator and zero-fill options (@D06-).
@D17, @D18, @T7 and @T8 follow the Windows settings, so use a
pattern for those.
MEMO and BLOB #
final notes = row.memo('NOTES'); // String?
final photo = row.blob('PHOTO'); // Uint8List?
print(row.memos); // {NOTES: ..., PHOTO: ...}
Code pages #
Clarion stores text in the ANSI code page of the PC that wrote the file. The default is Windows-1252 (Western Europe and the Americas). Older DOS programs often used CP850 or CP437:
final file = TpsFile.parse(bytes, encoding: cp850);
Any Encoding from dart:convert works too (latin1, utf8, ...).
Encrypted files #
final file = TpsFile.parse(bytes, password: 'owner-password');
Without the password, parse throws a TpsException that says the file
may be encrypted. toBytes() encrypts again with the same password.
Large files #
TpsFile.parse decodes the whole file, including every index entry. For
large files, use TpsReader. It reads only the pages a call needs and
keeps a small cache of recent pages:
import 'package:topspeed/topspeed_io.dart';
final reader = openTps(r'C:\data\INV.TPS', password: 'optional');
try {
final inv = reader.table();
print(inv.rowCount); // from the table's metadata: instant
print(inv.row(50740)?['DESCR']); // by record number: a few page reads
print(inv.get('ITEM_KEY', {'ITEM_NBR': '12345'})?['DESCR']); // by key
for (final row in inv.rows(fromRecord: 1000)) {
// rows are read as you go
}
} finally {
reader.close();
}
On a 52 MB file with 100,960 rows and 28 keys, a full scan takes under a
second and about 50 MB of memory. TpsFile.parse needs about 580 MB for
the same file. A lookup by key takes about a millisecond.
Files a Clarion program is using #
The reader opens files read-only and never locks them, so the Clarion
program keeps working. If it writes while you read, the read may mix old
and new pages. readTps handles that: it runs your read, and if the file
changed meanwhile, throws the result away and reads again.
final items = readTps(r'C:\data\INV.TPS', (reader) => [
for (final row in reader.table().byKey('ITEM_KEY')) row.toJson(),
]);
topspeed_io.dartneedsdart:io. On the web, pass the bytes to aTpsMemorySource, or implementTpsByteSourceyourself.- The reader is read-only. To change rows, use
TpsFile. - Reads are synchronous. In Flutter, run full scans in
Isolate.runso the UI keeps drawing. Singlerow(n)lookups are fast enough on the UI isolate. - A Clarion program may keep writing to the file while you read it. Use
readTps, or checkreader.changedOnDiskand open a new reader. - A damaged page tree makes the reader throw, or repeat rows when the tree
holds records out of order.
TpsFile.parsereads such files in recovery mode (file.recovered).
Writing #
final file = TpsFile.parse(await File(path).readAsBytes());
final table = file.table();
// Only write when the library reproduces this file's keys exactly.
if (!table.indexesVerified) throw StateError('keys not verified');
final n = table.insert({'CODE': 10, 'NAME': 'New customer', 'BALANCE': '0.00'});
table.update(n, {'NAME': 'Renamed'});
table.delete(7);
await File(path).writeAsBytes(file.toBytes());
- Changes stay in memory until you call
toBytes(). That call returns the whole file with a freshly built page tree and a higher change counter. - Each change rebuilds the table's keys and checks unique keys. A
duplicate throws
TpsException. indexesVerifiedcompares the keys the library rebuilds with the keys stored in the file. If they differ, the library does not encode that file's keys the way TopSpeed does, and you should not write it.- MEMO and BLOB values cannot be written yet.
updatekeeps them, anddeleteremoves them with the row.
Safety checklist for production data #
- Never write while a Clarion program has the file open. This library does not take TopSpeed's locks.
- Keep a backup. Write the new bytes, read them back, and compare.
- Check
indexesVerifiedbefore writing. - Do not write a file opened in recovery mode (
file.recovered).toBytes()refuses to do so.
Checking your own files #
The repository has a command-line checker. It parses every .tps file in
a folder, decodes all values and memos, verifies the keys, and does a full
rewrite in memory. It never changes the files.
dart run tool/tps_check.dart path/to/folder [maxBytes] [--recursive] [--full] [--password=x] [--passwords=file] [--encoding=cp850]
dart run tool/tps_dump.dart path/to/FILE.TPS [rows] [--password=x]
dart run tool/tps_export.dart path/to/FILE.TPS [--key=KEY] [--format=csv|jsonl] [--out=path] [--password=x]
dart run tool/tps_stream_bench.dart path/to/FILE.TPS [--compare] [--password=x]
tps_check also reads each file with TpsReader and compares, and looks
rows up by each of their keys: about 2,000 per key, or every row with
--full. --passwords names a file with one
password per line; each encrypted file is tried with all of them, and the
passwords are never printed. tps_export writes one table as CSV or JSON
Lines, in record or key order.
tps_stream_bench times TpsReader on one file. With --compare, it
also checks every row against TpsFile.parse.
The result is a tally such as {OK: 120}. Any other status names the file
and the problem: INDEX_MISMATCH, REWRITE_MISMATCH, READER_MISMATCH,
KEY_ORDER_MISMATCH, KEY_LOOKUP_MISS, STALE_KEYS (key entries whose
row is gone), ROW_COUNT_STALE (the stored row count is wrong),
RECOVERED, LOCKED (no password opened it), ERROR or SKIP. Please
report such files (without private data) as issues.
Limitations #
- MEMO/BLOB writing is not supported.
PDECIMAL,BFLOAT4andBFLOAT8follow Clarion's documentation and runtime, but no real file with them has been checked yet. Keys on them are compared as raw bytes.- The strong encryption added in Clarion 11.1 (CryptoAPI providers) is not
supported;
OWNERpasswords are. - A non-empty
externalFileon a key or memo is its ClarionNAME()attribute. In the files checked so far its data is still inside the.tpsand is read; memos kept in a separate file are not supported. TpsFile.parseloads the whole file into memory.TpsReaderdoes not, but it cannot write.- The format is not publicly documented. This library follows the
behaviour of real files and of tps-parse.
Before writing, always check
indexesVerified.
License #
MIT, see LICENSE. The encryption code is derived from tps-parse (Apache 2.0); see NOTICE.
Releasing (maintainers) #
Every push or merge to main runs the checks in .github/workflows/ci.yml.
If pubspec.yaml holds a version that is not on pub.dev yet, release.yml
creates the tag v<version>, and that tag starts publish.yml, which
publishes to pub.dev.
To release a new version:
- Raise
versioninpubspec.yaml(plainX.Y.Z). - Add a
## X.Y.Zsection toCHANGELOG.md. - Merge or push to
main.
One-time setup:
- Publish the first version by hand:
dart pub publish. - On pub.dev, open the package's Admin tab and enable Automated
publishing from GitHub Actions. Use repository
Isaac80536/topspeedand tag patternv{{version}}, and require the GitHub environmentpub.dev. - On GitHub, create the environment
pub.dev(Settings > Environments). - Create a fine-grained personal access token with Contents: Read and
write on this repository only, and save it as the Actions secret
RELEASE_TOKEN. Tags pushed with the default token do not start other workflows, so the tag must be pushed with this token.