sqlobject-discuss Mailing List for SQLObject (Page 429)

Hallo Ian,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> I think I fixed the caching problem you've been having -- there was some
> old code based on the lazy fetching of columns that was causing a
> problem.  

I did an update but I still get the same wrong behaviour, when I turn
caching on. To clarify my problem: 

It only (AFAIK) affects Joins. To give an example using the people.py:
A Page should make editing the PhoneNumbers possible:

	pid = getPersonIDSomehow()

	p = Person(pid)
	for number in p.phoneNumbers:
		self.writeln("""
		<form>
		<input type="hidden" name="NumberID" value="%d">
		<input type=input name="PhoneNumber" value="%s">
		<input type=input name="Type" value="%s"
		<input type=submit name="_action_Change" value="Change">
		</form>
		""" % (number.id, number.PhoneNumber, number.PhoneType) )

	def Change(self):
		req   = self.request()
		id    = int(req.value('NumberID', None))
		num   = req.value('PhoneNumber', None)
		type  = req.value('Type', None)
		
		p             = PhoneNumber(id)
		p.phoneNumber = num
		p.phoneType   = type

If Change() is called, the database actually gets updated, but if 
the edit form is showed in the response, it doesn't reflect the
changes in the list of PhoneNumbers.

As I said, only Joins like p.phoneNumbers are affected by this. I can,
and always could edit "Person"s directly without the caching errors,
only their Phones are messed up.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

On Mon, 2003-05-05 at 16:57, Nick wrote:
> On Mon, 2003-05-05 at 14:39, Ian Bicking wrote:
> > Validation may be important when fetching from the database, if
> > SQLObject doesn't have total control of the database, and the
> > constraints aren't as restrictive as the validation.  That might be an
> > obscure case, though.
> 
> Conversion is important, especially for DBM and SQLite, which only store
> strings, coming out of the database.  Hoever, *validating* data coming
> back out of the database can be devastating to an application if it
> can't control the data going into the database from another source. 
> You'd never be able to retrieve a row, even to fix the data, if you got
> an exception because the data already in there is bad.

DBMConnection actually is more shelve-like, pickling all its data, so
you get full types in and out.  But if you want to move to another
backend, validation to restrict that flexibility would serve you well,
so it's not beyond all of this.

At first I thought: yes, validating input from the backend isn't very
useful, since there's no good way to recover in the case of invalid
input.

But on second thought, one design decision that I feel is good in
Validator (and its predicessors) is that validation and conversion are a
single process, because they are whether you want them to be or not.

The easiest example would be for an integer (from a string).  The
converter would be int() itself (convenient :).  But then there's an
implicit validation too, because int("abc") isn't going to work.  You
will get an exception.

So you need a way to handle these exceptions, whether or not you want
to.  How you do that isn't clear to me... in some cases they may be an
example of corrupt data in the database (an all too common occurance). 
Throwing an exception is a pain, but really the only thing you can do at
that point is to provide an alternate mechanism so a person can fix
that.  Doing direct queries is one possibility right now.

In another instance, your validation is too tight.  It's a bug, and
should be fixed in your code.  You should always be able to do 
obj.x = obj.x, and I think it deserves an error if something gets in the
way of doing that (and if the validation is really odd or eclectic, you
should be overriding _get_x and _set_x so you can be as eclectic as you
want).  That error will show up eventually -- better sooner than later.

But, in both cases it's a real pain to deal with it automatically or
programmatically.  But it's not clear to me how it should be dealt with.

Using Validator you can actually be explicit about it, like:
  Validator.Int(ifInvalid=None)
So "x" becomes None when you validate it.  Or you could do:
  Validator.Any(Validator.Int(), Validator.Validator())
Which if an input can't be turned into an integer, it returns it as is
(Validator.Validator incidentally being the identity function of
validators). 

That said, I'm still open to more ideas, but handling exceptional
behavior here is a pain in the but, most definitely.  Maybe Validator
could distinguish between validating input and validating output (which
it doesn't currently -- it just distinguishes conversion).  Then you
could at least choose.

I think with some more long-winded programming, even with its features
Validator should be fairly efficient (i.e., multiple implementations of
attemptToPython and attemptFromPython, so you only pay for the features
you use).  Of course, I don't have any performance data at all, so maybe
that's premature optimization...

  Ian

On Mon, 2003-05-05 at 14:39, Ian Bicking wrote:
> Validation may be important when fetching from the database, if
> SQLObject doesn't have total control of the database, and the
> constraints aren't as restrictive as the validation.  That might be an
> obscure case, though.

Conversion is important, especially for DBM and SQLite, which only store
strings, coming out of the database.  Hoever, *validating* data coming
back out of the database can be devastating to an application if it
can't control the data going into the database from another source. 
You'd never be able to retrieve a row, even to fix the data, if you got
an exception because the data already in there is bad.

Nick

cvs.sf.net is back up...

Hallo,
Ian Bicking hat gesagt: // Ian Bicking wrote:

> I think I fixed the caching problem you've been having -- there was some
> old code based on the lazy fetching of columns that was causing a
> problem.  

Thanks, Ian. I'll give it a try when it's online. 

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

On Mon, 2003-05-05 at 13:49, Nick wrote:
> I haven't seen the form stuff you're talking about (cvs.sf.net doesn't
> respond at the moment), but you only really need to do the conversion
> when updating the db, which is another case for pythonic transaction
> handling.  Validation in most cases is lightweight, even if you're only
> going to match against a re, but necessary if you want to catch problems
> before they happen.  If you didn't want validation, should should have
> used Col.  Either that, or you can subclass XxxCol and override the
> validator to pass, assuming you move the validator into the class.

It should go both ways, so you can define the class of the result --
maybe unpickling a string, or creating a Point class, etc.  Essentially
it came down to a Validator object with two defined methods --
fromPython and toPython.  But there were a bunch of other options that
may not apply to SQLObject.

Validation may be important when fetching from the database, if
SQLObject doesn't have total control of the database, and the
constraints aren't as restrictive as the validation.  That might be an
obscure case, though.

  Ian

I think I fixed the caching problem you've been having -- there was some
old code based on the lazy fetching of columns that was causing a
problem.  I fixed it just after cvs.sf.net went down... I'll give you an
update when I get it put in.

  Ian

On Mon, 2003-05-05 at 12:52, Ian Bicking wrote:
> On Wed, 2003-04-30 at 12:41, Nick wrote:
> > 3) for each db api, there needs to be convert-from-result-to-python and
> > convert-from-python-to-sql-value functions as part of the Col class,
> > encapsulated by the mechanism described in 2.
> >
> I've been working on a validation/conversion system for a form
> processor.  I'm fairly happy with what I've got, at least for forms, and
> it could be applied to SQLObject -- though I wonder if it's too heavy,
> since it would be invoked for every get or set of a column.  It makes
> composition and definition of validators easier, but maybe that's not as
> important for something like SQLObject, where there's a lot more work in
> defining an object than just its validators.

I haven't seen the form stuff you're talking about (cvs.sf.net doesn't
respond at the moment), but you only really need to do the conversion
when updating the db, which is another case for pythonic transaction
handling.  Validation in most cases is lightweight, even if you're only
going to match against a re, but necessary if you want to catch problems
before they happen.  If you didn't want validation, should should have
used Col.  Either that, or you can subclass XxxCol and override the
validator to pass, assuming you move the validator into the class.

Nick

On Thu, 2003-05-01 at 12:20, Luke Opperman wrote:
> > Yes, but "NULL" is just computerese for "no value". :)
> 
> Maybe in your world. :) Ok, I guess I'll concede for now. Was this
> whole discussion just about whether notNull=False should imply
> default=None? I'm still not convinced that my statement: "a field
> that CAN have a NULL (notNull=False), but that you still want
> explicitly declared (no default)"  is useless or gibberish, but
> that's probably such a rare case that .... screw 'em.

I don't agree with this.  To me NULL (and None) has a very specific
meaning, though that meaning is usually context-specific (not specified,
not known, not applicable, etc).  It isn't the same as "default".

Python does not use None as a default (except the get method on
dictionaries).  Instead None must be specified explicitly, in argument
lists or elsewhere.  I think this is the appropriate behavior for
SQLObject to take on as well.  Writing "default=None" just doesn't seem
that difficult to me.

Now, I'm not entirely convinced on this, but if we have a "default"
argument to Col anyway, I'd prefer to be explicit about it.

  Ian

On Wed, 2003-04-30 at 15:49, Ian Bicking wrote:
> I'm not sold on using Python types, though I suspect there will be more
> confusion if I use SQL types (like DateTimeCol in MySQL, vs.
> TimestampCol in Postgres).  We can all agree on the Python types,
> because there's only one Python, but there's many databases, not all of
> which adhere to the SQL standard (DBMConnection obviously does not, for
> instance, nor would MetaKit).

I'm now feeling more convinced that I should stick to using Python type
names, not SQL names.  In particular, I've been working on this form
stuff, and handling situations where I'm translating from HTTP variables
(i.e., strings) to Python types.  A lot of the same concepts apply to
getting data from SQL, or XMLRPC -- so I'd like for their to be one way
of specifying the type/constraint information.  Python is the only
option, since it's the one thing all the data sources have in common --
the Python destination.

  Ian

On Wed, 2003-04-30 at 16:11, Nick wrote:
> On Wed, 2003-04-30 at 15:46, Ian Bicking wrote:
> > I would subclass RelatedJoin, changing the performJoin method.  In
> > general I think any table that doesn't have a key should be represented
> > with a join, and rows in that table won't be turned into full objects. 
> > That means they won't be mutable, but it shouldn't be a problem to just
> > add and delete rows instead of changing them.
> 
> Yes, that's pretty much how I did it with my old framework.  However,
> this can create a *lot* of write queries if you're changing a bunch of
> different columns.  Which goes back to the whole transaction discussion.

Another option might be an extend method for joins, e.g., addAddress()
and extendAddress().  That could be turned into a single INSERT.  For
deleting, I'm less sure.  Though clearAddresses() might be a
possibility.  

There's also the possibility of making the (example) .addresses into an
object more like what you get from .select().  That is, it would allow
list operations, and batch them.  So you'd get
somePerson.addresses.extend(addressList), and somePerson.addresses would
be an iterator that also had list-like methods (but you'd have to use
list() to actually get a list from it).

  Ian

On Thu, 2003-05-01 at 10:44, Nick wrote:
> If I want to keep a persistant db connection but not cache, I can see
> how I can do this by saying:
> 
> class._connection.cache = CacheSet()
> 
> but I'm not sure if mucking with the internals like that is the right
> way to go.  Is there some other way to handle this that I'm not seeing? 
> If not, should there be?

Caches should be stored entirely per-connection, including
per-transaction (which works by using a separate connection).  This
happens in SQLObject.py:315 -- see if that's what you're thinking of.

  Ian

On Mon, 2003-05-05 at 10:03, Luke Opperman wrote:
> Hello -
> 
> Having some issues with application of styles and changed behavior
> from the previous default 'style'. (This is all from CVS late last
> week..)
> 
> 1. Double capitals in table and column names are treated differently
> from before: ie, 'PProductXType' used to generate 'p_product_x_type',
> now generates 'pproduct_xtype'. It's not a very pretty name by
> itself, but the new conversion is fairly unexpected to me regardless.
> Simple changes to the RE ('[A-Z]' instead of '[A-Z]+') and not
> treating tablenames different from column names fixes these. In
> general I guess we may start using our own style object, but I'm just
> concerned that the new default Style breaks old (simpler) behavior.

Okay, that should be fixed.  I wanted it to be like HTMLName ->
html_name, which should be the present behavior (and PProductXType ->
p_product_x_type)

> 2. '_defaultOrder' in 0.3 and in my original suggestion would accept
> either database-style or python-style names. It now assumes it is in
> database-style. Simple fix is to put check that used to be in the
> metaclass back in (modified to use styles):

This should be fixed too.  More generally, it checks if you give a
column name in the orderBy argument, and if so gets the .dbName.

  Ian

On Wed, 2003-04-30 at 12:41, Nick wrote:
> [ earlier discussion about implementing different SQL types and
>   converting them to Python types ]
> 
> I've been looking at the code for Constraints and Col, and it looks to
> me like there needs to be a distiction between real constraints
> (MaxLength, Unique, Not Null, for example) and type checking/casting. 
> It's a little mixed right now.  In terms of type checking and
> conversion, I would propose:
> 
> 1) make validators part of Col instead of just random functions in
> Constraints that get referenced in Col.

Yes, that's possible, i.e., StringCol checks for strings (or maybe calls
str() on Python objects, for instance, or checks the length).  And, I
suppose, you could add new types for specific requirements, like numbers
in a certain range (e.g. IntCol(min=0))

By making column types and validators separate it should be possible to
use richer checks on objects.  

> 2) at the rate it's going, there are going to be a lot of parallel
> _mysql, _postgres, _sqlite, etc. functions for every operation under
> Col.  There needs to be a way to bundle a bunch of functions for the
> particular db api, probably related to whatever _connection type you're
> using.

It's challenging, because there's more than one way things can be built
out -- types may be added over time, or databases may be added.  

> 3) for each db api, there needs to be convert-from-result-to-python and
> convert-from-python-to-sql-value functions as part of the Col class,
> encapsulated by the mechanism described in 2.
> 
> I've got some ideas how to do this, but I don't want to go ripping
> through the code if you're going somewhere else with this.

I've been working on a validation/conversion system for a form
processor.  I'm fairly happy with what I've got, at least for forms, and
it could be applied to SQLObject -- though I wonder if it's too heavy,
since it would be invoked for every get or set of a column.  It makes
composition and definition of validators easier, but maybe that's not as
important for something like SQLObject, where there's a lot more work in
defining an object than just its validators.

Anyway, I'd be interested on some feedback on it.  You can see the
system here:
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/webware-sandbox/Sandbox/ianbicking/FormEncode/

Validator.py more specifically has the validation code.  Doc strings may
not be up to date (it's based on previous system, and the method names
have been changed -- attemptToPython and attemptFromPython are public
methods) 

  Ian

On Wed, 2003-04-30 at 23:34, David M. Cook wrote:
> I think Cols should have an attribute for database-specific type (if
> available).  This allows querying for a database type that may be
> significant to your UI, though not having a meaning in Python or not yet
> being handled by a constraint/validation system.

Sure.  There's now a sqlType keyword argument to Col that should do the
trick. 

  Ian

Hello -

Having some issues with application of styles and changed behavior
from the previous default 'style'. (This is all from CVS late last
week..)

1. Double capitals in table and column names are treated differently
from before: ie, 'PProductXType' used to generate 'p_product_x_type',
now generates 'pproduct_xtype'. It's not a very pretty name by
itself, but the new conversion is fairly unexpected to me regardless.
Simple changes to the RE ('[A-Z]' instead of '[A-Z]+') and not
treating tablenames different from column names fixes these. In
general I guess we may start using our own style object, but I'm just
concerned that the new default Style breaks old (simpler) behavior.

2. '_defaultOrder' in 0.3 and in my original suggestion would accept
either database-style or python-style names. It now assumes it is in
database-style. Simple fix is to put check that used to be in the
metaclass back in (modified to use styles):

line 135ish:
  if hasattr(newClass, '_defaultOrder') and newClass._defaultOrder:
      if newClass._defaultOrder != \
newClass._style.pythonAttrToDBColumn(newClass._defaultOrder):
          newClass._defaultOrder = \
newClass._style.pythonAttrToDBColumn(newClass._defaultOrder)


However, this will only work in the general case if Style functions
are discrete: pythonAttrToDBColumn(pythonAttrToDBColumn(name))
returns the same value as one call (in the default style, this is
accomplished because dbnames never have capitals, and python names
never have underscores, but it's a rather strict requirement).
Otherwise, we'd need an alternate way to know whether a given
identifier is in db or python form.

I suppose the following mostly works (in shorter pseudo-names):

  if name != pythonToDB(name) and name != dbToPython(name):
      ## then we just have to assume something
      ## as the conversion routines are not 'safe'
      pass ## or name = pythonToDB(name) if we assume pythonstyle, etc
  else:
      if name != pythonToDB(name):
          name = pythonToDB(name)

Part of this question is the same old: do we assume dbstyle or
pythonstyle in general? I've always thought of _defaultOrder as
specifying a column, so it made sense to have it in pythonstyle
(especially now that I can simply redefine my Style object if things
change), but I can also see it as similar to _idName or _table, which
are dbstyle (but I hopefully see these going away as Styles are used,
so...)

- Luke

On Fri, 2003-05-02 at 14:17, Mike Hostetler wrote:
>   INSERT INTO site_data (category, link, description, title, date,
> site_id) VALUES ('tech', 'http://slashdot.org/article.pl?sid=03/05/02/1645224', 'Smaz writes "Apparently Carnegie Mellon has set up a Hall of Fame for robots and their inventors. Wonder if it\'ll have the pull of a RnR Hall of Fame or
> ...', 'Robot Hall of Fame', '2003-05-02T18:15:48+00:00', 1)
> 
> I'm not sure what ":" in the statement SQLite doesn't like . . . I can
> use the same data with PostgreSQL.

My guess is it doesn't like \', but would prefer ''.  Try changing line
76 of SQLBuilder.py:

# original:
('\'', '\\\'')
# with:
("'", "''")

If that works I'll just check that change into CVS.

  Ian

I'm sure this is really a problem with SQLite or PySQLite, but since I'm
using SQLObject . . . .

When I try to insert a value into SQLite, I get the following message:

  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
713, in new
    inst._SO_finishCreate()
  File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line
733, in _SO_finishCreate
    names, values)
  File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
128, in queryInsertID
    return self._runWithConnection(self._queryInsertID, table, idName,
names, values)
  File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
72, in _runWithConnection
    val = meth(conn, *args)
  File "/usr/lib/python2.2/site-packages/SQLObject/DBConnection.py", line
497, in _queryInsertID
    c.execute(q)
  File "/usr/lib/python2.2/site-packages/sqlite/main.py", line 247, in
execute
    self.rs = self.con.db.execute(SQL)
_sqlite.DatabaseError: unrecognized token: ":"

I edited the DBConnection.py to print out the SQL statement before
running it -- that statement it:

  INSERT INTO site_data (category, link, description, title, date,
site_id) VALUES ('tech', 'http://slashdot.org/article.pl?sid=03/05/02/1645224', 'Smaz writes "Apparently Carnegie Mellon has set up a Hall of Fame for robots and their inventors. Wonder if it\'ll have the pull of a RnR Hall of Fame or
...', 'Robot Hall of Fame', '2003-05-02T18:15:48+00:00', 1)

I'm not sure what ":" in the statement SQLite doesn't like . . . I can
use the same data with PostgreSQL.

If you could shed any light on this, I'd really appreciate it.

-- mikeh

-- 

On 05/01/03 12:20, Luke Opperman wrote:
> 
> > Yes, but "NULL" is just computerese for "no value". :)
> 
> Maybe in your world. :) Ok, I guess I'll concede for now. Was this
> whole discussion just about whether notNull=False should imply
> default=None? I'm still not convinced that my statement: "a field
> that CAN have a NULL (notNull=False), but that you still want
> explicitly declared (no default)"  is useless or gibberish, but
> that's probably such a rare case that .... screw 'em.

Optimizing for the common case. :)

Not forcing the programmer to specify default = None can save a heck
of a lot of typing, and happily keep SQLObject completely unsurprising.

-- 
Brad Bollenbach
BBnet.ca

On Thu, 2003-05-01 at 12:08, Luke Opperman wrote:
> _connection = WhateverConnection(...., cacheValues=False)
> 
> is that all you're looking for? Maybe i misread your question.

No... I *do* want to cache objects for a particular session, but between
sessions I want a new cache.

Nick

> Yes, but "NULL" is just computerese for "no value". :)

Maybe in your world. :) Ok, I guess I'll concede for now. Was this
whole discussion just about whether notNull=False should imply
default=None? I'm still not convinced that my statement: "a field
that CAN have a NULL (notNull=False), but that you still want
explicitly declared (no default)"  is useless or gibberish, but
that's probably such a rare case that .... screw 'em.

It only comes up because "default" is overloaded to both provide a
default value and to control explict definition in the object
interface, but I can't think of a non-rediculous way to separate
these.

- Luke

_connection = WhateverConnection(...., cacheValues=False)

is that all you're looking for? Maybe i misread your question.

- Luke

Quoting Nick <ni...@dd...>:

> If I want to keep a persistant db connection but not cache, I can
> see
> how I can do this by saying:
>
> class._connection.cache = CacheSet()
>
> but I'm not sure if mucking with the internals like that is the
> right
> way to go.  Is there some other way to handle this that I'm not
> seeing?
> If not, should there be?
>
> Nick
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>


--
 i find your contempt for naked feet curious.

If I want to keep a persistant db connection but not cache, I can see
how I can do this by saying:

class._connection.cache = CacheSet()

but I'm not sure if mucking with the internals like that is the right
way to go.  Is there some other way to handle this that I'm not seeing? 
If not, should there be?

Nick

On 05/01/03 09:29, Luke Opperman wrote:
> 
> > To me:
> >
> >     default=None
> >
> > reads essentially "if no value is specified, this column has no
> > value", which is redundant. I think it would be reasonable for
> > SQLObject to internalize the default=None
> 
> To me, "None is NULL" for SQLObject, so it reads "if no value is
> specified, this column is NULL".  notNull=False is still independent

Yes, but "NULL" is just computerese for "no value". :)

> of this: I take default to mean "you don't need to be explicit in
> setting this field, we have a default". You could easily have a field
> that CAN have a NULL (notNull=False), but that you still want people
> to explicitly declare. In my mind.

And "allowed to be null" is computerese for "allowed to have no
value". "Allowed to have no value, except you have to specify some
default value" doesn't make sense, in my mind. What's the point of
allowing something to have no value, but forcing you to specify a
value (even if just as a default)?

-- 
Brad Bollenbach
BBnet.ca

> To me:
>
>     default=None
>
> reads essentially "if no value is specified, this column has no
> value", which is redundant. I think it would be reasonable for
> SQLObject to internalize the default=None

To me, "None is NULL" for SQLObject, so it reads "if no value is
specified, this column is NULL".  notNull=False is still independent
of this: I take default to mean "you don't need to be explicit in
setting this field, we have a default". You could easily have a field
that CAN have a NULL (notNull=False), but that you still want people
to explicitly declare. In my mind.

- Luke