- introducing TSQLRestClientURI.InternalCheckOpen/InternalClose methods to
      properly handle remote connection and re-connection
    - added TSQLRestClientURI.LastErrorCode/LastErrorMessage/LastErrorException
      properties, to retrieve additional information about remote URL() execution
    - added TSQLRestClientURI.ServiceRegister() and ServiceRegisterClientDriven()
      methods for easier Client-side interface-based services initialization
    - added JSONFileToObject() and ObjectToJSONFile() functions


    - speed up of TSQLTable.FieldIndex() method (using binary search)
    - added TSQLTable.ToObjectList() and ToObjectList<T: class>() methods
    - added TSQLTable.Step(), FieldBuffer() and Field() methods, handling a
      cursor at TSQLTable level, with optional late-binding column access
    - added TSQLRestClientURI.ServerTimeStampSynchronize method to force time
      synchronization with the server - can be handy to test the connection 
    - added TSQLRest.TableHasRows/TableRowCount methods, and overriden direct
      implementation for TSQLRestServer/TSQLRestServerStaticInMemory (including
      SQL pattern recognition for TSQLRestServerStaticInMemory)
................................................................................
{$ifdef SSPIAUTH}
  SynSSPIAuth,
{$endif}
  SynCommons;



{ ************ classes to access ORM database data }





















{.$define PUBLISHRECORD}
// - if defined, a sftBlobRecord will be defined in order to serialize published
// properties with some record type: but even if such properties can be defined,
// there is no RTTI generated for them: so it won't work :( see
// http://docwiki.embarcadero.com/RADStudio/en/Classes_and_Objects#Published_Members
// - should be defined globaly, e.g. in Synopse.inc
................................................................................
  // - this constant is used internaly to optimize memory usage in the
  // generated asm code
  // - you should not change it to a value lower than expected in an existing
  // database (e.g. as expected by TSQLAccessRights or such)
  MAX_SQLTABLES = 256;


  /// the used TAuthSession.IDCardinal value if the session not started yet
  // - i.e. if the session handling is still in its handshaking phase
  CONST_AUTHENTICATION_SESSION_NOT_STARTED = 0;

  /// the used TAuthSession.IDCardinal value if authentication mode is not set
  // - i.e. if TSQLRest.HandleAuthentication equals FALSE
  CONST_AUTHENTICATION_NOT_USED = 1;

type
  /// used to store bit set for all available Tables in a Database Model
  TSQLFieldTables = set of 0..MAX_SQLTABLES-1;

  /// a String used to store the BLOB content
  // - equals RawByteString for byte storage, to force no implicit charset
  // conversion, thatever the codepage of the resulting string is
................................................................................
    soInsert,
    soUpdate,
    soDelete);

  /// used to defined a set of CRUD associated SQL statement of a command
  TSQLOccasions = set of TSQLOccasion;

  /// abstract array of classes, will be filled e.g. with TSQLRecord descendants
  TClasses = array of TClass;

  /// 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
  // - GetA() or GetW() can be used in a TDrawString
  // - will be implemented as TSQLTableDB for direct SQLite3 database engine call,
  //  or as TSQLTableJSON for remote access through optimized JSON messages
  TSQLTable = class
  private
    fQueryTables: TClasses;
    fQuerySQL: RawUTF8;
    fFieldLengthMean: TIntegerDynArray;
    fFieldLengthMeanSum: integer;
  protected
    fRowCount,
    fFieldCount: integer;
    /// contains the data, as returned by sqlite3_get_table()
    fResults: PPUTF8CharArray;
    /// contains the TSQLFieldType and TypeInfo(enumerate), after calculation
    // from the fQueryTables values
    fFieldType: array of record
      // the field kind
      ContentType: TSQLFieldType;
      // initialized for both sftEnumerate and sftSet field kinds
      EnumTypeInfo: pointer;
      // the corresponding index in fQueryTables[]
      TableIndex: integer;
    end;
    /// used by FieldIndex() for fast binary searcg
    fFieldNameOrder: TCardinalDynArray;
    /// contain the fResults[] pointers, after a IDColumnHide() call
    fIDColumn, fNotIDColumn: array of PUTF8Char;
    /// bit set at parsing to mark was a string value (e.g. "..." in JSON)
    fFieldParsedAsString: set of 0..255;
    /// index of a 'ID' field, -1 if none (e.g. after IDColumnHide method call)
    fFieldIndexID: integer;
    /// the internal state counter of the database when the data was retrieved
    fInternalState: cardinal;
    /// contains the parameters used for sorting
    fSortParams: TSQLTableSortParams;
    /// contains the TSQLRecord instances created by NewRecord method
    fOwnedRecords: TObjectList;
    /// if the TSQLRecord is the owner of this table, i.e. if it must free it
    fOwnerMustFree: Boolean;
    /// current cursor row (1..RowCount), as set by the Step() method
    fStepRow: integer;
    /// fill the fFieldType[] array (from fQueryTables[] or fResults[] content)
    procedure InitFieldTypes;
  public
    /// initialize the result table
    // - you can use RecordClassesToClasses() wrapper function to convert an
    // array of TSQLRecordClass into the expected array of TClass
    constructor Create(const Tables: array of TClass; const aSQL: RawUTF8);
    /// free associated memory and owned records
    destructor Destroy; override;
    {/ read-only access to a particular field value, as UTF-8 encoded buffer
     - points to memory buffer allocated by Init() }
    function Get(Row,Field: integer): PUTF8Char;
    {/ read-only access to a particular field value, as RawUTF8 text }
    function GetU(Row,Field: integer): RawUTF8;
    {/ read-only access to a particular field value, as Win Ansi text }
    function GetA(Row,Field: integer): WinAnsiString;
    {/ read-only access to a particular field value, as Win Ansi text shortstring}
    function GetS(Row,Field: integer): shortstring;
    {$ifdef USEVARIANTS}
    {/ read-only access to a particular field value, as a Variant
     - will try to use the most approriate Variant type for conversion (will
       use e.g. TDateTime for sftDateTime or sftTimeLog, Extended for sftFloat...)
     - will handle any necessary conversion to VCL string text, ready to be displayed
     - the global UTF8ToString() function will be used for the conversion:
       for proper i18n handling before Delphi 2009, you should use the
       overloaded method with aUTF8ToString=Language.UTF8ToString }
    function GetVariant(Row,Field: integer; Client: TObject): variant; overload;
    {/ read-only access to a particular field value, as a Variant
     - will try to use the most approriate Variant type for conversion (will
       use e.g. TDateTime for sftDateTime or sftTimeLog, Extended for sftFloat...)
     - will handle any necessary conversion to VCL string text, ready to be displayed
     - the global UTF8ToString() function will be used for the conversion:
       for proper i18n handling before Delphi 2009, you should use the
       overloaded method with aUTF8ToString=Language.UTF8ToString }
    procedure GetVariant(Row,Field: integer; Client: TObject; var result: variant); overload;
    {$endif}
    {/ read-only access to a particular field value, as VCL string text
     - the global UTF8ToString() function will be used for the conversion:
     for proper i18n handling before Delphi 2009, you should use the
     overloaded method with aUTF8ToString=Language.UTF8ToString }
    function GetString(Row,Field: integer): string;
    {/ fill a unicode buffer with a particular field value
      - return number of wide characters written in Dest^ }
    function GetWP(Row,Field: integer; Dest: PWideChar; MaxDestChars: cardinal): integer;
    {/ read-only access to a particular field value, as UTF-16 Unicode text
      - Raw Unicode is WideChar(zero) terminated
      - its content is allocated to contain all WideChars (not trimed to 255,
        like GetWP() above }
    function GetW(Row,Field: integer): RawUnicode;
    {/ read-only access to a particular field value, as integer value }
    function GetAsInteger(Row,Field: integer): integer;
      {$ifdef HASINLINE}inline;{$endif}
    {/ read-only access to a particular field value, as Int64 value }
    function GetAsInt64(Row,Field: integer): Int64;
      {$ifdef HASINLINE}inline;{$endif}
    {/ read-only access to a particular field value, ready to be displayed
      - mostly used with Row=0, i.e. to get a display value from a field name
      - use "string" type, i.e. UnicodeString for Delphi 2009+
      - value is first un-camel-cased: 'OnLine' value will return 'On line' e.g.
      - then System.LoadResStringTranslate() is called if available }
    function GetCaption(Row,Field: integer): string;
    {/ read-only access to a particular Blob value
     - a new TSQLRawBlob is created
     - Blob data is converted from SQLite3 BLOB literals (X'53514C697465' e.g.)
       or Base-64 encoded content ('\uFFF0base64encodedbinary')
     - prefered manner is to directly use REST protocol to retrieve a blob field }
    function GetBlob(Row,Field: integer): TSQLRawBlob;
    {/ read-only access to a particular Blob value
     - a new TBytes is created
     - Blob data is converted from SQLite3 BLOB literals (X'53514C697465' e.g.)
       or Base-64 encoded content ('\uFFF0base64encodedbinary')
     - prefered manner is to directly use REST protocol to retrieve a blob field }
    function GetBytes(Row,Field: integer): TBytes;
    {/ read-only access to a particular Blob value
     - a new TCustomMemoryStream is created - caller shall free its instance
     - Blob data is converted from SQLite3 BLOB literals (X'53514C697465' e.g.)
       or Base-64 encoded content ('\uFFF0base64encodedbinary')
     - prefered manner is to directly use REST protocol to retrieve a blob field }
    function GetStream(Row,Field: integer): TStream;
    {/ read-only access to a particular field value, as VCL text
     - Client is one TSQLClient instance (used to display TRecordReference via
       the associated TSQLModel)
     - returns the Field Type
     - return generic string Text, i.e. UnicodeString for Delphi 2009+,
       ready to be displayed to the VCL, for sftEnumerate, sftTimeLog
       and sftRecord/sftID
     - returns '' as string Text, if text can by displayed directly
       with Get*() methods above
     - returns '' for other properties kind, if UTF8ToString is nil,
       or the ready to be displayed value if UTF8ToString event is set
       (to be used mostly with Language.UTF8ToString) }
    function ExpandAsString(Row,Field: integer; Client: TObject; out Text: string): TSQLFieldType;
    {/ read-only access to a particular field value, as VCL text
     - this method is just a wrapper around ExpandAsString method, returning
       the content as a SynUnicode string type (i.e. UnicodeString since Delphi
       2009, and WideString for non Unicode versions of Delphi)
     - it is used by the reporting layers of the framework (e.g. TSQLRibbon.AddToReport) }
    function ExpandAsSynUnicode(Row,Field: integer; Client: TObject; out Text: SynUnicode): TSQLFieldType;
    {/ read-only access to a particular DateTime field value
     - expect SQLite3 TEXT field in ISO 8601 'YYYYMMDD hhmmss' or
      'YYYY-MM-DD hh:mm:ss' format }
    function GetDateTime(Row,Field: integer): TDateTime;
      {$ifdef PUREPASCAL} {$ifdef HASINLINE}inline;{$endif} {$endif}
    {/ read-only access to a particular TTimeLog field value
      - return the result as Iso8601.Text() Iso-8601 encoded text }
    function GetTimeLog(Row,Field: integer; Expanded: boolean; FirstTimeChar: AnsiChar = 'T'): RawUTF8;
    {/ widechar length (UTF-8 decoded) of a particular field value }
    function LengthW(Row,Field: integer): integer;
    {/ get all values for a specified field into a dynamic RawUTF8 array
     - don't perform any conversion, but just create an array of raw PUTF8Char data }
    procedure GetRowValues(Field: integer; out Values: TRawUTF8DynArray); overload;
    {/ get all values for a specified field into a dynamic Integer array }
    procedure GetRowValues(Field: integer; out Values: TIntegerDynArray); overload;
    {/ get all values for a specified field as CSV
     - don't perform any conversion, but create a CSV from raw PUTF8Char data }
    function GetRowValues(Field: integer; Sep: AnsiChar=','): RawUTF8; overload;

    /// save the table values in JSON format
    // - JSON data is added to TStream, with UTF-8 encoding
    // - if Expand is true, JSON data is an array of objects, for direct use
    // with any Ajax or .NET client:
    // & [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]
    // - if Expand is false, JSON data is serialized (used in TSQLTableJSON)
    // & { "fieldCount":1,"values":["col1","col2",val11,"val12",val21,..] }
    // - RowFirst and RowLast can be used to ask for a specified row extent
    // of the returned data (by default, all rows are retrieved)
    procedure GetJSONValues(JSON: TStream; Expand: boolean;
      RowFirst: integer=0; RowLast: integer=0); overload;
    /// same as above, but returning result into a RawUTF8
    function GetJSONValues(Expand: boolean): RawUTF8; overload;
    /// save the table in CSV format
    // - if Tab=TRUE, will use TAB instead of ',' between columns
    // - you can customize the ',' separator - use e.g. the global ListSeparator
    // variable (from SysUtils) to reflect the current system definition (some
    // country use ',' as decimal separator, for instance our "douce France")
    // - AddBOM will add a UTF-8 Byte Order Mark at the beginning of the content
    procedure GetCSVValues(Dest: TStream; Tab: boolean; CommaSep: AnsiChar=',';
      AddBOM: boolean=false);

    {/ get the Field index of a FieldName
     - return -1 if not found, index (0..FieldCount-1) if found }
    function FieldIndex(FieldName: PUTF8Char): integer; overload;
    {/ get the Field index of a FieldName
     - return -1 if not found, index (0..FieldCount-1) if found }
    function FieldIndex(const FieldName: RawUTF8): integer; overload;
      {$ifdef HASINLINE}inline;{$endif}
    {/ get the Field content (encoded as UTF-8 text) from a property name
     - return nil if not found }
    function FieldValue(const FieldName: RawUTF8; Row: integer): PUTF8Char;
    {/ sort result Rows, according to a specific field
     - default is sorting by ascending order (Asc=true)
     - you can specify a Row index to be updated during the sort in PCurrentRow
     - sort is very fast, even for huge tables (more faster than any indexed
       SQL query): 500,000 rows are sorted instantly
     - this optimized sort implementation does the comparaison first by the
     designed field, and, if the field value is identical, the ID value is
     used (it will therefore sort by time all identical values) }
    procedure SortFields(Field: integer; Asc: boolean=true;
      PCurrentRow: PInteger=nil; FieldType: TSQLFieldType=sftUnknown);
    {/ sort result Rows, according to the Bits set to 1 first }
    procedure SortBitsFirst(var Bits);
    {/ 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; otherwize, 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; EnumTypeInfo: PPointer): TSQLFieldType;
    {/ 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)
    // - return the sum of all mean of character lengths
    function CalculateFieldLengthMean(var aResult: TIntegerDynArray;
      FromDisplay: boolean=false): integer;
    /// get the mean of characters length of this field
    // - the character length is for the first line of text only (stop counting
    // at every newline character, i.e. #10 or #13 char)
    // - very fast: calculated only once for all fields
    function FieldLengthMean(Field: integer): cardinal;
    /// get the sum of all mean of characters length of all fields
    // - very fast: calculated only once for all fields
    function FieldLengthMeanSum: cardinal;
    /// get the maximum number of characters of this field
    function FieldLengthMax(Field: integer; NeverReturnsZero: boolean=false): cardinal;
    /// get the record class (i.e. the table) associated to a field
    // - is nil if this table has no QueryTables property
    // - very fast: calculated only once for all fields
    function FieldTable(Field: integer): TClass;
    /// force the mean of characters length for every field
    // - expect as many parameters as fields in this table
    // - override internal fFieldLengthMean[] and fFieldLengthMeanSum values
    procedure SetFieldLengthMean(const Lengths: array of cardinal);
    /// 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
      be destroyed before this TSQLTable }
    procedure Assign(source: TSQLTable);

    /// search a text value inside the table data in a specified field
    // - the text value must already be uppercased 7-bits ANSI encoded
    // - return the Row on success, 0 on error
    // - search only in the content of FieldIndex data
    // - you can specify a Soundex pronunciation to use, or leave as sndxNone for
    // standard case insensitive character match; aUpperValue can optional
    // indicate a Soundex search, by predeceding the searched text with % for
    // English, %% for French or %%% for Spanish (only works with WinAnsi
    // char set - i.e. code page 1252)
    // - if UnicodeComparison is set to TRUE, search will use low-level Windows
    // API for Unicode-level conversion - it will be much slower, but accurate
    // for the whole range of UTF-8 encoding
    // - if UnicodeComparison is left to FALSE, UTF-8 decoding will be done only
    // if necessary: it will work only with standard western-occidental alphabet
    // (i.e. WinAnsi - code page 1252), but it will be very fast
    function SearchValue(const aUpperValue: RawUTF8; StartRow, FieldIndex: integer;
      Client: TObject; Lang: TSynSoundExPronunciation=sndxNone;
      UnicodeComparison: boolean=false): integer; overload;
    /// search a text value inside the table data in all fields
    // - the text value must already be uppercased 7-bits ANSI encoded
    // - return the Row on success, 0 on error
    // - search on all fields, returning field found in FieldIndex (if not nil)
    // - you can specify a Soundex pronunciation to use, or leave as sndxNone for
    // standard case insensitive character match; aUpperValue can optional
    // indicate a Soundex search, by predeceding the searched text with % for
    // English, %% for French or %%% for Spanish (only works with WinAnsi
    // char set - i.e. code page 1252)
    // - if UnicodeComparison is set to TRUE, search will use low-level Windows
    // API for Unicode-level conversion - it will be much slower, but accurate
    // for the whole range of UTF-8 encoding
    // - if UnicodeComparison is left to FALSE, UTF-8 decoding will be done only
    // if necessary: it will work only with standard western-occidental alphabet
    // (i.e. WinAnsi - code page 1252), but it will be very fast
    function SearchValue(const aUpperValue: RawUTF8; StartRow: integer;
      FieldIndex: PInteger; Client: TObject; Lang: TSynSoundExPronunciation=sndxNone;
      UnicodeComparison: boolean=false): integer; overload;
    /// search for a value inside the raw table data
    // - returns 0 if not found, or the matching Row number otherwise
    function SearchFieldEquals(const aValue: RawUTF8; FieldIndex: integer): integer;

    /// if the ID column is available, hides it from fResults[]
    // - usefull for simplier UI, with a hidden ID field
    // - use IDColumnHiddenValue() to get the ID of a specific row
    // - return true is ID was succesfully hidden, false if not possible
    function IDColumnHide: boolean;
    /// return the (previously hidden) ID value, 0 on error
    function IDColumnHiddenValue(Row: integer): integer;
    /// return all (previously hidden) ID values
    procedure IDColumnHiddenValues(var IDs: TIntegerDynArray);
    /// get all IDs where individual bit in Bits are set
    procedure IDArrayFromBits(const Bits; var IDs: TIntegerDynArray);
    /// get all individual bit in Bits corresponding to the supplied IDs
    // - warning: IDs integer array will be sorted within this method call
    procedure IDArrayToBits(var Bits; var IDs: TIntegerDynArray);
    /// get the Row index corresponding to a specified ID
    // - return the Row number, from 1 to RowCount
    // - return RowCount (last row index) if this ID was not found or no
    // ID field is available
    function RowFromID(aID: integer): integer;

    /// delete the specified data Row from the Table
    // - only overwrite the internal fResults[] pointers, don't free any memory,
    // nor modify the internal DataSet
    procedure DeleteRow(Row: integer);
    /// delete the specified Column text from the Table
    // - don't delete the Column: only delete UTF-8 text in all rows for this
    // field
    procedure DeleteColumnValues(Field: integer);

    /// retrieve QueryTables[0], if existing
    function QueryRecordType: TClass;

    /// create a new TSQLRecord instance for a specific Table
    // - use the specified TSQLRecord class or create one instance
    // of the first associated record class (from internal QueryTables[])
    // - use this method to create a working copy of a table's record, e.g.
    // - the record will be freed when the TSQLTable will be destroyed:
    // you don't need to make a Try..Finally..Free..end block with it
    function NewRecord(RecordType: TClass=nil): TObject;
    /// fill a TObjectList with TSQLRecord instances corresponding to this Table
    // - use the specified TSQLRecord class or create instances
    // of the first associated record class (from internal QueryTables[])
    // - returns nil if T does not inherit from TSQLRecord
    procedure ToObjectList(DestList: TObjectList; RecordType: TClass=nil); overload;
    {$ifdef UNICODE}
    /// fill a TObjectList<TSQLRecord> with TSQLRecord instances corresponding
    // - use the specified TSQLRecord class or create instances
    // of the first associated record class (from internal QueryTables[])
    // - returns a void list if T does not inherit from TSQLRecord
    function ToObjectList<T: class>: TObjectList<T>; overload;
    {$endif}

    {/ After a TSQLTable has been initialized, this method can be called
      one or more times to iterate through all data rows
     - you shall call this method before calling FieldBuffer()/Field() methods
     - return TRUE on success, with data ready to be retrieved by Field*()
     - return FALSE if no more row is available (i.e. exceeded RowCount)
     - if SeekFirst is TRUE, will put the cursor on the first row of results,
       otherwise, it will fetch one row of data, to be called within a loop
     - you can specify a variant instance (e.g. allocated on the stack) in
       optional RowVariant parameter, to access field values using late binding
     - typical use may be:
       ! while TableCustomers.Step do
       !   writeln(Field('name'));
     - or, when using a variant and late-binding:
       ! var customer: variant;
       ! ...
       !   while TableCustomers.Step(false,@customer) do
       !     writeln(customer.Name);
     }
    function Step(SeekFirst: boolean=false; RowVariant: PVariant=nil): boolean;
    /// read-only access to a particular field value, as UTF-8 encoded buffer
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - similar to Get() method, but for the current Step
    function FieldBuffer(FieldIndex: Integer): PUTF8Char; overload;
    /// read-only access to a particular field value, as UTF-8 encoded buffer
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - similar to Get() method, but for the current Step
    function FieldBuffer(const FieldName: RawUTF8): PUTF8Char; overload;
    {$ifdef USEVARIANTS}
    /// read-only access to a particular field value, as a variant
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - will call GetVariant() method for appropriate data conversion
    function Field(FieldIndex: integer): variant; overload;
    /// read-only access to a particular field value, as a variant
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - will call GetVariant() method for appropriate data conversion
    function Field(const FieldName: RawUTF8): variant; overload;
    {$endif}

    /// contains the associated record class on Query
    property QueryTables: TClasses read fQueryTables;
    /// contains the associated SQL statement on Query
    property QuerySQL: RawUTF8 read fQuerySQL;
    {/ read-only access to the number of data Row in this table
     - first row contains field name
     - then 1..RowCount rows contain the data itself }
    property RowCount: integer read fRowCount;
    {/ read-only access to the number of fields for each Row in this table }
    property FieldCount: integer read fFieldCount;
    /// read-only acccess to the current Row number, after a Step() call
    // - contains 0 if accessed outside valid Step() sequence call
    // - contains 1..RowCount after a valid Step() iteration
    property StepRow: integer read fStepRow;
    {/ this property contains the internal state counter of the server database
      when the data was retrieved from it
      - can be used to check if retrieved data may be out of date }
    property InternalState: cardinal read fInternalState write fInternalState;
    /// if the TSQLRecord is the owner of this table, i.e. if it must free it
    property OwnerMustFree: Boolean read fOwnerMustFree write fOwnerMustFree;
  end;

{$ifdef USEVARIANTS}
  /// a custom variant type used to have direct access to TSQLTable content
  // - use TSQLTable.Step(..,@Data) method to retrieve such a Variant
  TSQLTableRowVariantType = class(TSynInvokeableVariantType)
  protected
    procedure IntGet(var Dest: TVarData; const V: TVarData; Name: PAnsiChar); override;
    procedure IntSet(const V, Value: TVarData; Name: PAnsiChar); override;
  end;
{$endif USEVARIANTS}


  { Why use JSON? (extracted from the main framework documentation)
    - The JavaScript Object Notation (JSON) is a lightweight computer data
     interchange format
    - Like XML, it's a text-based, human-readable format for representing
     simple data structures and associative arrays (called objects)
    - It's easier to read, quicker to implement and smaller in size than XML
    - It's a very efficient format for cache
    - It's natively supported by the JavaScript language, making it a perfect
     serialization format for any Ajax application
    - The JSON format is specified in http://tools.ietf.org/html/rfc4627
    - The default text encoding for both JSON and SQLite3 is UTF-8, which
     allows the full Unicode charset to be stored and communicated
    - It is the default data format used by ASP.NET AJAX services created in
     Windows Communication Foundation (WCF) since .NET framework 3.5
    - For binary blob transmission, we simply encode the binary data as hexa
     using the SQLite3 BLOB literals format : hexadecimal data preceded by
     a single "x" or "X" character (for example: X'53514C697465'), or Base64
     encoding - see BlobToTSQLRawBlob() function }

  /// get a SQL result from a JSON message, and store it into its own memory
  TSQLTableJSON = class(TSQLTable)
  protected
    /// used if a private copy of the JSON buffer is needed
    fPrivateCopy: RawUTF8;
    /// contains the pointers of start of every field value in JSONData
    fJSONResults: array of PUTF8Char;
    /// contain the hash value of the last JSON data sent to ContentChanged()
    // - used to don't repeat parsing if data has not been changed
    fPrivateCopyHash: cardinal;
    /// fill the result table content from a JSON-formated Data message
    // - returns TRUE on parsing success
    // - returns FALSE if no valid JSON data was found
    // - update all content fields (fResults[], fRowCount, fFieldCount, etc...)
    // - expect the UTF-8 Buffer in either TSQLRequest.EngineExecute(DB,SQL,JSON)
    // format (i.e. expanded) or either in a not expanded format (as an
    // AJAX-ready array of objects)
    // - the conversion into PPUTF8CharArray is made inplace and is very fast
    // (no additional memory buffer is allocated)
    function ParseAndConvert(Buffer: PUTF8Char; BufferLen: integer): boolean;
    /// will check then set (if needed) internal fPrivateCopy[Hash] values
    // - returns TRUE if content changed (then fPrivateCopy+fPrivateCopyHash
    // will be updated)
    function PrivateCopyChanged(aJSON: PUTF8Char; aLen: integer): boolean;
  public
    /// create the result table from a JSON-formated Data message
    // - the JSON data is parsed and formatted in-place
    // - please note that the supplied JSON buffer content will be changed:
    // if you want to reuse this JSON content, you shall make a private copy
    // before calling this constructor and you shall NOT release the corresponding
    // variable (fResults/JSONResults[] will point inside this memory buffer):
    // use instead the overloaded Create constructor expecting aJSON parameter
    // making a private copy of the data
    // - you can use RecordClassesToClasses() wrapper function to convert an
    // array of TSQLRecordClass into the expected array of TClass
    constructor Create(const Tables: array of TClass; const aSQL: RawUTF8;
      JSONBuffer: PUTF8Char; JSONBufferLen: integer); overload;
    /// create the result table from a JSON-formated Data message
    // - the JSON data is parsed and formatted in-place, after having been
    // copied in the protected fPrivateCopy variable
    // - you can use RecordClassesToClasses() wrapper function to convert an
    // array of TSQLRecordClass into the expected array of TClass
    constructor Create(const Tables: array of TClass; const aSQL, aJSON: RawUTF8); overload;
    /// update the result table content from a JSON-formated Data message
    // - return true on parsing success, false if no valid JSON data was found
    // - set Refreshed to true if the content changed
    // - update all content fields (fResults[], fRowCount, fFieldCount, etc...)
    // - call SortFields() or IDColumnHide if was already done for this TSQLTable
    // - the conversion into PPUTF8CharArray is made inplace and is very fast
    // (only one memory buffer is allocated for the whole data)
    function UpdateFrom(const aJSON: RawUTF8; var Refreshed: boolean;
      PCurrentRow: PInteger): boolean;
    /// the private copy of the processed data buffer
    // - available e.g. for Create constructor using aJSON parameter,
    // or after the UpdateFrom() process
    // - this buffer is not to be access directly: this won't be a valid JSON
    // content, but a processed buffer, on which fResults[] elements point to
    property PrivateInternalCopy: RawUTF8 read fPrivateCopy;
  end;

const
  /// kind of fields not retrieved during normal query, update or adding
  NOT_SIMPLE_FIELDS: TSQLFieldTypes =
    [sftUnknown, sftBlob, sftMany];

  /// kind of fields which can be copied from one TSQLRecord instance to another
  COPIABLE_FIELDS: TSQLFieldTypes =
................................................................................
// - this function will call RemoveCommentsFromJSON() before process
function JSONFileToObject(const JSONFile: TFileName; var ObjectInstance;
  TObjectListItemClass: TClass=nil): boolean;

/// persist a class instance into a JSON file
procedure ObjectToJSONFile(Value: TObject; const JSONFile: TFileName;
  Options: TTextWriterWriteObjectOptions=[woHumanReadable]);



{ ************ some RTTI and SQL mapping routines }

{ type definitions below were adapted from TypInfo.pas
 - this implementation doesn't require to include Variant.pas any more (which
  allow easy server-side compile with LVCL, e.g.)
................................................................................
  SECURITY_DESCRIPTOR_REVISION = 1;
  SECURITY_DESCRIPTOR_MIN_LENGTH = 20;
{$ifend}
{$endif}
{$endif}

const
  /// maximum number of the locked record in a Table (used in TSQLLocks)
  // - code is somewhat faster and easier with a fixed cache size
  // - 512 seems big enough on practice
  MAX_SQLLOCKS = 512;

  /// maximum handled dimension for TSQLRecordRTree
  // - this value is the one used by SQLite3 R-Tree virtual table
  RTREE_MAX_DIMENSION = 5;

  /// used as "stored AS_UNIQUE" published property definition in TSQLRecord 
  AS_UNIQUE = false;

  /// HTML Status Code for "Success"
  HTML_SUCCESS = 200;
  /// HTML Status Code for "Created"
  HTML_CREATED = 201;
  /// HTML Status Code for "Not Modified"
  HTML_NOTMODIFIED = 304;
  /// HTML Status Code for "Bad Request"
................................................................................
  HTML_NOTIMPLEMENTED = 501;
  /// HTML Status Code for "Service Unavailable"
  HTML_UNAVAILABLE = 503;

/// convert any HTML_* constant to a short English text
procedure StatusCodeToErrorMsg(Code: integer; var result: RawUTF8);


























type
  /// exception raised in case of incorrect TSQLTable.Step / Field*() use
  ESQLTableException = class(ESynException);

  /// generic parent class of all custom Exception types of this unit
  EORMException = class(ESynException);

................................................................................
  /// exception dedicated to interface based service implementation
  EServiceException = class(EORMException);

  TSQLModel = class;
  TSQLRest = class;
  TSQLRestClient = class;
  TSQLModelRecordProperties = class;


{$M+} { we need the RTTI information to be compiled for the published
        properties of these classes and their children (like TPersistent),
        to enable ORM - must be defined at the forward definition level }
  TSQLRecord = class;      // published properties = ORM fields/columns
  TSQLRecordMany = class;
  TSQLAuthUser = class;
  TSQLRestServer = class;  // published methods = RESTful callbacks handlers
  TSQLRestClientURI = class;
{$M-}

  /// class-reference type (metaclass) of TSQLRecord
  TSQLRecordClass = class of TSQLRecord;








  /// information about a TSQLRecord class property
  // - sftID for TSQLRecord properties, which are pointer(RecordID), not
  // any true class instance
  // - sftMany for TSQLRecordMany properties, for which no data is
  // stored in the table itself, but in a pivot table
  // - sftObject for e.g. TStrings TRawUTF8List TCollection instances
................................................................................
    procedure SetValue(Instance: TObject; Value: PUTF8Char; wasString: boolean); override;
    procedure GetValueVar(Instance: TObject; ToSQL: boolean;
      var result: RawUTF8; wasSQLString: PBoolean); override;
    procedure GetBinary(Instance: TObject; W: TFileBufferWriter); override;
    function SetBinary(Instance: TObject; P: PAnsiChar): PAnsiChar; override;
  end;

  PClass = ^TClass;
  PSQLRecordClass = ^TSQLRecordClass;

  /// class-refrence type (metaclass) for a TSynFilter or a TSynValidate
  TSynFilterOrValidateClass = class of TSynFilterOrValidate;

  /// a dynamic array used to store the TSQLRecord classes in a Database Model
  TSQLRecordClassDynArray = array of TSQLRecordClass;

  /// the kind of SQlite3 (virtual) table
  // - TSQLRecordFTS3 will be associated with vFTS3, TSQLRecordFTS4 with vFTS4,
  // TSQLRecordRTree with vRTree, any native SQlite3 table as vSQLite3, and
  // a TSQLRecordVirtualTable*ID with rCustomForcedID/rCustomAutoID
  // - a plain TSQLRecord class can be defined as rCustomForcedID (e.g. for
  // TSQLRecordMany) after registration for an external DB via a call to
  // VirtualTableExternalRegister() from SQLite3DB unit
................................................................................
    {/ this property contains the internal state counter of the server database
      when the data was retrieved from it
      - may be used to check if retrieved data may be out of date }
    property InternalState: cardinal read fInternalState;
  published
    { published properties in inherited classes will be interpreted as SQL fields }
  end;

































































































































































































































































































































































































































































































  PSQLLocks = ^TSQLLocks;
  /// used to store the locked record list, in a specified table
  // - the maximum count of the locked list if fixed to 512 by default,
  // which seems correct for common usage
  TSQLLocks = {$ifndef ISDELPHI2010}object{$else}record{$endif}
    /// the number of locked records stored in this object
................................................................................
    // - TSQLRecordClass to be specified as its index in Rest.Model.Tables[]
    procedure NotifyDeletion(aTableIndex, aID: integer); overload;
  end;

  TSQLRestClass = class of TSQLRest;

  /// a generic REpresentational State Transfer (REST) client/server class
  TSQLRest = class(TObject)
  protected
    fModel: TSQLModel;
    fCache: TSQLRestCache;
    fTransactionActive: cardinal;
    fTransactionTable: TSQLRecordClass;
    fTransactionCriticalSession: TRTLCriticalSection;
    fAcquireWriteTimeOut: cardinal;
................................................................................

/// create a TRecordReference with the corresponding parameters
function RecordReference(Model: TSQLModel; aTable: TSQLRecordClass; aID: integer): TRecordReference;

/// convert a dynamic array of TRecordReference into its corresponding IDs
procedure RecordRefToID(var aArray: TPtrUIntDynArray);

/// wrapper to convert an array of TSQLRecordClass to a generic array of TClass
// - used e.g. by TSQLRestClientURI.List before calling TSQLTableJSON.Create
function RecordClassesToClasses(const Tables: array of TSQLRecordClass): TClasses;

/// get the order table name from a SQL statement
// - return the word following any 'ORDER BY' statement
// - return 'RowID' if none found
function SQLGetOrder(const SQL: RawUTF8): RawUTF8;

{$ifdef PUREPASCAL}{$ifdef HASINLINE}
/// this function is published only for class function TSQLRecord.RecordProps()
................................................................................
              EnumTypeInfo^ := SetEnumType;
          end;
        end;
      end;
    end;
end;

function FieldPropFromTables(const Tables: TClasses; const PropName: RawUTF8;
  EnumTypeInfo: PPointer; out TableIndex: integer): TSQLFieldType;
var SubProp: RawUTF8;
    i,t: integer;
begin
  TableIndex := -1;
  if length(Tables)=1 then begin
    result := FieldPropFromTable(TSQLRecordClass(Tables[0]),PropName,EnumTypeInfo);
    if result<>sftUnknown then
      TableIndex := 0;
  end else begin
    i := PosEx('.',PropName); 
    if i=0 then begin
      // no 'ClassName.PropertyName' format: find first exact property name
      for t := 0 to high(Tables) do begin
        result := FieldPropFromTable(TSQLRecordClass(Tables[t]),PropName,EnumTypeInfo);
        if result<>sftUnknown then begin
          TableIndex := t;
          exit;
        end;
      end;
    end else begin
      // we expect property names as 'ClassName.PropertyName'
................................................................................
        if IdemPropName( // found class?
          // new TObject.ClassName is UnicodeString (Delphi 20009) -> inline code with
          // vmtClassName = UTF-8 encoded text stored in a shortstring = -44
          PShortString(PPointer(PtrInt(Tables[t])+vmtClassName)^)^,
          pointer(PropName),i) then begin
        //if Tables[t].ClassNameIs(SubClass) then begin // not Delphi 2009 OK
          TableIndex := t;
          result := FieldPropFromTable(TSQLRecordClass(Tables[t]),SubProp,EnumTypeInfo); // get property type
          exit;
        end;
    end;
    result := sftUnknown;
  end;
end;

................................................................................
procedure TSQLTable.Assign(source: TSQLTable);
begin
  fResults := source.fResults;
  fRowCount := source.fRowCount;
  fFieldCount := source.fFieldCount;
end;

constructor TSQLTable.Create(const Tables: array of TClass; const aSQL: RawUTF8);
var n: integer;
begin
  inherited Create;
  n := length(Tables);
  if n>0 then begin
    SetLength(fQueryTables,n);
    move(Tables[0],fQueryTables[0],n*sizeof(TClass));
................................................................................

destructor TSQLTable.Destroy;
begin
  fOwnedRecords.Free;
  inherited;
end;

function TSQLTable.QueryRecordType: TClass;
begin
  if (self<>nil) and (pointer(fQueryTables)<>nil) then
      result := fQueryTables[0] else
      result := nil;
end;

function TSQLTable.NewRecord(RecordType: TClass=nil): TObject;
begin
  result := nil;
  if self=nil then
    exit;
  if RecordType=nil then begin
    RecordType := QueryRecordType;
    if RecordType=nil then
      exit;
  end;
  result := ClassInstanceCreate(RecordType);
  if fOwnedRecords=nil then
    fOwnedRecords := TObjectList.Create;
  fOwnedRecords.Add(result);
end;

{$ifdef UNICODE}
function TSQLTable.ToObjectList<T>: TObjectList<T>;
................................................................................
var R,Item: TSQLRecord;
    Row: PPUtf8Char;
    i: integer;
begin
  result := TObjectList<T>.Create;
  if (self=nil) or (RowCount=0) then
    exit;
  if not T.InheritsFrom(TSQLRecord) then
    exit;
  R := TSQLRecordClass(T).Create;
  try
    R.FillPrepare(self);
    Row := @fResults[FieldCount];     // Row^ points to first row of data
    {$ifdef ISDELPHIXE}
    result.Count := RowCount;         // faster than manual Add()
    for i := 0 to RowCount-1 do begin // TObjectList will free each instance
      Item := TSQLRecordClass(T).Create;
      PPointerArray(result.List)[i] := Item;
    {$else}
    for i := 0 to RowCount-1 do begin // TObjectList will free each instance
      Item := TSQLRecordClass(T).Create;
      Result.Add(Item);
    {$endif}
      R.fFill.Fill(pointer(Row),Item);
      Inc(Row,FieldCount); // next data row
    end;
  finally
    R.Free;
  end;
end;
{$endif}

procedure TSQLTable.ToObjectList(DestList: TObjectList; RecordType: TClass=nil);
var R: TSQLRecord;
    Row: PPUtf8Char;
    i: integer;
begin
  if DestList=nil then
    exit;
  DestList.Clear;
................................................................................
  if (self=nil) or (RowCount=0) then
    exit;
  if RecordType=nil then begin
    RecordType := QueryRecordType;
    if RecordType=nil then
      exit;
  end;
  if not RecordType.InheritsFrom(TSQLRecord) then
    exit;
  R := TSQLRecordClass(RecordType).Create;
  try
    R.FillPrepare(self);
    DestList.Count := RowCount;       // faster than manual Add()
    Row := @fResults[FieldCount];     // Row^ points to first row of data
    for i := 0 to RowCount-1 do begin // TObjectList will free each instance
      DestList.List[i] := TSQLRecordClass(RecordType).Create;
      R.fFill.Fill(pointer(Row),TSQLRecord(DestList.List[i]));
      Inc(Row,FieldCount); // next data row
    end;
  finally
    R.Free;
  end;
end;







{$ifdef USEVARIANTS}
var
  SQLTableRowVariantType: TCustomVariantType = nil;
{$endif}

function TSQLTable.Step(SeekFirst: boolean=false; RowVariant: PVariant=nil): boolean;
................................................................................
    end else
      // parse error
      result := false else
    // data didn't change (fPrivateCopyHash checked)
    result := true;
end;

constructor TSQLTableJSON.Create(const Tables: array of TClass;
  const aSQL: RawUTF8; JSONBuffer: PUTF8Char; JSONBufferLen: integer);
begin // don't raise exception on error parsing
  inherited Create(Tables,aSQL);
  ParseAndConvert(JSONBuffer,JSONBufferLen);
end;

constructor TSQLTableJSON.Create(const Tables: array of TClass; const aSQL,
  aJSON: RawUTF8);
var len: integer;
begin
  len := length(aJSON);
  PrivateCopyChanged(pointer(aJSON),len);
  Create(Tables,aSQL,pointer(fPrivateCopy),len);
end;
................................................................................
begin
  if self=nil then
    result := nil else
  with URI(Model.Root,'GET',@Resp,nil,@SQL) do
    if Lo=HTML_SUCCESS then begin // GET with SQL sent
      if high(Tables)=0 then
        result := TSQLTableJSON.Create([Tables[0]],SQL,Resp) else
        result := TSQLTableJSON.Create(RecordClassesToClasses(Tables),SQL,Resp);
      result.fInternalState := Hi;
    end else // get data
    result := nil;
end;

function TSQLRestClientURI.ServerInternalState: cardinal;
begin
................................................................................
    with TSQLRecord(Data[i]) do
      if (fID<>0) and (InternalState<>State) then begin // refresh needed?
        if not Refresh(fID,TSQLRecord(Data[i]),Refreshed) then
          result := false; // mark error retrieving new content
      end;
end;

function RecordClassesToClasses(const Tables: array of TSQLRecordClass): TClasses;
var n, i: integer;
begin
  n := length(Tables);
  SetLength(result,n);
  for i := 0 to n-1 do
    result[i] := Tables[i];
end;

function TSQLRestClientURI.List(const Tables: array of TSQLRecordClass;
  const SQLSelect, SQLWhere: RawUTF8): TSQLTableJSON;
var Resp, SQL: RawUTF8;
    U: RawUTF8;
    InternalState: cardinal;
begin
  result := nil;
................................................................................
    result := TSQLTableJSON.Create([Tables[0]],SQL,Resp); // get data
  end else begin
    // multiple tables -> send SQL statement as HTTP body
    with URI(Model.Root,'GET',@Resp,nil,@SQL) do
      if Lo<>HTML_SUCCESS then
        exit else
        InternalState := Hi;
    result := TSQLTableJSON.Create(RecordClassesToClasses(Tables),SQL,Resp); // get data
  end;
  result.fInternalState := InternalState;
end;

function TSQLRestClientURI.InternalListJSON(Table: TSQLRecordClass; const SQL: RawUTF8): TSQLTableJSON;
begin
  result := ExecuteList([Table],SQL);
................................................................................
end;

function TSQLRestServer.ExecuteList(const Tables: array of TSQLRecordClass; const SQL: RawUTF8): TSQLTableJSON;
var JSON: RawUTF8;
begin
  JSON := EngineList(SQL,false);
  if JSON<>'' then
    result := TSQLTableJSON.Create(RecordClassesToClasses(Tables),SQL,JSON) else
    result := nil;
end;

function TSQLRestServer.UnLock(Table: TSQLRecordClass; aID: integer): boolean;
begin
  result := Model.UnLock(Table,aID);
end;

    - introducing TSQLRestClientURI.InternalCheckOpen/InternalClose methods to
      properly handle remote connection and re-connection
    - added TSQLRestClientURI.LastErrorCode/LastErrorMessage/LastErrorException
      properties, to retrieve additional information about remote URL() execution
    - added TSQLRestClientURI.ServiceRegister() and ServiceRegisterClientDriven()
      methods for easier Client-side interface-based services initialization
    - added JSONFileToObject() and ObjectToJSONFile() functions
    - unit interface deep refactoring: e.g. now TSQLTable will refers explicitly
      to TSQLRecord classes and not to plain TClass (e.g. for QueryTables[])
    - speed up of TSQLTable.FieldIndex() method (using binary search)
    - added TSQLTable.ToObjectList() and ToObjectList<T: TSQLRecord>() methods
    - added TSQLTable.Step(), FieldBuffer() and Field() methods, handling a
      cursor at TSQLTable level, with optional late-binding column access
    - added TSQLRestClientURI.ServerTimeStampSynchronize method to force time
      synchronization with the server - can be handy to test the connection 
    - added TSQLRest.TableHasRows/TableRowCount methods, and overriden direct
      implementation for TSQLRestServer/TSQLRestServerStaticInMemory (including
      SQL pattern recognition for TSQLRestServerStaticInMemory)
................................................................................
{$ifdef SSPIAUTH}
  SynSSPIAuth,
{$endif}
  SynCommons;



{ ************ low level types and constants for handling JSON and fields }

  { Why use JSON? (extracted from the main framework documentation)
    - The JavaScript Object Notation (JSON) is a lightweight computer data
     interchange format
    - Like XML, it's a text-based, human-readable format for representing
     simple data structures and associative arrays (called objects)
    - It's easier to read, quicker to implement and smaller in size than XML
    - It's a very efficient format for cache
    - It's natively supported by the JavaScript language, making it a perfect
     serialization format for any Ajax application
    - The JSON format is specified in http://tools.ietf.org/html/rfc4627
    - The default text encoding for both JSON and SQLite3 is UTF-8, which
     allows the full Unicode charset to be stored and communicated
    - It is the default data format used by ASP.NET AJAX services created in
     Windows Communication Foundation (WCF) since .NET framework 3.5
    - For binary blob transmission, we simply encode the binary data as hexa
     using the SQLite3 BLOB literals format : hexadecimal data preceded by
     a single "x" or "X" character (for example: X'53514C697465'), or Base64
     encoding - see BlobToTSQLRawBlob() function }


{.$define PUBLISHRECORD}
// - if defined, a sftBlobRecord will be defined in order to serialize published
// properties with some record type: but even if such properties can be defined,
// there is no RTTI generated for them: so it won't work :( see
// http://docwiki.embarcadero.com/RADStudio/en/Classes_and_Objects#Published_Members
// - should be defined globaly, e.g. in Synopse.inc
................................................................................
  // - this constant is used internaly to optimize memory usage in the
  // generated asm code
  // - you should not change it to a value lower than expected in an existing
  // database (e.g. as expected by TSQLAccessRights or such)
  MAX_SQLTABLES = 256;










type
  /// used to store bit set for all available Tables in a Database Model
  TSQLFieldTables = set of 0..MAX_SQLTABLES-1;

  /// a String used to store the BLOB content
  // - equals RawByteString for byte storage, to force no implicit charset
  // conversion, thatever the codepage of the resulting string is
................................................................................
    soInsert,
    soUpdate,
    soDelete);

  /// used to defined a set of CRUD associated SQL statement of a command
  TSQLOccasions = set of TSQLOccasion;



















































































































































































































































































































































































































































































































const
  /// kind of fields not retrieved during normal query, update or adding
  NOT_SIMPLE_FIELDS: TSQLFieldTypes =
    [sftUnknown, sftBlob, sftMany];

  /// kind of fields which can be copied from one TSQLRecord instance to another
  COPIABLE_FIELDS: TSQLFieldTypes =
................................................................................
// - this function will call RemoveCommentsFromJSON() before process
function JSONFileToObject(const JSONFile: TFileName; var ObjectInstance;
  TObjectListItemClass: TClass=nil): boolean;

/// persist a class instance into a JSON file
procedure ObjectToJSONFile(Value: TObject; const JSONFile: TFileName;
  Options: TTextWriterWriteObjectOptions=[woHumanReadable]);



{ ************ some RTTI and SQL mapping routines }

{ type definitions below were adapted from TypInfo.pas
 - this implementation doesn't require to include Variant.pas any more (which
  allow easy server-side compile with LVCL, e.g.)
................................................................................
  SECURITY_DESCRIPTOR_REVISION = 1;
  SECURITY_DESCRIPTOR_MIN_LENGTH = 20;
{$ifend}
{$endif}
{$endif}

const












  /// HTML Status Code for "Success"
  HTML_SUCCESS = 200;
  /// HTML Status Code for "Created"
  HTML_CREATED = 201;
  /// HTML Status Code for "Not Modified"
  HTML_NOTMODIFIED = 304;
  /// HTML Status Code for "Bad Request"
................................................................................
  HTML_NOTIMPLEMENTED = 501;
  /// HTML Status Code for "Service Unavailable"
  HTML_UNAVAILABLE = 503;

/// convert any HTML_* constant to a short English text
procedure StatusCodeToErrorMsg(Code: integer; var result: RawUTF8);



{ ************ main ORM / SOA classes and types }

const
  /// the used TAuthSession.IDCardinal value if the session not started yet
  // - i.e. if the session handling is still in its handshaking phase
  CONST_AUTHENTICATION_SESSION_NOT_STARTED = 0;

  /// the used TAuthSession.IDCardinal value if authentication mode is not set
  // - i.e. if TSQLRest.HandleAuthentication equals FALSE
  CONST_AUTHENTICATION_NOT_USED = 1;

  /// maximum number of the locked record in a Table (used in TSQLLocks)
  // - code is somewhat faster and easier with a fixed cache size
  // - 512 seems big enough on practice
  MAX_SQLLOCKS = 512;

  /// maximum handled dimension for TSQLRecordRTree
  // - this value is the one used by SQLite3 R-Tree virtual table
  RTREE_MAX_DIMENSION = 5;

  /// used as "stored AS_UNIQUE" published property definition in TSQLRecord
  AS_UNIQUE = false;

type
  /// exception raised in case of incorrect TSQLTable.Step / Field*() use
  ESQLTableException = class(ESynException);

  /// generic parent class of all custom Exception types of this unit
  EORMException = class(ESynException);

................................................................................
  /// exception dedicated to interface based service implementation
  EServiceException = class(EORMException);

  TSQLModel = class;
  TSQLRest = class;
  TSQLRestClient = class;
  TSQLModelRecordProperties = class;
  TSQLTable = class;

{$M+} { we need the RTTI information to be compiled for the published
        properties of these classes and their children (like TPersistent),
        to enable ORM - must be defined at the forward definition level }
  TSQLRecord = class;      // published properties = ORM fields/columns
  TSQLRecordMany = class;
  TSQLAuthUser = class;
  TSQLRestServer = class;  // published methods = RESTful callbacks handlers
  TSQLRestClientURI = class;
{$M-}

  /// class-reference type (metaclass) of TSQLRecord
  TSQLRecordClass = class of TSQLRecord;

  PClass = ^TClass;
  PSQLRecordClass = ^TSQLRecordClass;

  /// a dynamic array used to store the TSQLRecord classes in a Database Model
  TSQLRecordClassDynArray = array of TSQLRecordClass;


  /// information about a TSQLRecord class property
  // - sftID for TSQLRecord properties, which are pointer(RecordID), not
  // any true class instance
  // - sftMany for TSQLRecordMany properties, for which no data is
  // stored in the table itself, but in a pivot table
  // - sftObject for e.g. TStrings TRawUTF8List TCollection instances
................................................................................
    procedure SetValue(Instance: TObject; Value: PUTF8Char; wasString: boolean); override;
    procedure GetValueVar(Instance: TObject; ToSQL: boolean;
      var result: RawUTF8; wasSQLString: PBoolean); override;
    procedure GetBinary(Instance: TObject; W: TFileBufferWriter); override;
    function SetBinary(Instance: TObject; P: PAnsiChar): PAnsiChar; override;
  end;




  /// class-refrence type (metaclass) for a TSynFilter or a TSynValidate
  TSynFilterOrValidateClass = class of TSynFilterOrValidate;




  /// the kind of SQlite3 (virtual) table
  // - TSQLRecordFTS3 will be associated with vFTS3, TSQLRecordFTS4 with vFTS4,
  // TSQLRecordRTree with vRTree, any native SQlite3 table as vSQLite3, and
  // a TSQLRecordVirtualTable*ID with rCustomForcedID/rCustomAutoID
  // - a plain TSQLRecord class can be defined as rCustomForcedID (e.g. for
  // TSQLRecordMany) after registration for an external DB via a call to
  // VirtualTableExternalRegister() from SQLite3DB unit
................................................................................
    {/ this property contains the internal state counter of the server database
      when the data was retrieved from it
      - may be used to check if retrieved data may be out of date }
    property InternalState: cardinal read fInternalState;
  published
    { published properties in inherited classes will be interpreted as SQL fields }
  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
  // - GetA() or GetW() can be used in a TDrawString
  // - will be implemented as TSQLTableDB for direct SQLite3 database engine call,
  //  or as TSQLTableJSON for remote access through optimized JSON messages
  TSQLTable = class
  private
    fQueryTables: TSQLRecordClassDynArray;
    fQuerySQL: RawUTF8;
    fFieldLengthMean: TIntegerDynArray;
    fFieldLengthMeanSum: integer;
  protected
    fRowCount,
    fFieldCount: integer;
    /// contains the data, as returned by sqlite3_get_table()
    fResults: PPUTF8CharArray;
    /// contains the TSQLFieldType and TypeInfo(enumerate), after calculation
    // from the fQueryTables values
    fFieldType: array of record
      // the field kind
      ContentType: TSQLFieldType;
      // initialized for both sftEnumerate and sftSet field kinds
      EnumTypeInfo: pointer;
      // the corresponding index in fQueryTables[]
      TableIndex: integer;
    end;
    /// used by FieldIndex() for fast binary searcg
    fFieldNameOrder: TCardinalDynArray;
    /// contain the fResults[] pointers, after a IDColumnHide() call
    fIDColumn, fNotIDColumn: array of PUTF8Char;
    /// bit set at parsing to mark was a string value (e.g. "..." in JSON)
    fFieldParsedAsString: set of 0..255;
    /// index of a 'ID' field, -1 if none (e.g. after IDColumnHide method call)
    fFieldIndexID: integer;
    /// the internal state counter of the database when the data was retrieved
    fInternalState: cardinal;
    /// contains the parameters used for sorting
    fSortParams: TSQLTableSortParams;
    /// contains the TSQLRecord instances created by NewRecord method
    fOwnedRecords: TObjectList;
    /// if the TSQLRecord is the owner of this table, i.e. if it must free it
    fOwnerMustFree: Boolean;
    /// current cursor row (1..RowCount), as set by the Step() method
    fStepRow: integer;
    /// fill the fFieldType[] array (from fQueryTables[] or fResults[] content)
    procedure InitFieldTypes;
  public
    /// initialize the result table
    // - you can optionaly associate the corresponding TSQLRecordClass types,
    // by which the results were computed (it will use RTTI for column typing)
    constructor Create(const Tables: array of TSQLRecordClass; const aSQL: RawUTF8);
    /// free associated memory and owned records
    destructor Destroy; override;
    {/ read-only access to a particular field value, as UTF-8 encoded buffer
     - points to memory buffer allocated by Init() }
    function Get(Row,Field: integer): PUTF8Char;
    {/ read-only access to a particular field value, as RawUTF8 text }
    function GetU(Row,Field: integer): RawUTF8;
    {/ read-only access to a particular field value, as Win Ansi text }
    function GetA(Row,Field: integer): WinAnsiString;
    {/ read-only access to a particular field value, as Win Ansi text shortstring}
    function GetS(Row,Field: integer): shortstring;
    {$ifdef USEVARIANTS}
    {/ read-only access to a particular field value, as a Variant
     - will try to use the most approriate Variant type for conversion (will
       use e.g. TDateTime for sftDateTime or sftTimeLog, Extended for sftFloat...)
     - will handle any necessary conversion to VCL string text, ready to be displayed
     - the global UTF8ToString() function will be used for the conversion:
       for proper i18n handling before Delphi 2009, you should use the
       overloaded method with aUTF8ToString=Language.UTF8ToString }
    function GetVariant(Row,Field: integer; Client: TObject): variant; overload;
    {/ read-only access to a particular field value, as a Variant
     - will try to use the most approriate Variant type for conversion (will
       use e.g. TDateTime for sftDateTime or sftTimeLog, Extended for sftFloat...)
     - will handle any necessary conversion to VCL string text, ready to be displayed
     - the global UTF8ToString() function will be used for the conversion:
       for proper i18n handling before Delphi 2009, you should use the
       overloaded method with aUTF8ToString=Language.UTF8ToString }
    procedure GetVariant(Row,Field: integer; Client: TObject; var result: variant); overload;
    {$endif}
    {/ read-only access to a particular field value, as VCL string text
     - the global UTF8ToString() function will be used for the conversion:
     for proper i18n handling before Delphi 2009, you should use the
     overloaded method with aUTF8ToString=Language.UTF8ToString }
    function GetString(Row,Field: integer): string;
    {/ fill a unicode buffer with a particular field value
      - return number of wide characters written in Dest^ }
    function GetWP(Row,Field: integer; Dest: PWideChar; MaxDestChars: cardinal): integer;
    {/ read-only access to a particular field value, as UTF-16 Unicode text
      - Raw Unicode is WideChar(zero) terminated
      - its content is allocated to contain all WideChars (not trimed to 255,
        like GetWP() above }
    function GetW(Row,Field: integer): RawUnicode;
    {/ read-only access to a particular field value, as integer value }
    function GetAsInteger(Row,Field: integer): integer;
      {$ifdef HASINLINE}inline;{$endif}
    {/ read-only access to a particular field value, as Int64 value }
    function GetAsInt64(Row,Field: integer): Int64;
      {$ifdef HASINLINE}inline;{$endif}
    {/ read-only access to a particular field value, ready to be displayed
      - mostly used with Row=0, i.e. to get a display value from a field name
      - use "string" type, i.e. UnicodeString for Delphi 2009+
      - value is first un-camel-cased: 'OnLine' value will return 'On line' e.g.
      - then System.LoadResStringTranslate() is called if available }
    function GetCaption(Row,Field: integer): string;
    {/ read-only access to a particular Blob value
     - a new TSQLRawBlob is created
     - Blob data is converted from SQLite3 BLOB literals (X'53514C697465' e.g.)
       or Base-64 encoded content ('\uFFF0base64encodedbinary')
     - prefered manner is to directly use REST protocol to retrieve a blob field }
    function GetBlob(Row,Field: integer): TSQLRawBlob;
    {/ read-only access to a particular Blob value
     - a new TBytes is created
     - Blob data is converted from SQLite3 BLOB literals (X'53514C697465' e.g.)
       or Base-64 encoded content ('\uFFF0base64encodedbinary')
     - prefered manner is to directly use REST protocol to retrieve a blob field }
    function GetBytes(Row,Field: integer): TBytes;
    {/ read-only access to a particular Blob value
     - a new TCustomMemoryStream is created - caller shall free its instance
     - Blob data is converted from SQLite3 BLOB literals (X'53514C697465' e.g.)
       or Base-64 encoded content ('\uFFF0base64encodedbinary')
     - prefered manner is to directly use REST protocol to retrieve a blob field }
    function GetStream(Row,Field: integer): TStream;
    {/ read-only access to a particular field value, as VCL text
     - Client is one TSQLClient instance (used to display TRecordReference via
       the associated TSQLModel)
     - returns the Field Type
     - return generic string Text, i.e. UnicodeString for Delphi 2009+,
       ready to be displayed to the VCL, for sftEnumerate, sftTimeLog
       and sftRecord/sftID
     - returns '' as string Text, if text can by displayed directly
       with Get*() methods above
     - returns '' for other properties kind, if UTF8ToString is nil,
       or the ready to be displayed value if UTF8ToString event is set
       (to be used mostly with Language.UTF8ToString) }
    function ExpandAsString(Row,Field: integer; Client: TObject; out Text: string): TSQLFieldType;
    {/ read-only access to a particular field value, as VCL text
     - this method is just a wrapper around ExpandAsString method, returning
       the content as a SynUnicode string type (i.e. UnicodeString since Delphi
       2009, and WideString for non Unicode versions of Delphi)
     - it is used by the reporting layers of the framework (e.g. TSQLRibbon.AddToReport) }
    function ExpandAsSynUnicode(Row,Field: integer; Client: TObject; out Text: SynUnicode): TSQLFieldType;
    {/ read-only access to a particular DateTime field value
     - expect SQLite3 TEXT field in ISO 8601 'YYYYMMDD hhmmss' or
      'YYYY-MM-DD hh:mm:ss' format }
    function GetDateTime(Row,Field: integer): TDateTime;
      {$ifdef PUREPASCAL} {$ifdef HASINLINE}inline;{$endif} {$endif}
    {/ read-only access to a particular TTimeLog field value
      - return the result as Iso8601.Text() Iso-8601 encoded text }
    function GetTimeLog(Row,Field: integer; Expanded: boolean; FirstTimeChar: AnsiChar = 'T'): RawUTF8;
    {/ widechar length (UTF-8 decoded) of a particular field value }
    function LengthW(Row,Field: integer): integer;
    {/ get all values for a specified field into a dynamic RawUTF8 array
     - don't perform any conversion, but just create an array of raw PUTF8Char data }
    procedure GetRowValues(Field: integer; out Values: TRawUTF8DynArray); overload;
    {/ get all values for a specified field into a dynamic Integer array }
    procedure GetRowValues(Field: integer; out Values: TIntegerDynArray); overload;
    {/ get all values for a specified field as CSV
     - don't perform any conversion, but create a CSV from raw PUTF8Char data }
    function GetRowValues(Field: integer; Sep: AnsiChar=','): RawUTF8; overload;

    /// save the table values in JSON format
    // - JSON data is added to TStream, with UTF-8 encoding
    // - if Expand is true, JSON data is an array of objects, for direct use
    // with any Ajax or .NET client:
    // & [ {"col1":val11,"col2":"val12"},{"col1":val21,... ]
    // - if Expand is false, JSON data is serialized (used in TSQLTableJSON)
    // & { "fieldCount":1,"values":["col1","col2",val11,"val12",val21,..] }
    // - RowFirst and RowLast can be used to ask for a specified row extent
    // of the returned data (by default, all rows are retrieved)
    procedure GetJSONValues(JSON: TStream; Expand: boolean;
      RowFirst: integer=0; RowLast: integer=0); overload;
    /// same as above, but returning result into a RawUTF8
    function GetJSONValues(Expand: boolean): RawUTF8; overload;
    /// save the table in CSV format
    // - if Tab=TRUE, will use TAB instead of ',' between columns
    // - you can customize the ',' separator - use e.g. the global ListSeparator
    // variable (from SysUtils) to reflect the current system definition (some
    // country use ',' as decimal separator, for instance our "douce France")
    // - AddBOM will add a UTF-8 Byte Order Mark at the beginning of the content
    procedure GetCSVValues(Dest: TStream; Tab: boolean; CommaSep: AnsiChar=',';
      AddBOM: boolean=false);

    {/ get the Field index of a FieldName
     - return -1 if not found, index (0..FieldCount-1) if found }
    function FieldIndex(FieldName: PUTF8Char): integer; overload;
    {/ get the Field index of a FieldName
     - return -1 if not found, index (0..FieldCount-1) if found }
    function FieldIndex(const FieldName: RawUTF8): integer; overload;
      {$ifdef HASINLINE}inline;{$endif}
    {/ get the Field content (encoded as UTF-8 text) from a property name
     - return nil if not found }
    function FieldValue(const FieldName: RawUTF8; Row: integer): PUTF8Char;
    {/ sort result Rows, according to a specific field
     - default is sorting by ascending order (Asc=true)
     - you can specify a Row index to be updated during the sort in PCurrentRow
     - sort is very fast, even for huge tables (more faster than any indexed
       SQL query): 500,000 rows are sorted instantly
     - this optimized sort implementation does the comparaison first by the
     designed field, and, if the field value is identical, the ID value is
     used (it will therefore sort by time all identical values) }
    procedure SortFields(Field: integer; Asc: boolean=true;
      PCurrentRow: PInteger=nil; FieldType: TSQLFieldType=sftUnknown);
    {/ sort result Rows, according to the Bits set to 1 first }
    procedure SortBitsFirst(var Bits);
    {/ 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; otherwize, 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; EnumTypeInfo: PPointer): TSQLFieldType;
    {/ 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)
    // - return the sum of all mean of character lengths
    function CalculateFieldLengthMean(var aResult: TIntegerDynArray;
      FromDisplay: boolean=false): integer;
    /// get the mean of characters length of this field
    // - the character length is for the first line of text only (stop counting
    // at every newline character, i.e. #10 or #13 char)
    // - very fast: calculated only once for all fields
    function FieldLengthMean(Field: integer): cardinal;
    /// get the sum of all mean of characters length of all fields
    // - very fast: calculated only once for all fields
    function FieldLengthMeanSum: cardinal;
    /// get the maximum number of characters of this field
    function FieldLengthMax(Field: integer; NeverReturnsZero: boolean=false): cardinal;
    /// get the record class (i.e. the table) associated to a field
    // - is nil if this table has no QueryTables property
    // - very fast: calculated only once for all fields
    function FieldTable(Field: integer): TClass;
    /// force the mean of characters length for every field
    // - expect as many parameters as fields in this table
    // - override internal fFieldLengthMean[] and fFieldLengthMeanSum values
    procedure SetFieldLengthMean(const Lengths: array of cardinal);
    /// 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
      be destroyed before this TSQLTable }
    procedure Assign(source: TSQLTable);

    /// search a text value inside the table data in a specified field
    // - the text value must already be uppercased 7-bits ANSI encoded
    // - return the Row on success, 0 on error
    // - search only in the content of FieldIndex data
    // - you can specify a Soundex pronunciation to use, or leave as sndxNone for
    // standard case insensitive character match; aUpperValue can optional
    // indicate a Soundex search, by predeceding the searched text with % for
    // English, %% for French or %%% for Spanish (only works with WinAnsi
    // char set - i.e. code page 1252)
    // - if UnicodeComparison is set to TRUE, search will use low-level Windows
    // API for Unicode-level conversion - it will be much slower, but accurate
    // for the whole range of UTF-8 encoding
    // - if UnicodeComparison is left to FALSE, UTF-8 decoding will be done only
    // if necessary: it will work only with standard western-occidental alphabet
    // (i.e. WinAnsi - code page 1252), but it will be very fast
    function SearchValue(const aUpperValue: RawUTF8; StartRow, FieldIndex: integer;
      Client: TObject; Lang: TSynSoundExPronunciation=sndxNone;
      UnicodeComparison: boolean=false): integer; overload;
    /// search a text value inside the table data in all fields
    // - the text value must already be uppercased 7-bits ANSI encoded
    // - return the Row on success, 0 on error
    // - search on all fields, returning field found in FieldIndex (if not nil)
    // - you can specify a Soundex pronunciation to use, or leave as sndxNone for
    // standard case insensitive character match; aUpperValue can optional
    // indicate a Soundex search, by predeceding the searched text with % for
    // English, %% for French or %%% for Spanish (only works with WinAnsi
    // char set - i.e. code page 1252)
    // - if UnicodeComparison is set to TRUE, search will use low-level Windows
    // API for Unicode-level conversion - it will be much slower, but accurate
    // for the whole range of UTF-8 encoding
    // - if UnicodeComparison is left to FALSE, UTF-8 decoding will be done only
    // if necessary: it will work only with standard western-occidental alphabet
    // (i.e. WinAnsi - code page 1252), but it will be very fast
    function SearchValue(const aUpperValue: RawUTF8; StartRow: integer;
      FieldIndex: PInteger; Client: TObject; Lang: TSynSoundExPronunciation=sndxNone;
      UnicodeComparison: boolean=false): integer; overload;
    /// search for a value inside the raw table data
    // - returns 0 if not found, or the matching Row number otherwise
    function SearchFieldEquals(const aValue: RawUTF8; FieldIndex: integer): integer;

    /// if the ID column is available, hides it from fResults[]
    // - usefull for simplier UI, with a hidden ID field
    // - use IDColumnHiddenValue() to get the ID of a specific row
    // - return true is ID was succesfully hidden, false if not possible
    function IDColumnHide: boolean;
    /// return the (previously hidden) ID value, 0 on error
    function IDColumnHiddenValue(Row: integer): integer;
    /// return all (previously hidden) ID values
    procedure IDColumnHiddenValues(var IDs: TIntegerDynArray);
    /// get all IDs where individual bit in Bits are set
    procedure IDArrayFromBits(const Bits; var IDs: TIntegerDynArray);
    /// get all individual bit in Bits corresponding to the supplied IDs
    // - warning: IDs integer array will be sorted within this method call
    procedure IDArrayToBits(var Bits; var IDs: TIntegerDynArray);
    /// get the Row index corresponding to a specified ID
    // - return the Row number, from 1 to RowCount
    // - return RowCount (last row index) if this ID was not found or no
    // ID field is available
    function RowFromID(aID: integer): integer;

    /// delete the specified data Row from the Table
    // - only overwrite the internal fResults[] pointers, don't free any memory,
    // nor modify the internal DataSet
    procedure DeleteRow(Row: integer);
    /// delete the specified Column text from the Table
    // - don't delete the Column: only delete UTF-8 text in all rows for this
    // field
    procedure DeleteColumnValues(Field: integer);

    /// retrieve QueryTables[0], if existing
    function QueryRecordType: TSQLRecordClass;

    /// create a new TSQLRecord instance for a specific Table
    // - use the specified TSQLRecord class or create one instance
    // of the first associated record class (from internal QueryTables[])
    // - use this method to create a working copy of a table's record, e.g.
    // - the record will be freed when the TSQLTable will be destroyed:
    // you don't need to make a Try..Finally..Free..end block with it
    function NewRecord(RecordType: TSQLRecordClass=nil): TSQLRecord;
    /// create a TObjectList with TSQLRecord instances corresponding to this
    // TSQLTable result set
    // - use the specified TSQLRecord class or create instances
    // of the first associated record class (from internal QueryTables[])
    // - always returns an instance, even if the TSQLTable is nil or void
    function ToObjectList(RecordType: TSQLRecordClass=nil): TObjectList; overload;
    /// fill an existing TObjectList with TSQLRecord instances corresponding
    // to this TSQLTable result set
    // - use the specified TSQLRecord class or create instances
    // of the first associated record class (from internal QueryTables[])
    procedure ToObjectList(DestList: TObjectList; RecordType: TSQLRecordClass=nil); overload;
    {$ifdef UNICODE}
    /// create a TObjectList<TSQLRecord> with TSQLRecord instances corresponding
    // to this TSQLTable result set
    // - use the specified TSQLRecord class or create instances
    // of the first associated record class (from internal QueryTables[])
    // - always returns an instance, even if the TSQLTable is nil or void
    function ToObjectList<T: TSQLRecord>: TObjectList<T>; overload;
    {$endif}

    {/ After a TSQLTable has been initialized, this method can be called
      one or more times to iterate through all data rows
     - you shall call this method before calling FieldBuffer()/Field() methods
     - return TRUE on success, with data ready to be retrieved by Field*()
     - return FALSE if no more row is available (i.e. exceeded RowCount)
     - if SeekFirst is TRUE, will put the cursor on the first row of results,
       otherwise, it will fetch one row of data, to be called within a loop
     - you can specify a variant instance (e.g. allocated on the stack) in
       optional RowVariant parameter, to access field values using late binding
     - typical use may be:
       ! while TableCustomers.Step do
       !   writeln(Field('name'));
     - or, when using a variant and late-binding:
       ! var customer: variant;
       ! ...
       !   while TableCustomers.Step(false,@customer) do
       !     writeln(customer.Name);
     }
    function Step(SeekFirst: boolean=false; RowVariant: PVariant=nil): boolean;
    /// read-only access to a particular field value, as UTF-8 encoded buffer
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - similar to Get() method, but for the current Step
    function FieldBuffer(FieldIndex: Integer): PUTF8Char; overload;
    /// read-only access to a particular field value, as UTF-8 encoded buffer
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - similar to Get() method, but for the current Step
    function FieldBuffer(const FieldName: RawUTF8): PUTF8Char; overload;
    {$ifdef USEVARIANTS}
    /// read-only access to a particular field value, as a variant
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - will call GetVariant() method for appropriate data conversion
    function Field(FieldIndex: integer): variant; overload;
    /// read-only access to a particular field value, as a variant
    // - raise an ESQLTableException if called outside valid Step() sequence
    // - will call GetVariant() method for appropriate data conversion
    function Field(const FieldName: RawUTF8): variant; overload;
    {$endif}

    /// contains the associated record class on Query
    property QueryTables: TSQLRecordClassDynArray read fQueryTables;
    /// contains the associated SQL statement on Query
    property QuerySQL: RawUTF8 read fQuerySQL;
    {/ read-only access to the number of data Row in this table
     - first row contains field name
     - then 1..RowCount rows contain the data itself }
    property RowCount: integer read fRowCount;
    {/ read-only access to the number of fields for each Row in this table }
    property FieldCount: integer read fFieldCount;
    /// read-only acccess to the current Row number, after a Step() call
    // - contains 0 if accessed outside valid Step() sequence call
    // - contains 1..RowCount after a valid Step() iteration
    property StepRow: integer read fStepRow;
    {/ this property contains the internal state counter of the server database
      when the data was retrieved from it
      - can be used to check if retrieved data may be out of date }
    property InternalState: cardinal read fInternalState write fInternalState;
    /// if the TSQLRecord is the owner of this table, i.e. if it must free it
    property OwnerMustFree: Boolean read fOwnerMustFree write fOwnerMustFree;
  end;

{$ifdef USEVARIANTS}
  /// a custom variant type used to have direct access to TSQLTable content
  // - use TSQLTable.Step(..,@Data) method to initialize such a Variant
  // - the variant members/fields are read-only by design 
  TSQLTableRowVariantType = class(TSynInvokeableVariantType)
  protected
    procedure IntGet(var Dest: TVarData; const V: TVarData; Name: PAnsiChar); override;
    procedure IntSet(const V, Value: TVarData; Name: PAnsiChar); override;
  end;
{$endif USEVARIANTS}


  /// get a SQL result from a JSON message, and store it into its own memory
  TSQLTableJSON = class(TSQLTable)
  protected
    /// used if a private copy of the JSON buffer is needed
    fPrivateCopy: RawUTF8;
    /// contains the pointers of start of every field value in JSONData
    fJSONResults: array of PUTF8Char;
    /// contain the hash value of the last JSON data sent to ContentChanged()
    // - used to don't repeat parsing if data has not been changed
    fPrivateCopyHash: cardinal;
    /// fill the result table content from a JSON-formated Data message
    // - returns TRUE on parsing success
    // - returns FALSE if no valid JSON data was found
    // - update all content fields (fResults[], fRowCount, fFieldCount, etc...)
    // - expect the UTF-8 Buffer in either TSQLRequest.EngineExecute(DB,SQL,JSON)
    // format (i.e. expanded) or either in a not expanded format (as an
    // AJAX-ready array of objects)
    // - the conversion into PPUTF8CharArray is made inplace and is very fast
    // (no additional memory buffer is allocated)
    function ParseAndConvert(Buffer: PUTF8Char; BufferLen: integer): boolean;
    /// will check then set (if needed) internal fPrivateCopy[Hash] values
    // - returns TRUE if content changed (then fPrivateCopy+fPrivateCopyHash
    // will be updated)
    function PrivateCopyChanged(aJSON: PUTF8Char; aLen: integer): boolean;
  public
    /// create the result table from a JSON-formated Data message
    // - the JSON data is parsed and formatted in-place
    // - please note that the supplied JSON buffer content will be changed:
    // if you want to reuse this JSON content, you shall make a private copy
    // before calling this constructor and you shall NOT release the corresponding
    // variable (fResults/JSONResults[] will point inside this memory buffer):
    // use instead the overloaded Create constructor expecting aJSON parameter
    // making a private copy of the data
    constructor Create(const Tables: array of TSQLRecordClass; const aSQL: RawUTF8;
      JSONBuffer: PUTF8Char; JSONBufferLen: integer); overload;
    /// create the result table from a JSON-formated Data message
    // - the JSON data is parsed and formatted in-place, after having been
    // copied in the protected fPrivateCopy variable
    constructor Create(const Tables: array of TSQLRecordClass; const aSQL, aJSON: RawUTF8); overload;
    /// update the result table content from a JSON-formated Data message
    // - return true on parsing success, false if no valid JSON data was found
    // - set Refreshed to true if the content changed
    // - update all content fields (fResults[], fRowCount, fFieldCount, etc...)
    // - call SortFields() or IDColumnHide if was already done for this TSQLTable
    // - the conversion into PPUTF8CharArray is made inplace and is very fast
    // (only one memory buffer is allocated for the whole data)
    function UpdateFrom(const aJSON: RawUTF8; var Refreshed: boolean;
      PCurrentRow: PInteger): boolean;
    /// the private copy of the processed data buffer
    // - available e.g. for Create constructor using aJSON parameter,
    // or after the UpdateFrom() process
    // - this buffer is not to be access directly: this won't be a valid JSON
    // content, but a processed buffer, on which fResults[] elements point to
    property PrivateInternalCopy: RawUTF8 read fPrivateCopy;
  end;

  PSQLLocks = ^TSQLLocks;
  /// used to store the locked record list, in a specified table
  // - the maximum count of the locked list if fixed to 512 by default,
  // which seems correct for common usage
  TSQLLocks = {$ifndef ISDELPHI2010}object{$else}record{$endif}
    /// the number of locked records stored in this object
................................................................................
    // - TSQLRecordClass to be specified as its index in Rest.Model.Tables[]
    procedure NotifyDeletion(aTableIndex, aID: integer); overload;
  end;

  TSQLRestClass = class of TSQLRest;

  /// a generic REpresentational State Transfer (REST) client/server class
  TSQLRest = class
  protected
    fModel: TSQLModel;
    fCache: TSQLRestCache;
    fTransactionActive: cardinal;
    fTransactionTable: TSQLRecordClass;
    fTransactionCriticalSession: TRTLCriticalSection;
    fAcquireWriteTimeOut: cardinal;
................................................................................

/// create a TRecordReference with the corresponding parameters
function RecordReference(Model: TSQLModel; aTable: TSQLRecordClass; aID: integer): TRecordReference;

/// convert a dynamic array of TRecordReference into its corresponding IDs
procedure RecordRefToID(var aArray: TPtrUIntDynArray);





/// get the order table name from a SQL statement
// - return the word following any 'ORDER BY' statement
// - return 'RowID' if none found
function SQLGetOrder(const SQL: RawUTF8): RawUTF8;

{$ifdef PUREPASCAL}{$ifdef HASINLINE}
/// this function is published only for class function TSQLRecord.RecordProps()
................................................................................
              EnumTypeInfo^ := SetEnumType;
          end;
        end;
      end;
    end;
end;

function FieldPropFromTables(const Tables: TSQLRecordClassDynArray;
  const PropName: RawUTF8; EnumTypeInfo: PPointer; out TableIndex: integer): TSQLFieldType;
var SubProp: RawUTF8;
    i,t: integer;
begin
  TableIndex := -1;
  if length(Tables)=1 then begin
    result := FieldPropFromTable(Tables[0],PropName,EnumTypeInfo);
    if result<>sftUnknown then
      TableIndex := 0;
  end else begin
    i := PosEx('.',PropName); 
    if i=0 then begin
      // no 'ClassName.PropertyName' format: find first exact property name
      for t := 0 to high(Tables) do begin
        result := FieldPropFromTable(Tables[t],PropName,EnumTypeInfo);
        if result<>sftUnknown then begin
          TableIndex := t;
          exit;
        end;
      end;
    end else begin
      // we expect property names as 'ClassName.PropertyName'
................................................................................
        if IdemPropName( // found class?
          // new TObject.ClassName is UnicodeString (Delphi 20009) -> inline code with
          // vmtClassName = UTF-8 encoded text stored in a shortstring = -44
          PShortString(PPointer(PtrInt(Tables[t])+vmtClassName)^)^,
          pointer(PropName),i) then begin
        //if Tables[t].ClassNameIs(SubClass) then begin // not Delphi 2009 OK
          TableIndex := t;
          result := FieldPropFromTable(Tables[t],SubProp,EnumTypeInfo); // get property type
          exit;
        end;
    end;
    result := sftUnknown;
  end;
end;

................................................................................
procedure TSQLTable.Assign(source: TSQLTable);
begin
  fResults := source.fResults;
  fRowCount := source.fRowCount;
  fFieldCount := source.fFieldCount;
end;

constructor TSQLTable.Create(const Tables: array of TSQLRecordClass; const aSQL: RawUTF8);
var n: integer;
begin
  inherited Create;
  n := length(Tables);
  if n>0 then begin
    SetLength(fQueryTables,n);
    move(Tables[0],fQueryTables[0],n*sizeof(TClass));
................................................................................

destructor TSQLTable.Destroy;
begin
  fOwnedRecords.Free;
  inherited;
end;

function TSQLTable.QueryRecordType: TSQLRecordClass;
begin
  if (self<>nil) and (pointer(fQueryTables)<>nil) then
      result := fQueryTables[0] else
      result := nil;
end;

function TSQLTable.NewRecord(RecordType: TSQLRecordClass=nil): TSQLRecord;
begin
  result := nil;
  if self=nil then
    exit;
  if RecordType=nil then begin
    RecordType := QueryRecordType;
    if RecordType=nil then
      exit;
  end;
  result := RecordType.Create;
  if fOwnedRecords=nil then
    fOwnedRecords := TObjectList.Create;
  fOwnedRecords.Add(result);
end;

{$ifdef UNICODE}
function TSQLTable.ToObjectList<T>: TObjectList<T>;
................................................................................
var R,Item: TSQLRecord;
    Row: PPUtf8Char;
    i: integer;
begin
  result := TObjectList<T>.Create;
  if (self=nil) or (RowCount=0) then
    exit;


  R := TSQLRecordClass(T).Create;
  try
    R.FillPrepare(self);
    Row := @fResults[FieldCount];     // Row^ points to first row of data
    {$ifdef ISDELPHIXE}
    result.Count := RowCount;         // faster than manual Add()
    for i := 0 to RowCount-1 do begin // TObjectList will free each instance
      Item := TSQLRecordClass(T).Create;
      PPointerArray(result.List)[i] := Item;
    {$else}
    for i := 0 to RowCount-1 do begin // TObjectList will free each instance
      Item := T.Create;
      Result.Add(Item);
    {$endif}
      R.fFill.Fill(pointer(Row),Item);
      Inc(Row,FieldCount); // next data row
    end;
  finally
    R.Free;
  end;
end;
{$endif}

procedure TSQLTable.ToObjectList(DestList: TObjectList; RecordType: TSQLRecordClass=nil);
var R: TSQLRecord;
    Row: PPUtf8Char;
    i: integer;
begin
  if DestList=nil then
    exit;
  DestList.Clear;
................................................................................
  if (self=nil) or (RowCount=0) then
    exit;
  if RecordType=nil then begin
    RecordType := QueryRecordType;
    if RecordType=nil then
      exit;
  end;
  R := RecordType.Create;


  try
    R.FillPrepare(self);
    DestList.Count := RowCount;       // faster than manual Add()
    Row := @fResults[FieldCount];     // Row^ points to first row of data
    for i := 0 to RowCount-1 do begin // TObjectList will free each instance
      DestList.List[i] := RecordType.Create;
      R.fFill.Fill(pointer(Row),TSQLRecord(DestList.List[i]));
      Inc(Row,FieldCount); // next data row
    end;
  finally
    R.Free;
  end;
end;

function TSQLTable.ToObjectList(RecordType: TSQLRecordClass=nil): TObjectList;
begin
  result := TObjectList.Create;
  ToObjectList(result,RecordType);
end;

{$ifdef USEVARIANTS}
var
  SQLTableRowVariantType: TCustomVariantType = nil;
{$endif}

function TSQLTable.Step(SeekFirst: boolean=false; RowVariant: PVariant=nil): boolean;
................................................................................
    end else
      // parse error
      result := false else
    // data didn't change (fPrivateCopyHash checked)
    result := true;
end;

constructor TSQLTableJSON.Create(const Tables: array of TSQLRecordClass;
  const aSQL: RawUTF8; JSONBuffer: PUTF8Char; JSONBufferLen: integer);
begin // don't raise exception on error parsing
  inherited Create(Tables,aSQL);
  ParseAndConvert(JSONBuffer,JSONBufferLen);
end;

constructor TSQLTableJSON.Create(const Tables: array of TSQLRecordClass; const aSQL,
  aJSON: RawUTF8);
var len: integer;
begin
  len := length(aJSON);
  PrivateCopyChanged(pointer(aJSON),len);
  Create(Tables,aSQL,pointer(fPrivateCopy),len);
end;
................................................................................
begin
  if self=nil then
    result := nil else
  with URI(Model.Root,'GET',@Resp,nil,@SQL) do
    if Lo=HTML_SUCCESS then begin // GET with SQL sent
      if high(Tables)=0 then
        result := TSQLTableJSON.Create([Tables[0]],SQL,Resp) else
        result := TSQLTableJSON.Create(Tables,SQL,Resp);
      result.fInternalState := Hi;
    end else // get data
    result := nil;
end;

function TSQLRestClientURI.ServerInternalState: cardinal;
begin
................................................................................
    with TSQLRecord(Data[i]) do
      if (fID<>0) and (InternalState<>State) then begin // refresh needed?
        if not Refresh(fID,TSQLRecord(Data[i]),Refreshed) then
          result := false; // mark error retrieving new content
      end;
end;










function TSQLRestClientURI.List(const Tables: array of TSQLRecordClass;
  const SQLSelect, SQLWhere: RawUTF8): TSQLTableJSON;
var Resp, SQL: RawUTF8;
    U: RawUTF8;
    InternalState: cardinal;
begin
  result := nil;
................................................................................
    result := TSQLTableJSON.Create([Tables[0]],SQL,Resp); // get data
  end else begin
    // multiple tables -> send SQL statement as HTTP body
    with URI(Model.Root,'GET',@Resp,nil,@SQL) do
      if Lo<>HTML_SUCCESS then
        exit else
        InternalState := Hi;
    result := TSQLTableJSON.Create(Tables,SQL,Resp); // get data
  end;
  result.fInternalState := InternalState;
end;

function TSQLRestClientURI.InternalListJSON(Table: TSQLRecordClass; const SQL: RawUTF8): TSQLTableJSON;
begin
  result := ExecuteList([Table],SQL);
................................................................................
end;

function TSQLRestServer.ExecuteList(const Tables: array of TSQLRecordClass; const SQL: RawUTF8): TSQLTableJSON;
var JSON: RawUTF8;
begin
  JSON := EngineList(SQL,false);
  if JSON<>'' then
    result := TSQLTableJSON.Create(Tables,SQL,JSON) else
    result := nil;
end;

function TSQLRestServer.UnLock(Table: TSQLRecordClass; aID: integer): boolean;
begin
  result := Model.UnLock(Table,aID);
end;

      a special request explicitely (note that JSON format returns BLOB data)
     - uses a TSQLTableJSON internaly: all currency is transformed to its floating
       point TEXT representation, and allows efficient caching
     - if the SQL statement is in the DB cache, it's retrieved from its cached
       value: our JSON parsing is a lot faster than SQLite3 engine itself,
       and uses less memory
     - will raise an ESQLException on any error }
    constructor Create(aDB: TSQLDatabase; const Tables: array of TClass;
      const aSQL: RawUTF8; Expand: boolean);
  end;

  TSQLRestServerDBClass = class of TSQLRestServerDB;

  TSQLVirtualTableModuleServerDB = class;

................................................................................


implementation


{ TSQLTableDB }

constructor TSQLTableDB.Create(aDB: TSQLDataBase; const Tables: array of TClass;
  const aSQL: RawUTF8; Expand: boolean);
var JSONCached: RawUTF8;
    R: TSQLRequest;
    n: PtrInt;
begin
  JSONCached := aDB.LockJSON(aSQL,@n);
  if JSONCached='' then // not retrieved from cache -> call SQLite3 engine
................................................................................
  if (self<>nil) and (n>0) then
  try // will use JSON cache if available:
    aSQL := Model.SQLFromSelectWhere(Tables,SQLSelect,SQLWhere);
    if n=1 then
      // InternalListJSON will handle both static and DB tables
      result := fServer.InternalListJSON(TSQLRecordClass(Tables[0]),aSQL) else
      // we access localy the DB -> TSQLTableDB handle Tables parameter
      result := TSQLTableDB.Create(fServer.DB,
        RecordClassesToClasses(Tables),aSQL,not fServer.NoAJAXJSON);
    if fServer.DB.InternalState<>nil then
      result.InternalState := fServer.DB.InternalState^;
  except
    on ESQLite3Exception do
      result := nil;
  end;
end;

      a special request explicitely (note that JSON format returns BLOB data)
     - uses a TSQLTableJSON internaly: all currency is transformed to its floating
       point TEXT representation, and allows efficient caching
     - if the SQL statement is in the DB cache, it's retrieved from its cached
       value: our JSON parsing is a lot faster than SQLite3 engine itself,
       and uses less memory
     - will raise an ESQLException on any error }
    constructor Create(aDB: TSQLDatabase; const Tables: array of TSQLRecordClass;
      const aSQL: RawUTF8; Expand: boolean);
  end;

  TSQLRestServerDBClass = class of TSQLRestServerDB;

  TSQLVirtualTableModuleServerDB = class;

................................................................................


implementation


{ TSQLTableDB }

constructor TSQLTableDB.Create(aDB: TSQLDataBase; const Tables: array of TSQLRecordClass;
  const aSQL: RawUTF8; Expand: boolean);
var JSONCached: RawUTF8;
    R: TSQLRequest;
    n: PtrInt;
begin
  JSONCached := aDB.LockJSON(aSQL,@n);
  if JSONCached='' then // not retrieved from cache -> call SQLite3 engine
................................................................................
  if (self<>nil) and (n>0) then
  try // will use JSON cache if available:
    aSQL := Model.SQLFromSelectWhere(Tables,SQLSelect,SQLWhere);
    if n=1 then
      // InternalListJSON will handle both static and DB tables
      result := fServer.InternalListJSON(TSQLRecordClass(Tables[0]),aSQL) else
      // we access localy the DB -> TSQLTableDB handle Tables parameter
      result := TSQLTableDB.Create(fServer.DB,Tables,aSQL,not fServer.NoAJAXJSON);

    if fServer.DB.InternalState<>nil then
      result.InternalState := fServer.DB.InternalState^;
  except
    on ESQLite3Exception do
      result := nil;
  end;
end;

  {$endif}
end;

procedure TTestSQLite3Engine._TSQLTableJSON;
var J: TSQLTableJSON;
    aR, aF, n: integer;
    Comp: TUTF8Compare;
    List: TObjectList;
    {$ifdef UNICODE}
    Peoples: TObjectList<TSQLRecordPeople>;
    {$endif}
    row: variant;
begin
  J := TSQLTableJSON.Create([],'',JS);
  try
................................................................................
          Check(row.FirstName=J.GetString(StepRow,FieldIndex('FirstName')));
          Check(row.LastName=J.GetString(StepRow,FieldIndex('LastName')));
          Check(row.YearOfBirth=J.GetAsInteger(StepRow,FieldIndex('YearOfBirth')));
          Check(row.YearOfDeath=J.GetAsInteger(StepRow,FieldIndex('YearOfDeath')));
          inc(n);
       until not Step(false,@row);
      check(n=J.RowCount);
      List := TObjectList.Create;
      try
        ToObjectList(List,TSQLRecordPeople);
        check(List.Count=J.RowCount);
        for aR := 1 to List.Count do
        with TSQLRecordPeople(List.Items[aR-1]) do begin

          Check(fID=J.GetAsInteger(aR,FieldIndex('ID')));
          Check(FirstName=J.GetU(aR,FieldIndex('FirstName')));
          Check(LastName=J.GetU(aR,FieldIndex('LastName')));
          Check(YearOfBirth=J.GetAsInteger(aR,FieldIndex('YearOfBirth')));
          Check(YearOfDeath=J.GetAsInteger(aR,FieldIndex('YearOfDeath')));
        end;
      finally
        List.Free;
      end;
      {$ifdef UNICODE}
      Peoples := ToObjectList<TSQLRecordPeople>;
      try
        Check(Peoples.Count=J.RowCount);
        for aR := 1 to Peoples.Count do
        with Peoples[aR-1] do begin

  {$endif}
end;

procedure TTestSQLite3Engine._TSQLTableJSON;
var J: TSQLTableJSON;
    aR, aF, n: integer;
    Comp: TUTF8Compare;

    {$ifdef UNICODE}
    Peoples: TObjectList<TSQLRecordPeople>;
    {$endif}
    row: variant;
begin
  J := TSQLTableJSON.Create([],'',JS);
  try
................................................................................
          Check(row.FirstName=J.GetString(StepRow,FieldIndex('FirstName')));
          Check(row.LastName=J.GetString(StepRow,FieldIndex('LastName')));
          Check(row.YearOfBirth=J.GetAsInteger(StepRow,FieldIndex('YearOfBirth')));
          Check(row.YearOfDeath=J.GetAsInteger(StepRow,FieldIndex('YearOfDeath')));
          inc(n);
       until not Step(false,@row);
      check(n=J.RowCount);
      with ToObjectList(TSQLRecordPeople) do
      try

        check(Count=J.RowCount);
        for aR := 1 to Count do

        with Items[aR-1] as TSQLRecordPeople do begin
          Check(fID=J.GetAsInteger(aR,FieldIndex('ID')));
          Check(FirstName=J.GetU(aR,FieldIndex('FirstName')));
          Check(LastName=J.GetU(aR,FieldIndex('LastName')));
          Check(YearOfBirth=J.GetAsInteger(aR,FieldIndex('YearOfBirth')));
          Check(YearOfDeath=J.GetAsInteger(aR,FieldIndex('YearOfDeath')));
        end;
      finally
        Free;
      end;
      {$ifdef UNICODE}
      Peoples := ToObjectList<TSQLRecordPeople>;
      try
        Check(Peoples.Count=J.RowCount);
        for aR := 1 to Peoples.Count do
        with Peoples[aR-1] do begin