[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[R.Smith]

On 2015-09-12 10:58 PM, Aurel Wisse wrote:
> The fastest solution is actually the temporary table:

Glad you found a faster solution.

> Still Richard : How about allowing recursive aggregate queries again ?

You say "allow" like it's something that worked and they blocked it out 
for no good reason.

It didn't work, your use case is a single accidental fringe that 
happened to work by virtue of the MIN() operation's character in the 
specific query, much in the way that if you throw a handful of seeds on 
a pavement and one of them happens to roll all the way over to a mossy 
moist area and found it could germinate, it is not a good reason to 
promote pavement-seeding - even if you happen to be the observer from 
the mossy patch.

Put another way: It isn't good to allow a convention that will "seem" to 
do a certain thing or work a certain way, but then give a completely 
wrong answer in 99% of cases - all because 1% happens to be correct.

MIN() actually has two implementations, one aggregate and one as a 
simple function, might be one could allow MIN() and MAX() if they always 
produced the correct results... but then we need test-cases to ensure 
they always give correct results, we need to maintain code that caters 
for it etc. and all that to achieve a prize which is handled better and 
faster by a temp table in the end.

[sqlite] Fwd: OT: Oracle functions for SQlite

[Charles Leifer]

This is really cool! I wonder if anyone else has collections of neat
user-defined functions/aggregates/virtual tables/etc? Is there a canonical
repository of these? Anyone know of some interesting ones?

On Tue, Sep 8, 2015 at 12:46 PM, Petite Abeille 
wrote:

> Perhaps of interest:
>
> http://sqlite-libs.cis.ksu.edu
>
>
>
> > Begin forwarded message:
> >
> > From: St?phane Faroult 
> > Subject: OT: Oracle functions for SQlite
> > Date: September 8, 2015 at 2:30:24 AM GMT+2
> > To: "Oracle-L (E-mail)" 
> > Reply-To: sfaroult at roughsea.com
> >
> > I don't know if there are many people on the list using SQLite, but I
> use it more and more often; teaching SQL is one reason (give a master file
> to students, and let them create, drop tables, run DML at will without any
> worry, and no need to bother about having a conveniently set server),
> another one is consulting, whenever I'd *like* to store some data but I am
> either unauthorized or unwilling to create my stuff on the database I'm
> working on. Great also for implementing the poor man's performance pack -
> dump your v$ every so often to a SQLite file, and you have something far
> more flexible than statspack.
> > The only snag is that SQLite is a bit weak function-wise. I have last
> spring given as assignment to the students in one of my classes the writing
> for SQLite of functions available in other products. Making everything
> homogeneous, writing a few functions I couldn't decently ask of
> undergraduates (even if I usually set the bar rather high), substituting my
> own date functions to the standard Unix ones so as to have the same
> behavior as Oracle in October 1582 and so forth has been a huge endeavor
> (not finished), it may still be a bit rough here and there but I have
> started publishing this collective effort as an open source library.
> >
> > It's at http://sqlite-libs.cis.ksu.edu/  >
> >
> > There isn't EVERYTHING, but all the classic functions are there.
> >
> > Enjoy.
> >
> > St?phane Faroult
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Luuk]

On 12-09-15 22:12, Aurel Wisse wrote:
> @Luuk : I tried your query and I cancelled after 9 minutes when it wasn't
> finished.
>
yes, it was/IS bad...

i should stick to non-recursive queries

select
 s1.calc_date,
 (select min(s2.calc_date)
 from securities s2
 where s2.calc_date>s1.calc_date)
from securities s1
order by s1.calc_date;

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Simon Slavin]

On 12 Sep 2015, at 9:14pm, Simon Slavin  wrote:

> Do you have an index on calc_date ?  If not, make one.

Uh ... and then run ANALYZE.

Simon.

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Simon Slavin]

> On 12 Sep 2015, at 9:12pm, Aurel Wisse  wrote:
> 
> CREATE TEMP TABLE offsets AS
> 
>  WITH RECURSIVE dtoff(calc_date, tmw) AS (
> 
> WITH dates AS (SELECT DISTINCT calc_date FROM securities)
> 
>SELECT NULL, (SELECT min(calc_date) FROM dates)
> ...
> This is inevitable as "securities" is a table with 6.2 million rows. This
> is the schema:
> 
> CREATE TABLE security (calc_date TEXT, security_code INT, mx_full REAL,
> mx_adj REAL, mx_std REAL, fu_full REAL, fu_adj REAL, few_full REAL, few_adj
> REAL);
> 
> 
> There are about 4000 distinct calc_date in securities.

Do you have an index on calc_date ?  If not, make one.

Simon.

[sqlite] Third test of json and index expressions, now it works

[James K. Lowden]

On Wed, 09 Sep 2015 09:56:12 +0200
"Domingo Alvarez Duarte"  wrote:

> With your knowledge could you give your view about how evaluation of
> calculated/function columns should be done to have correct results.  
...
> CREATE TABLE a(b);  
...
> SELECT a, random() as r FROM a WHERE r <> r;  

That's a syntax error, because there is no column a.r.  

Once you reorganize it to make it syntactically valid, the answer
becomes clear:

select a, r from ( 
SELECT a, random() as r FROM a   
) as R 
WHERE r <> r;

will yield zero rows, every time.  Whatever value RANDOM produces goes
in the "r" column and any value, including "r", is always equal to
itself.  

--jkl

[sqlite] Bug in SQLite 3.8.11.1 source code

[chris...@gmail.com]

Hello,

I was just looking at updating to SQLite 3.8.11.1 when I spotted what appears 
to be an error.
Here?s a patch to fix it:

--- sqlite-amalgamation-3081101/sqlite3.c   2015-07-30 03:06:58.0 
+0100
+++ sqlite3.c   2015-09-12 19:03:55.0 +0100
@@ -92265,7 +92265,7 @@
 }
 pParse->checkSchema = 1;
   }
-#if SQLITE_USER_AUTHENICATION
+#if SQLITE_USER_AUTHENTICATION
   else if( pParse->db->auth.authLevel

[sqlite] BEGINNER - Transactions in shell script

[Stephen Chrzanowski]

As with Rob, this is my final say as well.

On Fri, Sep 11, 2015 at 1:38 PM, Petr L?z?ovsk?  wrote:

> > 1. Security through obscurity is your first mistake. There is no such
> thing.
>
> Interesting It does not exist, but it have article on wikipedia.
> Sounds like UFO or Yetti...
>

"Security through Obscurity" in layman terms is that you provide no
additional valid information to whomever is accessing your system.  If
you're URL provides CGI in its path, it points (Doesn't necessarily MEAN)
more at that it is a Linux box than a Windows box.  That doesn't mean it
isn't, just that it is more likely to.  Looking at HTTP headers, you can
find out what OS your script is running on, what version of PHP, ASP, .NET,
whatever.  Black Hats typically keep a very long list of found security
holes on every single version of whatever script processor you're using.
Obscurity means you're just muddying the waters, or hiding facts, *or
acting like an outright liar*.  It doesn't mean your system becomes locked
down and people aren't going to be interested ESPECIALLY if you're found to
be on a fat pipe to the net.  Because Security through Obscurity is
becoming more mainstream, I'm sure these Black Hats don't even look at that
and have techniques to just get what they need done.


>
> > 2. Assuming that nobody is writing CGI scripts on Windows Servers is
> your next mistake. A lot of systems still do this, a lot of old systems
> still use this technique and some new ones, The attack vector is not
> necessarily through your CGI script itself but through the Windows Web
> server. Unless you have patched and patched and patched your web server,
> you will be attacked.
>
> Of course I keep my web server software up-to-date, why do you think I do
> not did it? I am talking here about my scripts, not about the server SW.
> But the server SW is relatively rare too...
>

"Scripts" and the underlying script processors are what do the processing.
OS updates are one thing, but the script processing engine is another, and
so are the scripts you write.  If any one of those three links in the chain
are broken, then buh-bye security.  The script processor talks to the OS to
get things done, such as allocate memory, set aside a portion of drive
space, execute other code, etc.  If you don't sanitize your inputs 100% of
the time properly, you're subject to losing a LOT of information or having
your machine taken over, or as Rob mentions, resources sold (A thought I
hadn't even  thought about).  The 'execute other code' should scare the
bejezus out of you.  If your system is allowed to run other code (Say, for
example, [ ls -ltr /etc ] to get a directory list), if you don't sanitize
your code, someone can (Not might, CAN if you don't sanitize) change that
to [ ls -ltr /etc ; rm -rf /etc ] and if Apache has access to write
anything there, you have a new install of an OS to do.

The company I work for sells software for the web that runs off both IIS
and Apache under Linux.  Both are scrutinized for security even though the
main portion of the software is behind a paywall (Need credentials to get
into the heart and main functions of the system) and all communications are
via HTTPS.  All HTTPS does is ensure that no one else knows what you're
saying on the socket, essentially.  It doesn't mean that the system isn't
hackable.  If we didn't ensure that the login field on the login page
wasn't sanitized, then we're pretty much bending over.


>
> > 3. You assume that nobody is interested in your machine. Wrong. A lot of
> people are very interested as they can add your hacked server to their
> bonnet and sell your resources on. Your machine does not have to be
> publicised at all. As an example, I have a private server which I use. It
> has no DNS entry (a common way to search for machines), so is only
> accessible through an IP address which has never been published. It only
> has a single ssh port open and port 80 for a private web server running
> some software there rest of the machine is locked down as best I can. The
> lock down took me a day to do. It is not trivial. My last weekly report
> showed over 200,000 attempts to break into the machine via ssh, http, and
> various CGI exploits. Thats 200,000 robot attempts, the most prevalent was
> an ssh attempt from a single machine which accounted for 72,000 goes. A
> public web server I have has over 1M hacking attempts per week. This is for
> a low usage machine.
>
> Script kiddies starting codes writen to attack widely spreaded systems,
> otherwise it will be not much fun. Some of this codes could be specialized
> to intrude minor systems, but I have doubts there are number of working
> scripts to successfuly intrude systems with rare occurance.
>
> Real hackers, those who are experienced in writing WORKING code targeted
> to intrude one specific rare system, need a REAL reason to did such job. My
> system does not offer such reason
>
>
"Real hackers" is funny.  I've never met a fake

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[R.Smith]

On 2015-09-12 06:30 PM, Florian Weimer wrote:
>> On 09/06/2015 11:13 AM, Florian Weimer wrote:
>>> Surely that's not true, and NFS and SMB are fine as long as there
>>> is no concurrent access?
>> And no program crashes, no network glitches, no optimisation in the
>> protocols to deal with latency, nothing else futzing with the files,
>> no programs futzing with them (backup agents, virus scanners etc), the
>> protocols are 100% complete compared to local file access, the
>> implementation of client and server for the protocol is 100% complete
>> and bug free, the operating systems don't treat network filesystems
>> sufficiently different to cause problems, you aren't using WAL, and
>> the list goes on.
>>
>> In other words it can superficially appear to work.  But one day
>> you'll eventually notice corruption, post to this list, and be told
>> not to use network filesystems.  The only variable is how long it
>> takes before you make that post.
> Sorry, this all sounds a bit BS to me.  Surely, as an fopen
> replacement, SQLite works with network file systems, be it the home
> NAS, or something in a typical datacenter.  And if the SQLite locking
> doesn't work in practice (which I doubt, remote file systems are
> better at locking than they used to be), should we really fall back
> on lock files with user overrides?  I hope not.

Your assumption and doubt is very wrong. Being able to open a file over 
the network is one thing, being able to lock it is another. Of course 
SQLite can request the lock (and does) like any other file access 
method, but the Network file system lies about the the actual locked 
state (for various reasons that requires more space than I aim to use here).

This is not an SQLite shortcoming, it is a network file-system shortcoming.

Your assertion that "remote file systems are better at locking than they 
used to be" may be superficially true, but that doesn't mean they are 
any more reliable. They simply lie all the time, because they have to in 
order to not make the connected World a very very slow place.


> (A lot of people run enterprise databases on NFS because that gives
> them snapshot-able storage and other goodies.  Not everyone uses iSCSI
> and block devices.)

That's like saying "A lot of people drive off-road vehicles in the bush, 
there is no reason why F1-cars should not also be driven in the bush."

Enterprise DBs have servers on the same machine as the Files they 
access, they do not actually use the network file-system to access the 
DB data-files over the network from multiple clients, or even servers 
(unless the DBs are partitioned so and ONLY accessed by the single 
process so locking is permanent and moot).

You cannot achieve a safe mechanism for opening, altering and locking 
files using fOpen over a network from other machines and multiple 
processes, but I invite you to try, maybe the exercise will be 
educational and remove some of the misconceptions.

You might even post your findings and code here after the fact. Lots of 
people do not realize the workings and will benefit from reading such an 
exercise.


Cheers,
Ryan

[sqlite] Native sqlite4 on FreeBSD

[Will Parsons]

On Friday, 11 Sep 2015 10:12 AM -0400, Valentin Davydov wrote:
> On Thu, Sep 10, 2015 at 01:30:37PM +0200, Eduardo Morras wrote:
>> 
>> Use gmake to compile.
>
> It didn't work either. Finally I've just installed some brand new linux 
> on a nearby virtual machine, made there make -f Makefile.linux-gcc and 
> thoroughly repeated it's output line-by-line on my FreeBSD while replacing 
> gcc by cc. Eventually it compiled well (with almost the same set of warnings) 
> and the result seems to work (at least within my own coverage).
>
> Thanks to the high general coding style, there were only two idiosyncrasies 
> to be corrected: lack of the system-wide malloc.h (which is replaced by 
> unistd.h in FreeBSD)

Perhaps I've missed something, but hasn't malloc.h been obsolete for
years now, with malloc() & friends being declared in stdlib.h as part
of standard C?

> and yet another lack of the fdatasync() syscall, which
> is already carefully screened from the sources by a special symbol called 
> __ANDROID__ ;-)

-- 
Will

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Aurel Wisse]

@R.Smith
I wasn't aware that recursive aggregate queries give wrong answers in 99%
of all general use cases. In that case, it is obviously preferable to
disable it.

On Sat, Sep 12, 2015 at 5:51 PM, R.Smith  wrote:

>
>
> On 2015-09-12 10:58 PM, Aurel Wisse wrote:
>
>> The fastest solution is actually the temporary table:
>>
>
> Glad you found a faster solution.
>
> Still Richard : How about allowing recursive aggregate queries again ?
>>
>
> You say "allow" like it's something that worked and they blocked it out
> for no good reason.
>
> It didn't work, your use case is a single accidental fringe that happened
> to work by virtue of the MIN() operation's character in the specific query,
> much in the way that if you throw a handful of seeds on a pavement and one
> of them happens to roll all the way over to a mossy moist area and found it
> could germinate, it is not a good reason to promote pavement-seeding - even
> if you happen to be the observer from the mossy patch.
>
> Put another way: It isn't good to allow a convention that will "seem" to
> do a certain thing or work a certain way, but then give a completely wrong
> answer in 99% of cases - all because 1% happens to be correct.
>
> MIN() actually has two implementations, one aggregate and one as a simple
> function, might be one could allow MIN() and MAX() if they always produced
> the correct results... but then we need test-cases to ensure they always
> give correct results, we need to maintain code that caters for it etc. and
> all that to achieve a prize which is handled better and faster by a temp
> table in the end.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Aurel Wisse
(514) 814-5421

[sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Florian Weimer]

* Roger Binns:

> On 09/06/2015 11:13 AM, Florian Weimer wrote:
>> Surely that's not true, and NFS and SMB are fine as long as there
>> is no concurrent access?
>
> And no program crashes, no network glitches, no optimisation in the
> protocols to deal with latency, nothing else futzing with the files,
> no programs futzing with them (backup agents, virus scanners etc), the
> protocols are 100% complete compared to local file access, the
> implementation of client and server for the protocol is 100% complete
> and bug free, the operating systems don't treat network filesystems
> sufficiently different to cause problems, you aren't using WAL, and
> the list goes on.
>
> In other words it can superficially appear to work.  But one day
> you'll eventually notice corruption, post to this list, and be told
> not to use network filesystems.  The only variable is how long it
> takes before you make that post.

Sorry, this all sounds a bit BS to me.  Surely, as an fopen
replacement, SQLite works with network file systems, be it the home
NAS, or something in a typical datacenter.  And if the SQLite locking
doesn't work in practice (which I doubt, remote file systems are
better at locking than they used to be), should we really fall back
on lock files with user overrides?  I hope not.

(A lot of people run enterprise databases on NFS because that gives
them snapshot-able storage and other goodies.  Not everyone uses iSCSI
and block devices.)

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Richard Hipp]

On 9/12/15, Aurel Wisse  wrote:
>
> In your example, there are 1000 rows of indexed dates.
>

It's easy enough to change it to 630 or whatever number is closer
to your test case.

I get:

Run Time: real 2.311 user 2.177056 sys 0.131887
Run Time: real 0.164 user 0.159917 sys 0.003997

The first time is for CREATE INDEX and the second is for the CREATE
TABLE offsets.

Test script:

-- Create a table "securities(calc_date)" and fill it with lots
-- with lots of dates in a crazy order.
--
CREATE TABLE securities(calc_date);
WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<630)
INSERT INTO securities(calc_date)
   SELECT datetime(2440587.5 + ((x*123456789012345)%1670)/1000.0) FROM c;
.timer on
CREATE INDEX securities_calcdate ON securities(calc_date);


-- Compute the "offsets(calc_date, tmw)" which has one entry for each
-- calc_date in securities and for which tmw is the first non-Sunday date
-- in securities that comes after calc_date.
--
CREATE TABLE offsets AS
  WITH RECURSIVE dtoff(calc_date, tmw) AS (
SELECT NULL, (SELECT min(calc_date) FROM securities)
UNION
SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
WHERE securities.calc_date>dtoff.tmw
  AND strftime('%w',securities.calc_date)<>'0')
  FROM dtoff
  )
  SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;



-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Luuk]

On 12-09-15 16:36, Aurel Wisse wrote:
> Yes it works, but it takes forever. By comparison:
>
> Original recursive query: 7-8 seconds (SQLite 3.8.10.1)
> Non recursive query (see below): 18-19 seconds. (SQLite 3.8.10.1)
> Proposed query: Not finished after 10 minutes. I am cancelling.
>
> Non recursive query with same result:
>
> CREATE TEMP TABLE dateoff AS
> WITH dates AS (SELECT DISTINCT calc_date FROM wts_next_day
> WHERE strftime('%w', calc_date)<>'0')
> SELECT dt1.calc_date AS calc_date, min(dt2.calc_date) AS tmw
> FROM dates AS dt1, dates AS dt2
> ON dt2.calc_date > dt1.calc_date
> GROUP BY dt1.calc_date;
>
> So the recursive option is still way ahead.
>
>
> On Sat, Sep 12, 2015 at 10:01 AM, E.Pasma  wrote:
>
>> Op 12 sep 2015, om 13:48 heeft Aurel Wisse het volgende geschreven:
>>
>>>
>>> Here is the example. It doesn't use an actual aggregation (sum, count),
>>> but
>>> the
>>>
>>> "min" aggregate function.
>>>
>>>
>>>  From a list of dates, create a lookup table with two
>>>
>>> columns: the original date (calc_date) and the date after calc_date (tmw).
>>>
>>>
>>> CREATE TABLE offsets AS
>>>
>>> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>>>
>>> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>>>
>>> SELECT NULL, min(cd) FROM dates
>>>
>>> UNION
>>>
>>> SELECT dtoff.tmw, min(dates.cd) FROM dtoff, dates
>>>
>>> WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
>>>
>>> AND strftime('%%w', dates.cd)<>'0')
>>>
>>> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>>>
>>>
>> The query below should work.
>> I hope that that is not a matter of good luck?
>>
>> CREATE TABLE securities(calc_date)
>> ;
>> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>>
>> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>>
>> SELECT NULL, min(cd) FROM dates
>>
>> UNION
>>
>> SELECT dtoff.tmw,
>>  -- begin change 150912
>>  (
>>  SELECT min(dates.cd)
>>  FROM dates
>>  WHERE dates.cd > dtoff.tmw
>>  AND strftime('%%w', dates.cd)<>'0'
>>  )
>>  -- end change 150912
>> FROM dtoff
>> WHERE dtoff.tmw NOTNULL
>>  )
>> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>>
>> ~
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
What about this one? i cannot test because i do not have (enough) 
data. ;)


WITH RECURSIVE dtoff(calc_date, tmw) AS (
   WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
   SELECT NULL, min(cd) FROM dates

   UNION

   SELECT dtoff.tmw, (dates.cd) FROM dtoff, dates
   WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
   AND strftime('%%w', dates.cd)<>'0')

SELECT * from dtoff  d1
WHERE calc_date NOTNULL
   AND (SELECT cOUNT(*) FROM dtoff d2 WHERE d2.calc_date=d1.calc_date 
and d2.tmw>d1.tmw)=0
ORDER BY calc_date;

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Aurel Wisse]

The fastest solution is actually the temporary table:

CREATE TEMP TABLE uniqdt AS SELECT DISTINCT calc_date FROM securities

WHERE strftime('%w', calc_date)<>'0' ORDER BY calc_date;

CREATE TEMP TABLE dtotemp AS

SELECT ud1.calc_date AS calc_date, ud2.calc_date AS tmw

FROM uniqdt AS ud1 INNER JOIN uniqdt AS ud2

ON ud2.rowid = ud1.rowid+1

ORDER BY calc_date;

DROP TABLE uniqdt;


Execution time:

Run Time: real 3.601 user 3.342178 sys 0.246156

Run Time: real 0.007 user 0.005320 sys 0.001221

Run Time: real 0.000 user 0.000218 sys 0.47

Still Richard : How about allowing recursive aggregate queries again ?


On Sat, Sep 12, 2015 at 4:45 PM, Aurel Wisse  wrote:

> There is an index on securities (6.2 million rows). On the dates query
> from the with clause,
> WITH dates AS (SELECT DISTINCT calc_date FROM securities), there can be no
> index.
>
> In your example, there are 1000 rows of indexed dates. The runtime of the
> full example (four steps) is:
>
> Run Time: real 0.002 user 0.000484 sys 0.000877
>
> Run Time: real 0.004 user 0.003905 sys 0.000404
>
> Run Time: real 0.001 user 0.001174 sys 0.000259
>
> Run Time: real 0.007 user 0.006640 sys 0.000405
>
> which is instantaneous, but unfortunately not reproducible with my
> datasource. Actually, even without the index the execution time of your
> example is still very reasonable:
>
> Run Time: real 0.000 user 0.000225 sys 0.48
>
> Run Time: real 0.004 user 0.003767 sys 0.000181
>
> Run Time: real 0.409 user 0.401034 sys 0.001259
>
>
> On Sat, Sep 12, 2015 at 4:27 PM, Richard Hipp  wrote:
>
>> On 9/12/15, Aurel Wisse  wrote:
>> > @Luuk : I tried your query and I cancelled after 9 minutes when it
>> wasn't
>> > finished.
>> >
>> > @Richard : The query isn't done after 15 minutes. I added a small
>> > modification, reinserting the WITH clause inside the recursive WITH (and
>> > using dates instead of securities in the remainder of the query) :
>>
>> Did you create the index on securities(calc_date) first, as shown in
>> my example.  I should have highlighted that point - the index is
>> important for performance.
>>
>> >
>> > CREATE TEMP TABLE offsets AS
>> >
>> >   WITH RECURSIVE dtoff(calc_date, tmw) AS (
>> >
>> > WITH dates AS (SELECT DISTINCT calc_date FROM securities)
>> >
>> > SELECT NULL, (SELECT min(calc_date) FROM dates)
>> > ...
>> > This is inevitable as "securities" is a table with 6.2 million rows.
>> This
>> > is the schema:
>> >
>> > CREATE TABLE security (calc_date TEXT, security_code INT, mx_full REAL,
>> > mx_adj REAL, mx_std REAL, fu_full REAL, fu_adj REAL, few_full REAL,
>> few_adj
>> > REAL);
>> >
>> >
>> > There are about 4000 distinct calc_date in securities. I want to avoid
>> an
>> > intermediate table with distinct dates. If I were to use an intermediate
>> > table, the offset would be trivial to create on an ordered version of
>> > dates, self joined with the JOIN clause d1.rowid+1=d2.rowid.
>> >
>> >
>> > The expected output is (first five rows):
>> >
>> > 2000-11-30|2000-12-01
>> >
>> > 2000-12-01|2000-12-04
>> >
>> > 2000-12-04|2000-12-05
>> >
>> > 2000-12-05|2000-12-06
>> >
>> > 2000-12-06|2000-12-07
>> >
>> >
>> > I have the impression that nontrivial SELECT queries in result columns
>> are
>> > quite expensive.
>> >
>> >
>> > The fact of the matter is that the original recursive query, up to this
>> > point, is the most efficient solution with the non recursive query
>> second.
>> > Richard: Any chance aggregate recursive queries can be reactivated ?
>> >
>> >
>> > Thanks,
>> >
>> >
>> > Aurel
>> >
>> >
>> >
>> > On Sat, Sep 12, 2015 at 12:25 PM, Richard Hipp  wrote:
>> >
>> >> On 9/12/15, Aurel Wisse  wrote:
>> >> > On 9/11/15, Richard Hipp  wrote:
>> >> >
>> >> > Here is the example.
>> >>
>> >> Have you considered using code like the following instead:
>> >>
>> >> -- Create a table "securities(calc_date)" and fill it with lots
>> >> -- with lots of dates in a crazy order.
>> >> --
>> >> CREATE TABLE securities(calc_date);
>> >> WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<1000)
>> >> INSERT INTO securities(calc_date)
>> >>SELECT datetime(2440587.5 + ((x*123456789012345)%1670)/1000.0)
>> >> FROM
>> >> c;
>> >> CREATE INDEX securities_calcdate ON securities(calc_date);
>> >>
>> >>
>> >> -- Compute the "offsets(calc_date, tmw)" which has one entry for each
>> >> -- calc_date in securities and for which tmw is the first non-Sunday
>> date
>> >> -- in securities that comes after calc_date.
>> >> --
>> >> CREATE TABLE offsets AS
>> >>   WITH RECURSIVE dtoff(calc_date, tmw) AS (
>> >> SELECT NULL, (SELECT min(calc_date) FROM securities)
>> >> UNION
>> >> SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
>> >> WHERE securities.calc_date>dtoff.tmw
>> >>   AND strftime('%w',securities.calc_date)<>'0')
>> >>   FROM dtoff
>> >>   )
>> >>   SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>> >> SE

[sqlite] sqlite3 file as database

[Simon Slavin]

On 12 Sep 2015, at 1:19pm, s.movaseghi at eramtec.ir wrote:

> I have a database file as database.db3 but I have to use database.sqlite3 .
> How can I convert the db3 file to sqlite3 file?

If it is actually a SQLite database already then just rename the file.  SQLite 
does not care what the file is called.

Simon.

[sqlite] sqlite3 file as database

[s.movase...@eramtec.ir]

Hello dears
I have a database file as database.db3 but I have to use database.sqlite3 .
How can I convert the db3 file to sqlite3 file?
Thanks for your help
sanam

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Aurel Wisse]

There is an index on securities (6.2 million rows). On the dates query from
the with clause,
WITH dates AS (SELECT DISTINCT calc_date FROM securities), there can be no
index.

In your example, there are 1000 rows of indexed dates. The runtime of the
full example (four steps) is:

Run Time: real 0.002 user 0.000484 sys 0.000877

Run Time: real 0.004 user 0.003905 sys 0.000404

Run Time: real 0.001 user 0.001174 sys 0.000259

Run Time: real 0.007 user 0.006640 sys 0.000405

which is instantaneous, but unfortunately not reproducible with my
datasource. Actually, even without the index the execution time of your
example is still very reasonable:

Run Time: real 0.000 user 0.000225 sys 0.48

Run Time: real 0.004 user 0.003767 sys 0.000181

Run Time: real 0.409 user 0.401034 sys 0.001259


On Sat, Sep 12, 2015 at 4:27 PM, Richard Hipp  wrote:

> On 9/12/15, Aurel Wisse  wrote:
> > @Luuk : I tried your query and I cancelled after 9 minutes when it wasn't
> > finished.
> >
> > @Richard : The query isn't done after 15 minutes. I added a small
> > modification, reinserting the WITH clause inside the recursive WITH (and
> > using dates instead of securities in the remainder of the query) :
>
> Did you create the index on securities(calc_date) first, as shown in
> my example.  I should have highlighted that point - the index is
> important for performance.
>
> >
> > CREATE TEMP TABLE offsets AS
> >
> >   WITH RECURSIVE dtoff(calc_date, tmw) AS (
> >
> > WITH dates AS (SELECT DISTINCT calc_date FROM securities)
> >
> > SELECT NULL, (SELECT min(calc_date) FROM dates)
> > ...
> > This is inevitable as "securities" is a table with 6.2 million rows. This
> > is the schema:
> >
> > CREATE TABLE security (calc_date TEXT, security_code INT, mx_full REAL,
> > mx_adj REAL, mx_std REAL, fu_full REAL, fu_adj REAL, few_full REAL,
> few_adj
> > REAL);
> >
> >
> > There are about 4000 distinct calc_date in securities. I want to avoid an
> > intermediate table with distinct dates. If I were to use an intermediate
> > table, the offset would be trivial to create on an ordered version of
> > dates, self joined with the JOIN clause d1.rowid+1=d2.rowid.
> >
> >
> > The expected output is (first five rows):
> >
> > 2000-11-30|2000-12-01
> >
> > 2000-12-01|2000-12-04
> >
> > 2000-12-04|2000-12-05
> >
> > 2000-12-05|2000-12-06
> >
> > 2000-12-06|2000-12-07
> >
> >
> > I have the impression that nontrivial SELECT queries in result columns
> are
> > quite expensive.
> >
> >
> > The fact of the matter is that the original recursive query, up to this
> > point, is the most efficient solution with the non recursive query
> second.
> > Richard: Any chance aggregate recursive queries can be reactivated ?
> >
> >
> > Thanks,
> >
> >
> > Aurel
> >
> >
> >
> > On Sat, Sep 12, 2015 at 12:25 PM, Richard Hipp  wrote:
> >
> >> On 9/12/15, Aurel Wisse  wrote:
> >> > On 9/11/15, Richard Hipp  wrote:
> >> >
> >> > Here is the example.
> >>
> >> Have you considered using code like the following instead:
> >>
> >> -- Create a table "securities(calc_date)" and fill it with lots
> >> -- with lots of dates in a crazy order.
> >> --
> >> CREATE TABLE securities(calc_date);
> >> WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<1000)
> >> INSERT INTO securities(calc_date)
> >>SELECT datetime(2440587.5 + ((x*123456789012345)%1670)/1000.0)
> >> FROM
> >> c;
> >> CREATE INDEX securities_calcdate ON securities(calc_date);
> >>
> >>
> >> -- Compute the "offsets(calc_date, tmw)" which has one entry for each
> >> -- calc_date in securities and for which tmw is the first non-Sunday
> date
> >> -- in securities that comes after calc_date.
> >> --
> >> CREATE TABLE offsets AS
> >>   WITH RECURSIVE dtoff(calc_date, tmw) AS (
> >> SELECT NULL, (SELECT min(calc_date) FROM securities)
> >> UNION
> >> SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
> >> WHERE securities.calc_date>dtoff.tmw
> >>   AND strftime('%w',securities.calc_date)<>'0')
> >>   FROM dtoff
> >>   )
> >>   SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
> >> SELECT * FROM offsets;
> >>
> >>
> >> --
> >> 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
> >>
> >
> >
> >
> > --
> > Aurel Wisse
> > (514) 814-5421
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> 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
>



-- 
Aurel Wisse
(514) 814-5421

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Richard Hipp]

On 9/12/15, Aurel Wisse  wrote:
> @Luuk : I tried your query and I cancelled after 9 minutes when it wasn't
> finished.
>
> @Richard : The query isn't done after 15 minutes. I added a small
> modification, reinserting the WITH clause inside the recursive WITH (and
> using dates instead of securities in the remainder of the query) :

Did you create the index on securities(calc_date) first, as shown in
my example.  I should have highlighted that point - the index is
important for performance.

>
> CREATE TEMP TABLE offsets AS
>
>   WITH RECURSIVE dtoff(calc_date, tmw) AS (
>
> WITH dates AS (SELECT DISTINCT calc_date FROM securities)
>
> SELECT NULL, (SELECT min(calc_date) FROM dates)
> ...
> This is inevitable as "securities" is a table with 6.2 million rows. This
> is the schema:
>
> CREATE TABLE security (calc_date TEXT, security_code INT, mx_full REAL,
> mx_adj REAL, mx_std REAL, fu_full REAL, fu_adj REAL, few_full REAL, few_adj
> REAL);
>
>
> There are about 4000 distinct calc_date in securities. I want to avoid an
> intermediate table with distinct dates. If I were to use an intermediate
> table, the offset would be trivial to create on an ordered version of
> dates, self joined with the JOIN clause d1.rowid+1=d2.rowid.
>
>
> The expected output is (first five rows):
>
> 2000-11-30|2000-12-01
>
> 2000-12-01|2000-12-04
>
> 2000-12-04|2000-12-05
>
> 2000-12-05|2000-12-06
>
> 2000-12-06|2000-12-07
>
>
> I have the impression that nontrivial SELECT queries in result columns are
> quite expensive.
>
>
> The fact of the matter is that the original recursive query, up to this
> point, is the most efficient solution with the non recursive query second.
> Richard: Any chance aggregate recursive queries can be reactivated ?
>
>
> Thanks,
>
>
> Aurel
>
>
>
> On Sat, Sep 12, 2015 at 12:25 PM, Richard Hipp  wrote:
>
>> On 9/12/15, Aurel Wisse  wrote:
>> > On 9/11/15, Richard Hipp  wrote:
>> >
>> > Here is the example.
>>
>> Have you considered using code like the following instead:
>>
>> -- Create a table "securities(calc_date)" and fill it with lots
>> -- with lots of dates in a crazy order.
>> --
>> CREATE TABLE securities(calc_date);
>> WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<1000)
>> INSERT INTO securities(calc_date)
>>SELECT datetime(2440587.5 + ((x*123456789012345)%1670)/1000.0)
>> FROM
>> c;
>> CREATE INDEX securities_calcdate ON securities(calc_date);
>>
>>
>> -- Compute the "offsets(calc_date, tmw)" which has one entry for each
>> -- calc_date in securities and for which tmw is the first non-Sunday date
>> -- in securities that comes after calc_date.
>> --
>> CREATE TABLE offsets AS
>>   WITH RECURSIVE dtoff(calc_date, tmw) AS (
>> SELECT NULL, (SELECT min(calc_date) FROM securities)
>> UNION
>> SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
>> WHERE securities.calc_date>dtoff.tmw
>>   AND strftime('%w',securities.calc_date)<>'0')
>>   FROM dtoff
>>   )
>>   SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>> SELECT * FROM offsets;
>>
>>
>> --
>> 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
>>
>
>
>
> --
> Aurel Wisse
> (514) 814-5421
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Aurel Wisse]

@Simon : Yes there is an index. The raw query

CREATE TEMP TABLE uniqdt AS SELECT DISTINCT calc_date FROM securities;

runs exactly 1.2 seconds.

@Luuk : Yes, there is a non recursive solution, but it is slower than the
recursive solution, by a factor of about 2.


On Sat, Sep 12, 2015 at 4:14 PM, Simon Slavin  wrote:

>
> On 12 Sep 2015, at 9:14pm, Simon Slavin  wrote:
>
> > Do you have an index on calc_date ?  If not, make one.
>
> Uh ... and then run ANALYZE.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Aurel Wisse
(514) 814-5421

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Aurel Wisse]

@Luuk : I tried your query and I cancelled after 9 minutes when it wasn't
finished.

@Richard : The query isn't done after 15 minutes. I added a small
modification, reinserting the WITH clause inside the recursive WITH (and
using dates instead of securities in the remainder of the query) :

CREATE TEMP TABLE offsets AS

  WITH RECURSIVE dtoff(calc_date, tmw) AS (

WITH dates AS (SELECT DISTINCT calc_date FROM securities)

SELECT NULL, (SELECT min(calc_date) FROM dates)
...
This is inevitable as "securities" is a table with 6.2 million rows. This
is the schema:

CREATE TABLE security (calc_date TEXT, security_code INT, mx_full REAL,
mx_adj REAL, mx_std REAL, fu_full REAL, fu_adj REAL, few_full REAL, few_adj
REAL);


There are about 4000 distinct calc_date in securities. I want to avoid an
intermediate table with distinct dates. If I were to use an intermediate
table, the offset would be trivial to create on an ordered version of
dates, self joined with the JOIN clause d1.rowid+1=d2.rowid.


The expected output is (first five rows):

2000-11-30|2000-12-01

2000-12-01|2000-12-04

2000-12-04|2000-12-05

2000-12-05|2000-12-06

2000-12-06|2000-12-07


I have the impression that nontrivial SELECT queries in result columns are
quite expensive.


The fact of the matter is that the original recursive query, up to this
point, is the most efficient solution with the non recursive query second.
Richard: Any chance aggregate recursive queries can be reactivated ?


Thanks,


Aurel



On Sat, Sep 12, 2015 at 12:25 PM, Richard Hipp  wrote:

> On 9/12/15, Aurel Wisse  wrote:
> > On 9/11/15, Richard Hipp  wrote:
> >
> > Here is the example.
>
> Have you considered using code like the following instead:
>
> -- Create a table "securities(calc_date)" and fill it with lots
> -- with lots of dates in a crazy order.
> --
> CREATE TABLE securities(calc_date);
> WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<1000)
> INSERT INTO securities(calc_date)
>SELECT datetime(2440587.5 + ((x*123456789012345)%1670)/1000.0) FROM
> c;
> CREATE INDEX securities_calcdate ON securities(calc_date);
>
>
> -- Compute the "offsets(calc_date, tmw)" which has one entry for each
> -- calc_date in securities and for which tmw is the first non-Sunday date
> -- in securities that comes after calc_date.
> --
> CREATE TABLE offsets AS
>   WITH RECURSIVE dtoff(calc_date, tmw) AS (
> SELECT NULL, (SELECT min(calc_date) FROM securities)
> UNION
> SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
> WHERE securities.calc_date>dtoff.tmw
>   AND strftime('%w',securities.calc_date)<>'0')
>   FROM dtoff
>   )
>   SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
> SELECT * FROM offsets;
>
>
> --
> 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
>



-- 
Aurel Wisse
(514) 814-5421

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[E.Pasma]

Op 12 sep 2015, om 13:48 heeft Aurel Wisse het volgende geschreven:
>
>
> Here is the example. It doesn't use an actual aggregation (sum,  
> count), but
> the
>
> "min" aggregate function.
>
>
> From a list of dates, create a lookup table with two
>
> columns: the original date (calc_date) and the date after calc_date  
> (tmw).
>
>
> CREATE TABLE offsets AS
>
> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>
> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>
> SELECT NULL, min(cd) FROM dates
>
> UNION
>
> SELECT dtoff.tmw, min(dates.cd) FROM dtoff, dates
>
> WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
>
> AND strftime('%%w', dates.cd)<>'0')
>
> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>


The query below should work.
I hope that that is not a matter of good luck?

CREATE TABLE securities(calc_date)
;
WITH RECURSIVE dtoff(calc_date, tmw) AS (

WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)

SELECT NULL, min(cd) FROM dates

UNION

SELECT dtoff.tmw,
 -- begin change 150912
 (
 SELECT min(dates.cd)
 FROM dates
 WHERE dates.cd > dtoff.tmw
 AND strftime('%%w', dates.cd)<>'0'
 )
 -- end change 150912
FROM dtoff
WHERE dtoff.tmw NOTNULL
 )
SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;

~

[sqlite] About backup example

[David Kazlauskas]

Hi, I see something in the second backup example ( 
http://www.sqlite.org/backup.html ) that seems like logical error to me.

 if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
   sqlite3_sleep  (250);
 }


Why should we sleep 250 milliseconds if sqlite result is OK? Shouldn't 
we wait only if database is busy or locked?

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Richard Hipp]

On 9/12/15, Aurel Wisse  wrote:
> On 9/11/15, Richard Hipp  wrote:
>
> Here is the example.

Have you considered using code like the following instead:

-- Create a table "securities(calc_date)" and fill it with lots
-- with lots of dates in a crazy order.
--
CREATE TABLE securities(calc_date);
WITH RECURSIVE c(x) AS (VALUES(1) UNION SELECT x+1 FROM c WHERE x<1000)
INSERT INTO securities(calc_date)
   SELECT datetime(2440587.5 + ((x*123456789012345)%1670)/1000.0) FROM c;
CREATE INDEX securities_calcdate ON securities(calc_date);


-- Compute the "offsets(calc_date, tmw)" which has one entry for each
-- calc_date in securities and for which tmw is the first non-Sunday date
-- in securities that comes after calc_date.
--
CREATE TABLE offsets AS
  WITH RECURSIVE dtoff(calc_date, tmw) AS (
SELECT NULL, (SELECT min(calc_date) FROM securities)
UNION
SELECT dtoff.tmw, (SELECT min(securities.calc_date) FROM securities
WHERE securities.calc_date>dtoff.tmw
  AND strftime('%w',securities.calc_date)<>'0')
  FROM dtoff
  )
  SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
SELECT * FROM offsets;


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] sqlite3 file as database

[Jim Callahan]

sanam
If renaming the file does not work; then use whatever package that can read
the file to read  it in and write it back out as a comma or tab delimited
file.

In the SQLite command line interface (CLI, implemented as SQLite3.exe) one
can:
"Use the ".import" command to import CSV (comma separated value) data into
an SQLite table.
... Note that it is important to set the "mode" to "csv" before running the
".import" command. This is necessary to prevent the command-line shell from
trying to interpret the input file text as some other format.

sqlite> *.mode csv*
sqlite> *.import C:/work/somedata.csv tab1*

https://www.sqlite.org/cli.html

Jim

On Sat, Sep 12, 2015 at 8:19 AM,  wrote:

> Hello dears
> I have a database file as database.db3 but I have to use database.sqlite3 .
> How can I convert the db3 file to sqlite3 file?
> Thanks for your help
> sanam
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Aurel Wisse]

Yes it works, but it takes forever. By comparison:

Original recursive query: 7-8 seconds (SQLite 3.8.10.1)
Non recursive query (see below): 18-19 seconds. (SQLite 3.8.10.1)
Proposed query: Not finished after 10 minutes. I am cancelling.

Non recursive query with same result:

CREATE TEMP TABLE dateoff AS
WITH dates AS (SELECT DISTINCT calc_date FROM wts_next_day
WHERE strftime('%w', calc_date)<>'0')
SELECT dt1.calc_date AS calc_date, min(dt2.calc_date) AS tmw
FROM dates AS dt1, dates AS dt2
ON dt2.calc_date > dt1.calc_date
GROUP BY dt1.calc_date;

So the recursive option is still way ahead.


On Sat, Sep 12, 2015 at 10:01 AM, E.Pasma  wrote:

>
> Op 12 sep 2015, om 13:48 heeft Aurel Wisse het volgende geschreven:
>
>>
>>
>> Here is the example. It doesn't use an actual aggregation (sum, count),
>> but
>> the
>>
>> "min" aggregate function.
>>
>>
>> From a list of dates, create a lookup table with two
>>
>> columns: the original date (calc_date) and the date after calc_date (tmw).
>>
>>
>> CREATE TABLE offsets AS
>>
>> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>>
>> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>>
>> SELECT NULL, min(cd) FROM dates
>>
>> UNION
>>
>> SELECT dtoff.tmw, min(dates.cd) FROM dtoff, dates
>>
>> WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL
>>
>> AND strftime('%%w', dates.cd)<>'0')
>>
>> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>>
>>
>
> The query below should work.
> I hope that that is not a matter of good luck?
>
> CREATE TABLE securities(calc_date)
> ;
> WITH RECURSIVE dtoff(calc_date, tmw) AS (
>
> WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)
>
> SELECT NULL, min(cd) FROM dates
>
> UNION
>
> SELECT dtoff.tmw,
> -- begin change 150912
> (
> SELECT min(dates.cd)
> FROM dates
> WHERE dates.cd > dtoff.tmw
> AND strftime('%%w', dates.cd)<>'0'
> )
> -- end change 150912
> FROM dtoff
> WHERE dtoff.tmw NOTNULL
> )
> SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;
>
> ~
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Aurel Wisse
(514) 814-5421

[sqlite] Feedback request: JSON support in SQLite

[Domingo Alvarez Duarte]

Hello !  

Looking at the documentation of json* functions and after see the example
given for a query on a field mixed owith string and json_array:  

___  SELECT name FROM user WHERE phone LIKE '704-%'  UNION  SELECT
user.nameFROM user, json_each(user.phone)   WHERE json_valid(user.phone) 
   AND json_each.value LIKE '704-%';  

___  

That sparked a question that also relates with the new "index over
expressions" given the example above would be a natural need to index on json
arrays.  

pseudo sql  

CREATE TABLE user(name, phones);  

CREATE INDEX user_phones ON user(json_array_each_one(phones));  

INSERT INTO user VALUES('Paul', '["12345678","87654324", "5302513"]');  

SELECT * FROM USER WHERE json_array_each_one(phones) = '87654321';  

___  

On the pseudo example above I was expecting the creation of an index with
zero or more entries for each record to allow quick searches.  

Cheers !  

?  
>  Fri Sep 11 2015 5:58:36 pm CEST CEST from "Richard Hipp"  
>Subject: [sqlite] Feedback request: JSON support in SQLite
>
>  Draft documentation for the current design of JSON support in SQLite
> can be seen on-line at
> 
> https://www.sqlite.org/draft/json1.html
> 
> Your feedback is encouraged.
> 
> All features described in the document above are implemented and
> working in the latest trunk version of SQLite, which you can download
> in source-code form from https://www.sqlite.org/src/timeline?y=ci and
> compile yourself using instructions found at
> https://www.sqlite.org/src/doc/trunk/README.md
> 
> -- 
> 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
> 
>
>  



?

[sqlite] Feedback request: JSON support in SQLite

[Domingo Alvarez Duarte]

Hello !  

I did some tests to see how the json* functions behave when we specify
collation to the column that stores it and it seems that the json* functions
ignore the collation completely, is that by design or is it a forgotten
implementation ?  

___  

CREATE TABLE a(j collate nocase);
INSERT INTO a values('"blue"'), ('"Blue"'), ('{"a":"blue","B":"BLUE"}');
SELECT 'select *',* FROM a;
SELECT 'j = ''"bLue"''',* FROM a WHERE j = '"bLue"';
SELECT 'j LIKE ''%bLue%''',* FROM a WHERE j LIKE '%bLue%';

SELECT 'json_extract(j, ''$'')', json_extract(j, '$') AS jstr FROM a;
SELECT json_extract(j, '$') AS jstr FROM a WHERE jstr = 'bLue'; -- I was
expecting collation to propagate here
SELECT json_extract(j, '$.a') AS jstr FROM a;
SELECT json_extract(j, '$.b') AS jstr FROM a; -- I was expecting $.B to match
$.b due to collation nocase here  

___  

Cheers !  
>  Fri Sep 11 2015 5:58:36 pm CEST CEST from "Richard Hipp"  
>Subject: [sqlite] Feedback request: JSON support in SQLite
>
>  Draft documentation for the current design of JSON support in SQLite
> can be seen on-line at
> 
> https://www.sqlite.org/draft/json1.html
> 
> Your feedback is encouraged.
> 
> All features described in the document above are implemented and
> working in the latest trunk version of SQLite, which you can download
> in source-code form from https://www.sqlite.org/src/timeline?y=ci and
> compile yourself using instructions found at
> https://www.sqlite.org/src/doc/trunk/README.md
> 
> -- 
> 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
> 
>
>  



?

[sqlite] BEGINNER - Transactions in shell script

[Rob Willett]

Petr,

Since this is the SQLite mailing list, we are moving away from the intentions 
of the list, however I think your points need addressing as they may be 
relevant to other people using this mailing list. I apologise to other people 
if this is off topic but I think its important enough to answer.

> On 11 Sep 2015, at 18:38, Petr L?z?ovsk?  wrote:
> 
>> 1. Security through obscurity is your first mistake. There is no such thing. 
> 
> Interesting It does not exist, but it have article on wikipedia. Sounds 
> like UFO or Yetti?   

Security through obscurity means that you assume that because nobody knows your 
system or knows your code then you are secure. That is what I was referring to. 
The article I assume you refer to

https://en.wikipedia.org/wiki/Security_through_obscurity 


makes this very point.

> 
>> 2. Assuming that nobody is writing CGI scripts on Windows Servers is your 
>> next mistake. A lot of systems still do this, a lot of old systems still use 
>> this technique and some new ones, The attack vector is not necessarily 
>> through your CGI script itself but through the Windows Web server. Unless 
>> you have patched and patched and patched your web server, you will be 
>> attacked. 
> 
> Of course I keep my web server software up-to-date, why do you think I do not 
> did it? I am talking here about my scripts, not about the server SW. But the 
> server SW is relatively rare too... 

I do not know what you do with your server, I would like to think you keep it 
patched up, but since you did not say all I can do is assume the worst and be 
proven wrong. However have you locked down every port, have you removed 
everything that is not needed, have to configured your applications that are 
front facing to be as secure as possible, have you put https for the web 
traffic that needs to be transmitted, have you checked that another machine on 
your local internet cannot get access to your machine, a side door approach. 
The scripts themselves may be secure, but the mechanism needed to run them 
needs to be just as secure. 

> 
>> 3. You assume that nobody is interested in your machine. Wrong. A lot of 
>> people are very interested as they can add your hacked server to their 
>> bonnet and sell your resources on. Your machine does not have to be 
>> publicised at all. As an example, I have a private server which I use. It 
>> has no DNS entry (a common way to search for machines), so is only 
>> accessible through an IP address which has never been published. It only has 
>> a single ssh port open and port 80 for a private web server running some 
>> software there rest of the machine is locked down as best I can. The lock 
>> down took me a day to do. It is not trivial. My last weekly report showed 
>> over 200,000 attempts to break into the machine via ssh, http, and various 
>> CGI exploits. Thats 200,000 robot attempts, the most prevalent was an ssh 
>> attempt from a single machine which accounted for 72,000 goes. A public web 
>> server I have has over 1M hacking attempts per week. This is for a low usage 
>> machine. 
> 
> Script kiddies starting codes writen to attack widely spreaded systems, 
> otherwise it will be not much fun. Some of this codes could be specialized to 
> intrude minor systems, but I have doubts there are number of working scripts 
> to successfuly intrude systems with rare occurance.
> 
> Real hackers, those who are experienced in writing WORKING code targeted to 
> intrude one specific rare system, need a REAL reason to did such job. My 
> system does not offer such reason?.

If you are using a Windows OS then your system is widely available. There may 
be millions of machines running your version of the OS, so you are a target 
from script kiddies. This comes back to Security through Obscurity. Your system 
is not unique and the resources it offers of an internet connection and 
processing power makes it attractive. The people searching the internet do not 
know the details of your machine, they are looking for machines to add to 
botnets. The fact you run a local database on it is of no interest, they want 
the machines to use to rent out for a DDOS attack or password cracking or spam 
sending. 

> 
>> I give your machine less than 24 hours once it is live on the internet if 
>> you put it on without taking security seriously. You need to get the OS 
>> patched up, the ports closed down, the web server patched up and correctly 
>> configured. Out of the box the security on a Windows server (depending on 
>> the version) is poor. You need to learn what you need to do (and there are 
>> loads of guides on the internet) otherwise your server will be owned by 
>> somebody else very quickly. 
> 
> As I already wrote, not using IIS. OS is protected by manualy configured 
> firewall. By concept Security through obscurity using this one 
> http://wipfw.sourceforge.net/ Intruding script perform OS detectio

[sqlite] sqlite3 file as database

[Gerry Snyder]

Have you tried simply renaming the file?
On Sep 12, 2015 8:29 AM,  wrote:

> Hello dears
> I have a database file as database.db3 but I have to use database.sqlite3 .
> How can I convert the db3 file to sqlite3 file?
> Thanks for your help
> sanam
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Worked in 3.8.9, but now "recursive aggregate queries not supported"

[Aurel Wisse]

On 9/11/15, Richard Hipp  wrote:

>On 9/11/15, Aurel Wisse  wrote:

>> I used a recursive aggregate query in 3.8.9 and it worked very well. Just

>> upgraded to 3.11.1 and the query is broken.

>>

>> This seems to be directly related to

>>

>> Check-in [6d2999af]: Do not allow recursive CTEs that use aggregate
queries

>> in the recursive part.

>>

>> It worked, and now it is disabled. Why ?

>>

>

>If it worked for you before, that was luck.  Aggregate queries in a

>recursive CTE have never worked for the general case.  In fact, I

>didn't realize that there were any cases where it did actually work.

>Can you share with use an example of an aggregate query in a recursive

>CTE that gives a reasonable answer?


Here is the example. It doesn't use an actual aggregation (sum, count), but
the

"min" aggregate function.


>From a list of dates, create a lookup table with two

columns: the original date (calc_date) and the date after calc_date (tmw).


CREATE TABLE offsets AS

WITH RECURSIVE dtoff(calc_date, tmw) AS (

WITH dates(cd) AS (SELECT DISTINCT calc_date FROM securities)

SELECT NULL, min(cd) FROM dates

UNION

SELECT dtoff.tmw, min(dates.cd) FROM dtoff, dates

WHERE dates.cd > dtoff.tmw AND dtoff.tmw NOTNULL

AND strftime('%%w', dates.cd)<>'0')

SELECT * from dtoff WHERE calc_date NOTNULL ORDER BY calc_date;


-- 
Aurel Wisse