Re: [sqlite] New word to replace "serverless"
On 29 Jan 2020, at 22:54, Brian Curley wrote: > The marketing buzzword usage will disappear... long before we’ll have the bike shed painted! /N ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to pass -Dxxx compile option
On 9 Jan 2020, at 9:41, Xingwei Lin wrote: > I always use ./configure && make to compile sqlite. > > ./configure can add some compile options, but I don't know how to add -Dxxx > option in this compilation process. I don't know (since I've never needed to build SQLite), but can offer a hint which may help you discover for yourself while waiting for real expert advice. Just before sending, I did check my incoming mail, and didn't see anything from a real expert yet. After running ./configure (without && make), inspect the Makefile and try to find how it runs the compiler, and how it passes options to the compiler. The man page for make is worth reading, but can be confusing. The Makefiles for other software packages that I have to build from time to time are written following the convention that the compiler is invoked using a macro CC, and that options are passed to it using a macro CFLAGS. If you find lines like $(CC) ... $(CFLAGS) ... then you can set a value for CFLAGS by giving an argument to make: make CFLAGS=-Dxxx If you see lines like CFLAGS = $(SOMEFLAGS) $(MOREFLAGS) $(EXTRAFLAGS) then you'll need to be more careful, and work out which of the macros on the right-hand side should be over-ridden by the argument to make. I hope this helps. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about floating point
On 15 Dec 2018, at 10:15, Frank Millman wrote: Simon Slavin says ‘Currency amounts should be stored as integers’. Does this apply to sqlite3 specifically, or is that your recommendation for all databases? It doesn't matter whether a database is involved. Using integers for currency amounts is long established as the only way to do the arithmetic needed for accounting. I'm not sure when I first learned this; it was surely more than 40 years ago. I wish I could cite a reference. You need to count farthings, pennies, centimes, millièmes, or whatever the smallest denomination of the currency is, and arrange appropriate display filtering. In 1972 or so, I learned PL/I, which then had a "STERLING" data type for representing sums as pounds, shillings, and pence. I gather from what a quick web search shows of current IBM documentation that this data type is no longer (advertised as) supported. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible NULL DEREFERENCES and DEAD STORES found by static analysis tools
On 21 Aug 2018, at 10:14, Patricia Monteiro wrote: > I have been analyzing the latest version of SQLite (3.24.0) with several > static analysis tools (Infer, Clang Static Analyzer, Cppcheck and Predator) > and after manually reviewing the code I have identified the following > errors: Variants of this question crop up from time to time. Please look in the mailing-list archives for replies from Richard Hipp dated 22 January 2014 and 23 March 2015, sent in response to earlier similar reports. Best regards, Niall O'Reilly signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to download SQLite for Mac?
On 6 Jun 2018, at 17:06, Sabrina Abdul Jalil wrote: > I am on MAC OS Sierra VER 10.12.06. How to download SQLite? As a couple of people have already pointed out, you actually have it already. Depending on your needs, it may be significant that the version of SQLite which is bundled with macOS (SQLite 3.16.0) is quite a bit behind the current one. I use the Homebrew package manager, and see that it has installed version 3.24.0 for me, and has taken care not to interfere with what Apple has installed. I hope this helps. Niall O'Reilly signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?
On 1 May 2018, at 15:20, Simon Slavin wrote: > Seems like the best way to solve this would be to write a converter for > Windows which converts SCSV to CSV. Then it could be used by all Excel users > instead of just SQLite users. As the heavy lifting of implementing Python on Windows has been done already, I think that some wrapping around this might do the job, since the separator is parameterized: https://docs.python.org/3.6/library/csv.html Like Simon, > I'd do it myself but I don't use Windows. 8-) Niall O'Reilly signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation update
On 9 Apr 2018, at 11:02, R Smith wrote: > Gentlemen - shall we call pistols at dawn to settle this? > Or my favourite duel: Face-pulling at midnight. :) 8-) /Niall signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation update
On 9 Apr 2018, at 2:08, Simon Slavin wrote: > "In unique indices, each NULL value is considered different to every other > NULL value. Thus each NULL value is unique." For the sake of my late English teacher, I hope the revised document will read "... different __from__ ...". Best regards, Niall O'Reilly signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_decltype and max and min
On 11 Jan 2018, at 13:23, Richard Hipp wrote, in reply to John G
<rjkgilles...@gmail.com>:
> You can
> download and/or compile your own up-to-date SQLite that is twice as
> fast and has all the latest features.
It may be more convenient to use the [Homebrew package
manager](https://brew.sh/), which tracks SQLite pretty closely. In order to
avoid interfering with the Apple-supplied installation, Homebrew deliberately
neglects to link the executable to _/usr/local/bin_, so some care is needed
when invoking SQLite in order to run the intended version.
Here's what I have on my laptop, running Sierra.
dhcp-162(niall)12: brew install sqlite
Updating Homebrew...
Warning: sqlite 3.21.0 is already installed
dhcp-162(niall)13:
dhcp-162(niall)13: which sqlite3
/usr/bin/sqlite3
dhcp-162(niall)14: /usr/bin/sqlite3
SQLite version 3.16.0 2016-11-04 19:09:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> ^D
dhcp-162(niall)15: /usr/local/Cellar/sqlite/3.21.0/bin/sqlite3
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
dhcp-162(niall)16:
I hope this helps.
Best regards,
Niall O'Reilly
signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Many ML emails going to GMail's SPAM
On 21 Nov 2017, at 16:27, Drago, William @ CSG - NARDA-MITEQ wrote: > Please, not a forum. The email list is instant, dynamic, and convenient. I > don't think checking into a forum to stay current with the brisk activity > here is very practical or appealing. I agree with Bill on this. It seems to me that the idea of re-architecting such a useful communications channel as this mailing list on account of a cluster of false positives raised by a single provider's triage system would best be characterized as an example of "the tail wagging the dog". I use this provider's service for the major bulk of my e-mail because the university where I used to work, which provides a continued e-mail service to retirees, long ago outsourced its previously in-house e-mail system, which I once had a hand in running, to Google. In my experience, this provider's triage system does a pretty good job, with very few false positives. I see the current high incidence of mis-classification of messages received through the SQLite mailing list as an aberration. Since the triage system is open to tuning by each recipient for their own incoming mail, I suggest that all that is needed is for each subscriber to this list who depends (as I do) on GMail for their mail feed, to apply this tuning for themselves. I found instructions here: https://support.google.com/mail/answer/6579 and have now set up the following filter: Matches: to:(sqlite-users@mailinglists.sqlite.org) Do this: Never send it to Spam Best regards, Niall O'Reilly signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What's the level of B+-Tree ?
On 11 August 2017 11:08:02 GMT+01:00, james ni <james...@live.cn> wrote: >Maybe we are talking the different thing. > > >Background of my problem: > >1, When one table grows larger, I found the INSERT speed is becoming >slower and slower; It seems to me that you may have chosen to view the problem from an angle which will hide the solution. I had a similar problem in a previous job. I had data arriving from a logging system with multiple events per second. This data had to be parsed and loaded into an SQLite db. At first, I retrieved log data every five minutes and ran an INSERT for each log entry. This "just worked" for a week or so. Then I noticed that elapsed time was growing. Advice from this list encouraged me to enclose multiple INSERT commands in a single transaction. The results were dramatically effective, although I should mention that this was not the only design optimization I needed. If my use case is actually similar to yours, I'ld suggest you try this too. Best regards, Niall O'Reilly -- Sent from Kaiten Mail. Please excuse my brevity. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)
On 29 Jun 2017, at 20:19, Peter da Silva wrote: The DECsystem 10 guys also referred to the other subdivisions of their 36 bit words as bytes, sometimes, they could be 6, 7, 8, or 9 bits long. I think they had special instructions for operating on them, but they weren’t directly addressable. A byte could be 1..36 bits long. The special instructions used a data structure called a byte pointer to reference the field within a word where the byte was to be placed or retrieved. Four different formats of byte pointer existed, not all supporting the full range of possible byte sizes. One of these days, when I really have too much free time, I must run up a VM with the Panda TOPS-20 distro and find some examples of interesting byte sizes which were actually used for something. 8-) /Niall ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index list
On 11 Feb 2017, at 14:50, Rob van der sloot wrote: > I want to use the index of a specific column of a table as a pulldown list > in my application. Wouldn't SELECT DISTINCT column FROM table; give you the same effect? I expect the query planner would use the table or not according to its estimate of the benefit of doing so. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE TABLE fails
On 22 Nov 2016, at 14:35, David Raymond wrote: It's needed. The arrow coming out of [column-def] (visually) goes past [table-constraint] first, with the option to loop down to a comma on its way to a [table-constraint] Thanks. My gut told me one thing, my eyes another. Eyes were wrong. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE TABLE fails
On 22 Nov 2016, at 14:03, Richard Hipp wrote:
On 11/22/16, Igor Korot <ikoro...@gmail.com> wrote:
Hi, ALL,
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS abc("abc_tnam" char(129) NOT NULL,
"abc_tid" integer, "abc_ownr" char(129) NOT NULL, "abc_cnam"
char(129) NOT
NULL
, "abc_cid" smallint, "abc_labl" char(254), "abc_lpos" smallint,
"abc_hdr"
char(
254), "abc_hpos" smallint, "abc_itfy" smallint, "abc_mask" char(31),
"abc_case"
smallint, "abc_hght" smallint, "abc_wdth" smallint, "abc_ptrn"
char(31),
"abc_bm
ap" char(1), "abc_init" char(254), "abc_cmnt" char(254), "abc_edit"
char(31), "a
bc_tag" char(254) PRIMARY KEY "abc_tnam", "abc_ownr", "abc_cnam");
Missing comma (,) in between "KEY" and "abc_tnam".
Or perhaps (if Igor's intent is to use three columns as a compound
primary
key) missing parens ['(' ... ')'] around the list of columns after
KEY ?
I was going to add "missing comma before PRIMARY" as well, but
https://www.sqlite.org/lang_createtable.html doesn't seem to indicate
that a comma is required between a column-def and a table-constraint.
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
On 21 Nov 2016, at 21:55, Igor Korot wrote: > You are of course correct. It does depend on an application. > However, I tried to explain the SQLite and its paradigm in terms of > the dBase/FoxPro. You were correct also, Igor, and gave good advice. Best regards, Niall ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a table
On 21 Nov 2016, at 17:29, John R. Sowden wrote: First of all, I come from the dBASE/Foxpro world. There is no distinction between a table and a database. I understand that with Sqlite a database includes tables and other items. The scenario that I do not understand, is: say I have a log file with about 7 fields totaling about 80 characters per record. How do I name the database and table. Currently I say log16 for the year 2016. There's no one true way to do this. I've read Igor Korot's reply, and what he suggests may well be what you need. In a previous job, I had an application where it made sense to use a different database file for each time period, and always to call the table 'LOGENTRY'. This was because I had to deal with hundreds of thousands of records a day, and seldom had queries whose scope was broader than a single calendar day. I hope this helps a little. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select older or not selected records
On 11 Nov 2016, at 12:42, Simon Slavin wrote: No. When you use a quote you update the 'last used on' date for that table row. I think that's actually a "yes". IIUC, OP wishes to pick at random from the items which haven't yet been used in the current cycle. By simply using the 'last used on' date, the first item in each cycle is guaranteed to be the same, and the second, and so on. To do what I think he wants, he'll need an additional selection criterion or two. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement sequence not updated by UPDATE
On 28 Oct 2016, at 12:47, Simon Slavin wrote: It guess it comes down to what one wants from "INTEGER PRIMARY KEY AUTOINCREMENT". If the requirement is only-ever-increasing then this is a bug. The behaviour described at https://sqlite.org/autoinc.html seems to match this requirement: If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. It looks to me as if there is a bug, and I'ld prefer to see the behaviour reported by the OP as buggy, rather than what is documented. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] smartest way to exchange a sqlite3 database with another empty
On 3 Oct 2016, at 10:18, Luca Ferrari wrote: > in one of my application I use a sqlite3 database as a log of > activity. As you can imagine the file grows as time goes by, so I'm > figuring I've to substitute it with an empty one once a good size is > reached. > What is the right way to do it without having to stop the application > (and therefore without knowing when a new I/O operation will be > issued)? > Does sqlite3 provide some facility that could come into help (e.g., > connected databases)? I think that your application must be responsible for this, not sqlite3. I don't know about a "right" way to do it, but I can describe what I did in a similar situation. In my case it seemed natural to start a new database every day, at 00:00 UTC. Hourly, weekly, monthly, or according to a size-related criterion may suit your situation better. Each time the application has something to write to the database, it must calculate the file name to use. If this has changed since the last time, the application must close the current database file and create a new one. For example, when the date changes from 2016-10-02 to 2016-10-03, it might be time to do this. Alternatively, the application could track the number of entries in the database and change over when the count reaches 100,000 or whatever. You need to have some idea how long, or how many entries, it takes to reach your chosen file-size limit. When reading the database, your application will need to identify and open as many files as necessary so as to avoid ignoring relevant data. I hope this helps. Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] a couple of pre-initial questions
On 6 Jul 2016, at 17:15, John R. Sowden wrote: A few questions. Q1: I am running xubuntu. When I search for sqlite3, lots of things show up. This database engine seems to be very popular, so I assume it is used my many programs on my computer, like Thunderbird. Why, then when I enter sqlite3 at terminal prompt do I get message saying that sqlite3 is not installed on my computer. I don't have an xubuntu or Ubuntu system with a screen just now, so can't check, but I expect there's a package manager with a graphic interface in which you can search for SQLite3 and select it for download and installation. Otherwise, you can open a terminal window and issue the command sudo apt-get install sqlite3 I hope this helps. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sweet 16
On 29 May 2016, at 18:28, Richard Hipp wrote: > The first check-in of SQLite code occurred 16 years ago today. > https://www.sqlite.org/src/timeline?c=2000-05-29 Congratulations! Keep up the great work. Niall O'Reilly ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple line command pasted to shell is split in history
On 20 May 2016, at 17:35, Jaromir Obr wrote: > Expected result: > > SELECT REPLACE(ingredients,'enriched_','') as item_ingredients > FROM baked_goods; > > Actual result: > > FROM baked_goods; > > Note: It works well in MariaDB shell, in the same terminal Either way has its advantages. I find keeping the original line-folding more convenient for both readability and making corrections than having the line wrapped (often splitting a token) at the edge of the window. I can also why you and others might prefer UP/RETURN to UP/UP/RETURN/UP/UP/RETURN. Best regards, Niall O'Reilly
[sqlite] How to order by absolute value ?
On 5 Apr 2016, at 10:59, R Smith wrote: > > The documentation is correct and the fault is not that the ORDER BY > did not only apply to the last select - the problem is more that the > ORDER BY abs(num) did not know that "num" is a valid column name in > the compound select - which seems understandable, and perhaps not a > bug, but probably something that can be fixed or enhanced. Thanks.
[sqlite] How to order by absolute value ?
On 5 Apr 2016, at 10:06, Graham Holden wrote: > Change "select abs(num)" in your second example to "select num" and > you should be good. I'm guessing in your first example the ORDER BY > only applies to the last SELECT. I expect you're right, but https://www.sqlite.org/lang_select.html seems to suggest that ORDER BY covers the entire compound SELECT. I wonder whether this is a documentation bug or just me. Best regards, Niall O'Reilly
[sqlite] website documentation wording
On Fri, 04 Dec 2015 18:46:27 +, Keith Medcalf wrote: > > Intel's Management has decided -- for the imperfect tense. > Intel's Managemant have decided -- for the past perfect tense. Eh? These examples show the same tense. Niall O'Reilly
[sqlite] [AGAIN] SQLite on network share
On Fri, 13 Nov 2015 18:29:32 +, A. Mannini wrote: > > Hi, > > i read SQLite FAQ and understood that use of SQLite on network share CAN > corrupts database file. > Fo me, it isn't clear if there is a way to safely use SQLite on a > network share in contests with few clients (max 5 for ex) and low read / > write concurrency.. Alessandro, It's not just for you that it isn't clear. It's not clear for anyone else either. Typically, remote file systems give potentially misleading signals that a file write operation has completed, even though data are still "in flight" and may never arrive at their destination. As a consequence, there is a risk, in using SQLite or any other application, that what is stored on disk is not as intended. It's not very long ago that there was a discussion on this list about the risk of corruption on a local file-system using consumer-grade disks. For a remote file-system using similar technology, the risk cannot be less. The scale of this risk depends on how your particular remote file system and network connections are set up. The acceptability of the risk depends on what the consequences may cost in your case. People on this mailing list can't do your risk assessment or impact analysis for you. Best regards, Niall O'Reilly
[sqlite] version 3.9.0 doc errors
On Thu, 08 Oct 2015 21:12:51 +0100, R.Smith wrote: > > > *** Correction *** It's not, but you don't want to get me started. 8-) > On 2015-10-08 10:03 PM, R.Smith wrote: > > > > To clarify, when used as an adverb to modify a verb, you may well > > add the s - such as saying "I'm moving backwards" or "It's a > > forwards marching army."//... > > "Marching" is of course an adverb here, not a verb. A more correct > example would be: "He's forwards marching". > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Awesome SQLite List - Collection of SQLite Goodies Started - Contributions Welcome
On Mon, 04 May 2015 17:15:17 +0100, Gerald Bauer wrote: > > Note: For now commercial only tools (e.g. SQLite Analyzer) will NOT > get include - sorry. If you want to get it included, buy a sponsored > link or a beer for everyone on the mailing list! Just kidding ;-) It seems to me that it would be useful to include them, flagged as "commercial only". Best regards Niall O'Reilly
[sqlite] Regarding testing
On Mon, 27 Apr 2015 17:51:43 +0100, Drago, William @ CSG - NARDA-MITEQ wrote: > > Never saw this before. LMAO. Will put it too good use... +1 Ossum!
Re: [sqlite] Should .dump preserve foreign_keys pragma?
At Thu, 08 Jan 2015 15:55:00 -0700, Keith Medcalf wrote: > > when you load a dump file you need to have that foreign > key enforcement off in order to be able to load the database. This > is because the tables and data are dumped in random order, not in > hierarchical order (parents of parents then their children then > their children and so on and so forth) or mayhaps there are > self-referential or referential loops which cannot be resolved > without turning off foreign key enforcement while loading the > database "in bulk" rather than by following the application business > logic processing to only add records the would meet referential > constraints. Thanks for explaining. This makes sense. > --- > Theory is when you know everything but nothing works. Practice is > when everything works but no one knows why. Sometimes theory and > practice are combined: nothing works and no one knows why. I've been appreciating that sig for a while, and am glad to have a real message as an opportunity for saying so! Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Should .dump preserve foreign_keys pragma?
At Thu, 8 Jan 2015 13:46:37 +, Simon Slavin wrote: > > > On 8 Jan 2015, at 1:38pm, Niall O'Reilly <niall.orei...@ucd.ie> wrote: > > > I'ld have expected the foreign_keys pragma setting to have been > > preserved. > > That makes sense in terms of how a sensible user would expect SQLite > to behave. But unfortunately it's not what SQLite does. See > section 2 of > > <https://www.sqlite.org/foreignkeys.html> > > I think that the reason is that FOREIGN KEYs were developed a long > time after SQLite3. A choice was made that they should default to > OFF to preserve backward compatibility. I think that was the right choice for default behaviour. What seems wrong to me is that the design doesn't provide for persistence of an explicit change to the default mode, just as in the case of the pragma which sets journal mode to WAL, or the other one which sets page size. I can't see why one would wish to have foreign key support for some connections but not for others. I expect that the work involved in having this pragma set persistent state (whether in the database header or in a special internal table) would require only modest effort and would be almost perfectly safe. I hope I may look forward to reading a reaction from the developers. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Should .dump preserve foreign_keys pragma?
Hello. What follows puzzles me. Either there's something I don't understand, or something is wrong. dhcp-179(niall)7: sqlite3 SQLite version 3.8.5 2014-08-15 22:37:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> PRAGMA foreign_keys=on; sqlite> PRAGMA foreign_keys; 1 sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; sqlite> ^D dhcp-179(niall)8: I'ld have expected the foreign_keys pragma setting to have been preserved. The version shown is currently bundled with Apple's OSX Yosemite. I've checked subsequent release history for changes and not found any of relevance. Thanks in anticipation for any enlightenment. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to load a blob from the shell?
At 08 Apr 2014 09:53 +0100, Tim Streater wrote: > > On 08 Apr 2014 at 00:13, Richard Hipp <d...@sqlite.org> wrote: > > > On Mon, Apr 7, 2014 at 6:56 PM, Keith Christian > > <keith1christ...@gmail.com>wrote: > > > >> > >> However, on production *nix machines, the path to the SQLite 'sar' > >> will probably have to be absolute, or else the native 'sar' (System > >> Activity Reporter) will run instead. > >> > > > > Huh. Never heard of it. It is not installed on my Ubuntu desktop. > > OS X has it, just checked. But I'd never heard of it either. Solaris too, even since before SunOS was re-branded "Solaris". ATB Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow select from database
At Wed, 12 Mar 2014 14:38:15 +0400,
Георгий Жуйков wrote:
>
> 1 . We have a database of measurements: time DATETIME, name TEXT, value
> NUMERIC
> indexes:
> 'tags_name_index' ON 'TAGS' ('NAME' ASC)
> 'tags_name_itemtime_index' ON 'TAGS' ('NAME' ASC ', ITEMTIME' ASC)
> In case of record auto_vacuum=INCREMENTAL flag is used
[...]
> 4 . The request of data is made for time slot, i.e. from several databases.
> For example:
> SELECT COUNT (*) as COUNTER FROM Entries WHERE (TIMESTAMP BETWEEN @STARTTIME
> AND @ENDTIME)
> SELECT * from Entries WHERE (TIMESTAMP BETWEEN @STARTTIME AND @ENDTIME) of
> ORDER BY TIMESTAMP DESC LIMIT 1000 OFFSET 0
You seem to be saying that your table has columns time, name, and value;
that you index on NAME and ITEMTIME; and that you query on TIMESTAMP.
Apart from name and NAME, none of this matches up.
I expect you need an index on whatever TIMESTAMP is. If you choose to
use a compound key for the index, you'll need to take care to make
TIMESTAMP the first component of this key.
I hope this helps.
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 2013 retrospective
At Tue, 31 Dec 2013 11:37:05 -0500,
Richard Hipp wrote:
>
> In addition to the above, there are countless new test cases and minor
> feature and performance enhancements.
>
> Our goal is to maintain this aggressive pace of innovation and enhancement
> in SQLite throughout 2014 and beyond.
Congratulations on a busy and productive year!
> Happy New Year to all.
The same to you, and to everyone on the list ...
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trouble with sqlite3 shell on OSX Mavericks
Hello. The demonstration script for a loadable extension I’m working on uses the sqlite3 command-line shell, and works as expected on Ubuntu. I’m at the stage of checking portability by building and demonstrating it on OSX. The shell bundled with OSX 10.9.1 (Mavericks) seems to be a custom build, as both the ‘.load’ command and the corresponding 'load_extension()’ function appear to be unavailable. The pre-compiled shell available at http://www.sqlite.org/2013/sqlite-shell-osx-x86-3080200.zip is built for the i386 architecture (see below), and so is incompatible with an extension built for x86_64, which is the default architecture for code built on my OSX platform. I wonder whether i386 is the intended architecture for the pre-compiled downloadable shell? Details follow below. dhcp-182(niall)15: ls sqlite* sqlite-shell-osx-x86-3080200.zip dhcp-182(niall)16: openssl sha1 ./sqlite-shell-osx-x86-3080200.zip SHA1(./sqlite-shell-osx-x86-3080200.zip)= 32aea883a5f6ad88a16e26f130d2d178e48ef2a9 Matches fingerprint shown on download page. dhcp-182(niall)17: unzip ./sqlite-shell-osx-x86-3080200.zip Archive: ./sqlite-shell-osx-x86-3080200.zip inflating: sqlite3 dhcp-182(niall)18: ls sqlite* sqlite-shell-osx-x86-3080200.zipsqlite3 dhcp-182(niall)19: file ./sqlite3 ./sqlite3: Mach-O executable i386 Oh? dhcp-182(niall)20: which sqlite3 /usr/bin/sqlite3 dhcp-182(niall)21: file `!!` file `which sqlite3` /usr/bin/sqlite3: Mach-O 64-bit executable x86_64 Apple provide a version with the expected architecture, but support for loading extensions is disabled. Best regards, and Happy Christmas! Niall O’Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite does not order greek characters correctly
> On 8 Dec 2013, at 21:34, Nikos Platis <npla...@gmail.com> wrote: > > Here is the correct order of greek characters (mixed case) as produced by > LibreOffice Calc: > > α Α ά Ά β Β γ Γ δ Δ ε Ε έ Έ ζ Ζ η Η ή Ή θ Θ ι Ι ί Ί ϊ Ϊ ΐ κ Κ λ Λ μ Μ ν Ν ξ > Ξ ο Ο ό Ό π Π ρ Ρ σ Σ τ Τ υ Υ ύ Ύ ϋΫ ΰ φ Φ χ Χ ψ Ψ ω Ω ώ Ώ > > Upper case letters are sorter right after the respective lower case ones, > and, most importantly, accented vowels are sorted right after the > non-accented ones. I notice that you didn't mention final sigma explicitly, and also that this seems (if I'm reading correctly) to occupy the Unicode code-point just before non-final sigma (so: ... ρ ς σ τ ..., ignoring upper case). I guess that's what you would want? Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select with dates
On 16 Sep 2013, at 18:43, Petite Abeille wrote: > What about simply using not overlapping intervals and call it a day? Sure! WFM. I thought that was what I was suggesting. 8-) /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select with dates
On 14 Sep 2013, at 18:09, Petite Abeille wrote: > Yeah... not sure why people are doing that to themselves though :D Consecutive closed intervals overlap. Depending on the application, this may be a problem; it can be avoided by using half-open ones. /N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update field from standard input with sqlite3 command line utility
On 26 Aug 2013, at 16:14, luis montes wrote:
> It seems to me that I should be able to
> do something like this from the command line:
>
> cat file.xml|sqlite3 database.db 'update table1 set column3=? where
> column1="some name";'
>
> That's it, I'm trying to update column 3 on a particular record with a
> string that is already stored on a file.
You don't make it clear what you expect from such a command.
Does the file which is named in the 'cat' command contain a series
of data values, one per line?
If not, does it perhaps contain some (hopefully well-formed) XML
date?
In either case, you seem to be hoping that the SQL command given
as an argument to the SQLite shell ('sqlite3') should somehow
be applied to each data value arriving via the shell ('bash')
pipe, as each value is somehow bound to the '?' in the SQL
command. This isn't a realistic hope.
If I was trying something like that, and if the data file contained
XML, I might use xsltproc to generate a safe series of UPDATE
commands and feed them to sqlite3.
Otherwise, I might use a Perl script based on the DBI module
(see, for example and without endorsement,
http://zetcode.com/db/sqliteperltutorial/).
I hope this helps
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BETWEEN and explicit collation assignment
On Thu, 22 Aug 2013 13:15:19 -0400
Igor Tandetnik <i...@tandetnik.org> wrote:
> On 8/22/2013 11:49 AM, Simon Slavin wrote:
> > Step 1 would be remove all ability to specify collation applying to a
> > single value.
>
> But it already applies, implicitly, to a single value that happens to be
> a column name. It seems you want to preserve that, right?
Surely not!
A column name is not a value, but a label for a set.
The collation associated with a column applies to each subset
(of that set) whose cardinality is exactly two, and creates an
ordering on the set for which the column name is a label.
But let's bring the thread back to the original problem.
What Simon Slavin seems (to me) to be pointing out is that the
counter-intuitive behaviour observed by the OP (Clemens Ladisch)
needs either to be corrected or explicitly documented; he also
seems to be trying to find a way to meet this need.
> Hi,
>
> the documentation says (on <http://www.sqlite.org/datatype3.html#collation>):
>
> | The expression "x BETWEEN y and z" is logically equivalent to two
> | comparisons "x >= y AND x <= z" and works with respect to collating
> | functions as if it were two separate comparisons.
>
> However, this is not true when the first operator has an explicit
> collation assignment:
>
> SQLite version 3.7.17 2013-05-20 00:56:22
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(x);
> sqlite> insert into t values('a');
> sqlite> insert into t values('A');
> sqlite> select * from t where x collate nocase between 'a' and 'b';
> a
> sqlite> select * from t where x collate nocase >= 'a' and x collate nocase <=
> 'b';
> a
> A
>
> It works only on the second and third operators:
>
> sqlite> select * from t where x between 'a' collate nocase and 'b' collate
> nocase;
> a
> A
>
> And adding it to the first operator breaks it again:
> sqlite> select * from t where x collate nocase between 'a' collate nocase and
> 'b' collate nocase;
> a
[I would use "operand" where Clemens uses "operator".]
This behaviour is inconsistent with the documentation (Rule 1 of
section 6.1 of http://www.sqlite.org/datatype3.html#collation) because
(a) the first operand of the BETWEEN operator is precisely the left
operand of each of the two comparisons to which the BETWEEN operator
is described as being equivalent, and (b) rule 1 just mentioned gives
precedence to the explicit collating function associated with the left
operand of a comparison. It should not therefore be necessary to
declare a collation for the second and third operands of BETWEEN; one
should rather be able to rely on the collation declared for the first
one. The observed behaviour indicates that precisely the opposite is
true in practice.
AFAICS, either the code or the documentation is broken, and either one
needs to be corrected.
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Threading makes SQLite 3x slower??
On 4 Aug 2011, at 20:40, Seth Price wrote:
> so THREADSAFE=2 should work fine (as I understand it).
What makes you think it isn't?
> [...] it destroys performance.
My guess is that you've moved the bottle-neck to your disk,
and are suffering from seek latency.
Have you a way of looking at the activity queue for your
disk subsystem? If so, what does it tell you?
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using in-memory DBs of the form: "file:memdb1?mode=memory=shared" (via Perl, DBD::SQLite & DBI)
On 19 Jul 2013, at 09:36, sqlite.20.browse...@xoxy.net wrote:
> Anyone here using SQLite via Perl & DBI & DBD::SQLite?
Yes, but not with an in-memory database.
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3: .import command handles quotation incorrectly
On 26 Jun 2013, at 13:07, Jay A. Kreibich wrote:
> We've been through this before a
> half-dozen times. Everyone seems convinced it would be really easy
> and really simple to make just one small change so that the importer
> works with their version CSV.
Not everyone; I may be in a minority of one, but I can't help
thinking that it would be "really easy and really simple" for
anyone who routinely encounters a particular "troublesome"
CSV format to write a bespoke normalizer addressing their
particular need.
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
On 28 Mar 2013, at 12:09, Jeff Archer wrote: > But my most basic question remains. Why is single transaction faster > than PRAGMA journal_mode = off? > > Seems to me that with no journal there should only be single set of > writes to the actual db and that journaling would double the number of > writes because data has to be written to journal file as well. > > 2.5 sec with journal > 5.5 sec without journal <= seems like this sould be the smaller number Your base-line for comparison is the case of multiple transactions with journalling. When you turn off journalling, you save something; when you consolidate the activity into a single transaction, you save something else. What you're seeing is that the saving achieved with reference to your base-line measurement by using a single transaction exceeds that achieved by disabling journalling. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SOLVED: Custom collation method works for comparison, not for ORDER BY
On 06/12/12 14:32, Niall O'Reilly wrote: > On 6 Dec 2012, at 14:14, Igor Tandetnik wrote: > > > Your code assumes, in several places, that strings passed to collation > > function are NUL-terminated. They don't have to be - that's why lengths are > > also passed. I think this may be causing the problem you are seeing: when > > the string comes from a literal (as in x < '' ) it just may happen to be > > NUL-terminated, but when it comes straight from the database, it may not > > be, and you are cheerfully reading garbage past the end of buffer. > > Thanks for your analysis and helpful comments. > > I'll need to take care to make a NUL-terminated copy of each source > string, > as inet_pton doesn't take a count argument. That seems to have done the trick. Thanks again, Igor. basement(niall)61: sqlite3 '' order by x; 5| ABCD| 3|100A| 4|128A| select rowid, *, NULL from foo order by x; 2|127.0.0.1| 1|::1| 6|| 5| ABCD| 3|100A| 4|128A| select rowid, *, NULL from foo order by x collate binary; 6|| 5| ABCD| 3|100A| 2|127.0.0.1| 4|128A| 1|::1| basement(niall)61: /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
On 6 Dec 2012, at 14:14, Igor Tandetnik wrote: > Your code assumes, in several places, that strings passed to collation > function are NUL-terminated. They don't have to be - that's why lengths are > also passed. I think this may be causing the problem you are seeing: when the > string comes from a literal (as in x < '' ) it just may happen to be > NUL-terminated, but when it comes straight from the database, it may not be, > and you are cheerfully reading garbage past the end of buffer. Thanks for your analysis and helpful comments. I'll need to take care to make a NUL-terminated copy of each source string, as inet_pton doesn't take a count argument. > Your test program, of course, always happens to pass NUL-terminated strings. Doh! 8-) /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
On 6 Dec 2012, at 05:21, Dan Kennedy wrote:
> It still seems likely that the collation returns the wrong result
> some circumstances. Posting the code for it is probably the right
> thing to do.
Thanks for the encouragement!
I've used conditionally compiled sections so that the same
code can be used to generate either an SQLite3 extension
or a stand-alone executable for testing and demonstration.
This demo compiles and runs under Ubuntu and OSX,
and presents the sorted strings in the expected order.
So far, I haven't tried to compile the extension under OSX.
This and my Makefile follow below, as I understand attachments
are not supported on this list.
/Niall
-- Makefile -- beware conversion of TABs --
# inlude for sqlite3
# replace by the directory that contains sqlite3ext.h
INCLUDE = -I/usr/include
ARCH=
SOEXT = so
CFLAGS =
so_files = libsqliteipv6.$(SOEXT)
all: $(so_files)
.PHONY: extend demo clean
sqlite3-ipv6-ext.o : sqlite3-ipv6-ext.c
demo: ip-extension-demo
ip-extension-demo: ip-extension.c
$(CC) $< -o $@ -lsqlite3
extend: ip-extension.so
ip-extension.so: CFLAGS=$(INCLUDE) -DEXTEND_SQLITE3 -fPIC -fno-stack-protector
$(ARCH)
ip-extension.so: ip-extension.o
$(LD) -shared -o $@ $<
clean :
rm -f *.o a.out core core.* *% *~ *.$(SOEXT)
-- ip-extension.c --
/*
** Parse an IP address, prefix, or range
*/
#include
#include
#include
#include
#include
#ifdef EXTEND_SQLITE3
#include
SQLITE_EXTENSION_INIT1
#endif
#define WO_DATA_SZ 256
struct work_object
{
size_t size;
struct {size_t length; unsigned char *data;} string;
struct {size_t length; unsigned char *data;} source;
unsigned char wire[sizeof(struct in6_addr)];
unsigned char type;
};
struct work_object*
new_work_object()
{
struct work_object *this;
unsigned char *p;
p = sqlite3_malloc(WO_DATA_SZ + sizeof(struct work_object));
this = p;
this->type = 255;
this->size = WO_DATA_SZ;
this->string.length = 0;
this->string.data = p + sizeof(struct work_object);
this->string.data[0] = '\0';
this->string.data[WO_DATA_SZ - 1] = '\0';
this->source.length = 0;
this->source.data = NULL;
return this;
}
struct work_object*
prime_work_object(struct work_object *this,
const int n,
const void *source
)
{
struct work_object *p;
size_t sz;
p = this ? this : new_work_object();
p->source.data = source;
p->source.length = n;
p->type = 255;
if (inet_pton(AF_INET6, p->source.data, p->wire) > 0) {
p->type = 6;
}
else if (inet_pton(AF_INET, p->source.data, p->wire) > 0) {
p->type = 4;
}
return p;
}
void
stringify_work_object(struct work_object *this)
{
this->string.length = 0;
this->string.data[0] = '\0';
this->string.data[WO_DATA_SZ - 1] = '\0';
if (this->type == 6) {
inet_ntop(AF_INET6, this->wire, this->string.data, this->size - 1);
this->string.length = strlen(this->string.data);
}
else if (this->type == 4) {
inet_ntop(AF_INET, this->wire, this->string.data, this->size - 1);
this->string.length = strlen(this->string.data);
}
}
int
compare_work_objects(struct work_object *this, struct work_object *that)
{
if (this->type != that->type)
return this->type - that->type;
if (this->type == 6)
return memcmp(this->wire, that->wire, sizeof(struct in6_addr));
if (this->type == 4)
return memcmp(this->wire, that->wire, sizeof(struct in_addr));
return strncmp(this->source.data, that->source.data,
1 +
(this->source.length < that->source.length) ?
this->source.length : that->source.length);
}
#ifdef EXTEND_SQLITE3
/* SQLite3 extension interface here */
int
compare_ipaddrs
(void *q, /* required by API: not used */
int na, const void *pa,/* a: length, string */
int nb, const void *pb /* b: length, string */
)
{
struct work_object *a, *b;
int v;
a = prime_work_object(NULL, na, pa);
b = prime_work_object(NULL, nb, pb);
v = compare_work_objects(a, b);
sqlite3_free(a);
sqlite3_free(b);
return v;
}
/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here. This is usually the only exported symbol in
** the shared library.
*/
int
sqlite3_extension_init
(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi)
{
SQLITE_EXTENSION_INIT2(pApi)
;
/*
sqlite3_create_function(db, "displayip", 1,
SQLITE_UTF8, 0, display1, 0, 0);
*/
sqlite3_create_collation(
db, /* sqlite3* */
"ipaddress", /* const char *zName */
SQLITE_UTF8, /* int eTextRep */
NULL,/* void *pArg -- not used */
compare_ipaddrs
Re: [sqlite] Custom collation method works for comparison, not for ORDER BY
On 05/12/12 21:12, Clemens Ladisch wrote: > Do these queries give the correct result? > > select '100A' collate ipaddress < '127.0.0.1'; > select '100A' collate ipaddress < ' ABCD'; > > I.e., does the collation function actually work? Thanks for the helpful suggestions. I wish I had thought of something so simple. Here's what I get: sqlite> select '100A' collate ipaddress < '127.0.0.1'; 0 sqlite> select '100A' collate ipaddress < ' ABCD'; 0 sqlite> select '127.0.0.1' collate ipaddress < '::1'; 1 sqlite> select '::1' collate ipaddress = '0::1'; 1 sqlite> select 'dead:beef::' collate ipaddress = 'DEAD:BEEF::'; 1 sqlite> These results match what I intended the collation function to do. It appears to work, including recognizing alternative notations for the same IPv6 address as equivalent. Similar tests using BETWEEN also work as intended. Thanks again. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Custom collation method works for comparison, not for ORDER BY
Hello.
I've coded up a custom collation method, and am seeing
what looks like strange behaviour. The method is
intended to sort IPv4 addresses before IPv6 addresses,
and IPv6 addresses before other text strings. It's
just a little wrapping around inet_ptoa and memcmp.
I'm running a back version of sqlite3, which is
apparently the latest bundled version known to the
standard software maintenance utility on the platform
I'm using (apt-get on Ubuntu precise).
I'ld like some advice, please.
First, does what follows appear strange to anyone else?
.version
SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e
select load_extension('./ip-extension.so');
create table foo (x collate ipaddress);
insert into foo values('::1');
insert into foo values('127.0.0.1');
select rowid, * from foo;
1|::1
2|127.0.0.1
select rowid, * from foo order by x;
2|127.0.0.1
1|::1
insert into foo values('100A');
insert into foo values('128A');
insert into foo values(' ABCD');
insert into foo values('');
select rowid, * from foo;
1|::1
2|127.0.0.1
3|100A
4|128A
5| ABCD
6|
select rowid, * from foo where x < '' order by x;
2|127.0.0.1
1|::1
select rowid, * from foo where x > '' order by x;
5| ABCD
3|100A
4|128A
select rowid, * from foo order by x;
6|
5| ABCD
3|100A
2|127.0.0.1
4|128A
1|::1
select rowid, * from foo order by x collate ipaddress;
6|
5| ABCD
3|100A
2|127.0.0.1
4|128A
1|::1
What I find strange is that comparisons against the
empty string behave as expected, but ORDER BY sorts
the values as if no custom collation had been specified,
even where this is explicit in the SELECT statement.
Next, should I best just download the 3.7.14 tarball
and build an up-to-date library before anything else?
I feel that sending my code at this stage would be
to presume too much on people's interest.
Thanks in advance.
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)
On 11 Jul 2012, at 18:36, Valentin Davydov wrote: > This is for IPv4 at least. No. This is for IPv4 ONLY. That doesn't meet my needs. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?
On 2 Jul 2012, at 18:20, Jay A. Kreibich wrote: > The idea of using a plugin system to expand database functionality > seems to fit well with the SQLite way of getting things done. > Functions, collations, and virtual tables are already done in a > similar way. Extending that to types seems like a natural thing. Indeed. > You can, of course, use a user-defined function that just converts a > string to a BLOB of some type. As long as you use the encoder function > for inputs and the decoder for all outputs, you should be good. Functionally, although involving more overhead, a collation is enough. The combination of encoder and decoder obviates repeated references to the collation function for ORDER BY, BETWEEN, and so on. > That > starts to get deep into your SQL, however. The ability to define > native types is similar in complexity to adding user-defined > functions. > > Just a thought. Any opinions? /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)
On 2 Jul 2012, at 17:52, Nico Williams wrote: > So an IPv4 CIDR block like 10.2.93.128/25 would encode as x'0A025D81' > and 10.2.93.128/26 as x'0A025D82', and so on, with 10.2.93.128/32 > encoded as x'0A025D8000' (that's 5 bytes). That is, IPv4 addresses > would require one more byte than usual. You're missing some cases which I would find indispensible. I have a trip tomorrow. I may be able to use the plane time to think about your examples above and to put together some complementary ones of my own. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IP address support (was: Consequences of lexicographic sorting of keys in SQLite4?)
On 2 Jul 2012, at 16:13, Nico Williams wrote: > That reminds me: it'd be nice to have a bit string type, since the > correct way to sort IPv4 CIDR blocks is as bit strings. Nice, definitely! > This is also > a proper way to sort IPv6 blocks. Alternatively, it'd be nice to have > native IP address types in SQLite4, as otherwise one has to jump > through hoops to handle IP addresses properly. Bit strings would be more general. Native IP would remove a sometimes-asserted motivation for preferring PostgreSQL. As I see it, ranges, as well as single addresses and CIDR prefixes, need to be supported, perhaps like the Perl Net::IP module does. With some care over the encoding, a natural ordering arises which places nested prefixes, ranges, and individual addresses in the "right" order. This would eliminate as much as possible of the hoop-jumping. I'll try to put together some examples of as illustrations. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Blobs and ordering [was: Consequences of lexicographic sorting of keys in SQLite4?]
Simon, Thanks for your considered comments. On 2 Jul 2012, at 12:20, Simon Slavin wrote: > Worth remembering that BLOBs don't have a well-ordering function. You can > compare two BLOBs and tell whether they're the same (usually, but lossless > encoding defeats this), but if they're not the same you can't put one > 'before' the other. OK, in the general case. > This is because BLOBs are essentially black boxes. You have no idea what the > data represents. If I'm responsible for the data, I can take care that applying memcmp() to two BLOBs is meaningful. > If you know what it represented, you'd probably be storing it as text or a > number. I'm not sure I can depend on having 128-bit unsigned integers available. Notational options make normalization necessary for text. With BLOB, I can use the result from inet_pton(); with TEXT, I have to apply inet_ntop() to the result of inet_pton(). Old-school parsimony makes me disinclined to do this. Perhaps I need to lighten up? > Think of storing images as BLOBs. How do you compare two images ? I don't think the analogy applies. Images belong to a different specialization of the same base class. Thanks again, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?
On 2 Jul 2012, at 10:51, Dan Kennedy wrote: > That would be a reasonable use. But the blob in this case will be what, > eight bytes (or 10 in its encoded form)? 10, 18, 34, or 66, depending on which of six classes [*] of object is involved, using the encoding I have in mind at the moment. Still small. * 2x address families, 3x kinds of object (address, prefix, range). /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Consequences of lexicographic sorting of keys in SQLite4?
On 29 Jun 2012, at 23:58, Richard Hipp wrote: > But you know: How often do people use BLOBs as keys? What other SQL > engines other than SQLite even allow BLOBs as keys? Are we trying to > optimize something that is never actually used? For an IPAM application I have on my back burner, BLOB seems a natural way to express IPv[46] addresses, ranges, and prefixes. A bulkier alternative would be hexadecimal encoding as text. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite4: type decl/def discrepancy (w/ fix)
On 29 Jun 2012, at 17:17, Stephan Beal wrote:
> If i can be of any assistance, i'm free to help this weekend. i feel kinda
> bad about spamming the user list so much, though :/.
No need to feel bad.
It helps us to see "over the horizon".
> Should we try to
> convince the admin ;) to set to a v4-specific list
Unless v4 isn't intended to be ready for a really long time, I would
hope that the admin won't be minded to build its own reservation
for it. 8-)
> (or i can alternately move to the dev list (subscribing now))?
May make sense. Please don't forget to let us mere users have a
trickle of news about v4.
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Can't create empty database
On 25 Jun 2012, at 13:24, Black, Michael (IS) wrote: > Does the shell compile differently for Mac? Sorry. I've no idea whether it does. SQLite comes bundled with OSX and I haven't had a need to build it from source. Besides, I haven't needed either to upgrade (?) to current OSX. You mentioned a later version of SQLite than the one I have. I guess that has something to do with the divergence in behaviour. Thanks again /N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create empty database
On 25 Jun 2012, at 12:48, Black, Michael (IS) wrote: > Well...it doesnt' any more on Windows and Linux at least as of 3.7.9 > > The file doesn't get created until you execute at least one command relevant > to it. > > So do a .schema or .dump or such and it creates the empty file. > > Or just enter a ";" and it will create it too (ergo the "" works from the > command line). I'm sorry. I live on a Mac with 3.6.12. Thanks for bringing me up to date. /Niall ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create empty database
On 25 Jun 2012, at 11:06, L Anderson wrote: > So then on page 'http://www.sqlite.org/quickstart.html' > under 'Create A New Database', the first bullet: > > 'At a shell or DOS prompt, enter: "sqlite3 test.db". This will create a new > database named "test.db". (You can use a different name if you like.)' > > is not strictly correct. It has always worked for me. /N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parameterized SQL
On 15 Jun 2012, at 10:45, Udi Karni wrote:
> Niall - thanks. If I understand correctly - you use bash to do the
> preprocessing of the substitutions and submit the prepared statements to
> Sqlite.
Well, 'prepared' is not the term I would use, as it has a specific
meaning in the context of SQLite (or other SQL implementations).
Bash does make substitutions in the 'pre-scripted' (for want of a
better term) block delimited by '<<EOF' and 'EOF' before passing
the modified text to sqlite3 as input. I understand that other
shells can do likewise, but bash is the one I'm familiar with.
So, yes and no ... 8-)
Good luck!
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parameterized SQL
On 14 Jun 2012, at 22:16, Udi Karni wrote:
> Is there a way to run SQL scripts in the Shell with parameters?
>
> Something like this?
>
> set = 2010
>
> SELECT COUNT (*) FROM TABLE WHERE YEAR = ;
>
> ???
I use bash and sqlite3, as in the fragment below.
#!/bin/bash
# query-script for netdb/SQL
qtype=${2-node_by_name_or_alias}
dbfile=${3-default-network.db}
case $qtype in
object_by_property)
qkey=${1-code=EE}
echo " Performing query '$qtype' for search argument '$qkey' in
database '$dbfile'"
echo
tag=`echo $qkey | sed -e 's/=.*//'`
val=`echo $qkey | sed -e 's/.*=//'`
/usr/bin/time /usr/local/bin/sqlite3 "$dbfile"
Re: [sqlite] CSV to SQLite to web page display
On 24 Apr 2012, at 15:53, b2 wrote:
> I have data that is exported to comma delimited format(CSV) daily /
> weekly and want to be able to display on the web.
If all you need to do is to transform some data from one text
format (CSV) to another (HTML), I don't see why you would need
to pass the data through a database.
If I'm not mistaken, Perl has at least one module for reading
CSV data; no doubt so have other scripting languages.
Sounds like a nice exercise!
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about The open-source Fossil version control system
On 5 Apr 2012, at 04:42, YAN HONG YE wrote:
> When I open the source on the page, but I don't found
> #include "config.h"
> #include "main.h"
> file, where it is?
You need to do your homework.
Persistently looking to people on the list to do it
for you is not just inappropriate, but simply rude.
I'ld prefer not to have to be so brutaly candid;
however, I've noticed that other list members have
tried to express this more politely, but you seem
not to be minded to heed their message.
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IPv{4,6} addressing extension
On 27 Feb 2012, at 10:51, Alexey Pechnikov wrote:
> You can use integer representation of IPv4 addresses as your "internal
> format" for sorting and sumilar tasks:
Thanks, Alexey.
I know that, but it's an approach which fragments the problem
which I very much want to unify: it's not common to both IP
versions, and it leaves the representation of prefixes mainly
to the application.
Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IPv{4,6} addressing extension
Hello. For a current project, I need an extension to SQLite which supports IP addresses and routing/subnet prefixes. Before I start building one, I'ld be glad to learn of any that are out there, other than those mentioned at either of the following URLs: http://www.mail-archive.com/sqlite-users@sqlite.org/msg35680.html http://freebsd.ntu.edu.tw/FreeBSD/distfiles/sqlite-ext/ipv4-ext.c From what I can see, neither of these supports IPv6, nor provides a sortable encoding for Internet addresses and/or routes. I'm looking for the following functionality: - feature parity between IPv4 and IPv6; - an internal format which allows sorting a collection of prefixes and addresses so that a containing prefix is sorted before a more specific contained prefix, and this before a contained address; - functions to convert between display and internal formats for representing IP addresses and prefixes; - functions for extracting the bounding addresses of a prefix; - functions for testing membership (address or prefix in prefix); - functions for extracting the count of addresses covered by a prefix (perhaps only for IPv4, as a 64-bit integer isn't adequate for doing this with IPv6). I expect to take inspiration from the extensions cited above, as well as from the CPAN Net::IP module. If I'm about to re-invent the wheel, I'ld appreciate a warning. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Introduction
Hello. I've just joined this list, so an introduction may be in order. I'll follow up with a "real message" separately. I work in IT Services at UCD, Ireland's largest university. While there, I've worked with OS/360, TOPS-20, VM/370, VMS, SunOS (before it became Solaris), and Linux, to mention only some operating systems. These days I work mainly on provisioning for DNS and DHCP. I like SQLite a lot, as it gives me SQL without the administrative overhead of managing (securing ...) a server process. I've used it at home (with Tcl/Tk) to build a document-imaging system to help me with my tax returns, and at work to provide a web-mediated retrieval system for our DHCP and RADIUS logs. I'm currently working on an IPAM application using SQLite to store the data; it's not clear just yet whether this will enter production, or rather end up as a tool to help migration to a well-known IPAM system. Best regards, Niall O'Reilly ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
