[sqlite] How many digits do you need ?

[Simon Slavin]

Please allow me a little levity, spinning off an earlier discussion of how many 
digits a decimal number type needs to store.



" Carbrook, for instance, is at -27.673862 153.25624 and at -27.673861999297635 
153.25624388146.

[...] those 15-place figures locate the suburb's latitude to the nearest tenth 
of a nanometre, about half the diameter of a chlorine atom. "

First, spot the '999' and '' suggesting a problem.  Second wonder whether 
anyone read the data.

Apart from that, the article is complimentary about the format used for making 
a lot of data easily searchable.  So it's a nice example to use when talking 
about care with data preparation.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Joshua Wise]

Oh of course, that makes sense. I suppose that means querying on REAL indexes 
should be slower than querying on INTEGER indexes, in the current SQLite3 
implementation? Has a benchmark of this ever been done?


> On Apr 3, 2019, at 5:29 PM, Richard Hipp  wrote:
> 
> On 4/3/19, Joshua Wise  wrote:
>> From my naive understanding, memcmp() is used to efficiently compare long
>> strings of bytes. But where in SQLite3 is it necessary to compare long
>> strings of floating point numbers? I, of course, can imagine SQL queries
>> plucking single floating point values from rows or indexes, but I can’t
>> imagine where the long strings would be. Could you enlighten me?
> 
> Comparing keys in a btree search uses a lot of CPU cycles.  If the
> comparison can be done using memcmp() rather than some custom
> function, the comparison goes much faster, which makes searching
> btrees faster.
> 
> -- 
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Richard Hipp]

On 4/3/19, Joshua Wise  wrote:
> From my naive understanding, memcmp() is used to efficiently compare long
> strings of bytes. But where in SQLite3 is it necessary to compare long
> strings of floating point numbers? I, of course, can imagine SQL queries
> plucking single floating point values from rows or indexes, but I can’t
> imagine where the long strings would be. Could you enlighten me?

Comparing keys in a btree search uses a lot of CPU cycles.  If the
comparison can be done using memcmp() rather than some custom
function, the comparison goes much faster, which makes searching
btrees faster.

-- 
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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Joshua Wise]

From my naive understanding, memcmp() is used to efficiently compare long 
strings of bytes. But where in SQLite3 is it necessary to compare long strings 
of floating point numbers? I, of course, can imagine SQL queries plucking 
single floating point values from rows or indexes, but I can’t imagine where 
the long strings would be. Could you enlighten me?


> On Apr 3, 2019, at 3:23 PM, Lifepillar  wrote:
> 
> 
> 
>> On 3 Apr 2019, at 20:04, Joshua Thomas Wise  
>> wrote:
>> 
>>> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
>>> severals moons ago, who convinced me that the IEEE 754 encoding was not
>>> an ideal storage format for databases]
>> 
>> I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?
> 
> At the time of SQLite4, I wrote to him asking why he had dismissed IEEE
> 754 as a storage format in favor of a custom encoding. His answer was
> that he wanted comparisons to be performed using memcmp(), which IEEE
> 754 does not allow. 
> 
> There may have been other reasons (complexity, range, ...), but that one
> stuck with me and prompted me to start searching for order-preserving
> encodings.
> 
> Life.
> ___
> 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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Lifepillar]

On 3 Apr 2019, at 19:37, Warren Young  wrote:
> 
> On Apr 3, 2019, at 6:30 AM, Lifepillar  wrote:
>> 
>> By default, the precision is limited to 39 digits and exponents must be
>> in the range [-99,999,999,+99,999,999] (for some mathematical
>> operations, the exponent must not exceed 99,999 in absolute value).
> 
> That’s enough. :)

Should you need more, the storage format would not need to be changed!

>> Also, this is my first public Fossil repository: if you have any
>> suggestions on how I should improve its configuration, let me know.
> 
> The CRLF line endings in the decNumber directory can cause problems if you 
> ever cause a merge conflict.
> 
> Fossil has no problems doing merges on arbitrary data, but when it writes out 
> the conflict-resolution files to help the user manually fix a merge conflict, 
> it does so with the assumption that you’re using LF-only line endings, 
> causing stray CRs in the output.

Thanks, I did not know that.

> Unless you really need CRLF for some reason, it’s best to convert those line 
> endings.  That’ll also avoid the need to set the Fossil crlf-glob setting.

So far I have left line endings as I have found them, but I will
proceed to convert them following your advice.


>> Git mirror (which exists only for testing `fossil git export`...):
>> 
>>   https://github.com/lifepillar/sqlite3decimal-mirror
> 
> I wouldn’t say “only.”  If nothing else, it’s advertising for your project, 
> and it’ll make it more likely to show up in web searches.

Actually, I aimed at targeted advertising in this mailing list :) There
was some discussion about big numbers in the past, so I thought my
project might be interesting for some people here.

And perhaps I should have added that I welcome feedback at Chisel
(tickets for now: I haven't configured the permissions for the forum
yet), if you wish to discuss the project in more detail.

Life.


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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Lifepillar]

> On 3 Apr 2019, at 20:04, Joshua Thomas Wise  
> wrote:
> 
>> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
>> severals moons ago, who convinced me that the IEEE 754 encoding was not
>> an ideal storage format for databases]
> 
> I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?

At the time of SQLite4, I wrote to him asking why he had dismissed IEEE
754 as a storage format in favor of a custom encoding. His answer was
that he wanted comparisons to be performed using memcmp(), which IEEE
754 does not allow. 

There may have been other reasons (complexity, range, ...), but that one
stuck with me and prompted me to start searching for order-preserving
encodings.

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

Re: [sqlite] Help with INDEXing a query

[Jose Isaias Cabrera]

Thanks.  I didn't know this.


From: Luuk 
Sent: Wednesday, April 3, 2019 02:34 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Help with INDEXing a query


On 3-4-2019 19:34, Jose Isaias Cabrera wrote:
> Never mind, guys.  I was missing the INDEX for the table for the first left 
> join:
>
> CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);
>
> Everything is nice, now.  Thanks.
>
>
> From: Jose Isaias Cabrera
> Sent: Wednesday, April 3, 2019 01:02 PM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Help with INDEXing a query
>
>
> Greetings!
>
> I am trying to speed up this query,
>
.

sqlite> .expert
sqlite> select * from testing where a=42;
CREATE INDEX testing_idx_0061 ON testing(a);

SEARCH TABLE testing USING INDEX testing_idx_0061 (a=?)

sqlite>


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

Re: [sqlite] Help with INDEXing a query

[Luuk]

On 3-4-2019 19:34, Jose Isaias Cabrera wrote:

Never mind, guys.  I was missing the INDEX for the table for the first left 
join:

CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);

Everything is nice, now.  Thanks.


From: Jose Isaias Cabrera
Sent: Wednesday, April 3, 2019 01:02 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Help with INDEXing a query


Greetings!

I am trying to speed up this query,


.

sqlite> .expert
sqlite> select * from testing where a=42;
CREATE INDEX testing_idx_0061 ON testing(a);

SEARCH TABLE testing USING INDEX testing_idx_0061 (a=?)

sqlite>

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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Joshua Thomas Wise]

> [Here, I must thank Dr. Hipp, with whom I had a brief email exchange
> severals moons ago, who convinced me that the IEEE 754 encoding was not
> an ideal storage format for databases]


I’m curious, what were the reasons behind Dr. Hipp’s opinion on this?

> On Apr 3, 2019, at 1:56 PM, Simon Slavin  wrote:
> 
> On 3 Apr 2019, at 6:51pm, Warren Young  wrote:
> 
>> On Apr 3, 2019, at 6:30 AM, Lifepillar  wrote:
>> 
>>> does SQLite support indexes on blobs?
>> 
>> It claims to:
> 
> Indeed.  Be careful to verify whether, from the perspective of your 
> programming language, it considers the first or the last byte to be most 
> significant.  I've seen people caught out by a similar issue.
> ___
> 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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Simon Slavin]

On 3 Apr 2019, at 6:51pm, Warren Young  wrote:

> On Apr 3, 2019, at 6:30 AM, Lifepillar  wrote:
> 
>> does SQLite support indexes on blobs?
> 
> It claims to:

Indeed.  Be careful to verify whether, from the perspective of your programming 
language, it considers the first or the last byte to be most significant.  I've 
seen people caught out by a similar issue.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Warren Young]

On Apr 3, 2019, at 6:30 AM, Lifepillar  wrote:
> 
> does SQLite support indexes on blobs?

It claims to:

$ sqlite3 x.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> create table x (a blob);
sqlite> create index xi on x(a);
sqlite> explain query plan select a from x where a=5;
QUERY PLAN
`--SEARCH TABLE x USING COVERING INDEX xi (a=?)
sqlite> 

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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Simon Slavin]

On 3 Apr 2019, at 6:37pm, Warren Young  wrote:

> I once calculated that 30 digits was enough to give a precise location to 
> every particle the size of a grain of sand or larger in the known universe.  
> You just need to define as many columns in your database as is needed for 
> your physics: 3 for normal 3-space, 4 for spacetime, 10, 11, or 26 for string 
> theory…

The first 39 digits of pi allow you to do maths on the size of the universe in 
units the width of a hydrogen atom.  Let's add one extra digit to deal with 
rounding errors.  Pi may have more than 40 digits, but the others have no 
practical use in this universe.

(Yes, you might consider them to be theoretically useful for things like number 
theory or testing computer components which do certain kinds of maths.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Warren Young]

On Apr 3, 2019, at 7:05 AM, Dominique Devienne  wrote:
> 
> Seems like you're using non-ascii chars in your source code, notably
> the #pragma mark.

I think you’ve got two nits there, not one.

As for the non-ASCII characters, they’re UTF-8, which is the de facto standard 
character set on the Internet since around the time of The Bubble.  Ignoring 
the embedded world, I can’t think of an in-support OS that doesn’t have 
built-in support for UTF-8.  The only place I’d caution against using such 
characters is in printf() output and such, and then only because the Windows 
Console defaults to UTF-16LE.

As for the nonstandard #pragmas, ISO C99 has you covered there.  Thus saith 
§6.10.6 of #pragmas not of the STDC form: ”Any such pragma that is not 
recognized by the implementation is ignored.”  In other words, if a compiler 
that doesn’t understand #pragma mark complains about it or refuses to compile 
code using it, it’s non-conforming.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Warren Young]

On Apr 3, 2019, at 6:30 AM, Lifepillar  wrote:
> 
> By default, the precision is limited to 39 digits and exponents must be
> in the range [-99,999,999,+99,999,999] (for some mathematical
> operations, the exponent must not exceed 99,999 in absolute value).

That’s enough. :)

I once calculated that 30 digits was enough to give a precise location to every 
particle the size of a grain of sand or larger in the known universe.  You just 
need to define as many columns in your database as is needed for your physics: 
3 for normal 3-space, 4 for spacetime, 10, 11, or 26 for string theory…

9 extra digits gets you down to the “large molecule” scale.

5+ digits worth of negative exponents is enough to get down to the sub-atomic 
scale.

Put another way, your defaults are already so large that no conceivable 
physical entity could build a computer big enough to simultaneously contain 
every distinct state your data type represents.  It’ll do. :)

I did the original calculation because someone was complaining that MySQL’s 
default — at the time — of 30 digits to the left of the decimal point wasn’t 
enough for their application.  MySQL’s decimal type then allows another 30 
digits to the *right* of the decimal point.  The original poster in that thread 
remained silent when challenged to divulge what application required more 
precision than that.

(The current MySQL limit default for the DECIMAL type is 65 total digits, with 
a configurable number of digits the right of the decimal, up to 30.)

> Also, this is my first public Fossil repository: if you have any
> suggestions on how I should improve its configuration, let me know.

The CRLF line endings in the decNumber directory can cause problems if you ever 
cause a merge conflict.

Fossil has no problems doing merges on arbitrary data, but when it writes out 
the conflict-resolution files to help the user manually fix a merge conflict, 
it does so with the assumption that you’re using LF-only line endings, causing 
stray CRs in the output.

Unless you really need CRLF for some reason, it’s best to convert those line 
endings.  That’ll also avoid the need to set the Fossil crlf-glob setting.

> Git mirror (which exists only for testing `fossil git export`...):
> 
>https://github.com/lifepillar/sqlite3decimal-mirror

I wouldn’t say “only.”  If nothing else, it’s advertising for your project, and 
it’ll make it more likely to show up in web searches.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help with INDEXing a query

[Jose Isaias Cabrera]

Never mind, guys.  I was missing the INDEX for the table for the first left 
join:

CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);

Everything is nice, now.  Thanks.


From: Jose Isaias Cabrera
Sent: Wednesday, April 3, 2019 01:02 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Help with INDEXing a query


Greetings!

I am trying to speed up this query,

SELECT a.*,b.*,c.Area,d.Bus_Area FROM Project_List AS a
LEFT JOIN Project_List_Extra AS b ON a.ProjID = b.ProjID
LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS
LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS
WHERE a.ProjID IN
(
  SELECT a.ProjID FROM Project_List WHERE
a.Progress != 'Completed'
AND
a.PMO_Board_Report != 'No'
AND
(
  (a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR
  (a.Target_Go_Live_Date = '' AND a.Finish_Date >  
substr(date('now'),1,4) || '-01-15')
)
AND
a.InsertDate =
(SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
  )
  ORDER BY a.ProjID;";

By creating an INDEX for it.  I have created these INDEXes:
CREATE INDEX PL_ProjID ON Project_List (ProjID);
CREATE INDEX ProjID_InsertDate ON Project_List (ProjID, InsertDate);
CREATE INDEX Manager ON Project_List (Manager);
CREATE INDEX ProjID_Progress_PMOBR_TGLD_FD_IDate ON Project_List (ProjID, 
Progress, PMO_Board_Report, Target_Go_Live_Date, Finish_Date, InsertDate);

I thought this last one would fix it, but it still takes a good 14.88 seconds. 
The above query results in 128 records, out of 128 * 10. Any help would be 
greatly appreciated. Thanks.

josé
___
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

[sqlite] Help with INDEXing a query

[Jose Isaias Cabrera]

Greetings!

I am trying to speed up this query,

SELECT a.*,b.*,c.Area,d.Bus_Area FROM Project_List AS a
LEFT JOIN Project_List_Extra AS b ON a.ProjID = b.ProjID
LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS
LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS
WHERE a.ProjID IN
(
  SELECT a.ProjID FROM Project_List WHERE
a.Progress != 'Completed'
AND
a.PMO_Board_Report != 'No'
AND
(
  (a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR
  (a.Target_Go_Live_Date = '' AND a.Finish_Date >  
substr(date('now'),1,4) || '-01-15')
)
AND
a.InsertDate =
(SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
  )
  ORDER BY a.ProjID;";

By creating an INDEX for it.  I have created these INDEXes:
CREATE INDEX PL_ProjID ON Project_List (ProjID);
CREATE INDEX ProjID_InsertDate ON Project_List (ProjID, InsertDate);
CREATE INDEX Manager ON Project_List (Manager);
CREATE INDEX ProjID_Progress_PMOBR_TGLD_FD_IDate ON Project_List (ProjID, 
Progress, PMO_Board_Report, Target_Go_Live_Date, Finish_Date, InsertDate);

I thought this last one would fix it, but it still takes a good 14.88 seconds. 
The above query results in 128 records, out of 128 * 10. Any help would be 
greatly appreciated. Thanks.

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

Re: [sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

[Jonathan Brandmeyer]

On Wed, Apr 3, 2019 at 8:55 AM Richard Hipp  wrote:
>
> On 4/3/19, Jonathan Brandmeyer  wrote:
> > What is the upper bound for stack consumption under the
> > SQLITE_USE_ALLOCA compile-time option?  I see that there are a number
> > of configurable size limits available as compile-time and/or run-time
> > options.  Which ones affect the maximum alloca?
> >
>
> I think the maximum alloca() allocation will be 7x the page size for
> the database file.  So a little less than 0.5 MB assuming a maximum
> page size of 64K.

Thanks!

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

Re: [sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

[Dominique Pellé]

Jonathan Brandmeyer  wrote:

> What is the upper bound for stack consumption under the
> SQLITE_USE_ALLOCA compile-time option?  I see that there are a number
> of configurable size limits available as compile-time and/or run-time
> options.  Which ones affect the maximum alloca?
>
> Thanks,
> Jonathan Brandmeyer

I recall using this tool to measure stack usage on Linux:

  https://github.com/d99kris/stackusage

It gives you the stack high watermarks for each thread,
with no noticeable runtime overhead.

Of course, there is no guarantee that you hit the theoritical
worse case for stack usage while using the tool, but at least you
get at least the typical stack usage.

You could try measuring with and without -DSQLITE_USE_ALLOCA.

Other relevant defines that may influence stack usage:

SQLITE_SPELLFIX_STACKALLOC_SZ
SQLITE_SMALL_STACK

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

Re: [sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

[Richard Hipp]

On 4/3/19, Jonathan Brandmeyer  wrote:
> What is the upper bound for stack consumption under the
> SQLITE_USE_ALLOCA compile-time option?  I see that there are a number
> of configurable size limits available as compile-time and/or run-time
> options.  Which ones affect the maximum alloca?
>

I think the maximum alloca() allocation will be 7x the page size for
the database file.  So a little less than 0.5 MB assuming a maximum
page size of 64K.

-- 
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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Lifepillar]

> > SQLite3 Decimal is an extension implementing exact decimal arithmetic 
> > for SQLite3. It is currently unfinished and under development. 
> 
> 
> I'm curious, what was your motivation for doing this? 
> Use cases envisioned for its use? 

Mainly financial applications. Beancount’s author
(http://furius.ca/beancount/) has a series of documents arguing convincingly
about the need for a library like this.

> > Decimals are stored as blobs and the storage format is a minor variant 
> > of decimalInfinite (https://arxiv.org/abs/1506.01598). 
> 
> Does it use some kind of magic cookie? 

No.

> How can you distinguish your encoding from an arbitrary blob? 

I don't. In fact, you may pass any blob, but if it doesn’t have the right
format, it results in a decoding error (which, currently, may not be very
well tolerated by the library-I have to check that).

> Unfortunately, SQLite does not support UDTs, but it did grow in recent 
> months 
> the https://www.sqlite.org/c3ref/value_subtype.html API, which plays a 
> poor-man's 
> substitute when call chaining. 

Thanks, I didn’t know about subtypes. I’ll read about them, although, at a
quick glance, I am not sure how they might be used to enforce type
checking in my case.

> That OTOH has no influence on how the value will be *stored in tables*, 
> unfortunately, so you cannot have alternate representation of the value, 
> one optimized for storage, another for processing (in call chains), so maybe 
> it's all moot, mentioning this. 
> 
> That is a totally 
> > ordered encoding, so decimals can be compared directly (memcmp()) and 
> > also indexed (does SQLite support indexes on blobs?). 
> > 
> 
> Yes it does. 

Great!

> > As I have said, I welcome any feedback, from the super-technical to the 
> > end-user oriented. There is no manual so far, but the code is mostly 
> > documented. You may find a sample session in the repository's home page. 
> > 
> 
> From an End-User POV, the call-chained-functions are "heavy looking”.

Yes, I agree. Keep in mind that the current interface is most likely bound
to be changed.

> So maybe a printf-like API that parses a now-looking math expression 
> and replaces its placeholders with the var-arg supplied arguments to the 
> function 
> would make the "SQL" much more readable. 
> 
> Since you cannot extend the regular arithmetic expressions in SQLite to act 
> on your UDTs 
> differently than the built-in processing, that would be the next best thing 
> IMHO. 

I will think about that!

> All in all, it looks very well done. You're obviously a very competent 
> programmer IMHO :). 

It looks so, because I stand on the shoulder of giants ;)

> Thanks, --DD 
> 
> PS: Seems like you're using non-ascii chars in your source code, notably 
> the #pragma mark. 
>   That's pretty, but asking for trouble IMHO, and likely to cause issues 
> for cross-platform support. 

Yes, that and a few other things need to be polished. My initial
idea was to keep this project for myself, so I took a liberal approach
style-wise. But it has grown more than I expected...

Thanks for the feedback,
Life.

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

[sqlite] Stack allocation upper bound with SQLITE_USE_ALLOCA

[Jonathan Brandmeyer]

What is the upper bound for stack consumption under the
SQLITE_USE_ALLOCA compile-time option?  I see that there are a number
of configurable size limits available as compile-time and/or run-time
options.  Which ones affect the maximum alloca?

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

Re: [sqlite] Why no persistent user settings in a database file?

[Tom Browder]

On Wed, Apr 3, 2019 at 06:57 R Smith  wrote:
> Hi Tom,
...
> About the CLI - It's a very useful piece of toolkit, but it's intent is
...
> For more usability, there are a few good options in the World from CLI's
> to GUI's. I see you already know SQLite studio, some of my favourites
> you might try are:
...

Thanks, Ryan, for a good set of references!

Best regards,

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

Re: [sqlite] ANN: SQLite3 Decimal Extension

[Dominique Devienne]

On Wed, Apr 3, 2019 at 2:31 PM Lifepillar  wrote:

> [I hope that this kind of announcement is not off-topic here]
>

Not at all, IMHO.


> SQLite3 Decimal is an extension implementing exact decimal arithmetic
> for SQLite3. It is currently unfinished and under development.


I'm curious, what was your motivation for doing this?
Use cases envisioned for its use?


> Decimals are stored as blobs and the storage format is a minor variant
> of decimalInfinite (https://arxiv.org/abs/1506.01598).


Does it use some kind of magic cookie?
How can you distinguish your encoding from an arbitrary blob?

Unfortunately, SQLite does not support UDTs, but it did grow in recent
months
the https://www.sqlite.org/c3ref/value_subtype.html API, which plays a
poor-man's
substitute when call chaining.

That OTOH has no influence on how the value will be *stored in tables*,
unfortunately, so you cannot have alternate representation of the value,
one optimized for storage, another for processing (in call chains), so maybe
it's all moot, mentioning this.

That is a totally
> ordered encoding, so decimals can be compared directly (memcmp()) and
> also indexed (does SQLite support indexes on blobs?).
>

Yes it does.


> As I have said, I welcome any feedback, from the super-technical to the
> end-user oriented. There is no manual so far, but the code is mostly
> documented. You may find a sample session in the repository's home page.
>

From an End-User POV, the call-chained-functions are "heavy looking".
So maybe a printf-like API that parses a now-looking math expression
and replaces its placeholders with the var-arg supplied arguments to the
function
would make the "SQL" much more readable.

Since you cannot extend the regular arithmetic expressions in SQLite to act
on your UDTs
differently than the built-in processing, that would be the next best thing
IMHO.

All in all, it looks very well done. You're obviously a very competent
programmer IMHO :).

Thanks, --DD

PS: Seems like you're using non-ascii chars in your source code, notably
the #pragma mark.
  That's pretty, but asking for trouble IMHO, and likely to cause issues
for cross-platform support.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why no persistent user settings in a database file?

[Tom Browder]

On Wed, Apr 3, 2019 at 05:52 Tom Browder  wrote:

> After coming back to SQLite from a long absence, I was surprised that
> setting things like .mode and .headers in a database didn't stay that way
> after exiting the file.
>
...

Okay, I agree with all the excellent arguments about NOT keeping user
settings in the db file.

However, why shouldn't sqliterc be documented on the SQLite website since
the sqlite3 CLI is part of the whole suite?

Simply adding the contents of the current man page would suffice for that.

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

[sqlite] ANN: SQLite3 Decimal Extension

[Lifepillar]

[I hope that this kind of announcement is not off-topic here]

SQLite3 Decimal is an extension implementing exact decimal arithmetic
for SQLite3. It is currently unfinished and under development.  At this
point anything, including the user interface and the internals, is
subject to change. I am publishing it early because I am seeking to get
as much feedback as possible to "get it right".

The extension is currently using the excellent decNumber library, but it
does not aim to become an IEEE 754 conforming implementation. It is also
totally unrelated to SQL decimal/numeric types.

Decimals are stored as blobs and the storage format is a minor variant
of decimalInfinite (https://arxiv.org/abs/1506.01598). That is a totally
ordered encoding, so decimals can be compared directly (memcmp()) and
also indexed (does SQLite support indexes on blobs?).

[Here, I must thank Dr. Hipp, with whom I had a brief email exchange
severals moons ago, who convinced me that the IEEE 754 encoding was not
an ideal storage format for databases]

By default, the precision is limited to 39 digits and exponents must be
in the range [-99,999,999,+99,999,999] (for some mathematical
operations, the exponent must not exceed 99,999 in absolute value). Such
parameters may be configured at compile time and also changed at
runtime. Any integer or fractional number satisfying such requirements
can be manipulated. Note that the on-disk representation is *not*
subject to such limits and can accommodate arbitrarily small or
arbitrarily large decimals. With the defaults just mentioned, a decimal
occupies between 1 and 24 bytes on disk (plus any overhead that blobs
may add).

As I have said, I welcome any feedback, from the super-technical to the
end-user oriented. There is no manual so far, but the code is mostly
documented. You may find a sample session in the repository's home page.

Also, this is my first public Fossil repository: if you have any
suggestions on how I should improve its configuration, let me know.

So, here for the adventurous ones:

Official repository:

https://chiselapp.com/user/lifepillar/repository/sqlite3decimal

Git mirror (which exists only for testing `fossil git export`...):

https://github.com/lifepillar/sqlite3decimal-mirror

Enjoy,
Life.


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

Re: [sqlite] Why no persistent user settings in a database file?

[Tim Streater]

On 03 Apr 2019, at 11:52, Tom Browder  wrote:

> After coming back to SQLite from a long absence, I was surprised that
> setting things like .mode and .headers in a database didn't stay that way
> after exiting the file.
>
> Then I remembered something about a resource file and found .sqliterc on an
> internet search and that allowed the persistent settings I wanted.
>
> However, would it not be more natural to keep those settings persistent
> inside the db file?

These settings are nothing to do with the database file. They're settings for 
the sqlite3 CLI program.



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

Re: [sqlite] Why no persistent user settings in a database file?

[R Smith]

Hi Tom,

The settings you mention, such as .headers and .mode (along with a slew 
of others) are usability settings contained in the Command-Line facility 
(the CLI) offered by the makers of SQLite.


It is however by far not the only such interface, nor is it in any way 
related to the data in the database file.  You could use any other CLI 
or GUI for sqlite which all sport their own settings - none of which 
should be in the database file because it is not pertinent to other 
users/uses of the DB file.


That's not to say you can't use and sqlite3 database file to house such 
settings, even pass it around as an application file format  (both of 
which are often the case) but it is up to the calling entity (program) 
to decide the meaning of its data, the database layer itself should 
never be involved with that - it should be fully agnostic to the Data 
and 100% transparent.


About the CLI - It's a very useful piece of toolkit, but it's intent is 
more to be absolutely correct, well tested and provide a way to test 
sqlite queries etc. in a controlled environment uncontaminated by 
another user-program, GUI-layer or such.


For more usability, there are a few good options in the World from CLI's 
to GUI's. I see you already know SQLite studio, some of my favourites 
you might try are:


- DB Browser for SQLite: https://github.com/sqlitebrowser/sqlitebrowser
Powerful and Free SQLite GUI that runs very well on Linux, MacOS, 
Windows and probably wherever else you fancy seeing the inside of an 
SQLite file.


- SQLitespeed: https://sqlitespeed.com/
Made for user-rich and speedy features plus a nice tool to tell you if 
your schema suffers from common mistakes made in SQLite - like 
double-quoted strings, wrongful Type definitions, spelling mistakes etc. 
It's only Windows exe though, but I've been told it runs perfectly fine 
in WINE.


- SQLiteExpert: http://www.sqliteexpert.com/
Great Windows GUI with a visual query builder (which helps a lot if you 
are not an expert yet at forming SQL queries) and the personal version 
is free.


Both of these Windows ones come with the DLL available on the sqlite 
downloads, BUT let's you roll your own and drop-in replace the DLL to 
run in your dev environment with your specific sqlite build.



There are obviously more tools out there, but between these three and 
the CLI we usually get our game on. We also typically use more than one 
tool for their different strengths - which is another reminder why you 
do not wish to have any specific tool's settings (or heaven forbid, all 
of them) in your database file.



Good luck!
Ryan


On 2019/04/03 12:52 PM, Tom Browder wrote:

After coming back to SQLite from a long absence, I was surprised that
setting things like .mode and .headers in a database didn't stay that way
after exiting the file.

Then I remembered something about a resource file and found .sqliterc on an
internet search and that allowed the persistent settings I wanted.

However, would it not be more natural to keep those settings persistent
inside the db file?

Thanks,

-Tom
___
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

Re: [sqlite] Why no persistent user settings in a database file?

[Rob Willett]

Tom,

Because the file is the database, your preferences for how things are 
displayed are just that, your preferences. Don't combine the two. If you 
have two users with different preferences, who wins?


We move the SQLite database around all the time, across different 
servers. We do not want the preferences for one to affect another 
server.


Rob

On 3 Apr 2019, at 11:52, Tom Browder wrote:


After coming back to SQLite from a long absence, I was surprised that
setting things like .mode and .headers in a database didn't stay that 
way

after exiting the file.

Then I remembered something about a resource file and found .sqliterc 
on an

internet search and that allowed the persistent settings I wanted.

However, would it not be more natural to keep those settings 
persistent

inside the db file?

Thanks,

-Tom
___
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

Re: [sqlite] [EXTERNAL] Why no persistent user settings in a database file?

[Hick Gunter]

User preferences should not be stored in a database file, particularly settings 
affecting the presentation layer of the access tool (sqlite shell) that have 
nothing to do with the function of the storage layer.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Tom Browder
Gesendet: Mittwoch, 03. April 2019 12:53
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] Why no persistent user settings in a database file?

After coming back to SQLite from a long absence, I was surprised that setting 
things like .mode and .headers in a database didn't stay that way after exiting 
the file.

Then I remembered something about a resource file and found .sqliterc on an 
internet search and that allowed the persistent settings I wanted.

However, would it not be more natural to keep those settings persistent inside 
the db file?

Thanks,

-Tom
___
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] Why no persistent user settings in a database file?

[Tom Browder]

On Wed, Apr 3, 2019 at 05:52 Tom Browder  wrote:

> After coming back to SQLite from a long absence, I was surprised that
> setting things like .mode and .headers in a database didn't stay that way
> after exiting the file.
>
> Then I remembered something about a resource file and found .sqliterc on
> an internet search and that allowed the persistent settings I wanted.
>

I forgot that the sqliterc IS mentioned clearly in the sqlite3 man page on
Linux systems.

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

[sqlite] Why no persistent user settings in a database file?

[Tom Browder]

After coming back to SQLite from a long absence, I was surprised that
setting things like .mode and .headers in a database didn't stay that way
after exiting the file.

Then I remembered something about a resource file and found .sqliterc on an
internet search and that allowed the persistent settings I wanted.

However, would it not be more natural to keep those settings persistent
inside the db file?

Thanks,

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

[sqlite] Missing docs: sqliterc, lint

[Tom Browder]

I have not been able to find any docs on the site about .sqliterc or lint.

There is some information on sqliterc in the mailing list archives, and
there is:

sqlite3> .help lint

But if one is not already aware of either one, how does one find out about
them?

Best regards,

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

Re: [sqlite] Can I get help with db design for SQLite use?

[Tom Browder]

On Wed, Apr 3, 2019 at 02:50 Shawn Wagner  wrote:

> Yay Perl! My favorite language. DBD::SQLite is definitely one of the
> better, fuller featured sqlite bindings out there. Though Tom is using
> perl6, not perl5. I have no idea how its version compares.

...

Glad to hear, Shawn, and the Perl 6 version is looking that way, too.

You Perl people really should look into Perl 6. I started with Perl 4 back
in 1993, went
to Perl 5 later than I should have (and used it to execute SQLite2 when I
first
heard about it), and ran with that until 2015 when I saw Perl 6
was almost ready for its first stable release.

I quickly realized Perl 6 is the Perl I envisioned, and have rarely gone
back
except to port favorite old scripts to Perl 6.

I invite you to visit  and look around. The community is
as
welcoming as this one has been to me.  The real place to hang out is the
#perl6 IRC channel.

(At the risk of going off topic, File::Slurp has issues. File::Slurper is a
> better alternative.)


By the way, in Perl 6, slurp is built-in and works great.

Best regards, and Happy Perling!

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

Re: [sqlite] Can I get help with db design for SQLite use?

[Tom Browder]

On Wed, Apr 3, 2019 at 02:08 Rob Willett 
wrote:

> Tom,
>
> We use the Perl DB::SQLite module. It works very well and I cannot
> recall a single issue with it in the last four years. There's not as
> much support for Perl on this mailing list as it's not as popular, but
> most issues you will probably encounter will be at the design level

...

Thanks, Rob, good to know.

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

Re: [sqlite] Can I get help with db design for SQLite use?

[Rob Willett]

Shawn,

I will investigate File::Slurper. Rather than pollute this list, send me 
a mail on

rob.sql...@robertwillett.com about the issues you had.

Thanks

Rob

On 3 Apr 2019, at 8:50, Shawn Wagner wrote:


Yay Perl! My favorite language. DBD::SQLite is definitely one of the
better, fuller featured sqlite bindings out there. Though Tom is using
perl6, not perl5. I have no idea how its version compares.

(At the risk of going off topic, File::Slurp has issues. File::Slurper 
is a

better alternative.)

On Wed, Apr 3, 2019, 12:08 AM Rob Willett 


wrote:


Tom,

We use the Perl DB::SQLite module. It works very well and I cannot
recall a single issue with it in the last four years. There's not as
much support for Perl on this mailing list as it's not as popular, 
but

most issues you will probably encounter will be at the design level
rather than at the CPAN module level. Our working assumption is that 
if
the DBI module looks like it has an issue, it doesn't and it's our 
code.

Pretty much the same as for SQLite. Whilst I know that SQLite has had
bugs, the chances of us finding them is minimal, so we assume it's 
our

code again.

However I can say that we use Perl for all our system code and use
SQLite within it and its fast and easy.

If you're using Perl, I would also recommend the following other
modules, this is a direct pull from our code. I've removed our code
specific modules.

```
#!/usr/bin/perl -w

use strict;
use warnings;

use Switch;
use DBI;
use JSON;
use Getopt::Long;  <-- Easiest way to get command line args in 
and

processed.
use Data::Dumper;  <-- Utterly essential, don't leave home 
without

it.
use Mojolicious::Lite; <-- Only need if you making a REST based 
server
use Mojo::Parameters;  <-- Only need if you making a REST based 
server
use Mojo::URL; <-- Only need if you making a REST based 
server
use Mojo::Log; <-- Only need if you making a REST based 
server

use REST::Client; <-- More rest based stuff
use Sereal::Encoder qw(encode_sereal sereal_encode_with_object);
use Sereal::Decoder qw(decode_sereal sereal_decode_with_object
scalar_looks_like_sereal);
use DateTime;  <-- Manage date and time properly.
use Net::Curl::Easy;
use Crypt::Random qw( makerandom );
use Log::Log4perl qw(get_logger :levels);
use File::Path qw(make_path); <-- Quick and easy way to make paths 
and

directories.
use Net::Address::IP::Local;  <-- Easy way to manipulate IP 
addresses.

use File::Slurp; <-- Quick and easy way to read and write files.
use Clone 'clone'; <-- You'll use a lot of objects and structures. 
Copy

them properly and fast.

```

We use Log4Perl a lot as it's easy to get things setup and then you 
can

modify one log file and get easy changes. Also we use Mojolicious for
all the REST code wrapping.

These are tried and tested CPAN modules that we know just work for us
and are a standard part of every new build. The most awkward one if 
the

curl one, there seems to be a lot of versions of the Curl::Easy stuff
which simply don't work on Mac OS X (or other OS's).

Just my 2p worth.

Rob

On 3 Apr 2019, at 0:21, Tom Browder wrote:


On Tue, Apr 2, 2019 at 17:30 am...@juno.com  wrote:


You might want to import everything into SQLite Studio or SQLite
Suite I
forget the exact name)--a freebie on the internet. I found it 
worked

for
me. In order to help others, I would appreciate it if you tell us 
on

this
usergroup how you made out. May it work for you. Peace! Alex



Thanks for the idea, Alex.

I'm doing all programmatically at the moment (using a Perl 6 module:
DB::SQLite), but using one of those tools you mentioned would help 
in

design for sure!

-Tom
___
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


___
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

Re: [sqlite] Can I get help with db design for SQLite use?

[Shawn Wagner]

Yay Perl! My favorite language. DBD::SQLite is definitely one of the
better, fuller featured sqlite bindings out there. Though Tom is using
perl6, not perl5. I have no idea how its version compares.

(At the risk of going off topic, File::Slurp has issues. File::Slurper is a
better alternative.)

On Wed, Apr 3, 2019, 12:08 AM Rob Willett 
wrote:

> Tom,
>
> We use the Perl DB::SQLite module. It works very well and I cannot
> recall a single issue with it in the last four years. There's not as
> much support for Perl on this mailing list as it's not as popular, but
> most issues you will probably encounter will be at the design level
> rather than at the CPAN module level. Our working assumption is that if
> the DBI module looks like it has an issue, it doesn't and it's our code.
> Pretty much the same as for SQLite. Whilst I know that SQLite has had
> bugs, the chances of us finding them is minimal, so we assume it's our
> code again.
>
> However I can say that we use Perl for all our system code and use
> SQLite within it and its fast and easy.
>
> If you're using Perl, I would also recommend the following other
> modules, this is a direct pull from our code. I've removed our code
> specific modules.
>
> ```
> #!/usr/bin/perl -w
>
> use strict;
> use warnings;
>
> use Switch;
> use DBI;
> use JSON;
> use Getopt::Long;  <-- Easiest way to get command line args in and
> processed.
> use Data::Dumper;  <-- Utterly essential, don't leave home without
> it.
> use Mojolicious::Lite; <-- Only need if you making a REST based server
> use Mojo::Parameters;  <-- Only need if you making a REST based server
> use Mojo::URL; <-- Only need if you making a REST based server
> use Mojo::Log; <-- Only need if you making a REST based server
> use REST::Client; <-- More rest based stuff
> use Sereal::Encoder qw(encode_sereal sereal_encode_with_object);
> use Sereal::Decoder qw(decode_sereal sereal_decode_with_object
> scalar_looks_like_sereal);
> use DateTime;  <-- Manage date and time properly.
> use Net::Curl::Easy;
> use Crypt::Random qw( makerandom );
> use Log::Log4perl qw(get_logger :levels);
> use File::Path qw(make_path); <-- Quick and easy way to make paths and
> directories.
> use Net::Address::IP::Local;  <-- Easy way to manipulate IP addresses.
> use File::Slurp; <-- Quick and easy way to read and write files.
> use Clone 'clone'; <-- You'll use a lot of objects and structures. Copy
> them properly and fast.
>
> ```
>
> We use Log4Perl a lot as it's easy to get things setup and then you can
> modify one log file and get easy changes. Also we use Mojolicious for
> all the REST code wrapping.
>
> These are tried and tested CPAN modules that we know just work for us
> and are a standard part of every new build. The most awkward one if the
> curl one, there seems to be a lot of versions of the Curl::Easy stuff
> which simply don't work on Mac OS X (or other OS's).
>
> Just my 2p worth.
>
> Rob
>
> On 3 Apr 2019, at 0:21, Tom Browder wrote:
>
> > On Tue, Apr 2, 2019 at 17:30 am...@juno.com  wrote:
> >
> >> You might want to import everything into SQLite Studio or SQLite
> >> Suite I
> >> forget the exact name)--a freebie on the internet. I found it worked
> >> for
> >> me. In order to help others, I would appreciate it if you tell us on
> >> this
> >> usergroup how you made out. May it work for you. Peace! Alex
> >
> >
> > Thanks for the idea, Alex.
> >
> > I'm doing all programmatically at the moment (using a Perl 6 module:
> > DB::SQLite), but using one of those tools you mentioned would help in
> > design for sure!
> >
> > -Tom
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I get help with db design for SQLite use?

[Rob Willett]

Tom,

We use the Perl DB::SQLite module. It works very well and I cannot 
recall a single issue with it in the last four years. There's not as 
much support for Perl on this mailing list as it's not as popular, but 
most issues you will probably encounter will be at the design level 
rather than at the CPAN module level. Our working assumption is that if 
the DBI module looks like it has an issue, it doesn't and it's our code. 
Pretty much the same as for SQLite. Whilst I know that SQLite has had 
bugs, the chances of us finding them is minimal, so we assume it's our 
code again.


However I can say that we use Perl for all our system code and use 
SQLite within it and its fast and easy.


If you're using Perl, I would also recommend the following other 
modules, this is a direct pull from our code. I've removed our code 
specific modules.


```
#!/usr/bin/perl -w

use strict;
use warnings;

use Switch;
use DBI;
use JSON;
use Getopt::Long;  <-- Easiest way to get command line args in and 
processed.
use Data::Dumper;  <-- Utterly essential, don't leave home without 
it.

use Mojolicious::Lite; <-- Only need if you making a REST based server
use Mojo::Parameters;  <-- Only need if you making a REST based server
use Mojo::URL; <-- Only need if you making a REST based server
use Mojo::Log; <-- Only need if you making a REST based server
use REST::Client; <-- More rest based stuff
use Sereal::Encoder qw(encode_sereal sereal_encode_with_object);
use Sereal::Decoder qw(decode_sereal sereal_decode_with_object 
scalar_looks_like_sereal);

use DateTime;  <-- Manage date and time properly.
use Net::Curl::Easy;
use Crypt::Random qw( makerandom );
use Log::Log4perl qw(get_logger :levels);
use File::Path qw(make_path); <-- Quick and easy way to make paths and 
directories.

use Net::Address::IP::Local;  <-- Easy way to manipulate IP addresses.
use File::Slurp; <-- Quick and easy way to read and write files.
use Clone 'clone'; <-- You'll use a lot of objects and structures. Copy 
them properly and fast.


```

We use Log4Perl a lot as it's easy to get things setup and then you can 
modify one log file and get easy changes. Also we use Mojolicious for 
all the REST code wrapping.


These are tried and tested CPAN modules that we know just work for us 
and are a standard part of every new build. The most awkward one if the 
curl one, there seems to be a lot of versions of the Curl::Easy stuff 
which simply don't work on Mac OS X (or other OS's).


Just my 2p worth.

Rob

On 3 Apr 2019, at 0:21, Tom Browder wrote:


On Tue, Apr 2, 2019 at 17:30 am...@juno.com  wrote:

You might want to import everything into SQLite Studio or SQLite 
Suite I
forget the exact name)--a freebie on the internet. I found it worked 
for
me. In order to help others, I would appreciate it if you tell us on 
this

usergroup how you made out. May it work for you. Peace! Alex



Thanks for the idea, Alex.

I'm doing all programmatically at the moment (using a Perl 6 module:
DB::SQLite), but using one of those tools you mentioned would help in
design for sure!

-Tom
___
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