UTimeM - UTime for MySQL

[ACProdigy]

Exactly what it sounds like: UTime rebuilt for MySQL integration. You must have your own MySQL server for this, obviously. UTimeM will create a table in the provided database if it does not already exist.

Requires:
ULib
gm_mysql module

Revision History
-[Version 4 Core 1.3 - 01/07/10]
     [FIXED] Nicknames with special characters not getting updated/created.

-[Version 3 Core 1.3 - 01/03/10]
     [ADDED] Several extra variables, useful for community inter-linking. New stored variables include Nick, Team, and SteamID.

-[Version 2 Core 1.3 - 07/04/09]
     [INITIAL RELEASE] Stays true to the original UTime, stores only ID, UID, Total Time, and Last Visit. As MySQL.

Attribution
The core of this code is licensed by Team Ulysses under the Creative Commons License, BY/NC/SA. I have modified it to make use of a different database system, and release it under the Creative Commons License, BY/NC/SA.

[Megiddo]

Good work! Especially useful with nick and steamid combo.

[JamminR]

The one that stores nicknames... won't create duplicates if different names are used, correct?
I wouldn't want a database to store 5 different records for the same uniqueid or steamid just because someone decided to change thier steam alias.

[ACProdigy]

The one that stores nicknames... won't create duplicates if different names are used, correct?

No, the table maintains its primary key based on the UID, and updates the nick with the total time.

Also, below is a real-time example of what can be done with PHP parsing that new shiny MySQL table. Bear in mind its the concept, my presentation isn't that pretty (I'm not a designer, after all). The image is being pulled from my webserver, which is hosting the DB that my development gameserver talks to. PHP extracts the information for the given user, populates the text fields, and then spits out an image.

[JamminR]

Nice. Good work.
Though I do not know MySQL or any related DB query related construct, I know many others will find this useful.
Thanks for the contribution, and I look forward to future releases.

<somewhat off topic>
If you ever wish to tackle making a ULib module for integrating UCL and MySQL, there was discussion and working code (from forum integration) here over almost two years ago. http://forums.ulyssesmod.net/index.php/topic,3241.0.html

At the time we had a dedicated Gmod server. Megiddo wrote code to allow ULib to give access in game depending on their access here using a custom profile field that contained thier Steam ID (STEAM_#:#...)

Like your example image... great!
Ha. I too am visual/graphical artisticly challenged. Probably why I married an graphic artist.
</off topic>

[Ploo]

Looks like something I might use instead of the original UTime. I'm gonna see if I can learn some lua by modifying it to have another table called 'servers' which holds all the servers by id, ip and name. Then each SteamID will have one entry for each server they visit. Total time will be the amount of time they spent on all servers but I will also be able to see how much they spent on individual servers.

Consider this a suggestion too. Don't think I'll succeed.

[NaRyan]

Nice version of Utime
I always had problems with the original Utime (after a few months the on-join crashes got realy bad, but deleting the sv.db file sorted it)

I do have one small problem though.
This version seems to have problems with some clan tags, and some unicode based names.
i.e my steam name is =ASY=Na'Ryan.

If I leave it set to that then it does not create/update the entry in the MySQL database.
When I change my name to NaRyan, it creates the info in the database just fine.
However if I change back to my clan tag name, it reads the info fine (total time), however it will not update the database.

Is there any way to have this version of Utime to ignore a players name, and just use their steamID?
Since player names can change 1001 times, but their steamID is always the same.

Thanks.

Edit.
Meh just realised it's because of the ' in my name.
Removing that fixes the problem.
No great loss removing that... Silly me should have realised that by looking at other player names in MySQL database...

[ACProdigy]

Looks like something I might use instead of the original UTime. I'm gonna see if I can learn some lua by modifying it to have another table called 'servers' which holds all the servers by id, ip and name. Then each SteamID will have one entry for each server they visit. Total time will be the amount of time they spent on all servers but I will also be able to see how much they spent on individual servers.

Consider this a suggestion too. Don't think I'll succeed.

There may be a better way to go about this. If you change the name of the table per server (i.e. "utime_s1", "utime_s2"), and keep it all in the same database "joes_utime" or some such, you could just use some more complicated MySQL queries to join them together when needed. Making a server table, in this case, seems a little out of the way. I'll look at implementing this (or just forking the development), if you're interested.

This version seems to have problems with some clan tags, and some unicode based names.


Edit.
Meh just realised it's because of the ' in my name.

Ah, I see. I'm glad you were able to resolve it, but it does show a mistake I made in the code. Thanks for pointing this out! I'll re-release it asap with a fix.
Edit: The above is fixed as of Version 4.

[Ploo]

There may be a better way to go about this. If you change the name of the table per server (i.e. "utime_s1", "utime_s2"), and keep it all in the same database "joes_utime" or some such, you could just use some more complicated MySQL queries to join them together when needed. Making a server table, in this case, seems a little out of the way. I'll look at implementing this (or just forking the development), if you're interested.

I still belive my method would be simpler. UTime checks if it has its own entry in the servers table (by simple checking if there's an entry with it's IP), if not, it enters an entry with its ip, hostname and an id (incase the server IP changes and you wanna keep the utime, this would obviously require the user to change the ip in the entry manually).

The utime table would contain entires with an additional column of server_id.

When counting the total UTime, simple query the utime table for a certain UID. Add up the results.

I think having utime_s1, utime_s2 wouldn't be a good option for several reasons. a) You would have to edit the luas for each server to tell it if its s1, s2 or whatever. b) You would have edit each lua to tell how many servers there are (and tables). c) If my concept executed correctly, it would require no involvment from the installer at all. Obviously this would all be in the same database.

I hope you understand.

[ACProdigy]

I think having utime_s1, utime_s2 wouldn't be a good option for several reasons. a) You would have to edit the luas for each server to tell it if its s1, s2 or whatever. b) You would have edit each lua to tell how many servers there are (and tables). c) If my concept executed correctly, it would require no involvment from the installer at all. Obviously this would all be in the same database.

I hope you understand.

I believe I understand now. I misunderstood your original comment. This would still require each separate server instance to have a unique identifier hard-coded into the lua configuration. Something like:

Code: [Select]

----- Database connection details -----

local dbhost = "0.0.0.0" -- Your MySQL IP / Hostname
local dbuser = "USER" -- MySQL Username
local dbpass = "PASSWORD" -- MySQL Password
local dbport = "3306" -- MySQL Server Port
local dbname = "DATABASE" -- Database name
local unique = "1" -- Unique gameserver identifier.

--=== DO NOT EDIT BELOW THIS POINT ====

Then the rest could be handled by whatever parses the rows, in order to add them up. That wouldn't be difficult at all really. I suggest a unique ID is manually specified in the header variables like so, as it would make it much easier to automatically update the IP/Servername later on.

[NaRyan]

I have been getting this error every so often.

Lua Error: ERROR: Hook 'UTimeInitialSpawn' Failed: autorun/sv_utime.lua:101: attempt to call global 'mysql_escape' (a nil value)
Lua Error: Removing Hook 'UTimeInitialSpawn'

It does it when a player connects.
It can be the 1st player to join, or the 10th.
The only common thing i have seen with the players it breaks on, is they have unicode names.

I am using V1.4 of UTimeM, and the release versions of ULib and ULX.

[ACProdigy]

I have been getting this error every so often.

It does it when a player connects.
It can be the 1st player to join, or the 10th.
The only common thing i have seen with the players it breaks on, is they have unicode names.

I am using V1.4 of UTimeM, and the release versions of ULib and ULX.

Is it *only* with players using unicode names?

[NaRyan]

Is it *only* with players using unicode names?

Not entirely sure.
The next time all the players have left server or it crashes, I'll add the "-condebug" launch option to scrds, so I get a better record of what player names it breaks on.

Edit.
Nope it just gave the error when the 1st person joined the server, and they did not have a unicode name.

[ACProdigy]

Interesting. Could you post some example names that it crashes on? I think I know what the problem is, but its nice to have an example or four.

[NaRyan]

Names that it has broke on have been

Code: [Select]

ThorN
^0COM. ^1SOAP ^4MACTAVISH
^2[S]carface^1[D^7K]
Defou_LCK
*had to put names in code tags, else it altered the message to have strikethrough font.

That's the 4 names I have seen it break on today.

Thorn was the last player to join (and have it break), he was also the 1st player to join the server, after it had the usual on-join crash.
He was on the server when it crashed, however I don't know if he had joined before or after that sessions UtimeM breakage.

This is what was in the console.log for him:

<blah blah blah server stuff>
[UTime] Table exists!
<blah blah blah more server stuff>
Client "ThorN" connected (xx.xx.xxx.xxx:27005).
[UTime] New Player Added: ThorN
ERROR: Hook 'UTimeInitialSpawn' Failed: autorun/sv_utime.lua:101: attempt to call global 'mysql_escape' (a nil value)
Removing Hook 'UTimeInitialSpawn'

Also just noticed it keeps re-setting my total time.
The SQL database had it at 7298 for my total time.
Yet when I joined it displayed my last time on server (01/09/10 04:44:28), however it showed my total time as 1 minute 12 seconds.
And the SQL database now shows my total time as 111.
Using the player name of =ASY=Na'Ryan.