#1175: Enhance dCursorMixin to support (1) editable and (2) compound primary
keys
--------------------------+-------------------------------------------------
Reporter: aecker | Owner: somebody
Type: enhancement | Status: closed
Priority: major | Milestone: 0.8.3
Component: db | Version: 0.8.4
Resolution: invalid | Keywords: editable multiple compound pk
--------------------------+-------------------------------------------------
Old description:
> First I have to say "'THANK YOU!! ' * 1000"... for to publish dabo to the
> community. Two weeks ago I found dabo via google because I had to make a
> little prototype for a project offer. Luckily I could spent some time to
> discover the dabo universe and I am so excited that I want help you where
> I can to maintain and enhance this fantastic framework.
>
> The prototype I had to make is for a management office where some hobby
> programmers / database administrators need to merge statistics from a
> group of companies. In the moment they enter and report on the data
> directly within the database admin/management tool. This is the main
> reason why they want to have meaningful and editable primary key values
> instead of (autoincrement) numbers, on some of the tables with compound
> PKs. When they buy it, I could publish all code to the community as an
> example dabo application after the project is finished.
>
> After I first created the database structure, run the dabo AppWizard and
> then started the generated applicaten I was really impressed about the
> functionality already built into dabo. All test data was showing fine,
> but when I then started to edit or add new records some strange errors
> occured. So I dived deeper...
>
> First I got the current revision 4664 out of SVN, then tried to fix the
> problems in the classes of my prototype application but it got ugly and I
> had to use a lot of private attributes of dCursorMixin (like
> cursor._mementos/._newRecords/._compoundKey). So finally I patched
> dCursorMixin and the code got more easy to maintain and understand.
> Please feel free to use this code into the code base and also to change
> and critize to fit dabo code conventions. Although I love python I can
> use it only rarely in my job (as Omnis Studio and .NET developer,..). so
> please bare with my python coding.
>
> == PATCHES FOR TO SUPPORT EDITABLE MULTIPLE PRIMARY KEY FIELDS ==
>
> A) Changed in dCursorMixin.setFieldValue() the "if fld == keyField:" code
> block (all until the respective "else:" code block) into this code:
>
> {{{
> if fld == keyField or fld in keyField:
> # Changing the key field(s)
> value, need to key the mementos on the new
> # value, not the old.
> Additionally, need to copy the mementos from the
> # old key value to the new one.
> if self._compoundKey:
> old_keyFieldValue =
> tuple([rec[k] for k in keyField])
> keyFieldValue =
> tuple([val if k == fld else rec[k] for k in keyField])
>
> }}}
> else:
>
> {{{
> old_keyFieldValue =
> old_val
> keyFieldValue = val
> # new records pk must be unique
> (at least within current dataset, filtered
> # datasets will throw db error if
> not unique)
> if
> (self._newRecords.has_key(old_keyFieldValue) \
> and
> self._getRowByPk(keyFieldValue) >= 0) \
> or
> self._newRecords.has_key(keyFieldValue):
> #raise
> dException.MissingPKException, _("The code/id/key value you entered
> already exists in the data set. val=") + str(val)
> return # best
> simply RETURN silently without changing cursor, biz.validateField()
> informs user (in most cases)
>
> old_mem =
> self._mementos.get(old_keyFieldValue, None)
> if old_mem is not None:
> self._mementos[keyFieldValue] = old_mem
> del
> self._mementos[old_keyFieldValue]
> if
> self._newRecords.has_key(old_keyFieldValue):
> self._newRecords[keyFieldValue] = None
> del
> self._newRecords[old_keyFieldValue]
>
> }}}
>
> B) Inserted into dCursorMixin.getFieldVal() after the code line:
>
> {{{
> rec = self._records[row]
>
> }}}
>
> this code block to support mult. field pk:
>
> {{{
> if isinstance(fld, basestring) and fld.find(",") >= 0:
> # convert also if multi PK KeyField value passed
> from bizObj layer
> fld = [f.strip() for f in fld.split(",")]
>
> }}}
>
> C) To warn user on duplicate pk I also had to add to Base.py bizobj class
> (maybe patch/include into dBizObj, what do you think? ... and what would
> be the right method/exception to notify the user from the framework
> level):
>
> {{{
> def validateField(self, fld, val):
> # If the new value is different from the current value, change it and
> also
> # update the mementos if necessary.
>
> row = self._CurrentCursor.RowNumber # MOVE TO
> dBizObj OR REPLACE PRIVATE ATTRIBUTES
> rec = self._CurrentCursor._records[row] # ..rec = self.Record?!?!?
> keyField = self._CurrentCursor.KeyField # ..used cursor
> prop. because biz.KeyField is comma sep str?!?!?
> old_val = rec[fld]
> # ..old_val = rec.getAttr(fld)
> if old_val != val:
> if fld == keyField or fld in keyField:
> # Changing the key field(s) value, need to key the mementos on
> the new
> # value, not the old. Additionally, need to copy the mementos
> from the
> # old key value to the new one.
> if self._CurrentCursor._compoundKey: # ..if
> isinstance(keyField, tuple):
> old_keyFieldValue = tuple([rec[k] for k in keyField])
> keyFieldValue = tuple([val if k == fld else rec[k] for k in
> keyField])
> else:
> old_keyFieldValue = old_val
> keyFieldValue = val
> # new records pk must be unique (at least within current dataset)
> if self._CurrentCursor._newRecords.has_key(old_keyFieldValue) \
> and self._CurrentCursor._getRowByPk(keyFieldValue) >= 0:
> return "Code/Id/PrimaryKey already exists in the dataset"
> elif self._CurrentCursor._newRecords.has_key(keyFieldValue):
> return "Duplicate Code/Id/PrimaryKey in another, new and
> unsaved record. Please enter unique value or delete the other new/unsaved
> record."
> return ""
> }}}
>
> With these patches the framework would support editable and
> compound primary keys. I first want to discuss this with you before I
> spend more time working this out, which could include also the following
> features:
>
> o add opt. fld+val arguments to dCursorMixin.pkExpression(self, rec=None,
> fld=None, val=None) and virtually overwrite the return value (or part of
> it) with passed val - to get a preview of the pk if val would be written
> to the database column specified by fld.
>
> o use extended method getPkExpression() in dCursorMixin.setFieldVal() and
> in Base.py (or dBizObj.fieldValidation() after code transfer from
> Base.py/validateField()) to minimize redundant code.
>
> o While investigation more on compound primary keys I found other places
> in dCursorMixin where compound primary keys are not supported. Not sure
> if the following extension of RemoteConnector.save() to support multiple
> field pk will not break the remote protocol.
>
> The extensoion could be done by:
>
> (1) in dCursorMixin.getRecordStatus()/_getNewRecordDiff() replace:
> {{{
> recs = [r for r in self._records if r[self._keyField] == pk]
> }}}
> with:
> {{{
> recs = [r for r in self._records if self.pkExpression(r) == pk]
> }}}
>
> (2) replace in dCursorMixin.getDataDiff().rowDiff():
>
> {{{
> ret[self._keyField] = pk
> }}}
> with:
> {{{
> if self._compoundKey:
> rec = [r for r in self._records if
> self.pkExpression(r) == pk][0]
> for fld in self._keyField:
> ret[fld] = rec[fld]
> else:
> ret[self._keyField] = pk
> }}}
>
> (3) in dCursorMixin.setDefaults() first remove the following local
> variables at the top:
>
> {{{
> keyField = self.KeyField
> keyFieldSet = False
> }}}
>
> then replace the last code part of the method:
>
> {{{
> if self._nullDefaults:
> for field in rec.keys():
> if field == keyField:
> continue
> self.setFieldVal(field, None)
> else:
> if keyField in vals.keys():
> # Must set the pk default value first,
> for mementos to be filled in
> # correctly.
> setDefault(keyField, vals[keyField])
> keyFieldSet = True
>
> for field, val in vals.items():
> if field == keyField and keyFieldSet:
> continue
> setDefault(field, val)
> }}}
>
> with something like:
>
> {{{
> if self._compoundKey:
> keyFields = [fld for fld in self.KeyField]
> else:
> keyFields = [self.KeyField]
> if self._nullDefaults:
> for field in rec.keys():
> if field not in keyFields:
> self.setFieldVal(field, None)
> else:
> # Must set the pk default value first, for
> mementos to be filled in
> # correctly.
> for field in keyFields:
> if field in vals:
> setDefault(field, vals[field])
> for field, val in vals.items():
> if field not in keyFields:
> setDefault(field, val)
> }}}
>
> (4) not sure if fld[2] in dCursorMixin._setTable() contains True for a pk
> field in all DBs, if yes then we could replace:
>
> {{{
> self._keyField = [fld[0] for fld in
> self.getFields(table)
> if fld[2] ][0]
> }}}
>
> with:
> {{{
> kf = tuple([fld[0] for fld in
> self.getFields(table)
> if fld[2] ])
> if len(kf) == 1:
> kf = kf[0]
> self._keyField = kf
> }}}
>
> Since long time I want to participate/support an open source project and
> dabo is exactly what I am looking for. Unfortunately I have still no
> internet access from my home ... far out, only from working place ... but
> my best boss of the world allows us to stay late and use our equipment
> privatly. I also will motivate my collueges as good as I can to use d a
> b o .
New description:
First I have to say "'THANK YOU!! ' * 1000"... for to publish dabo to the
community. Two weeks ago I found dabo via google because I had to make a
little prototype for a project offer. Luckily I could spent some time to
discover the dabo universe and I am so excited that I want help you where
I can to maintain and enhance this fantastic framework.
The prototype I had to make is for a management office where some hobby
programmers / database administrators need to merge statistics from a
group of companies. In the moment they enter and report on the data
directly within the database admin/management tool. This is the main
reason why they want to have meaningful and editable primary key values
instead of (autoincrement) numbers, on some of the tables with compound
PKs. When they buy it, I could publish all code to the community as an
example dabo application after the project is finished.
After I first created the database structure, run the dabo AppWizard and
then started the generated applicaten I was really impressed about the
functionality already built into dabo. All test data was showing fine, but
when I then started to edit or add new records some strange errors
occured. So I dived deeper...
First I got the current revision 4664 out of SVN, then tried to fix the
problems in the classes of my prototype application but it got ugly and I
had to use a lot of private attributes of dCursorMixin (like
cursor._mementos/._newRecords/._compoundKey). So finally I patched
dCursorMixin and the code got more easy to maintain and understand. Please
feel free to use this code into the code base and also to change and
critize to fit dabo code conventions. Although I love python I can use it
only rarely in my job (as Omnis Studio and .NET developer,..). so please
bare with my python coding.
== PATCHES FOR TO SUPPORT EDITABLE MULTIPLE PRIMARY KEY FIELDS ==
A) Changed in dCursorMixin.setFieldValue() the "if fld == keyField:" code
block (all until the respective "else:" code block) into this code:
{{{
if fld == keyField or fld in keyField:
# Changing the key field(s) value,
need to key the mementos on the new
# value, not the old.
Additionally, need to copy the mementos from the
# old key value to the new one.
if self._compoundKey:
old_keyFieldValue =
tuple([rec[k] for k in keyField])
keyFieldValue = tuple([val
if k == fld else rec[k] for k in keyField])
}}}
else:
{{{
old_keyFieldValue =
old_val
keyFieldValue = val
# new records pk must be unique
(at least within current dataset, filtered
# datasets will throw db error if
not unique)
if
(self._newRecords.has_key(old_keyFieldValue) \
and
self._getRowByPk(keyFieldValue) >= 0) \
or
self._newRecords.has_key(keyFieldValue):
#raise
dException.MissingPKException, _("The code/id/key value you entered
already exists in the data set. val=") + str(val)
return # best
simply RETURN silently without changing cursor, biz.validateField()
informs user (in most cases)
old_mem =
self._mementos.get(old_keyFieldValue, None)
if old_mem is not None:
self._mementos[keyFieldValue] = old_mem
del
self._mementos[old_keyFieldValue]
if
self._newRecords.has_key(old_keyFieldValue):
self._newRecords[keyFieldValue] = None
del
self._newRecords[old_keyFieldValue]
}}}
B) Inserted into dCursorMixin.getFieldVal() after the code line:
{{{
rec = self._records[row]
}}}
this code block to support mult. field pk:
{{{
if isinstance(fld, basestring) and fld.find(",") >= 0:
# convert also if multi PK KeyField value passed
from bizObj layer
fld = [f.strip() for f in fld.split(",")]
}}}
C) To warn user on duplicate pk I also had to add to Base.py bizobj class
(maybe patch/include into dBizObj, what do you think? ... and what would
be the right method/exception to notify the user from the framework
level):
{{{
def validateField(self, fld, val):
# If the new value is different from the current value, change it and
also
# update the mementos if necessary.
row = self._CurrentCursor.RowNumber # MOVE TO
dBizObj OR REPLACE PRIVATE ATTRIBUTES
rec = self._CurrentCursor._records[row] # ..rec = self.Record?!?!?
keyField = self._CurrentCursor.KeyField # ..used cursor
prop. because biz.KeyField is comma sep str?!?!?
old_val = rec[fld]
# ..old_val = rec.getAttr(fld)
if old_val != val:
if fld == keyField or fld in keyField:
# Changing the key field(s) value, need to key the mementos on the
new
# value, not the old. Additionally, need to copy the mementos from
the
# old key value to the new one.
if self._CurrentCursor._compoundKey: # ..if
isinstance(keyField, tuple):
old_keyFieldValue = tuple([rec[k] for k in keyField])
keyFieldValue = tuple([val if k == fld else rec[k] for k in
keyField])
else:
old_keyFieldValue = old_val
keyFieldValue = val
# new records pk must be unique (at least within current dataset)
if self._CurrentCursor._newRecords.has_key(old_keyFieldValue) \
and self._CurrentCursor._getRowByPk(keyFieldValue) >= 0:
return "Code/Id/PrimaryKey already exists in the dataset"
elif self._CurrentCursor._newRecords.has_key(keyFieldValue):
return "Duplicate Code/Id/PrimaryKey in another, new and unsaved
record. Please enter unique value or delete the other new/unsaved record."
return ""
}}}
With these patches the framework would support editable and
compound primary keys. I first want to discuss this with you before I
spend more time working this out, which could include also the following
features:
o add opt. fld+val arguments to dCursorMixin.pkExpression(self, rec=None,
fld=None, val=None) and virtually overwrite the return value (or part of
it) with passed val - to get a preview of the pk if val would be written
to the database column specified by fld.
o use extended method getPkExpression() in dCursorMixin.setFieldVal() and
in Base.py (or dBizObj.fieldValidation() after code transfer from
Base.py/validateField()) to minimize redundant code.
o While investigation more on compound primary keys I found other places
in dCursorMixin where compound primary keys are not supported. Not sure if
the following extension of RemoteConnector.save() to support multiple
field pk will not break the remote protocol.
The extensoion could be done by:
(1) in dCursorMixin.getRecordStatus()/_getNewRecordDiff() replace:
{{{
recs = [r for r in self._records if r[self._keyField] == pk]
}}}
with:
{{{
recs = [r for r in self._records if self.pkExpression(r) == pk]
}}}
(2) replace in dCursorMixin.getDataDiff().rowDiff():
{{{
ret[self._keyField] = pk
}}}
with:
{{{
if self._compoundKey:
rec = [r for r in self._records if
self.pkExpression(r) == pk][0]
for fld in self._keyField:
ret[fld] = rec[fld]
else:
ret[self._keyField] = pk
}}}
(3) in dCursorMixin.setDefaults() first remove the following local
variables at the top:
{{{
keyField = self.KeyField
keyFieldSet = False
}}}
then replace the last code part of the method:
{{{
if self._nullDefaults:
for field in rec.keys():
if field == keyField:
continue
self.setFieldVal(field, None)
else:
if keyField in vals.keys():
# Must set the pk default value first, for
mementos to be filled in
# correctly.
setDefault(keyField, vals[keyField])
keyFieldSet = True
for field, val in vals.items():
if field == keyField and keyFieldSet:
continue
setDefault(field, val)
}}}
with something like:
{{{
if self._compoundKey:
keyFields = [fld for fld in self.KeyField]
else:
keyFields = [self.KeyField]
if self._nullDefaults:
for field in rec.keys():
if field not in keyFields:
self.setFieldVal(field, None)
else:
# Must set the pk default value first, for
mementos to be filled in
# correctly.
for field in keyFields:
if field in vals:
setDefault(field, vals[field])
for field, val in vals.items():
if field not in keyFields:
setDefault(field, val)
}}}
(4) not sure if fld[2] in dCursorMixin._setTable() contains True for a pk
field in all DBs, if yes then we could replace:
{{{
self._keyField = [fld[0] for fld in
self.getFields(table)
if fld[2] ][0]
}}}
with:
{{{
kf = tuple([fld[0] for fld in
self.getFields(table)
if fld[2] ])
if len(kf) == 1:
kf = kf[0]
self._keyField = kf
}}}
Since long time I want to participate/support an open source project and
dabo is exactly what I am looking for. Unfortunately I have still no
internet access from my home ... far out, only from working place ... but
my best boss of the world allows us to stay late and use our equipment
privatly. I also will motivate my collueges as good as I can to use d a b
o .
Comment (by paul):
I agree with Ed. Meaningless PK fields should be hidden from the users but
always there, and candidate fields can be exposed to the users to make
them happy.
Changing PK values really can't be allowed at the application level, but
if you really want to do this, do it at your database level and you better
have all the database triggers set up to update the foreign keys in other
tables, etcetera. But in my opinion you are shooting yourself in the foot
by allowing the pk value to change arbitrarily, especially by a user.
Please don't be discouraged; it is obvious you've done a lot of work
getting this working for your case. We just can't put it into Dabo.
--
Ticket URL: <http://trac.dabodev.com/ticket/1175#comment:5>
Dabo Trac <http://trac.dabodev.com>
Trac Page for Dabo
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-dev
Searchable Archives: http://leafe.com/archives/search/dabo-dev
This message: http://leafe.com/archives/byMID/[EMAIL PROTECTED]
