Miva Script 101 /
Simple Database
Simple Database
This tutorial was last updated in 2005 and takes the form of commented source code shown below. Download the source code.
<MvCOMMENT>***********************************************************************
This simple_db.mv script has been heavily updated on Feb 25, 2003 to take advantage
of some of the new ways of passing data available in the Miva Scripting language.
There are NO guarantees. If you lose your business because you used this as a template for
your databases, tough luck. This is simply one method of manipulating databases. There
are many others and they should all be explored if you are working on an important project.
Feel free to contact me at http://www.scotsscripts.com if you have any questions at all.
The only configuration variable is the data directory below. It is set default to: simple_db
********************************************************************************</MvCOMMENT>
<MvASSIGN NAME = "g.data" VALUE = "{ 'simple_db' }">
<MvCOMMENT>***********************************************************************
Begin main stuff here
********************************************************************************</MvCOMMENT>
<html>
<head>
<title>Simple Miva Database</title>
<style>
<!--
a:link {
font-family: Arial, Helvetica, sans-serif;
color: #002AA2;
text-decoration: none;
font-size : small;
}
a:active {
font-family: Arial, Helvetica, sans-serif;
color: #002AA2;
text-decoration: none;
font-size : small;
}
a:visited {
font-family: Arial, Helvetica, sans-serif;
color: #002AA2;
text-decoration: none;
font-size : small;
}
a:hover {
font-family: Arial, Helvetica, sans-serif;
color: #882727;
text-decoration: none;
font-size : small;
}
-->
</style>
</head>
<body>
<MvIF EXPRESSION = "{NOT g.action}">
<MvASSIGN NAME = "g.action" VALUE = "{ 'main' }">
</MvIF>
<MvIF EXPRESSION = "{g.action}">
<MvASSIGN NAME = "l.ok" VALUE = "{ do_actions(g.action) }">
</MvIF>
</body>
</html>
<MvEXIT>
<MvCOMMENT>
###############################################################################
do_actions(action)
The actions function is a way to test for what function to run. Sometimes script
can contain thousands of functions and different flows. Using various actions
menus is a great way to keeping your source code readable.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "do_actions" PARAMETERS = "action">
<MvIF EXPRESSION = "{l.action EQ 'main'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ main() }">
</MvIF>
<MvIF EXPRESSION = "{l.action EQ 'record_add'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ record_add() }">
</MvIF>
<MvIF EXPRESSION = "{l.action EQ 'record_add2'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ record_add2() }">
</MvIF>
<MvIF EXPRESSION = "{l.action EQ 'modify_record'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ modify_record() }">
</MvIF>
<MvIF EXPRESSION = "{l.action EQ 'modify_record2'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ modify_record2() }">
</MvIF>
<MvIF EXPRESSION = "{l.action EQ 'delete_record'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ delete_record() }">
</MvIF>
<MvIF EXPRESSION = "{l.action EQ 'record_details'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ record_details() }">
</MvIF>
<MvIF EXPRESSION = "{l.action EQ 'search'}">
<MvASSIGN NAME = "l.ok" VALUE = "{ search() }">
</MvIF>
<MvASSIGN NAME = "l.ok" VALUE = "{ copyright() }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
Main Screen
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "main" PARAMETERS = "">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_table() }">
<TR bgcolor="lightyellow">
<TD colspan="3">
<big>Simple Database using Miva version 3.9x and above </big>
<div align="right">
<a href="{ s.documenturl $ 'action=record_add' }">Add a New Record</a>
</div>
</td>
</tr>
<MvASSIGN NAME = "l.ok" VALUE = "{ close_table() }">
<MvASSIGN NAME = "l.ok" VALUE = "{ list_records() }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
ADD and SAVE records
record_add: brings up a simple form to get information needed for new record
record_add2:
verifies information and then uses insert_simple_db(...) to add a new record in the
database
when adding a new record, all indexes must be opened so they are loaded properly
use ltrim and rtrim to remove beginning and trailing spaces. Beginning spaces will
mess up the index for proper alphabetical sorting
s.dyn_time_t makes a great ID value. The number changes every second so you are not
likely to get double IDs. If you are worried about unique id numbers, you can either
put a random number at the end of the id like this:
<MvASSIGN NAME = "db.d.id" VALUE = "{ dyn_time_t $ random(1000) }">
or do a quick MvFIND for the id you made to make sure it's not in the database (if it is,
then simply go back and create a new dyn_time_t and repeat the MvFIND process)
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "record_add" PARAMETERS = "">
<FORM ACTION="{ s.documenturl $ 'action=record_add2' }" METHOD="POST" ENCTYPE="application/x-www-form-urlencoded">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_table() }">
<tr bgcolor="lightyellow"><TD colspan="2"><big>Add Record</big></td></tr>
<TR bgcolor="white">
<TD align="right"><b>First Name:</b></td>
<TD bgcolor="#DFDFDF"><input type="text" name="fname" size="32"></td>
</tR>
<TR bgcolor="white">
<TD align="right"><b>Last Name:</b></td>
<TD bgcolor="#DFDFDF"><input type="text" name="lname" size="32"></td>
</tR>
<TR bgcolor="white">
<TD align="right"><b>Age:</b></td>
<TD bgcolor="#DFDFDF"><input type="text" name="age" size="4"></td>
</tr>
<TR bgcolor="white">
<TD valign="top" align="right"><b>Description:</b></td>
<TD bgcolor="#DFDFDF"><textarea name="desc" wrap="virtual" cols="50" rows="5"></textarea></td>
</tr>
<TR bgcolor="silver">
<TD colspan="2" align="right"><input type="submit" name="submit" value="Save Record"></td>
</tr>
<MvASSIGN NAME = "l.ok" VALUE = "{ close_table() }">
</FORM>
</MvFUNCTION>
<MvFUNCTION NAME = "record_add2" PARAMETERS = "">
<MvIF EXPRESSION = "{NOT g.fname OR NOT g.lname OR NOT g.age OR NOT g.desc}">
<B>Error: All fields must be filled in. Please go back and check your entries.</b>
<MvFUNCRETURN VALUE = "{ 0 }">
</MvIF>
<MvASSIGN NAME = "l.ok" VALUE = "{ insert_simple_db(s.dyn_time_t,g.fname,g.lname,g.age,g.desc) }">
<b>Record Saved</b>
<P>
<MvASSIGN NAME = "l.ok" VALUE = "{ main() }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
DELETE A RECORD
delete_record()
Very simple- find the record, verify it's the right record, and then set all the values to null.
The reason for setting values to null instead of using MvDELETE is that when you get
huge databases with hundreds of thousands of records, you can not MvPACK or MvREINDEX them.
The application times out and you forever have a messy database.
Using this method of setting fields to null, you recycle records everytime you add a new one.
Study insert_simple_db(...) and update_simple_db(...) to make this more clear.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "delete_record" PARAMETERS = "">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_simple_db('all') }">
<MvFIND NAME = "simple_db" VALUE = "{g.search_id}" EXACT="EXACT">
<MvIF EXPRESSION = "{simple_db.d.eof}">
<B>Error:</b> Record not found for deleting. Exiting.
<MvELSE>
<MvASSIGN NAME = "simple_db.d.id" VALUE = "{ '' }">
<MvASSIGN NAME = "simple_db.d.lname" VALUE = "{ '' }">
<MvASSIGN NAME = "simple_db.d.fname" VALUE = "{ '' }">
<MvASSIGN NAME = "simple_db.d.age" VALUE = "{ '' }">
<MvASSIGN NAME = "simple_db.d.date" VALUE = "{ '' }">
<MvUPDATE name="simple_db">
</MvIF>
<MvCLOSE name="simple_db">
<MvASSIGN NAME = "l.ok" VALUE = "{ main() }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
DISPLAY RECORD DETAILS
record_details():
This function displays the record. It uses the get_simple_db_info(..) function
to acquire a structure of all the information in the record designated by the
g.search_id variable.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "record_details" PARAMETERS = "">
<MvASSIGN NAME = "l.info" VALUE = "{ get_simple_db_info(g.search_id) }">
<MvIF EXPR = "{NOT l.info}">
<B>Error:</b> Record not found.
<MvFUNCRETURN VALUE = "{ 0 }">
</MvIF>
<MvASSIGN NAME = "l.ok" VALUE = "{ open_table() }">
<Tr bgcolor="lightyellow">
<TD colspan="2" align="center">
The Grisly Details of: <MvEVAL EXPR = "{l.info:fname $ ' ' $ l.info:lname}">
<div align="right">
<a href="{ s.documenturl $ 'action=modify_record&search_id=' $ l.info:id }">Modify</a>
<br>
<a href="{ s.documenturl $ 'action=delete_record&search_id=' $ l.info:id }">Delete</a>
<br>
<a href="{ s.documenturl $ 'action=main' }">List Records</a>
<br>
</div>
</td>
</tr>
<TR bgcolor="white">
<TD bgcolor="#DFDFDF" width="150" align="right"><B>First and Last Name:</B></td>
<TD><MvEVAL EXPR = "{l.info:fname $ ' ' $ l.info:lname}"></td>
</tr>
<TR bgcolor="white">
<TD bgcolor="#DFDFDF" align="right"><b>Age:</b></td>
<TD><MvEVAL EXPR = "{l.info:age}"></td>
</tr>
<TR bgcolor="white">
<TD bgcolor="#DFDFDF" valign="top" align="right"><b>Description:</b></td>
<TD><MvEVAL EXPR = "{glosub(l.info:desc,asciichar(13),'<br>')}"></td>
</tr>
<MvASSIGN NAME = "l.ok" VALUE = "{ close_table() }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
MODIFY RECORD:
This is an important function when making a database administration as you must be able to
edit records (normally).
Notice the script exits if the proper record is not found in both modify functions. It is
more important during the save so you don't overwrite other information, but it's a good
idea to check if you found the proper record EVERY SINGLE time you do an MvFIND.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "modify_record" PARAMETERS = "">
<MvASSIGN NAME = "l.info" VALUE = "{ get_simple_db_info(g.search_id) }">
<MvIF EXPR = "{NOT l.info}">
<B>Error:</b> Record not found.
<MvFUNCRETURN VALUE = "{ 0 }">
</MvIF>
<FORM ACTION="{ s.documenturl $ 'action=modify_record2&search_id=' $ g.search_id }" METHOD="POST" ENCTYPE="application/x-www-form-urlencoded">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_table() }">
<TR bgcolor="lightyellow">
<TD colspan="2">
<big>Modify Record</big>
<div align="right">
<a href="{ s.documenturl $ 'action=main' }">List Records</a>
</div>
</td>
</tr>
<TR bgcolor="white">
<TD align="right"><B>First Name:</b> </td>
<TD><input type="text" size="50" name="fname" value="{ encodeentities(l.info:fname) }"></td>
</tr>
<TR bgcolor="white">
<TD align="right"><B>Last Name:</b> </td>
<TD><input type="text" size="50" name="lname" value="{ encodeentities(l.info:lname) }"></td>
</tr>
<TR bgcolor="white">
<TD align="right"><b>Age:</b> </td>
<TD><input type="text" size="4" name="age" value="{ l.info:age }"></td>
</tr>
<tr bgcolor="white">
<TD valign="top" align="right"><B>Description:</B> </td>
<TD><textarea wrap="virtual" cols="60" rows="10" name="desc"><MvEVAL EXPRESSION = "{encodeentities(l.info:desc)}"></textarea></td>
</tr>
<TR bgcolor="gray">
<TD align="right" colspan="2"><input type="submit" name="submit" value="Save Changes"></td>
</tr>
<MvASSIGN NAME = "l.ok" VALUE = "{ close_table() }">
</FORM>
</MvFUNCTION>
<MvFUNCTION NAME = "modify_record2" PARAMETERS = "">
<MvASSIGN NAME = "l.ok" VALUE = "{ update_simple_db(g.search_id,g.fname,g.lname,g.age,g.desc) }">
<MvASSIGN NAME = "l.ok" VALUE = "{ main() }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
LIST RECORDS:
This will list the records in order of the "name" index (alphabetical)
For the Modify and Delete links we're using the "id" field as the search_id. This is
because the "id" index contains this value and makes it safe and easy to find a record.
When evaluating the "desc" field, we need to manually put in line breaks because of how
textarea input fields handle text. So, we do a search for two asciichars that serve as
line breaks in the textarea field, and replace them with "glosub" with a <br> to give it
the same line break that we see in the textarea input field.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "list_records" PARAMETERS = "">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_simple_db('lname') }">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_table() }">
<TR bgcolor="white">
<TD colspan="6">
<form method="post" action="{ s.documenturl $ 'action=search' }">
<b>Full or partial last name:</b>
<input type="text" name="sterm">
<input type="submit" value="Search">
</form>
</td>
</tr>
<tr bgcolor="silver">
<TD><b>Name</b> - click for details</td>
<TD><b>Date Entered</b></td>
<TD colspan="4"><b>Record ID</b></td>
</tr>
<MvWHILE EXPRESSION = "{NOT simple_db.d.eof}">
<MvWHILE EXPRESSION = "{NOT simple_db.d.id AND NOT simple_db.d.eof}">
<MvSKIP name="simple_db">
</MvWHILE>
<MvASSIGN NAME = "l.color" VALUE = "{ set_color(l.color,'white','#DFDFDF') }">
<TR bgcolor="{ l.color }">
<TD><a href="{ s.documenturl $ 'action=record_details&search_id=' $ simple_db.d.id }"><MvEVAL EXPRESSION = "{'<B>' $ encodeentities(simple_db.d.fname) $ ' ' $ encodeentities(simple_db.d.lname) $ '</b>'}"></a></td>
<TD>
<MvASSIGN NAME = "l.t_info" VALUE = "{ get_time_info(simple_db.d.date) }">
<MvEVAL EXPRESSION = "{'<B>' $ l.t_info:month $ '/' $ l.t_info:day $ '/' $ l.t_info:year $ ' ' $ l.t_info:hour $ ':' $ l.t_info:minute $ '</b>'}">
</td>
<TD>
<MvEVAL EXPRESSION = "{'<B>' $ simple_db.d.id $ '</b>'}">
</td>
<TD align="center">
<a href="{ s.documenturl $ 'action=modify_record&search_id=' $ simple_db.d.id }">Modify</a>
</td>
<TD align="center">
<a href="{ s.documenturl $ 'action=delete_record&search_id=' $ simple_db.d.id }">Delete</a>
</td>
</tr>
<MvSKIP name="simple_db">
</MvWHILE>
<MvASSIGN NAME = "l.ok" VALUE = "{ close_table() }">
<MvCLOSE name="simple_db">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
SEARCH FUNCTION
This is a simple function to search on the last name. It finds a full or partial
last name and continues listing until that string is not in the last name field
any longer.
So, for example, let's say you enter: SMI
It will find the first instance of SMI, which might be SMITH, then go ahead and
roll through the database until the last name does not have SMI in it any longer.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "search" PARAMETERS = "">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_table() }">
<TR bgcolor="lightyellow">
<TD colspan="5">
<big>Simple Database using Miva version 3.9x and above </big>
<div align="right">
<a href="{ s.documenturl $ 'action=record_add' }">Add a New Record</a>
<br>
<a href="{ s.documenturl $ 'action=main' }">List Records</a>
<br>
</div>
<big>Search Results:</big>
</td>
</tr>
<MvASSIGN NAME = "l.ok" VALUE = "{ open_simple_db('lname') }">
<MvFIND NAME = "simple_db" VALUE = "{g.sterm}">
<MvIF EXPR = "{simple_db.d.eof}">
<TR bgcolor="white"><TD colspan="5">No records found for: <b><MvEVAL EXPR = "{g.sterm}"></b></td></tr>
<MvELSE>
<MvWHILE EXPRESSION = "{NOT simple_db.d.eof AND g.sterm CIN simple_db.d.lname}">
<MvASSIGN NAME = "l.color" VALUE = "{ set_color(l.color,'white','#DFDFDF') }">
<TR bgcolor="{ l.color }">
<TD><a href="{ s.documenturl $ 'action=record_details&search_id=' $ simple_db.d.id }"><MvEVAL EXPRESSION = "{'<B>' $ encodeentities(simple_db.d.fname) $ ' ' $ encodeentities(simple_db.d.lname) $ '</b>'}"></a></td>
<TD>
<MvASSIGN NAME = "l.t_info" VALUE = "{ get_time_info(simple_db.d.date) }">
<MvEVAL EXPRESSION = "{'<B>' $ l.t_info:month $ '/' $ l.t_info:day $ '/' $ l.t_info:year $ ' ' $ l.t_info:hour $ ':' $ l.t_info:minute $ '</b>'}">
</td>
<TD>
<MvEVAL EXPRESSION = "{'<B>' $ simple_db.d.id $ '</b>'}">
</td>
<TD align="center">
<a href="{ s.documenturl $ 'action=modify_record&search_id=' $ simple_db.d.id }">Modify</a>
</td>
<TD align="center">
<a href="{ s.documenturl $ 'action=delete_record&search_id=' $ simple_db.d.id }">Delete</a>
</td>
</tr>
<MvSKIP name="simple_db">
</MvWHILE>
</MvIF>
<MvCLOSE name="simple_db">
<MvASSIGN NAME = "l.ok" VALUE = "{ close_table() }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
ADD A RECORD TO THE DATABASE
insert_simple_db(...):
This function is used to add a record to the database. The first thing it does
is search for the id of '', which is nothing. When deleting a record, the id
field is set to nothing, so when adding a record we search for a nothing id field
in order to recycle the record.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "insert_simple_db" PARAMETERS = "id,fname,lname,age,desc">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_simple_db('all') }">
<MvFIND NAME = "simple_db" VALUE = "{''}" EXACT="EXACT">
<MvASSIGN NAME = "simple_db.d.id" VALUE = "{ s.dyn_time_t }">
<MvASSIGN NAME = "simple_db.d.date" VALUE = "{ assign_time() }">
<MvASSIGN NAME = "simple_db.d.fname" VALUE = "{ ltrim(rtrim(l.fname)) }">
<MvASSIGN NAME = "simple_db.d.lname" VALUE = "{ ltrim(rtrim(l.lname)) }">
<MvASSIGN NAME = "simple_db.d.age" VALUE = "{ l.age }">
<MvASSIGN NAME = "simple_db.d.desc" VALUE = "{ l.desc }">
<MvIF EXPR = "{simple_db.d.eof}">
<MvADD NAME = "simple_db" >
<MvELSE>
<MvUPDATE name="simple_db">
</MvIF>
<MvCLOSE NAME = "simple_db">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
UPDATE A RECORD IN THE DATABASE
update_simple_db(...):
This function is used to update a record in the database.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "update_simple_db" PARAMETERS = "id,fname,lname,age,desc">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_simple_db('all') }">
<MvFIND NAME = "simple_db" VALUE = "{l.id}" EXACT="EXACT">
<MvIF EXPR = "{NOT simple_db.d.eof}">
<MvASSIGN NAME = "simple_db.d.fname" VALUE = "{ ltrim(rtrim(l.fname)) }">
<MvASSIGN NAME = "simple_db.d.lname" VALUE = "{ ltrim(rtrim(l.lname)) }">
<MvASSIGN NAME = "simple_db.d.age" VALUE = "{ l.age }">
<MvASSIGN NAME = "simple_db.d.desc" VALUE = "{ l.desc }">
<MvUPDATE name="simple_db">
<MvASSIGN NAME = "l.success" VALUE = "{ 1 }">
</MvIF>
<MvCLOSE NAME = "simple_db">
<MvFUNCRETURN VALUE = "{ l.success }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
GET DB INFO
get_simple_db_info(...):
This function returns a structure of the information in one record of the database
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "get_simple_db_info" PARAMETERS = "search_id">
<MvASSIGN NAME = "l.ok" VALUE = "{ open_simple_db('id') }">
<MvFIND NAME = "simple_db" VALUE = "{l.search_id}" EXACT="EXACT">
<MvIF EXPR = "{NOT simple_db.d.eof}">
<MvASSIGN NAME = "l.info:id" VALUE = "{ simple_db.d.id }">
<MvASSIGN NAME = "l.info:date" VALUE = "{ simple_db.d.date }">
<MvASSIGN NAME = "l.info:fname" VALUE = "{ simple_db.d.fname }">
<MvASSIGN NAME = "l.info:lname" VALUE = "{ simple_db.d.lname }">
<MvASSIGN NAME = "l.info:age" VALUE = "{ simple_db.d.age }">
<MvASSIGN NAME = "l.info:desc" VALUE = "{ simple_db.d.desc }">
</MvIF>
<MvCLOSE NAME = "simple_db">
<MvFUNCRETURN VALUE = "{ l.info }">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
OPENING THE DATABASE
open_db(alias,index)
This is a great way to open databases using a function. It saves all sorts of time
in scripts since you don't have to change your whole script when you make a change
to the indexes and such.
The "alias" parameter is what you will name your database (I use "db" in this script)
The "index" parameter is for what index you are using. Remember that when adding records,
you MUST use "all". All indexes have to be opened when adding a new record so they
can be updated when the record is added.
Note: When opening all indexes, I usually start with the "id" index since I'm either
adding, deleting, or modifying record, and for delete and modify I'll be looking
for a particular record using the "id" index (see modify_record2 and delete_record)
I always check for the existance of the database before opening. If it doesn't exist,
I create it. This is not always a safe move- if there were a hardware problem or something
the system may overwrite a database in error. It's always good to make a backup of
an existing database when creating a new one. Use fcopy(...) or frename(...) to copy and rename
datafiles.
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "open_simple_db" PARAMETERS = "index">
<MvIF EXPR = "{NOT fexists(g.data $ '/simple_db.dbf')}">
<MvASSIGN NAME = "l.ok" VALUE = "{ create_simple_db() }">
</MvIF>
<MvIF EXPRESSION = "{l.index EQ 'id'}">
<MvASSIGN NAME = "l.index" VALUE = "{ g.data $ '/simple_db_id.mvx' }">
</MvIF>
<MvIF EXPRESSION = "{l.index EQ 'lname'}">
<MvASSIGN NAME = "l.index" VALUE = "{ g.data $ '/simple_db_lname.mvx' }">
</MvIF>
<MvIF EXPRESSION = "{l.index EQ 'age'}">
<MvASSIGN NAME = "l.index" VALUE = "{ g.data $ '/simple_db_age.mvx' }">
</MvIF>
<MvIF EXPRESSION = "{l.index EQ 'all'}">
<MvASSIGN NAME = "l.index" VALUE = "{ g.data $ '/simple_db_id.mvx,' $ g.data $ '/simple_db_lname.mvx,' $ g.data $ '/simple_db_age.mvx,' $ g.data $ '/simple_db_date.mvx' }">
</MvIF>
<MvOPEN NAME = "simple_db" DATABASE = "{ g.data $ '/simple_db.dbf' }" INDEXES = "{ l.index }" TYPE="xbase3">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
CREATE THE DATABASE
create_simple_db()
This is a basic database initializing function.
Notice on the line where I create the simple_db_name.mvx index. I've used "to_lower(simple_db.d.name)"
for the index expression. The reason is that capital letters and small letters will not
sort properly since the letters are being sorted by ascii value and not the "letter" that
they are. By forcing all the letters to lower case in the index expression, you are
guaranteed that there can be no problems with sorting (as long as you remove spaces by using
ltrim(..) and rtrim(..) before saving the record- starting spaces will screw sorting up)
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "create_simple_db" PARAMETERS = "">
<MvIF EXPRESSION = "{NOT fexists(g.data)}">
<MvASSIGN NAME = "l.ok" VALUE = "{ fmkdir(g.data) }">
</MvIF>
<MvCREATE NAME = "simple_db"
DATABASE = "{ g.data $ '/simple_db.dbf' }"
FIELDS ="
id char(10),
fname char(32),
lname char(32),
age NUMBER(3),
date char(14),
desc MEMO()">
<MvMAKEINDEX NAME = "simple_db" INDEXFILE = "{ g.data $ '/simple_db_date.mvx' }" EXPRESSION = "{ simple_db.d.date }" FLAGS = "">
<MvMAKEINDEX NAME = "simple_db" INDEXFILE = "{ g.data $ '/simple_db_id.mvx' }" EXPRESSION = "{ simple_db.d.id }" FLAGS = "">
<MvMAKEINDEX NAME = "simple_db" INDEXFILE = "{ g.data $ '/simple_db_age.mvx' }" EXPRESSION = "{ simple_db.d.age }" FLAGS = "number">
<MvMAKEINDEX NAME = "simple_db" INDEXFILE = "{ g.data $ '/simple_db_lname.mvx' }" EXPRESSION = "{ tolower(simple_db.d.lname) }" FLAGS = "string">
<MvCLOSE name="simple_db">
</MvFUNCTION>
<MvCOMMENT>
###############################################################################
UTILITY FUNCTIONS
open_table: creates the table with the nifty lines in it
close_table: closes the table
assign_time: returns 14 digit time YYYYMMDDHHMMSS
set_color: controls color of lines in multiline list
get_time_info: returns a structure of time information from a 14 char assign_time value
###############################################################################
</MvCOMMENT>
<MvFUNCTION NAME = "open_table" PARAMETERS = "">
<table width="800" bgcolor="black" cellpadding="3" cellspacing="1" border="0">
</MvFUNCTION>
<MvFUNCTION NAME = "close_table" PARAMETERS = "">
</table>
</MvFUNCTION>
<MvFUNCTION NAME = "assign_time" PARAMETERS = "">
<MvFUNCRETURN VALUE = "{ s.dyn_tm_year $ padl(s.dyn_tm_mon,2,'0') $ padl(s.dyn_tm_mday,2,'0') $ padl(s.dyn_tm_hour,2,'0') $ padl(s.dyn_tm_min,2,'0') $ padl(s.dyn_tm_sec,2,'0')}">
</MvFUNCTION>
<MvFUNCTION NAME = "set_color" PARAMETERS = "current,color1,color2">
<MvIF EXPRESSION = "{l.color1 EQ l.current}">
<MvASSIGN NAME = "l.current" VALUE = "{ l.color2 }">
<MvELSE>
<MvASSIGN NAME = "l.current" VALUE = "{ l.color1 }">
</MvIF>
<MvFUNCTIONRETURN VALUE = "{ l.current }">
</MvFUNCTION>
<MvFUNCTION NAME = "get_time_info" PARAMETERS = "time">
<MvASSIGN NAME = "l.info:year" VALUE = "{ substring(l.time,1,4) }">
<MvASSIGN NAME = "l.info:month" VALUE = "{ substring(l.time,5,2) }">
<MvASSIGN NAME = "l.info:day" VALUE = "{ substring(l.time,7,2) }">
<MvASSIGN NAME = "l.info:hour" VALUE = "{ substring(l.time,9,2) }">
<MvASSIGN NAME = "l.info:minute" VALUE = "{ substring(l.time,11,2) }">
<MvASSIGN NAME = "l.info:second" VALUE = "{ substring(l.time,13,2) }">
<MvFUNCRETURN VALUE = "{ l.info }">
</MvFUNCTION>
<MvFUNCTION NAME = "copyright" PARAMETERS = "">
<font face="arial,helvetica" size="-1">
<P>
This script brought to you by <a href="http://www.scotsscripts.com">ScotsScripts.com</a>.
<br>
There are no guarantees, explicit or implied, that this script will work. It is intended to be treated as an
<br>
example and tutorial of one way to manipulate databases using Miva Script.
<br>
</font>
</MvFUNCTION>
updated April 23, 2013
