Forum OpenACS Development: Re: From database to JSON

My experiments so far do not look smarter than your example. I think this approach covers already most of the use cases fo SQL -> JSON. I agree with you that a complete solution may need to take into account nested JSON structures, which would increase the complexity of conversion.

There are actually quite a few ways to produce JSON in tcl, some we have mentioned, but e.g. tDOM or huddle can also do it.

I was wondering if OpenACS as a platform should provide some easier interface to produce JSON out of the box: so far we are strong in extracting data from the database and make it into markup, but modern UIs often fetch their data from JSON endpoints.

Do you think there is any value in providing some tool in OpenACS itself?

Regarding tjson library: it does not support Windows at the moment. It should not be too much work to get it to run on Windows but I don't have the setup yet - I will try and let u know. tjson works on Linux and MacOS at the moment.

Coming to the question at hand. I think it is the responsibility of the database or its driver to produce JSON or an intermediate format (because there are no types in TCL). This is what the AWS DynamoDB and the MongoDB drivers do (not 100% sure about the latter). They return an intermediate format that tjson can use in both cases. The reason this needs to happen in the database or its driver is because they are aware of the types already.

... I have to run, I will follow up later.

set sql "select user_id, username,  last_visit from users"
set json_arr [::tjson::parse {[]}]
db_foreach get_users $sql {
   ::tjson::add_item_to_array $json_arr \
        [list M \
                [list \
                        user_id [list N $user_id] \
                        username [list S $username] \
                        last_visit [list S $last_visit]]]
}
doc_return 200 application/json [::tjson::to_pretty_json $json_arr]
ad_script_abort

And here is what you get:

[
  {
    "user_id": 0,
    "username": "guest",
    "last_visit": ""
  },
  {
    "user_id": 759,
    "username": "test",
    "last_visit": "2023-07-26 09:27:37.590777-04"
  }
]

M means it is an object, N means it is a number, and S means it is a string. tjson also supports the triple notation that is used in mongodb driver (documentation for that feature is still pending).

I will add a create command though. Thanks for that.

Can you provide an example where tDOM fails to parse a valid JSON?

Please download this file: https://raw.githubusercontent.com/stripe/openapi/master/openapi/spec3.json

and then run:

package require tdom
set fp [open "spec3.json"]
set data [read $fp]
close $fp
puts [time "dom parse -json $data test" 1

I get: error "JSON syntax error" at position 15

tjson runs fine with it:

package require tjson
set fp [open "spec3.json"]
set data [read $fp]
close $fp
puts [time "::tjson::parse $data" 1]

=> 18316 microseconds for tjson

It's probably something that I am doing wrong. Please let me know if you have better luck with it.

Past EuroTcl 2024 Neophytos pointed me to this thread.

Antonio already pointed out that a valid test should read:

puts [time {dom parse -json $data test} 1]

Since the tjson code snipped had also the wrong double quotes I
wondered why tjson not also reported JSON error on that input.

There is a well-known JSON test-suite under
https://github.com/nst/JSONTestSuite/archive/refs/heads/master.zip

I adapted a small test script of mine (see below) and gave the current
tjson head code from github a try with 8.6 (it's currently not
migrated to Tcl 9 and seg faults, if I try to run it with that).

With Tcl 8.6 and 9 tDOM parses all valid JSON test data and raises
error on all invalid input data.

On the other hand tjson with 8.6 raises error on the valid JSON 

y_number_double_close_to_zero.json 

and does not raise error on the invalid data:

n_object_trailing_comment_slash_open.json
n_object_trailing_comment_slash_open_incomplete.json
n_string_invalid_unicode_escape.json
n_array_extra_close.json
n_string_unescaped_ctrl_char.json
n_structure_trailing_#.json
n_multidigit_number_then_00.json
n_structure_number_with_trailing_garbage.json
n_object_with_trailing_garbage.json
n_structure_close_unopened_array.json
n_number_-2..json
n_string_unescaped_newline.json
n_structure_whitespace_formfeed.json
n_number_0.e1.json
n_object_trailing_comment.json
n_number_2.e3.json
n_number_2.e+3.json
n_number_neg_real_without_int_part.json
n_structure_array_trailing_garbage.json
n_string_with_trailing_garbage.json
n_array_comma_after_close.json
n_number_2.e-3.json
n_number_-01.json
n_string_unescaped_tab.json
n_structure_object_followed_by_closing_object.json
n_number_with_leading_zero.json
n_structure_object_with_trailing_garbage.json
n_object_trailing_comment_open.json
n_structure_double_array.json
n_number_neg_int_starting_with_zero.json
n_number_real_without_fractional_part.json
n_structure_array_with_extra_array_close.json

Although, very probably this is not a shortcoming of the tjson code
but of the underlying interfaced C json parser.

package require tdom
load ./libtjson.so

set testdir JSONTestSuite-master/test_parsing

foreach type {i y n} {
    set r($type,nr) 0
    set r($type,readfailed) ""
    set r($type,failed) ""
    set r($type,ok) ""
    foreach file [glob $testdir/${type}_*] {
        incr r($type,nr)
        set filetail [file tail $file]
        set fd [open $file]
        if {[catch {
            set data [read $fd]
        }]} {
            close $fd
            lappend r($type,readfailed) $filetail
            continue
        }
        close $fd
        if {[catch {
            # for tdom use
            # set doc [dom parse -json -- $data]"
            set doc [tjson::parse $data]
        }]} {
            lappend r($type,failed) $filetail
        } else {
            lappend r($type,ok) $filetail
        }
    }
}

foreach {type report} {
    i {readfailed failed}
    y failed
    n ok
} {
    puts "type $type"
    puts "nr of tests $r($type,nr)"
    puts "readfailed [llength $r($type,readfailed)]"
    puts "failed [llength $r($type,failed)]"
    puts "ok [llength $r($type,ok)]"
    set details ""
    foreach toreport $report {
        set thisdetails [join $r($type,$toreport)]
        if {$thisdetails ne ""} {
            append details "$toreport: $thisdetails\n"
        }
    }
    if {$details ne ""} {
        puts "Details:"
        puts $details
    }
}

If you haven't done migration effort for Tcl 9 it is the "expected" result, that even if the build with Tcl 9 comes to an end with warnings (as it was for me) the lib very shortly raise seg fault. So, if you haven't tackled migration to Tcl 9 then it would be of course great if you do but otherwise there isn't something to do wrt the seg fault.