08-21-2013 01:15 AM - edited 08-21-2013 01:24 AM
The sample code I've found for BB10 / BBUI.js that uses SQLite database seems to rely on jQuery.
I know nothing about jQuery and what I'm trying to do is create an app that will read and write to a local SQLite database, just as a prototype, in an effort to demonstrate what the app should do. I don't expect this code to be the fastest or most efficient until it's optimzed, but I'm finding that every time I attempt to implement another aspect of my app, I'm having to reverse-engineer or learn about another library, rather than just review code that works in and of itself.
My app needs to create a SQLite database with about 15 tables: some of them will be populated with items that will be used to populate dropdown lists on data entry forms (units of measure, descriptions of areas in buildings, etc.) while others will need to be created as empty tables that will accept and store the data entered by the user over time.
I know how to do this in a Web database (LAMP) application and hoped it would be relatively straightforward albeit with different syntax than I'm used to... so I'm more than a bit frustrated and almost feel like giving up.
Even the code here:
which I'm sure is excellent and efficient, relies on jQuery to reference local .SQL scripts, and to me, reviewing that code raises more questions than provides answers.
I just want to do the equivalent of:
When the app is launched for the first time, the database won't exist.
The tables won't exist either.
Create them, using an .SQL file full of standard "CREATE TABLE" SQL commands.
The tables will all be empty
Populate them, using a .SQL file full of standard "INSERT" SQL commands.
When a specific screen is loaded, it will need to be able to hit the database with a "SELECT" statement and iterate through the records and create the UI components and when the user long-presses on an item in a list, call a function that will let them choose what they want to do with that item... or they press an Action button to create a New item, and they're shown a form and they enter the data (including choose options from dropdown lists pre-populated with choices from tables in the database) and when they press "SAVE", the "INSERT" statement is run to put the record into the database.
Maybe I'm wrong to expect it to be simpler than it is, but although I understand that libraries like jQuery are a boon to HTML5 app development, I hoped I'd be able to start simply without having to take a master class in multi-dimensional object modeling or whatever.
Maybe I should just use "localStorage"? But the data I need to store and retrieve is more complex than name-value pairs... I suppose I could use localStorage to store JSON objects but that seems like an ugly hack to store data for 15 tables some of which have up to 15 fields.
Thanks in advance for any advice you could give, or any simple example code you could point me at.
08-22-2013 10:17 PM
Got your PM: yes I was replying to myself ( because I don't want another helpful member wasting their time if i've found my own answer.)
08-23-2013 02:50 AM
On a more positive note I'm planning on writing a SQLite3 extension in early September
I'm just finishing Scoreloop right now so wanna get that one out of the way first
I want these technologies for products myself and some friends in Forums are working towards
Everything I write ends up in the BB GitHub
If you want a peek @ things while I'm developing check https://github.com/peardox/bb10webworks-ext but beware as those are likely to have bugs until I move them into the release repo
08-23-2013 07:05 PM
Thanks very much for that... I'm going to forge ahead with my app on my own, it's an app I want for myself as much as I think I can publish it and possibly earn some spending money from it in the long run... may be able to turn it into an actual business if I play my cards right... but I have read a lot of your posts helping others, and I look forward to your SQLite extension... if you could model the APIs after MySQL / PHP, you'd probably get marriage proposals :-)
08-23-2013 07:19 PM
As long as you're female I might take you up on the offer
To be honest any heavy DB work is best suited to a server taking on the work with the device doing JSON calls
There are exceptions to this of course
If your model is single user and mostly read transactions then SQLite will be fine - this is what I have planned for an app I intend to develop after I write the extension
One interesting idea I've thought about is writing a remote MySQL interface - its not that hard and caters for a distributed DB - then again the JSON route works just as well so I'm ambivolent over this concept
PM / Friend me and I'll give you early access to the beta SQLite extension after I finish v1 of Scoreloop
08-23-2013 10:57 PM
08-24-2013 06:21 AM
For god sake don't use filereader
Give me an API to follow and I'll write it that way (maybe)
I like the MySQL / PHP model as it is familiar to many of us
I tend to simplify things as many C libraries require do this, jump throught that hoop, now take the result and mess about in some way - not fun (look @ my Scoreloop code <g>)
I tend to abstract this to a simple one liner - do this does as expected, I hide the internals
Most of my PHP includes open_mysql_link
It does what it says making for a very simple invocation and return of the link
Why complicate things for the end user - if I can condence things to something simple give them that version
I do the same stuff with complicated libraries - the developer wants open this, do that, do something else
I welcome any input you may have on making the SQLIte extension easy
I'm thinking open this, read that, write the other is suffiecient in most cases