DBReaderSdf.pas

unit DBReaderSdf;

(*
SQL Server Compact Edition database file reader

Author: Sergey Bodrov, 2026 Minsk
License: MIT

Data format guessed from sample databases

Fixed page size = $1000
All numbers is little-endian

== Page header
u32 CRC?
u16 PageID
u16 PageType


== Table page (PageType = $40)
-- header (size = 24)
..   page header
$08  u32 ? some incrementing value
..
$10  u16 TableID
$12  u16 ?Partial records info
$14  u8  records count (full records)
$15  u16 records data size ?

-- table rows:
u32 next chunk addr (PageID + rec_no)
u32 columns count
u8  null bitmap (size = columns count / 8)
u8  bool bitmap  (optional)
..  fixed Data
u16 variable col position  (bit $8000 set - single-byte chars)
..
..  variable col data

-- Rows map array of from the end of page
u32 rows map item
  12 bits  offset
  12 bits  size
  8  bits  type = 0-part, 1-empty, 2-rec


== Partial record map page? (PageType = $30)
16b page header
-- 96b blocks
8b block header
   1b items count
8b PageID triplets

*)

interface

uses
  SysUtils, Classes, Types, Variants, DB, DBReaderBase, RFUtils;

type
  TSdfRowID = LongWord;

  TSdfFieldDef = record
    FieldName: string;
    FieldSize: Integer;
    FieldType: Integer;
    TypeName: string;
    FieldNum: Integer;     // ordinal position
    FieldPos: Integer;     // fixed pos / var index
    IsPrimaryKey: Boolean;
    IsNullable: Boolean;
    IsVariable: Boolean;
  end;

  { TSdfTableInfo }

  TSdfTableInfo = class(TDbRowsList)
  public
    TableID: Integer;
    RowCount: Integer;
    ColCount: Integer;
    VarCount: Integer;   // variable length fields count
    NullCount: Integer;  // nullable fields count
    BoolCount: Integer;  // boolean fields count
    NullSize: Integer;   // null bitmap size in bytes
    FixedSize: Integer;  // fixed-size part of row / variable part offset
    PageIdArr: array of Integer;
    PageIdCount: Integer;
    FieldInfoArr: array of TSdfFieldDef;
    TableSQL: string;
    // contain no rows
    function IsEmpty(): Boolean; override;
    // predefined table
    function IsSystem(): Boolean; override;
    // not defined in metadata
    function IsGhost(): Boolean; override;

    // set table metadata from SQL string
    procedure SetSQL(ASql: string);
    // AType - "TypeName(Size)"
    procedure AddFieldDef(AName, AType: string; ANum: Integer = -1; APos: Integer = -1);
    procedure AddPageID(APageID: Cardinal);
    // calculate fixed part size and var/null/bool counts from columns definitions
    procedure UpdateSizes();
  end;

  TSdfTableInfoList = class(TList)
  protected
    procedure Notify(Ptr: Pointer; Action: TListNotification); override;
  public
    function GetItem(AIndex: Integer): TSdfTableInfo;
    function GetByName(AName: string): TSdfTableInfo;
    function GetByTableID(ATabID: Cardinal): TSdfTableInfo;
    procedure SortByName();
  end;

  TSdfPageItem = class(TObject)
  public
    PageNo: Integer;
    PageID: Integer;
    PageType: Word;
  end;

  { TSdfPageItemList }

  TSdfPageItemList = class(TList)
  protected
    procedure Notify(Ptr: Pointer; Action: TListNotification); override;
  public
    function GetItem(AIndex: Integer): TSdfPageItem;
    procedure SortItems();
    // find page by PageID. If Result <0 it position for insert
    function FindPageIndex(APageID: Integer): Integer;
  end;

  { TDBReaderSdf }

  TDBReaderSdf = class(TDBReader)
  private
    FTableList: TSdfTableInfoList;
    FPagesList: TSdfPageItemList;
    //FDbVersion: Integer;
    FPageSize: Integer;
    FIsMetadataLoaded: Boolean;
    FPagesInfo: TSdfTableInfo;

    // fill schema tables from initial tables
    procedure FillTablesList();

    function ReadDataPage(var APageBuf: TByteDynArray; APagePos: Int64;
      ATableInfo: TSdfTableInfo; AList: TDbRowsList): Boolean;
    function ReadRowData(const APageBuf: TByteDynArray; ARowOffs, ARowSize: Integer;
      ATableInfo: TSdfTableInfo; AList: TDbRowsList): Boolean;

    function ReadNumeric(var rdr: TRawDataReader): string;

    // find row and read data, return next RowID or 1 on error
    function GetRowData(ARowID: TSdfRowID; var AData: AnsiString): TSdfRowID;

    // decode blob info and write to AData
    function GetBlobData(ABlobInfo: AnsiString; var AData: AnsiString): Boolean;
    // read data from blob page
    function GetBlobPageData(APageID, ASize: Integer; var AData: AnsiString): Boolean;
  public
    LocaleID: Integer;
    VerHi: Word;
    VerLo: Word;

    procedure AfterConstruction(); override;
    procedure BeforeDestruction(); override;

    function OpenFile(AFileName: string; AStream: TStream = nil): Boolean; override;
    // Read table data from DB to AList
    // AName - table name
    // ACount - how many items read
    procedure ReadTable(AName: string; ACount: Int64 = MaxInt; AList: TDbRowsList = nil); override;
    // get detailed multi-line description of table
    function FillTableInfoText(ATableName: string; ALines: TStrings): Boolean; override;

    // get tables count
    function GetTablesCount(): Integer; override;
    // get table by index 0..GetTablesCount()-1
    function GetTableByIndex(AIndex: Integer): TDbRowsList; override;

    property TableList: TSdfTableInfoList read FTableList;
  end;


implementation

const
  SDF_PAGE_HEAD_SIZE    = 24;
  SDF_ROW_HEAD_SIZE     = 8;
  SDF_TABLE_ID_SYS      = 1027; // __SysObjects

  SDF_PAGE_TYPE_FILE    = $00;  // file header
  SDF_PAGE_TYPE_UNKN_1  = $10;  // unknown_1
  SDF_PAGE_TYPE_UNKN_2  = $20;  // ? 8b records,
  SDF_PAGE_TYPE_PARTIAL = $30;  // ? partial records map?
  SDF_PAGE_TYPE_TABLE   = $40;  // table data
  SDF_PAGE_TYPE_DATA    = $50;  // ? blobs
  SDF_PAGE_TYPE_UNKN_6  = $60;  // ? pages btree 6b records ; tail = 2b addr

  {
  rowversion        ---
  ubigint           DBTYPE_UI8
  tinyint           DBTYPE_UI1
  ulong             DBTYPE_UI4
  ushort            DBTYPE_UI2
  }

  SDF_COL_TYPE_UNKNOWN  = 0;
  SDF_COL_TYPE_SMALLINT = 1;   // 2  smallint          DBTYPE_I2
  SDF_COL_TYPE_INT      = 3;   // 4  int               DBTYPE_I4
  SDF_COL_TYPE_BIGINT   = 5;   // 8  bigint            DBTYPE_I8
  SDF_COL_TYPE_NCHAR    = 7;   //    nchar             DBTYPE_WCHAR
  SDF_COL_TYPE_NVARCHAR = 8;   //    nvarchar          DBTYPE_WVARCHAR
  SDF_COL_TYPE_NTEXT    = 9;   // 8  ntext             DBTYPE_WLONGVARCHAR
  SDF_COL_TYPE_BINARY   = 10;  // ?? binary            DBTYPE_BINARY
  SDF_COL_TYPE_VARBIN   = 11;  // ?? varbinary         DBTYPE_VARBINARY
  SDF_COL_TYPE_IMAGE    = 12;  //    image             DBTYPE_LONGVARBINARY
  SDF_COL_TYPE_DATETIME = 13;  // 8  datetime          DBTYPE_DBTIMESTAMP
  SDF_COL_TYPE_GUID     = 14;  // 16 uniqueidentifier  DBTYPE_GUID
  SDF_COL_TYPE_BOOLEAN  = 15;  // 2  bit               DBTYPE_BOOL
  SDF_COL_TYPE_REAL     = 16;  // 4  real              DBTYPE_R4
  SDF_COL_TYPE_FLOAT    = 17;  // 8  float             DBTYPE_R8
  SDF_COL_TYPE_MONEY    = 18;  // 8  money             DBTYPE_CY
  SDF_COL_TYPE_NUMERIC  = 19;  // 19 numeric           DBTYPE_NUMERIC
  SDF_COL_TYPE_VARCHAR  = $FC; // zero-terminated varchar (for metadata)
  SDF_COL_TYPE_DEBUG    = $FE; // debug string

  SDF_COL_FLAG_UNSIGNED = $08; // unsigned?   DateTime
  SDF_COL_FLAG_NULLABLE = $10; // nullable
  SDF_COL_FLAG_VARIABLE = $20; // variable length

  SDF_COL_MAX_LENGTH    = 8000;  // variable length not specified = max for data type

type
  TSdfFileHeader = record
    //HeaderStr: array [0..$150] of AnsiChar;  //
    PageSize: Integer;                        // The database page size in bytes.
    // 150 bytes
  end;

  TSdfRecMapItem = record
    Offs: Word;
    Size: Word;
    Flag: Word;  // 0-part, 1-empty, 2-rec
  end;

  TSdfPageHead = record
    PageID: Integer;
    PageType: Word;       //
    SomeSize: Word;
    TableID: Word;
    RecCount: Word;
    RawData: string;
  end;

function ColTypeToStr(AType: Integer): string;
begin
  case AType of
    SDF_COL_TYPE_SMALLINT: Result := 'SMALLINT';
    SDF_COL_TYPE_INT:      Result := 'INT';
    SDF_COL_TYPE_BIGINT:   Result := 'BIGINT';
    SDF_COL_TYPE_NUMERIC:  Result := 'NUMERIC';
    SDF_COL_TYPE_NCHAR:    Result := 'NCHAR';
    SDF_COL_TYPE_NVARCHAR: Result := 'NVARCHAR';
    SDF_COL_TYPE_NTEXT:    Result := 'NTEXT';
    SDF_COL_TYPE_BINARY:   Result := 'BINARY';
    SDF_COL_TYPE_VARBIN:   Result := 'VARBINARY';
    SDF_COL_TYPE_IMAGE:    Result := 'IMAGE';
    SDF_COL_TYPE_DATETIME: Result := 'DATETIME';
    SDF_COL_TYPE_GUID:     Result := 'GUID';
    SDF_COL_TYPE_BOOLEAN:  Result := 'BOOLEAN';
    SDF_COL_TYPE_REAL:     Result := 'REAL';
    SDF_COL_TYPE_FLOAT:    Result := 'FLOAT';
    SDF_COL_TYPE_MONEY:    Result := 'MONEY';
    SDF_COL_TYPE_VARCHAR:  Result := 'VARCHAR';
    SDF_COL_TYPE_DEBUG:    Result := 'DEBUG';
  else
    Result := 'UNKNOWN_'+IntToStr(AType);
  end;
end;

function SdfColTypeToInt(AType: string): Integer;
var
  s: string;
  n: Integer;
begin
  //s := ExtractFirstWord(AType);
  n := 1;
  while n <= Length(AType) do
  begin
    if AType[n] in [' ', '('] then
      Break;
    Inc(n);
  end;
  s := Copy(AType, 1, n-1);

  s := UpperCase(s);
  if s = 'NULL' then Result := SDF_COL_TYPE_UNKNOWN
  else if s = 'INTEGER' then Result := SDF_COL_TYPE_INT
  else if s = 'INT' then Result := SDF_COL_TYPE_INT
  else if s = 'UINT' then Result := SDF_COL_TYPE_INT
  else if s = 'INT8' then Result := SDF_COL_TYPE_INT
  else if s = 'TINYINT' then Result := SDF_COL_TYPE_INT
  else if s = 'SMALLINT' then Result := SDF_COL_TYPE_INT
  else if s = 'MEDIUMINT' then Result := SDF_COL_TYPE_INT
  else if s = 'BIGINT' then Result := SDF_COL_TYPE_INT
  //else if s = 'TEXT' then Result := SDF_COL_TYPE_TEXT
  //else if s = 'CHAR' then Result := SDF_COL_TYPE_CHAR
  else if s = 'NCHAR' then Result := SDF_COL_TYPE_NCHAR
  else if s = 'VARCHAR' then Result := SDF_COL_TYPE_VARCHAR
  else if s = 'NVARCHAR' then Result := SDF_COL_TYPE_NVARCHAR
  else if s = 'NTEXT' then Result := SDF_COL_TYPE_NTEXT
  else if s = 'BINARY' then Result := SDF_COL_TYPE_BINARY
  else if s = 'VARBINARY' then Result := SDF_COL_TYPE_VARBIN
  else if s = 'IMAGE' then Result := SDF_COL_TYPE_IMAGE
  else if s = 'BLOB' then Result := SDF_COL_TYPE_IMAGE
  else if s = 'REAL' then Result := SDF_COL_TYPE_REAL
  else if s = 'FLOAT' then Result := SDF_COL_TYPE_FLOAT
  else if s = 'MONEY' then Result := SDF_COL_TYPE_MONEY
  else if s = 'NUMERIC' then Result := SDF_COL_TYPE_NUMERIC
  else if s = 'DATE' then Result := SDF_COL_TYPE_DATETIME
  else if s = 'DATETIME' then Result := SDF_COL_TYPE_DATETIME
  else if s = 'GUID' then Result := SDF_COL_TYPE_GUID
  else if s = 'BOOLEAN' then Result := SDF_COL_TYPE_BOOLEAN
  else if s = 'DEBUG' then Result := SDF_COL_TYPE_DEBUG
  else Result := SDF_COL_TYPE_UNKNOWN;
end;

function SdfColTypeToDbFieldType(AType: string): TFieldType;
var
  s: string;
  n: Integer;
begin
  //s := ExtractFirstWord(AType);
  n := 1;
  while n <= Length(AType) do
  begin
    if AType[n] in [' ', '('] then
      Break;
    Inc(n);
  end;
  s := Copy(AType, 1, n-1);

  s := UpperCase(s);
  if s = 'NULL' then Result := ftUnknown
  else if s = 'INTEGER' then Result := ftLargeint
  else if s = 'INT' then Result := ftInteger
  else if s = 'UINT' then Result := ftInteger
  else if s = 'INT8' then Result := ftInteger
  else if s = 'TINYINT' then Result := ftInteger
  else if s = 'SMALLINT' then Result := ftSmallint
  else if s = 'MEDIUMINT' then Result := ftInteger
  else if s = 'BIGINT' then Result := ftLargeint
  else if s = 'VARCHAR' then Result := ftString
  else if s = 'NVARCHAR' then Result := ftString
  else if s = 'CHAR' then Result := ftString
  else if s = 'NCHAR' then Result := ftString
  else if s = 'CHARACTER' then Result := ftString
  else if s = 'TEXT' then Result := ftString
  else if s = 'NTEXT' then Result := ftString
  else if s = 'BINARY' then Result := ftBytes  //ftBlob
  else if s = 'VARBINARY' then Result := ftBlob  //ftBlob
  else if s = 'IMAGE' then Result := ftBlob  //ftGraphic
  else if s = 'CLOB' then Result := ftMemo
  else if s = 'BLOB' then Result := ftBlob
  else if s = 'REAL' then Result := ftFloat
  else if s = 'DOUBLE' then Result := ftFloat
  else if s = 'FLOAT' then Result := ftFloat
  else if s = 'MONEY' then Result := ftCurrency
  else if s = 'NUMERIC' then Result := ftFloat
  else if s = 'DATE' then Result := ftDate
  else if s = 'DATETIME' then Result := ftDateTime
  else if s = 'GUID' then Result := ftGuid
  else if s = 'BOOLEAN' then Result := ftBoolean
  else if s = 'FIXED' then Result := ftBytes
  else if s = 'DEBUG' then Result := ftString
  else Result := ftUnknown;
end;

function IsNullValue(const ABitmap: TByteDynArray; AIndex: Integer): Boolean;
var
  NullFlagIndex, NullFlagOffs: Integer;
begin
  //NullFlagIndex := Length(ABitmap) - (AIndex div 8) - 1;
  NullFlagIndex := (AIndex div 8);
  NullFlagOffs := AIndex mod 8;
  if (NullFlagIndex < Length(ABitmap)) and (NullFlagIndex >= 0) then
    Result := (ABitmap[NullFlagIndex] and (Byte(1) shl NullFlagOffs)) <> 0
    //Result := (ABitmap[NullFlagIndex] and (Byte($80) shr NullFlagOffs)) = 0
  else
    Result := False;
end;

function GetBoolValue(const ABitmap: TByteDynArray; AIndex: Integer): Boolean;
var
  FlagIndex, FlagOffs: Integer;
begin
  FlagIndex := Length(ABitmap) - (AIndex div 8) - 1;
  //FlagIndex := (AIndex div 8);
  FlagOffs := AIndex mod 8;
  if (FlagIndex < Length(ABitmap)) and (FlagIndex >= 0) then
    Result := (ABitmap[FlagIndex] and (Byte(1) shl FlagOffs)) <> 0
    //Result := (ABitmap[FlagIndex] and (Byte($80) shr FlagOffs)) = 0
  else
    Result := False;
end;

{ TDBReaderSdf }

procedure TDBReaderSdf.AfterConstruction;
var
  TmpTable: TSdfTableInfo;
begin
  inherited;
  FIsSingleTable := False;
  FTableList := TSdfTableInfoList.Create();
  FPagesList := TSdfPageItemList.Create();

  // sys_pages
  TmpTable := TSdfTableInfo.Create();
  TmpTable.TableID := -1;
  TmpTable.SetSQL('CREATE TABLE sys_pages('
    + ' PageNo INTEGER, '     // [0]
    + ' PagePos INTEGER, '    // [1]
    + ' PageType INTEGER, '   // [2]
    + ' PageID INTEGER, '     // [3]
    + ' SomeSize INTEGER, '   // [4]
    + ' RecCount INTEGER, '   // [5]
    + ' TableID INTEGER, '    // [6]
    + ' Info TEXT, '          // [7]
    + ' RawHeader TEXT(240));'); // [8]
  TableList.Add(TmpTable);
  FPagesInfo := TmpTable;

  // __SysObjects
  TmpTable := TSdfTableInfo.Create();
  TmpTable.TableID := SDF_TABLE_ID_SYS;
  TmpTable.RowCount := -1;
  TmpTable.SetSQL('CREATE TABLE __SysObjects('
  //+ ' ref_flags1 INT(1) NOT NULL,'      // =C0 - name_4, name_5 (uq)
  //+ ' ref_flags2 UINT(1) NOT NULL,'   // =F6 - name_6 (pk/uq name)

  + ' col_flags UINT(2) NOT NULL,'    // SDF_COL_FLAG_
  + ' obj_type INT(2) NOT NULL,'  // 1-table, 2-key?, 4-column, 8-relation?
  + ' int_2 INT(2),'
  + ' int_3 INT(4),'
  + ' ObjN1 UINT(1),'
  + ' ObjN2 UINT(1),'
  + ' col_id INT(2),'      // 0..  also for idx/PK/UQ
  + ' col_flags_2 INT(2),'
  + ' ordinal_position INT(2),'      // 1..  column ordinal position
  + ' tab_id INT(2),'
  + ' int_10 INT(2),'
  + ' int_11 INT(2),'
  + ' int_12 INT(2),'
  + ' int_13 INT(2),'
  + ' int_14 INT(2),'
  + ' int_15 INT(2),'
  + ' data_type INT(2),'
  + ' data_size INT(2),'
  + ' small_18 INT(2),'
  + ' small_19 INT(2),'
  + ' col_pos INT(2),'      // fixed pos / var index
  + ' index_id INT(4),'
  + ' int_22 INT(2),'
  + ' int_23 INT(4),'
  + ' int_24 INT(4),'
  + ' int_25 INT(4),'
  + ' table_name VARCHAR,'
  + ' column_name VARCHAR,'
  + ' column_default VARCHAR,'
  + ' key_flags VARBINARY,'   // col_type=2  idx/PK/UQ      20 bytes bitmaps ?
  + ' key_content IMAGE,'     // col_type=2  idx/PK/UQ
  + ' refer_name VARCHAR,'    // col_type=8  (name of idx/PK/UQ field)
  + ' rel_name NVARCHAR,'     // col_type=8
  + ' rel_tab_name NVARCHAR,' // col_type=8
  + ' rel_flags VARBINARY,'   // col_type=8  flags?
  + ' default_name NVARCHAR,' // col_type=8  DF_ field  (name of field with default value)
  + ' info DEBUG'
  + ');');
  TableList.Add(TmpTable);

end;

procedure TDBReaderSdf.BeforeDestruction;
begin
  FreeAndNil(FPagesList);
  FreeAndNil(FTableList);
  inherited;
end;

function TDBReaderSdf.FillTableInfoText(ATableName: string; ALines: TStrings): Boolean;
var
  i: Integer;
  TmpTable: TSdfTableInfo;
  s: string;
begin
  Result := False;
  TmpTable := TableList.GetByName(ATableName);
  if not Assigned(TmpTable) then
    Exit;

  ALines.Add(Format('== Table Name=%s  RowCount=%d', [TmpTable.TableName, TmpTable.RowCount]));
  ALines.Add(Format('TableID=%x', [TmpTable.TableID]));
  ALines.Add(Format('ColCount=%d', [TmpTable.ColCount]));
  ALines.Add(Format('VarCount=%d', [TmpTable.VarCount]));
  ALines.Add(Format('NullCount=%d', [TmpTable.NullCount]));
  ALines.Add(Format('BoolCount=%d', [TmpTable.BoolCount]));
  ALines.Add(Format('FixedSize=%d', [TmpTable.FixedSize]));

  // PageNum array
  s := '';
  for i := Low(TmpTable.PageIdArr) to High(TmpTable.PageIdArr) do
    s := s + Format('%x,', [TmpTable.PageIdArr[i]]);
  //Delete(s, Length(s)-1, 1);
  ALines.Add(Format('PageNum[%d]=(%s)', [TmpTable.PageIdCount, s]));
  // SQL can be multiline
  ALines.Add(Format('TableSQL=%s', [TmpTable.TableSQL]));

  ALines.Add(Format('== FieldInfo  Count=%d', [Length(TmpTable.FieldInfoArr)]));
  for i := Low(TmpTable.FieldInfoArr) to High(TmpTable.FieldInfoArr) do
  begin
    s := Format('%.2d Name=%-20s  Type=%s  Size=%d  Num=%d  Pos=%d',
      [i,
        TmpTable.FieldInfoArr[i].FieldName,
        TmpTable.FieldInfoArr[i].TypeName,
        TmpTable.FieldInfoArr[i].FieldSize,
        TmpTable.FieldInfoArr[i].FieldNum,
        TmpTable.FieldInfoArr[i].FieldPos
      ]);
    ALines.Add(s);
  end;
end;

procedure TDBReaderSdf.FillTablesList;
var
  TmpTable, SysTable: TSdfTableInfo;
  SysRow: TDbRowItem;
  i: Integer;
  TabID: Integer;
  ObjType, iNum, iPos: Integer;
  wFlag: Word;
  sName, sType, sSize: string;
begin
  FIsMetadataLoaded := True;
  // normalize page num arrays
  for i := 0 to TableList.Count - 1 do
  begin
    TmpTable := TableList.GetItem(i);
    SetLength(TmpTable.PageIdArr, TmpTable.PageIdCount);
  end;

  // read schema table
  ReadTable('__SysObjects');

  // set tables from schema
  SysTable := TableList.GetByTableID(SDF_TABLE_ID_SYS);
  if Assigned(SysTable) then
  begin
    // -- tables
    for i := 0 to SysTable.Count - 1 do
    begin
      SysRow := SysTable.GetItem(i);
      ObjType := SysRow.Values[1]; // obj_type
      if ObjType <> 1 then
        Continue;

      TabID := SysRow.Values[9]; // tab_id
      TmpTable := TableList.GetByTableID(TabID);
      if Assigned(TmpTable) then
      begin
        sName := VarToStrDef(SysRow.Values[27], ''); // column_name
        TmpTable.TableName := VarToStrDef(sName, TmpTable.TableName);
        TmpTable.TableSQL := 'CREATE TABLE';
        TmpTable.Clear();

        // columns
        TmpTable.ColCount := 0;
        TmpTable.VarCount := 0;
        TmpTable.NullCount := 0;
        TmpTable.BoolCount := 0;

        SetLength(TmpTable.FieldsDef, 0);
        SetLength(TmpTable.FieldInfoArr, 0);
      end;
    end;

    // -- columns
    for i := 0 to SysTable.Count - 1 do
    begin
      SysRow := SysTable.GetItem(i);
      ObjType := SysRow.Values[1]; // obj_type
      if ObjType <> 4 then
        Continue;

      sName := VarToStrDef(SysRow.Values[26], ''); // table_name
      TmpTable := TableList.GetByName(sName);
      if Assigned(TmpTable) then
      begin

        sName := VarToStrDef(SysRow.Values[27], ''); // column_name
        sType := ColTypeToStr(VarToInt(SysRow.Values[16])); // data_type
        sSize := IntToStr(VarToInt(SysRow.Values[17])); // data_size
        iNum  := VarToInt(SysRow.Values[6]); // col_id
        iPos  := VarToInt(SysRow.Values[20]); // col_pos
        wFlag := VarToInt(SysRow.Values[0]); // col_flags
        sType := sType + '(' + sSize + ')';
        {if VarToStrDef(ColRow.Values[2], 'N') = 'Y' then
          sType := sType + ' PRIMARY KEY';  }

        if (wFlag and SDF_COL_FLAG_NULLABLE) = 0 then
          sType := sType + ' NOT NULL';

        TmpTable.AddFieldDef(sName, sType, iNum, iPos);
      end;
    end;
  end;

  // update tables fixed size
  for i := 0 to TableList.Count - 1 do
  begin
    TmpTable := TableList.GetItem(i);
    TmpTable.UpdateSizes();
  end;

end;

function TDBReaderSdf.GetTableByIndex(AIndex: Integer): TDbRowsList;
begin
  Result := TableList[AIndex];
end;

function TDBReaderSdf.GetTablesCount: Integer;
begin
  Result := TableList.Count;
end;

function TDBReaderSdf.OpenFile(AFileName: string; AStream: TStream): Boolean;
var
  PageBuf: TByteDynArray;
  rdr: TRawDataReader;
  iPagePos: Int64;
  iCurPageID: Cardinal;
begin
  Result := inherited OpenFile(AFileName, AStream);
  if not Result then Exit;
  Result := False;
  FPageSize := $1000;

  // read header
  SetLength(PageBuf, FPageSize);
  FFile.Position := 0;
  FFile.Read(PageBuf[0], FPageSize);
  rdr.Init(PageBuf[0], False);

  // $0028  Locale Identifier
  rdr.SetPosition($28);
  LocaleID := rdr.ReadInt32;
  // $0030  i16  Major version
  rdr.SetPosition($30);
  VerHi := rdr.ReadUInt16;
  // $0032  i16  Minor version
  VerLo := rdr.ReadUInt16;

  LogInfo(Format('SDF Version: %d.%d  Locale: %d', [VerHi, VerLo, LocaleID]));

  // scan pages
  FPagesList.Clear();
  iCurPageID := 0;
  iPagePos := 0;
  while iPagePos + FPageSize <= FFile.Size do
  begin
    FFile.Position := iPagePos;
    FFile.Read(PageBuf[0], FPageSize);
    rdr.Init(PageBuf[0], False);

    ReadDataPage(PageBuf, iPagePos, nil, nil);

    Inc(iCurPageID);
    iPagePos := iCurPageID * FPageSize;

    if Assigned(OnPageReaded) then
      OnPageReaded(Self);
  end;
  FPagesList.SortItems();

  SetLength(PageBuf, 0);

  FillTablesList();
end;

function TDBReaderSdf.ReadDataPage(var APageBuf: TByteDynArray; APagePos: Int64;
  ATableInfo: TSdfTableInfo; AList: TDbRowsList): Boolean;
var
  rdr: TRawDataReader;
  PageHead: TSdfPageHead;
  //RecOffsArr: array of Word;
  RecMapArr: array of TSdfRecMapItem;
  i, n, iRowOffs, iCount1, iCount2, iRowSize: Integer;
  bt: Byte;
  i64: Int64;
  CurPageID, nItem: Cardinal;
  NextRowID: TSdfRowID;
  TmpPageItem: TSdfPageItem;
  TmpRow: TDbRowItem;
  sPageHead, sPageInfo: string;
  RowData: AnsiString;  // array of Byte;
  RowDataBuf: TByteDynArray;
begin
  Result := False;
  CurPageID := (APagePos div FPageSize);
  rdr.Init(APageBuf[0], False);

  {!!
  if APagePos = $2000 then
  begin
    sPageHead := rdr.ReadBytes($1000);
    StrToFile(sPageHead, Format('$%.4x.raw', [APagePos]));
  end;
  }


  // page header
  rdr.ReadUInt32;   // CRC??
  PageHead.PageID := rdr.ReadUInt16;
  PageHead.PageType := rdr.ReadUInt16;
  PageHead.SomeSize := rdr.ReadUInt16;
  //PageHead.RecCount := rdr.ReadUInt16;
  rdr.SetPosition($10);
  PageHead.TableID := rdr.ReadUInt16;
  rdr.SetPosition($14);
  PageHead.RecCount := rdr.ReadUInt8;

  // add to pages map
  if (not FIsMetadataLoaded) then
  begin
    TmpPageItem := TSdfPageItem.Create();
    TmpPageItem.PageNo := CurPageID;
    TmpPageItem.PageID := PageHead.PageID;
    TmpPageItem.PageType := PageHead.PageType;
    FPagesList.Add(TmpPageItem);
  end;

  sPageInfo := '';
  //SetLength(RecMapArr, PageHead.RecCount);
  SetLength(RecMapArr, 0);

  if PageHead.PageType = SDF_PAGE_TYPE_TABLE then
  begin
    // read tail records (rec offs list)
    SetLength(RecMapArr, $100);
    i := 0;
    n := 0;
    while i < $100 do
    begin
      Inc(i);
      rdr.SetPosition(FPageSize - (i*4));
      nItem := rdr.ReadUInt32;

      RecMapArr[n].Offs := (nItem and $FFF);
      RecMapArr[n].Size := (nItem and $FFF000) shr 12;
      RecMapArr[n].Flag := (nItem and $FF000000) shr 24;

      sPageInfo := sPageInfo + Format('%x:%x:%x, ', [
        RecMapArr[n].Offs,
        RecMapArr[n].Size,
        RecMapArr[n].Flag
      ]);
      if (RecMapArr[n].Offs = 0) and (RecMapArr[n].Size = 0) and (RecMapArr[n].Flag = 0) then
        Break;
      Inc(n);
    end;
    //PageHead.RecCount := n;
    SetLength(RecMapArr, n);
  end
  else
  if PageHead.PageType = SDF_PAGE_TYPE_PARTIAL then
  begin
    iCount1 := 0;
    iCount2 := 0;
    i := 0;
    while i < $10 do
    begin
      // $10 page header
      // $60b blocks
      rdr.SetPosition($10 + (i * $60));
      // block header
      i64 := rdr.ReadInt64;
      bt := Byte(i64);
      if (i64 and $FFFFFFFFFFFFFF00) <> 0 then
        sPageInfo := sPageInfo + IntToHex(i64, 16) + ' ';
      if (bt > 0) and (bt < $80) then
      begin
        Inc(iCount1);
        n := (bt + 2) div 3;  // 8b parts
        while n > 0 do
        begin
          Dec(n);
          iRowOffs := rdr.ReadUInt16;
          iRowSize := rdr.ReadUInt16;
          nItem := rdr.ReadUInt32;
          if (nItem = 0) and (iRowOffs = 0) and (iRowSize = 0) then
            Break;

          Inc(iCount2);
          sPageInfo := sPageInfo + Format('%.2x  %x:%x:%.8x', [bt, iRowOffs,iRowSize,nItem]) + sLineBreak;
        end;
      end;
      Inc(i);
    end;
    sPageInfo := Format('partial refs  blocks=%d  items=%d %s', [iCount1, iCount2, sLineBreak + sPageInfo]);
  end
  else
  if PageHead.PageType = SDF_PAGE_TYPE_UNKN_2 then
  begin
    i := 0;
    while i < $10 do
    begin
      rdr.SetPosition(SDF_PAGE_HEAD_SIZE + (i*8)); // ? -8
      i64 := rdr.ReadInt64;

      if (i64 = 0) then
        Break;

      sPageInfo := sPageInfo + Format('%.16x, ', [i64]);
      Inc(i);
    end;

  end;

  TmpRow := nil;
  sPageHead := '';
  if IsDebugPages and (not FIsMetadataLoaded) then
  begin
    // raw data
    rdr.SetPosition(0);
    PageHead.RawData := rdr.ReadBytes(32);

    // sys_pages
    TmpRow := TDbRowItem.Create(FPagesInfo);
    SetLength(TmpRow.Values, Length(FPagesInfo.FieldsDef));
    FPagesInfo.Add(TmpRow);

    rdr.SetPosition(0);
    TmpRow.RawData := rdr.ReadBytes(128);

    TmpRow.Values[0] := CurPageID;
    TmpRow.Values[1] := APagePos;
    TmpRow.Values[2] := PageHead.PageType;
    TmpRow.Values[3] := PageHead.PageID;
    TmpRow.Values[4] := PageHead.SomeSize;
    TmpRow.Values[5] := PageHead.RecCount;
    TmpRow.Values[6] := PageHead.TableID;

    // records
    TmpRow.Values[7] := sPageInfo;   // info

    sPageHead := BufToHex(PageHead.RawData[1], 4)
      + ' ' + BufToHex(PageHead.RawData[5], 2)  // page id
      + ' ' + BufToHex(PageHead.RawData[7], 2)  // page type
      + ' ' + BufToHex(PageHead.RawData[9], 2)
      + ' ' + BufToHex(PageHead.RawData[11], 4)
      + ' ' + BufToHex(PageHead.RawData[15], 2)
      + ' ' + BufToHex(PageHead.RawData[17], 2)
      + ' ' + BufToHex(PageHead.RawData[19], 2)
      + ' ' + BufToHex(PageHead.RawData[21], 4);
    TmpRow.Values[8] := sPageHead;
  end;

  // read metadata
  if (PageHead.PageType = SDF_PAGE_TYPE_TABLE) then
  begin
    // find/add table for page
    if not Assigned(ATableInfo) then
      ATableInfo := TableList.GetByTableID(PageHead.TableID);

    if (not FIsMetadataLoaded) then
    begin
      if not Assigned(ATableInfo) then
      begin
        ATableInfo := TSdfTableInfo.Create();
        ATableInfo.TableID := PageHead.TableID;
        ATableInfo.TableName := Format('Table_%x', [PageHead.TableID]);
        ATableInfo.RowCount := PageHead.RecCount;
        TableList.Add(ATableInfo);
      end;

      ATableInfo.AddPageID(CurPageID);
    end;

    if Assigned(ATableInfo) and FIsMetadataLoaded then
    begin
      for i := Low(RecMapArr) to High(RecMapArr) do
      begin
        if (RecMapArr[i].Flag = 2)
        and (RecMapArr[i].Size > 0)
        and (RecMapArr[i].Size < FPageSize)
        and (RecMapArr[i].Offs < FPageSize)
        then
        begin
          rdr.SetPosition(RecMapArr[i].Offs + SDF_PAGE_HEAD_SIZE);
          NextRowID := rdr.ReadUInt32;
          if NextRowID = 0 then
            // full record
            ReadRowData(APageBuf, RecMapArr[i].Offs + SDF_PAGE_HEAD_SIZE, RecMapArr[i].Size, ATableInfo, AList)
          else
          begin
            // partial record
            // read head part
            rdr.SetPosition(RecMapArr[i].Offs + SDF_PAGE_HEAD_SIZE);
            RowData := rdr.ReadBytes(RecMapArr[i].Size);
            // read next part
            while NextRowID > 1 do
              NextRowID := GetRowData(NextRowID, RowData);

            if NextRowID = 0 then
            begin
              // AnsiString -> array of byte
              SetLength(RowDataBuf, Length(RowData));
              Move(RowData[1], RowDataBuf[0], Length(RowData));
              // restored record
              ReadRowData(RowDataBuf, 0, Length(RowData), ATableInfo, AList);
            end
            else
            begin
              LogInfo(Format('!Not readed partial record at %x:%x(%x)', [APagePos, i, RecMapArr[i].Size]));
            end;
          end;
        end;

        if (RecMapArr[i].Flag = 0)
        and (RecMapArr[i].Size > 0)
        and (RecMapArr[i].Size < FPageSize)
        and (RecMapArr[i].Offs < FPageSize)
        then
        begin
          //LogInfo('');
          //LogInfo(Format('!Part at %x:%x(%x)', [APagePos, i, RecMapArr[i].Size]));
          //LogInfo(BufferToHex(APageBuf[RecMapArr[i].Offs + SDF_PAGE_HEAD_SIZE], RecMapArr[i].Size));
        end;

      end;
    end;
  end;

  Result := True;
end;

function TDBReaderSdf.ReadNumeric(var rdr: TRawDataReader): string;
var
  btInt, btFrac, btSign: Byte;
  i64: Int64;
begin
  //<digits before dot?> <digits after dot> <sign?> <16 num>
  // 16 00 01  CA 96 69 1F 00 00 00 00 00 00 00 00 00 00 00 00 = 527013578 = 1F6996CA
  // 06 03 01  C0 12 00 00 00 00 00 00 00 00 00 00 00 00 00 00 = 4.8 (4800)
  btInt := rdr.ReadUInt8;
  btFrac := rdr.ReadUInt8;
  btSign := rdr.ReadUInt8;
  i64 := rdr.ReadInt64;
  rdr.ReadInt64;

  Result := IntToStr(i64);
  if (btFrac > 0) and (i64 <> 0) then
  begin
    Insert('.', Result, Length(Result) - btFrac + 1);
    //while Copy(Result, Length(Result), 1) = '0' do
    //  Result := Copy(Result, 1, Length(Result)-1);
  end;
  if btSign = 0 then
    Insert('-', Result, 1);
end;

function TDBReaderSdf.GetRowData(ARowID: TSdfRowID; var AData: AnsiString): TSdfRowID;
var
  PageBuf: TByteDynArray;
  rdr: TRawDataReader;
  PageItem: TSdfPageItem;
  i, n, iPageID, iRow: Integer;
  RecMapArr: array of TSdfRecMapItem;
  nItem: Cardinal;
begin
  Result := 1;
  iPageID := (ARowID and $FFFFF000) shr 12;
  iRow := (ARowID and $FF);

  // find page
  n := FPagesList.FindPageIndex(iPageID);
  if n < 0 then
  begin
    LogInfo(Format('!Not found PageID %x', [iPageID]));
    Exit;
  end;
  PageItem := FPagesList.GetItem(n);

  // read page to buf
  SetLength(PageBuf, FPageSize);
  FFile.Position := (PageItem.PageNo * FPageSize);
  FFile.Read(PageBuf[0], FPageSize);
  rdr.Init(PageBuf[0], False);

  // todo: read page header, check page type

  // read tail records (rec offs list)
  SetLength(RecMapArr, $100);
  i := 0;
  n := 0;
  while i < $100 do
  begin
    Inc(i);
    rdr.SetPosition(FPageSize - (i*4));
    nItem := rdr.ReadUInt32;

    RecMapArr[n].Offs := (nItem and $00000FFF);
    RecMapArr[n].Size := (nItem and $00FFF000) shr 12;
    RecMapArr[n].Flag := (nItem and $FF000000) shr 24;

    if (RecMapArr[n].Offs = 0) and (RecMapArr[n].Size = 0) and (RecMapArr[n].Flag = 0) then
      Break;
    Inc(n);
  end;
  SetLength(RecMapArr, n);

  if iRow > High(RecMapArr) then
  begin
    LogInfo(Format('!Row part index %d > out of %d', [iRow, Length(RecMapArr)]));
    Exit;
  end;

  if (RecMapArr[iRow].Flag = 0)
  and (RecMapArr[iRow].Size > 0)
  and (RecMapArr[iRow].Size < FPageSize)
  and (RecMapArr[iRow].Offs < FPageSize)
  then
  begin
    // read part data
    rdr.SetPosition(RecMapArr[iRow].Offs + SDF_PAGE_HEAD_SIZE);
    Result := rdr.ReadUInt32; // next part
    AData := AData + rdr.ReadBytes(RecMapArr[iRow].Size - 4);
  end
  else
  begin
    LogInfo(Format('!Row part rec wrong type %d: Offs=$%x Size=%d', [RecMapArr[iRow].Flag, RecMapArr[iRow].Offs, RecMapArr[iRow].Size]));
  end;
end;

function TDBReaderSdf.GetBlobData(ABlobInfo: AnsiString; var AData: AnsiString): Boolean;
var
  rdr: TRawDataReader;
  iVarSize, iBlobSize: Integer;
  PartAddr, PartAddr1, PartAddr2, PartAddr3: Integer;
  i64: Int64;
begin
  // i32 length
  // u32 ?? 00
  // u64 PageID triplets

  Result := False;
  rdr.Init(ABlobInfo[1], False);
  iVarSize := Length(ABlobInfo);

  iBlobSize := rdr.ReadInt32;
  PartAddr := rdr.ReadInt32;
  Dec(iVarSize, 8);
  if iVarSize = iBlobSize then
  begin
    AData := rdr.ReadBytes(iVarSize);
    Result := True;
    // !! debug
    //if AData <> '' then
    //  AData := BufferToHex(AData[1], Length(AData));
  end
  else
  begin
    //AData := Format('Blob size=%d  part=$%x  addr=', [iBlobSize, PartAddr]);
    AData := '';
    while iVarSize >= 8 do
    begin
      i64 := rdr.ReadInt64;
      PartAddr1 := (i64 and $000000000000FFFFF);
      PartAddr2 := (i64 and $0000000FFFFF00000) shr 20;
      PartAddr3 := (i64 and $00FFFFF0000000000) shr 40;
      Dec(iVarSize, 8);
      //AData := AData + Format(',$%x,$%x,$%x', [PartAddr1, PartAddr2, PartAddr3]);

      if PartAddr1 > 0 then
      begin
        if not GetBlobPageData(PartAddr1, iBlobSize, AData) then
          Exit;
        Dec(iBlobSize, $FF0);
      end;
      if PartAddr2 > 0 then
      begin
        if not GetBlobPageData(PartAddr2, iBlobSize, AData) then
          Exit;
        Dec(iBlobSize, $FF0);
      end;
      if PartAddr3 > 0 then
      begin
        if not GetBlobPageData(PartAddr3, iBlobSize, AData) then
          Exit;
        Dec(iBlobSize, $FF0);
      end;
    end;
  end;
end;

function TDBReaderSdf.GetBlobPageData(APageID, ASize: Integer; var AData: AnsiString): Boolean;
var
  PageBuf: TByteDynArray;
  rdr: TRawDataReader;
  PageItem: TSdfPageItem;
  n: Integer;
begin
  Result := False;

  if ASize <= 0 then
  begin
    LogInfo(Format('!Wrong blob size=%d for page %x', [ASize, APageID]));
    Exit;
  end;

  // find page
  n := FPagesList.FindPageIndex(APageID);
  if n < 0 then
  begin
    LogInfo(Format('!Not found blob PageID %x', [APageID]));
    Exit;
  end;
  PageItem := FPagesList.GetItem(n);

  // read page to buf
  SetLength(PageBuf, FPageSize);
  FFile.Position := (PageItem.PageNo * FPageSize);
  FFile.Read(PageBuf[0], FPageSize);
  rdr.Init(PageBuf[0], False);

  // todo: read page header, check page type

  // blob content
  // i32  ChunkID
  // i32  00000000
  // ..   data (max size $FF0 bytes)
  //rdr.ReadInt32;
  //rdr.ReadInt32;
  rdr.SetPosition($10);

  if ASize > $FF0 then
    ASize := $FF0;
  AData := AData + rdr.ReadBytes(ASize);
  Result := True;
end;

function TDBReaderSdf.ReadRowData(const APageBuf: TByteDynArray; ARowOffs, ARowSize: Integer;
  ATableInfo: TSdfTableInfo; AList: TDbRowsList): Boolean;
var
  rdr: TRawDataReader;
  TmpRow: TDbRowItem;
  NullBmp: TByteDynArray;
  BoolArr: TByteDynArray;
  VarMapArr: array of TSdfRecMapItem;
  RowSize, TmpPos, iColType, iColSize, iColPos, iColCount, iMaxOffs: Integer;
  i, iCol, iNullCol, iBitmapLen, iBool, iFixOffs: Integer;
  iVarSize, iVarOffs, iVarPos, iVarPosPrev: Integer;
  PartAddr: Cardinal;
  u16: Word;
  i32: LongInt;
  s: string;
  sData: AnsiString;
  v: Variant;
  dt: TDateTime;
  TmpGuid: TGuid;

begin
  Result := False;
  rdr.Init(APageBuf[0], False);
  rdr.SetPosition(ARowOffs);

  PartAddr := rdr.ReadUInt32;
  iColCount := rdr.ReadInt32;
  //btFlags := rdr.ReadUInt8;  // see also NullCount

  RowSize := ATableInfo.FixedSize;
  if (RowSize > ARowSize) then
  begin
    if IsDebugRows then
    begin
      LogInfo(Format('!Short record at %x: %.8x  FixSize=%d RowSize=%d', [ARowOffs, PartAddr, RowSize, ARowSize]));
    end;
    //Exit;
  end;
  iMaxOffs := ARowOffs + ARowSize;

  // Null bitmap
  iBitmapLen := ATableInfo.NullSize; // bytes count
  SetLength(NullBmp, iBitmapLen);
  if iBitmapLen > 0 then
    rdr.ReadToBuffer(NullBmp[0], iBitmapLen);

  // Bool bitmap
  if ATableInfo.BoolCount > 0 then
  begin
    iBitmapLen := ((ATableInfo.BoolCount + 7) div 8); // bytes count
    SetLength(BoolArr, iBitmapLen);
    rdr.ReadToBuffer(BoolArr[0], iBitmapLen);
  end;

  if not Assigned(AList) then
    AList := ATableInfo;
  TmpRow := TDbRowItem.Create(AList);
  AList.Add(TmpRow);

  // Columns
  iColCount := Length(AList.FieldsDef);
  SetLength(TmpRow.Values, iColCount);

  TmpPos := rdr.GetPosition;
  // raw data
  if IsDebugRows then
  begin
    SetLength(TmpRow.RawOffs, Length(ATableInfo.FieldsDef));
    rdr.SetPosition(ARowOffs);
    TmpRow.RawData := rdr.ReadBytes(ARowSize);
    rdr.SetPosition(TmpPos);
  end;

  // read variable-size data
  TmpPos := rdr.GetPosition;
  iVarOffs := ARowOffs + ATableInfo.FixedSize;
  iVarPosPrev := 0;
  rdr.SetPosition(iVarOffs);
  SetLength(VarMapArr, ATableInfo.VarCount);
  for i := Low(VarMapArr) to High(VarMapArr) do
  begin
    if iVarOffs > iMaxOffs then
    begin
      VarMapArr[i].Offs := 0;
      VarMapArr[i].Size := 0;
      Continue;
    end;

    u16 := rdr.ReadUInt16;
    Inc(iVarOffs, 2);

    iVarPos := u16 and $0FFF;  // position
    if (i > 0) and (iVarPos >= iVarPosPrev) then
      VarMapArr[i-1].Size := iVarPos - iVarPosPrev; // from prev pos
    iVarSize := (iMaxOffs-iVarOffs) - iVarPos;  // rest of row (by default)
    if iVarSize < 0 then
      iVarSize := 0;
    VarMapArr[i].Size := iVarSize;
    VarMapArr[i].Offs := iVarPos;
    VarMapArr[i].Flag := (u16 and $F000);

    iVarPosPrev := iVarPos;
  end;
  rdr.SetPosition(TmpPos);

  // read fixed-size data
  iFixOffs := rdr.GetPosition();
  iCol := 0;
  iNullCol := -1;
  iBool := 0;  // boolean field count
  while (iCol < iColCount) and (rdr.GetPosition <= (ARowOffs + RowSize)) do
  begin
    //Assert(iCol < ATableInfo.ColCount, Format('Table %s body read >%d columns!', [ATableInfo.TableName, ATableInfo.ColCount]));

    v := Null;
    iColType := ATableInfo.FieldInfoArr[iCol].FieldType;
    iColSize := ATableInfo.FieldInfoArr[iCol].FieldSize;
    iColPos  := ATableInfo.FieldInfoArr[iCol].FieldPos;

    // all columns in null bitmap
    if not ATableInfo.IsSystem() then
    begin
      Inc(iNullCol);
      if IsNullValue(NullBmp, iNullCol) then
      begin
        TmpRow.Values[iCol] := Null;
        Inc(iCol);
        Continue;
      end;
    end;

    if not ATableInfo.FieldInfoArr[iCol].IsVariable then
      rdr.SetPosition(iColPos + iFixOffs);

    if IsDebugRows then
      TmpRow.RawOffs[iCol] := rdr.GetPosition() - ARowOffs;

    case iColType of
      SDF_COL_TYPE_SMALLINT,
      SDF_COL_TYPE_INT,
      SDF_COL_TYPE_BIGINT:
      begin
        case iColSize of
          1: v := rdr.ReadInt8;
          2: v := rdr.ReadInt16;
          8: v := rdr.ReadInt64;
        else
          v := rdr.ReadInt32;
        end;
      end;

      {SDF_COL_TYPE_UINT,
      SDF_COL_TYPE_UINT64,
      SDF_COL_TYPE_UINT32:
      begin
        case iColSize of
          1: v := rdr.ReadUInt8;
          2: v := rdr.ReadUInt16;
          8: v := rdr.ReadUInt64;
        else
          v := rdr.ReadUInt32;
        end;
      end;  }

      SDF_COL_TYPE_BOOLEAN:
      begin
        v := GetBoolValue(BoolArr, iBool);
        Inc(iBool);
      end;

      SDF_COL_TYPE_DATETIME:
      begin
        i32 := rdr.ReadInt32; // seconds * 300
        dt := (i32 div 300) / SecsPerDay;
        i32 := rdr.ReadInt32; // days since 1900-01-01
        dt := dt + i32 + 2;
        if dt > MaxDateTime then
          dt := MaxDateTime;
        if dt < MinDateTime then
          dt := MinDateTime;
        v := dt;
      end;

      SDF_COL_TYPE_GUID:
      begin
        rdr.ReadToBuffer(TmpGuid, SizeOf(TmpGuid));
        v := GUIDToString(TmpGuid);
      end;

      SDF_COL_TYPE_NUMERIC:
        v := ReadNumeric(rdr);

      SDF_COL_TYPE_REAL:
        v := rdr.ReadSingle;

      SDF_COL_TYPE_FLOAT:
        v := rdr.ReadDouble;

      SDF_COL_TYPE_MONEY:
        v := rdr.ReadCurrency;

      SDF_COL_TYPE_NCHAR:
      begin
        //s := rdr.ReadCString(iColSize);
        s := rdr.ReadBytes(iColSize);
        s := WideDataToStr(s);
        v := s;
      end;

      SDF_COL_TYPE_BINARY:
        s := rdr.ReadBytes(iColSize);

      SDF_COL_TYPE_NVARCHAR,
      SDF_COL_TYPE_NTEXT,
      SDF_COL_TYPE_VARBIN,
      SDF_COL_TYPE_IMAGE,
      SDF_COL_TYPE_VARCHAR:
      begin
        TmpPos := rdr.GetPosition;
        if (iColPos <= High(VarMapArr)) then
        begin
          iVarSize := VarMapArr[iColPos].Size;
          rdr.SetPosition(iVarOffs + VarMapArr[iColPos].Offs);
          if IsDebugRows then
            TmpRow.RawOffs[iCol] := rdr.GetPosition() - ARowOffs;

          if iVarSize > (iMaxOffs - rdr.GetPosition()) then
            iVarSize := (iMaxOffs - rdr.GetPosition());

          if iVarSize > 0 then
          begin
            if iColType = SDF_COL_TYPE_VARCHAR then
              s := rdr.ReadCString(iVarSize)
            else
            if (iColType = SDF_COL_TYPE_NTEXT) then
            begin
              sData := rdr.ReadBytes(iVarSize);
              s := '';
              GetBlobData(sData, s);
            end
            else
            if (iColType = SDF_COL_TYPE_VARBIN) then
            begin
              VarMapArr[iColPos].Flag := $8000; // !!! not widechar!
              s := rdr.ReadBytes(iVarSize);
            end
            else
            if (iColType = SDF_COL_TYPE_IMAGE) then
            begin
              VarMapArr[iColPos].Flag := $8000; // !!! not widechar!
              sData := rdr.ReadBytes(iVarSize);
              s := '';
              GetBlobData(sData, s);
            end
            else
              s := rdr.ReadBytes(iVarSize);

            // flag $8000 single-char
            if VarMapArr[iColPos].Flag <> $8000 then
              s := WideDataToStr(s);
            v := s;
          end;
        end;
        rdr.SetPosition(TmpPos);
      end;

      SDF_COL_TYPE_DEBUG:
      begin
        v := '';
      end;
    end;

    TmpRow.Values[iCol] := v;
    Inc(iCol);
  end;

  Result := True;
end;

procedure TDBReaderSdf.ReadTable(AName: string; ACount: Int64; AList: TDbRowsList);
var
  TmpTable: TSdfTableInfo;
  TmpRow: TDbRowItem;
  i: Integer;
  PageBuf: TByteDynArray;
  iPagePos: Int64;
begin
  TmpTable := FTableList.GetByName(AName);
  if not Assigned(TmpTable) then
  begin
    LogInfo('!Table not found: ' + AName);
    Exit;
  end;
  if not Assigned(AList) then
    AList := TmpTable
  else
  begin
    AList.Clear();
    AList.FieldsDef := TmpTable.FieldsDef;

    if TmpTable.Count <> 0 then
    begin
      // copy data
      for i := 0 to TmpTable.Count - 1 do
      begin
        TmpRow := TDbRowItem.Create(AList);
        TmpRow.Assign(TmpTable.GetItem(i));
        AList.Add(TmpRow);
      end;
      Exit;
    end;
  end;

  SetLength(PageBuf, FPageSize);
  // read pages
  for i := Low(TmpTable.PageIdArr) to High(TmpTable.PageIdArr) do
  begin
    iPagePos := (TmpTable.PageIdArr[i]) * FPageSize;
    if iPagePos + FPageSize <= FFile.Size then
    begin
      FFile.Position := iPagePos;
      FFile.Read(PageBuf[0], FPageSize);

      ReadDataPage(PageBuf, iPagePos, TmpTable, AList);
    end
    else
      Assert(False, 'Page out of file: ' + IntToStr(TmpTable.PageIdArr[i]));

    if Assigned(OnPageReaded) then
      OnPageReaded(Self);
  end;
end;

{ TSdfTableInfo }

procedure TSdfTableInfo.AddFieldDef(AName, AType: string; ANum: Integer;
  APos: Integer);
var
  n, nTypeSize: Integer;
  sTypeName, sTypeSize: string;
begin
  n := Pos('(', AType);
  nTypeSize := 0;
  if n > 0 then
  begin
    sTypeName := Copy(AType, 1, n-1);
    sTypeSize := Copy(AType, n+1, MaxInt);
    n := Pos(')', sTypeSize);
    if n > 0 then
      sTypeSize := Copy(sTypeSize, 1, n-1);
    // NUMERIC(12,2)
    n := Pos(',', sTypeSize);
    if n > 0 then
      sTypeSize := Copy(sTypeSize, 1, n-1);
    nTypeSize := StrToIntDef(sTypeSize, 0);
  end
  else
    sTypeName := AType;

  n := Length(FieldInfoArr);
  if ANum < 0  then
    ANum := n
  else
    n := ANum;

  if n >= Length(FieldInfoArr) then
    SetLength(FieldInfoArr, n+1);
  FieldInfoArr[n].FieldName := AName;
  FieldInfoArr[n].FieldType := SdfColTypeToInt(AType);
  FieldInfoArr[n].IsPrimaryKey := (Pos('PRIMARY KEY', AType) > 0);
  FieldInfoArr[n].IsNullable := (Pos('NOT NULL', AType) = 0);
  FieldInfoArr[n].IsVariable := (FieldInfoArr[n].FieldType = SDF_COL_TYPE_VARCHAR)
                             or (FieldInfoArr[n].FieldType = SDF_COL_TYPE_NVARCHAR)
                             or (FieldInfoArr[n].FieldType = SDF_COL_TYPE_NTEXT)
                             or (FieldInfoArr[n].FieldType = SDF_COL_TYPE_BINARY)
                             or (FieldInfoArr[n].FieldType = SDF_COL_TYPE_VARBIN)
                             or (FieldInfoArr[n].FieldType = SDF_COL_TYPE_IMAGE);

  if FieldInfoArr[n].IsVariable then
    nTypeSize := 2;
  if (APos < 0) and (FieldInfoArr[n].IsVariable) then
    APos := VarCount;
  if (APos < 0) and (not FieldInfoArr[n].IsVariable) then
    APos := FixedSize;

  FieldInfoArr[n].FieldSize := nTypeSize;
  FieldInfoArr[n].TypeName := AType;
  FieldInfoArr[n].FieldNum := ANum;
  FieldInfoArr[n].FieldPos := APos;                                                                           ;

  // db fields
  if n >= Length(FieldsDef) then
    SetLength(FieldsDef, n+1);
  FieldsDef[n].Name := AName;
  FieldsDef[n].TypeName := AType;
  FieldsDef[n].FieldType := SdfColTypeToDbFieldType(sTypeName);
  FieldsDef[n].Size := nTypeSize;
  //FieldsDef[n].RawOffset

  if FieldInfoArr[n].IsNullable then
    Inc(NullCount);

  if FieldInfoArr[n].FieldType = SDF_COL_TYPE_BOOLEAN then
  begin
    Inc(BoolCount);
    nTypeSize := 0;
  end;

  if FieldInfoArr[n].IsVariable then
    Inc(VarCount);

  if (FieldInfoArr[n].FieldType = SDF_COL_TYPE_DEBUG) then
  else
    Inc(ColCount);

  Inc(FixedSize, nTypeSize);
end;

procedure TSdfTableInfo.AddPageID(APageID: Cardinal);
begin
  // store PageID
  Inc(PageIdCount);
  if PageIdCount >= Length(PageIdArr) then
    SetLength(PageIdArr, Length(PageIdArr) + 32);
  PageIdArr[PageIdCount-1] := APageID;
end;

procedure TSdfTableInfo.UpdateSizes();
var
  i, n: Integer;
begin
  ColCount := 0;
  NullCount := 0;
  BoolCount := 0;
  VarCount := 0;
  FixedSize := SDF_ROW_HEAD_SIZE;

  for i := Low(FieldInfoArr) to High(FieldInfoArr) do
  begin
    if FieldInfoArr[i].IsNullable then
      Inc(NullCount);

    if FieldInfoArr[i].FieldType = SDF_COL_TYPE_BOOLEAN then
      Inc(BoolCount);

    if FieldInfoArr[i].IsVariable then
      Inc(VarCount);

    if (FieldInfoArr[i].FieldType = SDF_COL_TYPE_DEBUG) then
    else
      Inc(ColCount);

    if (not FieldInfoArr[i].IsVariable)
    and (FieldInfoArr[i].FieldType <> SDF_COL_TYPE_BOOLEAN) then
      Inc(FixedSize, FieldInfoArr[i].FieldSize);
  end;

  // null bitmap
  NullSize := ((ColCount + 7) div 8); // bytes needed
  Inc(FixedSize, NullSize);

  // bool bitmap
  n := ((BoolCount + 7) div 8); // bytes count
  Inc(FixedSize, n);

  // var fields
  //n := VarCount * 2;
  //Inc(FixedSize, n);
end;

function TSdfTableInfo.IsEmpty: Boolean;
begin
  Result := (RowCount = 0);
end;

function TSdfTableInfo.IsGhost: Boolean;
begin
  Result := (TableSQL = '');
end;

function TSdfTableInfo.IsSystem: Boolean;
begin
  Result := (TableID = SDF_TABLE_ID_SYS) or (TableID < 0);
end;

procedure TSdfTableInfo.SetSQL(ASql: string);
var
  s, ss, sField: string;
  i, n: Integer;
begin
  TableSQL := ASql;
  ss := ASql;
  s := ExtractFirstWord(ss);
  if s <> 'CREATE' then Exit;
  s := ExtractFirstWord(ss);
  if s <> 'TABLE' then Exit;

  s := Trim(ExtractFirstWord(ss, '('));
  s := RemoveQuotes(s, '""');
  s := RemoveQuotes(s, '[]');
  TableName := s;

  // find unmatched ")"
  i := 1;
  n := 0;
  while i < Length(ss) do
  begin
    if Copy(ss, i, 1) = '(' then
      Inc(n)
    else
    if Copy(ss, i, 1) = ')' then
      Dec(n);
    if n < 0 then
      Break;
    Inc(i);
  end;
  ss := Copy(ss, 1, i-1);

  // fields
  while ss <> '' do
  begin
    //sField := Trim(ExtractFirstWord(ss, ','));
    // find unbracketed ','
    i := 1;
    n := 0;
    while i <= Length(ss) do
    begin
      if Copy(ss, i, 1) = '(' then
        Inc(n)
      else
      if Copy(ss, i, 1) = ')' then
        Dec(n)
      else
      if (Copy(ss, i, 1) = ',') and (n = 0) then
        Break;
      Inc(i);
    end;
    sField := Trim(Copy(ss, 1, i-1));
    ss := Copy(ss, i+1, MaxInt);

    s := ExtractFirstWord(sField);  // name
    if (UpperCase(s) = 'FOREIGN') then
      Continue;
    if (UpperCase(s) = 'CONSTRAINT') then
    begin
      if Pos('PRIMARY KEY', UpperCase(sField)) > 0 then
      begin
        // CONSTRAINT pk_name PRIMARY KEY(field_name)
        s := Trim( Copy(sField, Pos('(', sField)+1, MaxInt) );
        s := Trim( Copy(s, 1, Pos(')', s)-1) ); // s = field_name
        s := RemoveQuotes(s, '""');
        s := RemoveQuotes(s, '[]');
        for i := 0 to Length(FieldInfoArr) - 1 do
        begin
          if AnsiSameText(FieldInfoArr[i].FieldName, s) then
            FieldInfoArr[i].IsPrimaryKey := True;
        end;
      end;
      Continue;
    end;
    s := RemoveQuotes(s, '""');
    s := RemoveQuotes(s, '[]');
    AddFieldDef(s, Trim(sField));
  end;
  UpdateSizes();
end;

{ TSdfTableInfoList }

function TSdfTableInfoList.GetByName(AName: string): TSdfTableInfo;
var
  i: Integer;
begin
  for i := 0 to Count - 1 do
  begin
    Result := GetItem(i);
    if Result.TableName = AName then
      Exit;
  end;
  Result := nil;
end;

function TSdfTableInfoList.GetByTableID(ATabID: Cardinal): TSdfTableInfo;
var
  i: Integer;
begin
  for i := 0 to Count - 1 do
  begin
    Result := GetItem(i);
    if Result.TableID = ATabID then
      Exit;
  end;
  Result := nil;
end;

function TSdfTableInfoList.GetItem(AIndex: Integer): TSdfTableInfo;
begin
  Result := TSdfTableInfo(Get(AIndex));
end;

procedure TSdfTableInfoList.Notify(Ptr: Pointer; Action: TListNotification);
begin
  inherited;
  if Action = lnDeleted then
    TSdfTableInfo(Ptr).Free;
end;

function _DoSortByName(Item1, Item2: Pointer): Integer;
var
  TmpItem1, TmpItem2: TSdfTableInfo;
begin
  TmpItem1 := TSdfTableInfo(Item1);
  TmpItem2 := TSdfTableInfo(Item2);
  Result := AnsiCompareStr(TmpItem1.TableName, TmpItem2.TableName);
end;

procedure TSdfTableInfoList.SortByName;
begin
  Sort(@_DoSortByName);
end;

{ TSdfPageItemList }

procedure TSdfPageItemList.Notify(Ptr: Pointer; Action: TListNotification);
begin
  inherited Notify(Ptr, Action);
  if Action = lnDeleted then
    TSdfPageItem(Ptr).Free;
end;

function TSdfPageItemList.GetItem(AIndex: Integer): TSdfPageItem;
begin
  Result := TSdfPageItem(Get(AIndex));
end;

function ComparePageItems(Item1, Item2: Pointer): Integer;
var
  A, B: TSdfPageItem;
begin
  Result := 0;
  A := TSdfPageItem(Item1);
  B := TSdfPageItem(Item2);
  if A.PageID > B.PageID then
    Result := 1
  else
  if A.PageID < B.PageID then
    Result := -1
  else
  begin
    if A.PageNo > B.PageNo then
      Result := 1
    else
    if A.PageNo < B.PageNo then
      Result := -1;
  end;
end;

procedure TSdfPageItemList.SortItems();
begin
  Sort(ComparePageItems);
end;

function TSdfPageItemList.FindPageIndex(APageID: Integer): Integer;
var
  iL, iR, iM, iCmpRes: Integer;
  M: TSdfPageItem;
begin
  Result := -1;
  // binary search
  iL := 0;
  iR := Count-1;
  while iL <= iR do
  begin
    iM := (iL + iR) div 2;
    M := TSdfPageItem(Get(iM));
    // compare
    iCmpRes := 0;
    if APageID > M.PageID then
      iCmpRes := 1
    else
    if APageID < M.PageID then
      iCmpRes := -1;

    // get next mid
    if iCmpRes > 0 then
    begin
      iL := iM + 1;
      Result := -(iM + 2);
    end
    else
    if iCmpRes < 0 then
    begin
      iR := iM - 1;
      Result := -(iM + 1);
    end
    else
    begin
      Result := iM;
      Break;
    end;
  end;
end;

end.