[sqlite] SQLite crashing

[Igor Korot]

Clemens,

On Fri, Jan 29, 2016 at 3:19 AM, Clemens Ladisch  wrote:
> Igor Korot wrote:
>> 1>odbccp32.lib(dllload.obj) : error LNK2019: unresolved external
>> symbol __imp___vsnprintf referenced in function
>> _StringVPrintfWorkerA at 20
>
> Looks like a bug in the MSVC libraries:
> http://stackoverflow.com/questions/4596212/c-odbc-refuses-to-statically-link-to-libcmt-lib-under-vs2010

It looks like the solution is to add the MSVC 2008 odbccp32.lib to the project.

The trouble is - I don't know how compatible it will be with all other
libraries from MSVC 2010.
Moreover I would rather add the vsnprintf library from MSVC 2010 than
get old version of odbccp32.lib.

Is it possible to just add that library to the link command and not
old odbccp32.lib?

Thank you.

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

[sqlite] ​ Use Bash to Store Disc Info in SQLite

[Stephen Chrzanowski]

On Fri, Jan 29, 2016 at 8:59 PM, Cecil Westerhof 
wrote:

>
> > However, I've run into a few problems, so I'm offering my findings and
> > fixes.
> >
> > The first, line 3, you've got a reference to a script that doesn't exist
> > AFAIK.  You should maybe put a check to see if the file exists first,
> > before running it.  I just deleted that entry from my version of the
> > script.
> >
>
> ?That is my bash library. I will change it so you can use the script
> without the library.
> ?
>

I figured as such. :]


>
> Third, my version of df doesn't seem to support the --output parameter.
> > I've checked online man pages and I can't find an example or a man page
> > containing that parameter.  Removing the parameter, it still works.
> >
>
> ?I will look into that also. What does ?df --version? give and which
> version of Linux are you using? (uname -a)
>
>
On my home machine.

mc at core ~$ uname -a
Linux core 3.2.0-4-amd64 #1 SMP Debian 3.2.73-2+deb7u2 x86_64 GNU/Linux
mc at core ~$ df --version
df (GNU coreutils) 8.13
Copyright (C) 2011 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later .
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

The RH machines are all RH6.  I think all are 6.5, but not sure.  I can't
be assed to VPN in right now to find out.


>
> > Fourth, I'd maybe suggest that you either run a delete prior to updating
> > for todays date, or, change the resolution of the date to the accuracy
> of a
> > second.  The reason being is if you want to test (Like I'm doing) you're
> > going to run into constraint violations. With changing to a second
> > resolution, you can run it multiple times to get more results to get real
> > time info.  I'll also be changing my version so it doesn't show human
> > readable since I'll be looking at a few KB worth of changes at a time.
> >
>
> ?My idea was that you only run it once a day. But I will change that it can
> be run on minute base also. I will also implement a parameter to get rid of
> human readable.
> ?
>
>
In what I do and where I'd be using this most often, I deal with sick
computers all the time at a software level.  So if something goes rogue, I
need to know what is changing in as near real-time as necessary sometimes.
Once I build a front end for this, I'll be able to better monitor the
health of a system, or at least determine why a drive is getting full
quicker than expected.  I might even expand this a bit further to look at
cataloging file sizes, and then have the front end I build show me exactly
what files have changed in size.


>
>
> > On my MC server, I have the current partition setup:
> > rootfs36G  19G  15G 57% /
> > udev  10M0  10M 0% /dev
> > tmpfs397M 164K 397M 1% /run
> > /dev/disk/by-uuid/75b09020-4711-48d3-a664-7024a0d16db8  36G  19G  15G
> 57% /
> > tmpfs5.0M0 5.0M 0% /run/lock
> >
> > The fact that I have two  / mentioned, it errors out.  This might be an
> > edge case, but, just something I came across.  I'll have to filter out
> > either rootfs or dev/disk.  I'll need to see how the work machines are
> > setup (They're Redhat, I'm using Debian at home right now) so I'll decide
> > what happens then.
> >
>
> ?I would only use partitions?
> ? that are mounted on /dev/... But that is the nice thing about this
> solution: what is stored is driven by the database.
>
>
Read my other note.  It isn't / that caused me the grief, but the type of
FS that is mounted.  /run/lock and /run were both tempfs and your
constraint locked me out based on tempfs.  I changed the schema to enforce
the three fields of date, partition, and my new field mountPoint.


>
>
> > And finally, maybe not the scripts fault, but there was an oddball
> > directory made by one of the Minecraft mods that pooched the script.
> > Literally, the directory was
> > "_!'0!bw"k!(}!~@"y!(:!a@"v!'4!d!"y!'%!}w"r!'`!cg!x!#4!;w!u!$%!:!==".
> > I deleted the directory, and it went through completely.  I'll just
> modify
> > the script to ignore this particular directory.
> >
>
> ?You do not need to delete the directory, just do not put it in the
> database to be processed. :-D
>
>
Poh-tay-toe, poh-tah-toe.  To me, before Simon explained, I thought it was
a bad directory descriptor, so, junked it anyways.  But going forward, the
script didn't like the name for some reason, so it ultimately broke.  I'll
need to figure out how to ensure that doesn't happen at least on my MC
server.


>
>
> > All in all, excellent example.
> >
>
> ?Thank you.
> ?
>
> No problem.

Do you have any issues with my using this in a commercial environment?
It'd be just for diagnostic purposes and not resold.

[sqlite] ​ Use Bash to Store Disc Info in SQLite

[Stephen Chrzanowski]

Thanks for the input Simon.  I'm not sure why that'd end up on this
particular machine.  Must be the Author of the mod doing something
different for preferences, as this is the only directory that shows up.

The contents of the directories were just preference files.  I don't think
it'll affect anything in my game, so, not concerned about it, but will most
certainly keep that tidbit of info in the back of my head.

On Fri, Jan 29, 2016 at 7:25 PM, Simon Slavin  wrote:

>
> On 30 Jan 2016, at 12:14am, Stephen Chrzanowski 
> wrote:
>
> > And finally, maybe not the scripts fault, but there was an oddball
> > directory made by one of the Minecraft mods that pooched the script.
> > Literally, the directory was
> > "_!'0!bw"k!(}!~@"y!(:!a@"v!'4!d!"y!'%!}w"r!'`!cg!x!#4!;w!u!$%!:!==".
>
> I don't know anything about the subject of this thread, but the above
> string is the result of storing a Chinese string as Unicode and reading it
> back out as ASCII.  Thought you might find that useful.  Or at least
> diverting.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] ​ Use Bash to Store Disc Info in SQLite

[Stephen Chrzanowski]

The partition portion failed not because of multiple references to / but
multiple references to tempfs.  Maybe set the constraint to not only the
partition type but also the mount point?

On Fri, Jan 29, 2016 at 7:14 PM, Stephen Chrzanowski 
wrote:

> This is going to be useful for me for a couple of reasons.  First, I've
> got a machine at work that mysteriously fills up, and more importantly, I
> can monitor my Minecraft server at home. ;)
>
> However, I've run into a few problems, so I'm offering my findings and
> fixes.
>
> The first, line 3, you've got a reference to a script that doesn't exist
> AFAIK.  You should maybe put a check to see if the file exists first,
> before running it.  I just deleted that entry from my version of the script.
>
> On line 8, I'm erroring out with "declare: not found".  At first I thought
> it might be something to do with regexp, but even changing it to a static
> file name, it still bombs.  Then I realized that the file needs to be
> executable.  With a chmod, that works.
>
> Third, my version of df doesn't seem to support the --output parameter.
> I've checked online man pages and I can't find an example or a man page
> containing that parameter.  Removing the parameter, it still works.
>
> Fourth, I'd maybe suggest that you either run a delete prior to updating
> for todays date, or, change the resolution of the date to the accuracy of a
> second.  The reason being is if you want to test (Like I'm doing) you're
> going to run into constraint violations. With changing to a second
> resolution, you can run it multiple times to get more results to get real
> time info.  I'll also be changing my version so it doesn't show human
> readable since I'll be looking at a few KB worth of changes at a time.
>
> On my MC server, I have the current partition setup:
> rootfs36G  19G  15G 57% /
> udev  10M0  10M 0% /dev
> tmpfs397M 164K 397M 1% /run
> /dev/disk/by-uuid/75b09020-4711-48d3-a664-7024a0d16db8  36G  19G  15G 57% /
> tmpfs5.0M0 5.0M 0% /run/lock
>
> The fact that I have two  / mentioned, it errors out.  This might be an
> edge case, but, just something I came across.  I'll have to filter out
> either rootfs or dev/disk.  I'll need to see how the work machines are
> setup (They're Redhat, I'm using Debian at home right now) so I'll decide
> what happens then.
>
> And finally, maybe not the scripts fault, but there was an oddball
> directory made by one of the Minecraft mods that pooched the script.
> Literally, the directory was 
> "_!'0!bw"k!(}!~@"y!(:!a@"v!'4!d!"y!'%!}w"r!'`!cg!x!#4!;w!u!$%!:!==".
> I deleted the directory, and it went through completely.  I'll just modify
> the script to ignore this particular directory.
>
> All in all, excellent example.
>
> On Fri, Jan 29, 2016 at 12:47 PM, Cecil Westerhof 
> wrote:
>
>> I wrote the following article:
>> ??
>> Use Bash to Store Disc Info in SQLite
>> ??
>>
>> https://www.linkedin.com/pulse/use-bash-store-disc-info-sqlite-cecil-westerhof
>>
>> ?No rocket science, but I thought it could be interesting.
>> ?
>>
>> --
>> Cecil Westerhof
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] ​ Use Bash to Store Disc Info in SQLite

[Stephen Chrzanowski]

This is going to be useful for me for a couple of reasons.  First, I've got
a machine at work that mysteriously fills up, and more importantly, I can
monitor my Minecraft server at home. ;)

However, I've run into a few problems, so I'm offering my findings and
fixes.

The first, line 3, you've got a reference to a script that doesn't exist
AFAIK.  You should maybe put a check to see if the file exists first,
before running it.  I just deleted that entry from my version of the script.

On line 8, I'm erroring out with "declare: not found".  At first I thought
it might be something to do with regexp, but even changing it to a static
file name, it still bombs.  Then I realized that the file needs to be
executable.  With a chmod, that works.

Third, my version of df doesn't seem to support the --output parameter.
I've checked online man pages and I can't find an example or a man page
containing that parameter.  Removing the parameter, it still works.

Fourth, I'd maybe suggest that you either run a delete prior to updating
for todays date, or, change the resolution of the date to the accuracy of a
second.  The reason being is if you want to test (Like I'm doing) you're
going to run into constraint violations. With changing to a second
resolution, you can run it multiple times to get more results to get real
time info.  I'll also be changing my version so it doesn't show human
readable since I'll be looking at a few KB worth of changes at a time.

On my MC server, I have the current partition setup:
rootfs36G  19G  15G 57% /
udev  10M0  10M 0% /dev
tmpfs397M 164K 397M 1% /run
/dev/disk/by-uuid/75b09020-4711-48d3-a664-7024a0d16db8  36G  19G  15G 57% /
tmpfs5.0M0 5.0M 0% /run/lock

The fact that I have two  / mentioned, it errors out.  This might be an
edge case, but, just something I came across.  I'll have to filter out
either rootfs or dev/disk.  I'll need to see how the work machines are
setup (They're Redhat, I'm using Debian at home right now) so I'll decide
what happens then.

And finally, maybe not the scripts fault, but there was an oddball
directory made by one of the Minecraft mods that pooched the script.
Literally, the directory was
"_!'0!bw"k!(}!~@"y!(:!a@"v!'4!d!"y!'%!}w"r!'`!cg!x!#4!;w!u!$%!:!==".
I deleted the directory, and it went through completely.  I'll just modify
the script to ignore this particular directory.

All in all, excellent example.

On Fri, Jan 29, 2016 at 12:47 PM, Cecil Westerhof 
wrote:

> I wrote the following article:
> ??
> Use Bash to Store Disc Info in SQLite
> ??
>
> https://www.linkedin.com/pulse/use-bash-store-disc-info-sqlite-cecil-westerhof
>
> ?No rocket science, but I thought it could be interesting.
> ?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] ​ Use Bash to Store Disc Info in SQLite

[Cecil Westerhof]

I wrote the following article:
??
Use Bash to Store Disc Info in SQLite
??
https://www.linkedin.com/pulse/use-bash-store-disc-info-sqlite-cecil-westerhof

?No rocket science, but I thought it could be interesting.
?

-- 
Cecil Westerhof

[sqlite] Best way to store only date

[Jean-Christophe Deschamps]

At 16:23 29/01/2016, you wrote:
>Note that the fractional part in Julian day represents time-of-day 
>since noon, not since midnight.

Ah yes, I often forget about this point. Sorry for incorrect lead.

[sqlite] Best way to store only date

[Cecil Westerhof]

2016-01-29 16:23 GMT+01:00 Igor Tandetnik :

> On 1/29/2016 2:39 AM, Jean-Christophe Deschamps wrote:
>
>> select cast(julianday('now') as int)
>>
>> should do what you want.
>>
>
> Note that the fractional part in Julian day represents time-of-day since
> noon, not since midnight. The expression above will give different values
> at 11am and 1pm (UTC) of the same day, and the same value at 11pm and 1am
> of the same night. You would probably want cast(julianday('now')-0.5 as
> int) or perhaps cast(julianday('now', 'localtime')-0.5 as int)
>

?Yeah, I saw that. I went back to:
dateTEXT NOT NULL DEFAULT CURRENT_DATE
?



> Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other
> words, storing calendar dates as integers like 20160129.
>

?That is a nice one. Maybe I'll change it again. ;-)

-- 
Cecil Westerhof

[sqlite] Finding records containing non-ascii characters

[Cecil Westerhof]

2016-01-29 14:39 GMT+01:00 Dominique Devienne :

> On Fri, Jan 29, 2016 at 1:53 PM, Cecil Westerhof 
> wrote:
>
> > 2016-01-29 10:12 GMT+01:00 Richard Hipp :
> > > On 1/29/16, Cecil Westerhof  wrote:
> > > > In H2 I can find records containing non-ascii characters with:
> > > > SELECT *
> > > > FROM 
> > > > WHERE STRINGENCODE() LIKE '%\\u%'
> > > >
> > > > Is something like this also possible with SQLite?
> > >
> > > Perhaps something like this:
> > > SELECT * FROM 
> > > WHERE  GLOB ('*[^'||char(1,45,127)||']*');
> >
> > I tried it and it looks like it works. But I do not understand how it
> > works. Could you enlighten me?
> >
>
> char() returns text with 1 (unicode) char per unicode code point integer
> used as argument to this function.
> ??
>
> So char(1,45,127) return first-ascii-char - (i.e. dash) last-ascii-char
> (unicode is a superset of ascii).
> So the same way a-z is the character range between a and z, i.e. all
> lower-case letters
> NUL - DEL is the full range of ascii characters. (char(1) = NUL, char(127)
> = DEL)
>

?I should have been a little more persevering: I did not understand the
usage of char, but I could/should have worked it out.

Thanks.

?


> [^...] is the negation of the characters inside.
> So [^NUL-DEL] is all non-ascii (unicode) characters. It matches a single
> character.
> And *[^NULL-DEL]* allows that 1 character to be matched anywhere.
> * is the kleen star, which matches 0 or more arbitrary characters.
>
> Note that glob above is not unicode aware per se. It operates on UTF-8
> encoded strings,
> and in that encoding, all non-ascii characters are encoded into multiple
> bytes, which (the bytes) all are
> in the 128-255 range. So it works indeed.
>
> > Note, however, that the "*[" combination in a GLOB pattern in SQLite
> > > is inefficient. So the above might be slow for a large amount of text.
> >
>
> because the kleen star is "griddy" by default.
>
>
> > > Perhaps a better approach would be to create an application-defined
> > > function to do the search.
> > >
> > >   SELECT * FROM  WHERE contains_non_ascii();
> > >
> >
> > ?I am working (in this case) with bash. That is not possible then I
> > think.Or is it?
> >
>
> You can create a C shared library SQLite extension, that you .load in the
> sqlite3 shell.
> So that works in bash too, but you need to do some C programming. --DD
>

?In principal I share my things, so that could make things a little ?

?complicated, but I'll cross that bridge when I get there.

-- 
Cecil Westerhof

[sqlite] Finding records containing non-ascii characters

[Dominique Devienne]

On Fri, Jan 29, 2016 at 1:53 PM, Cecil Westerhof 
wrote:

> 2016-01-29 10:12 GMT+01:00 Richard Hipp :
> > On 1/29/16, Cecil Westerhof  wrote:
> > > In H2 I can find records containing non-ascii characters with:
> > > SELECT *
> > > FROM 
> > > WHERE STRINGENCODE() LIKE '%\\u%'
> > >
> > > Is something like this also possible with SQLite?
> >
> > Perhaps something like this:
> > SELECT * FROM 
> > WHERE  GLOB ('*[^'||char(1,45,127)||']*');
>
> I tried it and it looks like it works. But I do not understand how it
> works. Could you enlighten me?
>

char() returns text with 1 (unicode) char per unicode code point integer
used as argument to this function.
So char(1,45,127) return first-ascii-char - (i.e. dash) last-ascii-char
(unicode is a superset of ascii).
So the same way a-z is the character range between a and z, i.e. all
lower-case letters
NUL - DEL is the full range of ascii characters. (char(1) = NUL, char(127)
= DEL)
[^...] is the negation of the characters inside.
So [^NUL-DEL] is all non-ascii (unicode) characters. It matches a single
character.
And *[^NULL-DEL]* allows that 1 character to be matched anywhere.
* is the kleen star, which matches 0 or more arbitrary characters.

Note that glob above is not unicode aware per se. It operates on UTF-8
encoded strings,
and in that encoding, all non-ascii characters are encoded into multiple
bytes, which (the bytes) all are
in the 128-255 range. So it works indeed.

> Note, however, that the "*[" combination in a GLOB pattern in SQLite
> > is inefficient. So the above might be slow for a large amount of text.
>

because the kleen star is "griddy" by default.


> > Perhaps a better approach would be to create an application-defined
> > function to do the search.
> >
> >   SELECT * FROM  WHERE contains_non_ascii();
> >
>
> ?I am working (in this case) with bash. That is not possible then I
> think.Or is it?
>

You can create a C shared library SQLite extension, that you .load in the
sqlite3 shell.
So that works in bash too, but you need to do some C programming. --DD

[sqlite] Searching for words

[Cecil Westerhof]

2016-01-29 10:20 GMT+01:00 Richard Hipp :

> On 1/29/16, Cecil Westerhof  wrote:
> > In h2 I can search for words with:
> > quote REGEXP CONCAT('(\W|^)', :search_string, '(\W|$)')
> >
> > Is something like that also possible with SQLite?
>
> SQLite does not have a REGEXP operator enabled by default.  However,
> if you add the (https://www.sqlite.org/src/artifact/af92cdaa5058fcec)
> extension to your build, then REGEXP will be available and you can do:
>
> ... quote REGEXP ('\b'||:search_string||'\b);
>
> Or you could use Full Text Search (https://www.sqlite.org/fts3.html)
> which is built-in by default.
>

?That looks interesting. I am going to look into that.

-- 
Cecil Westerhof

[sqlite] Finding records containing non-ascii characters

[Cecil Westerhof]

2016-01-29 10:12 GMT+01:00 Richard Hipp :

> On 1/29/16, Cecil Westerhof  wrote:
> > In H2 I can find records containing non-ascii characters with:
> > SELECT *
> > FROM 
> > WHERE STRINGENCODE() LIKE '%\\u%'
> >
> > Is something like this also possible with SQLite?
>
> Perhaps something like this:
>
>
> ??
> SELECT * FROM 
> ??
> WHERE  GLOB ('*[^'||char(1,45,127)||']*');
>

I tried it and it looks like it works. But I do not understand how it
works. Could you enlighten me?



> Note, however, that the "*[" combination in a GLOB pattern in SQLite
> is inefficient. So the above might be slow for a large amount of text.
> Perhaps a better approach would be to create an application-defined
> function to do the search.
>
>   SELECT * FROM  WHERE contains_non_ascii();
>

?I am working (in this case) with bash. That is not possible then I think.
Or is it?

At the moment I do not need it a lot, so probably not a big problem (yet).

-- 
Cecil Westerhof

[sqlite] Bug: Successfully committed transaction rolled back after power failure

[Bernard McNeill]

===
...you must fsync the containing directory...
===

Is there an Sqlite option to achieve that?


In fact, to summarise:
Suppose I would like to maximise my chances of avoiding the 'Lost Post-It'
problem described above.
What are _all_ the Sqlite compile-time options, and their values, needed
under Linux?

(I appreciate the disk hard/firmware will need to be looked at as well)

Best regards



On Thu, Jan 28, 2016 at 4:37 PM, Howard Chu  wrote:

> Simon Slavin wrote:
>
>>
>> On 28 Jan 2016, at 1:38pm, Bernard McNeill  wrote:
>>
>> ===
>>> Like the user reading ?saving OK? and throwing away the
>>> Post-It with the original information
>>> ===
>>>
>>> This is exactly my concern.
>>> The user throwing away the Post-It is entirely reasonable if he sees a
>>> message like that.
>>>
>>> Do you happen to know if Linux/Debian (which I think uses a journalling
>>> filesystem) carries this risk?
>>>
>>
>> The problem is not at the software level.
>>
>
> Not true. There *is* a problem at the software level - on Linux, current
> BSD (and apparently also on QNX) you must fsync the containing directory
> when you make changes to the contents of a directory (create/delete/rename
> files). This is above and beyond whatever lies the hardware layer may tell
> you. It's a documented requirement in Linux, at least. It is also
> independent of whether or not the filesystem uses journaling.
>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

[Yannick Duchêne]

On Thu, 28 Jan 2016 19:18:06 -0500
"Keith Medcalf"  wrote:

> 
> When you create a table thusly:
> 
> create table x (x primary key, y, z);
> 
> you are creating a rowid table with columns x, y, an z.  You are also saying 
> that you want x to be the primary key.  Therefore, you will get a table 
> called x containing (ROWID, X, Y, Z).  This is a btree where the index is the 
> rowid.  In order to implement you PRIMARY KEY, an index must be constructed 
> containing the column X and ROWID in the table where the value of X is found. 
>  This index will be called sqlite_autoindex_x_1 because it is the first index 
> created automatically on table x.

Close to what I was suspecting. Thanks for these points.

> You could achieve the same result as follows:
> 
> create table x (x, y, z);
> create unique index pk_x_x on x(x);
> 
> except that now you have given the index on x an explicit name and one does 
> not have to be manufactured for you.
> 

I checked these indexes gets created even if I manually define similar indexes. 
But I don't mind. After some experiments, this seems more related to ROWID, and 
you seem to confirm a relation, above.


-- 
Yannick Duch?ne

[sqlite] Best way to store only date

[Igor Tandetnik]

On 1/29/2016 2:39 AM, Jean-Christophe Deschamps wrote:
> select cast(julianday('now') as int)
>
> should do what you want.

Note that the fractional part in Julian day represents time-of-day since 
noon, not since midnight. The expression above will give different 
values at 11am and 1pm (UTC) of the same day, and the same value at 11pm 
and 1am of the same night. You would probably want 
cast(julianday('now')-0.5 as int) or perhaps cast(julianday('now', 
'localtime')-0.5 as int)

Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other 
words, storing calendar dates as integers like 20160129.
-- 
Igor Tandetnik

[sqlite] Searching for words

[Cecil Westerhof]

In h2 I can search for words with:
quote REGEXP CONCAT('(\W|^)', :search_string, '(\W|$)')

Is something like that also possible with SQLite?

-- 
Cecil Westerhof

[sqlite] Finding records containing non-ascii characters

[Cecil Westerhof]

In H2 I can find records containing non-ascii characters with:
SELECT *
FROM 
WHERE STRINGENCODE() LIKE '%\\u%'

Is something like this also possible with SQLite?

-- 
Cecil Westerhof

[sqlite] Best way to store only date

[Cecil Westerhof]

2016-01-29 8:39 GMT+01:00 Jean-Christophe Deschamps :

> At 08:28 29/01/2016, you wrote:
>
>> For storing a date I probably could use strftime('%J'). But I would like
>> to
>> store it as an INT. But I see no way to cast a float to an int. Is that
>> not
>> possible?
>>
>
> select cast(julianday('now') as int)
>
> should do what you want.
>

?It does: thanks.?



-- 
Cecil Westerhof

[sqlite] SQLite crashing

[Clemens Ladisch]

Igor Korot wrote:
> 1>odbccp32.lib(dllload.obj) : error LNK2019: unresolved external
> symbol __imp___vsnprintf referenced in function
> _StringVPrintfWorkerA at 20

Looks like a bug in the MSVC libraries:
http://stackoverflow.com/questions/4596212/c-odbc-refuses-to-statically-link-to-libcmt-lib-under-vs2010


Regards,
Clemens

[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

[Yannick Duchêne]

On Fri, 29 Jan 2016 01:34:01 +0200
R Smith  wrote:

> 
> I think the dictionary would be faster for this use-case (mostly cause 
> it runs in-memory and we do not require all the DB data guarantees for 
> the initial sort). However, the fastest way in SQL would be to use a 
> temporary table, do you have any problems with that? It would mean you 
> would do the inserts in an insert loop and then simply calculate the 
> incidence values and insert to the real table - so it would require a 2 
> - step process.
> 
> In fact, let me write the SQL quick here. The table create is just like 
> before, and then:
> 
> BEGIN TRANSACTION;
> 
>-- Make Temporary table
> CREATE TEMPORARY TABLE eav (
>e TEXT COLLATE NOCASE,
>a TEXT COLLATE NOCASE,
>v TEXT COLLATE NOCASE
> );
> 
>-- The insert loop:
> INSERT INTO eav VALUES (:element, :attribute, :value);
>-- end of Insert loop
> 
> 
> CREATE INDEX Idx_eav ON eav(e,a,v);
> 
> INSERT OR IGNORE INTO element_attribute_values (element,attribute,value,cnt)
>SELECT e,a,v,COUNT(*)
>  FROM EAV
> WHERE 1
> GROUP BY e,a,v;
> 
>-- Cleanup
> DROP TABLE eav;
> 
> COMMIT;
> 
> 
> If that is not a LOT faster then I will be very surprised...

I tried this, using a single transaction for the whole and I measured 15 
seconds instead of 22. Given that the program takes 6 seconds without 
insertions, that's good.

I finally use another way: I use a Python counter dictionary (the `Counter` 
class in the `collections` module) to compute the counts on a per file basis, 
then there is an insert?or?ignore?then?update request for each triplet, which 
add to "cnt" instead of incrementing it by just one. In fewer words, there is 
only one insert/update per triplet for a given file. Instead of 103K requests 
to give 15K rows, there is now 23K requests to give the same 15K rows. Using 
this with a transaction per file (as the program always did), I measured 17 
seconds. So I devised to group transactions transparently, a transaction is 
really committed only each N times (it takes care of any reminder), and using 
25 for N, I measure 11 seconds. I believe that's good enough (I guess it may be 
good to avoid big transactions).


-- 
Yannick Duch?ne

[sqlite] Best way to store only date

[Jean-Christophe Deschamps]

At 08:28 29/01/2016, you wrote:
>For storing a date I probably could use strftime('%J'). But I would 
>like to
>store it as an INT. But I see no way to cast a float to an int. Is 
>that not
>possible?

select cast(julianday('now') as int)

should do what you want.

[sqlite] Best way to store only date

[Cecil Westerhof]

To store date and time at the moment I use:
datetimeINT NOT NULL DEFAULT (strftime('%s'))

For storing a date I probably could use strftime('%J'). But I would like to
store it as an INT. But I see no way to cast a float to an int. Is that not
possible?

Or is it better just keeping to use:
dateTEXT NOT NULL DEFAULT CURRENT_DATE

-- 
Cecil Westerhof

[sqlite] Problem with distinct select query

[Gary Baranzini]

Hi,

I have a query where I select 20 random distinct rows.

SELECT DISTINCT formulas.pinyin, formulas.majorcategory, 
majorfcategory.item_name
FROM formulas
JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
ORDER BY RANDOM() LIMIT 20

Each row contains a major category id, "majorcategory"
I want to improve on this query so that the select will NOT return 
duplicate major categories

I thought I could do:

SELECT DISTINCT formulas.pinyin, formulas.majorcategory, 
majorfcategory.item_name
FROM formulas
JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
WHERE formulas.majorcategory in (SELECT DISTINCT formulas.majorcategory 
FROM formulas) ORDER BY RANDOM() LIMIT 20

But that doesn't work.

Any suggestions will be greatly appreciated.

jb
-- 


Gary 'JB' Baranzini , MS, L.Ac., CMT
conanjb at gmail.com 
In Balance Acupuncture 
925.998.4768 or 209.221.0122

[sqlite] SQLite crashing

[Teg]

Hello Igor,

I'm  probably  go  the  opposite direction and make sure everything is
built  /MDd or /MD.  Basically dynamic link the whole thing. It means
all of your projects need to be rebuilt using the same RTL though.

You just don't want 1/2 to be /MT and the other 1/2 to be /MD.



C

Thursday, January 28, 2016, 11:25:10 PM, you wrote:

IK> Hi, ALL,


IK> On Tue, Jan 26, 2016 at 3:08 PM, Clemens Ladisch  
wrote:
>> Igor Korot wrote:
>>>  sqlite.dll!sqlite3_mutex_enter(sqlite3_mutex * p)  Line 19996 + 0xc 
>>> bytesC
>>>  sqlite.dll!sqlite3Close(sqlite3 * db, int forceZombie)  Line 726 + 0xc 
>>> bytesC
>>>  sqlite.dll!sqlite3_close(sqlite3 * db)  Line 772 + 0xe bytesC
>>>  sqlite.dll!SQLiteDatabase::Disconnect(...)  Line 51 + 0xc bytesC++
>>>  dialogs.dll!DisconnectFromDb(Database * db)  Line 108 + 0x13 bytes
>>> C++
>>>  docview.exe!MainFrame::~MainFrame()  Line 73 + 0xf bytesC++
>>>  docview.exe!MainFrame::`scalar deleting destructor'()  + 0x16 bytes
>>> C++
>>>  docview.exe!wxAppConsoleBase::DeletePendingObjects()  Line 637 + 0x23 
>>> bytesC++
>>>  docview.exe!wxAppConsoleBase::ProcessIdle()  Line 445C++
>>
>> This looks OK.
>>
>> Are you ever calling sqlite3_shutdown()?
>> Are you calling sqlite3_close() from more than one place, or more than once?
>> (Add logging to find out.)

IK> I tried to recompile the project with the /MTd, but I got this:

1>>odbccp32.lib(dllload.obj) : error LNK2019: unresolved external
IK> symbol __imp___vsnprintf referenced in function
IK> _StringVPrintfWorkerA at 20

IK> And here is my Linker Project Settings:

IK> 
odbccp32.lib;kernel32.lib;user32.lib;gdi32.lib;comdlg32.lib;winspool.lib;winmm.l?
IK> 
ib;shell32.lib;shlwapi.lib;comctl32.lib;ole32.lib;oleaut32.lib;uuid.lib;rpcrt4.li?
IK> b;advapi32.lib;version.lib;wsock32.lib;wininet.lib;%(AdditionalDependencies)

IK> Is there an easy way to fix this?

IK> Thank you.

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



-- 
 Tegmailto:Teg at djii.com

[sqlite] Problem with distinct select query

[Gary Baranzini]

Hi,

I have a query where I select 20 random distinct rows.

SELECT DISTINCT formulas.pinyin, formulas.majorcategory, 
majorfcategory.item_name
FROM formulas
JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
ORDER BY RANDOM() LIMIT 20

Each row contains a major category id, ?majorcategory?
I want to improve on this query so that the select will NOT return 
duplicate major categories

I thought I could do:

SELECT DISTINCT formulas.pinyin, formulas.majorcategory, 
majorfcategory.item_name
FROM formulas
JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
WHERE formulas.majorcategory in (SELECT DISTINCT formulas.majorcategory 
FROM formulas) ORDER BY RANDOM() LIMIT 20

But that doesn?t work.

Any suggestions will be greatly appreciated.

jb

[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

[Keith Medcalf]

> > You could achieve the same result as follows:
> >
> > create table x (x, y, z);
> > create unique index pk_x_x on x(x);
> >
> > except that now you have given the index on x an explicit name and one
> does not have to be manufactured for you.
> >
> 
> I checked these indexes gets created even if I manually define similar
> indexes. But I don't mind. After some experiments, this seems more related
> to ROWID, and you seem to confirm a relation, above.

Well, yes, of course.  If you ask for two indexes to be created, two indexes 
will be created.

create table x (x primary key, y, z);
create unique index pk_x_x on x(x);

requests the creation of two indexes.  One by the "primary key" contained in 
the table definition, and one by the create index.  If you only want one, then 
only say to create one.

https://www.sqlite.org/optoverview.html#autoindex

see especially the last paragraphs of that section, and also

https://www.sqlite.org/fileformat2.html#intschema


Except for without_rowid tables, specifying "primary key" and "unique" within 
the table definition is just syntactic sugar for creating the indexes manually 
using create index; much in the same way ... JOIN ... ON ... is merely 
syntactic sugar for FROM ..., ... WHERE ... (except in the case of outer joins 
and the non-standard CROSS JOIN which cannot be expressed without the sugar).

[sqlite] Searching for words

[Richard Hipp]

On 1/29/16, Cecil Westerhof  wrote:
> In h2 I can search for words with:
> quote REGEXP CONCAT('(\W|^)', :search_string, '(\W|$)')
>
> Is something like that also possible with SQLite?

SQLite does not have a REGEXP operator enabled by default.  However,
if you add the (https://www.sqlite.org/src/artifact/af92cdaa5058fcec)
extension to your build, then REGEXP will be available and you can do:

... quote REGEXP ('\b'||:search_string||'\b);

Or you could use Full Text Search (https://www.sqlite.org/fts3.html)
which is built-in by default.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Finding records containing non-ascii characters

[Richard Hipp]

On 1/29/16, Cecil Westerhof  wrote:
> In H2 I can find records containing non-ascii characters with:
> SELECT *
> FROM 
> WHERE STRINGENCODE() LIKE '%\\u%'
>
> Is something like this also possible with SQLite?

Perhaps something like this:

  SELECT * FROM  WHERE  GLOB ('*[^'||char(1,45,127)||']*');

Note, however, that the "*[" combination in a GLOB pattern in SQLite
is inefficient. So the above might be slow for a large amount of text.
Perhaps a better approach would be to create an application-defined
function to do the search.

  SELECT * FROM  WHERE contains_non_ascii();


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Pragma automatic_index and ROWID: which one is really responsible for automatic indexes?

[R Smith]

On 2016/01/28 11:44 PM, Yannick Duch?ne wrote:
> On Thu, 28 Jan 2016 22:08:02 +0200
> R Smith  wrote:
>
> Indeed, I was misunderstanding. Are these indexes visible? Is there 
> any relation with the ones I see from sqlitebrowser? 

If you mean you are looking at the DB schema and seeing automatic 
indices there, then those are mostly the created ones where the table 
doesn't contain a suitable index or perhaps the rowid. If you mean the 
automatic indices that would be created in a select query for which the 
query planner decides to add an index to facilitate faster querying - 
well those can't be seen like that, but you can inspect the output from 
"EXPLAIN QUERY PLAN" or just "EXPLAIN" (added to the front of your 
query). These will show the query planner's steps and scan plans 
respectively (showing any such auto indices it might use).

>
> The shape will change, it's far from final. The tables at that step depends 
> on the next steps in the overall procedure, which is not entirely fixed for 
> now.

Good to know.

>> Could you try these in your Query loop perhaps:
>>
>>
>> -- Table: Renamed field count to cnt because "count" is an SQL reserved
>> word (though it will work, just a better habit)
>> CREATE TABLE element_attribute_values (
>> element TEXT COLLATE NOCASE NOT NULL,
>> attribute TEXT COLLATE NOCASE NOT NULL,
>> value TEXT COLLATE NOCASE NOT NULL CHECK ((attribute <> '') OR (value
>> = '')),
>> cnt INTEGER NOT NULL DEFAULT 0,
>> PRIMARY KEY (element, attribute, value)
>> );
>>
>> -- Insert Loop start:
>> -- This will simply fail if the PK already exists, else start the line
>> with 0 count.
>> INSERT OR IGNORE INTO element_attribute_values VALUES (:element,
>> :attribute, :value, 0);
>>
>> -- This might be faster since it uses only one lookup loop, but it might
>> also not be.
>> -- If you share a list of example data to be inserted, we can find a
>> faster way. Try it and let us know...
>> WITH EAV(id,icnt) AS (
>> SELECT rowid,cnt+1
>>   FROM element_attribute_values
>>  WHERE (element = :element) AND (attribute = :attribute) AND (value =
>> :value)
>>  LIMIT 1
>> )
>> UPDATE element_attribute_values SET cnt = (SELECT icnt FROM EAV)
>>WHERE rowid = (SELECT id FROM EAV)
>> ;
>> -- Insert Loop end.
>>
> The default may even be omitted, and may be the `LIMIT 1` too, as each 
> triplet is unique.

Yeah, the LIMIT 1 is just to force single-row-results which are required 
for the sub-query - it is however not possible to obtain multiple row 
results here, so the limit is superfluous in this case, but I like 
keeping it in for legibility.

> I tried it, with the variant (no `LIMIT 1`) and it's about the same, even a 
> bit more slow (not much, between one and two seconds more long). While speed 
> is not the only concern, or perhaps I should not care that much about the DB 
> file size (the other matters).
>
> It's close to what I had at the beginning, which gave similar timings (just 
> changed to match your recommendation about "cnt"'s name):

Yeah, I did not have a test-bed but I thought it might not improve much. 
Post a table somewhere with typical values (the 103k rows) that would be 
needing insertion, we might find a real fast way.


>
> I'm aware this use case may be a bit pathological, as I could use Python's 
> dictionary. However, I decided to not to, for three reasons:
>
>   * I wanted to see what it's like to use an SQLite DB as an application data 
> container (file or memory)?;
>   * Using a persistent DB is better for incremental process (may stop and 
> resume later)?;
>   * Persistent data is welcome for human review (I think about defining views 
> in sqlitebrowser to dissect the results)?;
>
> For persistence, I first tried CSV files, but this shows to be a inadequate. 
> An SQL DB and a DB browser, looks better than CSV for this use?case.
>

I think the dictionary would be faster for this use-case (mostly cause 
it runs in-memory and we do not require all the DB data guarantees for 
the initial sort). However, the fastest way in SQL would be to use a 
temporary table, do you have any problems with that? It would mean you 
would do the inserts in an insert loop and then simply calculate the 
incidence values and insert to the real table - so it would require a 2 
- step process.

In fact, let me write the SQL quick here. The table create is just like 
before, and then:

BEGIN TRANSACTION;

   -- Make Temporary table
CREATE TEMPORARY TABLE eav (
   e TEXT COLLATE NOCASE,
   a TEXT COLLATE NOCASE,
   v TEXT COLLATE NOCASE
);

   -- The insert loop:
INSERT INTO eav VALUES (:element, :attribute, :value);
   -- end of Insert loop


CREATE INDEX Idx_eav ON eav(e,a,v);

INSERT OR IGNORE INTO element_attribute_values (element,attribute,value,cnt)
   SELECT e,a,v,COUNT(*)
 FROM EAV
WHERE 1
GROUP BY e,a,v;

   -- Cleanup
DROP TABLE eav;

COMMIT;


If that is not a LOT faster then I will be very surprised...