Serverless AWS Connection

Hello

In Aurelius I'm using the Zeos driver against a MySQL database. The database is a "serverless" database at Amazon Web Services where I send SQL statements as text to a REST interface rather than establish a direct connection.

I have no username, password or MySQL connection URL to provide. I have set the SQLExecutionEnabled property of the TDatabaseManager to False but on startup the driver appears to be attempting to establish a connection. The error I'm getting is

        Requested database driver was not found

The connection URL attempted is "zdbc:://".

Is there a way I can configure the connection to generate SQL without interacting with an actual database?

Thanks

David

Hello David,
Which is the full code you are using to generate the SQL statements? You should use BuildDatabase, not UpdateDatabase, because the latter would of course try to connect to the database to retrieve existing structure.
The Code:

constructor TAurelius.Create;
var
    localDbManager: TDatabaseManager;
    remoteDbManager: TDatabaseManager;
    dbPathName: String;
    stmt: string;
    log: TextFile;
begin
    inherited;

    dbPathName := TPath.Combine(TPath.GetDocumentsPath, STRDatabaseFileName);
    fLocalDBConnection := TSQLiteConnection.Create('Database=' + dbPathName + ';EnableForeighKeys=True');
    fLocalObjectManager := TObjectManager.Create(fLocalDBConnection, TMappingExplorer.Get(STRLocal));
    localDbManager := TDatabaseManager.Create(fLocalDBConnection);

    fRemoteDBConnection := TZeosLibConnectionAdapter.Create(TZConnection.Create(nil), 'MySQL', True);
    fRemoteObjectManager := TObjectManager.Create(fRemoteDBConnection, TMappingExplorer.Get(STRRemote));
    remoteDBManager := TDatabaseManager.Create(fRemoteDBConnection);
    remoteDBManager.SQLExecutionEnabled := False;

    try
        if(FileExists(dbPathName)) then
            localDbManager.UpdateDatabase
        else
            localDbManager.BuildDatabase;

        remoteDBManager.ValidateDatabase;  /<== error occurs here
        AssignFile(log, STRSQLLogPathName);
        Rewrite(log);
        for stmt in remoteDBManager.SQLStatements do
            WriteLn(log, stmt);
        CloseFile(log);

    finally
        localDbManager.DisposeOf;
        remoteDbManager.DisposeOf;
    end;
end;

I used ValidateDatabase in hope of avoiding a connection. Error occurs at ValidateDatabase.

My question to the entire database lifetime. After the database is built I would need to perform every Insert, Select, Update and Delete by sending SQL text to the serverless database, not by allowing the driver to execute the commands. So I am looking for a way to suppress execution of all DDL and DML SQL commands, instead capture them as text and sending them via HTTPS.

Thanks

ValidateDatabase "validates" a database, i.e., it needs to connect to it to check what's there before deciding and validating what's missing in it. You should use BuildDatabase to get a list of SQL statements that build up a full database from scratch, without needing a connection. UpdateDatabase and ValidateDatabase are intended to get a "delta", i.e., SQL statements needed to get the database structure to the current point. That needs a connection to retrieve current database state.


About your second question, I don't understand. Aren't you using Zeos to perform SQL statements in the database? Aurelius uses drivers to connect to databases and execute SQL statements. There are builtin drivers: FireDac, Zeos, UniDac, etc.. If they don't fit your needs, you should build your own driver. One way is simply copy/paste one of the current ones and modify them for your needs.
Thanks for showing me how to get the database built.

Regarding the second question, a MySQL serverless database at AWS can operate in either of 2 modes, using a conventional database connection, or using a REST interface. Security and cost implications differ for the 2 approaches and our situation favors the REST approach. Database drivers I've worked with in the past have supported a mode where the driver operates normally except the SQL that is generated is not actually executed on the database. Sort of a "test" mode allowing the developer to examine the SQL that would be executed. Aurelius has met all of our requirements so far and I thought I'd find out about the test mode before researching the Zeos driver, and then possibly having to modify an existing driver to get the behavior we need.

I'm taking your response to mean that Aurelius does not currently offer the SQL-only mode we are looking for.

Thanks
You can use 

TGlobalConfigs.GetInstance.SimuleStatements := True;

As described here:

http://www.tmssoftware.biz/business/aurelius/doc/web/global_configuration.html

Then you can use the OnSqlExecuting event to retrieve the SQL statements:

http://www.tmssoftware.biz/business/aurelius/doc/web/onsqlexecuting_event.html

That's one option, the other, as I mentioned, is create a driver that executes the statements the way you want, that would be a more "correct" way imho.
BINGO!

I was pretty sure I had seen it in your docs, but I did not re-read Global Configuration. My bad.

I think Aurelius is a brilliant product and this is a great answer. I have every intention of making your suggest work. If you feel like typing I would love to know why you think modifying a database driver would be a more correct solution. You've just made me glad, once again, that I chose Aurelius.

Thanks

David

A database driver is a more correct solution because in some situations it's the only solution.

For example, in some situations (when using version control) it has to know how many rows were affected by the SQL statement. You should be able to return that to Aurelius.
Even worse: for SELECT statements, Aurelius needs to be provided with data retrieved. How are you going to do that with the events?

Thanks for pointing these things out.

I saw the version control problem and circumvented it. I totally overlooked the need to provide Aurelius with the retrieved rows. Busted! If I fail miserably then I'll have to fall back to a normal database connection. But if I come up with a solution I'll have the data managment solution of my dreams. That's a huge carrot for me. Don't worry, I won't hound you with questions about how to hack Aurelius.

Thank you for your insights. Wish me luck ;-)

It should be relatively simple. Check Aurelius.Drivers.Interfaces unit. Just implement IDBConnection interface. That will require you to also implement IDBStatement, IDBTransaction, and IDBResultSet. Those are very simple interfaces. 


Most of what they do is execute SQL statements and return data, in very simple way. Once you do that, your Aurelius will be working with your serverless database transparently.

We provide custom services as well, if you would be interested. Just contact us directly via e-mail.