Converting subtables in v11

[Lee Hinde]

Hi;

Migrating a database that has subtables* to v11. As part of the conversion,
I de-subtable the subtable, breaking the auto-link to the parent table and
make all the requisite code changes. All the data survived the conversion
and decoupling.

Then, when I update with a fresh copy of the datafile (from 2004), the
subtables are decoupled, but the tables are empty.

This doesn't seem right.

Anyone else done this?

 - Lee

* the subtables were for keywords, ok. No laughing.
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[psmith-3]

Lee,

I would certainly suspect that if you break v11's way of identifying  
subtables subsequently in your v11 structure, 4D would have no way of  
knowing this and no way to fill the resulting tables when you convert  
a 2004 data file.

If you want to break the special link in v11and you will be converting  
several data files (such as with a commercial product), I would  
recommend getting rid of subtables first in 2004. That way, you  
wouldn't be relying on 4D's automatic conversion and its inherent  
limitations. An alternative would be to first convert all your users  
to the v11 structure and only then turn the "subtable" into a bonafide  
table.



Paul Smith

TSE International

> Hi;
>
> Migrating a database that has subtables* to v11. As part of the  
> conversion,
> I de-subtable the subtable, breaking the auto-link to the parent  
> table and
> make all the requisite code changes. All the data survived the  
> conversion
> and decoupling.
>
> Then, when I update with a fresh copy of the datafile (from 2004), the
> subtables are decoupled, but the tables are empty.
>
> This doesn't seem right.
>
> Anyone else done this?
>
> - Lee
>
> * the subtables were for keywords, ok. No laughing.

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Alan Chan]

Hi Lee,

This is normal. As Paul suggested, you could convert subtables in pre-v11
first. Or create 2 consecutive versions - say version A and B in v11.
Version A will have subtables and version B will have all subtables
replaced with related tables.

1) create a version (version A) of new structure - version a that would
let v11 do the conversion
2) Use the version a to continue for development like removing all
subtables, adding new features... etc.

During Deployment, what you need to do is:

1) Use version A to do the conversion
2) then use version B to open the data

Using the above approach, you don't need to create additional tables in
pre-v11 and don't need to write methods to copy data from subtables to
newly created tables.

Simply put, you have 2 versions - we named it as Conversion Version
(version A and this is not a working version) and the other as Deployment
Version (this is the final working version that your customers will be
using). We have been using the this approach in our testing phase. So far
so good.

Once you create and freeze the conversion version, you could freely add
new features, fixing compatibility issue, add new tables and fields,
delete tables and fields in the deployment version without fear of
compatibility with the Conversion version. Of course, once you started
working on the deployment version, no change should be done in the
original Conversion version. The best part of this approach is its minimum
downtime during the actual deployment.

I hope this help.

Alan chan

4D iNug Technical <4d_tech@...> writes:

>Hi;
>
>Migrating a database that has subtables* to v11. As part of the
>conversion,
>I de-subtable the subtable, breaking the auto-link to the parent table and
>make all the requisite code changes. All the data survived the conversion
>and decoupling.
>
>Then, when I update with a fresh copy of the datafile (from 2004), the
>subtables are decoupled, but the tables are empty.
>
>This doesn't seem right.
>
>Anyone else done this?
>
> - Lee
>
>* the subtables were for keywords, ok. No laughing.
>**********************************************************************
>Get the speed and power of 4D v11 SQL
>before upgrade prices increase - http://www.4d.com
>  
>
>4D Internet Users Group (4D iNUG)
>FAQ:  http://lists.4d.com/faqnug.html
>Archive:  http://lists.4D.com/archives.html
>Options: https://lists.4d.com/mailman/options/4d_tech
>Unsub:  mailto:4D_Tech-Unsubscribe@...
>**********************************************************************

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Conley]

This is a timely discussion for me as I am converting my OEM app from  
2004 to v11.

I am very concerned with this approach as I would expect many others  
with vertical market apps would be.  We have many hundreds of  
customers with various levels of computer expertise.  One big  
advantage we have is the ability to seamlessly upgrade from an older  
version of our app to the current version without any upgrade steps.

I have some legacy subtables that I too want to get rid of.  I want to  
create a new table, populate it with data from the subtables and then  
drop the old subtables.  What I am finding is that if I run this  
"upgrade" code in my development environment once and then do a build,  
I cannot run this on any customer's data as my deployment structure  
doesn't have the subtables anymore.  The only possible solution that I  
can see is to never let my development copy touch any upgrade code  
that changes the structure.  I don't like this idea because I'm sure  
that I will screw that up some day!  It would then seem that the only  
thing I can really do in the structure editor in development is things  
like we could do prior to v11 like add new tables and rename columns -  
but never drop tables or columns.

Another example of my dilemma would be if I wanted to take data in two  
tables and merge the data into a new table and then drop the original  
tables.  No problem if I just have a single data file to deal with,  
but how do I manage it in a vertical market app?  I can't change my  
development structure and deploy because the data from the two tables  
would be gone when my upgrade code tries to reference it to move it  
and drop the tables.

I need to find a way to keep the ability for my customers to upgrade  
from whatever older version of our app to the latest without having to  
do an upgrade, fire up the app to convert the data and then a final  
upgrade & fire up the app to convert the data.  Trust me when I say  
they won't like or understand the multi upgrade approach!

I think the thing that is killing me here is the tightly coupled  
nature of the 4D environment when it comes to DDL commands.
Surely others have this concern and maybe a solution to manage this?  
Perhaps I am way off and have nothing to worry about.  I could sure  
use some help understanding how to move forward.


Regards,

David Conley
By The Book, Inc.
815-234-7530
http://www.bythebook.com




On Oct 29, 2009, at 6:52 AM, Alan Chan wrote:

> Hi Lee,
>
> This is normal. As Paul suggested, you could convert subtables in  
> pre-v11
> first. Or create 2 consecutive versions - say version A and B in v11.
> Version A will have subtables and version B will have all subtables
> replaced with related tables.
>
> 1) create a version (version A) of new structure - version a that  
> would
> let v11 do the conversion
> 2) Use the version a to continue for development like removing all
> subtables, adding new features... etc.
>
> During Deployment, what you need to do is:
>
> 1) Use version A to do the conversion
> 2) then use version B to open the data
>
> Using the above approach, you don't need to create additional tables  
> in
> pre-v11 and don't need to write methods to copy data from subtables to
> newly created tables.
>
> Simply put, you have 2 versions - we named it as Conversion Version
> (version A and this is not a working version) and the other as  
> Deployment
> Version (this is the final working version that your customers will be
> using). We have been using the this approach in our testing phase.  
> So far
> so good.
>
> Once you create and freeze the conversion version, you could freely  
> add
> new features, fixing compatibility issue, add new tables and fields,
> delete tables and fields in the deployment version without fear of
> compatibility with the Conversion version. Of course, once you started
> working on the deployment version, no change should be done in the
> original Conversion version. The best part of this approach is its  
> minimum
> downtime during the actual deployment.
>
> I hope this help.
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Chip Scheide]

write the code that moves the data from 2 or more tables into one.

IN THE SAME CODE

once completed (verification etc completed first of course)

Drop the table(s) via code

you can probably do the same with the subtables
On Thu, 29 Oct 2009 15:23:12 -0500, david conley wrote:
>
> Another example of my dilemma would be if I wanted to take data in
> two tables and merge the data into a new table and then drop the
> original tables.  No problem if I just have a single data file to
> deal with, but how do I manage it in a vertical market app?  I can't
> change my development structure and deploy because the data from the
> two tables would be gone when my upgrade code tries to reference it
> to move it and drop the tables.
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Lee Hinde]

On Wed, Oct 28, 2009 at 11:08 PM, Lee Hinde <leehinde@...> wrote:

> Hi;
>
> Migrating a database that has subtables* to v11. As part of the conversion,
> I de-subtable the subtable, breaking the auto-link to the parent table and
> make all the requisite code changes. All the data survived the conversion
> and decoupling.
>
> Then, when I update with a fresh copy of the datafile (from 2004), the
> subtables are decoupled, but the tables are empty.
>
> This doesn't seem right.
>
> Anyone else done this?
>
>  - Lee
>
> * the subtables were for keywords, ok. No laughing.
>
>

Thanks for the confirmation everyone. I think I'll run an export routine in
2004 and just reimport into v11.
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[psmith-3]

David,

You make some good points which highlight the problems inherent in  
being able to make procedural changes to the database structure. I've  
thought about that problem too, but I've been too swamped with the  
process of upgrading several databases to v11 to work on long term  
solutions.

Developers have been asking for the ability to drop and create tables  
on the fly for years, yet 4D has been very hesitant in the past to  
address those requests. You problem underlines the reasons behind  
their reticence.

I think the problem requires some reflection, with possible solutions  
including (1) rendering "deleted" tables invisible but never deleting  
them or (2) managing some sort of meta data and methods which  
describes all the changes which must be carried out to the structure  
when new versions are deployed to users. In that case, you would  
certainly be sacrificing the simplicity associated with upgrades which  
has been the hallmark of 4D for years.


Paul Smith

TSE International

> This is a timely discussion for me as I am converting my OEM app  
> from 2004 to v11.
>
> I am very concerned with this approach as I would expect many others  
> with vertical market apps would be.  We have many hundreds of  
> customers with various levels of computer expertise.  One big  
> advantage we have is the ability to seamlessly upgrade from an older  
> version of our app to the current version without any upgrade steps.
>
> I have some legacy subtables that I too want to get rid of.  I want  
> to create a new table, populate it with data from the subtables and  
> then drop the old subtables.  What I am finding is that if I run  
> this "upgrade" code in my development environment once and then do a  
> build, I cannot run this on any customer's data as my deployment  
> structure doesn't have the subtables anymore.  The only possible  
> solution that I can see is to never let my development copy touch  
> any upgrade code that changes the structure.  I don't like this idea  
> because I'm sure that I will screw that up some day!  It would then  
> seem that the only thing I can really do in the structure editor in  
> development is things like we could do prior to v11 like add new  
> tables and rename columns - but never drop tables or columns.
>
> Another example of my dilemma would be if I wanted to take data in  
> two tables and merge the data into a new table and then drop the  
> original tables.  No problem if I just have a single data file to  
> deal with, but how do I manage it in a vertical market app?  I can't  
> change my development structure and deploy because the data from the  
> two tables would be gone when my upgrade code tries to reference it  
> to move it and drop the tables.
>
> I need to find a way to keep the ability for my customers to upgrade  
> from whatever older version of our app to the latest without having  
> to do an upgrade, fire up the app to convert the data and then a  
> final upgrade & fire up the app to convert the data.  Trust me when  
> I say they won't like or understand the multi upgrade approach!
>
> I think the thing that is killing me here is the tightly coupled  
> nature of the 4D environment when it comes to DDL commands.
> Surely others have this concern and maybe a solution to manage  
> this?  Perhaps I am way off and have nothing to worry about.  I  
> could sure use some help understanding how to move forward.
>
>
> Regards,
>
> David Conley
> By The Book, Inc.
> 815-234-7530
> http://www.bythebook.com

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Peter Jakobsson-2]

Hi David

I don't think you should be worried.

You have all the options available to you that existed before - just a  
few more. Clearly, you can't 'have your cake and eat it' as far as  
dropping tables goes, however I would just leave out the 'drop table'  
aspect of your upgrade process till a future upgrade (say a year down  
the line when you've got confidence in the new version and users are  
happy).

Also, you may consider this - if 4D's market share of the worldwide  
DBMS market is 0.2% (I have no clue what it is really), then that  
means that 99.8% of the worlds VM database applications have to deal  
with this problem when they send out upgrades - from Quickbooks to OS  
services.

Again, my suggestion would be to definitely not drop the subtables  
from your upgrade process since this gives you no going back if  
anything goes wrong. Just migrate the data to the new tables and leave  
the old data alone (obviously in a retired state).

Regards

Peter

On 29 Oct 2009, at 21:23, david conley wrote:

> I have some legacy subtables that I too want to get rid of.  I want  
> to create a new table, populate it with data from the subtables and  
> then drop the old subtables.  What I am finding is that if I run  
> this "upgrade" code in my development environment once and then do a  
> build, I cannot run this on any customer's data as my deployment  
> structure doesn't have the subtables anymore.

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Conley]

Thanks for your comments guys.  You've confirmed my thoughts.

It's great that 4D has given users the ability to drop tables,  
columns, etc., but I guess I won't be able to take advantage of those  
features due to the problems that it presents in a vertical market app  
with legacy releases to migrate from.  I really was looking forward to  
cleaning up our data model as our product has been around for a lot of  
years and versions of 4D.

Perhaps over time, some folks will come up with a sound methodology to  
use these features without making development or deployment to  
multiple sites a huge process.  I will certainly continue to mull that  
over and will look with interest with what others come up with.


Regards,

David Conley
By The Book, Inc.
815-234-7530
http://www.bythebook.com




On Oct 30, 2009, at 5:36 AM, Peter Jakobsson wrote:

> Hi David
>
> I don't think you should be worried.
>
> You have all the options available to you that existed before - just  
> a few more. Clearly, you can't 'have your cake and eat it' as far as  
> dropping tables goes, however I would just leave out the 'drop  
> table' aspect of your upgrade process till a future upgrade (say a  
> year down the line when you've got confidence in the new version and  
> users are happy).
>
> Also, you may consider this - if 4D's market share of the worldwide  
> DBMS market is 0.2% (I have no clue what it is really), then that  
> means that 99.8% of the worlds VM database applications have to deal  
> with this problem when they send out upgrades - from Quickbooks to  
> OS services.
>
> Again, my suggestion would be to definitely not drop the subtables  
> from your upgrade process since this gives you no going back if  
> anything goes wrong. Just migrate the data to the new tables and  
> leave the old data alone (obviously in a retired state).
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Chip Scheide]

ok -
how about a 2 step upgrade process?

step one :
a structure which *HAS* the subtables, and the new tables you want, has
code to move the data from the subtables to the new tables, **AND** has
code which drops/deletes the subtables.

Step two :
you "release" version of the database system.

not as clean as a single step - but still not a terrible process.
--
Now I have one other question with regards to this-
4D v11 automagically converts subtables into tables with a special
relation. I assume that the data in the subtable(s) are moved as well.

If you remove the special  subtable relation (as I understand) the
ex-subtable now becomes a regular table.

So.. given that the above is correct... what is wrong with letting 4D
convert the subtable to a table, then deleting the special relation
(should be do able in code)???

Doesnt that resolve the entire problem?

On Fri, 30 Oct 2009 11:36:42 +0100, Peter Jakobsson wrote:
>
>
> Again, my suggestion would be to definitely not drop the subtables
> from your upgrade process since this gives you no going back if
> anything goes wrong. Just migrate the data to the new tables and
> leave the old data alone (obviously in a retired state).
>
**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Conley]

Chip,

If in your development copy you convert your 2004 app to v11SQL (which  
creates the special relationship between the copied subtables and the  
one table) and then remove & create a new relationship, this works  
great on your development copy of the data.

If you do a build at this point and you've run this code through in  
your development copy, the structure no longer has any subtables left  
so when you open to another data file, the data in the subtable is  
lost during subsequent data file conversions.  This was to my point  
about one option was to never running any DDL in code through on your  
development copy.  Two reasons I don't like this is that 1) you can  
forget and screw up and then you are hosed and 2) The structure you  
see never really reflects what is actual (you would also need to look  
at DDL methods).  Even more importantly, I don't think this would work  
beyond 1 generation of rollout.

I still think you either need to stay away from drop table/field  
completely when you have multiple clients that your product is rolled  
out to or have a conversion release that everyone must roll to first  
that deals with any drops and then roll them to the final release.  If  
you have a tightly controlled rollout environment then this wouldn't  
be a big deal.  Image, however, a Quickbooks type of clientele  
(sophisticated to novice).  When they get a new version they run the  
installer and open up the product and go.  They would scream about  
having to do multiple iterations imho.


Regards,

David Conley
By The Book, Inc.
815-234-7530
http://www.bythebook.com




On Oct 30, 2009, at 9:39 AM, Chip Scheide wrote:

> ok -
> how about a 2 step upgrade process?
>
> step one :
> a structure which *HAS* the subtables, and the new tables you want,  
> has
> code to move the data from the subtables to the new tables, **AND**  
> has
> code which drops/deletes the subtables.
>
> Step two :
> you "release" version of the database system.
>
> not as clean as a single step - but still not a terrible process.
> --
> Now I have one other question with regards to this-
> 4D v11 automagically converts subtables into tables with a special
> relation. I assume that the data in the subtable(s) are moved as well.
>
> If you remove the special  subtable relation (as I understand) the
> ex-subtable now becomes a regular table.
>
> So.. given that the above is correct... what is wrong with letting 4D
> convert the subtable to a table, then deleting the special relation
> (should be do able in code)???
>
> Doesnt that resolve the entire problem?
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Nigel Greenlee]

All
I concur with this having dealt with the users of a commercial  
application for the past nearly 10 years, if i have learned a couple  
of things on upgrades it is this.

1) Don't over estimate the technical abilities of your users

2) Don't under estimate the ability of the users to take a process you  
think you have kept simple and break it. (or find the flaws!)

and specifically on the upgrade to V11 i have taken this approach:-

I have an application that contains about 10 subtables in 4D 2004 with  
about 6000 references to those subtables in the code and to get SQL  
compliant an important step is to get rid of the subtables, many  
clients over the years have wanted to use ODBC/SQL tools with our  
product which has never been practical, and reporting on the subtable  
information has always been a nightmare(several of the subtables  
should never have been subtables).

1) In our 2004 product I produced a final version. This is better for  
the users than an interim version which I did This version has code in  
to export the data. The code uses, EXPORT RECORD on all tables where  
there is no subtable-this is fast and tried and tested as a data  
transfer solution. On tables with a SUBTABLE we export the data from  
the main table into variables using a field name and data pairing,  
missing out the subtable field and data.. Then we export into a  
seperate document the subtable information in the same fashion but ADD  
the parent record KEY Field. Remodelling our existing export all data  
routine to do this really only took a few hours(and then several more  
hours trying to sort out a problem exporting data from picure fields  
with Superreport definitions in)

2) In the V11 version I did not create new tables to replace the  
subtables-this would have meant rewriting a lot of code and it would  
have been just too tempting to go through a process of remodelling  
that code. I added another field to each of the subtables to hold the  
KEY field from the parent rather than the special ID added by 4D.  I  
replaced all references to the tables as subtables with updated code,  
keeping things as simple as possible doing the minimum amount of  
change to that code. Had to take care of forms using the subtable  
information to make sure data being added to the former subtable would  
get saved(by adding a flag on 'ADD (SUB)RECORD, and checking the value  
of that flag at all significant points(SAVE (PARENT) RECORD to make  
sure the record is saved(We use listbox in all cases for displaying  
the subtable information).

3) Dropped the 'special' links to tables in V11 and removed the former  
subtable field(ID) on the parent records table, repurposed the  
ID_Added field on the former subtable to be a unique record ID on the  
'SUB' record table

4) Created an import routine to import the new data. I was lucky that  
we already had a mechanism that would respond to opening a NEW DATA  
FILE by asking if the user wanted to import data, all we had to do was  
put a breakout in there to allow them to import data from our V2004  
product. The import routine turns off all triggers prior to import and  
when it is finished continues the startup. The makes it ideal for a  
client server installation as the import can be kicked off as you  
leave  one night and when you come in next day the server is up and  
running without further interaction.

Using this approach it has been possible for me to drop references to  
some tables and remove those tables from the structure. The step from  
2004 to V11 is probably a good time to do this as the users are going  
to have to export the data, even if you were producing an interim  
version the path for users from 2004 to V11 is not a 'just open your  
data' upgrade, in my application as it is used by several  
installations with a mix of technical abilities I could never on a  
regular basis be dropping tables for that reason

One thing to be careful of with V11 on this which really can let users  
shoot themselves in the foot. The default location that 4D places the  
new datafile is inside the package(.4dbase).

On 30 Oct 2009, at 15:27, david conley wrote:

> Chip,
>
> If in your development copy you convert your 2004 app to v11SQL  
> (which creates the special relationship between the copied subtables  
> and the one table) and then remove & create a new relationship, this  
> works great on your development copy of the data.
>
> If you do a build at this point and you've run this code through in  
> your development copy, the structure no longer has any subtables  
> left so when you open to another data file, the data in the subtable  
> is lost during subsequent data file conversions.  This was to my  
> point about one option was to never running any DDL in code through  
> on your development copy.  Two reasons I don't like this is that 1)  
> you can forget and screw up and then you are hosed and 2) The  
> structure you see never really reflects what is actual (you would  
> also need to look at DDL methods).  Even more importantly, I don't  
> think this would work beyond 1 generation of rollout.
>
> I still think you either need to stay away from drop table/field  
> completely when you have multiple clients that your product is  
> rolled out to or have a conversion release that everyone must roll  
> to first that deals with any drops and then roll them to the final  
> release.  If you have a tightly controlled rollout environment then  
> this wouldn't be a big deal.  Image, however, a Quickbooks type of  
> clientele (sophisticated to novice).  When they get a new version  
> they run the installer and open up the product and go.  They would  
> scream about having to do multiple iterations imho.
>
>
> Regards,
>
> David Conley
> By The Book, Inc.
> 815-234-7530
> http://www.bythebook.com
>
>
>
>
> On Oct 30, 2009, at 9:39 AM, Chip Scheide wrote:
>
>> ok -
>> how about a 2 step upgrade process?
>>
>> step one :
>> a structure which *HAS* the subtables, and the new tables you want,  
>> has
>> code to move the data from the subtables to the new tables, **AND**  
>> has
>> code which drops/deletes the subtables.
>>
>> Step two :
>> you "release" version of the database system.
>>
>> not as clean as a single step - but still not a terrible process.
>> --
>> Now I have one other question with regards to this-
>> 4D v11 automagically converts subtables into tables with a special
>> relation. I assume that the data in the subtable(s) are moved as  
>> well.
>>
>> If you remove the special  subtable relation (as I understand) the
>> ex-subtable now becomes a regular table.
>>
>> So.. given that the above is correct... what is wrong with letting 4D
>> convert the subtable to a table, then deleting the special relation
>> (should be do able in code)???
>>
>> Doesnt that resolve the entire problem?
>>
>
> **********************************************************************
> Get the speed and power of 4D v11 SQL
> before upgrade prices increase - http://www.4d.com
>
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4D.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4D_Tech-Unsubscribe@...
> **********************************************************************

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[psmith-3]

Nigel,

I know you've already finished the job, but what would you think about  
this approach.

1) Create a final version of your 2004 database with new tables to  
replace all subtables with tables and eliminate all references to  
subtables in your code.

2) This final version could then usse EXPORT RECORD on all the tables,  
not just the ones without subtables.

3) Import the data in v11 with IMPORT RECORD into a fresh structure  
with no references at all to subtables (by dropping the subtable ID  
field etc)

This is similar to your approach, but avoids having to export the  
subtable records in text files. You do, of course, have to write code  
to populate the new tables in the 2004 database.

I'm not sure which version would be faster. I guess it would depend on  
the number of subtables you're dealing with.


Paul Smith

TSE International

> All
> I concur with this having dealt with the users of a commercial  
> application for the past nearly 10 years, if i have learned a couple  
> of things on upgrades it is this.
>
> 1) Don't over estimate the technical abilities of your users
>
> 2) Don't under estimate the ability of the users to take a process  
> you think you have kept simple and break it. (or find the flaws!)
>
> and specifically on the upgrade to V11 i have taken this approach:-
>
> I have an application that contains about 10 subtables in 4D 2004  
> with about 6000 references to those subtables in the code and to get  
> SQL compliant an important step is to get rid of the subtables, many  
> clients over the years have wanted to use ODBC/SQL tools with our  
> product which has never been practical, and reporting on the  
> subtable information has always been a nightmare(several of the  
> subtables should never have been subtables).
>
> 1) In our 2004 product I produced a final version. This is better  
> for the users than an interim version which I did This version has  
> code in to export the data. The code uses, EXPORT RECORD on all  
> tables where there is no subtable-this is fast and tried and tested  
> as a data transfer solution. On tables with a SUBTABLE we export the  
> data from the main table into variables using a field name and data  
> pairing, missing out the subtable field and data.. Then we export  
> into a seperate document the subtable information in the same  
> fashion but ADD the parent record KEY Field. Remodelling our  
> existing export all data routine to do this really only took a few  
> hours(and then several more hours trying to sort out a problem  
> exporting data from picure fields with Superreport definitions in)
>
> 2) In the V11 version I did not create new tables to replace the  
> subtables-this would have meant rewriting a lot of code and it would  
> have been just too tempting to go through a process of remodelling  
> that code. I added another field to each of the subtables to hold  
> the KEY field from the parent rather than the special ID added by  
> 4D.  I replaced all references to the tables as subtables with  
> updated code, keeping things as simple as possible doing the minimum  
> amount of change to that code. Had to take care of forms using the  
> subtable information to make sure data being added to the former  
> subtable would get saved(by adding a flag on 'ADD (SUB)RECORD, and  
> checking the value of that flag at all significant points(SAVE  
> (PARENT) RECORD to make sure the record is saved(We use listbox in  
> all cases for displaying the subtable information).
>
> 3) Dropped the 'special' links to tables in V11 and removed the  
> former subtable field(ID) on the parent records table, repurposed  
> the ID_Added field on the former subtable to be a unique record ID  
> on the 'SUB' record table
>
> 4) Created an import routine to import the new data. I was lucky  
> that we already had a mechanism that would respond to opening a NEW  
> DATA FILE by asking if the user wanted to import data, all we had to  
> do was put a breakout in there to allow them to import data from our  
> V2004 product. The import routine turns off all triggers prior to  
> import and when it is finished continues the startup. The makes it  
> ideal for a client server installation as the import can be kicked  
> off as you leave  one night and when you come in next day the server  
> is up and running without further interaction.
>
> Using this approach it has been possible for me to drop references  
> to some tables and remove those tables from the structure. The step  
> from 2004 to V11 is probably a good time to do this as the users are  
> going to have to export the data, even if you were producing an  
> interim version the path for users from 2004 to V11 is not a 'just  
> open your data' upgrade, in my application as it is used by several  
> installations with a mix of technical abilities I could never on a  
> regular basis be dropping tables for that reason
>
> One thing to be careful of with V11 on this which really can let  
> users shoot themselves in the foot. The default location that 4D  
> places the new datafile is inside the package(.4dbase).

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Nigel Greenlee]

Paul

I did try that in a test with one subtable. The new table exactly  
matched my table in V11 but still the 'IMPORT RECORD' caused a crash  
when it tried to import the data so i gave up on it. Although  
exporting the data out using variables is slower it works and is it a  
one time operation was not worth investing a whole heap of time into  
investigating why.

One thing i have been considering though which i would do if I
1) Had time
2) was doing this again

Would be to have the import able to to a second import without  
recreating everything...Why? Because the export import is a tedious  
task and if a user could set aside the datafile after the import they  
could all their testing and then import just updates from their live  
system to the ready to go datafile and save a lot of time..but I guess  
if i was going that far i would probably just a 4D-4D connection
(SOAP)...


On 1 Nov 2009, at 09:33, psmith wrote:

> Nigel,
>
> I know you've already finished the job, but what would you think  
> about this approach.
>
> 1) Create a final version of your 2004 database with new tables to  
> replace all subtables with tables and eliminate all references to  
> subtables in your code.
>
> 2) This final version could then usse EXPORT RECORD on all the  
> tables, not just the ones without subtables.
>
> 3) Import the data in v11 with IMPORT RECORD into a fresh structure  
> with no references at all to subtables (by dropping the subtable ID  
> field etc)
>
> This is similar to your approach, but avoids having to export the  
> subtable records in text files. You do, of course, have to write  
> code to populate the new tables in the 2004 database.
>
> I'm not sure which version would be faster. I guess it would depend  
> on the number of subtables you're dealing with.
>
>
> Paul Smith
>
> TSE International
>
>> All
>> I concur with this having dealt with the users of a commercial  
>> application for the past nearly 10 years, if i have learned a  
>> couple of things on upgrades it is this.
>>
>> 1) Don't over estimate the technical abilities of your users
>>
>> 2) Don't under estimate the ability of the users to take a process  
>> you think you have kept simple and break it. (or find the flaws!)
>>
>> and specifically on the upgrade to V11 i have taken this approach:-
>>
>> I have an application that contains about 10 subtables in 4D 2004  
>> with about 6000 references to those subtables in the code and to  
>> get SQL compliant an important step is to get rid of the subtables,  
>> many clients over the years have wanted to use ODBC/SQL tools with  
>> our product which has never been practical, and reporting on the  
>> subtable information has always been a nightmare(several of the  
>> subtables should never have been subtables).
>>
>> 1) In our 2004 product I produced a final version. This is better  
>> for the users than an interim version which I did This version has  
>> code in to export the data. The code uses, EXPORT RECORD on all  
>> tables where there is no subtable-this is fast and tried and tested  
>> as a data transfer solution. On tables with a SUBTABLE we export  
>> the data from the main table into variables using a field name and  
>> data pairing, missing out the subtable field and data.. Then we  
>> export into a seperate document the subtable information in the  
>> same fashion but ADD the parent record KEY Field. Remodelling our  
>> existing export all data routine to do this really only took a few  
>> hours(and then several more hours trying to sort out a problem  
>> exporting data from picure fields with Superreport definitions in)
>>
>> 2) In the V11 version I did not create new tables to replace the  
>> subtables-this would have meant rewriting a lot of code and it  
>> would have been just too tempting to go through a process of  
>> remodelling that code. I added another field to each of the  
>> subtables to hold the KEY field from the parent rather than the  
>> special ID added by 4D.  I replaced all references to the tables as  
>> subtables with updated code, keeping things as simple as possible  
>> doing the minimum amount of change to that code. Had to take care  
>> of forms using the subtable information to make sure data being  
>> added to the former subtable would get saved(by adding a flag on  
>> 'ADD (SUB)RECORD, and checking the value of that flag at all  
>> significant points(SAVE (PARENT) RECORD to make sure the record is  
>> saved(We use listbox in all cases for displaying the subtable  
>> information).
>>
>> 3) Dropped the 'special' links to tables in V11 and removed the  
>> former subtable field(ID) on the parent records table, repurposed  
>> the ID_Added field on the former subtable to be a unique record ID  
>> on the 'SUB' record table
>>
>> 4) Created an import routine to import the new data. I was lucky  
>> that we already had a mechanism that would respond to opening a NEW  
>> DATA FILE by asking if the user wanted to import data, all we had  
>> to do was put a breakout in there to allow them to import data from  
>> our V2004 product. The import routine turns off all triggers prior  
>> to import and when it is finished continues the startup. The makes  
>> it ideal for a client server installation as the import can be  
>> kicked off as you leave  one night and when you come in next day  
>> the server is up and running without further interaction.
>>
>> Using this approach it has been possible for me to drop references  
>> to some tables and remove those tables from the structure. The step  
>> from 2004 to V11 is probably a good time to do this as the users  
>> are going to have to export the data, even if you were producing an  
>> interim version the path for users from 2004 to V11 is not a 'just  
>> open your data' upgrade, in my application as it is used by several  
>> installations with a mix of technical abilities I could never on a  
>> regular basis be dropping tables for that reason
>>
>> One thing to be careful of with V11 on this which really can let  
>> users shoot themselves in the foot. The default location that 4D  
>> places the new datafile is inside the package(.4dbase).
>
> **********************************************************************
> Get the speed and power of 4D v11 SQL
> before upgrade prices increase - http://www.4d.com
>
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4D.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:4D_Tech-Unsubscribe@...
> **********************************************************************

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Alan Chan]

I forgot to mention a bug in subtable-special-link removal.

Removing subtables' special link will corrupt index in v11. It seems like
the 4D doesn't remove the indexes created in these subtables during
conversion cleanly. Run repair would fix the problem. These indexes must
be fixed. Or else, Set Automatic Relation will not work with these tables
if you create relation with these tables.

Although this could be fixed by repairing the data, this requires many
extra men-hours and downtime for each v11 upgrade deployment.

Alan Chan

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[John DeSoi]

In my structure I can't drop subtables at all, or even unlink the  
special relation. Every time I do this it corrupts the structure and  
4D won't open in anymore (error -10509 Can't open database). Opening  
in MSC gives this error:

Initialization error (Init_CritHandles)
The initialization of the resources failed. Please try repairing the  
application file

Trying to repair gives the same error.

This bug was confirmed by 4D in 11.4. As far as I know, it has not  
been fixed.

John DeSoi, Ph.D.



On Nov 1, 2009, at 6:19 PM, Alan Chan wrote:

> I forgot to mention a bug in subtable-special-link removal.
>
> Removing subtables' special link will corrupt index in v11. It seems  
> like
> the 4D doesn't remove the indexes created in these subtables during
> conversion cleanly. Run repair would fix the problem. These indexes  
> must
> be fixed. Or else, Set Automatic Relation will not work with these  
> tables
> if you create relation with these tables.
>
> Although this could be fixed by repairing the data, this requires many
> extra men-hours and downtime for each v11 upgrade deployment.

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Alan Chan]

Hi John,

That's bad. Is anything special with your structure? We could unlink
subtables and recreate manual relation with its orignal parent table. We
have also unlinked some unused subtables and deleted them without any
problem except the index corruption bug.

Alan Chan

4D iNug Technical <4d_tech@...> writes:

>In my structure I can't drop subtables at all, or even unlink the  
>special relation. Every time I do this it corrupts the structure and  
>4D won't open in anymore (error -10509 Can't open database). Opening  
>in MSC gives this error:
>
>Initialization error (Init_CritHandles)
>The initialization of the resources failed. Please try repairing the  
>application file
>
>Trying to repair gives the same error.
>
>This bug was confirmed by 4D in 11.4. As far as I know, it has not  
>been fixed.
>
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[Alan Chan]

Hi John,

Forget my question. I got that in bug list.

Thanks.

Alan

4D iNug Technical <4d_tech@...> writes:
>
>
>That's bad. Is anything special with your structure? We could unlink
>subtables and recreate manual relation with its orignal parent table. We
>have also unlinked some unused subtables and deleted them without any
>problem except the index corruption bug.
>
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************

[David Conley]

John,

I got that error as well.  In my case, I removed the link and dropped the
subtable in code.  What I didn't do was drop the column from the "one"
table too.  As soon as I did that, it worked correctly.



On Sun, 1 Nov 2009 18:27:50 -0500, John DeSoi <desoi@...> wrote:
> In my structure I can't drop subtables at all, or even unlink the  
> special relation. Every time I do this it corrupts the structure and  
> 4D won't open in anymore (error -10509 Can't open database). Opening  
> in MSC gives this error:
>

**********************************************************************
Get the speed and power of 4D v11 SQL
before upgrade prices increase - http://www.4d.com
   

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4D.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4D_Tech-Unsubscribe@...
**********************************************************************