What is the equivalent SQLite syntax for the Oracle SQL syntax below?
row_number() OVER (PARTITION BY x ORDER BY y DESC, z) AS aField
Example...
SELECT department_id, first_name, last_name, salary
FROM
(
SELECT
department_id, first_name, last_name, salary,
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
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 ?
Hello !
That's what json_extract does !
json_extract('[2,3,4,5,6]', '$[3]'); --> returns 5;
Cheers !
> Fri Sep 11 2015 8:40:36 pm CEST CEST from "Nelson, Erik - 2"
> Subject: Re: [sqlite] Feedback request:
>JSON support in SQLite
>
> Richard Hipp wrote on Friday, September 11, 2015
On 2015-09-11 07:50 PM, Keith Christian wrote:
> Pardon me, but: At what point does the code required for the
> inclusion of a multitude of supported data formats exceed the core
> purpose of the executable?
>
> At some point, obtaining a desired output format (from the potentially
> dozens
Hello !
I'm looking at these new extensions/functions and noticed that from scripting
languages to be able to use sqlite3_value_subtype we'll need access to
sqlite3_context parameters by index, because from the scripting language once
we are called there is no easy way from the script to refer
> 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...
> 2. Assuming that nobody is writing CGI scripts on Windows Servers is your
> next mistake. A lot of systems still
On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp wrote:
> Your feedback is encouraged.
>
https://www.sqlite.org/draft/c3ref/value_subtype.html does not say what
happens or which value we get,
should one call sqlite3_value_subtype on a sqlite_value* which which
no sqlite3_result_subtype()
was made.
On 9/11/2015 6:51 PM, Rousselot, Richard A wrote:
> What is the equivalent SQLite syntax for the Oracle SQL syntax below?
>
> row_number() OVER (PARTITION BY x ORDER BY y DESC, z) AS
> aField
>
> Example...
>
> SELECT department_id, first_name, last_name, salary
On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp wrote:
> Draft documentation for the current design of JSON support in SQLite [...]
Your feedback is encouraged.
>
"Experiments have so far been unable to find a binary encoding that is
significantly smaller or faster than a plain text encoding"
> On Sep 11, 2015, at 6:31 PM, Richard Hipp wrote:
>
> a passing fad
On the other hand, we can now embrace NormalFormZero without undue
embarrassment. Swell.
Richard Hipp wrote on Friday, September 11, 2015 11:59 AM
>
> Draft documentation for the current design of JSON support in SQLite
> can be seen on-line at
>
> https://www.sqlite.org/draft/json1.html
This looks really good!
With the understanding that json_extract() already has the
> On Sep 11, 2015, at 5:58 PM, Richard Hipp wrote:
>
> JSON support in SQLite
JSON, eh? No MERGE. No analytics. But serialization of the week is covered.
Sweet :D
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
Petr,
You are making a number of fundamental mistakes with your security.
1. Security through obscurity is your first mistake. There is no such thing.
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
On Fri, Sep 11, 2015 at 8:58 AM, Richard Hipp wrote:
> 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
There is a major difference: You are talking about SSH and Linux, this
combination running on hundred milions of network devices accross whole
internet. Thus develop intruding scripts does make sense. But I am using
Windows shell scripts as CGI, which is EXTREMELY rare. Who will study this
On 9/11/15, Dominique Devienne wrote:
> On Fri, Sep 11, 2015 at 5:58 PM, Richard Hipp wrote:
>
>> Your feedback is encouraged.
>>
>
> https://www.sqlite.org/draft/c3ref/value_subtype.html does not say what
> happens or which value we get,
> should one call sqlite3_value_subtype on a
On 9/11/15, Petite Abeille wrote:
>
> we can now embrace NormalFormZero without undue
> embarrassment.
Well put. :-)
--
D. Richard Hipp
drh at sqlite.org
On 9/11/15, Petite Abeille wrote:
> serialization of the week
The json.org website has been up since 2002. JSON itself predates
that. It is roughly the same age as SQLite itself and is older than
SQLite3. I'm thinking that maybe JSON is not just a passing fad.
Could be wrong though.
--
> SQL commands do not need to be on multiple lines (they only need a
> semicolon after each command).
> But dot commands do.
Good to know
> Have you tried the following?
> (ECHO .bail on
> ECHO %multiple commands%) | sqlite3.exe %dbname%
This could be solution, thanks
> But, much more
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
Pardon me, but: At what point does the code required for the
inclusion of a multitude of supported data formats exceed the core
purpose of the executable?
At some point, obtaining a desired output format (from the potentially
dozens available) might be offloaded to a different executable that
On Fri, Sep 11, 2015 at 11:31 AM, Richard Hipp wrote:
> On 9/11/15, Petite Abeille wrote:
> > serialization of the week
>
> The json.org website has been up since 2002. JSON itself predates
> that. It is roughly the same age as SQLite itself and is older than
> SQLite3. I'm thinking that
On 2015-09-11 9:31 AM, Richard Hipp wrote:
> On 9/11/15, Petite Abeille wrote:
>> serialization of the week
>
> The json.org website has been up since 2002. JSON itself predates
> that. It is roughly the same age as SQLite itself and is older than
> SQLite3. I'm thinking that maybe JSON is
On 11 Sep 2015, at 3:14am, Nguyen Dang Quang wrote:
> SYNCHRONOUS = OFF will make new data not visible to select command?
"PRAGMA synchronous" is about making sure that the disk (or other storage
medium) is updated to reflect changes in the database. Normally changes to
files are held in
SYNCHRONOUS = OFF will make new data not visible to select command? (the
same connection, before commit)
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Friday, September 11,
I assume nothing in the Server logs for security and applications at the
corresponding time?
-Original Message-
The environment is Windows Server 2012 R2, with the database on a local NTFS
drive.
You'd be surprised by what is out there trying to get into your system.
I had port 22 open on my home router to go to a Linux machine so I could
SSH into my home network from anywhere in the world, even though I rarely
ever leave the 519 area code. One day I went to look at my messages log
file
On 9/10/15, Doug Nebeker wrote:
> I'm hoping this might be of help to contribute to SQLite's robustness.
>
> We've got thousands of SQLite installations and they almost always work
> flawlessly. Every once in a while we get a corruption error and I finally
> have a log that catches it.
>
>
On Fri, Sep 11, 2015 at 6:51 AM, Stephen Chrzanowski
wrote:
> You'd be surprised by what is out there trying to get into your system.
>
> I had port 22 open on my home router to go to a Linux machine so I could
> SSH into my home network from anywhere in the world, even though I rarely
> ever
On 11 Sep 2015, at 1:17am, Peter Aronson wrote:
> I do not believe NOT NULL is a CHECK constraint, though you could use gender
> TEXT CHECK(typeof(gender) <> 'null') is and would work much the same way,
> though possibly with less efficiency.
Looking at
On 09/09/2015 08:48 PM, Matthew Flatt wrote:
> The documentation for SQLITE_READONLY_ROLLBACK suggests that it will
> only happen as a result of a previous crash or power failure, where a
> hot journal is left behind. I'm seeing that error without those events
> and with a small number of
Meh. ?Formatting:
sqlite> create table cc (c1 integer not null,c2 integer
check(typeof(c2)<>'null'));
sqlite> insert into cc values (null,null);
Error: NOT NULL constraint failed: cc.c1
sqlite> insert into cc values (1,null);
Error: CHECK constraint failed: cc
sqlite> insert into cc values (1,1);
That would be my assumption. ?And experimentation seems to back it up (at least
for NOT NULL):
sqlite> create table cc (c1 integer not null,c2 integer
check(typeof(c2)<>'null'));sqlite> insert into cc values (null,null);Error: NOT
NULL constraint failed: cc.c1sqlite> insert into cc values
On 11 Sep 2015, at 12:32am, Roman Fleysher
wrote:
> Meanwhile, I tested if PRAGMA integrity_check checks column constraints. You
> can bump up 90% of being sure it does not to 100%. It does not. Is there a
> way to do it, other than export the data out and try to re-insert it?
I can't think
On 11 Sep 2015, at 12:04am, Roman Fleysher
wrote:
> I wanted to check the behavior and set up a test database. I use (for now)
> SQLite 3.8.8.3 and discovered that setting ignore_check_constraints = 'yes'
> did not disable INT PRIMARY KEY NOT NULL constraint on a column. Is that
> expected?
I do not believe NOT NULL is a CHECK constraint, though you could use gender
TEXT CHECK(typeof(gender) <> 'null') is and would work much the same way,
though possibly with less?efficiency.
Peter?
On Thursday, September 10, 2015 4:48 PM, Roman Fleysher wrote:
Dear SQLiters,
I am
I just downloaded and tested using 3.8.11.1. It and 3.8.8.3 have the same
behavior -- do not disable.
Roman
From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at
mailinglists.sqlite.org] on behalf of Richard Hipp
Thank you Richard!
Roman
From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at
mailinglists.sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Thursday, September 10, 2015 7:50 PM
To: General Discussion of SQLite
Dear SQLiters,
I am trying to temporarily disable CHECK constraint given in columns of table
definition. As far as I understand,
PRAGMA ignore_check_constraints='yes';
should do it. However this example demonstrates that it is not:
CREATE TABLE subject(
subjectID INT,
gender TEXT
OK, Thank you.
Meanwhile (again) I check that PRAGMA integrity_check='yes' did not disable
TEXT NOT NULL. Is that a bug in 3.8.8.3?
Roman
From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at
mailinglists.sqlite.org] on behalf
On 10 Sep 2015, at 11:06pm, Roman Fleysher
wrote:
> PRAGMA integrity_check is described to check UNIQUE and NOT NULL constraints.
> Does it check other CHECK constraints specified in the column definition?
I'm 90% sure it does not. You should see this one though:
Thank you, Simon.
Meanwhile, I tested if PRAGMA integrity_check checks column constraints. You
can bump up 90% of being sure it does not to 100%. It does not. Is there a way
to do it, other than export the data out and try to re-insert it?
Roman
From:
On 2015-09-10 09:05 AM, Ludovic Aubert wrote:
> Hello,
>
> I am facing a strange issue:
> I am calling sqlite3_execute(db, "ANALYZE;") from a C program after a bunch
> of CREATE and INSERT,
> Then this program exits.
> Another exe tries to perform some selects into the db, but it seems like
>
Dear all,
I am using System.Data.SQLite for my .Net 2.0 application.
In the app, I always run a sequence like that:
1. Open connection to sqlite and apply pragma: SYNCHRONOUS = OFF ;
JOURNAL_MODE = MEMORY
2. Open a transaction
3. Update a row
4. Select the updated row in
Thank you, Simon. I saw that foreign keys must be checked separately.
I wanted to check the behavior and set up a test database. I use (for now)
SQLite 3.8.8.3 and discovered that setting ignore_check_constraints = 'yes' did
not disable INT PRIMARY KEY NOT NULL constraint on a column. Is that
47 matches
Mail list logo
