Re: [sqlite] Remove row to insert new one on a full database

[Richard Damon]

On 4/4/19 11:35 PM, Simon Slavin wrote:
> On 5 Apr 2019, at 4:14am, Richard Damon  wrote:
>
>> I think is logic is to attempt to insert a row, and if rather than
>> inserting it, the call returns the error condition, 'Database Full'
> Okay.  So now we understand what OP meant by the database being full.
>
> SQLITE_FULL does not mean 'Database Full'.  What's full is the volume the 
> database is stored on.  And the problem is worse than it might appear because 
> if the volume is full you can't reliably do anything to the database.  
> Because even if your next SQL command is DELETE, the first thing that'll 
> happen is that SQLite will try to write to the journal file.  And that may 
> fail, because there's no room for the journal file to get bigger.
>
> In other words, if your database ever gets this big, you need a human to come 
> sort things out.
>
> So don't do that.  Don't let your database get that big.  Monitor the free 
> space and start deleting stuff if free space gets below a certain amount.  
> Leave yourself 5Meg of space free or something.
>
> Simon.
Yes, if the error is the SQLite error from the OS saying the disk is
full, then you are in big trouble as you can't expect to be able to do
anything. If perhaps you have some hooks at the file system level that
return that error if this one file hits a certain size and doesn't allow
it to get bigger, but there is still space for the journal file, you
might get the sort of behavior described.

-- 
Richard Damon

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

Re: [sqlite] Remove row to insert new one on a full database

[Simon Slavin]

On 5 Apr 2019, at 4:14am, Richard Damon  wrote:

> I think is logic is to attempt to insert a row, and if rather than
> inserting it, the call returns the error condition, 'Database Full'

Okay.  So now we understand what OP meant by the database being full.

SQLITE_FULL does not mean 'Database Full'.  What's full is the volume the 
database is stored on.  And the problem is worse than it might appear because 
if the volume is full you can't reliably do anything to the database.  Because 
even if your next SQL command is DELETE, the first thing that'll happen is that 
SQLite will try to write to the journal file.  And that may fail, because 
there's no room for the journal file to get bigger.

In other words, if your database ever gets this big, you need a human to come 
sort things out.

So don't do that.  Don't let your database get that big.  Monitor the free 
space and start deleting stuff if free space gets below a certain amount.  
Leave yourself 5Meg of space free or something.

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

Re: [sqlite] Remove row to insert new one on a full database

[Richard Damon]

I think is logic is to attempt to insert a row, and if rather than
inserting it, the call returns the error condition, 'Database Full', you
remove a record and then try again (a form of error recovery), if it
succeeds, then you go on and get more data.

If full was X records, then they would ALWAYS get room to add a new
record after deleting one. Since the reported issue is that it can
require the deletion of possibly very many records to get space to
succeed tells me that 'full' is size based (maybe a max number of
pages). If the record being added is on the larger size of all the 'same
sized' records, then you need to keep deleting records until you get one
that big, or delete two that are consecutive so that you get a large
enough space, or you delete a records that has some spare space next to
it giving enough room.

One issue with this definition is that you can't tell if the database is
currently full except by trying to add the record (or knowing a LOT of
internal details of record storage), which is what the code is doing.

On 4/4/19 9:19 AM, Stephen Chrzanowski wrote:
> This almost sounds like "Full" is a software limitation, in that your
> application is specifying that "Full" means you can only have "X" number of
> rows.
>
> If you're looking to remove data, I'd suggest that you find some way to
> isolate the oldest record, either by a row identifier (Like an ID field
> that's using auto-increment) or a date/time stamp (Assigned by
> current_timestamp).
>
> Also, your logic is backwards in your pseudo-code.  You should check the
> status of the database before you do any kind of insert. The reason is, if
> you insert into an already full database, then you're database is over-full
> at that point.  Also, your pseudo-code has two conditions to look at...  Do
> this forever, and repeat while status is full.  Not to mention, if your
> database is messed up and nothing can be inserted even though the table is
> empty, you've introduced a lockup.
>
> What I think you're looking more for is:
>
> while (dbStatus() == full) {
>   remove_one_row_from_db();
> }
> result=insert_1_row_to_db();
> if (result != resOK) {
>   die("uhh.. Problem with the database?");
> }
>
>
> On Thu, Apr 4, 2019 at 6:53 AM Arthur Blondel 
> wrote:
>
>> Hello
>>
>> When I try to insert new data to a full SQLite database, I need to remove
>> much more than really needed. I'm doing the following:
>>
>> while(1) {
>> do {
>> status = insert_1_row_to_db();
>> if (status == full) {
>> remove_one_row_from_db();
>> }
>> } while (status == full);}
>>
>> The inserted data has always the same size. When the database is full,
>> removing only one row is enough to insert the new one. But after a while, I
>> need to remove 30, 40 and even more the 100 rows to be able to insert one
>> new row. Is it the correct behavior of SQLite? Is there a way to remove
>> only what is needed and no more? Thanks
>> ___
>> 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


-- 
Richard Damon

___
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 4 Apr 2019, at 10:12pm, Lifepillar  wrote:

> This is essentially a pragmatic choice, as the semantics of NULLs is 
> unspecified and ambiguous.

The way SQL handles NULLs may sometimes appear inconsistent, but is the result 
of SQL handling rows as sets.  Some of the behaviour is, carefully designed, 
with the knowledge that the NULL that results from one step of an expression is 
going to be fed into the next step of the expression.

For those purposes, NULL means one of two things: "no value" or "value 
unknown".  For instance, the sum of a result which includes a NULL value is 
NULL.  But the sum of a result with zero rows is not NULL, it's zero.

For the purposes of a decimal extension to SQLite, I would imitate what SQLite3 
does with REAL values.  If you have a question about how SQLite sees NULL it 
might be answered here:



It's especially important that the test script at the end of that page, behaves 
the same if you run it as is, and if you substitute the 'int' type with your 
decimal type.  If you have any questions, please don't hesitate to ask.
___
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 4 Apr 2019, at 21:36, James K. Lowden  wrote:
> 
> On Thu, 4 Apr 2019 17:30:29 +0200
> Lifepillar  wrote:
> 
>> On 4 Apr 2019, at 17:15, James K. Lowden 
>> wrote:
>>> On Wed, 3 Apr 2019 14:30:52 +0200
>>> Lifepillar  wrote:
 SQLite3 Decimal is an extension implementing exact decimal
 arithmetic for SQLite3. 
>>> 
>>> What does divide-by-zero yield?  
>> 
>> By default:
>> 
>> sqlite> select decDiv(1,0);
>> Error: Division by zero
>> 
>> You have the option to ignore the error, though, in which case you
>> get +Inf:
>> 
>> sqlite> delete from decTraps where flag = 'Division by zero';
>> sqlite> select decStr(decDiv(1,0));
>> Infinity
>> 
>> decTraps is a virtual table containing a list of flags which, when
>> set by some function, raise an error. The flags are from IEEE 754.
> 
> Nice.  Division by zero can be a problem in other DBMSs because, if it
> occurs, the query aborts, and you then have to search for the cases and
> decide what to do. By flagging and marking them, you make that task
> easier.  Especially when the query runs for a long time.

Thanks for the positive feedback!

>> NULLs are avoided where other results make sense. For instance:
>> 
>> sqlite> create table T (n blob);
>> sqlite> select decStr(decSum(n)) from T; -- Sum of an empty set of
>> sqlite> values
>> 0
>> sqlite> select decStr(decAvg(n)) from T; -- Avg. of empty set of
>> sqlite> values
>> NaN
> 
> Here, as you may know, you're in close touch with SQL's ambivalent
> ambiguity with NULL.  Should the sum of nulls be 0?  Should the count
> be 0?  Just one example of how SQL is a fossil from the 1980s.  

The examples above use an empty table, so no NULLs are present and there
is no ambiguity. In the presence of NULLs I have decided to go with SQL:
decimal aggregate functions simply ignore them and other functions, such
as decAdd() (addition), return NULL if any of their arguments is NULL
(which is consistent with SQL behaviour for + and similar operators).

This is essentially a pragmatic choice, as the semantics of NULLs is
unspecified and ambiguous. And I don’t think that it matters much, after
all: if you care about exact arithmetic (e.g., for accounting), you
should definitely avoid NULLs, at least in any numeric fields.

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

[James K. Lowden]

On Thu, 4 Apr 2019 11:21:41 -0400
Joshua Wise  wrote:

> > On the other hand, what table has a floating point number in its
> > key?  
> > 
> > How do you even express the value of such a key for an exact
> > match?  
> 
> Well I imagine it can be very useful for range queries. Imagine
> Julian dates, coordinate points, rankings, etc.

Julian dates are integers.  The tm structure is all integers, too.  

I suppose you could store lat/lon as floating point.  It's exactly the
kind of data that calls out of a tm-like structure, though, because
officially there are 60 minutes in a degree, and 60 seconds in a minute.
Just as with time, the governing authorities use a non-decimal
notation; decimal fractions of a degree are mere computational
convienience.  And, again, it's not part of the key.  

In financial analysis, range queries over large datasets are common.  If
it's not a range of dates, it's a range of
returns/price/earning/capitalization over time.  Yet Microsoft SQL
Server never suggested we use anything other than IEEE to store the
data.  Perhaps that's because, more often than not, floating point data
are manipulated as part of the query.  

If you're joining the table to itself to select price change over time
to compute, say, variance, the absolute magnitude of the data are
uninteresting.  You find the stocks by date, subtract the prices and
compute the variance, in IEEE format, of course, because that's what
the CPU supports.  Then you sort and filter the top quintile, or
whatever.  In such a case, the overhead of floating-point conversion
will be significant: twice for every row, overhead that is nonexistent
today.  

I'm skeptical of the claimed advantage.  The downside is clear.  If the
advantage can be shown, its use would be specialized.  OTOH, a
compiete BCD implementation would be ... interesting.  

--jkl

___
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

[James K. Lowden]

On Thu, 4 Apr 2019 17:30:29 +0200
Lifepillar  wrote:

> On 4 Apr 2019, at 17:15, James K. Lowden 
> wrote:
> > On Wed, 3 Apr 2019 14:30:52 +0200
> > Lifepillar  wrote:
> >> SQLite3 Decimal is an extension implementing exact decimal
> >> arithmetic for SQLite3. 
> > 
> > What does divide-by-zero yield?  
> 
> By default:
> 
> sqlite> select decDiv(1,0);
> Error: Division by zero
> 
> You have the option to ignore the error, though, in which case you
> get +Inf:
> 
> sqlite> delete from decTraps where flag = 'Division by zero';
> sqlite> select decStr(decDiv(1,0));
> Infinity
> 
> decTraps is a virtual table containing a list of flags which, when
> set by some function, raise an error. The flags are from IEEE 754.

Nice.  Division by zero can be a problem in other DBMSs because, if it
occurs, the query aborts, and you then have to search for the cases and
decide what to do. By flagging and marking them, you make that task
easier.  Especially when the query runs for a long time.

> NULLs are avoided where other results make sense. For instance:
> 
> sqlite> create table T (n blob);
> sqlite> select decStr(decSum(n)) from T; -- Sum of an empty set of
> sqlite> values
> 0
> sqlite> select decStr(decAvg(n)) from T; -- Avg. of empty set of
> sqlite> values
> NaN

Here, as you may know, you're in close touch with SQL's ambivalent
ambiguity with NULL.  Should the sum of nulls be 0?  Should the count
be 0?  Just one example of how SQL is a fossil from the 1980s.  

--jkl
 

___
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 4 Apr 2019, at 17:30, Lifepillar  wrote:
> 
>> You have the option to ignore the error, though, in which case you get +Inf:
> 
> sqlite> delete from decTraps where flag = 'Division by zero';
> sqlite> select decStr(decDiv(1,0));
> Infinity

Forgot to mention that in this case a flag is silently added to another virtual 
table called decStatus:

sqlite> select * from decStatus;
Division by zero

so you may still query for errors. You have to manually clear the status then:

delete from decStatus; -- Clear the status flags

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 4 Apr 2019, at 17:15, James K. Lowden  wrote:
> 
> On Wed, 3 Apr 2019 14:30:52 +0200
> Lifepillar  wrote:
> 
> 
>> SQLite3 Decimal is an extension implementing exact decimal arithmetic
>> for SQLite3. It is currently unfinished and under development.  
> ...
>> 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. 
> 
> What does divide-by-zero yield?  

By default:

sqlite> select decDiv(1,0);
Error: Division by zero

You have the option to ignore the error, though, in which case you get +Inf:

sqlite> delete from decTraps where flag = 'Division by zero';
sqlite> select decStr(decDiv(1,0));
Infinity

decTraps is a virtual table containing a list of flags which, when set by some 
function, raise an error. The flags are from IEEE 754.

> If NULL, no amount of exactitude will matter.  If the library is based
> on math, on the other hand, that would be a boon to SQLite users.  

NULLs are avoided where other results make sense. For instance:

sqlite> create table T (n blob);
sqlite> select decStr(decSum(n)) from T; -- Sum of an empty set of values
0
sqlite> select decStr(decAvg(n)) from T; -- Avg. of empty set of values
NaN

Life.


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

[sqlite] Feature request: ALTER TABLE ... ADD CONSTRAINT...

[Marko Vodanovi]

Hi!
I don't know if this has been already discussed, most probably yes. But are 
there any plans to implement the possibility to add constraints to existing 
tables? And I'm not necessarily asking for full-blown functionality. Currently 
to do that you have to drop the table and recreate it again with the 
constraint. Possibly you could rename it, create the new table with the 
constraint, transfer data manually from old to new and then drop the old table? 
I'd be fine if this functionality behaves like that, I'd be fine even if it 
just drops the table and then recreates it again, losing everything stored 
inside (if it's noted in the docs with big shiny red letters that that's how it 
works).
Some background... I'm a developer and we use the concept of migration files to 
make changes to the DB structure. So, if a feature needs changes in the DB, we 
write a new migration file which migrates its structure from the old version to 
the new one. We support different DB engines to feed the service and the 
migration logic is abstracted by a library which detects what DBMS is running 
and constructs the appropriate SQL for it. We use SQLite just for running our 
tests quickly while developing, proper instances mostly run Postgres or 
something similar. When we start our tests, the DB is empty and then migrations 
are run one by one to reach the needed DB structure for the application. This 
is why if you were to implement adding constraints which completely wipe the 
table, I personally wouldn't care (if that's easier for you). What's causing us 
problems right now is that in our migration files we have to write something 
approximately like { if (dbType == SQLite) { "DROP TABLE...; CREATE 
TABLE...CONSTRAINT...;" } else { "ALTER TABLE ... ADD CONSTRAINT ...;" } }. If 
we could use the same way of altering the DB for SQLite as for other databases, 
it would help us so so much. Migrations are also used to update existing 
systems with new releases whose data mustn't be wiped so going for the SQLite 
approach with non-SQLite databases isn't an option. We're occasionally weighing 
if SQLite is worth the hassle or should we just drop it and use something like 
Postgres even for running our local tests, even though it would be slower to 
run the tests (in case of a Dockerized environment) and more of a hassle to set 
up. I'm sure we're not the only devs out there with these kinds of problems / 
hassles.
So... Is this something we can look forward to in the near / not so near 
future? Or did you already decide against it?

Best regards,Marko
___
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]

> On the other hand, what table has a floating point number in its key?  
> 
> How do you even express the value of such a key for an exact match?  

Well I imagine it can be very useful for range queries. Imagine Julian dates, 
coordinate points, rankings, etc.

I suppose in the most common case, parsing the on-disk format to IEEE would 
only be necessary on a small number of rows, after the range query has already 
used memcmp() to find the rows it cares about. But yeah, in the case of bulk 
loading it’s probably only a loss.

> On Apr 4, 2019, at 11:15 AM, James K. Lowden  wrote:
> 
> On Wed, 3 Apr 2019 17:29:47 -0400
> 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.
> 
> On the other hand, what table has a floating point number in its key?  
> 
> How do you even express the value of such a key for an exact match?  
> 
> There is also a significant cost of converting to and from IEEE
> format.  In my experience bulk-loading quantitative databases, I
> encountered many occasions in which parsing the input accounted for 50%
> of the computation.  The limit wasn't network bandwidth or server
> speed, it was data conversion.  
> 
> --jkl
> ___
> 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

[James K. Lowden]

On Wed, 3 Apr 2019 14:30:52 +0200
Lifepillar  wrote:


> SQLite3 Decimal is an extension implementing exact decimal arithmetic
> for SQLite3. It is currently unfinished and under development.  
...
> 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. 

What does divide-by-zero yield?  

If NULL, no amount of exactitude will matter.  If the library is based
on math, on the other hand, that would be a boon to SQLite users.  

--jkl


___
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

[James K. Lowden]

On Wed, 3 Apr 2019 11:37:47 -0600
Warren Young  wrote:

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

Exactly (as it were).  

Physical entities cannot be measured to more than 6 orders of
magnitude.  Finer than that, error takes over.  

What is the distance from New York to Los Angeles?  

We know it's 3,944 km.  Do we know it's 3,944,000 meters?  3,944,000,000
mm? We do not.  

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

[James K. Lowden]

On Wed, 3 Apr 2019 17:29:47 -0400
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.

On the other hand, what table has a floating point number in its key?  

How do you even express the value of such a key for an exact match?  

There is also a significant cost of converting to and from IEEE
format.  In my experience bulk-loading quantitative databases, I
encountered many occasions in which parsing the input accounted for 50%
of the computation.  The limit wasn't network bandwidth or server
speed, it was data conversion.  

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

Re: [sqlite] Remove row to insert new one on a full database

[Stephen Chrzanowski]

This almost sounds like "Full" is a software limitation, in that your
application is specifying that "Full" means you can only have "X" number of
rows.

If you're looking to remove data, I'd suggest that you find some way to
isolate the oldest record, either by a row identifier (Like an ID field
that's using auto-increment) or a date/time stamp (Assigned by
current_timestamp).

Also, your logic is backwards in your pseudo-code.  You should check the
status of the database before you do any kind of insert. The reason is, if
you insert into an already full database, then you're database is over-full
at that point.  Also, your pseudo-code has two conditions to look at...  Do
this forever, and repeat while status is full.  Not to mention, if your
database is messed up and nothing can be inserted even though the table is
empty, you've introduced a lockup.

What I think you're looking more for is:

while (dbStatus() == full) {
  remove_one_row_from_db();
}
result=insert_1_row_to_db();
if (result != resOK) {
  die("uhh.. Problem with the database?");
}


On Thu, Apr 4, 2019 at 6:53 AM Arthur Blondel 
wrote:

> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
> ___
> 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] How many digits do you need ?

[Michael Falconer]

Nice one Simon,

as a resident Australian (and a lover of levity) I liked your link, which
led me on to the data source. I took a pair of those 15 digit coordinates
and pumped them into good old Google maps but sadly I was unable to zoom in
far enough to see any chlorine atoms. :-(. Shame that.

On Thu, 4 Apr 2019 at 16:50, Simon Slavin  wrote:

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


-- 
Regards,
 Michael.j.Falconer.
___
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 4 Apr 2019, at 10:37, Thomas Kurz  wrote:
> 
> I appreciate your effort towards this extension. In my opinion, however, this 
> is (along with bigint-support) a feature that belongs into core (for that 
> reason alone to get math operations, comparisons, aggregates, etc. working in 
> an intuitive way).
> 
> Years ago, for SQLite4, there seem to have been plans for "decimal math". The 
> concept looked very useful and I'd suggest taking that idea up again, but 
> this time for SQLite3 ;-)

You raise an interesting point. From a purely technical perspective, you are 
absolutely right: ui and performance would only benefit from full integration 
into SQLite. But an extension may be maintained independent of the main 
project’s goals, constraints, people and directions. Patching SQLite requires, 
IMO, coordination with SQLite’s developers. AFAIK, SQLite4 is a dead end: is 
there still any interest, or maybe a plan, for “decimal math” in the core?

Life.

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

[sqlite] Full Outer Join of 3 or more tables

[Dominique Devienne]

Up to now, we were doing 2-tables full-outer-join using the classic
emulation, since SQLite lacks support for that join.

But now we are doing it with 3 tables, and it gets ugly fast IMHO.
https://stackoverflow.com/questions/12759087/full-outer-join-in-sqlite-on-4-tables


I "think" the reason that join is not historically supported by SQLite is
because it only implements nested-loops, while full-outer-joins perform
best with merge-join (assuming joining on PK or indexed columns). (I'm not
expert here. Better minds, please correct me of course).

That, and the fact there's an "easy" work-around for 2 tables.
But for 3 or more tables, that "easy" work-around turns into rather awful
SQL though...

Why can't SQLite itself do the query rewriting itself, of the
full-outer-join?

The day SQLite gains "native" support for merge-join, the SQL does not have
to
change and get a speed-boost transparently. And intent is expressed clearly
and
declaratively, while it gets lost in the ugly work-around "manual rewrites"
at the SQL level.
And if merge-join nevers arrives, that's fine too, that's an impl detail
and an optimization.

With SQLite, there's always hope for missing features, even more complex
features did
arrive eventually, witness CTE and Window Functions (so the "Lite-clan" can
please save
your breath :)), so isn't it time to support Full-Outer-Join?

We're always harping about the declarative nature of SQL, so why here when
there's
a SQL-standard way to express intent, we have to jump through hoops and
emulate it
with a "programmatic" recipe that scale very poorly with the number of
tables?

Sorry for the "impatience" here... I wish SQLite would grow that support,
at last.

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

Re: [sqlite] Remove row to insert new one on a full database

[Richard Damon]

On 4/4/19 2:07 AM, Arthur Blondel wrote:
> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks

As people say, this isn't a good description of the problem, and I
suspect that your statement of 'same size' is a big part of the issue.
Did you realize that the size of a row can be affected by the values
being inserted into it? This means that if you do have an upper limit to
the size of the database, and need to delete some data to make room for
more, if you don't vacuum the database to squeeze out  the holes in the
database, you will need to delete a row that is big enough to store the
new row to have room to store it. After that, you may have room to store
a number of new rows that fit within the gaps you left behind.

Vacuuming a database can be a slow operation, because it basically needs
to copy the whole database into a new copy, squeezing out the gaps as it
goes. It also says you need space on your system for the two copies of
the database, so if that is the critical issue, might not be feasible.
Vacuuming, if practical, is the best way to (after you delete something)
to make room in the database, as it can bring together all the odd holes
from the various pieces of deleted data. 

-- 
Richard Damon

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

Re: [sqlite] Remove row to insert new one on a full database

[Roger Schlueter]

This looks to be an example of the classic XY Problem.  You are asking 
how to solve Problem X when what you're trying to do is solve Problem 
Y.  In this case, "X" is a full database, which is almost certainly an 
oxymoron since SQLIte can store millions of rows of data.  It is not 
clear what Problem Y really is.


How do you know the database is "full"?  What does the inserted data 
being the "same size" mean?  More generally, what are you trying to do, 
how much and what kind of data are you inserting and what platform and 
SQLite version are you using?


On 4/3/2019 23:07, Arthur Blondel wrote:

Hello

When I try to insert new data to a full SQLite database, I need to remove
much more than really needed. I'm doing the following:

while(1) {
 do {
 status = insert_1_row_to_db();
 if (status == full) {
 remove_one_row_from_db();
 }
 } while (status == full);}

The inserted data has always the same size. When the database is full,
removing only one row is enough to insert the new one. But after a while, I
need to remove 30, 40 and even more the 100 rows to be able to insert one
new row. Is it the correct behavior of SQLite? Is there a way to remove
only what is needed and no more? Thanks
___
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] importing a large TSV file

[Gert Van Assche]

Thank you all for these tips. Very helpful!

Op di 2 apr. 2019 om 08:35 schreef Rowan Worth :

> On Mon, 1 Apr 2019 at 19:20, Domingo Alvarez Duarte 
> wrote:
>
> > Hello Gert !
> >
> > I normally do this (be aware that if there is a power outage the
> > database is screwed):
> >
> > ===
> >
> > PRAGMA synchronous = OFF;
> > begin;
> >
> > --processing here
> >
> > commit;
> > PRAGMA synchronous = ON;
> >
>
> You can probably leave the pragma alone without overly affecting import
> time tbh. The main thing is putting all the work into one transaction, and
> at that point you're down to 2 or 3 sync() calls. I guess there's still
> value in not having to wait for the journal to hit disk though. Maybe even
> PRAGMA journal_mode = OFF would be appropriate.
>
> -Rowan
> ___
> 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] Remove row to insert new one on a full database

[Chris Locke]

> When the database is full

What do you mean by a full database?  Do you mean when the operating system
has run out of disk space?
A SQLite database can hold millions of rows, so technically, a database
cannot be 'full'.

It would be easier explaining the full issue and what you consider the
problem, rather than asking for help on a solution which may not be
required.


Thanks,
Chris

On Thu, Apr 4, 2019 at 11:53 AM Arthur Blondel 
wrote:

> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
> ___
> 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] Remove row to insert new one on a full database

[Arthur Blondel]

Hello

When I try to insert new data to a full SQLite database, I need to remove
much more than really needed. I'm doing the following:

while(1) {
do {
status = insert_1_row_to_db();
if (status == full) {
remove_one_row_from_db();
}
} while (status == full);}

The inserted data has always the same size. When the database is full,
removing only one row is enough to insert the new one. But after a while, I
need to remove 30, 40 and even more the 100 rows to be able to insert one
new row. Is it the correct behavior of SQLite? Is there a way to remove
only what is needed and no more? Thanks
___
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

[Thomas Kurz]

I appreciate your effort towards this extension. In my opinion, however, this 
is (along with bigint-support) a feature that belongs into core (for that 
reason alone to get math operations, comparisons, aggregates, etc. working in 
an intuitive way).

Years ago, for SQLite4, there seem to have been plans for "decimal math". The 
concept looked very useful and I'd suggest taking that idea up again, but this 
time for SQLite3 ;-)




- Original Message - 
From: Lifepillar 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Wednesday, April 3, 2019, 14:30:52
Subject: [sqlite] ANN: SQLite3 Decimal Extension

[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

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

Re: [sqlite] Backing up a SQLite database without the CLI

[Bohwaz/Fossil]

If you're trying to copy a file while connections still have it open
then you should use SQLite API calls to do it.  The obvious ones are
in the SQLite Online Backup API, which is the set of calls underlying
the '.backup' command you mentioned.  You can find documentation for
this here:



Unfortunately I don't think the PHP sqlite3 tools give access to this 
API.


Not yet, but I did a patch and there is a pending pull request for that: 
https://github.com/php/php-src/pull/3617


It does work, I'm using it, and you could too if you are willing to 
compile your own PHP extension.


Hopefully it will land in PHP 7.4 :)

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