MODIFY STRUCTURE example derived from the APPEND FROM... suggestions topic
1,093 views
Skip to first unread message
Zoran Sibinovic
Dec 20, 2015, 11:53:00 AM12/20/15
to Harbour Users
Hi to all,
from the suggestions
I create a compact MODIFY STRUCTURE function that works similar as in Foxpro, dbase or VFP.
The code is fresh and hope to have tested all the variations.
Everyone is welcome to try it and let know
Thanks
Zoran
Przemyslaw Czerpak
Dec 23, 2015, 10:55:43 AM12/23/15
to harbou...@googlegroups.com
On Sun, 20 Dec 2015, Zoran Sibinovic wrote:
Hi Zoran,
I think that you are trying to open already open doors.
All what you need is redirect error block to your own
routine and simply ignore errors or implement the
translations you need.
Below I'm attaching self contain example of code which
creates tables with incompatible fields (order, types
and range) and then executes APPEND FROM command with
it's own custom error block which makes all conversions
user may need.
I do not know how exactly MODIFY STRUCTURE works in VFP
and what hidden conversions it does for incompatible types
and values out of new field range but maybe you can update
attached err_append() function and add missing conversions.
Then we can add such error handler to Harbour repository
as base for VFP compatible version of APPEND FROM.
best regards,
Przemek
Hi Zoran,
All what you need is redirect error block to your own
routine and simply ignore errors or implement the
translations you need.
Below I'm attaching self contain example of code which
creates tables with incompatible fields (order, types
and range) and then executes APPEND FROM command with
it's own custom error block which makes all conversions
user may need.
I do not know how exactly MODIFY STRUCTURE works in VFP
and what hidden conversions it does for incompatible types
and values out of new field range but maybe you can update
attached err_append() function and add missing conversions.
Then we can add such error handler to Harbour repository
as base for VFP compatible version of APPEND FROM.
best regards,
Przemek
Zoran Sibinovic
Dec 24, 2015, 4:33:53 AM12/24/15
to Harbour Users
Hi Przemek,
A code integration of a database managing utility by using only one function is not a new idea, at least for me. I saw that your code using an error handler is very compact and clean and I surely i will integrate it in mine, but is only part of the idea.
Database managers as dbase, fox and VFP changed trough their existance, but one thing is common for all and that is the way of how to create, manage and modify the dbf databases structure (using the MODIFY STRUCTURE command). Of course, trough time were added new field types and some visual options for managing the databases but the command itself does the same things all the way.
To be honest, at the time of my commit I didn't make the optimization of the final code yet, thing that you did for a part of the code.
A purpose of a "MODIFY STRUCTURE" command, or function, can be nearly defined as: "A command or function that provide the ability to the app developer to add and delete fields of an existing database, change their lengths and change their names and types.
The append command in my code called APPCONV is a part of my MODISTRU() function, but can be used separately as a standalone command with more options if we want only to append data and handling the possible errors.
It's not a problem to create a database but modify an existing one without using lot of code lines.
Examples of use of my MODISTRU() (a kind of MODIFY STRUCTURE command):
1. you need to create a temp database based on an existing database structure and need also some additional fields and maybe, after finished some operations on it, later, to flush it to replace a production database and maybe, without some unnecessary fields and, more worse, with a need to change some field name for some other app usage. All of this keeping the existing data.
2. you have some app a database update for the client, you need to alterate some database, by adding, deleting fields, etc.. Without some above mentioned command, for a databases structure update, you must use some updated temp bases and append the existing data to them without reported errors. For 1-2 databases we can do this manually, the problem emerge if we have bigger number of them multiplied by the number of clients.
With MODISTR() we, first, create an array where we define the actions for the database
aFields:={ { "MIKA" , "C", 30, 0, "#A" },; //field to add
{ "ZIKA" , "C", 13, 0, "#A" },; //field to add
{ "IME" , "N", 15, 0, "#C" },;//change field properties (in my example convert from "C" to "N"
{ "JEDINICA", , , , "#D" },; //field to delete
{ "ZA_MESEC", , , , "GMESEC" } }//field to be rename with
and then call, in my example
MODISTRU("arhiva",aFields)
so, in one pass with one function we add 2 fields, delete 1 and change 1 field name without losing of data.
As improvement using the standard MODIFY STRUCTURE VFP command, the renaming action must be a separate operation from the other actions. With the above function we achieve both in one function call.
If someone finds this idea useful, it's welcome to use it.
NOTE: This idea doesn't applies to VFP user only. It is supposed to bean utility that can be useful if integrated in a developer app to everyone who need it.
Best regards
Zoran
A code integration of a database managing utility by using only one function is not a new idea, at least for me. I saw that your code using an error handler is very compact and clean and I surely i will integrate it in mine, but is only part of the idea.
Database managers as dbase, fox and VFP changed trough their existance, but one thing is common for all and that is the way of how to create, manage and modify the dbf databases structure (using the MODIFY STRUCTURE command). Of course, trough time were added new field types and some visual options for managing the databases but the command itself does the same things all the way.
To be honest, at the time of my commit I didn't make the optimization of the final code yet, thing that you did for a part of the code.
A purpose of a "MODIFY STRUCTURE" command, or function, can be nearly defined as: "A command or function that provide the ability to the app developer to add and delete fields of an existing database, change their lengths and change their names and types.
The append command in my code called APPCONV is a part of my MODISTRU() function, but can be used separately as a standalone command with more options if we want only to append data and handling the possible errors.
It's not a problem to create a database but modify an existing one without using lot of code lines.
Examples of use of my MODISTRU() (a kind of MODIFY STRUCTURE command):
1. you need to create a temp database based on an existing database structure and need also some additional fields and maybe, after finished some operations on it, later, to flush it to replace a production database and maybe, without some unnecessary fields and, more worse, with a need to change some field name for some other app usage. All of this keeping the existing data.
2. you have some app a database update for the client, you need to alterate some database, by adding, deleting fields, etc.. Without some above mentioned command, for a databases structure update, you must use some updated temp bases and append the existing data to them without reported errors. For 1-2 databases we can do this manually, the problem emerge if we have bigger number of them multiplied by the number of clients.
With MODISTR() we, first, create an array where we define the actions for the database
aFields:={ { "MIKA" , "C", 30, 0, "#A" },; //field to add
{ "ZIKA" , "C", 13, 0, "#A" },; //field to add
{ "IME" , "N", 15, 0, "#C" },;//change field properties (in my example convert from "C" to "N"
{ "JEDINICA", , , , "#D" },; //field to delete
{ "ZA_MESEC", , , , "GMESEC" } }//field to be rename with
and then call, in my example
MODISTRU("arhiva",aFields)
so, in one pass with one function we add 2 fields, delete 1 and change 1 field name without losing of data.
As improvement using the standard MODIFY STRUCTURE VFP command, the renaming action must be a separate operation from the other actions. With the above function we achieve both in one function call.
If someone finds this idea useful, it's welcome to use it.
NOTE: This idea doesn't applies to VFP user only. It is supposed to bean utility that can be useful if integrated in a developer app to everyone who need it.
Best regards
Zoran
Zoran Sibinovic
Dec 24, 2015, 2:00:20 PM12/24/15
to Harbour Users
Hi Przemek,
in the attachment can be found a picture of fields types that can be used
in the attachment can be found a picture of fields types that can be used
A a notice of fixed-width:
blob - 4
currency - 8
date - 8
datetime - 8
double - 8, decimal not
general - 4
integer - 4
integer (autoinc) - 4
logical - 1
memo - 4
memo (binary) - 4
blob - 4
currency - 8
date - 8
datetime - 8
double - 8, decimal not
general - 4
integer - 4
integer (autoinc) - 4
logical - 1
memo - 4
memo (binary) - 4
since I use the basic set of fields: character, date, numeric, memo and logical,
I have to investigate the usage and conversion of the others, when I did I let you know.
If some harbourians already knows that, than they are welcome to write their
experience with missing fields characteristics.
Regards
Zoran
Reply all
Reply to author
Forward
0 new messages