7

I am not able to both

  1. Change the color of comments marked by --, AND
  2. DO NOT replace hyphens (-) by the minus (−) sign.

A MRE follows.

\documentclass{report}

\usepackage{xcolor}
\usepackage{listings}
\lstset{language=SQL, columns=fullflexible, showstringspaces=false,
  basicstyle=\small\sffamily, keywordstyle=\color{blue},%
  % alsodigit={-},
  alsoletter={-},
  % literate={-}{-}1, % Do not replace hyphen by minus symbol
  morecomment=[l][commentstyle]{--},%
  morecomment=[s][commentstyle]{/*}{*/},%
  commentstyle=\color{red},
  morekeywords={REFERENCES,OVER,PARTITION,LEAD}}

\begin{document}

A SQL query.

\begin{lstlisting}
CREATE TABLE Vehicle (
  VehicleId INTEGER PRIMARY KEY,
  MakeId INTEGER NOT NULL,
  Model CHAR(30) NOT NULL,
  CreationDate DATE NOT NULL,
  ...
  FOREIGN KEY (CustomerId) REFERENCES Makes(makeId),
  .. )
\end{lstlisting}

Another SQL query with comments. I cannot make to both
(1) Change the color of comments marked by \textsf{-{-}}, and
(2) DO NOT replace hyphens (\textsf{-}) by the minus ($-$) sign.

\begin{lstlisting}
SELECT getTime(tint '[1@2001-01-01, 2@2001-01-11, 1@2001-01-15)');
-- {[2001-01-01, 2001-01-15)}
SELECT getTime(tint '{[1@2001-01-01, 2@2001-01-11],[1@2001-01-15]}');
/* {[2001-01-01 00:00:00, 2001-01-11 00:00:00],
   [2001-01-15 00:00:00, 2001-01-15 00:00:00]} */
\end{lstlisting}

Another query with an underline `{\sffamily \_}' character.

\begin{lstlisting}
SELECT ST_AsText(ST_Intersection(geometry 'Linestring(1 1,3 3)',
  geometry 'Linestring(1 3,3 1)'));
-- POINT(2 2)
\end{lstlisting}

\subsection*{Towards a Solution}

The last remaining problem after the suggestions here 
(in particular, using the cm-super fonts) is that when copy/pasting 
from the resulting PDF, the asterics are replaced by a square box.

\begin{lstlisting}[frame=single]
SELECT getTime(tint '{[1@2001-01-01, 2@2001-01-11],[1@2001-01-15]}');
/* {[2001-01-01 00:00:00, 2001-01-11 00:00:00],
   [2001-01-15 00:00:00, 2001-01-15 00:00:00]} */
\end{lstlisting}

\end{document}

Rationale

With the space restrictions of today's publications, in particular 2-column ACM or IEEE conference proceedings, we simply cannot afford to use \ttfamily for showing SQL queries, only the starting SELECT consumes 1/4 of the column width. For this reason we would need to print SQL queries in a more compact font. Using \sffamily brings many problems that are not in the \ttfamily, in particular the replacement of hyphens with minus signs.

In the following figures the problem can be seen. The first figure is showing when commenting out literate={-}{-}1

Red minus signs

The second figure shows when the literate command above is activated.

Black hyphens

As another problem, when using the \sffamily and I copy/paste the text from the resulting PDF file, the underline characters are missing, which is problematic since students must be able to reproduce the SQL queries in their terminal.

This is the result of a copy/paste

SELECT ST AsText(ST Intersection(geometry ’Linestring(1 1,3 3)’, geometry ’Linestring(1 3,3 1)’));

In summary, we would need to write SQL queries with exactly the same behavior as in the \ttfamily but in a more compact font.

Towards an Solution

The last remaining problem after the suggestions here (in particular, using the cm-super fonts) is that when copy/pasting from the resulting PDF, the asterics are replaced by a square box.

Asterics replaced by square box

Any idea to solve this ?

1
  • 1
    You should probably provide some images of what your current out put is. as it is not always obvious to see what you are refering to
    – daleif
    Commented Feb 13 at 15:26

4 Answers 4

4
+100

The issue with the hyphen is solved by applying the answer by Ulrike Fischer from https://tex.stackexchange.com/a/424193. This is to add

\makeatletter
\lst@CCPutMacro
    \lst@ProcessOther {"2D}{\lst@ttfamily{-{}}{-}}
    \@empty\z@\@empty
\makeatother

Also alsoletter={-}, is in comment.

The issue with the underscore is solved by applying the answer by egreg from https://tex.stackexchange.com/a/42817. This is to add \usepackage[T1]{fontenc}.

enter image description here

\documentclass{report}

\usepackage[T1]{fontenc}
\usepackage{xcolor}
\usepackage{listings}
\lstset{language=SQL, columns=fullflexible, showstringspaces=false,
  basicstyle=\small\sffamily, keywordstyle=\color{blue},%
  % alsodigit={-},
%  alsoletter={-},
  % literate={-}{-}1, % Do not replace hyphen by minus symbol
  morecomment=[l][commentstyle]{--},%
  morecomment=[s][commentstyle]{/*}{*/},%
  commentstyle=\color{red},
  morekeywords={REFERENCES,OVER,PARTITION,LEAD}}
\makeatletter
\lst@CCPutMacro
    \lst@ProcessOther {"2D}{\lst@ttfamily{-{}}{-}}
    \@empty\z@\@empty
\makeatother

\begin{document}

A SQL query.

\begin{lstlisting}
CREATE TABLE Vehicle (
  VehicleId INTEGER PRIMARY KEY,
  MakeId INTEGER NOT NULL,
  Model CHAR(30) NOT NULL,
  CreationDate DATE NOT NULL,
  ...
  FOREIGN KEY (CustomerId) REFERENCES Makes(makeId),
  .. )
\end{lstlisting}

Another SQL query with comments. I cannot make to both
(1) Change the color of comments marked by \textsf{-{-}}, and
(2) DO NOT replace hyphens (\textsf{-}) by the minus ($-$) sign.

\begin{lstlisting}
SELECT getTime(tint '[1@2001-01-01, 2@2001-01-11, 1@2001-01-15)');
-- {[2001-01-01, 2001-01-15)}
SELECT getTime(tint '{[1@2001-01-01, 2@2001-01-11],[1@2001-01-15]}');
/* {[2001-01-01 00:00:00, 2001-01-11 00:00:00],
   [2001-01-15 00:00:00, 2001-01-15 00:00:00]} */
\end{lstlisting}

Another query with an underline `{\sffamily \_}' character.

\begin{lstlisting}
SELECT ST_AsText(ST_Intersection(geometry 'Linestring(1 1,3 3)',
  geometry 'Linestring(1 3,3 1)'));
-- POINT(2 2)
\end{lstlisting}

\end{document}
9
  • I knew the T1 trick but alas this produces raster fonts which some publishers DO NOT accept, they want vector fonts .... Commented Feb 14 at 13:39
  • @EstebanZimanyi Concerning the font: see the following questions: tex.stackexchange.com/questions/56956/…, tex.stackexchange.com/questions/1291/… and tex.stackexchange.com/questions/548919/…. Commented Feb 14 at 17:39
  • Alas, when using \usepackage{lmodern} and copy/pasting from the PDF one query I obtain the following, which cannot be used by the students to reproduce the queries SE L E C T ST A sT e x t ( ST I n t e r se c t i o n ( g e o m e t r y ’ L i n e st r i n g ( 1 1 , 3 3 ) ’ , g e o m e t r y ’ L i n e st r i n g ( 1 3 , 3 1 ) ’ ) ) ; Any idea how to solve this ? Commented Feb 15 at 15:42
  • @EstebanZimanyi I cannot reproduce the issue you describe. Both with and without \usepackage{lmodern} I can copy and paste the underscore correctly. Commented Feb 15 at 19:44
  • I am using MikTex on Windows. Would that be the problem ? Commented Feb 16 at 14:37
2

Ad 2

To me your request ad 2 sounds like an artefact:

  • the way a hyphen is rendered, depends on ...
  • the font
  • so you probably want \ttfamily, courier or the like

See e.g. this question wrt listings.

Below I demonstrated this using

basicstyle=\small\ttfamily,% <<< rendering of - depends on font

but perhaps (!) read the manual about basicstyle again.

Ad 1

Ad 1, the coloration seems already to be in place ??

literate

For substitutions I also showed you some silly replacement of character sequences, should you need them:

  literate={get}{GOt**}1,%     <<< just for demonstration

However, you'll probably have a hard time ahead, should you insist on a certain rendering of hyphens in a specific font, which took a different route for a consistent font-layout.

Remarks

As daleif stated, I also would appreciate if you'd review and, where needed, put some more clarification and illustration into your question.

Result and code

result

\documentclass{report}

\usepackage{xcolor}
\usepackage{listings}
\lstset{language=SQL, columns=fullflexible, showstringspaces=false,
 % basicstyle=\small\sffamily,
  basicstyle=\small\ttfamily,% <<< rendering of - depends on font
  literate={get}{GOt**}1,%     <<< just for demonstration
  keywordstyle=\color{blue},%
  % alsodigit={-},
  alsoletter={-},
  % literate={-}{-}1, % Do not replace hyphen by minus symbol
  morecomment=[l][commentstyle]{--},%
  morecomment=[s][commentstyle]{/*}{*/},%
  commentstyle=\color{red},
  morekeywords={REFERENCES,OVER,PARTITION,LEAD}}

\begin{document}

A SQL query.

\begin{lstlisting}
CREATE TABLE Vehicle (
  VehicleId INTEGER PRIMARY KEY,
  MakeId INTEGER NOT NULL,
  Model CHAR(30) NOT NULL,
  CreationDate DATE NOT NULL,
  ...
  FOREIGN KEY (CustomerId) REFERENCES Makes(makeId),
  .. )
\end{lstlisting}
0
1

Here's a different approach, taking into account that literate works like a replacement, which also takes regular expressions, see manual:

  • it uses your original basicstyle=\small\sffamily,
  • but replaces hyphes with a different font, like so:
  • literate={--}{\ttfamily{--}}1 {-}{\ttfamily{-}}1, % <<<<<

Using regular expressions you could even more fine-tune font replacement, e.g. only for the ones in formatted dates etc.

result

\documentclass{report}

\usepackage{xcolor}
\usepackage{listings}
\lstset{language=SQL, columns=fullflexible, showstringspaces=false,
  basicstyle=\small\sffamily,
  literate={--}{\ttfamily{--}}1 {-}{\ttfamily{-}}1, % <<<<<
  keywordstyle=\color{blue},%
  % alsodigit={-},
  alsoletter={-},
  % literate={-}{-}1, % Do not replace hyphen by minus symbol
  morecomment=[l][commentstyle]{--},%
  morecomment=[s][commentstyle]{/*}{*/},%
  commentstyle=\color{red},
  morekeywords={REFERENCES,OVER,PARTITION,LEAD}}

\begin{document}

A SQL query.

\begin{lstlisting}
CREATE TABLE Vehicle (
  VehicleId INTEGER PRIMARY KEY,
  MakeId INTEGER NOT NULL,
  Model CHAR(30) NOT NULL,
  CreationDate DATE NOT NULL,
  ...
  FOREIGN KEY (CustomerId) REFERENCES Makes(makeId),
  .. )
\end{lstlisting}

Another SQL query with comments. I cannot make to both
(1) Change the color of comments marked by \textsf{-{-}}, and
(2) DO NOT replace hyphens (\textsf{-}) by the minus ($-$) sign.

\begin{lstlisting}
SELECT getTime(tint '[1@2001-01-01, 2@2001-01-11, 1@2001-01-15)');
-- {[2001-01-01, 2001-01-15)}
SELECT getTime(tint '{[1@2001-01-01, 2@2001-01-11],[1@2001-01-15]}');
/* {[2001-01-01 00:00:00, 2001-01-11 00:00:00],
   [2001-01-15 00:00:00, 2001-01-15 00:00:00]} */
\end{lstlisting}

\end{document}
2
  • What about a full solution that solves ALL the problems :-) ? Commented Feb 14 at 10:28
  • As it often happens, a package can put constraints on what you still can do or no langer can. E.g. adding \textcolor{}{} within literate won't work. So if you want to stay with this package, my other solution may be a better approach tex.stackexchange.com/a/709535/245790 . // Else rewrite from scratch, which may not be too complicated, if you use scripting inside (e.g. latex3) or outside (e.g. PERL) of LaTeX to generate the required LaTeX code. At least this gives you the required flexibility listings might not have. // Finally, you could request the author.
    – MS-SPO
    Commented Feb 14 at 10:45
0

For information. Maybe you should take a look at piton. With that package, here is what you can do (your problems are directly solved). However, piton works only with LuaLaTeX.

\documentclass{report}
\usepackage{xcolor}
\usepackage{piton}
\usepackage{fontspec}

\PitonOptions{language=SQL}

\SetPitonStyle[SQL]
  {
    Keyword = \color{blue} ,
    Comment = \color{red} ,
    Name.Table = \color{black} ,
    Name.Field = \color{black} ,
    Name.Builtin = \color{black} ,
    String.Long = \color{black} ,
    Number = \color{black}
  }


\begin{document}

\setmonofont{lmsans10-regular.otf}

\begin{Piton}
SELECT ST_AsText(ST_Intersection(geometry 'Linestring(1 1,3 3)',
  geometry 'Linestring(1 3,3 1)'));
-- POINT(2 2)
\end{Piton}

\begin{Piton}
SELECT getTime(tint '{[1@2001-01-01, 2@2001-01-11],[1@2001-01-15]}');
/* {[2001-01-01 00:00:00, 2001-01-11 00:00:00],
   [2001-01-15 00:00:00, 2001-01-15 00:00:00]} */
\end{Piton}

\end{document}

Output of the above code

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .