Copy structure or create new file dbf

[Silvio.Falconi]

to facilitate viewing, since I have too large an archive I decided to create a configuration to create another smaller archive

maybe if I had a smaller database the procedures could work better and faster

i tried inTdatabase

oLotto:CopyTo( cDir+"Integrale")
oLotto:CopyTo( cDir+"Virtuale")

but this makes a physical copy of the dbf, instead I wanted to create at least for the "Virtual" archive only the structure and then through a filtering system insert the selected records in the new archive

How could I do ?

Code: Select all  Expand view

If nTipo=2
    oDbf :=TDatabase():Open( , cDir+"Virtuale", "DBFCDX", .T. )
    oDbf:setorder(1)
    oDbf:Exec( <||
     SET FILTER TO   (  dFirst <= FIELD->DATA .AND.             ;
                        dLast >=  FIELD->DATA )
          return nil
      > )
    oDbf:gobottom()

 else
     oDbf :=TDatabase():Open( , cDir+"Integrale", "DBFCDX", .T. )
     oDbf:setorder(1)
     oDbf:gobottom()
  Endif

If I use
oDbf:Exec( <||
SET FILTER TO ( dFirst <= FIELD->DATA .AND. ;
dLast >= FIELD->DATA )
return nil
> )


then something not run

that's why I thought about creating a new archive by filtering from the full archive

[nageswaragunupudi]

Code: Select all  Expand view

USE SOURCEDBF ....
COPY TO NEWDBF FOR <filtercond>

[James Bott]

Using an index with a scope is much faster.

You create the index once when the database is first used, then you automatically open the index whenever the database object is opened. Then you set the scope and then the low and high values you want to see using oDB:setScope(xLow,xHigh). The only records read will be those you want.

I tested a 1 million record DBF file using a scope to filter out about 20,000 records and it takes less than a second! Using a filter instead takes 23 seconds.

[Marc Venken]

James, This is what you mean ? or a other way ?
Best to have the index set to the index of the field ?

Code: Select all  Expand view

//   oBrw[2]:bChange    := { || Scope("factinfo","document","factart","factart"),oBrw22:refresh() }

function Scope(cParent,cData,cChild,cChildTag)
  LOCAL cZoekdata:= alltrim(upper( (cParent)->&cData))

  (cChild)->(dbsetorder(cChildTag))

  (cChild)->(ORDSCOPE(0, "" ) )  // these 2 lines seems to be best used to clear the scope ?  Is this still needed ?
  (cChild)->(ORDSCOPE(1, "" ) )

  (cChild)->( ORDSCOPE(0, cZoekData ) )
  (cChild)->(ORDSCOPE(1, cZoekData ) )
  (cChild)->(DBGOTOP())

Return NIL
 

[Jimmy]

hi Silvio,

are you sure that you want Dates EXECPT "Filter"

Code: Select all  Expand view

  dFirst <= FIELD->DATA .AND. dLast >= FIELD->DATA

that only work for

Code: Select all  Expand view

  dFirst = FIELD->DATA = dLast

"normal" i want Dates between "Filter"-Top / -End

Code: Select all  Expand view

  dFirst >= FIELD->DATA .AND. dLast <= FIELD->DATA

---

Code: Select all  Expand view

   USE XXX
   COPY STRUCTURE [FIELDS <cFieldname,...>] TO <cFilename>

to create a EMPTY() new DBF

[Silvio.Falconi]

YesterDay I made as I made on Clipper

Before i create two Database
Create_VirtualeDb(cdir)
Create_IntegraleDb(cDir)

then

oLotto:=TDatabase():Open( , cDir+"lotto", "DBFCDX", .T. )
oLotto:setorder(1)
oLotto:Exec( <||
SET FILTER TO ( dFirst <= FIELD->DATA .AND. ;
dLast >= FIELD->DATA .AND. ;
aCountMesi[ MONTH( FIELD->DATA ) ] .AND. ;
aCountGiorni[ DOW( FIELD->DATA ) ] .AND. ;
aCountDate[ DAY( FIELD->DATA ) ] )
return nil
> )

oLotto:gobottom()

If nTipo=2

atempDbf:= oLotto:DbfToArray()
oVirtuale:=TDatabase():Open( , cDir+"Virtuale", "DBFCDX", .T. )
oVirtuale:SetOrder( 0 )
oVirtuale:ArrayToDBF( atempDbf, , nil, .t., .t. )
oVirtuale:close()
else
oLotto:gobottom()
atempDbf:= oLotto:DbfToArray()
oIntegrale:=TDatabase():Open( , cDir+"Integrale", "DBFCDX", .T. )
oIntegrale:SetOrder( 0 )
oIntegrale:ArrayToDBF( atempDbf, , nil, .t., .t. )
oIntegrale:close()

Endif
oLotto:close()


For Now it seems to run ok

[Marc Venken]

Silvio,

You have it running... That's mostly the goal ....

But did you try the scope ? I would think it should be faster and less code. A new second filtering (maybe not needed) would also be faster.

Mostly out of interest this question.

[Silvio.Falconi]

Silvio,

You have it running... That's mostly the goal ....

But did you try the scope ? I would think it should be faster and less code. A new second filtering (maybe not needed) would also be faster.

Mostly out of interest this question.

I don't really trust the ...scope
then Nages told me to use the filter
also because I have to filter many elements
- the months
- the days of the week
- the numbers of the month
- the monthly indices (which I have not entered for now)

all these data are stored in arrays with .t. or with .f.

so I tried as Nages suggested

Code: Select all  Expand view

oLotto:Exec( <||
     SET FILTER TO   (  dFirst <= FIELD->DATA .AND.             ;
                        dLast >=  FIELD->DATA .AND.             ;
                        aCountMesi[ MONTH( FIELD->DATA ) ]  .AND. ;
                        aCountGiorni[ DOW( FIELD->DATA ) ] .AND. ;
                        aCountDate[ DAY( FIELD->DATA ) ]   )
      return nil
      > )

it seems to be working fine for now

[James Bott]

Marc,

James, This is what you mean ? or a other way ?
Best to have the index set to the index of the field ?

Yes, but I would do this:

Save the current recno()

If they exist, save the database's state (current index and the top and bottom scopes and the current recordNo()).

set the index and scopes you need to use.

Do a gotop().

Do whatever...

Then at the end of the routine, restore the database's state (the old index and recno() and the old scopes (if they exist).

Another option is to open a new copy of the needed database within your routine. Then you don't have to worry about saving and restoring the database's state. But you do need to close the local copy of the database at the end of your routine.