Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
Updating Databases |
Wed, Jan 30 2013 12:41 AM | Permanent Link |
Adam H. | I'm full of questions today. Sorry.
In previous DBISam appilcations, I've had a wide variety of 'versions' out there. In fact, my two largest applications get modified almost on a daily basis. These modifications can include new fields, or new tables to the application (and database). I've found the easiest way (for me) to handle these in the past is to have a form where I place a Table component on there for each table, and Load all the persistent fields. If I add a new table to my database / application, I add the persistent field (or new table component) to this form and change a DBVer variable within the program. When the end user updates, it checks the DBVer variable, and if his is older, it executes a function that goes through each TDBISamTable component. If the table doesn't exist, it calls createtable. If the table does exist, it scans through each field and compares with the TPersistentfield in the table. If a field doesn't exist in his database, it then adds that field. It took a bit to write this procedure, but it has served me very well. I don't need to worry about where each client is upto with their database, and adding new fields to my application is a very simple task. Just add in DBSys, and add the persistent field (or table) to the form, and I'm done... However - now that I'm working on an application in EDB - I'm finding that it's going to be far more difficult for me to achieve the same function as I did in EDB. Probably not impossible, but before I start work and spend a lot of time, I wanted to see how others handle this, and whether I should be considering another option / method? Cheers Adam. |
Wed, Jan 30 2013 10:49 AM | Permanent Link |
Adam Brett Orixa Systems | Adam,
I would suggest that you open EDBManager and review data returned from the query: SELECT * FROM Information.TableColumns Adding WHERE TableName = 'YourTableName' will give you a valid field-list of the existing fields. You can even use the new EDB-SQL command: SELECT LIST(Name) FROM Information.TableColumns WHERE TableName = 'YourTableName' To generate a simple comma-list you could plug into a TStringList.CommaText -- It would be possible to write routines in Delphi which compared this list to your data-tables fields, and then run from here to a SQL statement which adds and removes columns. -- I have a second suggestion: EDBManager has a "create upgrade script" option. This creates a full script to update between 2 versions of a database. It is simple and effective, though under-documented You select the db you want to upgrade then click on "Migrate database in the "Tasks Panel" and work through the "Upgrade" steps thereafter. If you used EDBManager to generate such a script You could then incorporate this update script into a "run updates" method in your new executable and it could run the first time the exe ran. The reason I think you should think about this is because there is so much more to EDB than just tables, and table columns. There are triggers, views, procedures ... Once you start to use EDB you will want to use at least some these features, and the update method you describe will not carry over these additional features, as it only creates new columns. |
Wed, Jan 30 2013 4:41 PM | Permanent Link |
Adam H. | Hi Adam,
Thanks for your suggestion... > I would suggest that you open EDBManager and review data returned from the query: > > SELECT * FROM Information.TableColumns <Snip> > It would be possible to write routines in Delphi which compared this list to your data-tables fields, and then run from here to a SQL statement which adds and removes columns. Thanks for that suggestion. In the end, it looks like this would follow a similar process to what I'm currently doing. (ie, storing a list of 'current' fields and indexes, either in a TDataset as I am now, or in a TStringList. I'm guessing that I just need to work on converting my script across... > I have a second suggestion: > > EDBManager has a "create upgrade script" option. I did see this, and got excited for a moment - but unfortunately I don't think it's going to accomplish what I need. This is fine for upgrading one 'known' database to my current database, but it's not going to help where I distribute an update, and I have 40 clients that are all running different older versions that update to the current. It looks like I may need to continue to work on the original idea. I just thought that this might be a common situation where others may have already done something similar, but I guess most people have 'hands on' access to the databases they're maintaining to do it manually? Thanks again for your help! Adam. |
Fri, Feb 1 2013 12:07 PM | Permanent Link |
Adam Brett Orixa Systems | Adam,
I have a problem similar to yours where users can have versions 1, 2, 3, 4 ... of a product and be upgrading to a new version. I use the EDB Generate "Upgrade Script" for each distinct version & then have a method "UpgradeDatabase" which takes a VersionID (held in an INI) There are then a series of upgrade scripts, if the user is coming from version 2 the script for 3 + 4 will run ... etc. It is a bit clunky, and means having really long text-based SQL scripts built into the EXE, but it is secure & works, so long as each part of the script is closely tested and accurate. It is easier & more flexible than it perhaps sounds from the above ... |
Fri, Feb 1 2013 12:37 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< I did see this, and got excited for a moment - but unfortunately I don't think it's going to accomplish what I need. This is fine for upgrading one 'known' database to my current database, but it's not going to help where I distribute an update, and I have 40 clients that are all running different older versions that update to the current. >> The next minor release is going to have a new COMPARE DATABASE SQL statement that will generate a special system information table that contains the CREATE/ALTER/DROP statements that will be necessary to upgrade one database to another (in a minimal fashion and a dependency-sensitive order). Tim Young Elevate Software www.elevatesoft.com |
Fri, Feb 1 2013 1:06 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>The next minor release is going to have a new COMPARE DATABASE SQL statement >that will generate a special system information table that contains the >CREATE/ALTER/DROP statements that will be necessary to upgrade one database >to another (in a minimal fashion and a dependency-sensitive order). Nice Roy |
Mon, Feb 4 2013 5:24 PM | Permanent Link |
Adam H. | Hi Adam,
> I have a problem similar to yours where users can have versions 1, 2, 3, 4 ... of a product and be upgrading to a new version. > > I use the EDB Generate "Upgrade Script" for each distinct version & then have a method "UpgradeDatabase" which takes a VersionID (held in an INI) > > There are then a series of upgrade scripts, if the user is coming from version 2 the script for 3 + 4 will run ... etc. > > It is a bit clunky, and means having really long text-based SQL scripts built into the EXE, but it is secure & works, so long as each part of the script is closely tested and accurate. > > It is easier & more flexible than it perhaps sounds from the above ... Thanks for that. It's an option I was weighing up, and now I've just seen Tim's response about a compare database sql statement which sounds very inviting indeed. Cheers Adam. |
Mon, Feb 4 2013 5:25 PM | Permanent Link |
Adam H. | On 2/02/2013 5:06 AM, Roy Lambert wrote:
> Tim > >> The next minor release is going to have a new COMPARE DATABASE SQL statement >> that will generate a special system information table that contains the >> CREATE/ALTER/DROP statements that will be necessary to upgrade one database >> to another (in a minimal fashion and a dependency-sensitive order). > > Nice Very Nice indeed. I'll wait for this release and see if it'll do what I'm needing. Thanks Tim! Adam. |
Wed, Feb 6 2013 6:46 AM | Permanent Link |
Adam Brett Orixa Systems | >Roy Lambert wrote:
>Tim >>The next minor release is going to have a new COMPARE DATABASE SQL statement >>that will generate a special system information table that contains the >>CREATE/ALTER/DROP statements that will be necessary to upgrade one database >>to another (in a minimal fashion and a dependency-sensitive order). >Nice >Roy Plus one! |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |