|
Notes |
|
|
|
To continue this discussion:
Quote
That would somehow encourage mods not to interfere with each others data, but still doesn't enforce it. Adding the extra column won't be a problem, but then all of the ACS DB functions get an additional argument. What's the general opinion on this? Since a mod could just write "zanrun-time" (one argument, i.e. namespace) instead of "zanrun", "time" (two argument, i.e. namespace and section), we don't really get much out of the more complicated interface except of this slight encouragement to use a unique namespace. Another possibility would be renaming the namespace argument in the ACS functions to section and to force selecting a namespace via a new lump.
I don't think there is a way to enforce it.
The idea of a lump might work, though then it introduces a lot of overhead to deal with (ex: conflicts, who gets what priority, when...etc).
If we miss something here and find out later down the road, it could be very painful.
We also have to take into account hosting services like Best-Ever where people might maliciously try to write into other people's DB's.
How does one stop this? |
|
|
|
|
> That would somehow encourage mods not to interfere with each others data, but still doesn't enforce it. Adding the extra column won't be a problem, but then all of the ACS DB functions get an additional argument. What's the general opinion on this? Since a mod could just write "zanrun-time" (one argument, i.e. namespace) instead of "zanrun", "time" (two argument, i.e. namespace and section), we don't really get much out of the more complicated interface except of this slight encouragement to use a unique namespace. Another possibility would be renaming the namespace argument in the ACS functions to section and to force selecting a namespace via a new lump.
See that's the thing - I don't necessarily want to ENFORCE separation between mods. If one mod wants to peek into another mod's namespace, I'd like to leave that option open. I just feel like the EASY thing that most mod makers will end up doing is assuming that they have exclusive access to the database all to themselves.
However, part of me also agrees with you about the additional parameters to each DB command that would require. Perhaps the best thing we can do is document the use of these functions and ensure that the documentation has the mod-specific namespace as part of the documentation. That way, people who "learn" by copypasting will get this extra namepsacing "for free".
> I think technically strings in ACS are something like a "handle" internally, but I'm not fond of the idea of introducing more handle types. As far as I can tell ACS is not designed for these kind of things. IMO the Prepare and GetPrepared functions I proposed are more in line with the limited scope of ACS.
What I was going to suggest is not necessarily a new type, but merely returning an integer that acted like any other number from the point of view of ACS, but could be passed to functions that would do the handle lookup at the time of the call.
I can see where you're coming from in terms of complexity, but I am still not a fan of your use of global state. Especially considering the number of different scopes available in ACS, I feel like this will end up footgunning ACS coders in unexpected ways when they try to use these API's in more than one scope at the same time. Are there any other instances in the ZDoom ACS API that global state is used...other than perhaps setting the currently in-use font?
If I had handles, I feel like I could...say...run a couple of queries on OPEN and then merely keep the handles around in order to easily refer back to data as-needed without having to waste time copying the entire dataset out of the database by hand.
Also, just an unrelated side note, I feel like providing constants along with the API like DB_ORDER_ASC/DB_ORDER_DESC would be a good idea, to make the function calls more readable by sight. |
|
|
|
|
|
I'd just like to say that it's definitely useful to have full control over both a keyname and namespace as I've seen some question whether we should be allowed to specify namespace as a parameter in each function. The namespace gives an extra degree of freedom which makes it much easier to filter out data without having to rely too much on constantly constructing unique strings using strparam, plus it's very useful for sorting; how are you supposed to tell the database to sort only keys with say a MAP01_ prefix? |
|
|
|
|
Quote from Watermelon
We also have to take into account hosting services like Best-Ever where people might maliciously try to write into other people's DB's.
How does one stop this?
I don't see any feasible way to introduce a permission system, but Best-Ever simply could make each custom server to use its own database by forcing how the CVAR databasefile is set.
Quote from AlexMax
However, part of me also agrees with you about the additional parameters to each DB command that would require. Perhaps the best thing we can do is document the use of these functions and ensure that the documentation has the mod-specific namespace as part of the documentation. That way, people who "learn" by copypasting will get this extra namepsacing "for free".
So just let the documentation encourage to uses namespaces like "zanrun-time" and leave it at this?
Quote from AlexMax
Are there any other instances in the ZDoom ACS API that global state is used...other than perhaps setting the currently in-use font?
Not really, but there is nothing really comparable to the kind of handle you suggest either. Extending the global state idea to a kind of handle that is just an int to ACS shouldn't be that complicated though. The biggest issue is how to free handles. A mod would need to do this explicitly.
Quote from AlexMax
Also, just an unrelated side note, I feel like providing constants along with the API like DB_ORDER_ASC/DB_ORDER_DESC would be a good idea, to make the function calls more readable by sight.
Yes, we should do this.
Quote from Jroc
I'd just like to say that it's definitely useful to have full control over both a keyname and namespace as I've seen some question whether we should be allowed to specify namespace as a parameter in each function.
We will definitely keep both. The only question is whether it's really necessary to add a third thing, e.g. section. I think it's not as long as modders use namespaces like "zanrun-time". |
|
|
|
|
|
Can I request an "ignore parameter case" bool parameter for retrieving and setting DB entries? When retrieving, if this bool is true it will ignore the case and only match the letters when looking up namespace and keyname strings, and when setting it will force the namespace and keyname strings to be upper case. This not being the case currently has caused me a bunch of problems, for instance if you try to use n:PRINTNAME_LEVEL as part of your namespace, this will sometimes set it with a lowercase map lump name and sometimes upper case (of the same map lump) depending on certain circumstances, and so will set and retrieve different records depending on arbitrary stuff (such as whether you did map map01 or map MAP01 to change to that map). |
|
|
|
(0009271)
|
|
Watermelon
|
2014-06-14 03:30
(edited on: 2014-06-14 05:12) |
|
|
|
|
(0009273)
|
|
Dusk
|
2014-06-14 04:59
(edited on: 2014-06-14 05:00) |
|
maybe this? Didn't compile this either, but needs just one strparam call
#define CASE_DIFFERENCE ('a' - 'A')
int g_toLowerBuffer[1024];
function str toLower (str s)
{
int i, c;
for (i = 0; str[i] != '\0'; ++i)
{
c = GetChar (str, i);
if (c >= 'A' && c <= 'Z')
g_toLowerBuffer[i] = c + CASE_DIFFERENCE;
else
g_toLowerBuffer[i] = c;
}
g_toLowerBuffer[i] = '\0';
return strparam (a:g_toLowerBuffer);
}
|
|
|
|
|
|
Thanks, positron provided me with a strtoupper function but it requires bcc ('http://zandronum.com/forum/showthread.php?tid=4864 [^]' ) so I'm aware this stuff is possible. However many people might have no idea that some of the strings they are sending have inconsistent casing, I didn't catch this for a couple of days and it caused a lot of problems. |
|
|
|
(0009277)
|
|
Hypnotoad
|
2014-06-14 06:23
(edited on: 2014-06-14 06:25) |
|
Also, can I request the public beta on zandro.com be updated with the DB features? We've had a lot of fun populating the database with our personal bests on zanrun IDL flagrunning (see here:'http://modenov.com/hypnotoad/test/ [^]' , click on map names and player names for more stats ), but a lot of people are unable to join due to the difficulty for them of obtaining a cutting edge build and getting it to work with doomseeker/ide.
|
|
|
|
|
Quote from Jroc
Also, can I request the public beta on zandro.com be updated with the DB features? Sure. Is a beta of the latest 1.3 version sufficient or do you need features from 2.0? |
|
|
|
(0009279)
|
|
Hypnotoad
|
2014-06-14 10:04
(edited on: 2014-06-14 11:59) |
|
I'm not sure, could you provide a 1.3 build of the windows binary and files so I can see if zanrun works on it? Also, will account features be available for it yet? Otherwise, some way to get a player's ip address will probably be needed before it's public because at the moment I'm only using the player's name which could be changed or someone else could impersonate; Watermelon said he'd be making a function for getting the IP as an int so hopefully this can be put in first before the public beta is updated.
edit: come to think of it, the latest version of 2.0 should be fine, since 2.0 is what's linked under the development builds anyway.
|
|
|
|
(0009283)
|
|
Torr Samaho
|
2014-06-14 13:21
(edited on: 2014-06-14 13:35) |
|
The current 1.3 repository version has the same account and database support that 2.0 has. I'd prefer a 1.3 beta release since we plan to release this as stable rather soon and this would give us some extra testing for 1.3.
I'll upload a windows binary you can test.
EDIT: Here is the current 1.3 Windows build.
|
|
|
|
(0009285)
|
|
Hypnotoad
|
2014-06-14 14:04
(edited on: 2014-06-14 14:14) |
|
Testing at the moment, database doesn't seem to be working at all now, not reading from the database file, can't narrow down the problem at the moment, will update if I find out more info.
edit: problem seems to be the namespace, this function:
SetDBEntryInt (maplump, "MapRecordTime", timec );
Where str maplump = strtoupper(strparam(n:PRINTNAME_LEVEL));
for some reason stores to a namespace of the player's name, rather than the name of the map lump as it did before.
edit2: so looks like n:PRINTNAME_LEVEL doesn't exist in 1.3 only 2.0. Can that be backported, or another function to determine the maplumps name be added?
|
|
|
|
|
|
Did you print the result of strtoupper(strparam(n:PRINTNAME_LEVEL)) to the console to make sure that the problem is really in SetDBEntryInt? |
|
|
|
|
|
|
|
|
|
So far I didn't plan any ZDoom backports for 1.3. We'd like to finish this version as soon as possible. |
|
|
|
|
|
Well there definitely needs to be some way to get the maplump name, otherwise how can you get or set map specific stats? Dusk mentioned using levelnum but this only works for MAPXX format maps or if it's defined in mapinfo, and many mapsets don't use this naming format at all (and zanrun is designed to work for almost any ctf or survival mapset). |
|
|
|
|
|
I see and looked at the necessary changes. Fortunately, they are isolated. Please try this binary. |
|
|
|
|
|
Thanks, seems to be working so far. |
|
|
|
(0009294)
|
|
Dusk
|
|
2014-06-14 15:31
|
|
|
|
|
|
|
Since this says needs testing, thought I'd just further re-iterate that after some more hefty zanrun sessions there seems to have been absolutely no problems with the build provided. |
|
|
|
|
|
How is the DB working? All well? |
|
|
|
|
|
All is working well, but of course I'm being held back by lack of sorting and no way to uniquely identify players regardless of their on screen nick yet. |
|
|
|
(0009516)
|
|
Konar6
|
|
2014-06-16 09:08
|
|
|
You will have to wait for the account system being operational or use names for now. IP address is not a way to identify players across sessions, it only identifies the current connection. IP addresses of some players change daily. |
|
|
|
(0009542)
|
|
Hypnotoad
|
2014-06-17 11:21
(edited on: 2014-06-17 14:44) |
|
Another useful feature would be to have a method to get and store the date. It can be useful to know when certain stats, records etc.. were set, and how long they have been this way. It may also help further sort top 10 lists, as if multiple people get the same score, the person who got it first should be given priority.
edit: oh and on the topic of new features - I think along with the sorting, something like GetDBSortedPosition will be needed. It's all very well to be able to sort and get the top x entries, but what if you want to find the position of a specific entry? For instance, what if I want to display someone's personal best time on a map, and also display what position that time is in the rankings (e.g. Personal Best: 15.34s (5th)). Something like GetDBSortedPosition(str namespace, str keyname, bool ascending) or however the formatting would be with the prepare functions would be nice. This would return an int that is the position in the namespace the entry appears when sorted.
|
|
|
|
|
|
|
|
|
Quote from Jroc
I think along with the sorting, something like GetDBSortedPosition will be needed. It's all very well to be able to sort and get the top x entries, but what if you want to find the position of a specific entry
I see why you want this kind of function, but things are starting to get quite specialized here. I'll think about whether we want an ACS interface for this kind of query. Technically, it should be doable in SQLite by checking the value of the entry and then counting how many entries are not lower (or not higher) than the given value.
Quote from StrikerMan780
So, the database system only uses SQLite? Or is it possible to connect to an external MySQL database server? I kind of need to be able to connect to an external server if I'm gonna do what I plan to do.
So far our backend uses a local SQLite database. As long as you implement the functions necessary for the ACS interface, you can use any other database backend though. We tried to make the API independent of the specific database choice. Given the limitations of ACS it is important that the functions used by ACS return instantly. You can't use any kind of threading in ACS. |
|
|
|
|
|
Well, I wouldn't say it's that specialized. Player rankings will be one of the main uses of this database feature, there are already plans to add rankings for duel and ctf players. |
|
|
|
|
|
|
|
|
The necessary zspecial.acs entries to add the new functions to ACC are
-108:SetDBEntryInt(3),
-109:GetDBEntryInt(2),
-110:SetDBEntryString(3),
-111:GetDBEntryString(2),
-112:IncrementDBEntryInt(3),
-113:PlayerIsLoggedIn(1),
-114:GetPlayerAccountName(1),
The only documentation on them is what I wrote in the tracker tickets and the commit messages. |
|
|
|
(0009559)
|
|
StrikerMan780
|
2014-06-17 20:50
(edited on: 2014-06-17 20:54) |
|
@Torr: How would I connect it to a MySQL server then? Any examples?
About threading... Hate always using it to compare with, but SourcePawn cannot use threading either. However, they get around that by using Callbacks.
In that, you can call a function with several parameters. First being the database handle, the second being the query, and the third being what Script to use as a callback. The function returns immediately, and the rest of whatever script is running will continue to execute. Once the query is successfully completed, it executes another script (the one you defined in the function) where you can work with the results of the query.
Would a system like this work?
|
|
|
|
|
Quote from StrikerMan780
How would I connect it to a MySQL server then? Any examples? I have never connected to a MySQL server with C/C++, so I don't know. To use MySQL, the following internal Zandronum functions need to be implemented based on MySQL:
void DATABASE_SaveSetEntry ( const char *Namespace, const char *EntryName, const char *EntryValue );
void DATABASE_SaveSetEntryInt ( const char *Namespace, const char *EntryName, int EntryValue );
FString DATABASE_SaveGetEntry ( const char *Namespace, const char *EntryName );
void DATABASE_SaveIncrementEntryInt ( const char *Namespace, const char *EntryName, int Increment );
Quote from StrikerMan780
About threading... Hate always using it to compare with, but SourcePawn cannot use threading either. However, they get around that by using Callbacks.
Well, in theory you could probably define an ACS script as callback, but then you'll still need to somehow make Zandronum execute the query in a thread that will call the ACS script once the query is finished. This is completely incompatible with our ACS database API though. |
|
|
|
|
Coming back to some database concerns.
> So just let the documentation encourage to uses namespaces like "zanrun-time" and leave it at this?
Yeah.
> Not really, but there is nothing really comparable to the kind of handle you suggest either. Extending the global state idea to a kind of handle that is just an int to ACS shouldn't be that complicated though. The biggest issue is how to free handles. A mod would need to do this explicitly.
Yeah, that's the downside of having to manage handles yourself. On the other hand, with the global function if you want data to stick around you have to copy it out of the returned dataset by hand.
> Well, in theory you could probably define an ACS script as callback, but then you'll still need to somehow make Zandronum execute the query in a thread that will call the ACS script once the query is finished. This is completely incompatible with our ACS database API though.
Also, as soon as you start adding other databases to the mix, there's the possibility that the connection to them might be interrupted. What happens then...does the gamesim stop the world while your database query tries to execute?
Sigh, one of the big reasons I wanted to keep a simple API is because trying to get a real database API working means you have to solve all of these hard problems with a scripting language like ACS.
Does anybody have a list of things that coders want out of the database API that they're not getting as it stands? Not function names, but actual functionality? I know that Hypnotoad wants to be able to retrieve a sorted list of data and to be able to pull individual rows out of that sorted list...is there anything else that I missed in the gigantic thread? |
|
|
|
(0009565)
|
|
StrikerMan780
|
2014-06-17 23:16
(edited on: 2014-06-18 19:05) |
|
Sourcemod made calls to a seperate DLL module (dbi.mysql.ext.dll, which handles everything MySQL.), which would tell the core to run the script.
With Zandronum on the other hand, Is there a way to get an external library like that to tell Zandro to execute an ACS Script? or is it not capable of that whatsoever?
(I keep bringing Sourcemod up because it's familiar to me, it's very similar to ACS, and I can explain things easier through example. It has a very good system for handling Databases, and think it would be ideal to follow similar concepts.)
> Also, as soon as you start adding other databases to the mix, there's the possibility that the connection to them might be interrupted. What happens then...does the gamesim stop the world while your database query tries to execute?
Probably return blank query results, and parameter used to determine whether or not it failed, allowing the modder to choose how to react to the situation. The game would keep going as it's trying to connect. That's the whole idea of using a callback instead of just waiting for the function to return.
The only other thing I can think of... is some form of MySQL proxy. Having all stuff done via SQLite, and then having some sort of external system that synchronizes the databases periodically, merging changes/differences.
|
|
|
|
|
>is there anything else that I missed in the gigantic thread?
Well, I think someone mentioned a way to get the keyname from an entry value and namespace, which would save you from having to do some silly stuff.
GetDBEntryKeyfromString(str namespace, str entry), GetDBEntryKeyfromInt(str namespace, int entry) ? |
|
|
|
|
> Well, I think someone mentioned a way to get the keyname from an entry value and namespace, which would save you from having to do some silly stuff.
Keys are designed to be unique. Values are not. If you want a reverse mapping, just put the key/value pair in the database twice - once one way and once the other.
|
|
|
|
|
|
That's what I'm doing currently it just seems a bit silly. Nevermind. |
|
|
|
|
Quote from Jroc
Player rankings will be one of the main uses of this database feature, there are already plans to add rankings for duel and ctf players.
Alright, I added the internal function DATABASE_GetEntryRank and will expose this via ACS.
Quote from StrikerMan780
With Zandronum on the other hand, Is there a way to get an external library like that to tell Zandro to execute an ACS Script? or is it not capable of that whatsoever?
The only way to communicate with Zandronum from the outside so far is via RCON.
Quote from StrikerMan780
Probably return blank query results, and parameter used to determine whether or not it failed, allowing the modder to choose how to react to the situation. The game would keep going as it's trying to connect. That's the whole idea of using a callback instead of just waiting for the function to return.
As I said, callbacks would require a complete redesign of the ACS database interface and would make the whole thing more complicated to use. Not to mention that ACS is not designed for this kind of approach.
Quote from StrikerMan780
The only other thing I can think of... is some form of MySQL proxy. Having all stuff done via SQLite, and then having some sort of external system that synchronizes the databases periodically, merging changes/differences.
I think that this is the most feasible approach. If one would accept that the "get" functions sometimes return outdated values, Zandronum could keep a local copy of the external database, use that to handle "get" and send all "set" and "increments" to the external database. The fact that "get" may return an outdated value is a pretty bad design though.
Quote from AlexMax
Keys are designed to be unique. Values are not. If you want a reverse mapping, just put the key/value pair in the database twice - once one way and once the other.
Which will also only work if the value is unique.
Anyway, since Zandronum will not assume the uniqueness of values, GetDBEntryKeyfromString/Int doesn't make sense (unless you want a list of all entries with that value). |
|
|
|
|
Quote
Alright, I added the internal function DATABASE_GetEntryRank and will expose this via ACS.
Thanks, so assuming the prepare sort functions or something similar will also be implemented(?) all we need now is accounts and then as far as I'm concerned we have all that's needed. |
|
|
|
(0009602)
|
|
Hypnotoad
|
2014-06-20 11:50
(edited on: 2014-06-20 11:51) |
|
Oh I forgot to mention I had another idea. While the devs are against exposing the time/date to acs, I think what you could safely do is save a timestamp every time an entry is written which is stored in a new column in the database, the column would be inaccessible via acs, but would exist in the database so that websites and other things can make use of it.
|
|
|
|
(0009627)
|
|
StrikerMan780
|
2014-06-21 18:17
(edited on: 2014-06-21 18:23) |
|
"As I said, callbacks would require a complete redesign of the ACS database interface and would make the whole thing more complicated to use. Not to mention that ACS is not designed for this kind of approach."
No way to get ACS Functions to call things that don't reside in the ACS System in and of itself to break free of whatever limitations ACS may have? (Be it threading, or w/e.) Also, how would it be that much more complicated, if at all? I'm kind of modeling my approach after this:'https://wiki.alliedmods.net/SQL_%28SourceMod_Scripting%29 [^]'
"I think that this is the most feasible approach. If one would accept that the "get" functions sometimes return outdated values, Zandronum could keep a local copy of the external database, use that to handle "get" and send all "set" and "increments" to the external database. The fact that "get" may return an outdated value is a pretty bad design though."
Perhaps there's a way to get a timestamp / last modified time of sorts? There must be some way to determine if the values in the database are outdated.
|
|
|
|
(0009629)
|
|
Torr Samaho
|
2014-06-21 19:08
(edited on: 2014-06-21 19:09) |
|
Quote from StrikerMan780
No way to get ACS Functions to call things that don't reside in the ACS System in and of itself to break free of whatever limitations ACS may have?
There are a lot of things you could hack into ACS, but that doesn't necessarily mean that it's a good idea in the long run.
Quote from StrikerMan780
Perhaps there's a way to get a timestamp / last modified time of sorts? There must be some way to determine if the values in the database are outdated.
I think it should be no problem to add a fourth column to the table with the time the entry was last changed. This would also resolve Jroc's request: 0001831:0009602
The timestamp may not be exposed via ACS though, otherwise we run into 0001501:0009497. Nevertheless, you could have another program monitor the database and do things based on the timestamp (like synchronizing the data with another db).
|
|
|
|
|
I continued working on the database support. In addition to some changes in how Zandronum internally queries the SQLite database (increment is a single query now instead of a get / set query pair that is marked as exclusive transaction), Zandronum now maintains a fourth column in the database table that stores the unix epoch in seconds (including the fractional part) the last time the entry was changed. This column is inaccessible via ACS and meant to be used by external programs that access the SQLite database directly.
Furthermore, I finished a first version of the sorting related database ACS functions and added the following new functions:
int QuerySortedDBEntries ( string Namespace, int N, int Offset, bool Ascending ),
int GetDBResultSize ( int Handle ),
void ClearDBResultHandle ( int Handle ),
string GetDBResultEntryNameString ( int Handle, int ResultsIndex ),
string GetDBResultEntryValueString ( int Handle, int ResultsIndex ),
int GetDBEntryRank ( string Namespace, string EntryName, bool Ascending ),
Here is a binary that supports them. You'll need to put
-115:QuerySortedDBEntries(4),
-116:GetDBResultSize(1),
-117:ClearDBResultHandle(1),
-118:GetDBResultEntryNameString(2),
-119:GetDBResultEntryValueString(2),
-120:GetDBEntryRank(3),
into zspecial.acs to use them. This is all very experimental and needs to be tested thoroughly. |
|
|
|
|
This is great thanks! I'm just wondering though if you could help me with understanding how these new functions work.
The first function, what does the offset argument do exactly?
Where exactly do you get the handle from, is the handle created when you assign the first function to a variable?
Does "EntryName" in the 4th and 6th functions refer to the keyname?
Does the 5th function return a string from a sorted set of strings? How did you sort them if so? |
|
|
|
|
The int returned by QuerySortedDBEntries is the handle you need to access the results of the query. Here is an example that gets and prints the top 3 entries (with ascending order, i.e. lowest is best) of the namespace named "Random":
int handle = QuerySortedDBEntries ( "Random", 3, 0, 1 );
int numEntries = GetDBResultSize(handle);
Log (s:"Query returned entries: ", d:numEntries );
for ( int i = 0; i < numEntries; ++i )
{
Log ( s:GetDBResultEntryNameString(handle, i), s:" ", s:GetDBResultEntryValueString(handle, i) );
}
ClearDBResultHandle ( handle );
The offset parameter allows you to start with a different entry than the top entry, i.e. with an offset QuerySortedDBEntries will not return entries 1,...N, but 1+offset,...,N+offset.
Yes, EntryName means keyname.
I hope the example also answers your question regarding the 5th function. If you have more questions, feel free to ask. |
|
|
|
|
|
Thanks, this basically answers all of my questions, but why isn't there a function to return an entry value as an int rather than a string? True I can just use GetDBResultEntryNameString in GetDBEntryInt, but I can do that with GetDBEntryString too, so how come there is a function specifically to get the string? |
|
|
|
(0009652)
|
|
Dusk
|
|
2014-06-22 13:16
|
|
|
Looks like we could use an ACS constant for query sort order? That is, if I'm reading the QuerySortedDBEntries call correctly. |
|
|
|
(0009653)
|
|
Torr Samaho
|
2014-06-22 13:24
(edited on: 2014-06-22 13:31) |
|
Quote from Jroc
Thanks, this basically answers all of my questions, but why isn't there a function to return an entry value as an int rather than a string? True I can just use GetDBResultEntryNameString in GetDBEntryInt, but I can do that with GetDBEntryString too, so how come there is a function specifically to get the string? I added the string version because the value is stored internally as string and thus the string version is more general. It would be no problem to add GetDBResultEntryNameInt though.
Quote from Dusk
Looks like we could use an ACS constant for query sort order? That is, if I'm reading the QuerySortedDBEntries call correctly.
Yes, we should add a constant for the search order. AlexMax proposed DB_ORDER_ASC/DB_ORDER_DESC in 0001831:0009134.
EDIT: I added
// Zandronum database additions ---------------------------------------------
#define DB_ORDER_DESC 0
#define DB_ORDER_ASC 1
to zdefs.acs.
|
|
|
|
|
|
Just wondering, what exactly are the consequences if I fail to do ClearDBResultHandle. |
|
|
|
|
|
The results of the query will stick around in memory. I added code that is supposed to clear them when moving to the next map, but didn't test yet if it works. |
|
|
|
(0009658)
|
|
Dusk
|
2014-06-22 14:07
(edited on: 2014-06-22 14:08) |
|
Perhaps the query could be automatically cleared when the script terminates?
|
|
|
|
|
|
The handle is in no way tied to the script that called the function creating the handle. Nobody stops you from storing the handle in a global var or from handing it to other scripts. AFAIK the general practice for handles is the same as for pointers: The one who created it is responsible for freeing it. |
|
|
|
(0009660)
|
|
Hypnotoad
|
2014-06-22 14:44
(edited on: 2014-06-22 14:45) |
|
I'm getting errors now when attempting to set new database values, previously it worked fine in the old binary:
Could not prepare statement. Error: table Zandronum has 3 columns but 4 values were supplied
Could not bind text. Error: table Zandronum has 3 columns but 4 values were supplied
Could not bind text. Error: table Zandronum has 3 columns but 4 values were supplied
Could not bind text. Error: table Zandronum has 3 columns but 4 values were supplied
Could not execute statement. Error: table Zandronum has 3 columns but 4 values were supplied
Could not prepare statement. Error: table Zandronum has 3 columns but 4 values were supplied
Could not bind text. Error: table Zandronum has 3 columns but 4 values were supplied
Could not bind text. Error: table Zandronum has 3 columns but 4 values were supplied
Could not bind text. Error: table Zandronum has 3 columns but 4 values were supplied
Could not execute statement. Error: table Zandronum has 3 columns but 4 values were supplied
Do I need to start with a brand new database? Can the old one be modified to add a 4th column?
|
|
|
|
|
|
Did you create the table with the old binary? Then it lacks the timestamp column and thus only has three columns. You need to delete the database. Zandronum will then automatically create a new table with four columns. |
|
|
|
|
|
I've managed to add the fourth column to the existing database myself, it seems to work for now. |
|
|
|
|
BTW: The interface I created so far (0001831:0009648) is not necessarily final. If anybody has suggestions on how to improve it, I'm all ears.
One I idea I had about the handles: Instead of having QuerySortedDBEntries (or similar functions we may add in the future) create a new handle every time it is called, we could separate the allocation into a new function, e.g. NewDBResultHandle and supply the handle as argument to QuerySortedDBEntries. While this makes the code a little longer, it would make more clear that you are allocating something that has to be freed. |
|
|
|
(0009664)
|
|
Hypnotoad
|
2014-06-22 15:32
(edited on: 2014-06-22 15:34) |
|
Regarding GetDBEntryRank, how does it sort among entries that have exactly the same value? Suppose the 2nd lowest value is achieved twice by two entries, will it consider them "joint 2nd" for instance so return both of them as 2? If not, how does it decide which is 2nd and which is 3rd, is it based on the timestamp? Would it be possible to add a parameter which allows you to specify what to do in this situation, whether to rank by timestamp (or whatever is currently default), giving them the same lowest possible rank (e.g. both 2) or giving them both the highest possible rank (e.g. both 3)?
So the function could be changed to:
GetDBEntryRank ( string Namespace, string EntryName, bool Ascending, int ranktype)
And you could add constants for the last parameter corresponding to 3 options, e.g:
#define DB_RANK_TIES_BY_TIMESTAMP 0
#define DB_RANK_TIES_AS_LOWEST_POSSIBLE 1
#define DB_RANK_TIES_AS_HIGHEST_POSSIBLE 2
|
|
|
|
|
|
GetDBEntryRank counts how many entries have a strictly lower (ascending case, strictly higher for descending) value and returns that number + 1. So, in your case, if multiple entries have the second lowest value, they are considered "join 2nd". In theory, we could add another parameter to configure how ties are handled, but here we are starting to get too specialized for my taste. |
|
|
|
|
|
So just to be clear, if you have 11 entries, one with value 10, and the remaining 10 with a value of 20, all of those remaining 10 entries will return a rank of 2 (assuming ascending)? |
|
|
|
|
|
At least that what's supposed to happen. Considering how little testing the ranking code received so far, I don't give any guarantees that it actually does happen ;). |
|
|
|
|
Well, that behavior suits my needs for now fortunately.
As for the interface, I personally think it's fine how it is now, but a function to get an int rather than string would probably be convenient. |
|
|
|
|
> The results of the query will stick around in memory. I added code that is supposed to clear them when moving to the next map, but didn't test yet if it works.
How does this work with global ACS scripts? |
|
|
|
|
|
Good question. Now that I think about it, if you store the handle in a global var it would become invalid. I'll change it so that the handles are only cleared when the global ACS vars are reset. |
|
|
|
|
I've gone over the new ACS functions and tried to normalize and simplify their names:
void SetDBEntry(string namespace, string key, int value);
int GetDBEntry(string namespace, string key);
void IncrementDBEntry(string namespace, string key);
void SetDBEntryString(string namespace, string key, string value);
string GetDBEntryString(string namespace, string key);
int SortDBEntries(string namespace, int limit, int offset, bool descending);
int CountDBResults(int handle);
void FreeDBResults(int handle);
int GetDBResultKey(int handle, int index);
int GetDBResultValue(int handle, int index);
string GetDBResultKeyString(int handle, int index);
string GetDBResultValueString(int handle, int index);
int SortedDBEntryPosition(string namespace, string key, bool descending);
A few notes:
- I have removed the "Int" from any integer-related functions, in order to be consistent with the CVar functions.
- Ascending sorting is the "default" i.e. if you use 0 instead of a named constant. This is in line with how SQL works. |
|
|
|
|
|
The int returned by SortedDBEntryPosition is not the same as the position in the results returned by SortDBEntries, so I'd rather keep a bigger difference in the name of the function. IMO "Rank" should be in the name. |
|
|
|
|
|
Could there be a flag that can enable or disable the database features, and can it be off by default? I think a malicious wad could fill up a database with thousands of entries of gibberish every tic as soon as you run it which would be harmful. Something like sv_enabledatabase. |
|
|
|
|
|
sv_db_maxsize <0 for unlimited storage | positive integer otherwise> |
|
|
|
|
Quote from Jroc
Could there be a flag that can enable or disable the database features, and can it be off by default? To prevent this the DB already defaults to volatile in-memory storage. Isn't this enough? Sure, a mod could clog the RAM, but there are already many ways for a mod to make the game unplayable.
|
|
|
|
|
|
It seems like it archives the value whenever you set it. If I host a server and specify a database file on that, then if I run a wad in singleplayer afterwards that will still be the database file. |
|
|
|
|
|
Yes, the CVAR databasefile is archived intentionally. |
|
|
|
|
Okay, just seems a bit dangerous if a user forgets to change it back after hosting a server.
Anyway, since it seems as if the accounts system will take longer than thought, is it possible the public beta of 1.3/new 2.0 can be updated without it for now? I can just keep using player names for the time being. |
|
|
|
|
|
No, please release 1.3 with the account system. I am willing to wait. |
|
|
|
|
|
AlexMax and I got the planned changes on the account protocol done on Sunday evening. I'm just polishing a few things and plan to release the 1.3 beta soon (probably tomorrow). |
|
|
|
|
|
It doesn't look like any server is using the current 1.3 beta at the moment. I don't know about other modders, but if a version of 1.3 beta with sorting function is released I'll be able to get a Zanrun (and possibly another mod I'm working on) server up and running so that the new account/db features can get a more thorough testing. |
|
|
|
|
Made a bunch of changes and ported them to 1.3:
- Added new CVAR database_maxpagecount (defaults to 32768) that controls the maximal allowed size in pages of the database.
- Added new ACS functions SortDBEntries, CountDBResults, FreeDBResults, GetDBResultKeyString, GetDBResultValueString, GetDBResultValue and GetDBEntryRank.
This covers the renaming and the bool changes AlexMax suggested, except that I kept the name GetDBEntryRank (see 0001831:0009717) and didn't add GetDBResultKey. The key is supposed to be a string (all functions only allow a string argument for the key), so I don't think that it would make sense to return the key as int.
|
|
|
|
|
|
Awesome, what do I need to add to my zspecial.acs? Are the numbers the same as last time? |
|
|
|
|
zspecials.acs
-115:SortDBEntries(4),
-116:CountDBResults(1),
-117:FreeDBResults(1),
-118:GetDBResultKeyString(2),
-119:GetDBResultValueString(2),
-120:GetDBResultValue(2),
-121:GetDBEntryRank(3),
zdefs.acs
#define DB_ORDER_ASC 0
#define DB_ORDER_DESC 1
|
|
|
|
(0009955)
|
|
Hypnotoad
|
2014-07-12 20:10
(edited on: 2014-07-12 22:15) |
|
Thanks, everything seems to work fine.
edit: will a new public beta be released with this stuff?
|
|
|
|
(0010074)
|
|
Hypnotoad
|
2014-07-29 08:31
(edited on: 2014-07-29 08:51) |
|
So it appears too much database activity can cause major cpu load, causing the server to hang and player's ping to skyrocket temporarily. This is currently very difficult to reproduce, as this seems to only happen sometimes. I'm looking for ways to optimize the script at the moment, I'm pretty sure this is the script which causes the occasional cpu load:'http://pastebin.com/syicm01N [^]' , however not that much is being written to the database, one entry per player in the database so in the test we jut did there was only 3 people in the database so it only wrote 3 times. The loop is mostsly just checking the DB entries, and I don't see why that should cause so much cpu load.
edit: this loop is another candidate'http://pastebin.com/2b5MEgmq [^]' but I'm pretty sure from the testing I've done that it's not this.
|
|
|
|
|
For instance, calling GetDBEntryString ("MapNames", mapid ) thrice instead of only once and saving the result in script 463 certainly doesn't improve performance ;).
What exactly is script 463 supposed to do? Since I have no idea what you are saving, I don't know what kind of logic you are following. I just noticed that you have playerid, playerid2 and playerid3 and all seem to be exactly the same. |
|
|
|
(0010092)
|
|
Hypnotoad
|
2014-07-29 21:24
(edited on: 2014-07-29 21:27) |
|
Playerid, playerid2 and playerid3 are the same because they occur at different times, and strparam (really really annoyingly) only lasts for one tic. There is a specific reason that they had to be 3 unique variables for it to work properly, rather than constantly reassigning the same variable, but I can't recall now, but some kind of ACS limitation or bug forced me to I believe, it shouldn't really affect performance regardless though.
This is what the script does, it accumulates all the points for a player. Every time someone gets a personal best time, they may improve their rank on that map, you are awarded points depending on your rank for that map; if your rank goes up you earn more points, if someone else overtakes you causing your rank to go down you then lose points. The latter script in the edit checks whether each player in the database has changed in rank for that map, then changes their point score for that map, it then executes 463 which tally's up all the points for each player by adding all of their points from each map to provide a total points score by which players are globally ranked.
I've managed to completely bypass this script for now, what happens instead now is that when a player's point score for one map is changed, their previous score is taken away from their totalpoints and then the new score is added, rather than having to tally up all the points each time. I think this has improved performance somewhat but it's hard to tell due to the rarity of performance drops, I have noticed the occasional ping spike still which I cannot rule out as coincidental but has happened occasionally enough (this may change when lots of people play at the same time) that it's hard to know for sure exactly what causes it.
Perhaps some guidance on how many times Zandro can query the database in one tic before performance is affected would be helpful.
|
|
|
|
|
Quote from Jroc
Perhaps some guidance on how many times Zandro can query the database in one tic before performance is affected would be helpful. AFAIK nobody has ever used Zandronum's database backend for anything performance critical, so there is no experience with this yet.
What I see from your script though is a limitation of the current database interface that makes you spam the database with unnecessary calls. For instance, the outer loop is supposed to iterate over all entries in the namespace "PlayerNames", isn't it? Ignoring the inner loop completely, the outer loop leads to 2*MAXIDS database queries (with my comment above you can cut this down to MAXIDS queries) no matter how many entries are in the namespace. It would be much better to add a database function that just iterates over all entries in a namespace directly (like the function that iterates over the top N entries). |
|
|
|
|
FYI it doesn't iterate over all of MAXIDS, only over the amount of playerids in the PlayerNames namespace, and will break out of the loop as soon as it encounters no player name recorded for the id. Since there were about 3 or 4 players in the database at the time of that initial test, this would have been 8 queries in the outer loop.
But yes, being able to iterate over all entries in a namespace would be handy. |
|
|
|
(0010223)
|
|
Hypnotoad
|
2014-08-23 20:18
(edited on: 2014-08-23 20:19) |
|
Sorry for yet another feature request but I thought it'd be worth a shot:
I was looking into using the database to make wads that record players wins and losses, particularly in gamemodes like duel, but also in ctf, and then use these stats to create rankings/league tables. But I've ran into a snag, in order to properly rank players in a gamemode that doesn't have a fixed amount of matches/players you generally need some kind of special algorithm to award or reduce a player's points. Most notable is the Elo ranking, which is used in chess, as well as other sports and some video games. Ranking players by the number of wins, or the ratio of wins to losses for instance, is simply not suitable as it doesn't take into account the quality of players' opponents, or the differences in quantity of games played. Therefore it seems that it is impossible to properly rank players without using an algorithm like this.
The problem is, algorithms like Elo are a bit complex mathematically, enough to basically be impossible or extremely difficult to implement in acs. So far the only way one would be able to do a proper ranking would be to make some external application to perform calculations on the database.
What would be extremely great is if a new function could be created for Zandronum, you supply it with three numbers and a bool (the rating of the player, the rating of his opponent, the K-factor, and whether the player won or lost), and it then internally calculates how many points to award or take away from the player using the elo algorithm (or something similar), which should be much more straightforward to implement using a proper programing language, and returns that number. This would be really great for Zandronum as it would enable people to easily add player rankings to their mod if it involves competitive PvP play.
|
|
|
|
|
|
IMHO if the math to do a particular ranking system is too complicated to implement in ACS, then it's probably a better idea to leave the idea of a ranking system alone and try an alternative, or simply rank them by number of wins or something simple. |
|
|
|
(0010225)
|
|
Hypnotoad
|
2014-08-23 22:35
(edited on: 2014-08-23 23:02) |
|
It is hard in acs because acs does not have floating point, but I believe it would not be difficult to hardcode into Zandronum. Ranking simply by number of wins does not mean much, it gives an advantage to people who have played longer, and it does not take into account the quality of the opposition. I could just play a clone of myself, or a friend, hundreds of times and lead the ranking in number of wins without having to face difficult opposition, it wouldn't be a true ranking.
edit: to be clear, it's not difficult or complicated in the sense that it requires a lot of number crunching or to sift through lots of data, it's only "difficult" because doing something like A' = A + K*(S - ( 1/(1 + 10^((x-y)/400)))) (this is the elo formula itself) is really hard with fixed point only.
|
|
|
|
|
|
I agree that it would be good if mods could do Elo like ranking. Is it already possible to do the necessary calculations in ACS with the latest ZDoom version? If so, we can talk about backporting the necessary ACS additions. |
|
|
|
|
|
It wouldn't be any easier to do the calculations in acs using the latest zdoom, as far as i know, I didn't see any new mathematical functions on the wiki that would be of use. |
|
|
|
|
|
In that case, I wouldn't mind adding one such function. We have to make sure though that we chose a rating system the majority of the players accepts. |
|
|
|
(0010229)
|
|
Hypnotoad
|
2014-08-24 18:57
(edited on: 2014-08-24 19:09) |
|
|
|
|
(0010231)
|
|
Hypnotoad
|
2014-08-24 20:03
(edited on: 2014-08-24 20:56) |
|
I just noticed Quake Live Ranks uses ELO'http://www.qlranks.com/ [^]' and this seems to be a popular resource, so since Elo is well accepted by the quake community I don't see why it wouldn't be accepted by the Zandro community.
|
|
|
|
(0010234)
|
|
Blzut3
|
|
2014-08-25 07:00
|
|
Jroc:'http://pastebin.com/SdzVXgrF [^]'
Give that a try. The MAXELO constant isn't quite right due to potential signed integer overflow I think. But the general idea there is sacrificing a little bit of range gives a few more bits of precision allowing it to be done entirely in ACS. From what I understand elo typically ranges between 0-3000. |
|
|
|
|
|
Wow, thanks a lot for this, this is really great! So far it seems to be working fairly well, the only problem is it seems to always round down the result, but then this may not be a bad thing if you want to keep Elo changes slightly conservative? Do you know if there is an optimal K-value and/or starting values that this library will work best with? |
|
|
|
|
|
Actually this rounding is more serious than I thought, it seems that you will often (practically always) lose more points than you will gain. If I gain 2, the other person will usually lose 3 for instance. |
|
|
|
(0010238)
|
|
Blzut3
|
|
2014-08-25 19:06
|
|
I believe you can fix that with: return ra + ((k*((w<<16) - e) + 0.5)>>16);
If negative values need special handling, remember you can ultimately replace that right shift 16 with a rounding function of your choice. At the typical k values, I do not believe any possible loss of fractional precision for e makes any practical difference so it's just a matter of how you decide to do the conversion to a normal integer (in the code I pastebin'd it's simple truncation).
I believe the safe input range for r is [0-3130] assuming my thoughts about an overflow going to 3251 is correct. For k it should work up to 32767, but that would be useless. |
|
|
|
|
For the record, I added the new ACS functions BeginDBTransaction, EndDBTransaction and GetDBEntries.
|
|
|
|
(0010347)
|
|
Dusk
|
|
2014-10-05 22:22
|
|
|
Marking as resolved as 1.3 is now released. |
|