LUA <-> MySQL Updates

Solar2D General Questions/Discussion
#1

Hi,

I’m starting work on a new app which is a native app version of an existing client project

My clients current process is when they are working in the field they use safari on an ipad to load and login to a php/mysql web-app. The remote staff team then works on the job filling in simple form inputs, taking photos and writing a short final report statement before submitting the form and it does a set of writes to the mysql tables which are then turned into a pdf report by the office admin team.

I’m looking to develop the native app in Corona and build an app to do this. Obviously the form fields etc are straightforward and I can save the images and form contents locally to the device using the standard Corona methods. I will then need to sync this information to a MySQL database on a server. I have a few quick (hopefully simple questions?) - I’m a reasonably proficient PHP/MySQL dev and have picked up the basics of Corona by doing the tutorials and some reading but I’m still a bit green on the whole app dev side.

  1. Can Corona talk directly to a MySQL database?

I’ve done some digging on this since writing this first post and realising I needed to wait for it to be approved and I think I’d need to write a PHP middleware script which does the database querying, converting from and back to JSON - I’m happy with this approach.

  1. Is this wise? I’m currently reading/writing to tables on my VPS as I have a secure login process for the web-app, if I put a user/pass in the LUA code can this be exposed by “opening/cracking” the .apk? (or .ipa)

Using the middleware solution from (1) the user/pass is less of a concern however I’d want to pass a ‘token’ or similar to the script so that only app installations I permit can run the scripts - should I assume basically anything I write into the LUA source code can be read as plain text when extracted from the .apk/.ipa? Or is there something basic I’m missing which I can pass to the script as a identifier?

  1. Should I use a plugin for this? I’ve seen an S3 plugin and a Coronium Core plugin in the store? I’m not sure if these are more secure than a DIY solution and worth pursuing or whether they would be a sledgehammer to crack a nut so to speak?

I suppose this is really the core question - does my use case match the delivery of either of these? Or am I better writing my own php middleware?

  1. The web-app is clearly a “live” connection, some of the form selection contents are loaded on the fly as per the clients customers instructions, as the native app is offline some of the time I’ll need to sync the data before creating a “job card” for the field team - is there a recommended plugin or some sample code for this process? I’ve looked around and some of the older forum posts refer to it but I’m not sure if they are still relevant to the latest versions of the SDK.

Thanks for any advice/help - it’d be much appreciated.

#2

Did you start with the process of coding on it?

Regards.

Smith Plex https://tutuappx.com/ https://vidmate.onl/

#3

Hi Smith,

I’ve started laying the groundwork - as I’ve dug deeper I’ve taken a punt on Coronium Core being the way to best do this so I’ve bought a licence and set up a droplet at Digital Ocean. I’ve constructed a test MySQL database on there and started the process of working out how to push updates to the mySQL database via core using jobs and bringing down an (updated) test table into a corona app in the simulator. Its all a bit crude at the moment but I’m under way at least. Be interested if you have any thoughts on this or if you’ve tried using mySQL with Corona before how you went about it?

Regards, George.

#4

Quick update as I’ve spent a number of hours working on this today. I’ve managed to achieve the following:

  1. Purchase and Install Coromium Core to a Digital Ocean Droplet.

  2. Populate a MySQL database on the droplet using HeidiSQL.

  3. Load the MySQL data into a Corona App and display it.

  4. Download the MySQL database to the App locally and create a structured JSON table from the data.

  5. Query both the JSON table and the MySQL table using both server and client LUA scripts.

  6. Write back to the MySQL tables from the App using both sanitised user inputs and App driven inputs.

  7. Store the JSON table locally for use/updating on subsequent App loads.

  8. Delete data from both local JSON table, JSON stored file and the MySQL database.

Its been a slog as I’m learning LUA on the hoof whilst implementing a middleware solution I’ve never used before which has its own semantic language for undertaking tasks - for example I tried for about 20 minutes to use a greater than symbol in the WHERE clause for the client side params for a core.mysql look up. Turns out unless I’m missing something it can’t be done - the alternative is to run a true MySQL query on the server and respond back with the result - that obviously requires a connection to the server for each transaction.

As my project only requires me to update the table I want to search once every few days I’ve set it to do the update first on initialising the App, then it copies the table contents locally via the client core.mysql api, after which I can then store the tabular data as a JSON table which can be easily queried with a greater than or less than variable (or any other filter I wish).

I save the user inputs to the same JSON table and then (currently with a button but eventually/hopefully a proper sync routine) push the data back to the MySQL database saving the users report ready for the server to run a “job” (which I’ve not worked how to use yet) which will push the updated MySQL table to the clients main reports database on another server.

I’ve spent about 12 hours on it today getting my head around Coronium mostly and getting the main back end stuff working and using the immediate/debug windows to check I’ve got my ducks in a row each time. Its been a mix of frustration and progression today but I think what I’ve achieved in a relatively short period of time from a standing start probably should be credited to Corona and Coronium being pretty solid bases to work on. I’ll keep this updated with progress in case its of interest to anyone.

#5

Hi,

You should be able to query MySQL with operators in the where key like so:

local params = { db = "app", tbl = "users", where = "`score` \> 100", limit = 10 } core.mysql.select(params, apiListener)

Make sure you are using back-ticks on field names. If you try it and it does not work, please let me know.

In all cases the local API makes a server call. My general recommendation is to always put your MySQL queries on the server side, and call a custom api to get your data. It may be a little more work, but it allows for greater flexibility (you can adjust queries without rebuilding an app for example) and safety.

If you want to bounce more ideas, or need further assistance drop a post in the Coronium sub-forum: https://forums.coronalabs.com/forum/643-coronium/

Thanks for using Coronium Core!

-dev

#6

Did you start with the process of coding on it?

Regards.

Smith Plex https://tutuappx.com/ https://vidmate.onl/

#7

Hi Smith,

I’ve started laying the groundwork - as I’ve dug deeper I’ve taken a punt on Coronium Core being the way to best do this so I’ve bought a licence and set up a droplet at Digital Ocean. I’ve constructed a test MySQL database on there and started the process of working out how to push updates to the mySQL database via core using jobs and bringing down an (updated) test table into a corona app in the simulator. Its all a bit crude at the moment but I’m under way at least. Be interested if you have any thoughts on this or if you’ve tried using mySQL with Corona before how you went about it?

Regards, George.

#8

Quick update as I’ve spent a number of hours working on this today. I’ve managed to achieve the following:

  1. Purchase and Install Coromium Core to a Digital Ocean Droplet.

  2. Populate a MySQL database on the droplet using HeidiSQL.

  3. Load the MySQL data into a Corona App and display it.

  4. Download the MySQL database to the App locally and create a structured JSON table from the data.

  5. Query both the JSON table and the MySQL table using both server and client LUA scripts.

  6. Write back to the MySQL tables from the App using both sanitised user inputs and App driven inputs.

  7. Store the JSON table locally for use/updating on subsequent App loads.

  8. Delete data from both local JSON table, JSON stored file and the MySQL database.

Its been a slog as I’m learning LUA on the hoof whilst implementing a middleware solution I’ve never used before which has its own semantic language for undertaking tasks - for example I tried for about 20 minutes to use a greater than symbol in the WHERE clause for the client side params for a core.mysql look up. Turns out unless I’m missing something it can’t be done - the alternative is to run a true MySQL query on the server and respond back with the result - that obviously requires a connection to the server for each transaction.

As my project only requires me to update the table I want to search once every few days I’ve set it to do the update first on initialising the App, then it copies the table contents locally via the client core.mysql api, after which I can then store the tabular data as a JSON table which can be easily queried with a greater than or less than variable (or any other filter I wish).

I save the user inputs to the same JSON table and then (currently with a button but eventually/hopefully a proper sync routine) push the data back to the MySQL database saving the users report ready for the server to run a “job” (which I’ve not worked how to use yet) which will push the updated MySQL table to the clients main reports database on another server.

I’ve spent about 12 hours on it today getting my head around Coronium mostly and getting the main back end stuff working and using the immediate/debug windows to check I’ve got my ducks in a row each time. Its been a mix of frustration and progression today but I think what I’ve achieved in a relatively short period of time from a standing start probably should be credited to Corona and Coronium being pretty solid bases to work on. I’ll keep this updated with progress in case its of interest to anyone.

#9

Hi,

You should be able to query MySQL with operators in the where key like so:

local params = { db = "app", tbl = "users", where = "`score` \> 100", limit = 10 } core.mysql.select(params, apiListener)

Make sure you are using back-ticks on field names. If you try it and it does not work, please let me know.

In all cases the local API makes a server call. My general recommendation is to always put your MySQL queries on the server side, and call a custom api to get your data. It may be a little more work, but it allows for greater flexibility (you can adjust queries without rebuilding an app for example) and safety.

If you want to bounce more ideas, or need further assistance drop a post in the Coronium sub-forum: https://forums.coronalabs.com/forum/643-coronium/

Thanks for using Coronium Core!

-dev