-- One table with whole files
CREATE TABLE content(
id INTEGER PRIMARY KEY,
data BLOB NOT NULL
);
-- And second table with line boundaries
CREATE TABLE lines(
id INTEGER NOT NULL REFERENCES content(id),
line_no INTEGER NOT NULL,
bytes_from INTEGER NOT NULL,
bytes_to INTEGER NOT NULL,
PRIMARY KEY(id, line_num)
) WITHOUT ROWID;
-- Use Incremental BLOB I/O https://www.sqlite.org/c3ref/blob_open.html
I would also consider keeping the largest files external to the
database. https://www.sqlite.org/intern-v-extern-blob.html
2018-08-03 21:04 GMT+02:00, Abramo Bagnara <abramo.bagn...@bugseng.com>:
>
> I need to save text files (let say between 1 KB to 20 MB) in a SQLite DB.
>
> I see two possibilities:
>
> 1) save all the content in a single column:
>
> create table content(id integer not null primary key,
> text blob not null);
>
> 2) split the content in lines:
>
> create table line(content integer not null,
> line integer not null,
> text blob not null,
> primary key(content, line));
>
> Some queries will need to extract the whole file, while other queries
> will need to extract the text for a range of lines.
>
> According to your experience it is better/faster the first option, the
> second option or a smarter option I've not considered?
>
> My partial considerations are:
>
> - 1 is simpler
> - 1 leads to faster load
> - 1 is slower to retrieve a range of lines (not 100% sure)
>
> --
> Abramo Bagnara
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
