Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[Rowan Worth]

On 6 August 2018 at 22:20, R Smith  wrote:

> Think of paragraphs in English as large records delimited by 2 or more
> Line-break characters (#10+#13 or perhaps only #10 if on a *nix platform)
> between texts.
>
> Each paragraph record could be comprised of one or more sentences (in
> English) as records delimited by a full-stop+Space or full-stop+linebreak,
> or even simply the paragraph end.


This is perfect because English has such strict rules and is so consistent;
how fortunate that the symbol used for delimiting sentences is never reused
for other purposes eg. to indicate an abbreviation or something crazy like
that.

:P
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] vtab stat*

[Richard Hipp]

On 8/6/18, Giorgi Guliashvili  wrote:
> I'm wondering if
> SQLITE_ENABLE_STAT4 works for vtabs and what are the other options I have.

No.

STAT4 provides information about indexes.  But virtual table have no
indexes - at least not indexes visible to the rest of the system.  (A
specific virtual table implement might implement its own internal
indexes, and indeed most of them do, but those indexes are not visible
as indexes to the rest of the system.)  Since STAT4 only provides
information about indexes, it cannot provide any information about
virtual tables.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] vtab stat*

[Giorgi Guliashvili]

Hi folks,

I'm using only Eponymous virtual tables, however, so far, the only hint I
give to the query planner was through some heuristic computing
xBestIndex->estimatedCost.

Now I'm considering other automatic, updating weights. I'm wondering if
SQLITE_ENABLE_STAT4 works for vtabs and what are the other options I have.

Thanks

Regards,
Giorgi Guliashvili
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exception at changing a row in .NET FW 4.7.1

[Joe Mistachkin]

Rtm Rbtsk wrote:
>
> If I change the data in the first row with the 'id'='1' in the table
> 'leslie', I get a DBConcurrencyException at the Update-command:
> 
> System.Data.DBConcurrencyException: 'Concurrency violation: the
> UpdateCommand affected 0 of the expected 1 records.' 
> 

It's hard to say what exactly is happening without seeing the C# code
involved; however, in the past, these types of issues have been caused
by a mismatch between how the .NET Framework and SQLite treat typing
of column values and/or a mismatch between the data type used in the
query and the one actually stored in the database file.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different behavior of .NET client 1.0.108.0SQLiteDataAdapter / DataReader on literal int values in select list.

[Joe Mistachkin]

mi...@willyschwabe.de wrote:
>
> i found out, that the following select statement produces different
> schemata in DataTable when using SQLiteDataAdapter.Fill and data
> rows are returned or not returned from the SELECT statement.
> 
> SELECT 1 AS literal FROM anTable WHERE 1<1 
> 

Since no rows are returend, there is no type information available
based on the value of the returned data (as there is no data).

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Exception at changing a row in .NET FW 4.7.1

[Rtm Rbtsk]

Hy,

If I change the data in the first row with the 'id'='1' in the table
'leslie', I get a DBConcurrencyException at the Update-command:

System.Data.DBConcurrencyException: 'Concurrency violation: the
UpdateCommand affected 0 of the expected 1 records.'

By deleting the row it raises in Program.cs in the Main-Class:

System.Data.DeletedRowInaccessibleException: 'Deleted row information
cannot be accessed through the row.'

It is only this one row, the other rows can be changed/deleted without
any problem. The database is here: 
https://www.dropbox.com/s/tqepj4ni806xa68/SQLITE.db?dl=0


Microsoft Visual Studio Community 2017 version 15.7.5    (.NET Framework
4.7.1)

Best,

Artem


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Exception at changing a row

[Rtm Rbtsk]

Hy,

If I change the data in the first row with the 'id'='1' in the table 
'leslie', I get a DBConcurrencyException at the Update-command:


System.Data.DBConcurrencyException: 'Concurrency violation: the 
UpdateCommand affected 0 of the expected 1 records.'


By deleting the row it raises in Program.cs in the Main-Class:

System.Data.DeletedRowInaccessibleException: 'Deleted row information 
cannot be accessed through the row.'


It is only this one row, the other rows can be changed/deleted without 
any problem. The database is attached.



Microsoft Visual Studio Community 2017 version 15.7.5    (.NET Framework 
4.7.1)


Best,

Artem

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Different behavior of .NET client 1.0.108.0 SQLiteDataAdapter / DataReader on literal int values in select list.

[micha]

Hello everyone,
i found out, that the following select statement produces different schemata in 
DataTable when using SQLiteDataAdapter.Fill and data rows are returned or not 
returned from the SELECT statement.

SELECT 1 AS literal FROM anTable WHERE 1<1

Especially i need those literals in UNION selects to identify the source select 
the particular data row came from.

If data rows are present, then the data type of the column in DataTable is 
System.Int64.
If no data rows are present, then the data type of the column in DataTable is 
System.Object.

When using FillSchema-Method then in both cases the data type of the column in 
DataTable is System.Object.

Best greetings 
Michael

Sample code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SQLite;

namespace SQLiteTest
{
class Program
{
static void Main(string[] args)
{
var file = Path.GetTempFileName();
var uri = "Data Source=:memory:";
var con = new SQLiteConnection(uri);
con.Open();
using (var cmd = con.CreateCommand())
{
Console.WriteLine("New database " + con.ConnectionString);
cmd.CommandText = "CREATE TABLE t1 (f1 BLOB)";
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO t1(f1) VALUES(NULL)";
Console.WriteLine("Insert one row: " + cmd.CommandText);
cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT 1 AS constVal FROM t1 WHERE 1<1";
Console.WriteLine("create SQLiteDataAdapter with SELECT: " + 
cmd.CommandText);
var ada = new SQLiteDataAdapter(cmd);
var set = new DataSet();
ada.SelectCommand = cmd;
Console.WriteLine("new DataSet -> call DbDataAdapter.Fill() 
while no data in table");
ada.Fill(set);
LogTypes(set);
Console.WriteLine("!!! wrong data type for column constVal 
!!!\n");
LogSchemaTable(cmd);
cmd.CommandText = "SELECT 1 AS constVal FROM t1 WHERE 1=1";
set = new DataSet();
ada.SelectCommand = cmd;
Console.WriteLine("new DataSet -> call DbDataAdapter.Fill() 
while one row in table");
ada.Fill(set);
LogTypes(set);
 Console.WriteLine("!!! correct data type for column constVal 
!!!\n");
 LogSchemaTable(cmd);

Console.WriteLine("\nSame test with 
SQLiteDataAdapter.FillSchema method\n\n" 
+ cmd.CommandText + "\n");

cmd.CommandText = "SELECT 1 as constVal FROM t1 WHERE 1<1";
Console.WriteLine("create SQLiteDataAdapter with SELECT: " + 
cmd.CommandText);
ada = new SQLiteDataAdapter(cmd);
set = new DataSet();
ada.SelectCommand = cmd;
Console.WriteLine("new DataSet -> call 
DbDataAdapter.FillSchema() while no data in table");
ada.FillSchema(set, SchemaType.Source);
LogTypes(set);
Console.WriteLine("!!! wrong data type for column constVal 
!!!\n");
cmd.CommandText = "INSERT INTO t1(f1) VALUES(NULL)";
Console.WriteLine("Insert one row: " + cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT 1 AS constVal FROM t1 WHERE 1=1";
set = new DataSet();
ada.SelectCommand = cmd;
Console.WriteLine("new DataSet -> call 
DbDataAdapter.FillSchema() while one row in table");
ada.FillSchema(set, SchemaType.Source);
LogTypes(set);
Console.WriteLine(@"!!! STILL wrong data type for column 
constVal !!!

conclusion: When using DataAdapter.Fill method with a SELECT statement that 
includes literal int values in select list,
then DataColumn-objects with different data types are generated, wether the 
SELECT statement returns rows or not.
When no rows are returned, System.Object type is used (wrong).
Whenat least on row is returned, System.Int64 is used (right). 
The FillSchema-Method in both cases generates System.Object column instead of 
System.Int64 column.");


Console.ReadLine();
}
}

private static void LogSchemaTable(SQLiteCommand cmd)
{
cmd.CommandText = "SELECT 1 as constVal FROM t1";
using (var rdr = cmd.ExecuteReader())
using (var sw = new StringWriter())
{
var t = rdr.GetSchemaTable();
t.WriteXml(sw);
Console.WriteLine("Schema table:\n" + sw.ToString());
}
}

private static void LogTypes(DataSet set)

Re: [sqlite] Save text file content in db: lines or whole file?

[Abramo Bagnara]

Il 04/08/2018 07:07, Abramo Bagnara ha scritto:
> Il 03/08/2018 23:53, Abroży Nieprzełoży ha scritto:
>> -- 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
> 
> Incremental BLOB I/O is faster than using substr(data, start, len) in a
> SELECT?

Someone familiar with implementation of BLOB I/O and sqlite VM can
answer to that?

I'd guess that *if* there is a difference it is greater with bigger
blobs, but it is also possible there is not any sensible difference if
substr act similarly to sqlite3_blob_read.

But my uninformed guesses are definitely not relevant, until an aware
Samaritan will come to clarify that ;-)

-- 
Abramo Bagnara

BUGSENG srl - http://bugseng.com
mailto:abramo.bagn...@bugseng.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestions: '.mode insert table' output to be within transaction, and REPLACE equivalent

[Tony Papadimitriou]

A couple of suggestions (I don’t know if they have come up before):

1. I often update the same table (e.g., a phone directory table) in various 
independent databases and it’s simpler to cascade the changes by doing this:

sql a.db “.mode insert table” “select * from table” | sql b.db

than with this:

sql a.db “.mode insert table_name” “select * from table_name” > xxx.sql
sql b.db
>begin;
>.read xxx.sql
>end;

The problem is the shell command method lacks the transaction and the process 
takes a lot longer for large tables.

So, my suggestion is for the ‘.mode insert table’ output to automatically 
include the beginning BEGIN; and the corresponding ending END; (or COMMIT;).

If the current behavior is also required as is then perhaps some option to do 
it this or that way so that this procedure can be accomplished with a single 
line shell command.

2. For the same case as above, but instead of INSERT to use REPLACE (or INSERT 
OR REPLACE) with a new mode, e.g.: .mode replace table_name

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using CTE with date comparison

[David Raymond]

The whole fencepost thing is probably doing weird things. Here's my take on it.
This is "time to get from A to B". If you want "Total timespan from A to B 
inclusive" then just add 1 day.

Trying to do the +1 day or -1 day in the middle for the fencepost thing is 
probably what's causing the error you mentioned. With one of the days being the 
1st, the -1 day might leave it on the 31st or the 30th or the 28th or the 29th, 
depending on the start month. Best to just wait to the very end and say "oh 
yeah, plus the start day"


with recursive
dates (startDate, endDate) as (values (:startDate, :endDate)),
yearsTable (startDate, years, months, days, resultDate, endDate)
as (
select min(startDate, endDate),
0, 0, 0,
min(startDate, endDate), max(startDate, endDate)
from dates
union all
select startDate, years + 1, months, days,
date(startDate,
'+' || cast(years + 1 as text) || ' years'),
endDate
from yearsTable where resultDate < endDate
),
monthsTable (startDate, years, months, days, resultDate, endDate)
as (
select * from (
select * from yearsTable where resultDate <= endDate
order by years desc, months desc, days desc limit 1)
union all
select startDate, years, months + 1, days,
date(startDate,
'+' || cast(years as text) || ' years',
'+' || cast(months + 1 as text) || ' months'),
endDate
from monthsTable where resultDate < endDate
),
daysTable (startDate, years, months, days, resultDate, endDate)
as (
select * from(
select * from monthsTable where resultDate <= endDate
order by years desc, months desc, days desc limit 1)
union all
select startDate, years, months, days + 1,
date(startDate,
'+' || cast(years as text) || ' years',
'+' || cast(months as text) || ' months',
'+' || cast(days + 1 as text) || ' days'),
endDate
from daysTable where resultDate < endDate
)
select  startDate, years, months, days, resultDate
from daysTable where resultDate = endDate;


startDate   years   months  daysresultDate
--  --  --  --  --
2004-02-02  0   0   0   2004-02-02
2004-02-02  14  6   1   2018-08-03
1983-07-11  1   1   20  1984-08-31
1984-11-01  1   2   14  1986-01-15
1986-01-16  13  6   0   1999-07-16
1970-01-01  48  7   5   2018-08-06


Leap year

startDate   years   months  daysresultDate
--  --  --  --  --
2016-02-28  1   0   1   2017-03-01
2015-02-28  1   0   2   2016-03-01


Around the end of a month

startDate   years   months  daysresultDate
--  --  --  --  --
2018-01-15  0   0   30  2018-02-14
2018-02-15  0   0   27  2018-03-14
2016-02-15  0   0   28  2016-03-14
2018-04-15  0   0   29  2018-05-14


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Csányi Pál
Sent: Monday, August 06, 2018 10:55 AM
To: SQLite mailing list
Subject: Re: [sqlite] Using CTE with date comparison

On Sun, Aug 05, 2018 at 01:12:17PM -0600, Keith Medcalf wrote:
> 
> >Some where in the WITH clause above I want to put '+1 day' in the
> >command out there.
>   
> That is because the query does not count the StartDate but does count the 
> EndDate, so if your EndDate is the next day from the StartDate you get 1 day, 
> not two.  You need to move the fencepost created by the StartDate to 
> counteract this (so that, effectively, the starting point becomes the day 
> before the first day, or "day 0") ... This makes the dateY / dateM reflect 
> the date on which, at the completion of that day, the year or month 
> respectively was completed:
> 
> WITH RECURSIVE
>  dates (StartDate, EndDate) as
>(
> select '2004-02-02', '2004-02-02'
>),
>  yearC (dateY) AS
>(
> SELECT date(StartDate, '+1 year', '-1 day')
>   FROM dates
>  WHERE date(StartDate, '+1 year', '-1 day') <= EndDate
>  UNION ALL
> SELECT date(dateY, '+1 year')
>   FROM yearC, dates
>  WHERE date(dateY, '+1 year') <= EndDate
>),
>  years (dateY, years) as
>(
> SELECT coalesce((SELECT max(dateY)
>FROM yearC), date(StartDate, '-1 day')),
>coalesce((SELECT count(*)
>FROM yearC), 0)
>   FROM dates
>),
>  monthC (dateM) as
>(
> SELECT date(dateY, '+1 month')
>   FROM years, dates
>  WHERE date(dateY, '+1 month') <= EndDate
>  UNION ALL
> SELECT date(dateM, '+1 month')
>   FROM monthC, dates
>  WHERE date(dateM, '+1 month') <= EndDate
>),
>  months (dateM, months) as
>

[sqlite] Ajqvue Version 2.0 Released

[dmp]

The Ajqvue project is pleased to release v2.0 to the public. The release
is a major maintenance, cleanup, and consolidation of the code base. In
part most of the work was driven by a DB_to_FileMemoryDB plugin and insuring
a more robust handling of SQLite affinity. The sqlite-jdbc library has again
been included with the application. All libraries have been updated. The
plugin DataCharts included with Ajqvue has been updated with JFreeChart,
1.5.0.

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com
https://github.com/danap/ajqvue


Ajqvue provides an easy to use Java based user interface front-end
for viewing, adding, editing, or deleting entries in several mainstream
databases. A query frame allows the building of complex SQL statements
and a SQL Query Bucket for saving such. The application allows easy
sorting, searching, and import/export of table data. A plug-in framework
has allowed the inclusion of tools to visually build queries, profile and
plot data for analysis.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Default Values Pragma bug

[E.Pasma]

Hello Ryan,

Your already moderate complaint needs further moderation
After reading  https://www.sqlite.org/lang_createtable.html#dfltval 
 I see that the default 
value may be a function name (when written inside parenthesis) or a special 
name like current_date. 
So it is not just a constant and needs parsing anyway.
This may make it better acceptable as it is, with comments included in the 
output of pragma table_info().

E. Pasma

PS there is a small typing mistake in above document:
..constant if it does contains
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[R Smith]

On 2018/08/06 4:49 PM, Hick Gunter wrote:

Good luck with quoted speech that contains more than one sentence. E.g.

William Faulkner said, “Never be afraid to raise your voice for honesty and 
truth and compassion against injustice and lying and greed. If people all over 
the world...would do this, it would change the earth.”


Very true, and even if you parse Quoted text as individual sections, it 
can get weird since sometimes a Quote can contain partial sentences, 
full sentences, full paragraph and even whole multi-paragraph pages. Do 
we regard those as one sentence? or one "line" or do you break it into 
lines on a second-level.
All these are possible via varying levels of difficulty, but they are 
all questions to be answered by the source format, the use case and the 
application designer.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using CTE with date comparison

[Csányi Pál]

On Sun, Aug 05, 2018 at 01:12:17PM -0600, Keith Medcalf wrote:
> 
> >Some where in the WITH clause above I want to put '+1 day' in the
> >command out there.
>   
> That is because the query does not count the StartDate but does count the 
> EndDate, so if your EndDate is the next day from the StartDate you get 1 day, 
> not two.  You need to move the fencepost created by the StartDate to 
> counteract this (so that, effectively, the starting point becomes the day 
> before the first day, or "day 0") ... This makes the dateY / dateM reflect 
> the date on which, at the completion of that day, the year or month 
> respectively was completed:
> 
> WITH RECURSIVE
>  dates (StartDate, EndDate) as
>(
> select '2004-02-02', '2004-02-02'
>),
>  yearC (dateY) AS
>(
> SELECT date(StartDate, '+1 year', '-1 day')
>   FROM dates
>  WHERE date(StartDate, '+1 year', '-1 day') <= EndDate
>  UNION ALL
> SELECT date(dateY, '+1 year')
>   FROM yearC, dates
>  WHERE date(dateY, '+1 year') <= EndDate
>),
>  years (dateY, years) as
>(
> SELECT coalesce((SELECT max(dateY)
>FROM yearC), date(StartDate, '-1 day')),
>coalesce((SELECT count(*)
>FROM yearC), 0)
>   FROM dates
>),
>  monthC (dateM) as
>(
> SELECT date(dateY, '+1 month')
>   FROM years, dates
>  WHERE date(dateY, '+1 month') <= EndDate
>  UNION ALL
> SELECT date(dateM, '+1 month')
>   FROM monthC, dates
>  WHERE date(dateM, '+1 month') <= EndDate
>),
>  months (dateM, months) as
>(
> SELECT coalesce((SELECT max(dateM)
>FROM monthC), dateY),
>coalesce((SELECT count(*)
>FROM monthC), 0)
>   FROM years
>),
>  dayC (dateD) as
>(
> SELECT date(dateM, '+1 day')
>   FROM months, dates
>  WHERE date(dateM, '+1 day') <= EndDate
>  UNION ALL
> SELECT date(dateD, '+1 day')
>   FROM dayC, dates
>  WHERE date(dateD, '+1 day') <= EndDate
>),
>  days (dateD, days) as
>(
> SELECT coalesce((SELECT max(dateD)
>FROM dayC), DateM),
>coalesce((SELECT count(*)
>FROM dayC), 0)
>   FROM months
>)
> SELECT StartDate,
>DateY,
>DateM,
>DateD,
>EndDate,
>years,
>months,
>days
>   FROM dates, years, months, days;
> 
> StartDate   dateY   dateM   dateD   EndDate years   
> months  days
> --  --  --  --  --  --  
> --  --
> 2004-02-02  2018-02-01  2018-08-01  2018-08-03  2018-08-03  14  6 
>   2

The output of code above is:
WITH RECURSIVE dates (StartDate, EndDate) as ( select '2004-02-02', 
'2004-02-02' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year', '-1 day') 
FROM dates WHERE date(StartDate, '+1 year', '-1 day') <= EndDate UNION ALL 
SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= 
EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT max(dateY) FROM 
yearC), date(StartDate, '-1 day')), coalesce((SELECT count(*) FROM yearC), 0) 
FROM dates ), monthC (dateM) as  ( SELECT date(dateY, '+1 month') FROM years, 
dates WHERE date(dateY, '+1 month') <= EndDate UNION ALL SELECT date(dateM, '+1 
month') FROM monthC, dates WHERE date(dateM, '+1 month') <= EndDate ), months 
(dateM, months) as ( SELECT coalesce((SELECT max(dateM) FROM monthC), dateY), 
coalesce((SELECT count(*) FROM monthC), 0) FROM years ), dayC (dateD) as ( 
SELECT date(dateM, '+1 day') FROM months, dates WHERE date(dateM, '+1 day') <= 
EndDate UNION ALL SELECT date(dateD, '+1 day') FROM dayC, dates WHERE 
date(dateD, '+1 day') <= EndDate ), days (dateD, days) as ( SELECT 
coalesce((SELECT max(dateD) FROM dayC), DateM), coalesce((SELECT count(*) FROM 
dayC), 0) FROM months ) SELECT StartDate, DateY, DateM, DateD, EndDate, years, 
months, days FROM dates, years, months, days;

StartDate   dateY   dateM   dateD   EndDate years   months  
days  
--  --  --  --  --  --  
--  --
2004-02-02  2004-02-01  2004-02-01  2004-02-02  2004-02-02  0   0   
1

which is wrong because the StarDate and EndDate are the same:
'2004-02-02'. The output should be zero '0' in this case.

I tried this SQL command for three cases. The output of two cases are
the expected, but one is not. See bellow.

1983-07-11' - '1984-08-31'
^^
sqlite> WITH RECURSIVE dates (StartDate, EndDate) as ( select '1983-07-11', 
'1984-08-31' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year', '-1 day') 
FROM dates WHERE date(StartDate, '+1 year', '-1 day') <= EndDate UNION ALL 
SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= 
EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT 

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[Hick Gunter]

Good luck with quoted speech that contains more than one sentence. E.g.

William Faulkner said, “Never be afraid to raise your voice for honesty and 
truth and compassion against injustice and lying and greed. If people all over 
the world...would do this, it would change the earth.”

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von R Smith
Gesendet: Montag, 06. August 2018 16:20
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole 
file?

On 2018/08/06 12:00 PM, R Smith wrote:
>
>> I need to save text files (let say between 1 KB to 20 MB) in a SQLite
>> DB.
>>
> Why not do both?
>
> If it was me, I would write some code to split the text into sentences
> (not lines - which is rather easy in English, but might be harder in
> some other languages).
//...

I've received two off-line questions as to how I could parse text into 
sentences in "English" even, and thought I would reply here since it might 
clear up the confusion for others too.

The said questions indicated that the authors probably imagined me possessing 
some fancy AI comprehending the language into what constitutes notional 
sentences (Subject+Predicate) or such, but I fear the meaning was much more 
arbitrary, based on common syntax for written English - as William Faulkner 
wrote in a letter to Malcolm Cowley:

*"I am trying to say it all in one sentence, between one Cap and one
period."*


Think of paragraphs in English as large records delimited by 2 or more
Line-break characters (#10+#13 or perhaps only #10 if on a *nix
platform) between texts.

Each paragraph record could be comprised of one or more sentences (in
English) as records delimited by a full-stop+Space or
full-stop+linebreak, or even simply the paragraph end.

By these simple rules, the following can easily parsed into 1 paragraph
with 2 sentences and a second paragraph with 1 sentence (lines here used
as formatting only, actual line-breaks indicated with "<-" marker):
<-
The quick brown fox jumps over the
lazy dog.  My grandma said to your
grandma, I'm gonna set your flag
on fire.<-
<-
Next paragraph here...<-
<-

Now a more difficult paragraph would be a the following, all of which
would translate in to 1 single sentence if only the above rules are
catered for:
<-
I have three wishes:<-
   - to be outlived by my children<-
   - to fly in space once before I die<-
   - to see Halley's comet once more<-
<-

That will be a single-sentenced paragraph.  It's up to the
end-implementation to gauge whether that would be sufficient a split or
not.

To put this into a DB, I would strip out the line-breaks inside
sentences (perhaps not strip out, but replace with space characters,
much like HTML does) to make them more easily handled as "lines". The
final DB table might then look like this:

ID |  fileID | parNo | parLineNo | docLineNo | txtLine
  1 | 1   |   1   | 1 | 1 | The quick brown fox
jumps over the lazy dog.
  2 | 1   |   1   | 2 | 2 | My grandma said to your
grandma, I'm gonna set your flag on fire.
  3 | 1   |   2   | 1 | 3 | Next paragraph here...
  4 | 1   |   3   | 1 | 4 | I have three wishes: -
to be outlived by my children - to fly in space once before I die - to
see Halley's comet once more

So yes, not a perfect walk-in-the-park, but easy to do for basic text
parsing.
Stating the obvious: If the intent is to re-construct the file 100%
exact (so it scores the same output for a hashing algorithm) then you
cannot strip out line-breaks and you need to carefully include each and
every character byte-for-byte used to split paragraphs and the like. It
all depends on the implementation requirements.

The above text format should hold for 99.9% of English literature text
that can be had in text files (i.e. no images, tables, etc.). Not so
easy for scientific papers, research material, movie scripts and a few
others.

Sorry for not presenting that great AI solution.  :)
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[R Smith]

On 2018/08/06 12:00 PM, R Smith wrote:


I need to save text files (let say between 1 KB to 20 MB) in a SQLite 
DB.



Why not do both?

If it was me, I would write some code to split the text into sentences 
(not lines - which is rather easy in English, but might be harder in 
some other languages).

//...

I've received two off-line questions as to how I could parse text into 
sentences in "English" even, and thought I would reply here since it 
might clear up the confusion for others too.


The said questions indicated that the authors probably imagined me 
possessing some fancy AI comprehending the language into what 
constitutes notional sentences (Subject+Predicate) or such, but I fear 
the meaning was much more arbitrary, based on common syntax for written 
English - as William Faulkner wrote in a letter to Malcolm Cowley:


*"I am trying to say it all in one sentence, between one Cap and one 
period."*



Think of paragraphs in English as large records delimited by 2 or more 
Line-break characters (#10+#13 or perhaps only #10 if on a *nix 
platform) between texts.


Each paragraph record could be comprised of one or more sentences (in 
English) as records delimited by a full-stop+Space or 
full-stop+linebreak, or even simply the paragraph end.


By these simple rules, the following can easily parsed into 1 paragraph 
with 2 sentences and a second paragraph with 1 sentence (lines here used 
as formatting only, actual line-breaks indicated with "<-" marker):

<-
The quick brown fox jumps over the
lazy dog.  My grandma said to your
grandma, I'm gonna set your flag
on fire.<-
<-
Next paragraph here...<-
<-

Now a more difficult paragraph would be a the following, all of which 
would translate in to 1 single sentence if only the above rules are 
catered for:

<-
I have three wishes:<-
  - to be outlived by my children<-
  - to fly in space once before I die<-
  - to see Halley's comet once more<-
<-

That will be a single-sentenced paragraph.  It's up to the 
end-implementation to gauge whether that would be sufficient a split or 
not.


To put this into a DB, I would strip out the line-breaks inside 
sentences (perhaps not strip out, but replace with space characters, 
much like HTML does) to make them more easily handled as "lines". The 
final DB table might then look like this:


ID |  fileID | parNo | parLineNo | docLineNo | txtLine
 1 | 1   |   1   | 1 | 1 | The quick brown fox 
jumps over the lazy dog.
 2 |     1   |   1   |     2     |     2     | My grandma said to your 
grandma, I'm gonna set your flag on fire.

 3 |     1   |   2   |     1     |     3     | Next paragraph here...
 4 |     1   |   3   |     1     |     4     | I have three wishes: - 
to be outlived by my children - to fly in space once before I die - to 
see Halley's comet once more


So yes, not a perfect walk-in-the-park, but easy to do for basic text 
parsing.
Stating the obvious: If the intent is to re-construct the file 100% 
exact (so it scores the same output for a hashing algorithm) then you 
cannot strip out line-breaks and you need to carefully include each and 
every character byte-for-byte used to split paragraphs and the like. It 
all depends on the implementation requirements.


The above text format should hold for 99.9% of English literature text 
that can be had in text files (i.e. no images, tables, etc.). Not so 
easy for scientific papers, research material, movie scripts and a few 
others.


Sorry for not presenting that great AI solution.  :)
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

[R Smith]

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?


Why not do both?

If it was me, I would write some code to split the text into sentences 
(not lines - which is rather easy in English, but might be harder in 
some other languages).


Then I would save the lines in a two-table Database like this:

CREATE TABLE files(
  ID INTEGER PRIMARY KEY, -- This is only to get a handle on the rowid
  fileName TEXT,  -- Add COLLATE NOCASE if the file system is case 
insensitive, like Windows.

  filePath TEXT,
  ...  -- Any other things you want to store about the origin file, or 
perhaps a timestamp etc.

);

CREATE TABLE content(
  ID INTEGER PRIMARY KEY,
  fileID INT NOT NULL REFERENCES files(ID), -- This to know which 
origin file [*].
  lineNo INT NOT NULL,  -- So that a line can be referenced by position 
into a file.

  txtLine TEXT,
  ...
);

Now you can easily query all lines with a specific fileID to see the 
entire document, or JOIN by fileName even to list the content of any 
file by name, or simply refer to any specific line in any file by either 
its fileID+lineNo or simply its own ID.  I would probably go further 
(since I'm code-parsing the file anyway) and include a paragraph number 
or even chapter + page numbers if that's relevant.


This approach will work whether you split lines into sentences or just 
physical positional lines, though a sentence-split makes more sense to 
me (unless it's a data format).


[*] - I'm only showing basic options, but you'd typically want to add 
some ON UPDATE CASCADE or ON DELETE referencing methods.



Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users