Introduction

This is the first post of a series that will introduce you to new features of the upcoming NexusDB V3. Each of the series (starting with the second post) will be accompanied by peek-preview binaries that you can use to experiment with the new feature.

Server Side Pascal Scripting

One major new feature of NexusDB V3 is the ability to run server side pascal scripts. These scripts have full access to the server infrastructure (like server engine, monitors, settings) as well as to all databases. The initial release will only include a simple access control to this feature on the basis of a security token which can be assigned to a user (same as the R, W and A tokens in NexusDB V2). Incremental updates will introduce finer access control to the server infra structure and databases.

Let's take a look at a small script that executes a query and dumps the data to the output.

Example

var
  aSession: TnxSession;
  adb: TnxDatabase;
  aQuery: TnxQuery;
  i: integer;
begin
  aSession:=TnxSession.Create(nil);
  adb:=TnxDatabase.Create(nil);
  aQuery:=TnxQuery.Create(nil);
  try
    aSession.ServerEngine:=ServerEngine;
    aSession.Active:=true;
    adb.AliasName:='Test';
    adb.Session:=aSession;
    adb.Open;
    aQuery.Database:=adb;
    aQuery.SQL.Text:='select * from test';
    aQuery.Open; 
    while not aQuery.eof do
    begin
      for i:=0 to aQuery.FieldCount-1 do
        Output.Write(aQuery.Fields.Fields[i].AsString+',');
      Output.WriteLn('');
      aQuery.Next;
    end;
    Output.SaveToFile('c:\out.txt');
  finally
    aQuery.Free;
    adb.free;
    aSession.Free;
  end;
end.

As you can see, very straight forward, the only thing different to a standard Delphi function is the use of the global variable ServerEngine (which is a direct reference to the main server engine of the server where the script is executed) and the use of the global Output class instance (preliminary class members) to dump the data.

The script processing on the server side is implemented as a special handler of the also new nxRemoteRemoteCommands plugin. This plugin has an extensible Type parameter and one such type is cmdExecScript.

How to run a script?

Now that's the question is it. Simple to answer: All you need to do is call the plugins ExecCommand method. The server side implementation of the plugin creates an Alias 'ServerRemoteCommand' which will have the the output of the script buffered in a (per command unique) table.

One of the simplest ways start a script and get return the output is the following:

begin
  Transport:=TnxWinsockTransport.Create(nil);
  ServerEngine:=TnxRemoteServerEngine.Create(nil);
  Session:=TnxSession.Create(nil);
  rc:=TnxRemoteRemoteCommandsPlugin.Create(nil);
  try
    try
      Transport.ServerNameRuntime:='nexusdb@127.0.0.1';
      ServerEngine.Transport:=Transport;
      Session.ServerEngine:=ServerEngine;
      rc.Session:=Session;
      rc.Active:=true;
      rc.ExecCommand('cmdExecScript', 'myscript.nxscript', '', error, errormessage, taskid);

      if (error<>0) or (taskid=0) then
        writeln(error, ' - ', errormessage)
     else
     begin
        Taskinfo:=TnxRemoteTaskInfo.Create(TnxRemoteSession(Session.AbstractSession), TaskID);
        Database:=TnxDatabase.Create(nil);
        ProgressTable:=TnxTable.Create(nil);
        if Assigned(TaskInfo) then
        try
          Database.Session:=Session;
          Database.AliasName:='ServerRemoteCommand';
          Database.Open;
          ProgressTable.Database:=Database;
          ProgressTable.TableName:='ServerRemoteCommand_'+IntToStr(taskid);
          repeat
            ProgressTable.Open;
            while not ProgressTable.Eof do
            begin
              Write(ProgressTable.FindField('Lines').AsString);
              ProgressTable.Delete;
            end;
            ProgressTable.Close;
            TaskInfo.GetStatus(Completed, Status);
            if not Completed then begin
              Sleep(500);
            end;
          until Completed;

          ProgressTable.Open;
          while not ProgressTable.Eof do
          begin
            Write(ProgressTable.FindField('Lines').AsString);
            ProgressTable.Delete;
          end;
          ProgressTable.Close;
        finally
          ProgressTable.Free;
          Database.Free;
          TaskInfo.Free;
        end;
      end;
    except
      on E:Exception do
      begin
        Writeln(E.Classname, ': ', E.Message);
      end;
    end;
  finally
    rc.Free;
    Session.Free;
    ServerEngine.Free;
    Transport.Free;
  end;
end.

Even thought that's already pretty simple (hey, it IS!), we will make it even easier by providing some wrapper functions for calling scripts like:

function ExecuteServerScript(servername, username, password, scriptname: string; 
  result: TStringlist; var Error: String): boolean;

Na now say hello to a very small but in combination with server scripting very powerful tool:

nxCommand.exe

This nifty utility is an extended command line implementation of above code and can be used to call NexusDB server side scripts. The command line arguments are as following:

nxCommand /Type:cmdExecScript /Script:myscript.nxscript [/param1:asneededbythescript ...]

See a screen shot here.

You can also execute a script directly by replacing the last parameter with

/Direct:"begin Output.Write('abc'); end."

in future (together with updated security) we will of course add support for server, username and password arguments, but for the time being this will only work on a local server.

Script Repository

We will provide at least 2 scripts at launch which will be shipped with NexusDB and at the same time will be available from our upcoming online script repository. Every registered user of our web site will be able to access and add scripts in that global repository.

The first script runSQL.nxscript will execute a SQL command and allow to format the output with delimeters, quoting etc. This can be used to very quickly export some data to a CSV (or similar) file. Please look at the preliminary source for the details. You can call it like this (for a local server with no user/password):

nxCommand /Type:cmdExecScript /Script:runsql.nxscript /database:dbname 
  /sql:"select * from t1" /delimiter:%20 /qualifier:%22 > export.txt

This will create nice CSV formated file of the given query.

The second script is Backup.nxscript which can be used in the pre-backup event of your favourite backup solution to create a consistent backup of your database. Call it like that:

nxCommand /Type:cmdExecScript /Script:Backup.nxscript 
  /Source:srcalias|srcpath /Destination:destalias|destpath

nxCommand returns, like a good command line utility, an error code<>0 if something didn't work as expected and dumps the error message to STDOUT/STDERR.

(We're also considering to ship an extra nxScript.exe command line utility that would remove the need for explicitly specifying the Type parameter.)

Try it yourself

We've made a test version of NexusDB V3 available for everyone interested to try out the new features. Please give it a shot and if you find any problems please report them to our new Issue Tracker (website registration required).

Home | Community | Blogs | Hannes' Blog