Save edited list back to the DB - SQLite

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Save edited list back to the DB - SQLite

Post by quailcreek » Sun Jun 14, 2015 1:08 am

I wanted to get some of the opinions of the SQLite experts on the forum. I have a native iOS input control (call it inputA) which is initially populated on an openCard script from the same table in the script below. The table only has one column and is only used to store the list of items which is then used in other places in the app. So, what I want to accomplish with the script below, is allow the user to edit the lines of control inputA and save the list back to the DB. They could add lines, remove lines or edit what's there. What I have works but it just seems like it could be more concise.

Code: Select all

on addEditElec
   local tCurrentProps, nDBNames, tDBNames, tStuff
   
   ## Gather the list from the table
   put "Select equipName FROM equipment" into tSQLStatement
   put revDataFromQuery(tab,cr, the uDatabaseID of this stack ,tSQLStatement) into tDBNames
   put the num of lines of tDBNames into nDBNames
   
   ## Gather the list from the input control
   put mobileControlGet("AddElec","text") into tListPropNames
   filter tListPropNames without empty
   sort tListPropNames
   put the num of lines of tListPropNames into nListPropNames
   
   if nListPropNames > nDBNames then
      -- add some rows to the column
      put (nListPropNames - nDBNames) into addCount
      put "NewCell" into tStuff
      repeat with x= 1 to addCount
         put "INSERT into equipment(equipName) VALUES ('"&tStuff&"')" into  tSQLStatement
         revExecuteSQL  the uDatabaseID of this stack ,tSQLStatement
      end repeat
      
   else -- nCurrentProps < nDBNames
      -- delete some rows from the column
      put (nDBNames - nListPropNames) into delCount -- the number of rows to delete
      delete line 1 to nListPropNames of nDBNames -- the PropIDs to delete
      
      repeat with x= 1 to delCount
         put line x of nDBNames into tDelete
         put "DELETE FROM equipment WHERE equipID ='"&tDelete&"' " into tSQLStatement
         revExecuteSQL  the uDatabaseID of this stack ,tSQLStatement
      end repeat
   end if
   
   repeat with x= 1 to nListPropNames
      put line x of tListPropNames onto pPropName
      put "UPDATE equipment SET equipName = '"&pPropName&"'  WHERE equipID = '"& x &"'  " into tSQLStatement2
      revExecuteSQL  the uDatabaseID of this stack ,tSQLStatement2
   end repeat
   
   if the result is "1" then
      mobileControlSet "AddElec","text" , tListPropNames
      answer information "List updated"
   else
      answer error "There was an error updating the list"
   end if
end addEditElec
Tom
MacBook Pro OS Mojave 10.14

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Save edited list back to the DB - SQLite

Post by Mikey » Sun Jun 14, 2015 3:39 pm

Was it Knuth that said that you should never optimize early? I wouldn't implement anything this way, but I'm not you, and this is your baby, so run with it. If you're saying "I hate this, and I want you to write it for me", then

1) Write a wrapper for your database routines. Both of them can be in the same wrapper, called "query", for example. Query would have a global/script local/stack property/whatever that holds your dbid, and deals with the errors, and returns results from SELECT statements. By the way, the tab and cr can be omitted (just use blank commas) in your revDataFromQuery
1a) I also have a wrapper for putting quotes around arguments
2) I would get rid of inserting the garbage rows. In fact, all of this effort seems to be unnecessary. You only have one column in this table, and the data from the field is going to become the data in the table, so I would just
DELETE FROM equipment
and then insert the rows of the field into the table.
3) If this is for ios then I would spend the money and buy tmc2 from Scott Rossi, because it'll make your life easier and make it easier to make your app look better.

so if I was writing this, it would be

Code: Select all

on AMDElec
   put "DELETE FROM equipment" into tSQL
   get query(tSQL)

   put mobileControlGet("AddElec","text") into theLines
   repeat for each line theLine in theLines
      if theLine is empty then next repeat
      put "INSERT INTO equipment VALUES"&&quoteIt(theline) into tSQL
      if query(tSQL) is not empty then exit AMDElec
   end repeat #for each line theLine in theLines
end AMDElec
The code for query and quoteIt is left as an exercise for the reader.

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Save edited list back to the DB - SQLite

Post by phaworth » Sun Jun 14, 2015 8:39 pm

I agree with Mikey on the strategy, assuming you need SQLite for this app. If there is only one table in the database and it only has one column, I don't think I would bother using SQLite at all, just use a plain text file. Less overhead and probably easier to code. But assuming there is a need for SQLite, here's how I would code it (untested and subject to typos).

Code: Select all

on AMDElec

   get executeSQL("BEGIN")
   if it is not an integer then
      -- error handling code
      exit AMDElec
   end if

   get executeSQL("DELETE FROM equipment")
   if it is not an integer then
      -- error handling code
      exit AMDElec
   end if

   put mobilecontrolGet("AddElec","text") into the lines
   repeat for each line theLine in theLines
      if theLine is empty then next repeat
      put "INSERT INTO equipment (equipname) VALUES(:1)" into tSQL
      if executeSQL(tSQL,theLine) is not an integer then
         get executeSQL("ROLLBACK")
         -- error handling here
         exit AMDElec
      end if
   end repeat
   get executeSQL("COMMIT")
 
exit AMDElec

function executeSQL psql,pparm

   if pparm is empty then
       revExecuteSQ the uDatabaseID of this stack, psql
   else 
      revExecuteSQ the uDatabaseID of this stack, psql,"pparm"
   end if

   return the result

end executeSQL
The BEGIN/ROLLBACK/COMMIT statements guarantee the integrity of your database should one of the statements fail and also increases performance.

I'm using the varslist parameter of revExecuteSQL instead of embedding the value in the INSERT statement. That way, no need to quote the value and also no need to check if the string contains any quotes which need to be escaped. Check the dictionary for more info.

I always include the column names to be inserted in an INSERT statement so I'm protected from whatever structural changes are made to the db.

HTH

Pete

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Save edited list back to the DB - SQLite

Post by Mikey » Sun Jun 14, 2015 9:54 pm

Pete is right, I would also parameter use the query, and I would also generally use a transaction.

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Save edited list back to the DB - SQLite

Post by quailcreek » Mon Jun 15, 2015 3:19 am

Thanks guys. Just to clarify. This particular table only has one column but the DB has 15 or so more and there are another 6 like this one with only one column. I was using a .txt file for each single column table but I thought because I eventually want to backup the DB to dropbox it would be better to have them in the DB.

Once I digest the information you've provided I'll let you know how it goes. Thanks a lot.
Tom
MacBook Pro OS Mojave 10.14

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Save edited list back to the DB - SQLite

Post by quailcreek » Mon Jun 15, 2015 10:48 pm

Pete, your code was like Marry Poppins... practically perfect in every way. I thought about deleting the contents of the column but I wasn't sure it was the proper way to handle this. Thanks for the confirmation and the lesson.
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Save edited list back to the DB - SQLite

Post by phaworth » Tue Jun 16, 2015 12:21 am

Wow, I'm keeping that quote for my scrapbook!
Pete

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Save edited list back to the DB - SQLite

Post by quailcreek » Sun Dec 06, 2015 12:52 am

Hi,
I have a follow-on question. I have several instances in my app where I do an INSERT to a table and a UPDATE to another table in the same piece of code. Should I have a BEGIN, ROLLBACK and COMMIT for each operation or just one BEGIN, a ROLLBACK for each operation and one COMMIT at the end?
Tom
MacBook Pro OS Mojave 10.14

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Save edited list back to the DB - SQLite

Post by phaworth » Sun Dec 06, 2015 1:55 am

Sequence should be:

BEGIN
INSERT
If error then ROLLBACK and exit
UPDATE
If error then ROLLBACK and exit
COMMIT

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Save edited list back to the DB - SQLite

Post by quailcreek » Sun Dec 06, 2015 2:13 am

Great, Pete. Thanks again.
Tom
MacBook Pro OS Mojave 10.14

Post Reply

Return to “Databases”