Creation/Dev/Gscript/Using SQLite

This article is currently being written or rewritten by Chris Vimes.

Preamble

Target Audience

This guide will not teach you how to script. It is directed toward already-competent scripters who wish to incorporate SQLite into their scripts.

Required prior knowledge

You should have:

• A basic understanding of GS2
• A basic understanding of storing data in traditional ways (flatfile)
• A basic understanding of the way tables work (for example, Microsoft Excel or Access)

Introduction

What is SQLite?

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

In English, it is a script that stores data into tables, the way any SQL language works (e.g. MySQL).

Why use SQLite?

There are advantages and disadvantages to using SQLite for storing data. Like any data storage method, there are times where it is practical to use it, and times when it isn't so practical.

The main advantage to using SQLite is that all of your data is stored in a central location. This allows you to compare the data quickly and easily, without a lot of extra code.

This isn't going to make a lot of sense until later, but let's pretend I have a table with two columns: "account" and "hours". If I wanted to figure out which players have more than 5 hours, I can simply run a query like this:

SELECT * FROM 'players' WHERE hours > 5

Using traditional storage methods, such as flatfile, the file might look something like this:

(npcserver)=6
Stefan=10
unixmad=3

In order to figure out which of these players has more than 5 hours, I would need to do something to this effect:

temp.file.loadVars("data/file.txt");
temp.pls = {};


for (temp.pl : file.getDynamicVarNames()) {
  if (file.(@ pl) > 5) { // has more than 5 hours
    pls.add({pl, file.(@ pl)});
  }
}

The equivalent code for a properly organized SQL table might be:

temp.pls = requestSQL("SELECT * FROM 'players' WHERE hours > 5", true).rows;

In essence, SQLite is a fast, easy, and organized way to store your data.

Preparation

Getting started with SQLite is extremely easy, but for the purpose of this tutorial, I'm going to point you toward a tool that will help you greatly. Before that, though, you will need access to a server where you can learn. If you don't already have access to some server, you can sign up for the Testbed Server.

If you aren't using the Testbed Server, you will need to upload a tool to help with this tutorial

• Go to this forum post and download the text of the "SQL Explorer".
• Create a new weapon, and name it "Tools/SQL Explorer" (or any other name). Paste the text of the SQL explorer in here.
  • Before you save the weapon, turn off rights control (around line 30; set it to 'false'). This will be discussed later on.
• Add yourself the weapon

If you are using the Testbed Server, just add yourself the weapon "Tools/SQL Explorer".

Getting Started

How is data stored?

Data is stored into tables. Like any table, these tables have columns and rows.

Columns are like the table headings in the table below. The rows are under the column headings. Notice that each row has a piece of data in each column. One row has a piece of data for each column in the table.

account	hours
(npcserver)	6
Stefan	10
unixmad	3

When you ask SQLite for information, it will give you an array of rows. These rows each are broken up into the various columns. For example, if I were to ask for all of the data in the table above, it would give it to me in a format equivalent to this:

{
  {"(npcserver)", 6},
  {"Stefan", 10},
  {"unixmad", 3},
}

How do I tell SQLite what to do?

To tell SQLite what to do, you pass commands as strings, rather than using a set of functions. These strings are called "queries". The query contains a set of functions that tell SQLite what to do. Queries start with the base command, and are followed by command-specific syntax. As you use SQLite, you'll become more accustomed to how it works.

There are security issues associated with user input in queries, which are discussed later in the tutorial. For this reason, you should not start using SQLite for actual content until you understand the risks and how to avoid them.

First steps with SQLite

Creating your first table

You have to walk before you can fly, so when we start with SQLite, we won't even be using Gscript. Log on to your testing server (be it Testbed or whichever server you plan to learn on), and press "N". Assuming that you added the SQL explorer to yourself correctly, there should now be a blue window with two tabs at the top on your screen. Depending on whether the server already uses SQLite or not, you may see a list of "tables". Ignore them, and instead click on the very top-right tab that says "Execute SQL". You should now see this window:

In order to start with SQL, we first need to have a table to place information in. The SQL Explorer utility you're using has a rudimentary table-creation feature, but you won't learn by using that; just know that it exists in case you ever need it.

We're going to start forming the query to create the table. SQLite, and most forms of SQL, tend to work exactly how you would expect them to using basic English. So, to create a table, we start with the query command

CREATE TABLE

This command is followed by the table name. In MySQL and some other forms of SQL, the table name is always preceded and succeeded by a back quote. In SQLite, you do not have to surround the table name with anything, but you may surround it with single quotation marks. You do not have to, but for the purpose of this tutorial, I will. The name of the table goes directly after the query command, like so:

CREATE TABLE 'players'

(in keeping with the spirit of this tutorial, we'll continue to work with the account/hours table)

Before this query is ready to be ran, we need to create some columns along with the table. To do so, we add an opening parenthesis ("("), followed by the name of the column and the column type, and then finishing off with a closing parenthesis (")"). The query could now look like this:

CREATE TABLE 'players' (
  account TEXT
)

(note: Indentation is personal preference and is only used for readability; you can write the whole statement on one line and it will work the same. This applies for all examples in this guide)

You'll notice that I named the column 'account', following with the example, and also that it is of the "text" data type. Don't worry about this too much now, as we'll go over the data types and how to use them later.

To add another column to the table we're creating, we can add a comma before the closing parenthesis, and then another column definition.

CREATE TABLE 'players' (
  account TEXT,
  hours INT
)

Again, don't worry too much about the "int" data type - it stands for integer, as for this example, we will only be placing integers in the column.

Paste the completed CREATE TABLE query into the execute pane of the SQL explorer, and click "Execute Query".

If everything worked correctly, you should see "CREATE query completed successfully" in the console (if you don't, check your query with the one above). To see our new table, click the "Data" tab at the top of the window, and click "Refresh" at the bottom of the screen. This will re-load the list of tables. You should see one named "players". Double click on it to view the table.

Notice the column headers at the top of the table. The careful observer will also notice "rowid", which is a default column that SQLite creates for all tables. Each row you insert will be given a unique ID. Don't worry about this column; you won't be using it until later, if at all.

Click the "-" button in the top-right corner to close the tab, click on the "Execute" tab at the top, and we can start adding data to our table.

Filling the table

To insert a row into our table, we use the query command "INSERT INTO". This is followed by the name of the table.

INSERT INTO 'players'

We then tell SQLite which columns of the row we will be giving it. This is useful if you want to specify only certain data (for example, "account" and "hours") and don't want to put data for some other column. You'll understand this later.

To specify the columns, you create a comma-separated list in parentheses.

INSERT INTO players (account, hours)

Now, you need to specify the values of the columns. Make sure to follow the same order for values as you did for columns. These should be enclosed in parenthesis, just like the column names.

INSERT INTO 'players' (account, hours)
  VALUES ('Stefan', 10)

I did a couple of things:

• I placed "VALUES" after the column listing, which specifies that the following list is for values. The reason to do this is because it is also possible to insert without specifying column names, but this is normally a bad idea.
• I enclosed "Stefan" in single quotations; all strings in SQLite (and most/all other SQL languages) must be enclosed in single quotations.

Place the completed query into the query field, and run it. Now, try to insert these two rows:

account	hours
(npcserver)	6
unixmad	3

If you're having trouble, look at the syntax used for the last one. It shouldn't be hard at all if you're really already a competent scripter.

Once you've done that, view the "players" table again (click the "Data" tab at the top, then double click "players"). Your table should look like this:

rowid	account	hours
1	Stefan	10
2	(npcserver)	6
3	unixmad	3

(in the future, I won't be including "rowid" as a column because it is assumed)

Getting the data

Now that we know how to fill our tables with data, we can start learning how to get this data out of the table. To do this, we use a query statement called "SELECT". The basic SELECT statement looks like this:

SELECT [columns] FROM [table]

This will give us all of the rows from the specified table. We can specify certain columns from the table, like so:

SELECT account FROM players

When SQLite returns data to Gscript, it returns it as a multi-dimensional array. However, it can easily be drawn out as a table, as the SQL Explorer does. Click over to the "Execute" tab and enter the command above. You should see the following result:

account
Stefan
(npcserver)
unixmad

To select all of the columns from the table, we can do this:

SELECT * FROM players

which returns a table like this:

account	hours
Stefan	10
(npcserver)	6
unixmad	3

This is great, but not very useful. Normally, we'll want to limit the data somehow. For this example, we'll only get the rows of accounts that have at least 5 hours. In order to do this, we use a "WHERE" clause. This is appended to the original SQL statement, like so:

SELECT * FROM players
   WHERE condition

In order to select from rows with more than 5 hours, our condition would be "hours > 5", which leaves us with a query like:

SELECT * FROM players
   WHERE hours > 5

Go ahead and run that in SQL Explorer. It should return this:

account	hours
Stefan	10
(npcserver)	6

Notice how "unixmad" was excluded because he only had 3 hours. We can also add more conditions using the "AND" operator.

SELECT * FROM players
   WHERE hours > 5
      AND hours < 10

Run that query, and the result should look like this:

account	hours
(npcserver)	6

Both Stefan and unixmad were excluded since they didn't have between 5 and 10 hours.

Appendix

Data Types

• NULL
  • This is a NULL value in a table. You won't use this very much.
• INTEGER (also INT)
  • A number with no decimal (-1, 0, 1, ...)
• REAL
  • This is real number, like a float. It can contain decimals (e.g. 3.1415), integers, and most other numbers
• TEXT
  • This is a string.
• BLOB
  • You probably won't ever use this with Graal. It stores the data exactly as it's entered, and doesn't tamper with it.

Numerical Comparison Operators

Most of these are the same as used in Gscript and other languages.

• <
  • Less than
• <=
  • Less than or equal to
• >
  • Greater than
• >=
  • Greater than or equal to
• =
  • Equal to (remember not to use '==')
• !=
  • Not equal to

Text Comparison Operators

Most of these are the same as used in Gscript and other languages. Note that these are case-sensitive.

• =
  • Equal to (remember not to use '==')
• !=
  • Not equal to

External Links

• SQLite documentation site
• SQL Explorer forum thread
• SQLite Database Browser (cross-platform SQLite browsers; opens the 'main.db' file)