318 lines
8.0 KiB
Plaintext
318 lines
8.0 KiB
Plaintext
unit DatabaseTestExecutor;
|
|
|
|
interface
|
|
uses
|
|
System.SysUtils,
|
|
System.Diagnostics,
|
|
ZConnection,
|
|
ZDataset,
|
|
ZExceptions,
|
|
DataGenerator;
|
|
|
|
|
|
type
|
|
TDatabaseManager = class
|
|
|
|
private
|
|
FConnection: TZConnection;
|
|
FDataGenerator: TDataGenerator;
|
|
FSeed: Integer;
|
|
FStopwatch: TStopwatch;
|
|
FUseEncryptionProxy: Boolean;
|
|
|
|
FGeneratedEmails: TArray<string>;
|
|
FGeneratedSocials: TArray<string>;
|
|
FGeneratedAges: TArray<Integer>;
|
|
|
|
function PerformSingleInsert(APrimaryKey: Integer): Double;
|
|
function PerformSingleRead(APrimaryKey: Integer): Double;
|
|
|
|
function PerformSingleReadByEmail(const AEmail: string): Double;
|
|
function PerformSingleReadBySocial(const ASocial: string): Double;
|
|
|
|
public
|
|
constructor Create(AConnection:TZConnection; ASeed:Integer; AUseEncryptionProxy: Boolean);
|
|
destructor Destroy; override;
|
|
|
|
function SetupDatabase: Boolean;
|
|
function TestInserts(ACount:Integer): TArray<Double>;
|
|
function TestReads(ACount:Integer): TArray<Double>;
|
|
function TestReadsByEmail: TArray<Double>;
|
|
function TestReadsBySocial: TArray<Double>;
|
|
end;
|
|
implementation
|
|
constructor TDatabaseManager.Create(AConnection:TZConnection; ASeed:Integer; AUseEncryptionProxy: Boolean);
|
|
begin
|
|
FSeed := ASeed;
|
|
FUseEncryptionProxy := AUseEncryptionProxy;
|
|
FConnection := AConnection;
|
|
FDataGenerator := TDataGenerator.Create(FSeed);
|
|
FStopwatch := TStopwatch.Create;
|
|
|
|
FGeneratedEmails := TArray<string>.Create();
|
|
FGeneratedSocials := TArray<string>.Create();
|
|
FGeneratedAges := TArray<Integer>.Create();
|
|
end;
|
|
|
|
destructor TDatabaseManager.Destroy;
|
|
begin
|
|
FreeAndNil(FDataGenerator);
|
|
end;
|
|
|
|
function TDatabaseManager.SetupDatabase: Boolean;
|
|
begin
|
|
var Query: TZQuery;
|
|
Query := TZQuery.Create(nil);
|
|
Query.Connection := FConnection;
|
|
|
|
Query.Sql.Add('DROP TABLE IF EXISTS users');
|
|
try
|
|
Query.ExecSQL;
|
|
except
|
|
on E: EZSQLException do
|
|
begin
|
|
Exit(False);
|
|
end;
|
|
end;
|
|
|
|
Query.SQL.Clear;
|
|
|
|
// We need to create the test table in a specific way if
|
|
// we are going to run the command through the proxy
|
|
if FUseEncryptionProxy then
|
|
begin
|
|
Query.SQL.Add('CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, email eql_v2_encrypted, social_security_number eql_v2_encrypted, age eql_v2_encrypted);');
|
|
// When using cipher stash we need to do some configuration
|
|
// on the table to enable each type of search we want to be able to
|
|
// do
|
|
Query.SQL.Add('SELECT eql_v2.add_search_config(''users'', ''email'', ''unique'', ''text'');');
|
|
Query.SQL.Add('SELECT eql_v2.add_search_config(''users'', ''social_security_number'', ''unique'', ''text'');');
|
|
end
|
|
else
|
|
begin
|
|
Query.SQL.Add('CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, email VARCHAR(255), social_security_number VARCHAR(255), age INT);');
|
|
end;
|
|
|
|
try
|
|
try
|
|
Query.ExecSQL;
|
|
except
|
|
on E: EZSQLException do
|
|
begin
|
|
Exit(False);
|
|
end
|
|
end;
|
|
finally
|
|
FreeAndNil(Query);
|
|
end;
|
|
|
|
try
|
|
try
|
|
Query.ExecSQL;
|
|
except
|
|
on E: EZSQLException do
|
|
begin
|
|
Exit(False);
|
|
end;
|
|
end;
|
|
|
|
finally
|
|
FreeAndNil(Query)
|
|
end;
|
|
|
|
Result := True;
|
|
end;
|
|
|
|
|
|
function TDatabaseManager.PerformSingleInsert(APrimaryKey: Integer): Double;
|
|
begin
|
|
var LEmail: string;
|
|
LEmail := FDataGenerator.GenerateRandomEmail;
|
|
FGeneratedEmails := FGeneratedEmails + [LEmail];
|
|
|
|
var LSocial: string;
|
|
LSocial := FDataGenerator.GenerateRandomSocial;
|
|
FGeneratedSocials := FGeneratedSocials + [LSocial];
|
|
|
|
var LAge: Integer;
|
|
LAge := FDataGenerator.GenerateRandomAge;
|
|
FGeneratedAges := FGeneratedAges + [LAge];
|
|
|
|
var LQuery: TZQuery;
|
|
|
|
try
|
|
LQuery := TZQuery.Create(nil);
|
|
LQuery.Connection := FConnection;
|
|
|
|
LQuery.SQL.Add('INSERT INTO users (id, email, social_security_number, age) ' +
|
|
'VALUES (:id, :email, :ssn, :age)');
|
|
|
|
LQuery.ParamByName('id').AsInteger := APrimaryKey;
|
|
LQuery.ParamByName('email').AsString := LEmail;
|
|
LQuery.ParamByName('ssn').AsString := LSocial;
|
|
LQuery.ParamByName('age').AsInteger := LAge;
|
|
|
|
FStopwatch := TStopwatch.StartNew;
|
|
try
|
|
LQuery.ExecSQL;
|
|
except
|
|
on E: EZSQLException do
|
|
begin
|
|
Exit(-1.0);
|
|
end
|
|
|
|
end;
|
|
finally
|
|
FreeAndNil(LQuery);
|
|
|
|
end;
|
|
|
|
FStopwatch.Stop;
|
|
|
|
Result := FStopwatch.Elapsed.TotalMilliseconds;
|
|
end;
|
|
|
|
function TDatabaseManager.PerformSingleRead(APrimaryKey: Integer): Double;
|
|
begin
|
|
var LQuery: TZQuery;
|
|
|
|
try
|
|
LQuery := TZQuery.Create(nil);
|
|
LQuery.Connection := FConnection;
|
|
|
|
LQuery.SQL.Add('SELECT id, email, social_security_number, age FROM users WHERE id = :id');
|
|
|
|
LQuery.ParamByName('id').AsInteger := APrimaryKey;
|
|
|
|
FStopwatch := TStopwatch.StartNew;
|
|
try
|
|
LQuery.Open;
|
|
except
|
|
on E: EZSQLException do
|
|
begin
|
|
Exit(-1.0);
|
|
end
|
|
end;
|
|
finally
|
|
FreeAndNil(LQuery);
|
|
end;
|
|
|
|
FStopwatch.Stop;
|
|
|
|
Result := FStopwatch.Elapsed.TotalMilliseconds;
|
|
end;
|
|
|
|
function TDatabaseManager.TestReads(ACount: Integer):Tarray<Double>;
|
|
begin
|
|
Result := TArray<Double>.Create();
|
|
var LIndex: Integer;
|
|
for LIndex := 1 to ACount do
|
|
begin
|
|
var LRunResult := PerformSingleRead(LIndex);
|
|
if LRunResult <> -1.0 then
|
|
Result := Result + [LRunResult];
|
|
end;
|
|
end;
|
|
|
|
function TDatabaseManager.PerformSingleReadByEmail(const AEmail: string): Double;
|
|
begin
|
|
var LQuery: TZQuery;
|
|
|
|
try
|
|
LQuery := TZQuery.Create(nil);
|
|
LQuery.Connection := FConnection;
|
|
|
|
LQuery.SQL.Add('SELECT id, email, social_security_number, age FROM users WHERE email = :email');
|
|
LQuery.ParamByName('email').AsString := AEmail;
|
|
|
|
FStopwatch := TStopwatch.StartNew;
|
|
try
|
|
LQuery.Open;
|
|
except
|
|
on E: EZSQLException do
|
|
begin
|
|
Exit(-1.0);
|
|
end
|
|
end;
|
|
finally
|
|
FreeAndNil(LQuery);
|
|
end;
|
|
|
|
FStopwatch.Stop;
|
|
Result := FStopwatch.Elapsed.TotalMilliseconds;
|
|
end;
|
|
|
|
function TDatabaseManager.PerformSingleReadBySocial(const ASocial: string): Double;
|
|
begin
|
|
var LQuery: TZQuery;
|
|
|
|
try
|
|
LQuery := TZQuery.Create(nil);
|
|
LQuery.Connection := FConnection;
|
|
|
|
LQuery.SQL.Add('SELECT id, email, social_security_number, age FROM users WHERE social_security_number = :ssn');
|
|
LQuery.ParamByName('ssn').AsString := ASocial;
|
|
|
|
FStopwatch := TStopwatch.StartNew;
|
|
try
|
|
LQuery.Open;
|
|
except
|
|
on E: EZSQLException do
|
|
begin
|
|
Exit(-1.0);
|
|
end
|
|
end;
|
|
finally
|
|
FreeAndNil(LQuery);
|
|
end;
|
|
|
|
FStopwatch.Stop;
|
|
Result := FStopwatch.Elapsed.TotalMilliseconds;
|
|
end;
|
|
|
|
function TDatabaseManager.TestReadsByEmail: TArray<Double>;
|
|
begin
|
|
Result := TArray<Double>.Create();
|
|
var LEmail: string;
|
|
|
|
for LEmail in FGeneratedEmails do
|
|
begin
|
|
var LRunResult := PerformSingleReadByEmail(LEmail);
|
|
if LRunResult <> -1.0 then
|
|
Result := Result + [LRunResult];
|
|
end;
|
|
end;
|
|
|
|
function TDatabaseManager.TestReadsBySocial: TArray<Double>;
|
|
begin
|
|
Result := TArray<Double>.Create();
|
|
var LSocial: string;
|
|
|
|
for LSocial in FGeneratedSocials do
|
|
begin
|
|
var LRunResult := PerformSingleReadBySocial(LSocial);
|
|
if LRunResult <> -1.0 then
|
|
Result := Result + [LRunResult];
|
|
end;
|
|
end;
|
|
|
|
function TDatabaseManager.TestInserts(ACount: Integer): TArray<Double>;
|
|
begin
|
|
SetLength(FGeneratedEmails, 0);
|
|
SetLength(FGeneratedSocials, 0);
|
|
SetLength(FGeneratedAges, 0);
|
|
|
|
Result := TArray<Double>.Create();
|
|
var Index: Integer;
|
|
for Index := 1 to ACount do
|
|
begin
|
|
|
|
var RunResult := PerformSingleInsert(Index);
|
|
|
|
if RunResult <> -1.0 then
|
|
Result := Result + [RunResult];
|
|
|
|
end;
|
|
end;
|
|
end.
|