Let’s say I have a table that has a column for messages. It contains keys into properties files which contain the localized messages. The keys should have meaningful names, rather than key.1, key.2, etc. I also want to paginate the results as there may be hundreds of records and I don’t want to return any more data than necessary.
Is there a way to sort the results by this column and have it sort correctly for different languages?
Thanks,
Rob
Hey Rob,
Can you provide a little more information as to what sort of database this is and/or what software you are using? I probably still won’t be able to answer your question, but an actual smart person may be able to with some additional info…
Well, we use Oracle, but I was looking for a general solution to the problem, i.e. can you make a localizable message field sortable?
The only way I can think of doing it is with some sort of stored procedure.
Thanks,
Rob
If I understand the question correctly, you want to sort the localized messages alphabetically, where “alphabetically” is properly defined for each language in question, yes?
In that case, you’ll need to define proper collations for each table’s language; Oracle calls this “NLS” for National Language Support.
I’ll echo **unstrung **with some detail.
It sounds like your database table key acts as an index into multilple property files. The property file record has the key, and then a message. I’ll leave it up to you how you map the database key into the property files.
Why do you want to have “meaningful” names for the keys? What does “meaningful” mean?
What do you mean by “results”? What query are you using to return these results? What data is in the results?
Not sure why you are concerned about pagination, what are you thinking about there? Do you mean repeating headers after a page break, something like that?
Not sure what you mean by not wanting to return any more data than necessary, that is normally a given. In your case, what is necessary?
Not sure what you mean by “sort correctly for different languages.” Do you mean that the messages are in a bunch of different languages, possibly with different character sets, and you want the sort to work for all possible characters? That should be the default, assuming your database can handle the character sets in the first place (as opposed to making it binary data or something).
Do you want to sort the key (seems trivial) or the message itself extracted from the properties files?
It would help immensely if you mention what database product you are using, showed us your table structure, and described a properties file.
By meaningful, I mean something like status.pending, status.complete, rather than status1, status2. IOW, key names that let you know at a glance what they are for.
SELECT * FROM MY_TABLE ORDER BY message_column
And return rows 26 to 50. (I realize how that is done varies from DB to DB, but ORM tools abstract it out).
By pagination, I mean returning only a certain subset of rows from the query. See above.
See above.
Are you saying that the database should automatically sort based on locale (assuming the query contains an “order by” clause)? If that is the case, it makes my question moot.
The message.
The properties file looks something like this:
status.pending=Pending
status.complete=Complete
status.pending=<however you say “pending” in Swiss German>
status.complete=<however you say “complete” in Swiss German>
Thanks,
Rob
Split the key and language fields.
KEY LANGUAGE MESSAGE
warning 1 Your feet are stinky!
warning 2 貴方の足が臭い！
warning 3 Votre puanteur de pieds!
error 1 No worky
error 2 しらな～い
error 3 C'est dimanche
If that doesn’t work for you, you need to explain in more depth what it is you’re trying to do exactly.
I was hoping to use property files to store the localized strings rather than storing the strings in the database itself. Is it the case that databases can be set up to sort based on the user’s locale? Note that the RDBMS is central.
Thanks,
Rob
That’s perfectly possible. You just use the concatenation of the KEY and LANGUAGE as the filename or whatever.
Putting the strings in a database is usually going to be a better solution though, seeing as you’re already connected to it and have found the data row. Adding in a separate file access just slows things down, and then you still have to figure out how to access the data within the file, which is more overhead and programming to do.
If the goal is to make the files accessible, just use CSV files. You can import straight from the file to the database. The data will be the same, but the people won’t have to access to the DB.
Is this what you mean by paginating limiting the result set to only the data you want?
For example: Imagine a table with peoples names and ages. You want a result set with only people younger than 18. Is this the type of thing you want to do?
In SQL , this is done through a where clause.
Select *
from tableofNamesandAges
where age < 18
Sorting is done with an order by clause. Same table, still only want under 18, but you also want them in age order.
Select *
from tableofNamesandAges
where age < 18
order by age
In MS SQL Server a clustered index on a column will cause the data to be stored in the order of the index. Results will return as if ordered by the indexed column, even if no order by statement is used. Only one column per table can have a clustered index. I wouldn’t base my clustered index on how I want things ordered, I would use an order by statement.
It sounds like you are trying to separate result sets by location. Perhaps for a report?
Are the properties files separate files from the database? I would have that data stored in the database.
A simple model for this would have 4 tables:
1.Main data table
2.table for locations
3.table status codes
4.table linking status codes to location, with the translated “display message”. For every status code/location combination, there should be a message. I am assuming that each location has the same status codes, and you just want to display it in the location language.
The main data table would have keys indicating location and status codes. You use the combination of those two values to get the correct message to display. The schema is similar if this is database that supports reporting or an application. IT might be different if the users are distributed geographically.
You mentioned wanting meaningful keys. Performance wise, at least in SQL server, this is not the best approach. SQL server will much more efficiently handle things if the are simple integer values. That is part of why status codes and locations are in their own tables. The text values are stored there, and can be used to display in an application or a report, but SQL code used to get the data should use the key values.
To get the specific data you want for a report or application, construct the appropriate SQL statement.
I’m getting a better idea of what you’re doing. You didn’t show your table structure, but I am trying to figure out: Is the key unique across all languages, or must you have the key plus the name of the file to look up a message? Does the table have the message key plus a language indicator?
Also, I am getting the idea that “sort correctly for different languages” means you want to sort such that first all the French messages, then the English, or something like that. Your SQL query just dumps the whole table; not sure what criteria you would like to use (as mentioned above you need a WHERE clause).
I agree with this completely, unless you have some sort of automated tool or interface that is generating/maintaining the files already.
I agree with this, too. I recommend letting Oracle use a sequence to generate keys.
I do not know if you can do file I/O with Oracle stored procedures. Normally you would do that sort of thing with a high-level language using embedded SQL, like SQL*Plus, or JDBC. Stored procedures should really provide functionality as an abstraction layer on top of the database, rather than trying to build in business logic (although people do it all the time).
Sorry I’m not getting you concrete answers but this is the kind of thing that’s much easier when we’re sitting in front of a white board, could nail in 5-10 minutes.
True that!
I think you, me, and SageRat have pretty similar data models in our heads. This stuff is just hard to communicate via message board. And it makes it a little harder that we can’t look at the tables and application being used.