create persistent highscore store

[basisbit]

Actual behaviour

Highscores are not tracked, and not stored.

Expected behaviour

The game stores the singers scores depending on difficulty level and thus allows for statistics, comparing with previous tries, competitions, Top 5 Ranking and so on.

Steps to reproduce

1. Open the game, sing and finish a few songs
2. Check and confirm the Top 5 Ranking
3. Restart the game and check the singing statistics

[nikolaosginos]

I am currently looking into this. I would store it in a binary file in Application.persistentDataPath if that's okay?

I guess we would need a highscore data structure containing a list of scores for each song. A score is related to a song and a player and has a value. This would be enough to show statistics for a player or a song and for listing the top scores etc.

Am I missing something or is there anything more you would like to see in it?

[basisbit]

sounds good, just don't forget to also store the difficulty (easy/medium/difficult)

[eldan-dex]

After taking a look at this, I think there are two options here:

• Having one score file per song stored either in the song's folder (portable scores) or a separate highscores folder (scores separated from actual song data) and read highscores from these files on demand. Pros: Simple, doesn't need a database. Cons: Possibly inefficient, hard to navigate, creates a lot of files
• Having a centralized score database like in USDX. Pros: Straightforward, sensible, allows for higher efficiency if a good database solution is used Cons: Would most probably need a database solution, just like USDX

Right now I'm more in favour of the second option and using something like iBoxDB (https://www.iboxdb.com/) or MarcelloDB (http://www.marcellodb.org/), since they seem to be simple, fast enough and made with unity and multiplatform deployment in mind. Any thoughs on this?

The structure of the database would be pretty straightforward:

• List of songs, accessible by a query on either directly the song name or a hash thereof
• Three categories (easy, medium, hard) for every song
• A (sorted?) list of entries (Name - Date - Score) for each category

Upon inserting a new highscore into the database, an online submission could also be sent if an online highscore server was configured. For online submissions, should we care about validating the score submissions?

[basisbit]

iBoxDB seems to be fast, but not open source and I wasn't able to find any license file / documentation on their website. Looks like it needs a precompiled dll to be available. Also, various stuff is hosted on sourceforge...

MarcelloDB is MIT licensed, an open source project and offers rather limited features.

These systems both use object serialization to store their data. I am now wondering if maybe we could just use FullSerializer for serialization of local highscore data and keep that lightweight, only load/deserialize at game startup, and still have good performance for the queries that we want to do.

[achimmihca]

My approach to this would be to keep the data as a simple object hierarchy in the RAM and (de-)serialize this for persistence to JSON using FullSerializer. So, basically the same approach that is used for the settings at the moment.

I think the performance will be sufficient. Loading can be done at startup. Quieries can be done with LINQ. If needed, some Dictionaries that hold the data in a more efficient way to make certain quieries could be added, but I doubt this will be required. For example, on the below hierarchy, one could create a Dictionary in the root Statistics object that maps from songTitle to SongStatistics.

I suggest to extend the SettingsManager into a PersistenceManager. It can hold a Settings object, and a Statistics object. As alternative, a dedicated StatisticsManager could be added.

I would save only the top 10 scores per difficulty per song.

---

For the Statistics object hierarchy, I guess something as the following should fit:

Statistics

• long totalPlayTimeInSeconds
• List songStatistics
• List webStatistics

SongStatistics

• string songTitle
• string songArtist
• int timesStarted
• int timesFinished
• SongRating rating
• List songScores // Only added for a finished song

SongScoreStatistics

• string playerName
• Difficulty difficulty
• int score
• Date date

WegStatistics

• string webSite
• List webSongStatistics

WebSongStatistics

• string songTitle
• string songArtist
• List songScores

[achimmihca]

Screenshot of USDX SQLite database scheme:

[eldan-dex]

I like your statistics structure proposal, I'd only add a few datetime elements, such as datetimeLastPlayed for songs and a datetimeHighscore for a highscore entry, so that it is possible to display these chronologically ordered. I know USDX holds the structure in memory, but my concern is whether that wouldn't take up too much space in some extreme cases, such as with a few thousand loaded songs (USDX can take up a lot of ram when a big number of songs is loaded, not sure how much of that is the stats, but probably a fair bit). An in-memory structure would nevertheless be the fastest and easiest way, provided we agree that the memory impact will be acceptable.

[basisbit]

don't worry, that won't take up too much memory as long as you only collect data for songs that were already played and only for difficulties that were played, keep the object types as small as feasible, don't store any artist/title strings (use md5 hash of the txt file minus the header part instead). Store the top 10 all time scores and song names + artists; and also the top all time song score + song name + song artist separately, so displaying it is fast and easy

[eldan-dex]

After giving it a thought, my proposed format is an inmemory structure serialized (probably as json) into a single highscore file. The inmemory structure would be getting written to disc every time a song is finished (probably the only significant update). The scructure is currently as follows, heavily inspired by achimmihca's proposal:

Statistics

• ulong totalPlayTimeInSeconds
• Dictionary<string, LocalStatistic> localStatistics
• Dictionary<string, WebStatistic> webStatistics
• List< TopEntry> topTen
• TopEntry topScore

LocalStatistics

• uint timesStarted
• uint timesFinished
• DateTime lastPlayed
• SortedSet< SongStatistic> songStatistic

WebStatistic

• string webSite
• SortedSet< SongStatistic> songStatistic

SongStatistic

• string playerName
• Difficulty difficulty
• uint score
• DateTime dateTime

TopEntry

• string playerName
• string songName
• string songArtist
• Difficulty difficulty
• uint score
• DateTime dateTime

A few notes about this:

• When is a good time to update the totalPlayTime, given that the number should increase each second? How often should this be written to disc?
• Use default hashing of C# Dictionary (fast but only 32 bits) or replace it by something like md5?
• Possibly merge timesStarted and timesFinished into timesPlayed (=finished)
• Make songScores a single sorted set of all difficulties, or n (n = number of diffs) sets, each one for a specific difficulty?

[Baklap4]

I wouldn't use a SortedSet since no duplicate items are allowed. I wouldn't also create a database around high scores/stastistics. In fact i'd create a database for the application instead.

This way if something needs to be saved you always have a persistent store for it available.

A proposal for this might be the following tables.

Artists Songs SongDetails Players PlayerSongs Statistics Logs

The tables may be defined as following (click)

# Artists | Column | Type| Length| |---------|:-------------:|------:| | Id | Guid | 36 | | FirstName | String | 100 | | LastName | String | 100 | | CreatedAt | DateTime | N/A | | UpdatedAt | DateTime | N/A | # Songs | Column | Type| Length| |---------|:-------------:|------:| | Id | Guid | 36 | | Title | String | 140 | | ArtistId | Guid | 36 | | CreatedAt | DateTime | N/A | | UpdatedAt | DateTime | N/A | # SongDetails | Column | Type| Length| |---------|:-------------:|------:| | Id | Guid | 36 | | SongId | Guid | 36 | | Length | Int32 | N/A | | Edition | String | 140 | | Language | String | 60 | | Year | Int32 | N/A | | IsDuet | Boolean | N/A | | Path | String | 256 | | Cover | String | 256 | | Background | String | 256 | | Video | String | 256 | | Difficulty | Int32 | N/A | | CreatedAt | DateTime | N/A | | UpdatedAt | DateTime | N/A | # Players | Column | Type| Length| |---------|:-------------:|------:| | Id | Guid | 36 | | Name | String | 140 | | CreatedAt | DateTime | N/A | | UpdatedAt | DateTime | N/A | # PlayerSongs | Column | Type| Length| |---------|:-------------:|------:| | Id | Guid | 36 | | PlayerId | Guid | 36| | SongId | Guid | 36 | | Status | Int32 | N/A | | CreatedAt | DateTime | N/A | # Statistics | Column | Type| Length| |---------|:-------------:|------:| | Id | Guid | 36 | | Type | Int32 | N/A | | PlayerId | Guid? | 36 | | SongId | Guid? | 36 | | TypeOfValue | Int32 | N/A | | Value | Object | N/A | | CreatedAt | DateTime | N/A | # Logs | Column | Type| Length| |---------|:-------------:|------:| | Id | Guid | 36 | | Level | LogLevel | N/A | | Event | Int32 | N/A | | Description | String | 1024 | | Message | String | 1024 | | Exception | String | 1024 | | MetaInfo | Object | N/A | | CreatedAt | DateTime | N/A |

With this strategy one could easily use a database for:

• quickly searching and selecting songs to play
• Creating statistics in a generic way without much overhead
• Keep a track of logs like broken songs etc
• Print out a list of all the songs you have for publishing on a website or printing
• Web frontend (locally) to make interfacing with Play easier.

To get back to the issue of storing HighScores per player, one could save these scores within 'Statistics' with the following record:

Id	Type	PlayerId	SongId	TypeOfValue	Value	CreatedAt
Guid	Score	SomePlayerId	SomeSongId	Int32	9500	2019-11-29T13:53:00.000000Z

Other statistics can be inserted aswel since it's genericly built.

Database

All tables listed above are using c# object-types a respectivly DB type needs to be added but is decided by what kind of db we're using. I'd suggest using a database like SqlLite or even maybe just a lightweight mariadb. I think for now SqlLite meets all of the requirements listed above and persists to file/disk.

Memory Usage

As for performance a database is most of the time a bit better in managing RAM/CPU than we are. Instead of loading all songs into memory and making them available for selecting one would just query the database, get the results and clear memory when selection is done. Then just load the song in memory incl notes. When song is done repeat. Shouldn't use lots of memory (< 200mb RAM)

[basisbit]

@Baklap4 I guess you didn't read all of this thread here. How would you add sqlite to this project and still be able to target all the various build platforms including Android, PlayStation, Xbox, and so on? One of the goals of UltraStar play is to be rather lightweight and not include too many different dependencies. We don't want to implement every last feature that a use might request, but instead keep the game very easy to use and very easy to configure.

Anyways, if whoever implements the code gets it working well for all the various platforms, I would be okay with either a serialization based solution, or a database based solution. Whatever works, wins.

[basisbit]

"Web frontend (locally) to make interfacing with Play easier." ... hey, this is what Performous is there for. UltraStar Play might eventually support network multiplayer together with performous, but will be the lightweight client for mobile devices and so on, not a replacement for performous.

[eldan-dex]

Given that the statistic entries have a DateTime element, no two entries can be the same, as it isn't possible to sing a song twice at the same time.

We have already had a discussion about SQLite and decided that it was too impractical to use in this case due to it being difficult to use for non-pc platforms in Unity.

I am not sure whether it makes sense to have a full application database. Why should we care about having a database of song information or logs? The first ones are stored in the song files themselves (and the only time we need to access them is when browsing or playing songs and a system for that is already present) and logs can easily be stored in a separate plaintext/serilaized logfile, making it easier to access and read for humans than a db file would be.

What you are proposing would significantly change the core of the game, as everything would start to rely on the database, instead of individual components working mostly independently from each other (as far as my understanding of it goes).

[achimmihca]

I agree.

Furthermore, (as I said before) I argue that we do not need a database because we don't have multiple users (in the game, a website is a different topic) that concurrently access and modify the same data. So there is no need for sessions, transactions, rollbacks, etc. which is normally provided by a database.

Object-relational mapping (ORM) would add a new layer to the application, which also adds to the complexity.

[achimmihca]

Regarding cross-platform: I think SQLite could be used on all platforms. An SQLite DB is stored as a file. Also, I found this blog post about SQLite and Unity.

However, I don't see a benefit of using SQLite over simple serialization of our data structure to JSON.

[achimmihca]

@eldan-dex

When is a good time to update the totalPlayTime, given that the number should increase each second? How often should this be written to disc?

I would update the totalPlayTime at application shutdown. Unity stores the uptime of the application: see Time.time. If we want to record the totalSingTime (i.e. time spent in the SingScene) then I would update this when leaving the SingScene.

Use default hashing of C# Dictionary (fast but only 32 bits) or replace it by something like md5?

I would use the default. Why not use it?

Possibly merge timesStarted and timesFinished into timesPlayed (=finished)

Please, keep them separated. I have some songs that I start and exit in the middle because the txt file is not well made. For me, having started a song multiple times but not finished it is an indicator for a not-well-made txt file.

[Baklap4]

Let's get some things clear;

1. SQLite is compatible but not limited to; Windows, Linux, iOS, Android 1a. Possible Playstation, not sure about xBox
2. Ultrastar needs to be kept 'lightweight'; SQLite has no external dependencies
3. There's currently only a Windows build available within the CI, with mac and linux coming up next i presume
4. Web frontend, sure scratch that but it's a possibility that's why i've listed it

Given that the statistic entries have a DateTime element, no two entries can be the same, as it isn't possible to sing a song twice at the same time.

Correct.

We have already had a discussion about SQLite and decided that it was too impractical to use in this case due to it being difficult to use for non-pc platforms in Unity.

Hmm as far as i can see you only need to setup a build system which is done just once and write with the api of SQLite at application level which is a uniform language, i don't see a problem here. The build system has to contain a build process for getting the correct library for the correct output per OS this might be a bit challenging.

I am not sure whether it makes sense to have a full application database.

Currently the application is based on 'in memory' allocation and de-allocation. I have no current knowledge of how it is implemented (browsing the songs). But I assume the meta tags are kept in memory (at all time as a singleton) to get some decent search speed and show songs and such. With a few hundred songs this is doable but getting into multiple of thousand of songs this might get troublesome for low-end devices. An improvement to this (within a database perhaps) would be to introduce some kind of paging system where when you browse you keep on getting the new songs. That's where my song information is coming from.

Regarding logs, you've got a point there.

What you are proposing would significantly change the core of the game, as everything would start to rely on the database, instead of individual components working mostly independently from each other

The core of the game is singing songs. The end-user doesn't care how the program is working underneath the hood except for it has to be fast, responsive, and available on all devices (assumed since there will be builds for all kinds of devices) thus not consuming too much battery (cpu), and slowing everything down (memory)

Furthermore, (as I said before) I argue that we do not need a database because we don't have multiple users (in the game, a website is a different topic) that concurrently access and modify the same data. So there is no need for sessions, transactions, rollbacks, etc. which is normally provided by a database.

I agree there's no concurrency at least not yet for the local game. Json seems very fast and easy since there's currently some basic models. However if you're going to need to link items to eachother for example a user, and there will be multiple of them (4 people singing the same time are 4 different players), to a highscore. I bet you a database is soon going to be a better and way faster option to use for searching, connecting, and getting a way lower memory usage. The database won't be used for storing sessions like within an web application, it'd rather just store the raw data, with links to eachother. And if the time is there for linking data to eachother will definitly perform better than implementing such a storage yourself 🤷‍♂

[Chase22]

Lets back down for a moment and see what we actually need to save for a high score. It boils down to:

• The song played
• The difficulty
• The time the highscore was scored
• The player that scored it
• How much points were scored

I don't know right now how the players are stored. But on a most basic level you just need a name. We could write a few json files, one per song with a structure like:

easy: [
  {time:"2019-11-30T11:00:52Z", player: "guest", score: 5124}
]
medium: []
hard: []

That way, when we need the highscore for a song we can load that file and parse it, saving memory. When a user finishes a song, we just add the high score to the file and save it. I don't think we need a database just for highscores since most other data is already saved as files.

We can then decide to switch the player for an id that we have saved the metadata to in memory and can easily display it

[eldan-dex]

If you take a look at my first post in this thread, I already mentioned this approach as a possible but probably not a desirable solution. While it makes loading and saving individual scores easier for the programmer, it creates a lot of new files making operations like moving the play database or just the songs without the database entries to a different machine quite hard to do. I will start implementing the structure I mentioned in my last proposal, taking into account all of your comments and suggestions, and see where I end up.

[basisbit]

Most of the features mentioned in this issue were implemented by @eldan-dex , so I'll close this issue for now. Further changes can be discussed in new issues, if necessary.