On Wed, Apr 1, 2009 at 8:32 PM, David Niergarth <jdn...@gmail.com> wrote:
> > 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 I think you meant http://www.postgresql.org/docs/8.3/interactive/sql-createrule.html and looking at this, it seems fairly straight forward: if your view is just the table, with a field re-named to 'id' then this seems simple; Can you try the pattern shown by example in http://developer.postgresql.org/pgdocs/postgres/rules-update.html 36.3.2, and do something like this: CREATE RULE myw2pview_ins AS ON INSERT TO mytable_w2p DO INSTEAD INSERT INTO mytable VALUES ( ## let id field autoincerement NEW.name, ## rest of of the server table names NEW.avail, NEW.color, NEW.len, NEW.unit ); CREATE RULE myw2pview_upd AS ON UPDATE TO mytable_w2p DO INSTEAD # no need to change the 'id' equiv field UPDATE mytable SET name = NEW.name, avail = NEW.avail, color = NEW.color, len = NEW.len, unit = NEW.unit WHERE orig_id = OLD.orig_id; ## not sure about this... CREATE RULE myw2pview_del AS ON DELETE TO mytable_w2p DO INSTEAD DELETE FROM mytable WHERE orig_id = OLD. orig_id; Let us know if this works. Regards, Yarko > 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 -~----------~----~----~----~------~----~------~--~---
