Re: [sqlite] Consider adding aggregate function "string_agg" as synonym of "group_concat"

[Zsbán Ambrus]

On Sun, Sep 2, 2018 at 12:29 AM Simon Slavin  wrote:
> On 1 Sep 2018, at 11:03pm, Zsbán Ambrus  wrote:
> > Would you consider adding an aggregate function "string_agg" which is
> > a synonym for the "group_concat" function but takes only two
> > arguments?
>
> Could you look up the definition of the existing function on
>
> <https://sqlite.org/lang_aggfunc.html#groupconcat>
>
> and describe that again ?  The existing function already has a two-argument 
> form.

Thank you for your quick reply.  Yes, the group_concat function does
exactly what I'm trying to do.  It is an aggregate function that
concatenates several strings, one string computed from each row in the
same aggregate group, omitting NULL values, and adds a separator
string between each two strings.  That's why the new function I'm
proposing would be a _synonym_: it behaves exactly the same as the
group_concat function that SQLite already has, except that it might
refuse to work with just one operand.

As an example, I've recently used this function in an aggregate query
in MS SQL, which you can see at
"http://data.stackexchange.com/scifi/query/891712/; .  The query
statement is:

SELECT MAX(r.CreationDate) AS d,
'site://posts/' + CAST(r.PostId AS nvarchar) + '/revisions|' +
STRING_AGG(r.Comment, ' ; ') AS c,
ISNULL(MAX(p.Title), MAX(q.Title)) AS t
FROM PostHistory as r
JOIN Posts as p ON r.PostId = p.Id
LEFT JOIN Posts as q ON p.ParentId = q.Id
JOIN Users AS m ON m.AccountId = ##MyAccountId?1192385##
WHERE m.Id = p.OwnerUserId AND m.ID = r.UserId
AND r.PostHistoryTypeId IN (4,5,6,7,8,9)
AND EXISTS(SELECT o.Id FROM PostHistory AS o
WHERE r.PostId = o.PostId AND o.PostHistoryTypeId IN (4,5,6,7,8,9)
AND m.Id <> o.UserId AND o.CreationDate < r.CreationDate)
GROUP BY r.PostId
ORDER BY MAX(r.CreationDate) DESC;

This groups rows of the PostHistory as r table by the r.PostId field,
takes the r.Comment field from each row within a group, concatenates
it separated by a semicolon and spaces, and returns it in the c column
of the result.  The joins of p and q are used to produce the t column,
the join of m is used in a filter condition, these are not relevant
for understanding how I use the string_agg function here.

The part between double hash marks is a placeholder, which is custom
syntax of data.stackexchange.com.  The + operators here do string
concatenation, I would have to change those to || in SQLite.  But
apart from these two and the string_agg function, the rest of the
statement would probably work in SQLite unchanged if you had the same
database schema.  The SQLite query could be simplified, because you
could omit the cast from number to string and omit the two MAX calls
that produce the t column, but they don't do any harm in SQLite
either.

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

[sqlite] Consider adding aggregate function "string_agg" as synonym of "group_concat"

[Zsbán Ambrus]

Dear SQLite developers,

Would you consider adding an aggregate function "string_agg" which is
a synonym for the "group_concat" function but takes only two
arguments?

This would let me use the function the same in SQLite and MS SQL
Server and PostgreSQL.  The relevant documentation for the function in
the latter two are
"https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017;
and "https://www.postgresql.org/docs/9.0/static/functions-aggregate.html;
.   Neither MS SQL nor PostgreSQL have a "group_concat" function.

This would be very easy to implement from the SQL core, which already
implements group_concat, and more difficult to implement as a user
extension that only uses the SQLite API.  SQLite already has a
precedent for having a synonym for a function provided by the core:
"ifnull" is a synonym for "coalesce" with two arguments.

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

Re: [sqlite] "Responsive" website revamp at www.sqlite.org

[Zsbán Ambrus]

On Mon, Sep 5, 2016 at 10:55 PM, Richard Hipp  wrote:
> Most of the world views the internet on their phone now, I am told,
> and websites are suppose to be "responsive",

Most of the changes work well as far as I've seen, but I have one
problem.  The page "http://sqlite.org/draft/docs.html; gives links
inside sections you can fold or unfold.  If I view the page with
javascript disabled but CSS rules enabled in Firefox, then all but the
first section starts as closed, and there's no obvious way to open
them, so all the links are very hard to access.  I believe the code
for the page should behave in such a way that if the page is viewed
without javascript, then all the folds start as open, and it's only
the javascript that closes them.  (Alternately, if javascript is not
loaded, the fold headers should be links that go to other pages that
show the content under that particular fold.)

The rest of this mail tries to explain how you can make the folds
start as open, but make the javascript close them if they don't load,
in a way that I believe should work on most browsers.  You probably
already know how to do that, in which case you can skip the rest of
this mail.

The CSS embedded to the HTML contains this rule:

.showhide ul {
  display: none;
  list-style-type: disc;
}

Split this to two rules as follows:

.showhide ul {
  display: none;
}
.showhide ul {
  list-style-type: disc;
}

The second rule is irrelevant here.  The first rule is what hides
links under closed folds.  Remove that first rule from the CSS
embedded in the HTML page, and put it to a separate CSS file
accessible through the server, which I'll call "sqlitejs.css" for now.
Then put the following declaration to the HTML HEAD.



This will tell the browser that this style sheet exists, but is not
used by default.  The title attribute is ignored by most browsers, but
it is needed for compatibility with some older browsers which would
enable the style sheet by default otherwise (I learned that the hard
way).

Now you want this stylesheet to be enabled if an only if the
javascript embedded in the page is loaded.  This is useful, because if
the javascript is loaded, then hopefully the user can open the folds
using it.  So append the following lines to the embedded javascript.

function initfolds() {
var ssa = document.getElementById("style_js");
ssa.disabled = false;
};
initfolds();

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

Re: [sqlite] A problem with 'pragma table_info(...)'

[Zsbán Ambrus]

On Wed, Sep 21, 2016 at 9:57 AM, Stepan Zakharov  wrote:
> We are using VFS however, may be that can be ill-implemented somehow so it 
> makes PRAGMA not to work..

It can.  And I think you're the third person on the mailing list to
fall into that trap.

From an earlier mail
("http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2015-April/059108.html;)
> could you check if it's your
> vfs that is handling those pragmas?  The documentation at
> "http://sqlite.org/c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma;
> describes that when you run a PRAGMA statement on a database, sqlite
> will call the xFileControl method of the vfs file handle (as given in
> a sqlite3_io_methods structure) with SQLITE_FCNTL_PRAGMA as the second
> parameter.  If that method returns SQLITE_OK, then sqlite will assume
> the vfs has handled the pragma, and will not handle it itself.  This
> could cause pragmas to fail silently.

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

Re: [sqlite] Problem with rename table

[Zsbán Ambrus]

On Tue, Sep 6, 2016 at 3:37 PM, Radovan Antloga
 wrote:
> I can't find a solution how to fix my database after
> I have renamed table DOKUMENTI to DOKUMENTI2.
> Table DOKUMENTI had trigger dokumenti_trigger1
> and after renaming table I cant execute any sql. I forgot
> to drop trigger first. So now I always get error:
> malformed database schema (dokumenti_trigger1) -
> no such table main.dokumenti.

I wonder, in such a case, is it possible to temporarily disable
triggers with the sqlite3_db_config(db,
SQLITE_DBCONFIG_ENABLE_TRIGGER, 0, (int *)nullptr); call, then drop
that trigger, then re-enable triggers?  I haven't tried to see if this
works or not.

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

Re: [sqlite] Number of SQLITE_OMIT_ defines not working correctly

[Zsbán Ambrus]

On Sat, Jul 16, 2016 at 6:53 PM, Olivier Mascia  wrote:
> I think you might have skipped this: 
> https://www.sqlite.org/compile.html#omitfeatures
>
>> Important Note: The SQLITE_OMIT_* options may not work with the 
>> amalgamation. SQLITE_OMIT_* compile-time options usually work correctly only 
>> when SQLite is built from canonical source files.

Let me add that the "http://sqlite.org/howtocompile.html; page lists
exactly which of those options you cannot use when compiling the
amalgamation.

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

Re: [sqlite] SQLite Logo

[Zsbán Ambrus]

On Fri, Jun 24, 2016 at 2:03 PM, R.A. Nagy  wrote:
> I am putting together a commercial training for SQLite. I would like to use
> the SQLite logo - as seen on the website - on the cover of the materials.


SQLite has a logo...

wow.  I didn't notice that before.  I just checked the homepage
"http://sqlite.org/;, and it shows a feather in the corner that looks
basically the same as the Apache logo.  Is there a reason why they're
so similar?  I mean, they're both for software, so it can be confusing
for people who (unlike me) actually pay attention to logos.

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

Re: [sqlite] unable to set 'PRAGMA journal_mode = off' for vfs

[Zsbán Ambrus]

On Tue, Jul 5, 2016 at 4:46 PM, Dobrean, Adrian  wrote:
> I am unable to turn off journal for vfs (I am using SQLite version 3.8.7.2).
> I am not getting any error and the journal mode does not get set (i.e. to 
> PAGER_JOURNALMODE_OFF).
> Any idea what am I doing wrong? (it used to work in 3.7.5)
>
>
> sqlite3_open_v2("/path/mysqfile", , SQLITE_OPEN_READWRITE | 
> SQLITE_OPEN_CREATE, "myvfs");
> sqlite3_exec(ddDB, "PRAGMA journal_mode = off",NULL, 0, );
>
On Tue, Jul 5, 2016 at 5:05 PM, Dobrean, Adrian  wrote:
> I am not getting any error, i.e. sqlite3_exec returns 0 and zErrMsg is NULL.

If you define your custom vfs, make sure to check what your
xFileControl method (in the sqlite3_io_methods structure) does when
called with SQLITE_FCNTL_PRAGMA as its second argument.  This vfs
method can override pragmas that should normally be handled by the
sqlite3 core, not the vfs.  See
https://www.sqlite.org/c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma
for documentation.

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

[sqlite] The Session Extension (future SQLite extension)

[Zsbán Ambrus]

As for the session extension
"https://www.sqlite.org/draft/sessionintro.html;, what I'd like to ask
is when this is more useful than the RBU extension
"http://sqlite.org/rbu.html; ?  The two seem to serve a similar
purpose.

Ambrus

[sqlite] Work toward making the schema parsing logic simplier

[Zsbán Ambrus]

On 1/15/16, Domingo Alvarez Duarte  wrote:
> Now that you are refactoring on the schema parsing could be nice if somehow
> sqlite3 expose the schema/sql parser for developers.

Note that sqlite3 already exposes most of the schema in a form
readable to an application.  The SQLITE_MASTER and SQLITE_TEMP_MASTER
tables give you the list of tables and views.  The PRAGMA table_info
statement gives information about a table or view; PRAGMA index_list
and PRAGMA index_info give information about indexes.

-- Ambrus

[sqlite] Index on computed value?

[Zsbán Ambrus]

On Wed, Dec 16, 2015 at 9:17 AM, Deon Brewis  wrote:
> Is it possible to have an index on a computer value?
>
> E.g. I have a 40 byte value in one of my columns. I only want an index over 
> the first 4 bytes of it.
>
> However, I don't really want to repeat those 4 bytes inside another column on 
> the main table.

See http://sqlite.org/expridx.html

Ambrus

[sqlite] SQLite crashes

[Zsbán Ambrus]

On the SQLITE_OMIT_LOOKASIDE macro.

On Fri, Nov 27, 2015 at 2:45 PM, Richard Hipp  wrote:
> The lookaside memory allocator is a fast memory pool used by
> individual database connections for short-lived memory allocations.
> It makes SQLite run faster, but by bypassing the system
> malloc()/free() it can mask some types of heap corruption.  Best to
> turn it off when dealing with suspected heap problems.

This is useful to know.  Can you put it to the documentation under
http://sqlite.org/compile.html#omit_lookaside please?

-- Ambrus

[sqlite] Minor errors in the documentation, building sqlite

[Zsbán Ambrus]

On 11/9/15, Richard Hipp  wrote:
> It is traditional, in a permuted index, to include the complete
> unedited title of each document in the index, even if that title
> begins with a stop-word.  You'll notice that documents that have
> "SQLite" as an interior word are not listed among the "SQLite"
> documents in the permuted index - only those documents that actually
> being with the word "SQLite".  You'll likewise notice that there are
> examples of titles in the permuted index that begin with "The", though
> interior "The" words in titles are not indexed.

That make sense.  Thank you for the explanation.

-- Ambrus

[sqlite] Minor errors in the documentation, building sqlite

[Zsbán Ambrus]

Hello.  In this mail I'd like to point out some minor problems with
the SQLite documentation.  (By the way, let me thank you for adding
named anchors to every heading in the docs so I can link to them
easily.  A lot of webpages don't do this.)

1. In Compilation Options For SQLite "http://sqlite.org/compile.html;,
under HAVE_MALLOC_USABLE_SIZE, "Apply systems" is probably a typo for
"Apple systems".  Please fix it, because it's confusing.  If it does
not mean Apple systems, then please clarify in the text what it means.

2. In the same document, in chapter 1.7, this sentence is confusing:
"Any SQLITE_OMIT_* options which can be used directly with the
amalgamation are listed below, however, the warnings in the following
paragraph should be noted."  The document should either explain which
of the omit options can be used with the amalgamation, or explicitly
tell that the "http://sqlite.org/howtocompile.html; document (that
this sentence links to) tells which options can't be used with the
amalgamation.  As a fix, I suggest you replace that paragraph and the
following one with this.

Important Note: The SQLITE_OMIT_* options do
not work with the amalgamation.
SQLITE_OMIT_* compile-time options only work correctly when SQLite is
built from canonical source files.
If any of these options are defined, then the same set of
SQLITE_OMIT_* options must also be defined when using the 'lemon' tool
to generate the parse.c file and when compiling the 'mkkeywordhash'
tool which generates the keywordhash.h file. Because of this, some of
these options may only be used when the library is built from
canonical source, not from the amalgamation, nor from the collection
of pre-packaged C files provided on the website to build older
versions of SQLite for non-Unix like platforms.  http://sqlite.org/howtocompile.html;>How To Compile SQLite
lists the options that can't be used directly with the amalgamation.

3. There's something of a contradiction between the reference
Compilation Options For SQLite "http://sqlite.org/compile.html; and
the introductory document How To Compile SQLite
"http://sqlite.org/howtocompile.html#cli;.  The former document says
that "All of the SQLITE_OMIT_* options are unsupported".  The latter
document, which describes the easiest ways to build SQLite, however,
says "If these features are not required, then they can be omitted
using SQLITE_OMIT_LOAD_EXTENSION compile-time option" without
mentioning that the option is unsupported.  Either something in the
former should claim that the SQLITE_OMIT_LOAD_EXTENSION option is
supported as an exception, or the latter document should not mention
this option or should mention it only with a note that it's
unsupported.  Marking this compilation option as supported would be
preferable if you take the claims of SQLite Is Self-Contained
"http://sqlite.org/selfcontained.html; seriously, since that document
says "SQLite is written in ANSI-C and should be easily compiled by any
standard C compiler. It makes minimal use of the standard C library".

4. The permuted index
"http://www.sqlite.org/draft/sitemap.html#pindex; has several entries
starting with "SQLite".  Most of those are superfluous, because the
whole index is about SQLite documentation only.  I think you should
remove most of them.  Keep "SQLite As An Application File Format",
"SQLite Consortium", "SQLite In 5 Minutes Or Less" (unless you add an
index entry at "5 Minutes" instead), "SQLite Is Self-Contained",
"SQLite Is Serverless", "SQLite Is Transactional".  Make sure every
entry you remove has an entry elsewhere in the permuted index.

-

The following minor problems with the documentation are repeats from
older mails I sent.

5. Something in the documentation should say explicitly that you're
not allowed to use parameters (placeholders) in expressions where the
expression itself would be stored in the schema.  Recent versions of
SQLite seem to enforce this in all the cases I've tested, but
"http://www.sqlite.org/draft/lang_expr.html#varparam; doesn't mention
it.

6. There are some typos on the documentation page for SQLite Result
codes "http://sqlite.org/rescode.html;.

Under the heading "(1038) SQLITE_CANTOPEN_CONVPATH", the paragraph
talks about the error code "SQLITE_CANTOPEN_SEEK".  That constant does
not exist.  The text probably means "SQLITE_CANTOPEN_CONVPATH"
instead.

Similarly, under the heading "(6410) SQLITE_IOERR_GETTEMPPATH" and
"() SQLITE_IOERR_CONVPATH", the text mentions "SQLITE_IOERR_SEEK"
which is an existing error code, but is probably not the one meant.

The meaning of several error codes are also not documented on this
page, such as those of SQLITE_ABORT, SQLITE_CANTOPEN, SQLITE_EMPTY,
SQLITE_FORMAT, SQLITE_IOERR, SQLITE_MISMATCH,  SQLITE_IOERR_READ,
SQLITE_IOERR_WRITE, etc.  This may not be a bug. The sqlite3.h header
file has short comments next to each primary error code at least.

7. The Indexes On Expressions "http://sqlite.org/draft/expridx.html;
document should be 

[sqlite] PRAGMA integrity_check says row 2 missing from index

[Zsbán Ambrus]

Hello.

The PRAGMA integrity_check is telling me that "row 2 missing from
index tbvk".  This is reproducible with the following commands.
Should I be getting this message?  Or is it a bug?

SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE tb(k INTEGER PRIMARY KEY, v);
sqlite> CREATE INDEX tbvk ON tb(v, k);
sqlite> INSERT INTO tb(k, v) VALUES (8, 356282677878746339);
sqlite> INSERT INTO tb(k, v) VALUES (7, 356282677878746339.0);
sqlite> INSERT INTO tb(k, v) VALUES (6, 356282677878746340);
sqlite> DELETE FROM tb WHERE k = 7;
sqlite> SELECT k, v, typeof(v) FROM tb ORDER BY v, k;
6|356282677878746340|integer
8|356282677878746339|integer
sqlite> PRAGMA integrity_check;
row 2 missing from index tbvk
sqlite> DROP INDEX tbvk;
sqlite> SELECT k, v, typeof(v) FROM tb ORDER BY v, k;
8|356282677878746339|integer
6|356282677878746340|integer
sqlite> PRAGMA compile_options;
OMIT_LOAD_EXTENSION
SYSTEM_MALLOC
THREADSAFE=1
sqlite> .quit

I am using sqlite 3.9.2 built from the amalgamation on linux x86_64
with gcc 4.8.1 with the following command line:

gcc -pthread -O2 -DSQLITE_OMIT_LOAD_EXTENSION=1 -o sqlite3 sqlite3.c shell.c

-- Ambrus

[sqlite] Non-transitive numeric equality

[Zsbán Ambrus]

Dear SQLite,

It seems that equality of numeric values isn't transitive, when both
integers and reals are involved.  Here's an example output from the
shell, which shows that the numeric value in the 'c' row is equal to
both the value in the 'b' and the 'd' rows, but the value in the 'b'
row isn't equal to the value in the 'd' row.  Neither null values nor
collations seem to be involved here.

SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table tb(n, v);
sqlite> insert into tb(n, v) values ('b', 1<<58), ('c', (1<<58) +
1e-7), ('d', (1<<58) + 1);
sqlite> select n, v, typeof(v) from tb;
b|288230376151711744|integer
c|2.88230376151712e+17|real
d|288230376151711745|integer
sqlite> select l.n, r.n, l.v = r.v from tb as l, tb as r;
b|b|1
b|c|1
b|d|0
c|b|1
c|c|1
c|d|1
d|b|0
d|c|1
d|d|1
sqlite> .quit

Is this behavior by design?  Can this cause problems with indexes,
sorting or grouping by?

I believe the cause of this behavior is the sqlite3MemCompare private
function in the sqlite3 implementation, which compares an integer to a
real by converting the integer to a real.  The conversion can lose
precision of the integer, and as a result, the value in the 'd' row
compares equal to the value in the 'c' row, despite that the numeric
values they represent isn't equal.  Sadly, comparing an integer to a
floating point number is not easy, so I don't know an easy fix.

I ran the test above with sqlite 3.9.2 built from the amalgamation
source on windows x86_64 with gcc 4.8.3 and the following compiler
options:

gcc -march=core2 -mavx -mvzeroupper -pthread -O2 -g
-DSQLITE_OMIT_LOAD_EXTENSION=1 -o sqlite3 sqlite3.c shell.c

-- Ambrus

[sqlite] The problem of inserting data too slow with index

[Zsbán Ambrus]

On 10/30/15, aa  wrote:
>But recently I meet a problem about inserting data into table.It is
> too slow whit index.
>The first I create a table like this:
> CREATE TABLE mac_tb  (mac BIGINT PRIMARY KEY?
> If I insert into mac_tb with mac ordey by num desc or asc , then the
> speed is fast.
> If I insert the mac randomly, then the speed will grow slower whit
> the increasing of data.

Don't forget that you should use transactions.  Start a transaction
with the BEGIN TRANSACTION statement, insert many rows (possibly all
million you have, possibly just ten thousand), and then use the COMMIT
TRANSACTION statement.  If you do not explicitly start a transaction,
then sqlite will execute each statement in a separate transaction,
which could mean a transaction per row.  This can be expensive, as
described in "http://sqlite.org/faq.html; .

Further, if I understand correctly, if you do lots of inserts at the
same time, then WAL mode might slow down the operation a bit.  WAL
mode is not the default, but someone might have enabled it in your
database.  Check with the PRAGMA journal_mode command that it is not
enabled: if that command returns "wal", then WAL mode is active.  Try
to disable WAL mode if you're doing mass inserts.

-- ambrus

[sqlite] SQLite version 3.8.12 enters testing

[Zsbán Ambrus]

On Wed, Oct 7, 2015 at 9:42 PM, Richard Hipp  wrote:
> On 10/7/15, Zsb?n Ambrus  wrote:
> New documentation covering indexes on expressions has been added.
> Please let me know if you think more is needed.

Thanks, that's much better.  The
"http://sqlite.org/draft/expridx.html; documentation tells all I
wanted to know.  However, it seems you forgot to link that
documentation from "http://sqlite.org/draft/docs.html; .  It should
probably be linked from under the heading "SQLite Features and
Extensions".

There is one apparent inconsistency I was wondering about.  For a
partial index, the WHERE clause of the index cannot contain functions;
whereas for an index on an expression, the expression can contain
deterministic functions.  What is the explanation of this difference?
Is it only historical, because the deterministic functions flag is a
new addition?

Ambrus

[sqlite] SQLite version 3.8.12 enters testing

[Zsbán Ambrus]

On Wed, Oct 7, 2015 at 4:42 PM, Richard Hipp  wrote:
> A preliminary change log for version 3.8.12 can be seen at
> https://www.sqlite.org/draft/releaselog/3_8_12.html and preliminary
> documentation can be seen at https://www.sqlite.org/draft/
>
> If you have issues or concerns with the current SQLite trunk, please
> speak up *now*.

I have concerns with the documentation.

"http://sqlite.org/draft/changes.html; mentions "Added support for
indexes on expressions.", and points to
"http://sqlite.org/draft/lang_createindex.html; which tells that an
index row may contain an expression computed from the columns of the
table row.  This implies that an index could be ordered by such an
expression, or it could just cover such an expression if the previous
columns of that index determine the sort order uniquely.

However, I have not seen anything in
"http://sqlite.org/draft/optoverview.html; on how such an expression
column in an index can be used.  Section 1.0 in that document does not
seem to indicate that sqlite can use an expression index to satisfy a
WHERE clause involving an expression computed from a column.
According to section 6.0, this seems to imply that such indexes also
cannot be used to satisfy the ON clause of a JOIN.  Section 8.0
mentions only columns, so I can't tell whether sqlite will use the
expression column of an index to get the value of a result-column
expression of a SELECT from the index, without having to get the value
of the columns involved in the expression from the table, if those
columns aren't in the index.  Section 9.0 seems the most vague, I
can't tell from that whether sqlite can use an index on an expression
to speed u pa SELECT with an ORDER BY clause on the same expression.

It would be nice if you could modify
"http://sqlite.org/draft/optoverview.html; to explicitly mention how
an index on an expression can be used.  If sqlite cannot yet use
indexes on expressions in any way, but only create and maintain them,
making them effectively useless until a future version of sqlite that
will be able to use them, then instead mention that fact in
"http://sqlite.org/draft/lang_createindex.html; .

Thanks,

Zsb?n Ambrus

[sqlite] Parser Grammar for MAX,MIN,SUM,COUNT,AVG functions

[Zsbán Ambrus]

On Mon, Sep 21, 2015 at 9:11 AM, Prakash Premkumar
 wrote:
> I'm reading the sqlite parser grammar.
>
> I could not find the grammar rules which parses aggregate functions like
> MAX,MIN,SUM,COUNT,TOTAL.
>
> Can you please tell me how the aggregate functions are parsed (the grammar
> rule that parses them)

A call to an aggregate function is parsed the same way as a call to an
ordinary function.  Sqlite then looks up the function name and number
of arguments to determine if this corresponds to an ordinary function
or an aggregate function, and changes the meaning of the query
according to that.

The page http://sqlite.org/lang_select.html details how the meaning of
a SELECT statement changes if the selected expression contains an
aggregate function: it becomes an aggregate query even if there is no
GROUP BY clause, and it computes one result row from all the input
rows.  Aggregate functions can also be used in the expression of
ordinary aggregate queries, containing a GROUP BY clause, or in the
HAVING clause of such a query.

I probably forgot a few more uses of aggregate functions, but in most
other contexts, an aggregate function found in an expression results
in an error.

Ambrus

[sqlite] Escape pathname to URL suitable for ATTACH

[Zsbán Ambrus]

Hello, sqlite3 mailing list.

The ATTACH command and the sqlite3_open function can take an URI
instead of a plain filename, if sqlite3 is so configured.  This is
documented at "http://sqlite.org/uri.html;.  This is useful, because
it lets you specify extra options for attaching that you couldn't
easily set otherwise.

The documentation explains how to quote a filename to be suitable as
an URI path.  Is there a C function in the public API that I can call
to do this quoting for me automatically?  If so, what is it or where
is it documented?  This would be useful for using URI filenames.

It's also not clear to me how to quote filenames on unix if they
contain non-ascii bytes.  The description of URI filenames at the
documentation page of the sqlite3_open function at
"http://sqlite.org/c3ref/open.html; says that the filename has to be
UTF-8 encoded before it is URI-escaped, which makes sense on a Windows
system, but not very meaningful on a unix system where the filenames
are strings of bytes.

-- Ambrus

[sqlite] Proper way to abort

[Zsbán Ambrus]

On Mon, Aug 24, 2015 at 6:22 PM, Scott Doctor  wrote:
> I have some queries that may take 5-15 seconds to complete. Sometimes the
> situation changes shortly after starting the query where my program does not
> need those results anymore and the program wants to abort and begin a
> different query instead.
>
> My question is: What is the proper way to abort a query, or other operation
> during execution that will not cause any issues?

You can use the sqlite3_progress_handler function for this, as
documented in "http://sqlite.org/c3ref/progress_handler.html;.

-- Ambrus

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Zsbán Ambrus]

On Mon, Jul 27, 2015 at 9:35 PM, Simon Slavin  wrote:
> On 27 Jul 2015, at 8:03pm, Zsb?n Ambrus  wrote:
> I tried this once a couple of years ago, and both platforms use whatever the 
> expected variable name was for that OS.  In other words, a native programmer 
> to that OS would get whatever behaviour they expected.

Hopefully that means TMPDIR on unix and TEMP on windows, which seem to
be the most widely used environment variables for this.

Sadly, programs aren't uniform in what environment variable(s) they
respect for this.  I think something on some system uses the TMP or
TMP_DIR variables. Some simply don't respect anything and just put
temporary files in the current directory or your home directory
regardless.

>From the source code, it seems that for at least some operations,
sqlite3 checks the following environment variables: SQLITE_TMPDIR,
TMPDIR, TMP, TEMP, USERPROFILE.

-- Zsb?n Ambrus

[sqlite] pragma temp_store_directory is deprecated, what is the alternative?

[Zsbán Ambrus]

On Mon, Jul 27, 2015 at 12:28 PM, Richard Hipp  wrote:
> On 7/27/15, Paolo Bolzoni  wrote:
>> I found the temp_store_directory, but it is deprecated. So I was wondering, 
>> what is the suggested alternative?
>
> Set the TEMP environment variable to the location of your temporary
> storage area.

Thank you, I was wondering on this too.  Can you please document this
right at that pragma on the pragmas page
"http://sqlite.org/pragma.html#pragma_temp_store_directory; ?

Does this work with the same environment variable name on both unix
and windows?  I'm asking because unix and windows programs each use
different conventions for what environment variable to care about when
determining the temporary directory, and these conventions differ on
unix and windows, though I believe TEMP is the customary variable on
windows (it comes from back when the DOS shell stored pipe data
there).

Tangentially related, is there a way to set a limit the size of
temporary files sqlite3 will create, such as with a pragma or
sqlite3_limit call?  I've asked this already around 2015-05-18, but I
don't think I've got an answer.

-- Zsb?n Ambrus

[sqlite] Likelihood() in Left Join affecting results

[Zsbán Ambrus]

On Thu, Jun 18, 2015 at 4:34 PM, Richard Hipp  wrote:
> Thanks for the bug report.  The problem is now fixed on trunk.
>
> On 6/18/15, Jean Chevalier  wrote:
>> The likelihood() function, which should help select a query plan but
>> otherwise be logic-neutral, nevertheless seems to affect results returned by
>> a Left Join, when used as part of the join condition, possibly in other
>> circumstances.

Hello.  May I ask if fixing this bug is supposed to be mentioned on
the draft website page "http://sqlite.org/draft/changes.html; ?  If
so, which entry covers it?  If not, will you add an entry later?

-- Ambrus

[sqlite] Limit size of write-ahead log file

[Zsbán Ambrus]

On 5/19/15, Kees Nuyt  wrote:
> Did you consider
>   PRAGMA wal_autocheckpoint=N; and
>   PRAGMA database.wal_checkpoint(); ?
>
> http://www.sqlite.org/pragma.html#pragma_wal_autocheckpoint

Thank you for your reply.  Yes, but that will not always limit the
size of the wal file.  I believe the wal file could still grow very
large if the changes of the transaction doesn't fit in SQLite's page
cache.  It also won't help limit the size of other temporary files.

-- Zsb?n Ambrus

[sqlite] Re : index broken by insert

[Zsbán Ambrus]

On 5/20/15, REGIANY Lucie  wrote:
> J ai cr?? une table
> CREATE TABLE table 1 (idsas (5) PRIMARY KEY NOT NULL);

I don't see how that would work.  Sqlite complains about syntax error
for that.  If "table 1" is meant to be the name of the table, you have
to double quote it, because it contains a space.  Similarly, if "idsas
(5)" is the name of the column, you have to double quote it; or if
"(5)" is somehow part of the column type, then quote that part alone
or add a word before it.

For example, with the column called "idsas", this statement could work:

CREATE TABLE "table 1" (idsas BLOB(5) PRIMARY KEY NOT NULL);

or with the column called "idsas (5)", this statement could work:

CREATE TABLE "table 1" ("idsas (5)" PRIMARY KEY NOT NULL);

-- Ambrus

[sqlite] Limit size of write-ahead log file

[Zsbán Ambrus]

The PRAGMA max_page_count statement lets me set a limit on the size
the database is allowed to grow.  This is useful to protect myself
against accidental errors in my program, where I fill the file system
with a huge database file, which could disrupt other processes that
are trying to write the same filesystem.

However, I don't see a setting that would let me limit the size of the
write-ahead log file, or of other temporary files listed in
"http://sqlite.org/tempfiles.html;.  Is there a setting for that?
Also, is there perhaps a way to limit the size of temporary files
opened for a database handle?

I expect that SQLite itself should be able to handle such a limit,
because SQLite promises that it can handle failed filesystem
operations gracefully in all cases.

The PRAGMA journal_size_limit does not do this, at least according to
the documentation.  The WAL file may still grow limitlessly regardless
that pragma as long as that file is still needed by active database
connections.

-- Zsb?n Ambrus

[sqlite] Can I define collation-aware SQL functions?

[Zsbán Ambrus]

On 5/15/15, Richard Hipp  wrote:
> No there is not.  The APIs used to implement those built-in functions
> are not exposed to the application-defined function interface.

Understood. Thank you for the reply.

-- Ambrus

[sqlite] Determine type of prepared statement via C Interface?

[Zsbán Ambrus]

On Fri, May 8, 2015 at 8:20 PM, Simon Slavin  wrote:
> Suppose you have this statement
>
> DELETE FROM myTable WHERE id=600
>
> and there are no rows with id=600.  I presume that the function will return 
> FALSE but a literal reading of the description says that it will return TRUE. 
>  If I'm right then it might be useful to rewrite the documentation a little.

I don't think sqlite_stmt_readonly does that.  If you want to know if
a statement has actually written to the database, call
sqlite_total_changes before and after executing the statement, and
compare their return values.  Make sure you don't run other statements
or write operations on the database during that time.

-- Ambrus

[sqlite] Problems with pragma journal_mode

[Zsbán Ambrus]

On Wed, Apr 22, 2015 at 10:46 AM, Janke, Julian
 wrote:
> I have tested some of the other pragmas and none worked. After discussing 
> with you now I come to the conclusion that the problem is caused more by my 
> local setup. I

In that case, as you have a custom vfs, could you check if it's your
vfs that is handling those pragmas?  The documentation at
"http://sqlite.org/c3ref/c_fcntl_busyhandler.html#sqlitefcntlpragma;
describes that when you run a PRAGMA statement on a database, sqlite
will call the xFileControl method of the vfs file handle (as given in
a sqlite3_io_methods structure) with SQLITE_FCNTL_PRAGMA as the second
parameter.  If that method returns SQLITE_OK, then sqlite will assume
the vfs has handled the pragma, and will not handle it itself.  This
could cause pragmas to fail silently.

-- Ambrus

[sqlite] Hex literals not working?

[Zsbán Ambrus]

On Thu, Apr 16, 2015 at 10:29 AM, Ketil Froyn  wrote:
> Hexadecimal integer literals follow the C-language notation of
> "0x" or "0X" followed
> by hexadecimal digits. For example, 0x1234 means the same as 4660...
>
> Am I doing sometihng wrong? Or is this a feature that is newer than
> Ubuntu 14.04's bundled sqlite3, which is 3.8.2?

See http://sqlite.org/changes.html which tells you that hexadecimal
literals are available from sqlite version 3.8.6.

-- ambrus

[sqlite] Request: Metadata about C API constants and functions

[Zsbán Ambrus]

On 4/10/15, Dominique Devienne  wrote:
> But that's build time. There is no way (i.e. pragma) to list registered SQL
> functions at runtime.
>
> This is something that I've asked for before, and I'm having trouble
> understanding why nobody cares.
>
> When you run an SQLite "shell", the host program can have enabled/disabled
> built-in functions at build-time, and/or added any number of custom SQL
> functions. But you have no way to know.

Um, you're talking about SQL functions.  But I think Roger asked for C
functions in the public C api of Sqlite.

Ambrus

Re: [sqlite] sqlite3 tool bug

[Zsbán Ambrus]

On 1/16/15, Dave Dyer  wrote:
> I have a class of database for which using sqlite3 to create
> a copy via the "pipe" method fails.   Using an explicit intermediate
> file seems to work ok.
>
> I can supply a sample database to anyone interested in investigating.

Rather than the full database, can you show us the full schema of this
database, including triggers?

Does this database have any custom collations (mentioned after the
COLLATE keyword in the schema), or custom SQL functions mentioned in
the schema (such as in DEFAULT or CHECK constraint clauses)?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting SQLITE_IOERR_WRITE when running sqlite

[Zsbán Ambrus]

On 1/2/15, Dan Kennedy  wrote:
> On 01/02/2015 04:44 PM, Waiba, Aswin wrote:
>> we were getting SQLITE_IOERR_WRITE (778).
>
> It means a call to write(), pwrite(), fallocate() or similar has failed.
> Because it ran out of disk space, or the media was removed or perhaps is
> faulty. Or a bug in SQLite might be causing invalid parameters to be
> passed to one of these system calls.

Note that if you run out of disk space, you're usually supposed to get
 SQLITE_FULL as the error code from sqlite, rather than
SQLITE_IOERR_WRITE.

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

[sqlite] Docs: typos in SQLite Result Codes

[Zsbán Ambrus]

There are some typos on the documentation page for SQLite Result codes
"http://sqlite.org/rescode.html;.

Under the heading "(1038) SQLITE_CANTOPEN_CONVPATH", the paragraph
talks about the error code "SQLITE_CANTOPEN_SEEK".  That constant does
not exist.  The text probably means "SQLITE_CANTOPEN_CONVPATH"
instead.

Similarly, under the heading "(6410) SQLITE_IOERR_GETTEMPPATH" and
"() SQLITE_IOERR_CONVPATH", the text mentions "SQLITE_IOERR_SEEK"
which is an existing error code, but is probably not the one meant.

The meaning of several error codes are also not documented on this
page, such as those of SQLITE_ABORT, SQLITE_CANTOPEN, SQLITE_EMPTY,
SQLITE_FORMAT, SQLITE_IOERR, SQLITE_MISMATCH,  SQLITE_IOERR_READ,
SQLITE_IOERR_WRITE, etc.  This may not be a bug, because the page
claims only that the document "strives to" explain each error code.
The sqlite3.h header file has short comments next to each primary
error code at least.

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

Re: [sqlite] BUG: Aggregate functions in subqueries

[Zsbán Ambrus]

On 9/14/14, Lea Verou  wrote:
> Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM
> table return the value of y from the same row that contains the maximum x
> value. However, this:

Hello!  I don't think this is a bug.  The documentation for the SELECT
statement at "http://sqlite.org/lang_select.html; says:

> If the SELECT statement is an aggregate query without a GROUP BY clause, then 
> [...] Each non-aggregate expression in the result-set is evaluated once for 
> an arbitrarily selected row of the dataset. The same arbitrarily selected row 
> is used for each non-aggregate expression.

This applies to your query, so y will return the value y from any row.
If it happens to return the value where x is maximal, that is an
accident, and might depend on what indexes you have or how sqlite
chooses to optimize your query.  You have no reason to trust sqlite to
choose any particular column.

If you would definitely like to get the value of y from the row where
x is maximal, I recommend a query like this:

  SELECT x, y FROM sometable ORDER BY x DESC LIMIT 1;

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

Re: [sqlite] Docs: ON CONFLICT clause versus CHECK constraints

[Zsbán Ambrus]

On 8/15/14, Richard Hipp  wrote:
> http://www.sqlite.org/draft/lang_conflict.html

Thanks for fixing it.

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

[sqlite] Docs: ON CONFLICT clause versus CHECK constraints

[Zsbán Ambrus]

Hi, I'd like to report a small confusion in the documentation.

The documentation page for the ON CONFLICT clause at
"http://sqlite.org/draft/lang_conflict.html; describes at the
beginning what kind of constraints this clause deals with:

> The ON CONFLICT clause applies to UNIQUE and NOT NULL constraints [...]. The 
> ON CONFLICT algorithm does not apply to FOREIGN KEY constraints.

This leaves open the question on what this clause on an INSERT or
UPDATE statement does with CHECK constraints.  The docs for CREATE
TABLE at "http://sqlite.org/draft/lang_createtable.html; does answer
this, and the constraints page mentions CHECK constraints later as
well.  Still, I find this phrasing a bit confusing, as if the
documentation was trying to hide something about CHECK constraints.

Thus, could you please just add CHECK constraints to that first
sentence I've quoted?  Thanks,

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

Re: [sqlite] Docs: link "How To Compile SQLite" from main documentation list

[Zsbán Ambrus]

On 8/5/14, Richard Hipp <d...@sqlite.org> wrote:
> On Tue, Aug 5, 2014 at 3:51 PM, Zsbán Ambrus <amb...@math.bme.hu> wrote:
>> please link "http://sqlite.org/howtocompile.html;
>> from "http://sqlite.org/docs.html; .

Thank you for adding the link so quickly.

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

[sqlite] Segmentation fault in command line using aggregate function in column default value

[Zsbán Ambrus]

Dear sqlite3 maintainers,

I've got a segmentation fault when trying to execute the following two
statements in the sqlite 3.8.5 command line program:

CREATE TABLE tab(amt DEFAULT (max(1))); INSERT INTO tab DEFAULT VALUES;

I believe this is a bug, because the vanilla command line should not
segfault from an invalid command, and should give some error message
instead.  The statement has an error because max(1) is a call to an
aggregate function so should only appear in a result column of a
SELECT statement or similar contexts, not in a column default.

I don't load any database for this, so the command line starts from a
new temporary database with empty schema and default settings.

The command line reports its version as "SQLite version 3.8.5
2014-06-04 14:06:34".  It is running on a debian-based linux x86-64
machine.  I have built it from the sqlite-amalgamation-3080500 source
to x86-64 application with a vanilla gcc 4.8.1 with the following
compiler command line:

gcc -Wall -ldl -lpthread -O2 -o sqlite3 shell.c sqlite3.c

If you need any further information for reproducing this bug, please contact me.

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

[sqlite] Docs: link "How To Compile SQLite" from main documentation list

[Zsbán Ambrus]

Dear sqlite maintainers!

Besides my previous question "Where exactly are parameters accepted in
an expression?" let me ask an easier request for improving the sqlite3
documentation on the homepage.

The document "How To Compile SQLite" at
"http://sqlite.org/howtocompile.html; is very important, because it
contains information that almost anyone using the C API directly wants
to know.

Yet, this documentation is very well hidden.  In particular, it is not
currently linked from the main documentation list (Categorical Index
Of SQLite Documents) at "http://sqlite.org/docs.html; .  If someone
wants to find out how to compile sqlite3, and search that list, they
will find only documents for more advanced users, such as "Compilation
Options" and "Customizing And Porting SQLite".  This even happens to
me, despite that I know there's a "how to compile" document, I can't
find the link.  So please link "http://sqlite.org/howtocompile.html;
from "http://sqlite.org/docs.html; .

Further, you may also want to link this document from some other
documents, such as "http://sqlite.org/quickstart.html; (SQLite In 5
Minutes Or Less) or "http://sqlite.org/amalgamation.html; (The SQLite
Amalgamation).

Thanks,

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

Re: [sqlite] Expected behaviour of COUNT with no GROUP BY?

[Zsbán Ambrus]

On 8/4/14, Mark Lawrence  wrote:
> I try to remember to define GROUP BY values when using aggregate
> functions (and I wish SQLite considered it an error otherwise) but I
> forget once and the result surprised me.
>
> It is expected behaviour that a row is returned in this situation even
> through the tables are empty?  Adding a "GROUP BY x.id" returned the
> expected empty set.

This is the behavior I expect from the documentation.

Specifically, the documentation for the SELECT statement at
"http://sqlite.org/lang_select.html; says

> If the SELECT statement is an aggregate query without a GROUP BY clause, then 
> each aggregate expression in the result-set is evaluated once across the 
> entire dataset. [...] Or, if the dataset contains zero rows, then each 
> non-aggregate expression is evaluated against a row consisting entirely of 
> NULL values.
>
> [...] An aggregate query without a GROUP BY clause always returns exactly one 
> row of data, even if there are zero rows of input data.

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

[sqlite] Where exactly are parameters accepted in an expression?

[Zsbán Ambrus]

Hello sqlite list!

The following statement gives an error in sqlite 3.8.4.3.

CREATE VIEW vp AS SELECT ? AS x;

The error message is:

Error: parameters are not allowed in views

The parameter (placeholder) is definitely be the problem here, because
this similar statement executes without error.

CREATE VIEW vs AS SELECT 'flt' AS x;

This error seems reasonable, but I can't find anything about it in
documentation.  Could you tell me where the documentation explains
where exactly I can use parameters (bound parameters, variables,
placeholders, question marks) in an expression?  In particular,
whether it is valid to use a placeholder in any of the following.

 - The SELECT part in a CREATE VIEW statement.
 - The default values of columns in a CREATE TABLE statement.
 - The CHECK constraint expressions in a CREATE TABLE statement.
 - The trigger conditions after WHEN in a CREATE TRIGGER statement.
 - Inside statements in a trigger.

As a special case, the documentation already tells that parameters are
invalid in conditions for partial indexes in a CREATE INDEX statement.

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

Re: [sqlite] Hexadecimal integer literals

[Zsbán Ambrus]

Hello!


Just like the others in this conversation, I also believe that you
must not change the rules how strings are converted to integers by
type affinity, or by type conversions of arithmetic operators.  Thus,
you must not add hexadecimal representation to conversions (nor hex
floats or 'inf' or any other new numeric representation).

Let me explain why.  I create the following table in a database:

CREATE TABLE tb(cl NUMERIC PRIMARY KEY);
INSERT INTO tb VALUES('3.50');
INSERT INTO tb VALUES('0x20');

This inserts the real 3.5 and the string '0x20' into the table.  Then,
any of the following queries finds a row from that table.

SELECT cl FROM tb WHERE cl = 3.5;
SELECT cl FROM tb WHERE cl = '3.5';
SELECT cl FROM tb WHERE cl = '3.50';
SELECT cl FROM tb WHERE cl = '3.500';
SELECT cl FROM tb WHERE cl = '0x20';

Now suppose hypothetically that you changed a future version of sqlite
to convert the string '0x20' to the number 32.  Load the existing
database into this new version.  This table will then have a strange
state: it will have a row with the string ('0x20') which is something
you couldn't insert with the new version of sqlite, because if you
tried to insert it, the new version would convert it to a number.
Further, the queries

SELECT cl FROM tb WHERE cl = '0x20';
SELECT cl FROM tb WHERE cl = 32;

would now either not find that row, or would have to search in
multiple places in the index, because '0x20' is still indexed as a
string, not as a number.

Similarly, a table could have a check constraint that the previous
version of sqlite had validated at insertion, but is no longer true in
the hypothetical new version; or a partial index that used to be
correct in a previous version of sqlite but is now suddenly missing
rows that should be in there in the hypothetical new version.  (I
don't know whether there could be a problem with foreign keys.)


Now of course adding hexadecimal literals in the parser doesn't cause
any of these problems, as long as you make sure any statement or
schema with such a hexadecimal literal definitely gives an error in
previous versions of sqlite.  Similarly, if you believe that parsing
numbers from a hexadecimal representation is useful at runtime, you
could add an ordinary SQL function that does this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding SQLITE installation

[Zsbán Ambrus]

On Thu, Apr 17, 2014 at 9:04 PM, Nitin Nimran  wrote:
> I want to install sqlite for - Linux (X86_64) and NetBSD 6.1  (X86_64)
>
> What should be appropriate options for executing configure script to
> install for specified target.

Please see http://sqlite.org/howtocompile.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making string changes in a table

[Zsbán Ambrus]

On 3/9/14, Simon Slavin  wrote:
> Check out REPLACE():
>
> Technically speaking this might mess up if the string '/path/to/' occurs in
> the middle of the string as well as at its beginning,

For that reason, I think it would be better to use the substr function.

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

Re: [sqlite] SQL quine using with

[Zsbán Ambrus]

And here's a quine which simply concatenates six named strings a lot of times.


SELECT
ab||a||a||a||a||aa||b||a||b||a||bb||b||
a||aa||a||aa||aa||b||a||bb||a||bb||bb||b||
a||ab||a||aa||bb||b||a||ba||a||bb||aa||ba
FROM(SELECTa,','b,'a'aa,'b'bb,'SELECT
ab||a||a||a||a||aa||b||a||b||a||bb||b||
a||aa||a||aa||aa||b||a||bb||a||bb||bb||b||
a||ab||a||aa||bb||b||a||ba||a||bb||aa||ba
FROM(SELECT'ab,');'ba);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Out of memory error for SELECT char();

[Zsbán Ambrus]

On 3/8/14, Simon Slavin  wrote:
> SQLite version 3.7.13 2012-07-17 17:46:21

The char function was added in 3.7.16.

> Could you please post your OS and the version of the SQLite shell tool
> you're using ?

I'm using Linux amd64, compiling with gcc 4.8.1.  I've got the out of
memory result in both sqlite 3.8.1 and a few days old preview
sqlite-amalgamation-201403051440.

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

Re: [sqlite] SQL quine using with

[Zsbán Ambrus]

Anyway, here's a different quine using the replace function.

SELECT replace(s,char(33),)||s||'''s);'FROM(SELECT'SELECT
replace(s,char(33),)||s||!!!s);!FROM(SELECT!'s);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Out of memory error for SELECT char();

[Zsbán Ambrus]

In the sqlite3 console, the following very simple statement gives
"Error: out of memory":

SELECT char();

I think this is a bug.  This query should need very little memory, so
it should not give such an error.  I believe it should return a single
row with a single value of an empty string.

I've reproduced this in both a few days old preview
sqlite-amalgamation-201403051440 and the stable sqlite3 3.8.1.

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

Re: [sqlite] SQL quine using with

[Zsbán Ambrus]

I have a favourite general method to write a quine in any programming
language.  This involves a list of strings and a list of numeric
indexes.  The second list is used to subscript into the first list,
and the found strings are then extracted.

This is possible in sqlite3, but comes out particularly ugly.  The
reasons for the ugliness is mostly that it's not easy to concatenate a
list of strings.  The group_concat function doesn't work, because you
can't guarantee the order it concatenates the strings.

Anyway, I show my solution in the bottom of this mail.

Ambrus


CREATE TABLE pt(p);
INSERT INTO pt VALUES (),('),('),('CREATE TABLE pt(p);
INSERT INTO pt VALUES ('),(');
CREATE TABLE nt(n);
INSERT INTO nt VALUES
(3),(1),(1),(1),(1),(2),(1),(2),(1),(2),(1),(3),(1),(2),
(1),(4),(1),(2),(1),(5),(1),(4),(1),(1),(5);
CREATE TABLE rt(r);
INSERT INTO rt VALUES ('),(');
CREATE TABLE mt(m);
CREATE TRIGGER mg AFTER INSERT ON mt BEGIN
UPDATE rt SET r = r || new.m;
END;
INSERT INTO mt SELECT p FROM pt, nt WHERE pt.oid = n;
SELECT r FROM rt;');
CREATE TABLE nt(n);
INSERT INTO nt VALUES
(3),(1),(1),(1),(1),(2),(1),(2),(1),(2),(1),(3),(1),(2),
(1),(4),(1),(2),(1),(5),(1),(4),(1),(1),(5);
CREATE TABLE rt(r);
INSERT INTO rt VALUES ('');
CREATE TABLE mt(m);
CREATE TRIGGER mg AFTER INSERT ON mt BEGIN
UPDATE rt SET r = r || new.m;
END;
INSERT INTO mt SELECT p FROM pt, nt WHERE pt.oid = n;
SELECT r FROM rt;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RPAD/LPAD

[Zsbán Ambrus]

On 3/7/14, RSmith  wrote:
> Add to this the fact that you can - through SQL's ability to add
> user-defined functions (an almost unique ability among SQL engines)

Is that really so?  I thought most sql engines had that.  You can
define functions in at least postgreSQL:

http://www.postgresql.org/docs/9.3/interactive/xfunc.html

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

Re: [sqlite] Syntax diagram missing in sqlite3 docs

[Zsbán Ambrus]

On 2/20/14, Zsbán Ambrus <amb...@math.bme.hu> wrote:
> The page "http://sqlite.org/lang_transaction.html; should show the
> syntax diagrams for commit-stmt and rollback-stmt.

It seems that these bugs are now fixed in the draft documentation for
sqlite 3.8.4.  Thank you, sqlite maintainers.

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

[sqlite] Syntax diagram missing in sqlite3 docs

[Zsbán Ambrus]

Hi,

The page "http://sqlite.org/lang_transaction.html; should show the
syntax diagrams for commit-stmt and rollback-stmt.  Similarly, the
page "http://sqlite.org/lang_savepoint.html; should show the diagram
for rollback-stmt.  These missing diagrams show up correctly in
"http://sqlite.org/draft/syntaxdiagrams.html;.

Could you please include the missing diagrams?  Thanks,

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

Re: [sqlite] Field not editable

[Zsbán Ambrus]

On 2/13/14, O.D. vidal  wrote:
> I would like a field is not editable. What is the best solution?
>
> I want the name of the country can not be changed.

You can put all the tables you want to be not changable to a separate
database file which you attach as read-only.  Use the ATTACH statement
with the URI filename syntax, as documented in
"http://sqlite.org/uri.html; , to attach the database as read-only.
Of course, when you initially create and fill the table, you have to
attach the same database file in read-write mode, but later your
application can open it read-only.

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

Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

[Zsbán Ambrus]

On 1/30/14, Richard Hipp  wrote:
> Fixed.  Do you see any more problems?

Sqlite 3.8.3 is now released, but I found one more problem today.

The page "http://sqlite.org/lang_transaction.html; should show the
syntax diagrams for commit-stmt and rollback-stmt.  Similarly, the
page "http://sqlite.org/lang_savepoint.html; should show the diagram
for rollback-stmt.

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

[sqlite] Order of rows in a VALUES select expression

[Zsbán Ambrus]

The future release sqlite 3.8.3 adds VALUES statements as a shortcut
form of SELECT statements.  This shortcut simply creates a results set
made of any number of rows, any number of (unnamed) columns in each,
and all the values given by separate explicit expressions.

My question is whether sqlite guarantees that the order of result rows
from such a statement is the same as the order they are listed in the
statement.  The docs at "http://www.sqlite.org/draft/lang_select.html;
seem to imply that the order is undefined, the engine is free to
choose whatever order it wishes.  Further, you can't even use an
explicit ORDER BY clause on a VALUES statement.  If this is the case,
what's the easiest way to generate a set of fixed number of rows like
VALUES does but in an order of your choice?

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

Re: [sqlite] order of = in join

[Zsbán Ambrus]

On 1/30/14, E. Timothy Uy  wrote:
> #1 - f.term = t.term
> Query #1 takes 300 ms, and query #2 takes 30 ms.

Can you show the schema for the tables?  Is it possible that the two
equals comparisons use different affinity or collation, which changes
the semantics of your statement?

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

Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

[Zsbán Ambrus]

On 1/30/14, Richard Hipp  wrote:
> Thanks Kevin and Ambrus for the error reports.  A revised copy of the
> documentation is up at http://www.sqlite.org/draft/lang_select.html -
> please let me know if you see any other problems.

Great, but the diagram for table-or-subquery still has an error.  The
branch where you use a parenthisized join clause as a
table-or-subquery still shows that wrong syntax that allows only an
odd number of terms.

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

Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

[Zsbán Ambrus]

On 1/30/14, Richard Hipp  wrote:
> Documentation on SELECT statements at
> http://www.sqlite.org/draft/lang_select.html has now been updated and
> amplified.

Thank you, that seems cleaner.

However, there seems to be an error.  From the diagram for select-stmt
and also in the diagram for simple-select-stmt, it seems that a FROM
clause always has to have an odd number of table-or-subquery parts,
which is probably wrong.

Further, as select statements can now start with VALUES instead of
SELECT, I think it might help to add a VALUES entry to the list of
commands "http://www.sqlite.org/draft/lang.html;, just like how there
are already entries for REPLACE and WITH.  This should lead to a short
page which refers the reader to SELECT.

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

[sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

[Zsbán Ambrus]

Hi!

I'm writing to you about the syntax diagram that appears on
"http://sqlite.org/draft/lang_select.html;, and is a draft for the
next version of sqlite (3.8.3).  I find this diagram confusing, and
would rather prefer to have something similar to
"http://sqlite.org/lang_select.html;, only of course updated to show
the syntax changes in 3.8.3.

I have two concrete problems with this diagram.

1. It seems to imply that LIMIT clauses are attached to each part of a
compound select.  For example, in a select statement like this:

SELECT col1 FROM tbl1 UNION ALL SELECT col2 FROM tbl2 ORDER BY 1 LIMIT 5;

does the LIMIT clause cause sqlite3 to limit the result of the whole
compound statement, or does it ask only to limit the tbl2 branch and
then take the union?  The text of the page later appears to imply that
the LIMIT applies to the whole statement (correct me if I'm wrong
here), but the new syntax diagram really seems to show the opposite.

2. This diagram for select too big.  The diagram in the released docs
is broken down to smaller parts, and I find that easier to read.

In fact I don't like to read diagrams like this at all.  I'd be
happier to read an alternate representation of the grammar without
images, using only a bnf-like text description.  I'm not saying the
images should go away, only that a text representation should also be
available somewhere, possibly on a separate page like
"http://sqlite.org/draft/syntaxdiagrams.html; if you don't want them
on the main pages.

That said, there is at least one change I like in the new diagram.
Namely, the grammar rule single-source is renamed to
table-or-subquery, and I think the new name is clearer, so thank you
for that.

Thank you for your work on improving sqlite3,

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

Re: [sqlite] WAL/TCL Question

[Zsbán Ambrus]

On 1/14/14, Tilsley, Jerry M.  wrote:
> I have a database that I am using with a couple processes that will modify
> the database at near the same time.  So I thought this would be the ideal
> time to investigate the WAL mode.  How do I enable this on the database, and
> what do I need to watch for when talking to this db after WAL mode is
> enabled?

Does the document http://sqlite.org/wal.html answer your questions?
Read the list of disadvantages on what you need to watch for.

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

[sqlite] Does not detect invalid column name when subexpression optimized away

[Zsbán Ambrus]

Why does the following select statement succeed, instead of giving a
"no column error"?  Is this a feature or a bug?

sqlite> select nonsensename and 0;
0
sqlite> .version
SQLite 3.8.1 2013-10-17 12:57:35 c78be6d786c19073b3a6730dfe3fb1be54f5657a

The select statement is not a subquery and has no from clause, so
nonsensename is a column that should not exist and so should give an
error in my opinion.

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

Re: [sqlite] Failed test on aarch64

[Zsbán Ambrus]

On Tue, Nov 26, 2013 at 5:01 PM, Richard Hipp  wrote:
> The check-in above changes the behavior of REAL-to-INTEGER casts where the
> REAL value is larger than the largest possible integer.  For example:
>
> SELECT CAST(9223372036854775808.0 to INTEGER);
>
> The change causes the query to yield
> 9223372036854775807,

What is the scope of this change?
http://www.sqlite.org/draft/releaselog/current.html mentions only CAST
expressions, is CAST expressions the only case when this happens?  In
particular, will the behavior of the sqlite3_column_int64 function
when retrieving a float value result change?

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

Re: [sqlite] What takes the most time

[Zsbán Ambrus]

On Wed, Nov 13, 2013 at 11:50 PM, David de Regt  wrote:
> If a single _step() call takes a long time to execute (a few minutes), is my 
> only option to just wait for it? Does SQLite not allow any kind of callback 
> mechanism for each _step() to indicate how many percentages are done (or how 
> many bytes have been read/written), and allow for cancellation of the process?

See the sqlite3_progress_handler function, see
http://sqlite.org/c3ref/progress_handler.html ; also possibly the
sqlite3_update_hook function.

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

Re: [sqlite] Where does SQLite write out files?

[Zsbán Ambrus]

On 11/7/13, L. Wood  wrote:
> What directories can SQLite possibly write files to?
>
> * I'm aware of the files that SQLite can write to the *same* directory as
> that of the actual database file,

Take particular attention to master journal files.  As
http://sqlite.org/tempfiles.html describes, these can be created if
you modify multiple attached databases in the same transaction, then
if the application  crashes during the transaction, opening any of
those databases later will open the master journal and all the other
database files affected.

> "The manner in which SQLite uses temporary files is not considered part of
> the contract that SQLite makes with applications.

Note that http://sqlite.org/c3ref/vfs.html tries to give some more
concrete guarantees about what files can be opened.

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

Re: [sqlite] Handling move/rename of an SQLite database file

[Zsbán Ambrus]

On Thu, Nov 7, 2013 at 4:31 PM, L. Wood  wrote:
> Users could rename/move a database file while my (Mac OS X) program has made 
> an SQLite connection to the file. I want to handle this properly.

One thing you have to be particularly careful about is the extra files
sqlite creates next to the database file, such as the rollback journal
files.  The page http://sqlite.org/tempfiles.html documents how sqlite
currently uses these.  In particular, if sqlite finds the database
file but does not file the associated journal file, your database can
go corrupted.  I don't know how you could assure that this does not
happen.

Instead of trying to handle errors from the frontend of sqlite, you
might get a more robust solution if you wrote a custom VFS backend for
sqlite, possibly by modifying the existing uniq VSF backend.  The VFS
is documented on http://sqlite.org/vfs.html .

Sqlite performs all operating system dependent functions through the
replacable VFS backend.  These operations include opening a file,
reading, writing, file locking.  This way if, for example, if you
detect that a user have moved the database file and sqlite wants to
open the corresponding rollback journal file, you can make sure that
it opens the correct rollback journal.

Besides ensuring that there the extra files are found correctly, you
will also have to make sure that file locking works correctly accross
moves.  All this gets quite complicated (maybe even impossible),
especially if multiple programs want to open the same sqlite database
concurrently.

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

Re: [sqlite] SQLite keeps on searching endlessly

[Zsbán Ambrus]

On 10/19/13, Raheel Gupta  wrote:
> Does SQLite support multi column primary keys ?

Yes.

> Also wouldnt primary keys actually slow down further inserts. I have
> queries to insert nearly 1 rows in one second. With larger database
> multi column primary keys might slow down right ?

Not really, not more than it would if you had an ordinary index on the
same columns.  A primary key is almost the same as a separate unique
index in sqlite.  Once you have an index, sqlite has to store inserted
records in the index anyway, and then if that index is unique it can
check the neighbouring entries in the index when it's doing the
insertion.

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