Doing Database

Need help with a script? This is the place to discuss how to get your code running!

Doing Database

Postby JoeKoomen2011 » Thu Jul 06, 2017 4:59 am

I have a large project (about 14,000 cards) and am considering converting it into something more efficient. Specifically exporting all the data to a text file and using it as a database. I know that there are a few externals out there that allow access to true MySQL database structures but I worry about using any externals that might get orphaned, so I only use externals created by Solutions Etc.

I have a ton of questions. :D

I have exported every field to an RTF file and discovered how much bigger the data is outside of SuperCard. My project is 19.6 MB while the exported data is 21.5 MB. With this 2 MB disparity always be about the same or will it get worse as the database gets bigger?

I use characters not accessible on the keyboard to separate records. I’ve looked at the gobbledegook that is RTF mark-up and it looks like other metadata/HTML mark-up using regular keyboard characters. Is there any chance that RTF uses any of my separator characters?

[code]
global gFS, gGS, gRS, gUS
-- FS = File Separator
-- GS = Group Separator
-- RS = Record Separator
-- US = Unit Separator
put numToChar(28) into gFS
put numToChar(29) into gGS
put numToChar(30) into gRS
put numToChar(31) into gUS
[\code]

While the RTF data is 21.5 MB, the straight text export results in a file size on only 2.3 MB. Does it make sense to keep two databases? I was thinking that keeping the plain text data in a global wouldn’t be a hardship for my processor and would give me pretty quick access to data for sorting and compiling different lists and chunks of data. I would only need to access the RTF data for display on screen when looking at individual records.

I figure I would keep track of which record was being displayed, and any changes to the data would be put back into the same position int the database. Any new records would get tacked on at the end, so it would be just as easy to pop data into two files as one.

Any other suggestions/pitfalls to look forward to as I move in this direction? I’d love to hear from anyone else whose doing this kind of thing with SuperCard. I’ve never done any kind of database work outside of messing with MS Excel, so this is all new to me.
Joe Koomen
>> Random! ...Damn near killed 'em! <<
User avatar
JoeKoomen2011
 
Posts: 470
Joined: Thu Mar 12, 2009 1:38 pm

Re: Doing Database

Postby Scott » Thu Jul 06, 2017 6:30 am

Rtf includes lots of format info, that's why it is bigger. The OS X shell includes all the necessary functions for SQLite 3, which is built into MacOS, so you can use that (no externals required).
User avatar
Scott
Site Admin
 
Posts: 1226
Joined: Sat Jul 05, 2008 1:37 pm
Location: Northern Sierra Nevada

Re: Doing Database

Postby JoeKoomen2011 » Thu Jul 06, 2017 10:27 am

Woah! Now there's a whole other can of worms.

Thanks Scott.
Joe Koomen
>> Random! ...Damn near killed 'em! <<
User avatar
JoeKoomen2011
 
Posts: 470
Joined: Thu Mar 12, 2009 1:38 pm

Re: Doing Database

Postby sctell » Thu Jul 06, 2017 11:20 am

Scott wrote:The OS X shell includes all the necessary functions for SQLite 3, which is built into MacOS, so you can use that (no externals required).


In my experience, although it does work, it is slow and unwieldy.

I did port FMDB for my own use but although FMDB has been updated and continues to be maintained I have not updated my port to the latest FMDB as it still does what I want.

FMDB can be found here:

https://github.com/ccgus/fmdb

It is possible (have experimented, a couple of days ago) to download FMDB compile it in Xcode to a framework, place it in your frameworks folder and access it with ApplescriptObjC from an AppleScript. Whether or not that performs any better than OSX Shell I am not convinced.

Supercard does need an officially supported SQLite external, this would give the performance improvements needed to use Sqlite for anything that Supercard could create.

With what appears to be on Marks plate, I would not hold my breath.

I was also thinking of using the array external as a Database but having looked through the docs there does not appear to be anyway to do selections. There is another external referred to in the docs
(More advanced search capabilities are available via the ExtractArray XFCN, also included in this collection.)
but I have never come across this external so it may be out of date and even the array external may not be fully functional.

Perhaps these externals could be updated so that you could load your data from a text file with delimiters and manipulate the data within the project, then save it at some particular point.


All the best

Terry
sctell
 
Posts: 1130
Joined: Sun Jul 06, 2008 10:41 am

Re: Doing Database

Postby vinnie-bob » Thu Jul 06, 2017 6:48 pm

I don't know what information your cards contain, but if possible, I would shoot for using a plain text file with tab or comma separated data. If the data allows…. then you just have to write to a text file, which will be quick. Plus you can import it into any commercial database to search or manipulate it. (As an aside, with a plain comma or tab delimited text file, you could sort it — on any field — using unix 'awk' command, and search it with 'grep' - heh, who needs a database program?)

Second thought would be to use JSON to store your data — more info on this is available online as it is the hot new format for data storage and portability. There is a free JSON 'plug in' for applescript available, and you can also use a RubyGem if you know Ruby.

The SQLite available via the unix command is also doable, but I find searching with SQLite a bit tedious. It technically will be a little slower if you are using a shell script in SC, but what the heck, let it run overnight, who's that gonna bother?
------
vince
------
User avatar
vinnie-bob
 
Posts: 229
Joined: Sun Jul 06, 2008 10:55 am
Location: Des Moines, Iowa, USA


Return to Scripting in SuperTalk

Who is online

Users browsing this forum: No registered users and 1 guest