Corona Database questions

[hailyeah15]

So my app requires us to use a database to store account username, passwords. Also the app requires us to store information created by one user, which then can be viewed by a different user of the app. How would i make a database to do ths?

[piotrz55]

It’s rather sqlite question. When you search for corona sdk sqlite tutorial you will find examples how open and end execute sql commands. However, structure of this commands is sql thing, so y o u better look for some online

[hailyeah15]

What are you even saying?

[Christopher_Bishop]

He means you need to use sqlite db and there are tutorials on corona on how to do that but off the top of my head, (haven’t tested)…

local sql = require("sqlite3") local path = system.pathForFile("databasename", system.DocumentsDirectory) local db = nil local tableInfo = [[CREATE TABLE IF NOT EXISTS Users (username VARCHAR(50), password VARCHAR( 50 ));]] db:exec(tableInfo) local function InsertUser(username, password) db:exec("INSERT INTO Users (username, password) VALUES ('" .. tostring(username) .. "', '" .. tostring(password) .. "')") end local function GetUsers() local rValue = {} for row in self.db:nrows("SELECT \* FROM Users") do --Normally you wouldn't do it this way, just doing it to show you looping --through the database records. table.insert(rValue, row) end return rValue end --[[--Create dummy users InsertUser("hello", "world") InsertUser("baby", "cakes") --Loop through table. local testUserList = GetUsers() for i = 1, #testUserList do print("Username::", testUserList[i].username) end ]]--

[Appsessed]

Hi Christopher;

Thank you for your example. Can we use this and make the DB before hand say with MYSql on our server? How would the code change - take out the “IF NOT EXIST” portion?

Thanks.

[Christopher_Bishop]

You can remove 

local tableInfo = [[CREATE TABLE IF NOT EXISTS Users (username VARCHAR(50), password VARCHAR( 50 ));]] db:exec(tableInfo)

and it will not create a table, however if you are going to create the db ahead of time then you will need to copy it to the documents folder as you can not modify a db in the resource directory.

If 90% of your data is going to be on the server and you are just using the local db for offline etc. then just use a json request and build the local db from the json result from your server.

Looking at the code above I forgot to add a sqlite3.open(path)  to actually open the database before calling any execs just fyi

[Christopher_Bishop]

On a side note: IF you are using mySQL on your server you will need to convert it to a sqlite database as sqlite and mysql are two different database engines.

Here is a link on how to do that via mySQL forums

http://forums.mysql.com/read.php?145,68269,92627#msg-92627

[Appsessed]

@Christopher\_Bishop

You can remove 

local tableInfo = [[CREATE TABLE IF NOT EXISTS Users (username VARCHAR(50), password VARCHAR( 50 ));]] db:exec(tableInfo)

and it will not create a table, however if you are going to create the db ahead of time then you will need to copy it to the documents folder as you can not modify a db in the resource directory.

If 90% of your data is going to be on the server and you are just using the local db for offline etc. then just use a json request and build the local db from the json result from your server.

Looking at the code above I forgot to add a sqlite3.open(path)  to actually open the database before calling any execs just fyi

Ok great thanks.

So, all of the DB reading and writing will be done locally and then when the app sleeps or closes the DB is uploaded to the server? If thats the case then what about multiple users at the same time?

Thanks for your help I appreciate it. 

[Appsessed]

@Christopher\_Bishop

On a side note: IF you are using mySQL on your server you will need to convert it to a sqlite database as sqlite and mysql are two different database engines.

Here is a link on how to do that via mySQL forums

http://forums.mysql.com/read.php?145,68269,92627#msg-92627

OK great - I will check out the link - Thanks - I did not know that there was a difference between SQLite and MYSql - hmm… I better do some more reading. 

[Appsessed]

It would seem we would have to have Shell access to run the conversion script… hmm

[Appsessed]

Oh wait. What about Game Centre Mutli-user turn based games? Maybe I should look into that first before trying to make my own DB and all that. Not sure what it is capable of though.

I will be back.

Thanks again for the help.  :ph34r:

[Christopher_Bishop]

In order for it to sync with the server you will need to write some communications to send/receive the data because they have no idea about each other. 

If all you are trying to do is simple multi-player stuff then yes use something like game center or one of the plugins for corona here

http://coronalabs.com/resources/plugins/

otherwise you are going to need a lot more knowledge in databases and client/server communications

[Appsessed]

OK I will check the plugins out.

Yes indeed…  " a lot more knowledge in databases and client/server communications " is very true I see.  ;) 

Cheers!

[piotrz55]

It’s rather sqlite question. When you search for corona sdk sqlite tutorial you will find examples how open and end execute sql commands. However, structure of this commands is sql thing, so y o u better look for some online

[hailyeah15]

What are you even saying?

[Christopher_Bishop]

He means you need to use sqlite db and there are tutorials on corona on how to do that but off the top of my head, (haven’t tested)…

local sql = require("sqlite3") local path = system.pathForFile("databasename", system.DocumentsDirectory) local db = nil local tableInfo = [[CREATE TABLE IF NOT EXISTS Users (username VARCHAR(50), password VARCHAR( 50 ));]] db:exec(tableInfo) local function InsertUser(username, password) db:exec("INSERT INTO Users (username, password) VALUES ('" .. tostring(username) .. "', '" .. tostring(password) .. "')") end local function GetUsers() local rValue = {} for row in self.db:nrows("SELECT \* FROM Users") do --Normally you wouldn't do it this way, just doing it to show you looping --through the database records. table.insert(rValue, row) end return rValue end --[[--Create dummy users InsertUser("hello", "world") InsertUser("baby", "cakes") --Loop through table. local testUserList = GetUsers() for i = 1, #testUserList do print("Username::", testUserList[i].username) end ]]--

[Appsessed]

Hi Christopher;

Thank you for your example. Can we use this and make the DB before hand say with MYSql on our server? How would the code change - take out the “IF NOT EXIST” portion?

Thanks.

[Christopher_Bishop]

You can remove 

local tableInfo = [[CREATE TABLE IF NOT EXISTS Users (username VARCHAR(50), password VARCHAR( 50 ));]] db:exec(tableInfo)

and it will not create a table, however if you are going to create the db ahead of time then you will need to copy it to the documents folder as you can not modify a db in the resource directory.

If 90% of your data is going to be on the server and you are just using the local db for offline etc. then just use a json request and build the local db from the json result from your server.

Looking at the code above I forgot to add a sqlite3.open(path)  to actually open the database before calling any execs just fyi

[Christopher_Bishop]

On a side note: IF you are using mySQL on your server you will need to convert it to a sqlite database as sqlite and mysql are two different database engines.

Here is a link on how to do that via mySQL forums

http://forums.mysql.com/read.php?145,68269,92627#msg-92627

[Appsessed]

@Christopher\_Bishop

You can remove 

local tableInfo = [[CREATE TABLE IF NOT EXISTS Users (username VARCHAR(50), password VARCHAR( 50 ));]] db:exec(tableInfo)

and it will not create a table, however if you are going to create the db ahead of time then you will need to copy it to the documents folder as you can not modify a db in the resource directory.

If 90% of your data is going to be on the server and you are just using the local db for offline etc. then just use a json request and build the local db from the json result from your server.

Looking at the code above I forgot to add a sqlite3.open(path)  to actually open the database before calling any execs just fyi

Ok great thanks.

So, all of the DB reading and writing will be done locally and then when the app sleeps or closes the DB is uploaded to the server? If thats the case then what about multiple users at the same time?

Thanks for your help I appreciate it.