When do you switch to SQLite?

Getting Started Newbie Questions
#1

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…) [import]uid: 41884 topic_id: 14095 reply_id: 314095[/import]

#2

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
[import]uid: 9440 topic_id: 14095 reply_id: 51905[/import]

#3

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. [import]uid: 41884 topic_id: 14095 reply_id: 52039[/import]

#4

You’re right, there is no good tutorial, and in my opinion the implementation stinks. But I blame Lua for that, not Corona SDK. :slight_smile: 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
[import]uid: 9440 topic_id: 14095 reply_id: 52056[/import]

#5

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. [import]uid: 26769 topic_id: 14095 reply_id: 52301[/import]

#6

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. :wink: But once I struggle through recoding tableView into something practical, I’ll be tackling this (to varying levels of success) next. [import]uid: 41884 topic_id: 14095 reply_id: 52347[/import]

#7

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. [import]uid: 31262 topic_id: 14095 reply_id: 52370[/import]

#8

I’ve with aaaron on this: I use SQLite for even basic storage. [import]uid: 26769 topic_id: 14095 reply_id: 52378[/import]

#9

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. [import]uid: 41884 topic_id: 14095 reply_id: 52536[/import]

#10

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 [import]uid: 9440 topic_id: 14095 reply_id: 52584[/import]

#11

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.

[import]uid: 19626 topic_id: 14095 reply_id: 52603[/import]

#12

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? [import]uid: 41884 topic_id: 14095 reply_id: 52611[/import]

#13

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.

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

I’m not an OOP evangelist. I believe you should find the solution that best works for you. The above works for me and if you look at the code, it’s just like using any other dot syntax in Corona. But, Jay is right, there is no need to code this way. [import]uid: 26769 topic_id: 14095 reply_id: 52652[/import]

#14

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)

[import]uid: 19626 topic_id: 14095 reply_id: 52668[/import]

#15

Hey guys, just want to update and give thanks; the SQL documentation is more or less useless for basic Corona use, so the examples and ideas listed here have been huge for getting my on my feet with this stuff. I’m serious - robmiracle gave me the perfect context on how to select/update. Touchjunkies had enormously useful sample code. JA was huge in helping me figure out stuff in context to my code.

(I’m still only 50% there but I can generate a table and write to it/update it, at least…)

I do have one remaining question that I just can’t seem to figure out, though;

Is there a way to get SQlite to output an integer instead of a string?

local i = 1  
for row in db:nrows("SELECT \* FROM table") do  
table[i] = { TeamNumber = row.Team } --row.Team is outputted as a string.  
end

This is a simplified example, but if all of the entries in the SQlite file for the “Team” column are numbers, the output is never a number, but instead a string (“13” instead of 13)

Was hoping there was some really simple way to put a number rather than have to run tonumber() every time I want to pull data from the database. [import]uid: 41884 topic_id: 14095 reply_id: 62876[/import]

#16

@richard9

I found this post incredibly useful when I implemented the SQLite save/load system in the app I’m working on:

http://developer.anscamobile.com/forum/2011/02/02/how-can-i-easily-implement-save-and-load-system-0

I would recommend using the modified code provided by mattlmattlmattl in post #25 and saving it as ‘settings.lua’ or something similar (an external module). This will give you a way of easily saving/loading string, number, and boolean values.

Regarding your question about needing the output to be a number instead of a string, try this:

[lua]local i = 1
for row in db:nrows(“SELECT * FROM table”) do
table[i] = { TeamNumber = tonumber(row.Team) }
end[/lua] [import]uid: 27119 topic_id: 14095 reply_id: 63952[/import]

#17

Yeah, that was my solution as well. I just figured that if I could get SQlite to use a different output I wouldn’t need a dozen tonumber() calls. (And the link in this thread with execSQLNoRecords() is arguably the most useful, but I’ll definitely stare at the Save/Load later…)

What I’m trying to figure out now - and I feel like it’s solveable, but probably just a bit past my understanding - is how to turn a string into a table. I saw how often examples were using [[]] to trick the SQlite commands to work, but now I’m wondering if there’s some sort of equivalent that might work for LUA.

local shots = "1,2,7,3,10"  
Stats = {}  
  
-- What I'd like is to somehow turn:  
Stats = { shots }  
-- into:  
Stats = {1,2,7,3,10}

Basically, is there a way I can pass the variable so that it’s interpreted into creating the table entries? (I’m trying really desperately to use this approach as otherwise I need about 10,000 SQlite rows that really won’t be manageable at all.)
[import]uid: 41884 topic_id: 14095 reply_id: 63956[/import]

#18

I was trying to do exactly that earlier today. I didn’t find a good way. You can use a lua function or two with a “for” statement to speed up writing to the table, but you’re right that you’ll still potentially end up with a lot of rows.

To keep things straight in my database, I use lua table names for the “name” column (ie. foo[1], foo[1][test]). I know it’s unnecessary, but it helps me when I’m pulling the data back into lua. It’s easy to read and I can pass number variables easily.

I tried several different solutions for getting table data into and out of a single row value, but I never found a way. If anyone has done it or knows how, I’d love to find out the answer! [import]uid: 27119 topic_id: 14095 reply_id: 63981[/import]

#19

Assuming you want to turn a comma-separated value list - 1, 2, 3, 4, 5, 6 - into a table, then you could try a Split function. Not sure what the Lua equivalent is off the top of my head, but I have used one which someone on this site created.

Basically, the Split strips out each value between the comma (ignoring spaces), so you can easily iterate over the values and insert them into a table.

Slight aside: I wouldn’t worry about handling 10,000 rows in SQL. That’s what SQL is made for. The only time I’d be concerned is if the volume of data meant there would be performance issues. [import]uid: 26769 topic_id: 14095 reply_id: 63984[/import]

#20

@lordmooch

I had an epiphany in the shower this morning, realizing that lua can easily do exactly what you described. I’m headed to work right now, but this evening when I get a chance I’ll post the function I come up with. [import]uid: 27119 topic_id: 14095 reply_id: 64005[/import]