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
[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] 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
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.comwrote: 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=memorycache=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] 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 arpa/inet.h
#include stdio.h
#include stdlib.h
#include string.h
#include sqlite3.h
#ifdef EXTEND_SQLITE3
#include sqlite3ext.h
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 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] 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 demo-ip-extension.sql
.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, *, NULL from foo;
1|::1|
2|127.0.0.1|
select rowid, *, NULL 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, *, NULL from foo;
1|::1|
2|127.0.0.1|
3|100A|
4|128A|
5| ABCD|
6||
select rowid, *, NULL from foo where x '' order by x;
2|127.0.0.1|
1|::1|
select rowid, *, NULL from foo where x '' 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
[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] 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
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] 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] 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] 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
[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
[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
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
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] 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] 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] 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] 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 YEAR = 2010
SELECT COUNT (*) FROM TABLE WHERE YEAR = 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 EOF
-- tailor display
-- .mode tabs
.separator ' '
-- select memory for temporary storage
pragma temp_store = memory;
create temporary table tmp_objects (object_ref integer);
-- collect objects whose name or alias exactly matches the search key
insert into tmp_objects
select distinct object_ref from property where tag = '$tag' and value =
'$val';
-- show count
select count(), 'object(s) found' from (select distinct object_ref from
tmp_objects);
-- collect linked objects (ranges, interfaces ...)
insert into tmp_objects
select origin_ref from tie where target_ref in (select distinct
object_ref from tmp_objects);
select id, '', class, '' from object where id in (select distinct object_ref
from tmp_objects)
union all
select origin_ref, ' ', class, target_ref from tie where target_ref in
(select object_ref from tmp_objects)
union all
select object_ref, ' ', tag, value from property where object_ref in
(select object_ref from tmp_objects)
order by object_ref asc;
EOF
;;
# Other cases omitted ...
*)
echo Unknown query: '$qtype'
;;
esac
I hope this helps.
Best regards,
Niall O'Reilly
University College Dublin IT Services
___
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] 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 http://www.fossil-scm.org/ 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
[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
[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
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
