DB Master One

DB Master One was a database for the Atari ST home computer, circa 1986. The best description of it that I have been able to find is at https://www.atarimagazines.com/v5n2/Freeware.html
I haven't managed to find the two programs mentioned, MAKEONE.PRG and USEONE.PRG, but I have located two database files and written a python script to convert the database files to excel spread sheets. Because I have sample size of two, there are many assumptions made, but I hope this will be useful. Both my samples have only a single table.

I am going to attempt to describe the structure of the database file.

Note that two byte fields are high byte first because the 68000 was a big-endian processor.

The first 282 bytes of the file are a file header, most of which I have not figured out.
The first two bytes have the value 00 01 - a nod to the program name "DB Master ONE"
The fifth and sixth bytes are a count of  records in the file. Starting at byte 0xDE is the name of the file.

From Byte 0x11a to the end of the file is a sequence of records.
Each record begins with a two byte record marker 0xF2F3.

There are two classes of records, null records and other records.
A Null record is a total of four bytes:
F2 F3 00 00
A Null record seems to placed roughly every 10,000 bytes.

All other records have an eight byte header of the form:
F2 F3 00 len type 00 00 00
The length of the record (including the 8-byte header) is 2*len. It is possible that the len field is actually two bytes, but the longest records I have seen are 318 byte report definitions (len = 0x9F)

The Record Types are:
00The first record. Database summary? The last two bytes are a count of the number of data records.
01The second record. Probably Screen Layout? Seems to have roughly 26 bytes per field plus about a dozen bytes after the header. Screen Coordinates are in pixels, somaximum field lengths are not recorded explicitly, but are calculated by the size and shape of the field.
02List of field Names. After the header there are 12 more nulls, then comes a one byte count of the number of fields, followed by null separated Field Names
0AReport definition. Begins with the name of the report. The rest of record is unexplained, but there are a lot of 0xFF bytes.
1EData Records. Null seperated fields, but with a two byte insertion at the 11th and 12th byte (described below). The last field does not need to be followed by a null.
 
Each data record has a two byte insertion at the 11th and 12th byte. This ensures that the first twelve bytes of the record are guarranteed to be unique. The insertion seems to be given the value 00 01 if the first 10 bytes of the record are unique in the table. Subsquent records that are identical in the first ten bytes get insertion values of 00 02, 00 03 etc.

Having said all that, here is the code for a python 3 script to convert a Db Master One file into a spreadsheet.


#
#   Program to read a DB Master One database and
#   generate a spreadsheet.
#   DB Master One was an in-memory database program on the Atari ST.
#
#   Each record has an 8 byte header (except for null records)
#   The Start of Record Marker is F2 F3.
#   This is followed by 00 len type 00 00 00 then the first field.
#   Len is the number of 16-bit words in the record (including the header)
#   If Len is zero, then the header is only 4 bytes and there is no type.
#   (This seems to happen about every 10000 bytes).(e.g. F2 F3 00 00 F2 F3 ...)
#   If type is 1E this indicates a data record. (eg F2 F3 00 2A 1E 00 00 00...)
#   The header is followed by the first field, followed by a null (00).
#   Each field is terminated by a null? (except maybe the last).
#
#   Every data record seems to be interrupted by 2 bytes at the 11th & 12th bytes
#   00 0x (x=1 to c)
#   These two bytes guarrantee that the first 12 bytes form a unique key for
#   the record.
#   They will be 00 01 if the first 10 bytes of the record are different from
#   the first 10 bytes of the previous record. They will be 00 02 if the first
#   10 bytes are identical to the previous record (etc).
#
#   00 00 indicates an emtpy field (ie two end of fields in a row).
#
#   Record Types are:
#   00 - The first record. Don't know what it does, possibly a database summary
#   01 - The second record. Possibly screen layout?
#   02 - count of fields, followed by null separated Field Names
#   0A - Name of Report, followed by unknown stuff.
#   1E - Data Record. Fields, separated by Nulls.
#        There can be a null after the last field.

import xlwt     # write an Excel Spread Sheet. See https://pypi.org/project/xlwt/

def main(dbname, outfile):
    f = open(dbname,"rb")
    upto = 0xde
    x = f.read(upto)
    fname = f.read(21)
    upto +=21
    print("Filename=",fname)
    wb = xlwt.Workbook()     # Create an Excel Workbook
    ws = wb.add_sheet("DbMaster")    # Add a Worksheet
    xlrow = 0
    pos = 0x11a
    delta = pos - upto
    x = f.read(delta)
    upto+=delta
    marker = f.read(2)
    upto+=2
    rec_count = 0
    while marker != b'':
        rec_count +=1
        if marker != b'\xf2\xf3':
            print('bad marker', marker,'at',upto)
        #print(marker)
        header = f.read(2)
        if header == b'':
            break
        upto+=2
        #print(header)
        size = header[1]
        read_size = size*2 -8
        if size == 0:
            print("null record at",upto)
            marker = f.read(2)
            continue
        header = f.read(4)
        upto +=4
        rec_type = header[0]
        if( header[1]!=0 or header[2]!=0 or header[3]!=0):
            print("unexpected header", header)
        record_1 = f.read(read_size)
        record = bytearray(record_1)
        upto+=read_size
        if rec_type == 2:
            # field names
            field_num = 0
            print("field names")
            num_fields = record[12]
            pos = 13
            print("field_count=",num_fields)
            for i in range(0,num_fields):
                field_name=''
                c=record[pos]
                while c!=0:
                    field_name+=chr(c)
                    pos+=1
                    c=record[pos]
                pos+=1
                ws.write(xlrow,field_num,field_name)  #Write a field name into the Column Header
                field_num +=1
                print(field_name)
            xlrow+=1   
        elif rec_type == 30:
            pos = 0
            field_num = 0
            if record[10] != 0:
                print('no null at ',xlrow, upto)
                # A null is usually end of field, but every record
                # seems to have a null followed by a non-null control
                # at the 11th and 12th byte.
                # Get rid of them so that we can treat nulls as end of field.
            else:
                record.pop(10)
                record.pop(10)
            # now reprocess the cleaned up record to extract the fields
            pos = 0
            for i in range(0,num_fields):
                field_value = ''
                if pos >= len(record):
                    break
                c = record[pos]
                while c!=0:
                    field_value+=chr(c)
                    pos+=1
                    if pos>=len(record):
                        break
                    c=record[pos]
                pos+=1
                ws.write(xlrow,field_num,field_value)
                field_num+=1
            xlrow+=1
        elif rec_type == 10:    # Report definition. Apart from the report name, I haven't
                                # cracked the report description
.
            report_name = ''
            pos = 0
            c = record[pos]
            while c!=0:
                report_name+=chr(c)
                pos+=1
                c=record[pos]
                if pos>=len(record):
                    break
            print("Report Name:",report_name)
        marker = f.read(2)
        upto+=2
    if outfile !='':
        wb.save(outfile)
    print("count=",rec_count, "file size=",upto)
if __name__ == "__main__":
    main('d:/Atari/floppy/MLIST.ONE','mlist.xls')