When do you switch to SQLite?

For you experienced folks out there: At what point does it become more advantageous to use SQlite as opposed to good-ol' nested tables?

I'm currently working on a tutorial project - some fantasy sports stuff - and as you can imagine with 200+ players and probably 20+ entries in each table[x], it gets quite large. I'm still reasonably happy doing things this way because I think I need to learn more LUA before tackling something like SQlite, but is there a point where you make the switch? (I am going to need to write changes to these tables periodically, updating stats and whatnot...)

Switch now, I say.

If you're dealing with more than a few pieces of data, use a database.

You only have to write the database functions one time and then use them forever (with maybe minor tweaking to each reuse).

Jay

Alright, I'll give it a look. How would you rate the difficulty of using it though? I'm guessing there's no easy tutorial.

You're right, there is no good tutorial, and in my opinion the implementation stinks. But I blame Lua for that, not Corona SDK. :) On the other hand, I probably just got spoiled with things like ColdFusion, RB, etc., that have database access built in from the ground up.

I'm testing the next build of Corona Project Manager tonight so I'll do that by chopping out some SQLite routines I'm using in my Roly-Polies game and putting them together. I don't have time to do an actual tutorial, but there are a few good threads about databases here in the forum.

When I have the code ready I'll post a link.

Jay

I agree with Jay; Switch now!

SQLite is very powerful and, if you build yourself some classes to handle common database stuff, you'll find it very easy to use.

Check out this link where I post some code for easily working with a database: http://developer.anscamobile.com/forum/2011/04/21/ways-implement-oop-lua

Note: the code relating to databases in that link above is actually from a sports management game I was working on - so I was working with hundreds of players details etc - SQLite was the only option I'd consider for something like that.

Taking a look at it now.

My hesitation, for what it's worth, is simply because
a) Not a programmer by trade
b) Already trying to learn LUA using Corona.

SQlite looks quite powerful but also daunting. The moment someone mentions building "classes" my eyes sort of glaze over. ;) But once I struggle through recoding tableView into something practical, I'll be tackling this (to varying levels of success) next.

I use SQLite just because I'm familiar with it. I'm the other way around, I would have to learn how to implement tables. For this reason I use SQL for even very small single table projects.

I've with aaaron on this: I use SQLite for even basic storage.

Well, my basic project is well beyond basic storage. Probably will end up around 300 entries in the main table, each entry with probably 3 arrays and a dozen+ fields.

I gotta ask something first before I try to dive in - is data sorting easier in SQlite than in LUA tables? I'd say my biggest problem right now is writing the functions to:

a. Sort by a specific table category (eg data[i].job)
b. Only show specific data while doing so (eg: sort by data[i].job but only show entries that have data[i].tier == 2)

If it's really just LUA that handles that part of it, I'll hold off until I have a functional app.

richard9, that's the perfect reason to use a database.

a. select * from mytable order by job

b. select * from mytable where tier = 2 order by job

Those are the SQL queries you'd pass to the database and it would bring back just the info you want.

As far as classes go, you do NOT have to uses classes of any sort. OOP folks like to do that, but after almost 30 years of professional programming I haven't found a good case for always using OOP -- or even for mostly using OOP. So don't let that scare you off.

Jay

I'd say for what you're doing a database would make more sense since you don't need access to all the data all the time. If your data was more like something you were manipulating a lot or speed was important, keeping it cached in memory might make more sense or if it was a smaller data set where you just don't need the overhead of the SQL code.

Okay, you've got me convinced. But where's the go to source to learn how to turn a table into a database and the commands I'll need to organize it? Or is it pretty much just a matter of staring at Corona's SQlite sample code?

There are some things being said here that are potentially misleading.

SQLite is a mechanism for persisting your data, i.e. storing it so that it's not lost when the app finishes executing. It is not a replacement for tables or variables, rather it's a way of storing your table/variable data so it's not lost.

For speed, you'll load the the data from SQLite into, for example, Lua tables to manipulate whilst your app is running. Then you'll store the changed data back in SQLite when you need to.

As for the question of OOP/classes, here's some code that I would use with OOP principles to load/save data. The use of "database" is a reference to my own database.lua file.

1
2
3
4
local myPlayer = database.player(1)  -- Loads a Player record with a PlayerID of 1 from the SQLite database, returns the record as an OOP class in myPlayer
myPlayer.incrementAge()  -- Adds 1 to the Player's age
myPlayer.surname("Dalglish")  -- Change the Player's surname
myPlayer.save()  -- Saves the data back to the database

Lordmooch is quite right, but I think the OP was talking about having all his information start in a Lua table, as opposed to some offline storage like a flat file, JSON, or SQL.

Clearly while you're working with the records you need them in tables.

As for learning SQL Lite, there should be a billion tutorials and information just by googling SQL Lite.

There are pretty much four statements you need to master, and they are in plain language which makes it easy to learn (if you natural language is English!)

Those four statements are:

SELECT
INSERT INTO
UPDATE and
DELETE FROM

You will also need some table creation and removal statements:

CREATE TABLE
DROP TABLE

SQL statements are not case sensitive, but common coding practice is to show the SQL bits in all upper case, while variables, strings and other values are in the case they are created as (that part is case sensitive)

To init a database you would do a

CREATE TABLE tablename [list of fields in each row] (I don't know the SQL Lite syntax for CREATE TABLE off the top of my head)

INSERT INTO tablename (var1, var2, var3) VALUES (value_for_var1, value_for_var2, value_for var3)

do a bunch of inserts to populate the table.

Then later to get data from the database, you will use the SELECT statement:

SELECT var1, var2, var3 FROM tablename

or SELECT * FROM tablename
to get everything.

You can modify the return set with various clauses on the select statement, like:

SELECT var1, var2 FROM tablename WHERE var3 < 10 ORDER BY var2

would fetch only var1 and var2 from all records of tablename where ver3 is less than 10, but they would be sorted by var2's values.

Updating records is pretty simple:

UPDATE table SET var1 = value WHERE (some condition to keep it from updating everything)

views:1318 update:2011/10/11 15:24:38
corona forums © 2003-2011