sqlYoga: sqlite primary key and a few detailed questions

[Trevor DeVore]

On Apr 6, 2010, at 1:44 PM, David Bovill wrote:

> I've had some time to start implementing the local handler database in
> sqlYoga: and have noticed a couple of things:
>
>   1. An sqlite table "handler" db with a field "id" set as a primary  
> key
>   does not show in the db schema of sqlYoga. Do I have to script  
> this using
>   something ike "sqlrecord_set theRecordA, "id", theProjectID" - and  
> if so
>   using a model behavior called when the table is created?

If you have already called dbobject_reloadSchema and the primary key  
field is not showing up under schema > tables > handler > primary key  
(use the plugin to check) then email your database to support at bluemangolearning.com 
  so I can take a look.

>   2. How do I delete multiple records - can;t find an example to  
> learn from
>   - I'm trying something along the lines of:
>
>      -- put "id is in :1" into theParamsA ["conditions"]
>      -- put "1,2" into theParamsA ["condition bindings"]["1"]
>
>      put "handler.object is ':1'" into theParamsA ["conditions"]
>      put someObject into theParamsA ["condition bindings"]["1"]
>      sqlrecord_find "handler", theParamsA, theRecordsA
>
>      sqlrecord_delete theRecordA
>
> But I'm only getting one record returned in theRecordsA. Does anyone  
> have a
> code snippet for deleting multiple records?

If you copied and pasted this code directly then you have a typo. You  
are using theRecordsA with sqlrecord_find and you are passing  
theRecordA to sqlrecord_delete.

> I'd also love to know a little about how to pass the arrays around  
> between
> "objects" - I'm digging into the innards of the arrays now, but it  
> would be
> great to have some more examples? Like how do you take an array from
> sqlquery_X and use it with sqlrecord_

You don't :-)

sqlquery_ calls directly convert results from a query to an array in  
Rev. All columns returned are stored in the second dimensions of the  
array.

sqlrecord_ calls store meta information about the table the data comes  
from, etc. Query results will also be converted to nested arrays  
depending on the relationships of the tables in the query. You can  
also use callbacks and custom table properties with SQL Records. SQL  
Record objects carry more overhead.

If you ever want to look inside the arrays just use printKeys(), a  
helper function included with SQL Yoga.

put printkeys(theRecordA)

> Finally in the example below:
>
> 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
>>
>
> does - "sqlrecord_set theRecordA, "id", theProjectID" really set the
> primary key - it is not in the API documentation, or is it something
> specific to the record object?

sqlrecord_set is in the API docs:

http://www.bluemangolearning.com/revolution/docs/sql_yoga/api_docs/Documents/stack_libSQLYoga_command_sqlrecord_set.htm

It just fills in the value for the "id" property in the SQL Record  
object (theRecordA). It is similar to a line of revTalk that sets the  
property of a button object (just not as pretty):

set the hilite of button "MyButton" to true

Although theRecordA is just an array in Rev, you use sqlrecord_get/set  
because meta information is stored in the array as well. That meta  
information is updated whenever you set a property (like 'id').

-- 
Trevor DeVore
Blue Mango Learning Systems
ScreenSteps: http://www.screensteps.com
Releasable Revolution Resources for Developers: http://revolution.bluemangolearning.com