So what for problem I have had earlier? I don't know.
I have tested today and it works.
Made I some mistake? Or something has changed in newer Web2py versions?
Great ! Great ! Proper sorting in SQLite smartgrid now works.
I do this in model (to be sure it run always, which is most important if I
decide make experiments with SQLite indexes with collating - however what I
describe here works without indexes):
import platform
if 'windows' in platform.system().lower():
locale.setlocale(locale.LC_ALL, 'Czech_Czech republic.1250') #
Microsoft's stupidities
else:
locale.setlocale(locale.LC_ALL, 'cs_CZ.utf8')
db._adapter.connection.create_collation("czech", locale.strcoll) #
for sqlite only !
and in smartgrid all next posibilities work:
orderby={'places': 'places.place collate `czech`'},
orderby={'places': place collate `czech`'},
orderby={'places': place collate czech'},
It is nice in pydal select() too. However in pydal select() we could fix
the order later at python level like so:
rows.sort(key=lambda row: (locale.strxfrm(row,place), row.cnt))
But inside the smartgrid we could not correct it, so it is really nice that
it works.
The other possibility is
- create tables via localhost:8000/application/appadmin
- drop table places (in sqlite manager)
- create table places ..... place char(255) collate `czech`
This works too.
However this in conflict with automatic migration - next migration will
break the collating.
Dne středa 20. května 2015 17:34:58 UTC+2 Mirek Zvolský napsal(a):
>
> Using SQLite I want have proper alphabetical sorting for the users
> language at the database level (from ORDER BY).
>
> My question is:
>
> 1) is there a way to get this behaviour ?
>
> 2) if not, can it be achieved with code bellow without modifying the
> Web2py framework ?
>
> 3) if not (if it will require Web2py framework change), what is the best
> way how to implement this (or..can it implement somebody from core
> developers) ?
>
>
> I have tested that collating/compare function can be set for SQLite so:
> import locale
> locale.setlocale(locale.LC_ALL, 'cs_CZ.utf8')
> db = DAL('sqlite://db.sqlite')
> db._adapter.connection.create_collation("lexical", locale.strcoll)
>
> and then the raw sql command:
> db.executesql('SELECT nick FROM auth_user ORDER BY nick COLLATE lexical;')
> return records in correct alphabetical order for given language.
>
> So, so far we need no change in the framework.
>
> In SQLFORM.Grid works
> orderby=db.auth_user.nick
> and
> orderby='auth_user.nick'
> too.
>
> But with hack like
> orderby='auth_user.nick COLLATE lexical'
> I was not successfull.
>
>
--
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.
