mORMot Open Source

gclaxton

Member

Registered: 2011-05-23

Posts: 6

Use CreateMissingTables to make a column "UNIQUE"

Is there a way to use CreateMissingTables to make a column "UNIQUE"?

unit FilesInclude.Client;

interface

uses
  Windows, FilesInclude.Data;

procedure CreateDatabase(const theFilename: string);

implementation

uses
  SQLite3;

function GetFileSize(const theFilename: string): TULargeInteger;
var
  Find: THandle;
  Data: TWin32FindData;
begin
  Find := Windows.FindFirstFile(PChar(theFilename), Data);
  if (Find <> INVALID_HANDLE_VALUE) then
  try
    Result.LowPart := Data.nFileSizeLow;
    Result.HighPart := Data.nFileSizeHigh;
  finally
    Windows.FindClose(Find);
  end
  else
    Result.QuadPart := -1;
end;

procedure CreateDatabase(const theFilename: string);
begin
  // If the file is 0 bytes, create the missing tables
  if (GetFileSize(theFilename).QuadPart = 0) then
  begin
    with TSQLRestClientDB(FilesIncludeClient) do
    begin
      // Todo: Use CreateMissingTables to make the 'filename' column "UNIQUE" as below. ?*****?
      Server.CreateMissingTables(0);
      (*
      Server.DB.Execute('CREATE TABLE IF NOT EXISTS [filesInclude] (' +
        '[id] INTEGER PRIMARY KEY AUTOINCREMENT, ' +
        '[filename] TEXT UNIQUE COLLATE NOCASE, '  +
        '[checked] INTEGER, '                      +
        '[description] TEXT COLLATE NOCASE);');
      *)

      // if TableRowCount = 0 then, set the default data values
      if (TableRowCount(TSQLFilesInclude) = 0) then
      begin
        Server.DB.Execute('INSERT OR IGNORE INTO filesInclude ' +
          '(filename, checked, description) VALUES ("*.$$$",     0, "MS-DOS Temporary File")');
        Server.DB.Execute('INSERT OR IGNORE INTO filesInclude ' +
          '(filename, checked, description) VALUES ("*.bak",     1, "Temporary Backup Files")');
        Server.DB.Execute('INSERT OR IGNORE INTO filesInclude ' +
          '(filename, checked, description) VALUES ("*.chk",     0, "Temporary Log Files")');
        Server.DB.Execute('INSERT OR IGNORE INTO filesInclude ' +
          '(filename, checked, description) VALUES ("*.err",     0, "Temporary Error File")');
        Server.DB.Execute('INSERT OR IGNORE INTO filesInclude ' +
          '(filename, checked, description) VALUES ("*.tmp",     1, "Temporary Files")');
        Server.DB.Execute('INSERT OR IGNORE INTO filesInclude ' +
          '(filename, checked, description) VALUES ("thumbs.db", 0, "Microsoft Thumbnails File")');
      end;
    end;
  end;
end;

end.

Last edited by gclaxton (2011-05-26 19:37:20)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Use CreateMissingTables to make a column "UNIQUE"

Please look at the documentation: just mark the field "stored false" in the class definition, and it will be created as unique.

For instance, here is how the TSQLAuthUser class is defined, with the LogonName column forced to be UNIQUE:

  TSQLAuthUser = class(TSQLRecord)
(...)
  published
    /// the User identification Name, as entered at log-in
    // - the same identifier can be used only once (this column
    // is marked as unique)
    property LogonName: RawUTF8 read fLogonName write fLogonName stored false;
(...)

And IMHO you should not use "SQL by hand" on the server side, but rely on ORM methods of the framework, if possible without using Server.DB but the Client instance, i.e. TSQLRestClientDB in your case.
I've updated the framework documentation to explain better how to use high-level ORM methods instead of SQL.
Please take a look at the TSQLRest methods: you'll find here almost all needed ORM-oriented methods to change the TSQLRecord content.