sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] Re: SQLObject

[Ian B. <ia...@co...>]

On Thu, 2003-04-10 at 00:38, Edmund Lian wrote:
> Composite keys are used an awful lot in any schema of moderate complexity,
> and they are important (when used with foreign key constraints) for
> enforcing relational integrity. If the aim is for SQLObject to support
> complex projects, then composite key support would be pretty important.

Can you describe some situations where they'd be used?  For instance, if
either key was modified at any time that'd cause a lot of problems for
SQLObject.  If not, then what would be the problem with a third simple
ID/PRIMARY KEY column?

  Ian

Re: [SQLObject] ANN: SQLObject 0.3

[Ian B. <ia...@co...>]

On Wed, 2003-04-09 at 02:52, Bud P.Bruegger wrote:
> * createTable(ifNotExists=True):
>   I understand that the default (False) means that tables are created
>   in any case, even if they exist already.  Wouldn't it be a better
>   default to create them only if they don't exist already?  (Does it
>   raise an exception if already existing tables are re-created or does
>   it simply drop the old ones? -- maybe you could add that to the
>   doc).  My rational for asking for this default is that it seems
>   safer and the option could possibly be called "force" instead of
>   ifNotExists--that seems closer to what I would expect (at least in a
>   Unix environment).
> 
> * dropTable(ifExists=..):
>   is this really necessary?  What happens if it does not exist
>   (nothing or exception)?  Maybe there is something I don't
>   understand, but I would be happy that after calling dropTable, the
>   table is gone (or else, an exception is raised) no matter what the
>   state was before.  (I try to avoid methods with pre-conditions as
>   much as ever possible)

To me these should act something like mkdir and rmdir do in the
filesystem, or like the CREATE and DROP commands do in the database. 
You should *know* that the tables do or do not exist at the time you run
these commands, and if things are not as you expect you should get an
error (actually generated by the database).

In particular for testing or example situations, where you expect for
junk to have been left around, being tolerant is well and good.  But by
default I don't believe they should be tolerant.

> * dropTable(dropJoinTables=...):
>   I am wondering whether it makes sense to keep joinTables around
>   since really they make reference to rows in the table and leaving
>   them around leads to dangling references.  I noted that the SQL you
>   create for relations does not seem to define foreign keys (at least
>   in an example given in the documentation of an earlier version).
>   But if you had the DBMS take care of these, it would probably even
>   complain to leave the joinTable while removing the other table...

Yes, I just left that option in for no particular reason.  There's
probably not a good reason why you'd drop the join but not the table.

Right now the join table is made arbitrarily by one of the SQLObject
classes (whichever one comes alphabetically first, I think), and dropped
along with that same table.  Really I suppose it should be created
whenever the second table is created, and dropped with the first table
is dropped.

  Ian

Re: [SQLObject] Re: SQLObject

[Edmund L. <el...@in...>]

Steve Holden wrote:

>> 5. Your assertion that MySQL does not support transactions is out of
date,
>> since they can be supported by databases built from InnoDB tables, I
believe
>> (or have I imagined this?).

Be aware that InnoDB is not free for commercial use, unlike PostgreSQL,
Firebird, SAPDB, etc. If you use InnoDB in any product that the user must
pay you for, you must buy a commercial license.

Tacking on something that does transactions does not make MySQL transaction
safe. See
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci788645,00.html?FromTaxonomy

%2Fpr%2F284872


Ian Bicking wrote:

>I don't know.  I like single, simple primary keys.  They work well.  If
>the table is really so minimal that it can't stand to have an extra
>field for the id, then I suspect a SQLObject class is too heavy for it
>(like with the join tables).  Of course, when adapting to a pre-existing
>schema you might not have as many options.  Maybe it wouldn't be that
>hard to fudge it (maybe make the ID into a tuple), but SQLObject really
>expects for each object to have a single ID.

Composite keys are used an awful lot in any schema of moderate complexity,
and they are important (when used with foreign key constraints) for
enforcing relational integrity. If the aim is for SQLObject to support
complex projects, then composite key support would be pretty important.

...Edmund.

[SQLObject] Re: SQLObject

[Ian B. <ia...@co...>]

I hope you don't mind me copying you to the list.

On Wed, 2003-04-09 at 04:15, Steve Holden wrote:
> Ian:
> 
> I've just spent a little time looking at SQLObject, as I'm always interested
> in object-relational mapping frameworks. It's a nice idea, and please bear
> in mind these remarks relate to my own use of it. Whether you take them
> seriously enough to think about modifying something that clearly works for
> you is entirely up to you, and this isn't intended as criticism of your
> efforts. At leat you know that someone's taken ther trouble to review
> SQLObject. Specifically:
> 
> 1. You document the magic _idName, but people like me who only read the code
> samples end up using _id instead, and getting and "Unknown column id" SQL
> error.

Yes, you weren't the only one caught by this.

> 2. I found the name translations imposed by your scheme a little confusing.
> Is this because you've had a lot of experience with bizarre databases where
> people have taken liberties with the names (like including spaces in table
> and/or attribute names)? 

No, I haven't particularly.  Right now reasonable names should all work,
but spaces in attribute names won't.  I suppose it wouldn't be hard to
add the proper quotes in to make that possible (just backquotes,
right?), but I resist such forcefully bad names :-/

> Perhaps it might be better to avoid discussion of
> these issues until after the simple examples. Personally I think I'd prefer
> it if you used some other mechanism to map object names to database names
> (the one you use for columns is better than the one you use for tables) in
> those cases where python names can't be used for database objects.

Yes, it would probably make sense to start with automatic schema
generation (which is fast to get working with), and then have a section
on ways to adapt your class to a pre-existing table.

> 3. You talk about "relations" when I suspect you really mean
> *relationships*. Formally, a relation is what a table in a relational
> database represents, and it contains occurrences of a single entity-type
> identified by primary key value. A relationship is a mapping from
> occurrences of one entity-type to occurrences of another entity-type.

Noted and changed.

> 4. The whole business of using "id" as a default name for a primary key is
> somewhat contrived, and increases the difficulty of mapping SQLObject onto
> existing database schemas.

I don't have a lot of experience with pre-existing schemas, and I've
just adopted the style that I was first introduced to.  I realize it's
common to use tablename_id or tablenameid for the primary key, though I
don't see any particular advantage besides some sort of implicit column
matching between tables.

At some point I'll probably make some sort of Style object which you can
use to give your default method/database mapping functions.  Then people
who use different conventions don't have to go through too much trouble.

> 5. Your assertion that MySQL does not support transactions is out of date,
> since they can be supported by databases built from InnoDB tables, I believe
> (or have I imagined this?).

That's true.  But I've yet to meet someone actually using transactions,
and my own (admittedly brief) attempts to get transactions working were
unsuccessful.  I suppose it's possible, and there's no particular
barriers in place to keep someone from using transactions with MySQL (if
MySQLdb supports them...?), but until someone does it I find MySQL's
claim less than convincing.

> 6. Under "Creating and Dropping Tables" you discuss creating a *database*
> rather than a *table*.

FileMaker has infected me with its nefarious terminology!

> 7. I don't see any support for tables with composite primary keys. Is this
> an omission you plan to rectify?

I don't know.  I like single, simple primary keys.  They work well.  If
the table is really so minimal that it can't stand to have an extra
field for the id, then I suspect a SQLObject class is too heavy for it
(like with the join tables).  Of course, when adapting to a pre-existing
schema you might not have as many options.  Maybe it wouldn't be that
hard to fudge it (maybe make the ID into a tuple), but SQLObject really
expects for each object to have a single ID.

> Anyway, thanks for an interesting piece of software!

And thank you for your careful reading of the documentation.

  Ian

Re: [SQLObject] _defaultOrder with Joins: bug?

[Edmund L. <el...@go...>]

On 04/10/2003 12:53:46 AM Ian wrote:

>Again based on my familiarity with MySQL, which doesn't have real
>foreign keys.  If you can suggest the changes, to the SQL in particular,
>that would be good.  Well, I should probably just read up on it, since
>I'm starting to use Postgres more myself.

It is well worth it Ian. PostgreSQL is very faithful to SQL-92, so what you
learn would apply to Sybase, SQL Server, and even Oracle to some degree.

Once you start using integrity constraints, triggers, etc. to enforce data
semantics, you'll never be able to not use them again. The meaning in a
database is captured in the constraints, not the data.

...Edmund.

Re: [SQLObject] _defaultOrder with Joins: bug?

[Ian B. <ia...@co...>]

On Wed, 2003-04-09 at 20:28, Luke Opperman wrote:
> Yep, I never submitted my (possibly ugly) patch for this
> from when I made the suggestion. Here's the sticky part:
> SQLObject's join's don't actually do an SQL JOIN, they just
> get Ids. 

Again based on my familiarity with MySQL, which doesn't have real
foreign keys.  If you can suggest the changes, to the SQL in particular,
that would be good.  Well, I should probably just read up on it, since
I'm starting to use Postgres more myself. 

> So there's no easy way to just add an "ORDER BY"
> clause into the SQL code. It's going to come down to speed
> one way or another, so it may be worthwhile to make them do
> full JOINs, but here's the fix for the least amount of code
> change: Just sort the list of objects after you instantiate
> them. In each Join's performJoin function,
> 
>    return [cls(id) for (id,) in ids]
> 
> becomes
> 
>    objs = [cls(id) for (id,) in ids]
>    if cls._defaultOrder:
>        order = cls._defaultOrder
>        objs.sort(lambda x,y: cmp(getattr(x, order),
> getattr(y, order)))
>     return objs

Really this should be changed to do a more complete SELECT, as I'm doing
with .select results (it fetches not just IDs, but also column values). 
This code precedes that change.  Once you are fetching the entire
object, it should be relatively easy to do ORDER BY in the SQL, rather
than after the fact.

  Ian

Re: [SQLObject] question on SQL schemas

[Ian B. <ia...@co...>]

On Wed, 2003-04-09 at 12:59, Bud P.Bruegger wrote:
> The person table:
> 
> CREATE TABLE person (
>     id serial NOT NULL,
>     username character varying(20) NOT NULL,
>     first_name character varying(30) NOT NULL,
>     middle_initial character varying(1),
>     last_name character varying(50) NOT NULL
> );
> 
> What I am surprised about is the lack of use of UNIQUE and PRIMARY KEY
> that I would have expected for username (since the alternateID option
> is set) and id, respectively.  Note that in PostgreSQL, serial does
> not automatically set UNIQUE (see
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL)
> Also, in the phone_number table, I would have expected person_id to
> have a "REFERENCES person" option to indicate that it is a foreign
> key...  

Quite true, quite true.  I thought SERIAL implied PRIMARY KEY, but
apparently not.  I'll change the ID column to be SERIAL PRIMARY KEY.

As far as UNIQUE, that requires another option to Col.  But that's
reasonable.

For foreign keys, the KeyCol needs to be expanded.  I'm used to MySQL,
which has very simple (primitive?) joins, where it's entirely implied. 
Postgres goes further, but I'm not familiar with the syntax.  KeyCol
needs to be expanded in general, because its arguments should be
different than Col's arguments.

  Ian

Re: [SQLObject] _defaultOrder with Joins: bug?

[Luke O. <lu...@me...>]

Yep, I never submitted my (possibly ugly) patch for this
from when I made the suggestion. Here's the sticky part:
SQLObject's join's don't actually do an SQL JOIN, they just
get Ids. So there's no easy way to just add an "ORDER BY"
clause into the SQL code. It's going to come down to speed
one way or another, so it may be worthwhile to make them do
full JOINs, but here's the fix for the least amount of code
change: Just sort the list of objects after you instantiate
them. In each Join's performJoin function,

   return [cls(id) for (id,) in ids]

becomes

   objs = [cls(id) for (id,) in ids]
   if cls._defaultOrder:
       order = cls._defaultOrder
       objs.sort(lambda x,y: cmp(getattr(x, order),
getattr(y, order)))
    return objs


Take it or leave it, but it's working pleasantly enough for
us.

- Luke



Quoting Frank Barknecht <fb...@fo...>:

> Hallo,
>
> I found, that the _defaultOrder magic attribute doesn't
> work correctly
> with joined tables ("QueryAll"-queries). In the attached
> example
> (which is a bit confuse, but it's me getting comfortable
> with joins in
> SO), the two last operations give different kinds of
> order for the
> Answer-table, which should be ordered with a defaultOrder
> on field
> "position".
>
> ciao
> --
>  Frank Barknecht                               _
> ______footils.org__
>


--
 Many people are hamstrung by things like affection for
 fellow employees, honesty, desire to appear to be a
 "nice person," and other crippling limitations not
 suffered by the truly powerful and successful.

[SQLObject] _defaultOrder with Joins: bug?

[Frank B. <fb...@fo...>]

Hallo,

I found, that the _defaultOrder magic attribute doesn't work correctly
with joined tables ("QueryAll"-queries). In the attached example
(which is a bit confuse, but it's me getting comfortable with joins in
SO), the two last operations give different kinds of order for the
Answer-table, which should be ordered with a defaultOrder on field
"position".

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] deleting rows

[Frank B. <fb...@fo...>]

Hallo,
Brad Bollenbach hat gesagt: // Brad Bollenbach wrote:

> On 04/09/03 22:14, Frank Barknecht wrote:
> > quick question: How am I supposed to delete a SQLObject?
> pete.destroy()

Ah, thanks. Works like a charm. Pete's gone for good now.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

Re: [SQLObject] deleting rows

[Brad B. <br...@bb...>]

On 04/09/03 22:14, Frank Barknecht wrote:
> Hallo,
> 
> quick question: How am I supposed to delete a SQLObject?
> Say I have:
> 
>  pete = Person.new(name="Pete")
> 
> is there something like 
>  
>  pete.delete()
> 
> to get rid of pete? Or how do I get rid of him with something
> involving SQLBuilder?

pete.destroy()

-- 
Brad Bollenbach
BBnet.ca

[SQLObject] deleting rows

[Frank B. <fb...@fo...>]

Hallo,

quick question: How am I supposed to delete a SQLObject?
Say I have:

 pete = Person.new(name="Pete")

is there something like 
 
 pete.delete()

to get rid of pete? Or how do I get rid of him with something
involving SQLBuilder?

ciao
-- 
 Frank Barknecht                               _ ______footils.org__

[SQLObject] question on SQL schemas

[Bud P. B. <bu...@si...>]

Ian and all,

I am playing with ther person.py example with postgres and would like
to ask some questions to better understand what's happening.

For reference, see the sql for creating the tables [that I dumped using
the following postgresql command: pg_dump -F p -s <database>]
(see attached file)

The person table:

CREATE TABLE person (
    id serial NOT NULL,
    username character varying(20) NOT NULL,
    first_name character varying(30) NOT NULL,
    middle_initial character varying(1),
    last_name character varying(50) NOT NULL
);

What I am surprised about is the lack of use of UNIQUE and PRIMARY KEY
that I would have expected for username (since the alternateID option
is set) and id, respectively.  Note that in PostgreSQL, serial does
not automatically set UNIQUE (see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=datatype.html#DATATYPE-SERIAL)
Also, in the phone_number table, I would have expected person_id to
have a "REFERENCES person" option to indicate that it is a foreign
key...  

I'm not 100% sure, but wouldn't the use of UNIQUE and PRIMARY KEY also
imply that the dbms automatically creates indices and thus queries run
much faster?

thanks for the explanations!

--bud

/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

[SQLObject] SQLObject 0.3: people.py example

[Bud P. B. <bu...@si...>]

Ian,

I slighly modified person.py such that it runs "out of the box"
(attached).  

It simply always drops the tables and then recreates them.  Also, the
connection information is given on the commandline.  

Example ./person.py pgsql mydb myusername

hope this is helpful

--b

Re: [SQLObject] ANN: SQLObject 0.3

[Bud P. B. <bu...@si...>]

Hi Ian,

I'll probably try to clean up the example and send it (looking at the
tests and the code...).  

Also, I was looking through the doc of automatic schema generation and
have some questions (that I hope are productive):

* createTable(ifNotExists=True):
  I understand that the default (False) means that tables are created
  in any case, even if they exist already.  Wouldn't it be a better
  default to create them only if they don't exist already?  (Does it
  raise an exception if already existing tables are re-created or does
  it simply drop the old ones? -- maybe you could add that to the
  doc).  My rational for asking for this default is that it seems
  safer and the option could possibly be called "force" instead of
  ifNotExists--that seems closer to what I would expect (at least in a
  Unix environment).

* dropTable(ifExists=..):
  is this really necessary?  What happens if it does not exist
  (nothing or exception)?  Maybe there is something I don't
  understand, but I would be happy that after calling dropTable, the
  table is gone (or else, an exception is raised) no matter what the
  state was before.  (I try to avoid methods with pre-conditions as
  much as ever possible)

* dropTable(dropJoinTables=...):
  I am wondering whether it makes sense to keep joinTables around
  since really they make reference to rows in the table and leaving
  them around leads to dangling references.  I noted that the SQL you
  create for relations does not seem to define foreign keys (at least
  in an example given in the documentation of an earlier version).
  But if you had the DBMS take care of these, it would probably even
  complain to leave the joinTable while removing the other table...

hope this is helpful

--b

On 08 Apr 2003 12:30:30 -0500
Ian Bicking <ia...@co...> wrote:

> Damn... I forgot to update the example, it's all broken now. 
> tests/test.py might be a better example for now (even if not as
> compelling)
> 
> On Tue, 2003-04-08 at 12:24, Bud P.Bruegger wrote:
> > Ian,
> > 
> > I was excited to see the new release.  I just encountered a problem
> > when running the people.py example.  Gotta run to get my daughter--so
> > I thought I'll write this message anyways before I can look into it in
> > detail.  
> > 
> > I'm running the example with PostgreSQL.  I attach the diff that makes
> > this somewhat easier (a configuration variable controls which dbms is
> > used).
> > 
> > I'm using Python 2.2.1 on Linux.
> > 
> > This is the output to calling
> > 
> > ./people.py create
> > 
> > 
> > Accessing with user bud and password 
> > Traceback (most recent call last):
> >   File "./people.py", line 99, in ?
> >       table.createTable(ifExists=True)
> > TypeError: createTable() got an unexpected keyword argument 'ifExists'
> > 
> > 
> > Will look at it in more detail tomorrow unless you already solved the
> > problem.
> > 
> > cheers
> > --bud
> > 
> > 
> > 
> > 
> > On 07 Apr 2003 23:28:52 -0500
> > Ian Bicking <ia...@co...> wrote:
> > 
> > > SQLObject 0.3 has been release:
> > >   http://sqlobject.org
> 
> 


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

[SQLObject] ANN: SQLObject 0.3

[Ian B. <ia...@co...>]

SQLObject 0.3 has been release:
  http://sqlobject.org

Changes
-------

* Automatic table creation based off class.
* Create classes from database.
* Dynamically add and remove columns to live classes and databases.
* anydbm-based backend (queryable database without SQL).
* Better caching.
* Some bugs fixed.

What is it?
-----------

SQLObject is an object-relational mapper.  It allows you to translate
RDBMS table rows into Python objects, and manipulate those objects to
transparently manipulate the database.

SQLObject emphasizes convenience.  It's easy to learn and easy to use,
and doesn't require too much typing or use of any tools outside of your
editor and your Python source.


-- 
Ian Bicking  ia...@co...  http://colorstudy.com
4869 N. Talman Ave., Chicago, IL 60625  /  773-275-7241
"There is no flag large enough to cover the shame of 
 killing innocent people" -- Howard Zinn

Re: [SQLObject] More fun in CVS (DBM backend)

[Ian B. <ia...@co...>]

On Mon, 2003-04-07 at 03:42, Bud P.Bruegger wrote:
> Metakit (http://www.equi4.com/metakit/python.html) may be another
> interesting backend to consider...

Yes, someone brought this up before, which was the first time I thought
about being SQL-independent.  Though I haven't really looked at
Metakit... looks interesting.  Somewhere between general persistence and
the structured persistence of an RDBMS.  Though I wonder that SQLObject
should use a lower-level interface than metakit.py, as they both operate
on a similar level.

  Ian

[SQLObject] Naming conventions

[Ian B. <ia...@co...>]

Does anyone have an opinion on the names of the magic variables? 
(_columns, _idName, etc)

I didn't really think about them when I first created them, and now they
look wrong to me.  Should they be public variables, like columns? 
Should they be magic like __columns__?  If I go with magic, I wonder if
I should make them more most normal magic variables, like __id_name__ or
__idname__ instead of __idName__...

Thoughts?

  Ian

Re: [SQLObject] More fun in CVS (DBM backend)

[Bud P. B. <bu...@si...>]

Metakit (http://www.equi4.com/metakit/python.html) may be another
interesting backend to consider...

thanks for all your work!

--b

On 06 Apr 2003 20:28:39 -0500
Ian Bicking <ia...@co...> wrote:

> I just commit a bunch of changes that moved all the SQL out of
> SQLObject.py, now all in SQLBuilder and DBConnection.
> 
> To test the flexibility of this, I created a DBM backend, i.e.,
> non-SQL.  It has much of the functionality of the other databases, but
> probably has some bad performance issues, since there's no indexing
> except on ID (though that can be fixed).
> 
> The neat part is you can even use .select(), so long as you use
> SQLBuilder instead of creating the SQL yourself.  Without indexes (or a
> query optimizer) it essentially evaluates the expression for every
> possible combination of rows, but for a significant number of queries
> this isn't any worse than a RDBMS would do.
> 
> Besides amusing me, and proving the potential of SQLBuilder/SQLObject as
> a storage abstraction layer (since this is a significantly different
> sort of storage), this also can be useful to make SQLObject-based
> applications easy to install, with no dependencies (in return for
> decreased performance).  Of course, SQLite and the not-yet-supported
> Gadfly (no doubt easy to support) have similar intentions.  But I do
> this in ~375 lines of code, which I think is smaller than either of
> those :) -- I suspect the lack of SQL parsing is the biggest reason for
> simplicity.  And the lack of indexing :)  But someday...
> 
> Having done all this, I'm going to start working on getting a release
> together.  There's some Postgres and SQLite things that need to be
> fixed, and then it should be ready.
> 
>   Ian
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.net email is sponsored by: ValueWeb: 
> Dedicated Hosting for just $79/mo with 500 GB of bandwidth! 
> No other company gives more support or power for your dedicated server
> http://click.atdmt.com/AFF/go/sdnxxaff00300020aff/direct/01/
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
> 


/-----------------------------------------------------------------
| Bud P. Bruegger, Ph.D. 
| Sistema (www.sistema.it)
| Via U. Bassi, 54
| 58100 Grosseto, Italy
| +39-0564-411682 (voice and fax)
\-----------------------------------------------------------------

[SQLObject] More fun in CVS (DBM backend)

[Ian B. <ia...@co...>]

I just commit a bunch of changes that moved all the SQL out of
SQLObject.py, now all in SQLBuilder and DBConnection.

To test the flexibility of this, I created a DBM backend, i.e.,
non-SQL.  It has much of the functionality of the other databases, but
probably has some bad performance issues, since there's no indexing
except on ID (though that can be fixed).

The neat part is you can even use .select(), so long as you use
SQLBuilder instead of creating the SQL yourself.  Without indexes (or a
query optimizer) it essentially evaluates the expression for every
possible combination of rows, but for a significant number of queries
this isn't any worse than a RDBMS would do.

Besides amusing me, and proving the potential of SQLBuilder/SQLObject as
a storage abstraction layer (since this is a significantly different
sort of storage), this also can be useful to make SQLObject-based
applications easy to install, with no dependencies (in return for
decreased performance).  Of course, SQLite and the not-yet-supported
Gadfly (no doubt easy to support) have similar intentions.  But I do
this in ~375 lines of code, which I think is smaller than either of
those :) -- I suspect the lack of SQL parsing is the biggest reason for
simplicity.  And the lack of indexing :)  But someday...

Having done all this, I'm going to start working on getting a release
together.  There's some Postgres and SQLite things that need to be
fixed, and then it should be ready.

  Ian

Re: [SQLObject] a simple question and small code changes

[Ian B. <ia...@co...>]

On Thu, 2003-04-03 at 10:28, Peter Wilkinson wrote:
> I did notice that with SQLite due to the way it locks the db you can't 
> do something like:
> for p in Person.select(...):
> 	p.destroy() # this will throw an error about the db being locked

Hmm... youd want to do:

for p in list(Person.select(...)):
    p.destroy()

I kind of makes sense, because you're implicitly modifying the result
while you're iterating, which generally a bad thing.  Using list() makes
the result concrete.

I assume other databases won't have that problem.  Does it give a good
exception message?  If it does, that should be explanation enough...
though I'll put a little note in the docs.

> I imagine this looping and deleting would work fine on other db's so 
> maybe some note in the docs could help anyone else that hits this.
> 
> Another question...
> 
> How do you normally deal with knowing if a row exists in the database?
> e.g. (looking for person id 12 which doesn't exist in db)
> p = Person(12)
> p.id will return 12
> p.name throws an error (likewise for other columns)
> 
> Would something along the lines of p.exists() make any sense?

Yes, Luke was bringing this up before.  Right now I'm considering
getting rid of the lazy updating from the database -- where it fetches
the row only once you've accessed a needed attribute.  Either it should
fetch on instance creation, or if caching is turned off on every
access.  I think there's no good use case for fetching an object where
you really want to put off fetching the column values.  If you care
enough to create the object, you should care enough to want the column
values.

Once that happens, the exists thing should basically be fixed.  I'll add
a class method, so you can do something like Person.exists(12), but
Person(12) will throw an exception.

  Ian

Re: [SQLObject] a simple question and small code changes

[Luke O. <lu...@me...>]

>
> How do you normally deal with knowing if a row exists in
> the database?
> e.g. (looking for person id 12 which doesn't exist in db)
> p = Person(12)
> p.id will return 12
> p.name throws an error (likewise for other columns)
>
> Would something along the lines of p.exists() make any
> sense?

This is something that is missing from SQLObject, IMO. There
are two possibilities as I see if for fixed functionality:

p = Person(12) still works, and we add p.exists() as a
function (functionality already exists in the DBConnection,
as it is what makes p.name fail).

or

p = Person(12) throws an exception (KeyError?) (based on
internally calling self.exists()/equivalent).

I prefer option two, as I can't come up with a scenario
where I want an invalid Person object to be instantiated;
it means that everytime I instantiate an object I'll end up
calling .exists() anyways. I suppose the downside is for
.select(), as there is now an SQL check for every
instantiation, when we're pretty confident the objects
exist... but it's price I'm willing to pay, especially with
caching on. Hmm.

Public .exists() functionality should be available too, for
situations where SQLObject-based access is used alongside
other database access methods, since in these cases an
object can become obsolete without SQLObject knowing it
(but these situations are not preferrable, so I would
rather leave it up to the implementor to deal with this
than calling .exists() at every get/update in SQLObject.)

Modified code below (an addition to the end of _init(), and
exists()). This could alternatively be a function in
DBConnection, if we're continuing to move SQL out of
SQLObject.


    def _init(self, id, connection=None):
        ........
        # Stop everything if this doesn't exist.
        if not self.exists():
            raise KeyError, "The object %s by the ID %s does
not exist" % (self.__class__.__name__, self.id)

    def exists(self):
        q = "SELECT %s FROM %s WHERE %s = %s" % \
         (self._idName, self._table, self._idName, self.id)
        results = self._connection.queryOne(q)
        return bool(results)


Enjoy,

- Luke

Re: [SQLObject] a simple question and small code changes

[Peter W. <pw-...@te...>]

On Thursday, April 3, 2003, at 05:11  AM, Ian Bicking wrote:

> You can call .destroy() on the instance you want to kill.  It should
> probably be a class method, as self-destruction isn't very pythonic...
> but anyway, it's there.
>

Thanks very much Ian - I should have seen that in source...

I did notice that with SQLite due to the way it locks the db you can't 
do something like:
for p in Person.select(...):
	p.destroy() # this will throw an error about the db being locked

I imagine this looping and deleting would work fine on other db's so 
maybe some note in the docs could help anyone else that hits this.

Another question...

How do you normally deal with knowing if a row exists in the database?
e.g. (looking for person id 12 which doesn't exist in db)
p = Person(12)
p.id will return 12
p.name throws an error (likewise for other columns)

Would something along the lines of p.exists() make any sense?

Oh and I'm new-ish to Python so I may well being missing the obvious :-)

--
peter w.

Re: [SQLObject] a simple question and small code changes

[Ian B. <ia...@co...>]

On Wed, 2003-04-02 at 09:43, Peter Wilkinson wrote:
> Hi.
> I've just started having a look at SQLObject and so far very impressed 
> - seems nice and natural.
> 
> I'm using the download from the website, not cvs, and can't find a way 
> to delete a row from the database, is there something that I'm 
> overlooking or is this not implemented yet?

You can call .destroy() on the instance you want to kill.  It should
probably be a class method, as self-destruction isn't very pythonic...
but anyway, it's there.

> Also I've been using SQLite quite extensively lately and noticed a 
> couple of places in DBConnection.py that can be added to/changed.

Cool, I've added those to CVS (haven't installed SQLite, so might still
have a typo ;)

  Ian

Re: [SQLObject] a simple question and small code changes

[Frank B. <fb...@fo...>]

Hallo,
Peter Wilkinson hat gesagt: // Peter Wilkinson wrote:
> Also I've been using SQLite quite extensively lately and noticed a 
> couple of places in DBConnection.py that can be added to/changed.
> 
> To find if a table exists (def tableExists) in SQLite use:
> select tbl_name from sqlite_master where type='table' where tbl_name = 
> 'TABLE'
> 
> Also createIDColumn should be:
> 	return '%s INTEGER PRIMARY KEY' % soClass._idName
> INTEGER is the only type specified that means anything to SQLite.

Cool. I didn't have time (and won't have the next week) to adapt the
new db-creation changes for SQLite. They should in general be very
easy, as SQLIte is typeless and just gives back, what you throw at it. 

BTW: Did you use sqlite/sqlobject in a threaded environment like
Webware already? Somehow I couldn't get this to work properly.

ciao
-- 
 Frank Barknecht                               _ ______footils.org__