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");