Datatool query database \dtlgetrowindex argument expansion

Question

I am building a database to gather information from various places of my document. I would like to do the following: query database to look for a row with specific value for a key. If such a row is found (which should be unique by the construction of the database), do something to this row, otherwise add a new row with that key and value. Note that the value is stored in a macro and not known prior. Here is the question: what is the best way to implement this?

I will describe my "not-so-successful" attempts of doing this. What should be a routine database operation turned out to be more complicated than I have expected (or maybe I am not enlightened enough).

Minicing the exmaple on page 96 of the datatool-user manual, I have the following (non)working code. I understand the reason is that \dtlgetrowindex does not expand its argument \foo (More on that later).

\documentclass{article}
\usepackage{datatool}
\begin{document}
\DTLnewdb{mydb}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{cat}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{dog}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{pig}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{sheep}

\def \foo{dog}

\dtlgetrowindex{\myrowidx}{mydb}{1}{\foo}
\ifx\myrowidx\dtlnovalue
   Not Found
\else
   Found in row \myrowidx
\fi

\end{document}

The output will be

Not Found

As I mentioned before, the argument \foo is not expanded when passing to \dtlgetrow, thus causing match not found. I tried really hard to get around this, and the last "successful" attempt is based on Werner's answer to another datatool question

\makeatletter
\begingroup
\let \dtlgetrowindex\relax%
\protected@xdef\GetRowIndex#1{
\dtlgetrowindex{#1}{mydb}{1}{\foo}
}
\endgroup
\makeatother

\GetRowIndex{\myrowidx}
\ifx\myrowidx\dtlnovalue
   Not Found
\else
   Found in row \myrowidx
\fi

This will produce

Found in row 2

But surely this is not the intended usage?

I also tried to iterate through the database and find matches. Again a partial "success":

\def\foo{humming bird}

\newcounter{mycnt} 
\begin{DTLenvforeach}[\DTLiseq{\foo}{\animals}]{mydb}{\animals=Animals}
 %Found match!
\DTLappendtorow{Class}{Bird}
\stepcounter{mycnt}
\end{DTLenvforeach}

\ifnum \themycnt=0 
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{hummingbird}
\DTLnewdbentry{mydb}{Class}{Bird}
\fi

\DTLdisplaydb{mydb}

Imho this is a rather counter-intuitive way of doing things:(1) the whole database is checked, and (2) an extra variable has to be introduced to keep track of the iteration. Moreover, I call it a partial success because apparently, \DTLiseq strips all spaces from its arguments, as verified by the following code:

\ifthenelse{\DTLiseq{football}{ f o o t b a l l }}{Equal!}{Not equal!}

which produces (why?)

Equal!

I think it is possible that two words combine into a third one when concatenated, but they mean different things. Striping all spaces nullifies the difference.

Answer 1

\dtlgetrowindex is defined as follows:

\newcommand*{\dtlgetrowindex}[4]{%
  \toks@{#4}%
  \edef\dtl@dogetrowindex{\noexpand\@dtlgetrowindex{\noexpand#1}{#2}{\number#3}{\the\toks@}}%
  \dtl@dogetrowindex
}

The value is temporarily stored in a token register which prevents expansion. We can define a modified version which allows expansion instead:

\newcommand*{\xdtlgetrowindex}[4]{%
  \protected@edef\dtl@dogetrowindex{\noexpand\@dtlgetrowindex{\noexpand#1}{#2}{\number#3}{#4}}%
  \dtl@dogetrowindex
}

This will need to be enclosed in \makeatletter / \makeatother when defined in the document:

\documentclass{article}

\usepackage{datatool}

\makeatletter
\providecommand*{\xdtlgetrowindex}[4]{%
  \protected@edef\dtl@dogetrowindex{\noexpand\@dtlgetrowindex{\noexpand#1}{#2}{\number#3}{#4}}%
  \dtl@dogetrowindex
}
\makeatother

\begin{document}
\DTLnewdb{mydb}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{cat}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{dog}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{pig}
\DTLnewrow{mydb}
\DTLnewdbentry{mydb}{Animals}{sheep}

\def\foo{dog}
\xdtlgetrowindex{\myrowidx}{mydb}{1}{\foo}
\ifx\myrowidx\dtlnovalue
   Not Found
\else
   Found in row \myrowidx
\fi

\end{document}

This correctly produces:

Found in row 2

I've used \providecommand as I will add this to the next version of datatool (but I'm not sure when I'll have a chance to do that as I have a backlog of other stuff that needs doing first).