none
How to add New Table Structure to Database RRS feed

  • Question

  • Hey guys/gals, this one is harder than it seems.  You'll have to read slowly and thoroughly to get the REAL QUESTION.

    I have a VFP 9 app to be compiled into EXE. EXE has been running successfully for years. I recently have to make some structure changes to one of the main tables (adding more than 20 fields etc.). In development (interactive mode) I have no problem getting program to work.

    The problem(s) are :

    1. How do I get the structure changes back into my Database (programmatically)?
        -- The DBC wants to report an Invalid Database when I use OS to copy new structured file over older dbf.
        -- Remember, the client has the old dbf and dbc files on their computer
        -- Can't figure out how to change both these items

    2. I don't recall ever using (knowingly) the DBC container purposely in the first place, I always liked FREE TABLES.
        So why would my development be putting my stuff in a DBC if I didn't ask for it to do so?

    3. How can I turn the DBC off, I tried "close database all" and function DBC() reports blank, however I keep running
        into problems with the change of Table structure?  It wants to continue to compare it to something old!!!

    Please help if you have a valid solution to this tricky problem.

       

    Monday, January 28, 2019 4:04 AM

Answers

  • Look at the most general situation, not about files at all, any database with tables.

    Upgrading it doesn't mean overwriting files, but doing ALTER TABLE statements. Because:

    a) Users have data they want to keep, when a structure changes, you can't just simnply overwrite exising table files.

    b) Databases are usually not files like DBC and DBF (plus more)

    That problem also hits you with free tables. So that's really not a DBC problem. You can't just overwrite. You don't seem to have thought that out very far, if you still think free DBFs are much easier to handle.

    Now you need to know a few details about DBC and DBF and what the ALTER TABLE does, Even if your data is read only You can't just overwrite DBF and get no errors, as the DBC contains some meta data about the DBF structure too, like long field names, and that's not only the case if field names are longer than a free dbf allows. All field names of a DBF are stored in the DBC, also the short ones and that's not the only info.

    In a read only data case you can, in fact, simply overwrite everything.

    I don't know where problem of first needing to close the database comes from, a database update is either done as a separate process or embedded into the application EXE at its start before even opening the DBC, so there won't be any problems of already open data. That's not a general problem, only your problem of handling this wrong.

    In the simplest cases you do some alter table statements and both DBC and DBFs are modified accordingly, you end up the same way. In some sense unsatisfactorial, as the DBC/DCX/DCT may not end up binary 1:1 as the development files.

    So another strategy is as simple as creating a temp dir, put in new DBC and empty DBF files and append all current data into it. It's not even slower than ALTER TABLE, as ALTER TABLE does not modify existing DBF files, it creates a new temp file, appends data and renames the old to BAK TBK and some other file extensions and the temp file to the old names, so it just looks as if the existing files were modified. Just the same as PACK does, creating the same structured dbf and appending all records without deletion mark.

    You can waste a lot of time in the strategy to start out with an empty new DBC/DBFs folder, as you also copy over unaltered data with APPEND into an empty DBF, where the same files would indeed work just copied or faster moved into the new folder. There's a con against move, that you then can't revert to the old valid database state, if anything goes wrong out of even simple reasons of insufficient disk space or power outage. Well, you hopefully could revert to a backup, but when was the last made? So you better even include a backup as start of the whole upgrade process anyway.

    What you didn't encounter yet, you also have a problem with view definitions when you only bring in new DBF files, append data to them and don't change the DBC data about views, even a simple SELECT * view stores the single fields with all other necessary per field meta data and that won't match a changed DBF.

    The best pro argument for doing your database upgrade without an SQL Script is you can't miss a thing, the customer has the binary exactly matching DBC, obviously not the case for the DBF data, but the headers will be 1:1, too.

    There are some catches, like autoinc integers, referential integrity rules firing at APPEND FROM old dbf which aren't caused by ALTER TABLE, but it's not that hard to distribute DBFs with normal integer fields turned to autoinc in a last step and also implement a removal of all insert triggers and restore them or have an intermediatre DBC without procedures, which are appended as last step.

    There's GENDBC to create a new empty DBC of your development database, but of course you can also copy all database files and iterate the tables with the help of ADBOBJECTS() and zap them to have that upgrade temp DBC. And there is no tool, at least no native one, to give you necessary ALTER TABLE scripts. Edit: Tamar mentions Stonefield Database Toolkit. The version should of course support all the features you need supported, like autoinc ints. I know they embed DBC events, which even act automatically with view definition changes when you alter a table, no matter if with ALTER TABLE or in the table designer, both changes go through database events.

    In the end, if you'd upgrade a directory of free DBFs, you'd also need to do that with empty DBFs you use to append the customers state of data into, the DBC doesn't change much in that way of upgrading a database except autoinc (same problem with free dbfs, though) and suppressing triggers firing during append of data.

    Edit2: Overall in the RDBMS world database upgrades are a special topic and SQL Server has nice (enough) SDDT and concepts like dacpac, bacpak and database .NET projects generating update scripts. You could in principle use the same trickery and sometimes the table designer of SSMS also creates a table with different name, inserts all previous data, regenerates indexes and drops the old and renames the new table to avoid problems of constraints. So this isn't a DBC problem, this is a problem of any database a little more complex, but as already said you also don't just overwrite free tables, if they contain data not being part of your initial database but data saved by the user, you at least need to append that in some way into a new structure.

    Bye, Olaf.

    Monday, January 28, 2019 6:21 AM
  • Only my opinion (I know many will disagree):

    DBCs have advantages, but I've found programming life to be much simpler (easier?) without them, unless absolutely required.

    Generally, if you agree and have access to the code, you could create (or recreate) DBFs with the new fields and append your data as needed. You may need to shorten some of your field names to 10 characters (max).

    If you don't have code access or if code is too burdensome, perhaps you could write a separate PRG to modify the DBFs and DBC appropriately.

    Steve

    Monday, January 28, 2019 1:14 PM
  • Whether a DBC is involved or not, my practice is that when I'm making changes to an application that involve data structure changes, at the same time, I create a program to apply those changes to the live data. I deliver to the client the new EXE for the application and a separate EXE that must be run first to update the data.

    If you're using Stonefield Database Toolkit, it can handle this for you in most cases and you only have to supply the updated metadata with the updated application.

    Tamar

    Monday, January 28, 2019 9:35 PM
    Moderator

All replies

  • Look at the most general situation, not about files at all, any database with tables.

    Upgrading it doesn't mean overwriting files, but doing ALTER TABLE statements. Because:

    a) Users have data they want to keep, when a structure changes, you can't just simnply overwrite exising table files.

    b) Databases are usually not files like DBC and DBF (plus more)

    That problem also hits you with free tables. So that's really not a DBC problem. You can't just overwrite. You don't seem to have thought that out very far, if you still think free DBFs are much easier to handle.

    Now you need to know a few details about DBC and DBF and what the ALTER TABLE does, Even if your data is read only You can't just overwrite DBF and get no errors, as the DBC contains some meta data about the DBF structure too, like long field names, and that's not only the case if field names are longer than a free dbf allows. All field names of a DBF are stored in the DBC, also the short ones and that's not the only info.

    In a read only data case you can, in fact, simply overwrite everything.

    I don't know where problem of first needing to close the database comes from, a database update is either done as a separate process or embedded into the application EXE at its start before even opening the DBC, so there won't be any problems of already open data. That's not a general problem, only your problem of handling this wrong.

    In the simplest cases you do some alter table statements and both DBC and DBFs are modified accordingly, you end up the same way. In some sense unsatisfactorial, as the DBC/DCX/DCT may not end up binary 1:1 as the development files.

    So another strategy is as simple as creating a temp dir, put in new DBC and empty DBF files and append all current data into it. It's not even slower than ALTER TABLE, as ALTER TABLE does not modify existing DBF files, it creates a new temp file, appends data and renames the old to BAK TBK and some other file extensions and the temp file to the old names, so it just looks as if the existing files were modified. Just the same as PACK does, creating the same structured dbf and appending all records without deletion mark.

    You can waste a lot of time in the strategy to start out with an empty new DBC/DBFs folder, as you also copy over unaltered data with APPEND into an empty DBF, where the same files would indeed work just copied or faster moved into the new folder. There's a con against move, that you then can't revert to the old valid database state, if anything goes wrong out of even simple reasons of insufficient disk space or power outage. Well, you hopefully could revert to a backup, but when was the last made? So you better even include a backup as start of the whole upgrade process anyway.

    What you didn't encounter yet, you also have a problem with view definitions when you only bring in new DBF files, append data to them and don't change the DBC data about views, even a simple SELECT * view stores the single fields with all other necessary per field meta data and that won't match a changed DBF.

    The best pro argument for doing your database upgrade without an SQL Script is you can't miss a thing, the customer has the binary exactly matching DBC, obviously not the case for the DBF data, but the headers will be 1:1, too.

    There are some catches, like autoinc integers, referential integrity rules firing at APPEND FROM old dbf which aren't caused by ALTER TABLE, but it's not that hard to distribute DBFs with normal integer fields turned to autoinc in a last step and also implement a removal of all insert triggers and restore them or have an intermediatre DBC without procedures, which are appended as last step.

    There's GENDBC to create a new empty DBC of your development database, but of course you can also copy all database files and iterate the tables with the help of ADBOBJECTS() and zap them to have that upgrade temp DBC. And there is no tool, at least no native one, to give you necessary ALTER TABLE scripts. Edit: Tamar mentions Stonefield Database Toolkit. The version should of course support all the features you need supported, like autoinc ints. I know they embed DBC events, which even act automatically with view definition changes when you alter a table, no matter if with ALTER TABLE or in the table designer, both changes go through database events.

    In the end, if you'd upgrade a directory of free DBFs, you'd also need to do that with empty DBFs you use to append the customers state of data into, the DBC doesn't change much in that way of upgrading a database except autoinc (same problem with free dbfs, though) and suppressing triggers firing during append of data.

    Edit2: Overall in the RDBMS world database upgrades are a special topic and SQL Server has nice (enough) SDDT and concepts like dacpac, bacpak and database .NET projects generating update scripts. You could in principle use the same trickery and sometimes the table designer of SSMS also creates a table with different name, inserts all previous data, regenerates indexes and drops the old and renames the new table to avoid problems of constraints. So this isn't a DBC problem, this is a problem of any database a little more complex, but as already said you also don't just overwrite free tables, if they contain data not being part of your initial database but data saved by the user, you at least need to append that in some way into a new structure.

    Bye, Olaf.

    Monday, January 28, 2019 6:21 AM
  • Only my opinion (I know many will disagree):

    DBCs have advantages, but I've found programming life to be much simpler (easier?) without them, unless absolutely required.

    Generally, if you agree and have access to the code, you could create (or recreate) DBFs with the new fields and append your data as needed. You may need to shorten some of your field names to 10 characters (max).

    If you don't have code access or if code is too burdensome, perhaps you could write a separate PRG to modify the DBFs and DBC appropriately.

    Steve

    Monday, January 28, 2019 1:14 PM
  • Whether a DBC is involved or not, my practice is that when I'm making changes to an application that involve data structure changes, at the same time, I create a program to apply those changes to the live data. I deliver to the client the new EXE for the application and a separate EXE that must be run first to update the data.

    If you're using Stonefield Database Toolkit, it can handle this for you in most cases and you only have to supply the updated metadata with the updated application.

    Tamar

    Monday, January 28, 2019 9:35 PM
    Moderator
  • Tamar,

    I usually update the main exe by adding a method which makes the changes, like
    IF <not new version>
     <run added method to  make the changes>
     <update version date/number>
    ENDIF

    This method does make the exe bigger (unnecessary bloat?) and possibly unattachable to an email. I like your method better.

    Steve
    Tuesday, January 29, 2019 2:30 AM