SQLite Question: How to query the names of the tables in a database?

[RegieC]

I just started learning SQLite and Lua SQLite. There’s one thing that I just can’t figure out and its been two days since and I still don’t have an answer. I just couldn’t find it anywhere.

How do you find out the names of Tables in a database?

for example, I have userdata.db. And in it let say it has 3 tables and named Joe, Paul, Susan. Lets pretend I don’t know the names of the tables and I have no idea how many tables are in userdata.db

How do you go about querying those table names or how would you find out how many tables are in the database?

Any help would be greatly appreciated.

[import]uid: 121875 topic_id: 23486 reply_id: 323486[/import]

[shaharzrihen]

Hey RegieC -

There are several ways to do it and without knowing more about your exact needs, I suggest your read up and decide yourself.

http://stackoverflow.com/questions/82875/how-do-i-list-the-tables-in-a-sqlite-database-file

BR,
Shahar [import]uid: 13553 topic_id: 23486 reply_id: 94246[/import]

[RegieC]

Thanks Shahar for the reply.

I tried the link you provided but I still can’t get it. Like I said I just started learning lua and sql.

What I’m trying to do is to record user’s performances. I want to do this with one database (i.e. userdata.db) and every user will have their own table which will be named whatever name the user provided (i.e. John). My problem is at the beginning where the user needs to either select an existing user or create a new one.

Now to do this the code has to first search the database for existing users (which are the tables in the database) and display it on the screen if there’s any which the user can then select. Finding out if there’s any existing tables in the userdata.db is where I’m getting stuck(using lua syntax)

Thanks in advance for any support.

[import]uid: 121875 topic_id: 23486 reply_id: 94284[/import]

[gtatarkin]

>What I’m trying to do is to record user’s performances. I want to do this with one database (i.e. userdata.db) and every user will have their own table which will be named whatever name the user provided (i.e. John).

It’s not best way to deal with databases. Why not create one table (for example Users) and put users in rows? Table should have rows like ID, name and best (best result).
Let’s start:

[lua]local path = system.pathForFile(“mygame.db”, system.DocumentsDirectory)
db = sqlite3.open( path )

local pointsStore = [[CREATE TABLE IF NOT EXISTS points (id INTEGER PRIMARY KEY, name, best);]]
db:exec( pointsStore )[/lua]

Let’s create new user but check if user name exist:
[lua]local get_user = 0
for row in db:nrows("SELECT name FROM points WHERE name = "…user) do
get_user = get_user + 1
end
if get_user = 0 then
local pointsStoreInit = [[INSERT INTO points VALUES(’]]…i…[[’, name, 0);]]
db:exec( pointsStoreInit )
end[/lua]

Select best result for current user:

[lua]local user = John
local best
for row in db:nrows("SELECT best FROM points WHERE name = "…user) do
best = row.best
end[/lua]

Now you have best result for user John in lua variable and you can check if current result is better when stored in database. If not do nothing but if current result is better you can do something like this:
[lua]if (points > best) then
local pointsDbUpdate = [[UPDATE points SET best=]]…points…[[WHERE name=]]…user…[[;]]
db:exec( pointsDbUpdate )
end[/lua]

Hope this will help you to deal with databases.

BTW. But if you don’t like it there is great lib called Ice ( http://developer.anscamobile.com/code/ice )
Regards [import]uid: 12704 topic_id: 23486 reply_id: 94313[/import]

[RegieC]

Thanks for the response gtatarkin. Your code looks great, however I did started similar to the one you posted but I realized that it won’t work in my case (probably there’s a way but I just couldn’t see it). Because I will have to keep previous data and use it to plot the performance on every level and display it to the user to give them feedback of the improvement or lack thereof.

There’s a game/tool called Memory Trainer by URBIAN (excellent by the way) and it has this option called ‘My Progress’. That’s exactly what I want to do.

Anyways I did found a way to get the table names in a database and it goes like this;

for row in db:nrows("SELECT \* FROM sqlite\_master")do  
print(row.name)  
end  

cheers [import]uid: 121875 topic_id: 23486 reply_id: 94320[/import]

[gtatarkin]

It’s database meennn you can keep lot’s of data inside For example - table points or users:

Structure:
ID, user_id, user, level, points. date

Data:
1, 1, John, 1, 100, 2012-03-16
2, 1, John, 1, 150, 2012-03-16
3, 2, Sara, 1, 120, 2012-03-17
4, 2, Sara, 1, 150, 2012-03-17
…
…
…
10000202020, 1, John, 1, 3000, 2012-03-20

And now use just SQL like (it’s just example not working code!):

SELECT * FROM points WHERE user_id = 1 AND level = 1 ORDER BY date DESC
for all entry on level 1 for user John ordered by date - sorting in decreasing order
or
SELECT date(YYYY-MM-DD) WHERE user_id = 1 AND level = 1
for all entry on level 1 for user John and date you expect
or
SELECT date(‘now - 1 day’) WHERE user_id = 1
for all yesterday’s John results

For game like Memory Trainer SQLite database will be the best and only solution but you don’t have to build separate tables for all users. Users identification BY ID is much better because you can have many users bearing the name of John.
[import]uid: 12704 topic_id: 23486 reply_id: 94333[/import]