Creation/Dev/Gscript/Using SQLite - Revision history

Cbk1994: /* How do I tell SQLite what to do? */

2011-07-25T11:46:13Z

How do I tell SQLite what to do?

← Older revision		Revision as of 07:46, 25 July 2011
Line 92:		Line 92:
	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.		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.

	~~<div style~~=~~"width: 80%; margin-left: auto; margin-right: auto; border: 1px solid gray; padding: 5px; background-color: #FBEFEF;">~~		{{Warning\|message=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}}
	~~<div style="float: left; margin-right: 7px; padding-top: 3px; margin-left: 3px;">[[Image:Error.png]]</div>~~
	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.
	~~</div>~~
	~~<br />~~

	= First steps with SQLite =		= First steps with SQLite =

Cbk1994: /* How is data stored? */

2010-08-29T17:43:30Z

How is data stored?

← Older revision		Revision as of 13:43, 29 August 2010
Line 83:		Line 83:
	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:		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:

	~~temp.rows =~~ {		{
	{"(npcserver)", 6},		{"(npcserver)", 6},
	{"Stefan", 10},		{"Stefan", 10},
	{"unixmad", 3},		{"unixmad", 3},
	};		}

	== How do I tell SQLite what to do? ==		== How do I tell SQLite what to do? ==

Cbk1994: /* How is data stored? */

2010-08-29T17:41:13Z

How is data stored?

← Older revision		Revision as of 13:41, 29 August 2010
Line 62:		Line 62:
	Data is stored into tables. Like any table, these tables have '''columns''' and '''rows'''.		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 ~~many different "pieces"~~ of data.		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.

	<center>		<center>

Cbk1994: /* How is data stored? */

2010-08-29T17:40:20Z

How is data stored?

← Older revision		Revision as of 13:40, 29 August 2010
Line 81:		Line 81:
	</center>		</center>

	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 ~~might~~ give it to be in a format equivalent to this:		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:

	temp.rows = {		temp.rows = {

Cbk1994: /* How is data stored? */

2010-08-29T17:39:26Z

How is data stored?

← Older revision		Revision as of 13:39, 29 August 2010
Line 60:		Line 60:
	= Getting Started =		= Getting Started =
	== How is data stored? ==		== 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:~~		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 many different "pieces" of data.

	<center>		<center>
Line 79:		Line 81:
	</center>		</center>

	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~~.		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 might give it to be in a format equivalent to this:

			temp.rows = {
			{"(npcserver)", 6},
			{"Stefan", 10},
			{"unixmad", 3},
			};

	== How do I tell SQLite what to do? ==		== How do I tell SQLite what to do? ==

Cbk1994: /* Preparation */

2010-08-29T17:33:45Z

Preparation

← Older revision		Revision as of 13:33, 29 August 2010
Line 48:		Line 48:

	== Preparation ==		== 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 [[Worlds/Testbed Server\|Testbed Server]]		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 [[Worlds/Testbed Server\|Testbed Server]].

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

Cbk1994: /* Why use SQLite? */

2010-08-29T17:32:53Z

Why use SQLite?

← Older revision		Revision as of 13:32, 29 August 2010
Line 42:		Line 42:
	The equivalent code for a properly organized SQL table might be:		The equivalent code for a properly organized SQL table might be:

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

Cbk1994: /* Why use SQLite? */

2010-08-29T17:30:54Z

Why use SQLite?

← Older revision		Revision as of 13:30, 29 August 2010
Line 20:		Line 20:

	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:		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 > 50		SELECT * FROM 'players' WHERE hours > 5

	Using traditional storage methods, such as [http://en.wikipedia.org/wiki/Flat_file_database flatfile], the file might look something like this:		Using traditional storage methods, such as [http://en.wikipedia.org/wiki/Flat_file_database flatfile], the file might look something like this:

Cbk1994: /* Why use SQLite? */

2010-08-29T17:30:22Z

Why use SQLite?

← Older revision		Revision as of 13:30, 29 August 2010
Line 19:		Line 19:
	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.		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:		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 AND hours < 10~~		SELECT * FROM 'players' WHERE hours > 50

	Using traditional storage methods, such as [http://en.wikipedia.org/wiki/Flat_file_database flatfile], the file might look something like this:		Using traditional storage methods, such as [http://en.wikipedia.org/wiki/Flat_file_database flatfile], the file might look something like this:

Cbk1994: /* Why use SQLite? */

2010-08-29T17:29:45Z

Why use SQLite?

← Older revision		Revision as of 13:29, 29 August 2010
Line 20:		Line 20:

	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:		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		SELECT * FROM 'players' WHERE hours > 5 AND hours < 10

	Using traditional storage methods, such as [http://en.wikipedia.org/wiki/Flat_file_database flatfile], the file might look something like this:		Using traditional storage methods, such as [http://en.wikipedia.org/wiki/Flat_file_database flatfile], the file might look something like this: