Gran Turismo Tunes DB / software project

[Obli]

Hi all,

I wanted to show my Gran Turismo Tunes DB / software project to you all. I've been working on it the last 1 - 2 weeks, and I've had to learn a lot! For example, I learnt a bit about database design at Uni (as part of my Comp Science Degree) but have never had a good reason to apply what I learnt... I knew (in theory) how to design an SQL database to third normal form, using EER diagrams and a conceptual-to-logical mapping process, but I have never had to do it in reality... the practical side!

So, my project... Well, it's not in a finished state, but I can show you all what I have working so far. I have installed XAMPP for Windows that has allowed me to turn my PC into a webserver (with Apache and MySQL); another learning experience! I have designed and created the database, and have made a good start on the user interface and server-side programs (servlets). The front-end is a Java applet, which can be integrated nice and easily inside a web browser. Apparantly, there are some advantages to Java applets in comparison with PhP (if I recall: true OO, better support for threading, a mature DB API, better security) but I don't really know as I don't know PhP / HTML / XML... so for me, it could only be Java (or C / C++).

Anyway, the design is as follows:


Java applet <----------> Java servlets <----------> MySQL database


The applet is downloaded when a user goes to the web page where it is hosted. This is the GUI (the 'front-end').

The applet communicates with the servlets. The servlets do the work of interfacing with the DB, i.e. adding data, retrieving data, handling connections and synchronising access. If I recall correctly, an applet can only access resources on the web server it is hosted on, so I decided to use the above three-tier design. This design means that the web site (page) hosting the applet and the database can be on different servers - much more flexible.

Some screenshots (click to enlarge) and a walk through:



1) Logon screen - the code is in place to check if the user name and password exist in the database, and returns the result (there is a feedback label that works - the screenshot shows a valid user name and password) but at present I'm not sure what to do with the permissions (more on that later).




2) Here is the main 'home page'... the 'Refresh List' and 'Add Tune' features are working. 'View Tune' does retrieve the selected tune info from the database, but I haven't written the code to have it populate a window with that information yet. Shouldn't be too hard as I can use the same window as the one used to add a tune. Anyway, the list can be sorted ascending / descending, and the columns adjusted for width, and can also be re-ordered (drag and drop). I decided to keep the main tunes list simple, so that all tune data for every tune in the database wouldn't be retrieved every refresh. So, click 'Add Tune'...




3) ...and up pops this window. This is all working. Select a manufacturer from the drop down list, and only the relevant models for the selected manufacturer are shown. Same for game and version; why I did this I'm not sure now... tuning for GT5 is likely to be different, and I know GT4 is different. There is also a list of tuners. All this info is retrieved from the database on-the-fly so it is entirely dynamic (no need to Java change code, just add a new model, manufacturer, game version (spec IV? ) to the database and that info will appear in the applet).

I've used a Dutch Customs tune in this example... so I've filled out all the info in the third screenshot. I've tried to make this as user-friendly as possible. i.e., you cannot enter a 7th gear value until the first six are populated. One can enable areodynamics and F/R torque distribution for the relevant cars, otherwise they can be left greyed-out (blank). I've used my basic tuning experience to apply sensible upper and lower limits to the values so users cannot enter ridiculous data. In this example, the 'suitable tracks' drop down list is currently empty. I can add suitable tracks for this tune by clicking the '+' button next to the list...




4) ...and we get a new window with a tracks list and adjacent check boxes... pretty self-explanatory. Eagle eyes may notice an extra track in there. This tracks window is created dynamically depending on the tracks in the database, so I wanted to add a track to see how it worked. I might need to rework this for a load more tracks, but it works for Prologue + some extra tracks (should we get any). So, I select a track (Daytona Road only in this example, but it works to choose multiple tracks, or all of them) and click the OK button, and the track list window closes...




5) ... and there is Daytona Road, added to the suitable tracks drop down list. With all details filled out, I click the 'Add Tune!' button and the add tune window closes...



6) ... click refresh again, and there is the new tune added to the database!


So, that's it so far. I'm not sure what to do with logins / users / passwords at present. Really, it would be best to encrypt passwords. I also need to improve a few things (add database connection pooling, ensure the servlets are thread-safe and improve the code). I'm not even sure how it would cope with multiple users. The code is a tad messy in places and should be refactored!. I also need to get the view tune feature working, but that should be fairly straight-forward.

I was thinking that a way this could work is as follows... Anybody could use the program to view tunes. Users that exist in the database can log in and may add tunes. Users that belong to a tuner (or many tuners - the database has been designed with this in mind) will have the choice of tuners they are members of in the add tune window. Users that can add tunes but are not members of a tuner would have a default 'user tune' for the tune's tuner name.

Perhaps I am getting ahead of myself a bit! I have this system working locally on my PC but I haven't got a clue how to host it for a public test. If GT Planet would eventually like to use it, I'd be happy with that. A proper server with 24/7 access for the general public would be ideal. If I get this running up on my PC with public access, I cannot run it indefinitely, or 24 hours a day! Really though, I have taken on this project to keep myself busy (I am unemployed at present), develop my CV and apply some of the stuff I learnt at Uni.

I'd love to hear ideas and suggestions. The main things I'd like to know is:

1) Would you use this?
2) Is there anyone that can help me get this up and running for the public to test out? (I'm clueless about web development, but I could probably figure something out!)

It's not ready yet... might be a fair few more weeks as I have a busy week this week coming (two weddings!) and I certainly wouldn't be comfortable releasing it as it is. This has been a big learning experience for me and I've had to learn about applets, servlets, MySQL, PhPMyAdmin, brush up on SQL statements... etc.! All good fun though! 👍

EDIT: I hope Dutch Customs and The Lions Den don't mind me having used a couple of your tunes for data entry... I will remove them if it is a problem (I should have asked first - sorry!)

 

[Chadwicksracing]

That looks great man..Just looks great!

 

[PASM]

Amazing! Keep up the good work.

 

[Japspecvr6]

Good stuff. Can't wait to use it.

 

[JvM]

Looks really nice buddy! 👍

 

[Angerdx]

Wow, awsome work man, just had a feeling you would get it done...

Thanks for doing it!

 

[nk4e]

Amazing work man!

 

[Obli]

Thanks for the positive comments!

I haven't had a lot of time to progress since the first post... the main addition is the ability to view a tune. One just highlights a tune from the main list and clicks the 'View Tune' button and up pops the window (nearly an exact same version of the 'Add Tune' dialog box).

Things to do:

+ Ensure the servlet code is thread safe (accounting for multiple users simultaneously performing viewing and adding operations). I've been reading up on the options and have a good grounding on what I need to do.
+ Finish the log-on / user system. There's a fair bit to do here. The DB side of things is accounted for, as is the logon GUI and the actual user check with the database. I'm still not sure exactly how to design it...
+ Implement database connection pooling - rather than each user interaction with the database creating a connection each time, there is a pool of connections available as and when required. This speeds up operations (creating a connection can take a second or two) and should reduce the load.
+ Add the ability for a user to rate a tune. Again, the database has been designed to store tune ratings, and the avergae of a tune's rating(s) is already calculated and displayed in the last column of the tunes list.
+ (Ultimately, ) I'd like users to be able to customise tune searches. The list is workable as it is. For example, retrieving the tunes list, sorting by manufacturer then by PP does sort the tunes in the order you'd expect... manufacturers in order with ascending / descending PPs... but, I'd like more flexibility. Perhaps, searching by track, filtering out tunes with a PP over 650, etc. This will be a little more challenging, depending on how flexible it is.
+ (Depending on popularity, ) Perhaps a way for admins to manage more functionality, e.g. deleting tunes, users etc. (instead of doing it with, for example, the database interface MyPhPAdmin). I even thought that users could request to become part of a tuner, and the tuner owner(s) have the ability to manage this. There are a fair few possibilities!

I'd like to try and get something publically available within two weeks (but don't hold me to that - busy times!). If anyone has a web server that is operating 24/7 and would like to help out with public testing, please let me know. The server would need to host a MySQL database, the applet, a web page pointing to the applet, and the servlets (the web server would require Apache Tomcat, or some similar implementation for running Java Servlets). Ideally, GTPlanet would be interested... Perhaps I should ask!

Failing any of that, I'll get it up and running on my web server (my PC) for testing purposes. That might be the best idea initially!

Cheers all!

 

[Icyw1nd]

This looks really cool, any chance you could make a simpler version of this for offline use perhaps? I love the idea with this. I would totally use it.

 

[NLxAROSA]

Concerning thread safety: just make sure you don't use instance variables to hold user data in your servlet, local variables only. Then you will be safe. Only use instance variables for application components (like DAO's). Variables defined inside a method will be local to that method (e.g. each call will have it's own variables).

Connection pooling/transaction management I never code myself. I use the Spring framework in combination with Hibernate for transaction management and database access. Have Tomcat do the connection pooling for you.

I have a dedicated server BTW, setup with Debian/MySQL/Apache2/Tomcat/Java6. I'd be quite happy to help you test it. I have 2000GB/month of bandwith, that should be enough.

Are you going to run it using Applets? I'm working on a similar project using Apache Wicket for the frontend.

 

[Obli]

Icyw1nd: no plans for an offline one, but I do plan to have a means to mark / filter by favourite tunes.

NLxAROSA: Thanks for the tip. I had read elsewhere that avoiding instance/member variables and strictly using local variables is one solution and the one I preferred (synchronise would cause bottlenecks). I'd love to take up your offer of hosting this... perhaps we could work together on it in same way? At present, I have the MySQL database, the front end is a Java Applet - both interfaced by the Servlets. I shall try to complete the login/sign-up code today. I'd love to get a public beta going

I'll pm you my contact details NLxAROSA 👍