Building data base aps with Rev (Tuviah M Snyder)

Sat May 24 23:48:01 EDT 2003

Yo!  Let's keep this thread alive.  Really happy to see you have joined
the discussion Tuviah.

Swami explained to me how to find the undocumented API so I was able to
browser around in there and understand a bit more of what Jan was up to.
I'm really hoping he will make us some simple master-detail form as an
example but I realize it is a lot to ask.

Now, I wanted to post my progress so far.  My first posts about DB aps
were .. I couldn't even connect!  I have come much farther so those
of you trying to write data base apps, keep trying!  It does work.

At this point I have progressed through the help of Jan, Geoff and others
to actually having a working applicaton.  Ok, it's rather trivial but
it's useful for me.  I will post the code (below) in case anyone might
find examples helpful.  Naturally the server name, login, password etc
have been changed to protect the innocent.

First I would like to mention a conclusion so far - the query builder
is ok at the testing state to prove you are connecting to a data base
and getting back data, after that, you have to write handlers to make it
actually do useful work so why not add one or two more lines of code to
include the query?  In other words, it's an ok start but ends up being
too much trouble without more functionality.  This is not exactly a
complaint, more like, expressing hope for more features in the future,
a truly useful query builder.

I found my programs kept hanging up when the query builder was trying
to do something automatic, it was erratic with queries sometimes being
connected, sometimes not.  So I dropped it and then things started
working much better.

Here's my real wish for the future - an automatic screen builder.
Just give this thing a record type, and connection info, and it builds
a card with all the fields from the record ready for data entry, labels
matching the field names, and add/modify/inquire/delete buttons ready
to go.  This is very realistic, meaning, technically feasible, not some
wild impossible dream.  Furthermore if you could drag tabbed areas around
that could be associated with separate records for the master-detail
type of data input/maintenance.  Ok, so enough with the wish list.

Next before putting code, here's the background.  I have a data base with
two tables, audio and transcript.  The audio table has 3 fields, audio
date, audio url, and title.  These point to audio files stored on the
web server.  The transcript table has 3 fields, audio url, transcript url,
and title.  Ok, title is redundant, its not 3rd normal form.  But helped
when populating with data.  Purpose of this is to connect the two.
The audio files were posted continuously for years in separate 'dated'
directories and then later on they began to be transcribed, and posted
somewhere else.  For someone listening to an audio file if they wanted
the transcript, no way to find it.  Therefore the data base was built
with an html query form allowing the user to search for the transcripts
for the audio files.

However, there is no user interface to this postgres data base except
doing everything manually writing sql queries.  That makes maintenance
a chore!  Enter REV.

Ok, so now we have an application with three cards, one that lists
the two tables in list fields side by side for comparison, one
for maintenance of the audio table, and one for the transcript table.
Each with add, modify, delete buttons, and some other buttons to view
the associated web page, etc.

I think an application like this could have come 80% 'out of the box'
with a button push but hey, I am delighted it could be coded by hand
instead.  

Without further ado, the code.  (You will kindly forgive poor style
and allow for newbie contributions, I hope!)  However, you can flame
me for  no comments.  And also it appears my editor has wrapped some
long lines splitting them in two, so  you will have to join them back if
you want to actually try this.  Sorry!

Aloha,
Sadhu

......................................

global pConnectionID, tSQLQuery, tCursor, tColumnNames
global useIPNumber

---------------------------------------------------------
on openDataBase
   global pConnectionID, tSQLQuery, tCursor, tColumnNames, useIPNumber

   -- note, i took out this chunk of code that connected automatically
   -- upon launch because if the connection wasn't working you had
   -- no control. You sat there twidling thumbs.
   --
   --  --  answer "Stand by, I'm connecting to your data base ..."
   --
   --  put revOpenDatabase("Postgresql","yourservercom","yourdb","yourname","yourpasswd") into pConnectionID
   --  if (pConnectionID) is a number then
   --    put "Your  data base on yourserver opened successfully with connection ID " into tResult
   --    put pConnectionID after tResult
   --    answer tResult
   --    go to card "listCard"
   --  else
   --    answer "Gosh darn, what's up?  The data base connection failed."

   ask "Do you want to try to connect by IP number?" with "put your IP here"
   if (it is not empty) then
     put it into useIPNumber
     put revOpenDatabase("Postgresql","yourservercom","yourdb","yourname","yourpasswd") 
into pConnectionID
     if (pConnectionID) is a number then
       put "Your Cybertalks data base on Gurudeva opened successfully with 
connection ID " into tResult
       put pConnectionID after tResult
       answer tResult
       go to card "listCard"
     else
       answer "THAT DID NOT WORK, tough luck baby ..."
       exit to top
     end if
   else
     answer "Ok, will not attempt a connection"
   end if

   --  repeat for each item thisDB in revOpenDatabases()
   --    answer "Open Data Bases are" && thisDB
   --  end repeat

end openDataBase

---------------------------------------------------------

on closeDataBase
   global pConnectionID, tColumnNames

repeat for each item thisDB in revOpenDatabases()
     revCloseDatabase thisDB
   end repeat

   repeat for each item thisDB in revOpenDatabases()
     answer "Open Data Bases are" && thisDB
   end repeat
end closeDataBase

-------------------------------------------------------

on closeStack
   closeDataBAse
end closeStack

-------------------------------------------------------
on openStack
   openDataBase
end openStack

-------------------------------------------------------

on refreshScreen
   global tCursor, tColumnNames, pConnectionID
   put revDatabaseColumnNames(tCursor) into tColumnNames
   revMoveToFirstRecord pConnectionID

   repeat for each item tColumnName in tColumnNames
     if there is a field tColumnName then
       put revDatabaseColumnNamed(tCursor, tColumnName)into field tColumnName
     end if
   end repeat
end refreshScreen

---------------------------------------------------------

on transcriptClear
   repeat for each item this_item in "transcript_url,audio_url,title,show 
web page"
     put empty  into field this_item
   end repeat
end transcriptClear

-----------------------------------------------------------

on audioClear
   repeat for each item this_item in "audio_date,audio_url,title,show web page"
     put empty  into field this_item
   end repeat
end audioClear

-----------------------------------------------------------

on testURL tURLField
   global useIPNumber

   put "http://" into tURL
   put useIPNumber after tURL
   put "put your path name in here" after tURL
   put field tURLField after tURL
   set the htmlText of field "Show Web Page" \
      to URL tURL
end testURL

-----------------------------------------------------------

on preOpenStack
   repeat for each item thisDB in revOpenDatabases()
     revCloseDatabase thisDB
   end repeat
   repeat for each item thisDB in revOpenDatabases()
     answer "Open Data Bases are" && thisDB
   end repeat

end preOpenStack

---------------------------------------------------------

on audioDateSearch
   global pConnectionID

   if (field "audio_date" is not a date) then answer "Date must be valid"
   else
     put "select * from audio where audio_date = '$AUDIO_DATE'" into tSQLQuery
     replace "$AUDIO_DATE" with field "audio_date" in tSQLQuery
     put revQueryDatabase(pConnectionID, tSQLQuery) into tCursor
     if (char 1 to 8 of tCursor is "revdberr") then
       put cr & "Data base error - EEEK - call support" & cr after tCursor
       answer tCursor
       audioClear
     else
       put revNumberOfRecords(tCursor) into tRecordCount
       if (tRecordCount =0) then
         answer "Sorry, I could not find any records matching" & cr & cr & 
field "audio_date" & cr & cr
       else
         put revDatabaseColumnNames(tCursor) into tColumnNames
         revMoveToFirstRecord pConnectionID
         audioClear
         refreshScreen
         if (tRecordCount is a number and tRecordCount > 1) then
           repeat for tRecordCount - 1 times
             revMoveToNextRecord tCursor
             visual effect scroll left
             clone this card
             refreshScreen
           end repeat
         end if
       end if
     end if
   end if
end audioDateSearch

---------------------------------------------------------

on audioURLSearch recordType
   --  global pConnectionID
   --
   if (field "audio_url" is empty) then
     answer "Audio URL must be filled in"

   else

     if (recordType = "audio") then
       put "select * from audio where audio_url ~* '$AUDIO_URL'" into tSQLQuery
     else
       if (recordType = "transcript") then
         put "select * from transcript where audio_url = '$AUDIO_URL'" into 
tSQLQuery
       else
         answer "Unknown record type"
       end if // transcrip type
     end if //record types

     replace "$AUDIO_URL" with field "audio_url" in tSQLQuery

     put revQueryDatabase(pConnectionID, tSQLQuery) into tCursor

     if (char 1 to 8 of tCursor is "revdberr") then
       put cr & "Data base error - EEEK - call support" & cr after tCursor
       answer tCursor

       if (recordType = "audio") then
         audioClear
       else
         transcriptClear
       end if

     else  // no db error
       put revNumberOfRecords(tCursor) into tRecordCount
       if (tRecordCount =0) then
         answer "Sorry, I could not find any records matching" & cr & cr & 
field "audio_url" & cr & cr
       else  // column names
         put revDatabaseColumnNames(tCursor) into tColumnNames
         revMoveToFirstRecord pConnectionID

         if (recordType = "audio") then
           audioClear
         else
           transcriptClear
         end if

         refreshScreen

         if (tRecordCount is a number and tRecordCount > 1) then
           repeat for tRecordCount - 1 times
             revMoveToNextRecord tCursor
             visual effect scroll left
             clone this card
             refreshScreen
           end repeat
         end if

       end if //no db error

     end if // record count 0

   end if //audio url empy
end audioURLSearch

---------------------------------------------------------
on audioList

   go to card "listCard"
   put empty into field "audio"
   put "select * from audio order by audio_url" into tSQLQuery
   put revQueryDatabase(pConnectionID, tSQLQuery) into tCursor
   put revNumberOfRecords(tCursor)into tRecordCount
   if (tRecordCount is zero) then
     answer "Sorry, your data base is empty" & cr
   else
     revMoveToFirstRecord tCursor -- start at the beginning
     repeat for tRecordCount times
       put revDatabaseColumnNamed(tCursor, "audio_date") & tab after field 
"audio"
       put revDatabaseColumnNamed(tCursor, "audio_url")  & tab after field 
"audio"
       put revDatabaseColumnNamed(tCursor, "title")      &  cr after field 
"audio"
       revMoveToNextRecord tCursor
     end repeat
   end if
end audioList

---------------------------------------------------------

on transcriptList

   go to card "listCard"
   put empty into field "transcript"
   put "select * from transcript order by audio_url" into tSQLQuery
   put revQueryDatabase(pConnectionID, tSQLQuery) into tCursor
   put revNumberOfRecords(tCursor)into tRecordCount
   if (tRecordCount is zero) then
     answer "Sorry, your data base is empty" & cr
   else
     revMoveToFirstRecord tCursor -- start at the beginning
     repeat for tRecordCount times
       put revDatabaseColumnNamed(tCursor, "transcript_url") & tab after 
field "transcript"
       put revDatabaseColumnNamed(tCursor, "audio_url")  & tab after field 
"transcript"
       put revDatabaseColumnNamed(tCursor, "title")      &  cr after field 
"transcript"
       revMoveToNextRecord tCursor
     end repeat
   end if
end transcriptList

---------------------------------------------------------

on transcriptDelete
   global pConnectionID

   if (field "title" is empty or field "audio_url" is empty or field 
"transcript_url" is empty) then
     answer "All fields must be filled in to process a delete"
   else
     put "delete from transcript where transcript_url = '$TRANSCRIPT_URL' 
and title = '$TITLE' and audio_url = '$AUDIO_URL'" into tSQLQuery
     replace "$TRANSCRIPT_URL" with field "transcript_url" in tSQLQuery
     replace "$AUDIO_URL" with field "audio_url" in tSQLQuery
     replace "$TITLE" with field "title" in tSQLQuery
     revExecuteSQL pConnectionID, tSQLQuery
     put the result into deleteError

     if (deleteError is a number and deleteError >= 1) then
       answer "Your deletion was successful on" & cr & deleteError & field 
"transcript_url"
       transcriptClear
     else
       answer "Sorry, I could not delete that record" & cr & cr & field 
"transcript_url"
     end if
   end if
end transcriptDelete

---------------------------------------------------------

on audioDelete
   global pConnectionID

   if (field "title" is empty or field "audio_url" is empty or field 
"audio_date" is empty) then
     answer "All fields must be filled in to process a delete"
   else
     put "delete from audio where audio_date = '$AUDIO_DATE' and title = 
'$TITLE' and audio_url = '$AUDIO_URL'" into tSQLQuery
     replace "$AUDIO_DATE" with field "audio_date" in tSQLQuery
     replace "$AUDIO_URL" with field "audio_url" in tSQLQuery
     replace "$TITLE" with field "title" in tSQLQuery
     revExecuteSQL pConnectionID, tSQLQuery
     put the result into deleteError

     if (deleteError is a number and deleteError >= 1) then
       answer "Your deletion was successful on" & cr & deleteError & field 
"audio_date"
       audioClear
     else
       answer "Sorry, I could not delete that record" & cr & cr & field 
"audio_date"
     end if
   end if
end audioDelete

---------------------------------------------------------

on transcriptAdd
   global pConnectionID

   put "insert into transcript (transcript_url, audio_url, title) values 
('$TRANSCRIPT_URL', '$AUDIO_URL', '$TITLE');" into tSQLQuery
   replace "$TRANSCRIPT_URL" with field "transcript_url" in tSQLQuery
   replace "$AUDIO_URL" with field "audio_url" in tSQLQuery
   replace "$TITLE" with field "tITLE" in tSQLQuery
   revExecuteSQL pConnectionID, tSQLQuery
   put the result into addError

   if (addError is a number and addError >= 1) then
     answer "Your Addition was successful on" & cr & addError & field 
"transcript_url"
   else
     answer "Sorry, I could not add that record" & cr & addError & field 
"transcript_url"
   end if
end transcriptAdd

---------------------------------------------------------

on audioAdd
   global pConnectionID

   put "insert into audio (audio_date, audio_url, title) values 
('$AUDIO_DATE', '$AUDIO_URL', '$TITLE');" into tSQLQuery
   replace "$AUDIO_DATE" with field "audio_date" in tSQLQuery
   replace "$AUDIO_URL" with field "audio_url" in tSQLQuery
   replace "$TITLE" with field "tITLE" in tSQLQuery
   revExecuteSQL pConnectionID, tSQLQuery
   put the result into addError

   if (addError is a number and addError >= 1) then
     answer "Your Addition was successful on" & cr & addError & field 
"audio_date"
   else
     answer "Sorry, I could not add that record" & cr & addError & field 
"audio_date"
   end if
end audioAdd

---------------------------------------------------------

on transcriptChange
   global pConnectionID

   put "update transcript set audio_url = '$AUDIO_URL', transcript_url = 
'$TRANSCRIPT_URL', title = '$TITLE' where transcript_url = 
'$TRANSCRIPT_URL'" into tSQLQuery

   replace "$TRANSCRIPT_URL" with field "transcript_url" in tSQLQuery
   replace "$AUDIO_URL" with field "audio_url" in tSQLQuery
   replace "$TITLE" with field "title" in tSQLQuery
   revExecuteSQL pConnectionID, tSQLQuery
   put the result into changeError

   if (changeError is a number and changeError >= 1) then
     answer "Your Modification was successful on" & cr & changeError & " 
records"  & cr & field "transcript_url"
   else
     answer "Sorry, I could not modify that record" & cr & changeError & 
field "transcript_url"
   end if
end transcriptChange

---------------------------------------------------------

on audioChange
   global pConnectionID

   put "update audio set audio_url = '$AUDIO_URL', audio_date = 
'$AUDIO_DATE', title = '$TITLE' where audio_date = '$AUDIO_DATE'" into 
tSQLQuery

   replace "$AUDIO_DATE" with field "audio_date" in tSQLQuery
   replace "$AUDIO_URL" with field "audio_url" in tSQLQuery
   replace "$TITLE" with field "title" in tSQLQuery
   revExecuteSQL pConnectionID, tSQLQuery
   put the result into changeError

   if (changeError is a number and changeError >= 1) then
     answer "Your Modification was successful on" & cr & changeError & " 
records"  & cr & field "audio_date"
   else
     answer "Sorry, I could not modify that record" & cr & changeError & 
field "audio_date"
   end if
end audioChange

---------------------------------------------------------

on listSearch recordType

   if (recordType is "audio") then
     put the selectedText of field "audio" into searchPattern
     put getFieldOne(searchPattern) into audioDate
     retrieveAudioByDate audioDate
   else
     if (recordType is "transcript") then
       put the selectedText of field "transcript" into searchPattern
       put getFieldOne(searchPattern) into transcriptURL
       retrieveTranscriptByURL transcriptURL
     end if
   end if

end listSearch

------------------------------------------------------------
on retrieveTranscriptByURL transcriptURL
   global pConnectionID

   go to card "transcriptCard"
   put "select * from transcript where transcript_url = '$TRANSCRIPT_URL'" 
into tSQLQuery
   replace "$TRANSCRIPT_URL" with transcriptURL in tSQLQuery
   put revQueryDatabase(pConnectionID, tSQLQuery) into tCursor

   if (char 1 to 8 of tCursor is "revdberr") then
     put cr & cr & "Sorry, I could not find that entry." & cr & cr after 
tCursor
     answer tCursor
     transcriptClear
   else
     refreshScreen
   end if
end retrieveTranscriptByURL

-------------------------------------------------------------

on retrieveAudioByDate audioDate
   global pConnectionID, tCursor

   go to card "audioCard"
     put "select * from audio where audio_date = '$AUDIO_DATE'" into tSQLQuery
   replace "$AUDIO_DATE" with audioDate in tSQLQuery
   put revQueryDatabase(pConnectionID, tSQLQuery) into tCursor

   if (char 1 to 8 of tCursor is "revdberr") then
     put cr & cr & "Sorry, I could not find that entry." & cr & cr after 
tCursor
     answer tCursor
     audioClear
   else
     refreshScreen
   end if
end retrieveAudioByDate

-------------------------------------------------------------

function getFieldOne thisLine
   -- return the contents of the first cell of a line in a table
   if (thisLine contains tab) then //valid type of line
     put offset(tab, thisLine) into pipeOff
     put char 1 to (pipeOff  - 1) of thisLine into fieldOne
     return fieldOne
   else
     return "Error"
   end if
end getFieldOne

------------------------------------------------------------