The online racing simulator
[Dev Discussion] Databases
(5 posts, started )
#1 - Krayy
[Dev Discussion] Databases
I thought it would be a good idea to create a series of threads that can be used for LFSLapper Development Discussions where aspiring or contributing developers can raise issues or suggest potential improvements or additions and get feedback from other devs to help move the process along.

Please note that most of these discussions will be targetted at Developers who are familiar with the Lapper source code and have a working knowledge of its internals and C#. These threads are not the place to ask how to run Lapper or download it etc.

You can however, contribute if you feel that your post has relevance to the topic at hand and can be deemed of use, i.e. a suggestion on how to improve a feature that we are discussion or how it can be integrated into scripts that you are writing.

I thought I'd start with the subject of Databases...

SQLite is a great tool for local storage. Lapper can export .eps files to make data imports a little easier, although for real time web based stats displays it does have its limitations.

To that end, I have been looking at integrating suport for MySQL as an optional addon for Lappper. Essentially there would be branching code to support a number of different database options. This would make it possible to keep using the local SQLite database, while at the same time connecting to an external MySQL database and storing data there as well.

So you could write to a local SQLIte db, a remote MySQL one or both at the same time.

It's quite a bit of work, so I need to ask if this would be a feature that people would actually use? If so, then I'll work out a schema and what kind of data would be stored.
I think that one of the most obvious things would be admin account information to be stored within a MySQL or SQLite database. On the MySQL side, I would use the InnoDB engine as it supports FOREIGN KEY referential-integrity constraints, witch helps to make polymorphic associations possible within the database using a trigger. This become really very important when talking about the actions that admins can perform, and making sure that while you can delete an action that an admin can do from the database, you can't corrupt the database by deleting an admin first, before deleting the actions they can do. So that's a pretty powerful system in and of it's self.

That said, if your looking for maximum capabilities between SQLite and MySQL you could simply use the csv storage engine as that's really what SQLite uses by default. That would make the databases directly compatable with each other, meaning that you could directly build one from the other should something happen to one, but not the other.

Again on the MySQL side, the FEDERATED storage engine could be used to connect to an outside resource, such as your SQLite database to store information remotely from one command. I don't know if they have a proper implementation for this as I can't find and documentation for that fact, but it's an interesting concept to look into.

I think, above all, I would still use InnoDB for the MySQL Side of things, and I think I would use MEMORY on the SQLite local side of things. But I have to ask, why have two database connections in the first place?

--- [ EDIT ] ---

Actually, I'm strongly behind InnoDB. This is particularly important from the stand point of you and I. It stops lesser programmers, or lesser database admins from breaking shit! And that's hugely valuable when your distributing your environment to other people. It's the "No, no, you shouldn't do that." that I would of told to dev if they came to me, but this time, it's being enforce on their machine with the use of FOREIGN KEY referential-integrity constraints.
#3 - Krayy
SQLite is a DLL and doesn't have a listener, so it wouldn't be able to be linked directly to a MySQL db. Essentially they would both be standalone.

As we could support both at the same time, this would mean that if there are apps that use the .elp files, they would remain the same. The MySQL db gives us the opportunity to have a web based front end that is referencing real time race and user data, sort of like what CTRA did.

The schema and engine would need to be worked through to see what features and data we want to cappture and support.

I for one want to not only store race data, but also capture a number of races and a qualifying session as an Event, so I would want to add tables to support that.

I have a coilleague who was keen on writing a front end to display the race data and I know that there are a few other things out there like LFSStats and Gai had a web frontend that I managed to get going locally a while back, so we can pull together a lot of samples to start with.

Which begs the question, what sort of stuff would you want to see in web based stats displays?
Quote from Krayy :Which begs the question, what sort of stuff would you want to see in web based stats displays?

From a FiA - FOM stand point, I would want:
Position, Player Name, [Sector Time, Best Speed in Sector][x 1 - 4], Lap Time, Delta to Car Behind.

For a car nut / status nut point of view, I would like to be able to click on a sector time, and see the other times posted by that client over the course of an event. For example. In qualifying, I would like to see WHEN I did my fastest sector, and a history of other sector times that I did. This could take the form of the color of the text changing the display if I set a personal best (Green) or a session best (Purple).

The reason i haven't implemented MySql is the fact the standard end user can't install mysql database in a easy way on his computeur.

But you can implement both but is it the priority? How many user can do that?

However it's a good idea!


[Dev Discussion] Databases
(5 posts, started )