Hi again my Peeps!!
Last we were together, we learned about the SELECT statement and how to cover yourself in order to protect the innocent (and the Data). We learned what data might be stored for the Space-Capture-the-Flag Game. Today we'll be discussing the creation of some of those tables as well as how to Manipulate the data.
Is this IKEA or a Database Management System?
Now, I've already went over the fact that the tables we will be using are different than the tables you might have to use for meal-times. These tables store information and cannot hold food (unless the food is virtual and in the form of 1's and 0's). Our game tables will hold great amounts of information and I am probably not even considering all of the details that will be needed since I've never tackled a project quite this large before. The beauty with SQL Tables, though, is that I can create an updated table with new columns for unexpected data items and then copy in the old data (with caveats) and renaming/swapping tables.
Here are the tables that we decided to start with:
Tables:
space_ships
space_ship_addons
star_map
team_stats
player_info
weapons
materials
location_types
shields
communication_logs
error_logs
achievements
leaderboard
The Keys to the City
This is the perfect time to discuss Primary and Foreign Keys at the surface level. Primary Keys are data elements that are necessary in order to have a data column. As an example, a spaceship as it relates to the data, would need an ID of sorts. All of these keys are needed in order to flesh out the data properly and store it so that you eliminate problems with duplication and redundancy. Duplication can spell disaster when it comes to a game... you don't, all-of-a-sudden, want your game to look like you have double-vision by having multiple spaceships popping up on the screen all doing the same thing (unless it was a Power Up, of course). In addition, redundancy can ruin the experience of playing the game because the more places that you have to UPDATE in the Database, the slower the queries will become and the more risk of failure will be placed on the system.
Primary Keys can be a single, unique column or a set of columns that, together, are unique and required. For example, a Student Record at a school might be a unique ID that pertains to a single student only; or a Student Record could be a combination of First Name, Last Name, Grade, Teacher (as long as you never have the same-named child in the same Grade with the same Teacher... this is where you really need to think of the word unique when creating Databases and Tables).
Foreign Keys, in contrast to their Primary cousins, are fields in one table that link it to another table. These should always be using the same data type, be a number or text... doing so will ensure strength of data. Without doing this, you might unintentionally try to pull up the power of your weapon as a percentage (0-100) only to find that it was stored as text and accidentally pulls as a letter (G) and then either causes an error in the game or makes it default to zero (powerless). That would be a horrible gaming experience that I'm sure we've all had (glitches in the game). Appropriate usage of Foreign Keys will ensure that a tight ship is run.
Yoga for the Data: Let's Manipulate Things!
INSERT: The Insert statement is a great way to add new datarows into a table. There are a few ways to push up multiple rows simultaneously, but I'm not going to go into all of that right now... I will teach you about that another day. For now I'm going to focus on the basic INSERT statement so that you can, at the least, start adding individual rows of data. There are also other components that we will not be discussing here (indexes in particular) that need more focus than this specific post.
The situation: Add a row into the login_table table for the game, which uses an Index field called ID (which is the Primary Key), a username and password field as well as a Foreign Key field called ship_id that corresponds to the ship_id field in the space_ships table.
The INSERT statement, in a nutshell, tells the system to INSERT INTO a specific table by name using a list of fields (excluding Indexes), specifying the VALUES that you need in a corresponding list of data. Following is a very simple example:
INSERT INTO login_table (username, password, ship_id) VALUES ('test_gamer', 'test_pwd', '')
I tested it out and it worked great. Remember, always use the BEGIN TRAN and COMMIT or ROLLBACK statements.
UPDATE: The UPDATE statement updates data after the rows have already been added. To use this, you write a query that begins with the UPDATE statement, specify the field(s) that you are going to SET new information into (along with what the new information is), as well as the WHERE clause to filter to the appropriate data. With the UPDATE statement, it is vital that you use the transactons method to protect yourself from updating incorrect rows of data. It would really suck, BAAAAAAAD, if you ended up changing all of the users to the same password because of a poor UPDATE statement. The below example shows me updating the test_gamer user with a new password to testing123.
BEGIN TRAN t1
UPDATE login_table SET password = 'testing123' WHERE username = 'test_gamer'
--COMMIT TRAN t1;
--ROLLBACK TRAN t1;
** I ended up using the ROLLBACK after verifying the data changed b/c I wanted to keep the previous password.
DELETE: The DELETE statement actually removes a row from the Database. This is the case where you need to never use this statement unless you use transactions. I'm going to do the same thing as I did with the UPDATE statement in order to remove that user (but I'm going to use the rollback statement in order to not allow the DELETE to actually go through). Following is the DELETE statement:
BEGIN TRAN t1
DELETE FROM login_table WHERE username = 'test_gamer'
--COMMIT TRAN t1;
--ROLLBACK TRAN t1;
** Again I verified the data and did the ROLLBACK.
I CANNOT STRESS THIS ENOUGH NEVER TO WRITE ANY OF THE QUERY TYPES FROM THIS POST WITHOUT USING THE "BEGIN TRAN", "COMMIT" AND/OR "ROLLBACK" STATEMENTS TO PROTECT YOURSELVES!!! IF NOTHING ELSE STICKS, LET IT BE THIS!
SQL-Forbeginners Posts:
sql-beginner-s-tutorial-writing-your-first-queries
sql-beginner-s-tutorial-relational-databases-and-sql
sql-beginner-s-tutorial-select-data-to-use-in-the-game
Posted on Utopian.io - Rewarding Open Source Contributors