Choosing a database and LC tools

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Choosing a database and LC tools

tbodine
Hi,

I want to add a content library module to a LiveCode-built project. It will enable users to store, tag, sort and retrieve their content. I've read various threads and articles about databases, but I have minimal database experience and I want to get it right the first time. So I'd appreciate answers to these questions and any other insights:

1) Is SQLite the best choice for local database file with a Livecode interface? (Each database record will need to hold a few sentences of Unicode text, 1 or 2 small arrays, the text contents of a few cprops, and a field for tags the user can apply to categorize the various records.) I doubt the number of records would ever exceed 10,000 with an average more like 2,000.

2) The content of some records may have associated images or audio files. I can store the paths to those, but paths break easily. How much does it bloat or burden a database to store jpgs, pngs, wavs or aifs files in records?

3) I see there are a few 3rd-party tools for database work with Livecode. (SQLyoga, SQLiteAdmin and SQLMagic). Are there others? Are these current with LC7?

4) Are there contract developers in the LC community that specialize in setting up databases with LC interfaces?

Thanks!
Tom B.
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

Mark Talluto

> On Aug 10, 2015, at 9:36 AM, tbodine <[hidden email]> wrote:
>
> 1) Is SQLite the best choice for local database file with a Livecode
> interface? (Each database record will need to hold a few sentences of
> Unicode text, 1 or 2 small arrays, the text contents of a few cprops, and a
> field for tags the user can apply to categorize the various records.) I
> doubt the number of records would ever exceed 10,000 with an average more
> like 2,000.
>
> 2) The content of some records may have associated images or audio files. I
> can store the paths to those, but paths break easily. How much does it bloat
> or burden a database to store jpgs, pngs, wavs or aifs files in records?
>
> 3) I see there are a few 3rd-party tools for database work with Livecode.
> (SQLyoga, SQLiteAdmin and SQLMagic). Are there others? Are these current
> with LC7?
>
> 4) Are there contract developers in the LC community that specialize in
> setting up databases with LC interfaces?

Hi Tom,

Another 3rd party option is CassiaDB. The local database is free. You can learn more about it at LiveCloud.io.
If you have any questions about it, feel free to write us.

Best regards,

Mark Talluto
canelasoftware.com

CassiaDB: The easy to use, free local storage database made for LiveCode Developers: livecloud.io




_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

Peter Haworth
In reply to this post by tbodine
Hi Tom,
SQLite should do what you need but a few notes for you.

SQLite doesn't have an array datatype.  The usual way of handling this type
of data in any SQL database is to store each key and value of the array in
a separate table that is linked to your main table by an id of some sort
that would have the same value in both tables.

You could also get round it by arrayEncoding them before putting them in
the database and then arrayDecoding them on the way out.

It's pretty easy to store images in columns with a data type of BLOB,
although it requires some slightly different LC coding to SELECT them.
Also be aware that LC used to encode BLOB data in a proprietary way but
then removed the encoding in a release which I can't quite remember.  Only
an issue if you expect to access the database with versions of LC both
before and after the change.  Plus it's controllable via an sqliteoption
parameter for revOpenDatabase.

I will contact you off list re contract work and a couple of other
suggestions.

Pete


On Mon, Aug 10, 2015 at 9:40 AM tbodine <[hidden email]>
wrote:

> Hi,
>
> I want to add a content library module to a LiveCode-built project. It will
> enable users to store, tag, sort and retrieve their content. I've read
> various threads and articles about databases, but I have minimal database
> experience and I want to get it right the first time. So I'd appreciate
> answers to these questions and any other insights:
>
> 1) Is SQLite the best choice for local database file with a Livecode
> interface? (Each database record will need to hold a few sentences of
> Unicode text, 1 or 2 small arrays, the text contents of a few cprops, and a
> field for tags the user can apply to categorize the various records.) I
> doubt the number of records would ever exceed 10,000 with an average more
> like 2,000.
>
> 2) The content of some records may have associated images or audio files. I
> can store the paths to those, but paths break easily. How much does it
> bloat
> or burden a database to store jpgs, pngs, wavs or aifs files in records?
>
> 3) I see there are a few 3rd-party tools for database work with Livecode.
> (SQLyoga, SQLiteAdmin and SQLMagic). Are there others? Are these current
> with LC7?
>
> 4) Are there contract developers in the LC community that specialize in
> setting up databases with LC interfaces?
>
> Thanks!
> Tom B.
>
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/Choosing-a-database-and-LC-tools-tp4694777.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

Wprothero
Tom:
I use Navicat to manage my databases. It will access a variety of online databases, and works with SQLite too. I use it all the time.
In my app work, I use livecode with POST commands to php that talks to an online mySQL database. PHP is totally robust and won’t fail on you, and it’s built into most Apache installations. On Mac, there is also a great free tool called MAMP, which allows you to run a complete web server with php on your local machine, for debugging. Again, Navicat is your friend and will save you hours of wondering whether your data actually got into the db or not.

I read that storage of images in a mySQL database isn’t recommended. But, I have stored them in SQLite db’s and it worked fine. Haven’t tried it on mySQL.

I will send you some php scripts, if you are interested. Contact me offline.
Best,
Bill

> On Aug 10, 2015, at 11:01 AM, Peter Haworth <[hidden email]> wrote:
>
> Hi Tom,
> SQLite should do what you need but a few notes for you.
>
> SQLite doesn't have an array datatype.  The usual way of handling this type
> of data in any SQL database is to store each key and value of the array in
> a separate table that is linked to your main table by an id of some sort
> that would have the same value in both tables.
>
> You could also get round it by arrayEncoding them before putting them in
> the database and then arrayDecoding them on the way out.
>
> It's pretty easy to store images in columns with a data type of BLOB,
> although it requires some slightly different LC coding to SELECT them.
> Also be aware that LC used to encode BLOB data in a proprietary way but
> then removed the encoding in a release which I can't quite remember.  Only
> an issue if you expect to access the database with versions of LC both
> before and after the change.  Plus it's controllable via an sqliteoption
> parameter for revOpenDatabase.
>
> I will contact you off list re contract work and a couple of other
> suggestions.
>
> Pete
>
>
> On Mon, Aug 10, 2015 at 9:40 AM tbodine <[hidden email]>
> wrote:
>
>> Hi,
>>
>> I want to add a content library module to a LiveCode-built project. It will
>> enable users to store, tag, sort and retrieve their content. I've read
>> various threads and articles about databases, but I have minimal database
>> experience and I want to get it right the first time. So I'd appreciate
>> answers to these questions and any other insights:
>>
>> 1) Is SQLite the best choice for local database file with a Livecode
>> interface? (Each database record will need to hold a few sentences of
>> Unicode text, 1 or 2 small arrays, the text contents of a few cprops, and a
>> field for tags the user can apply to categorize the various records.) I
>> doubt the number of records would ever exceed 10,000 with an average more
>> like 2,000.
>>
>> 2) The content of some records may have associated images or audio files. I
>> can store the paths to those, but paths break easily. How much does it
>> bloat
>> or burden a database to store jpgs, pngs, wavs or aifs files in records?
>>
>> 3) I see there are a few 3rd-party tools for database work with Livecode.
>> (SQLyoga, SQLiteAdmin and SQLMagic). Are there others? Are these current
>> with LC7?
>>
>> 4) Are there contract developers in the LC community that specialize in
>> setting up databases with LC interfaces?
>>
>> Thanks!
>> Tom B.
>>
>>
>>
>> --
>> View this message in context:
>> http://runtime-revolution.278305.n4.nabble.com/Choosing-a-database-and-LC-tools-tp4694777.html
>> Sent from the Revolution - User mailing list archive at Nabble.com.
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Http://es.earthednet.org
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

PystCat
Hi Bill,

If you wouldn't mind... I would love to see some of those PHP scripts... I've always used MySQL locally but I was thinking of branching out to use it on the web.  I have a Founders account but never really use the MySQL from there... I never did like the (lack of) security.

Thanks
Paul



> On Aug 10, 2015, at 9:15 PM, William Prothero <[hidden email]> wrote:
>
> Tom:
> I use Navicat to manage my databases. It will access a variety of online databases, and works with SQLite too. I use it all the time.
> In my app work, I use livecode with POST commands to php that talks to an online mySQL database. PHP is totally robust and won’t fail on you, and it’s built into most Apache installations. On Mac, there is also a great free tool called MAMP, which allows you to run a complete web server with php on your local machine, for debugging. Again, Navicat is your friend and will save you hours of wondering whether your data actually got into the db or not.
>
> I read that storage of images in a mySQL database isn’t recommended. But, I have stored them in SQLite db’s and it worked fine. Haven’t tried it on mySQL.
>
> I will send you some php scripts, if you are interested. Contact me offline.
> Best,
> Bill
>
>> On Aug 10, 2015, at 11:01 AM, Peter Haworth <[hidden email]> wrote:
>>
>> Hi Tom,
>> SQLite should do what you need but a few notes for you.
>>
>> SQLite doesn't have an array datatype.  The usual way of handling this type
>> of data in any SQL database is to store each key and value of the array in
>> a separate table that is linked to your main table by an id of some sort
>> that would have the same value in both tables.
>>
>> You could also get round it by arrayEncoding them before putting them in
>> the database and then arrayDecoding them on the way out.
>>
>> It's pretty easy to store images in columns with a data type of BLOB,
>> although it requires some slightly different LC coding to SELECT them.
>> Also be aware that LC used to encode BLOB data in a proprietary way but
>> then removed the encoding in a release which I can't quite remember.  Only
>> an issue if you expect to access the database with versions of LC both
>> before and after the change.  Plus it's controllable via an sqliteoption
>> parameter for revOpenDatabase.
>>
>> I will contact you off list re contract work and a couple of other
>> suggestions.
>>
>> Pete
>>
>>
>> On Mon, Aug 10, 2015 at 9:40 AM tbodine <[hidden email]>
>> wrote:
>>
>>> Hi,
>>>
>>> I want to add a content library module to a LiveCode-built project. It will
>>> enable users to store, tag, sort and retrieve their content. I've read
>>> various threads and articles about databases, but I have minimal database
>>> experience and I want to get it right the first time. So I'd appreciate
>>> answers to these questions and any other insights:
>>>
>>> 1) Is SQLite the best choice for local database file with a Livecode
>>> interface? (Each database record will need to hold a few sentences of
>>> Unicode text, 1 or 2 small arrays, the text contents of a few cprops, and a
>>> field for tags the user can apply to categorize the various records.) I
>>> doubt the number of records would ever exceed 10,000 with an average more
>>> like 2,000.
>>>
>>> 2) The content of some records may have associated images or audio files. I
>>> can store the paths to those, but paths break easily. How much does it
>>> bloat
>>> or burden a database to store jpgs, pngs, wavs or aifs files in records?
>>>
>>> 3) I see there are a few 3rd-party tools for database work with Livecode.
>>> (SQLyoga, SQLiteAdmin and SQLMagic). Are there others? Are these current
>>> with LC7?
>>>
>>> 4) Are there contract developers in the LC community that specialize in
>>> setting up databases with LC interfaces?
>>>
>>> Thanks!
>>> Tom B.
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://runtime-revolution.278305.n4.nabble.com/Choosing-a-database-and-LC-tools-tp4694777.html
>>> Sent from the Revolution - User mailing list archive at Nabble.com.
>>>
>>> _______________________________________________
>>> use-livecode mailing list
>>> [hidden email]
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

tbodine
In reply to this post by tbodine
Thanks guys for the information on this! Very helpful.
Tom Bodine
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

tbodine
In reply to this post by Wprothero
Hi Bill.

Thanks for those insights. I'm not sure which route I'll take yet, but I
would welcome access to your php script collection.

Thanks,
Tom Bodine


On 8/10/2015 8:12 PM, Wprothero [via Runtime Revolution] wrote:

> Tom:
> I use Navicat to manage my databases. It will access a variety of online
> databases, and works with SQLite too. I use it all the time.
> In my app work, I use livecode with POST commands to php that talks to
> an online mySQL database. PHP is totally robust and won’t fail on you,
> and it’s built into most Apache installations. On Mac, there is also a
> great free tool called MAMP, which allows you to run a complete web
> server with php on your local machine, for debugging. Again, Navicat is
> your friend and will save you hours of wondering whether your data
> actually got into the db or not.
>
> I read that storage of images in a mySQL database isn’t recommended.
> But, I have stored them in SQLite db’s and it worked fine. Haven’t tried
> it on mySQL.
>
> I will send you some php scripts, if you are interested. Contact me
> offline.
> Best,
> Bill
=============================================

*Bodine Training Games LLC*

8417 Hallet St., Lenexa, KS 66215 USA

www.bodinetraininggames.com / 913-492-7709

Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

Wprothero
Tom and Peter:
Here is a link to a working stack, that includes the php scripts, so you can see what’s involved.
https://www.dropbox.com/s/zeldbpafcnno3x5/Db Access.zip?dl=0 <https://www.dropbox.com/s/zeldbpafcnno3x5/Db%20Access.zip?dl=0>
This link won’t last forever, so I’ll plan on keeping it active for a week.
Good luck,
Bill

> On Aug 11, 2015, at 11:24 AM, tbodine <[hidden email]> wrote:
>
> Hi Bill.
>
> Thanks for those insights. I'm not sure which route I'll take yet, but I
> would welcome access to your php script collection.
>
> Thanks,
> Tom Bodine
>
>
> On 8/10/2015 8:12 PM, Wprothero [via Runtime Revolution] wrote:
>> Tom:
>> I use Navicat to manage my databases. It will access a variety of online
>> databases, and works with SQLite too. I use it all the time.
>> In my app work, I use livecode with POST commands to php that talks to
>> an online mySQL database. PHP is totally robust and won’t fail on you,
>> and it’s built into most Apache installations. On Mac, there is also a
>> great free tool called MAMP, which allows you to run a complete web
>> server with php on your local machine, for debugging. Again, Navicat is
>> your friend and will save you hours of wondering whether your data
>> actually got into the db or not.
>>
>> I read that storage of images in a mySQL database isn’t recommended.
>> But, I have stored them in SQLite db’s and it worked fine. Haven’t tried
>> it on mySQL.
>>
>> I will send you some php scripts, if you are interested. Contact me
>> offline.
>> Best,
>> Bill
> =============================================
>
> *Bodine Training Games LLC*
>
> 8417 Hallet St., Lenexa, KS 66215 USA
>
> www.bodinetraininggames.com / 913-492-7709
>
>
>
>
>
> --
> View this message in context: http://runtime-revolution.278305.n4.nabble.com/Choosing-a-database-and-LC-tools-tp4694777p4694819.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Http://es.earthednet.org
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

Wprothero
Tom and Peter:
Also, please note that:
Best practice involves NOT sending a complete query, which I use in the demo. It’s not so bad, since I require a password as a sent parameter, and since it is intended that the query be generated by livecode in a protected script (not in an input field that anybody can access). It’s not a horrible security issue, but easily breakable by someone monitoring wifi or other network traffic or using a keylogger, etc. But, for critical information, you should modify the php code to use placeholders for the sql parameters and use ssh protocols to send the commands. I will implement this at some time soon, but don’t have it now.

Glad to share when I get it done.

Best,
Bill

> On Aug 11, 2015, at 11:54 AM, William Prothero <[hidden email]> wrote:
>
> Tom and Peter:
> Here is a link to a working stack, that includes the php scripts, so you can see what’s involved.
> https://www.dropbox.com/s/zeldbpafcnno3x5/Db Access.zip?dl=0 <https://www.dropbox.com/s/zeldbpafcnno3x5/Db%20Access.zip?dl=0>
> This link won’t last forever, so I’ll plan on keeping it active for a week.
> Good luck,
> Bill
>
>> On Aug 11, 2015, at 11:24 AM, tbodine <[hidden email]> wrote:
>>
>> Hi Bill.
>>
>> Thanks for those insights. I'm not sure which route I'll take yet, but I
>> would welcome access to your php script collection.
>>
>> Thanks,
>> Tom Bodine
>>
>>
>> On 8/10/2015 8:12 PM, Wprothero [via Runtime Revolution] wrote:
>>> Tom:
>>> I use Navicat to manage my databases. It will access a variety of online
>>> databases, and works with SQLite too. I use it all the time.
>>> In my app work, I use livecode with POST commands to php that talks to
>>> an online mySQL database. PHP is totally robust and won’t fail on you,
>>> and it’s built into most Apache installations. On Mac, there is also a
>>> great free tool called MAMP, which allows you to run a complete web
>>> server with php on your local machine, for debugging. Again, Navicat is
>>> your friend and will save you hours of wondering whether your data
>>> actually got into the db or not.
>>>
>>> I read that storage of images in a mySQL database isn’t recommended.
>>> But, I have stored them in SQLite db’s and it worked fine. Haven’t tried
>>> it on mySQL.
>>>
>>> I will send you some php scripts, if you are interested. Contact me
>>> offline.
>>> Best,
>>> Bill
>> =============================================
>>
>> *Bodine Training Games LLC*
>>
>> 8417 Hallet St., Lenexa, KS 66215 USA
>>
>> www.bodinetraininggames.com / 913-492-7709
>>
>>
>>
>>
>>
>> --
>> View this message in context: http://runtime-revolution.278305.n4.nabble.com/Choosing-a-database-and-LC-tools-tp4694777p4694819.html
>> Sent from the Revolution - User mailing list archive at Nabble.com.
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Http://es.earthednet.org
Reply | Threaded
Open this post in threaded view
|

Re: Choosing a database and LC tools

Bob Sneidar-2
In reply to this post by PystCat
If you use mySQL, (and perhaps others) there is an attribute for binary data. You should be able to safely store image data in such a column.

Bob S


On Aug 10, 2015, at 20:44 , PystCat <[hidden email]<mailto:[hidden email]>> wrote:

I read that storage of images in a mySQL database isn’t recommended. But, I have stored them in SQLite db’s and it worked fine. Haven’t tried it on mySQL.

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode