[web2py] Re: Wiki with Oracle?
The resolution of
issue#1432https://code.google.com/p/web2py/issues/detail?id=1432 (submitted
under my other account, I need to unify these) has now solved the first
error you listed (*ORA-00904: WIKI_TAG.WIKI_PAGE: invalid identifier*),
which was a IS_NOT_IN_DB query error of the type I described in the issue.
So I tested the wiki again in Oracle to see if I would still run into the
second error you posted; I did, but can now add the full SQL queries that
were generated.
For the Search page, the query looks like this:
SELECT DISTINCT COUNT(wiki_tag.wiki_page), wiki_page.id, wiki_page.slug,
wiki_page.title, wiki_page.tags, wiki_page.can_read, wiki_page.body
FROM (
SELECT w_tmp.*, ROWNUM w_row FROM (
SELECT COUNT(wiki_tag.wiki_page), wiki_page.id, wiki_page.slug,
wiki_page.title, wiki_page.tags, wiki_page.can_read, wiki_page.body
FROM wiki_tag, wiki_page
WHERE (((wiki_page.id = wiki_tag.wiki_page) AND (wiki_tag.name IN
('stuff'))) OR (wiki_page.title LIKE (('%' || (REPLACE('stuff','%','%%'))
|| '%'
GROUP BY wiki_page.id, wiki_page.slug, wiki_page.title, wiki_page.tags,
wiki_page.can_read, wiki_page.body
ORDER BY COUNT(wiki_tag.wiki_page) DESC
) w_tmp WHERE ROWNUM=100
) wiki_tag, wiki_page
WHERE (((wiki_page.id = wiki_tag.wiki_page) AND (wiki_tag.name IN
('stuff'))) OR (wiki_page.title LIKE (('%' || (REPLACE('stuff','%','%%'))
|| '%' AND w_row 0
GROUP BY wiki_page.id, wiki_page.slug, wiki_page.title, wiki_page.tags,
wiki_page.can_read, wiki_page.body
ORDER BY COUNT(wiki_tag.wiki_page) DESC
There are two problems here. The larger problem not surprisingly occurs due
to pagination, which I know has openly been acknowledged as a limitation
with Oracle and the DAL:
Oracle also does not support pagination. It does not support neither the
OFFSET nor the LIMIT keywords. Web2py achieves pagination by translating a
db(...).select(limitby=(a,b)) into a complex three-way nested select (as
suggested by official Oracle documentation). *This works for simple
select but may break for complex selects involving aliased fields and or
joins*.
Indeed we do have a JOIN and pagination here. I've been looking at problems
with the existing pagination solution and hope to find another way forward
eventually (still using subqueries, but slightly modifying which conditions
get written into the inner and outer queries); if I get somewhere, I'll
certainly post my attempt.
But putting that aside for the moment, even when I remove the pagination
(manually commented out to test), the query for this page gets generated
incorrectly for Oracle:
SELECT DISTINCT COUNT(wiki_tag.wiki_page), wiki_page.id,
wiki_page.slug,wiki_page
.title, wiki_page.tags, wiki_page.can_read, wiki_page.body
FROM wiki_tag, wiki_page
WHERE (((wiki_page.id = wiki_tag.wiki_page) AND (wiki_tag.name IN ('stuff'
))) OR (wiki_page.title LIKE (('%' || (REPLACE('stuff','%','%%')) || '%'
GROUP BY wiki_page.id, wiki_page.slug, wiki_page.title, wiki_page.tags,wiki_page
.can_read, wiki_page.body
ORDER BY COUNT(wiki_tag.wiki_page) DESC
The error returned is: *ORA-00932: inconsistent datatypes: expected - got
CLOB*
The problem is that Oracle does not permit GROUP BY on columns of type
CLOB; nor does it permit DISTINCT on these, for that is essentially another
type of grouping. The CLOB columns in this table as defined include:
wiki_page.body
wiki_page.tags
wiki_page.html
wiki_page.can_read
wiki_page.can_edit
The query made for the page attempts to simultaneously fetch all the
important information from the WIKI_PAGE table (including the full text of
the post) *and* the count of matches in the WIKI_TAG table; to achieve
this, it uses GROUP BY on all the WIKI_PAGE columns and then uses COUNT()
on the WIKI_TAG entries.
Here is the relevant wiki code:
count = db.wiki_tag.wiki_page.count()
fields = [db.wiki_page.id, db.wiki_page.slug,
db.wiki_page.title, db.wiki_page.tags,
db.wiki_page.can_read]
if preview:
fields.append(db.wiki_page.body)
if query is None:
query = (db.wiki_page.id == db.wiki_tag.wiki_page) \
(db.wiki_tag.name.belongs(tags))
query = query | db.wiki_page.title.contains(request.vars.q)
if self.settings.restrict_search and not self.manage():
query = query (db.wiki_page.created_by == self.auth.
user_id)
*pages **= db(query).select(count,
*fields, **dict(orderby=orderby or ~count,
groupby=reduce(lambda a, b: a | b, fields),
distinct=True,
limitby=limitby))*
Since the *wiki_page.tags* and *wiki_page.can_read *columns are also CLOBs,
it won't help to disable preview and thereby skip the *body* column;
grouping across CLOBs will persist as written.
From a general
[web2py] Difficulty with auth.signature when renaming auth tables
When I follow what I thought to be standard practice (following this
documentationhttp://www.web2py.com/books/default/chapter/29/09#Renaming-Auth-tables)
for using alternative names for the auth tables, I'm seeing an incorrect
foreign key reference on any subsequent table defined that uses
auth.signature.
Here's the basic test code in a db.py:
auth = Auth(db)
auth.settings.table_user_name = 'cst_user'
auth.settings.table_group_name = 'cst_group'
auth.settings.table_membership_name = 'cst_membership'
auth.settings.table_permission_name = 'cst_permission'
auth.settings.table_event_name = 'cst_event'
auth.settings.table_cas_name = 'cst_cas'
auth.define_tables(username=True,signature=False)
db.define_table('tmp_mytable',
Field('something'),
auth.signature)
This works so far as the auth tables are concerned; web2py creates them
with the alternate names specified, properly referencing each other.
However, the query generated to create the subsequent table that uses
auth.signature (*tmp_mytable* above) refers in its foreign key declaration
to the standard auth table names instead. The query below was generated
(using SQLITE to test, though I first encountered this with Oracle).
CREATE TABLE mytable(
id INTEGER PRIMARY KEY AUTOINCREMENT,
something CHAR(512),
is_active CHAR(1),
created_on TIMESTAMP,
created_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE,
modified_on TIMESTAMP,
modified_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE
);
Note that it has reverted to the default name auth_user for the
referenced foreign keys. What am I doing wrong here?
(Version 2.4.5-stable+timestamp.2013.04.06.10.09.56)
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: Difficulty with auth.signature when renaming auth tables
Thank you so much, that solved it.
(It might be helpful to others if that important caveat makes it into the
relevant
section of
documentationhttp://www.web2py.com/books/default/chapter/29/09#Renaming-Auth-tables
at
some point, though I know that not every detail can be explained succinctly
there.)
On Sunday, April 7, 2013 7:29:00 PM UTC-5, Anthony wrote:
Looks like by default, auth.signature gets defined when Auth is
initialized, which is before you have set the custom table names. To avoid
this do:
auth = Auth(db, signature=False)
In that case, auth.signature will instead be defined when you call
auth.define_tables(), by which point the custom tables names will already
be set.
Anthony
On Sunday, April 7, 2013 6:58:09 PM UTC-4, Jason Phillips wrote:
When I follow what I thought to be standard practice (following this
documentationhttp://www.web2py.com/books/default/chapter/29/09#Renaming-Auth-tables)
for using alternative names for the auth tables, I'm seeing an incorrect
foreign key reference on any subsequent table defined that uses
auth.signature.
Here's the basic test code in a db.py:
auth = Auth(db)
auth.settings.table_user_name = 'cst_user'
auth.settings.table_group_name = 'cst_group'
auth.settings.table_membership_name = 'cst_membership'
auth.settings.table_permission_name = 'cst_permission'
auth.settings.table_event_name = 'cst_event'
auth.settings.table_cas_name = 'cst_cas'
auth.define_tables(username=True,signature=False)
db.define_table('tmp_mytable',
Field('something'),
auth.signature)
This works so far as the auth tables are concerned; web2py creates them
with the alternate names specified, properly referencing each other.
However, the query generated to create the subsequent table that uses
auth.signature (*tmp_mytable* above) refers in its foreign key
declaration to the standard auth table names instead. The query below was
generated (using SQLITE to test, though I first encountered this with
Oracle).
CREATE TABLE mytable(
id INTEGER PRIMARY KEY AUTOINCREMENT,
something CHAR(512),
is_active CHAR(1),
created_on TIMESTAMP,
created_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE,
modified_on TIMESTAMP,
modified_by INTEGER REFERENCES auth_user (id) ON DELETE CASCADE
);
Note that it has reverted to the default name auth_user for the
referenced foreign keys. What am I doing wrong here?
(Version 2.4.5-stable+timestamp.2013.04.06.10.09.56)
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
