Re: [web2py] legacy databases
You need to define your tables in web2py. As of yet there is no db
introspection for db.
db.define_table('tablename', *Fields)
There is also a thing called keyedtables which is for legacy databases.
-Thadeus
On Fri, Mar 19, 2010 at 10:44 AM, pacopyc paco...@gmail.com wrote:
Hi, I need to develope a web application with legacy database
(Oracle). Database's tables have an auto increment field (id) and then
this is ok. I tried to build a new application and I set database
connection (db.py). No error (connection string is ok) but if I try to
use appadmin application I can't see database's tables. Can I build
models from tables automatically? Is this the problem? What can I do?
Anything similar inspectdb of Django? Also with Rails no problem, I
don't understand ... can you help me?
Thank you
--
You received this message because you are subscribed to the Google Groups
web2py-users group.
To post to this group, send email to web...@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.
--
You received this message because you are subscribed to the Google Groups
web2py-users group.
To post to this group, send email to web...@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.
[web2py:19638] Re: web2py legacy databases, 'id' field requirement
Thanks for the update, David. On Mon, Apr 6, 2009 at 8:51 AM, David Niergarth jdn...@gmail.com wrote: On Apr 1, 9:20 pm, Yarko Tymciurak yark...@gmail.com wrote: http://www.postgresql.org/docs/8.3/interactive/sql-createrule.html and looking at this, it seems fairly straight forward: Yes it does. The manual threw me when it said that *Currently* views are read only but that you can get the *effect* of an updatable view by creating rules -- sounded to me like hack I didn't want to get into. ;) Reading more about rules in PostgreSQL, views are actually implemented using rules so using rules to add insert/update/delete behavior is the completely natural/blessed way to do it. Performance- wise, queries I ran using the writable views seemed just as fast as queries using the underlying tables. Running 20K updates on a view sometimes took twice as long as updates on the table, but that's probably more related to me testing on a laptop. Thanks for pushing me to explore this more. 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 ... Let us know if this works. It works! I was skeptical but I'm feeling pleasantly surprised. The insert, update, and delete rules will be a bit t-e-d-i-o-u-s to write (especially with lots of tables). Any on-the-fly tables you might create (e.g., cached search results) will also need corresponding views and rules created on-the-fly (at least if you need to join against them). But beyond the extra views and rules, legacy PostgreSQL databases can be used with the DAL this way. --David --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[web2py:19252] Re: web2py legacy databases, 'id' field requirement
On Apr 1, 9:20 pm, Yarko Tymciurak yark...@gmail.com wrote: http://www.postgresql.org/docs/8.3/interactive/sql-createrule.html and looking at this, it seems fairly straight forward: Yes it does. The manual threw me when it said that *Currently* views are read only but that you can get the *effect* of an updatable view by creating rules -- sounded to me like hack I didn't want to get into. ;) Reading more about rules in PostgreSQL, views are actually implemented using rules so using rules to add insert/update/delete behavior is the completely natural/blessed way to do it. Performance- wise, queries I ran using the writable views seemed just as fast as queries using the underlying tables. Running 20K updates on a view sometimes took twice as long as updates on the table, but that's probably more related to me testing on a laptop. Thanks for pushing me to explore this more. 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 ... Let us know if this works. It works! I was skeptical but I'm feeling pleasantly surprised. The insert, update, and delete rules will be a bit t-e-d-i-o-u-s to write (especially with lots of tables). Any on-the-fly tables you might create (e.g., cached search results) will also need corresponding views and rules created on-the-fly (at least if you need to join against them). But beyond the extra views and rules, legacy PostgreSQL databases can be used with the DAL this way. --David --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[web2py:18932] Re: web2py legacy databases, 'id' field requirement
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 -~--~~~~--~~--~--~---
[web2py:18939] Re: web2py legacy databases, 'id' field requirement
But how would you handle updates (views being read-only). --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 -~--~~~~--~~--~--~---
[web2py:18945] Re: web2py legacy databases, 'id' field requirement
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 -~--~~~~--~~--~--~---
[web2py:18954] Re: web2py legacy databases, 'id' field requirement
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
[web2py:18905] Re: web2py legacy databases, 'id' field requirement
Can you create a custom database view and map the field? 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 -~--~~~~--~~--~--~---
