I was using sqlite but that should not matter.
Now my concern is:
How to create Translation object , that can refer to multiple tables?,
how can I create copy of tables (schema) with only different table
name?
otherwords: How can I implement the solution with multiple Translation
tables (table per language) ?
On 13 Lis, 10:05, [EMAIL PROTECTED] wrote:
> well i've no idea about scalability. it's just general
> (semi-theoretical) OO approaches.
> your approach with table-per-language seems faster... but how u make a
> (switchable) pointer to a table? i'm not fluent in SQL, i dont know
> if u can make the Book table point (foreignkey?) to some virtual View
> and when changing language switch the View to contain the proper
> table for the language - so query-wise it all looks same, replacing
> the pointed table with another (but who'll update the foreignkeys
> then).
>
> anyway, if u say that 10K objects with one attr per object kills it,
> i've no idea about my 100K+ objects with average 5 attrs per
> object...
>
> ciao
> svil
>
> On Wednesday 12 November 2008 23:43:08 g00fy wrote:
>
> > I already implemented your solution:
>
> > Book : table with "general data"
> > id, isbn, category_id ....
> > BookTranslation: table with "translations"
> > id, book_id, title, language
>
> > Correct me if this is not what you meant
> > ##################################
>
> > and this generally works up to 10k objects.
> > since I have 10 other multilingual objects connected with Book, it
> > was awfully slow to sort and order when the number of books reached
> > 1000. This can't be enterprise solution.
>
> > Adding language
> > ##############
> > This can be easily done by just adding another translation with new
> > language in it.
> > In this case you don't have to do nothing to make it work.
>
> > The best (fastest query) solution is to create columns with
> > suffixes like _en _cz _de _pl, create indexes on languages and
> > defer other languages columns.
> > You can also do a small trick: define property on a class based on
> > a function:
>
> > def title(self):
> > return self.getattr('title_%s' % common.get_language(),
> > 'title') [I actually don't know any python so sorry if I am
> > mistaken ]
>
> > or add column properties while mapping synonym to expected
> > language columns to use "Book.title" API:
>
> > mapper(Book, book_table, properties={
> > 'title': synonym('title_%s' % common.get_language(),
> > map_column=True)
> > })
>
> > Adding language:
> > ###############
> > Since this implementation uses columns in one table it is obvious
> > that adding new language would require altering the table.
> > This might be more complicated but really: how often do you add a
> > language?
> > Altering table might be done by triggers
> > when you add a language to a database, you simply run the altering
> > table trigger that creates another set of columns and creates
> > indexes on them.
> > Of course you would have to copy another set of language columns
> > => modify your table definition. But that might not be such a big
> > effort. This is only bad when you consider it as a bad programming
> > practice ("doubling the code").
>
> > -------------------------------------------------------------------
> >-------------------------------
>
> > I am also considering different solution that is a bit similar to
> > your proposal.
>
> > you store data in multiple tables
>
> > Book : table with "general data"
> > id, isbn, category_id ....
> > BookTranslation_en: table with "translations"
> > id, book_id, title
> > BookTranslation_de: table with "translations"
> > id, book_id, title
>
> > this will also do better than 2 simple tables since you have less
> > join conditions and therefore data to join (search for).
> > But this harder to implement in SqlAlchemy. I don't know yet how do
> > I tell SqlAlchemy that one model (BookTranslation in this case) can
> > have multiple tables, and uses one.
>
> > Adding language:
> > ###############
> > Here we would have to create another table for new language. the
> > table definition is all the same only the table name (suffix)
> > differs.
>
> > -------------------------------------------------------------------
> >-------------------------------
>
> > Still, I am not fully satisfied with this two solutions and I am
> > still
> > *******************************************************************
> >*********** awaiting other proposals.
> > *****************************
>
> > On 12 Lis, 10:05, [EMAIL PROTECTED] wrote:
> > > currency part:
> > > i dont know if unified_price has to be in same table or in
> > > separate one - or just a calculable property (non-db). maybe best
> > > is calculable in separate table, with triggers or (ala
> > > SQLAlchemyAggregator) mapperExt's to update it at every change of
> > > appropriate rate and/or orig.values.
>
> > > on the multilingual part:
> > > beware, this is general approach for very complex system with
> > > hundreds of classes/ attributes-to-translate. we've invented 2
> > > approaches: * association between the attribute-in-question and
> > > the language, carrying the translation, i.e. AssocTable( ref2obj,
> > > attr, ref2lang, translation). u'll need a property/descriptor
> > > that looks up the attr using current lang (maybe context like).
> > > pros / cons:
> > > + transparent, i.e. no changes anywhere else, easy to turn
> > > on/off; ~ orig.column stays as is, can be used as cache or
> > > something - bitemporal aspect has some worries with this, as
> > > pointers are inwards to bitemporal obj from the assoc.table - but
> > > i guess u're not bitemporal
> > > * complex pointer: a separate table is made (attribute,language,
> > > translation) - common for all types or one-per-type, and all
> > > translatable attributes are pointers/queries to a row in the
> > > table using current lang. pros/cons:
> > > +~ all can be loaded using simple join given the language
> > > - changes the model - extra columns per attribute (or the
> > > orig. attr.column becomes a ref)
> > > ~ orig.column can be used as cache if pointer is in extra
> > > column + bitemporaly this is transparent, as the pointers are
> > > outward of the obj, not inwards
>
> > > both have an issue of uniqueness of attr, which can be solved by
> > > making it typeid+attrid, except that this wont work for
> > > bitemporals. we have done some prototyping on both, but have not
> > > reached any state of completeness so far (and it's not in visible
> > > must-do horizon).
>
> > > i'll be interested if u find something else, or something that is
> > > ready and working.
>
> > > ciao
> > > svil
>
> > > On Wednesday 12 November 2008 00:43:00 g00fy wrote:
> > > > What is the best way to store content in multiple languages
> > > > (translations) ?
> > > > The same question about the prices in multiple currencies ?
>
> > > > The problem:
> > > > ############
> > > > World Wide Book Shop (collection of books from multiple
> > > > countries in multiple currencies and languages)
>
> > > > Model:
> > > > ######
> > > > Book
>
> > > > Question:
> > > > #########
> > > > Every book has it's own original title and multiple
> > > > translations. How do I store in the DB translations?
>
> > > > Question:
> > > > #########
> > > > Every book has it's own price in currency of the production
> > > > country. How do I store prices with their currencies?
>
> > > > *Note: I cannot unify prices into one currency, because
> > > > currency rate does change and therefore price. I need to store
> > > > currency in original form , and then (when searching/filtering,
> > > > or daily) unify it to one currency so I can easily order by
> > > > price.
> > > > Example:
> > > > Book1: original price = 10 USD
> > > > Book2: original price = 10 EUR
> > > > Now order from the cheapest : Book1, Book2,
> > > > Now filter where price>=11 USD : Book2
>
> > > > *Note: the number of currencies and languages is not specified
> > > > and is likely to change (the solution should be flexible)
>
> > > > ---------------------------------------------------------------
> > > >---- -------------------------------
> > > >My suggested solution about "Prices
> > > > and Currencies" is:
> > > > to have 3 columns in table:
> > > > original_price
> > > > original_currency
> > > > unified_price
>
> > > > This way, I would update unified_price daily according to
> > > > currency rate and I gain ability to order and filter by price
> > > > by bringing search criteria to unified currency.
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
