SQLite Forum

Timeline
Login

50 most recent forum posts by user 1codedebugger

2021-09-17
06:37 Reply: SQLite blessing anniversary (artifact: eeabbcd42c user: 1codedebugger)

If more of us requested forgiveness from each other, it'd be a lot easier to get along down here. If more of us recognized our greater need and considered Him who paid the penalty making it possible for forgiveness to be extended to us, then we as individuals and nations might be granted the grace to fulfill that blessing and much more.

05:51 Reply: Novice question about running UTF-8 on CLI from file. (artifact: fd105f57de user: 1codedebugger)

I realize this is a bit old now but I think I understand what you mean about Notepad. I switched to Linux shortly after this post and use Kate text editor. I also use an older machine that runs Windows 7 and found out a couple days ago that Kate works on Windows. I remembered this exchange and thought it might be useful. I didn't get it from the MS store but their nightly builds page. If you turn off most of the features, it works much like Notepad but with a long undo/redo chain, tabs for each open file, and you can save all the open tabs as a session to be opened together next time.

05:36 Edit: (Deleted) (artifact: fea5895260 user: 1codedebugger)
Deleted
2021-09-16
02:21 Edit reply: Can BLOB incremental I/O be employed to manage edits to a block of text? (artifact: 1777c9fc53 user: 1codedebugger)

Thank you very much. This is what I was trying to find and didn't know enough about the subject to have the right search terms. It appears that piece tables made all the difference for Word 1.0 and 1.1 when machines were not so powerful and quick. I don't want to spend a year trying to build something complicated because I'm not capable of it and building a text editor isn't the objective. But I hate to do something poorly regardless, even if no one really notices a delay. Anyway, pointing me to this as a start is very helpful. Thank you.

I wanted to add that, if I'm understanding the piece table correctly, one issue is that once it is applied to build the new document from the original and the append buffer, the undo/redo chain is useless because the data for any deletions is permanently lost, at least from the original. Thus, the undo/redo chain cannot be restored between sessions if the append buffer is applied and the piece table reset upon open or close.

I thought I had a method figured out that would hold each change event as a table row, passing only additions and no deletions data. Then those rows could be used to build a piece table only when the original is to be updated and overwritten to reflect the changes. I thought building the piece table once at that time would make updating the original more efficient than attempting to apply all the recorded changes. Then realized that the deletion data required for preserving undo/redo between sessions would be lost.

I think the method can still be used in a database set up but the deletions will need to be passed with each event also; and am still working through that. It appears that the method is/was made for an in-RAM only process, apart from the quick save, that didn't preserve undo/redo between sessions.

Thanks again for pointing me to it. It was interesting but all the documents I came across give the method and leave out how to handle the actual splitting of the piece table ranges as edits take place.

2021-09-15
00:13 Edit reply: Can BLOB incremental I/O be employed to manage edits to a block of text? (artifact: dc1ad465c9 user: 1codedebugger)

Thank you very much. This is what I was trying to find and didn't know enough about the subject to have the right search terms. It appears that piece tables made all the difference for Word 1.0 and 1.1 when machines were not so powerful and quick. I don't want to spend a year trying to build something complicated because I'm not capable of it and building a text editor isn't the objective. But I hate to do something poorly regardless, even if no one really notices a delay. Anyway, pointing me to this as a start is very helpful. Thank you.

2021-09-14
23:04 Reply: Can BLOB incremental I/O be employed to manage edits to a block of text? (artifact: eea2bc0e5f user: 1codedebugger)

Thank you very much. This is what I was trying to find and didn't know enough about the subject to have the right search terms. It appears that piece tables made all the difference for Word 1.0 and 1.1 when machines were not so powerful and quick. I don't want to spend a year trying to build something complicated because I'm not capable of it and building a text editor isn't the objective. But I hate to do something poorly regardless, even if know one really notices a delay. Anyway, pointing me to this as a start is very helpful. Thank you.

18:21 Post: Can BLOB incremental I/O be employed to manage edits to a block of text? (artifact: 4cb8a2fa68 user: 1codedebugger)

Hello, I'm not sure this would be considered a "pure" SQLite question and may have more to do with data structure than how to accomplish something in SQLite alone; but I remember someone on here, Keith I think, explaining that a BLOB and a chunk of text are very similar overall, and that got me to thinking there might be a better way than what I've been doing.

I'm trying to add a basic text editor to a library tool in which users can write their interpretations, etcetera concerning specific resource documents. It currently works along with an undo/redo chain but I'm not confident I'm handling the storing and updating steps as efficiently as they ought to be.

My question is how should the state of a text document be updated in the database? I've been tracking the undo/redo data and storing one copy of the current state of the full document. But when a minor edit is made and the equivalent of a change event takes place, the entire document is passed back to the database and used to overwrite the last version or state. That should work fine most of the time because the text blocks are most often relatively small notes but others could be similar to an introduction to a chapter or book and run several pages; but I expect those would still always be around 1 to 2 MB maximum.

Is it okay at these sizes to simply overwrite the full text block or can the text block be treated like a BLOB and something like incremental BLOB I/O employed to apply the last change of the undo/redo chain to it, rather than passing the full document and overwriting the content upon each edit? If so, is it more work for SQLite to perform the incremental I/O than to simply overwrite the full content, but the efficiency gain is in not having to pass the full content at every edit but only the information required to adjust the last saved state to the current state?

I was thinking that, if there were no limit to the number of undo/redo items stored, they are collectively the full document; adn there had to be a better approach than what I was doing.

Obviously, this isn't my area of expertise and I'm likely not using the most accurate terms. If there is a name for this concept in general of how to handle the storage of editable text documents that I could read upon, would you please let me know what it is called? I tried searching on it but without any success.

Thank you.

2021-08-31
02:27 Reply: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? (artifact: dee2a3affa user: 1codedebugger)

Thanks for the information and document link. At a high level, is it correct that the SQL function in my little example would be a method defined in a module, and which must be written in C? And there is no equivalent in the SQLite Tcl API?

I suppose one could write it in C and invoke it from Tcl as needed, and the method in my case is pretty simple. I readily admit that much of what I read is very new to me and I'd need to study and experiment for a couple weeks, likely, just to get a basic grasp of it; and I wanted first to make sure I understand where exactly this is to be done. Thanks again.

2021-08-30
03:24 Edit reply: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? (artifact: 1627aea4bd user: 1codedebugger)
Thank you. What about trying to make use of the JSON1 extension's table-valued functions?
Like this, for example. It worked pretty well, as far as I can tell (the data is written and accurate); but I'm not a programmer and have nothing to which to compare it. I'm sure that's obvious from the code and question alone.

Does the optimization note under section 3.1 of the With Clause apply at all when it is not recursive? I mean are the first two tables completely built in memory before anything is inserted, or does SQLite process one row at a time from the table storing the existing data, inserting all of the resultant rows from it into the empty table, and then moves to the next row?

set sql {
with \
 iters as ( \
   select version as t, book_no as b, chapter_no as c, verse_no as v \
   from Bible_translations \
   where t=0 ),\
 jeach as ( \
   select it.t as t, it.b as b, it.c as c, it.v as v, \
          json_extract( jt.value, '$.j' ) as j, \
          json_extract( jt.value, '$.w' ) as w, \
          json_extract( jt.value, '$.p' ) as p \
   from iters it, json_each( (select transfunc(version, book_no, chapter_no, verse_no, text_content) \
                           from Bible_translations bt \
                           where bt.version=it.t \
                             and bt.book_no=it.b \
                             and bt.chapter_no=it.c \
                             and bt.verse_no=it.v)) jt) \
insert into concordance (version, book_no, chapter_no, verse_no, index_no, word_orig, word_clean) \
select * from jeach;}

dbt function transfunc -argcount 5 -deterministic -directonly -returntype text {jsonbuild}

proc jsonbuild { t b c v w } {
 global punc
 set u [encoding convertfrom utf-8 $w]
 set json [join lmap word [split $u] {
  incr j
  set p [string map $punc $word]
  set word "\"p[incr a]\":{\"t\":$t,\"b\":$b,\"c\":$c,\"v\":$v,\"j\":$j,\"w\":\"$word\",\"p\":\"$p\"}"
 }] ,]
 return \{$json\}
}
2021-08-29
20:31 Reply: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? (artifact: ab0191a454 user: 1codedebugger)
Thank you. What about trying to make use of the JSON1 extension's table-valued functions?
Like this, for example. It worked pretty well, as far as I can tell (the data is written and accurate); but I'm not a programmer and have nothing to which to compare it. I'm sure that's obvious from the code and question alone.

Does the optimization note under section 3.1 of the With Clause apply at all when it is not recursive? I mean are the first two tables completely built in memory before anything is inserted, or does SQLite process one row at a time from the table storing the existing data, inserting all of the resultant rows from it into the empty table, and then moves to the next row?

set sql {
with \
 iters as ( \
   select version as t, book_no as b, chapter_no as c, verse_no as v \
   from Bible_translations \
   where t=0 ),\
 jeach as ( \
   select it.t as t, it.b as b, it.c as c, it.v as v, \
          json_extract( jt.value, '$.j' ) as j, \
          json_extract( jt.value, '$.w' ) as w, \
          json_extract( jt.value, '$.p' ) as p \
   from iters it, json_each( (select transfunc(version, book_no, chapter_no, verse_no, text_content) \
                           from Bible_translations bt \
                           where bt.version=it.t \
                             and bt.book_no=it.b \
                             and bt.chapter_no=it.c \
                             and bt.verse_no=it.v)) jt) \
insert into concordance (version, book_no, chapter_no, verse_no, index_no, word_orig, word_clean) \
select * \
from jeach;}

dbt function transfunc -argcount 5 -deterministic -directonly -returntype text {jsonbuild}

proc jsonbuild { t b c v w } {
 global punc
 set u [encoding convertfrom utf-8 $w]
 chan puts stdout $w
 set json {}
 set l [llength $u]
 lmap word $u {
  incr j
  set p [string map $punc $word]
  lappend json "\"p[incr a]\":{\"t\":$t,\"b\":$b,\"c\":$c,\"v\":$v,\"j\":$j,\"w\":\"$word\",\"p\":\"$p\"}[expr {($j<$l) ? "," : ""}]"
 }
 return \{[join $json ""]\}
}
03:40 Edit: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? (artifact: 4bb0eee081 user: 1codedebugger)
Hello,

I'm trying to use the SQLite Tcl API to transform rows that contain binary text of a few sentences into rows of words and write the rows to a new table, after removing any prefixed/suffixed punctuation.

The code below, of course, doesn't work; one reason at least being that the procedure returns only one value, the list, instead of three values. 

    set sql {insert into table_2 (id, index, word) select transfunc(id, textdata) from table_1}
    db function transfunc -argcount 2 -deterministic -directonly -returntype text {SplitToWords}

    proc SplitToWords {id str} {
     global punc
     set listw {}
     lmap word [encoding convertfrom utf-8 $str] {
      lappend $listw {[incr i] [string map $punc $word]}
     }
     return $listw
    }

    db eval $sql

If the procedure is changed to write each row from within it, as below, it works. But is this the correct approach?
Thank you for any guidance you may be able to provide.

    set sql {select transfunc(id, textdata) from table_1}

    proc SplitToWords {id str} {
     global punc
     lmap word [encoding convertfrom utf-8 $str] {
      incr i
      set p [string map $punc $word]
      set sql {insert into table_2 (id, index, word) values($id, $i, $p)}"
      db eval $sql
     }
    }
03:13 Post: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? (artifact: 48a20c9697 user: 1codedebugger)
Hello,

I'm trying to use the SQLite Tcl API to transform rows that contain binary text of a few sentences into rows of words and write the rows to a new table, after removing any prefixed/suffixed punctuation.

The code below, of course, doesn't work; one reason at least being that the procedure returns only one value, the list, instead of three values. 

    set sql {insert into table_2 (id, index, word) select transfunc(id, textdata) from table_1}
    db function transfunc -argcount 2 -deterministic -directonly -returntype text {SplitToWords}

    proc SplitToWords {id str} {
     global punc
     set listw {}
     lmap word [encoding convertfrom utf-8 $str] {
      lappend $listw {[incr i] [string map $punc $word]}
     }
     return $listw
    }

    db eval $sql

If the procedure is changed to write each row from within it, as below, it works. But is this the correct approach?
Thank you for any guidance you may be able to provide.

    set sql {select transfunc(id, textdata) from table_1}

    proc SplitToWords {id str} {
     global punc
     lmap word [encoding convertfrom utf-8 $str] {
      set sql "insert into table_2 (id, index, word) values( $id, [incr i], '[string map $punc $word]')"
      db eval $sql
     }
    }
2021-08-27
02:16 Reply: Request for general guidance on approach to searching text for word combinations. (artifact: 85c2ba1299 user: 1codedebugger)
Thank you. That is a very useful tool, it appears, especially for content that changes often.

I wonder how it compares to the concordance method since my content isn't going to change and each row is rather small. I've been working on that and there are issues of punctuation and words in italics used for smooth translation that aren't really in the original manuscripts.  The FTS extension might handle that automatically.  I'll have to experiment.

Thanks again.
01:54 Edit: Request for general guidance on approach to searching text for word combinations. (artifact: fb030f7c4c user: 1codedebugger)
Hello, I have a novice question concerning searching for words and word combinations within strings of text.

The table includes translation_no, book_no, chapter_no, verse_no, text. I'd like to be able to return all rows in which, for example, a variable number of specific words occur in any order in the text of a particular translation; and I'd like to return the results in order of all verses that have the words in exact order, then the words sequentially in any order, and, after that, it really doesn't matter much apart from being in book_no, chapter_no, verse_no order.

I assume it would be very inefficient to search through the text of every row in this table for each request.  The text column ranges in length from 11 to 556 characters.

Should I build a table that is the equivalent of an electronic concordance, where there is one row for each unique word that occurs across all rows of text, and a list of every combination of book_no, chapter_no, verse_no, word_no; and then examine the intersection of the three in non-SQL code (currently using Tcl)?

Or, have multiple rows for every unique word with its book_no,..., word_no values, and determine the result from within SQLite? Perhaps, a CTE that returns a table for each individual word, and then select all rows that have the same book_no, chapter_no, verse_no as a start?

Or is there a better approach? Thank you for any guidance you may be able to provide.
2021-08-26
23:26 Edit: Request for general guidance on approach to searching text for word combinations. (artifact: 314a9e6459 user: 1codedebugger)
Hello, I have a novice question concerning searching for words and word combinations within strings of text.

The table includes translation_no, book_no, chapter_no, verse_no, text. I'd like to be able to return all rows in which, for example, a variable number specific words occur in any order in the text of a particular translation; and I'd like to return the results in order of all verses that have the three words in exact order, then three words sequentially in any order, and, after that, it really doesn't matter much apart from being in book_no, chapter_no, verse_no order.

I assume it would be very inefficient to search through the text of every row in this table for each request.  The text column ranges in length from 11 to 556 characters.

Should I build a table that is the equivalent of an electronic concordance, where there is one row for each unique word that occurs across all rows of text, and a list of every combination of book_no, chapter_no, verse_no, word_no; and then examine the intersection of the three in non-SQL code (currently using Tcl)?

Or, have multiple rows for every unique word with its book_no,..., word_no values, and determine the result from within SQLite? Perhaps, a CTE that returns a table for each individual word, and then select all rows that have the same book_no, chapter_no, verse_no as a start?

Or is there a better approach? Thank you for any guidance you may be able to provide.
23:03 Post: Request for general guidance on approach to searching text for word combinations. (artifact: 36c944905c user: 1codedebugger)
Hello, I have a novice question concerning searching for words and word combinations within strings of text.

The table includes translation_no, book_no, chapter_no, verse_no, text. I'd like to be able to return all rows in which, for example, a variable number specific words occur in any order in the text of a particular translation; and I'd like to return the results in order of all verses that have the three words in exact order, then three words sequentially in any order, and, after that, it really doesn't matter much apart from being in book_no, chapter_no, verse_no order.

I assume it would be very inefficient to search through the text of every row in this table for each request.  

Should I build a table that is the equivalent of an electronic concordance, where there is one row for each unique word that occurs across all rows of text, and a list of every combination of book_no, chapter_no, verse_no, word_no; and then examine the intersection of the three in non-SQL code (currently using Tcl)?

Or, have multiple rows for every unique word with its book_no,..., word_no values, and determine the result from within SQLite? Perhaps, a CTE that returns a table for each individual word, and then select all rows that have the same book_no, chapter_no, verse_no as a start?

Or is there a better approach? Thank you for any guidance you may be able to provide.
2021-06-14
04:07 Reply: Novice question concerning adding conditional incremental integer counter (artifact: cfbeb49769 user: 1codedebugger)

Thank you. At first, I didn't understand this but see that, if rows change strongs_no values from not 'punc2' to 'punc2', they would not get set to null on a new update run without this union code. That is something I would have definitely missed, especially since have never used a window function before.

03:53 Reply: Novice question concerning adding conditional incremental integer counter (artifact: af2669f9bb user: 1codedebugger)
Thank you very much.  I came across this type of method in searching but didn't realize that row_number() was a window function and thought it was available only in sql server. I should've been smart enough to have just typed row_number in the SQLite search box.

The best I came up with was to count the number of 'punc2' rows in a subquery. It appears to work but I assume is not very efficient.

  select book_no, chapter_no, verse_no, strongs_no, index_no,
    (case when strongs_no = 'punc2' then null
          else
               index_no - (select count(*)
                           from bh_interlinear
                           where book_no=b.book_no
                           and chapter_no=b.chapter_no
                           and verse_no=b.verse_no
                           and strongs_no='punc2'
                           and index_no <= b.index_no)
     end ) as new_index
  from bh_interlinear b
  where b.book_no=1
    and b.chapter_no=1
    and b.verse_no < 4


┌─────────┬────────────┬──────────┬────────────┬──────────┬───────────┐
│ book_no │ chapter_no │ verse_no │ strongs_no │ index_no │ new_index │
├─────────┼────────────┼──────────┼────────────┼──────────┼───────────┤
│ 1       │ 1          │ 1        │ H7225      │ 1        │ 1         │
│ 1       │ 1          │ 1        │ H1254      │ 2        │ 2         │
│ 1       │ 1          │ 1        │ H430       │ 3        │ 3         │
│ 1       │ 1          │ 1        │ H853       │ 4        │ 4         │
│ 1       │ 1          │ 1        │ H8064      │ 5        │ 5         │
│ 1       │ 1          │ 1        │ H853       │ 6        │ 6         │
│ 1       │ 1          │ 1        │ H776       │ 7        │ 7         │
│ 1       │ 1          │ 1        │ punc2      │ 8        │           │
│ 1       │ 1          │ 2        │ H776       │ 1        │ 1         │
│ 1       │ 1          │ 2        │ H1961      │ 2        │ 2         │
│ 1       │ 1          │ 2        │ H8414      │ 3        │ 3         │
│ 1       │ 1          │ 2        │ punc2      │ 4        │           │
│ 1       │ 1          │ 2        │ H922       │ 5        │ 4         │
│ 1       │ 1          │ 2        │ punc2      │ 6        │           │
│ 1       │ 1          │ 2        │ H2822      │ 7        │ 5         │
│ 1       │ 1          │ 2        │ H5921      │ 8        │ 6         │
│ 1       │ 1          │ 2        │ H6440      │ 9        │ 7         │
│ 1       │ 1          │ 2        │ H8415      │ 10       │ 8         │
│ 1       │ 1          │ 2        │ punc2      │ 11       │           │
│ 1       │ 1          │ 2        │ H7307      │ 12       │ 9         │
│ 1       │ 1          │ 2        │ H430       │ 13       │ 10        │
│ 1       │ 1          │ 2        │ H7363      │ 14       │ 11        │
│ 1       │ 1          │ 2        │ H5921      │ 15       │ 12        │
│ 1       │ 1          │ 2        │ H6440      │ 16       │ 13        │
│ 1       │ 1          │ 2        │ H4325      │ 17       │ 14        │
│ 1       │ 1          │ 2        │ punc2      │ 18       │           │
│ 1       │ 1          │ 3        │ H559       │ 1        │ 1         │
│ 1       │ 1          │ 3        │ H430       │ 2        │ 2         │
│ 1       │ 1          │ 3        │ punc2      │ 3        │           │
│ 1       │ 1          │ 3        │ H1961      │ 4        │ 3         │
│ 1       │ 1          │ 3        │ H216       │ 5        │ 4         │
│ 1       │ 1          │ 3        │ punc2      │ 6        │           │
│ 1       │ 1          │ 3        │ H1961      │ 7        │ 5         │
│ 1       │ 1          │ 3        │ H216       │ 8        │ 6         │
│ 1       │ 1          │ 3        │ punc2      │ 9        │           │
└─────────┴────────────┴──────────┴────────────┴──────────┴───────────┘
02:49 Post: Novice question concerning adding conditional incremental integer counter (artifact: 49896fcb63 user: 1codedebugger)
Hello, would you please tell me if it is possible in SQLite to add a new index_no column to this data that counts each row, skipping rows with strongs_no of 'punc2'?
I can add a new column and update it in a Tcl script or C code, but don't know how to start a counter in plain SQL or have it restart at 1 at each change in book, chapter, verse. By that I mean, when book_no=1 and chapter_no=1 and verse_no=3, the new index_no needs to start at 1 again and increment until verse_no 4 is reached.

I don't want to eliminate the 'punc2' rows, but just want another index_no that starts at 1 and increments sequentially ignoring all 'punc2' rows.  The purpose is to attempt to use the combination of book, chapter, verse, index to join with two other sources in place of the desired match column which is Hebrew and which I cannot get to work properly even after unicode normalization on all source tables. 

At this point, I don't need to add a new column to the table, but would like to use a CTE to see if the sources will match properly, but I'm completely stumped as to how to get a value in a select statement that can be incremented.  I've looked at recursion in a with statement but don't see how to restart the counter when move to a new verse.

Thank you for any guidance you may be able to provide.


book_no  chapter_no  verse_no  index_no  strongs_no      desired new index
-------  ----------  --------  --------  ----------      -----------------
1        1           2         1         H776                   1
1        1           2         2         H1961                  2
1        1           2         3         H8414                  3
1        1           2         4         punc2              
1        1           2         5         H922                   4
1        1           2         6         punc2       
1        1           2         7         H2822                  5
1        1           2         8         H5921                  6
1        1           2         9         H6440                  7
1        1           2         10        H8415                  8
1        1           2         11        punc2   
1        1           2         12        H7307                  9
1        1           2         13        H430                  10
1        1           2         14        H7363                 11
1        1           2         15        H5921                 12
1        1           2         16        H6440                 13
1        1           2         17        H4325                 14
1        1           2         18        punc2    
1        1           3          1        H430                   1
2021-06-02
02:14 Reply: What am I doing wrong to get errors when trying to compile amalgamation file in Linux (artifact: 9718b4f7ac user: 1codedebugger)

Thank you for the explanation; it's very helpful to me and I appreciate it.

2021-06-01
14:49 Reply: What am I doing wrong to get errors when trying to compile amalgamation file in Linux (artifact: 0743188ba0 user: 1codedebugger)

Thank you. Does this apply to Linux only? I ask because I just compiled the same on Windows 7 from the sqlite3.o made in 3.34.0 without these flags and it worked without error. I haven't used the amalgamation in about six months, and thought maybe I had forgotten that I had used these flags before but it appears I didn't use them.

I'm pretty sure the only thing used to compile to sqlite3.o was -DSQLITE_ENABLE_JSON1.

Although I'm not attempting to compile the CLI because the Manjaro software installer did that for me, that note given but Larry Brasfield mentions the flags for Windows when using mingw+mysys. I'm using mingw-w64 but don't need those flags.

What am I still not understanding? Thanks.

06:43 Post: What am I doing wrong to get errors when trying to compile amalgamation file in Linux (artifact: aa65182302 user: 1codedebugger)
Hello, I'm attempting to compile a C file with the sqlite3 amalgamation and get the following errors.  I tried on versions 3.34.0 and 3.35.5 and am using Manjaro Linux.  I'm certain that I compiled the same on Windows 7 without any issue; and am sure the cause is that I don't know something I should about Linux.

Would you please let me know what I'm doing wrong?  Thank you.

$ gcc -O2 -c sqlite3.c -- generates the sqlite3.o file without error.

$ gcc -O2 sqlite3.o myfile.c -o myfile.exe

/usr/bin/ld: sqlite3.o: in function `pthreadMutexTry':
sqlite3.c:(.text+0x3c65): undefined reference to `pthread_mutex_trylock'
/usr/bin/ld: sqlite3.o: in function `sqlite3ThreadCreate':
sqlite3.c:(.text+0x867c): undefined reference to `pthread_create'
/usr/bin/ld: sqlite3.o: in function `vdbeSorterJoinThread':
sqlite3.c:(.text+0x8718): undefined reference to `pthread_join'
/usr/bin/ld: sqlite3.o: in function `pthreadMutexAlloc':
sqlite3.c:(.text+0x12ed6): undefined reference to `pthread_mutexattr_init'
/usr/bin/ld: sqlite3.c:(.text+0x12ee3): undefined reference to `pthread_mutexattr_settype'
/usr/bin/ld: sqlite3.c:(.text+0x12ef6): undefined reference to `pthread_mutexattr_destroy'
/usr/bin/ld: sqlite3.o: in function `unixDlError':
sqlite3.c:(.text+0x29e2f): undefined reference to `dlerror'
/usr/bin/ld: sqlite3.o: in function `sqlite3VdbeExec':
sqlite3.c:(.text+0x830ba): undefined reference to `pthread_join'
/usr/bin/ld: sqlite3.o: in function `unixDlClose':
sqlite3.c:(.text+0x4674): undefined reference to `dlclose'
/usr/bin/ld: sqlite3.o: in function `unixDlSym':
sqlite3.c:(.text+0x4687): undefined reference to `dlsym'
/usr/bin/ld: sqlite3.o: in function `unixDlOpen':
sqlite3.c:(.text+0x4699): undefined reference to `dlopen'
collect2: error: ld returned 1 exit status
2021-05-22
05:31 Post: Benefits of using SQLite functions (artifact: 8fdd194820 user: 1codedebugger)
This is a rather novice question I'm sure but could you please tell me if declaring a SQLite function results in efficiency of some sort or is mostly for convenience? Specifically, I was trying to understand if these two are really different in whether or not they bring database data into Tcl or handle it all in SQLite land, so to speak, and whether or not they write one large piece of data to disk in a single operation or perform a separate write operation for each individual update.

Thank you for considering my question.

proc normalize {string {form nfc}} {
    exec uconv -f utf-8 -t utf-8 -x "::$form;" << $string
}

dbws function normalize -returntype text -deterministic -directonly { normalize }

dbws eval { update src_original set uni_norm = normalize(original) }

versus:

dbws eval { select id, original from src_original } rowstep {
 set n [ normalize $rowstep(original) ]
 dbws eval { update src_original set uni_norm = $n where id = $rowstep(id) }
}
05:11 Reply: Joining on Hebrew words including vowel points and cantillation marks (artifact: fbd8ee868b user: 1codedebugger)
This may not be an area in which many people are interested, but I was given an answer on stackoverflow concerning how to accomplish this in Tcl and Linux.

The answer is here if links are permitted. (https://stackoverflow.com/questions/67629309/unicode-normalization-of-hebrew-in-tcl-sqlite-or-whatever-else-will-work-in-li/67635812#67635812)

The main part is this procedure provided by Donal Fellows.

proc normalize {string {form nfc}} {
    exec uconv -f utf-8 -t utf-8 -x "::$form;" << $string
}
2021-05-20
04:10 Reply: Joining on Hebrew words including vowel points and cantillation marks (artifact: 0cb59774da user: 1codedebugger)

Thank you. I've never heard of any of this before and it certainly sounds like the issue I have with these Hebrew sources. Tcl and JavaScript appear to have normalization methods. The Tcl link looks like it hasn't been updated in a long time though. ( https://core.tcl-lang.org/tcllib/doc/trunk/embedded/md/tcllib/files/modules/stringprep/unicode.md ) Thanks again.

01:47 Edit: Joining on Hebrew words including vowel points and cantillation marks (artifact: 8603d24b83 user: 1codedebugger)

Hello, I'm working with three different sources of Hebrew and would like to join the tables based on the Hebrew words. I'm pretty sure everything is in UTF-8 and all three sources claim to have used the exact same source for the Hebrew but many words don't match, yet they appear identical on the screen including vowel points and cantillation marks.

For example, for the word below, each source displays the exact same word visually, but if they are written out character-by-character, it appears that they are built in a different order. The right most letter in one table is built as first the reversed-looking C symbol, then the dot inside of it, and then the double stacked dots below it. In another table, the stacked dots are second and the single dot last.

בְּרֵאשִׁית

Is there anything that can be done to be make them match for a join? Or is it a case of bytes are bytes and, if their not in the same order, they're different no matter how they render?

Thank you.

01:46 Post: Joining on Hebrew words including vowel points and cantillation marks (artifact: 220c770657 user: 1codedebugger)

Hello, I'm working with three different sources of Hebrew and would like to join the tables based on the Hebrew words. I'm pretty sure everything is in UTF-8 and all three sources claim to have used the exact same source for the Hebrew but many words don't match, yet they appear identical on the screen including vowel points and cantillation marks.

For example, for the word below, each source displays the exact same word visually, but if they are written out character-by-character, it appears that they are built in a different order. The right most letter in one table is built as first the reversed-looking C symbol, then the dot inside of it, and then the double stacked dots below it. In another table, the stacked dots are second and the single dot last.

בְּרֵאשִׁית

Is there anything that can be done to be make them match for a join? Or is it a case of bytes are bytes and, if their not in the same order, their different no matter how they render?

Thank you.

2021-05-17
00:16 Edit reply: Novice question concerning tcl sqlite and eval options and reusing prepared statements (artifact: 1e4e18ad3b user: 1codedebugger)

Thank you. May I ask a specific follow-up please?

Is the proper method of preparing a statement that of passing the SQL in curly braces with the $var inside such that the variable substitution does not take place before it is passed to the eval command? Does that treat the $var something like a '?' in the C API and generate an equivalent prepared statement that will be used again when $var is a different value?

Or are prepared statements cached only after substitution such that there is not an equivalent in Tcl SQLite of a prepared statement with ?'s that can be reset and new values set through binding, whether automatic or manually coded?

For instance, regarding the SQLite documentation example of db1 eval {INSERT INTO t1 VALUES(5,$bigstring)} would there be a prepared statement with a '?" for $bigstring, such that, if the same statement was performed again when $bigstring was a different value, a new statement would not be prepared but the cached one would be reset and a new value bound?

I don't mean to be tedious or annoying, but am just not understanding how the way the Tcl expression is constructed impacts the way SQLite prepares statements for re-use.

Thank you.

00:15 Reply: Novice question concerning tcl sqlite and eval options and reusing prepared statements (artifact: ab50365ed0 user: 1codedebugger)

Thank you. May I ask a specific follow-up please?

Is the proper method of preparing a statement that of passing the SQL in curly braces with the $var inside such that the variable substitution does not take place before it is passed to the eval command? Does that treat the $var something like a '?' in the C API and generate an equivalent prepared statement that will be used again when $var is a different value?

Or are prepared statements cached only after substitution such that there is not an equivalent in Tcl SQLite of a prepared statement with ?'s that can be reset and new values set through binding, whether automatic or manually coded?

For instance, regarding the SQLite documentation example of db1 eval {INSERT INTO t1 VALUES(5,$bigstring)} would there be a prepared statement with a '?" for $bigstring, such that, if the same statement was performed again when $bigstring was a different value, a new statement would not be prepared but the cached one would be reset and a new value bound?

I don't meant be tedious or annoying, but am just not understanding how the way the Tcl expression is constructed impacts the way SQLite prepares statements for re-use.

Thank you.

2021-05-16
20:32 Post: Novice question concerning tcl sqlite and eval options and reusing prepared statements (artifact: aa7a8d4311 user: 1codedebugger)
I've used only the C API and CLI in the past and am trying to use the Tcl API now and am a bit confused about the concept of reusing prepared statements in Tcl API.

In the documentation, the form of eval is:
dbcmd  eval  ?options?  sql   ?array-name?  ?script?

The only option I saw was -withoutnulls.  If there are others, would you please tell me where their documentation can be located?

Regarding the caching of prepared statements, how is a prepared statement reset and new values of arguments/parameters bound?  I understand that regular Tcl substitution will handle the binding but I don't understand how using eval again will reuse a query previously run using eval as opposed to just creating a new one altogether; because eval sort of does it all--prepares, binds(or already bound by Tcl substitution before passed to the command), executes, and steps.

Also, is there any reason to use the TDBC extension?  I was reading over NadKarni's book on Tcl and see that in TDBC the prepared statement appears to be stored in a variable, if there are variable parameters then they are passed at execution of the statement which is coded explicitly.  Other than perhaps that, I don't see what it adds over the methods documented on the SQLite Tcl API documentation.

Thank you.
2021-04-21
04:16 Post: Should tables be populated based upon a known/expected pattern of retrieval? (artifact: eae63cb9d6 user: 1codedebugger)

I'm trying to compare two sources of data that are supposed to each contain at least one column of identical data (the same original source from which each claims to have been constructed) and be very close otherwise.

I unpacked the ugly data blocks from each source into two separate tables, in which there are about 500,000 rows of 10 columns, where no column is over 50 bytes.

If everything were perfect, there'd be an equal number of rows and one identical column, such that the two tables could be joined on that column to add the other columns from table 2 into table 1.

Since that isn't the case and I cannot make it so without adding my "opinion" to one or both of the data sources, I have a question of whether the two tables should remain separate or combined vertically since the layouts are identical.

I read here before that, if the table structures are the same, there's no good reason to have two tables instead of one with an added identifier.

If that is true, I've another question. Even though the tables don't match exactly in a one-to-one correspondence, each has the exact same number of divisions in it, where a division is generally between 2 and 50 rows of data. Since almost every case of examining this data will be to compare the same division from each table, should the two tables be combined in that manner?

If table 2 is simply appended to table 1, when two divisions are compared, n number of rows will be retrieved from the top of the table and then n or close to n rows will be retrieved about 500,000 rows lower down in the table.

I'm writing way beyond my depth of understanding here but should the final combined table be built ordered by table 1 block 1, table 2 block 1, table 1 block 2, table 2 block 2, ..., such that both blocks may more likely be on the same page making the retrieval query more efficient?

Also, if this data is to be made accessible through a GUI that permits the user to edit the data, which is done by adding a new row to the table with an incremented version number, such that the original is always preserved, does that remove any efficiency gained by ordering the table by division?

Thank you for considering my very novice question.

2021-04-04
00:55 Reply: In C API, do prepared statements of simple inserts execute repeatedly even if not reset? (artifact: 58f90c126b user: 1codedebugger)

Oh, I see, now. Thank you very much for the explanation. I'm sure I read this before and applied it when the queries returned more than one row. But I use SQLite in small intervals with with big gaps in between and got stupid. I don't think I'll forget this again now. Thanks a lot.

2021-04-03
19:12 Reply: In C API, do prepared statements of simple inserts execute repeatedly even if not reset? (artifact: 77f344e74d user: 1codedebugger)
Thank you for the explanation.  I think I now understand why sqlite3_step() didn't fail for the insert statements without a manual reset.  However, now my question is why does the select statement fail without a manual reset?

In the code below, the sqlite3_step(db_base->insertLexi) works repeatedly without a manual reset.

The first time the sqlite3_step(db_base->getLexi) it is executed, both sqlite3_column_text(db_base->getLexi, 0) and sqlite3_column_text(db_base->getLexi,8) point to data. The second time it is executed, without a manual reset, sqlite3_step(db_base->getLexi) returns SQLITE_DONE and both the sqlite3_column_text(...) return NULL. But with a manual reset, both the sqlite3_column_text(...) again point to data.

It appears that the statement is not being reset automatically for the sqlite3_step(db_base->getLexi).  I'm not arguing that it is not being reset but only saying that it appears to behave that way in that without the manual reset, I can't get the statement to return data. All the query requests is a select of the row with the greatest rowid. 

Thank you.

void write_json_lexi( const char *c, const char *tab )
  { 
    int rc = 0;     
    if ( ( rc = sqlite3_step( db_base->insertLexi ) ) != SQLITE_DONE )
      {
        sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":1,\"msg\":\"Failed to db_base->insertLexi.\" }", tab, *c );
        send_response( response );
        sqlite3_reset( db_base->insertLexi );
        sqlite3_reset( db_base->getLexi );
        return;
      }

    sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":0, \"msg\":\"Successfully wrote to json_raw.\"}", tab, *c );
    send_response( response );

    char *data,
         *strongs_no;

    if ( ( ( rc = sqlite3_step( db_base->getLexi ) ) != SQLITE_ROW && rc != SQLITE_DONE ) || 
         ( strongs_no = sqlite3_column_text( db_base->getLexi, 0 ) ) == NULL ||
         ( data = sqlite3_column_text( db_base->getLexi, 8 ) ) == NULL )
      {
        sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":1,\"rc\":%d,\"msg\":\"Failed to db_base->getLexi.\" }", tab, *c, rc );
        send_response( response );
        sqlite3_reset( db_base->insertLexi );
        sqlite3_reset( db_base->getLexi );
        return;
      }

    int szBytes = sqlite3_column_bytes( db_base->getLexi, 8 );
    sprintf( response, "{\"tab\":\"%s\",\"c\":\"%c\",\"s\":0,\"strongs_no\":\"%s\",\"bytes\":%ld,\"data\":\"%s\" }", tab, *c, strongs_no, szBytes, data );
    send_response( response );
    
    sqlite3_reset( db_base->insertLexi );
    sqlite3_reset( db_base->getLexi );

    return;

  } // close write_json_lexi
06:17 Post: In C API, do prepared statements of simple inserts execute repeatedly even if not reset? (artifact: 5d1b54dd09 user: 1codedebugger)

I have a stupid question that I'm curious about because I made a stupid mistake but half of the process appears to have worked despite the error.

I left out some sqlite3_reset() statements for persistent prepared statements that were used to insert data. The same prepared statement was executed thousands of times without being reset and the data looks fine.

However, when I altered the code to test the success of each write to the database by extracting a piece of the just written data and sent it back to the sending application, it failed to extract the data; and that is how I noticed my mistake.

Do prepared statements that are simple inserts continue to work properly without running sqlite_reset()? The inserts are pretty simple because identically structured JSON passed to the C code is parsed using JSON_tree() and then written to the database based on path or fullkey.

Of course, I don't intend to code that way but wondered why the code didn't error/fail upon attempting to execute the prepared statement a second time for insert when it does for select.

Thank you.

2021-03-31
05:28 Edit reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 19f7240e88 user: 1codedebugger)
Thank you for the responses.  Perhaps this will be a bore but I add this clarification for anyone else at my limited level of understanding of these basic topics. I may still be misunderstanding.  Ultimately, it's not a SQLite question apart from whether or not the form of the GIF image passed to C should be converted in C before being stored in the database, or if there is way to store the UTF-8 as passed. 

Computer-science terminology is far from my native language but I'm not fabricating the information. The response returned by the fetch promise is consumed as a blob, which also provides a "type" property providing the mime type.  That is the source of "image/gif;charset=utf-8". I'm not qualified to comment on whether or not that is bizarre and leave that to whoever coded the browser or image to convey that information.  Even after the blob is converted to a base64 data URL the string begins with "data:image/gif;charset=utf-8;base64,...".

Many methods are discussed in the browser documentation and other forums and I tried most of them before posting. Using the text() method of the blob object returns a UTF-8 string.  Converting the blob to an array buffer and then decoding it to the default UTF-8 provides the exact same result. That's what I meant by a circle.  Both of these can be placed in a JSON string, but binary data cannot be directly placed in a JSON string (if you know how, please let me know).  The GIF image cannot be shoved into a JSON string.  Nor can it be converted in C, in this scenario, because it cannot be passed to C since JSON is the only method of communication offered by the browser extension APIs. It simply won't accept it or replaces it with nothing, such as an empty object. Thus, the web-extension native-messaging API cannot directly pass binary to the C application.  Apparently, the browser's object serialization methods take care of this step when storing a blob in indexedDB, which I thought was really SQLite behind it. Perhaps I was naive in thinking that if one can simply pass a blob to the add() and put() methods of indexedDB, it would be as easy in these circumstances using SQLite directly.  The SQLite part is easy but my lack of understanding of the different typed arrays and JSON limitations makes it a bit more challenging to get the GIF there.

Not knowing much, I thought UTF-8 would work in C but the \u0000 is the same as \0 and the read of the strings is terminated before desired completion. Perhaps they could be escaped before passing to C but I don't think UTF-8 is of much value, in that it would have to be converted again before displaying as an image.

It appears that converting the array buffer to string works okay and, perhaps, can be converted to binary or base64 in C.  The array buffer can be passed in the JSON string without converting to string when you build it but the browser will convert it to an object in the messaging API, including every index as text also, before finally converting it to string; and that increases the size by more than double. 

In attempt at following the browser documentation concerning typed arrays, I tried using btoa(), which errors on UTF-8 for characters larger than one byte; but converting the array buffer to Uint8Array() and then using btoa(), as suggested, produces base64.  The ratio of the byte length of base64 to that of the array buffer, in my small number of tests, has matched exactly the predicted 133% in the MDN documents. However, if you try to convert that base64 string back to an image, it doesn't work, even before passing it to C.  Perhaps it will work if you convert it to Uint8Array() again and then try to get that back to an array buffer to make a blob to use in an object URL.  But that is rather ridiculous.

Using the filereader() to read the blob as a data URL converts it to base64 and the image source can be set directly to base64 without conversion to display it. It's quite simple.  I tested this by passing to C, storing in database, retrieving, passing back to browser and displaying by setting image source to exact base64 string, no manual conversion needs.

The Tcl/Tk manual pages seem to state that base64 is better for GIF images than binary, where it reads "base64-encoded data (this is currently guaranteed to be supported for PNG and GIF images)."  So, that may do it, in that no additional conversion will be required to display it through Tk. I still need to see if these base64 strings will work in Tk. You'd think they would but I'm not confident of it.

There are about five thousand of these small GIF images; so I don't know whether or not it would be better to pass them as base64 to C and then convert them to binary before storing, if possible, to reduce storage space, or just save them as base64 and never convert again.

Thank you.


          fetch( iSrc )
            .then( response =>
              { 
                if ( !response.ok )
                  throw new Error( 'Network response was not ok.' );
                else
                  return response.blob();
              } )
            .then( result =>
              { 
                console.log( result.type ); // => "image/gif;charset=utf-8"

                // None of this code inside arrayBuffer produces anything usable that can be retrieved and displayed as an image again.
                // At least not in a few steps.

                result.arrayBuffer().then( ( arrBuf ) =>
                  {
                    const buffer = new Uint8Array( arrBuf );
                    const btoaBuf = btoa( buffer ); // This yields base64.

                    // btoaBuf.length / buffer.toString().length ~= 133%.

                    let utf8decoder = new TextDecoder(); // default 'utf-8'
                    const decBuf = utf8decoder.decode( arrBuf ); // Same as blob.text().

                    buffer.toString() is "179,12,0,..."

                  } );                  

                // This works and is simple.
                var reader = new FileReader();  
                reader.onloadend = function() { let base64 = reader.result; };
                reader.readAsDataURL( result );
05:25 Reply: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: e7199f37f3 user: 1codedebugger)
Thank you for the responses.  Perhaps this will be a bore but I add this clarification for anyone else at my limited level of understanding of these basic topics. I may still be misunderstanding.  Ultimately, it's not a SQLite question apart from whether or not the form of the GIF image passed to C should be converted in C before being stored in the database, or if there is way to store the UTF-8 as passed. 

Computer-science terminology is far from my native language but I'm not fabricating the information. The response returned by the fetch promise is consumed as a blob, which also provides a "type" property providing the mime type.  That is the source of "image/gif;charset=utf-8". I'm not qualified to comment on whether or not that is bizarre and leave that to whoever coded the browser or image to convey that information.  Even after the blob is converted to a base64 data URL the string begins with "data:image/gif;charset=utf-8;base64,...".

Many methods are discussed in the browser documentation and other forums and I tried most of them before posting. Using the text() method of the blob object returns a UTF-8 string.  Converting the blob to an array buffer and then decoding it to the default UTF-8 provides the exact same result. That's what I meant by a circle.  Both of these can be placed in a JSON string, but binary data cannot be directly placed in a JSON string (if you know how, please let me know).  The GIF image cannot be shoved into a JSON string.  Nor can it be converted in C, in this scenario, because it cannot be passed to C since JSON is the only method of communication offered by the browser extension APIs. It simply won't accept it or replaces it with nothing, such as an empty object. Thus, the web-extension native-messaging API cannot directly pass binary to the C application.  Apparently, the browser's object serialization methods take care of this step when storing a blob in indexedDB, which I thought was really SQLite behind it. Perhaps I was naive in thinking that if one can simply pass a blob to the add() and put() methods of indexedDB, it would be as easy in these circumstances using SQLite directly.  The SQLite part is easy but my lack of understanding of the different typed arrays and JSON limitations makes it a bit more challenging to get the GIF there.

Not knowing much, I thought UTF-8 would work in C but the \u0000 is the same as \0 and the read of the strings is terminated before desired completion. Perhaps they could be escaped before passing to C but I don't think UTF-8 is of much value, in that it would have to be converted again before displaying as an image.

It appears that converting the array buffer to string works okay and, perhaps, can be converted to binary or base64 in C.  The array buffer can be passed in the JSON string without converting to string when you build it but the browser will convert it to an object in the messaging API, including every index as text also, before finally converting it to string; and that increases the size by more than double. 

In attempt at following the browser documentation concerning typed arrays, I tried using btoa(), which errors on UTF-8 for characters larger than one byte; but converting the array buffer to Uint8Array() and then using btoa(), as suggested, produces base64.  The ratio of the byte length of base64 to that of the array buffer, in my small number of tests, has matched exactly the predicted 133% in the MDN documents. However, if you try to convert that base64 string back to an image, it doesn't work, even before passing it to C.  Perhaps it will work if you convert it to Uint8Array() again and then try to get that back to an array buffer to make a blob to use in an object URL.  But that is rather ridiculous.

Using the filereader() to read the blob as a data URL converts it to base64 and the image source can be set directly to base64 without conversion to display it. It's quite simple.  I tested this by passing to C, storing in database, retrieving, passing back to browser and displaying by setting image source to exact base64 string, no manual conversion needs.

The Tcl/Tk manual pages seem to state that base64 is better for GIF images than binary, where it reads "base64-encoded data (this is currently guaranteed to be supported for PNG and GIF images)."  So, that may do it, in that no additional conversion will be required to display it through Tk. I still need to see if these base64 strings will work in Tk. You'd think they would but I'm not confident of it.

There are about five thousand of these small GIF images; so I don't know whether or not it would be better to pass them as base64 to C and then convert them to binary before storing, if possible, to reduce storage space, or just save them as base64 and never convert again.

Thank you.


          fetch( iSrc )
            .then( response =>
              { 
                if ( !response.ok )
                  throw new Error( 'Network response was not ok.' );
                else
                  return response.blob();
              } )
            .then( result =>
              { 
                console.log( result.type ); // => "image/gif;charset=utf-8"

                // None of this code inside arrayBuffer produces anything usable that can be retrieved and displayed as an image again.
                // At least not in a few steps.

                result.arrayBuffer().then( ( arrBuf ) =>
                  {

                    const buffer = new Uint8Array( arrBuf );

                    const btoaBuf = btoa( buffer ); // This yields base64.

                    // btoaBuf.length / buffer.toString().length ~= 133%.

                    let utf8decoder = new TextDecoder(); // default 'utf-8'
                    const decBuf = utf8decoder.decode( arrBuf ); // Same as blob.text().

                    buffer.toString() is "179,12,0,..."

                  } );                  


                // This works and is simple.

                var reader = new FileReader();
  
                reader.onloadend = function() { let base64 = reader.result; };

                reader.readAsDataURL( result );
2021-03-30
08:39 Edit: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 3cb5f64306 user: 1codedebugger)

All on the client, I'm passing a JSON string from the browser to a C file compiled with the SQLite amalgamation version 3.34, in which one property is an image of mime type "image/gif;charset=utf-8" and around 120kb. Writing the object to the console before sending it, displays many symbols. The writing of the data to the SQLite database succeeds without error and at the CLI the data can be selected, but the GIF data is only about 13 characters long.

I thought perhaps it was just a display issue because earlier, when working with Hebrew and Greek text, the CLI displayed a lot of ??? but when the data was retrieved and passed back to the browser it was displayed properly. I tried retrieving the GIF data and passing it back but it still was only 13 characters in length. Even strlen() in C is 13 characters.

I tried passing it as a blob and storing as a blob, converting the blob to array buffer and encoding as UTF-8 before passing (which I think was nothing more than a circle since the GIF was UTF-8) and storing it as text and again as blob; and the only difference is that passing it back from sqlite3_column_blob() fails at the first character and sqlite3_column_text() passes the 13 characters.

I'm sure I'm doing something stupid because I don't know something important, but how can I tell whether or not the GIF was written completely? I can see at the CLI that the rest of the data was written completely.

The JSON sent back to the browser is built in C using sprintf() and %s is used for the GIF from sqlite3_column_text(). If it printed properly at the CLI, I'd know it was something to do with building the JSON. SQLite doesn't care what the data type is; so, if it is writing it to the database then could it be that one of those UTF-8 symbols is acting as a termination character in the string? And, if so, how can that be corrected? An error in the browser when attempting to parse the JSON states "bad control character".

[ I can see now that only 13 characters are written to the database because sqlite3_column_bytes() returns 13. The 14th character is \u0000, which I just learned is the same as \0. Perhaps conversion to base64 will correct this; but I thought that was 133% larger. Converting to Uint8 made content multiple times larger.]

Also, would it be better to change the GIF to another format before storing it? The images are stored to be viewed in Tcl/Tk not the browser, if that impacts what format would be best.

Thank you for any direction you may be able to provide.

07:03 Edit: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 6a7001b651 user: 1codedebugger)

All on the client, I'm passing a JSON string from the browser to a C file compiled with the SQLite amalgamation version 3.34, in which one property is an image of mime type "image/gif;charset=utf-8" and around 120kb. Writing the object to the console before sending it, displays many symbols. The writing of the data to the SQLite database succeeds without error and at the CLI the data can be selected, but the GIF data is only about 13 characters long.

I thought perhaps it was just a display issue because earlier, when working with Hebrew and Greek text, the CLI displayed a lot of ??? but when the data was retrieved and passed back to the browser it was displayed properly. I tried retrieving the GIF data and passing it back but it still was only 13 characters in length. Even strlen() in C is 13 characters.

I tried passing it as a blob and storing as a blob, converting the blob to array buffer and encoding as UTF-8 before passing (which I think was nothing more than a circle since the GIF was UTF-8) and storing it as text and again as blob; and the only difference is that passing it back from sqlite3_column_blob() fails at the first character and sqlite3_column_text() passes the 13 characters.

I'm sure I'm doing something stupid because I don't know something important, but how can I tell whether or not the GIF was written completely? I can see at the CLI that the rest of the data was written completely.

The JSON sent back to the browser is built in C using sprintf() and %s is used for the GIF from sqlite3_column_text(). If it printed properly at the CLI, I'd know it was something to do with building the JSON. SQLite doesn't care what the data type is; so, if it is writing it to the database then could it be that one of those UTF-8 symbols is acting as a termination character in the string? And, if so, how can that be corrected? An error in the browser when attempting to parse the JSON states "bad control character".

[ I can see now that only 13 characters are written to the database because sqlite3_column_bytes() returns 13. ]

Also, would it be better to change the GIF to another format before storing it? The images are stored to be viewed in Tcl/Tk not the browser, if that impacts what format would be best.

Thank you for any direction you may be able to provide.

06:27 Edit: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 2735a2a31e user: 1codedebugger)

All on the client, I'm passing a JSON string from the browser to a C file compiled with the SQLite amalgamation version 3.34, in which one property is an image of mime type "image/gif;charset=utf-8" and around 120kb. Writing the object to the console before sending it, displays many symbols. The writing of the data to the SQLite database succeeds without error and at the CLI the data can be selected, but the GIF data is only about 13 characters long.

I thought perhaps it was just a display issue because earlier, when working with Hebrew and Greek text, the CLI displayed a lot of ??? but when the data was retrieved and passed back to the browser it was displayed properly. I tried retrieving the GIF data and passing it back but it still was only 13 characters in length. Even strlen() in C is 13 characters.

I tried passing it as a blob and storing as a blob, converting the blob to array buffer and encoding as UTF-8 before passing (which I think was nothing more than a circle since the GIF was UTF-8) and storing it as text and again as blob; and the only difference is that passing it back from sqlite3_column_blob() fails at the first character and sqlite3_column_text() passes the 13 characters.

I'm sure I'm doing something stupid because I don't know something important, but how can I tell whether or not the GIF was written completely? I can see at the CLI that the rest of the data was written completely.

The JSON sent back to the browser is built in C using sprintf() and %s is used for the GIF from sqlite3_column_text(). If it printed properly at the CLI, I'd know it was something to do with building the JSON. SQLite doesn't care what the data type is; so, if it is writing it to the database then could it be that one of those UTF-8 symbols is acting as a termination character in the string? And, if so, how can that be corrected? An error in the browser when attempting to parse the JSON states "bad control character".

Also, would it be better to change the GIF to another format before storing it? The images are stored to be viewed in Tcl/Tk not the browser, if that impacts what format would be best.

Thank you for any direction you may be able to provide.

06:23 Post: Trouble writing/retrieving a small UTF-8 GIF image. (artifact: 6fb14e85dd user: 1codedebugger)

All on the client, I'm passing a JSON string from the browser to a C file compiled with the SQLite amalgamation version 3.34, in which one property is an image of mime type "image/gif;charset=utf-8" and around 120kb. Writing the object to the console before sending it, displays many symbols. The writing of the data to the SQLite database succeeds without error and at the CLI the data can be selected, but the GIF data is only about 13 characters long.

I thought perhaps it was just a display issue because earlier, when working with Hebrew and Greek text, the CLI displayed a lot of ??? but when the data was retrieved and passed back to the browser it was displayed properly. I tried retrieving the GIF data and passing it back but it still was only 13 characters in length. Even strlen() in C is 13 characters.

I tried passing it as a blob and storing as a blob, converting the blob to array buffer and encoding as UTF-8 before passing (which I think was nothing more than a circle since the GIF was UTF-8) and storing it as text and again as blob; and the only difference is that passing it back from sqlite3_column_blob() fails at the first character and sqlite3_column_text() passes the 13 characters.

I'm sure I'm doing something stupid because I don't know something important, but how can I tell whether or not the GIF was written completely? I can see at the CLI that the rest of the data was written completely.

The JSON sent back to the browser is built in C using sprintf() and %s is used for the GIF from sqlite3_column_text(). If it printed properly at the CLI, I'd know it was something to do with building the JSON. SQLite doesn't care what the data type is; so, if it is writing it to the database then could it be that one of those UTF-8 symbols is acting as a termination character in the string? And, if so, how can that be corrected?

Also, would it be better to change the GIF to another format before storing it? The images are stored to be viewed in Tcl/Tk not the browser, if that impacts what format would be best.

Thank you for any direction you may be able to provide.

04:44 Edit: (Deleted) (artifact: 8f46d9b832 user: 1codedebugger)
Deleted
2021-03-29
19:00 Post: (Deleted) (artifact: 7a2a50db41 user: 1codedebugger)
A table has rows of JSON data similar to below where the length of "a" is variable and the number of objects withing any a[i] is variable.  I'd like to write this data to another table in separate columns as below, in which "v" is the index on "a" plus 1 and "i" is the index of any a[i] plus 1.

The query provided works and runs fast enough for my task, which is only a one-time job to build these tables and not something that needs to be optimized for repeated use.  However, I'd like to understand it better.  I thought perhaps that it could be done recursively since it is known that "b" and "c" start at "1" and have no gaps; but the fact that json_tree() and json_each() are tables has me confused on how to do so. Is there a better method?

Thank you.

{ 'b' : 1, "c" : 1, "a" : [ [ { "t" : "text", "s" : "text" }, { "t" : "text", "s" : "text" } ],
 
                            [ { "t" : "text", "s" : "text" }, { "t" : "text", "s" : "text", "v" : { "s" : "text", "a" : "text" } ] 

                          ] }

 b  c  v  i  t     s     v.s   v.a
 -  -  _  -  -     -     ---   ---
 1  1  1  1  text  text  text  text
 1  1  1  2  text  text  text  text
 1  1  2  1  text  text  text  text
 1  1  2  2  text  text  text  text
 1  1  2  3  text  text  text  text
 1  2  1  1  text  text  text  text
 2  1  1  1  text  text  text  text

----
with
  bkch as
    ( select book_no as b,
             chapter_no as c
             from json_raw
             group by b, c
             order by b, c ),
  temp as
    ( select k.b as b,
             k.c as c,
             --cast( je.key as integer ) as k,
             je.key as k,
             je.value as value
             from json_each( json_extract( ( select json_text as j
                                             from json_raw r
                                             where r.book_no = k.b 
                                               and r.chapter_no = k.c ), '$.a' ) ) je, bkch k ),
  keys as
    ( select b, c, k
      from temp
      group by b, c, k
      order by b, c, k )

select k.b, k.c, k.k + 1, 
       ( json_extract( value, '$.j' ) + 1 ) as i,
       json_extract( value, '$.t' ) as t,
       json_extract( value, '$.s' ) as s,
       json_extract( value, '$.v.s' ) as vs,
       json_extract( value, '$.v.a' ) as va
from json_each( ( select value
                  from temp t
                  where t.k = k.k
                  and t.b = k.b
                  and t.c = k.c ) ) je, keys k
2021-03-27
19:56 Post: (Deleted) (artifact: f2b97bd99d user: 1codedebugger)

When using json_tree() on JSON that includes multi-dimensional arrays such that path='$.a[0]' through '$.a[n]' and fullkey='$.a[0][0]' through '$.a[n][m]' is there a way to get the index of the $.a[n] in the path or fullkey?

I see that key is the text value of the second dimension of fullkey but don't see how to get the first dimension from either path or fullkey.

I'd like to be able to write a row with the dimensions as separate columns.

The JSON is similar to {"book":1,"chapter":2,"a":[[],[],[],[]]} where the the first dimension of a is the verse and the dimension within each array in a is the position within the verse. I'd like to make a table that has separate columns of book chapter, verse, position as integers.

{ "book":1,"chapter":"1",` 
  "a":[ [{"t":"In the beginning","s":"H7225"},
         {"t":"God","s":"H430"},
         {"t":"created","s":"H1254","v":{"s":"Qal","a":"Perfect"}},
         {"t":"","s":"H853"},
         {"t":"the heaven","s":"H8064"},
         {"t":"and","s":"H853"},
         {"t":"the earth.","s":"H776"} ],
        [{"t":"And the earth","s":"H776"},
         {"t":"was","s":"H1961","v":{"s":"Qal","a":"Perfect"}}, 
         {"t":"without form,","s":"H8414"},
         {"t":"and void;","s":"H922"},
         {"t":"and darkness","s":"H2822"},
         {"t":"<em>was</em> upon the face","s":"H6440"}, 
         {"t":"of the deep.","s":"H8415"},
         {"t":"And the Spirit","s":"H7307"}
         {"t":"of God","s":"H430"},
         {"t":"moved","s":"H7363","v":{"s":"Piel","a":"Participle"}},
         {"t":"upon","s":"H5921"},
         {"t":"the face","s":"H6440"}, 
         {"t":"of the waters.","s":"H4325"}] ] }

Thus, path='$.a[0]' is verse 1 and fullkey='$.a[0][2]' is the third position in the first verse or the object where 't' = "created". Column key provides the 2 but I can't get the 0.

The following works up to getting the 0.

SELECT cast(key as integer) AS k, value, path, fullkey FROM request WHERE path LIKE '$.a[%]' AND fullkey LIKE '%]' AND type='object' ORDER BY path, k

but the results aren't ordered numerically by the first index in path because it is text.

Also, is it possible to get book and chapter in every result row in the same query, such that the row could be 1, 1, 1, 2, {"t":"created","s":"H1254","v":{"s":"Qal","a":"Perfect"}}?

Thank you.

17:47 Reply: Novice question about running UTF-8 on CLI from file. (artifact: 937727cc4e user: 1codedebugger)

I was referring to what I read at the first link below; but I have subsequently checked on a Windows 10 Pro machine to which I have access and it is as was already stated by TripeHound.

The second link claims it can be changed by running a script but I did not try that out of fear I'd irreversibly mess something else up in the OS doing so.

https://www.bleepingcomputer.com/news/microsoft/windows-10-notepad-is-getting-better-utf-8-encoding-support/

https://www.tenforums.com/software-apps/110704-unreadable-non-ansi-characters-notepad.html

04:47 Reply: Novice question about running UTF-8 on CLI from file. (artifact: 7ef72b1b51 user: 1codedebugger)

Thanks. It appears that cannot be done in Notepad unless using Windows 10. I'm not up to date. Guess a different editor is required.

04:02 Post: Novice question about running UTF-8 on CLI from file. (artifact: 1e5222a094 user: 1codedebugger)

I'm using version 3.34.0 at the CLI and have a Windows Notepad file that has UTF-8 characters that I want to run using .read filename.sql.

When I try it, it fails and states "Error near ?select" which is the very first word in the file.

If I type "Pragma encoding;", it returns UTF-8.

Would you please tell me if this should work and, if so, what needs to be done?

Thank you.

2021-03-21
04:48 Reply: Proper set up for working with SQLite, C, Tcl/Tk? (artifact: fb1254a4f3 user: 1codedebugger)

Just wanted to say thanks for the reference to magicsplat. I had never heard of it before and it lead me to a more recent book by eight years which includes information on starkits and starpacks; and the same web page linked to a tutorial on updates to Tk and some other useful documents. I'm not the greatest at web searching so this comment may be a bit unfair, but, where it's been written that Tcl/Tk is the "best-kept secret in the software industry" it might be in part to the fact that it is rather hard to find even when one is looking for it.

2021-03-20
04:03 Reply: Proper set up for working with SQLite, C, Tcl/Tk? (artifact: 3a4e1f9d0e user: 1codedebugger)

Maybe I need to reconsider Visual Studio. I've been trying to find tclkit and whatever else is required to build a starpack and every page/source I find hasn't been updated in 5 to 10 years.

I bought a newly printed book, although it was written about ten years ago, but nearly every source it references is out of date or a confusing visual mess to me.

2021-03-18
05:00 Post: Proper set up for working with SQLite, C, Tcl/Tk? (artifact: 6bf4ceaac1 user: 1codedebugger)

This isn't a SQLite how-to question but a where or in-what question. If out of scope here, please let me know and I will delete it if I have the permission to do so. It just seems that this may be the place from which to get the most-practical and least-subjective answer.

If one wants to develop using SQLite, C, and Tcl/Tk from a home office(bonus room) using a desktop set up, is there an operating system and/or other tool sets that would be preferrable in the long term? I've been using the SQLite C API and write my code in regular old notepad. I want to move about 50k lines of code from a different language into those mentioned above before writing more code, and received my 800 page Tcl/Tk book today.

I'm just an old novice and I don't care about IDEs and color-coding my code, just functionality in the things I can't see taking place. I don't care for Windows and hated Visual Studio; I'm not purchasing a Mac/Apple; I can't make any good sense out of the multitude of Linux distributions(they all seem the same apart from the GUI); and I'm too old to build my own even if I had the ability.

Is it all just about a visual preference or is there an OS truly better in fundamentals for coding in SQLite, C, and Tcl/Tk?

Thank you for any suggestions you may be able to provide.

2021-03-10
07:12 Edit reply: How to handle double backslashes when binding parameters in C API? (artifact: 205aa358f1 user: 1codedebugger)

Thank you very much for the explanation.

I thought these comments relative to the local text file rather than stdout in general, because I read similar information last night. I don't mess with Chrome browser at all but found in their development area a warning concerning Windows, much like your information, and it directed to a Windows web page that discussed a _setmode() function and it used _O_BINARY flag for stdin and stdout.

I tried using _setmode() on stdout with the _O_BINARY flag, with and without the underscores (for some reason half of Windows function names appear to have been deprecated and prefixed with an underscore) and it failed. It worked for stdin but not stdout.

After reading your response, I went back to that Chrome developer message and the referenced Windows web page and tried it all again; and this time it worked!

It changed _setmode() on stdout to flag _O_BINARY and the extension now reads 266 and 522 successfully. I wouldn't be surprised if I typed an 0 instead of an O in O_BINARY when I added it for stdout or something stupid like that after so many tries.

Thanks again! I can't tell you what a relief it is that it works. I was ready to throw the browser away and Windows away, and either give up or try another UI and OS. I may still do that when I get a chance anyways.

More ↓