MISSQL Command is a an interactive game and tutorial meant to teach basic SQL in an engaging way. The player plays a game inspired by Missile Command with SQL queries, defending a city from incoming missiles.

#What and why?

SQL is an incredibly successful and solid technology. The problem with SQL is it seems everyone hates its guts. It is a weird obtuse kind of "non-language" that most programmers can't stand. -Zed Shaw, Learn SQL The Hard Way

As Zed Shaw goes on to explain, SQL is everywhere and it is important. Yet, while there are plenty of interactive online resources for learning Ruby, Python and Javascript, boring old SQL is neglected. No more! While Missql Command will not make you a database administrator, it will introduce you to the basic concepts in relational databases in a way that is engaging, friendly and dare we say fun?

#How it works Missql Command is built in a way that is modular and extensible. Levels are loaded from yaml files and are easy to create and update.

###Users and Databases Each user has their own sandboxed Postgres database with which queries are run and then rolled back. Whenever a level is loaded their database is populated with the tables and data for that particular level. If a user is not logged in, a guest user is created and stored for the session.

###The REPL The Missql Command REPL sends an ajax request with the user query directly to the user's database, and returns either a result or an error. To prevent the user from corrupting the level, all transactions are rolled back after being checked for correctness. In cases where there is no return value (such as table creation), the table columns are rendered.

#Creating & Editing a MISSQL Command Level Levels are generated using YAML files that describe the level's attributes in a nested data structure, and SQL files that contain the commands necessary to generate the level's database. The YAML file also contains attributes for nested resources like level_pages, level_tests, and level_schemas, which are described below. The db:seed, missql:reset and missql:reload_all tasks will pick up any new yml files in the levels directory. There are also several rake tasks that assist in level creation and editing:

• missql:update_all Updates existing levels from their yaml files.
• missql:reload[n] takes a stage number as an argument and reloads that level from the yaml file. Destroys the original level.
• missql:reload_all drops all levels and reloads them from yaml.
• missql:reset resets the entire missql database (for use in development only) and rebuilds it from the seeds file.

##Attributes Of A Level:

• stage_number (integer): The ordinal number of the level, independent from its id. Used for ordering/labelling levels in the interface.
• title (string): The display title of the level. Should be short and punchy, max 50 characters.
• prompt (text): A short, direct description of the goal of the lesson. Max 150 characters. HTML formatting allowed.
• database_path (string): Path to the database dump for this level. Path can be absolute, or relative to the root directory of the app. Dumps are generally stored in db/dumps.
• answer (text): Used by "read" type levels to check the correctness of a result set. Should be empty for non-read levels. Generated by getting a correct pg result object in the rails console, then calling .to_a.to_s on it.
• default_text (text): This attribute is used to store text that should be displayed in the REPL on level-load, if the level needs it. Generally, this would be the results of the correct query from the previous level, and should be formatted as such.
• type (string): Used by execute to switch both answer checking and output generating logic. The types are:

• read: Used for SELECT and JOIN levels.
• drop: Used for DROP TABLE levels.
• insert: Used for INSERT
• update: Used for UPDATE levels
• alter: Used for ALTER TABLE levels
• delete: Used for DELETE levels

• correct_query*: This attribute is only stored in a level's YML file. It provides the canonically correct query for that level for use in development and testing.

##Relationships of a Level: The relationships marked with a * must be defined in the level's YML. A level:

• has_many level_pages*
• has_many level_tests*
• has_many level_schemas*
• has_many user_databases

###Level Pages: Each level page is a pane of instructional text for the level. Level pages are HTML formatted. Each block of text (1-2 sentences or a code block) should be wrapped with a <p> tag. Inline code should be wrapped with <code>. Code blocks should be wrapped with <pre><code>. SQL terms should be formatted as inline code. SQL terms should be italicized using <em> tags. Placeholder text like table_name or column_name in code blocks should also be italicized. A page can have an arbitrary ammount of text, but 5-6 paragraphs or 450 characters is ideal.

For each level page you will need the following in the YML:

• page_number: integer
• content: html formatted text, surrounded in double quotes. Double quotes inside content must be escaped.

###Level Tests: Level tests are used by levels to assess correctness and provide playre feedback. They check either the state of the user_db or result of a query, depending on the level type.

For each level test you will need the following in the YML:

• test_query: string
• expected_output: string
• error_message: string

For write levels, each one is essentially a hash with a query, an expected result, and a message to send back if the results don't match. The expected result is the string representation of the array format of a PG result object.

For a read level, the test_query references a LevelTest method, and expected_output is the expected result of that test.

##Level Schema: For every table in a level's dump, a level schema object should be registered on the level. The level schema itself only stores the name of the table, and has_many schema_columns. Each schema column contains a key value pair of the column's name, and the columns type.

For each level schema you will need the following in the YML:

• table_name: string
• schema_columns
• column_name: string
• column_type: string

##Prose Style In level pages, use the second person and the present tense i.e: "You now know everything there is to know about SELECT. Try it yourself now." For conditional instructions use the future imperative, i.e.: "To make a query of type x you would write y"

##User's Character The user is a hybrid missile commander / database admin. In the world of MISSQL command, nuclear arsenals and city defense systems are managed with relational databases. The user should be assumed to have NO knowledge of SQL or other programming languages. Try to imagine you're explaining SQL to Harry Truman.