Data Dictionary
In my applications, I frequently have to modify the field's length according to my clients' needs. Therefore, I try a different approach
in my programming, which enables me to give the feature of user-defined length.
In Datadict.ch, I do the following:
- I define all fields in my tables to eliminate Clipper's warnings
- I prepare the declaration of lenFields_ variable for storing the fields' length
In Datadict.prg, I do the following:
- I define the number of tables and indexes, as shown in nNumTables and nNumIndexes
- I create a file-wide variable for storing the progress bar's position
- I create a hard-coded variable for determining the index's extension since ordBagExt() returns '.idx' instead of '.cdx' :(
- I create a number of variables for storing the table's structure.
Be careful, if you use a lot of tables, then you'll ended up with plenty
of static variables which is not recommended. In this case, you should change to an array, similar to my method for lenFields_
- I call initStruct(), which initializes the tables' structures to respective variable
- I call initProgress(), which simply displays the progress window along with the progress bar
- I call checkTable(), which checks for tables' existence. If a table is not found, then the index will be deleted to ensure the
index is synchronized after the table is created.
- I call openTable(), which opens the table in shared mode and update the progress bar
- I call openIndex(), which checks for indexes' existence. If an index is not found, then the program will reopen the table in
exclusive mode, create the index, reopen the table in shared mode, and update the progress bar
- I call exitProgress(), which close the progress window and restore the screen content
There are several functions, as follows:
- reIndex(), which uses the simplest method by deleting all indexes, call dataDict(), and close the tables :)
- packData(), which opens all tables in exclusive mode, pack data, and close all tables
- chkStruct(), which compares the tables' structures with the structures stored in variables.
If the structure is different, then it will ask the user's confirmation or it will automatically fix the structure's
difference, depending on the parameter it receives. Whenever a structure inconsistencies is encountered, it will
perform Reindex as well.
In SetupTbl.prg, I do the following:
- I create the public variable lenFields_ as array and initialized all all elements with 0
- I define the default fields' length
- For certain clients (if necessary), I redefine the fields' length
- I detect the existence of a special file which stores the custom fields' structures. If allowed (configurable from the application's
.INI file), the custom fields' structures will be loaded.
There are several functions, as follows:
- useSen(), which determines whether the application uses decimals for transaction amount
- setTableLen(), which provides a modifiable structure browser. If the user confirms for the custom fields' structures, then it will
save the custom structure to a file, check the structure and force to fix the structure, and reload the structure information
/*----------------------------------------------------------------------------+
¦ ¦
¦ Designer: Hianoto Santoso ¦
¦ Author : Hianoto Santoso ¦
¦ Language: CA-Clipper 5.2x ¦
¦ ¦
¦ File : Datadict.ch ¦
¦ Purpose : The header for Data Dictionary System ¦
¦ ¦
¦ Copyright (C) 1992-98, dbMaster Software Development. ¦
¦ ¦
+----------------------------------------------------------------------------*/
#include "dB-Pro.ch"
// Declare FIELDs
// --------------
field grpKode, lokKode
field brgKode, brgNama, brgSatuan, brgNotes, ;
brgSaldoAwal, brgTtlMasuk, brgTtlKeluar, ;
brgPanjang, brgLebar, brgPjgInch, brgLbrInch, brgDept, ;
prcStdBeli, prcStdJual, brgJmlMin, brgJmlMax
field brgBBM, brgNoSJ, brgTglMasuk, brgJml, brgHrg, brgTglExp
// Structure definition
// --------------------
#define NUMCFG_FIELDS 99
memVar lenFields_
#xTranslate lenGrpKode => lenFields_\[ 11 ]
#xTranslate lenLokKode => lenFields_\[ 13 ]
#xTranslate lenBrgKode => lenFields_\[ 21 ]
#xTranslate lenBrgNama => lenFields_\[ 22 ]
#xTranslate lenBrgSatuan => lenFields_\[ 23 ]
#xTranslate lenBrgJumlah => lenFields_\[ 24 ]
#xTranslate lenBrgDesimal => lenFields_\[ 25 ]
#xTranslate lenBrgHarga => lenFields_\[ 26 ]
#xTranslate lenBrgBBM => lenFields_\[ 27 ]
#xTranslate lenTxnSJ => lenFields_\[ 32 ]
#xTranslate lenTxnNotes => lenFields_\[ 36 ]
Go Top
/*----------------------------------------------------------------------------+
¦ ¦
¦ Projects: dRIP ¦ dbMaster Receivable - Inventory - Payable ¦
¦ Designer: Hianoto Santoso ¦
¦ Author : Hianoto Santoso ¦
¦ Language: CA-Clipper 5.2x ¦
¦ ¦
¦ Function: dataDict() ¦
¦ Purpose : The Data Dictionary module ¦
¦ ¦
¦ History : ¦
¦ - 30-Dec-96...Write dataDict() ¦
¦ - 27-Mar-98...Add version control ¦
¦ ¦
¦ Copyright (C) 1992-98, dbMaster Software Development. ¦
¦ ¦
+----------------------------------------------------------------------------*/
#include "Datadict.ch"
#define nJmlDBF 3
#define nJmlIDX 8
static nPosProgress
static cOrdBagExt := ".cdx"
static strBrg
static strBrgDtl
static strBrgSld
function dataDict()
local sCursor := setCursor( 0 )
initStruct()
initProgress()
checkTable()
openTable()
openIndex()
exitProgress()
setCursor( sCursor )
return nil
function initStruct()
// File Barang
// -------------------------------------------------------------------------
strBrg := { ;
{ "brgKode", "C", lenBrgKode, 0 }, ;
{ "brgNama", "C", lenBrgNama, 0 }, ;
{ "grpKode", "C", lenGrpKode, 0 }, ;
{ "brgSatuan", "C", lenBrgSatuan, 0 }, ;
{ "brgSaldoAw", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgTtlMasu", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgTtlKelu", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgTtlRusa", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgTtlPinj", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "prcStdBeli", "N", lenBrgHarga, iif( useSen(), 2, 0 ) }, ;
{ "prcStdJual", "N", lenBrgHarga, iif( useSen(), 2, 0 ) }, ;
{ "brgJmlMin", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgJmlMax", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgNotes", "C", lenTxnNotes, 0 } ;
}
if isKaca()
aAdd( strBrg, { "brgPanjang", "N", 9, 4 } )
aAdd( strBrg, { "brgLebar", "N", 9, 4 } )
aAdd( strBrg, { "brgPjgInch", "N", 9, 4 } )
aAdd( strBrg, { "brgLbrInch", "N", 9, 4 } )
endif
if isDeptStore()
aAdd( strBrg, { "brgDept", "N", 2, 0 } )
endif
// File Barang Detail
// -------------------------------------------------------------------------
strBrgDtl := { ;
{ "brgKode", "C", lenBrgKode, 0 }, ;
{ "brgBBM", "C", lenBrgBBM, 0 }, ;
{ "brgNoSJ", "C", lenTxnSJ, 0 }, ;
{ "brgTglMasu", "D", 8, 0 }, ;
{ "lokKode", "C", lenLokKode, 0 }, ;
{ "brgJml", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgHrg", "N", lenBrgHarga, iif( useSen(), 2, 0 ) } ;
}
// File Barang Saldo
// ------------------------------------------------------------------------
strBrgSld := { ;
{ "brgKode", "C", lenBrgKode, 0 }, ;
{ "lokKode", "C", lenLokKode, 0 }, ;
{ "brgJml", "N", lenBrgJumlah, lenBrgDesimal }, ;
{ "brgHrg", "N", lenBrgHarga, iif( useSen(), 2, 0 ) } ;
}
return nil
static function checkTable()
local sMessage
// Save environment
sMessage := dMessage( "Memeriksa keberadaan file˛ database, tunggu sebentar..." )
// Check tables' existence
if ! file( "Brg.dbf" )
fErase( "Brg" + cOrdBagExt )
dbCreate( "Brg", strBrg )
endif
if ! file( "BrgDtl.dbf" )
fErase( "BrgDtl" + cOrdBagExt )
dbCreate( "BrgDtl", strBrgDtl )
endif
if ! file( "BrgSld.dbf" )
fErase( "BrgSld" + cOrdBagExt )
dbCreate( "BrgSld", strBrgSld )
endif
// Restore environment
dMessage( sMessage )
return nil
static function openTable()
local sMessage
// Save environment
sMessage := dMessage( "Membuka semua file database, tunggu sebentar..." )
// Open all tables
close all
dNetUse( "Brg", FALSE,,, 0 ); incProgress( "Membuka file Brg.dbf" )
dNetUse( "BrgDtl", FALSE,,, 0 ); incProgress( "Membuka file BrgDtl.dbf" )
dNetUse( "BrgSld", FALSE,,, 0 ); incProgress( "Membuka file BrgSld.dbf" )
// Restore environment
dMessage( sMessage )
return nil
static function openIndex()
local sMessage
#xCommand CHECK INDEX TAG <cTag> USING <cKey> IN <cWorkArea> => ;
incProgress( "Index " + <(cWorkArea)> + cOrdBagExt + " -» " + <(cKey)> );;
if <cWorkArea>->( ordNumber( <(cTag)> ) ) == 0;;
<cWorkArea>->( dbCloseArea() );;
dNetUse( <(cWorkArea)>, TRUE,,, 0 );;
index on <cKey> tag <cTag>;;
<cWorkArea>->( dbCloseArea() );;
dNetUse( <(cWorkArea)>, FALSE,,, 0 );;
endif
// Save environment
sMessage := dMessage( "Memeriksa keberadaan dan membuka file˛ index, tunggu sebentar..." )
// Check/create and open index files
select Brg
check index tag brgKode using brgKode in Brg
check index tag brgNama using brgNama in Brg
check index tag grpKode using grpKode in Brg
set order to tag brgKode
select BrgDtl
check index tag brgKodeBBM using brgKode + brgBBM in BrgDtl
check index tag brgKodeTgl using brgKode + dTos( brgTglMasuk ) in BrgDtl
check index tag brgKodeLok using brgKode + lokKode in BrgDtl
check index tag brgBBM using brgBBM in BrgDtl
set order to tag brgKodeBBM
select BrgSld
check index tag brgKodeLok using brgKode + lokKode in BrgSld
set order to tag brgKodeLok
// Restore environment
dMessage( sMessage )
return nil
/*
+--------------------------------------------------------------------+
| |
| Action: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| +------------------------------------------------------------+ |
| | | |
| | | |
| +------------------------------------------------------------+ |
| |
+--------------------------------------------------------------------+
*/
function initProgress()
nPosProgress := 1
create box centered size 9,70 color "W+/BG" nozoom
@ dBoxRowTop()+2, dBoxColLeft()+5 say "Action:" color "W+/BG"
dispBox( dBoxRowTop()+3, dBoxColLeft()+4, dBoxRowBottom()-2, dBoxColRight()-4, B_SINGLE, "N/BG" )
return nil
function incProgress( cAction )
local nLen := ( nPosProgress++ / ( nJmlDBF + nJmlIDX ) ) * 60
@ dBoxRowTop()+2, dBoxColLeft()+13 say padR( cAction, 50 ) color "GR+/BG"
@ row()+2, dBoxColLeft()+5 say replicate( "¦", nLen ) color "W+*/W*"
@ row()+1, dBoxColLeft()+5 say replicate( "¦", nLen ) color "W+*/W*"
return nil
function exitProgress()
close box nozoom
return nil
function reIndex( lForce )
default lForce type logical to FALSE
if lForce .or. dAlert( "Apakah Anda akan melakukan INDEX ULANG ??", { " Ya ", " Tidak " } ) == 1
dBoxFlip( "Sedang meng-INDEX ULANG, tunggu sebentar...", -1 )
fErase( "Brg" + cOrdBagExt )
fErase( "BrgDtl" + cOrdBagExt )
fErase( "BrgSld" + cOrdBagExt )
dataDict()
close database
close box nozoom
endif
return nil
function packData( lConfirm )
local sMessage := dMessage()
local sCursor := setCursor( 0 )
default lConfirm type logical to TRUE
if lConfirm .and. dAlert( "Apakah Anda akan melakukan PACK data ??", { " Ya ", " Tidak " } ) == 1
#xCommand PACK TABLE <cDBF> WITH INDEX <cNDX,...> => ;
if dNetUse( <(cDBF)>, TRUE );;
dMessage( "Sedang melakukan PACK untuk " + <(cDBF)> + ".dbf, tunggu sebentar..." );;
pack;;
close <cDBF>;;
endif
dBoxFlip( "Sedang meng-PACK data, tunggu sebentar...", -1 )
pack table Brg with index Brg, Brg1, Brg2
pack table BrgDtl with index BrgDtl, BrgDtl1, BrgDtl2, BrgDtl3
pack table BrgSld with index BrgSld
close box nozoom
endif
setCursor( sCursor )
dMessage( sMessage )
return nil
function chkStruct( lForce )
local lReIndex := FALSE
local cErrorText_
local sCursor := setCursor( 0 )
#xCommand CHECK STRUCTURE TABLE <cTable> IN <aStruct> => ;
if ! chkStruct2( <aStruct>, <cTable>->( dbStruct() ), <(cTable)>, @cErrorText_ );;
if lForce .or. dDispText( cErrorText_, "Error Structure in Table", { " Perbaiki ", " Batal " }, "W+/R" ) == 1;;
fixStruct( <(cTable)>, <aStruct> );;
lReIndex := TRUE;;
else;;
dbCloseAll();;
dBoxClose();;
if lReIndex;;
reIndex();;
end;;
return FALSE;;
end;;
end
default lForce type logical to FALSE
dBoxFlip( "Sedang memeriksa struktur database, tunggu sebentar...", -1 )
dataDict()
check structure table Brg in strBrg
check structure table BrgDtl in strBrgDtl
check structure table BrgSld in strBrgSld
close databases
close box nozoom
if lReIndex .or. lForce
reIndex( TRUE )
endif
setCursor( sCursor )
return TRUE
static function chkStruct2( str1_, str2_, cTable, cErrorText_ )
local lOk := TRUE
local nMax
local cTemp
local nCounter
local nCounter2
if len( str1_ ) != len( str2_ )
lOk := FALSE
else
for nCounter := 1 to len( str1_ )
for nCounter2 := 1 to len( str1_[ nCounter ] )
if valType( str1_[ nCounter ][ nCounter2 ] ) == "C"
if ! upper( str1_[ nCounter ][ nCounter2 ] ) == upper( str2_[ nCounter ][ nCounter2 ] )
lOk := FALSE
endif
elseif ! str1_[ nCounter ][ nCounter2 ] == str2_[ nCounter ][ nCounter2 ]
lOk := FALSE
endif
next
next
endif
if ! lOk
cErrorText_ := { "" }
aAdd( cErrorText_, "Incorrect structure in " + cTable + ".dbf" )
aAdd( cErrorText_, replicate( "-", 65 ) )
aAdd( cErrorText_, " Supposed: Current:" )
aAdd( cErrorText_, " --------- --------" )
nMax := max( len( str1_ ), len( str2_ ) )
for nCounter := 1 to nMax
cTemp := " "
// Current Field Info
// ------------------
if nCounter > len( str1_ ) // Blanks
cTemp += space( 28 )
else
cTemp += padR( str1_[ nCounter ][ 1 ], 10 ) + " " + ;
fieldType( str1_[ nCounter ][ 2 ] ) + " " + ;
str( str1_[ nCounter ][ 3 ], 2 ) + " " + ;
transform( str1_[ nCounter ][ 4 ], "@Z 9" )
endif
cTemp += space( 5 )
// Supposed Field Info
// -------------------
if nCounter > len( str2_ ) // Blanks
cTemp += space( 28 )
else
cTemp += padR( str2_[ nCounter ][ 1 ], 10 ) + " " + ;
fieldType( str2_[ nCounter ][ 2 ] ) + " " + ;
str( str2_[ nCounter ][ 3 ], 2 ) + " " + ;
transform( str2_[ nCounter ][ 4 ], "@Z 9" )
endif
aAdd( cErrorText_, cTemp )
next
aAdd( cErrorText_, "" )
endif
return lOk
static function fieldType( cFieldType )
if cFieldType == "C"
cFieldType := "Character"
elseif cFieldType == "D"
cFieldType := "Date "
elseif cFieldType == "N"
cFieldType := "Numeric "
elseif cFieldType == "L"
cFieldType := "Logical "
elseif cFieldType == "M"
cFieldType := "Memo "
endif
return cFieldType
static function fixStruct( cTable, aStruct )
// Backup first !!
// ---------------
dBoxFlip( "Sedang mem-BACKUP data, tunggu sebentar...", -1 )
select ( cTable )
copy to ( cTable + ".sav" )
dBoxClose()
// Create a temporary file with the correct structure
// --------------------------------------------------
dbCreate( "_" + cTable, aStruct )
use ( "_" + cTable ) new exclusive
append from ( cTable + ".sav" )
close ( "_" + cTable )
// Erase the file and rename the temporary !!
// ------------------------------------------
close ( cTable )
fErase( cTable + ".dbf" )
rename ( "_" + cTable + ".dbf" ) to ( cTable + ".dbf" )
return nil
Go Top
/*----------------------------------------------------------------------------+
¦ ¦
¦ Projects: dRIP ¦ dBMaster Receivable - Inventory - Payable ¦
¦ Designer: Hianoto Santoso ¦
¦ Author : Hianoto Santoso ¦
¦ Language: CA-Clipper 5.2x ¦
¦ ¦
¦ Function: setUpTable() ¦
¦ Purpose : The table initialization module ¦
¦ ¦
¦ History : ¦
¦ - 30-Dec-96...Write setUpTable() ¦
¦ ¦
¦ Copyright (C) 1992-98, dBMaster Software Development. ¦
¦ ¦
+----------------------------------------------------------------------------*/
#include "Datadict.ch"
function setUpTable()
local nError := 0
local lenTemp_ := {}
public lenFields_ := array( NUMCFG_FIELDS )
aFill( lenFields_, 0 )
// Master Tables
// -------------
lenGrpKode := 3
lenLokKode := 3
lenBrgKode := 15
lenBrgNama := 65
lenBrgSatuan := 6
lenBrgJumlah := 5
lenBrgDesimal := 0
lenBrgHarga := 8 + iif( useSen(), 3, 0 )
lenBrgBBM := 5
// Transaction tables
// ------------------
lenTxnSJ := 10
lenTxnNotes := 20
if isKaca()
lenTxnSJ := 13
lenBrgJumlah := 7
endif
if isDeptStore()
lenBrgKode := 8
lenBrgNama := 16
lenBrgJumlah := 9
lenBrgDesimal := 3
endif
// New feature: User-defined Field Length
if file( "dRIP.len" )
if iniModifyLength()
lenTemp_ := dArrayRest( "dRIP.len", @nError )
if nError == 0 .and. len( lenTemp_ ) == len( lenFields_ )
aCopy( lenTemp_, lenFields_ )
dBoxFlip( "Custom Field-Length Definition is loaded !!", .1 )
endif
endif
else
dArraySave( lenFields_, "dRIP.len", @nError )
endif
return nil
function useSen()
return isSparepart()
function setTableLen()
local oTB
local cPrompt_ := {}
local nPos := 1
local sColor := setColor( "W+/B, W+/N,,, W/N" )
// Prepare the variables
aAdd( cPrompt_, { "Kode Barang ", lenBrgKode } )
aAdd( cPrompt_, { "Nama Barang ", lenBrgNama } )
aAdd( cPrompt_, { "Satuan Barang ", lenBrgSatuan } )
aAdd( cPrompt_, { "Jumlah Barang (Qty) ", lenBrgJumlah } )
aAdd( cPrompt_, { "Desimal Barang (Qty) ", lenBrgDesimal } )
aAdd( cPrompt_, { "Harga Barang (Price) ", lenBrgHarga } )
aAdd( cPrompt_, { "No.BBM ", lenBrgBBM } )
aAdd( cPrompt_, { "Kode Group Barang ", lenGrpKode } )
aAdd( cPrompt_, { "Kode Lokasi ", lenLokKode } )
aAdd( cPrompt_, { "No.SJ ", lenTxnSJ } )
aAdd( cPrompt_, { "Keterangan Transaksi ", lenTxnNotes } )
// Create the dTBrowse()
oTB := dTblArray():new( 5, 22, dMaxRow()-4, maxCol()-23 )
oTB:setWndCoords( 4, 21, dMaxRow()-3, maxCol()-22 )
oTB:canAppend := FALSE
oTB:canDelete := FALSE
oTB:initDataSource( cPrompt_, { |x| iif( x == nil, nPos, nPos := x ) } )
oTB:addColumn( dColBase():new( "Keterangan", { || cPrompt_[ nPos ][ 1 ] } ) )
oTB:addColumn( dColBase():new( "Len", { || transform( cPrompt_[ nPos ][ 2 ], "@Z 999" ) } ) )
oTB:getColumn( 1 ):canEdit := FALSE
oTB:getColumn( 2 ):getPicture := "@Z 999"
oTB:getColumn( 2 ):getValid := { |o| val( o:varGet() ) > 0 }
oTB:getColumn( 2 ):getSave := { |n,o| cPrompt_[ nPos ][ 2 ] := val( n ) }
oTB:colPos := 2
oTB:readModal()
// Save results
dBeep()
if dAlert( "Confirm: Do you want to CHANGE the STRUCTURE ??", { " Yes ", " No " } ) == 1
dBoxFlip( "Converting to new structures, wait a moment...", -1 )
nPos := 1
lenBrgKode := cPrompt_[ nPos++ ][ 2 ]
lenBrgNama := cPrompt_[ nPos++ ][ 2 ]
lenBrgSatuan := cPrompt_[ nPos++ ][ 2 ]
lenBrgJumlah := cPrompt_[ nPos++ ][ 2 ]
lenBrgDesimal := cPrompt_[ nPos++ ][ 2 ]
lenBrgHarga := cPrompt_[ nPos++ ][ 2 ]
lenBrgBBM := cPrompt_[ nPos++ ][ 2 ]
lenGrpKode := cPrompt_[ nPos++ ][ 2 ]
lenLokKode := cPrompt_[ nPos++ ][ 2 ]
lenTxnSJ := cPrompt_[ nPos++ ][ 2 ]
lenTxnNotes := cPrompt_[ nPos++ ][ 2 ]
close box nozoom
dArraySave( lenFields_, "dRIP.len", @nPos )
chkStruct( TRUE )
initStruct()
endif
// Restore environment
setColor( sColor )
return nil
Go Top