Discussion Forums  >  Config Data, JSON, App Refresh

Replies: 23    Views: 230

Fingers Crossed
Veteran developer
Profile
Posts: 234
Reg: Jan 16, 2012
Simcoe, ON
6,390
04/04/15 07:20 PM (4 years ago)

Using SQLLite in Buzztouch - Opinions on how to accomplish

OK, so I've kicked this cat a few times, and I still have SO many things that could benefit from a simple database. For this post, I'll use the example of a contacts list.
I am well aware that I can have a web server and a php script, but I kind of get hung up on multiple data for multiple users, then needing ID's and passwords, and then being secure and and and.
At the end of the day, I would prefer to keep the database local and use it in BT (understandably WITH extra coding being needed). I know that SQLite is already used by BT in it's internal setup.

I reviewed a useful article (http://www.appcoda.com/sqlite-database-ios-app-tutorial/) which had some useful code, and allowed simple work with Databases. I could build on this outside of BT, but BT is such a cool tool I'd rather try and integrate it.

Instead of tackling it from a multitude of angles, I thought I'd ask the gurus here as to how I could go about trying to integrate it with BT, to allow me to then clone those changes and with less changes in the future be able to leverage a SQL database.
I do have the USC plugin,but can't easily reference the fields within, but aside from that, it doesn't provide a list mechanism.

Would it be worthwhile trying to hijack the DATAURL code in my custom coding and try to 'build' a JSON like input based upon the SQL code?
Ultimately (for me), it's about getting the data and being able to pass it onto other BT screens. I've never had a lot of luck being able to do that.

As the example, I'd like to hold the first name, last name and phone number in a SQL database.
Be able to list these in a menu item.
When I select the menu item, flip to another menu/screen (Eg: USC, or a new menu)
Be able to use the Call plugin, or text message plugin from there to send the message to the user/number returned in the database.

Any thoughts/suggestions on how to achieve this internally (not php), and still be able to utilize BT in general.
(Again, I don't expect BT to be able to do this vanilla, but curious on how I should approach a solution)

(I am aware that Parse is an option, and if I can't achieve it I may go that route.)
(Other thread like this: https://www.buzztouch.com/forum/thread.php?tid=0C105D3CCEDAB626CA7D5DC&currentPage=65)


If you're still reading, thanks. I'd appreciate any feedback.

Graham.
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
04/05/15 04:40 AM (4 years ago)
I've done a little work with SQLite and BT, and went so far as to create a plugin for accessing an SQLite database... but the challenge (from the plugin developer side) is that each plugin sorta needs to 'connect' to the database rather than use childItems in the traditional sense, and finding a common way to 'fit all suits' is a lot more than I bargained for. Subsequently I still plan on using it, but I don't think I'll be releasing it into the market.

Therefore, for your entertainment I have a half finished tutorial and a half finished plugin. You're welcome to take a look at either or both. I also did work on the Android side should you get there as well. Just shout out and I'll dig it up. Sometimes I find old work in old projects that weren't finished or set aside. As such, nothing is 'pretty'.

https://dl.dropboxusercontent.com/u/115208762/SW_SmugData.pdf
https://dl.dropboxusercontent.com/u/115208762/SW_SmugData.zip

Hope this helps a bit. Feel free to ask anything you can come up with. The only caveat is, I forget my name after a day, and I haven't worked with this for a few months, so it *might* take a minute to jog my memory...

Cheers!

-- Smug
 
Fingers Crossed
Veteran developer
Profile
Posts: 234
Reg: Jan 16, 2012
Simcoe, ON
6,390
like
04/05/15 05:01 AM (4 years ago)
Hey thanks Smug, I appreciate the share! I don't expect something to work across the board, but once the basics are there, I should be able to clone/edit it.
Eg: Change Firstname to Car Model, Lastname to Car Make and Phone # to Car Year etc.
I'll definitely take a look.
Happy Easter.
 
LA
Aspiring developer
Profile
Posts: 3270
Reg: Aug 16, 2012
Jerseyville, IL
42,800
like
04/05/15 07:19 AM (4 years ago)
Smug,

Where is your plugin?



LA
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
04/05/15 10:21 AM (4 years ago)
I haven't published it. Maybe someday down the road, if we can setup some kind of plugin 'standard' to accept several data sources, it might be viable. But at the moment, there is no way to use it without modifying the code of an existing plugin, and I haven't *quite* figured out how to 'generalize' data aquisition better. Right now everything is pretty specific to the database, so any user would kind of need to know what they want, and can modify the code to accomodate specific needs, not a 'one size fits all'... I need to think a little harder on that.

And I have 'shiny object' syndrome, where I'll be working on one thing, then something else grabs my attention, and I put it aside for a little while... which can end up being months.

Are you working on a Database project?

Cheers!

-- Smug
 
Fingers Crossed
Veteran developer
Profile
Posts: 234
Reg: Jan 16, 2012
Simcoe, ON
6,390
like
04/05/15 07:09 PM (4 years ago)
@SMug,
While it may not be how you want it to be, but if it's explained well enough, I'd be pretty sure people would adopt it as a 'plugin'.
I purchased the 'Instant Database App' and that requires a great deal to customise (xib changes), and didn't unfortunately work as a CSV DB either. So I don't mind hitting code if the instructions are relatively clear.

That said, it could be a world of pain to support...

IF I get it to work however, I do plan on posting something here as I've seen numerous requests, and in many of the other 'app creators', they can't do it either!

Thank you again. I pretty much have the ability to create the databases, so I have that covered, so I'm wading through the code and think I have an understanding of what's going on there too, so now I'll try and slap together a simple menu using SQL options and see what kind of trouble I can get in :)
 
Niraj Shah
buzztouch Evangelist
Profile
Posts: 2937
Reg: Jul 11, 2012
Cerritos
37,870
like
04/05/15 09:02 PM (4 years ago)
Did you know the BuzzTouch Core has a database class in it?

We "discovered" it during the Las Vegas CodeCamp.

Within your BT project, use the Xcode search, find "database" and start with those APIs.

-- Niraj
 
Susan Metoxen
buzztouch Evangelist
Profile
Posts: 1699
Reg: May 01, 2011
Hopkins, Minnes...
26,190
like
04/05/15 09:33 PM (4 years ago)
So you want the database in the app instead of in the cloud? It would be easy to do this with mysql and JSON....SmugWimp and I have work we can share with you. One is specifically a contacts system.
 
LA
Aspiring developer
Profile
Posts: 3270
Reg: Aug 16, 2012
Jerseyville, IL
42,800
like
04/06/15 04:23 AM (4 years ago)
Yes I am Smug! You still have my email?


LA
 
Fingers Crossed
Veteran developer
Profile
Posts: 234
Reg: Jan 16, 2012
Simcoe, ON
6,390
like
04/06/15 06:54 AM (4 years ago)
@Niraj: As I was adding the SQL code from the tutorial I posted I did come across almost exactly the same code :) So I did see it out there. Now I'm trying to get data from a database into a BT screen.

@Susan: Yes, I'll drop you a note and see if that helps fill the gap(s) and I'll update this thread when/if I make progress to assist future seekers :)
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
04/06/15 10:06 AM (4 years ago)
Yeah, that built in code pretty much covers what I was trying to do, lol!

What you might want to do, is look at my code for how I built the childItems, and use that portion in your plugin...

Basically modify the plugin to accept an additional data source... usually it's dataURL, local file, or config.txt childItems... you'll want to add one more option for 'via database'. Then build the childItems and 'feed' them to the 'parseScreenData' method. After that, it's all the same.

Cheers!

-- Smug
 
krompa
Lost but trying
Profile
Posts: 257
Reg: Jun 14, 2013
Bristol
8,820
like
04/07/15 06:09 AM (4 years ago)
Hi All

Smug - the .zip link in your first post is broken - any chance you could mend?

Susan - I would love to see your work with Smug on mySQL / JSON. I have been tinkering on the edges of databases for quiz leaderboards but I'm not getting very far. Any java to mySQL examples will help me, even if they don't relate to the quiz plugin.

Many thanks.
Krompa
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
04/07/15 06:54 AM (4 years ago)
Krompa,

The link should be fixed; apologies.

Susan mentioned the wb_addons. Great stuff. I keep a reasonably recent copy here:

https://dl.dropboxusercontent.com/u/115208762/wb_addons.zip

but that may change in a few months, if I ever get off my dead duff and fixup my website.

a 'companion' video is located here: https://www.youtube.com/watch?v=7s2h9x30loc

I know I did more in Eclipse/Java than what I can show for... it's in a project 'somewhere', but I can't seem to find it right now. All I have is this:

https://dl.dropboxusercontent.com/u/115208762/SW_SmugData.java.zip

but you'll need to have more than that for it to work. If I can find my code, I'll be happy to share. But since David did most of it already, it might be worth exploiting his code for use, and my code as some kind of twisted example of building childItems from the database once it's accessed.

Cheers!

-- Smug

@LA, the wb_addOns are what you want to work with first. Get it into your self hosted panel, and 'design' the output via php file. Let me know if you have questions! :)
 
krompa
Lost but trying
Profile
Posts: 257
Reg: Jun 14, 2013
Bristol
8,820
like
04/07/15 07:12 AM (4 years ago)
Thanks Smug, i'll take a look
 
LA
Aspiring developer
Profile
Posts: 3270
Reg: Aug 16, 2012
Jerseyville, IL
42,800
like
04/07/15 07:25 AM (4 years ago)
Smug,

The wb_addOns downloaded and working on them now. I'm in the process of migrating to hostgator.



LA
 
Fingers Crossed
Veteran developer
Profile
Posts: 234
Reg: Jan 16, 2012
Simcoe, ON
6,390
like
04/07/15 07:32 AM (4 years ago)
Thanks @ Smug & Susan, the wb_addons did work fine in Self Hosted. I'll play with these for a bit.

While originally I was trying to get it all working offline inside the app, this I think will be sufficient, and potentially more flexible.
I did like that I can hold multiple entries in the same database, but for different apps (via the appGuid)
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
04/07/15 07:41 AM (4 years ago)
@fingers crossed:

Once you get comfortable with the wb_addOns, you may still want to look at an internal SQLite solution inside the app.

What you're really doing is replacing the php functions with ObjC/Java. It's all related, albiet sometimes distantly, but once the 'logic' is in place, it's easy to correlate what needs to be done within a Class, whether it is php, ObjC, or Java. And although there is no 'direct' translation of functions and procedures, it's still a lot easier.

Cheers!

-- Smug
 
chris1
Code is Art
Profile
Posts: 3860
Reg: Aug 10, 2012
Oklahoma City, ...
50,100
like
04/19/15 05:20 PM (4 years ago)
How'd you come along with this? I actually created the BT_database class (SQLite wrapper), so let me know what questions you have. I think there's a bug in one of the lookup methods, so if you come across that let me know and I'll send you a fix. I also have the equivalent Android class completed. I emailed it to David, but I think it's probably in the middle of a long list for him to get to :)
 
Fingers Crossed
Veteran developer
Profile
Posts: 234
Reg: Jan 16, 2012
Simcoe, ON
6,390
like
04/19/15 07:10 PM (4 years ago)
Smug & Susan have been most helpful with the wbaddons, I've got that working. It's not the SQL class, but I was working on baby steps :)
Currently looking at some php builders to create the maintenance screens etc.
(I've been on vacation for the last week or so, so I didn't make much progress)

At the end of the day, the nirvana I'm hoping for is to be able to use the USC plugin, and use those fields/values in a SQL database with more ease than we appear to have now.
I think that's a ways away, until then it would have to be what Susan/Smug shared :)

Once I have that figured out, I"ll likely come back to using these classes, as it's closer to what I wanted to achieve.
Thanks for popping in though.
 
chris1
Code is Art
Profile
Posts: 3860
Reg: Aug 10, 2012
Oklahoma City, ...
50,100
like
04/19/15 07:15 PM (4 years ago)
Ah - yeah, the Ultimate Screen Creator plugin has a lot of code to sort through, for sure! But, it's definitely possible to save and load values into a SQLite database on the device from a screen using that plugin. Actually, it shouldn't be very difficult at all. There's already a method called "saveDataToDevice" and one called "loadSavedDataToFields", so these would be the methods to alter to use SQLite. (Currently they use Shared Preferences).

If you want an example of how the BT_database class might be used, check out the Checklist Pro plugin. It makes use of it to save and load data to a SQLite database. Actually, I think it uses a class called CR_database, but it's the same thing. It was essentially what I created before it landed into the core project.
 
Niraj Shah
buzztouch Evangelist
Profile
Posts: 2937
Reg: Jul 11, 2012
Cerritos
37,870
like
04/19/15 10:04 PM (4 years ago)
Chris -- can you post those items?

1. Bug fix in the lookup method

2. Android equivalent to the Database class
 
Niraj Shah
buzztouch Evangelist
Profile
Posts: 2937
Reg: Jul 11, 2012
Cerritos
37,870
like
04/19/15 10:09 PM (4 years ago)
Graham -- what PHP Builder tools have you found useful?

Before there were mobiles, I was an avid user of PHPmyEdit. A few years ago, a buddy found MATE -- MySQL AJAX Table Editor.

I agree that we need any easy way to create the content maintenance screens for the database items.

Let us know what you discover, even if you eventually had to discard those found diamonds.

-- Niraj
 
Fingers Crossed
Veteran developer
Profile
Posts: 234
Reg: Jan 16, 2012
Simcoe, ON
6,390
like
04/28/15 07:44 PM (4 years ago)
Just to keep you up to speed, at the moment I'm taking a brief redirection on the SQL front. I noticed that the Corona SDK is now free, and it supports SQL out of the box. While its not GUI driven like BT, (It's LUA code), but I found it a breeze to prototype an app in a couple of hours.
Once I get back to this on BT, I'll post an update :)

In the mean time, I'll see how good/bad/indifferent Corona is!
 
SmugWimp
Smugger than thou...
Profile
Posts: 6316
Reg: Nov 07, 2012
Tamuning, GU
81,410
like
04/28/15 07:49 PM (4 years ago)
Corona isn't too bad... But I prefer Dos Equis, myself.

Stay thirsty, my friend.

Cheers!

-- Smug
 

Login + Screen Name Required to Post

pointerLogin to participate so you can start earning points. Once you're logged in (and have a screen name entered in your profile), you can subscribe to topics, follow users, and start learning how to make apps like the pros.