Database back end for Supercard

[cmclane]

For a Supercard project I am considering I think it will be helpful to have a database behind the scenes to store the data. Here is what I have found in a couple quick searches (with my notes added on the Fourth World list):

Found this at Fourth World:

Among the DBMS engines that work with SuperCard are: 

HyperHIT (DAS Works)   < - - May now be ListTable $149
FileFlex (Component Software) < - - Appears to be no longer available
Butler (Everyware Software)     < - - Appears to be no longer available
dtf (dtf Software)   < - - Appears to be no longer available
PoINK SQL (PoINK Software)      < - - Appears to be no longer available

For many applications, simple flat-file data management may do. Fourth World offers an Array XCMD, written by SuperCard Engineer Mark Lucas, which provides true array support and does pretty fast sorts and searches among other things.

 

http://www.fourthworld.com/supercard/FAQ_pages/Q0086.html

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

Found this at Hairy Highland Cow - Mac Stuff:

Update - 24 Nov 2008

 

SQLITE

This is an updated version of Tomas Franzén's external that allows control of SQLite databases. It is a universal binary and supports SC 4.6.

 

http://www.hairyhighlandcow.net/software/SC-Projects.html

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 

 

It appears my choices are Mark's Array XCMD and the SQLITE external. Does anyone have experience with either? Are there other options I should examine? Thanks for any help.

- Charlie

 

[Jonathan Feinstein]

SQLite is a very good product. It is widely used and well supported. You may not need a database engine, but if you do SQLite is a good choice. 

Sent from my iPhone

--
You received this message because you are subscribed to the Google Groups "SuperCard Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supercard-tal...@googlegroups.com.
To post to this group, send email to superca...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/supercard-talk/40fece38-16b6-4c8e-a536-9f56f0c1baf2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Scott]

SQlite is fast, compact, and built into any Mac that would run SuperCard.

BTW, you don't need an external as there are command line functions you can call with the Shell function.

sqlite3 <pathToDatabase> '<sqLite arguments>'

[cmclane]

Jonathan - - Based on your information I downloaded the SQLITE external file from the "Mac Stuff" page just to test it, and really like what a Supercard project can do using SQLite. Thank you.

 

Scott - - Based on your information on the Shell function, I think I would like to just use SQLite from my script(s), but I’m at a bit of a loss as to how to do that. The only thing the User Guide says is:

"Application development and prototyping - SuperCard lets you create all kinds of applications, . . . You can even communicate with other Macintosh applications using AppleScript, and access OS X’s built in Unix shell based commands and functions."

But it doesn’t say how.

Supercard Help says:

"get shell(statement[,waitForTicks][,raw])

Shell is a function which allows you to evaluate command line expressions via the BSD subsystem.

 

See the shell.proj sample project for some examples of what you can do with this powerful feature."

 

But I can’t seem to find the "shell.proj" sample project file in any of the files I received with my recent 4.8 purchase. And I can't find it on the Supercard site.

 

Could you please let me know where I could find the example project file? Thanks.

[codegreen]

You can grab an updated copy here.

HTH,

-Mark

[Scott]

The Shell.Proj is old and not of much use to you here. Simpler if I give you a simple example. Open the Terminal app and type in

ls

followed by a return. Notice the output.

Now create a SuperCard project with a button and a field. In the button script put:

on mouseup
 put shell("ls") into cd fld 1
end mouseup

Note the field's contents when you click the button.

It's that simple. But note when passing SQLite arguments you may need to merge variables and or quoted strings within what you pass to Shell(). It can be pretty complicated to concatenate that all together, and this is why the merge function exists. It allows you to merge a complex SuperTalk expression in a single step. The following is an sqlite3 call to return a record in table1 whose ID is contained in 'it', from a database whose path is contained in gDatabase:

get merge("sqlite3 [[gDatabase]] 'SELECT * FROM table1 WHERE RecordID = `[[it]]`;'")
get shell(it)

Hope this helps!

[Charles McLane]

Thanks Mark.

--
You received this message because you are subscribed to a topic in the Google Groups "SuperCard Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/supercard-talk/tuRDsM1RSIQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to supercard-talk+unsubscribe@googlegroups.com.
To post to this group, send email to supercard-talk@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/supercard-talk/4cbc5ded-cffb-4337-8ec9-cf46c3058fc5%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Charles F. McLane III, PhD

Principal

707 Alexander Road, Suite 206 - Princeton NJ 08540

Direct: 609-919-2701  Fax: 609-987-8488

Email: cmc...@mclaneenv.com

[Charles McLane]

Scott I started to look through the Shell example project but for what I am trying to do your explanation is much simpler and more direct. Thank you.

I tested your first example and, although the folder/file lists are slightly different on my machine between Terminal and SC Field 1, I can see what shell is doing - - running a standard Unix "ls" command.

I looked up the merge command in SC Help and read your second example and it makes sense. At first "it" contains the record id; but then after the merge "it" contains the merged command; so when shell is called "it" contains everything necessary to execute the sqlite3 operation and then store the result in "it".

Thank you everyone for your quick and helpful responses.

- Charlie

On Sat, Jun 23, 2018 at 3:20 PM, Scott <scottin...@gmail.com> wrote:

The Shell.Proj is old and not of much use to you here. Simpler if I give you a simple example. Open the Terminal app and type in

ls

followed by a return. Notice the output.

Now create a SuperCard project with a button and a field. In the button script put:

on mouseup
 put shell("ls") into cd fld 1
end mouseup

Note the field contents.

It's that simple. But note when passing SQLite arguments you may need to merge variables and or quoted strings within what you pass to Shell(). It can be pretty complicated to concatenate that all together, and this is why the merge function exists. It allows you to merge a complex SuperTalk expression in a single step. The following is an sqlite3 call to return a record in table1 whose ID is contained in 'it', from a database whose path is contained in gDatabase:

get merge("sqlite3 [[gDatabase]] 'SELECT * FROM table1 WHERE RecordID = `[[it]]`;'")
get shell(it)

Hope this helps!

--

You received this message because you are subscribed to a topic in the Google Groups "SuperCard Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/supercard-talk/tuRDsM1RSIQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to supercard-talk+unsubscribe@googlegroups.com.
To post to this group, send email to supercard-talk@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/supercard-talk/b8603ea2-d617-4885-9ff4-b28e63776d7c%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Mike Yenco]

For what it's worth, all of my yenco.com apps (Archive, Finance, and iKeeper) are using SQLite databases to handle user data. It works great for me.  I find it incredibly fast and rather efficient (well, at least it is now that someone (thanks Mark) beat me over the head with how inefficiently I was storing things).  I'm using it through the external, mostly because I started with the external and I'm used to it and even have some SuperCard wrapper functions I already wrote around it... but as pointed out here, you can also use the shell to access it.  I can't recall for sure, but the external MIGHT be using an older copy of SQLite than comes with macOS (especially newer versions of macOS).  This isn't generally a concern as the core features of SQLite remain pretty consistent across versions, but it is something to consider when evaluating between going with the external or the shell.  Of course, if you are targeting running your project on older Macs, that can go in the opposite direction and the version of SQLite contained in the external might be newer than what those older versions of macOS might have available via shell.

[cmclane]

Mike this for me adds to the confirmation that SQLite seems to be the way to go. And I now understand (I think) the difference between using SQLite3 directly from SC using Shell and using it via an external. But your post has made me think of other questions:

1. What are "wrapper functions" around an external (User Guide and Help are mum on this)?

2. What is an efficient way to store data as opposed to some inefficient ways that I should avoid?

3. Is there a way to tell what version of SQLite the external is running?

Thanks for your comments on this.

- Charlie

[Mike Yenco]

 1. Just functions to make it easier to interact with the external or the shell commands.  For example, I have this function in my projects for submitting queries to the SQLite external:

function SQLite_Query pID, pQuery, pRowDel, pColDel

  if pRowDel is empty then put "cr" into pRowDel

  if pColDel is empty then put "tab" into pColDel

  put SQLite("query",merge("[[pID]];[[pRowDel]];[[pColDel]][[cr]][[pQuery]]")) into tResult

  if line 1 of tResult = "<<SQLiteError>>" then

    delete line 1 of tResult

    writeLog(tResult)

  end if

  if line 1 of tResult = "false" and line 2 of tResult contains "SQLite failed" then

    delete line 1 of tResult

    writeLog(tResult)

  end if

  return tResult

end SQLite_Query

Ignore the "writeLog"... that is another function I wrote, but you could just as easily substitute an alert or alert sheet and pass along the result in the case of failure as well.  The key here is to look at the put SQLite line.  That is basically how you call on the external normally, but with my function here all I have to do is:

put merge("DELETE FROM Passwords WHERE PasswordID = `[[tPasswordID]]`;") into tQuery

get SQLite_Query(gDBID,tQuery)

It is a lot easier to call on that function every time I have to interact with the database and if I do something wrong it can sometimes even tell me exactly where I messed up in the SQLite syntax.

2. Anything you can do to store a smaller amount of data.  For example, I was storing dates as international date and international time (2018-06-23 20:00:00 -0400) and wasn't thinking much about that.  SQLite has a variety of formats it can search on, but this was the most direct that I could tell.  Thing is, they also have UNIX seconds (which are different from Mac seconds due to a difference in the starting date).  I just never thought about it much, but yeah, seconds look like:  3612629684  and you can convert them back and forth with a function to either add or subtract 2082844800.  For one or two records, the difference in the string size doesn't make much of a difference, but when you have thousands of records the longer string can really start to add up.  You can also reference things from one table to another.  For example, I had the ability for the user to create and assign labels... and then I was storing the actual label name with every single record that the user might have added.  If you put the label into a table of its own, you can then just reference it with a small ID... 1, 2, 3, etc.   So again... a long string like "Very Important" over several hundred or thousand records can be stored as a "1" or a "12" or whatever the ID might be. Potentially HUGE impact.

3. I think the external has a built in function for this try:

put SQLite_Version()

[Mike Yenco]

3.  Oh, that was a scripted function.  The direct call to the external is:

put SQLite("getversion","")

[Charles McLane]

1. Got it. I can see how that makes things much less cumbersome for each call.

2. Thanks for the efficient data storage examples.

3. That displays 3.4.2 in the Message Box, which matches the version shown on the first card of the example project I downloaded from Alec's "Mac Stuff" site. My Mac reports that under High Sierra it has SQLite 3.19.3 installed.

--

You received this message because you are subscribed to a topic in the Google Groups "SuperCard Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/supercard-talk/tuRDsM1RSIQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to supercard-talk+unsubscribe@googlegroups.com.
To post to this group, send email to supercard-talk@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/supercard-talk/474c4834-5205-4d55-bdf8-f555305450be%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Charles McLane]

Both your original scripted function and the direct call report 3.4.2. Thanks for both versions, and for the information and tips you provided.

On Sat, Jun 23, 2018 at 8:34 PM, Mike Yenco <mi...@yenco.com> wrote:

3.  Oh, that was a scripted function.  The direct call to the external is:

put SQLite("getversion","")

--

You received this message because you are subscribed to a topic in the Google Groups "SuperCard Discussion" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/supercard-talk/tuRDsM1RSIQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to supercard-talk+unsubscribe@googlegroups.com.
To post to this group, send email to supercard-talk@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/supercard-talk/e7a10a20-f2cc-4d7c-a007-d785129ed019%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Terence Heaford]

I don’t want to throw a spanner into the works here but I have an external that compiles using the latest sqlite library (3.19.3) built into MacOS.

It is a SC wrapper of a freeware Sqlite implementation called FMDB.

See here:

https://github.com/ccgus/fmdb

Here are some examples of it’s use.

put "CREATE TABLE '" & tAccountName & "'" & " (recID integer primary key autoincrement,date integer, type text, description text, amount currency, balance currency,category text)" into tSQL

put FMDB("executeUpdate","MyDB",tSQL) into tInfo

put "SELECT accountName FROM accountsList where accountType != 'Investment' ORDER BY accountName ASC" into tSQL

put FMDB("executeQueryForFields","MyDB",tSQL,"accountName") into tAccntArray

put merge("DROP TABLE '[[the uAcctName of this project]]'") into tSQL

put FMDB("executeUpdate","MyDB",tSQL) into tInfo

put merge("INSERT OR IGNORE INTO '[[the uAcctName of this project]]' (date,type,description,amount,balance,category) VALUES('[[tDate]]','[[tType]]','[[tDescription]]','[[tAmount]]','[[tBalance]]','[[tCategory]]')") into tSQL

put FMDB("executeUpdate","MyDB",tSQL) into tInfo

put "date,type,description,amount,balance,category,recID" into tFields

put merge ("SELECT [[tFields]] FROM '[[the uAcctName of this project]]' WHERE amount > 0.0 ORDER BY") into tSQL

return FMDB("executeQueryForFields","MyDB",tSQL && the uSortOrder of this cd,tFields)

put merge ("SELECT [[tFields]] FROM '[[the uAcctName of this project]]' WHERE date >= [[tStartDate]] AND date < [[tEndDate]] AND category = '[[tCategory]]' ORDER BY") into tSQL

return FMDB("executeQueryForFields","MyDB",tSQL && the uSortOrder of this cd,tFields)

Here are the functions available:

databaseWithPath

close

databasePath

open

setShouldCacheStatements

executeUpdate

version

executeQuery

executeQueryForFields

beginTransaction

commit

getSchema

getTableSchema

tableExists

columnExists

resultNext

stringForColumn

resultClose

setKey

dispose

If anyone is interested in trying it, they are welcome.

I will need to tidy up the documentation (limited, does not teach SQLite).

Anyone interested, I could post a version with a the docs referred to above sometime in the coming week.

All the best

Terry

[cmclane]

I can say that for myself I would certainly be interested and it is generous of you to offer. But it may be more than I need and a bit over my head right now. 

I checked out the GitHub page and it talks about FMDB, and CocoaPods, and Carthage (none of which I have ever heard of), and installation instructions. I think I just need to focus on learning and testing out the sqlite3 commands, and doing some basic things with the Shell function. If I find out I need more power, or I need something that makes the SC scripting easier and cleaner (as you and Mike have indicated it does) I may reach out to you. But I would hate for you to take the trouble at this point to prepare documentation and everything and then me end up not being able to figure out how to use it. Others who are more experienced scripters, of course, may be interested in testing it right now.

Thanks again for the offer.

- Charlie

[Alec Hole]

Hi,

Sorry, missed this thread– looks like you found the SQLite external on my website. There are wrapper functions in the demo project for the external, if you look through the scripts hopefully it should give you an idea of how to read and write data to a SQLite database. I haven't been able to keep this external up to date with the latest version of SQLite, but I've not found the need for anything beyond version 3.4.2 so far– looks like Terry has that covered now with his new external.

All the best,

Alec

[cmclane]

Alec,

I've only just started exploring the SC project with your database external and at first it seemed like magic that it was picking up my every search keystroke and narrowing the data list in real time. I am beginning to see how some of the script portions (the loops for example) assemble and processes the query. And your documentation if very helpful. As you suggested I am going to study it further to try to better understand the opening, closing, and writing to the database file.

Would you be so kind as to explain what the button located between card field 3 (search results) and card field 4 (query) does? I have looked at its script a couple times now, and it seems that it would trigger on mouseup; but I never actually have to click it because the search is happening in real time as I type.

Charlie

[Bill Bowling]

I mentioned while back that I had a 5gb flat file I trying to work with. Will the shell / sql calls result in spinning beach balls too?

Bill

-- 

You received this message because you are subscribed to the Google Groups "SuperCard Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supercard-tal...@googlegroups.com.

To post to this group, send email to superca...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/supercard-talk/594ce105-de72-47f9-8909-39b584847745%40googlegroups.com.

[Alec Hole]

Hi Charlie,

The top search field uses the 'keyinfield' handler; when you type in it, a global variable is updated which will be noticed by an 'idle' handler in the card script. If you go to the card script you will see the 'idle' handler contains a script that checks if the variable 'doSearch' is true and if so, gets the contents of card field 2 and builds a query that searches the database.

The query it builds using the 'merge' command will output to this, if the search field contained the characters 'fr':

SELECT * FROM people WHERE (firstname LIKE "%fr%" OR lastname LIKE "%fr%" OR country LIKE "%fr%") ORDER BY country

The '%' characters added at the start and end are wildcards, so the search will match words with other characters before or after the 'fr' like 'France' or 'Africa'.

The button you are asking about runs the query inside card field 4. If you type something in the top search field, the built query appears in card field 4. But you can type any query you like in card field 4 and run it by clicking that button with the up arrow in it. Here is an example you can type into card field 4:

SELECT * FROM people WHERE firstname = "Charles" ORDER BY lastname LIMIT 3

When you press the up arrow button, you should see three results appear with the first name 'Charles'. As it is ordered by 'lastname', you get the first three surnames in the alphabet, while the limit of 3 cuts off the number of records at 3. Remove the 'LIMIT 3' and all of the records with the first name 'Charles' should show up when you run the query.

Hope this helps,

Alec

[Scott]

I can't honestly answer your question as I have never worked with a database that large, but SQLite certainly supports it (many times over in fact).

https://sqlite.org/

[cmclane]

Aahhhaaa . . . that is very slick. Now I see what you meant about this being a very useful tool for someone (like me) to learn how to prepare proper searches by typing them into card field 4 and executing them by clicking on the small button. And then the other SQLite commands in the scripts throughout the project will show me how to work with database files and tables. I will continue to explore.

Thanks for the explanation.

- Charlie