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
Here is the source code of DataDict.ch:
/*----------------------------------------------------------------------------+ ¦ ¦ ¦ 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 ]
Here is the source code of DataDict.prg:
/*----------------------------------------------------------------------------+ ¦ ¦ ¦ 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 TAGUSING IN => ; incProgress( "Index " + <(cWorkArea)> + cOrdBagExt + " -» " + <(cKey)> );; if ->( ordNumber( <(cTag)> ) ) == 0;; ->( dbCloseArea() );; dNetUse( <(cWorkArea)>, TRUE,,, 0 );; index on tag ;; ->( 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 WITH INDEX => ; if dNetUse( <(cDBF)>, TRUE );; dMessage( "Sedang melakukan PACK untuk " + <(cDBF)> + ".dbf, tunggu sebentar..." );; pack;; close ;; 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 IN => ; if ! chkStruct2( , ->( dbStruct() ), <(cTable)>, @cErrorText_ );; if lForce .or. dDispText( cErrorText_, "Error Structure in Table", { " Perbaiki ", " Batal " }, "W+/R" ) == 1;; fixStruct( <(cTable)>, );; 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
Here is the source code of SetupTbl.prg:
/*----------------------------------------------------------------------------+ ¦ ¦ ¦ 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