Yes, I meant SQL UPDATE/INSERT statements. As it turns out, in
PostgreSQL views are read-only.
Currently, views are read only: the system will not allow an
insert, update,
or delete on a view. You can get the effect of an updatable view
by creating
rules that rewrite inserts, etc. on the view into appropriate
actions on other
tables. For more information see CREATE RULE.
http://www.postgresql.org/docs/8.3/interactive/sql-createview.html
It seems like web2py and most other ORMs, except perhaps SQLAlchemy,
assume they own the database. I'm not criticizing that design
decision, just observing it doesn't fit comfortably with large legacy
databases. Having to create views for a database with lots of tables
and lots of optimized indexes is just impractical and I would guess
the performance, especially if views were writable, would also be
unacceptable. I am content with this and don't fault the DAL. I
learned at PyCon that the DAL is the only ORM that works on AppEngine
-- quite a coup. I can still let the DAL manage my auth tables.
Massimo explained we're not limited to just db.sqlexecute(); we can
also use db._cursor to access all the usual DB API cursor methods
(execute, fetchone, fetchall, etc.). That's all the flexibility I've
ever had plus the SQLDB() handles the connection pooling for me.
--David
On Apr 1, 5:36 pm, Yarko Tymciurak <yark...@gmail.com> wrote:
> On Wed, Apr 1, 2009 at 3:22 PM, David Niergarth <jdn...@gmail.com> wrote:
>
> > But how would you handle updates (views being read-only).
>
> Views are not read-only. SQLite only supports READ-ONLY views.
>
> When derived columns are from multiple sources, there are constraints - but
> for what we're talking about here, there should be no such constraints in
> any server I'm aware of.
>
> If by updates you mean table-altering web2py actions, that's another
> discussion;
>
> If by updates you mean row modifying or inserting operations, I think there
> is no problem (except for SQLite)
>
> Correct me if I'm wrong.
>
> - Yarko
>
>
>
> > --David
>
> > On Apr 1, 2:34 pm, Yarko Tymciurak <yark...@gmail.com> wrote:
> > > On Wed, Apr 1, 2009 at 12:34 AM, mdipierro <mdipie...@cs.depaul.edu>
> > wrote:
>
> > > > Can you create a custom database view and map the field?
>
> > > Yes - this is the way to do it in your db server. You also want to set
> > your
> > > web2py table definition to migrate=False (so that an ALTER TALE is not
> > > attempted by web2py). NOTE: you only need to define (in web2py) the
> > columns
> > > you intend to access (not the entire table). Of course, you can do the
> > same
> > > in your VIEW when you create it.
>
> > > > On Apr 1, 12:06 am, Kevin Butler <kevinjbut...@gmail.com> wrote:
> > > > > This was posted as a comment tohttp://
> > > > mdp.cti.depaul.edu/AlterEgo/default/show/55:
> > > > > Which reads:
>
> > > > > > web2py can import tables saved in csv format therefore it can
> > access
> > > > legacy data.
> > > > > > web2py can also access existing databases directly (MySQL,
> > PostgreSQL,
> > > > SQLite and Oracle) if
> > > > > > each table has an auto increment field called "id" and if this is
> > the
> > > > field used for referencing.
>
> > > > > > Most legacy databases can be converted into the above format by
> > using
> > > > ALTER TABLE. If the legacy
> > > > > > database cannot be converted into such format, it probably has
> > design
> > > > issues and should be
> > > > > > reworked anyway. web2py tries to enforce good software engineering
> > > > practice. HOWEVER, IN
> > > > > > REALITY THIS IS UNREALISTIC AND IS A GREAT DRAWBACK IN ANY ATTEMPT
> > TO
> > > > USE
> > > > > > WEB2PY ON LEGACY SYSTEMS.
>
> > > > > This really is inconvenient for legacy databases that are still in
> > use
> > > > > by other applications.
>
> > > > > In my case, I want to read and possibly modify a user table belonging
> > > > > to a Dekiwiki installation, but because web2py will not be the
> > > > > exclusive owner of the table, I can't alter the table to meet
> > web2py's
> > > > > requirements.
>
> > > > > The table has a "user_id" primary key, so if I could do something
> > > > > like:
>
> > > > > db.define_table( "users",
> > > > > ...,
> > > > > id="user_id",
> > > > > migrate=False
> > > > > )
>
> > > > > I'd be able to do use the web2py database layer, but as it is, I
> > added
> > > > > code to use MySQLdb directly.
>
> > > > > kb
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
