07-02-05, Vjaka
- Added simple mySQL support. New settings MySqlHost, MySqlUser, MySqlPassword,
MySqlDatabase will be used to connect to the DB in case if MYSQL is set to 1.
You will need extra .dll/.so to download for sphere installation. They will
join the packages.
30-07-05, Vjaka
- Added: MySQL support to scripts. You can execute any queries and statements,
as well as get data from mysql link. A special script object similar to the
FILE or OBJ added and is named DB.
DB Properties:
CONNECTED - the current state of database connection (1 or 0)
ROW.NUMROWS - number of rows returned by last query
ROW.NUMCOLS - number of columns returned by last query
ROW.* - list of all columns returned by last query. Both column names and
numbers are saved in the list, so you can use indexes [0..] or column
names [id, name, etc] here.
DB Methods:
CLOSE - disconnects the SQL server
CONNECT - connects to the database (using mysql settings in sphere.ini)
EXECUTE - executes SQL query not filling any results anywhere
QUERY - executes SQL query filling ROW.* properties information
Example of a simple query:
DB.query "SELECT id FROM users WHERE account='<SRC.ACCOUNT.NAME>'"
SERV.log "Got ID = <DB.ROW.id>"
Note: Server does not create connection upon start, so if you wish to make
the connection with the server start, add DB.connect to the function
f_onserver_start.
Shinigami, its hard to code or you don`t see "for what it is"
(Sorry - bad English)
MySQL runs too slow to be linked to the core directly via commands. It would better to make an AUX system that connects to PHP/ASP/etc to do this work.
Now on my shard i use AUX scripts but this is whery slow and hard to code.
You need write on php|perl daemon. Daemon check new data on socket and if need send data to socket.
But its too slow.
If so slow - use other DB - postgree.PostgreeSQL - faster.
Well AUX isnt that slow. We use it for shard status + playerlist on a web page, and it takes a few milliseconds to send full details of about 100+ players. I'll agree that it's hard to code, but once you're used to it, it's quite simple really.
Just pack everything into a big array, transmit it, then use Muad's unpacker to unpack it in the web page.
I had suggested earlier that there be a new type of aux script that was for outgoing connections that would probably be easier to implement for the devs and would work for other types of databases. But, as everyone else is saying, it's completely unncessary if you just have an aux script and another script running on your mysql server that connects to the aux script (possibly done in perl) and waits for data from the aux script.
Would it be possible to implement a syshook for server loads? A syshook for loading NPC data, another for loading Item data, etc...? Within that syshook you could possibly make use of the outgoing connection feature, which would connect to some database and get the data. This seems like it would be easier than coding a bunch of specific core functions for MySQL and other databases that people whine about wanting. I'm just looking out for the devs. I'm assuming it would be easier to just make things syshookable and let the escripters do everything else.
You could write wrappers for different databases using escript if one could just make outgoing connections.
AUX scripts are useful for either ONLY sendings or ONLY reception. But work in both directions is complicated.
Example:
Economic model of the world.
The prices for the goods should correspond to popularity of the goods and dynamically change.
AUX a script I can contain statistics for the goods.
However to request cost of each goods through AUX is too complex.
I personally use XML, emitted by POL into a directory with a 'GUID' filename. Then I have a simple Queue system built into a script in POL (the purpose of Queueing is in case the AUX connection is not available, or for when new information comes faster than the DBMS can update it). The Queue system performs many other functions on my system, but one of the things it is used for is to let the .NET program which is connected to the AUX know of new XML files.
The program then just slurps up the data in the XML file and updates the appropriate SQL Server table(s).
Incidentally, I didn't even need to do the Queue part simply for doing these updates via XML; A .NET FileSystemWatcher object could have been used to simply watch the directory where the XML files are output by the POL script, and automatically load them. However, the Queue system allows me to be sure the POL script is actually DONE writing, before the .NET program starts trying to load the file.
CrazyMan wrote:AUX scripts are useful for either ONLY sendings or ONLY reception. But work in both directions is complicated.
I'm not sure where you would get that idea. The Queue system I described above has no trouble sending or receiving data from the .NET program connected to it, and the script to do so is very simple, really. You just have to know how the data stream works, I suppose.
And for that matter... even if you can't figure out how to do both in the same script... just make two separate AUX scripts. One for sending, one for receiving.
