Conceptual database questions...

Robert Brenstein rjb at robelko.com
Thu May 26 10:29:48 EDT 2005


>Hello everyone...
>
>I feel like I am just not getting some basic concepts in trying to use
>the Valentina kernal for RunRev. So, I have a few questions:
>
>1) Is a base object (table) basically a set of columns?

It is better to think of a baseObject (table) as a combination of 
columns and rows: columns are fields and rows are records. Of course, 
when you work with schema (database structure) records are ignored.

>2) A base object can contain zero records, yet you are still allowed to
>set the value of a field in a base object. Does setting the value of a
>field, without any selected record, basically have no effect?

A working (current) record is basically a temporary memory buffer. 
When a cursor has no records, you can still set values of fields and 
then use AddRecord to copy them into the database.

When your cursor has some records, the current record is loaded into 
that buffer. When you set the field values, overwriting old data, you 
can then use either UpdateRecord to modify that record in the 
database or AddRecord to save the modified record as a new record.

If a table has records and you want to add a new record, the easiest 
is to create a new empty cursor with SqlSelect using

Select * From billingdata Where recId=0

>3) Is the following the correct order of events for creating a record:
>	a) Create database or make sure it exists
>	b) Create table (base object) or make sure it exists
>	c) Create columns in table or make sure they already exist
>	d) add record, at which time that record becomes the selected
>record for that table
>	e) insert information into that record

Correct except that d) is creating a cursor. Actual adding is in e).

>
>4) Since a cursor is a selection of records, can you have a cursor if
>the table has zero records?

Yes, you can have a cursor when table has no or million records (cf 
above). Of course, for an empty table, you can have only empty cursor.

>
>5) When is it necessary, and when is it not necessary but probably
>better, to use a cursor rather than working directly with the base
>object?

To work with data, you must use cursors.

However, I think you really mean to ask about using the direct API 
versus SQL for working with data. Using SQL, you can do the same 
without explicitely creating cursors. You should not mix these, 
though, and use either direct API or SQL.

>6) Do records have specific IDs, such that I could call up record 57 of
>base object "billingdata"?

Yes, each record has a so called recId (record id) associated with 
it. It is essentially a physical number of the record in the table, 
so it is unique for each record and stays fixed for the lifetime of a 
given record.

I say lifetime, because when you delete records from table, their 
space is reused for new records, so a newer record gets recId of any 
of the free (deleted) records if there are any.

In order to have recId included in your cursor, you need to list it 
explicitely in SQLselect query, like

Select recId,* From billingdata
Select billRefNumber,billOwnerId,recId From billingdata

>Thanks, and my apologies if I am asking ignorant questions for which I
>should know better.
>
>Jonathan

Hope this will get you going :)

Robert Brenstein


More information about the use-livecode mailing list