Hi there, I was waiting quite a long time for a proper solution of the language dependent fields ... - an I am looking forward to the announced solution for OXID 4.5!! :-)
I also think that a single table would be enough for keeping the lang stuff. This table should have: - OXID -> primary key - OXOBJECTID -> ID of according dataset, e. g. OXID of an article - OXFIELDNAME -> name of the table field, e. g. "OXTITLE" - OXLANG -> language id - OXVALUE -> content of field in fitting language By this you should be able to have as much languages as you wish and you could store content from all sorts of tables in here. And it should be possible to build a compatibility fallback for oxi18n class which first checks the new table and if there is no entry there it could try the old logic. This will cost a bit of performance but should anyway be a temporary solution. But, building a new logic like mentioned above, you probably should also have a proper oxlanguages table as well. Beste Grüße aus Dortmund! Andreas Ziethen | Geschäftsführung -- anzido GmbH Kirchhörder Str. 12 44229 Dortmund Tel.: 0231 - 60 71 079 Fax.: 0231 - 60 71 081 Mobil:0176 - 8325 1488 Email: [email protected] Web: http://www.anzido.com ( http://www.anzido.com/ ) USt-ID: DE257982972 Geschäftsführung: Andreas Ziethen Amtsgericht Dortmund HRB 20883 -----Ursprüngliche Daten----- Datum: 17.06.2010 17:52:25 Von: Alexander Kludt <[email protected]> An: <[email protected]> Betreff: Re: [oxid-dev-general] Problem with Translation DB fields Vorgang: T-G2C9SD99IF-31 > I also don't see any reason why we need a single table for each language > - one table with an identifier is enough. But what about custom modules > that use the current implementation - we are relying heavily on this in > our shirtnetwork plugin and I'm wondering what we will have to do to > work with the new system. > -- > > mit freundlichen Grüßen > Alexander Kludt > __________________________ > Phone: 09283-5925453 > Skype: kingschnulli > Email: [email protected] > Website: www.aggrosoft.de > > __________________________ > Aggrosoft it intelligence GbR > Tannstrasse 12 > 95111 Rehau > GERMANY > > Sitz Rehau, Amtsgericht Hof > Steuernummer: 223/165/54508 > Ust.-Id. Nr. gemäß § 27 a Umsatzsteuergesetz: DE260722773 > > ___________________________ > Diese Nachricht ist nur für den Empfänger () bestimmt, sollten > Sie nicht der Empfänger sein löschen Sie diese Nachricht > umgehend und geben Sie uns bitte per Email ([email protected]) Bescheid > über den fälschlichen Erhalt. > > > > Chris Jolly schrieb: > > Hi Erik, > > > > Four additional comments to the feedback from Christopher Simon: > > > > * So long as this doesn't break the oxarticles__<fieldname> > > functionality, the "Proposed Solution" sounds acceptable and should protect > > most of the 3rd party modules& tools, with the exception of those that > > perform direct database access (e.g. xtc2oxid) > > > > > > * Have you counted how many fields (columns) will still be resident in > > oxarticles ? Perhaps some other fields could be moved elsewhere or > > generalised ? > > > > > > * As per the comment from Christopher Simon, do you really need a table > > per language ? > > > > > > * Since there will be fewer oxarticles columnsthan today and you will > > be moving data between tables, this makes for a more complicated and risky > > MySQL upgrade process than normal > > > > > > > > > > ________________________________ > > From: Erik Kort<[email protected]> > > To: [email protected] > > Sent: Thu, June 17, 2010 4:56:07 PM > > Subject: Re: [oxid-dev-general] Problem with Translation DB fields > > > > Hi, > > > > Yes, you are right, this is an internal limitation of innodb. > > > > We have investigated it and we can confirm this is the case. InnoDB > > internally limits the combined row size INCLUDING all VARxxx data types to > > 65535 bytes.. It's this limit we are hitting, especially if the shop is an > > utf8 shop, because utf8 needs max 3 bytes per character. BTW it's actually > > not limited by the max rowsize mysql internally has (8000 bytes) as for > > this the variable length data types are not part of this 8000 bytes. > > Variable length data types are stored somewhere else. > > > > > > Following bugs have been opened for this: > > https://bugs.oxid-esales.com/view.php?id=1915 > > https://bugs.oxid-esales.com/view.php?id=1705. > > > > > > Workarounds: > > 1. Use/convert language columns to TEXT fields instead of VARCHAR. > > Disadvantages: Selects with ORDER BY or GROUP BY will force disk based > > temporary tables. Thus reducing your shop performance by making the > > database earlier disk bound then before. > > 2. Reduce size of your existing language columns to maximum of your used > > data. Disadvantages: Newer data may not fit. > > > > > > Proposed Solution (we prefer): > > Taking out the language columns from oxarticle table into separate multiple > > tables. This is basically sharding the tables. In effect making unlimited > > number of languages possible. Shop will manage the multiple tables and > > select correct table based on language identifier. First possible version > > we can implement this is version 4.5. > > > > Any comments or other input about this ? > > > > > > Regards, Erik > > > > > > More info on mysql / innodb limitation: > > ----- > > See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html > > > > The maximum row length, except for variable-length columns (VARBINARY, > > VARCHAR, BLOB and TEXT), is slightly less than half of a database page. > > That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT > > columns must be less than 4GB, and the total row length, including BLOB and > > TEXT columns, must be less than 4GB. > > > > If a row is less than half a page long, all of it is stored locally within > > the page. If it exceeds half a page, variable-length columns are chosen for > > external off-page storage until the row fits within half a page, as > > described in Section 13.2.11.2, "File Space Management". > > > > Although InnoDB supports row sizes larger than 65535 internally, you cannot > > define a row containing VARBINARY or VARCHAR columns with a combined size > > larger than 65535: > > > > > > Regards, Erik > > > > > > > > -----Ursprüngliche Nachricht----- > > Von: [email protected] > > [mailto:[email protected]] Im Auftrag von Christopher > > Simon > > Gesendet: Montag, 14. Juni 2010 15:56 > > An: [email protected] > > Betreff: [oxid-dev-general] Problem with Translation DB fields > > > > Hi there, > > > > i'm kinda stuck on a problem with an EE here. It's a shop which has > > about 14 languages, therefore it has all translateable fields 14 times. > > in oxarticles, thats a total of 206 (sic!) columns. > > > > This blows up the row limit of 8000 bytes of innodb, if you want to add > > another row to this table and you get "got error 139 from storage > > engine". Some other developer seems to have already converted the > > variant fields to text fields, instead of varchars. if you convert this > > fields back to varchar, you get this: > > > > #1118 - Row size too large. The maximum row size for the used table > > type, not counting BLOBs, is 65535. You have to change some columns to > > TEXT or BLOBs > > > > Because of this, you aren't able to add one more column to this table, > > which makes it incredibly hard to maintain this shop and do some updates > > or install modules. i surely will find a solution for this, but it's an > > unecessary "stone" in my way to update this shop. > > > > I think the denormalized database layout should be seriously revamped at > > least for multilanguage fields, and the step has to be to a more > > normalized table layout for this purpose. > > > > Any ideas for a new language management? Maybe we can figure out some > > solution for oxid in general, together. > > _______________________________________________ > > dev-general mailing list > > [email protected] > > http://dir.gmane.org/gmane.comp.php.oxid.general > > _______________________________________________ > > dev-general mailing list > > [email protected] > > http://dir.gmane.org/gmane.comp.php.oxid.general > > > > > > > > > > _______________________________________________ > > dev-general mailing list > > [email protected] > > http://dir.gmane.org/gmane.comp.php.oxid.general > > > > ---------------------------------------------------------------------------- > > _______________________________________________ > dev-general mailing list > [email protected] > http://dir.gmane.org/gmane.comp.php.oxid.general _______________________________________________ dev-general mailing list [email protected] http://dir.gmane.org/gmane.comp.php.oxid.general
