~~~
$ sqlite3.exe
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> drop table if exists FileBlob;
sqlite> create table if not exists FileBlob( id integer primary key, content text, size integer, separator text );
sqlite> insert or replace into FileBlob(id,content) values( 1, replace(ReadFromFile('D:\PEMS.WIN\RAD\SQLite3x\sandbox\RowsInLines.txt'),X'0d0a',X'0a') );
sqlite> update FileBlob set size=length(Content), separator=cast(X'0a0a' as text) where id==1;
~~~
then some basic stuff before I try to go into the details for better understandin how it is working:
~~~
sqlite> .mode box
sqlite> with recursive Lines(RowContent,EndPos) as (
...> values( NULL, 1 ) --previous endpos=startpos
...> union
...> select L.EndPos||'+content' as RowContent
...> ,(L.EndPos + 6) as EndPos -- new endpos
...> from Lines L join FileBlob B on B.id==1
...> where L.EndPos < length(B.content)
...> )
...> select * from Lines where RowContent notNull;
┌─────────────┬────────┐
│ RowContent │ EndPos │
├─────────────┼────────┤
│ 1+content │ 7 │
│ 7+content │ 13 │
│ 13+content │ 19 │
│ 19+content │ 25 │
│ 25+content │ 31 │
│ 31+content │ 37 │
...
│ 469+content │ 475 │
│ 475+content │ 481 │
│ 481+content │ 487 │
└─────────────┴────────┘
sqlite>
~~~
now replacing the constant values by results from instr() function using the FileBlob and its content size
~~~
sqlite> .mode line
sqlite> with recursive Lines(RowContent,EndPos) as (
...> values( NULL, 1 ) --previous endpos=startpos
...> union
...> select substr(B.content,L.EndPos, instr(substr(B.content,L.EndPos),B.separator) ) as RowContent
...> , L.EndPos + instr(substr(B.content,L.EndPos),B.separator) + 2 as EndPos
...> from Lines L join FileBlob B on B.id==1
...> where L.EndPos between 1 and B.size -- recursion end condition
...> )
...> select * from Lines where RowContent notNull;
RowContent = Hardside Cabin Luggage Trolley Black
senator
View Product Details
N18602991A KH132-20_BLK
KH13220BLK
02
Days
04
Hrs
25
Mins
25
Sec
1
1
0
EndPos = 141
RowContent = Hardside 4 Wheels Cabin Luggage Trolley Burgundy
senator
View Product Details
N42588980A KH134-20_BGN
KH13420BGN
02
Days
04
Hrs
25
Mins
25
Sec
1
1
0
EndPos = 293
RowContent = Softside Cabin Luggage Trolley Purple
senator
View Product Details
N32139616A LL051-20_PRP
LL05120PRP
02
Days
09
Hrs
25
Mins
25
Sec
1
1
0
EndPos = 434
sqlite>
~~~
or for easier readability with the line breaks in .box mode
~~~
sqlite> .mode box
sqlite> with recursive Lines(StartPos,RowContent,EndPos) as (
...> values( NULL, NULL, 1 ) --previous endpos=startpos
...> union
...> select L.EndPos as StartPos
...> , substr(B.content,L.EndPos, instr(substr(B.content,L.EndPos),B.separator) ) as RowContent
...> , L.EndPos + instr(substr(B.content,L.EndPos),B.separator) + 2 as EndPos
...> from Lines L join FileBlob B on B.id==1
...> where L.EndPos between 1 and B.size -- recursion end condition
...> )
...> select StartPos,replace(RowContent,X'0a',' ')Content,EndPos
...> from Lines
...> where RowContent notNull;
┌──────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────┐
│ StartPos │ Content │ EndPos │
├──────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────┤
│ 1 │ Hardside Cabin Luggage Trolley Black senator View Product Details N18602991A KH132-20_BLK KH13220BLK 02 Days 04 Hrs 25 Mins 25 Sec 1 1 0 │ 141 │
│ 141 │ Hardside 4 Wheels Cabin Luggage Trolley Burgundy senator View Product Details N42588980A KH134-20_BGN KH13420BGN 02 Days 04 Hrs 25 Mins 25 Sec 1 1 0 │ 293 │
│ 293 │ Softside Cabin Luggage Trolley Purple senator View Product Details N32139616A LL051-20_PRP LL05120PRP 02 Days 09 Hrs 25 Mins 25 Sec 1 1 0 │ 434 │
└──────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┘
sqlite>
~~~
So far just to proove that reading blocks of lines can be done with standard SQLite3 with the help of the functions instr() and substr()
Next exercise will be to use the rows and separate the fields out of them.... to be continued