Re: [sqlite] Exit value of "PRAGMA integrity_check" in command line

[林自均]

Hi Simon,

Thank you for the suggestions. Yes, we already found the reason why the
database would corrupt and fixed it. However, we still need to check users'
database in our newer version of software in order to write new logs. We
don't check this very often.

The choice between deleting and renaming is not really that important. We
keep the corrupt database only for the developers to debug, not for anyone
else to use.

Best,
John Lin

Simon Slavin  於 2017年11月23日 週四 下午12:03寫道：

>
>
> On 23 Nov 2017, at 3:11am, 林自均  wrote:
>
> > It's for logs. If it corrupts, we rename it with a suffix ".corrupt" and
> > write new logs into a new sqlite file. Does that affect the way I check
> it?
>
> I was interested why you were checking for corruption, so your answer just
> inspires more questions.
>
> Why are your sqlite database files being corrupted so frequently that it’s
> worth checking for corruption ?  SQLite is a very good database system.  It
> does not corrupt its files.  You might spend your time better finding out
> why you keep getting corrupt log files.  Maybe there is a fault in your
> programming, or in your hardware.
>
> Why are you not just deleting the corrupt database files ?  They’re
> corrupt.  You cannot trust the contents at all.  You do not know if they
> have one row missing or hundreds.  They do not tell you anything useful
>
> ALso, why not just create a new log each time ?  It can take a very long
> time to execute "PRAGMA integrity_check".  The longer your file goes
> uncorrupted, the longer the check will take.  Eventually you will get a
> pause of many minutes every time you start up your program.
>
> Simon.
> ___
> 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] Callback for sqlite3_finalize()

[petern]

Ben.  Your post was in spam.

Is your extension function an aggregate?

From https://www.sqlite.org/c3ref/get_auxdata.html  "These functions may be
used by (non-aggregate) SQL functions..."

The trace hook API has some statement level modes you might hook for to
manage your cached data:

https://www.sqlite.org/c3ref/c_trace.html

You are also completely free to reference your own data structures by name
from your custom extension.

Thinking about this, it occurs to me again, it would help many if SQLite
also had the similar sqlite3_get_data()  and sqlite3_set_data()  per
connection.   In your case you could organize your data on the connection
and use trace hook to keep it relevant to extension function query(s).  As
it stands the only connection level object appears to be a custom VFS shim
which is overkill for this.

Does DRH read these posts?  The question is why isn't there a connection
level get/set cache API similar to the auxdata calls?

Peter










On Tue, Nov 21, 2017 at 3:03 PM, Stadin, Benjamin <
benjamin.sta...@heidelberg-mobil.com> wrote:

> Hi,
>
> I register a custom SQL function using dms_create_function_v2, and in the
> C callback I create a rather heavy C++ helper class which I need to prepare
> the result.
>
> I currently use sqlite3_get_auxdata and sqlite3_set_auxdata, but my
> problem is that the finalization callback  of sqlite3_set_auxdata is called
> after each step().
>
> Is there a way to hook into sqlite3_finalize(), in order to manage the
> lifecycle of application data that is created together with a statement and
> to be destroyed once the statement is finalized?
>
> Ben
>
> ___
> 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] Exit value of "PRAGMA integrity_check" in command line

[Simon Slavin]

On 23 Nov 2017, at 3:11am, 林自均  wrote:

> It's for logs. If it corrupts, we rename it with a suffix ".corrupt" and
> write new logs into a new sqlite file. Does that affect the way I check it?

I was interested why you were checking for corruption, so your answer just 
inspires more questions.

Why are your sqlite database files being corrupted so frequently that it’s 
worth checking for corruption ?  SQLite is a very good database system.  It 
does not corrupt its files.  You might spend your time better finding out why 
you keep getting corrupt log files.  Maybe there is a fault in your 
programming, or in your hardware.

Why are you not just deleting the corrupt database files ?  They’re corrupt.  
You cannot trust the contents at all.  You do not know if they have one row 
missing or hundreds.  They do not tell you anything useful

ALso, why not just create a new log each time ?  It can take a very long time 
to execute "PRAGMA integrity_check".  The longer your file goes uncorrupted, 
the longer the check will take.  Eventually you will get a pause of many 
minutes every time you start up your program.

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

Re: [sqlite] Can I recursively concatenate strings?

[petern]

Shane.  printf() will pad spaces you can replace with 'x' or whatever.

WITH lengths(id,l) AS (VALUES (1,4),(2,1),(3,9))
SELECT id,l,replace(printf('%'||l||'s'),' ','x')mask FROM lengths;
id,l,mask
1,4,
2,1,x
3,9,x

If printf() weren't available, it would be worth the effort to add your own
extension function that does the exact thing efficiently.   Extensions are
extremely powerful.  You can even write extensions to produce desired side
effects.





On Wed, Nov 22, 2017 at 1:56 PM, Shane Dev  wrote:

> Let's say I have a table of stringlengths -
>
> sqlite>select * from stringlengths;
> length
> 4
> 1
> 9
> ...
>
> Can I create a view xstrings containing strings (for example of char 'x')
> with the lengths specified in stringlengths?
>
> desired result -
>
> sqlite>select * from xstrings;
> string
> 
> x
> 
> ...
>
> P.S I know that substr('x', 1, stringlengths.length) would work in
> this particular case but then I must know maximum value of
> stringlengths.length at the point of time when I construct the query. Is
> there a more flexible way?
>
>
> ..
> ___
> 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] Exit value of "PRAGMA integrity_check" in command line

[林自均]

Hi Simon,

It's for logs. If it corrupts, we rename it with a suffix ".corrupt" and
write new logs into a new sqlite file. Does that affect the way I check it?

Best,
John Lin

Simon Slavin  於 2017年11月23日 週四 上午11:04寫道：

>
>
> On 23 Nov 2017, at 3:00am, 林自均  wrote:
>
> > In other word, to check if a sqlite file is corrupted, I have to check
> the
> > stdout instead of the exit value. Am I right?
>
> Why are you checking for corrupted databases ?
> What will you do if the database is corrupt ?
>
> Simon.
> ___
> 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] Exit value of "PRAGMA integrity_check" in command line

[Simon Slavin]

On 23 Nov 2017, at 3:00am, 林自均  wrote:

> In other word, to check if a sqlite file is corrupted, I have to check the
> stdout instead of the exit value. Am I right?

Why are you checking for corrupted databases ?
What will you do if the database is corrupt ?

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

Re: [sqlite] Exit value of "PRAGMA integrity_check" in command line

[林自均]

Hi Richard,

Thank you for the explanation!

In other word, to check if a sqlite file is corrupted, I have to check the
stdout instead of the exit value. Am I right?

Best,
John Lin

Richard Hipp  於 2017年11月23日 週四 上午10:53寫道：

> On 11/22/17, 林自均  wrote:
> >
> > $ sqlite3 other-db.sqlite 'PRAGMA integrity_check'
> > Error: database disk image is malformed
> > $ echo $?
> > 11
> >
> > Is that normal?
> >
>
> Yes.  The error is occurring as SQLite is trying to parse the schema,
> not while running "PRAGMA integrity_chck".
> --
> 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] Exit value of "PRAGMA integrity_check" in command line

[Richard Hipp]

On 11/22/17, 林自均  wrote:
>
> $ sqlite3 other-db.sqlite 'PRAGMA integrity_check'
> Error: database disk image is malformed
> $ echo $?
> 11
>
> Is that normal?
>

Yes.  The error is occurring as SQLite is trying to parse the schema,
not while running "PRAGMA integrity_chck".
-- 
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] Exit value of "PRAGMA integrity_check" in command line

[林自均]

Hi Richard,

After I update to 3.21.0, the situation doesn't change.

$ sqlite3 --version
3.21.0 2017-10-24 18:55:49
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de48827

For "some-db.sqlite" I mentioned in the previous mail, it exit with 0, like
you said. However, if I copy "some-db.sqlite" to "other-db.sqlite" and make
some arbitrary changes in "other-db.sqlite" using vim:

$ sqlite3 other-db.sqlite 'PRAGMA integrity_check'
Error: database disk image is malformed
$ echo $?
11

Is that normal?

Best,
John Lin

Richard Hipp  於 2017年11月23日 週四 上午9:39寫道：

> On 11/22/17, 林自均  wrote:
> >
> > Usually, if the database corrupts, the exit value is non-zero. However, I
> > recently bumped into a corrupted sqlite file that makes the previous
> > command exit with zero.
> >
>
> The behavior of PRAGMA integrity_check changed with release 3.21.0.
> It should now always return error message text and an SQLITE_OK return
> code.  See item 9 on the relase notes:
> https://www.sqlite.org/releaselog/3_21_0.html
>
> --
> 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] Exit value of "PRAGMA integrity_check" in command line

[Richard Hipp]

On 11/22/17, 林自均  wrote:
>
> Usually, if the database corrupts, the exit value is non-zero. However, I
> recently bumped into a corrupted sqlite file that makes the previous
> command exit with zero.
>

The behavior of PRAGMA integrity_check changed with release 3.21.0.
It should now always return error message text and an SQLITE_OK return
code.  See item 9 on the relase notes:
https://www.sqlite.org/releaselog/3_21_0.html

-- 
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] how into insert row into middle of table with integer primary key

[Stephen Chrzanowski]

SQL in any flavor isn't going to get what you want directly, as you've seen
in these posts.

The data is in your database for a reason.  Its data.  It isn't supposed to
be manipulated in the way you want to do in this thread.

Displaying something in a particular order is done at the application
level, not the database level.  There is an an 'order by' clause, but your
use is somewhat invalid.  I've used "order by" statements that push things
either to the top or bottom of a list when I'm sorting alphabetically (IE:
select * from ComponentList order by Enabled=1,Caption;) but that is the
most advanced I've ever gotten as I know that the UI should be responsible
for dealing with an ordering issue.

"ID" fields should never be given to the user.  Its a way to identify that
a piece of information in your database relates directly to a UI element,
something in a list in memory, or whatever.  Its not a means to order by
anything, but a way to identify.  Its not a human consumable bit of
information, and shouldn't be managed by a user.

In situations where I need to put items in a certain order that isn't
alphabetical or numerical sorts (Beyond what I mentioned above), I dig into
linked lists and have my application code deal with displaying the
information in the order of that list.  The application also specifies the
relationship between the previous, current, and next item.

My application makes a simple select from the database, with no order
specified, asking for the items ID, its parent, and its child, and put it
into list of custom record sets or classed objects.   I loop through the
the list I pulled out looking for the item that has ParentID=0 and stake
claim that this is the top of the list.  Now, when I want to display
information from the list, I know where my first/top item is, and I know
where I need to go from there to get the rest of the list.  When ChildID=0,
I know I'm at the end of the list.

While working only in memory, if I need to insert information between two
items, I get the ParentID and ChildID from the item I want to insert
after.  I get a new ID either by inserting the record into the database to
get the last_insert_id or I look at the largest ID in my recordset, then +1
it.  I change the ChildID of the previous record to the new item ID, I
change the ParentID of the ChildID record to the new item ID, and then I
set the new item IDs parent and child IDs to the ID of the parent and child
respectively.  If I were using a class, a function would do this for me in
memory, and, write the changes to the database, then update the UI however
needed.



On Sun, Nov 19, 2017 at 3:37 PM, Shane Dev  wrote:

> Let's say I have a table of fruit -
>
> sqlite> .sch fruit
> CREATE TABLE fruit(id integer primary key, name text);
>
> with some entries -
>
> sqlite> select * from fruit;
> id|name
> 1|apple
> 2|pear
> 3|kiwi
>
> Is there an easy way to insert 'banana' between apple and pear while still
> maintaining a consistent order of the ID field?
>
> desired result -
>
> sqlite> select * from fruit;
> 1|apple
> 2|banana
> 3|pear
> 4|kiwi
> ___
> 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] Exit value of "PRAGMA integrity_check" in command line

[林自均]

Hi folks,

The document of "PRAGMA integrity_check" says:

> If the integrity_check pragma finds problems, strings are returned (as
multiple rows with a single column per row) which describe the problems.

But it doesn't specify the exit value of:

$ sqlite some-db.sqlite "PRAGMA integrity_check"

Usually, if the database corrupts, the exit value is non-zero. However, I
recently bumped into a corrupted sqlite file that makes the previous
command exit with zero.

Here is the execution result:

$ sqlite3 some-db.sqlite 'PRAGMA integrity_check'
*** in database main ***
Page 6153: btreeInitPage() returns error code 11
Page 6053: btreeInitPage() returns error code 11
Page 5953: btreeInitPage() returns error code 11
... (ignore similar lines)
Page 261: btreeInitPage() returns error code 11
Page 133: btreeInitPage() returns error code 11
Page 132: btreeInitPage() returns error code 11
Page 3 is never used
Page 4 is never used
Page 5 is never used
Page 6 is never used
... (ignore similar lines)
Page 38 is never used
Page 39 is never used
Page 40 is never used
Page 41 is never used
$ echo $?
0
$ sqlite3 some-db.sqlite 'SELECT * FROM task_status;'
Error: database disk image is malformed
$ echo $?
11

Thank you for your help!

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

Re: [sqlite] Bug

[Simon Slavin]

On 23 Nov 2017, at 12:40am, Ivan De La Cruz  
wrote:

> SQLiteDataAdapater is dropping characters after a space in the field when 
> filling a datatable (c# winforms).
> 
> I.e.
> Field   : 100 
> ml
> Select returns  : 100 
> ml
> DataTable column after Fill : 100
> If I add ' marks around the text: 
> '100 ml'

Is the column defined as numeric ?  If so, then SQLite is doing the right 
thing.  It is parsing the string and retrieving a number from it.

If not, please tell us which versions of which development tools you’re using 
so other people can reproduce your results.

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

[sqlite] Bug

[Ivan De La Cruz]

SQLiteDataAdapater is dropping characters after a space in the field when 
filling a datatable (c# winforms).

I.e.
Field   
   : 100 ml
Select returns  
  : 100 ml
DataTable column after Fill 
  : 100
If I add ' marks around the text
  : '100 ml'

My workaround for now is to add ' marks and then remove them after the 
datatable has been populated.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I recursively concatenate strings?

[Simon Slavin]

On 22 Nov 2017, at 9:56pm, Shane Dev  wrote:

> P.S I know that substr('x', 1, stringlengths.length) would work in
> this particular case but then I must know maximum value of
> stringlengths.length at the point of time when I construct the query. Is
> there a more flexible way?

Your above solution is the simplest fastest way of getting your result.  It 
will perform extremely quickly and continue to work if you ever decide to move 
from SQLite to some other SQL engine.  It is the solution I’d use unless the 
lengths could get above 200 or so.

A technically cleaner solution would be to implement your own external function 
which takes an integer and produces the appropriate string.  This would be a 
lot more work, and would stop working if you switched to some other SQL engine.

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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Simon Slavin]

On 22 Nov 2017, at 8:30pm, Shane Dev  wrote:

> Imagine I have a GUI element with a drop down list of fruit. The source of
> the list is my fruit table and it may have many entries. It might more
> convenient to list the popular fruit near the top. In that case the
> fruit.sort_order could represent relative popularity of the fruit entries.

Storing sort order is not a good way to do this.  If you want to list fruits in 
order of popularity do this:

CREATE TABLE fruits (id INTEGER PRIMARY KEY,
name TEXT COLLATE NOCASE,
numberSold INTEGER);
CREATE INDEX fruits_numberSold ON fruits (numberSold);

Do not mess with the ID value: never change it, never display it.  That’s just 
for the computer.

When a new fruit is introduced INSERT it with a value of 0 for numberSold.
When a fruit is sold, UPDATE its row to increase the numberSold value.

When you want your list do

SELECT name,numberSold FROM fruits ORDER BY numberSold DESC

You get your list in the order you want.  At no point are the order positions 
stored in the table.  The numbers mean nothing and can change at any minute, so 
it would be pointless to do so.

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

Re: [sqlite] Can I recursively concatenate strings?

[R Smith]

On 2017/11/22 11:56 PM, Shane Dev wrote:

Let's say I have a table of stringlengths -

sqlite>select * from stringlengths;
length
4
1
9
...

Can I create a view xstrings containing strings (for example of char 'x')
with the lengths specified in stringlengths?


Pretty easily:

  -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed 
version 2.0.2.4.


  -- Script Items: 5  Parameter Count: 0
  -- 2017-11-23 00:53:19.542  |  [Info]   Script Initialized, 
Started executing...
  -- 




CREATE TABLE SL(id INTEGER PRIMARY KEY, sLength INT);

INSERT INTO SL(sLength) VALUES (4),(1),(9),(72),(5);

SELECT * FROM SL;

  --  id  | sLength
  --  | ---
  --   1  |    4
  --   2  |    1
  --   3  |    9
  --   4  |    72
  --   5  |    5


CREATE VIEW xstrings AS
WITH SB(i,xs) AS (
  SELECT 0, ''
  UNION ALL
  SELECT i+1, xs||'x' FROM SB WHERE i<=(SELECT MAX(sLength) FROM SL)
)
SELECT xs
  FROM SL,SB
 WHERE SB.i = SL.sLength
 ORDER BY SL.id
;

SELECT * FROM xstrings;


  -- xs
  -- 


  -- 
  -- x
  -- x
  -- 


  -- x

  --   Script Stats: Total Script Execution Time: 0d 00h 00m and 
00.031s
  -- Total Script Query Time: 0d 00h 00m and 
00.001s

  -- Total Database Rows Changed: 5
  -- Total Virtual-Machine Steps: 2250
  -- Last executed Item Index:    5
  -- Last Script Error:
  -- 





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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Keith Medcalf]

>> Hi, Shane,

>> What I don't understand is why do you need to do that?

>Imagine I have a GUI element with a drop down list of fruit. The
>source of
>the list is my fruit table and it may have many entries. It might
>more
>convenient to list the popular fruit near the top. In that case the
>fruit.sort_order could represent relative popularity of the fruit
>entries.

Would not it simply make more sense then to have the table defined thusly:

create table popularities
(
  id integer primary key,
  description text collate nocase unique
);
insert into popularities(0, 'Uncommon Fruits');

create table fruits
(
  id integer primary key,
  popularity integer not null default (0) references popularities,
  fruit  text not null collate nocase unique
);
create unique index fruitorder on fruits (popularity desc, fruit);

Then merely set the "popularity" to the "zone" in which you want the fruit to 
appear (the higher the number the higher group up the list, and still in 
life-form recognizable scanning order within each zone) -- after creating the 
popularities zone of course so that you can label those groupings.

It would cause me to delete your application immediately if it did not sort 
entries into alphabetical order for quick location but instead used some 
addle-minded method of ordering that was illogical and not conducive to 
immediate recognition.  Trust me -- most life forms in the multiverse will see 
this exactly the same way.

select popularity, fruit from fruits order by popularity desc, fruit;




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

Re: [sqlite] Many ML emails going to GMail's SPAM

[jose isaias cabrera]

On  Wednesday, November 22, 2017 10:32 AM, Wout Mertens wrote...


In SO you have very little socialization going on. This mailinglist is


GUILTY!  And I am not Dominique. :-) This is the only mailing list that I am 
subscribed twice. There is so much knowledge in here, that it should be kept 
intact.  We probably have resolved many of the world's problems.  I joined 
back in 2006 when my boss said, "Jose, we need to know..." and I googled 
"easy to use sql serverless system" and the rest is history.  So, this is my 
socialization with the world. I don't belong to of those social sites/spots. 
This is my social spot.  I feel like I know each one of you.  By the way, I 
want to take this time to thank Dr. Hipp and the team, and you guys for all 
the knowledge you have provided in this last 11+ years.


josé


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

[sqlite] Can I recursively concatenate strings?

[Shane Dev]

Let's say I have a table of stringlengths -

sqlite>select * from stringlengths;
length
4
1
9
...

Can I create a view xstrings containing strings (for example of char 'x')
with the lengths specified in stringlengths?

desired result -

sqlite>select * from xstrings;
string

x

...

P.S I know that substr('x', 1, stringlengths.length) would work in
this particular case but then I must know maximum value of
stringlengths.length at the point of time when I construct the query. Is
there a more flexible way?


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

Re: [sqlite] Many ML emails going to GMail's SPAM

[jose isaias cabrera]

This one.  That one.  Esta. Aquella. :-)


-Original Message- 
From: Peter Da Silva

Sent: Wednesday, November 22, 2017 8:12 AM
To: SQLite mailing list
Subject: Re: [sqlite] Many ML emails going to GMail's SPAM

On 11/21/17, 9:54 PM, "sqlite-users on behalf of jose isaias cabrera" 
 wrote:

But, whatever it is, I will be part of the next phase of communication.


This.

___
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 into insert row into middle of table with integer primary key

[Igor Korot]

Hi,

On Wed, Nov 22, 2017 at 2:30 PM, Shane Dev  wrote:
> On 22 November 2017 at 17:08, Igor Korot  wrote:
>
>> Hi, Shane,
>>
>>
>> What I don't understand is why do you need to do that?
>>
>
> Imagine I have a GUI element with a drop down list of fruit. The source of
> the list is my fruit table and it may have many entries. It might more
> convenient to list the popular fruit near the top. In that case the
> fruit.sort_order could represent relative popularity of the fruit entries.
>
> Database idea is to store the data and then retrieve them in any way
>> you want at any given time.
>>
>> So all you need to know that there is a record inserted into the table
>> "fruit" on the schema
>> "garden".
>> Then when the time comes by you can retrieve the records with the
>> "ORDER BY" clause.
>> Whether you will sort the data by alphabet - fruit_name" or by number
>> increment - "Sort_order"
>> doesn't really matter.
>> Inserting the record is an implementation detail which shouldn't
>> bother you at all.
>>
>
> Actually, it interests me. If I knew insertions and updates in the fruit
> table were mostly for unpopular fruits, then  Peter Nichvolodov's trigger
> solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
> might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL
> solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106865.html)
> might more be efficient. However, querying may be slower.

Then have a popularity column in the table and update it with every single hit
using trigger.
Then do the query to fill out you list with "ORDER BY popularity".

Once again - how the records are inserted is implementation detail which
shouldn't be of the concern in any situations.

Thank you.

P.S.: Basically you are trying to create a problem where there is no problem
and a nice and simple solution.

>
>
>> Unless you can sow us that the time required to retrieve the
>> sorting data will SIGNIICANTLY
>> differ in both cases.
>>
>> I am ready to hear arguments against this approach. ;-)
>>
>> Thank you.
>>
>> >
>> >
>> > On 22 November 2017 at 00:11, Igor Korot  wrote:
>> >
>> >> Simon,
>> >>
>> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
>> >> wrote:
>> >> >
>> >> >
>> >> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
>> >> >
>> >> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>> >> >>>
>> >> >>> That assumes you are not starting from an integer part (like 4000)
>> and
>> >> hitting the exact same relative insert spot every time, which /can/
>> happen,
>> >> but is hugely unlikely.
>> >> >>
>> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
>> >> say you sort rows by date. You’ve already got some entries from 2015 in
>> >> your database, and some from 2017. Someone now inserts 60 entries from
>> >> 2016, and to be ‘helpful’, they insert them in chronological order.
>> Wham,
>> >> this immediately hits that case.
>> >> >
>> >> > Yes, if you use this method, you do need to renumber them every so
>> >> often.  You assess this when you’re working out (before + after) / 2,
>> and
>> >> you do it using something like the double-UPDATE command someone came up
>> >> with earlier.
>> >> >
>> >> > But that just brings us back to the question of why OP wants to store
>> ID
>> >> numbers which might change.
>> >>
>> >> Homework exercise?
>> >> Stupid requirements?
>> >>
>> >> Thank you.
>> >>
>> >> >
>> >> > Simon.
>> >> > ___
>> >> > 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
>>
> ___
> 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 into insert row into middle of table with integer primary key

[Shane Dev]

On 22 November 2017 at 17:08, Igor Korot  wrote:

> Hi, Shane,
>
>
> What I don't understand is why do you need to do that?
>

Imagine I have a GUI element with a drop down list of fruit. The source of
the list is my fruit table and it may have many entries. It might more
convenient to list the popular fruit near the top. In that case the
fruit.sort_order could represent relative popularity of the fruit entries.

Database idea is to store the data and then retrieve them in any way
> you want at any given time.
>
> So all you need to know that there is a record inserted into the table
> "fruit" on the schema
> "garden".
> Then when the time comes by you can retrieve the records with the
> "ORDER BY" clause.
> Whether you will sort the data by alphabet - fruit_name" or by number
> increment - "Sort_order"
> doesn't really matter.
> Inserting the record is an implementation detail which shouldn't
> bother you at all.
>

Actually, it interests me. If I knew insertions and updates in the fruit
table were mostly for unpopular fruits, then  Peter Nichvolodov's trigger
solution (
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL
solution (
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106865.html)
might more be efficient. However, querying may be slower.


> Unless you can sow us that the time required to retrieve the
> sorting data will SIGNIICANTLY
> differ in both cases.
>
> I am ready to hear arguments against this approach. ;-)
>
> Thank you.
>
> >
> >
> > On 22 November 2017 at 00:11, Igor Korot  wrote:
> >
> >> Simon,
> >>
> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> >> wrote:
> >> >
> >> >
> >> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >> >
> >> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >> >>>
> >> >>> That assumes you are not starting from an integer part (like 4000)
> and
> >> hitting the exact same relative insert spot every time, which /can/
> happen,
> >> but is hugely unlikely.
> >> >>
> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> >> say you sort rows by date. You’ve already got some entries from 2015 in
> >> your database, and some from 2017. Someone now inserts 60 entries from
> >> 2016, and to be ‘helpful’, they insert them in chronological order.
> Wham,
> >> this immediately hits that case.
> >> >
> >> > Yes, if you use this method, you do need to renumber them every so
> >> often.  You assess this when you’re working out (before + after) / 2,
> and
> >> you do it using something like the double-UPDATE command someone came up
> >> with earlier.
> >> >
> >> > But that just brings us back to the question of why OP wants to store
> ID
> >> numbers which might change.
> >>
> >> Homework exercise?
> >> Stupid requirements?
> >>
> >> Thank you.
> >>
> >> >
> >> > Simon.
> >> > ___
> >> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
> Behalf Of Keith Medcalf
> Sent: Wednesday, November 22, 2017 11:33 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Many ML emails going to GMail's SPAM
>
> >> There is nothing wrong with email - but there is an awful lot wrong
> >> with gnail and Google's ideas on how email is done.  (Not to mention
> >> Yahoo, but it seems that MS have the sense to leave the underpinnings
> >> of hotmail as they were.)
>
> >> To put it simply - friends don't let friends use gmail.
>
> >> Cheers,
> >> GaryB-)
>
> >Short of running my own server, what do you recommend? After losing my
> >lifelong email provider (Suffolk.lib.ny.us) and trying a few others, I
> >eventually settled for gmail, which I use with Thunderbird.
> >I don't have any significant issues with that setup.
>
> And that is OK, as long as you realize that it is quite possible for the 
> e-mail
> provider to be the one causing the issues and that they are not inherent in e-
> mail itself.  E-mail adds very little in the way of non-pre-existing 
> conditions
> that did not exist before the advent of e-mail -- the only real difference
> being that one does not need to pay postage to send e-mail.
>
> I run my own e-mail server and have since before there was an "Internet" as
> such and still do.  It has moved locations and data centers many times over
> the last almost four decades but it has always been mine.  I do have trust
> issues with third-parties so perhaps that is part of the reason.  More likely 
> is
> that it was interesting to set up way back in the later part of the 80's and 
> early
> 90's and there was no reason to discontinue using it.  It does cost a few
> dollars a month to maintain the infrastructure and does require care and
> feeding (particularly security feeding), from time to time, however, mostly 
> all
> the running and maintenance is automated, as it should be.  Do something
> manually maybe once or twice.  If it needed doing twice then it should be
> automated so you don't have to do it manually again.  The exceptions
> happen to be major OS or software upgrades which I prefer to do by hand
> since they only occur with relative infrequency and may have/cause "other
> issues".
>
> Periodically I have to re-jig some of the security to counter new tactics
> employed by the variety of miscreants out there in the world, but that does
> not really need to be done that often at this point (which is really 
> interesting
> because as you figure out one method and appropriate countermeasures,
> another technique that was hidden in the noise becomes exposed -- this can
> really be a really entertaining process).  The fact that a few (4 or 5) spam 
> e-
> mail slip through per day, and that the automation tells me that an equal
> number of new miscreants have been banished per day, plus the daily
> automated log audits lets me know that everything is working properly.
>

Thanks for the interesting reply. What you describe is exactly what I envision, 
which is why I never ran my own server (although I have been very tempted). I 
have enough hobbies for now.

-Bill
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how into insert row into middle of table with integer primary key

[Chris Locke]

> Why do I want store ID numbers
> whose values may change? Why not.

Because that's not what the row id column is for. Not strictly. That's why
it's called 'id' - it's an identification field. You can't (shouldn't) be
using it for other means. A database requirement later might need that
column to link to another table. Create the database properly and use the
columns properly.



Thanks,
Chris

On 22 Nov 2017 6:40 am, "Shane Dev"  wrote:

Hi Igor,

Homework exercise? No, this is purely a hobby project in my free time. My
goal is see how much logic can moved from application code to the database.

Why do I want store ID numbers whose values may change? Why not. Obviously,
this would be bad idea if the ID column was referenced by other column /
table. In that case, I would have created a different table such as

sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, sort integer unique, name text);

However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even if I use the stepped approach (fruit.sort
= 100, 200, 300 ...) or define sort as real unique, I will still need to
determine if it is necessary to reset the gaps between sort column values.
Peter Nichvolodov's trigger solution (
https://www.mail-archive.com/sqlite-users@mailinglists.
sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.


On 22 November 2017 at 00:11, Igor Korot  wrote:

> Simon,
>
> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> wrote:
> >
> >
> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >
> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >>>
> >>> That assumes you are not starting from an integer part (like 4000) and
> hitting the exact same relative insert spot every time, which /can/
happen,
> but is hugely unlikely.
> >>
> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> say you sort rows by date. You’ve already got some entries from 2015 in
> your database, and some from 2017. Someone now inserts 60 entries from
> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> this immediately hits that case.
> >
> > Yes, if you use this method, you do need to renumber them every so
> often.  You assess this when you’re working out (before + after) / 2, and
> you do it using something like the double-UPDATE command someone came up
> with earlier.
> >
> > But that just brings us back to the question of why OP wants to store ID
> numbers which might change.
>
> Homework exercise?
> Stupid requirements?
>
> Thank you.
>
> >
> > Simon.
> > ___
> > 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] Many ML emails going to GMail's SPAM

[Niall O'Reilly]

On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote:

> Please, not a forum. The email list is instant, dynamic, and convenient. I 
> don't think checking into a forum to stay current with the brisk activity 
> here is very practical or appealing.

I agree with Bill on this.

It seems to me that the idea of re-architecting such a useful communications
channel as this mailing list on account of a cluster of false positives raised
by a single provider's triage system would best be characterized as an example
of "the tail wagging the dog".

I use this provider's service for the major bulk of my e-mail because the
university where I used to work, which provides a continued e-mail service
to retirees, long ago outsourced its previously in-house e-mail system,
which I once had a hand in running, to Google.

In my experience, this provider's triage system does a pretty good job,
with very few false positives.  I see the current high incidence of
mis-classification of messages received through the SQLite mailing list
as an aberration.

Since the triage system is open to tuning by each recipient for their own
incoming mail, I suggest that all that is needed is for each subscriber to
this list who depends (as I do) on GMail for their mail feed, to apply this
tuning for themselves.

I found instructions here: https://support.google.com/mail/answer/6579 and
have now set up the following filter:

  Matches: to:(sqlite-users@mailinglists.sqlite.org)
  Do this: Never send it to Spam


Best regards,

Niall O'Reilly


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Igor Korot]

Hi,

On Wed, Nov 22, 2017 at 10:53 AM, jungle Boogie  wrote:
> On 22 November 2017 at 07:56, Igor Korot  wrote:
>> Hi,
>> Postgres very recently switched to PGLister for their ML
>>
>> This software switch tries to do exactly that - it tries to stay
>> complaint with all this DMARC stuff.
>>
>> Here is the announcement that was posted on their wiki page:
>> https://wiki.postgresql.org/wiki/PGLister_Announce.
>>
>
> Not a bad consideration. Where's the documentation on the software,
> though? Is it open source/free?

I don't know. I just subscribed to the PG ML general and got that notification.
If there is an interest I can try to ask or probably Mr Hipp can do that imself
and give more info.

One thing that I don't like is that the nice footer I see in the SQLite MLas:

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

will be gone. But this is the only downside as it stands right now
from my perspective as a
user.

Thank you.

> ___
> 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] Many ML emails going to GMail's SPAM

[dmp]

Well I think some have pointed out the issues with a online web
forum, logging in, lack of email notifications?

Mailing lists are one aspect of the Internet that in the last
25yrs has not disappointed me. Keeping the mailing list seems
to work or the irc option perhaps.

With that being said I would really like the Internet too take
a major change, to a model that is more distributed instead of
web server oriented. Something more like Steam.

With that in mind I have been working on a framework, and I'm
now trying to come up with a mechanism for I guess might be called
a forum of some type. One way or another I will implement something
and would be willing to work/experiment on this for perhaps a
solution that might meet communications needs of the list.

Dana Proctor
http://dandymadeproductions.com/projects/lindyFrame/lindyFrame_about.html

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[jungle Boogie]

On 22 November 2017 at 07:56, Igor Korot  wrote:
> Hi,
> Postgres very recently switched to PGLister for their ML
>
> This software switch tries to do exactly that - it tries to stay
> complaint with all this DMARC stuff.
>
> Here is the announcement that was posted on their wiki page:
> https://wiki.postgresql.org/wiki/PGLister_Announce.
>

Not a bad consideration. Where's the documentation on the software,
though? Is it open source/free?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Keith Medcalf]

>> There is nothing wrong with email - but there is an awful lot wrong
>> with gnail and Google's ideas on how email is done.  (Not to 
>> mention Yahoo, but it seems that MS have the sense to leave the 
>> underpinnings of hotmail as they were.)

>> To put it simply - friends don't let friends use gmail.

>> Cheers,
>> GaryB-)

>Short of running my own server, what do you recommend? After losing
>my lifelong email provider (Suffolk.lib.ny.us) and trying a few
>others, I eventually settled for gmail, which I use with Thunderbird.
>I don't have any significant issues with that setup.

And that is OK, as long as you realize that it is quite possible for the e-mail 
provider to be the one causing the issues and that they are not inherent in 
e-mail itself.  E-mail adds very little in the way of non-pre-existing 
conditions that did not exist before the advent of e-mail -- the only real 
difference being that one does not need to pay postage to send e-mail.

I run my own e-mail server and have since before there was an "Internet" as 
such and still do.  It has moved locations and data centers many times over the 
last almost four decades but it has always been mine.  I do have trust issues 
with third-parties so perhaps that is part of the reason.  More likely is that 
it was interesting to set up way back in the later part of the 80's and early 
90's and there was no reason to discontinue using it.  It does cost a few 
dollars a month to maintain the infrastructure and does require care and 
feeding (particularly security feeding), from time to time, however, mostly all 
the running and maintenance is automated, as it should be.  Do something 
manually maybe once or twice.  If it needed doing twice then it should be 
automated so you don't have to do it manually again.  The exceptions happen to 
be major OS or software upgrades which I prefer to do by hand since they only 
occur with relative infrequency and may have/cause "other issues".

Periodically I have to re-jig some of the security to counter new tactics 
employed by the variety of miscreants out there in the world, but that does not 
really need to be done that often at this point (which is really interesting 
because as you figure out one method and appropriate countermeasures, another 
technique that was hidden in the noise becomes exposed -- this can really be a 
really entertaining process).  The fact that a few (4 or 5) spam e-mail slip 
through per day, and that the automation tells me that an equal number of new 
miscreants have been banished per day, plus the daily automated log audits lets 
me know that everything is working properly.




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

Re: [sqlite] how into insert row into middle of table with integer primary key

[Igor Korot]

Hi, Shane,

On Wed, Nov 22, 2017 at 12:40 AM, Shane Dev  wrote:
> Hi Igor,
>
> Homework exercise? No, this is purely a hobby project in my free time. My
> goal is see how much logic can moved from application code to the database.
>
> Why do I want store ID numbers whose values may change? Why not. Obviously,
> this would be bad idea if the ID column was referenced by other column /
> table. In that case, I would have created a different table such as
>
> sqlite> .sch fruit
> CREATE TABLE fruit(id integer primary key, sort integer unique, name text);
>
> However, this just moves the problem from the id to the sort column. I
> still have to consider how to manage changes to values in the sort column.
> Apparently there is no single SQL statement which can insert a record in to
> any arbitrary sort position. Even if I use the stepped approach (fruit.sort
> = 100, 200, 300 ...) or define sort as real unique, I will still need to
> determine if it is necessary to reset the gaps between sort column values.
> Peter Nichvolodov's trigger solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
> is elegant, but might be slow if the table had many entries.

What I don't understand is why do you need to do that?
Database idea is to store the data and then retrieve them in any way
you want at any given time.

So all you need to know that there is a record inserted into the table
"fruit" on the schema
"garden".
Then when the time comes by you can retrieve the records with the
"ORDER BY" clause.
Whether you will sort the data by alphabet - fruit_name" or by number
increment - "Sort_order"
doesn't really matter.
Inserting the record is an implementation detail which shouldn't
bother you at all.

Unless you can sow us that the time required to retrieve the
sorting data will SIGNIICANTLY
differ in both cases.

I am ready to hear arguments against this approach. ;-)

Thank you.

>
>
> On 22 November 2017 at 00:11, Igor Korot  wrote:
>
>> Simon,
>>
>> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
>> wrote:
>> >
>> >
>> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
>> >
>> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>> >>>
>> >>> That assumes you are not starting from an integer part (like 4000) and
>> hitting the exact same relative insert spot every time, which /can/ happen,
>> but is hugely unlikely.
>> >>
>> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
>> say you sort rows by date. You’ve already got some entries from 2015 in
>> your database, and some from 2017. Someone now inserts 60 entries from
>> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
>> this immediately hits that case.
>> >
>> > Yes, if you use this method, you do need to renumber them every so
>> often.  You assess this when you’re working out (before + after) / 2, and
>> you do it using something like the double-UPDATE command someone came up
>> with earlier.
>> >
>> > But that just brings us back to the question of why OP wants to store ID
>> numbers which might change.
>>
>> Homework exercise?
>> Stupid requirements?
>>
>> Thank you.
>>
>> >
>> > Simon.
>> > ___
>> > 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] Many ML emails going to GMail's SPAM

[Igor Korot]

Hi,
Postgres very recently switched to PGLister for their ML

This software switch tries to do exactly that - it tries to stay
complaint with all this DMARC stuff.

Here is the announcement that was posted on their wiki page:
https://wiki.postgresql.org/wiki/PGLister_Announce.

Since SQLite follows PostgreSQL maybe we should have something similar?


Just my $0.02.

On Wed, Nov 22, 2017 at 9:44 AM, Drago, William @ CSG - NARDA-MITEQ
 wrote:
>> There is nothing wrong with email - but there is an awful lot wrong with 
>> gnail
>> and Google's ideas on how email is done.  (Not to mention Yahoo, but it
>> seems that MS have the sense to leave the underpinnings of hotmail as they
>> were.)
>>
>> To put it simply - friends don't let friends use gmail.
>>
>> Cheers,
>> GaryB-)
>
> Short of running my own server, what do you recommend? After losing my 
> lifelong email provider (Suffolk.lib.ny.us) and trying a few others, I 
> eventually settled for gmail, which I use with Thunderbird. I don't have any 
> significant issues with that setup.
>
> --
> Bill Drago
> Staff Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / william.dr...@l3t.com
>
> CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use 
> of the intended recipient and may contain material that is proprietary, 
> confidential, privileged or otherwise legally protected or restricted under 
> applicable government laws. Any review, disclosure, distributing or other use 
> without expressed permission of the sender is strictly prohibited. If you are 
> not the intended recipient, please contact the sender and delete all copies 
> without reading, printing, or saving.
>
> Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of 
> all @L-3Com.com email addresses. To ensure delivery of your messages to this 
> recipient, please update your records to use william.dr...@l3t.com.
> ___
> 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] Many ML emails going to GMail's SPAM

[Drago, William @ CSG - NARDA-MITEQ]

> There is nothing wrong with email - but there is an awful lot wrong with gnail
> and Google's ideas on how email is done.  (Not to mention Yahoo, but it
> seems that MS have the sense to leave the underpinnings of hotmail as they
> were.)
>
> To put it simply - friends don't let friends use gmail.
>
> Cheers,
> GaryB-)

Short of running my own server, what do you recommend? After losing my lifelong 
email provider (Suffolk.lib.ny.us) and trying a few others, I eventually 
settled for gmail, which I use with Thunderbird. I don't have any significant 
issues with that setup.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com

CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of 
the intended recipient and may contain material that is proprietary, 
confidential, privileged or otherwise legally protected or restricted under 
applicable government laws. Any review, disclosure, distributing or other use 
without expressed permission of the sender is strictly prohibited. If you are 
not the intended recipient, please contact the sender and delete all copies 
without reading, printing, or saving.

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all 
@L-3Com.com email addresses. To ensure delivery of your messages to this 
recipient, please update your records to use william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Keith Medcalf]

On Wednesday, 22 November, 2017 04:47, Richard Damon  
wrote:

>There is a fundamental problem with the email system that it goes
>back to a kinder and gentler time, and it is trivial to spoof most 
>mail.

Including good old-fashioned snail mail of course.  There is also no way to 
determine the "identity" of the sender of postal mail either.  Both the 
envelope addresses and the inside addresses can be forgeries (same as they can 
with e-mail).  In both cases only the postmark (or Received headers) are all 
that can be reliably determined.

However, it is generally immediately noticeable if the envelope-from, inside, 
and signature's on snail mail are inconsistent and the postmark indicates the 
originating post office was is Boogaloo rather than Dumphries.  This 
information is not commonly displayed for e-mail.  SPF is the e-mail equivalent 
of matching the envelope-from to the sending MTA (post office).  DKIM adds to 
this by cryptographic assurance of the inside and body to the sending MTA (post 
office).  DMARC is simply to address the issue that DKIM signatures cannot be 
verified until after the recipient has taken custody of the message rather than 
rejecting the receipt of the message in the first place.  DMARC is the 
electronic replacement of the old-fashioned dustbin.

>SPF/DKIM/DMARC are part of the attempt to fix this, and I think the
>developers of those understand they have just started. The issue is
>that
>some others have taken these beginnings and deployed it outside the
>intended sphere where there are issues still to be resolved.




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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Wout Mertens]

In SO you have very little socialization going on. This mailinglist is
awesome because it is filled with people who will enthousiastically share
very detailed information, each in their own way. On SO there is no
long-term narrative.

I subscribed to this mailing list because I needed help, and I didn't
unsubscribe because of the interesting threads. With SO you have to be
dedicated to follow a content stream.

In my experience, Discourse offers a good balance between being chat-like
and mailinglist-like, while also storing the collected wisdom for
posterity. It does get indexed by search engines, see for example
https://www.google.pl/search?q=site:forum.waytools.com or
https://www.bing.com/search?q=site%3aforum.waytools.com

Indeed though, it requires JS to be enabled to post, but you can set up
reply by email:
https://meta.discourse.org/t/new-reply-via-email-support/7764

On Wed, Nov 22, 2017 at 3:36 PM Dominique Devienne 
wrote:

> On Wed, Nov 22, 2017 at 3:08 PM, Wout Mertens 
> wrote:
>
> > One more reason for some forum vs a mailing list: You can "like" a post
> > without spamming everyone, thus showing your appreciation to the poster
> and
> > surfacing interesting content for summarization algorithms.
>
>
> Or then reputation points can build up too.
> But then you're quickly converging on stack-overflow...
>
> In fact, if there wasn't this high quality high responsiveness mailing
> list,
> the SQLite stack overflow subgroup would be better/faster I suspect. --DD
> ___
> 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] Many ML emails going to GMail's SPAM

[Dominique Devienne]

On Wed, Nov 22, 2017 at 3:08 PM, Wout Mertens 
wrote:

> One more reason for some forum vs a mailing list: You can "like" a post
> without spamming everyone, thus showing your appreciation to the poster and
> surfacing interesting content for summarization algorithms.


Or then reputation points can build up too.
But then you're quickly converging on stack-overflow...

In fact, if there wasn't this high quality high responsiveness mailing list,
the SQLite stack overflow subgroup would be better/faster I suspect. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Wout Mertens]

One more reason for some forum vs a mailing list: You can "like" a post
without spamming everyone, thus showing your appreciation to the poster and
surfacing interesting content for summarization algorithms.

On Wed, Nov 22, 2017 at 2:13 PM Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> On 11/21/17, 9:54 PM, "sqlite-users on behalf of jose isaias cabrera" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of
> jic...@barrioinvi.net> wrote:
> > But, whatever it is, I will be part of the next phase of communication.
>
> This.
>
> ___
> 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 into insert row into middle of table with integer primary key

[Peter Da Silva]

On 11/22/17, 1:43 AM, "sqlite-users on behalf of R Smith" 
 
wrote:
> Oh there are many valid reasons why to have Order in data, one I use 
> regularly is to dictate the process flow in manufacturing where some thing 
> needs to go to machine Y before it can move on to machine X, or process E, 
> for a specific item, has to happen before process B etc.

That’s a partial ordering though, based on a dependency graph. You’d want to 
maintain the dependencies in the database as the ground truth, and when needed 
generate a topological ordering based on the dependencies. That can be 
maintained in some kind of cache table, but it’s not something that you would 
need to dynamically update like the OP but rather regenerate it when the 
dependencies change.
 

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Peter Da Silva]

On 11/21/17, 9:54 PM, "sqlite-users on behalf of jose isaias cabrera" 
 wrote:
> But, whatever it is, I will be part of the next phase of communication. 

This.

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

Re: [sqlite] Many ML emails going to GMail's SPAM

[J Decker]

Amongs all of these, I'd like to throw in that I'd like to see a gitter.im
channel for sqlite (as opposed to discord or slack).  gitter is much easier
to share code snippets on and is much faster.  It's also got nice
integration with github... although that's less important since sqlite
isn't hosted there.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[Richard Damon]

On 11/21/17 3:29 PM, Keith Medcalf wrote:

And checking SPF is pretty useful as well.  Once you have enforced strict 
compliance, however, the effect of SPF is negligible (less than 1/1000%).

DKIM/DMARC generally causes more trouble than it solves (it was designed by a 
committee of idiots after all) and should be mostly ignored other than for 
displaying a DKIM Signature Status in the mail reader interface.

Most of the problem is the horribly broken e-mail clients, none of which 
display useful information.  For those old enough to remember postal mail, it 
is like having a secretary that throws out the envelope and trims off most of 
the inside and signature information before giving you your mail.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

DMARC is actually works very well for its originally intended usage, the 
preventing of spoofs of important emails (like from banks). The key 
point is any domain that uses DMARC must not also be used with a 3rd 
party remailing system, like mailing-lists, The problem everyones has 
with DMARC is that the yahoos at Yahoo adopted it as a solution for 
their security breaches, and rather than tell their users that they have 
takens this action and they can not use mailing list or other remailing 
services, they told the world, yes, we broke email, we are big enough it 
is your job to fix the mess we created.


There is a fundamental problem with the email system that it goes back 
to a kinder and gentler time, and it is trivial to spoof most mail. 
SPF/DKIM/DMARC are part of the attempt to fix this, and I think the 
developers of those understand they have just started. The issue is that 
some others have taken these beginnings and deployed it outside the 
intended sphere where there are issues still to be resolved.


--
Richard Damon

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

Re: [sqlite] how into insert row into middle of table with integer primary key

[R Smith]

On 2017/11/22 8:40 AM, Shane Dev wrote:

However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even if I use the stepped approach (fruit.sort
= 100, 200, 300 ...) or define sort as real unique, I will still need to
determine if it is necessary to reset the gaps between sort column values.


So it's not so much a solution you are after, it's a quick and easy 
one-line-of-sql solution.


Sorry to inform you, that doesn't exist in any SQL engine, because order 
is not intrinsic to data and overwhelmingly often it is just not 
important inside the DB. In the same way integer values are not stored 
with thousand separators, because that is not important inside a DB, 
it's only humans that like to see things ordered and formatted, so the 
DB engine may let the output be controlled for format and order etc, but 
it doesn't maintain that sort of thing internally and as such have no 
integrated functionality to deal with it internally.


More importantly, there are a myriad ways to maintain the kinds of 
ordering we've discussed, some of them are more efficient in one kind of 
use case, and others are more efficient in other cases.
Why should the Database engine get to decide which to use? It should be 
your choice.


We often see here questions that indicate the poster was informed by 
friends or colleagues: "Oh you should use a DB, it's much quicker and 
easier", and while that is true in the long run, it is often mistaken to 
mean: "It's quicker and easier /for you to program/" - something that is 
also mostly true, but the real statement should read: "It's quicker and 
easier at /correctly handling data/".


Emphasis there on *correctly*. It doesn't offer quick and easy 
short-cuts where those do not also underpin a good data-handling 
practice.  Not a good DB engine anyway.
Put another way: It doesn't offer millions of tools you /may/ need in 
data-handling, it offers a few tolls you /will/ need and ensures those 
work 100% accurate and consistent so you don't have to care about that 
in your programming. The rest is up to you.



Cheers,
Ryan

PS: Yeah I know, adjust that figure to 99.% for the occasional bug 
perhaps. :)


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