Re: context and sqlite - here is the code

[Hans Hagen <j.hagen@xs4all.nl>]

[-- Attachment #1: Type: text/plain, Size: 2692 bytes --]

On 10/7/2018 9:35 AM, Jörg Hofmann wrote:
> Hallo Hans,
> hallo Luigi,
> 
> Am Samstag, den 06.10.2018, 20:01 +0200 schrieb Jörg Hofmann:
>> I've been trying to connect ConTeXt to sqlite for almost a week
>> but I have repeatedly failed. Neither the
>> "internal" variant according to the documentation, nor the attempt on
>> luasql
>> have worked. Above all, the Google request showed me that
>> not alone with this problem - how reassuring. ;-)
>>
>> At the moment, concentrate I am accessing via swiglib and think my
>> problem lies here (from my log file):
>>
>> sql> start loading method 'sqlite'
>> swiglib> unknown: 'swiglib.sqlite.core'
>> swiglib> unknown: 'swiglib.helpers.core'
>>
>> Obviously, the appropriate modules are not found, but where
>> Can I get them and where do they belong? And maybe
>> also someone a working code example for me.
>> I'm working with Texlive 2017 on XUBUNTU 16.04.
>
> Many thanks for your help. I have read both manuals but may not
> understand it correctly. That's why I send my file and the log file.
the latest versions of context use ffi for mysql and sqlite interfacing 
so you just need the libraries

if you add \enabletrackers[*ffi*] to your file you get something

ffilib          > requiring library 'sqlite3' with version 'any'
ffilib          > tds path 1: .
ffilib          > tds path 2: 
c:/data/develop/tex-context/tex/texmf-win64/bin/lib/context/lua//
ffilib          > tds path 3: 
c:/data/develop/tex-context/tex/texmf-win64/bin/lib/luatex/lua//
ffilib          > tds path 4: 
c:/data/develop/tex-context/tex/texmf-win64/bin/lib//lua//
ffilib          > attemp 1, engine 'luatex'
ffilib          > checking tds lib paths strictly
ffilib          > checking with version: 'sqlite3.dll'
ffilib          > found: 
'c:/data/develop/tex-context/tex/texmf-win64/bin/lib/luatex/lua/copies/sqlite/sqlite3.dll'
ffilib          > stored library: 'sqlite3'
sql             > loading method 'sqlite' done

anyway, best keep your libe in the tex tree because otherwise you get 
some random one that is found on your system

attached a better example file (hard to test without a database here so 
need to make one ... next time proviode a real mwe)

in util-sql-imp-sqlite.lua remove a pragma line so we get:

local f_preamble = formatters[ [[
ATTACH `%s` AS `%s` ;
PRAGMA `%s`.synchronous = normal ;
]] ]



-----------------------------------------------------------------
                                           Hans Hagen | PRAGMA ADE
               Ridderstraat 27 | 8061 GH Hasselt | The Netherlands
        tel: 038 477 53 69 | www.pragma-ade.nl | www.pragma-pod.nl
-----------------------------------------------------------------

[-- Attachment #2: lua-test.tex --]
[-- Type: text/plain, Size: 2763 bytes --]

\starttext

\enabletrackers[*ffi*]

\startluacode
require("util-sql")

local sql = utilities.sql

sql.setmethod("sqlite")

document.sqlpresets = {
    database = "aufgaben",
    id       = "aufgaben",
}

local template = [[
    CREATE TABLE IF NOT EXISTS `aufgaben` (
        `id`          INTEGER PRIMARY KEY AUTOINCREMENT,
        `titel`       BLOB NOT NULL,
        `sachverhalt` BLOB NOT NULL,
        `auftraege`   BLOB NOT NULL
    ) ;
]]

function document.createdatabase()

    return utilities.sql.execute {
        presets   = document.sqlpresets,
        template  = template,
    }

end

local template = [[
    INSERT INTO `aufgaben` (
        `titel`,
        `sachverhalt`,
        `auftraege`
    ) VALUES (
        '%titel%',
        '%sachverhalt%',
        '%auftraege%'
    ) ;
]]

function document.addtodatabase(t)

    return utilities.sql.execute {
        presets   = document.sqlpresets,
        template  = template,
        variables = {
            titel       = t.titel,
            sachverhalt = t.sachverhalt,
            auftraege   = t.auftraege,
        },
    }

end

local template_id = [[
    SELECT
        *
    FROM
        `aufgaben`
    WHERE
        `id` = '%id%' ;
]]

document.sqlconverter = sql.makeconverter {
    { name = "id",          type = "number" },
    { name = "titel",       type = "string" },
    { name = "sachverhalt", type = "string" },
    { name = "auftraege",   type = "string" },
}

function document.getfromdatabase(t)

    if t.id then

        return utilities.sql.execute {
            presets   = document.sqlpresets,
            template  = template_id,
            variables = { id = t.id },
         -- converter = document.sqlconverter,
        }

    end

end

\stopluacode

\startluacode

document.createdatabase()

local tufte = io.loaddata(resolvers.findfile("tufte.tex"))
local ward  = io.loaddata(resolvers.findfile("ward.tex"))
local knuth = io.loaddata(resolvers.findfile("knuth.tex"))

document.addtodatabase { titel = "aufgabe 1", sachverhalt = "one",   auftraege = tufte }
document.addtodatabase { titel = "aufgabe 2", sachverhalt = "two",   auftraege = ward }
document.addtodatabase { titel = "aufgabe 3", sachverhalt = "three", auftraege = knuth }

function document.showrecord(n)

    local result = document.getfromdatabase { id = n }

    for i=1,#result do
        local r = result[i]
        if r.titel then
            context.title(r.titel)
            context.subject(r.sachverhalt)
            context.par()
            context("text: ")
            context.viafile(r.auftraege)
            context.page()
        end
    end

end
\stopluacode

\startluacode
    document.showrecord(1)
    document.showrecord(2)
    document.showrecord(3)
\stopluacode

\stoptext


[-- Attachment #3: Type: text/plain, Size: 492 bytes --]

___________________________________________________________________________________
If your question is of interest to others as well, please add an entry to the Wiki!

maillist : ntg-context@ntg.nl / http://www.ntg.nl/mailman/listinfo/ntg-context
webpage  : http://www.pragma-ade.nl / http://context.aanhet.net
archive  : https://bitbucket.org/phg/context-mirror/commits/
wiki     : http://contextgarden.net
___________________________________________________________________________________