Re: [sqlite] Consider adding aggregate function "string_agg" as synonym of "group_concat"
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"
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
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(...)'
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
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
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
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
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", &ddDB, SQLITE_OPEN_READWRITE | > SQLITE_OPEN_CREATE, "myvfs"); > sqlite3_exec(ddDB, "PRAGMA journal_mode = off",NULL, 0, &zErrMsg); > 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)
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
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?
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
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
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
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
[sqlite] PRAGMA integrity_check says row 2 missing from index
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
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
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
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
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
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
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
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?
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?
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
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
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
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
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?
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] Can I define collation-aware SQL functions?
Dear SQLite list, A few of the built-in SQL functions of SQLite has a result that depends on the collation sequence of the arguments, and compare text values using these collation functions. These functions are "min" and "max" (both aggregate and scalar versions) and "nullif". Is there a way to define new SQL functions that behave in such a way? I haven't seen any way in the public C api that would let a function determine what collation sequence is used for its arguments. Plus, even if I could find out the collation used, Thanks, -- Ambrus
[sqlite] Determine type of prepared statement via C Interface?
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
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?
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
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
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
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
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
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
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
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
On 8/5/14, Richard Hipp wrote: > On Tue, Aug 5, 2014 at 3:51 PM, Zsbán Ambrus 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
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
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?
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?
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
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
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
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
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();
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
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();
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
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
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
On 2/20/14, Zsbán Ambrus 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
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
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
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
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
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
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
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
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
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
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
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
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?
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
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
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