Re: [web2py] Powertable remarks
Thank you Bruno for this beautiful SQLTABLES drop in replacement. Joined Rows, virtual fields on Rows work! You saved my week. Looks great and I love client side sorting and searching. On small(hundreds of rows) its faster than server side.
Re: [web2py] grid
Wold love to test it with joins.
Re: [web2py] administrator controller
I saw one previously on this list but can't seem to find it right now. Was it plugin by Selecta? http://groups.google.com/group/web2py/browse_thread/thread/52014e4b0adf5888/013d69bb1fe9008c?lnk=gstq=tools+to+manage+users+%2F+groups+%2F+permissions#013d69bb1fe9008c
Re: [web2py] SQLTABLE with joined data, but without repeats?
Is there an SQL query that gives the desired result?
Re: [web2py] Re: Changing links on SQLTABLE
You can put anything in SQLTABLE by modifying Rows object with rows.setvirtualfields. See this post http://groups.google.com/group/web2py/browse_thread/thread/826a37f56c26d689
Re: [web2py] Errors when a table references itself (was Re: What does this mean?)
Your code works on my 3 weeks old web2py and MySQL. I can insert/view the table with db admin.
Re: [web2py] Re: patch to make Rows.setvirtualfields work with SQLTABLE
This is my working
code: http://snipt.net/rochacbruno/virtual-fields-in-sqltabe
Note the headers=None
So need to patch the header definition to use the virtualfieldname, or
better, How to define a label for a virtual field?
I think you can try to add label property to your MyVirtualFields class.
Like:
__init__(self):
self.label = mylabel
for work with SQLTABLE (or plugin_datatable) we need to add the colname for
every virtual field: myrows.colnames.append('task.teste')
How to append every virtual field to rows.colnames? I think this could be
done in sqlhtml.py as:
for field in sqlrows.virtualfields:
sqlrows.colnames.append('tablename.'+field)
Is it possible?
Its late, I can be understanding you wrong.
Why you don't want to do it by hand the way it is in your code?
myrows.colnames.append('task.teste')
Re: [web2py] problem with web2py and plugin_datatables
Example of usage is like this:
{{=plugin_datatable(db(db.shout.id0).select(),_class='datatable')}}
I think plugin_datatable wants Rows object as a parameter, not a table
created with SQLTABLE
Re: [web2py] Re: crud operations for joins?
I have a 'central' table in my design called 'entity'
which contains lots of data (including names, company, emails, phones,
address, etc.) and I want many other tables to point to ONE entity
instance, i.e. 'entity' as an _extension_ of records in many different
tables.
When you say 'joined tables' do you mean
SELECT tabl1.some_col, tabl2.other_col FROM tabl1 LEFT JOIN tabl2 ON .
or something else?
I don't know a web2py component that can be used to edit a result of a
joined select.
If your 'entity' table includes all fields you want to present to a
user you can avoid joins by use of references. I don't use db
references myself. I think usage can look like this:
db.define_table('names', Field('name', 'string'))
db.define_table('companies', Field('company', 'string'))
db.define_table('entity',
Field('name', db.names.name),
Field('company', db.companies.company))
also you can alter the way a field is represented with .represent and
.requires methods
The details are in documentation on Database Abstraction Layer(DAL)
http://www.web2py.com/book/default/chapter/06
SQLFORM allows to edit a row in a table which is fine if you have all
fields in 'entity' table.
If your 'entity' table contains references to rows in other tables you
will need SELECT...JOIN...
To edit its result you will have to make a page with multiple
SQLFORMs to edit individual rows in tables ' entity' refers to , or
make a custom form with SQLFORM.factory, or invent something.
My background is with object databases, and this kind of design makes
sense, although I'm not sure if it makes sense with relation
databases?.
It's pretty common to store an object information in a relation db as
a set of tables.
Re: [web2py] Re: crud operations for joins?
In the example I provide (entity), specifically for web2py and
relational databases, does it actually make sense to separate 'entity'
to its own table and use 1:1 relationships from tables A/B/C, or is it
recommended to just embed the 'entity' fields into tables A/B/C
(without any references)?.
What are the pros and cons of such alternatives?.
The pro I see about using an independent 'entity' table is
maintenance, but the con might be the access / manipulation of this
extra reference ...?
For general info on relational database you can google for database
normalization.
On the practical side sometimes 1:1 relations are useful. For example
if we want to store 'dressed person' object which contains
person_name, current_jacket. Its convenient to store in 2 tables
db.define_table('jackets', Field('jacket', 'string'))
db.define_table('dressed_person', Field('person_name','string'),
Field(current_jacket', db.jackets.id)
So when a person changes a jacket we don't loose info on the jacket
he was wearing in case he wants to put old jacket on again.
On web2py side its much easier to design your app with 1 table= 1
page approach.
Re: [web2py] Re: Manipulate Rows Object and/or SQLTABLE
The patch is in the text of this message: patch to make Rows.setvirtualfields work with SQLTABLE http://groups.google.com/group/web2py/browse_thread/thread/826a37f56c26d689/210036457d278cdc?lnk=gstq=patch+to+make+Rows.setvirtualfields+work+with+SQLTABLE#210036457d278cdc or e-mailing patched sqlhtml.py to you is the right way?
Re: [web2py] Re: The primary key constraint on legacy tables
Perhaps my statement is wrong.
DAL works fine with keyed tables.
When I started to make web2py interface to a legacy database I tried
keyed tables because many tables in the db have composite primary
keys. I kept bumping in documentation examples that describe how to do
things with normal id tables which will not work with keyed ones.
Don't remember exactly what were the problems. Its been a while since
I realized I don't have to use all fields that are defined as a
primary key in a legacy db SQL schema. If there is a unique int field
I can
db.define_table('mytable', Field('unique_int_column', 'id', migrate=False)
and have all the web2py magic.
If there is no unique int field it can be added to a table with SQL
and this will not affect legacy software that uses the db.
Re: [web2py] Re: Manipulate Rows Object and/or SQLTABLE
Sorry for posting what I'v already posted, but it looks like my post
on the patch to make SQLTABLE work with Rows object with added virtual
fields was lost.
I think the easiest solution to add a column to select result and view
the result in SQLTABLE wold be something like:
class ExtraFields:
def new_column(self):
if self.some_field_in_select==' something':
return A('some_action_link', _href=URL(
f=some_action_controller_function,
args=[self.id]))
else:
return A('great thing', _href='http://www.web2py.com')
rows=db(db.some_table).select()
rows.setvirtualfields(some_table=ExtraFields())
rows.colnames.append('some_table.new_column')
table=SQLTABLE(rows)
This will give an exception telling that
sqlhtml.py:SQLTABLE.__init__
can't do
field = sqlrows.db[tablename][fieldname]
because there is no 'new_column' in the database.
It wants to get the field from the db model to know how to render it.
This doesn't matter when we add a new field to select result
because the new field shell(and will by a view) be cast to string.
The patch:
change
sqlhtml.py:SQLTABLE.__init__
field = sqlrows.db[tablename][fieldname]
to
try:
field = sqlrows.db[tablename][fieldname]
except:
field = None
change
sqlhtml.py:SQLTABLE.__init__
if field.represent:
r = field.represent(r)
to
if not field:
pass
elif field.represent:
r = field.represent(r)
---Now we are able to include a new field in SQLTABLE
containing anything(text, link, image, button, form, whatever)
depending on row contents(in ExtraFields.new_column(self): 'self' is a single
Row in Rows object returned by select) ,
by adding the field to db().select() result.
---Limitation:
in
rows.colnames.append('some_table.new_column')
rows.setvirtualfields(some_table=ExtraField())
'some_table' shell be a table mentioned in select and present in
the db,
otherwise
'some_table' will be added as a sub dict to Row objects
and you will not see it in SQLTABLE.
[web2py] Re: Create TRs dinamically in a FORM
Maybe WebGrid(http://web2pyslices.com/main/slices/take_slice/39) can help you? I didn't use it but it says that it lets you build a table that supports paging, sorting, editing and totals easily. Or you can put forms in a SQLTABLE column. How to add columns to SQLTABLE is being discussed in tread: Manipulate Rows Object and/or SQLTABLE http://groups.google.com/group/web2py/browse_thread/thread/87596d39a4e46c1b/01e764e9db39455b?lnk=gstq=row+object#01e764e9db39455b
[web2py] Re: Manipulate Rows Object and/or SQLTABLE
I want to customize the result of SQLTABLE so that it can make me a nice table without lines and lines of code in my view file. To achieve that, I need for example to: 1) Add columns to hold icons and links and extra stuff. 2) Customize the rows, e.g. links which depend on content, different icons etc You can add any column to select result. The result can be passed to SQLTABLE. See: http://groups.google.com/group/web2py/browse_thread/thread/826a37f56c26d689/210036457d278cdc?lnk=gstq=patch+to+make+Rows.setvirtualfields+work+with+SQLTABLE#210036457d278cdc
[web2py] Re: Manipulate Rows Object and/or SQLTABLE
I want to customize the result of SQLTABLE so that it can make me a nice table without lines and lines of code in my view file. To achieve that, I need for example to: You can add any column to select result. The result can be passed to SQLTABLE. See: http://groups.google.com/group/web2py/browse_thread/thread/826a37f56c26d689/210036457d278cdc?lnk=gstq=patch+to+make+Rows.setvirtualfields+work+with+SQLTABLE#210036457d278cdc
Re: [web2py] Re: Manipulate Rows Object and/or SQLTABLE
Will the patch be included in Web2py? I hope Massimo likes it. So do I. Otherwise I will have to patch after every web2py update.
Re: [web2py] The primary key constraint on legacy tables
2010/11/14 Rishu pareshverma...@gmail.com:
Hi all,
I have a project in which I am supposed to use mysql. The database
already made up. I am facing an issue with the primary key check.
Whensover i insert a duplicate value for the primary key i end up with
a ticket rather than a graceful handling.
Here's the db model:
db.define_table('document',
Field('sno','integer',requires=[IS_NOT_EMPTY(),IS_NOT_IN_DB(db,'document.sno')]),
Field('doc_no','string',requires=[IS_NOT_EMPTY()]),
Field('issue','date',requires=[IS_NOT_EMPTY()]),
Field('expiry','date',requires=[IS_NOT_EMPTY()]),
Field('summary','text'),
primarykey=['sno'],
migrate=False,
)
and the error that i receive when duplicating is :
What is the type of the 'document.sno'?
If its a unique int just set its type to 'id' and don't use primarykey
and everything will work. If you there is some other unique int column
set it to type 'id'. If there is no unique int column create it, it
shell not break functioning of other software that use the database.
DAL keyed tables support is fragile and outdated.
[web2py] patch to make Rows.setvirtualfields work with SQLTABLE
motivation:
It wold by nice to have a tool to add arbitrary columns to
db().select() result
to be able to create multiple views of the same tables(including
joins).
That wold be especially valuable when dealing with legacy databases
when you can't design the database according to the web user
interface.
The easiest solution wold be something like:
class ExtraField:
def new_column(self):
if self.some_field_in_select==' something':
return A('some_action_link', _href=URL(
f=some_action_controller_function,
args=[self.id]))
#if select contains 'as' it shell be
'self.as.id'
#if select contains join it shell be
'self.table.id'
#'id' is just an example, any othe
field can be
#used
else:
return A('great thing', _href='http://www.web2py.com')
rows=db(db.some_table).select()
rows.colnames.append('some_table.new_column')
rows.setvirtualfields(some_table=ExtraField())
table=SQLTABLE(rows)
This will give an exception telling that
sqlhtml.py:SQLTABLE.__init__ can't do
field = sqlrows.db[tablename][fieldname]
because there is no 'new_column' in the database.
It wants to get the field from the db model to know how to render it.
This is doesn't matter when we add a new field to select result
because it shell(and will by a view) be cast to string.
The patch:
change
sqlhtml.py:SQLTABLE.__init__
field = sqlrows.db[tablename][fieldname]
to
try:
field = sqlrows.db[tablename][fieldname]
except:
field = None
change
sqlhtml.py:SQLTABLE.__init__
if field.represent:
r = field.represent(r)
to
if not field:
pass
elif field.represent:
r = field.represent(r)
---Now you are able to include a new field in SQLTABLE
containing anything(text, link, image, button, form, whatever)
depending on row contents(ExtraField.new_column(self): self is the
Row) ,
by adding the field to db().select() result,.
---Note:
in
class ExtraField:
name of the class doesn't matter. The only time you use it is
rows.setvirtualfields(some_table=ExtraField())
then address your new column by 'some_table.new_column' while rows
object exists.
---Limitation:
in
rows.colnames.append('some_table.new_column')
rows.setvirtualfields(some_table=ExtraField())
'some_table' shell be a table mentioned in your select and present in
the db,
otherwise
'some_table' will be added as a sub dict to Row object
(IMHO this is wrong, the Row object shell be 1d always, the 'as' info
shell be in column names)
and you will not see it.
WBR
Ivan.
---Disclaimer : my understanding of web2py is not mature. For real
thing ask Massimo.
PS.
---Thank you Massimo. web2py is a great stuff. 1 month I'm learning
python and web2py is the motivation.
PPS.
Can I use code or pre or other tags to post code on the list?
Re: [web2py] Re: A strange syntax error on updating a table row in a legacy db.
Thank you Massimo,
with KeyedTable I get the same errors.
BUT if tables defined like this:
db.define_table('cars',
Field('car_id','id'), #note 'id' type
Field('model_id','integer'),
migrate=False
)
#note no primaykey
everything works!
I think it shell be int the docs in BIG letters that KeyedTable shell
NOT be used if a table has a unique integer field that shell be mapped
to 'id' field by giving it 'id' type.
Re: [web2py] Re: 'DEMO_MODE' is not defined
You can simply put DEMO_MODE=False in your db.py
Re: [web2py] Re: plugin legacy mysql: generates web2py code to access your mysql legacy db
2010/10/7 DJ sebastianjaya...@gmail.com:
The legacy database to Web2py conversion would be a great add-on. I
get the following errors when I tried this script on a database with
tables having primary key 'id' set to autoincrement.
C:\Program Files (x86)\web2py\scriptsextract_mysql_models.py
bio:b...@nrcf
Traceback (most recent call last):
File C:\Program Files (x86)\web2py\scripts
\extract_mysql_models.py, line 106
, in module
print mysql(m.group(3),m.group(1),m.group(2))
File C:\Program Files (x86)\web2py\scripts
\extract_mysql_models.py, line 82,
in mysql
table_name = re.search('CREATE TABLE .(\S+). \(',
sql_lines[0]).group(1)
AttributeError: 'NoneType' object has no attribute 'group'
Looks like sql_lines[0] is empty, the script has found no lines in
output from mysqldump/mysql(they are statnalone programs that are part
of MySQL server).
Is MySQL server installed?
Can you run mysqldump and mysql programs from the directory where the
script is from command line?
Did you check if the script can access mysqldump and mysql commands?
Re: [web2py] Re: DAL, legacy keyed table, field references
Massimo, Denes,
thank you for your answers.
Before touching sql.py I have updated web2py to recent version(1.86.1)
and found that now references in KeyedTable do work now if string
notation is used like this:
Field('model_id', 'reference car_models.model_id'),
After this
print( getattr(db.car_models,'_referenced_by'))
prints
[('cars', 'model_id')]
But the reference field is not presented as a dropdown list in
appadmin insert form:
http://localhost:8000/monicar_db_test/appadmin/insert/db/cars
Same for web2py created tables.
What is more important on KeyedTables you can delete row from a
referenced table. Constraint will not work if its not in the SQL
schema. You can't emulate constrains at DAL level.
Total:
database field references are useless for legacy databases as they
don't keep db integrity and they
don't help in rendering db to html.
References only matter when a table is created by web2py model. Then
the constraint will be put in to the SQL schema and work at SQL server
level.
I think this shell be in the docs.
Also IMHO it would be useful to add
ondelete = ''SET NULL
constraint because
CASCADE kills information that shell be kept sometimes and
NO ACTION leaves references to deleted rows which need to be checked
with every select. Checking for NULL is simpler.
Re: [web2py] Re: DAL, legacy keyed table, field references
Hi Denes,
2010/10/3 DenesL denes1...@yahoo.ca:
Hi Ivan,
mysql is not one of the supported DBs for keyed tables:
only DB2, MSSQL, Ingres, and Informix have been added as per
I looked at dal.py (web2py 1.83.2 2010-08-15). It has
# list of drivers will be built on the fly
# and lists only what is available
drivers = []
[...]
try:
import MySQLdb
drivers.append('MySQL')
[...]
class MySQLAdapter(BaseAdapter):
types = {
'boolean': 'CHAR(1)',
so MySQL support is there? Or shell something else be added?
Re: [web2py] Re: DAL, legacy keyed table, field references
2010/10/3 mdipierro mdipie...@cs.depaul.edu: Since August we had many improvements in sql.py that were not reflected into dal.py. So shell I start hacking DAL or wait for improvements to be put in? web2py is still using sql.py and it will be a little while before we move to dal.py If I start hacking how do I make web2py use dal.py insted of sql.py?
Re: [web2py] Re: DAL, legacy keyed table, field references
2010/10/2 Mariano Reingart reing...@gmail.com
Did you try string notation for references?
db.define_table('cars',
Field('car_id','integer'),
Field('model_id', reference car_models.model_id),
Field('note','text'),
primarykey=['car_id'],
migrate=False
)
Anyway, if you are using single-field integer primary keys, i think
you may try id field type:
db.define_table('car_models',
Field('model_id','id'),
Field('model_name','string'),
migrate=False
)
db.define_table('cars',
Field('car_id','id'),
Field('model_id','reference car_models'),
Field('note','text'),
migrate=False
)
Tried both: string notation for references and making primary key field type
'id'. No luck.
Now I'm looking at SQLAlchemy(SA). Want to try to use it as legacy db
backend. It is realy easy to get db scheme from a db with SA. Just 3 lines
of code:
engine = create_engine('mysql://myuser:myp...@localhost/mydb', echo=True)
metadata = MetaData()
metadata.reflect(bind = engine)
and the scheme is in metadata, ready to provide access to all tables in the
db.
Re: [web2py] Re: DAL, legacy keyed table, field references
2010/10/3 mdipierro mdipie...@cs.depaul.edu ... but SQLFORM and web2py crud will not work with sqlalchemy. one reasons we cannot do something like SQLAlchemy's reflect in web2py is that the database is not aware of how web2py should treat the field. For example a 'varchar' field could be a 'string', a 'password', an 'upload', or a 'list something'. The db does not know so in web2py you have to declare (define_table) it. My idea is to create a 'database centric' webapp. A webapp that you can slap on a legacy db(mysql for now) with minimal effort. Requirements: 1. db is schema is a set of SQL commands 2. webapp shell autoload the schema and use tables field types field relations 3. the autogenerated webapp model shell name tables/fields exactly as in the db schema 4. the model shell be manually customisable to add information absent in the scheme(like foreign keys in MyISAM tables) 5. the model shell permit to create virtual tables from fields of real tables WITHOUT mapping tables to classes to assist unify interface to different but similar databases and to create 'views' composed from fields of different tables. 6. virtual tables shell be CRUDable 7. the model shell be reusable with different web frameworks so as much code as possible in the model. Requirements 1,4 are to let DBA and programmer think in same terms and avoid situations like: DBA - your program is producing crazy queries! You sort on field 'somefield' and its a BLOB! programmer - in my ORM 'somefield' is a password. DAL is almost up to the requirements. Its only real problem is the 'id' field issue but I can't fix it and SQLAlchemy(SA) offers so much more for legacy db. ... but SQLFORM and web2py crud will not work with sqlalchemy. maybe we can have another db adapter that doesn't issue SQL but calls SA functions? the database is not aware of how web2py should treat the field. It wold be relatively easy to add this information to the model manually OR we can put this information in the scheme comments like tis: CREATE TABLE testing ( name VARCHAR(5) ) COMMENT='field=web2pytype'; OR automatically derive this from field name. Any way SQLFORMs will deal mostly with 'virtual view tables'(see requirement 5) so we will be able to name field to specified convention OR(IMHO the right way) store this information in a separate table editable with the webapp. This will help in slapping the webapp on a db. Just autoload the scheme, check generated model for relations, add virtual view tables, go to web page to fill in SQLFORMs type information from a drop list... Maybe relations and virtual tables are also to be stored in a webapp editable tables? That would allow 0 coding legacy db CRUD...
[web2py] DAL, legacy keyed table, field references
Hello All,
I'm trying to make a web2py interface to a legacy db(mysql).
The db is defined like this:
CREATE TABLE `car_models` (
`model_id` int(10) unsigned NOT NULL auto_increment,
`model_name` text NOT NULL,
PRIMARY KEY (`model_id`)
)
CREATE TABLE `cars` (
`car_id` int(10) unsigned NOT NULL auto_increment,
`model_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`car_id`)
)
a model:
db.define_table('car_models',
Field('model_id','integer'),
Field('model_name','string'),
primarykey=['model_id'],
migrate=False
)
db.define_table('cars',
Field('car_id','integer'),
Field('model_id','integer'),
Field('note','text'),
primarykey=['car_id'],
migrate=False
)
works fine in appadmin. Shows tables, inserts/edits rows.
But if I'm trying to define a reference
db.define_table('cars',
Field('car_id','integer'),
Field('model_id', db.car_models.model_id),
Field('note','text'),
primarykey=['car_id'],
migrate=False
)
I get this error:
Error traceback
Traceback (most recent call last):
File /home/ivm/prg/web2py/gluon/restricted.py, line 186, in
restricted
exec ccode in environment
File /home/ivm/prg/web2py/applications/monicar_db_test/controllers/
appadmin.py, line 410, in ?
File /home/ivm/prg/web2py/gluon/globals.py, line 96, in lambda
self._caller = lambda f: f()
File /home/ivm/prg/web2py/applications/monicar_db_test/controllers/
appadmin.py, line 124, in insert
form = SQLFORM(db[table], ignore_rw=ignore_rw)
File /home/ivm/prg/web2py/gluon/sqlhtml.py, line 668, in __init__
if field.type == 'id':
File /home/ivm/prg/web2py/gluon/sql.py, line 2551, in __eq__
return Query(self, '=', value)
File /home/ivm/prg/web2py/gluon/sql.py, line 2993, in __init__
right = sql_represent(right, left.type, left._db._dbname,
left._db._db_codec)
File /home/ivm/prg/web2py/gluon/sql.py, line 555, in sql_represent
return str(int(obj))
ValueError: invalid literal for int(): id
Looks like DAL wants me to refere to a field of type 'id'
but if I change 'model_id' field type to id
db.define_table('car_models',
Field('model_id','id'),
Field('model_name','string'),
primarykey=['model_id'],
migrate=False
)
appadmin shows 'car_models' but doesn't let me edit rows
and shows empty 'cars' table(its not empty )
web2py 1.83.2
python2.4
Re: [web2py] Re: DAL, legacy keyed table, field references
2010/10/1 mdipierro mdipie...@cs.depaul.edu
keyed tables are only partially supported. Perhaps user Denes can say
more about that. For now here is a quick hack that should work:
Thank you for fast reply.
db.define_table('cars',
Field('car_id','integer'),
Field('model_id',
'integer',requires=IS_IN_DB(db,'car_models.model_id','%
(model_name)s')),
Field('note','text'),
primarykey=['car_id'],
migrate=False
)
Thats what I'm using now. Some nice tools (like WebGrid) don't work this
way. Maybe I'l try to fix WebGrid.
