Tag Archives: File Format

Reading Bookmark Library System data files

Leave a comment

bookmark library system logoThe school I work for has a library system called Bookmark. At the start of the year, just before the students come back, I help the librarian update all the student details in the system. I do this by exporting out the details from SAS2000 and importing them into Bookmark. Of course, as it only occurs every 12 months, we forget how to do the process each time and have to find the documentation how to do it each time. Also throughout the year, new students come and others leave. My goal was to make this all easier to handle and make it so the student details would already be in the library system before they first went to borrow a book. What follows is part of my effort to make it easier to keep the library system up to date.

Bookmark Library System

Reverse Engineering the File Format

Bookmark uses its own custom binary file format. It stores various details in different files making a kind of relational database. To help reverse engineer the file format, I used a hex editor. The borrower names are stored in a file called BORROWER.DAT. Some extra info about the borrower is stored in a file called BORRCMTS.DAT. In the hex editor, it shows the location of different pieces of data in the file. From this I was able to determine that each borrower had a record of 256 bytes, with a 256 byte header at the start of the file. Also for borrowers that have been deleted the relevant 256 byte section is simply zeroed out. The next step was to split each record up into the fields. 

  TLibraryStud = record
    Name     : array[0..26] of AnsiChar;
    XX       : array[0..1] of AnsiChar;
    Group    : array[0..8] of AnsiChar;
    MaxCount : BYTE;
    xxx      : BYTE;
    Addr     : array[0..29] of AnsiChar;
    City     : array[0..19] of AnsiChar;
    postcode : array[0..5] of AnsiChar;
    Phone    : array[0..11] of AnsiChar;
    Group2   : array[0..1] of AnsiChar;
    Gender   : ansichar;
  end;

After a bit of guessing and trail and error I came up with the record above to hold the data.

Saving Borrower Details to Database

First I created a table called bookmark in a database in SQL Server. This table stored the same info as what was in the bookmark data file. Because the record id’s don’t change, I am able to simply delete all the data in the bookmark table and reload it from the bookmark files. I do this in a transaction, so that if anythings fails, I am able to rollback to the last complete successful import.
Below is the final code to make it work.

procedure TDataModule.LoadBorrowers(path:String);
var
  FromFStream : TFileStream;
  FromF2Stream: TFileStream;
  NumRead,numRead2, NumWritten: Integer;
  str : AnsiString;
  stud : TLibraryStud;
  studCom : TLibraryStCm;
  filename,filename2 : String;
  currentPos : Integer;
  lastPos : Integer;
  i : Integer;
begin
  filename  := IncludeTrailingPathDelimiter(path) +'BORROWER.DAT';
  filename2 := IncludeTrailingPathDelimiter(path) +'BORRCMTS.DAT';

  if(not (FileExists(filename) and FileExists(filename2))) then
    begin
      Exit;
    end;
    FromFStream := TFileStream.Create(filename,fmOpenRead or fmShareDenyNone);
    FromF2Stream := TFileStream.Create(filename2,fmOpenRead or fmShareDenyNone);
  begin
    begin
        lastPos := 0;
    i := 0;

      ADOConnection1.BeginTrans;
      ADOQuery1.SQL.Text := 'DELETE FROM Bookmark;';
      ADOQuery1.ExecSQL;
      ADOQuery1.SQL.Text := 'INSERT INTO Bookmark (tid,Name,Group1,Addr,City,Postcode,Phone,Group2,Gender,AdminID,maxCount) VALUES(:ID,:name,:group1,:addr,:city,:postcode,:phone,:group2,:gender,:adminID,:maxCount);';

    try
      repeat
        FromFStream.ReadBuffer(stud,128);
        FromF2Stream.ReadBuffer(studCom,64);
        if((length(Trim(stud.NAME))<>0) and (i>0)) then
         begin
          ADOQuery1.Parameters.ParamByName('ID').Value := i;
          ADOQuery1.Parameters.ParamByName('name').Value := Trim(stud.NAME);
          ADOQuery1.Parameters.ParamByName('group1').Value := StripNonAscii(Trim(stud.Group));
          ADOQuery1.Parameters.ParamByName('addr').Value := Trim(stud.Addr);
          ADOQuery1.Parameters.ParamByName('city').Value := Trim(stud.City);
          ADOQuery1.Parameters.ParamByName('postcode').Value := Trim(stud.postcode);
          ADOQuery1.Parameters.ParamByName('phone').Value := Trim(stud.Phone);
          ADOQuery1.Parameters.ParamByName('gender').Value := Trim(stud.Gender);
          ADOQuery1.Parameters.ParamByName('group2').Value := Trim(stud.Group2);
          ADOQuery1.Parameters.ParamByName('adminID').Value := StringReplace(Trim(studcom.barcode), '-', '/', [rfReplaceAll, rfIgnoreCase]);
          ADOQuery1.Parameters.ParamByName('maxCount').Value := stud.MaxCount;
          ADOQuery1.ExecSQL; 
         end;
        Inc(i);
      until (FromFStream.Position-FromFStream.Size=0);
    finally
      FreeAndNil(FromFStream);
      FreeAndNil(FromF2Stream);
      ADOConnection1.CommitTrans;
    end;
    end;
  end;
end;

Automating Import

To ensure the data is always up to date in the SQL Server database, as part of the backup process each night, this program is run with Windows Task Scheduler.

Going further

Bookmark has more files that can be imported in a similar way. Files for book records, loan data and book reviews are all there.

Restoring a Vista backup that wouldn’t restore with Windows Backup

Leave a comment

windows backup icon
After completing a backup with the backup program in Vista and then wiping the computer and reinstalling Vista and updating to Service Pack 2 and later updates, I discovered the backup program wouldn’t restore the backup. Fortunately the backup program in Windows Vista as well as the backup program in Windows 7, store all the files in a series of compressed zip files that of about 200Mb in size. Files larger than this a split over multiple files, but not in the standard zip spanning method.

Attempt 1

My first attempt at uncompressing all the files was to use this was to use the 7-zip command line in a batch file with a FOR loop.

@ECHO OFF
REM unzipbackup.bat
c:\Program Files\7-zip\7z e %1 -oc:\output\


@ECHO OFF
REM restoreback.bat
FOR %%G in (*.ZIP) do unzipbackup.bat "%%G"

This worked for the most part – but asked many times to overwrite an existing file. This was obviously not going to be satisfactory for the customer.

Attempt 2

With my next attempt I opened up Embarcadero Delphi XE2, and created a console project. The first step was to get a list of the zip files that made the complete backup. This particular backup contained over 270 zip files. I used FindFirst/FindNext .. FindClose, but this resulted in the files not listed in the correct order. After a initially trying to split the filename up and extract the number part of the filename, a bit of googling turned up a windows API call called StrCmpLogical. Next I tried using the TZip component included with XE2. After trying to extract the files with the component, I found that it was only creating 0 byte files. A bit more googling trying to work out what I was doing wrong turned up a bug report for the component. It had a suggestion to fix the code, but I wasn’t quite sure where to apply it and I wasn’t confident that it would cause corruption of the data anyway.

Attempt 3

Back to google. After some more searching, I settled on TZipMaster. After trying to use the ForEach method of TZipMaster to extract the files, I found that it was doing exactly the same thing as TZip – creating the files but not copying data into them.

 function ForEachFunction(theRec: TZMDirEntry; var Data): Integer;
 var
   iData: Int64 Absolute Data;
   fStream : TFileStream;
 begin
    // code to open filestream and copy data from one stream to the other.
 end;

  ZipMaster.ForEach(ForEachFunction, total);

Final Attempt

Instead of using the ForEach function of TZipMaster, I changed it to just loop through the DirEntry property, which stores info about each file in the zip file. FEfunc1 is called for each file in the zip. This function will create the path that is indicated in the zip file (using ForceDirectories). If the file already exists, the data will be appended to the existing file. This correctly restores the files that span multiple zip files.


 function ForEachFunction(theRec: TZMDirEntry; var Data): Integer;
 var
   iData: Int64 Absolute Data;
   fStream : TFileStream;
 begin
   Result := 0;         // success
   iData := iData + theRec.CompressedSize;
   Writeln(outputPath +theRec.FileName);
   WriteFileLog(outputPath +theRec.FileName);

   if(not FileExists(outputPath +theRec.FileName)) then
     begin
       ForceDirectories ( ExtractFilePath(outputPath +theRec.FileName));
       fStream := TFileStream.Create(outputPath +theRec.FileName,fmCreate);
       try
         theRec.UnzipToStream(fStream);
         WriteFileLog(outputPath +theRec.FileName +' ' + IntToStr(fStream.Size)  + ' ' + IntToStr(theRec.UncompressedSize));
       finally
         FreeAndNil(fStream);
       end;
       FileSetDate(outputPath +theRec.FileName, DateTimeToFileDate(theRec.DateStamp));
     end
   else
     begin
       WriteBigFileLog(outputPath +theRec.FileName);
       fStream := TFileStream.Create(outputPath +theRec.FileName,fmOpenReadWrite);
       try
         fStream.Seek(0,soFromEnd);
         theRec.UnzipToStream(fStream);
       finally
         FreeAndNil(fStream);
       end;
       FileSetDate(outputPath +theRec.FileName, DateTimeToFileDate(theRec.DateStamp));
     end;
 end;

   for i:=0 to ZipMaster1.Count-1 do
     begin
      try
       ForEachFunction(ZipMaster1.DirEntry[i],total);
      except
          on E : Exception do
            WriteErrorLog(ZipMaster1.DirEntry[i].FileName + ' ' + e.Message);
      end;
     end;

Unfortunately, because I had limited time to complete the restore, I wasn’t able to fully complete what would be required create a fully not destructive restore. Some of the files in the zip file actually represent attributes of other files. Also some extra details about the files is also stored in a .wbcat file that is a binary file. But fortunately I had recovered the data and was able to hand the computer back.