experimental: decimal and pickable and custom columns

[mdipierro]

I do not know if this is a good idea and I'd like to hear your
opinions:
I have added in trunk to define custom column types

Here is an example of usage:

import cPickle
from gluon.sql import SQLCustomType
from decimal import Decimal

decimal = SQLCustomType(native='NUMERIC(10,2)',decoder=(lambda x:
Decimal(str(x))))

pickable = SQLCustomType(type='text',encoder=(lambda x:
"'%s'"%cPickle.dumps(x).replace("'","''")),decoder=(lambda x:
cPickle.loads(x)))

db.define_table('test',
SQLField('my_decimal',type=decimal),
SQLField('my_pickle',type=pickable))

the SQLCustomType constructor takes the following arguments:
- type indicates how web2py sqlform should treat this field
- native indicates how the database should treat this field
- encoder indicates how to represent (and escape) a value in SQL
- decoder indicates how to process the value once it is extracted from
the database

It seems to work well with migrations. Of course using native=....
makes the custom table not portable across databases.

The implementation is not very clean but can be improved.

Is this a good idea?

Massimo

[Alexey Nezhdanov]

I think that's handy. Dropping cross-db compartibility is bad indeed,
but you just need to have a big warning in the place where user have
to make that choice.

Also - I don't think that you can safely replace ' with " on pickled
object. Most likely you are ruining it. Either proper sql escaping
should be introduced or better yet - base64 encoding. If I am not
mistaken - pickled objects are binary.

[mdipierro]

you are right. it was just an example

[BearXu]

Can I save a formula in it?

2009/5/25 mdipierro <mdip...@cs.depaul.edu>

[mdipierro]

formula?

On May 25, 1:16 pm, BearXu <bearx...@gmail.com> wrote:
> Can I save a formula in it?
>

> 2009/5/25 mdipierro <mdipie...@cs.depaul.edu>

[BearXu]

can we design a custom column that can save a function in it?

Thus the customer can create or modify their own function to manipulate other data in the database.

So such kind of field saves the code instead of the value.

2009/5/25 mdipierro <mdip...@cs.depaul.edu>

[Iceberg]

This is not identical to your request, but if you can accept "app
developer (you) create your own function, user submit data", you can
try the accepts(...,onvalidation=callback)

By the way, what is your circumstance which need user to create a
function to manipulate data in db? Sounds vulnerable.

On May26, 7:53am, BearXu <bearx...@gmail.com> wrote:
> can we design a custom column that can save a function in it?
> Thus the customer can create or modify their own function
> to manipulate other data in the database.
>
> So such kind of field saves the code instead of the value.
>

> 2009/5/25 mdipierro <mdipie...@cs.depaul.edu>

[Francois (Jersey)]

The link to the Sqlalchemy page should be useful:
http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html

[dlypka]

In "By the way, what is your circumstance which need user to create a

function to manipulate data in db? Sounds vulnerable. "

and in

"Thus the customer can create or modify their own function
to manipulate other data in the database. "

I believe "customer" / "user" really refers to 'developer", not "end
user", not "customer"

i.e I believe BerXu meant to say
"Thus the developer can create or modify their own function
to manipulate other data in the database. '

I hope my guess is correct...

[BearXu]

yes

Maybe the customer is a system-administrator.

2009/5/26 dlypka <dly...@gmail.com>

[BearXu]

The reason to save some rules into the database is because sometimes these rules are changing with time. So maybe every year we have a new version of rules. We may add some new rules in the future.

What is a better solution?

2009/5/26 BearXu <bear...@gmail.com>

[Iceberg]

This is something about your design, not about the web2py. You may,
for example, define your "rules" table, and let your app's user (ok,
"maybe he is a system administrator") to define new rules. But after
all, these rules are logically stored as data inside web2py's db.
Then, you may define specific actions to use those rules.

PS: I am doing same thing in my app, an order management system.
Perhaps we can exchange some experience in private mail.

On May 27, 12:57 am, BearXu <bearx...@gmail.com> wrote:
> The reason to save some rules into the database is because sometimes these
> rules are changing with time. So maybe every year we have a new version of
> rules. We may add some new rules in the future.
> What is a better solution?
>

> 2009/5/26 BearXu <bearx...@gmail.com>
>
> > yesMaybe the customer is a system-administrator.

[HansD]

one way to get portability is to express it in the supported types:
eg: decimals can be *100 stored in integer

On 25 mei, 07:17, mdipierro <mdipie...@cs.depaul.edu> wrote:
> I do not know if this is a good idea and I'd like to hear your
> opinions:
> I have added in trunk to define custom column types
>
> Here is an example of usage:
>
> import cPickle
> from gluon.sql import SQLCustomType
> fromdecimalimportDecimal
>

> decimal= SQLCustomType(native='NUMERIC(10,2)',decoder=(lambda x:Decimal(str(x))))

[Yarko Tymciurak]

On Mon, Jun 1, 2009 at 12:10 PM, HansD <hans....@pobox.com> wrote:

one way to get portability is to express it in the supported types:
eg: decimals can be *100 stored in integer

see discussions around fixed point arithmetic, and binary vs. decimal encoding, and then the various decimal encoding schemes (starting w/ bcd):

http://en.wikipedia.org/wiki/Fixed-point_arithmetic
http://en.wikipedia.org/wiki/Binary-coded_decimal

 

[HansD]

not yet sure how to fix this, but fields of this type results in:

class="<gluon.sql.SQLCustomType instance at 0x0990DC10>"

On 25 mei, 07:17, mdipierro <mdipie...@cs.depaul.edu> wrote:

> I do not know if this is a good idea and I'd like to hear your
> opinions:
> I have added in trunk to define custom column types
>
> Here is an example of usage:
>
> import cPickle
> from gluon.sql importSQLCustomType
> from decimal import Decimal
>

> decimal =SQLCustomType(native='NUMERIC(10,2)',decoder=(lambda x:
> Decimal(str(x))))
>
> pickable =SQLCustomType(type='text',encoder=(lambda x:

> "'%s'"%cPickle.dumps(x).replace("'","''")),decoder=(lambda x:
> cPickle.loads(x)))
>
> db.define_table('test',
>    SQLField('my_decimal',type=decimal),
>    SQLField('my_pickle',type=pickable))
>

> theSQLCustomTypeconstructor takes the following arguments:

[mdipierro]

I know what to do. I will fix it.

[Hans Donner]

appreciate that, still learning python so I'm looking forward what I
can learn from your fix.

[mdipierro]

I think this is now fixed but I have not tried. Can you please try it?

On Jun 5, 12:49 pm, Hans Donner <hans.don...@pobox.com> wrote:
> appreciate that, still learning python so I'm looking forward what I
> can learn from your fix.
>

[Hans Donner]

consider it fixed.