Deleting Projects, People and To-Do Items

Deleting records from a database using SQL Yoga is really easy. Let's quickly go through the code that deletes a project, person or to-do item.

Update the uiDeletePerson Command

Replace the empty uiDeletePerson command in the card script with the following RevTalk code.

----------

Copy & Paste The Following Code

----------

command uiDeletePerson

## Get id of person selected in left column

put the uSelectedPersonID of group "ProjectsPeople" into thePersonID

## Create a SQL Record object for 'people'

put sqlrecord_createObject("people") into theRecordA

## Fill in primary key field for 'people'

sqlrecord_set theRecordA, "id", thePersonID

## Delete record

sqlrecord_delete theRecordA

put the result into theError

if theError is empty then

## Refresh list

RefreshProjectsPeopleList

end if

if theError is not empty then

answer "Error deleting person:" && theError & "."

end if

end uiDeletePerson

Deleting a Person

To delete a person from the database you can use a SQL Record object (1). You just need to fill in the primary key field for the table which is 'id' (2).

sqlrecord_delete will then generate the SQL to delete the record from the database (3).

Test

Select New Person in the UI and press the delete key or backspace key.

New Person will be removed.

Deleting Projects

Replace the empty uiDeleteProject command in the card script with the following RevTalk code. You can then use the delete or backspace key to delete a project.

----------

Copy & Paste The Following Code

----------

command uiDeleteProject

## Get id of project selected in left column

put the uSelectedProjectID of group "ProjectsPeople" into theProjectID

## Create a SQL Record object for 'projects'

put sqlrecord_createObject("projects") into theRecordA

## Fill in primary key field for 'projects'

sqlrecord_set theRecordA, "id", theProjectID

## Delete record

sqlrecord_delete theRecordA

put the result into theError

if theError is empty then

## Refresh list

RefreshProjectsPeopleList

end if

if theError is not empty then

answer "Error deleting project:" && theError & "."

end if

end uiDeleteProject

Deleting To-Do Items

Replace the empty uiDeleteToDo command in the card script with the following RevTalk code. You can then use the delete or backspace key to delete a to-do item.

----------

Copy & Paste The Following Code

----------

command uiDeleteToDo

## Get id of project selected in left column

put the uSelectedID of group "ToDo" into theToDoID

## Create a SQL Record object for 'projects'

put sqlrecord_createObject("todo_items") into theRecordA

## Fill in primary key field for 'projects'

sqlrecord_set theRecordA, "id", theToDoID

## Delete record

sqlrecord_delete theRecordA

put the result into theError

if theError is empty then

## Refresh list

RefreshToDoList

end if

if theError is not empty then

answer "Error deleting to-do item:" && theError & "."

end if

end uiDeleteToDo

A Short Note on Triggers and SQLite

When relationships exist between two tables in a database you sometimes need to delete records in other tables when deleting a record from a related table. For example, if you delete a person from the database the link between that person and any to-do items needs to be removed and vice versa. If you delete a project from the database you need to delete any related to-do items.

While some databases will take care of deleting the records for you, SQLite will not. This is where Triggers come into play. A Trigger is a way of performing operations in a database when certain events occur. If you look at the Triggers for the to-do application database you will see that there are three of them. This triggers delete to-dos when a project is deleted and remove the link between people/to-dos when either one is deleted.

You could mimic this logic in the application code but by incorporating the logic into the database you can edit records in a database manager and know that your data does not become corrupted (i.e. to-dos linked to projects that don't exist).

0 Comments

Add your comment

E-Mail me when someone replies to this comment