tt4t.net
tips & tricks
for translators
SQL for DVX  
Contents  

ABOUT SQL

Working with SQL

SQL Resources

Row Status - for use with SDL commands

Deleting all populated cells

EXAMPLES / PROJECT

To change status from Fuzzy to Exact Match
(including: how to change row status without SQL)

Deleting all populated cells

EXAMPLES / TERMINOLOGY DATABASE

About the AttrTypeID field

Terminology entries for a given client

Terminology entries for a given subject

EXAMPLES / MEMORY DATABASE

Memory pairs from a single Project

Memory pairs without project ID

Memory pairs for a subject

Memory pairs for a given client

Memory pairs for a given client and subject

 

ABOUT SQL...

Important:
It is not possible to undo an SQL command. Back up your file before running your command or run a similar SQL Statement filter to check results and then execute the command.

 After deleting or modifying records, repair/compact your database.

Working with SQL

 

DVX works based on databases, so it is possible to execute SQL commands to update, modify, or filter your database (memory, terminology, or project database).

There is no UNDO, so be very careful when using this tool.

To execute an SQL command, go to Project > Execute SQL.

To filter your database click on Row Selector and select SQL Statement. It is the last option.

BTW, it does not matter if you use single or double quotes in your statements.


Top - Home/Disclaimer

SQL Resources

 

 


Top - Home/Disclaimer

Row Status - for use in SQL commands

 

Match Type

0 = Normal
1 = Exact
2 = Fuzzy
4 = Assembled
8 = Propagated
16 = Autopropagated

Row Status
256 = Finished
512 = Locked
1024 = Pending
4096 = Exact Match

Warnings
65536 = MultipleMatches
131072 = RenumberedMatch
262144 = Populated*

Errors
16777216 = WrongCodes

DVX uses the operator OR to combine different status. That is, if a sentence is Exact (1) and Locked (512), its status is 513. To calculate a combined status, use Windows Calculator in its Scientific view (there is an OR operator)

Match Type +(or) Row Status will give you the basic Row Status

* Populated - Using Polpulate Current Language. When populating only one or a group of sentences using F5 does not change cells status.


Top - Home/Disclaimer

SQL Examples - Project ________ *

To change status from Fuzzy to Exact Matches

UPDATE Pairs SET Status_#### = Status_#### – 2 + 4096 WHERE ((Status_#### \ 2) MOD 2) = 1 AND ((Status_#### \ 512) MOD 2) <> 1

Where #### is your language code.

Alternatively

How to change row status without SQL?
Duncan Bell

This is just a tip in case you need to change row status values, or
anything else global, in a project's segments, and can't do it with SQL (in my case because I haven't learnt it :).

Simply export the project to Word/RTF in an External View, including the row status info and, (essential, this!), segment IDs.

Then, find the row status values you want to change and search and replace them with the required values. You can delete them by replacing them with "nothing" in the Replace text box (where "nothing" really is nothing at all, i.e. you don't type anything).

The safest way to do this, especially in a document that might contain values that match a row status value, is to select the Status column in Word. When it is highlighted, the Word Search and Replace dialog says "Search Down", and only replaces the texts selected in that column.

Or you could replace one at a time, clicking OK when you are prompted to.

If you're not sure of the value of your existing row status, but need to know that, to change it, change the first segment of the project to that new row status, in DVX, before the export to External View. Then you will see the row status value number in the Status column cell for that segment, in Word.

To find out what value to change your existing row status value to, change the second segment of the project to that new row status, in DVX, before the export to External View. Then you will see the row status value number in the Status column cell for that segment, in Word.

At the end, save your document, and reimport it into Word in the usual way to update your project.


Top - Home/Disclaimer

 

Deleting all populated cells

This command deletes all cells populated as a group, but not cells populated individually. To be used, e.g., after exporting a partial translation.

UPDATE pairs SET Target_#### = ' ' WHERE Status_#### = 262144

Where #### is your language code.

Close the translation window and open it again to see your changes.


Top - Home/Disclaimer

SQL Examples - Memory Database

To find memory pairs from a single Project

Alan Lounds

ID IN (SELECT ID FROM Translations WHERE PrjID = 8805201)


Top - Home/Disclaimer

To find memory pairs without project ID

Alan Lounds

E.g.: aligned entries

ID IN (SELECT ID FROM Translations WHERE PrjID = 0)


Top - Home/Disclaimer

To find memory pairs for a subject

Alan Lounds

ID IN (SELECT ID FROM Translations WHERE Subject ='03')


Top - Home/Disclaimer

To find memory pairs for a given client

Alan Lounds

ID IN (SELECT ID FROM Translations WHERE Client ='03')


Top - Home/Disclaimer

To find memory pairs for a given client and subject

Alan Lounds

ID IN (SELECT ID FROM Translations WHERE Subject ='04' and Client='02')


Top - Home/Disclaimer

SQL Examples - Terminology Database

About the AttrTypeID field

DVX allows you to build your TDB using different templates. It seems that the AttrTypeID (used in the commands below) varies according to the choosen template. To see the IDs of your TDB, open your TDB (file extension .dvtdb) in MS-Access, then open the table AttrTypes. In my case, this is what I have:

ID 5 is subject; ID 6 is client; IDs 8 and 9 are customized fields; etc.


Top - Home/Disclaimer

To find terminology entries for a given client

Alan Lounds

ID IN (SELECT LemmaID FROM Attributes WHERE AttrTypeID=6 and TextValue ='02')


Top - Home/Disclaimer

To find terminology entries for a given subject

Alan Lounds

ID IN (SELECT LemmaID FROM Attributes WHERE AttrTypeID=5 and TextValue ='02')


Top - Home/Disclaimer