MCDzienny

SQLite is something I've always wanted to learn, but I've been lazy with it lol; however, I have this tutorial so you aren't lazy like me.

SQLite Basics
The first -- and most important -- thing about sqlite is obviously the namespace: "using System.Data;". Make sure to add that first, and reference it if you need to.

The engine you will be using for executing SQLite commands is Dzienny's DBInterface class.

The biggest part of coding has to do with the datatypes. In SQLite there are similar ones to C#, but there are also some not available in the .NET 3.5 framework.
Note: There are many other datatypes and datatype configurations, but these should be enough to do plenty.
Integers

TINYINT : 0 - 255
SMALLINT : +-32,767
MEDIUMINT : +- 8,388,608
INT : +- 2,147,483,648
BIGINT : +- 9,223,372,036,854,775,808

Strings

CHAR : best for values that will always be the same length e.g. cat, bat, poo, dog, etc
VARCHAR : best for values that vary e.g. cat, werewolf, watermelon, hi

Bools

BOOL : 0 = false and 1 = true

This is really all you need to begin using SQLite.
Let's practice them.

Let's say we wanted to create a database slot for a player's pet.
This is what we would do.

Code: Select all

DBInterface.ExecuteQuery("CREATE TABLE if not exists `"+p.name+"pets`(name VARCHAR(12), hunger TINYINT, happy BOOL);");


So let's analyze what has been laid out.

Certain words must be CAPITALIZED or else it will mean something else to SQLite.
The ` character symbolizes the table's name.
Unlike C#, the name of a datatype comes first
SQLite strings have parenthesis depicting the max chars available
TINYINT and BOOL cannot have parenthesis because it is a set length already
The command ends with ;, but that is not true for all commands

Ok, well now we have a table for a player's pet.
Let's put it to use.
How about we actually insert something.

Code: Select all

DBInterface.ExecuteQuery("INSERT INTO `"+p.name+"pets` (name, hunger, happy) VALUES ('deathmaster900', 0, 1);

Nothing too different.

You can see that when defining a string, you need to put ' around the value or else it will return an error.
VALUES must be put between variables and values
Not all variables need to be defined, you could also do (name, happy) VALUES ('deathmaster900',1);

That's a lot so far, but a vital part of databases is updating them.
Looks like deathmaster900 has unfortunately become unhappy with us ):.

Code: Select all

DBInterface.ExecuteQuery("UPDATE `"+p.name+"pets` SET name='happyhugs39', happy=1, hunger=0 WHERE happy=0 AND name='deathmaster900'

We have a new pet and some new usages

Update statements must always use the SET qualifier to begin updating.
Commas seperate update values, and not all values have to be updated
The WHERE statement searches through the pets for a specific pet
the AND statement shouldn't be hard to figure out.
Plus, no semicolon ;

Now, if you wanted to do something with pets, you could select it.

Code: Select all

using(DataTable table = DBInterface.fillData("SELECT * FROM `"+p.name+"pets` WHERE happy=1")
{
  p.SendMessage("Unhappy pets");
  foreach(var row in table.Rows)
  {
    p.SendMessage(row["name"]);
  }
}

New Stuff:

The using clause is pretty much a DataTable that disposes itself when done
SELECT * FROM is standard usage for find
The code creates a new datatable that contains the pets in rows
To get information from a row you use brackets [] with quotes "" and the variable name

This should be good for today, and let me know if there is any errors in here. I did not test for time sake .

Really usefull! I didn't know any of it yet!
Also, reminds me of this joke:

Code: Select all

SELECT * FROM users WHERE IQ > 60
0 rows returned

Very nice article, I just used SQL for my university project, and I still find the basics difficult So I have no doubt this will be very helpful to a vast majority of people!

There is a lot of useful information about the SQL syntax and features here.

I noticed that the presented table creation method is related to the actual way MCDzienny database is structured. Unfortunately, the current structure should be improved, because it's far from perfect. Changing MCDzienny db scheme would be a pain with very little reward. Hence, it won't happen anytime soon. But, in the presented case, I recommend the more correct database scheme that includes one or a few tables for all user/pets instead of one table per user.
Still, it's a very nice article