[sqlite] SQLite3: Windows filename format in command-line program

[rayB]

Filenames using the Windows SQLite3 version of the CLI program now have to
be specified using forward-slashes, rather than the back-slashes used by the
SQLite2 CLI. Would someone kindly point me to where this change has been
documented, as my searches have so far proved fruitless?

 

Thank you in advance.

 

Regards.

 

rayB

Re: [sqlite] disk write bound while doing selects?

[D. Richard Hipp]

Bob Gilson wrote:
We believe the disk writes are caused by fcntl(). Its updates to the 
bits on the file can be has resource consuming as an fsync() or write().
fcntl(F_RDLCK) and fcntl(F_UNLCK) should be adjusting some data
structures inside the kernel only.  Those calls should *never*
modify the disk.  What OS are you running?
Also, we can't understand why each select statement has access() and 
fstat64() calls for each select.
These are to check to see if another process has modified the database
since you last read it.  They also check to see if another process
was in the middle of modifying the database but crashed and thus the
partially completed changes need to be rolled back.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

[sqlite] disk write bound while doing selects?

[Bob Gilson]

Hello SQLiters,
We're doing some benchmarks on our app using SQLite and it appears disk 
writes are bottlenecking selects against cached data. The benchmark is 
doing a series of selects only. The vast majority of these selects 
return a single row. The database is small, 1000ish rows.  It is cached 
as iostat shows almost no disk reads.  Meanwhile, iostat shows our 
pathetic little IDE drive is maxed with disk writes, 1500ish. This 
occurs when "PRAGMA default_synchronous = NORMAL" or "PRAGMA 
default_synchronous = FULL". But it doesn't happen when "PRAGMA 
default_synchronous = OFF". We observe a 5x speedup with "PRAGMA 
default_synchronous = OFF". Too bad its not appropriate for our app.

We ran our benchmark with strace . Here's illustrative output:
[pid 18933] fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, 
start=1073741824, len=1}, 0xbe3ff0c4) = 0
[pid 18933] fcntl64(16, F_SETLK64, {type=F_RDLCK, whence=SEEK_SET, 
start=1073741826, len=510}, 0xbe3ff0c4) = 0
[pid 18933] fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, 
start=1073741824, len=1}, 0xbe3ff0c4) = 0
[pid 18933] access("/u2/peter/sandbox/herc/var/spool/jsm.db-journal", 
F_OK) = -1 ENOENT (No such file or directory)
[pid 18933] fstat64(16, {st_mode=S_IFREG|0644, st_size=4664320, ...}) = 0
[pid 18933] fcntl64(16, F_SETLK64, {type=F_UNLCK, whence=SEEK_SET, 
start=0, len=0}, 0xbe3ff0b4) = 0
[pid 18933] write(2, "20050105T23:52:05: SQLite.cpp:19"..., 
15220050105T23:52:05: SQLite.cpp:193 - (elapsed 0.000625) select 
user_id, namespace, value, length(value) from generic where user_id = ? 
AND namespace = ?
) = 152

We believe the disk writes are caused by fcntl(). Its updates to the 
bits on the file can be has resource consuming as an fsync() or write().

Also, we can't understand why each select statement has access() and 
fstat64() calls for each select.

Has anyone else seen this? Is this making sense? The result is odd 
enough that I fear someone will give the "pull your head out of your 
backside" response.

Much thanks,
Bob Gilson

[sqlite] Seg fault/core dump solaris 8 - sqlite 2.8.15 in attach2-3.1

[John P. Rouillard]

Hi all:

I am trying to compile sqlite-2.8.15 on a Solaris 8 machine using
gcc-3.4.1 and tcl-8.4.9. The compile goes cleanly, but when I run
"make test" (with the appropriate LD_LIBRARY_PATH), I get a
segmentation fault in the attach2-3.1. Since the testfixture isn't
compiled with debugging symbols, gdb's backtrace is kind of useless.

The tcl install passed all regressions tests. The same compiler was
used for both builds.

I did have to modify the sqlite configure generated Makefile because the 
ifeq preprocessors weren't being executed. I turned

  ifeq (${INMEMORYDB},0)
  INCOREFLAGS += -DSQLITE_OMIT_INMEMORYDB=1
  endif
 and

  # Only build the in-core DB if it is required.
  ifeq (${INMEMORYDB},1)
  LIBOBJ += btree_rb.lo
  endif

into

  LIBOBJ += btree_rb.lo

since INMEMORYDB = 1.

Also as soon as the segfault occurs, all tests stop running. How do I
disable just one test when running the testsuite? I assume I have to
run the testfixture manually rather then through the Makefile.

So has anybody seen this before on Solaris? Any idea where to start debugging?
Any recipes that "just work for you"?

-- rouilj
John Rouillard
===
My employers don't acknowledge my existence much less my opinions.

Re: [sqlite] Appropriate class for database

[Michael Hunley]

In my experience you have to create a custom control.  If you are doing 
this under MFC, subclass listbox or grid.  IIRC there might be a table 
class that is a good starter.  I did something similar in a recent app 
(BackupBuddy 2) using wxWidgets.  Another alternative we are looking at 
seriously is RealBasic since it is cross platform.  There are lots of 
shareware utility classes depending on your environment -- CodeProject 
(.NET), Duff's Device, SourceForge or CodeGuru are all good places to find 
pre-fab utility code.  CodeProject is in a tutorial format so it can be 
quite helpful.

HTH.
michael
At 08:34 AM 1/5/2005, aleks ponjavic wrote:
I am creating a program for basketball coaches.
I am having big problems choicing a suitable class for my database.
It should be one for players i.e
player shirt-nr age rating and so on in columns then beneath the user 
should be able to add in a row and by pressing the columns that columns 
get sorted and the rows are still connected to eachother.

I have tried out grid and listbox, listbox didn't have columns and I don't 
find grid as appropriate as I'd want it to be.
Any suggestions!?
I have looked a little at listctrl but it doesn't seem to be what I'm 
looking for...

_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

[sqlite] sqlite3_errmsg() question

[Charles Mills]

Hi,
Do errors encountered when using sqlite3_step(), sqlite3_bind(), and 
other API functions which work on sqlite3_stmt's set the error message 
string in the corresponding sqlite3 db structure?

The documentation leads me to believe that the answer is yes, but I 
wanted to be sure.

Thanks,
Charlie

[sqlite] Appropriate class for database

[aleks ponjavic]

I am creating a program for basketball coaches.
I am having big problems choicing a suitable class for my database.
It should be one for players i.e
player shirt-nr age rating and so on in columns then beneath the user should 
be able to add in a row and by pressing the columns that columns get sorted 
and the rows are still connected to eachother.
I have tried out grid and listbox, listbox didn't have columns and I don't 
find grid as appropriate as I'd want it to be.
Any suggestions!?
I have looked a little at listctrl but it doesn't seem to be what I'm 
looking for...

_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

[sqlite] Appropriate class for database

[aleks ponjavic]

I am creating a program for basketball coaches.
I am having big problems choicing a suitable class for my database.
It should be one for players i.e
player shirt-nr age rating and so on in columns then beneath the user should 
be able to add in a row and by pressing the columns that columns get sorted 
and the rows are still connected to eachother.

I have tried out grid and listbox, listbox didn't have columns and I don't 
find grid as appropriate as I'd want it to be.
Any suggestions!?
I have looked a little at listctrl but it doesn't seem to be what I'm 
looking for...

_
Express yourself instantly with MSN Messenger! Download today it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

[sqlite] Saving decimal values

[Joga Singh]

Hi I am using sqlite3 on Linux.

I have a table with the following schema:

CREATE TABLE packagedet (
  pkgno int not null,
  policy_order int not null,
  dayno int not null,
  start time not null,
  endtime time not null,
  qosno int not null,
  bytepulse real not null,
  timepulse real not null
);

When I execute the following statement using the 'sqlite3_get_table' API in C, 
value for the bytepulse is is saved as zero:

insert into packagedet 
(pkgno,policy_order,dayno,start,endtime,qosno,bytepulse,timepulse) values 
(116,1,1,'00:00','00:00',1,1.00,1.00)

following is the output in sqlite3 console.

116|1|1|00:00|00:00|1|0|1

But when I execute the same statement from the sqlite3 console, it is saving 
perfectly.


Any ideas???


JS

Re: [sqlite] String Concatenation

[Eric Bohlman]

Keith Herold wrote:
OwnerID, AString, Sequence
1,  'concatenate', 0
1, 'some', 1
1, 'strings', 2
1, 'together', 3
What I need to do is create a single string out of the AString,
Sequence pairs, for a given owner. Obviously I could do this through
some C++ code, but I would prefer to do it within SQL code, but can't
think of a way.  The sequences are not always the same lenght, i.e., a
particular owner may have 1, 10, or 1972 word long sequences.  With
cursors, I don't think this would be difficult, but I can't figure out
how to do this within SQLite.
Mike Chirico's tutorial 
() 
might be helpful; it looks like what you want to do is conceptually 
similar to creating the "pivot tables" he describes.

Re: [sqlite] Indices and Joins

[Thorsten Schuett]

On Wednesday 05 January 2005 13:05, D. Richard Hipp wrote:
> Thorsten Schuett wrote:
> > have I missed something obvious or is my explanation that bad?
>
> Your original posting was lengthy and dense.  I doubt many people took
> the time to read it.
Thanks for the kind paraphrase of "hard to read". ;-)

> > Can I make sqlite use a more complex index for the joins? E.g.:
> > foreach row_type in  type where row_type.mdc_value="MDC_HFN"
> >  foreach row_parent in parent where row_parent.mdc_value="/tmp/file-test"
> > and row_parent.mdc_oid = row_type.mdc_oid
> > [...]
> > So that the if-statements are converted into index-lookups?
>
> Create indices as follows:
>
> CREATE INDEX idx1 ON type(mdc_value);
> CREATE INDEX idx2 ON parent(mdc_value, mdc_oid);
Damnit. I create such indices but not on all tables.

Thanks a lot,
 Thorsten

Re: [sqlite] Indices and Joins

[D. Richard Hipp]

Thorsten Schuett wrote:
have I missed something obvious or is my explanation that bad?
Your original posting was lengthy and dense.  I doubt many people took
the time to read it.
Can I make sqlite use a more complex index for the joins? E.g.:
foreach row_type in  type where row_type.mdc_value="MDC_HFN"
 foreach row_parent in parent where row_parent.mdc_value="/tmp/file-test"
and row_parent.mdc_oid = row_type.mdc_oid
[...]
So that the if-statements are converted into index-lookups?
Create indices as follows:
   CREATE INDEX idx1 ON type(mdc_value);
   CREATE INDEX idx2 ON parent(mdc_value, mdc_oid);
The second index could also be this:
   CREATE INDEX idx2 ON parent(mdc_oid, mdc_value);\
See http://www.sqlite.org/php2004/page-001.html and
especially http://www.sqlite.org/php2004/page-058.html
and the surrounding pages.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Indices and Joins

[Thorsten Schuett]

Hi, 

have I missed something obvious or is my explanation that bad? I would really 
like to promote sqlite in our group but if joins are so slow that won't 
happen. Any advice?

Thorsten

On Monday 03 January 2005 17:14, Thorsten Schuett wrote:
> Hi,
>
> I am currently porting an application from a custom db backend to sqlite
> and have some difficulties understanding how sqlite uses indices in joins.
> I have several tables (MDCATTR_*) which have two columns mdc_oid and
> mdc_value. I have for testing created indices on mdc_oid, mdc_value and
> both combinations of both columns. The queries I'm interested in, look like
> the following:
>
> SELECT objs.mdc_oid FROM
> MDCATTR_MDC_TYPE AS type, MDCATTR_NAME AS name,
> MDCATTR_PARENT_NAME AS parent, MDCOBJECTS AS objs
> WHERE (
> (name.mdc_oid=type.mdc_oid) AND (parent.mdc_oid=name.mdc_oid) AND
> (objs.mdc_oid=parent.mdc_oid) AND (type.mdc_value='MDC_HFN') AND
> (parent.mdc_value='/tmp/file-test') AND (name.mdc_value='foo0.0'));
>
> I am basically joining several tables on the mdc_oid and selecting
> different values for different tables. The assembler of the vm roughly
> looks like that:
>
> foreach row_type in  type where row_type.mdc_value="MDC_HFN"
>  foreach row_parent in parent where row_parent.mdc_value="/tmp/file-test"
>   if(row_type.mdc_oid != row_parent.oid)
>continue;
>   foreach row_name in name where row_name.mdc_value="foo0.0"
> if(row_type.mdc_oid != row_name.oid)
>continue;
>call callback;
>
> where the expressions in the "where"-part are used for index-lookups. The
> question I am having is as follows:
> Can I make sqlite use a more complex index for the joins? E.g.:
> foreach row_type in  type where row_type.mdc_value="MDC_HFN"
>  foreach row_parent in parent where row_parent.mdc_value="/tmp/file-test"
> and row_parent.mdc_oid = row_type.mdc_oid
> [...]
> So that the if-statements are converted into index-lookups?
>
> Thanks in advance,
>  Thorsten

Re: [sqlite] String Concatenation

[Jakub Adamek]

I think it is not possible. You can't express an arbitrary loop with 
SQL. Perhaps you could write a group-by user function doing this, I am 
not sure, but user functions are again C code.

Jakub
Keith Herold wrote:
I have a semi-quick question about how to do successive string concatenation.
I have a table:
CREATE TABLE mystrings
(
   OwnerID INTEGER NOT NULL,
   AString VARCHAR(900),
   Sequence INTEGER DEFAULT 0
);
Each AString is associated with an Owner; a single owner can have
multiple strings assigned to it, with the order of the strings held by
sequence:
OwnerID, AString, Sequence
1,  'concatenate', 0
1, 'some', 1
1, 'strings', 2
1, 'together', 3
What I need to do is create a single string out of the AString,
Sequence pairs, for a given owner. Obviously I could do this through
some C++ code, but I would prefer to do it within SQL code, but can't
think of a way.  The sequences are not always the same lenght, i.e., a
particular owner may have 1, 10, or 1972 word long sequences.  With
cursors, I don't think this would be difficult, but I can't figure out
how to do this within SQLite.
Any help?
--Keith
--
Jakub Adamek
Programmer
Telematix Software, a.s.
Karoliny Svetle 12, Praha 1
http://www.telematix.cz
Tel: +420 608 247197
Office: +420 224233470