On Wed, Mar 11, 2009 at 5:19 PM, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> It should not be too difficult to support this in web2py
>
> What about the syntax:
>
> db.define_view('myview',db.Field('col1'),db.Field('col2'),select=db
> (...)._select(....))
To implement the view - db.define_view('myview'..... part sounds good.
Do you mean the select=db(xxxx)._select() to be the mapping? e.g. from
db.nativedb()...
How will you define the actual foreign table / type info? just give
db.nativedb.field.... and assume it exists, and matches the type of (for
example) db.Field('col1')?
If so I think this syntax is error prone --- I think a dict might be the
way to do it:
db.VIEWField({'id':'foreign_table_id'}), and so on... if not a dict, then
assume same name; always assume same type;
have same syntax as for db.Field, with the exception that the name can be a
dict (???)... also you MUST be able to map DAL 'id' this way --- that, after
all, has been the biggest complaint, whole point (_I_ have an application
for this).
There are some important points that could be useful even for web2py (not
just legacy uses):
- must be able to handle .... SELECT foo as bar .... (that is, field
re-mapping -- that's one motivation for VIEWS);
- must be able to present several tables "as if" one (another important
application of a view),
e.g. --- select db.person.name as name, db.payment.amount as payment where
db.payment.person_id = db.person.id
The third (as I can see) motivation for views is to keep a select "in
memory" for a session, for performance... keeping s select in memory...
I think we want to cover these two bullet points.
I think that second bullet generally might be difficult. We need to think
about how to do this so it's useful, and limit it so it's not too
complicated.
My thoughts for now...
Yarko
>
> and access it as a table db.myview as a normal table.
> A view would be a special table that does not have an "id" field and
> does not support insert.
Correction: DOES / MUST HAVE AN ID field, but it is NOT default - it must
be mapped.
I think if you add this constraint that the first view field defined MUST be
'id' -- and may or may not be mapped - then EVERYTHING in web2py will work
on this.
Hmm.... Or if there is _really_ reason to say "there is no primary key
field" .... (really????) then at least require something like {'id':None}
--- but then everything in gluon (and everywhere) would need to be changed
to handle id=None...
I'm not convinced this is useful --- so I suggest require a 'id' field, so
that everything in web2py just works.... then lets see if there's a REAL
situation where there is no primary key (or at least unique key in a table
that can be used _as_ 'id') .... and deal with it when it somes up....
Yarko.
>
> Comments? Suggestions?
>
> Massimo
>
>
> On Mar 11, 4:20 pm, Yarko Tymciurak <yark...@gmail.com> wrote:
> > A view is kind of a special SQL select statement - so maybe this THIS
> could
> > be included as part of the DAL... that would actually be the right way to
> > let people easily map from web2py (I don't know why I didn't think of
> that
> > before).
> > You can make a aview be a temporary table (I don't know what the
> performance
> > implications for the various dbs are for temporary vs. persistent view -
> I
> > know is some cases a view is basically an SQL select statement that runs
> at
> > each access).
> >
> > so something like SELECT foo as bar from MY_TABLE is the general
> > idea...
> >
> > View syntax (some examples):
> >
> > http://www.postgresql.org/docs/8.2/interactive/sql-createview.html
> >
> >
> http://dev.mysql.com/doc/refman/5.0/en/create-view.htmlhttp://dev.mysql.com/doc/refman/5.0/en/view-syntax.html
> >
> > http://www.sql-server-performance.com/articles/dba/view_basics_p1.aspx
> >
> >
> http://www.sqlite.org/lang_createview.htmlhttp://www.sqlite.org/omitted.html
> >
> > http://www.dba-oracle.com/concepts/views.htm
> >
> > http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topi...
> >
> > Did I miss any?
> >
> > Regards,
> > Yarko
> >
> > On Wed, Mar 11, 2009 at 3:54 PM, mdipierro <mdipie...@cs.depaul.edu>
> wrote:
> >
> > > Can you point us to an example of how to make a view?
> >
> > > On Mar 11, 3:48 pm, Yarko Tymciurak <yark...@gmail.com> wrote:
> > > > in essence, web2py imposes a standard, consistent primary key for all
> > > web2py
> > > > tables...
> > > > This is not bad.
> >
> > > > I still argue (and the more I think of it, the more strongly) that
> this
> > > > mapping for operating w/ legacy, existing databases should NOT be
> handled
> > > by
> > > > web2py --- rather, the DB is the place to handle this.
> >
> > > > Make a VIEW in your DB.
> >
> > > > It's a db / connection specific issue by definition (so that strongly
> > > > suggests it does NOT belong in web2py);
> > > > It's something any DB can handle already (no need to re-invent an
> > > existing
> > > > wheel);
> > > > In sqllite (probably mostly an issue if you're working on an embedded
> > > > system), the view is READ ONLY, and even this is not a bad thing.
> There
> > > > are ways around it in sqlite;
> >
> > > > The more I hear this discussion (and it has been - in one way or
> another
> > > -
> > > > going on for maybe a year...) the more two things settle for me:
> >
> > > > - I REALLY like the idea of mapping existing databases so that you
> can
> > > > interface them with web2py;
> > > > - I REALLY think this mapping belongs in the DB --- NOT in web2py
> code.
> >
> > > > Yarko
> >
> > > > On Wed, Mar 11, 2009 at 11:15 AM, mdipierro <mdipie...@cs.depaul.edu
> >
> > > wrote:
> >
> > > > > It is more than a convenience. What if the user types an
> unprintable
> > > > > character in the ISBN? The database would take it but it would be
> > > > > nearly impossible to get to the record. You can have other unique
> > > > > fields in web2py, but you must also have the ID.
> >
> > > > > On Mar 11, 11:08 am, DenesL <denes1...@yahoo.ca> wrote:
> > > > > > On Mar 11, 9:56 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> >
> > > > > > > Should the record id be assigned by the DB or by the user? I
> > > > > > > think it should be assigned by the DB. The practical issue is
> that
> > > > > > > SQLFORM assumes the current record id. You change that and
> > > everything
> > > > > > > break. It can be fixed but web2py will become much more
> bloated. I
> > > > > > > believe having an autoincrement id should be considered good
> > > practice
> > > > > > > and enforced by web2py.
> >
> > > > > > Record id is just a convenience, you can get to a specific record
> > > > > > using its primary key(s) and that is how most tables are normally
> > > > > > built.
> > > > > > So, e.g. the ISBN number can be a primary key in a books DB and
> there
> > > > > > is not much difference (unless I am failing to see something)
> between
> > > > > > using it or using an extra id field.
> > > > > > When the primary key consists of several fields the id-ing can be
> > > done
> > > > > > using a list.
> > > > > > I honestly don't know how much bloating can this produce.
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
