On 2/10/06, Oleg Broytmann <[EMAIL PROTECTED]> wrote:
> On Thu, Feb 09, 2006 at 06:05:29PM -0800, Mike Orr wrote:
> > I can see the SQL in my head and could just do connection.queryAll with:
> > SELECT Chemical.id AS id, Name.name AS name
> > FROM Chemical, Chemical_Name, Name
> > WHERE Chemical.id = Chemical_Name.Chemical_id
> > AND Name.id = Chemical_Name.Name_id
> > AND Chemical.canonical_nameID = Name.id
> > AND Name.name LIKE 'A%'
> > ORDER BY 2
> >
> > But I'm looking for more SQLObject-ish alternatives.
>
> http://svn.colorstudy.com/SQLObject/docs/FAQ.txt
>
> "What about GROUP BY, UNION, etc?"
>
> "In short - not every query can be represented in SQLObject..."
>
> > There's a Select object mentioned in "How to use database agnostic SQL
> > in SQLObject", but it doesn't seem to be officially documented.
>
> It is not "officially" documented not because it is "internal" but
> because SQLObject desperately needs documentation writers!
Yes, I understand. Just pointing it out in case it got forgotten. I
can help with docs as soon as I understand it. It looks like I'll be
doing a lot with SQLBuilder and "unorthodox" queries because my
dataset is pretty ornery in places, but I'm tired of maintaining ad
hoc SQL libraries and am looking for something standard like
SQLObject. So far what I've learned is:
1) SQLObject has three "levels".
* Col.get(), Col.byName(), etc, are the most OO but can only
represent simple queries.
* Col.select() can do more complicated joins and WHEREs but you're
still getting entire records back; that is, all the fields in one
table with the usual access to related tables, but not some fields
from one table and some from another.
* Select() with connection.queryAll(sql) can return arbitrary
values rather than entire sqlobjects, and also handle unsupported
queries. connection.query(sql) does backend-specific commands, can
create/drop databases, etc.
2) Table.select(RelatedTable.q...) works, and RelatedTable.q can also
be on the right side of an expression and in an orderBy.
> > I played with that and got:
> >
> > >>> sql = conn.sqlrepr(Select([Chemical.q.id, Name.q.name],
> > where=AND(Name.q.name.startswith('A'), Chemical.q.canonical_nameID ==
> > Name.q.id),orderBy=Name.q.name))
> > >>> sql
> > "SELECT Chemical.id, Name.name FROM Chemical, Name WHERE ((Name.name
> > LIKE 'A%') AND (Chemical.canonical_nameID = Name.id)) ORDER BY
> > Name.name"
> > >>> conn.queryAll(sql)
> > ( tuple of several 2-tuples listed )
> >
> > => That works. I guess that's what I want. Any other ideas?
>
> That's what you want, and it works, and it's SQLObject-ish enough...
OK, thanks. Good to have developer confirmation. Now I just have to
find out where TurboGears stores the connection object. But I do have
it working with Chemical.select() for the meantime.
Getting heavy performance differences between MySQL and SQLite. My
original query takes two seconds in MySQL but around 20 in SQLite. I
added a unique index on the Name table and that halved the MySQL time
(1 second) but didn't help the SQLite time. So I'll either have to
get a lot smarter with indexes, or build some cache tables, or forget
about SQLite for this.
One thing with indexes. My darned dataset has names up to 407
characters, so I defined it as length 500. That's too long for an
alternateId index in MySQL 5. I had to create a special index to
index just the left part: "CREATE UNIQUE INDEX name_index ON
(name(255))". So I don't get a byName method. :( SQLite meanwhile
can index the entire field but doesn't allow a length, so it needs a
different CREATE INDEX statment. In MySQL 4 it's worse because
VARCHAR columns cannot be > 255 chars, so a length 500 UnicodeCol is
illegal. An unlengthed UnicodeCol is OK, but you can't have an
anternateId index on unlengthed columns (which are TEXT). You can
have an index on the left 255 characters, but that's the same problem
as above. The 255 limit on VARCHAR columns is what finally made me
upgrade to MySQL 5. :)
I may just put the canonical name directly in the Chemical table and
ignore the normalization faux pas. Either that or make a .name
property for it.
--
Mike Orr <[EMAIL PROTECTED]>
([EMAIL PROTECTED] address is semi-reliable)
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid3432&bid#0486&dat1642
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
