TOnSQLTableGetValue = function(Sender: TSQLTable; Row, Field: integer;
    HumanFriendly: boolean): RawJSON of object;

  /// store TSQLFieldType and RTTI for a given TSQLTable field
  TSQLTableFieldType = record
    /// the field kind, as in JSON (match TSQLPropInfo.SQLFieldTypeStored)
    ContentType: TSQLFieldType;



    /// the field size in bytes; -1 means not computed yet
    ContentSize: integer;
    /// used for sftEnumerate, sftSet and sftBlobDynArray fields
    ContentTypeInfo: pointer;
    /// the corresponding index in fQueryTables[]
    TableIndex: integer;
  end;


  /// wrapper to an ORM result table, staticaly stored as UTF-8 text
  // - contain all result in memory, until destroyed
  // - first row contains the field names
  // - following rows contains the data itself
  // - GetString() can be used in a TDrawString
  // - will be implemented as TSQLTableJSON for remote access through optimized
................................................................................
    /// guess the field type from first non null data row
    // - if QueryTables[] are set, exact field type and (enumerate) TypeInfo() is
    // retrieved from the Delphi RTTI; otherwise, get from the cells content
    // - return sftUnknown is all data fields are null
    // - sftBlob is returned if the field is encoded as SQLite3 BLOB literals
    // (X'53514C697465' e.g.)
    // - since TSQLTable data is PUTF8Char, string type is sftUTF8Text only
    function FieldType(Field: integer; out FieldTypeInfo: TSQLTableFieldType): TSQLFieldType; overload;
    /// get the appropriate Sort comparaison function for a field,
    // nil if not available (bad field index or field is blob)
    // - field type is guessed from first data row
    function SortCompare(Field: integer): TUTF8Compare;
    /// get the mean of characters length of all fields
    // - the character length is for the first line of text only (stop counting
    // at every newline character, i.e. #10 or #13 char)
................................................................................
    // - you can define a specific type for a given column, and optionally
    // a maximum column size
    // - FieldTypeInfo can be specified for sets or enumerations, as such:
    // ! aTable.SetFieldType('Sample',sftEnumerate,TypeInfo(TEnumSample));
    // ! aTable.SetFieldType('Samples',sftSet,TypeInfo(TSetSamples));
    procedure SetFieldType(const FieldName: RawUTF8; FieldType: TSQLFieldType;
      FieldTypeInfo: pointer=nil; FieldSize: integer=-1); overload;


    /// increase a particular Field Length Mean value
    // - to be used to customize the field appareance (e.g. for adding of left
    // checkbox for Marked[] fields)
    procedure FieldLengthMeanIncrease(aField, aIncrease: integer);

    /// copy the parameters of a TSQLTable into this instance
    // - the fResults remain in the source TSQLTable: source TSQLTable has not to
................................................................................
        ContentTypeInfo := FieldTypeInfo;
        ContentType := NullableTypeToSQLFieldType(FieldTypeInfo);
        if ContentType=sftUnknown then
          ContentType := sftNullable;
      end;
      {$endif}
      end;

    TableIndex := FieldTableIndex;
  end;
end;

procedure TSQLTable.SetFieldType(const FieldName: RawUTF8; FieldType: TSQLFieldType;
   FieldTypeInfo: pointer=nil; FieldSize: integer=-1);
begin
  SetFieldType(FieldIndex(FieldName),FieldType,FieldTypeInfo,FieldSize);
end;














function TSQLTable.GetRowCount: integer;
begin
  if self=nil then
    result := 0 else
    result := fRowCount;
end;
................................................................................
procedure TSQLTable.InitFieldTypes;
var f,i,len: integer;
    sft: TSQLFieldType;
    info: pointer;
    prop: TSQLPropInfo;
    size,tableindex: integer;
    U: PPUTF8Char;

    tlog: TTimeLog;
begin
  if Assigned(fQueryColumnTypes) and (FieldCount<>length(fQueryColumnTypes)) then
    raise ESQLTableException.CreateUTF8('%.CreateWithColumnTypes() called with % '+
      'column types, whereas the result has % columns',
      [self,length(fQueryColumnTypes),FieldCount]);
  SetLength(fFieldType,FieldCount);
  for f := 0 to FieldCount-1 do begin
    prop := nil;
    info := nil;
    size := -1;
    tableindex := -1;

    // init fFieldType[] from fQueryTables/fQueryColumnTypes[]
    if Assigned(fQueryColumnTypes) then
      sft := fQueryColumnTypes[f] else
    if Assigned(QueryTables) then begin // retrieve column info from field name
      sft := FieldPropFromTables(fResults[f],prop,tableindex);
      if prop<>nil then begin
        if prop.InheritsFrom(TSQLPropInfoRTTI) then
................................................................................
        size := prop.FieldWidth;
      end;
    end else
      sft := sftUnknown;
    if sft=sftUnknown then
      // not found in fQueryTables/fQueryColumnTypes[]: guess from content
      if IsRowID(fResults[f]) then
        sft := sftInteger else

      if f in fFieldParsedAsString then  begin
        // the parser identified string values -> check if was sftDateTime
        sft := sftUTF8Text;
        U := @fResults[FieldCount+f];
        for i := 1 to fRowCount do
          if U^=nil then  // search for a non void column
            inc(U,FieldCount) else begin
            len := StrLen(U^);
            tlog := Iso8601ToTimeLogPUTF8Char(U^,len);
            if tlog<>0 then
              if (len in [8,10]) and (cardinal(tlog shr 26)-1800<300) then
                sft := sftDateTime else // e.g. YYYYMMDD date (Y=1800..2100)
              if len>=15 then
                sft := sftDateTime; // e.g. YYYYMMDDThhmmss date/time value
            break;
          end;
      end else begin
        U := @fResults[FieldCount+f];
        for i := 1 to fRowCount do begin
          sft := UTF8ContentNumberType(U^);
          inc(U,FieldCount);
          if sft=sftUnknown then
            continue else // null -> search for a non void column
          if sft=sftInteger then // may be a floating point with no decimal
            if FieldTypeIntegerDetectionOnAllRows then
              continue else
              // we only checked the first field -> best guess...
              sft := sftCurrency;
          break; // found a non-integer content (e.g. sftFloat/sftUtf8Text)
        end;
      end;

    SetFieldType(f,sft,info,size,tableindex);


  end;
end;

function TSQLTable.FieldType(Field: integer): TSQLFieldType;
begin
  if (self<>nil) and (cardinal(Field)<cardinal(FieldCount)) then begin
    if not Assigned(fFieldType) then
      InitFieldTypes;
    result := fFieldType[Field].ContentType;
  end else
    result := sftUnknown;
end;

function TSQLTable.FieldType(Field: integer; out FieldTypeInfo: TSQLTableFieldType): TSQLFieldType;
begin
  if (self<>nil) and (cardinal(Field)<cardinal(FieldCount)) then begin
    if not Assigned(fFieldType) then
      InitFieldTypes;
    FieldTypeInfo := fFieldType[Field];
    result := FieldTypeInfo.ContentType;
  end else

    result := sftUnknown;

end;

function TSQLTable.Get(Row, Field: integer): PUTF8Char;
begin
  if (self=nil) or (fResults=nil) or (cardinal(Row)>cardinal(fRowCount)) or
     (cardinal(Field)>=cardinal(FieldCount)) then // cardinal() -> test <0
    result := nil else
................................................................................
    MoveFast(pointer(Trail)^,P[len^],length(Trail));
  tmp.Done;
end;

procedure TSQLTable.GetJSONValues(JSON: TStream; Expand: boolean;
  RowFirst, RowLast, IDBinarySize: integer);
var W: TJSONWriter;
    F,R: integer;
    U: PPUTF8Char;

    i64: Int64;
    directWrites: set of 0..255;
begin
  W := TJSONWriter.Create(JSON,Expand,false);
  try
    if (self=nil) or (FieldCount<=0) or (fRowCount<=0) then begin
      W.CancelAllVoid;
      exit;
    end;
................................................................................
      RowLast := fRowCount;
    if RowFirst<=0 then
      RowFirst := 1; // start reading after first Row (Row 0 = Field Names)
    // get col names and types
    if QueryTables<>nil then
      InitFieldTypes;
    SetLength(W.ColNames,FieldCount);
    FillCharFast(directWrites,(FieldCount shr 3)+1,0);
    for F := 0 to FieldCount-1 do begin
      W.ColNames[F] := fResults[F]; // first Row is field Names
      if not Assigned(OnExportValue) then
        if F=fFieldIndexID then begin
          include(directWrites,F); // RowID is a ftInt64
          if IDBinarySize>0 then
            W.ColNames[F] := 'id'; // ajax-friendly
        end else
        if QueryTables<>nil then
          with fFieldType[F] do
          if SQLFieldTypeToDBField(ContentType,ContentTypeInfo) in
             [ftInt64,ftDouble,ftCurrency] then
            include(directWrites,F);
    end;
    W.AddColumns(RowLast-RowFirst+1); // write or init field names (see JSON Expand)
    if Expand then
      W.Add('[');
    // write rows data
    U := @fResults[FieldCount*RowFirst];
    for R := RowFirst to RowLast do begin
      if Expand then
        W.Add('{');
      for F := 0 to FieldCount-1 do begin
        if Expand then
          W.AddString(W.ColNames[F]); // '"'+ColNames[]+'":'
        if Assigned(OnExportValue) then
          W.AddString(OnExportValue(self,R,F,false)) else
        if (IDBinarySize>0) and (F=fFieldIndexID) then begin
          SetInt64(U^,i64);
          W.AddBinToHexDisplayQuoted(@i64,IDBinarySize);
        end else
        if U^=nil then
          W.AddShort('null') else
        // IsStringJSON() is fast and safe: no need to guess exact value type
        if (F in directWrites) or ((QueryTables=nil) and not IsStringJSON(U^)) then
          W.AddNoJSONEscape(U^,StrLen(U^)) else begin

          W.Add('"');
          W.AddJSONEscape(U^,StrLen(U^));
          W.Add('"');




        end;
        W.Add(',');
        inc(U); // points to next value
      end;
      W.CancelLastComma;
      if Expand then begin
        W.Add('}',',');
        if R<>RowLast then
          W.AddCR; // make expanded json more human readable
      end else
        W.Add(',');
    end;
    W.EndJSONObject(1,0); // "RowCount": set by W.AddColumns(RowLast-RowFirst+1)
  finally
    W.Free;
................................................................................
  // ftUnknown, ftNull, ftInt64, ftDouble, ftCurrency,
     '','',' dt:type="i8"',' dt:type="float"',' dt:type="number" rs:dbtype="currency"',
  // ftDate, ftUTF8, ftBlob
     ' dt:type="dateTime"',' dt:type="string"',' dt:type="bin.hex"');
var W: TJSONWriter;
    f,r: integer;
    U: PPUTF8Char;
    fieldType: TSQLDBFieldTypeDynArray;
begin
  W := TJSONWriter.Create(Dest,16384);
  try
    W.AddShort('<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" '+
      'xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" '+
      'xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">');
    if (self<>nil) and ((FieldCount>0) or (fRowCount>0)) then begin
      // retrieve normalized field names and types
      if length(fFieldNames)<>fFieldCount then
        InitFieldNames;
      if not Assigned(fFieldType) then
        InitFieldTypes;
      SetLength(fieldType,FieldCount);
      for f := 0 to FieldCount-1 do
        with fFieldType[F] do
          fieldType[f] := SQLFieldTypeToDBField(ContentType,ContentTypeInfo);
      // check range
      if RowLast=0 then
        RowLast := fRowCount else
      if RowLast>fRowCount then
        RowLast := fRowCount;
      if RowFirst<=0 then
        RowFirst := 1; // start reading after first Row (Row 0 = Field Names)
................................................................................
      W.AddShort('<s:Schema id="RowsetSchema"><s:ElementType name="row" content="eltOnly">');
      for f := 0 to FieldCount-1 do begin
        W.AddShort('<s:AttributeType name="f');
        W.Add(f);
        W.AddShort('" rs:name="');
        W.AddString(fFieldNames[f]);
        W.Add('"');
        W.AddString(FIELDTYPE_TOXML[fieldType[f]]);
        W.Add('/','>');
      end;
      W.AddShort('</s:ElementType></s:Schema>');
      // write rows data
      U := @fResults[FieldCount*RowFirst];
      W.AddShort('<rs:data>');
      for r := RowFirst to RowLast do begin
        W.AddShort('<z:row ');
        for f := 0 to FieldCount-1 do begin
          if U^<>nil then begin
            W.Add('f');
            W.Add(f);
            W.Add('=','"');
            case fieldType[f] of
            ftUnknown:
              if IsStringJSON(U^) then // no need to guess exact value type here
                W.AddXmlEscape(U^) else
                W.AddNoJSONEscape(U^,StrLen(U^));
            ftInt64, ftDouble, ftCurrency:
              W.AddNoJSONEscape(U^,StrLen(U^));
            ftDate, ftUTF8, ftBlob:
              W.AddXmlEscape(U^);
            end;
            W.Add('"',' ');
          end;
          inc(U); // points to next value
        end;
        W.Add('/','>');
      end;
      W.AddShort('</rs:data>');
................................................................................
  '<manifest:file-entry manifest:full-path="meta.xml" manifest:media-type="text/xml"/><manifest:file-entry manifest:full-path="settings.xml" manifest:media-type="text/xml"/>'+
  '<manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/><manifest:file-entry manifest:full-path="styles.xml" manifest:media-type="text/xml"/></manifest:manifest>';
var Zip: TZipWriteToStream;
    Dest: TRawByteStringStream;
    content: RawUTF8;
    W: TTextWriter;
    U: PPUTF8Char;
    R,F: integer;
    fieldType: TSQLDBFieldTypeDynArray;
begin
  Dest := TRawByteStringStream.Create;
  try
    Zip := TZipWriteToStream.Create(Dest);
    try
      Zip.AddStored('mimetype',pointer(ODSmimetype),length(ODSmimetype));
      Zip.AddDeflated('styles.xml',pointer(ODSstyles),length(ODSstyles));
................................................................................
        if (self<>nil) and ((FieldCount>0) or (fRowCount>0)) then begin
          if withColumnTypes then begin
            // retrieve normalized field names and types
            if length(fFieldNames)<>fFieldCount then
              InitFieldNames;
            if not Assigned(fFieldType) then
              InitFieldTypes;
            SetLength(fieldType,FieldCount);
            for f := 0 to FieldCount-1 do
              with fFieldType[F] do
                fieldType[f] := SQLFieldTypeToDBField(ContentType,ContentTypeInfo);
          end;
          // write column names
          W.AddShort('<table:table-row>');
          U := pointer(fResults);
          for F := 1 to FieldCount do begin
            W.AddShort('<table:table-cell office:value-type="string"><text:p>');
            W.AddXmlEscape(U^);
            W.AddShort('</text:p></table:table-cell>');
            inc(U); // points to next value
          end;
          W.AddShort('</table:table-row>');
          // and values
          for R := 1 to fRowCount do begin
            W.AddShort('<table:table-row>');
            if withColumnTypes then begin
              for F := 0 to FieldCount-1 do begin
                W.AddShort('<table:table-cell office:value-type="');
                case fieldType[F] of
                  ftInt64, ftDouble, ftCurrency: begin
                    W.AddShort('float" office:value="');
                    W.AddXmlEscape(U^);
                  end;
                  ftDate: begin
                   W.AddShort('date" office:date-value="');
                   W.AddXmlEscape(U^);
                  end;
                else //ftUnknown, ftNull, ftUTF8, ftBlob:
                  W.AddShort('string');
                end;
                W.AddShort('"><text:p>');
                if fieldType[F] in [ftUnknown, ftUTF8, ftBlob] then
                  W.AddXmlEscape(U^);
                W.AddShort('</text:p></table:table-cell>');
                inc(U); // points to next value
              end;
            end else
              for F := 0 to FieldCount-1 do begin
                W.AddShort('<table:table-cell office:value-type="string"><text:p>');
                W.AddXmlEscape(U^);
                W.AddShort('</text:p></table:table-cell>');
                inc(U);
              end;
            W.AddShort('</table:table-row>');
          end;
................................................................................
  StartRow, FieldIndex: integer; Client: TObject; Lang: TSynSoundExPronunciation;
  UnicodeComparison: boolean): integer;
var U: PPUTF8Char;
    Kind: TSQLFieldType;
    Search: PAnsiChar;
    UpperUnicode: RawUnicode;
    UpperUnicodeLen: integer;
    info: TSQLTableFieldType;
    Val64: Int64;
    ValTimeLog: TTimelogBits absolute Val64;
    i,err: integer;
    EnumValue: RawUTF8;
    s: string;
    P: PShortString;
    EnumValues: set of 0..63;
................................................................................
function TSQLTable.GetVariant(Row, Field: integer): Variant;
begin
  GetVariant(Row,Field,result);
end;

procedure TSQLTable.GetVariant(Row,Field: integer; var result: variant);
var aType: TSQLFieldType;
    info: TSQLTableFieldType;
begin
  if Row=0 then // Field Name
    RawUTF8ToVariant(GetU(0,Field),result) else begin
    aType := FieldType(Field,info);
    ValueVarToVariant(Get(Row,Field),aType,TVarData(result),true,info.ContentTypeInfo);
  end;
end;
................................................................................
    GetVariant(r,v,Result);
end;

{$endif NOVARIANTS}

function TSQLTable.ExpandAsString(Row, Field: integer; Client: TObject;
  out Text: string; const CustomFormat: string): TSQLFieldType;
var info: TSQLTableFieldType;
    err: integer;
    Value: Int64;
    ValueTimeLog: TTimeLogBits absolute Value;
    ValueDateTime: TDateTime;
    Ref: RecordRef absolute Value;
label IsDateTime;
begin // Text was already forced to '' because was defined as "out" parameter
................................................................................
  sftEnumerate, sftSet, sftRecord, sftID, sftTID, sftRecordVersion, sftSessionUserID,
  sftTimeLog, sftModTime, sftCreateTime, sftUnixTime, sftUnixMSTime: begin
    Value := GetInt64(Get(Row,Field),err);
    if err<>0 then
      // not an integer -> to be displayed as sftUTF8Text
      result := sftUTF8Text else
    case result of
      sftEnumerate: begin

        Text := PEnumType(info.ContentTypeInfo)^.GetCaption(Value);
        exit;
      end;
      sftTimeLog, sftModTime, sftCreateTime:
        goto IsDateTime;
      sftUnixTime: begin
        ValueTimeLog.FromUnixTime(Value);
        goto IsDateTime;
      end;
      sftUnixMSTime: 
................................................................................
  Update(Row,Field,U);
end;
{$endif NOVARIANTS}

procedure TSQLTableWritable.Join(From: TSQLTable; const FromKeyField, KeyField: RawUTF8);
var fk,dk,f,i,k,ndx: integer;
    n,fn: RawUTF8;
    info: TSQLTableFieldType;
    new: TIntegerDynArray;
begin
  dk := FieldIndexExisting(KeyField);
  SetLength(new,FieldCount);
  fk := From.FieldIndexExisting(FromKeyField);
  From.SortFields(fk); // faster merge with O(log(n)) binary search
  for f := 0 to From.FieldCount-1 do // add From fields (excluding FromKeyField)
................................................................................
      if From.FieldType(f,info)=sftUnknown then  // set TSQLTableFieldType
        i := AddField(fn) else
        if info.TableIndex>=0 then
          i := AddField(fn,From.QueryTables[info.TableIndex],n) else begin
          i := AddField(fn);
          if i>=length(fFieldType) then
            SetLength(fFieldType,i+1);
          fFieldType[i] := info;
        end;
      new[f] := i;
    end;
  ndx := FieldCount;
  for i := 1 to RowCount do begin // merge data
    k := From.SearchFieldSorted(fResults[ndx+dk],fk);
    if k>0 then begin 
................................................................................
    if IdemPropName(BlobFields[i].PropInfo^.Name,PropName,PropNameLen) then begin
      result := BlobFields[i].PropInfo;
      exit;
    end;
  result := nil;
end;

const
  DBTOFIELDTYPE: array[TSQLDBFieldType] of TSQLFieldType  = (sftUnknown,
    sftUnknown,sftInteger,sftFloat,sftCurrency,sftDateTime,sftUTF8Text,sftBlob);

function TSQLRecordProperties.SQLFieldTypeToSQL(FieldIndex: integer): RawUTF8;
const
  /// simple wrapper from each SQL used type into SQLite3 field datatype
  // - set to '' for fields with no column created in the database
  DEFAULT_SQLFIELDTYPETOSQL: array[TSQLFieldType] of RawUTF8 =
   ('',                              // sftUnknown
    ' TEXT COLLATE NOCASE, ',        // sftAnsiText

  TOnSQLTableGetValue = function(Sender: TSQLTable; Row, Field: integer;
    HumanFriendly: boolean): RawJSON of object;

  /// store TSQLFieldType and RTTI for a given TSQLTable field
  TSQLTableFieldType = record
    /// the field kind, as in JSON (match TSQLPropInfo.SQLFieldTypeStored)
    ContentType: TSQLFieldType;
    /// how this field could be stored in a database
    // - equals ftUnknown if InitFields guessed the field type
    ContentDB: TSQLDBFieldType;
    /// the field size in bytes; -1 means not computed yet
    ContentSize: integer;
    /// used for sftEnumerate, sftSet and sftBlobDynArray fields
    ContentTypeInfo: pointer;
    /// the corresponding index in fQueryTables[]
    TableIndex: integer;
  end;
  PSQLTableFieldType = ^TSQLTableFieldType;

  /// wrapper to an ORM result table, staticaly stored as UTF-8 text
  // - contain all result in memory, until destroyed
  // - first row contains the field names
  // - following rows contains the data itself
  // - GetString() can be used in a TDrawString
  // - will be implemented as TSQLTableJSON for remote access through optimized
................................................................................
    /// guess the field type from first non null data row
    // - if QueryTables[] are set, exact field type and (enumerate) TypeInfo() is
    // retrieved from the Delphi RTTI; otherwise, get from the cells content
    // - return sftUnknown is all data fields are null
    // - sftBlob is returned if the field is encoded as SQLite3 BLOB literals
    // (X'53514C697465' e.g.)
    // - since TSQLTable data is PUTF8Char, string type is sftUTF8Text only
    function FieldType(Field: integer; out FieldTypeInfo: PSQLTableFieldType): TSQLFieldType; overload;
    /// get the appropriate Sort comparaison function for a field,
    // nil if not available (bad field index or field is blob)
    // - field type is guessed from first data row
    function SortCompare(Field: integer): TUTF8Compare;
    /// get the mean of characters length of all fields
    // - the character length is for the first line of text only (stop counting
    // at every newline character, i.e. #10 or #13 char)
................................................................................
    // - you can define a specific type for a given column, and optionally
    // a maximum column size
    // - FieldTypeInfo can be specified for sets or enumerations, as such:
    // ! aTable.SetFieldType('Sample',sftEnumerate,TypeInfo(TEnumSample));
    // ! aTable.SetFieldType('Samples',sftSet,TypeInfo(TSetSamples));
    procedure SetFieldType(const FieldName: RawUTF8; FieldType: TSQLFieldType;
      FieldTypeInfo: pointer=nil; FieldSize: integer=-1); overload;
    /// set the exact type of all fields, from the DB-like information
    procedure SetFieldTypes(const DBTypes: TSQLDBFieldTypeDynArray);
    /// increase a particular Field Length Mean value
    // - to be used to customize the field appareance (e.g. for adding of left
    // checkbox for Marked[] fields)
    procedure FieldLengthMeanIncrease(aField, aIncrease: integer);

    /// copy the parameters of a TSQLTable into this instance
    // - the fResults remain in the source TSQLTable: source TSQLTable has not to
................................................................................
        ContentTypeInfo := FieldTypeInfo;
        ContentType := NullableTypeToSQLFieldType(FieldTypeInfo);
        if ContentType=sftUnknown then
          ContentType := sftNullable;
      end;
      {$endif}
      end;
    ContentDB := SQLFieldTypeToDBField(ContentType,ContentTypeInfo);
    TableIndex := FieldTableIndex;
  end;
end;

procedure TSQLTable.SetFieldType(const FieldName: RawUTF8; FieldType: TSQLFieldType;
   FieldTypeInfo: pointer; FieldSize: integer);
begin
  SetFieldType(FieldIndex(FieldName),FieldType,FieldTypeInfo,FieldSize);
end;

const
  DBTOFIELDTYPE: array[TSQLDBFieldType] of TSQLFieldType  = (sftUnknown,
    sftUnknown,sftInteger,sftFloat,sftCurrency,sftDateTime,sftUTF8Text,sftBlob);

procedure TSQLTable.SetFieldTypes(const DBTypes: TSQLDBFieldTypeDynArray);
var f: integer;
begin
  if length(DBTypes)<>FieldCount then
    raise ESQLTableException.CreateUTF8('%.SetFieldTypes(DBTypes?)',[self]);
  for f := 0 to FieldCount-1 do
    SetFieldType(f,DBTOFIELDTYPE[DBTypes[f]]);
end;

function TSQLTable.GetRowCount: integer;
begin
  if self=nil then
    result := 0 else
    result := fRowCount;
end;
................................................................................
procedure TSQLTable.InitFieldTypes;
var f,i,len: integer;
    sft: TSQLFieldType;
    info: pointer;
    prop: TSQLPropInfo;
    size,tableindex: integer;
    U: PPUTF8Char;
    guessed: boolean;
    tlog: TTimeLog;
begin
  if Assigned(fQueryColumnTypes) and (FieldCount<>length(fQueryColumnTypes)) then
    raise ESQLTableException.CreateUTF8('%.CreateWithColumnTypes() called with % '+
      'column types, whereas the result has % columns',
      [self,length(fQueryColumnTypes),FieldCount]);
  SetLength(fFieldType,FieldCount);
  for f := 0 to FieldCount-1 do begin
    prop := nil;
    info := nil;
    size := -1;
    tableindex := -1;
    guessed := false;
    // init fFieldType[] from fQueryTables/fQueryColumnTypes[]
    if Assigned(fQueryColumnTypes) then
      sft := fQueryColumnTypes[f] else
    if Assigned(QueryTables) then begin // retrieve column info from field name
      sft := FieldPropFromTables(fResults[f],prop,tableindex);
      if prop<>nil then begin
        if prop.InheritsFrom(TSQLPropInfoRTTI) then
................................................................................
        size := prop.FieldWidth;
      end;
    end else
      sft := sftUnknown;
    if sft=sftUnknown then
      // not found in fQueryTables/fQueryColumnTypes[]: guess from content
      if IsRowID(fResults[f]) then
        sft := sftInteger else begin
        guessed := true;
        if f in fFieldParsedAsString then  begin
          // the parser identified string values -> check if was sftDateTime
          sft := sftUTF8Text;
          U := @fResults[FieldCount+f];
          for i := 1 to fRowCount do
            if U^=nil then  // search for a non void column
              inc(U,FieldCount) else begin
              len := StrLen(U^);
              tlog := Iso8601ToTimeLogPUTF8Char(U^,len);
              if tlog<>0 then
                if (len in [8,10]) and (cardinal(tlog shr 26)-1800<300) then
                  sft := sftDateTime else // e.g. YYYYMMDD date (Y=1800..2100)
                if len>=15 then
                  sft := sftDateTime; // e.g. YYYYMMDDThhmmss date/time value
              break;
            end;
        end else begin
          U := @fResults[FieldCount+f];
          for i := 1 to fRowCount do begin
            sft := UTF8ContentNumberType(U^);
            inc(U,FieldCount);
            if sft=sftUnknown then
              continue else // null -> search for a non void column
            if sft=sftInteger then // may be a floating point with no decimal
              if FieldTypeIntegerDetectionOnAllRows then
                continue else
                // we only checked the first field -> best guess...
                sft := sftCurrency;
            break; // found a non-integer content (e.g. sftFloat/sftUtf8Text)
          end;
        end;
      end;
    SetFieldType(f,sft,info,size,tableindex);
    if guessed then
      fFieldType[f].ContentDB := ftUnknown; // may fail on some later row
  end;
end;

function TSQLTable.FieldType(Field: integer): TSQLFieldType;
begin
  if (self<>nil) and (cardinal(Field)<cardinal(FieldCount)) then begin
    if not Assigned(fFieldType) then
      InitFieldTypes;
    result := fFieldType[Field].ContentType;
  end else
    result := sftUnknown;
end;

function TSQLTable.FieldType(Field: integer; out FieldTypeInfo: PSQLTableFieldType): TSQLFieldType;
begin
  if (self<>nil) and (cardinal(Field)<cardinal(FieldCount)) then begin
    if not Assigned(fFieldType) then
      InitFieldTypes;
    FieldTypeInfo := @fFieldType[Field];
    result := FieldTypeInfo^.ContentType;
  end else begin
    FieldTypeInfo := nil;
    result := sftUnknown;
  end;
end;

function TSQLTable.Get(Row, Field: integer): PUTF8Char;
begin
  if (self=nil) or (fResults=nil) or (cardinal(Row)>cardinal(fRowCount)) or
     (cardinal(Field)>=cardinal(FieldCount)) then // cardinal() -> test <0
    result := nil else
................................................................................
    MoveFast(pointer(Trail)^,P[len^],length(Trail));
  tmp.Done;
end;

procedure TSQLTable.GetJSONValues(JSON: TStream; Expand: boolean;
  RowFirst, RowLast, IDBinarySize: integer);
var W: TJSONWriter;

    U: PPUTF8Char;
    f,r: integer;
    i64: Int64;
label nostr,str;
begin
  W := TJSONWriter.Create(JSON,Expand,false);
  try
    if (self=nil) or (FieldCount<=0) or (fRowCount<=0) then begin
      W.CancelAllVoid;
      exit;
    end;
................................................................................
      RowLast := fRowCount;
    if RowFirst<=0 then
      RowFirst := 1; // start reading after first Row (Row 0 = Field Names)
    // get col names and types
    if QueryTables<>nil then
      InitFieldTypes;
    SetLength(W.ColNames,FieldCount);

    for f := 0 to FieldCount-1 do begin
      W.ColNames[f] := fResults[f]; // first Row is field Names
      if not Assigned(OnExportValue) then
        if (f=fFieldIndexID) and (IDBinarySize>0) then


          W.ColNames[f] := 'id'; // ajax-friendly






    end;
    W.AddColumns(RowLast-RowFirst+1); // write or init field names (see JSON Expand)
    if Expand then
      W.Add('[');
    // write rows data
    U := @fResults[FieldCount*RowFirst];
    for r := RowFirst to RowLast do begin
      if Expand then
        W.Add('{');
      for f := 0 to FieldCount-1 do begin
        if Expand then
          W.AddString(W.ColNames[f]); // '"'+ColNames[]+'":'
        if Assigned(OnExportValue) then
          W.AddString(OnExportValue(self,r,f,false)) else
        if (IDBinarySize>0) and (f=fFieldIndexID) then begin
          SetInt64(U^,i64);
          W.AddBinToHexDisplayQuoted(@i64,IDBinarySize);
        end else
        if U^=nil then
          W.AddShort('null') else
        case fFieldType[f].ContentDB of
          ftInt64,ftDouble,ftCurrency:
nostr:      W.AddNoJSONEscape(U^,StrLen(U^));
          ftDate,ftUTF8,ftBlob: begin
str:        W.Add('"');
            W.AddJSONEscape(U^,StrLen(U^));
            W.Add('"');
          end;
          else if IsStringJSON(U^) then // fast and safe enough
            goto str else
            goto nostr;
        end;
        W.Add(',');
        inc(U); // points to next value
      end;
      W.CancelLastComma;
      if Expand then begin
        W.Add('}',',');
        if r<>RowLast then
          W.AddCR; // make expanded json more human readable
      end else
        W.Add(',');
    end;
    W.EndJSONObject(1,0); // "RowCount": set by W.AddColumns(RowLast-RowFirst+1)
  finally
    W.Free;
................................................................................
  // ftUnknown, ftNull, ftInt64, ftDouble, ftCurrency,
     '','',' dt:type="i8"',' dt:type="float"',' dt:type="number" rs:dbtype="currency"',
  // ftDate, ftUTF8, ftBlob
     ' dt:type="dateTime"',' dt:type="string"',' dt:type="bin.hex"');
var W: TJSONWriter;
    f,r: integer;
    U: PPUTF8Char;

begin
  W := TJSONWriter.Create(Dest,16384);
  try
    W.AddShort('<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" '+
      'xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" '+
      'xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">');
    if (self<>nil) and ((FieldCount>0) or (fRowCount>0)) then begin
      // retrieve normalized field names and types
      if length(fFieldNames)<>fFieldCount then
        InitFieldNames;
      if not Assigned(fFieldType) then
        InitFieldTypes;




      // check range
      if RowLast=0 then
        RowLast := fRowCount else
      if RowLast>fRowCount then
        RowLast := fRowCount;
      if RowFirst<=0 then
        RowFirst := 1; // start reading after first Row (Row 0 = Field Names)
................................................................................
      W.AddShort('<s:Schema id="RowsetSchema"><s:ElementType name="row" content="eltOnly">');
      for f := 0 to FieldCount-1 do begin
        W.AddShort('<s:AttributeType name="f');
        W.Add(f);
        W.AddShort('" rs:name="');
        W.AddString(fFieldNames[f]);
        W.Add('"');
        W.AddString(FIELDTYPE_TOXML[fFieldType[f].ContentDB]);
        W.Add('/','>');
      end;
      W.AddShort('</s:ElementType></s:Schema>');
      // write rows data
      U := @fResults[FieldCount*RowFirst];
      W.AddShort('<rs:data>');
      for r := RowFirst to RowLast do begin
        W.AddShort('<z:row ');
        for f := 0 to FieldCount-1 do begin
          if U^<>nil then begin
            W.Add('f');
            W.Add(f);
            W.Add('=','"');








            W.AddXmlEscape(U^);

            W.Add('"',' ');
          end;
          inc(U); // points to next value
        end;
        W.Add('/','>');
      end;
      W.AddShort('</rs:data>');
................................................................................
  '<manifest:file-entry manifest:full-path="meta.xml" manifest:media-type="text/xml"/><manifest:file-entry manifest:full-path="settings.xml" manifest:media-type="text/xml"/>'+
  '<manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/><manifest:file-entry manifest:full-path="styles.xml" manifest:media-type="text/xml"/></manifest:manifest>';
var Zip: TZipWriteToStream;
    Dest: TRawByteStringStream;
    content: RawUTF8;
    W: TTextWriter;
    U: PPUTF8Char;
    r,f: integer;

begin
  Dest := TRawByteStringStream.Create;
  try
    Zip := TZipWriteToStream.Create(Dest);
    try
      Zip.AddStored('mimetype',pointer(ODSmimetype),length(ODSmimetype));
      Zip.AddDeflated('styles.xml',pointer(ODSstyles),length(ODSstyles));
................................................................................
        if (self<>nil) and ((FieldCount>0) or (fRowCount>0)) then begin
          if withColumnTypes then begin
            // retrieve normalized field names and types
            if length(fFieldNames)<>fFieldCount then
              InitFieldNames;
            if not Assigned(fFieldType) then
              InitFieldTypes;




          end;
          // write column names
          W.AddShort('<table:table-row>');
          U := pointer(fResults);
          for f := 1 to FieldCount do begin
            W.AddShort('<table:table-cell office:value-type="string"><text:p>');
            W.AddXmlEscape(U^);
            W.AddShort('</text:p></table:table-cell>');
            inc(U); // points to next value
          end;
          W.AddShort('</table:table-row>');
          // and values
          for r := 1 to fRowCount do begin
            W.AddShort('<table:table-row>');
            if withColumnTypes then begin
              for f := 0 to FieldCount-1 do begin
                W.AddShort('<table:table-cell office:value-type="');
                case fFieldType[f].ContentDB of
                  ftInt64,ftDouble,ftCurrency: begin
                    W.AddShort('float" office:value="');
                    W.AddXmlEscape(U^);
                  end;
                  ftDate: begin
                    W.AddShort('date" office:date-value="');
                    W.AddXmlEscape(U^);
                  end;
                else //ftUnknown, ftNull, ftUTF8, ftBlob:
                  W.AddShort('string');
                end;
                W.AddShort('"><text:p>');
                if fFieldType[f].ContentDB in [ftUnknown, ftUTF8, ftBlob] then
                  W.AddXmlEscape(U^);
                W.AddShort('</text:p></table:table-cell>');
                inc(U); // points to next value
              end;
            end else
              for f := 0 to FieldCount-1 do begin
                W.AddShort('<table:table-cell office:value-type="string"><text:p>');
                W.AddXmlEscape(U^);
                W.AddShort('</text:p></table:table-cell>');
                inc(U);
              end;
            W.AddShort('</table:table-row>');
          end;
................................................................................
  StartRow, FieldIndex: integer; Client: TObject; Lang: TSynSoundExPronunciation;
  UnicodeComparison: boolean): integer;
var U: PPUTF8Char;
    Kind: TSQLFieldType;
    Search: PAnsiChar;
    UpperUnicode: RawUnicode;
    UpperUnicodeLen: integer;
    info: PSQLTableFieldType;
    Val64: Int64;
    ValTimeLog: TTimelogBits absolute Val64;
    i,err: integer;
    EnumValue: RawUTF8;
    s: string;
    P: PShortString;
    EnumValues: set of 0..63;
................................................................................
function TSQLTable.GetVariant(Row, Field: integer): Variant;
begin
  GetVariant(Row,Field,result);
end;

procedure TSQLTable.GetVariant(Row,Field: integer; var result: variant);
var aType: TSQLFieldType;
    info: PSQLTableFieldType;
begin
  if Row=0 then // Field Name
    RawUTF8ToVariant(GetU(0,Field),result) else begin
    aType := FieldType(Field,info);
    ValueVarToVariant(Get(Row,Field),aType,TVarData(result),true,info.ContentTypeInfo);
  end;
end;
................................................................................
    GetVariant(r,v,Result);
end;

{$endif NOVARIANTS}

function TSQLTable.ExpandAsString(Row, Field: integer; Client: TObject;
  out Text: string; const CustomFormat: string): TSQLFieldType;
var info: PSQLTableFieldType;
    err: integer;
    Value: Int64;
    ValueTimeLog: TTimeLogBits absolute Value;
    ValueDateTime: TDateTime;
    Ref: RecordRef absolute Value;
label IsDateTime;
begin // Text was already forced to '' because was defined as "out" parameter
................................................................................
  sftEnumerate, sftSet, sftRecord, sftID, sftTID, sftRecordVersion, sftSessionUserID,
  sftTimeLog, sftModTime, sftCreateTime, sftUnixTime, sftUnixMSTime: begin
    Value := GetInt64(Get(Row,Field),err);
    if err<>0 then
      // not an integer -> to be displayed as sftUTF8Text
      result := sftUTF8Text else
    case result of
      sftEnumerate:
        if info.ContentTypeInfo<>nil then begin
          Text := PEnumType(info.ContentTypeInfo)^.GetCaption(Value);
          exit;
        end;
      sftTimeLog, sftModTime, sftCreateTime:
        goto IsDateTime;
      sftUnixTime: begin
        ValueTimeLog.FromUnixTime(Value);
        goto IsDateTime;
      end;
      sftUnixMSTime: 
................................................................................
  Update(Row,Field,U);
end;
{$endif NOVARIANTS}

procedure TSQLTableWritable.Join(From: TSQLTable; const FromKeyField, KeyField: RawUTF8);
var fk,dk,f,i,k,ndx: integer;
    n,fn: RawUTF8;
    info: PSQLTableFieldType;
    new: TIntegerDynArray;
begin
  dk := FieldIndexExisting(KeyField);
  SetLength(new,FieldCount);
  fk := From.FieldIndexExisting(FromKeyField);
  From.SortFields(fk); // faster merge with O(log(n)) binary search
  for f := 0 to From.FieldCount-1 do // add From fields (excluding FromKeyField)
................................................................................
      if From.FieldType(f,info)=sftUnknown then  // set TSQLTableFieldType
        i := AddField(fn) else
        if info.TableIndex>=0 then
          i := AddField(fn,From.QueryTables[info.TableIndex],n) else begin
          i := AddField(fn);
          if i>=length(fFieldType) then
            SetLength(fFieldType,i+1);
          fFieldType[i] := info^;
        end;
      new[f] := i;
    end;
  ndx := FieldCount;
  for i := 1 to RowCount do begin // merge data
    k := From.SearchFieldSorted(fResults[ndx+dk],fk);
    if k>0 then begin 
................................................................................
    if IdemPropName(BlobFields[i].PropInfo^.Name,PropName,PropNameLen) then begin
      result := BlobFields[i].PropInfo;
      exit;
    end;
  result := nil;
end;





function TSQLRecordProperties.SQLFieldTypeToSQL(FieldIndex: integer): RawUTF8;
const
  /// simple wrapper from each SQL used type into SQLite3 field datatype
  // - set to '' for fields with no column created in the database
  DEFAULT_SQLFIELDTYPETOSQL: array[TSQLFieldType] of RawUTF8 =
   ('',                              // sftUnknown
    ' TEXT COLLATE NOCASE, ',        // sftAnsiText

'1.18.4163'

'1.18.4164'