Confirmed users
675
edits
Update:Remora Localization: Difference between revisions
→example
m (→example) |
|||
| Line 94: | Line 94: | ||
* more complicated SQL - enough so to affect preformance? It depends on how many translations are needed in one query. I'll start to get worried at six in a single query. | * more complicated SQL - enough so to affect preformance? It depends on how many translations are needed in one query. I'll start to get worried at six in a single query. | ||
====example==== | ====example - fetching values ==== | ||
translation table | translation table | ||
| Line 107: | Line 107: | ||
+----+--------+------------------+ | +----+--------+------------------+ | ||
A table | |||
+----+----------+----------+ | +----+----------+----------+ | ||
| Line 128: | Line 128: | ||
Now there are a couple of problems with this simplified technique. First, if you have a query that requires ten localized strings, then you need to add ten joins to the query. Nesting joins that deep make the SQL nearly unreadable. Second, if a localized string is missing, there is no simple (non-hacky) way to fetch a default value without resorting to another query. | Now there are a couple of problems with this simplified technique. First, if you have a query that requires ten localized strings, then you need to add ten joins to the query. Nesting joins that deep make the SQL nearly unreadable. Second, if a localized string is missing, there is no simple (non-hacky) way to fetch a default value without resorting to another query. | ||
====example - inserting new values==== | |||
Let's say we need to add a new row to the A table above. We must insert the localized strings into the translations table before inserting the new row in the A table. And, since we cannot rely on the database to automatically generate a new primary key, we'll need to get a new key from a sequence first. | |||
To get a guaranteed unique new key: | |||
UPDATE translations_seq SET id=LAST_INSERT_ID(id+1) | |||
SELECT LAST_INSERT_ID() | |||
With that new key, we can insert a new translation: | |||
insert into translations (id, locale, localized_String) values (newId, 'en-US', 'howdy'); | |||
Now assuming we's done those steps twice (once for each new localized string required by a new row in A table). | |||
insert into a (greeting, danger) values (newKey, secondNewKey) | |||
To add a second language option for this new row, we need to both newKey and secondNewKey. Either these have been stored programmatically or we've refetched them by querying the A table. | |||
insert into translations (id, locale, localized_String) values (newId, 'de', 'Gruss Gott'); | |||
insert into translations (id, locale, localized_String) values (secondNewKey, 'de', 'der Himmel fällt'); | |||
====example - deleting rows==== | |||
Unfortunately, the cascading deletes using referential integrity within the database do not help us much in deleting translations if their parent row is deleted. This is because cascading deletes only work to delete rows that refer to a deleted primary key. If we were to delete a row from the A table, the corresponding rows in the translations table would not disappear because they do not refer to A's primary keys. This behavior could be repaired at the expense of complicating the new value insertion technique). | |||
For now, we must manually make sure that we delete the translations after we've deleted the target row in the A table. However, we must save the keys to the translations from the target row before we delete it. Once the target row is deleted, we can iterate throught the saved list of keys and delete the translations. | |||
Here's an example paraphrased from the python migration script: | |||
listOfTranslationsToDelete = newDB.executeSql(""" | |||
select name from addons where id = ''targetAddonIDForDeletion'' | |||
union | |||
select homepage from addons where id = ''targetAddonIDForDeletion'' | |||
union | |||
select description from addons where id = ''targetAddonIDForDeletion'' | |||
union | |||
select summary from addons where id = ''targetAddonIDForDeletion'' | |||
union | |||
select developercomments from addons where id = ''targetAddonIDForDeletion'' | |||
union | |||
select eula from addons where id = ''targetAddonIDForDeletion'' | |||
union | |||
select privacypolicy from addons where id = ''targetAddonIDForDeletion''""") | |||
newDB.executeSql("delete from addons where id = ''targetAddonIDForDeletion''") | |||
newDB.executeManySql("delete from translations where id = %s", listOfTranslationsToDelete) | |||
== Updating locales == | == Updating locales == | ||