Custom Command Database
"Data is the new oil." // Clive Humby
Last updated
"Data is the new oil." // Clive Humby
Last updated
Databases are used for storing persistent data that you want to keep between custom command executions. You access and manipulate them with functions which we are going to elaborate on in this guide.
This section covers the structure of a database entry, the database's size limits, as well as the entry's size limit and lastly the interaction limit per custom command execution.
A database entry has the following structure:
| Field | Description |
|---|---|
This can also be found in the documentation.
It is to note that the user ID doesn't actually have to point to a valid user - it can be any integer. If you for example used 2000 as ID, this will be .UserID. If you used a non-user ID, the .User object will be invalid.
The fields .CreatedAt, .UpdatedAt, and .ExpiresAt all evaluate to the time.Time structure and can therefore be used with time.Time related methods, which can be found here.
As everything that has to do with computers and data, there are limitations. Our YAGPDB database is no exception. However, we tried to make them as large as possible, whilst still remaining somewhat conservative and not going completely overboard.
In general, you can have 50 * Members values (entries) in your server's database. If your server has premium activated, this increases to 500 * Members. Note that this will not immediately change as this value is cached, should your members leave or newly join. If you go above your maximum entries, all new write functions will fail. Honestly though, already 50 * Members is a lot to deal with, so you are safe for a while.
Please note though that this is not 50 (or 500 with premium) entries per user, but rather for everything. This of course means that one user could take up every entry there is. So, be careful with your database!
Database keys are strings and are limited to 256 bytes in length (aka 256 characters). If used with dbSet or dbSetExpire, the key argument will be internally converted to a string, so you can actually pass whatever you want.
Lastly, each value can hold up to 100kB, which is, considering we only deal with characters (mostly), a lot. Try writing a .txt document that is 100kB large, then you know how much this seemingly small space can actually hold.
To prevent spam and therefore possible lag across YAGPDB, we put interaction limits in place. First of all, you can't use more than 10 overall interactions, so every function that starts with db counts towards that limit. This limit increases to 50 if you have premium active. Moreover, there are so-called multiple interactions, namely dbCount, dbGetPattern, dbGetPatternReverse, dbTopEntries, and dbBottomEntries. These are limited at two total calls per custom command execution, 10 with premium. You don't have to understand how they work right now, we'll get later into that. Just keep in mind that they are counting towards a second limit.
That concludes the overview, now let's get into basic interactions!
These interactions are your constant companion when dealing with the database. You are most likely to use them more often that the upcoming functions, so really get them into your muscle memory, then the rest will be super easy!
To start, let's take a look at the syntax:
We've already covered UserID and Key further up. As a small refresher: UserID can be any integer, and Key is a string, the name of the entry so to say.
Now, what exactly is that Value? You surely know how variables work in YAGPDB-CC, and the value is nothing else. Just that it is persistent between custom command executions, and variables are not.
Okay, we can now set a value into the database, but how on earth are we going to retrieve it? Well, this is where dbGet comes in handy. As its name already suggests, it gets an entry (not the value!) from the database with the given Key and ID.
As you can see, we don't have a value as above, just simply ID and Key. The call of this function returns an entry object, as described above. So, before you continue reading, take a moment to think about how to get the value.
Now we know how to set and get stuff with the database, but a good program also frees up unused memory, and custom commands are no exemption to that. These cases are where we use dbDel.
Write a custom command that sets an entry "Database is cool!" under the key yagpdbDatabase for the triggering user. Then you should get the entry, print out the time when this entry was updated, its key and its value. The format of the time does not matter. You should access those fields using the database entry object. Afterwards, delete the entry again.
Now, you might want to become a little more special with your database - That's why we have a few more functions, dbIncr and dbSetExpire.
With these functions, you are able to add a whole new layer to your custom commands, so let's get started!
dbIncr is quite a handy function, as it increases the value inside the entry by the given increment and returns the increased value in the same moment, allowing you to save it to a variable.
You might ask yourself, what exactly is the increment? This argument can be any valid number, so integers and floats. Please note however that the return type of dbIncr is always a float. So if you are using integers as increment and plan to use them as such, please don't forget to convert them. Now let us take a quick look at the syntax.
What's also noteworthy is the fact that dbIncr sets the value to the given increment, shouldn't the entry exist already.
Try thinking about how you would implement a custom command that increases a given entry by a set amount, gets the value, but also sets a new entry if it doesn't already exist. I think you can see how helpful dbIncr really is, considering that this function can condense the following code into one function call.
As you see, using only basic functions essentially requires you to waste a database function call you can probably use elsewhere. And as we discussed in the beginning, those are limited at 10 (50 with premium), so quite a precious resource that should not be wasted.
Now you might want to set entries which get deleted after a while. You could of course use a delay using execCC, or use dbSetExpire. Why is it worth to use dbSetExpire? You don't have to waste an execCC to delete the entry later on. As we recall from the beginning, the .ExpiresAt field is of type time.Time, so the related functions are available to us. Let's take a glimpse at the syntax:
The Expires in is given in seconds, the rest is standard stuff as explained under Basic Interactions.
A common use case for this function is a cooldown: As long as the entry exists, the command is still on cooldown.
As a side effect, expired entries will be considered gone (i.e. deleted) by YAGPDB, but still remain in the underlying database.
Lastly there are special functions which allow you to get multiple entries. We call those coincidentally multiple entry interactions. Every function except one returns a slice of entries. Depending on what function you use, this slice is sorted by certain criteria.\
This is the only function interacting with multiple entries not returning a slice. Since this function is fairly easy to understand, we'll start with that. Let's take a quick look at the syntax:
dbCount counts the entries either for the given database userID or the pattern for database keys. Alternatively, you can make it count entries as one query by passing in an sdict with the keys userID for the ID and pattern for database keys that are to be counted.
Please note that this function returns the amount of entries, so to avoid random spam, you'd have to store it into a variable. Apart from that, this function is very simple to use and might come in handy for a few things.
These functions return a slice of entry objects, in case of dbTopEntries ordered by descending value, in case of dbBottomEntries in ascending order. You cannot retrieve more than 100 elements in one call - honestly though, 100 entries in one go is definitely a lot, probably more you would have to actually deal with.
What's new here are three things: pattern, amount, and nSkip. Let's walk through them one by one. For pattern, we use basic PostgreSQL patterns, you can read on them further down. The amount specifies how many entries we want to retrieve. Lastly, you tell YAGPDB how many entries it should skip using the nSkip argument.
Now to retrieve the value of each entry, we range over the given slice and access the .Value field:
In analogy to the above code example, you can access any other field as well. These functions might come in handy when you are for example trying to compose a leaderboard.
These two functions allow you to get multiple entries under one user ID with matching keys, again using patterns. They return a slice of entries sorted by value, just as the above functions. The only difference here is only the limitation to one user/ID instead of all IDs.
Just as above, we range over the given slice to access fields of the entry object. For simplicity's sake however, no code example, as it should be pretty clear how to do this.
This function allows you to delete multiple entries in one go, making dbDel spam no longer necessary. Its syntax is a little more intricate than other functions:
query is a sdict with the following keys:
userID: delete entries under this user ID. If this key is not provided, it'll default to all IDs.
pattern: delete entries with keys matching this pattern.
reverse:if true, start deleting entries with lowest value first. Defaults to false.
amount specifies how many entries should be deleted in one go, maxing out at 100. skip specifies how many of matching entries should be skipped.
Please note that this function returns the amount of deleted entries, so to avoid random messages popping up, catch it by assigning it to a variable. With all that in mind, you could use the following code to delete up to 100 matching entries the pattern test% under the current user:
This function returns the rank of a specified entry in the set of entries matching criteria provided by query.
query is as above a sdict with the following options:
userID: search only through entries stored under this ID. Will default to all IDs, if not provided.
pattern: only count entries with matching keys; defaults to entries with any key.
reverse: if true, lower valued entries will have a higher (better) rank. Default is false.
As an example, to find the rank of the entry with the key test for the current user in all of this user's entries, you may want to use the following code:
Note that this function returns the rank, so to avoid random spam, don't forget to assign it to a variable.
As mentioned earlier, we use patterns for a set of functions. Now, you rightfully ask yourself "what the hell are patterns?". Allow me to lift some confusion: We're lucky, it's not RegEx. Instead, there are only two special characters: % and _. More are not used, just those two. In case you need to escape those, prepend them with a backslash \.
Quite easy, actually:
The percent sign % matches any sequence of zero or more characters
The underscore _ matches any single character
Okay, with that in mind, let's take a look at an example. The following pattern will match anything that starts with the letter l and ends in n.
The following example showcases the usage of _.
This pattern matches words such as hello, helgo, heloo. I think it is pretty clear now what % and _, so let's get onto some tasks!
Write a pattern that matches any string ending in er.
Write a pattern that matches string having h as the second character, any at the start and lastly ending in l. Tip: between h and l you need to match any character sequence
Saving values with custom types to database may result in their values being serialized to a different type, meaning that you might have to convert it back to its original type when retrieving. For example, saving the result of a cembed to call to database will result in it becoming a map[string] interface{}.
The following code will showcase this behaviour:
However, most commonly used types will be saved with their type information intact, meaning that there will be no need to convert them after retrieval. In particular, sdict, dict, and cslice may be saved directly to database and will retain their original types.�
You might have noticed that, whenever you're storing a user ID, channel ID, etc. into your database, it will come back as a weird value, such as 5.241379415938826e+17. This is because they're saved as floats, hence it being messed up. To prevent this, simply convert them to a string before storing and converting back to an int upon retrieving, like so:
When you've been using the database for quite a while now, you surely have heard of so-called "global" and "per-user" entries.
These terms are often used and help getting the point across when explaining the effect, but when it comes to understanding the workings behind it, this is not the right way to think about it.
When you do so, you exclude all possible variations and just think "If I have a 0 as the userID, it's a global db and if it's .User.ID, it becomes a per-user db". You block yourself from creating systems that are case-dependant, over-complicate things and confuse yourself.
The way to go about this is to think of it in terms of the database entries themselves, and how they're used/going to be used in your system. As I talk about this further, I do so with the assumption that you have used at least a few database functions already. If not, consider reading from the top of this page instead of jumping around on it.
In a sdict, or dict, you have key-value pairs, where each value corresponds to its key. Database entries work in a similar fashion, except each value corresponds to the combination of the userID and key. Basically, two database entries are identical (rather, the same) when they both have the same userID and key.
Each of the following line corresponds to and returns different database entries, since they don't share the same set of userID and key.
Having understood DB entries, we can now define these terms in a better way:
Global Entries: If everyone/everything refers to the same database entry, we conventionally call it a global entry.
User/Channel-specific entries: If different users/channels refer to different entries based on any set conditions, we call them per-user entries, or similar terms.
Before you write your code, you need to decide how your command will use databases and then take action accordingly.
Need a different database entry in each channel that is independent of the user? Use dbSet channelID "key" value.
Need different database entries in separate channels that are dependent on the user? Use dbSet .User.ID "channelID" value or dbSet channelID (str .User.ID) value depending on what kind of data you're expecting to get from dbTopEntries, or just any other custom command.
It's all about playing with the userID and key to get what you need. This should hopefully give you a little better idea and push you to think in the right direction.
.ID
The ID of this entry. Not to be confused with the User ID.
.GuildID
The server ID.
.UserID
ID of the associated user.
.User
The associated user object.
.CreatedAt
When this entry was firstly created.
.UpdatedAt
When this entry was lastly updated.
.ExpiresAt
When this entry will expire.
.Key
The key of this entry.
.Value
The value of this entry.