Re: [sqlite] New word to replace "serverless"

[David Baird]

Edit, that last part should say "skim server" :D

On Mon, Jan 27, 2020, 10:54 PM David Baird  wrote:

> How about "skim server"? So if, "server" means a whole server, then like
> whole milk versus skim milk, a fraction of a server becomes severless :)
>
> On Mon, Jan 27, 2020, 9:57 PM Stephen Chrzanowski 
> wrote:
>
>> I'd stick with "serverless".  The marketing teams that make "serverless"
>> mean that websites don't run with "servers" are I-D-TEN-Ts.  It's a fad
>> phrase that'll go away eventually.  I understand marketing, and its
>> purpose, but, in this case, they're pushing it.
>>
>>
>> On Mon, Jan 27, 2020 at 9:31 PM sub sk79  wrote:
>>
>> > Also maybe Slipstreamed?
>> >
>> > -Neal
>> >
>> > On Monday, January 27, 2020, sub sk79  wrote:
>> >
>> > > How about Seamless, Integrated or Baked-in?
>> > >
>> > > -Neal
>> > >
>> > > On Monday, January 27, 2020, Warren Young  wrote:
>> > >
>> > >> On Jan 27, 2020, at 3:18 PM, Richard Hipp  wrote:
>> > >> >
>> > >> > "serverless" has become a popular buzz-word that
>> > >> > means "managed by my hosting provider rather than by me.”
>> > >>
>> > >> “Serverless” it a screwy buzzword anyway, because of course there’s
>> > still
>> > >> a server under its new meaning.
>> > >>
>> > >> My vote?  Keep using the term.  We were here first.
>> > >>
>> > >> This is an ancient problem.  It is why is any serious dictionary the
>> > >> count of definitions considerably exceeds the count of headwords.
>> These
>> > >> new kids?  “serverless, sense 2.”
>> > >> ___
>> > >> sqlite-users mailing list
>> > >> sqlite-users@mailinglists.sqlite.org
>> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> > >>
>> > >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[David Baird]

How about "skim server"? So if, "server" means a whole server, then like
whole milk versus skim milk, a fraction of a server becomes severless :)

On Mon, Jan 27, 2020, 9:57 PM Stephen Chrzanowski 
wrote:

> I'd stick with "serverless".  The marketing teams that make "serverless"
> mean that websites don't run with "servers" are I-D-TEN-Ts.  It's a fad
> phrase that'll go away eventually.  I understand marketing, and its
> purpose, but, in this case, they're pushing it.
>
>
> On Mon, Jan 27, 2020 at 9:31 PM sub sk79  wrote:
>
> > Also maybe Slipstreamed?
> >
> > -Neal
> >
> > On Monday, January 27, 2020, sub sk79  wrote:
> >
> > > How about Seamless, Integrated or Baked-in?
> > >
> > > -Neal
> > >
> > > On Monday, January 27, 2020, Warren Young  wrote:
> > >
> > >> On Jan 27, 2020, at 3:18 PM, Richard Hipp  wrote:
> > >> >
> > >> > "serverless" has become a popular buzz-word that
> > >> > means "managed by my hosting provider rather than by me.”
> > >>
> > >> “Serverless” it a screwy buzzword anyway, because of course there’s
> > still
> > >> a server under its new meaning.
> > >>
> > >> My vote?  Keep using the term.  We were here first.
> > >>
> > >> This is an ancient problem.  It is why is any serious dictionary the
> > >> count of definitions considerably exceeds the count of headwords.
> These
> > >> new kids?  “serverless, sense 2.”
> > >> ___
> > >> sqlite-users mailing list
> > >> sqlite-users@mailinglists.sqlite.org
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

On Dec 9, 2015 7:14 PM, "Simon Slavin"  wrote:
>
>
> On 9 Dec 2015, at 9:10pm, David Baird  wrote:
>
> > My cocnern is that: the database engine shouldn't
> > needlessly waste/discard perectly good chunks of rowids
>
> Please don't think of them as 'chunks'.  There is no order to the numbers
it picks.  An alternative way to think of the AUTOINCREMENT mechanism is
that it simply picks a random number, checks to see that it's not already
in use, and returns that.
>
> You shouldn't depend on anything 'sequency' about the rowids it creates.
If your program depends on them being a sequence, generate the numbers you
want yourself, in the sequence you want.
>

Yes, I am aware. Note that the application is correct as it doesn't care
about rowid 'sequenceyness'- but that's not what the OP is about at all.
The OP was concerning a minor technical issue regarding the implementation
details of SQLite. All other points, I agree with, but they're moot anyways
because this isn't about application correctness (the application is
already happily correct!).

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

On Dec 9, 2015 8:06 AM, "Richard Hipp"  wrote:
>
> On 12/9/15, David Baird  wrote:
> >
> > Looks like it's fixed, as long as I stick to new versions. *cross
fingers*
>
> Bisecting shows that the change in behavior occurred here:
> https://www.sqlite.org/src/timeline?c=56bc5ce8
>
> As others have a pointed out, though, the "desired" behavior is
> "undocumented".  SQLite nor any other SQL database engine is obligated
> to provide the behavior you want.  It happens as you like purely by
> chance.  Do not depend on this behavior since it might change at any
> moment, without warning.

I tend to agree with this, that clients shouldn't depend on specific rowids
being generated repeatably/deterministically. But that's not the concern I
wanted to highlight. My cocnern is that: the database engine shouldn't
needlessly waste/discard perectly good chunks of rowids: That is what's
actually undesired. Aside from that, I agree that client shouldn't care
what particular rowids were generated. If it started at 9 and
counted down, I wouldn't care, as long ad it is making good (contiguous)
allocation of the key space.

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch  wrote:

> David Baird wrote:
> > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior
> > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior
>
> Works for me.  Are you sure you actually executed this actual code?
>
> <http://www.sqlite.org/autoinc.html> guarantees that a newly inserted
> row uses the next available rowid value, if you have an INTEGER PRIMARY
> KEY column.  You might get the undesired behaviour only with an INTEGER
> PRIMARY KEY AUTOINCREMENT column.
>

Just tried two different versions:

SQLite version 3.8.2 2013-12-06 14:53:30 (that is bundled with my OS): has
the undesired behavior

SQLite version 3.9.2 2015-11-02 18:31:45 (that I built myself): has desired
behavior

Looks like it's fixed, as long as I stick to new versions. *cross fingers*
Thanks!

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

Hi,

I have a use case in which a table is being used to associate integers to
unique strings. When loading the strings, it's possible there may be some
redundancies, which INSERT OR IGNORE successfully ignores. However,
redundant values that should have been ignored _sometimes_ have a side
effect that is undesired: they consume extra rowids. Here are a couple
examples:


-- Undesired behavior, side effect on rowid:
CREATE TABLE StringIntern (
id INTEGER,
string TEXT,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX StringIntern_string ON StringIntern(string);
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b');
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'), ('c'); --
'a' and 'b' have a side effect on the rowid
.dump
# >>>
# INSERT INTO "StringIntern" VALUES(1,'a');
# INSERT INTO "StringIntern" VALUES(2,'b');
# INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior


-- Desired behavior:
CREATE TABLE StringIntern (
id INTEGER,
string TEXT,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX StringIntern_string ON StringIntern(string);
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b');
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'); -- 'a' and
'b' are ignored (all good here!)
INSERT OR IGNORE INTO StringIntern(string) VALUES ('c');
.dump
# INSERT INTO "StringIntern" VALUES(1,'a');
# INSERT INTO "StringIntern" VALUES(2,'b');
# INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior


Is what I call undesired behavior actually intentional by sqlite3? Would it
be possible to change it to the desired behavior?

Thanks,
David

Re: [sqlite] SQlite C API screwing port access?

[David Baird]

On Wed, Mar 24, 2010 at 11:19 AM, Martin Sigwald  wrote:
> Problem Solved: As some one point out, it was MY fault.
> When allocating memory for my ICMP packets I wasnt doind a bzero to fill all
> fields with 0, so some "garbage" generated by Sqlite use of memory was
> "corrupting" my packets.
> Thank you all for the help. One of the best user groups I ever met. Terrible
> sorry for wasting your time.

Glad you found it ... forgetting to initialize data happens even to
the best of us sometimes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite C API screwing port access?

[David Baird]

On Wed, Mar 24, 2010 at 11:09 AM, Martin Sigwald  wrote:
> Doing N pings after a _close or a query has the same result as doind one:
> not one of them works.

Do 2 pings work ever?  For example, how about each of these scenarios?

open_db
ping
ping
close_db

or

ping
ping

or

open_db
close_db
ping
ping

?

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

Re: [sqlite] SQlite C API screwing port access?

[David Baird]

On Wed, Mar 24, 2010 at 9:42 AM, Martin Sigwald  wrote:
> I meant socket. I know sockets are FDs. My mistake, sorry.
> Yes, I tried putting the call before Sqlite calls and it works perfectly. If
> I put it between open and close it works, provided I dont do anything else.
> For example, if I open the DB, ping, then run a query then ping again, the
> second ping wont work.

What if you try doing two pings in a row?  Does the second ping ever
work?  Does it always work?  Does it work sometimes?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite C API screwing port access?

[David Baird]

On Wed, Mar 24, 2010 at 9:24 AM, David Baird <dhba...@gmail.com> wrote:
> On Wed, Mar 24, 2010 at 9:05 AM, Martin Sigwald <msigw...@gmail.com> wrote:
>> While I could gather, both the open system called generated by the DB and
>> the socket() syscall are returning a FD=3.
>> That is, they are both trying to use the same filedescriptor. My guess is
>> packets get sent to that file descriptor, instead of the port. How can I
>> changed this? I just followed standar procedure to allocate a socket:
>> sockfd = socket(AF_INET, SOCK_RAW, IPPROTO_ICMP))
>>
>> Shouldn't the Kernel take care of this and provide an unused FD??
>
> According to the strace, the kernel is taking care of that properly.
> You get a socket, call sendto, select, recvfrom, then close it.  And
> then you open guido.db.  Since you just closed your socket, the fd=3
> is reused for guido.db.  This is perfectly legitimate.  Something else
> funny is going on.  Here's the relevant portions from the strace:

Actually, I just realized that a socket is opened twice in your
strace.  So, here's the portion that includes both socket opens and
the database open:

First socket open and close:

socket(PF_INET, SOCK_RAW, IPPROTO_ICMP) = 3
setsockopt(3, SOL_IP, IP_HDRINCL, [1], 4) = 0
sendto(3, "E\0\34\0Eg\0\0\377\1P\223\0\0\0\0\n\0\0\3\10\0;\320#\306"...,
28, 0, {sa_family=AF_INET, sin_port=htons(0),
sin_addr=inet_addr("10.0.0.3")}, 16) = 28
select(4, [3], NULL, NULL, {2, 50}) = 1 (in [3], left {2, 496000})
recvfrom(3, "e\0\0\0...@\0\0@\0015\233\n\0\0\3\n\0\0\4\0\0C\320#\306"...,
28, 0, {sa_family=AF_INET, sin_port=htons(0),
sin_addr=inet_addr("10.0.0.3")}, [16]) = 28
fstat64(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 2), ...}) = 0
mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0xb7f16000
write(1, "El Server 10.0.0.3 esta en el es"..., 43) = 43
close(3)= 0

Database open and close (with a dup in the middle that also creates fd=4)...

getcwd("/home/martin", 5000)= 13
open("/home/martin/guido.db", O_RDWR|O_CREAT|O_LARGEFILE, 0644) = 3
fcntl64(3, F_GETFD) = 0
fcntl64(3, F_SETFD, FD_CLOEXEC) = 0
fstat64(3, {st_mode=S_IFREG|0777, st_size=2048, ...}) = 0
dup(3)  = 4
mmap2(NULL, 8392704, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS,
-1, 0) = 0xb7537000
mprotect(0xb7537000, 4096, PROT_NONE)   = 0
clone(child_stack=0xb7d374c4,
flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID,
parent_tidptr=0xb7d37bd8, {entry_number:6, base_addr:0xb7d37b90,
limit:1048575, seg_32bit:1, contents:0, read_exec_only:0,
limit_in_pages:1, seg_not_present:0, useable:1},
child_tidptr=0xb7d37bd8) = 6240
mmap2(NULL, 8392704, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS,
-1, 0) = 0xb6d36000
mprotect(0xb6d36000, 4096, PROT_NONE)   = 0
clone(child_stack=0xb75364c4,
flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID,
parent_tidptr=0xb7536bd8, {entry_number:6, base_addr:0xb7536b90,
limit:1048575, seg_32bit:1, contents:0, read_exec_only:0,
limit_in_pages:1, seg_not_present:0, useable:1},
child_tidptr=0xb7536bd8) = 6241
close(4)= 0
_llseek(3, 0, [0], SEEK_SET)= 0
read(3, "SQLite format 3\0\4\0\1\1\0@  \0\0\0\4\0\0\0\0"..., 100) = 100
close(3)= 0
getuid32()  = 0

And second socket open and close

socket(PF_INET, SOCK_RAW, IPPROTO_ICMP) = 3
setsockopt(3, SOL_IP, IP_HDRINCL, [1], 4) = 0
sendto(3, "E\0\34\0Hs\4\10\377\1I\177\0\0\0\0\n\0\0\3\10\0\276\256"...,
28, 0, {sa_family=AF_INET, sin_port=htons(0),
sin_addr=inet_addr("10.0.0.3")}, 16) = 28
select(4, [3], NULL, NULL, {2, 50}) = 0 (Timeout)
write(1, "TIMEOUT\n", 8)= 8
close(3)= 0


In both cases that you call sendto, it appears to have succeeded (i.e.
because they returned a positive value, i.e. 28).  In the second case,
select timed out (as you said it does).

I don't notice any cases of where a stale file descriptor is being
accessed.  I'm stumped :-/

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

Re: [sqlite] SQlite C API screwing port access?

[David Baird]

On Wed, Mar 24, 2010 at 9:05 AM, Martin Sigwald  wrote:
> While I could gather, both the open system called generated by the DB and
> the socket() syscall are returning a FD=3.
> That is, they are both trying to use the same filedescriptor. My guess is
> packets get sent to that file descriptor, instead of the port. How can I
> changed this? I just followed standar procedure to allocate a socket:
> sockfd = socket(AF_INET, SOCK_RAW, IPPROTO_ICMP))
>
> Shouldn't the Kernel take care of this and provide an unused FD??

According to the strace, the kernel is taking care of that properly.
You get a socket, call sendto, select, recvfrom, then close it.  And
then you open guido.db.  Since you just closed your socket, the fd=3
is reused for guido.db.  This is perfectly legitimate.  Something else
funny is going on.  Here's the relevant portions from the strace:

Socket is opened and closed here:

socket(PF_INET, SOCK_RAW, IPPROTO_ICMP) = 3
setsockopt(3, SOL_IP, IP_HDRINCL, [1], 4) = 0
sendto(3, "E\0\34\0Eg\0\0\377\1P\223\0\0\0\0\n\0\0\3\10\0;\320#\306"...,
28, 0, {sa_family=AF_INET, sin_port=htons(0),
sin_addr=inet_addr("10.0.0.3")}, 16) = 28
select(4, [3], NULL, NULL, {2, 50}) = 1 (in [3], left {2, 496000})
recvfrom(3, "e\0\0\0...@\0\0@\0015\233\n\0\0\3\n\0\0\4\0\0C\320#\306"...,
28, 0, {sa_family=AF_INET, sin_port=htons(0),
sin_addr=inet_addr("10.0.0.3")}, [16]) = 28
fstat64(1, {st_mode=S_IFCHR|0620, st_rdev=makedev(136, 2), ...}) = 0
mmap2(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1,
0) = 0xb7f16000
write(1, "El Server 10.0.0.3 esta en el es"..., 43) = 43
close(3)= 0

...and then database is opened here:

getcwd("/home/martin", 5000)= 13
open("/home/martin/guido.db", O_RDWR|O_CREAT|O_LARGEFILE, 0644) = 3
fcntl64(3, F_GETFD) = 0
fcntl64(3, F_SETFD, FD_CLOEXEC) = 0
fstat64(3, {st_mode=S_IFREG|0777, st_size=2048, ...}) = 0
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing Table Contents

[David Baird]

On Wed, Jul 1, 2009 at 9:50 PM, Rick Ratchford wrote:
> From what I understand, ANYTIME you do a SQL statement, such as "SELECT...",
> you are doing this to a TABLE and returning the result in a sort of
> 'recordset'.
>
> So then, the table is this sqlite_master, the field is tbl_name, and the
> result of the SQL query can be had from the recordset it returns. Right?

That is correct.  I think that, to be very pedantic, "column" might be
a better term than "field" for tbl_name.

> So using VB and my wrapper, with SQLite, it is...
>
> Set Rs = Cnn.OpenRecordset(SQLString)
>
> I did this and it worked! The returned recordset has a field named after the
> function, called count(*).

Here's another trick you can do too if you want to give specific names
to the resulting columns:

SELECT count(*) AS my_count FROM sqlite_master WHERE tbl_name = 'Foo';

> So I guess the answer I was looking for as to where to find the returned
> result is "RECORDSET". It wasn't that obvious to me until a few minutes ago.

Glad you found it! :-)

If you're still new to SQL and want to delve more into theory, you
could search Google for "normal forms" (e.g. database
normalization)... but that may not be too important until you start
working with larger or complex data.

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

Re: [sqlite] Changing Table Contents

[David Baird]

On Wed, Jul 1, 2009 at 6:05 PM, Rick Ratchford wrote:
> I'm using a VB wrapper, and so I run this by...
>
>    Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name =
> 'DeltaGrid'"
>
> Thing is, I don't know where to check for the return value.

I'm afraid I can't help with this - I don't have Visual Basic experience :-(
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing Table Contents

[David Baird]

On Wed, Jul 1, 2009 at 5:29 PM, Rick Ratchford wrote:
> 1. Determine if the table has already been created due to a prior run.
>
> 2. If so, to remove the information currently in that table and replace it
> with new information.
>
> I'm not sure how to determine whether the table already exists.
>
> If it does exist, I suppose I can then use the SQL Delete to delete all the
> records and then write the new stuff. If this is not the way to do it, maybe
> someone can suggest the proper way.

Well, here are a few commands that may give you some ideas, and you
can see what works best for you

Here is how you can use sqlite_master to determine if a table exists:

SELECT count(*) FROM sqlite_master WHERE tbl_name = 'Foo';
-- returns 1 if table exists, 0 otherwise

If you want to drop your table and then create it, you could do this:

DROP TABLE Foo;
-- It's okay to execute this command, even if Foo does not exist already.

CREATE TABLE Foo ( ... );

Here you can see whether or not a table contains any rows at all:

SELECT count(*) FROM Foo;
-- returns 0 if table is empty

If you want to clean out your table:

DELETE FROM Foo;
-- This will delete all rows from Foo

If you just want to update some already existing data:

UPDATE Foo SET column2 = 33 WHERE column1 = 1;
-- This lets you update (modify) information that already exists
in the table


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

Re: [sqlite] sql statement to concatinate two rows.

[David Baird]

No problem.  As a side note, it appears that you might also want to
use group_concat on either hostName or remoteWXType too, but I can't
quite tell 100%.  As it stands right now, if there are multiple
distinct values in the group, sqlite will arbitrarily choose one
single value to represent the entire group (unless you use
group_concat).

On Tue, Feb 17, 2009 at 11:29 PM, Joanne Pham  wrote:
> Thank a lot David!
> It worked.
> This is exatcly sql statement that I want to have.
> Once again thanks a ton David,
> JP
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql statement to concatinate two rows.

[David Baird]

On Tue, Feb 17, 2009 at 11:16 PM, Joanne Pham  wrote:
> Hi All,
> I have the select statement as below
> sqlite> select remoteId, hostName , remoteWXType from remoteWXTable order by 
> hostName;
> and the output  is below:
>
> 1|HostName1-T432|2
> 2|HostName2-T421|2
> 3|HostName3-XP|2
> 4|HostName3-XP|2
>
> But I would like the sql statement to return as below:
> (HostName3 has two remoteId so I want to return as one row but two different 
> remoteId as below)
> 1|HostName1-T432|2
> 2|HostName2-T421|2
> 3,4|HostName3-XP|2
> Can you please help to change the sql statement to return the above result 
> set.
> Thanks,
> JP
>

No problem...

SELECT group_concat(remoteId, ','), hostName, remoteWXType
FROM remoteWXTable
GROUP BY hostName; -- or remoteWXType ...?

I think group_concat is only supported in moderately recent versions
of sqlite3, so make sure to not be using something 1 or 2 years old.

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

Re: [sqlite] SQLite3 to SQLite2?

[David Baird]

On Mon, Jun 16, 2008 at 6:18 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote:
> We have an application that can only read SQLite2 databases. Is there
> an easy way to convert a SQLite3 database file into the SQLite2
> format?

This might work:

echo .dump | sqlite3 input.db | sqlite output.db

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

Re: [sqlite] graph question

[David Baird]

On Mon, Jun 9, 2008 at 3:49 PM, Bruce Robertson <[EMAIL PROTECTED]> wrote:
>>  select f || ' ' || group_concat(t, ' ')
>>  from w
>>  where f in (1, 2, 3);
>>  group by f;
>>
>> HTH
>> Dennis
>
> Can you explain that?
>
> What's the || ' ' || part?

|| is the string concat operator.  e.g.

SELECT "foo" || "bar";

will print:

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

Re: [sqlite] graph question

[David Baird]

On Mon, Jun 9, 2008 at 11:40 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> David Baird wrote:
>>
>> Okay, just built SQLite 3.5.9 and group_concat does in fact work:
>>
>> select group_concat(t, ' ') from w where f=1;
>
> You forgot the parent value at the beginning. Also, the OP may want to
> do this for several parents which can be accomplished by grouping the
> results.
>
>   select f || ' ' || group_concat(t, ' ')
>   from w
>   where f in (1, 2, 3);
>   group by f;

Ahh, right.  Thanks for fixing that :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] graph question

[David Baird]

On Mon, Jun 9, 2008 at 11:19 AM, David Baird <[EMAIL PROTECTED]> wrote:
> On Mon, Jun 9, 2008 at 11:09 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>> Wilson, Ron P
>> <[EMAIL PROTECTED]>
>> wrote:
>>> select t from w where f=1;
>>> 2
>>> 3
>>> 4
>>> 5
>>> 6
>>> 7
>>>
>>> I would like the output to look like this:
>>>
>>> 1 2 3 4 5 6 7
>>>
>>> i.e. parent child1 child2 ... childN
>>
>> SQL is not formatting or reporting library. It gives you raw data, and
>> it's up to your application to build a presentation from it.
>>
>
> Actually, it seems that the group_concat aggregation function should
> do the trick:
>
>http://www.sqlite.org/lang_aggfunc.html
>
> But, my SQLite (3.4.2) on Ubuntu seems to be missing the group_concat
> function though :-(

Okay, just built SQLite 3.5.9 and group_concat does in fact work:

select group_concat(t, ' ') from w where f=1;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] graph question

[David Baird]

On Mon, Jun 9, 2008 at 11:09 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> Wilson, Ron P
> <[EMAIL PROTECTED]>
> wrote:
>> select t from w where f=1;
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>>
>> I would like the output to look like this:
>>
>> 1 2 3 4 5 6 7
>>
>> i.e. parent child1 child2 ... childN
>
> SQL is not formatting or reporting library. It gives you raw data, and
> it's up to your application to build a presentation from it.
>

Actually, it seems that the group_concat aggregation function should
do the trick:

http://www.sqlite.org/lang_aggfunc.html

But, my SQLite (3.4.2) on Ubuntu seems to be missing the group_concat
function though :-(

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

Re: [sqlite] Select TOP n

[David Baird]

On Sun, Apr 20, 2008 at 5:07 PM, James Dodd <[EMAIL PROTECTED]> wrote:
> Hi,
>
>  (Newbie to sqlite, some experience with SQL Server 2000). I tried to do a
>  "SELECT TOP 10 * FROM tab1" and sqlite3 complained. Then I looked at the SQL
>  syntax page and indeed TOP doesn't seem to be there. Is there a reason for
>  this and, better still, is there a way to get around it? Or is it there and
>  I've missed it?

Perhaps this?

SELECT * FROM tab1 LIMIT 10;

You might also want to throw in an ORDER BY clause.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance.....

[David Baird]

On Wed, Mar 5, 2008 at 8:21 AM,  <[EMAIL PROTECTED]> wrote:
>  My main concern is performance. Has anyone had any similar application
>  experience they could comment on? I want to be able to insert data arriving
>  on the SPI bus and then query the data to update a GUI at a very high rate
>  (less than 250Ms). This is not real time so 250Ms is desirable but does not
>  have to be guaranteed.

Well, SQLite stores all its data into a BTree.  So, if you have a
normalized schema, like this:

CREATE TABLE Signals3 (
channel_number INTEGER PRIMARY KEY,
sample_number INTEGER PRIMARY KEY,
sample_value INTEGER
);

...or perhaps even something simpler like this (if you don't have to
worry about multiple channels):


CREATE TABLE Signals2 (
sample_number INTEGER PRIMARY KEY,
sample_value INTEGER
);

The problem with these normalized schemas is that the require a BTree
lookup for every sample that you try to insert or retrieve.  BTrees
are great for random access, but BTrees are not optimized for special
purpose applications such as data acquisition.  Data acquisition
systems have the special property that you are going to insert or
retreive several contiguous values at a time.  A better data
structure, for example, could be a hash table with a custom hash
function.  The hash function can be designed to densely pack the
samples (since they are expected to be contiguous and therefore
dense).

I have done some performance testing with the above schema (Signals3)
and have been able to get somewhere around 30k INSERTs per second on
an Intel Core2 Duo, but only when:

1. I use an in-memory SQLite database
or
2. All the INSERTs are grouped into a single transaction for a
disk-based SQL database

If you do not group the INSERTs into a single transaction, the
performance is much worse: something like 300 INSERTs per second.

So, while excellent for random access applications, the BTree in
SQLite doesn't work very well for high-speed data acquisition
applications.

Here is a workaround.  Denormalize the data by storing several samples
into a BLOB:

CREATE TABLE DenormalizedSamples (
channel_number INTEGER PRIMARY KEY,
beginning_sample_number INTEGER PRIMARY KEY,
blob_of_1024_samples BLOB
);

This decreases the BTree accesses to 1 access every 1024 samples.  I
don't have any performance results for this technique that I can tell
about yet.  I also don't like this technique because it is a real pain
to pack and unpack the BLOBs.

Incidentally, I have been considering the idea of patching SQLite for
higher performance in data acquisition systems.  I guess I should make
a post and see if there is any interest in that, but I wouldn't be
able to get started on that for about a month and I don't know how
much time would be required.  Furthermore, there are other problems
such as marshalling data to/from SQL strings and it could perhaps be
better to have a lower-level interface for interacting with the
database to skip the parsing step.

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

Re: [sqlite] indexing

[David Baird]

On Feb 13, 2008 1:01 PM, Kasper Daniel Hansen <[EMAIL PROTECTED]> wrote:
> Thank you for a clear and precise answer.
>
> I seem to recall that it is possible to have in-memory databases with
> sqlite. But perhaps that is only possible with alot of tinkering and
> using the C functions. Is that true (I am thinking of avoiding the I/
> O bottleneck on our network drive by just moving everything into
> memory since we have a large mem server - but only if it is quick and
> easy)

Instead of specifying a filename for the database, try this instead:
":memory:".  If you need a file with that name, you would type this:
"./:memory:".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Innovative examples / user stories

[David Baird]

On Feb 11, 2008 10:51 AM, Lars Aronsson <[EMAIL PROTECTED]> wrote:
> Is there any documentation of how people use SQLite in odd ways in
> their everyday activities?

I used to work with the NS-2 network simulator as an undergrad at NMT
for some experiments we were doing in wireless power control systems.
After frustrations with parsing NS-2 data files, I decided to start
adding my own hooks to their code which would print simulation results
in somewhat normalized ASCII tabular formats.  Then I imported the
tables into an SQLite database (because it is fast and extremely easy
to setup/use).  This was heaven compared to what we were doing before.

Our schema looked something like this (probably there should be more
indexes or primary keys...):

CREATE TABLE ns_phytx (
time REAL,
pktid INTEGER,
nodeid INTEGER,
pkttype STRING,
source INTEGER,
dest INTEGER,
txpwr REAL,
bytes INTEGER,
duration REAL,
x REAL,
y REAL,
PRIMARY KEY (pktid)
);
CREATE TABLE ns_phyrx (
time REAL,
pktid INTEGER,
nodeid INTEGER,
rxpwr REAL,
x REAL,
y REAL,
PRIMARY KEY (pktid, nodeid)
);
CREATE TABLE ns_macrx (
time REAL,
pktid INTEGER,
nodeid INTEGER,
min_SIR REAL,
PRIMARY KEY (pktid, nodeid)
);
CREATE TABLE ns_collision (
time REAL,
nodeid INTEGER,
pktid1 INTEGER,
pktid2 INTEGER,
type INTEGER
);
CREATE TABLE ns_interference (
time REAL,
nodeid INTEGER,
total_power REAL,
accepted_power REAL
);
-- possibly with more tables, depending on what types of simulation
data we were working with

SQLite allowed us to easily and quickly perform aggregations and
joins, and then we could takes those results and do further analysis
and visualization using Octave/MATLAB, GNUPLOT, and other tools.

For example, you can easily get a link gain by joining the ns_phytx
and ns_phyrx tables on pktid.  Scrounging around, I found some queries
like this (embedded in a bash script):

function q3() {
title="Histogram of collisions at node $nodeid"
echo -n "q3 [query]"
sqlite3 z.db << EOF > $filename-1
.separator ' '
.mode list
SELECT ROUND(c.time/1 - 0.5) * 1 AS histtime, COUNT(*)
FROM
ns_collision AS c
INNER JOIN
ns_phytx AS t1
ON c.pktid1 = t1.pktid
INNER JOIN
ns_phytx AS t2
ON c.pktid2 = t2.pktid
WHERE
c.nodeid = $nodeid AND
c.type = 1
GROUP BY
histtime;
EOF

echo -n " [gnuplot]"
gnuplot << EOF
set term postscript eps
set xrange [$starttime:$endtime]
set yrange [0:$collscale]
set title "$title"
set output '$filename.eps'
plot \
'$filename-1' using (\$1):(\$2) \
t "sum over 1 second intervals" w impulses
EOF

echo " $title"
}

I don't really hear a lot of people talking about how they used
databases to collect and assist with analysis of simulation results.
Has anyone else been doing something like this?  I think SQL is
excellent for this.

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

Re: [sqlite] Why attach databases?

[David Baird]

On Feb 7, 2008 3:21 PM, Jason Tudor <[EMAIL PROTECTED]> wrote:
> In general, what is the benefit of attaching databases verses maintaining
> multiple connections?

Well, let's say that you don't have the same schema in both databases.

Attaching two databases allows you to do queries across multiple
tables and would require much more effort if you maintained separate
connections.

e.g.

SELECT People.name, Pets.name
   FROM
People AS People
other_database.Pets AS Pets
WHERE
People.id = Pets.owner_id;

It would be less pleasant to do that query via multiple connections.

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

Re: [sqlite] Broken links to "sqlite3" on website

[David Baird]

On Jan 31, 2008 1:45 PM,  <[EMAIL PROTECTED]> wrote:
> Tnx.  Please try it again now.

Great, seems to be working now, even for other pages that I didn't list.

Thanks!

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Broken links to "sqlite3" on website

[David Baird]

Hi,

I'm not complaining (I love SQLite), but I just wanted to point out
that the "sqlite3" link is broken from multiple web pages:

http://www.sqlite.org/c3ref/objlist.html
http://www.sqlite.org/c3ref/intro.html

Also, links to "sqlite3_stmt" are broken.  It seems that any link
containing a %20 is broken, e.g.

http://www.sqlite.org/c3ref/database%20connection.html
http://www.sqlite.org/c3ref/prepared%20statement.html

and I suspect that they are supposed to be these instead:

http://www.sqlite.org/c3ref/sqlite3.html
http://www.sqlite.org/c3ref/stmt.html

-David

-
To unsubscribe, send email to [EMAIL PROTECTED]
-