SuperCard as a user's interface for a database

[Ramanoir]

Le mardi 24 août 2021 à 18:31:59 UTC-4, Scott a écrit :

We used to use FoxBase (before it acquired and killed by MicroSoft) for our customer data. Now a days I use SuperCard. The project manages several tens of thousands of records, but the project only contains one card. You can store terabytes of data in an SQLite data file, and use SuperCard as a front end to search, display, and edit that data.

-----

If my memory is correct, there used to be some examples and tutorial for this in the previous web Forum! Would have some material, examples, tutorials still available to perform such an installation? With SQLite for instance?

I used to do about the same with the combination of HyperCard and WindowScript. One WindowScript window would allow the access to hundred of thousands of cards. HyperCard was used a database and as the scripting engine for the interface. 

Parts of that project are still in use nowadays running under Classic! The other parts are currently being progressively ported to SuperCard, replacing both HC and WS, with encouraging success. 

It would be great to have a professionally made set of functions to perform the tasks of a multi users, networkable database within SuperCard!

Best regards,

[Scott]

I am not sure just how much you're looking for... as it is more about learning SQLite than anything else.

https://www.sqlite.org/lang.html

All of the commands and functions are available via the shell in MacOS, and hence available using SuperCard's shell function. Some example handlers include...

-- 1.) Setting the database file:

on SetDatabase

  global gDatabase, theID

  put "Stuff:File Server:registrations.db" into gDatabase

  if not exists(file,gDatabase) then

    beep

    alert sheet stop "The Database Was Not Found" explain gDatabase

    exit to SuperCard

  end if

  put replace(hfsToPosix(gDatabase),space,"\ ") into gDatabase

  put false into theID

end SetDatabase

--2.) Searching the database based on variable fields with results put into a list:

function DBglobalSearch theString

  set the hilitedlines of cd fld 2 to empty

  global gDatabase, gSearchResults

  get merge("sqlite3 [[gDatabase]] 'SELECT RecordID,FirstName,LastName,Email FROM SuperCard WHERE LastName LIKE `%[[theString]]%` OR Email LIKE `%[[theString]]%` OR Company LIKE `%[[theString]]%` OR RegistrationNumbers LIKE `%[[theString]]%` ORDER by LastName COLLATE NOCASE ASC;'")

  get shell(it)

  put it into gSearchResults

  set the itemdel to "|"

  repeat with L=1 to the number of lines of it

    delete item 1 of line L of it

  end repeat

  get replace(it,numtochar(124),space)

  put unescapeQuotes(it) into cd fld 2

end DBglobalSearch

--3.) Loading a record from search results:

on getRecord tLineNumber

  global gDatabase, gSearchResults,theID

  set the itemdel to "|"

  get item 1 of line tLineNumber of gSearchResults

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

  get shell(it)

  put unescapeQuotes(it) into it

  put item 1 of it into theID

  put item 2 of it into cd fld "DateCreated"

  put item 3 of it into cd fld "DateModified"

  put item 4 of it into cd fld "Email"

  put item 5 of it into cd fld "Sal"

  put item 6 of it into cd fld "FirstName"

  put item 7 of it into cd fld "LastName"

  put item 8 of it into cd fld "Title"

  put item 9 of it into cd fld "Company"

  put item 10 of it into cd fld "Address1"

  put item 11 of it into cd fld "Address2"

  put item 12 of it into cd fld "City"

  put item 13 of it into cd fld "State"

  put item 14 of it into cd fld "Zip"

  put item 15 of it into cd fld "Country"

  put item 16 of it into cd fld "RegistrationNumbers"

  put item 17 of it into cd fld "CurrentVersion"

  put item 18 of it into cd fld "Use"

  put item 19 of it into cd fld "Heard"

  put item 20 of it into cd fld "Notes"

end getRecord

--4.) Saving changes to a record:

on SaveRecord

  select empty

  global gDatabase, theID

  PUT cd fld "DateCreated" INTO tDateCreated

  PUT today() INTO tDateModified

  PUT cd fld "Email" INTO tEmail

  PUT escapeQuotes(cd fld "Sal") INTO tSal

  PUT escapeQuotes(cd fld "FirstName") INTO tFirstName

  PUT escapeQuotes(cd fld "LastName") INTO tLastName

  PUT escapeQuotes(cd fld "Title") INTO tTitle

  PUT escapeQuotes(cd fld "Company") INTO tCompany

  PUT escapeQuotes(cd fld "Address1") INTO tAddress1

  PUT escapeQuotes(cd fld "Address2") INTO tAddress2

  PUT escapeQuotes(cd fld "City") INTO tCity

  PUT escapeQuotes(cd fld "State") INTO tState

  PUT escapeQuotes(cd fld "Zip") INTO tZip

  PUT escapeQuotes(cd fld "Country") INTO tCountry

  PUT cd fld "RegistrationNumbers" INTO tRegistrationNumbers

  PUT cd fld "CurrentVersion" INTO tCurrentVersion

  PUT cd fld "Use" INTO tUse

  PUT cd fld "Heard" INTO tHeard

  PUT escapeQuotes(cd fld "Notes") INTO tNotes

  if theID is false then

    -- set up to save new record

    PUT today() INTO tDateCreated

    put the seconds into theID

    get merge("sqlite3 [[gDatabase]] 'INSERT INTO SuperCard VALUES (`[[theID]]`,`[[tDateCreated]]`,`[[tDateModified]]`,`[[tEmail]]`,`[[tSal]]`,`[[tFirstName]]`,`[[tLastName]]`,"&¬

"`[[tTitle]]`,`[[tCompany]]`,`[[tAddress1]]`,`[[tAddress2]]`,`[[tCity]]`,`[[tState]]`,`[[tZip]]`,`[[tCountry]]`,`[[tRegistrationNumbers]]`,`[[tCurrentVersion]]`,`[[tUse]]`,`[[tHeard]]`,`[[tNotes]]`);'")

    get shell(it)

  else

    -- append existing record

    get merge("sqlite3 [[gDatabase]] 'UPDATE SuperCard SET DateModified = `[[tDateModified]]`, Email = `[[tEmail]]`, Sal = `[[tSal]]`, FirstName = `[[tFirstName]]`, LastName = `[[tLastName]]`,"&¬

"Title = `[[tTitle]]`, Company = `[[tCompany]]`, Address1 = `[[tAddress1]]`, Address2 = `[[tAddress2]]`, City = `[[tCity]]`, State = `[[tState]]`, Zip = `[[tZip]]`,"&¬

"Country = `[[tCountry]]`, RegistrationNumbers = `[[tRegistrationNumbers]]`, CurrentVersion = `[[tCurrentVersion]]`, Use = `[[tUse]]`, Heard = `[[tHeard]]`, Notes = `[[tNotes]]` WHERE RecordID = `[[theID]]`;'")

    get shell(it)

  end if

  set the modified of this wd to false

end SaveRecord

Other functions such as creating new or deleting existing records are much simpler.

It is a bit verbose (and I am sure Mark could streamline it a fair bit, which would most likely make it less readable to me), but once you familiarize yourself with SQLite it is pretty simple.

[Ramanoir]

Thanks Scott, 

That's a good start! I'll dig into that!

André