Creation/Dev/Gscript/Using SQLite

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 Skills and Knowledge

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

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 and less then 10, 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 = req("SELECT * FROM players WHERE hours > 5");

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, change line 29, 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. The simplest way to think of the setup is that a row is one piece of data, and columns are different parts of that data. An example of a table might look like this:

account	hours
(npcserver)	6
Stefan	10
unixmad	3

When you request data from the database, you receive a list of "rows", based on what you ask for. The data in these rows is stored in columns.

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.

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)