Cfrgtkky

I found ConTeXt document and I get file with MetaPost like this :

   % language=uk



% author  : Hans Hagen, PRAGMA ADE, NL

% license : Creative Commons, Attribution-NonCommercial-ShareAlike 3.0 Unported



usemodule[art-01,abr-02]



definecolor

  [maincolor]

  [r=.4]



setupbodyfont

  [10pt]



setuptype

  [color=maincolor]



setuptyping

  [color=maincolor]



definefont

  [TitlePageFont]

  [file:lmmonolt10-bold.otf]



setuphead

  [color=maincolor]



usesymbols

  [cc]



setupinteraction

  [hidden]



startdocument

  [metadata:author=Hans Hagen,

   metadata:title=SQL in ConTeXt,

   author=Hans Hagen,

   affiliation=PRAGMA ADE,

   location=Hasselt NL,

   title=SQL in CONTEXT,

   support=www.contextgarden.net,

   website=www.pragma-ade.nl]



startMPpage



    StartPage ;



    numeric w ; w := bbwidth(Page) ;

    numeric h ; h := bbheight(Page) ;



    fill Page withcolor MPcolor{maincolor} ;



    draw textext.urt("TitlePageFont Q")       xysized (1.1   w,0.9 h) shifted (-.05w,.05h) withcolor .20white ;

    draw textext.top("TitlePageFont SQL")     xysized (0.4725w,0.13h) shifted (.675w,.24w) withcolor .60white ;

    draw textext.top("TitlePageFont CONTEXT") xsized  (0.6   w)       shifted (.675w,.10w) withcolor .60white ;



    StopPage ;



stopMPpage



startsubject[title=Contents]



placelist[section][alternative=a]



stopsubject



startsection[title=Introduction]



Although CONTEXT is a likely candidate for typesetting content that comes from

databases it was only in 2011 that I ran into a project where a connection was

needed. After all, much document related typesetting happens on files or

dedicated storage systems.



Because we run most projects in an infrastructure suitable for TEX, it made

sense to add some helper scripts to the CONTEXT core distribution that deal

with getting data from (in our case) MYSQL databases. That way we can use the

already stable infrastructure for installing and updating files that comes with

CONTEXT.



As LUA support is nicely integrated in CONTEXT, and as dealing with

information from databases involves some kind of programming anyway, there is (at

least currently) no TEX interface. The examples shown here work in CONTEXT,

but you need to keep in mind that LUA scripts can also use this interface.



{em Although this code is under construction the interfaces are unlikely to

change, if only because we use it on production.}



stopsection



startsection[title=Presets]



In order to consult a database you need to provide credentials. You also need

to reach the database server, either by using some client program or via a

library. More about that later.



Because we don't want to key in all that information again and again, we will

collect it in a table. This also permits us to store it in a file and load it

on demand. For instance:



starttyping

local presets = {

    database = "test",

    username = "root",

    password = "none",

    host     = "localhost",

    port     = 3306,

}

stoptyping



You can put a table in a file type {presets.lua} like this:



starttyping

return {

    database = "test",

    username = "root",

    password = "none",

    host     = "localhost",

    port     = 3306,

}

stoptyping



and then load it as follows:



starttyping

local presets = table.load("presets.lua")

stoptyping



If you really want, you can use some library to open a connection, execute a

query, collect results and close the connection, but here we use just one

function that does it all. The presets are used to access the database and the

same presets will be used more often it makes sense to keep a connection open as

long as possible. That way you can execute much more queries per second,

something that makes sense when there are many small ones, as in web related

services. A connection is made persistent when the presets have an type {id}

key, like



starttyping

presets.id = "myproject"

stoptyping



stopsection



startsection[title=Templates]



A query often looks like this:



starttyping

SELECT

    `artist`, `title`

FROM

    `cd`

WHERE

    `artist` = 'archive' ;

stoptyping



However, often you want to use the same query for multiple lookups, in which case

you can do this:



starttyping

SELECT

    `artist`, `title`

FROM

    `cd`

WHERE

    `artist` = '%artist%' ;

stoptyping



In the next section we will see how type {%artist%} can be replaced by a more

meaningful value. You can a percent sign by entering two in a row: type {%%}.



As with any programming language that deals with strings natively, you need a

way to escape the characters that fence the string. In SQL a field name is

fenced by type {``} and a string by type {''}. Field names can often be

used without type {``} but you can better play safe.



starttyping

`artist` = 'Chilly Gonzales'

stoptyping



Escaping of the type {'} is simple:



starttyping

`artist` = 'Jasper van''t Hof'

stoptyping



When you use templates you often pass a string as variable and you don't want to

be bothered with escaping them. In the previous example we used:



starttyping

`artist` = '%artist%'

stoptyping



When you expect embedded quotes you can use this:



starttyping

`artist` = '%[artist]%'

stoptyping



In this case the variable {artist} will be escaped. When we reuse a template we

store it in a variable:



starttyping

local template = [[

    SELECT

        `artist`, `title`

    FROM

        `cd`

    WHERE

        `artist` = '%artist%' ;

]]

stoptyping



stopsection



startsection[title=Queries]



In order to execute a query you need to pass the previously discussed presets

as well as the query itself.



starttyping

local data, keys = utilities.sql.execute {

    presets   = presets,

    template  = template,

    variables = {

        artist = "Dream Theater",

    },

}

stoptyping



The variables in the presets table can also be passed at the outer

level. In fact there are three levels of inheritance: settings, presets

and module defaults.



starttabulate

NC presets        NC a table with values NC NR

NC template       NC a query string NC NR

NC templatefile   NC a file containing a template NC NR

NC em resultfile NC a (temporary) file to store the result NC NR

NC em queryfile  NC a (temporary) file to store a query NC NR

NC variables      NC variables that are subsituted in the template NC NR

NC username       NC used to connect to the database NC NR

NC password       NC used to connect to the database NC NR

NC host           NC the quote {machine} where the database server runs on NC NR

NC port           NC the port where the database server listens to NC NR

NC database       NC the name of the database NC NR

stoptabulate



The type {resultfile} and type {queryfile} parameters are used when a client

approach is used. When a library is used all happens in memory.



When the query succeeds two tables are returned: type {data} and type {keys}. The

first is an indexed table where each entry is a hash. So, if we have only one

match and that match has only one field, you get something like this:



starttyping

data = {

    {

        key = "value"

    }

}



keys = {

    "key"

}

stoptyping



stopsection



startsection[title=Converters]



All values in the result are strings. Of course we could have provided some

automatic type conversion but there are more basetypes in MYSQL and some are

not even standard SQL. Instead the module provides a converter mechanism



starttyping

local converter = utilities.sql.makeconverter {

    { name = "id",      type = "number" },

    { name = "name",    type = "string" },

    { name = "enabled", type = "boolean" },

}

stoptyping



You can pass the converter to the execute function:



starttyping

local data, keys = utilities.sql.execute {

    presets   = presets,

    template  = template,

    converter = converter,

    variables = {

        name = "Hans Hagen",

    },

}

stoptyping



In addition to numbers, strings and booleans you can also use a function

or table:



starttyping

local remap = {

    ["1"] = "info"

    ["2"] = "warning"

    ["3"] = "debug"

    ["4"] = "error"

}



local converter = utilities.sql.makeconverter {

    { name = "id",     type = "number" },

    { name = "status", type = remap },

}

stoptyping



I use this module for managing CONTEXT jobs in web services. In that case we

need to store jobtickets and they have some common properties. The definition of

the table looks as follows: footnote {The tickets manager is part of the

CONTEXT distribution.}



starttyping

CREATE TABLE IF NOT EXISTS %basename% (

    `id`        int(11)     NOT NULL AUTO_INCREMENT,

    `token`     varchar(50) NOT NULL,

    `subtoken`  INT(11)     NOT NULL,

    `created`   int(11)     NOT NULL,

    `accessed`  int(11)     NOT NULL,

    `category`  int(11)     NOT NULL,

    `status`    int(11)     NOT NULL,

    `usertoken` varchar(50) NOT NULL,

    `data`      longtext    NOT NULL,

    `comment`   longtext    NOT NULL,



    PRIMARY KEY                     (`id`),

    UNIQUE INDEX `id_unique_index`  (`id` ASC),

    KEY          `token_unique_key` (`token`)

)

DEFAULT CHARSET = utf8 ;

stoptyping



We can register a ticket from (for instance) a web service and use an independent

watchdog to consult the database for tickets that need to be processed. When the

job is finished we register this in the database and the web service can poll for

the status.



It's easy to imagine more fields, for instance the way CONTEXT is called, what

files to use, what results to expect, what extra data to pass, like style

directives, etc. Instead of putting that kind of information in fields we store

them in a LUA table, serialize that table, and put that in the data field.



The other way around is that we take this data field and convert it back to LUA.

For this you can use a helper:



starttyping

local results = utilities.sql.execute { ... }



for i=1,#results do

    local result = results[i]

    result.data = utilities.sql.deserialize(result.data)

end

stoptyping



Much more efficient is to use a converter:



starttyping

local converter = utilities.sql.makeconverter {

    ...

    { name = "data", type = "deserialize" },

    ...

}

stoptyping



This way you don't need to loop over the result and deserialize each data

field which not only takes less runtime (often neglectable) but also takes

less (intermediate) memory. Of course in some cases it can make sense to

postpone the deserialization.



A variant is not to store a serialized data table, but to store a key|-|value

list, like:



starttyping

data = [[key_1="value_1" key_2="value_2"]]

stoptyping



Such data fields can be converted with:



starttyping

local converter = utilities.sql.makeconverter {

    ...

    { name = "data", type = utilities.parsers.keq_to_hash },

    ...

}

stoptyping



You can imagine more converters like this, and if needed you can use them to

preprocess data as well.



starttabulate[|Tl|p|]

NC "boolean"     NC This converts a string into the value type {true} or type {false}.

                      Valid values for type {true} are: type {1}, type {true}, type

                      {yes}, type {on} and type {t} NC NR

NC "number"      NC This one does a straightforward type {tonumber} on the value. NC NR

NC function      NC The given function is applied to value. NC NR

NC table         NC The value is resolved via the given table. NC NR

NC "deserialize" NC The value is deserialized into LUA code. NC NR

NC "key"         NC The value is used as key which makes the result table is now hashed

                      instead of indexed. NC NR

NC "entry"       NC An entry is added with the given name and optionally with a default

                      value. NC NR

stoptabulate



stopsection



startsection[title=Typesetting]



For good reason a CONTEXT job often involves multiple passes. Although the

database related code is quite efficient it can be considered a waste of time

and bandwidth to fetch the data several times. For this reason there is

another function:



starttyping

local data, keys = utilities.sql.prepare {

    tag = "table-1",

    ...

}



-- do something useful with the result



local data, keys = utilities.sql.prepare {

    tag = "table-2",

    ...

}



-- do something useful with the result

stoptyping



The type {prepare} alternative stores the result in a file and reuses

it in successive runs.



stopsection



startsection[title=Methods]



Currently we have several methods for accessing a database:



starttabulate

NC client  NC use the command line tool, pass arguments and use files NC NR

NC library NC use the standard library (somewhat tricky in LUATEX as we need to work around bugs) NC NR

NC lmxsql  NC use the library with a LUA based pseudo client (stay in the LUA domain) NC NR

NC swiglib NC use the (still experimental) library that comes with LUATEX NC NR

stoptabulate



All methods use the same interface (type {execute}) and hide the dirty details

for the user. All return the data and keys tables and all take care of the proper

escaping and parsing.



stopsection



startsection[title=Helpers]



There are some helper functions and extra modules that will be described when

they are stable.



There is an quote {extra} option to the type {context} command that can be used

to produce an overview of a database. You can get more information about this

with the command:



starttyping

context --extra=sql-tables --help

stoptyping



stopsection



startsection[title=Colofon]



starttabulate[|B|p|]

NC author    NC getvariable{document}{author}, getvariable{document}{affiliation}, getvariable{document}{location} NC NR

NC version   NC currentdate NC NR

NC website   NC getvariable{document}{website} endash getvariable{document}{support} NC NR

NC copyright NC symbol[cc][cc-by-sa-nc] NC NR

stoptabulate



stopsection



stopdocument

If you compile it and you will get like this :

Can I make it with LaTeX ? Only for cover page, Let's have MWE like this :

documentclass{article}

% this is preamble, if need 

begin{titlepage}

beginfig (1);

%this is code from ConTeXt

endfig;

end.

end{titlepage}

begin{document}

Context to LaTeX

end{document}

You can copy-paste this more or less. You're lucky because the luamplib supports the MetaFun format.

documentclass[a4paper,10pt]{article}



usepackage{eso-pic}



usepackage{luamplib}

mplibsetformat{metafun}



usepackage{xcolor}

definecolor{maincolor}{rgb}{.4,0,0}



usepackage{fontspec}

newfontfamilyTitlePageFont{lmmonolt10-bold.otf}



begin{document}



AddToShipoutPictureFG*{%

  begin{mplibcode}

    beginfig(0) ;



    path Page ; Page := unitsquare xscaled (mpdim{paperwidth}) yscaled (mpdim{paperheight}) ;



    numeric w ; w := bbwidth(Page) ;

    numeric h ; h := bbheight(Page) ;



    fill Page withcolor mpcolor{maincolor} ;



    draw textext.urt("TitlePageFont Q")       xysized (1.1   w,0.9 h) shifted (-.05w,.05h) withcolor .20white ;

    draw textext.top("TitlePageFont SQL")     xysized (0.4725w,0.13h) shifted (.675w,.24w) withcolor .60white ;

    draw textext.top("TitlePageFont CONTEXT") xsized  (0.6   w)       shifted (.675w,.10w) withcolor .60white ;



    setbounds currentpicture to Page ;



    endfig ;

  end{mplibcode}%

}



% Generate a page

leavevmode

thispagestyle{empty}



end{document}