[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules(revisited)

[a...@zator.com]

>
>  Mensaje original 
> De: Dan Kennedy 
> Para:  sqlite-users at mailinglists.sqlite.org
> Fecha:  Fri, 11 Dec 2015 02:41:43 +0700
> Asunto:  Re: [sqlite] Problem when upgrading from FTS3/4 to FTS5 
> modules(revisited)
>
>>
...
>> 1a.- Delete the previous table.
>> DROP TABLE IF EXIST ftsm
>>
>> This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives 
>> an erroro in 1a:  "database disk image is malformed".
>>
>> Note that in previous attemps I believed that the problem was into try to 
>> delete a ftsm table build with the previous modules, but the error happen 
>> when trying delete a table build with the FTS5 module.
>>
...
>>
>> Some clues?
>
>Not really sure why it might fail there. Can you post the entire 
>database schema (results of "SELECT * FROM sqlite_master" or the output 
>of the .schema shell tool command)?
>

Dan:

Here is the schema:

CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 remove_diacri
tics 0',columnsize=0);

And here the entire database schema as produced by the shell:

sqlite> SELECT * FROM sqlite_master;
table|usr|usr|2|CREATE TABLE usr (ky INTEGER PRIMARY KEY,id CHARACTER UNIQUE,lev
 INTEGER,pwd TEXT)
index|sqlite_autoindex_usr_1|usr|3|
table|block|block|4|CREATE TABLE block (Stat INTEGER,User INTEGER,Page TEXT,Text
 INTEGER)
table|FreqUse|FreqUse|5|CREATE TABLE FreqUse (Stat INTEGER,User INTEGER,Page TEX
T,Text INTEGER)
table|blb|blb|6|CREATE TABLE blb (Id INTEGER PRIMARY KEY,Nm INTEGER)
table|atm|atm|7|CREATE TABLE atm (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl INTEGER,D
c REAL,Dm REAL,St INTEGER)
table|coco|coco|8|CREATE TABLE coco (Id INTEGER PRIMARY KEY,Nm INTEGER,Cl INTEGE
R,Dc REAL,Dm REAL,St INTEGER)
table|lnk|lnk|9|CREATE TABLE lnk (So INTEGER NOT NULL,Ta INTEGER NOT NULL,Cl INT
EGER,Tpt INTEGER,UNIQUE (So,Ta,Cl),CHECK(typeof(So)='integer'),CHECK(typeof(Ta)=
'integer'),CHECK((typeof(Cl)='integer') OR (typeof(Cl)='null')))
index|sqlite_autoindex_lnk_1|lnk|10|
table|prm|prm|11|CREATE TABLE prm(ref INTEGER, val INTEGER, own INTEGER, UNIQUE(
ref, own))
index|sqlite_autoindex_prm_1|prm|12|
table|email|email|13|CREATE TABLE email (Id INTEGER PRIMARY KEY, Tit INTEGER, No
m INTEGER, Org INTEGER,eHnm INTEGER, ePort INTEGER, eUnm INTEGER, ePsw INTEGER,
eScon INTEGER, eDel INTEGER,sHnm INTEGER, sPort INTEGER, sUnm INTEGER, sPsw INTE
GER, sScon INTEGER,Enam INTEGER, Rnam INTEGER, Unam INTEGER, Onam INTEGER, iucs
INTEGER, sec1 INTEGER, sec2 INTEGER, sec3 INTEGER, sec4 INTEGER,Cl INTEGER, St I
NTEGER, aux1 INTEGER, aux2 INTEGER, aux3 INTEGER, aux4 INTEGER, aux5 INTEGER, au
x6 INTEGER, aux7 INTEGER)
view|AgVtHolder|AgVtHolder|0|CREATE VIEW AgVtHolder AS SELECT id FROM atm WHERE
id IN(SELECT so FROM lnk L WHERE L.ta=73 AND L.cl=47)
view|AgVtIDt|AgVtIDt|0|CREATE VIEW AgVtIDt AS SELECT L.ta AS 'Hd', C.nm AS 'Dt'
FROM atm C, lnk L WHERE C.cl=17 AND C.id IN (SELECT L.so FROM lnk L WHERE L.cl=4
8 AND L.ta IN(SELECT id FROM AgVtHolder)) AND L.so=C.id
view|AgVtPre|AgVtPre|0|CREATE VIEW AgVtPre AS SELECT L.ta AS 'Hd', CAST(Nm AS IN
T) AS 'Pr' FROM atm C, lnk L WHERE C.cl=17 AND C.id IN(SELECT so FROM lnk L WHER
E L.cl=49 AND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L W
HERE L.ta=73 AND L.cl=47))) AND L.So=C.id
view|AgVtos|AgVtos|0|CREATE VIEW AgVtos AS SELECT D.Hd AS 'Hd', D.Dt AS 'Dt', P.
Pr AS 'Pr' FROM AgVtIDt D, AgVtPre P WHERE P.Hd=D.Hd
view|AgPdHolder|AgPdHolder|0|CREATE VIEW AgPdHolder AS SELECT id FROM atm WHERE
id IN(SELECT So FROM lnk L WHERE L.ta=75 AND L.cl=53)
view|AgPdIDt|AgPdIDt|0|CREATE VIEW AgPdIDt AS SELECT L.ta AS 'Hd', C.Nm AS 'Dt'
FROM atm C, lnk L WHERE C.Cl=18 AND C.id IN (SELECT L.so FROM lnk L WHERE L.cl=5
4 AND L.ta IN(SELECT id FROM AgPdHolder)) AND L.so=C.id
view|AgEfHolder|AgEfHolder|0|CREATE VIEW AgEfHolder AS SELECT id FROM atm WHERE
id IN(SELECT So FROM lnk L WHERE L.ta=77 AND L.cl=59)
view|AgEfIDt|AgEfIDt|0|CREATE VIEW AgEfIDt AS SELECT L.ta AS 'Hd', C.Nm AS 'Dt'
FROM atm C, lnk L WHERE C.Cl=19 AND C.id IN (SELECT L.So FROM lnk L WHERE L.cl=6
0 AND L.ta IN(SELECT id FROM AgEfHolder)) AND L.So=C.id
view|AgEfKlv|AgEfKlv|0|CREATE VIEW AgEfKlv AS SELECT L.ta AS 'Hd', C.Nm AS 'Kl'
FROM atm C, lnk L WHERE C.cl=19 AND C.id IN(SELECT so FROM lnk L WHERE L.cl=61 A
ND L.ta IN (SELECT C.id FROM atm C WHERE id IN(SELECT so FROM lnk L WHERE L.ta=7
7 AND L.cl=59))) AND L.so=C.id
view|AgEfemer|AgEfemer|0|CREATE VIEW AgEfemer AS SELECT D.Hd AS 'Hd', D.Dt AS 'D
t', P.Kl AS 'Kl' FROM AgEfIDt D, AgEfKlv P WHERE P.Hd=D.Hd
table|ftsm|ftsm|0|CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unico
de61 remove_diacritics 0',columnsize=0)
table|ftsm_data|ftsm_data|11332|CREATE TABLE 'ftsm_data'(id INTEGER PRIMARY KEY,
 block BLOB)
table|ftsm_idx|ftsm_idx|11333|CREATE TABLE 'ftsm_idx'(segid, term, pgno, PRIMARY
 KEY(segid, term)) WITHOUT ROWID
table|ftsm_content|ftsm_content|11334|CREATE TABLE 'ftsm_content'(id INTEGER PRI
MARY KEY, c0)
table|ftsm_config|ftsm_config|11335|CREATE TABLE 'ftsm_config'(k PRIMARY 

[sqlite] maybe bug in regexp and replace with newlines?

[michael]

On 12/2/2015 7:54 AM, michael wrote:
>>/sqlite3 -header flexsql.db "select distinct replace(lower(text),0x0A,'') 
>>/>>/from v_term_item where lower(text) REGEXP('.*some_text.*') limit 1;" /
>You are removing newlines in the wrong spot. Do it on the left-hand side 
>of REGEXP, not in SELECT clause.

>The reason it doesn't work is that, with most regular expression 
>engines, unless certain flags are used, "." (period) doesn't match 
>newline characters.
>-- 
>Igor Tandetnik

Thanks for your answers. But I only pasted wrong line here.
The statement:
select distinct text from v_term_item where replace(lower(text),0x0A,'') 
REGEXP('.*/some_text/.*');
doesn't work like that:
select distinct text from v_term_item where replace(lower(text),'
,'') REGEXP('.*/some_text/.*');

with debian and /usr/lib/sqlite3/pcre.so there is one more problem. 
REGEXP(/'//some_text'/) works like REGEXP(/'//some_text/.*')

thanks
Michael

[sqlite] build failure: undeclared identifier 'uintptr_h'

[Richard Hipp]

On 12/10/15, jungle Boogie  wrote:
>
> Updating to trunk this morning results in this failure when attempting to
> build:
>

Should be fixed now.  Please try again.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] i think this is a bug in sqlite? thanks

[Marc L. Allen]

I was about the suggest the same thing that was suggest in that thread.  Verify 
the types of those values.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Anthony 
Damico
Sent: Thursday, December 10, 2015 10:56 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] i think this is a bug in sqlite? thanks

https://github.com/rstats-db/RSQLite/issues/125#event-488337588
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] i think this is a bug in sqlite? thanks

[Anthony Damico]

https://github.com/rstats-db/RSQLite/issues/125#event-488337588

[sqlite] Making data unique

[Bart Smissaert]

Ah, yes they both work and tell me the indexed fields.
Saves me some work parsing this out from the table create SQL.
Thanks for that.

RBS


On Thu, Dec 10, 2015 at 10:19 AM, Simon Slavin  wrote:

>
> On 10 Dec 2015, at 10:17am, Bart Smissaert 
> wrote:
>
> > This will show in sqlite_master like this:
> >
> > type   name   tbl_name rootpage sql
> >
> ---
> > index sqlite_autoindex_dataset_1 dataset  717
> >
> > So, no SQL. Problem with this is that there is no way to see what fields
> > the index is on.
>
> What happens if you do either of
>
> PRAGMA index_info(sqlite_autoindex_dataset_1);
> PRAGMA index_xinfo(sqlite_autoindex_dataset_1);
>
> ?
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Making data unique

[Simon Slavin]

On 10 Dec 2015, at 10:17am, Bart Smissaert  wrote:

> This will show in sqlite_master like this:
> 
> type   name   tbl_name rootpage sql
> ---
> index sqlite_autoindex_dataset_1 dataset  717
> 
> So, no SQL. Problem with this is that there is no way to see what fields
> the index is on.

What happens if you do either of

PRAGMA index_info(sqlite_autoindex_dataset_1);
PRAGMA index_xinfo(sqlite_autoindex_dataset_1);

?

[sqlite] Making data unique

[Bart Smissaert]

This will show in sqlite_master like this:

type   name   tbl_name rootpage sql
---
index sqlite_autoindex_dataset_1 dataset  717

So, no SQL. Problem with this is that there is no way to see what fields
the index is on.
I suppose the only way round this is to look at the table create SQL.
Is this just the way it is?

RBS

On Tue, Dec 8, 2015 at 2:52 AM, Keith Medcalf  wrote:

>
> create table dataset
> (
>id integer not null,
>timestamp integer not null,
>data integer not null,
>unique (id, timestamp)
> );
>
> > -Original Message-
> > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> > bounces at mailinglists.sqlite.org] On Behalf Of Andrew Stewart
> > Sent: Monday, 7 December, 2015 08:01
> > To: 'SQLite mailing list'
> > Subject: [sqlite] Making data unique
> >
> > Hi,
> > I have a table that consists of 3 elements:
> > ID - integer
> > Date/time - integer
> > Data - integer
> > A single ID can exist multiple times.
> > A single Date/time can exist multiple times.
> > An ID & Date/time combination is unique.
> >
> > What is the best way to ensure uniqueness in this table.
> >
> > Thanks,
> > Andrew Stewart
> > Software Designer
> >
> > Argus Controls
> > #101 - 18445 53 AVE
> > Surrey, BC  V3S 7A4
> >
> > t: 1-888-667-2091  ext : 108
> > t: 1-604-536-9100  ext : 108
> > f: 604-538-4728
> > w: www.arguscontrols.com
> > e: astewart at arguscontrols.com
> >
> > Notice: This electronic transmission contains confidential information,
> > intended only for the person(s) named above. If you are not the intended
> > recipient, you are hereby notified that any disclosure, copying,
> > distribution, or any other use of this email is strictly prohibited. If
> > you have received this transmission by error, please notify us
> immediately
> > by return email and destroy the original transmission immediately and all
> > copies thereof.
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] build failure: undeclared identifier 'uintptr_h'

[jungle Boogie]

On 10 December 2015 at 10:00, Richard Hipp  wrote:
> On 12/10/15, jungle Boogie  wrote:
>>
>> Updating to trunk this morning results in this failure when attempting to
>> build:
>>
>
> Should be fixed now.  Please try again.


Well done! Thanks.

-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] build failure: undeclared identifier 'uintptr_h'

[jungle Boogie]

Hello,

Updating to trunk this morning results in this failure when attempting to build:

% ./configure --disable-editline

G_H -DBUILD_sqlite -DNDEBUG -I/usr/local/include/tcl8.6
-DSQLITE_THREADSAFE=1 -DSQLITE_TEMP_STORE=1 -c sqlite3.c -o sqlite3.o
sqlite3.c:21951:9: error: use of undeclared identifier 'uintptr_h';
did you mean 'uintptr_t'?
if( SQLITE_WITHIN(p, sqlite3GlobalConfig.pScratch, mem0.pScratchEnd) ){
^
sqlite3.c:9032:23: note: expanded from macro 'SQLITE_WITHIN'
((uintptr_t)(P)>=(uintptr_h)(S) && (uintptr_t)(P)<(uintptr_t)(E))
  ^
/usr/include/sys/_stdint.h:78:22: note: 'uintptr_t' declared here
typedef __uintptr_t uintptr_t;
^
sqlite3.c:21987:10: error: use of undeclared identifier 'uintptr_h';
did you mean 'uintptr_t'?
  return SQLITE_WITHIN(p, db->lookaside.pStart, db->lookaside.pEnd);
 ^
sqlite3.c:9032:23: note: expanded from macro 'SQLITE_WITHIN'
((uintptr_t)(P)>=(uintptr_h)(S) && (uintptr_t)(P)<(uintptr_t)(E))
  ^
/usr/include/sys/_stdint.h:78:22: note: 'uintptr_t' declared here
typedef __uintptr_t uintptr_t;
^
sqlite3.c:62342:12: error: use of undeclared identifier 'uintptr_h';
did you mean 'uintptr_t'?
   || !SQLITE_WITHIN(pCell,aOld,[usableSize])
   ^
sqlite3.c:9032:23: note: expanded from macro 'SQLITE_WITHIN'
((uintptr_t)(P)>=(uintptr_h)(S) && (uintptr_t)(P)<(uintptr_t)(E))
  ^
/usr/include/sys/_stdint.h:78:22: note: 'uintptr_t' declared here
typedef __uintptr_t uintptr_t;
^
3 errors generated.
*** Error code 1

Stop.
make: stopped in /usr/home/sean/fossil-repos/sqlite3


Current installed version: SQLite version 3.10.0 2015-12-03 13:43:07

-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Bug with DATETIME('localtime')

[Keith Medcalf]

In both cases the conversion is only correct when it is correct.  Microsoft is 
a teeny weeny company that exists and does business only in one time zone and 
has existed for such a short period of time (and produces software which covers 
such short periods of time) that they have never ever encountered a daylight 
savings time rule change.

The only way to convert datetime data on windows is to use a third-party 
package that does it properly, or write it yourself.  Using the WinAPI 
functions is equivalent to "writing it yourself" because they do not actually 
do anything -- you have to manage all the meaningful data and deal with the 
vagaries of the under-documented closed proprietary function implementations in 
windows (which Microsoft even admits do not work properly).

Remarks

The SystemTimeToTzSpecificLocalTime function takes into account whether 
daylight saving time (DST) is in effect for the local time to which the system 
time is to be converted.

The SystemTimeToTzSpecificLocalTime function may calculate the local time 
incorrectly under the following conditions:

The time zone uses a different UTC offset for the old and new years.
The UTC time to be converted and the calculated local time are in different 
years.


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Vitaly Baranov
> Sent: Wednesday, 9 December, 2015 07:49
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Bug with DATETIME('localtime')
> 
> Hi,
> I've found a bug with using 'localtime' in functions DATETIME(), DATE(),
> TIME().
> 
> Platform: Windows 7.
> Steps to reproduce:
> 1. Set your system time zone as "Russia Time Zone 2, (UTC+03:00) Moscow,
> St. Petersburg, Volgograd)".
> 2. Execute the following script:
> 
> SELECT DATETIME(1414267200, 'unixepoch');
> SELECT DATETIME(1414267200, 'unixepoch', 'localtime');
> 
> Actual results:
> 2014-10-25 20:00:00
> 2014-10-25 23:00:00
> 
> Expected results:
> 2014-10-25 20:00:00
> 2014-10-26 00:00:00
> 
> Current local time in Moscow is "UTC+3", however it was "UTC+4" on this
> date.
> 
> The following is the result of my own investigation, I hope this will be
> useful. sqlite uses C library function localtime(). However Microsoft's
> implementation of this function sometimes returns incorrect value. I
> sent this information to Microsoft (see
> https://connect.microsoft.com/VisualStudio/feedback/details/1984408 ),
> and they advised to use WinAPI instead of localtime_s(). As far as I can
> see, WinAPI function SystemTimeToTzSpecificLocalTime() works correctly
> for any date.
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to see SQLite debugging information

[da...@andl.org]

On Windows you will get a console and standard output if you are running a
console application, and otherwise not.

I think you need a simple console app to call your ActiveX DLL, or find some
other way. Windows GUI app and standard output do not play well together.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



On 12/8/15, Bart Smissaert  wrote:
> So, what/where is that standard output channel?
> This is on a Win7 machine. How do I bring up that console window?
>

The standard output is what displays on your screen when you are in a DOS
box.

SQLite does not have any facilities for debugging in a GUI on Windows.

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