Re: [sqlite] Fix for 32bit compilation on Solaris

[Bob Friesenhahn]

On Thu, 30 Nov 2017, Vladimir Marek wrote:


Hi,

Compilers shipped with Solaris were traditionally compiling 32bit
binaries unless specified otherwise. This changed recently, the default
is 64bit binaries. So if you want to compile 32bit object, you have to
specify -m32. That slightly breaks sqlite configuration script as it
expects 32bit output with no arguments. The fix is simple - specify
'-m32' for 32bit compilation. The change is backwards compatible, -m32
always meant 32bit objects. I am attaching the patch to latest sqlite
release.


The tcl.m4 file comes from the TEA package.  Sqlite could fix its copy 
but you should report this upstream from where it came from so that 
all Tcl extensions which update their TEA package will benefit.


See http://wiki.tcl.tk/327 and https://github.com/tcltk/tclconfig

Bob
--
Bob Friesenhahn
bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,http://www.GraphicsMagick.org/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key lint has issues with without rowid

[Dan Kennedy]

Thanks for reporting this. Now fixed here:

  http://www.sqlite.org/src/info/5771b1d611b3562e

Dan.


On 12/01/2017 02:02 AM, David Raymond wrote:

I’m using the CLI’s .lint fkey-indexes command, and it appears to be having 
issues with without rowid tables. A low priority thing to look into when 
someone's bored.


sqlite> create table parent (id integer primary key);

sqlite> create table child1 (id integer primary key, parentID int references 
parent);

sqlite> .lint fkey-indexes
CREATE INDEX 'child1_parentID' ON 'child1'('parentID'); --> parent(id)

sqlite> create index idx_child1 on child1 (parentID);

sqlite> .lint fkey-indexes

sqlite> create table child2 (id int primary key, parentID int references 
parent) without rowid;

sqlite> .lint fkey-indexes
no such column: rowid

sqlite> create index idx_child2 on child2 (parentID);

sqlite> .lint fkey-indexes
no such column: rowid

sqlite>
___
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] Automatic index, despite existing index?

[Olivier Mascia]

> Le 1 déc. 2017 à 15:00, Clemens Ladisch <clem...@ladisch.de> a écrit :
> 
> Olivier Mascia wrote:
>> 20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
>> 
>> Here is that part of the schema:
>> 
>> CREATE INDEX IX_REMINDER_USER on REMINDER(USER_LOGON);
>> 
>> Could it be that it might need a DESC index?
> 
> Probably not; the index direction usually does not matter.
> 
> But are you using COLLATE NOCASE (or LIKE)?

COLLATE is used nowhere, but sure, LIKE is liked by some people here.  I'll 
have to check the code closer.

If I'm following you, if there are lookups on REMINDER(USER_LOGON) using LIKE 
it somehow implies some COLLATE NOCASE and so would like such an index?

If so, there is an opportunity for enhancement in the logging.

Something like:
automatic index on REMINDER(USER_LOGON collate nocase)

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

Re: [sqlite] Automatic index on mi(ID) or tal(ID)

[Olivier Mascia]

> Le 1 déc. 2017 à 14:58, Clemens Ladisch  a écrit :
> 
> Olivier Mascia wrote:
>> I'm seeing such things in my error.log out of SQLite 3.21.0 (but I am sure 
>> it was already the case with 3.20):
>> 
>> automatic index on tal(ID)
>> automatic index on mi(ID)
>> ...
>> 
>> I do not have a clue as to what these 'tal(ID)' and 'mi(ID)' refer to.
> 
> "tal" and "mi" sound like table/subquery aliases.

Indeed it was.  Thanks Clemens!  I was too focused on checking the schema.
Did a full text search on the full source codes, and yes 'mi' and 'tal' have 
been used bob someone, for some subquery aliases.

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia



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

Re: [sqlite] Sqlite and docker performance question

[Sebastien HEITZMANN]

to close this subject I have some more information.

After a try on my ubuntu I notice that the différence doesn't exist. So I
ugraded the production HOST to à 4.4 linux kernel.

The timing is now the same on the host and in the container ( but both are
slower. snif ... )

fs tuning is not so easy.

Thanks all for your help.


2017-11-30 19:29 GMT+01:00 Scott Robison :

> Perhaps the file sync performed by SQLite is more expensive in the docker
> environment than in the host. That would make sense to me.
>
> On Nov 30, 2017 7:07 AM, "Sebastien HEITZMANN" <2...@2le.net> wrote:
>
> > In my last mail i have multiple table creation and index. It seam that
> the
> > overtime is for all the create statement.
> >
> > It really looks like a disk pb. But can't identify so much difference in
> my
> > direct disk access ( with DD )
> >
> > I will try it on an other host.
> >
> > 2017-11-30 14:59 GMT+01:00 Simon Slavin :
> >
> > > OP wrote:
> > >
> > > > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime
> INTEGER,
> > > > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> > >
> > > Is this the first content of a new file ?  If so, SQLite has to create
> > the
> > > file and write some structure information as well as writing the table.
> > I
> > > suspect that the time taken for the overhead is far more than the time
> > > taken for the CREATE command.
> > >
> > > Could you try changing f.sql to create ten tables ?  For example create
> > > the table "f1 as above then create tables "f2" to "f1" with the same
> > > columns ?  It would be interesting to see what this does to both
> timings.
> > >
> > > Simon.
> > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > *Sébastien HEITZMANN*
> > Gérant & Directeur technique
> > +33 (0)3 89 333 889
> > Plus d'info sur : www.2le.net
> >
> >
> >  > lentreprise/194148499368?ref=ts>
> >   
> > 
> > ___
> > 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
>



-- 
*Sébastien HEITZMANN*
Gérant & Directeur technique
+33 (0)3 89 333 889
Plus d'info sur : www.2le.net



  

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

Re: [sqlite] Automatic index, despite existing index?

[Simon Slavin]

On 1 Dec 2017, at 1:50pm, Olivier Mascia  wrote:

> Could it be that it might need a DESC index?

SQLite should not be doing that.  It understands that an index can be used 
"backwards" if it needs to reverse the sort order.

> Could the "automatic index on ..." in the error log be slightly enhanced to 
> give precision on *why* it needs the automatic index?
> Is there any way I missed in the API to get a pointer to the statement which 
> is executing while any or all of the messages are emitted?

The pseudo table names it’s using — tal and mi — suggest that SQLite is 
creating its own temporary tables in order to execute your functions.  If it 
was indexing real tables of yours it would give the real tablenames.

It does seem that that you need more information than you are getting.  What is 
creating your SQLite log ?  Are you using sqlite3_set_authorizer() ?

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

Re: [sqlite] Automatic index, despite existing index?

[R Smith]

My first guess (and I might be wrong) would be that you ORDER BY in some 
query on the USER_LOGON or use it with LIKE or such that it needs CASE 
INSENSITIVE or non-NULL Indexing or Unique indexing (GROUP BY - though 
unlikely that would be require that speciifc autoindex) or such.


If you enhance the Table definition to say:

...
  USER_LOGON text NOT NULL COLLATE NOCASE,
...


it might no longer need that Auto-Index - though that may not fit with 
your design criteria.


Logging the reason for an autoindex might be complex. What does Explain 
Query plan say on that Query that creates the logs? That should make it 
clear and is much better info than the logging provides.





On 2017/12/01 3:50 PM, Olivier Mascia wrote:

Hi all,

I'm also seeing such things in my SQLite log:

20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
20171201 120326.763 284: automatic index on REMINDER(USER_LOGON)
20171201 120326.763 284: automatic index on REMINDER(USER_LOGON)
20171201 120330.887 284: automatic index on REMINDER(USER_LOGON)
20171201 120330.887 284: automatic index on REMINDER(USER_LOGON)

Here REMINDER is actually a table and USER_LOGON a colomn).
Here is that part of the schema:

---
sqlite> .schema REMINDER
CREATE TABLE REMINDER(PKID integer primary key not null,
 CONTENT text,
 USER_LOGON text,
 ALARM boolean default 0,
 BELL_TS timestamp default (now()),
 FK_EVT integer,
 FK_MSG integer,
 FK_FAX integer,
 FOLDER_ID integer,
 FK_TIK integer,
 SENT_TS timestamp,
 foreign key(FK_EVT) references EVT(PKID) on update cascade on delete 
cascade,
 foreign key(FK_FAX) references EDOCUMENTS(PKID) on update cascade on 
delete cascade,
 foreign key(FK_MSG) references MSG(PKID) on update cascade on delete 
cascade);
CREATE INDEX IFK_REMINDER_EVT on REMINDER(FK_EVT);
CREATE INDEX IFK_REMINDER_DOC on REMINDER(FK_FAX);
CREATE INDEX IFK_REMINDER_MSG on REMINDER(FK_MSG);
CREATE INDEX IX_REMINDER_USER on REMINDER(USER_LOGON);
CREATE TRIGGER TR_REMINDERINSERT after insert on REMINDER begin select 
signal_reminder();end;
CREATE TRIGGER TR_REMINDERDELETE after delete on REMINDER begin select 
signal_reminder();end;
---

Could it be that it might need a DESC index?
Could the "automatic index on ..." in the error log be slightly enhanced to 
give precision on *why* it needs the automatic index?
Is there any way I missed in the API to get a pointer to the statement which is 
executing while any or all of the messages are emitted?



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

Re: [sqlite] Automatic index, despite existing index?

[Clemens Ladisch]

Olivier Mascia wrote:
> 20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
>
> Here is that part of the schema:
>
> CREATE INDEX IX_REMINDER_USER on REMINDER(USER_LOGON);
>
> Could it be that it might need a DESC index?

Probably not; the index direction usually does not matter.

But are you using COLLATE NOCASE (or LIKE)?


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

Re: [sqlite] Automatic index on mi(ID) or tal(ID)

[Clemens Ladisch]

Olivier Mascia wrote:
> I'm seeing such things in my error.log out of SQLite 3.21.0 (but I am sure it 
> was already the case with 3.20):
>
> automatic index on tal(ID)
> automatic index on mi(ID)
> ...
>
> I do not have a clue as to what these 'tal(ID)' and 'mi(ID)' refer to.

"tal" and "mi" sound like table/subquery aliases.


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

[sqlite] Automatic index, despite existing index?

[Olivier Mascia]

Hi all,

I'm also seeing such things in my SQLite log:

20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
20171201 120319.404 284: automatic index on REMINDER(USER_LOGON)
20171201 120326.763 284: automatic index on REMINDER(USER_LOGON)
20171201 120326.763 284: automatic index on REMINDER(USER_LOGON)
20171201 120330.887 284: automatic index on REMINDER(USER_LOGON)
20171201 120330.887 284: automatic index on REMINDER(USER_LOGON)

Here REMINDER is actually a table and USER_LOGON a colomn).
Here is that part of the schema:

---
sqlite> .schema REMINDER
CREATE TABLE REMINDER(PKID integer primary key not null,
CONTENT text,
USER_LOGON text,
ALARM boolean default 0,
BELL_TS timestamp default (now()),
FK_EVT integer,
FK_MSG integer,
FK_FAX integer,
FOLDER_ID integer,
FK_TIK integer,
SENT_TS timestamp,
foreign key(FK_EVT) references EVT(PKID) on update cascade on delete 
cascade,
foreign key(FK_FAX) references EDOCUMENTS(PKID) on update cascade on 
delete cascade,
foreign key(FK_MSG) references MSG(PKID) on update cascade on delete 
cascade);
CREATE INDEX IFK_REMINDER_EVT on REMINDER(FK_EVT);
CREATE INDEX IFK_REMINDER_DOC on REMINDER(FK_FAX);
CREATE INDEX IFK_REMINDER_MSG on REMINDER(FK_MSG);
CREATE INDEX IX_REMINDER_USER on REMINDER(USER_LOGON);
CREATE TRIGGER TR_REMINDERINSERT after insert on REMINDER begin select 
signal_reminder();end;
CREATE TRIGGER TR_REMINDERDELETE after delete on REMINDER begin select 
signal_reminder();end;
---

Could it be that it might need a DESC index?
Could the "automatic index on ..." in the error log be slightly enhanced to 
give precision on *why* it needs the automatic index?
Is there any way I missed in the API to get a pointer to the statement which is 
executing while any or all of the messages are emitted?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia, http://integral.software


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

[sqlite] Automatic index on mi(ID) or tal(ID)

[Olivier Mascia]

Hi all,

I'm seeing such things in my error.log out of SQLite 3.21.0 (but I am sure it 
was already the case with 3.20):

automatic index on tal(ID)
automatic index on mi(ID)
automatic index on tal(ID)
automatic index on mi(ID)
automatic index on mi(ID)
automatic index on mi(ID)
automatic index on tal(ID)
automatic index on mi(ID)
automatic index on tal(ID)
...

I do not have a clue as to what these 'tal(ID)' and 'mi(ID)' refer to.  I 
apparently have nothing in the schema coming close to that.

Does someone sees something similar?
What is it?

(Not that I suffer from any abnormal behaviour, it just caught my eye).

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Ignore missing UDFs for command-line EXPLAIN?

[nomad]

On Wed Nov 29, 2017 at 01:57:29PM +, David Raymond wrote:
> http://www.sqlite.org/compile.html
> 
> SQLITE_ENABLE_UNKNOWN_SQL_FUNCTION

Exactly what I was looking for, just in the wrong places. Thanks David.

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