uses
mormot.core.base,
mormot.core.data,
mormot.core.text,
mormot.core.perf,
mormot.core.search,
mormot.core.unicode,
mormot.core.variants,
mormot.core.datetime,
mormot.core.os,
mormot.net.client,
mormot.orm.base,
mormot.orm.core,
mormot.orm.sqlite3,
mormot.rest.core,
mormot.rest.sqlite3,
mormot.db.raw.sqlite3,
mormot.db.raw.sqlite3.
static;
type
TCsvItem =
packed record
created_at:
String;
entry_id: Integer;
field1: Double;
field2: Double;
field3: Double;
field4:
String;
end;
TCsvItems =
array of TCsvItem;
TOrmCsvItem =
class(TOrm)
private
FCreatedAt: TDateTime;
FField1: Double;
FField2: Double;
FField3: Double;
FField4: Integer;
published
property CreatedAt: TDateTime
read FCreatedAt
write FCreatedAt;
property Field1: Double
read FField1
write FField1;
property Field2: Double
read FField2
write FField2;
property Field3: Double
read FField3
write FField3;
property Field4: Integer
read FField4
write FField4;
end;
function UpdateCsvItems(
const pmcDBFileName: TFileName): Integer;
const
BASE_URL = '
https://api.thingspeak.com/channels/1671193/feeds.csv?start=';
var
csvItems: TCsvItems;
begin
Result := -1;
var restClient: TRestClientDB := TRestClientDB.Create(TOrmModel.Create([TOrmCsvItem]),
Nil, pmcDBFileName, TRestServerDB,
{HandleUserAuthentication=}False);
try
restClient.Model.Owner := restClient;
restClient.DB.Synchronous := smOff;
restClient.DB.LockingMode := lmExclusive;
restClient.Server.CreateMissingTables(0, [itoNoAutoCreateGroups, itoNoAutoCreateUsers]);
var ormItem: TOrmCsvItem := TOrmCsvItem.Create;
try
var startTime: TDateTime := IncYear(Now, -100);
var maxID: TID := restClient.TableMaxID(TOrmCsvItem);
if (maxID >= 0)
and restClient.Retrieve(maxID, ormItem)
then
startTime := ormItem.CreatedAt;
var url: RawUtf8 := BASE_URL + DateTimeToIso8601(startTime,
{Expanded=}True, '
T',
{WithMS=}False, '
"');
if DynArrayLoadCsv(csvItems, HttpGet(
url), TypeInfo(TCsvItems))
then
begin
if Length(csvItems) = 0
then Exit;
//=>
restClient.BatchStart(TOrmCsvItem);
for var i: Integer := 0
to High(csvItems)
do
begin
if csvItems[i].entry_id <= maxID
then Continue;
with csvItems[i]
do
begin
ormItem.IDValue := entry_id;
ormItem.CreatedAt := Iso8601ToDateTime(StringToUtf8(created_at));
ormItem.Field1 := field1;
ormItem.Field2 := field2;
ormItem.Field3 := field3;
ormItem.Field4 := field4.Trim.ToInteger;
end;
restClient.BatchAdd(ormItem, True, True);
end;
var insRowIDs: TIDDynArray;
if restClient.BatchSend(insRowIDs) = HTTP_SUCCESS
then
Result := Length(insRowIDs);
end;
finally
ormItem.Free;
end;
finally
restClient.Free;
end;
end;