Adding fields to a database

I'm a newcomer to programming Miva databases. All my previous
experience was on flat files.

So my first effort to create, add, and delete records has been
successful, but now I see that having some sort of serial number on
each record might make it easier to do editing and/or deleting of a
specific record. Is it possible to add a new field, or do I have to
go through the creation process all over again? If so, what happens
to the data I've already collected?

Are there approved ways of doing this sort of thing?

Sandy

Adding fields to a database



This is an easy process.

Do something like this:

1. Create a new database that has all of your old fields plus the new
ones you want.
2. Open the old database
3. Roll through the old database and copy all the old records to the new
database until you reach d.eof
4. Close old and new databases
5. Delete old database files, rename new database files, and you'll be
set to go.

I did it this morning on one- below I've copied some condensed code so
you can see what I"m talking about. New fields would be "hits" and "visible"

There are some helpful tutorial style scripts for dealing with databases
using miva script if you want to check 'em out.

Scot
<A HREF ="http://www.scotsscripts.com">http://www.scotsscripts.com</A>

Example code for adding fields:

<mvcomment>Assign a data path</mvcomment>

<MvASSIGN NAME = "g.data" VALUE = "{ 'data_path' }">

<mvcomment>create new db using same alias as original one for index
purposes</mvcomment>

<MvCREATE NAME = "db"
DATABASE = "{ g.data $ '/db2.dbf' }"
FIELDS ="
id char(10),
name char(128),
visible bool(),
hits number()
">

<MvMAKEINDEX NAME = "db" INDEXFILE = "{ g.data $ '/db_id.mvx'}"
EXPRESSION = "{ db.d.id }" FLAGS = "string">

<MvMAKEINDEX NAME = "db" INDEXFILE = "{ g.data $ '/db_name.mvx'}"
EXPRESSION = "{ tolower(db.d.name)}" FLAGS = "">

<mvcomment>open old database</mvcomment>
<MvOPEN NAME = "old" DATABASE = "{ g.data $ '/db.dbf' }" INDEXES = ""
TYPE="xbase3">

<mvcomment>roll through old database and copy old values (id and name)
into new database record and set new variables (visible and hits) to
default values, then add record</mvcomment>

<MvWHILE EXPR = "{NOT old.d.eof}">
<MvASSIGN NAME="db.d.id" VALUE="{ old.d.id}">
<MvASSIGN NAME="db.d.name" VALUE="{ old.d.name}">

<MvASSIGN NAME="db.d.visible" VALUE="{ 1 }">
<MvASSIGN NAME="db.d.hits" VALUE="{ 0 }">

<MvADD name="db">
<MvSKIP name="old">
</MvWHILE>

<mvclose name="old">
<MvCLOSE name="db">

<mvcomment>rename new database to original name- index files, memo
fields, etc, will be good to go if you do it this way</mvcomment>

<MvASSIGN NAME = "l.ok" VALUE = "{ frename(g.data $ '/db2.dbf',g.data $
'/db.dbf') }">



Sandy Rozhon wrote:
> I'm a newcomer to programming Miva databases. All my previous
> experience was on flat files.
>
> So my first effort to create, add, and delete records has been
> successful, but now I see that having some sort of serial number on
> each record might make it easier to do editing and/or deleting of a
> specific record. Is it possible to add a new field, or do I have to
> go through the creation process all over again? If so, what happens
> to the data I've already collected?
>
> Are there approved ways of doing this sort of thing?
>
> Sandy
>

Adding fields to a database



Only in the most simple of database situations should alias.d.recno be
used because unlike using an index based on an id field (alias.d.id or
something), the alias.d.recno number can change, especially after MvPACK
is used.

For example, if you have a simple contacts database but in the future
decide to add a personal information/blog/etc database and don't want to
clutter up the main contact info database, the only way you're going to
be able to link them together is by a common ID number that never
changes (each db would have alias.d.id in it and the id value would be
the same).

Anyway, my view is to keep databases and scripts flexible and open ended
so that future updates are easy instead of otherwise.

Scot

Patrick Locke wrote:
> I am not familiar with the database you are building but if it is not
> updated by many people you can use alias.d.recno for this. I have some dbs
> that I am the only one that updates the information and i use this method
> all the time to update information. But with multiple people being able to
> update, it sometimes can cause problems.
>
> Patrick
> ----- Original Message -----
> From: "Sandy Rozhon" <[email protected]>
> To: "Miva Userlist" <[email protected]>
> Sent: Saturday, February 12, 2005 1:58 PM
> Subject: [meu] Adding fields to a database
>
>
>
>>I'm a newcomer to programming Miva databases. All my previous
>>experience was on flat files.
>>
>>So my first effort to create, add, and delete records has been
>>successful, but now I see that having some sort of serial number on
>>each record might make it easier to do editing and/or deleting of a
>>specific record. Is it possible to add a new field, or do I have to
>>go through the creation process all over again? If so, what happens
>>to the data I've already collected?
>>
>>Are there approved ways of doing this sort of thing?
>>
>>Sandy
>>

Adding fields to a database



Merchant tables use an ID field that is incremented with each new record. It
uses a separate keys table for keeping track of the "last used" ID in each
table. Instead, I use record number 1 (sticking the lastID number into an
existing field) for tracking the last ID added to the table. All access to
the database then ignore the first record except when adding a new record to
the table. I read the first record, add 1 to the lastID, write record 1,
then Add the new record using the new lastID. This gives me a continuously
incrementing RecordID that never gets re-used an never changes for each
record.

-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf
Of Scot Ranney
Sent: Monday, February 14, 2005 12:52 PM
To: Miva Userlist
Subject: Re: [meu] Adding fields to a database

Only in the most simple of database situations should alias.d.recno be used
because unlike using an index based on an id field (alias.d.id or
something), the alias.d.recno number can change, especially after MvPACK is
used.

For example, if you have a simple contacts database but in the future decide
to add a personal information/blog/etc database and don't want to clutter up
the main contact info database, the only way you're going to be able to link
them together is by a common ID number that never changes (each db would
have alias.d.id in it and the id value would be the same).

Anyway, my view is to keep databases and scripts flexible and open ended so
that future updates are easy instead of otherwise.

Scot

Patrick Locke wrote:
> I am not familiar with the database you are building but if it is not
> updated by many people you can use alias.d.recno for this. I have
> some dbs that I am the only one that updates the information and i use
> this method all the time to update information. But with multiple
> people being able to update, it sometimes can cause problems.
>
> Patrick
> ----- Original Message -----
> From: "Sandy Rozhon" <[email protected]>
> To: "Miva Userlist" <[email protected]>
> Sent: Saturday, February 12, 2005 1:58 PM
> Subject: [meu] Adding fields to a database
>
>
>
>>I'm a newcomer to programming Miva databases. All my previous
>>experience was on flat files.
>>
>>So my first effort to create, add, and delete records has been
>>successful, but now I see that having some sort of serial number on
>>each record might make it easier to do editing and/or deleting of a
>>specific record. Is it possible to add a new field, or do I have to
>>go through the creation process all over again? If so, what happens
>>to the data I've already collected?
>>
>>Are there approved ways of doing this sort of thing?
>>
>>Sandy
>>

Adding fields to a database



I am not familiar with the database you are building but if it is not
updated by many people you can use alias.d.recno for this. I have some dbs
that I am the only one that updates the information and i use this method
all the time to update information. But with multiple people being able to
update, it sometimes can cause problems.

Patrick
----- Original Message -----
From: "Sandy Rozhon" <[email protected]>
To: "Miva Userlist" <[email protected]>
Sent: Saturday, February 12, 2005 1:58 PM
Subject: [meu] Adding fields to a database


> I'm a newcomer to programming Miva databases. All my previous
> experience was on flat files.
>
> So my first effort to create, add, and delete records has been
> successful, but now I see that having some sort of serial number on
> each record might make it easier to do editing and/or deleting of a
> specific record. Is it possible to add a new field, or do I have to
> go through the creation process all over again? If so, what happens
> to the data I've already collected?
>
> Are there approved ways of doing this sort of thing?
>
> Sandy
>

Adding fields to a database



Unless you have an index on the serial column, a relatively secure and easy
way to increment a record is to rely on the physical order of the records in
the db. For this, you don't use an index.

MvOpen DB -> without <- any indices
MvLockfile
MvGO db row=bottom
MvASSIGN =l.lastID VALUE=d.serial_column
MvSetIndex all indices
MvASSIGN =d.serial_column Value=l.lastID+1
MvASSIGN = all other columns....
MvADD
/MvLockfile

One should not rely on the built-in locking mechanism of MvADD/MvUPDATE and
instead lock the table manually with MvLOCKFILE, because the function
(especially opening the indices) might be quite I/O expensive (slow).

Obviously, this technique only works as long as the table is not physically
reorganized. In that case, it's better to use an index on d.serial_column
and/or do a lookup to check if the value already exists before adding a new
one.

Markus








-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf
Of Patrick Locke
Sent: Montag, 14. Februar 2005 20:31
To: Sandy Rozhon; Miva Userlist
Subject: Re: [meu] Adding fields to a database

I am not familiar with the database you are building but if it is not
updated by many people you can use alias.d.recno for this. I have some dbs
that I am the only one that updates the information and i use this method
all the time to update information. But with multiple people being able to
update, it sometimes can cause problems.

Patrick
----- Original Message -----
From: "Sandy Rozhon" <[email protected]>
To: "Miva Userlist" <[email protected]>
Sent: Saturday, February 12, 2005 1:58 PM
Subject: [meu] Adding fields to a database


> I'm a newcomer to programming Miva databases. All my previous
> experience was on flat files.
>
> So my first effort to create, add, and delete records has been
> successful, but now I see that having some sort of serial number on
> each record might make it easier to do editing and/or deleting of a
> specific record. Is it possible to add a new field, or do I have to
> go through the creation process all over again? If so, what happens
> to the data I've already collected?
>
> Are there approved ways of doing this sort of thing?
>
> Sandy
>