Re: [web2py] Re: Programatically create Row

[David Orme]

Thanks for the suggestion. I don't need to create the form programmatically
- the factory is great for that. I've already got code to create the form
but want to create a dummy row on the server side to populate a display
example form.

Cheers,
David

On Sat, 13 Feb 2021, 08:37 Jacinto Parga,  wrote:

>
> http://www.web2py.com/books/default/chapter/29/07/forms-and-validators#SQLFORM-factory
>
> El viernes, 12 de febrero de 2021 a las 18:26:42 UTC+1, david...@gmail.com
> escribió:
>
>> I have a controller that displays confidential data from a table but want
>> to be able to show the form publicly with some dummy data. The controller
>> makes use of field representation and references so I basically need a Row.
>>
>> I can create the object I need using the table.insert() methods and then
>> db.rollback() to avoid committing the dummy data but is there a better
>> method. I've had a look at the internals of Row and suspect that trying to
>> create a Row object by hand would be pretty fraught, but is there a better
>> way than just using insert() and rollback()? It feels like using a
>> sledgehammer to crack a nut.
>>
>> Cheers,
>> David
>>
> --
> Resources:
> - http://web2py.com
> - http://web2py.com/book (Documentation)
> - http://github.com/web2py/web2py (Source code)
> - https://code.google.com/p/web2py/issues/list (Report Issues)
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "web2py-users" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/web2py/FSHPxYTxr24/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> web2py+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/web2py/5ce7ca44-d5d7-4854-96c9-99b78b14fcdcn%40googlegroups.com
> 
> .
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/CAJnoBj60Cus76nmCUyUh9dVQAdwdh4VMOsoedEyZ%3Dv3Maa3Ofw%40mail.gmail.com.

[web2py] Re: Altering reference table format in SQLFORM

[David Orme]

Thanks! I had played with the represent attribute, with no success, but 
using that first option within the controller works. Changing the validator 
seems a bit over the top just to change the format, but it works!

On Tuesday, 11 August 2020 16:07:36 UTC+1, villas wrote:
>
> Off the top of my head, maybe these ideas would put you on a better 
> track...  :)
>
> 1.  The reference field has a default validator, try changing it to 
> something like this:
>
> db.assignments.marker.requires = IS_IN_DB(db, 'markers.id', '%(last_name)s, 
> %(first_name)s (%(email)s)')
>
> 2. Check out the .represent attribute
>
>
>  
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/dc15ee08-5608-43b2-97fe-2c998aec5a9do%40googlegroups.com.

[web2py] Altering reference table format in SQLFORM

[David Orme]

I'm sure I'm missing something obvious but I am trying to alter the 
representation of a referenced field in a SQLFORM and failing hopelessly 
(Web2Py 2.20.4 on Python 3.7.7). The two tables involved are:

db.define_table('markers',
Field('first_name','string'),
Field('last_name','string'),
Field('email', 'string', requires=IS_EMAIL()),
format='%(first_name)s %(last_name)s')

db.define_table('assignments',
Field('student_first_name','string', notnull=True),
Field('student_last_name','string', notnull=True),
Field('marker','reference markers'))

Most of the time, I want marker names as "John Smith", so have set that as 
the format in the model. However in a SQLFORM to create a new assignment, I 
want the dropdown to be "Smith, Bob (b...@smith.org)", to make it easier to 
find people. So:

def new_assignment():
   
db.markers._format = '%(last_name)s, %(first_name)s (%(email)s)'

form = SQLFORM(db.assignments)

if form.process().accepted:
response.flash = 'Assignment created'
redirect(URL('assignments'))

return dict(form=form)

But that does not change what appears in the SQLFORM dropdown menu for 
marker. If I swap the format string in the model, yup, exactly as expected, 
but it doesn't seem to be respecting the local redefinition.



-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/a38b94bf-f5d6-47be-83d7-412fb49c51a6o%40googlegroups.com.

[web2py] Re: changing default redirect after failed login attempt.

[David Orme]

Just to note that I had a similar fix to make and I think this solution is 
flawed:
 

> auth.settings.controller = 'plugin_user_admin'
>
>
It's exactly the right setting, but initialising an Auth instance uses 
`settings.controller` to  initialise a whole bunch of links and I don't 
think these get updated if you alter the setting after the event. You can 
specify the controller when you create the Auth instance, and then the 
controller is used throughout the setup

auth = Auth(db, 
host_names=configuration.get('host.names'),
controller='plugin_user_admin')


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/df77cbc5-b6b5-44fd-ab2d-69ca3e90397fo%40googlegroups.com.

[web2py] Re: import_from_csv_file and auth.signature

[David Orme]

Well now that is embarrassing. Thanks!

For reference, this was my recipe to put at the top of a file that is going 
to populate multiple tables:

if db(db.auth_user).count() == 0:
#Bulk load account
admin_user_id = db.auth_user.insert(first_name='Bulk uploader')
else:
admin_user_id = db(db.auth_user.first_name == 'Bulk uploader').select().
first().id

for table in ['table','names']:
table_object = db[table]
table_object.created_by.default = admin_user_id
table_object.modified_by.default = admin_user_id



On Friday, 24 July 2020 12:46:12 UTC+1, villas wrote:
>
> Did you try...
>
> db.locations.created_by.default = 1 # or 
> whateverdb.locations.modified_by.update = 1
> prior to:  import_from_csv_file
>
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/c7a44eb0-d464-4c46-a8de-51f8b72fa4d0o%40googlegroups.com.

[web2py] import_from_csv_file and auth.signature

[David Orme]

Hello,

I've got a table like this:

db._common_fields.append(auth.signature)

db.define_table('locations',
Field('title', 'string'),
Field('capacity', 'integer'),
Field('celcat_code', 'string'),
Field('is_external', 'boolean', default=False),
format = lambda row: f"{row.title}")

I'm trying to populate that with legacy data using:

filepath = os.path.join(request.folder, 'static', 'data', 
'teaching_staff.csv')

with open(filepath, encoding="utf8") as csvfile:
db.teaching_staff.import_from_csv_file(csvfile)

And I get a ticket:

IntegrityError(FOREIGN KEY constraint failed)

I think what is going on is that - because no-one is logged in when that 
import is run - the `auth.signature` fields are None and so that is the 
constraint that is failing. Is there a canonical way to handle this? I had 
a quick look at the code and couldn't see an option for inserting default 
values. I can write a loop and add default values myself to individual 
record inserts but is there a standard way?

Many thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/5ff89449-5883-4310-af1b-d419949acb84o%40googlegroups.com.

[web2py] Re: Using parse_as_rest: problem with cross joins?

[David Orme]

Sorry, being dim. The web2py repo links out to pydal, so I can just clone 
web2py/web2py.

Cheers,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/f6537025-96bd-4a47-9526-7a45baab2b37%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Using parse_as_rest: problem with cross joins?

[David Orme]

Hi Massimo,

I saw that announcement shortly after submitting the question. I've 
downloaded the developer source (/static/nightly, with VERSION contents 
"Version 2.18.5-stable+timestamp.2019.04.08.04.22.03") and neither dbapi.py 
(nor it's recent rebranding as restapi.py) is in that zip. Is it reasonable 
to drop a clone of pydal/pydal into packages or is there a release coming 
out shortly with the new api in it?

Thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/a0ad4f5c-0431-48a8-9302-f3db54c22c4f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Using parse_as_rest: problem with cross joins?

[David Orme]

Hi,

I'm looking at parse_as_rest() to provide an API and can't get it to work 
as expected. I have two table: datasets and fields, with a 1 to N 
relationship and I want to create an API that returns datasets that have a 
field name matching a pattern so (using the tuple version of patterns to 
provide a pattern, base query and exposed fields).

patterns = [
("/field_name/{fields.field_name.contains}/data[datasets.id]", 
None, None),
]

I thought this was fine, but now I want to restrict results to the latest 
version of datasets either through:

patterns = [
("/field_name/{fields.field_name.contains}/data[datasets.id]",  (db.
datasets.latest == True), None),
]

or:
  
  parser = db.parse_as_rest(patterns, args, vars, queries=(db.datasets.latest 
== True))

That was returning datasets that are not the latest version. I stuck a 
print(dbset._select()) into pydal/helpers/rest.py to try and figure it out. 
I think that the example in the manual goes from 1 to N (people to pets), 
whereas here I am going from N to 1 (fields to datasets) and the underlying 
SQL from that select is performing a cross join:

SELECT * 
FROM "datasets" 
WHERE ("datasets"."id" IN (
SELECT "fields"."dataset_id" 
FROM "fields", "datasets" 
WHERE (("fields"."field_name" ILIKE '%search_text%') 
AND ("datasets"."latest" = 'T') ESCAPE '\'))); 

That cross join is breaking the link between the two tables. If I edit that 
by hand to check:

SELECT * 
FROM "datasets" 
WHERE ("datasets"."id" IN (
SELECT "datasets"."latest", "datasets"."id", "fields"."dataset_id" 
FROM "fields", "datasets" 
WHERE (("fields"."field_name" ILIKE '%search_text%') 
AND ("datasets"."latest" = 'T') ESCAPE '\')));

then I get rows like this:

 latest  | id  | dataset_id 
-+-+
 T   | 134 |177
 T   | 134 |177
 T   | 134 |177
 T   | 134 |180
 T   | 134 |180
 T   | 134 |180
 T   | 158 |177
 T   | 158 |177
 T   | 158 |177

What am I doing wrong?

Cheers,
David


 From looking at the code, it seems like the current options are:

1. If parse_at_rest() gets a queries object that isn't a dict, the is 
applied 

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/web2py/982e6d3f-a03e-4347-b23c-0860d3846a68%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] CORS headers on static file

[David Orme]

Hi,

I'm running a static website from an S3 bucket that calls an API running on 
web2py and I've run into a problem with CORS.  I can update the headers for 
API calls by editing the calls() controller:

def call():
"""
exposes services. for example:
http:///[app]/default/call/jsonrpc
decorate with @services.jsonrpc the functions to expose
supports xml, json, xmlrpc, jsonrpc, amfrpc, rss, csv
"""


# Set response headers
response.headers['Pragma'] = None
response.headers['Access-Control-Allow-Origin'] = '*'
response.headers['Access-Control-Allow-Headers'] = 'Content-Type'
response.headers['Access-Control-Allow-Methods'] = 'GET, OPTIONS'


# Dump the session to remove Set-Cookie
session.forget(response)


return service()


What I can't figure out how to do is to provide those headers for some 
static resources. I could wrap them up in a service and cache them, but is 
there a way to preserve the existing path and adjust the CORS headers? A 
quick look at gluon/main.py makes it seem tricky, but there are a lot of 
moving parts in there!

Cheers,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Return random row on left join in one to many

[David Orme]

Hi,

I have two tables: `tx` contains a set of named objects and then `tx_vals` 
contain 0 to many possible values for those objects. I want to obtain a 
table that contains all named objects in `tx` and one random value from 
`tx_vals`. I think that has to be a left join to get the null values when a 
tx object does not appear in tx_vals. I can obviously do this by finding 
all the tx rows and then running a loop of queries on tx_vals, but I 
wondered if there was a way to get the DAL to do it in one pass.

The table structure is:

db.define_table('tx', Field('name', 'string'))
db.tx.bulk_insert([{'name':'a'}, 
   {'name':'b'},
   {'name':'c'}])

db.define_table('tx_vals', 
Field('tx_id', 'reference tx'), 
Field('val', 'integer'))

db.tx_vals.bulk_insert([{'tx_id': 1, 'val':1}, 
{'tx_id': 1, 'val':2}, 
{'tx_id': 2, 'val':3}, 
{'tx_id': 2, 'val':4})

What I'm after is something that returns:

[('a', 1), ('b', 3), ('c', None)]


and where `a` will get 1 or 2 and `b` will get 3 or 4 randomly.

Thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: AWS Lambda Serverless and Zappa

[David Orme]

Hi,

Just wanted to ask about that recipe. I'm using web2py to provide some 
database management pages and then an API to feed data to a front end. The 
front end developer is looking for speedups and was asking about deploying 
to now.sh, to take advantage of the CDN that sits in front of it for 
caching. That seems to be a very similar approach to what you've done - 
now.sh uses AWS Lambdas under the hood - and the configuration set up of 
zappa looks similar to now.sh.

It would be good to get some guidance if you've already solved some of the 
problems!

Cheers,
David


On Friday, 23 November 2018 06:56:39 UTC, appj...@gmail.com wrote:
>
> Hey arafay, Sorry for the delay. 
>
> I plan to post a deployment recipe soon.  There are a lot of little 
> gotchas I've had to work through.  I was able to do it successfully with 
> Zappa (https://github.com/Miserlou/Zappa)
>
> I'm working on doing it for another app and should be able to post my 
> steps in the near future.
>
>
> On Saturday, October 6, 2018 at 1:17:09 PM UTC-5, arafay...@gmail.com 
> wrote:
>>
>> Hi there, 
>> Can you please tell me the procedure of migrating the web2py app in AWS 
>> Lamda or provide me a link that you are following ??
>>
>> On Saturday, September 8, 2018 at 12:30:28 PM UTC+5, appj...@gmail.com 
>> wrote:
>>>
>>> I am trying to migrate my Web2Py app from Heroku to AWS Lambda, using 
>>> Zappa.  I can get it to start running, but I'm running into errors because 
>>> web2py is trying to write to the filesystem:
>>>
>>>
>>> [Errno 30] Read-only file system: '/var/task/deposit': OSError
>>> Traceback (most recent call last):
>>> File "/var/task/handler.py", line 574, in lambda_handler
>>> return LambdaHandler.lambda_handler(event, context)
>>> File "/var/task/handler.py", line 244, in lambda_handler
>>> handler = cls()
>>> File "/var/task/handler.py", line 150, in __init__
>>> import gluon.main
>>> File "/var/task/gluon/main.py", line 64, in 
>>> create_missing_folders()
>>> File "C:\web2py\gluon\admin.py", line 460, in create_missing_folders
>>> File "C:\web2py\gluon\admin.py", line 454, in try_mkdir
>>> OSError: [Errno 30] Read-only file system: '/var/task/deposit'.  
>>>
>>> So I commented out the following functions from gluon/main.py
>>>
>>> create_missing_app_folders() 
>>> and
>>> create_missing_folders() 
>>>
>>>
>>> However, now I'm getting stuck with the DB trying to create tables:
>>>
>>>
>>> [ERROR] 2018-09-08T07:05:35.270Z 905ef1e6-b335-11e8-816d-07b31200db80 
>>> Traceback (most recent call last):
>>> File "/var/task/gluon/restricted.py", line 219, in restricted
>>> exec(ccode, environment)
>>> File "/var/task/applications/ads/models/db.py", line 72, in 
>>> session.connect(request, response, db=db)
>>> File "C:\web2py\gluon\globals.py", line 946, in connect
>>> File "/var/task/gluon/packages/dal/pydal/base.py", line 587, in 
>>> define_table
>>> table = self.lazy_define_table(tablename, *fields, **args)
>>> File "/var/task/gluon/packages/dal/pydal/base.py", line 621, in 
>>> lazy_define_table
>>> polymodel=polymodel)
>>> File "/var/task/gluon/packages/dal/pydal/adapters/base.py", line 797, in 
>>> create_table
>>> return self.migrator.create_table(*args, **kwargs)
>>> File "/var/task/gluon/packages/dal/pydal/migrator.py", line 275, in 
>>> create_table
>>> query), table)
>>> File "/var/task/gluon/packages/dal/pydal/migrator.py", line 483, in log
>>> logfile = self.file_open(table._loggername, 'ab')
>>> File "/var/task/gluon/packages/dal/pydal/migrator.py", line 491, in 
>>> file_open
>>> fileobj = portalocker.LockedFile(filename, mode)
>>> File "C:\web2py\gluon\packages\dal\pydal\contrib\portalocker.py", line 
>>> 185, in __init__
>>> File "C:\web2py\gluon\packages\dal\pydal\contrib\portalocker.py", line 
>>> 170, in open_file
>>> IOError: [Errno 2] No such file or directory: 
>>> '/var/task/applications/ads/databases/sql.log'
>>>
>>>
>>> Is this a never-ending rabbit hole or is there a way to tell web2py that 
>>> it cannot write to the filesystem.  I tried 
>>> global_setttings.web2py_runtime_gae=True
>>>
>>> but that causes a bunch of other problems because GAE resources are not 
>>> present.
>>>
>>> I'd love to get this on AWS Lambda because my application can have 
>>> temporary 1000x spikes in traffic and Serverless lets me handle this 
>>> without ops and without paying for idle server time in the evenings.
>>>
>>> If I can get this working I will post a deployment recipe.
>>>
>>> Thanks,
>>> -Mike
>>>
>>>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: ST_Transform in GIS support

[David Orme]

So, in addition to st_transform, I would add st_aswkb. This isn't an 
official Postgis command - it provides a method to bypass the automatic 
conversion to WKT found in here:

https://github.com/web2py/pydal/blob/master/pydal/adapters/base.py#L550

With a suitably modified PostgreRepresenter, that then allows this:


from pydal import geoPoint, geoLine, geoPolygon

trans_test = db.define_table('transform_test', 
 Field('point', 'geometry()'), 
 Field('point_wec', 'geometry(public, 4087, 2)'
))

trans_test.bulk_insert([{'point': geoPoint(0,0)}, {'point': geoPoint(3,0)}])
# [1L, 2L]

db(db.transform_test).select().as_list()
# [{'id': 1L, 'point': 'POINT(0 0)', 'point_wec': None},
#  {'id': 2L, 'point': 'POINT(3 0)', 'point_wec': None}]

copy_wkb = db(db.transform_test.id == 1).select(db.transform_test.point.
st_aswkb().with_alias('wkb')).first()

rec = db.transform_test[2]
rec.update_record(point=copy_wkb['wkb'])
# 

db(db.transform_test).select().as_list()
# [{'id': 1L, 'point': 'POINT(0 0)', 'point_wec': None},
#  {'id': 2L, 'point': 'POINT(0 0)', 'point_wec': None}]

This avoids the round trip through WKT. 

Note also that the extended WKB provided by PostGIS contains a reference to 
the geometry SRID, so trying to update a field with a different SRID gets 
caught, as you might hope.

In [10]: rec.update_record(point_wec=copy_wkb['wkb'])
---
ProgrammingError  Traceback (most recent call last)
# 
ProgrammingError: ('ERROR', '22023', 'Geometry SRID (4326) does not match 
column SRID (4087)')

I don't think this breaks any existing functions. 

Cheers,
David



-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: ST_Transform in GIS support

[David Orme]

I guess more generally there is an issue with having to do a round trip 
through WKT to update a record - it loses precision and is there is an 
explicit warning about using it for passing data:

http://postgis.org/docs/ST_AsText.html

I don't see any point in altering how the select represents the record (and 
WKT is more interpretable) but it would be better if you could pass a WKB 
straight back to the record. The update method avoids the problem but I 
think it might be fixable by updating the representer here:

https://github.com/web2py/pydal/blob/master/pydal/representers/postgre.py#L22

If the first character of the value is 0 (zero) then the code could attempt 
to treat the value as WKB and just return the value (possibly with SRID) 
rather than having to put it through ST_GeomFromText(). The backend 
validates WKB inputs, so you can't just pass in any old nonsense starting 
with a zero.

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] ST_Transform in GIS support

[David Orme]

Hi,

I wanted to see if anyone has opinions on adding ST_Transform in to the 
list of available GIS commands. I've only looked at this from the point of 
view of PostgreSQL and PostGIS, but there is precedent for having Postgre 
only GIS functions. The basic argument is that coordinate transformation is 
a fundamental part of GIS functionality that would be helpful to expose via 
the DAL. In my case, I have users providing coordinates as latitude and 
longitude and want to make distance calculations - we have st_distance but 
it is essentially useless on geographic coordinates.

We can always use `db.executesql` to run more complex GIS commands 
directly, but I don't think it is too hard to make it transformation 
through the DAL. I've got some code I can push but I wanted to see if I'm 
reinventing the wheel or if there is an implementation issue I haven't 
considered.

First, some demo data:

from pydal import geoPoint, geoLine, geoPolygon

# table with two geometry columns, one as WGS84, one as World Equidistant 
Cylindrical
trans_test = db.define_table('transform_test', 
 Field('point', 'geometry()'), 
 Field('point_wec', 'geometry(public, 4087, 2)'
))

trans_test.bulk_insert([{'point': geoPoint(0,0)}, {'point': geoPoint(3,0)}])

There are some caveats about the current implementation and the GIS data 
formats that get passed. Record updates work expect a geometry to be 
provided as Well Known Text (WKT) and it seems like the underlying Well 
Known Binary (WKB) representation is always translated to WKT when a 
geometry field is selected. You might expect the output of these to differ 
in the data, but they don't:

db(db.transform_test.id).select(db.transform_test.point).as_list()
#[{'point': 'POINT(0 0)'}, {'point': 'POINT(3 0)'}]

db(db.transform_test).select(db.transform_test.point.st_astext()).as_list()
#[{'_extra': {'ST_AsText("transform_test"."point")': 'POINT(0 0)'}},
# {'_extra': {'ST_AsText("transform_test"."point")': 'POINT(3 0)'}}]

Other functions do return WKB. Compare:

db(db.transform_test).select(db.transform_test.point.st_simplify(0)).as_list
()
#[{'_extra': {'ST_Simplify("transform_test"."point",0.0)': 
'010120E610'}},
# {'_extra': {'ST_Simplify("transform_test"."point",0.0)': 
'010120E6100840'}}]

db(db.transform_test).select(db.transform_test.point.st_simplify(0).
st_astext()).as_list()
#[{'_extra': {'ST_AsText(ST_Simplify("transform_test"."point",0.0))': 
'POINT(0 0)'}},
# {'_extra': {'ST_AsText(ST_Simplify("transform_test"."point",0.0))': 
'POINT(3 0)'}}]

If we want to update a record with a simplified geometry, then we have to 
pass in WKT, so have to do this:

simplified = db(db.transform_test.id == 2).select(db.transform_test.point.
st_simplify(0).st_astext().with_alias('simp')).first()
rec =  db.transform_test[2]
rec.update_record(point = simplified['simp'])

# 

But you can do it much more simply using a direct update, because the 
underlying WKB data never leaves the backend.

db(db.transform_test.id == 2).update(point = db.transform_test.point.
st_simplify(0))

I'd suggest that an st_transform function follows the model of st_simplify. 
Given the existing code as model, I have an implementation that seems to 
work:

db(db.transform_test.id).select(db.transform_test.point.st_transform(4087)).
as_list()
#[{'_extra': {'ST_Transform("transform_test"."point",4087)': 
'010120F70F'}},
# {'_extra': {'ST_Transform("transform_test"."point",4087)': 
'010120F70F2589B7E319621441'}}]

db(db.transform_test.id).select(db.transform_test.point.st_transform(4087).
st_astext()).as_list()
#[{'_extra': {'ST_AsText(ST_Transform("transform_test"."point",4087))': 
'POINT(0 0)'}},
# {'_extra': {'ST_AsText(ST_Transform("transform_test"."point",4087))': 
'POINT(333958.472379821 0)'}}]

I've chosen the WEC projection because that is easy to verify that a point 
3 degrees east along the equator from the origin has a X cooordinate in WEC 
of 1/120th of the diameter of the Earth given the underlying radius at the 
equator in the WGS84 datum:

import math
(6378137 * 2 * math.pi) / 120
333958.4723798207

We can then do either:

transformed = db(db.transform_test.id == 2).select(db.transform_test.point.
st_transform(4087).st_astext().with_alias('wec')).first()
rec =  db.transform_test[2]
rec.update_record(point_wec = transformed['wec'])

#

or 

db(db.transform_test.id == 2).update(point_wec = db.transform_test.point.
st_transform(4087))

Thoughts? I haven't made a pull request to pydal but I'm happy to if this 
seems sensible.

Cheers,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-u

[web2py] Re: Geometry fields and validators

[David Orme]

Thanks Leonel,

I'd actually seen that solution, but that is specifically validating that 
the coordinates of a Point(X Y) WKT string are in the domain of a 
geographic coordinate system. I'm interested in the more general assessment 
of whether an alleged WKT string is well formatted - could be any of the 
geometry types.

My solution doesn't actually work without importing the Exception - and 
those are specific to backends. This fixes it, but is this the best way to 
catch the error in a general way?


if geometry is not None:
try:
geo_wkb = db.executesql("select 
st_geomfromtext('{}');".format(geometry))
except db._adapter.driver.ProgrammingError:
raise HTTP(404, "Invalid WKT Geometry")

Cheers,
David



On Friday, 15 March 2019 13:21:43 UTC, David Orme wrote:
>
>
> Hi,
>
> I'm looking for an approach to validate a user provided WKT geometry. The 
> obvious answer is to let the spatial backend do the validation, so this 
> works:
>
> if geometry is not None:
> try:
> geometry = db.executesql("select 
> st_geomfromtext('{}');".format(geometry))
> except ProgrammingError:
> raise HTTP(404, "Invalid WKT Geometry")
>
> I suspect that is probably pretty robust across backends but am I missing 
> something more general?
>
> Cheers,
> David
>
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Geometry fields and validators

[David Orme]

Hi,

I'm looking for an approach to validate a user provided WKT geometry. The 
obvious answer is to let the spatial backend do the validation, so this 
works:

if geometry is not None:
geometry = db.executesql("select 
st_geomfromtext('{}');".format(geometry))
except ProgrammingError:
raise HTTP(404, "Invalid WKT Geometry")

I suspect that is probably pretty robust across backends but am I missing 
something more general?

Cheers,
David


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Re: Left join with filters on joined table

[David Orme]

Thanks, I hadn't realised that db.table.on() could be passed more complex 
queries. I'd tried feeding a list of queries - the online book mentions 
that left=  can take an iterable - but each list item creates a separate 
left join statement. Being able to pass a single query including qualifiers 
is exactly what I needed.

I particularly wanted to be able to create the db.table.on() dynamically, 
to add a set of filters if requested and that turns out to be easy -  
create the main left join as a query object and then &= on filter if there 
are any, before passing that to db.table.on()

filters = (db.audio.start_time > '12:00:00')

qry = (db.audio.site_id == db.sites.id)

if filters is not None:
qry &= filters

data = db().select(db.sites.ALL,
   db.audio.id.count().with_alias('n_audio'),
   left=db.audio.on(qry),
   groupby=db.sites.id)

Thanks again,
David

On Saturday, 24 November 2018 23:20:31 UTC, Val K wrote:
>
> Try to move db.audio.start_time > '12:00:00'  in the left join ON:
> left=db.audio.on((db.audio.site_id == db.sites.id) & (db.audio.start_time 
> > '12:00:00'))
>
>
> On Sunday, November 25, 2018 at 1:34:54 AM UTC+3, David Orme wrote:
>>
>> Thanks for the suggestion. I've actually tried that and get exactly the 
>> same results - I read somewhere that using an empty db() could be 
>> problematic, so tried both.
>>
>> It doesn't affect the result without any audio queries, but any audio 
>> queries added into the selected set (qry in my code), cause the left join 
>> to get reduced. I have to admit I'm not completely sure why, but the SQL 
>> examples show it happening!
>>
>> David
>>
>>
>> On Sat, 24 Nov 2018, 22:18 Val K >
>>> try remove db.sites from query (just db().select(...))
>>>  db(db.sites) is shortcut for  db(db.sites.id>0) which turns into  
>>> WHERE ((sites.id IS NOT NULL) 
>>>
>>>
>>> On Sunday, November 25, 2018 at 1:00:56 AM UTC+3, David Orme wrote:
>>>>
>>>> Hi,
>>>>
>>>> I've got a table 'sites' and a table 'audio', where sites.id = 
>>>> audio.site_id. I'm trying to create a service that returns a list of sites 
>>>> with the number of audio recordings at each site, including zero counts. 
>>>> That's easily achieved using:
>>>>
>>>> sitedata = db(db.sites).select(db.sites.ALL,
>>>>db.audio.id.count().with_alias('n_audio'
>>>> ),
>>>>left=db.audio.on(db.audio.site_id == db.
>>>> sites.id),
>>>>groupby=db.sites.id)
>>>>
>>>> The problem arises when I want to count only a subset of the audio 
>>>> table, controlled by variables passed in with the call. At the moment, I'm 
>>>> trying to implement that by chaining together queries to add in filters. 
>>>> So 
>>>> for example:
>>>>
>>>> qry = db(db.sites)
>>>>
>>>> # code modifies qry, such as:
>>>> qry = qry(db.audio.start_time > '12:00:00')
>>>>
>>>> sitedata = qry.select(db.sites.ALL,
>>>>   db.audio.id.count().with_alias('n_audio'),
>>>>   left=db.audio.on(db.audio.site_id == db.sites.id
>>>> ),
>>>>   groupby=db.sites.id)
>>>>
>>>> Now that runs, but it loses the entry for each site, retaining only 
>>>> those where some data is present. The filtered example above produces:
>>>>
>>>> SELECT  sites.id, ... , COUNT(audio.id) AS n_audio 
>>>> FROM sites LEFT JOIN audio ON (audio.site_id = sites.id) 
>>>> WHERE ((sites.id IS NOT NULL) 
>>>> AND (audio.start_time > '12:00:00')) 
>>>> GROUP BY sites.id;"
>>>>
>>>> As I understand it, that where clause outside of the Left Join filters 
>>>> the result of the join, dropping rows, and what I need to achieve is to 
>>>> move the filter clauses inside the left join. The filters are going to 
>>>> need 
>>>> to include > < as shown but also an option using .belongs() to test for 
>>>> particular values.
>>>>
>>>> SELECT  sites.id, ..., COUNT(audio.id) AS n_audio 
>>>> FROM sites LEFT JOIN audio 
>>>> ON (audio.site

Re: [web2py] Re: Left join with filters on joined table

[David Orme]

Thanks for the suggestion. I've actually tried that and get exactly the
same results - I read somewhere that using an empty db() could be
problematic, so tried both.

It doesn't affect the result without any audio queries, but any audio
queries added into the selected set (qry in my code), cause the left join
to get reduced. I have to admit I'm not completely sure why, but the SQL
examples show it happening!

David


On Sat, 24 Nov 2018, 22:18 Val K  try remove db.sites from query (just db().select(...))
>  db(db.sites) is shortcut for  db(db.sites.id>0) which turns into  WHERE
> ((sites.id IS NOT NULL)
>
>
> On Sunday, November 25, 2018 at 1:00:56 AM UTC+3, David Orme wrote:
>>
>> Hi,
>>
>> I've got a table 'sites' and a table 'audio', where sites.id =
>> audio.site_id. I'm trying to create a service that returns a list of sites
>> with the number of audio recordings at each site, including zero counts.
>> That's easily achieved using:
>>
>> sitedata = db(db.sites).select(db.sites.ALL,
>>db.audio.id.count().with_alias('n_audio'),
>>left=db.audio.on(db.audio.site_id == db.
>> sites.id),
>>groupby=db.sites.id)
>>
>> The problem arises when I want to count only a subset of the audio table,
>> controlled by variables passed in with the call. At the moment, I'm trying
>> to implement that by chaining together queries to add in filters. So for
>> example:
>>
>> qry = db(db.sites)
>>
>> # code modifies qry, such as:
>> qry = qry(db.audio.start_time > '12:00:00')
>>
>> sitedata = qry.select(db.sites.ALL,
>>   db.audio.id.count().with_alias('n_audio'),
>>   left=db.audio.on(db.audio.site_id == db.sites.id),
>>   groupby=db.sites.id)
>>
>> Now that runs, but it loses the entry for each site, retaining only those
>> where some data is present. The filtered example above produces:
>>
>> SELECT  sites.id, ... , COUNT(audio.id) AS n_audio
>> FROM sites LEFT JOIN audio ON (audio.site_id = sites.id)
>> WHERE ((sites.id IS NOT NULL)
>> AND (audio.start_time > '12:00:00'))
>> GROUP BY sites.id;"
>>
>> As I understand it, that where clause outside of the Left Join filters
>> the result of the join, dropping rows, and what I need to achieve is to
>> move the filter clauses inside the left join. The filters are going to need
>> to include > < as shown but also an option using .belongs() to test for
>> particular values.
>>
>> SELECT  sites.id, ..., COUNT(audio.id) AS n_audio
>> FROM sites LEFT JOIN audio
>> ON (audio.site_id = sites.id)
>> AND ((sites.id IS NOT NULL)
>> AND (audio.start_time > '12:00:00'))
>> GROUP BY sites.id;
>>
>> That last query generates the output I need but I can't figure out how to
>> pass them into the DAL. Any suggestions? I'm keen on the concept of passing
>> request.vars to a handler that can be shared by different calls and returns
>> a suitably tweaked subset of audio to be searched, but it seems like I need
>> to pass the filters in somewhere else.
>>
>> Thanks,
>> David
>>
>>
>>
>> --
> Resources:
> - http://web2py.com
> - http://web2py.com/book (Documentation)
> - http://github.com/web2py/web2py (Source code)
> - https://code.google.com/p/web2py/issues/list (Report Issues)
> ---
> You received this message because you are subscribed to a topic in the
> Google Groups "web2py-users" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/web2py/RIeIVoLbGy8/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> web2py+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Left join with filters on joined table

[David Orme]

Hi,

I've got a table 'sites' and a table 'audio', where sites.id = 
audio.site_id. I'm trying to create a service that returns a list of sites 
with the number of audio recordings at each site, including zero counts. 
That's easily achieved using:

sitedata = db(db.sites).select(db.sites.ALL,
   db.audio.id.count().with_alias('n_audio'),
   left=db.audio.on(db.audio.site_id == db.sites
.id),
   groupby=db.sites.id)

The problem arises when I want to count only a subset of the audio table, 
controlled by variables passed in with the call. At the moment, I'm trying 
to implement that by chaining together queries to add in filters. So for 
example:

qry = db(db.sites)

# code modifies qry, such as:
qry = qry(db.audio.start_time > '12:00:00')

sitedata = qry.select(db.sites.ALL,
  db.audio.id.count().with_alias('n_audio'),
  left=db.audio.on(db.audio.site_id == db.sites.id),
  groupby=db.sites.id)

Now that runs, but it loses the entry for each site, retaining only those 
where some data is present. The filtered example above produces:

SELECT  sites.id, ... , COUNT(audio.id) AS n_audio 
FROM sites LEFT JOIN audio ON (audio.site_id = sites.id) 
WHERE ((sites.id IS NOT NULL) 
AND (audio.start_time > '12:00:00')) 
GROUP BY sites.id;"

As I understand it, that where clause outside of the Left Join filters the 
result of the join, dropping rows, and what I need to achieve is to move 
the filter clauses inside the left join. The filters are going to need to 
include > < as shown but also an option using .belongs() to test for 
particular values.

SELECT  sites.id, ..., COUNT(audio.id) AS n_audio 
FROM sites LEFT JOIN audio 
ON (audio.site_id = sites.id) 
AND ((sites.id IS NOT NULL) 
AND (audio.start_time > '12:00:00')) 
GROUP BY sites.id;

That last query generates the output I need but I can't figure out how to 
pass them into the DAL. Any suggestions? I'm keen on the concept of passing 
request.vars to a handler that can be shared by different calls and returns 
a suitably tweaked subset of audio to be searched, but it seems like I need 
to pass the filters in somewhere else.

Thanks,
David



-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Download link for dynamic upload folder

[David Orme]

And in my case, using the represent option to feed an id variable to the 
URL,  a download function like this works:


from gluon.contenttype import contenttype

def download_dataset():

# get the id and record
ds_id = request.vars['id']

if ds_id is None:
# non-existent id provided
session.flash = "Database id not provided"
redirect(URL('datasets','administer_datasets'))

try:
ds_id = int(ds_id)
except ValueError:
# non-integer id provided
session.flash = "Database id not an integer"
redirect(URL('datasets','administer_datasets'))

record = db.datasets[ds_id]

if record is None:
# non-existent id provided
session.flash = "Database record id does not exist"
redirect(URL('datasets','administer_datasets'))

# get the file name into the download header
file_header = "attachment; filename=" + record.file_name

# set up the response
response.headers['ContentType'] = contenttype(record.file_name)
response.headers['Content-Disposition'] = file_header

# get a stream of the file
path = os.path.join(request.folder, 'uploads', 'datasets', 
str(record.dataset_id), record.file)
stream = open(path, 'rb')
return response.stream(stream)



On Saturday, 3 February 2018 21:44:23 UTC, David Orme wrote:
>
> Thanks, Anthony. So to sum up what I think the options are:
>
> 1) The code laying out the standard SQLFORM.grid record view handles the 
> display of each field and - for upload fields - the download link uses: 
>
> https://.../controller/sqlform_grid_function/download/file_name
>
> That can be changed using the upload argument to SQLFORM grid. If you pass 
> in a string (like the output of URL) then file name value is appended,  so:
>
> form = SQLFORM.grid(..., upload = URL('dataset', 'download_dataset'), ...)
>
> will set the download link to:
>
> https://.../dataset/download_dataset/file_name
>
> You can pass in a function instead, in which case the output of the 
> function is used as the link (*without* appending the file name value). 
> So if you do:
>
> form = SQLFORM.grid(..., upload = lambda value: URL('datasets', 
> 'download_dataset'), ...)
>
> then the link is  not terribly functional:
>
> https://.../dataset/download_dataset
>
> The only local variable that function has access to is the file name, so 
> this bit of code provides a function that duplicates just providing the URL 
> as a string:
>
> form = SQLFORM.grid(..., upload = lambda value: URL('datasets', 
> 'download_dataset', value), ...)
>
> That isn't particularly useful, but the value could be used in a more 
> complex way. For example, as Anthony says, by looking up the filename 
> (which will be unique, thanks to the random component of the internal file 
> names) in the database table and using this to populate a link with the 
> custom download information. For example, this function allows the URL to 
> be expanded to use other parts of the record for that file:
>
> def _lookup(value):
> record = db(db.datasets.file == value).select().first()
> return URL('datasets','download_dataset', args=[record.dataset_id, 
> record.file])
> 
> form = SQLFORM.grid(...,  upload = lambda value: _lookup(value), ...)
>
> That produces links like this (for a record with dataset_id = 15):
>
> https://.../datasets/download_dataset/15/file_name
>
> I don't think there is any way to get the default download controller to 
> know about the nested download - it uses the field.retrieve method to get 
> the data and that doesn't know about the record specific upload folder. So, 
> I think you *have to* then use a custom function to provide the download. 
>
> Another alternative is to change the representation of the file field in 
> the controller function before creating the SQLFORM.grid. For example:
>
> db.datasets.file.represent = lambda value, row: A('Download file', _href=
> URL('datasets', 'download_dataset', vars={'id': row.id}))
>
> I thought that would derail the upload widget in the SQLFORM edit view but 
> it doesn't seem to. Its probably a simpler way to get a custom download 
> link that points to the record rather than just the filename, but doesn't 
> get around the need for a custom download function.
>
>
> On Saturday, 3 February 2018 17:37:32 UTC, Anthony wrote:
>>
>> On Saturday, February 3, 2018 at 1:58:26 AM UTC-5, David Orme wrote:
>>>

[web2py] Re: Download link for dynamic upload folder

[David Orme]

Thanks, Anthony. So to sum up what I think the options are:

1) The code laying out the standard SQLFORM.grid record view handles the 
display of each field and - for upload fields - the download link uses: 

https://.../controller/sqlform_grid_function/download/file_name

That can be changed using the upload argument to SQLFORM grid. If you pass 
in a string (like the output of URL) then file name value is appended,  so:

form = SQLFORM.grid(..., upload = URL('dataset', 'download_dataset'), ...)

will set the download link to:

https://.../dataset/download_dataset/file_name

You can pass in a function instead, in which case the output of the 
function is used as the link (*without* appending the file name value). So 
if you do:

form = SQLFORM.grid(..., upload = lambda value: URL('datasets', 
'download_dataset'), ...)

then the link is  not terribly functional:

https://.../dataset/download_dataset

The only local variable that function has access to is the file name, so 
this bit of code provides a function that duplicates just providing the URL 
as a string:

form = SQLFORM.grid(..., upload = lambda value: URL('datasets', 
'download_dataset', value), ...)

That isn't particularly useful, but the value could be used in a more 
complex way. For example, as Anthony says, by looking up the filename 
(which will be unique, thanks to the random component of the internal file 
names) in the database table and using this to populate a link with the 
custom download information. For example, this function allows the URL to 
be expanded to use other parts of the record for that file:

def _lookup(value):
record = db(db.datasets.file == value).select().first()
return URL('datasets','download_dataset', args=[record.dataset_id, 
record.file])

form = SQLFORM.grid(...,  upload = lambda value: _lookup(value), ...)

That produces links like this (for a record with dataset_id = 15):

https://.../datasets/download_dataset/15/file_name

I don't think there is any way to get the default download controller to 
know about the nested download - it uses the field.retrieve method to get 
the data and that doesn't know about the record specific upload folder. So, 
I think you *have to* then use a custom function to provide the download. 

Another alternative is to change the representation of the file field in 
the controller function before creating the SQLFORM.grid. For example:

db.datasets.file.represent = lambda value, row: A('Download file', _href=URL
('datasets', 'download_dataset', vars={'id': row.id}))

I thought that would derail the upload widget in the SQLFORM edit view but 
it doesn't seem to. Its probably a simpler way to get a custom download 
link that points to the record rather than just the filename, but doesn't 
get around the need for a custom download function.


On Saturday, 3 February 2018 17:37:32 UTC, Anthony wrote:
>
> On Saturday, February 3, 2018 at 1:58:26 AM UTC-5, David Orme wrote:
>>
>> So at the moment, what users are seeing is a controller presenting 
>> SQLFORM.grid of dataset records, and then in the 'view' argument to that 
>> controller for a particular record, the file field is shown as the output 
>> of the represent method of UploadWidget (so the word 'file' wrapped up 
>> with the download link, which is ignorant of the subfolder). It isn't 
>> obvious to me that there is a way to insert new_ds_id into that mechanism?
>>
>
> The "upload" argument of SQLFORM.grid can be a function that takes the 
> value and returns a URL, but you are still stuck having to figure out the 
> URL from the filename, which will require a database lookup per row of the 
> grid. An alternative is to not display the default file column in the grid 
> and instead use the "links" argument to generate a custom column with 
> custom download links (see "links" under 
> http://web2py.com/books/default/chapter/29/07/forms-and-validators#SQLFORM-grid-signature).
>  
> Another option is to create a custom column using a virtual field.
>
> Anthony
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Download link for dynamic upload folder

[David Orme]

So at the moment, what users are seeing is a controller presenting 
SQLFORM.grid of dataset records, and then in the 'view' argument to that 
controller for a particular record, the file field is shown as the output 
of the represent method of UploadWidget (so the word 'file' wrapped up with 
the download link, which is ignorant of the subfolder). It isn't obvious to 
me that there is a way to insert new_ds_id into that mechanism?

The new_ds_id (which is an integer) is stored in the record as 
datasets.dataset_id, so I've tried setting the uploadfolder in the model 
after the table definition using this:

db.datasets.file.uploadfolder = lambda id, row: os.path.join(request.folder, 
'uploads','datasets', str(row.dataset_id))

But that breaks - I get an error message:  'function' object has no attribute 'endswith'. 
I think that is field.retrieve firmly expecting a string and trying to find 
the end character of the lambda function.

So, if that isn't possible, then I need a custom view of the record, which 
represents the file with a custom download function that I can pass 
new_ds_id to? 

On Friday, 2 February 2018 22:04:26 UTC, David Orme wrote:
>
> Hi,
>
> I'm collecting dataset files from users. There can be several versions as 
> problems with the files get fixed and I want to organise the uploaded files 
> by their common dataset id. So in my controller I do this:
>
> # set the upload directory locally
> upload_dir = os.path.join(request.folder, 'uploads', 'datasets', str(
> new_ds_id))
> db.datasets.file.uploadfolder = upload_dir
> 
> # Setup the form
> form = SQLFORM(db.datasets, 
>record = record, 
>fields=['project_id', 'file'],
>showid=False,
>deletable=False,
>button='Upload')
>
> That works really nicely and I just have to remember to use that path 
> where I need to find the file within the code. 
>
> However, I can't work out how to get the download controller to work with 
> the folder structure. For example, I'm currently using SQLFORM.grid to 
> provide a table of uploaded datasets, and when users click through to view 
> a particular record then they get the nice automatically generated file 
> download link. Unfortunately, that has no idea that there is the extra 
> component in the path, so it doesn't work.
>
> I've had a look at the source for response.download, heading into 
> field.retrieve and it looks like I should be able to set a custom_retrieve, 
> but I can't find example usage.
>  Any suggestions?
>
> Many thanks,
> David
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Download link for dynamic upload folder

[David Orme]

Hi,

I'm collecting dataset files from users. There can be several versions as 
problems with the files get fixed and I want to organise the uploaded files 
by their common dataset id. So in my controller I do this:

# set the upload directory locally
upload_dir = os.path.join(request.folder, 'uploads', 'datasets', str(
new_ds_id))
db.datasets.file.uploadfolder = upload_dir

# Setup the form
form = SQLFORM(db.datasets, 
   record = record, 
   fields=['project_id', 'file'],
   showid=False,
   deletable=False,
   button='Upload')

That works really nicely and I just have to remember to use that path where 
I need to find the file within the code. 

However, I can't work out how to get the download controller to work with 
the folder structure. For example, I'm currently using SQLFORM.grid to 
provide a table of uploaded datasets, and when users click through to view 
a particular record then they get the nice automatically generated file 
download link. Unfortunately, that has no idea that there is the extra 
component in the path, so it doesn't work.

I've had a look at the source for response.download, heading into 
field.retrieve and it looks like I should be able to set a custom_retrieve, 
but I can't find example usage.
 Any suggestions?

Many thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] API for database sequence?

[David Orme]

Hi,

I've got a table where I want to group related rows with a unique id. I 
could use something like:

next_val = db.table.unique_id.max() + 1

But that does not seem safe against updates happening at almost the same 
time (I might be being paranoid). I could also use:

next_val = uuid.uuid4

But that seems like overkill - and an ugly thing to pass as a variable to a 
URL. So, the most obvious solution is to use a DB sequence (I'm using 
postgres, if it matters). That way I can grab the next value from the 
sequence, be sure I'm always getting a unique value and I've got simple 
integers. I can always create the sequence manually on the backend and then 
use:

next_val = db.executesql("select nextval('unique_id_seq');")

But is there an elegant way to do this within pydal? It would be nice to 
define the sequence in the model in the same way that tables are defined.

Thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Sharing function between controller and scheduler

[David Orme]

I've still got some very odd differences between the controller and the 
scheduler. The task function uses an external module, stored in the 
application `modules` folder. I've updated it recently, and deleted the 
.pyc before doing so, so it should all be up to date. I've also restarted 
the apache2 webserver.

If I run the function from the controller, it works as expected, but 
scheduled tasks are using the older version of the external module. I've no 
idea where it could be finding the code - is there anywhere I should look 
for cached versions?

Oh hang on... Is it that the worker processes were started when the old 
version was in place? Do you need to restart the workers after updating 
python modules? I've added new modules, so maybe new modules get added, but 
existing ones use the version in place when the worker started?

Thanks,
David



On Thursday, 17 August 2017 11:22:50 UTC+1, David Orme wrote:
>
> Hi,
>
> I have a function that checks an uploaded file. I want the check to get 
> scheduled automatically on upload but I also want admin user to be able to 
> run the check live from the website (it doesn't take too long). I currently 
> have a single function in a model that gets shared, but then it has to cope 
> with the  differences in the runtime environment for a controller and for a 
> scheduler worker.
>
> One example is that  worker does not have access to the host name from the 
> request environment, so  `URL(..., host=True)` provides localhost 
> (127.0.0.1). That's easy to solve by loading the host name via AppConfig(), 
> which is in both environments.
>
> The other one is that a worker needs to run db.commit() to get the DAL to 
> run any updates or inserts in the function. Easy enough to stick 
> `db.commit()` in before returning from the function, but the controller 
> will then also commit when it is run from the website. Is this a problem, 
> either for overhead or for the database? Are there any other issues which 
> mean I should keep the scheduler version and controller version separate?
>
> Thanks,
> David
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Sharing function between controller and scheduler

[David Orme]

Aha. That is very helpful. Thanks very much.

Cheers,
David

> On 17 Aug 2017, at 14:20, Leonel Câmara  wrote:
> 
> That seems fine to me. Note however that you can know if the function is 
> being run by the scheduler looking at request.is_scheduler
> 
> -- 
> Resources:
> - http://web2py.com 
> - http://web2py.com/book  (Documentation)
> - http://github.com/web2py/web2py  (Source 
> code)
> - https://code.google.com/p/web2py/issues/list 
>  (Report Issues)
> --- 
> You received this message because you are subscribed to a topic in the Google 
> Groups "web2py-users" group.
> To unsubscribe from this topic, visit 
> https://groups.google.com/d/topic/web2py/a7hHPXDX2OI/unsubscribe 
> .
> To unsubscribe from this group and all its topics, send an email to 
> web2py+unsubscr...@googlegroups.com 
> .
> For more options, visit https://groups.google.com/d/optout 
> .

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Sharing function between controller and scheduler

[David Orme]

Hi,

I have a function that checks an uploaded file. I want the check to get 
scheduled automatically on upload but I also want admin user to be able to 
run the check live from the website (it doesn't take too long). I currently 
have a single function in a model that gets shared, but then it has to cope 
with the  differences in the runtime environment for a controller and for a 
scheduler worker.

One example is that  worker does not have access to the host name from the 
request environment, so  `URL(..., host=True)` provides localhost 
(127.0.0.1). That's easy to solve by loading the host name via AppConfig(), 
which is in both environments.

The other one is that a worker needs to run db.commit() to get the DAL to 
run any updates or inserts in the function. Easy enough to stick 
`db.commit()` in before returning from the function, but the controller 
will then also commit when it is run from the website. Is this a problem, 
either for overhead or for the database? Are there any other issues which 
mean I should keep the scheduler version and controller version separate?

Thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Accessing all attributes for all SQLFORM grid rows

[David Orme]

Hi,

I've got a controller that is serving up a searchable SQLFORM grid of some 
map locations along with a Leaflet map containing the locations. The 
underlying table looks like this:

db.define_table('gazeteer',
Field('location', 'string', unique=True),
Field('type', 'string', requires=IS_IN_SET(gaz_types)),
Field('parent', 'string'),
Field('display_order', 'integer'),
Field('region','string', requires=IS_IN_SET(gaz_regions)),
Field('plot_size', 'string'),
Field('fractal_order', 'integer'),
Field('transect_order', 'integer'),
Field('centroid_x', 'float'),
Field('centroid_y', 'float'),
Field('geom_type', 'string'),
Field('geom_coords', 'json'))

I only want users to see a small number of those fields in the grid 
(location, type and a couple more) so I make the others unreadable. I want 
to do the following:

1) Populate the Leaflet map with features by passing GeoJSON (mostly just 
geom_type and geom_coords) for rows selected in the SQLFORM. The form 
object returned by SQLFORM.grid contains all the fields (even the ones with 
readable set to FALSE), but it only returns the first page of rows. This 
makes sense, since this is what is rendered, but I'd like to display all 
the features selected not just those in the current page. Options:

a) I could turn off pagination (but there are hundreds of rows) 
b) I can work around by intercepting the keywords, using 
SQLFORM.build_query and running a separate query to capture the row set 
used by SQLFORM.grid (which seems really clunky and does the same database 
query twice).


2) I want to provide a GPX file export, which requires the centroid_x and 
centroid_y fields. That's basically fine, I can provide a new Exporter 
class for the SQLFORM.grid. The Exporter does get all the rows (pagination 
presumably happens just before returning from SQLFORM.grid) but the problem 
now is that the unreadable fields are not present in form.rows. Options:

a) I could make centroid_x and centroid_y readable (looks cluttered, not 
really useful for users)
b) I could scan all the ids out of form.rows and go and look up the details 
(more DBIO).


The version below uses 1b and 2a but am I missing something simple that 
allows me to get at all the data in both cases? The CSV_hidden exporter 
suggests that Exporters can access hidden fields.


def gazeteer():

"""
Controller to provide a map view of the gazeteer data and a searchable
interface with GPX download.
"""

# If the grid has set up some search keywords, and the keywords aren't 
an empty 
# string then use them to select those rows, otherwise get all rows
sfields = [db.gazeteer.location, db.gazeteer.type, db.gazeteer.plot_size
, 
   db.gazeteer.fractal_order, db.gazeteer.transect_order]

if 'keywords' in request.get_vars and request.vars.keywords != '':
qry = SQLFORM.build_query(sfields, keywords=request.vars.keywords)
else:
qry = db.gazeteer

# get the (selected) rows and turn them into geojson, ordering them
# so that the bottom ones get added to the leaflet map first
rws = db(qry).select(orderby=db.gazeteer.display_order)
# removed formating code not relevant to question

# provide a single export format - GPX
export = dict(gpx=(ExporterGPX, 'GPX'), csv_with_hidden_cols=False,
  csv=False, xml=False, html=False, json=False,
  tsv_with_hidden_cols=False, tsv=False)

# hide these fields - except that we need them for GPX
# output and populating the leaflet map
db.gazeteer.id.readable = False
db.gazeteer.centroid_x.readable = True
db.gazeteer.centroid_y.readable = True
db.gazeteer.display_order.readable = False
db.gazeteer.geom_type.readable = False
db.gazeteer.geom_coords.readable = False
db.gazeteer.region.readable = False
db.gazeteer.parent.readable = False

form = SQLFORM.grid(db.gazeteer,
csv=True,
exportclasses=export,
maxtextlength=250,
deletable=False,
editable=False,
create=False,
details=False)

return dict(form=form, sitedata=json(rws))


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Representing field in IS_IN_DB

[David Orme]

That's great. Thanks - I simply hadn't thought that I could switch which 
table the IS_IN_DB 'table.value_field' argument was pointing to in order to 
shift which fields were available.

To give the specific answer for my case:

db.datasets.project_id.requires = IS_IN_DB(query, 
'project_details.project_id', 
   '(%(project_id)s) %(title)s',
   zero='Select project.')


Cheers,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Representing field in IS_IN_DB

[David Orme]

Hi,

I'm trying to set up a dropdown on a form to have representation text that 
merges information from multiple tables.

The table setup is one where I'm tracking users and datasets associated 
with projects. Because project details can get updated, I have a permanent 
project id, and the details (including the title) are stored in a different 
table and the version associated with an id can be updated. So the 
(stripped down) tables are:

# Permanent Project ID. The project details id gets updated as new versions 
arrive
db.define_table('project_id', 
Field('project_details_id', 'integer'))


# A table of details. 
db.define_table('project_details',
Field('project_id', 'reference project_id'),
Field('version', 'integer'),
Field('title','string', notnull=True)
format='%(title)s') 


# Tag users to projects
db.define_table('project_members',
Field('project_id', 'reference project_id', notnull=True),
Field('user_id', 'reference auth_user', notnull=True))


# Tag datasets to projects
db.define_table('datasets',Field('uploader_id', 'reference auth_user'),
Field('project_id', 'reference project_id'),
Field('file','upload'))


So, the controller below is to allow users to populate the datasets table. 
I'm using requires locally to restrict a user to uploading datasets for 
projects that they are a member of. 

def submit_dataset():
"""
Controller to upload a dataset to the website for checking
"""
  
# restrict choice of projects for standard users
query = db((db.project_members.user_id == auth.user.id) &
   (db.project_members.project_id == db.project_id.id) &
   (db.project_id.project_details_id == db.project_details.id))

db.datasets.project_id.requires = IS_IN_DB(query, 'project_id.id', 'Project 
%(id)s',
   zero='Select project.')

form = SQLFORM(db.datasets, fields=['project_id', 'file'])

This works well, but the resulting dropdown only has the project ID number, 
which is going to be confusing. What I'd like to do is change the 
representation of the dropdown so that it uses the project ID and project 
title, but only the id is stored in the datasets table. I guess I was 
hoping that something like this would work:

db.datasets.project_id.requires = IS_IN_DB(query, 'project_id.id',
   '(%(id)s) 
%(db.project_details.title)s',
   zero='Select project.')

Any solutions?

Many thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [web2py] Javascript load order and templates

[David Orme]

Ha, I was right:

> Do blocks allow me to split insert the contents of a view into different 
> parts of the layout? 

It’s a good thing that it is a really complex mechanism, so I don’t look like a 
total idiot ;-)

Many thanks - fantastic to get such a quick answer.

Cheers,
David



> On 31 Mar 2017, at 15:56, Carlos Costa  wrote:
> 
> A proper way to do this is to put the page javascript in the page_js block
> It is the declared in the default layout.html.
> 
> To use it just do this in your view
> 
> {{block page_js}}
>  ...put your js here...
> {{end page_js}}
> 
> This way it will be inserted in the right place into the rendered page.
> 
> 
> 2017-03-31 11:45 GMT-03:00 David Orme  <mailto:david.o...@gmail.com>>:
> Hello,
> 
> I'm using wavesurfer in a site, so have included it at the bottom of 
> layout.html.
> 
> 
> 
> 
>  src="<a  rel="nofollow" href="http://cdnjs.cloudflare.com/ajax/libs/wavesurfer.js/1.0.52/wavesurfer.min.js">http://cdnjs.cloudflare.com/ajax/libs/wavesurfer.js/1.0.52/wavesurfer.min.js</a>
>  
> <<a  rel="nofollow" href="http://cdnjs.cloudflare.com/ajax/libs/wavesurfer.js/1.0.52/wavesurfer.min.js">http://cdnjs.cloudflare.com/ajax/libs/wavesurfer.js/1.0.52/wavesurfer.min.js</a>>">
> 
> But then one of my views has javascript code to setup a wavesurfer player. 
> This is currently included at the bottom of the view - note that it includes 
> some web2py expressions to populate variables.
> 
> 
> 
>var wavesurfer = WaveSurfer.create({
>  container: '#waveform',
>});
>   wavesurfer.load("{{=URL('static', record.static_filepath)}}");
> 
> 
> It doesn't work, but I think for the simple reason that the script in the 
> view is included ahead of the scripts loaded in the layout (the contents of 
> the view all gets stuck inside {{include}} in the layout, right?). If I put 
> copy those layout javascript lines into the view ahead of my local inline 
> script, then it works. However, I don't think I want pages loading the same 
> javascript twice, so now I have to load those javascript files at the end of 
> every view.
> 
> Is there a canonical way to handle this? Do blocks allow me to split insert 
> the contents of a view into different parts of the layout? Or am I better off 
> turning the script into a static JS file that all pages load and adding a 
> simple order-agnostic javascript to define the java variables.
> 
> Many thanks,
> David
> 
> -- 
> Resources:
> - http://web2py.com <http://web2py.com/>
> - http://web2py.com/book <http://web2py.com/book> (Documentation)
> - http://github.com/web2py/web2py <http://github.com/web2py/web2py> (Source 
> code)
> - https://code.google.com/p/web2py/issues/list 
> <https://code.google.com/p/web2py/issues/list> (Report Issues)
> --- 
> You received this message because you are subscribed to the Google Groups 
> "web2py-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to web2py+unsubscr...@googlegroups.com 
> <mailto:web2py+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout 
> <https://groups.google.com/d/optout>.
> 
> 
> 
> -- 
> 
> 
> Carlos J. Costa
> Cientista da Computação  | BS Computer Science 
> Esp. Gestão em Telecom   | PgC Telecom Mangement
> <º))><
> 
> -- 
> Resources:
> - http://web2py.com <http://web2py.com/>
> - http://web2py.com/book <http://web2py.com/book> (Documentation)
> - http://github.com/web2py/web2py <http://github.com/web2py/web2py> (Source 
> code)
> - https://code.google.com/p/web2py/issues/list 
> <https://code.google.com/p/web2py/issues/list> (Report Issues)
> --- 
> You received this message because you are subscribed to a topic in the Google 
> Groups "web2py-users" group.
> To unsubscribe from this topic, visit 
> https://groups.google.com/d/topic/web2py/k0xtY1Jz9-s/unsubscribe 
> <https://groups.google.com/d/topic/web2py/k0xtY1Jz9-s/unsubscribe>.
> To unsubscribe from this group and all its topics, send an email to 
> web2py+unsubscr...@googlegroups.com 
> <mailto:web2py+unsubscr...@googlegroups.com>.
> For more options, visit https://groups.google.com/d/optout 
> <https://groups.google.com/d/optout>.

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Javascript load order and templates

[David Orme]

Hello,

I'm using wavesurfer in a site, so have included it at the bottom of 
layout.html.




http://cdnjs.cloudflare.com/ajax/libs/wavesurfer.js/1.0.52/wavesurfer.min.js";>

But then one of my views has javascript code to setup a wavesurfer player. 
This is currently included at the bottom of the view - note that it 
includes some web2py expressions to populate variables.



   var wavesurfer = WaveSurfer.create({
 container: '#waveform',
   });
  wavesurfer.load("{{=URL('static', record.static_filepath)}}");


It doesn't work, but I think for the simple reason that the script in the 
view is included ahead of the scripts loaded in the layout (the contents of 
the view all gets stuck inside {{include}} in the layout, right?). If I put 
copy those layout javascript lines into the view ahead of my local inline 
script, then it works. However, I don't think I want pages loading the same 
javascript twice, so now I have to load those javascript files at the end 
of every view.

Is there a canonical way to handle this? Do blocks allow me to split insert 
the contents of a view into different parts of the layout? Or am I better 
off turning the script into a static JS file that all pages load and adding 
a simple order-agnostic javascript to define the java variables.

Many thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Add a field to primary key in non-legacy tables

[David Orme]

I guess another alternative is to hand code it as SQL, so somewhere in 
application do this:

db.sqlexecute('alter table project_details drop constraint 
project_details_pkey;')
db.sqlexecute('alter table project_details add primary key (id, "Oid");')

I'm not sure where the best place to put that might be - it presumably 
shouldn't be in db.py where it will get repeatedly called, but somewhere in 
something like a zzz_indexing.py model file that checks first whether the 
primary keys have been modified?

Cheers,
David

On Thursday, 18 August 2016 13:35:36 UTC+1, David Orme wrote:
>
> Following up - I've tried some options to set up a table creating the 'id' 
> field explicitly to work around pairing the two keys.
>
> *A) Declare the field 'id' with type 'id' and set a two part primary key:*
>
> db.define_table('key_test',
> Field('id', 'id'),
> Field('oid', length=64, default=uuid.uuid4),
> primarykey = ['id', 'oid'])
>
> This doesn't work, I think because the type 'id' automatically makes it 
> the primary key.
>
> *B) Declare an integer field 'id' and set a two part primary key:*
>
> db.define_table('key_test',
> Field('id', 'integer'),
> Field('oid', length=64, default=uuid.uuid4),
> primarykey = ['id', 'oid'])
>
> The table is created without any problems, but id is not a serial with an 
> id value sequence. I can't immediately find a mechanism to set a field to 
> have an auto increment sequence independent of using a field type of 'id'.
>
> *C) More complexity with capitalised names*
>
> As if that wasn't enough, the other application is case sensitive and uses 
> Oid for the field. I can fix this using rname, but then I can't get option 
> B) to work because the names aren't recognized:
>
> db.define_table('key_test2',
> Field('id', 'integer'),
> Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
> primarykey = ['id', 'oid'])
>
> This throws the following back from the PGSQL backend:  'gluon.contrib.pg8000.ProgrammingError'> ('ERROR', '42703', 'column "oid" 
> does not exist'). Which is right, because it doesn't - "Oid" does. 
>
> However, this also fails
>
> db.define_table('key_test2',
> Field('id', 'integer'),
> Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
> primarykey = ['id', '"Oid"'])
>
> The error thrown is:  primarykey must be a 
> list of fields from table 'key_test2. That's also right, of course: "Oid" 
> is not in db.key_test2.fields
>
> Any suggestions of an approach that might work?
>
> David
>
> On Thursday, 18 August 2016 12:13:23 UTC+1, David Orme wrote:
>>
>> Hi,
>>
>> I've got an application that shares a DB with another application running 
>> a different framework. The setup is that my application declares a set of 
>> tables that the other application can read from but won't write to, so I 
>> haven't been exploring the mechanisms for connecting to legacy databases.
>>
>> However, the other application requires a UUID primary key called Oid, to 
>> use as a foreign reference in its own tables. I can easily add that as a 
>> field:
>>
>> db.define_table('project_details',
>> Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
>> Field('project_id', 'reference project_id'),
>> Field('version', 'integer'))
>>
>> What I can't figure out how to do is add that field to the primary key. 
>> For legacy tables, it seems like using this would be the approach:
>>
>> primarykey=['id','oid'],
>>
>> However, from what I can tell, once you provide primarykey, the usual 
>> mechanisms for creating the default integer id primary key get disrupted. 
>>
>> Any suggestions?
>>
>> Thanks,
>> David
>>
>>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Add a field to primary key in non-legacy tables

[David Orme]

Following up - I've tried some options to set up a table creating the 'id' 
field explicitly to work around pairing the two keys.

*A) Declare the field 'id' with type 'id' and set a two part primary key:*

db.define_table('key_test',
Field('id', 'id'),
Field('oid', length=64, default=uuid.uuid4),
primarykey = ['id', 'oid'])

This doesn't work, I think because the type 'id' automatically makes it the 
primary key.

*B) Declare an integer field 'id' and set a two part primary key:*

db.define_table('key_test',
Field('id', 'integer'),
Field('oid', length=64, default=uuid.uuid4),
primarykey = ['id', 'oid'])

The table is created without any problems, but id is not a serial with an 
id value sequence. I can't immediately find a mechanism to set a field to 
have an auto increment sequence independent of using a field type of 'id'.

*C) More complexity with capitalised names*

As if that wasn't enough, the other application is case sensitive and uses 
Oid for the field. I can fix this using rname, but then I can't get option 
B) to work because the names aren't recognized:

db.define_table('key_test2',
Field('id', 'integer'),
Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
primarykey = ['id', 'oid'])

This throws the following back from the PGSQL backend:  ('ERROR', '42703', 'column "oid" 
does not exist'). Which is right, because it doesn't - "Oid" does. 

However, this also fails

db.define_table('key_test2',
Field('id', 'integer'),
Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
primarykey = ['id', '"Oid"'])

The error thrown is:  primarykey must be a 
list of fields from table 'key_test2. That's also right, of course: "Oid" 
is not in db.key_test2.fields

Any suggestions of an approach that might work?

David

On Thursday, 18 August 2016 12:13:23 UTC+1, David Orme wrote:
>
> Hi,
>
> I've got an application that shares a DB with another application running 
> a different framework. The setup is that my application declares a set of 
> tables that the other application can read from but won't write to, so I 
> haven't been exploring the mechanisms for connecting to legacy databases.
>
> However, the other application requires a UUID primary key called Oid, to 
> use as a foreign reference in its own tables. I can easily add that as a 
> field:
>
> db.define_table('project_details',
> Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
> Field('project_id', 'reference project_id'),
> Field('version', 'integer'))
>
> What I can't figure out how to do is add that field to the primary key. 
> For legacy tables, it seems like using this would be the approach:
>
> primarykey=['id','oid'],
>
> However, from what I can tell, once you provide primarykey, the usual 
> mechanisms for creating the default integer id primary key get disrupted. 
>
> Any suggestions?
>
> Thanks,
> David
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Add a field to primary key in non-legacy tables

[David Orme]

Hi,

I've got an application that shares a DB with another application running a 
different framework. The setup is that my application declares a set of 
tables that the other application can read from but won't write to, so I 
haven't been exploring the mechanisms for connecting to legacy databases.

However, the other application requires a UUID primary key called Oid, to 
use as a foreign reference in its own tables. I can easily add that as a 
field:

db.define_table('project_details',
Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
Field('project_id', 'reference project_id'),
Field('version', 'integer'))

What I can't figure out how to do is add that field to the primary key. For 
legacy tables, it seems like using this would be the approach:

primarykey=['id','oid'],

However, from what I can tell, once you provide primarykey, the usual 
mechanisms for creating the default integer id primary key get disrupted. 

Any suggestions?

Thanks,
David

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Case sensitivity in PostgreSQL Table and Field names

[David Orme]

Hello,

I've got an application with a PostgreSQL backend that shares a small 
subset of tables with another application. At the moment, I have Web2Py 
defining all the tables and the expectation is that the other application 
just read information from the tables (and does what it likes with any 
other non Web2Py tables).

However, the other application has some fairly hard wired expectations of 
capitalised names for tables and fields. That isn't great for all sorts of 
reasons (like having to quote all the field and table names in any SQL 
queries):

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

What I'm trying to figure out is if there is a sensible workaround to 
accommodate this in the Web2Py app? Obviously, if I define the table 
through the DAL, then it converts everything to lower_case as the Postgres 
standard. That is, these two create the same table:

db.define_table("Project",
Field("Oid", length=64, default=uuid.uuid4),
Field("contents","text"))

db.define_table("project",
Field("oid", length=64, default=uuid.uuid4),
Field("contents","text"))

>From the DB backend, I can create tables that have the required 
capitalisation, but one *must have* quoted identifiers. 

-- desired table
create table "Project" (id SERIAL, 
"Oid" uuid,
contents text);
-- actually created table
create table Project (id SERIAL, 
  Oid uuid,
  contents text);

Having tried some things I think I can answer my own question, I've found 
that the rname argument does seem to allow this to work! So the following 
Web2Py code, creates the 'desired table' as desribed in the SQL code above:

db.define_table('project',
Field('oid', length=64, default=uuid.uuid4, rname='"Oid"'),
Field('contents','text'),
rname='"Project"')

I'm going to post this anyway in case anyone else runs into the issue - and 
in case there is a serious problem with this solution!

Cheers,
David


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: simple_hash and salt order

[David Orme]

Hi Anthony,

Many thanks. Just to check - option 1 would have to be done in the web2py 
codebase (i.e. outside of the application code)? I don't think there's any 
simple way of overriding the version of simple_hash imported from 
gluon.utils that LazyCrypt() calls.

Both the other options can be done in a model file, I think. 

Cheers,
David

On Friday, 27 May 2016 05:51:40 UTC+1, Anthony wrote:
>
> Aside from forking the framework, I suppose you could take one of these 
> approaches:
>
>1. Monkey patch gluon.utils.simple_hash.
>2. Subclass validators.CRYPT and validators.LazyCrypt, and in 
>LazyCrypt, replace the __str__ method with one that calls a custom 
>simple_hash function.
>3. Create an entirely new custom hashing validator that replicates the 
>algorithm used by the other app.
>
> Anthony
>
> On Thursday, May 26, 2016 at 1:19:35 PM UTC-4, David Orme wrote:
>>
>> Hello,
>>
>> I've got an application where I'm sharing a database with a second (non 
>> web2py) framework. I want my web2py application to handle user registration 
>> and would like to avoid users having two passwords (partly so that only 
>> web2py ever writes to the auth_user table).
>>
>> Inevitably, the hashed password storage formats differ, but I can match 
>> the hash algorithm between the two frameworks:
>>
>> db.auth_user.password.requires = CRYPT(digest_alg='sha512')
>>
>> Then I can just calculate the value of a second hashed password field in 
>> the foreign format - it involves recoding the string as base64, not hex, 
>> but that can be achieved using a computed field.
>>
>> def alt_password(r):
>> passwd = r.password.split('$')
>> alt = base64.b64encode(passwd[1].decode('hex')) + \
>> '*' + base64.b64encode(passwd[2].decode('hex'))
>> return alt
>>
>>
>> auth.settings.extra_fields['auth_user']= [
>> Field('alt_password', compute=lambda r: alt_password(r))
>> ]
>>
>>
>>
>> *Except...* the simple_hash function in web2py uses (password + salt) as 
>> an input and the second framework uses (salt + password), which means there 
>> is no way to reproduce the second format from the stored hashed password. I 
>> can hack the web2py utils.py file on my installation to reverse this but I 
>> wanted to check if there was a more elegant way of overloading the 
>> simple_hash function without having to change the codebase, which makes my 
>> application unstable to upgrade.
>>
>> I did wonder about extending the settings to include a salt order, but I 
>> think that would mean you'd have to extend the password string to record 
>> the order: alg$order$salt$hash. That seems like a bit of a big change for a 
>> fairly fringe use case!
>>
>>
>>
>>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] simple_hash and salt order

[David Orme]

Hello,

I've got an application where I'm sharing a database with a second (non 
web2py) framework. I want my web2py application to handle user registration 
and would like to avoid users having two passwords (partly so that only 
web2py ever writes to the auth_user table).

Inevitably, the hashed password storage formats differ, but I can match the 
hash algorithm between the two frameworks:

db.auth_user.password.requires = CRYPT(digest_alg='sha512')

Then I can just calculate the value of a second hashed password field in 
the foreign format - it involves recoding the string as base64, not hex, 
but that can be achieved using a computed field.

def alt_password(r):
passwd = r.password.split('$')
alt = base64.b64encode(passwd[1].decode('hex')) + \
'*' + base64.b64encode(passwd[2].decode('hex'))
return alt


auth.settings.extra_fields['auth_user']= [
Field('alt_password', compute=lambda r: alt_password(r))
]



*Except...* the simple_hash function in web2py uses (password + salt) as an 
input and the second framework uses (salt + password), which means there is 
no way to reproduce the second format from the stored hashed password. I 
can hack the web2py utils.py file on my installation to reverse this but I 
wanted to check if there was a more elegant way of overloading the 
simple_hash function without having to change the codebase, which makes my 
application unstable to upgrade.

I did wonder about extending the settings to include a salt order, but I 
think that would mean you'd have to extend the password string to record 
the order: alg$order$salt$hash. That seems like a bit of a big change for a 
fairly fringe use case!



-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Upload locations in web2py_ckeditor plugin

[David Orme]

Hi,

I've installed the web2py_ckeditor plugin from:
https://github.com/timrichardson/web2py_ckeditor4/releases

I think this will working really nicely for providing a table of simple 
blog posts, but I'd like to control where uploaded files get stored. I had 
a look at the source and it seemed like ckeditor.settings.uploadfs might be 
the answer, but I can't get it to work. Everything just gets stores in the 
root of app/uploads.

In an ideal world, I'd like to able to modify this in different controllers 
so that uploads for 'blog' posts go to one folder and uploads for 'news' 
posts go to another.

Cheers,
David


*db tables*

from plugin_ckeditor import CKEditor
ckeditor = CKEditor(db)
ckeditor.define_tables()

db.define_table('blog_posts',
Field('authors', 'string'),
Field('title', 'string'),
Field('content', 'text', widget=ckeditor.widget))



*controller*

def new_blog_post():

# set where the controller is going to save uploads
ckeditor.settings.uploadfs = 'uploads/blog'

form = SQLFORM(db.blog_posts)

if form.process().accepted:
response.flash = CENTER(B('Blog post submitted.'), _style='color: 
green')
else:
response.flash = CENTER(B('Problems.'), _style='color: red')

return dict(form=form)

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Getting the next ID value to create linked rows (or multiple updates on a single view)

[David Orme]

Hi,

I'm trying to create a view that allows a user to create a new project and 
add (existing) users to that project. In order to represent the two tables 
on a single page, I've been following a recipe from pyslices:

http://www.web2pyslices.com/article/show/1542/manage-users-and-memebership-in-the-same-form

So I have a project table and a project_members table and the db and 
controller functions are below.

The structure is subtly different from the slice: that is editing an 
existing member, so the auth_user id is known and is used in the forms to 
select relevant records. In my case, I'm trying to create a new project, so 
neither controller 'knows' the next project table id, and my view shows the 
SQLFORM to create a new project but then a SQLFORM.grid of *all* members of 
*all* projects.

If it was just the project SQLFORM, it wouldn't be a problem, because 
project would get the ID automatically on create, but I want to be able to 
pass the project row ID to the  new_project_membership() controller, so 
that I can get the SQLFORM.grid to show only member of this project (as 
they are added) and insert it for newly added entries to project_members. 
So my plan was to get the row ID from the db somehow and pass it on to the 
new_project_membership() controller.

Of course, the deeper issue is that I really want an atomic commit - the 
project row and project_membership row(s) only go in when a single final 
submit is pressed, so the answer may be "don't do it like that"! In which 
case, pointers to the right approach would be really welcome!

Thanks,
David


*Table definitions:*

db.define_table('project',
Field('title','string', notnull=True),
Field('start_date','date', notnull=True),
Field('end_date','date', notnull=True))

db.define_table('project_members',
Field('project_id', 'reference project', notnull=True),
Field('user_id', 'reference auth_user', notnull=True),
Field('project_role', notnull=True))

*Controllers:*

@auth.requires_login()
def new_project():

# set up the project form
form = SQLFORM(db.project, fields = ['title', 'start_date', 'end_date'
]).process()

# set up a membership panel
membership_panel = LOAD(request.controller,
'new_project_membership.html',
 #args=[project_id],
 ajax=True)

# pass both of those to the view
return dict(form=form, membership_panel=membership_panel)


@auth.requires_login()
def new_project_membership():

# function to return a grid containing a membership panel
form = SQLFORM.grid(db.project_members,
   #args=[project_id],
   searchable=False,
   deletable=False,
   details=False,
   selectable=False,
   csv=False) 
return form

*View:*

{{extend 'layout.html'}}
{{=H2('New project and members')}}
 Please use the form below to create a new project and add members and 
roles/
{{=H4('Project details')}}

{{=form}}

{{=H4('Project members')}}

{{=membership_panel}}





-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Using writable=False fields in an onupdate function

[David Orme]

That is exactly what I was looking for. I don't know how it compares for 
performance with the record id query, but it makes for very clean code, and 
the fact that it exposes the reference table fields is very neat.

Many thanks!

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Using writable=False fields in an onupdate function

[David Orme]

Hello all,

Thanks for the information and help. 

- I can see that using SQLFORM simplifies it, but the SQLFORM.grid API 
makes it really easy to present a user with a summary table of options that 
they can then click into to see details.
- The other options suggested both carry out the query I was trying to 
avoid (getting the volunteer_id from the help_offered table), so it makes 
more sense to me to put that directly into the onvalidate function. 

I guess I had the intuition that the volunteer_id value was lurking 
somewhere in the form API (it is shown on screen!) so that I could just 
retrieve it rather than submitting a query to the DAL, but that may not be 
the overhead I think it is!

So, this now works as an onvalidate function:

def update_administer_volunteers(form):

# Email the decision to the proposer
vol_id = db.help_offered(form.vars.id).volunteer_id
row = db(db.auth_user.id == vol_id).select().first()
volunteer_email = row.email
volunteer_fn = row.first_name

# alternatives
if form.vars.admin_status == 'Approved':
mail.send(to=volunteer_email,
  subject='Decision',
  message='Approved'
elif form.vars.admin_status == 'Rejected':
mail.send(to=volunteer_email,
  subject='Decision',
  message='Rejected')
else:
pass

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Using writable=False fields in an onupdate function

[David Orme]

That's the problem - the auth_user row _can't_ be retrieved because 
form.vars.volunteer_id is None.

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Using writable=False fields in an onupdate function

[David Orme]

Hello,

I'm trying to use a variable from a form in an onupdate function, but 
because I've set it to writeable=False in the controller, it doesn't get 
passed in form.vars. In more depth:

I've got a table for volunteers to post offers of help on a project. Those 
have to be approved by an admin before becoming visible, but I don't want 
the admin to be able to change the original data. So, I have this as my 
table:

db.define_table('help_offered',
Field('volunteer_id', 'reference auth_user'),
Field('volunteer_type', requires=IS_IN_SET(volunteer_type), notnull=True
),
Field('available_from','date', notnull=True),
Field('admin_status','string', requires=IS_IN_SET(['Pending', 'Approved'
,'Rejected']),
Field('approval_notes','text'),

For users, I just have a SQLFORM, which just doesn't show the two admin 
fields (admin_status and approval_notes):

def volunteer():

form = SQLFORM(db.help_offered,
   fields =['volunteer_type', 'research_statement', 
'available_from'])

Now for admin users, I want a table of pending offer, where they can view 
records but only edit those two admin fields. So, SQLFORM.grid fits 
perfectly:

def administer_volunteers():

# lock down which fields can be changed
db.help_offered.volunteer_id.writable = False
db.help_offered.volunteer_type.writable = False
db.help_offered.available_from.writable = False

# get a query of pending requests with user_id
form = SQLFORM.grid(query=(db.help_offered.admin_status == 'Pending'), 
csv=False,
fields=[db.help_offered.volunteer_id,
db.help_offered.volunteer_type,
db.help_offered.available_from],
 deletable=False, editable=True, create=False, 
details=False,
 onupdate = update_administer_volunteers)

However, now I want to email the volunteers the decision, so onupdate does 
this:

def update_administer_volunteers(form):

# Email the decision to the proposer
row = db(db.auth_user.id == form.vars.volunteer_id).select().first()
volunteer_email = row.email
volunteer_fn = row.first_name

# alternatives
if form.vars.admin_status == 'Approved':
mail.send(to=volunteer_email,
  subject='Decision',
  message='Approved'
elif form.vars.admin_status == 'Rejected':
mail.send(to=volunteer_email,
  subject='Decision',
  message='Rejected')
else:
pass

But, form.vars only contains the writable fields and the row id:

admin_status : Approved
approval_decision_date : 2016-03-10
approval_notes : Sounds good
approver_id : 1L
id : 3L

I've seen solutions using hidden but I don't want to hide the field, just 
make it read only, and I can't work out how to insert volunteer_id back 
into my form. I could look up id in help_offered and then get the 
volunteer_id that way but that seems like an unnecessary extra step!

A long post for a simple question!


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Re: Fields from multiple tables in a single form

[David Orme]

Thanks for the help. I'm still working on this - I will come back with some 
code to show what worked for me - but just wanted to correct another typo 
in my code.

The correct syntax for referencing other tables is not:

Field('project_id', 'references projects')

but:

Field('project_id', 'reference projects')

That extra 's' causes all sorts of confusion. I got error messages next to 
my SQLFORM.grid saying "Query Not Supported: 'references'" and it took a 
while to spot what was going wrong!





-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Fields from multiple tables in a single form

[David Orme]

I have a table like this:

db.define_table('visit',
Field('project_id', 'integer', 'references projects'),
Field('arrival_date','date'),
Field('departure_date','date'),
Field('number_of_visitors','integer'),
Field('purpose','text'),
Field('proposer_id', 'integer', 'references auth_user'),
Field('approval_status', 'string', 
  requires=IS_IN_SET(['Yes','No','Pending']),
  default='Pending')

I'm using SQLFORM.grid to see a list of Pending visits and allow admin 
users to click through to the SQLFORM for a given visit to set the approval 
status. This all works fine, except that I'd like the SQLFORM for a visit 
to substitute in the project name and proposer name from the two referenced 
tables, rather than just giving the ID numbers.

I've tried playing around with passing in a joined query and using the 
'left=' argument, but as far as I can tell you can only get the fields from 
a single table (although you can switch _which_ table using 'field_id=').

My controller currently looks like this - I'm using editargs to control 
which field appear in the SQLFORM and I have tried including fields from 
joined tables in here, but with no success.

@auth.requires_membership('admin')
def visit_admin_old():

# don't want the admin to change any of this about a visit
db.visit.project_id.writable = False
db.visit.proposer_id.writable = False
db.visit.arrival_date.writable = False
db.visit.departure_date.writable = False
db.visit.number_of_visitors.writable = False
db.visit.purpose.writable = False

# get a query of pending requests with user_id
form = SQLFORM.grid(query=(db.visit.visit_status == 'Pending'), csv=
False,
fields=[db.visit.arrival_date, 
db.visit.departure_date,
db.visit.number_of_visitors, 
db.visit.purpose],
 maxtextlength=250,
 deletable=False,
 editable=True,
 create=False,
 details=False,
 editargs = {'fields': ['project_id','proposer_id',
'arrival_date',
'departure_date',
'number_of_visitors',
'purpose', 
'visit_status',
'visit_decision_notes'],
 'showid': False})

return dict(form=form)

Thanks in advance,
David


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[web2py] Use menu selection to change a variable

[David Orme]

Hello,

I'd like to use a simple menu to switch between two possible languages for 
content. I've got the content separated out following the markmin 
get_content() model so want to use a global variable `lang`  to direct the 
path of the content to be loaded in pages. I've got a couple of URLs 
defined showing flags for the two languages and an if statement that 
switches which is 'active'.

{{en_URL = IMG(_src=URL('static','images/gb.svg'), _height='12px')}}
{{my_URL = IMG(_src=URL('static','images/my.svg'), _height='12px')}}
{{if lang == 'en': 
active_lang_html = en_URL 
inactive_lang_html = my_URL
  elif lang == 'my':
inactive_lang_html = en_URL
active_lang_html = my_URL
}}
{{pass}}


These are then shown to the user by a small menu defined in layout.html:

{{=MENU([(active_lang_html, True, None, [
   (inactive_lang_html, True, None, [])
])],
 _class='nav navbar-nav',li_class='dropdown',
 ul_class='dropdown-menu')}}

So the active language is shown as the menu header, with the inactive 
language shown as a dropdown submenu item. What I'd like to be able to do 
is to have choosing the submenu change the value of `lang` so that this 
code can then swap the top menu items and allow the content to be toggled. 

I can see that each menu item could have a controller that changes the 
variable `lang`, but I don't then want to load a new view, just alter the 
view of the current page with the new value of the global variable.

Any suggestions?

Cheers,
David


-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.