Copying a table structure

[AndrewMozley]

What is the best way of copying the structure of Table1 - which is a part of dbc1 - into a different folder so that it exists there as Table1.dbf, part of dbc2, with all its captions, input masks &c.

I seem to be making heavy weather of this. COPY STRUCTURE EXTENDED can be used to copy the main features, including long names, but at present I am having to process the original and new tables, using DBGETPROP(), DBSETPROP().

Is there a better way?

Thanks. Andrew

 

[Olaf Doschke]

What are you trying to accomplish overall? Copy a DBC empty?
Use GENDBC(X) or simply cope files.
You can start copying the DBC/DCT/DCX files 1:1 into the new root folder, that copies all the advanced properties stored in the DBC, as it simple copies the DBC itself.
Then USE dbfs of the original DBC, COPY TO newpath/same.dbf DATABASE newdbc WITH CDX FOR .F. to only create empty DBFs.

To only reproduce a singele table use AFIELDS rather than COPY STRU, the afield array contains all details, too, including default values, table and field rules. As they can surely call into DBC stored procs it boils down to making a copy of the DBC/DCX/DCT anyway, you can finally DROP any tables you don't want in the new DBC.

Bye, Olaf.

 

[AndrewMozley]

Thank you Olaf

I want to add a new table to Database dbc2, (which already exists, with other tables in it). And I want to add this table with all its captions, input masks &c, as they were in the database from which it came,

AFIELDS() does indeed give information about the fields, including the long field name, from the original , but I do not believe that it contains properties such as the caption and input mask.

So at present I am using DBGETPROP() and DBSETPROP() to set these properties for the new (empty) table. But if there is a better way of doing this, I would like to know.

 

[GriffMG]

If you want caption and input mask (specifically) there does not seem to be an alternative to using the DBGETPROP() and DBSETPROP() functions,
I would think you could create quite a tidy wrapper to make your new table, but you might experience difficulties with triggers and validation
if they rely on other tables or functions existing in your second database.

You didn't specify them though... so it would probably be ok

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

OK, that part is not included, but nevertheless look at CREATE TABLE FROM ARRAY, the necessary array info is given by AFIELDS.

Some properties need to be copied over, if the situation includes a dbc2 with other tables in it. GENDBC will be helpful in creating the necessary code, as it also includes adding stored proc, referential integrity rules, comments and in short everything about a DBC and its DBFS, despite table paths in subdirectories. I don't know if GENDBCX cares about that, anyway you can also pick out just the part you want about single tables, the generated code is organized in a procedure per table.

Bye, Olaf.

 

[GriffMG]

I don't think he can be realistically interested in referential integrity Olaf - it can't be done because there is no way
for the table to be linked to any of the tables in the source - the unique IDs for any linked tables cannot be guaranteed
to match.

Andrew, are you doing this as some kind of back-up?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

I'm just stating GENDBC covers everything, you can still pick out the code you want. Remember, Griff, GENDBC does not copy a DBC directly, it generates code to create the same DBC, That code is its result and you are free to use it 1:1 as is or only part of it.

Bye, Olaf.

 

[AndrewMozley]

Thanks very much Griff; Grateful for your guidance.

(you are right : in this case there were not any related tables or external functions)

At least I know that I’m not wasting my time - had just wondered whether there might have been a special command in VFP9¾ which said :

COPY TABLE <pathname> INTO FOLDER <foldername> AS <filename>  IN <databasename> [WITH PROPERTIES] [ZAP].

. . but I couldn’t find it in the Help file!

I will follow your suggestion and package it up as a function (is that what you meant by a wrapper]

Andrew M.

 

[GriffMG]

Yes that's what I meant as a wrapper, one call to copy_table() rather than thirty lines of code.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

You're not paying good attention, while there is no single solution to copy a table without data already works with AFIELDS, COPY STRU and COPY TO ... FOR .F.

Here's code GENDBC produces for example for the northwind!customers table:

FUNCTION MakeTable_CUSTOMERS
***** Table setup for CUSTOMERS *****
CREATE TABLE 'CUSTOMERS.DBF' NAME 'CUSTOMERS' (CUSTOMERID C(5) NOT NULL, ;
                        COMPANYNAME C(40) NOT NULL, ;
                        CONTACTNAME C(30) NULL, ;
                        CONTACTTITLE C(30) NULL, ;
                        ADDRESS C(60) NULL, ;
                        CITY C(15) NULL, ;
                        REGION C(15) NULL, ;
                        POSTALCODE C(10) NULL, ;
                        COUNTRY C(15) NULL, ;
                        PHONE C(24) NULL, ;
                        FAX C(24) NULL)

***** Create each index for CUSTOMERS *****
ALTER TABLE 'CUSTOMERS' ADD PRIMARY KEY CUSTOMERID TAG CUSTOMERID COLLATE 'MACHINE'
INDEX ON UPPER(COMPANYNAME) TAG COMPANYNAM COLLATE 'MACHINE'
INDEX ON UPPER(CITY) TAG CITY COLLATE 'MACHINE'
INDEX ON UPPER(POSTALCODE) TAG POSTALCODE COLLATE 'MACHINE'
INDEX ON UPPER(REGION) TAG REGION COLLATE 'MACHINE'

***** Change properties for CUSTOMERS *****
DBSETPROP('CUSTOMERS.CUSTOMERID', 'Field', 'Caption', "Customer ID")
DBSETPROP('CUSTOMERS.CUSTOMERID', 'Field', 'Comment', "Unique five-character code based on customer name.")
DBSETPROP('CUSTOMERS.CUSTOMERID', 'Field', 'InputMask', "XXXXX")
DBSETPROP('CUSTOMERS.CUSTOMERID', 'Field', 'Format', "!A")
DBSETPROP('CUSTOMERS.COMPANYNAME', 'Field', 'Caption', "Company Name")
DBSETPROP('CUSTOMERS.CONTACTNAME', 'Field', 'Caption', "Contact Name")
DBSETPROP('CUSTOMERS.CONTACTTITLE', 'Field', 'Caption', "Contact Title")
DBSETPROP('CUSTOMERS.ADDRESS', 'Field', 'Caption', "Address")
DBSETPROP('CUSTOMERS.ADDRESS', 'Field', 'Comment', "Street or post-office box.")
DBSETPROP('CUSTOMERS.CITY', 'Field', 'Caption', "City")
DBSETPROP('CUSTOMERS.REGION', 'Field', 'Caption', "Region")
DBSETPROP('CUSTOMERS.REGION', 'Field', 'Comment', "State or province.")
DBSETPROP('CUSTOMERS.POSTALCODE', 'Field', 'Caption', "Postal Code")
DBSETPROP('CUSTOMERS.COUNTRY', 'Field', 'Caption', "Country")
DBSETPROP('CUSTOMERS.PHONE', 'Field', 'Caption', "Phone")
DBSETPROP('CUSTOMERS.PHONE', 'Field', 'Comment', "Phone number includes country code or area code.")
DBSETPROP('CUSTOMERS.FAX', 'Field', 'Caption', "Fax")
DBSETPROP('CUSTOMERS.FAX', 'Field', 'Comment', "Phone number includes country code or area code.")
CREATE TRIGGER ON 'CUSTOMERS' FOR DELETE AS __ri_delete_customers()
CREATE TRIGGER ON 'CUSTOMERS' FOR UPDATE AS __ri_update_customers()
ENDFUNC

It's obviously not generically usable for any table, but you at least see what commands and functions to use to have a complete4 copy.

You neither need the CREATE TABLE nor INDEXes if you start off with [tt]COPY TO DATABASE target WITH CDX FOR .F.[/tt], that copies all fields, and indexes, what remains mainly is the DBSETPROPs. The missing DBSETPROPs in above code sample are about features the CUSTOMERS table does not have, eg CUSTOMERS hasn't got a table comment, nor table of field rules. If you want to see necessary code to generate these just prepare a table having maxed out features and GENDBC will handle it.

And to see how GENDBC retrieves the info, that enables it to generate that code, you can look into the GenDBC code itself. I's in Home(')+"\Tools\Gendbc\gendbc.prg". It's clear you get values you need for DBSETPROP via DBGETPROP, so the only really hard problem remaining is how to find out which triggers are set. If you don't want to copy them, that's not necessary to know, but then you find that in DBGETPROP, too: DBGETPROP("Customers","TABLE","DeleteTrigger") for example retrieves what needs to be put after [tt]CREATE TRIGGER ON 'CUSTOMERS' FOR DELETE AS[/tt], notice: The inverse DBSETPROP() of "DeleteTrigger" (or others) isn't possible.

What helps to build up all the DBSETPROPS for each field of course is either AFIELDS field array info or FIELD(N) function, but AFIELDS also will include the Trigger expressions in the first array row, so it's more helpful.

In the simplest overall case you work on the GENDBC code and extract the part of it copying all the table properites you want copied. You can, but not need to simplify it, and you are even allowed to use and redistribute gendbc code as is or in parts (here I mean gendbc code itself, not only code it generates).

Bye, Olaf.

 

[GriffMG]

There is (as Olaf says above)

COPY TO FileName [DATABASE DatabaseName [NAME LongTableName]]
   [FIELDS FieldList | FIELDS LIKE Skeleton | FIELDS EXCEPT Skeleton]
   [Scope] [FOR lExpression1] [WHILE lExpression2] 
   [ [WITH] CDX ] | [ [WITH] PRODUCTION ] [NOOPTIMIZE] 
   [ [TYPE] [ FOXPLUS | FOX2X | DIF | MOD | SDF | SYLK | WK1 | WKS | WR1
   | WRK | CSV | XLS | XL5 | DELIMITED [ WITH Delimiter | WITH BLANK 
   | WITH TAB | WITH CHARACTER Delimiter ] ] ] [AS nCodePage]

In the help file but that does not copy captions
I used this to test it:

COPY TO c:\MyTable DATABASE MyDatabase NAME MyLongTableName WITH production FOR type="XX"

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Yes, we've already been through the thought there is no single solution covering everything. COPY TO copies both fields and indexes, CREATE TABLE FROM ARRAY does not as AFIELD does not retrieve info on indexes, it only has one row per field and that could, if at all, only contain the info of simple tags on the one field, ATAGINFO() would help on that, but you can't CREATE TABLE on the basis of two tables. To complete the set of incompleteness COPY FILE also won't work, as the DBC needs some info about the new table and not just a file copy, besides of course this will copy dbf including data.

Anyway, COPY TO gets you further than any other and the rest necessary to copy over captions etc is less code than starting off with any other option.

Gendbc.prg shows how to get any info you need and how to generate code from that. Instead of generating you can also always execute it, be it via macro expansion, so it has everything you need. You can also use its code and let it generate the CREATE TABLE statement instead of using any copy mechanism we already discussed.

In my last post I was only concentrating on the proposed option "ZAP" of a non existing COPY TABLE command. That option to not copy over data exists in the form of making use of the FOR clause as FOR .F., you simply filter FOR .F., meaning no record fulfills the filter, so COPY TO FOR .F. copies the empty structure. And the rest you need to add is less than any other way, even compared to GENDBCs generated code, as COPY TO already covers the CREATE TABLE statement AND all the INDEX commands. The necessary rest then only is about comments, rules, input masks, formats, and triggers, if you want.

Bye, Olaf.

 

[AndrewMozley]

Thank you Olaf for your notes and research

And thank you Griff. I have built on your example and added the table as a two stage process.. First

   USE (<template filename>)
COPY TO (<data filename>) DATABASE (<Database name>)  WITH CDX FOR .F.

In this case COPY TO is preferred to COPY STRUCTURE EXTENDED or AFIELDS(), because it creates the CDX file, if it is present; In the present case I don’t really need the “FOR .F.” statement, because the template file is itself empty – but I have left it in.

Then, the properties are copied using DBGETPROP() and DBSETPROP(). For this purpose the Template database (ldbc1) and the live data database (ldbc2) are opened, and the structure of the table is copied using COPY STRUCTURE EXTENDED (it could have used AFIELDS), and scanned to transfer the additional table properties from the template to the live data table. The resultant code is something like this.

    LOCAL lDbc1, lDbc2, lcName, lField, lCaption, lMask, lComment
   *  The template table is open and selected)
   . . .
   COPY STRUCTURE EXTENDED TO TempStruct
   OPEN DATABASE (lDBC1)
   OPEN DATABASE (lDBC2) 
   USE (lDestFile) IN 0 ALIAS Destfile
   USE TempStruct IN 0
   SELECT TempStruct
   SCAN
      lField = RTRIM(TempStruct.field_name)
      lcName = lFileName + "." + lField
      SET DATABASE TO (lDBC1)
      lCaption = DBGETPROP(lcName,"Field","Caption")
      lMask = DBGETPROP(lcName,"Field","InputMask")
      lComment = DBGETPROP(lcName,"Field","Comment")
      SET DATABASE TO (lDBC2)
      DBSETPROP(lcName,"Field","Caption", lCaption)
      DBSETPROP(lcName,"Field","InputMask", lMask)
      DBSETPROP(lcName,"Field","Comment", lComment)
      ENDSCAN
      . . .

There are other properties which could be copied.

Again, thank you for your help.

 

[GriffMG]

That looks like it's nicely packaged.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Why do you say you use COPY TO and then use COPY STRUCTURE EXTENDED instead? For what reason?
You can use the original table and iterate this way:

FOR i= 1 TO FCOUNT()
   lcName = lFileName + "." + FIELD(i)
...
ENDFOR

Your code isn't packaged well, some things still "fall from the sky", eg the lFilename variable is neither declared nor set before you use it. lDbc1, lDbc2 are declared as LOCAL, but you skip how you set them.

Tr to really make a function of it, the information passed in should only be
a) The target DBC (pass in DBC() while having selected the target DBC as active database)
b) the name of the table to copy.

You can determine the file name of the currently open table with DBF() and the alias name it has as ALIAS(), but both not generally give you the table name within the database. If you work inversely and make that table name a parameter of your function USE (lcTablename) will open that table, whatever its file name is.

You may also work on the condition tablename=alias, and make that another prerequisite, most developers use this as a soft rule but indeed you can CREATE TABLE xyz.dbf NAME abc and have a name of the DBC table object differing from the dbf file name.

Bye, Olaf.

 

[AndrewMozley]

Thank you Griff. It's good to have your approval and indeed guidance.

 

[GriffMG]

Hi Olaf,

I assumed Andrew was just giving indicative code - probably trimmed to hide details, and I meant it was good to do something tidy and easily understood, not that it needed to be rigorously tested for on here.

This isn't a FAQ really, just an approach and a solution.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

It could become an FAQ, though, Griff.

It's not all that important, Andrew, as long as you're happy and can use what you have, but this also really is something universal enough to be of help to anyone.

Bye, Olaf.

 

[GriffMG]

This would be the basis for a good FAQ you are not wrong there Olaf

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​