MySSQL

[CurryWurst]

Script Name: MySSQL
Script Description Database functions to manage your own database.
Author: CurryWurst
Compile Test: Passed
Core Version: 2.6.5

MySSQL v2.1 syntax examples & descriptions

function CreateTable(var Table: TMySSQL_Table; const Tablename: string): boolean;

Creates a new table file and links a TMySSQL_Table object against it.
Returns true, if the table has been created and loaded into the TMySSQL_Table object.
Tables are stored in ".myssql" files in the folder "./tables" of your soldat servers' root directory. Grant at least 755 rights to this folder (for linux users only).

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;

procedure ActivateServer();
begin
  if (not(CreateTable(MyTable, 'tablename'))) then
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


procedure DestroyTable(var Table: TMySSQL_Table);

Unloads a TMySSQL_Table object and unlinks it from the table file. The table will not be saved.
You may reuse the TMySSQL_Table object you passed for other tables after calling this procedure.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;

procedure ActivateServer();
begin
  if (not(CreateTable(MyTable, 'tablename'))) then
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
  DestroyTable(MyTable);
end;


function LoadTable(var Table: TMySSQL_Table; const Tablename: string): boolean;

Loads a table from a file into a TMySSQL_Table object and links the object against the table file.
If this function is not successful, it will return false and the TMySSQL_Table object will not be modified.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;

procedure ActivateServer();
begin
  if (not(LoadTable(MyTable, 'tablename'))) then
  begin
    WriteLn('Failed to load table, reason:' + MyTable.Exception);
  end;
end;


function UnloadTable(var Table: TMySSQL_Table): boolean;

Saves the table of a TMySSQL_Table object (if it has been modified), unloads it and unlinks it from the table file.
Returns true, in case the function was successful.
TMySSQL_Table object can be reused for other tables after calling this function.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;

procedure ActivateServer();
begin
  if (not(LoadTable(MyTable, 'tablename'))) then
  begin
    WriteLn('Failed to load table, reason:' + MyTable.Exception);
  end;
  if (not(UnloadTable(MyTable))) then
  begin
    WriteLn('Could not unload table, reason: ' + MyTable.Exception);
  end;
end;


function SaveTable(var Table: TMySSQL_Table): boolean;

Saves the table of a TMySSQL_Table object into the file it's linked against.
Returns true, if the table was saved successfully.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;

procedure ActivateServer();
begin
  if (not(CreateTable(MyTable, 'new_tablename'))) then
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
  if (not(SaveTable(MyTable))) then
  begin
    WriteLn('Could not save table, reason: ' + MyTable.Exception);
  end;
end;


function CreateRow(var Table: TMySSQL_Table; const Columns: array of variant): word;

Creates a new row in your table. Columns can be of any variable type.
Returns the row index of the newly created row. You may save this index to access this row later on.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;

procedure ActivateServer();
begin
  if (not(CreateTable(MyTable, 'tablename'))) then
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
  WriteLn('New row created. Index: ' + IntToStr(CreateRow(MyTable, [20, 'data', 12.5, true])));
end;


function DestroyRow(var Table: TMySSQL_Table; const Row: word): boolean;

Removes a row from your table, whereas the parameter Row (index which you received from calling CreateRow) specifies which row is to be removed.
If the row was removed successfully, the function will return true.
Please note that this function may change the order of your rows within the table. Hence, if you saved indexes of rows you need to refresh them. Normally, the index of your last row will be the index of the row you removed, i.e. assume you got a table with three rows with the indexes 0, 1, 2 and you delete the second row (index = 1), the third row will have the index 1.


function FetchColumn(var Table: TMySSQL_Table; const Row, Column: word; var DummyC: TMySSQL_Column): boolean;

"Slow" function to fetch a record of a column. Requires to have a object of the type TMySSQL_Column passed on.
Your TMySSQL_Column object will contain the value of your column record, in case the function was called successfully.
Returns true, if the column record could be fetched.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  MyColumn: TMySSQL_Column;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'accounts')) then
  begin
    Index:= CreateRow(MyTable, ['Curry', 'password', true]);
    if (FetchColumn(MyTable, Index, 0, MyColumn)) then
    begin
      WriteLn('Username: ' + MyColumn.Value); // Output: "Username: Curry"
    end else
    begin
      WriteLn('Unable to fetch column, reason: ' + MyTable.Exception);
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


function FetchRowByColumn(var Table: TMySSQL_Table; const Column: word; const Value: variant): integer;

Particular function to fetch a row by a (unique) column record. Especially for nick based account systems.
Searches for a row in which the column Column (= index of the column) contains the record Value. Value can be of any variable type.
If a row which matches the condition was found, it will return the row index, if not the index will be -1.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: integer;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'accounts')) then
  begin
    CreateRow(MyTable, ['Curry', 'password', true]);
    Index:= FetchRowByColumn(MyTable, 0, 'Curry');
    if (Index > -1) then
    begin
      WriteLn('Row index of account Curry: ' + IntToStr(Index)); // Output: "Row index of account Curry: 0"
    end else
    begin
      WriteLn('No row found');
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


function SetColumn(var Table: TMySSQL_Table; const Row, Column: word; const Value: variant): boolean;

"Slow" function to set the record of a column of a row to any value you want it to be.
Returns true, if the column record was set to the new value.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'animals')) then
  begin
    Index:= CreateRow(MyTable, ['dogs', 'cats', 'horses']);
    if (SetColumn(MyTable, Index, 2, 90)) then
    begin
      WriteLn('Column successfully updated');
    end else
    begin
      WriteLn('Could not update column, reason: ' + MyTable.Exception);
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


function AppendColumn(var Table: TMySSQL_Table; const Row: word; const Value: variant): boolean;

Appends a new column to the end of each row.
In case the function is successfully, it will return true,

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'nature')) then
  begin
    Index:= CreateRow(MyTable, ['nature', 'tree']);
    if (AppendColumn(MyTable, Index, 'flower')) then
    begin
      WriteLn('Successfully appended a new column');
    end else
    begin
      WriteLn('Could not append a new column, reason: ' + MyTable.Exception);
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


function CacheRow(var Table: TMySSQL_Table; const Row: word): boolean;

Loads a row and all its columns into the cache and grants it very fast fetch/set time.
You always should prefer to cache rows, you want to fetch or set columns from, if you are going to access these rows more than one time, instead of using FetchColumn and SetColumn.
Returns true, if the function is successful and allows you to fetch/set columns via CFetchColumn/CSetColumnand CIncColumn. Alternatively, you can set or fetch columns by accessing the variables of a TMySSQL_Table object...

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'nature')) then
  begin
    Index:= CreateRow(MyTable, ['nature', 'tree', 'flower']);
    if (CacheRow(MyTable, Index)) then
    begin
      WriteLn('Row cached');
      // You can now use CFetchColumn, CSetColumnand and CIncColumn to fetch/set columns
      // or use the alternative syntax, which is not recommended, but for the sake of this script, here it is...
      WriteLn(MyTable.Rows[Index].Columns[2].Value) // Output: "flower"
      MyTable.Rows[Index].Columns[2].Value:= 'rose';
      WriteLn(MyTable.Rows[Index].Columns[2].Value) // Output: "rose"
    end else
    begin
      WriteLn('Failed to cache row: ' + MyTable.Exception);
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


procedure UncacheRow(var Table: TMySSQL_Table; const Row: word);

Removes a row from the cache and disables the functions CFetchColumn, CSetColumn and CIncColumn for it.
If you don't need to access a row anymore, you always should remove it from the cache.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'nature')) then
  begin
    Index:= CreateRow(MyTable, ['nature', 'tree', 'flower', 'daffodil']);
    if (CacheRow(MyTable, Index)) then
    begin
      WriteLn('Row cached');
      // Use CFetchColumn, CSetColumn and CIncColumn here
      UncacheRow(MyTable, Index);
      // Using CFetchColumn, CSetColumn and CIncColumn now will result into a MySSQL exception and can be "catched" with MyTable.Exception
    end else
    begin
      WriteLn('Could not cache row, reason: ' + MyTable.Exception);
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


function CFetchColumn(var Table: TMySSQL_Table; const Row, Column: word): string;

Very fast function to fetch a column record of a row.
Returns a blank string in case the column record is blank or does not exist, but I doubt you will make use of blank columns, so checking CFetchColumn against <> '' is a good way to determine whether it failed or not.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'nature')) then
  begin
    Index:= CreateRow(MyTable, ['nature', 'tree', 'flower', 'daffodil']);
    if (CacheRow(MyTable, Index)) then
    begin
      if (CFetchColumn(MyTable, Index, 0) <> '') then
      begin
        WriteLn('Value is: ' + CFetchColumn(MyTable, Index, 0)); // Output: "Value is: nature"
      end else
      begin
        WriteLn('Failed to fetch column, reason: ' + MyTable.Exception);
      end;
      UncacheRow(MyTable, Index);
    end else
    begin
      WriteLn('Could not cache row, reason: ' + MyTable.Exception);
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;

function CSetColumn(var Table: TMySSQL_Table; const Row, Column: word; const Value: variant): boolean;

Very fast function to set a column record of a row to any value you want it to be. Requires the row, you want to set a column of, to be cached.
If this function succeeds, it will return true.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'nature')) then
  begin
    Index:= CreateRow(MyTable, ['nature', 'tree', 'flower', 'daffodil']);
    if (CacheRow(MyTable, Index)) then
    begin
      CSetColumn(MyTable, Index, 3, 'rose');
      if (CFetchColumn(MyTable, Index, 3)) then
      begin
        WriteLn('New value is: ' + CFetchColumn(MyTable, Index, 3)); // Output: "New value is: rose"
      end else
      begin
        WriteLn('Failed to fetch column, reason: ' + MyTable.Exception);
      end;
      UncacheRow(MyTable, Index);
    end else
    begin
      WriteLn('Could not cache row, reason: ' + MyTable.Exception);
    end;
  end else
  begin
    WriteLn('Unable to create a new table, reason: ' + MyTable.Exception);
  end;
end;


function CIncColumn(var Table: TMySSQL_Table; const Row, Column: word; const Increase: extended): boolean;

Very fast function to increase a column representing a numeric value, including float values.
Returns true, if the column has been increased or rather decreased if Increase is negative.

Code: (pascal) [Select]

var
  MyTable: TMySSQL_Table;
  Index: word;

procedure ActivateServer();
begin
  if (CreateTable(MyTable, 'math')) then
  begin
    Index:= CreateRow(MyTable, ['20', 12, '155']);
    if (CacheRow(MyTable, Index)) then
    begin
      CIncColumn(MyTable, Index, 2, 5);
      if (CFetchColumn(MyTable, Index, 2)) then
      begin
        WriteLn('New value is: ' + CFetchColumn(MyTable, Index, 2)); // Output: "New value is: 160"
      end else
      begin
        WriteLn('Failed to fetch column, reason: ' + MyTable.Exception);
      end;
      UncacheRow(MyTable, Index);
    end else
    begin
      WriteLn('Could not cache row, reason: ' + MyTable.Exception);
    end;
  end;
end;

Actually it is not necessary to check whether a MySSQL function/procedure call was successfully, but I implemented it to show you how you can "catch" possible exceptions. Most time calls to functions or rather procedures will succeed, unless you handle them wrong and pass on an invalid row index to a function for example.

[iDante]

EnEsCe recently said in his blog that he wanted to include MySQL into the next scriptcore if at all possible. If that doesn't work out then this is almost as good

Nice work!

[scarface09]

Cool script. Yes EnEsCe did say that in his blog. Will this be good enough don't you think? Although this would be a good other choice if he doesn't work out making what he wants.

[y0uRd34th]

Good work Curry!
It was time that someone releases sth. like this. 
Look there:

Code: [Select]

function _RowExists(Row: integer): boolean;
begin
   if (ArrayHigh(database) >= Row) then
   begin
     result:= true;
   end;
end;If you have an function that returns an Boolean, the Result normally is set to true.
And if you have only one Result that returns true, the Result would be
always true or not? Tell me if I'm wrong.
(Hope you check my English explaining skill..)

[JFK]

Excellent script. Seems very efficient.
I think in some cases it might be better to spread large databases over multiple files. But having too much files in a directory can also be a bad thing.

As for the boolean: pretty sure it's default state is false.
Putting a small extra line for clearity would be good practice imo :p

[iDante]

Boolean is set to false, numbers are 0.0, arrays have 0 elements (unless otherwise specified).
Pascal is nice that way

[DorkeyDear]

I prefer having a Result := false; just in case, but I would probably do this to keep it shorter:

Code: [Select]

function _RowExists(Row: integer): boolean;
begin
  result := ArrayHigh(database) >= Row;
end;But what is there should be fine.

[CurryWurst]

Script updated...

- Fixed a very stupid bug where it was not possible to create a new row
- Modified _RowExists according to DorkeyDear's idea
- Renamed script to MySSQL (My Soldat SQL)

I highly recommend to redownload the script because CreateRow should
work now.

[CurryWurst]

Here's the second update if someone is interested in the script...

Code: [Select]

const
  _FOLDER_DATABASE = '';
  _NAME_DATABASE = 'database.txt';

var
  database: array of string;

function IXSplit(const SOURCE: string; Delimiter: string): array of string;
var
  i, x, d: integer;
  s, b: string;
begin
  d:= length(Delimiter);
  i:= 1;
  SetArrayLength(Result, 0);
  while (i <= length(SOURCE)) do
  begin
    s:= Copy(SOURCE, i, d);
    if (s = Delimiter) then
    begin
      SetArrayLength(Result, x + 1);
      Result[x]:= b;
      Inc(i, d);
      Inc(x, 1);
      b:= '';
    end else
    begin       
      b:= b + Copy(s, 1, 1);
      Inc(i, 1);
    end;
  end;
  if (b <> '') then
  begin
    SetArrayLength(Result, x + 1);
    Result[x]:= b;
  end;
end;

function ReadFromFile(File: string): string;
begin
  Result:= ReadFile(File);
  Result:= Copy(Result, 0, length(Result) - 2);
end;

function DoesFileExist(Name: string): boolean;
begin
  if (GetSystem() = 'windows') then
  begin
    if (FileExists(Name)) then
    begin
      result:= true;
    end;
  end else
  begin
    if ((FileExists(Name)) or (ReadFromFile(Name) <> '')) then
    begin
      result:= true;
    end;
  end;
end;

procedure _LoadDatabase();
begin
  if (DoesFileExist(_FOLDER_DATABASE + _NAME_DATABASE)) then
  begin
    database:= IXSplit(ReadFromFile(_FOLDER_DATABASE + _NAME_DATABASE), #13#10);
  end else
  begin
    WriteFile(_FOLDER_DATABASE + _NAME_DATABASE, '');
  end;
end;
 
procedure _SaveDatabase();
var
  i: integer;
  b: string;
begin
  for i:= 0 to GetArrayLength(database) - 1 do
  begin
    if (Trim(database[i]) <> '') then
    begin
      if (b <> '') then
      begin
        b:= b + #13#10 + database[i];
      end else
      begin
        b:= database[i];
      end;
    end;
  end;
  SetArrayLength(database, 0);
  if (b <> '') then
  begin
    database:= IXSplit(b, #13#10);
  end;
  WriteFile(_FOLDER_DATABASE + _NAME_DATABASE, b);
end;

function _RowExists(Row: integer): boolean;
begin
  result:= ArrayHigh(database) >= Row;
end;

function _getColumnInfo(Row, Column: integer): integer;
var
  ch, x, tabs: integer;
  b: string;
begin
  tabs:= -1;
  b:= database[Row];
  while (tabs <> Column) do
  begin
    x:= StrPos(#9, b);
    if ((x = 0) and (tabs <> Column)) then
    begin
      exit;
    end;
    Inc(tabs, 1);
    if (tabs = Column) then
    begin
      result:= ch + 1;
      break;
    end else
    begin
      ch:= ch + x;
      Delete(b, 1, x);
    end;
  end;
end;

function GetTypeOF(Value: variant): string;
begin
  case VarType(Value) of
    3  : result:= IntToStr(Value);
    5  : result:= FloatToStr(Value);
    11 : result:= iif(Value, 'true', 'false');
    256: result:= Value;
    else result:= 'unknown Type';
  end;
end;

procedure _CreateRow(Columns: array of variant);
var
  i, x: integer;
begin
  SetArrayLength(database, GetArrayLength(database) + 1);
  x:= GetArrayLength(database) - 1;
  for i:= 0 to GetArrayLength(Columns) - 1 do
  begin
    database[x]:= database[x] + GetTypeOF(Columns[i]) + #9;
  end;
  _SaveDatabase();
end;

function _DeleteRow(Row: integer): boolean;
begin
  if (_RowExists(Row)) then
  begin
    database[Row]:= '';
    _SaveDatabase();
    result:= true;
  end;
end;

function _UpdateColumn(Row, Column, Increase: integer): string;
var
  pos: integer;
  data: string;
begin
  if (_RowExists(Row)) then
  begin
    pos:= _getColumnInfo(Row, Column);
    if (pos > 0) then
    begin
      data:= GetPiece(database[Row], #9, Column);
      if (RegExpMatch('^-?\d+$', data)) then
      begin
        result:= IntToStr(StrToInt(data) + Increase);
        Delete(database[Row], pos, length(data));
        Insert(result, database[Row], pos);
      end;
    end;
  end;
end;

function _SetColumn(Row, Column: integer; Value: variant): boolean;
var
  pos: integer;
  data: string;
begin
  if (_RowExists(Row)) then
  begin
    pos:= _getColumnInfo(Row, Column);
    if (pos > 0) then
    begin
      data:= GetPiece(database[Row], #9, Column);
      Delete(database[Row], pos, length(data));
      Insert(GetTypeOF(Value), database[Row], pos);
      result:= true;
    end;
  end;
end;

function _AppendColumn(Row: integer; Value: string): boolean;
begin
  if (_RowExists(Row)) then
  begin
    database[Row]:= database[Row] + Value + #9;
    result:= true;
  end;
end;

procedure ActivateServer();
begin
  _LoadDatabase();
end;

I'll upload the update to soldatcentral later.

The update makes life much more comfortable when you're dealing with databases.

To create a new row with columns or to set an existing column you don't have to convert all variables into strings anymore.
Pass on every type (integers/strings/floating-point number/booleans) you want without using strings.
The delimiter "#9" is also not required anymore.

The input is now an array of variant, therefore you may do things like this:

_CreateRow(['string', 12, true, -22.45]);
_SetColumn(0, 1, 12.0);

The first command above will result into ...

Code: [Select]

string 12 true -22.45
in your database file.

I also added a function to check if a file exists.

Have fun creating your own databases

[CurryWurst]

According to the new LogInSystem update I created an update for MySSQL.
It speeds up the performance of creating and deleting rows as well as saving the complete database significant.
I highlighted the specific lines in the code for the new method.

Here's the changelog for the second update:

Code: [Select]

- Implemented a new method to save and delete rows (SwapSQLRowID()), which is much faster than using XSplit
  Hence it avoids frequent crashes caused by calling XSplit too much
  Note: Every time a row is deleted you have to refresh your "row pointers" because the new method swaps to RowIDs

- Renamed _SaveDatabase() to _SnapDatabase() and cleaned up the code a bit
- Renamed Row to RowID for every function which expects this parameter to be passed on

EDIT: I made a third update to MySSQL:

Code: [Select]

- added OnErrorOccur() function which holds the last error occured in the new variable Exception
  In case a MySSQL function such as _Update-, _Set-, _Append-, _DeleteColumn wasn't successful you can output the last
  exception with WriteLn()
  Example:
  if (not(_SetColumn(RowID, ColumnID, Value))) then
  begin
    WriteLn(Exception);
  end;
- _UpdateColumn() supports now float values and returns a boolean instead of a string
- _AppendColumn() supports now variants as value


(Size 1.64 KB)
- http://soldatcentral.com/index.php?page=script&f=109 -

** Script hosted by Soldat Central! Please visit the author's script page and Rate this script **

[Zabijaka]

I have stupid question - if I make another script (ex. stats), it's possible to another script use functions from this script without itegration these two scripts ??

[CurryWurst]

Yes it is if you use CrossFunc to separate MySSQL from your script. I haven't written all of these CrossFunc functions yet, but they will be included in the next update which will be out soon. If you don't want to wait, you may write these functions on your own.

Here are some example codes, written a few days ago by me, how to separate the script. Not all of them may be correct, because they already contain code from the upcomming update.

Code: (Pascal) [Select]

const
  MySSQL   = 'MySSQL';

procedure Initialize();
begin
  CrossFunc([], MySSQL + '._Initialize');
end;

procedure SelectDatabase(const NAME_DATABASE: string);
begin
  CrossFunc([NAME_DATABASE], MySSQL + '._SelectDatabase');
end;

procedure SnapDatabase();
begin
  CrossFunc([], MySSQL + '._SnapDatabase');
end;

function GetColumnItem(RowID, ColumnID: integer): string;
begin
  Result:= CrossFunc([RowID, ColumnID], MySSQL + '._GetColumnItem');
end;

function CreateRow(ColumnItems: array of variant): integer;
begin
 Result:= CrossFunc([ColumnItems], MySSQL + '._CreateRow');
end;

function DeleteRow(RowID: integer): boolean;
begin
  Result:= CrossFunc([RowID], MySSQL + '._DeleteRow');
end;

function UpdateColumn(RowID, ColumnID: integer; Increase: extended): boolean;
begin
  Result:= CrossFunc([RowID, ColumnID, Increase], MySSQL + '._UpdateColumn');
end;

function SetColumn(RowID, ColumnID: integer; Value: variant): boolean;
begin
  Result:= CrossFunc([RowID, ColumnID, Value], MySSQL + '._SetColumn');
end;

function AppendColumn(RowID: integer; Value: variant): boolean;
begin
  Result:= CrossFunc([RowID, Value], MySSQL + '._AppendColumn');
end;
[/code ]
They have to be implemented in your script to allow the use of MySSQL.

[Quantifier]

Quick question, where exactly is SQL here (apart from the name) ?

[jrgp]

Quick question, where exactly is SQL here (apart from the name) ?

No where.

[Quantifier]

Then it shouldn't be in the name.

[jrgp]

Then it shouldn't be in the name.

Correct, as he just took the name to be cool, which is annoying and misleading as hell since the code and usage bares absolutely no resemblance to Structured Query Language whatsoever.

Fortunately, in one of the next server releases eC has promised to support SQLITE support, which will render this abomination deprecated.

[DorkeyDear]

Fortunately, in one of the next server releases eC has promised to support SQLITE support, which will render this abomination deprecated.

Not really. Those who don't have any SQL database to connect to can still use this as an alternative. This may also be used for those using older versions of Soldat Dedicated Server as well.

[jrgp]

Fortunately, in one of the next server releases eC has promised to support SQLITE support, which will render this abomination deprecated.

Not really. Those who don't have any SQL database to connect to can still use this as an alternative. This may also be used for those using older versions of Soldat Dedicated Server as well.

Not really. SQLITE doesn't use a database "to connect to." With it, the "database" is all in one local file, which is accessed.

[DorkeyDear]

Not really. SQLITE doesn't use a database "to connect to." With it, the "database" is all in one local file, which is accessed.

It creates/hosts a database?

[jrgp]

Not really. SQLITE doesn't use a database "to connect to." With it, the "database" is all in one local file, which is accessed.

It creates/hosts a database?

The file _is_ the database.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

[DorkeyDear]

oohh! thats stupid.. so no other apps can access the same data?

[jrgp]

oohh! thats stupid.. so no other apps can access the same data?

Umm. the database file is a normal .sqlite one. Any program that uses SQLITE can open it up and "connect" to the database and read/write to it. At the same time, too.

[DorkeyDear]

oohh! thats stupid.. so no other apps can access the same data?

Umm. the database file is a normal .sqlite one. Any program that uses SQLITE can open it up and "connect" to the database and read/write to it. At the same time, too.

Ah, okay; ty for clearing that up
(sry about the slightly-unrelated convo on this topic)

[CurryWurst]

I was bored the last couple of days, therefore I wrote MySSQL 2.0. The script is kind of object-oriented and it's goal is to make database handling easier than before as well as increasing read/write performance on databases.
 
Now you have an object or rather type of TMySSQL_Table and you can pass on this object on a bunch of functions to stream data out of the table (= database), the object is linked against, or into it. The couple of functions you may use for streaming data can be categorized into three different types...

The first type are functions to create/load/unload/save basic tables. They are responsible for the table integrity and that all data is stored/loaded to the proper TMySSQL_Table object. They also care about the table's structure. So if you want to create a table called "accounts", you simply define a global variable as TMySSQL_Table type and pass this var on CreateTable as well as "accounts" as table name. After calling CreateTable, your global variable is now linked against a table file which ends on ".myssql" and starts with your table name you defined when you called CreateTable, if everything went well. You can now stream data into the table. Whenever you think it's necessary to save important data, so it doesn't get lost in case the soldat server crashes, you can call SaveTable with your TMySSQL_Table object and all data will be stored in the table file. When you're done a with a table and you don't need to access it anymore, call UnloadTable with your table object and all the temporary data will be saved to the specific file and your object will be unlinked from the table file. The object can be now reused to load/create other tables. Next time you start up your server you may want to access your previous created table again. No problem. Just call LoadTable with an TMySSQL_Table object and specify the name of the table you want to load. Remember not to put ".myssql" to the end of your name. It will be added by the function.

Okay, you have just learned how to use basic table functions, but I doubt you don't want to store data into your table. Time for the second type of functions

The second type of functions we will be concerned about, includes functions to create/destroy rows and additionally to read (select) and set columns and last but not least to append a column to a row. But before I start to explain the functions in particular, I'll first show you how a table is structured in general.
A table consists of a plain ".myssql" file which is actually a normal ".txt" file. This file comprises data rows which are a collection of records. Each row starts with a new line, so rows are delimited by a new line char. The index of your first row is always "0". Within these rows there are columns, they are your actual records. Columns are separated by tabulators to avoid char conflicts and the index of your first column within a row is also always "0". Before you get bored of reading this text, have a look at the following excerpt to understand what I try to explain (the following illustration might look stupid to some advanced scripters, but I think it can help beginners to understand the circumstance) :

Code: [Select]

             - Tab -               - Tab -
MyFirstColumn        MySecondColumn        MyLastColumnOfMyFirstRow        // First row, index = 0
MyFirstColumInMySecondRow        MySecondColumn        MyThirdColumn        // Second row, index = 1

As you can see above, that's how table file may look alike, except for "-Tab-" and the comments on the right of the rows. They won't be included in your file. You can also edit table files manually by opening them with an appropriated editor such as notepad for example. But be careful when you append columns to an existing row. At the end of the last column you're supposed to add a tabulator, otherwise you will run in trouble when loading the table.

That's it for the general table structure. The second type of functions is waiting to be explained

The first type of functions dealt with the basic table handling, it's now about time to fill our table with rows and columns. In order to create columns which store your data, you first need to have a few rows. Rows can be created with CreateRow. CreateRow expects a TMySSQL_Table object to be passed on, and also an array of columns you want to be saved within your row. Assume you want to create a script which stores every player name along with their total distance they moved on your server. So you need to save the player's name as well as the distance he moved within one row. Call CreateRow with these parameters like this "CreateRow(MyTableObject, [PlayerNameGoesHere, DistanceHere]);". It will return the new index of the new row you just created. You may save this index for next time you want to modify the data such as Distance. Of course the player will continue to "jump" around in your server, therefore we need to update his/her record. You can update existing rows with their record if you call SetColumn with a table object, the row you want to alter and also the column where the saved data remains you want to update. According to our previous example we can now update the distance a player moved as follows: "SetColumn(MyTableObject, RowIndexYouSavedFromCallingCreateRow, 1, NewDistance);". Note that "1" represents the index of the column you want to update. In our example it's "1", because we also save the player name which goes into the first column ("0").
Hmmm ... distance a player moved seems to be boring alone. Why don't we save the total time he has needed to move around? We already have a row, where we saved the name and the distance of the player. We can now extend this row with an additional column which saves the time the player has needed to move around. Simply call AppendColumn with table object, the row you want to update and the value your new column should contain. I won't make an example here, because the syntax is almost similar to SetColumn.
If you want to get rid of rows and their collection of records, you can call DestroyRow with the necessary parameters.
Sometimes you may want to display records to your players. I implemented a function to get the records. SelectColumn reads data from a specific column of a row and returns it as TMySSQL_Column object. Actually I created this function for other functions to be used, but it can also be "abused" to read records and return them. If you want to get records with it, define a TMySSQL_Column object before and pass it onto the function:

Code: [Select]

var
 MyColumnObject: TMySSQL_Column;
begin
  SelectColumn(MyTableObject, 0, 1, MyColumnObject);
end;

MyColumnObject contains now the value of the column you selected, if everything went right. You access this value with "MyColumnObject.Value". Don't be afraid about "MyColumnObject.Offset". It's variable to be used by other functions. Note that this function is compared to CSelectColumn, which will be explained later, very slow. So only use this function if it's really required.

Okay. Now let's move on to the last type of functions, which are actually the most interesting of all.

You will deal with the last couple of functions the most time, because they are very efficient in performance and handling. CSelectColumn, CSetColumn, CIncColumn are all prefixed with a "C" because they are used in a particular way. Before you can access any of these functions, you have to call CacheRow, to cache the row with its columns into the RAM. This makes them accessible like a normal variable, but due to handling reasons I disguised with the previous named functions. CSetColumn works similar to SetColumn and has the same parameters. Use this function to set a column to any value you want to. CSelectColumn is the pendant of SelectColumn, but as stated before, it's much faster and returns only string which is the actual record of the column you selected. Hence, you don't have to define a TMySSQL_Column before you read a record. To increase a value by another value, you can call CIncColumn. But this assumes the column you want to be increased to be a numeric value. The function won't work on non-numeric columns and will fail in this case. If you don't need to access a row anymore, uncache it from the memory with UncacheRow and it will be removed from the memory immediately.
You should not cache rows you only need to access once. Prefer to use SelectColumn in this case despite it is slower than CSelectColumn. But caching a row is slow if you want to access a row once, compared to SelectColumn, because you also need to uncache it. If a row is accessed frequently, you should definitely cache it, because in the long run it's faster than SelectColumn.

Thanks for spending your time. If you have any questions, feel free to post them here. And do not forget to put the folder "tables" into your soldat server's root directory. Unfortunately a CrossFunc implementation has not been added to make the script portable and to minimize additional code.

P.S: This script does not contain any MySQL elements. I only adopted the name of it, but i don't think that's a problem.

[y0uRd34th]

wow.. looks like you were very bored as good as any other of your works

[CurryWurst]

MySSQL 2.1

Code: [Select]

const
  MySSQL_PATH = './tables/';

type
  TMySSQL_Column = record
    Offset: word;
    Value: string;
  end;

  TMySSQL_Row = record
    Columns: array of TMySSQL_Column;
    Data: string;
    Cached: boolean;
  end;
 
  TMySSQL_Table = record
    Rows: array of TMySSQL_Row;
    Name, Exception: string;
    Modified, Linked: boolean;
  end;

function GetTypeOF(Value: variant): string;
begin
  case VarType(Value) of
    3  : Result:= IntToStr(Value);
    5  : Result:= FloatToStr(Value);
    11 : Result:= iif(Value, 'true', 'false');
    256: Result:= Value;
  end;
end;

function AryVntToAryStr(const AryVnt: array of variant): array of string;
var
  i: integer;
begin
  Result:= [];
  SetArrayLength(Result, GetArrayLength(AryVnt));
  for i:= 0 to GetArrayLength(Result) - 1 do
  begin
    Result[i]:= AryVnt[i];
  end;
end;

function Implode(const Source: array of string; const Delimiter: string): string;
var
  i: integer;
begin
  Result:= '';
  for i:= 0 to GetArrayLength(Source) - 1 do
  begin
    Result:= Result + Source[i] + Delimiter;
  end;
end;

function ExplodeRow(Source: string; const Delimiter: string): array of TMySSQL_Row;
var
  x, y, l: integer;
  b: word;
begin
  l:= length(Delimiter);
  b:= length(Source);
  Source:= Source + Delimiter;
  Result:= [];
  repeat
    x:= Pos(Delimiter, Source);
    SetArrayLength(Result, y + 1);
    Result[y].Data:= Copy(Source, 1, x - 1);
    y:= y + 1;
    Delete(Source, 1, x + l - 1);
  until (x = 0);
  SetArrayLength(Result, iif(b = 0, y - 2,  y - 1));
end;

function ReadFromFile(const Path, Filename: string): string;
begin
  Result:= ReadFile(Path + Filename);
  Result:= Copy(Result, 0, length(Result) - 2);
end;

function RowExists(const Table: TMySSQL_Table; const Row: word): boolean;
begin
  Result:= GetArrayLength(Table.Rows) - 1 >= Row;
end;

function ColumnCached(var Table: TMySSQL_Table; const Row, Column: word): boolean;
begin
  if (RowExists(Table, Row)) then
  begin
    Result:= GetArrayLength(Table.Rows[Row].Columns) - 1 >= Column;
  end;
end;

procedure OnErrorOccur(var Table: TMySSQL_Table; const Method, Message: string);
begin
  Table.Exception:= ' [*] [Error] MySSQL -> (' + Method + '): ' + Message;
end;

function DumpCacheRow(var Table: TMySSQL_Table; const Row: word): boolean;
var
  Cache: string;
  i: integer;
begin
  if (not(RowExists(Table, Row))) then
  begin
    exit;
  end;
  if (Table.Rows[Row].Cached) then
  begin
    Cache:= '';
    for i:= 0 to GetArrayLength(Table.Rows[Row].Columns) - 1 do
    begin
      Cache:= Cache + Table.Rows[Row].Columns[i].Value + #9 ;
    end;
    Table.Rows[Row].Data:= Cache;
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'DumpCacheRow', 'Row (' + IntToStr(Row) + ') does not exist');
  end;
end;

procedure DumpCacheTable(var Table: TMySSQL_Table);
var
  i: integer;
begin
  for i:= 0 to GetArrayLength(Table.Rows) - 1 do
  begin
    DumpCacheRow(Table, i);
  end;
end;

procedure UncacheRow(var Table: TMySSQL_Table; const Row: word);
begin
  if (DumpCacheRow(Table, Row)) then
  begin
    Table.Rows[Row].Columns:= [];
    Table.Rows[Row].Cached:= false;
  end;
end;

function SaveTable(var Table: TMySSQL_Table): boolean;
var
  Rows: string;
  i: integer;
begin
  if (Table.Linked) then
  begin
    Rows:= '';
    DumpCacheTable(Table);
    for i:= 0 to GetArrayLength(Table.Rows) - 1 do
    begin
      Rows:= Rows + #13#10 + Table.Rows[i].Data;
    end;
    Delete(Rows, 1, 2);
    WriteFile(MySSQL_PATH + Table.Name + '.myssql', Rows);
    Table.Modified:= false;
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'SaveTable', 'Table is not linked');
  end;
end;

function LoadTable(var Table: TMySSQL_Table; const Tablename: string): boolean;
begin
  if (FileExists(MySSQL_PATH + Tablename + '.myssql')) then
  begin
    Table.Rows:= ExplodeRow(ReadFromFile(MySSQL_PATH, Tablename + '.myssql'), #13#10);
    Table.Name:= Tablename;
    Table.Linked:= true;
    Table.Modified:= false;
    Table.Exception:= '';
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'LoadTable', 'Unable to load table "' + Tablename + '"');
  end;
end;

function UnloadTable(var Table: TMySSQL_Table): boolean;
begin
  if (Table.Linked) then
  begin
    if (Table.Modified) then
    begin
      SaveTable(Table);
    end;
    Table.Rows:= [];
    Table.Linked:= false;
    Table.Name:= '';
    Table.Exception:= '';
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'UnloadTable', 'Table is not linked');
  end;
end;

function CreateTable(var Table: TMySSQL_Table; const Tablename: string): boolean;
begin
  WriteFile(MySSQL_PATH + Tablename + '.myssql', '');
  if (LoadTable(Table, Tablename)) then
  begin
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'CreateTable', 'Unable to create table "' + Tablename + '"');
  end;
end;

procedure DestroyTable(var Table: TMySSQL_Table);
begin
  Table.Rows:= [];
  Table.Linked:= false;
  Table.Name:= '';
  Table.Exception:= '';
end;

function CreateRow(var Table: TMySSQL_Table; const Columns: array of variant): word;
begin
  Result:= GetArrayLength(Table.Rows);
  SetArrayLength(Table.Rows, Result + 1);
  Table.Rows[Result].Data:= Implode(AryVnttoAryStr(Columns), #9);
  Table.Modified:= true;
end;

function DestroyRow(var Table: TMySSQL_Table; const Row: word): boolean;
var
  HIndex: integer;
begin
  if (RowExists(Table, Row)) then
  begin
    HIndex:= GetArrayLength(Table.Rows) - 1;
    if (HIndex <> Row) then
    begin
      Table.Rows[Row]:= Table.Rows[HIndex];
    end;
    SetArrayLength(Table.Rows, iif(HIndex > 0, HIndex, 0));
    Table.Modified:= true;
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'DestroyRow', 'Row (' + IntToStr(Row) + ') does not exist');
  end;
end;

function FetchColumn(var Table: TMySSQL_Table; const Row, Column: word; var DummyC: TMySSQL_Column): boolean;
var
  Source: string;
  TPos: integer;
  x, c: word;
begin
  if (not(RowExists(Table, Row))) then
  begin
    exit;
  end;
  Source:= Table.Rows[Row].Data;
  c:= 1;
  for x:= 0 to Column do
  begin
    Delete(Source, 1, TPos);
    c:= c + TPos;
    TPos:= Pos(#9, Source);
  end;
  if (TPos > 0) then
  begin
    DummyC.Value:= Copy(Source, 1, Pos(#9, Source) - 1);
    DummyC.Offset:= c;
    Result:= true;
  end;
end;

function FetchRowByColumn(var Table: TMySSQL_Table; const Column: word; const Value: variant): integer;
var
  C: TMySSQL_Column;
  i: integer;
begin
  Result:= -1;
  for i:= 0 to GetArrayLength(Table.Rows) - 1 do
  begin
    if ((FetchColumn(Table, i, Column, C) = true) and (C.Value = Value)) then
    begin
      Result:= i;
      break;
    end;
  end;
end;

function CacheRow(var Table: TMySSQL_Table; const Row: word): boolean;
var
  C: TMySSQL_Column;
  i, x: word;
begin
  if (RowExists(Table, Row)) then
  begin
    if (not(Table.Rows[Row].Cached)) then
    begin
      while (FetchColumn(Table, Row, i, C) = true) do
      begin
        SetArrayLength(Table.Rows[Row].Columns, x + 1);
        Table.Rows[Row].Columns[x]:= C;
        x:= x + 1;
        i:= i + 1;
      end;
      Table.Rows[Row].Cached:= true;
      Result:= true;
    end;
  end else
  begin
    OnErrorOccur(Table, 'CacheRow', 'Row (' + IntToStr(Row) + ') does not exist');
  end;
end;

function SetColumn(var Table: TMySSQL_Table; const Row, Column: word; const Value: variant): boolean;
var
  C: TMySSQL_Column;
  Cached: boolean;
begin
  if (RowExists(Table, Row)) then
  begin
    Cached:= Table.Rows[Row].Cached;
    UnCacheRow(Table, Row);
    if (FetchColumn(Table, Row, Column, C)) then
    begin
      Delete(Table.Rows[Row].Data, C.Offset, Length(C.Value));
      Insert(GetTypeOF(Value), Table.Rows[Row].Data, C.Offset);
      Table.Modified:= true;
      Result:= true;
    end else
    begin
      OnErrorOccur(Table, 'SetColumn', 'Out of row range (' + IntToStr(Row) + '/' + IntToStr(Column) + ')');
    end;
    if (Cached) then
    begin
      CacheRow(Table, Row);
    end;
  end else
  begin
    OnErrorOccur(Table, 'SetColumn', 'Row (' + IntToStr(Row) + ') does not exist');
  end;
end;

function CFetchColumn(var Table: TMySSQL_Table; const Row, Column: word): string;
begin
  if (ColumnCached(Table, Row, Column)) then
  begin
    Result:= Table.Rows[Row].Columns[Column].Value;
  end else
  begin
    OnErrorOccur(Table, 'CFetchColumn', 'Column (' + IntToStr(Column) + ') is not cached');
  end;
end;

function CSetColumn(var Table: TMySSQL_Table; const Row, Column: word; const Value: variant): boolean;
begin
  if (ColumnCached(Table, Row, Column)) then
  begin
    Table.Rows[Row].Columns[Column].Value:= GetTypeOF(Value);
    Table.Modified:= true;
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'CSetColumn', 'Column (' + IntToStr(Column) + ') is not cached');
  end;
end;

function CIncColumn(var Table: TMySSQL_Table; const Row, Column: word; const Increase: extended): boolean;
var
  Value: string;
begin
  if (ColumnCached(Table, Row, Column)) then
  begin
    Value:= CFetchColumn(Table, Row, Column);
    if (RegExpMatch('^-?(\d+|\d+.?\d+)$', Value)) then
    begin
      Table.Rows[Row].Columns[Column].Value:= FloatToStr(StrToFloat(Value) + Increase);
      Table.Modified:= true;
      Result:= true;
    end else
    begin
      OnErrorOccur(Table, 'CIncColumn', 'Column (' + IntToStr(Column) + ') represents no numeric value');
    end;
  end else
  begin
    OnErrorOccur(Table, 'CIncColumn', 'Column (' + IntToStr(Column) + ') is not cached');
  end;
end;

function AppendColumn(var Table: TMySSQL_Table; const Row: word; const Value: variant): boolean;
var
  Cached: boolean;
begin
  if (RowExists(Table, Row)) then
  begin
    Cached:= Table.Rows[Row].Cached;
    UncacheRow(Table, Row);
    Table.Rows[Row].Data:= Table.Rows[Row].Data + GetTypeOF(Value) + #9;
    if (Cached) then
    begin
      CacheRow(Table, Row);
    end;
    Table.Modified:= true;
    Result:= true;
  end else
  begin
    OnErrorOccur(Table, 'AppendColumn', 'Row (' + IntToStr(Row) + ') does not exist');
  end;
end;

Haven't got time to write the changelog yet - 1 bugfix as far as i can remember and the rest are new features/optimizations.
I'm probably going to post it tomorrow.

[dominikkk26]

I hope you enjoy this script!   

[CurryWurst]

Here we go...

Code: [Select]

- Added a global variable Cached for each row to save whether the row is cached or not
- Slightly improved the Implode function
- Renamed DumpCache to DumpCacheRow and applied minor code improvements
  Also added a new error message in case the row which ought to be dumped does not exit
- Added DumpCacheTable which dumbs all cached rows to the first table layer
- Modified UncacheRow to correspond with DumpCacheRow
- Modified SaveTable to make it compatible with DumpCacheRow
- Added a new procedure DestroyTable which "destroys" a MySSQL_Table object
- Fixed DestroyRow, so it does not delete a wrong row
- Renamed SelectColumn to FetchColumn
- Renamed SelectRowByColumnValue to FetchColumnByRow
- SetColumn is now synchronized with cached rows "synchronous 2 layer principle"
- CacheRow will not longer recache a row if the row has already been cached
- Renamed CSelectColumn to CFetchColumn
- AppendColumn is now synchronized with cached rows
Synchronous 2 layer principle simply means your table exists out of two layers, whereas the first layer represents the lowest instance before data is written to a file and the second layer is the cache of all rows, which first has to be dumped to the first layer before it can be saved. For now, only SetColumn and AppendColumn work according to this principle, so if you modify a cached column with SetColumn, the change will immediately take effect to the cached column as well as to the first layer. Columns you added with AppendColumn will also be available as cached column in case the row you modified has been cached.

[Zabijaka]

Maybe add new readme file for noobs xD ?

[iDante]

Add in simple indexing if you haven't already and this will be amazing.

[CurryWurst]

I moved the code examples and descriptions to the very first post to keep this thread clean.

[Iq Unlimited]

Thanks a million.