Hi,
I want to implement my own internationalization type for tables like this
one where the different languages are stores in columns rather than an
extra i.e. country_i18n table.
I know this is not so flexible when adding extra languages but our goal is
to eliminate as much joins as possible.
CREATE TABLE `country` (
`id` *int*(*11*) auto_increment NOT NULL AUTO_INCREMENT,
`iso2` char(*2*) NOT NULL,
`name_en` *varchar*(*64*),
`name_de` *varchar*(*64*),
PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into `country`(`id`,`iso2`,`name_de`,`name_en`) values (*1*,'DE',
'Germany','Deutschland');
insert into `country`(`id`,`iso2`,`name_de`,`name_en`) values (*2*,'IT',
'Italy','Italien');
The goal is to have a mapped class Country with a property “name” that is
populated depending on a current language setting i.e. in the user session.
With the help of the documentation I did it this way:
# global language setting (for this example in a global var)
GLOBAL_LANGUAGE = 'en'
# custom column clause: modifies the column name depending on
GLOBAL_LANGUAGE
class i18NColumnClause(ColumnClause):
pass
@compiles(i18NColumnClause)
def compile_i18n_column_clause(element, compiler, **kw):
return '%s_%s' % (element.name, GLOBAL_LANGUAGE)
# custom type
class i18NType(sqlalchemy.types.TypeDecorator):
impl = sqlalchemy.types.Unicode
def column_expression(self, colexpr):
return i18NColumnClause(colexpr)
# test class for custom type
class Country(Base):
__tablename__ = 'country'
id = Column(Integer, primary_key=True)
iso2 = Column(String(2))
name = Column(i18NType(64))
def __repr__(self):
return "<Country(%d/%s/%s)>" % (self.id, self.iso2, self.name)
This works for read operations because the new type uses a custom
column_expression so I can do something like this:
germany = session.query(Country).get(1)
print germany # <Country(1/DE/Germany)>
Unfortunately it does not work when saving an object:
australia = Country()
australia.iso2 = 'AU'
australia.name = 'Australia'
session.add(australia)
session.commit()
The following query is executed and fails because the column “name” is not
in table, it should be name_en:
INSERT INTO country (iso2, name) VALUES ('AU', 'Australia')
*What am I missing? Or is this a practicable approach at all? Or are there
better ways to do that?*
Can anyone help?
Cheers, Alex
(I am using Sqlalchemy 0.8.0b2, mysql 5.5.16 and python 2.7.3)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/8A0zPDjHYG0J.
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.
