updating remote database with local sqlite database

[FlawlessDog]

Hi all,

Long time since me last post

Anyway, here's what I gots...
We will be putting a few kiosks at the mall.
They will be running an air app, and we needed usage analytic info recorded.
There is a lot of info not needed like any other web analytics.
Session info, user/browser info and the like...

All we need is what button is pressed on what kiosk, at what time of day.

So after some hair pulling I managed to use air to create, and populate a local sqlite db with the required info, so that's cool.

Now here's where I need help...
Every couple days, I need to use this data that has been collected to update a remote database and then display the results in a page online, kindof like google analytics...tho not so indepth...obviously.
Only time these kiosks will be online is when the db sinc takes place, so google analytics wouldn't do me any good.

Each kiosk db has one table and 4 columns:
kiosk ID, ButtonID, Client ad info, time/date.

As to the remote db, I guess there should be a table for each kiosk(?) with this info added to it, when we do our sync every couple days.

Not sure what kindof db/sql/server we will have yet, tho I suspect it will be php/mySql.

Is a bit new at this, and is learning on the fly...
hope this mess makes somekindof sence

derp...help? LOL

Thanks in advance

[LOLFlash]

what I would do:

there is no needs for database on local computer if you don't run queries to it constantly.

save your date in txt file with append MODE
start this file every day: like put data in name of the file.

There is a few technology for delivering data

(considering we are on AS3 forum)

Use upload request to PHP to deliver files on server
delete files after successful upload

use hard coded username and password in flash and php side
on server side store files in folders according username

Load txt files in flash from server for analyzes

hint: use newline for row, coma for column

build your graphs and save them in shared object on client computer

[poltuda]

As to the remote db, I guess there should be a table for each kiosk(?) with this info added to it, when we do our sync every couple days.



There should be one table for all of those kiosk. Every kiosk have to its own ID which should store in that table with date/time so that you can fetch the data by IDs and date time and if your user have to swap their card which have UID then that should also store in that row so you can find which user's data is that.

What Google Analytics will do here I don't understand but any way you could make it in an interval to update the data fetching from SQLite to MySQL or if it fine no error in the net connection.



poltuda

[FlawlessDog]

Thanks for the replies :)

Yeah, I think a database will be needed as there will be some queries run to it every so often as I need to display the results in graph form as well as being able to separate each kiosk in those results.

Google analytics was just an example of the sort of thing I am trying to achieve. As I said before, the results just don't need to be as in depth as all that.

So Poltuda has the right idea. In-fact, sqlite on the server side would be enough for what I need were it not for some of the limitations of sqlite as compared to mySQL. Namely the size of the db. Will need to keep these events stored for quite sometime I think.

So as I read more on the subject, I will need to update the remote mySQL db with the local sqlite db a couple times a week. Once each update is complete, the local db can be wiped or even deleted and a new one started each time, as my air app will create a new one if there is not one present when it first fires up.

Will need to learn how to query the database, so I can get the graph display...google graphs or even open flash chart would do the trick once the mySQL db has been populated.

...learn, learn, learn... :P

[poltuda]

This is a sample of MySQL database and its structure.

You can coincide local and remote data in available time or in every event. You can also create a temp database to store when data fails to send to remote server.


poltuda

[Flash Gordon]

We will be putting a few kiosks at the mall.
They will be running an air app, and we needed usage analytic info recorded.
There is a lot of info not needed like any other web analytics.
Session info, user/browser info and the like...

Urgh....so, if I HAD to do something like this I would


write a cron tab/job on each of the kiosk to call a transfer script once a day
write a transfer script to post data to a public php page with a token and id
based upon the token and auth, add the data to the master database


option 2:

write data directly to remote database. Will require a static IP address for db host auth