simple MySQL support

CrazyMan · **Joined:** Wed Apr 05, 2006 8:08 am **Posts:** 54

Now i see Sphere emu changelog:

Code:

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)

MuadDib · **Posted:** Sat May 27, 2006 3:22 pm

Personally, I would not recommend this.

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.

CrazyMan · **Joined:** Wed Apr 05, 2006 8:08 am **Posts:** 54

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.

Aeros · **Joined:** Mon Apr 24, 2006 10:56 am **Posts:** 69

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.

Shinigami · **Posted:** Sun May 28, 2006 1:41 am

it is not so hard to implement... but using it for savegames (e.g.) doesn't make sence because of speed...

other tasks, hmm, maybe. for now we handle it via aux and php.

Shinigami

tekproxy · **Posted:** Sun May 28, 2006 4:02 pm

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.

CrazyMan · **Joined:** Wed Apr 05, 2006 8:08 am **Posts:** 54

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.

Marilla · **Posted:** Mon May 29, 2006 5:52 am

Just to toss some extra stuff out here;

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.

Marilla · **Posted:** Mon May 29, 2006 5:54 am

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.

CrazyMan · **Joined:** Wed Apr 05, 2006 8:08 am **Posts:** 54

or just to create log ( example = gm commands)
If it`s not hard to code - just do it.

( I dont say about saveworld in database - its realy to slow ( see wolfpack&MySQL), i say about escript module )

CrazyMan · **Joined:** Wed Apr 05, 2006 8:08 am **Posts:** 54

Shinigami:
to be or not to be?

CrazyMan · **Joined:** Wed Apr 05, 2006 8:08 am **Posts:** 54

Shini, don`t forget ....

PenUltima Online

simple MySQL support

Who is online