Script Name: MySSQL
Script Description Database functions to manage your own database.
Author: CurryWurst (http://forums.soldat.pl/index.php?action=profile;u=10539)
Compile Test: (http://soldatcentral.com/images/pass.gif) 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).
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.
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.
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.
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.
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.
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.
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.
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.
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,
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...
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.
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.
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.
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.
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.
(http://reauq.com/misc/soldat/sf/buynow.gif) (http://reauq.com/misc/soldat/scripts/MySSQL+v2.1.zip)
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:
- 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:
- 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
(http://soldatcentral.com/images/download.gif) (http://soldatcentral.com/dl.php?id=109&act=1)
(Size 1.64 KB)
- http://soldatcentral.com/index.php?page=script&f=109 -
** Script hosted by Soldat Central (http://soldatcentral.com/index.php?page=script&f=109)! Please visit the author's script page (http://soldatcentral.com/index.php?page=script&f=109) and Rate this script **