advice on creating an update routine to change customers data tables when program is updated

[DrAlbany]

Hi Guys..,

I've done a bit of searching but i'm either using the wrong terminology or my search terms are too generic to get the results i need.

I am letting a customer have a beta copy of my software, I will need to create new tables and update existing tables as the program evolves and then when I issue an update to the customer I will need to update the tables to match the latest version etc....

Does any one know of a solution.

My plan if I have to create a solution would be to have a text file with the installed version on the clients machine so when I issue an update I can check and change the customers data tables installed to match the new program data structures.

I would create a separate program to scan my development environment and create a data table with the names of the programs tables and field so i can track the changes and write a routine to update the customers data tables etc...

Any ideas?

Thanks in advance.

Regards

Steve

If you alway do what you've always done, you always get what you've always got.

 

[Mike Lewis]

Steve,

One very effective way of doing this is to use the Stonefield Database Toolkit. Essentially, it records the state of a database (the structures, indexes, etc, not the actual data). Then, on any given computer, it gives the option to sync the structures with those of the master copy. You need to install the software with your app, and you also need to add some code to your main program (or your separate update utility) to interface with it. But, once you've done that, it does exactly what you need.

If you find that solution to be overkill (it is fairly complicated to set up, and it costs money), then it's fairly easy to write something yourself. The way I usually handle it is to produce a script containing the various ALTER TABLE, CREATE TABLE and DROP TABLE commands that you need to bring the user's database into line with the master copy. You would have to write that script manually, but you would probably do that anyway in your development environment, as you introduce the changes.

You then build the script into a VFP executable, send it to the user, and have them run it. You also need some system to record the fact that this has been done, to ensure they don't run the same script twice. You can handle that with the text file that you mentioned.

I hope this answers your question. If I have misunderstood it, perhaps you could clarify.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

By the way, I assume that your data is all held in DBFs. If you are using a back-end database, then that's another matter. Some databases - including, for example, SQL Server - have the ability to generate the update script for you. But, as far as I know, there is no automatic way of doing that in VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[DrAlbany]

Hi Mike..,

Many thanks...

You hit the nail on the head

I have seen the stonefields program but like you said it costs money $495 which i'm not unreasonable... I'm very tempted to purchase it but want to make sure that its not overkill

The program i'm devising is going to be quite a beast so purchasing the software may be the best bet in the long run.

Once again many thanks... Its nice to get other people prospective on things sometimes being a lone developer can be "lonely" so to speak

Cheers.

Regards

Steve

If you alway do what you've always done, you always get what you've always got.

 

[Mike Lewis]

Glad I was of some help, Steve.

I see there is an "SDT Demo" available for download from the Stonefield site. I don't know whether that's an evaluation version of the software or simply a video demonstration, but it might be worth your taking a look at it. Also, Doug Hennig, who is the author of SDT, often stops by here, so if you have any questions on matters of detail, I'm sure he'll be able to help.

Glancing at the testimonials on the product page on their site, I see I wrote a "rave review" of the product. Funny that I needed reminding of that. Then again, it was in 2003.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[DrAlbany]

Hehe yeah I noticed your name was mentioned

I'm at a road block at the moment... holiday in 4 weeks... but I need to get the beta version of my software into the client for testing... hmmm... I will get Stonefields when I come back of my Hol's...

With my workload at the moment I think the learning curve / implementation time will cause time issues.

Once again...

Many thanks.

Regards

Steve

If you alway do what you've always done, you always get what you've always got.

 

[EzLogic]

I have my own method.
in my startup, i have this:

IF NOT checkUpdate()
	MESSAGEBOX("There was an error applying system update.  Please contact IT support",16,_SCREEN.CAPTION)
	RETURN .F.
ENDIF


FUNCTION checkUpdate()

	LOCAL lcVersion
	lcVersion = GetPOSVersion() && AGETFILEVERSION(AVER,'\ezcell\ezcell.exe')
	cUpdateFile = lcVersion + '.upd' && AVER[4]+'.upd'



	IF NOT FILE(cUpdateFile)
		IF PrePareforUpdate()
			WAIT WINDOW "Updating EzCell POS to Version: " + lcVersion + ", Please Wait...." +CHR(13) + CHR(13) +;
				"Please do NOT launch POS on any other machine until this message disappear" + CHR(13) + ;
				"If you do so, you will interrupt your Update process and might damage your EzCellPOS files." NOWAIT

			ERASE \ezcellERP\*.upd

			DO UpdateTables
			FCREATE(cUpdateFile)
			IF FILE("\ezcellERP\ExitEzCell.txt")
				ERASE \ezcellERP\exitezcell.txt
			ENDIF
			WAIT CLEAR
			RETURN .T.
		ELSE
			RETURN .F.
		ENDIF
	ELSE
		RETURN .T.
	ENDIF
ENDFUNC
FUNCTION GetPOSVersion()
	LOCAL lcVersion

	lcVersion = '0.0'
	IF FILE("EzCellERP.exe")
		DIMENSION laVer[1]
		AGETFILEVERSION(laVer, "ezcellerp.exe")
		IF ALEN(laVer) > 0
			lcVersion = ALLTRIM(laVer[4])
		ENDIF
	ENDIF
	RETURN lcVersion
ENDFUNC

FUNCTION PrePareforUpdate()
	TRY
		IF NOT FILE("\EzCellERP\exitezcell.txt")
			STRTOFILE("do not use pos","\ezcellerp\exitezcell.txt")
		ENDIF

		LOCAL laTables, lcTableName, lcKey
		CLOSE DATABASES ALL

		IF NOT DBUSED("EzCellERP")
			OPEN DATABASE "\EzCellERP\DATA\EzCellERP" EXCLUSIVE
		ENDIF
		SET DATABASE TO ezcellERP
		DIMENSION laTables[1]
		ADBOBJECTS(laTables, 'TABLE')
		FOR EACH lcTableName IN laTables
			USE (lcTableName) IN 0 EXCLUSIVE
		ENDFOR

		CLOSE DATABASES ALL
		llSuccess = .T.
		EXIT
	CATCH TO loexp
		MESSAGEBOX("Please make sure the system is not being used in order to apply the update.",16,"Failed to update")
		llSuccess = .F.
		ERASE \ezcellERP\exitezcell.txt
		EXIT
	ENDTRY
	CLOSE DATABASES ALL
	RETURN llSuccess
ENDFUNC


FUNCTION AddNewField(tcTable,tcField,tcType,tnLen,tnDec)

	lcTable = UPPER(ALLTRIM(tcTable))
	lcField	= UPPER(ALLTRIM(tcField))
	lcType	= UPPER(ALLTRIM(tcType))
	lnLen	= tnLen
	lnDec	= tnDec
	IF EMPTY(tcTable) OR EMPTY(tcField) OR EMPTY(tcType)
		RETURN .F.
	ENDIF
	IF NOT INLIST(lcType,'C','N','D','T','L','M','I')
		RETURN .F.
	ENDIF
	IF (EMPTY(lnLen) OR lnLen = 0) AND INLIST(lcType, "C", "N")
		RETURN .F.
	ENDIF

	lcFileName = ADDBS(gcDataFolder)+lcTable + '.dbf'
	IF NOT FILE(lcFileName)
		RETURN .F.
	ENDIF

	DO CASE
		CASE lcType = 'C'
			lcType = [C(]+ALLTRIM(STR(lnLen))+[)]
		CASE lcType = 'N'
			lcType = [N(]+ALLTRIM(STR(lnLen))+[,]+ALLTRIM(STR(lnDec))+[)]
		CASE lcType = 'L'
			lcType = [L]
		CASE lcType = 'M'
			lcType = 'M'
		CASE lcType = 'D'
			lcType = 'D'
		CASE lcType = 'T'
			lcType = 'T'
		CASE lcType = 'I'
			lcType = 'I'
	ENDCASE

	CLOSE DATABASES ALL
	USE (lcFileName) IN 0 EXCLUSIVE
	IF FieldExist(tcTable,tcField)
		llAdd = .F.
	ELSE
		llAdd = .T.
	ENDIF
	lcExec = ""
	IF llAdd
		ALTER TABLE &tcTable ADD COLUMN &tcField &lcType
		CLOSE DATABASES ALL
		RETURN .T.
	ENDIF
	CLOSE DATABASES ALL
	RETURN .F.
ENDFUNC
**********************************
FUNCTION AlterField(tcTable,tcField,tcType,tnLen,tnDec)
	LOCAL lcTable, lcField, lcType, lnLen, lnDec, llReturn, lcFileName

	lcTable = UPPER(ALLTRIM(tcTable))
	lcField	= UPPER(ALLTRIM(tcField))
	lcType	= UPPER(ALLTRIM(tcType))
	lnLen	= tnLen
	lnDec	= tnDec
	IF EMPTY(tcTable) OR EMPTY(tcField) OR EMPTY(tcType)
		RETURN .F.
	ENDIF
	IF NOT INLIST(lcType,'C','N','D','T','L','M','I')
		RETURN .F.
	ENDIF
	IF (EMPTY(lnLen) OR lnLen = 0) AND INLIST(lcType, "C", "N")
		RETURN .F.
	ENDIF

	lcFileName = ADDBS(gcDataFolder)+lcTable + '.dbf'
	IF NOT FILE(lcFileName)
		RETURN .F.
	ENDIF

	DO CASE
		CASE lcType = 'C'
			lcType = [C(]+ALLTRIM(STR(lnLen))+[)]
		CASE lcType = 'N'
			lcType = [N(]+ALLTRIM(STR(lnLen))+[,]+ALLTRIM(STR(lnDec))+[)]
		CASE lcType = 'L'
			lcType = [L]
		CASE lcType = 'M'
			lcType = 'M'
		CASE lcType = 'D'
			lcType = 'D'
		CASE lcType = 'T'
			lcType = 'T'
		CASE lcType = 'I'
			lcType = 'I'
	ENDCASE


	CLOSE DATABASES ALL
	USE (lcFileName) IN 0 EXCLUSIVE
	IF FieldExist(tcTable,tcField)
		ALTER TABLE &tcTable ALTER COLUMN &tcField &lcType
	ELSE
		ALTER TABLE &tcTable ADD COLUMN &tcField &lcType
	ENDIF

	llReturn = .T.

	CLOSE DATABASES ALL
	RETURN llReturn
ENDFUNC

******************
FUNCTION FieldExist(tcTable,tcField)

	IF NOT USED(tcTable)
		RETURN .T. && something wrong or
	ENDIF

	llFound	= .F. && not found
	SELECT (tcTable)
	FOR gnCount = 1 TO FCOUNT( )  && Loop for number of fields
		IF UPPER(ALLTRIM(FIELD(gnCount))) == UPPER(ALLTRIM(tcField))  && Display each field
			llFound = .T. && found it
			EXIT
		ENDIF
	NEXT
	RETURN llFound
ENDFUNC

My UpdatePOS.PRG (sample codes and lines from it)

CLOSE DATABASES ALL
CreateARDocList()
CreateICSerialParts()

addNewField("WOSerial","EmpID","c",10)
AddNewField("ICItem","fk_woqclist","I",4)
AddIndex("icitem","fk_woqclis","fk_woqclist")
AddNewField("WOSerial","LaborPrice","N",10,2)
AddNewField("WOSerial","PartPrice","N",10,2)

AddNewField("employee","ESD","L")
AddNewField("employee","Exempt","L")

AddNewField('icitem','Clearance','L')
AddNewField('WOSerialParts','SerPart','C',20)
AddIndex("WOSerialParts",'SerPart','SerPart')
AddIndex("ICSerialParts",'CartonNO','CartonNo')

AddIndex('ARLeads','contact','contact')
AddNewCarrier('Safelink')
AddNewCarrier('PreWay')
AddNewCarrier('ILBTV')

AddNewField("ARMast","VoidUser","C",10)
AddNewField("ARMast","VoidDate","T")
AddNewField('ARTran','RealPrice',"N",10,2)  && price in iciloc at the time of sale. to know discount
AddNewField("ARReceipt","BatchNo","C",10)

CreateCoxCom()
CreateDefaultcom()

Ali Koumaiha
TeknoSoft Inc.
Michigan

 

[TamarGranor]

I second the recommendation for Stonefield. Get it up and running before you deploy to the customer or you'll have to do some work to retrofit and you'll miss some of the benefits.

I have found that sometimes I need to do stuff that SDT can't handle. That's been mostly when I'm dealing with a database that was badly designed in the first place (like by an amateur). Here's an article I wrote about it:

http://www.tomorrowssolutionsllc.com/Articles/Retro-fitting Primary Keys.PDF

Tamar

 

[Dan Freeman]

I'll third the recommendation for Stonefield, and heartily endorse Tamar's comments. It shouldn't take more than a couple of hours to plug in SDT in the first place and will save you a bunch of time thereafter -- but DO get it plugged in for your first release.

 

[DrAlbany]

Nice one guys..,

I really appreciate the input and recommendations. I will get the trial and have a play.

Many thanks.

Regards

Steve

If you alway do what you've always done, you always get what you've always got.

 

[Olaf Doschke]

I also worked with SQL Scripts instead of Stonefield, though we have it. The good thing with it is, it can replay changes you did manually, as it simply compares the schema it took from your development database, compares it to the schema at the customer site and "computes" the needed changes.

Some changes require a bit mor intervention on the data level, too, so I prefered to write scripts to be able to do a little data migration/aggregation routines, when there was need for that, too.

There is a rather mechanical third approach, that is you create an empty database in the new schema and fill in old data. It has other advantages and disadvantages. For one, you have to move all data, even if only one table changes. You can mend that problem by moving unchanged dbfs, but you have to know what you're doing to keep the tables matching to the DBC, if there is a new one. What's less of a problem is, that you need to move all data from the tables you change, ALTER TABLE does the same, it never changes the original file, it creates a new one and moves data anyway.

One way to do that very mechanically is using gendbc (or gendbcx) to create the database schema empty and then append from the old dbfs. That's outlining it very roughly. All three approaches with ALTER scripts, SDT or copying data to a new empty scheme have their pros and cons.

Bye, Olaf.