When do you switch to SQLite?

richard9 · October 28, 2011, 6:20am

Well, it’s good to know that SQlite won’t bog down under the size, but I was more worried from the Corona (how does the target device handle parsing giant tables?) and personal (how the heck am I going to fill out 10,000+ entries?) aspects. Cutting down the number of cells by a factor of 16 would certainly make it more usable in terms of data entry/reading it in an SQL table viewer.

I think I need to find some way to convert a spreadsheet into SQlite data as well (so long as oAuth is out of date, anyway) but one mess at a time… [import]uid: 41884 topic_id: 14095 reply_id: 64007[/import]

lordmooch · October 28, 2011, 6:33am

@richard9 That’s a great point re. Corona’s performance with big datasets, and something you always have to consider.

Using a spreadsheet to help with SQLite data is straightforward. For example, my new game Super Smoothies stores all level data in a spreadsheet. The spreadsheet contains one column per column in the corresponding database table, plus a couple of extra columns that auto-generate SQL insert/update statements. So any time I change a value in a cell I automatically have the correct SQL generated to paste directly into my Lua code.

If I had more details about what exactly you’re doing I could probably help you better [import]uid: 26769 topic_id: 14095 reply_id: 64009[/import]

lordmooch · October 28, 2011, 7:20am

What format do you have the data in? I presume you’ll be getting it in some electronic format?

There are SQLite apps, e.g. I use SQLiteManager on Mac. Some of them may have data import facilities that could help you, but I don’t have experience of that to advise you. May be worth looking into though. [import]uid: 26769 topic_id: 14095 reply_id: 64016[/import]

richard9 · October 28, 2011, 7:21am

@lordmooch Yeah, that’s why I have to purchase that Corona SDK subscription (and a Mac…) soon. I’m not doing anything stressing for an iPad yet but I’d rather know sooner than later.

Right now I’m building a limited Fantasy Football app for player ranking. Nothing hideously complex - just a list of ~300 players with viewable stat information and some interesting new ways for users to set custom ranks.

In terms of data handling, each player has a unique id, and then I need to track roughly:
300 players * 32 stats * 3 seasons * 16 games

Even if I cut that down to 1 season, that’s 100k entries, way over my ballpark earlier! By using job-specific tables I can probably cut the number of entries by 80% (ie: have a kicker-specific table, since they only need 6-7 stats covered) but it’s still pretty large.

In terms of ways to get this data, it’s either:
a. Manual entry (ughh…)
b. Some sort of spreadsheet import
c. oAuth (not updated enough for the SDK and a third language to learn this year…)

The stats table won’t be user-editable (history is history).

EDIT: So as to explain why I’d consider strings - since each player plays a reliable number of games, I could simply use one column called “throws” and have “1,2,3” etc rather than have 16 columns, one for each week, just to cover throws. [import]uid: 41884 topic_id: 14095 reply_id: 64013[/import]

richard9 · October 28, 2011, 7:23am

Yeah, I can grab the data in XLS form and then save out as CSV if needed. I’ve been using a quick and dirty SQlite viewer for Windows but it only supports text-import. If you’re saying SQliteManager does spreadsheet import…?..then perhaps I need to speed up that mac purchase. [import]uid: 41884 topic_id: 14095 reply_id: 64017[/import]

lordmooch · October 28, 2011, 7:32am

No, I’m not saying that as I haven’t checked that out personally!

I’m saying that there may be some tools that do implement import routines. I’m sure there are apps on Windows for managing SQLite databases; maybe one of those has a .csv import function. [import]uid: 26769 topic_id: 14095 reply_id: 64019[/import]

richard9 · October 28, 2011, 7:38am

Ah. I misunderstood. I thought you said you were using spreadsheets directly with Corona, but you’re just using it to generate data for cut/paste. Got it. I’ll keep looking. [import]uid: 41884 topic_id: 14095 reply_id: 64022[/import]

richard9 · October 28, 2011, 9:34pm

Well, I suppose I should answer my own question:

Unfortunately Corona SDK does not support split().
string.gmatch() seems like a candidate to work but the search specification coding is a mess.
Luckily, someone here has already come up with the solution, in the form of the custom function, explode! (Since lordmooch posted in that thread, perhaps that’s the split function you’re thinking of?)

local shots = "1,2,7,3,10" local shotsTable = explode(",", shots) print(#shotsTable) -- 5 print(shotsTable[3]) -- 7

The question that immediately comes to mind, now, is whether or not this is a significant load on an iOS device to process out for 10k+ records, but I suppose I’ll find out soon enough. ;’)

Big thanks to everyone. [import]uid: 41884 topic_id: 14095 reply_id: 64170[/import]

drewns · October 29, 2011, 8:28am

Excellent find! Thanks for following up! Now I have some performance testing to do… [import]uid: 27119 topic_id: 14095 reply_id: 64281[/import]

Omnigeek_Media · October 29, 2011, 9:08am

Just as a bit of history. I’m not sure if split originated in perl or javascript, but one took it from the other. Explode is a PHP native call, but at the end of the day they do the same thing.

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