MySSQL

[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.