tt4t.net
tips & tricks
for translators
SQL for Déjà Vu 3  
Contents  

SQL

 

SQL Resources

 

Row status

 

Examples of SQL commands:

 

Deleting all entries in a MDB with project ID = "0"

 

Deleting entries in a TDB with a specific date

 

Selecting/deleting records whose source is all capitals

 

Adding a caret symbol (^) at the end of each translated line

 

Updating Subject and Client in a MDB

 

Finding rows containing no letters

 

Painting rows using SQL

 

Output text contains unwanted "hard returns"

 

Setting a client

SQL

SQL Resources


Top - Home/Disclaimer

Row Status - for use in SQL commands

Null White
0 White
-1 Finished, grey
1 Exact match, green
2 Fuzzy, magenta
3 Wrong codes, red
4 Locked, yellow
5 Pending, cyan
6 Autopropagated, light green
7 Exact but different codes or numerals, dark magenta
8 Assembled, dark blue
9 Multiexact, dark green

 


Top - Home/Disclaimer

 

Examples of SQL commands

TRY YOUR COMMAND FIRST!

Important: It is not possible to undo a command, so, you should always select your entries first, check  results, and then execute the command.  After deleting or modifying records, repair your database. Examples below were posted in the list — I have not tried them all. Use them at your own risk.


Top - Home/Disclaimer

Deleting all entries in a MDB with project ID = "0"

DELETE FROM LanguagePairs WHERE PrjID = 0


Top - Home/Disclaimer

 

Deleting entries in a TDB with a specific date

DELETE FROM TerminologyPairs WHERE DATEVALUE(DateStamp)=#mm/dd/yyyy#


Top - Home/Disclaimer

 

Deleting records whose source is all capitals

Emilio Benito

A DV user asked me how to go about selecting (and possibly deleting) records from an MDB or TDB whose source is all capitals.

SQL Select, MDB:

instr(1,SourceText,Ucase(SourceText),0)>0 and SourceText like '*[a-z]*'

SQL Select, TDB:

instr(1,SourceTerm,Ucase(SourceTerm),0)>0 and SourceTerm like '*[AZ]*'

SQL Execute, MDB:

delete from LanguagePairs where instr(1,SourceText,Ucase(SourceText),0)>0 and SourceText like '*[AZ]*'

SQL Execute, TDB:

delete from TerminologyPairs where instr(1,SourceTerm,Ucase(SourceTerm),0)>0 and SourceTerm like '*[AZ]*'

Note 1. Never run a command (SQL Execute) without selecting the corresponding subset to verify that the records you had in mind are those that will be affected.

Note 2. If you delete records from the database, or otherwise modify records with SQL commands, don't forget to repair the database afterwards. This is to let Déjà Vu reindex the text.

For those proficient with SQL: a simple comparison such as

SourceText = Ucase(SourceText)

is not enough, as this condition will always return TRUE.

The key is using instr with 0 as the last parameter, which forces a binary comparison.

The "and SourceText like '*[AZ]*'" part ensures that rows contain at least one letter.


Top - Home/Disclaimer

 

Adding a caret symbol (^) at the end of each translated line

UPDATE PAIRS SET Target_de = Source & "^"


Top - Home/Disclaimer

 

Updating Subject and Client in a MDB

UPDATE LanguagePairs SET Client='xxx', Subject='xxx' WHERE PrjID='0000000'


Top - Home/Disclaimer

 

Finding rows containing no letters

NOT (Source like '*[AZ]*')


Top - Home/Disclaimer

 

Painting rows using SQL

E.g.:

UPDATE Pairs SET status_xx = 2 WHERE status_xx = 0

xx = your language

This command will turn fuzzy (2, magenta) into white (0). For color codes, see Row Status.


Top - Home/Disclaimer

 

Output text contains unwanted "hard returns"

Search for hard returns in Déjà Vu project file using this SQL Select command:

Target_xx like '*' & chr(13) & '*'

(replace xx with your target language code).

 

Search for hard returns in translation memories:

MDB: TargetText like '*' & chr(13) & '*'

TDB: TargetTerm like '*' & chr(13) & '*'

 

See: http://groups.yahoo.com/group/dejavu-l/message/28897

 


Top - Home/Disclaimer

Setting a client

Riccardo

UPDATE LanguagePairs SET Client='xxx' WHERE

DATEVALUE(DateStamp)=#mm/DD/yyyy# AND TIMEVALUE (DateStamp)>=#hh:mm:ss# AND

TIMEVALUE(DateStamp)<#hh:mm:Ss#

 

Use LanguagePairs in the MDB; TerminologyPairs in the TDB; Pairs in your project.

xxx = client number

 

Use this command as an example for other updates, e.g., subject.

 


Top - Home/Disclaimer