Switch to: V9V8V7V6V5

Lesson 1 - Creating an Employee Database

This lesson provides you with a basic introduction to how the Valentina database system works with the LiveCode development environment.

What You Should Already Know

This lesson is designed for the users of LiveCode who already understand the basic structure of LiveCode projects, functions, handlers, and using interface tools. It does not require the use of RevDB.

What You Will Learn

After you complete this tutorial, you will be able to:

  • Initialize and shut down the Valentina engine
  • Create user interface elements with scripts that create Valentina databases
  • Create a user interface for browsing records

Step 1: Initialization

New project creation

Start LiveCode IDE. Create a new empty stack and save it.

Valentina Engine Init

In order to start, you should initialize Valentina with the help of Valentina_Init() function. It can be done in the “on openCard” event, for example.

To do this you should do the following:

  • Choose “Edit Script” from the card popup menu.
  • Write the following text:
on openCard
    get Valentina_Init( 10 * 1024 * 1024  )
end openCard

Valentina Engine Shutdown

In order to finish, you should use Valentina_ShutDown() function. It can be done in “on CloseCard” event, for example.

To do this you should do the following:

  • Choose “Edit Script” from the card popup menu.
  • Write the following text:
on closeCard
    get Valentina_ShutDown
end closeCard

Step 2: Database Creation

At this step, you will learn how to create a new database, to open and to close the database.

In this example, we want to create a new database on card open if there is no database on the disk or to open the existing one.

Creation of DatabaseCreate event handler

Firstly, we have to construct a database object. It will be stored inside the plugin. After this, we have to save resulting reference into some variable for further operating. And finally, we write some code to open the database or to create it.

To do this you should do the following:

  • Choose “Edit Script” from the card popup menu.
  • Declare local variable mDatabase.
  • Add DatabaseCreate event firing into “on openCard” handler just after valentina_Init call.
  • Add DatabaseCreate event handler in such a way:
local mDatabase

on DatabaseCreate
    put VDatabase_Constructor() into mDatabase
   
    -- Build db full path near to stack:
    get the effective filename of this stack
    set the itemDel to slash
    put the (item 1 to -2 of it) & slash & "Database1.vdb" into dbPath

    get VDatabase_Open( mDatabase, dbPAth )
    
    if VDatabase_ErrNumber(mDatabase) is not "0" then
       get VDatabase_Create( mDatabase, dbPAth, "kDscDatBlbInd" )      
    end if
   
    get VDatabase_DateFormat( mDatabase, "kYMD" ) 
    get VDatabase_DateSep( mDatabase, "-" ) 
end DatabaseCreate

Creation DatabaseClose event handler

In order to close the database, you should call VDatabase_Close function. Then, plugin object could be destroyed using VDatabase_Destructor call.

To do this you should do the following:

  • Choose “Edit Script” from the card popup menu.
  • Add DatabaseClose event firing into ” on closeCard” handler just before Valentina_ShutDown call.
  • Add DatabaseClose event handler in such a way:
on DatabaseClose
    get VDatabase_Close( mDatabase )
    put VDatabase_Destructor( mDatabase ) into mDatabase
end DatabaseClose

Step 3: Table Creation

At this step, you will learn how to create a database structure.

Creation of CreateStructure event handler

We want to create a table with three fields: Name, LName, BirthDate. The similar way we constructed database we will get the reference to the created table.

To do this you should do the following:

  • Declare local variable pTable.
  • Create CreateStructure event handler.
  • Write down the following text in the code editor window:
on CreateStructure
    put VDatabase_CreateTable(mDatabase, "Person") into pTable
  
    get VTable_CreateVarcharField( pTable, "Name", 1022, "fIndexed" )
    get VTable_CreateVarcharField( pTable, "LName", 1022, "fIndexed" )
    get VTable_CreateDateField( pTable, "BirthDate", "fIndexed" )
end CreateStructure

Method CreateStructure() Call in Open()

You need to fire CreateStructure event from DatabaseCreate event handler in order to create the structure of the database on its creation.

To do this you should do the following:

  • Append the following text - just after VDatabase_Create call:
CreateStructure

After that DatabaseCreate handler will look like:

on DatabaseCreate

    put VDatabase_Constructor() into mDatabase

    -- Build db full path near to stack:
    get the effective filename of this stack
    set the itemDel to slash
    put the (item 1 to -2 of it) & slash & "Database1" into dbPath

   get VDatabase_Open( mDatabase, dbPAth )
   
   if VDatabase_ErrNumber(mDatabase) is not "0" then
      get VDatabase_Create( mDatabase, dbPAth, "kDscDatBlbInd" )      
      CreateStructure
   end if
   
   get VDatabase_DateFormat( mDatabase, "kYMD" ) 
   get VDatabase_DateSep( mDatabase, "-" ) 
end DatabaseCreate

Step 4: Window Design

At this step, you will learn how to create graphic elements to control the database. As a result of mentioned below actions we will get the window of such kind:

Creation of Edit Fields

You need to create edit fields for displaying and editing of table field values. Each table field should have a corresponding edit field. To do this you should do the following:

  • Create three EditField controls and place them as it is shown on the picture. Give them names: efName, efLName, efBirthDate.
  • Create three StaticText controls and place them as it is shown on the picture. Set the text property to be Name, Last Name, BirthDate correspondingly for each element.


Creation of Record Modification Buttons

We need three buttons for adding, updating and deleting records in order to have the ability to change records.

To do this you should do the following:

  • Create three buttons.
  • Set the value of Name property equal to pbAdd, pbDelete, pbUpdate correspondingly.
  • Set the value of Caption property equal to Add, Delete, Update correspondingly.


Creation of Navigation Buttons

In order to have the ability to navigate records. We need four buttons for the movement to the first, last, previous or next record

To do this you should do the following:

  • Create four buttons.
  • Set the value of Name property equal to pbFirst, pbPrev, pbNext, pbLast correspondingly.
  • Set the value of Caption property equal to |«, <, >, »| correspondingly.


Step 5: Add Logic to Controls

At this step you will learn how to program logic for controls in order to add, update, delete records and also to navigate in records.

Pay attention that we have only mDatabase variable. That's why in every following function we should ask for Person table pointer.

Implementation of Action() for Add Button

You need to write the action which will be executed on pressing Add button.

To do this you should do the following:

  • Set mouseUp event handler and call DoAdd() function there.
  • Write the following text down into the card script:
function DoAdd
    put VDatabase_Table( mDatabase, "Person" ) into tblPerson
    get VTable_SetBlank(tblPerson)
    get CollectPanes()
    get VTable_AddRecord(tblPerson)
end DoAdd

Here we prepare the record buffer using VTable_SetBlank() function. We call CollectPanes() function which copies values from edit fields to table fields. We add the record to the table. VTable_AddRecord() function returns RecID of new record. In the given example we ignore this.

Implementation of Action() for Update Button

You need to write the action which will be executed on pressing Update button.

To do this you should do the following:

  • Set mouseUp event handler and call DoUpdate() function there.
  • Write the following text down into the card script:
function DoUpdate
    put VDatabase_Table( mDatabase, "Person" ) into tblPerson
    get CollectPanes()
    get VTable_UpdateRecord(tblPerson)
end DoUpdate

Here we call CollectPanes() function, which copies values from edit fields to table fields. We update the record of the table.

Implementation of Action() for Delete Button

You need to write the action which will be executed on pressing Delete button. To do this you should do the following:

  • Set mouseUp event handler and call DoDelete() function there.
  • Write the following text down into the card script:
function DoDelete
    put VDatabase_Table( mDatabase, "Person" ) into tblPerson
    get VTable_DeleteRecord(tblPerson)
    get PopulateFields()
end DoDelete

CollectPanes Function Implementation

We create CollectPanes() function which copies values from edit fields to table fields.

To do this you should do the following:

  • Write the following text down into the card script:
function CollectPanes
     put VDatabase_Table( mDatabase, "Person" ) into tblPerson
  
     put VTable_Field( tblPerson, "Name" )  into pName
     put VTable_Field( tblPerson, "LName" )  into pLName
     put VTable_Field( tblPerson, "BirthDate" )  into pBirthDate
  
    get VField_Value( pName, field efName )  
    get VField_Value( pLName, field efLName )  
    get VField_Value( pBirthDate, field efBirthDate )  
end CollectPanes

PopulateFields Function Implementation

We create PopulateFields() function which copies values from table fields to edit field. This function is the reverse one to the CollectPanes().

To do this you should do the following:

  • Write the following text down into the card script:
function PopulateFields
    put VDatabase_Table( mDatabase, "Person" ) into tblPerson
    put VTable_Field( tblPerson, "Name" )  into pName
    put VTable_Field( tblPerson, "LName" )  into pLName
    put VTable_Field( tblPerson, "BirthDate" )  into pBirthDate
    put VField_Value( pName ) into field efName
    put VField_Value( pLName ) into field efLName
    put VField_Value( pBirthDate ) into field efBirthDate
end PopulateFields

Implementation for |« Button

You need to write the action which will be executed on pressing |« button.

To do this you should do the following:

  • Set mouseUp event handler and call DoFirst() function there.
  • Write the following text down into the card script:
function DoFirst
    put VDatabase_Table( mDatabase, "Person" ) into tblPerson
    get VTable_FirstRecord(tblPerson)
    get PopulateFields()
end DoFirst

Implementation for < Button

You need to write the action which will be executed on pressing < button.

To do this you should do the following:

  • Set mouseUp event handler and call DoPrev() function there.
  • Write the following text down into the card script:
function DoPrev
  put VDatabase_Table( mDatabase, "Person" ) into tblPerson
  get VTable_PrevRecord(tblPerson)
  get PopulateFields()
end DoPrev

Implementation for > Button

You need to write the action which will be executed on pressing > button.

To do this you should do the following:

  • Set mouseUp event handler and call DoNext() function there.
  • Write the following text down into the card script:
function DoNext
    put VDatabase_Table( mDatabase, "Person" ) into tblPerson
    get VTable_NextRecord(tblPerson)
    get PopulateFields()
end DoNext

Implementation for »| Button

You need to write the action which will be executed on pressing »| button.

To do this you should do the following:

  • Set mouseUp event handler and call DoLast() function there.
  • Write the following text down into the card script:
function DoLast
    put VDatabase_Table( mDatabase, "Person" ) into tblPerson
    get VTable_LastRecord(tblPerson)
    get PopulateFields()
end DoLast

Step 6: Refactoring - References Extraction

In this step you will learn the simple refactoring - the extraction of references. In the given project you can see that the same codes are often repeated. You can avoid this and simplify the program code if you store the references to the local variables or custom properties.

Creation of local variables

You need to declare variables for table and all fields references. And filled them in DatabaseCreate event handler.

To do this you should do the following:

  • Declare local variables: mPerson, mName, mLName, mBirthDate.
  • Make the following changes in DatabaseCreate handler text.
  • And use these references instead of looking for them.
...
    put VDatabase_Table( mDatabase, "Person" ) into mPerson
    put VTable_Field( mPerson, "Name" )  into mName
    put VTable_Field( mPerson, "LName" )  into mLName
    put VTable_Field( mPerson, "BirthDate" )  into mBirthDate
end DatabaseCreate