[sqlalchemy] Database-side data mangling

2011-07-27 Thread Sergey V. 
Good day,

I'm trying to figure out how to do something similar to the Symmetric
Encryption recipe (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/
SymmetricEncryption), only on the database side, not in Python.

I have a suspicion that @compiles decorator may provide a solution,
but having trouble understaning how to apply it to my case.

For simplicity, let's imagine a field which stores data in upper case
but always returns it in lower case... so it needs to generate SQL
similar to

INSERT INTO tablename VALUES (..., upper(...), ...)

on insert and

SELECT ..., lower(fieldname) as fieldname, ... FROM tablename

on select.

I'm using orm and I imagine the final result would look like

class MyModel(Base):
...
myfield = AlwaysLowercaseColumn(sqlalchemy.String)

or

class MyModel(Base):
...
myfield = sqlalchemy.Column(AlwaysLowercaseString)

Thanks,
Sergey

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Database-side data mangling

2011-07-27 Thread Michael Bayer 

On Jul 27, 2011, at 8:56 PM, Sergey V. wrote:

 Good day,
 
 I'm trying to figure out how to do something similar to the Symmetric
 Encryption recipe (http://www.sqlalchemy.org/trac/wiki/UsageRecipes/
 SymmetricEncryption), only on the database side, not in Python.
 
 I have a suspicion that @compiles decorator may provide a solution,
 but having trouble understaning how to apply it to my case.
 
 For simplicity, let's imagine a field which stores data in upper case
 but always returns it in lower case... so it needs to generate SQL
 similar to
 
 INSERT INTO tablename VALUES (..., upper(...), ...)

this can be assigned, (1) i.e.

myobject.fieldname = func.upper(somename)

which you can apply with a @validates decorator  (2)


 
 on insert and
 
 SELECT ..., lower(fieldname) as fieldname, ... FROM tablename

for this you'd use column_property().  (3)

for the SQL functions themselves we're using func (4)

1: 
http://www.sqlalchemy.org/docs/orm/session.html#embedding-sql-insert-update-expressions-into-a-flush
2: http://www.sqlalchemy.org/docs/orm/mapper_config.html#simple-validators
3: 
http://www.sqlalchemy.org/docs/orm/mapper_config.html#sql-expressions-as-mapped-attributes
4: 
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.func

For a slightly old example of some of this kind of thing (probably more 
complicated than you'd need here), see the PostGIS example under 
examples/postgis/.



-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.