date:20060406


Thanks for that info Richard - I will certainly try it for size.
Congratulations on your most prestgious award. 

NJH
--
View this message in context: 
http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3794224
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread John Stanton

Provided your new database has the features you are using it is simple. 
 For example we use Sqlite and PostgreSQL and transferring between them 
is simple.  Even the application program API is similar so a conditional 
compile in the programs takes care of that and links appropriately.

JS

Lenster wrote:
Thanks for your reply Richard 


After further Googling and reading the replies I've received here I am
coming around to the idea that SQLite probably is the best choice.

I would like to know whether - at some point in the future should it be
necessary - I could convert my entire SQLite databases including tables etc
to another RDMS such as MySQL? Is this straightforward or convoluted?

Regards
Len
--
View this message in context: 
http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3788822
Sent from the SQLite forum at Nabble.com.

[sqlite] www.sqlite.org Server setup

2006-04-06 Thread drh

JP <[EMAIL PROTECTED]> wrote:
> Sounds like an interesting setup! Maybe off topic, but, would you care 
> to elaborate on that topic? Server configuration, virtualization 
> software running, etc.?
> 

The http://www.sqlite.org/ website runs on a leased virtual
server.  The virtual server is leased from http://www.linode.com/
and is a Linode 160.  This is roughly a 200MHz machine with
exactly 160MiB of RAM and about 8GiB of disk space.  There are
around 19 or so equivalent virtual machines running on the same
physical box.

The same virtual server hosts multiple websites.  Besides
SQLite it hosts:

   http://www.cvstrac.org/
   http://canvas3d.tcl.tk/
   http://tkhtml.tcl.tk/

And there are other proprietary sites on the same VM.  But
most of the load comes from SQLite.

On a typical weekday, the SQLite website is visited by around
6000 distinct IP addresses and takes 7 or so hits.  Total
traffic is around 1.7GiB per day.  The webserver is a custom 
one-file server, the source for which can be found at:

   http://www.sqlite.org/althttpd.c

The webserver is launched from inetd.  Thus, a new process is
forked for each incoming connection.  This is not the most
efficient way to handle http requests, but it is perfectly
adequate for the kind of traffic that www.sqlite.org sees - 
only one request per second.  The althttpd webserver handles 
direct files delivery or CGI.  Nothing fancy.  There is a single
unified log file.

Althttpd runs each request in a chroot jail for security.

CVSTrac (http://www.cvstrac.org/) is used for version tracking,
tickets, and wiki.  An older version of CVSTrac is currently used - 
one that uses SQLite version 2.  The SQLite database file that
backs up this CVSTrac instance is about 30MiB in size.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] sqlite-3.3.4 and extra float decimals

2006-04-06 Thread John Stanton


Dennis Cote wrote:

Floppe wrote:


Will Leshner wrote:
 


On 4/5/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
 
  


Hello,

When using sqlite-3.3.4 with windows I get the following strange 
behaviour.


create table Muppet (Kermit float);
insert into Muppet values (100);
select * from Muppet;
100.0
   


That's not strange. Kermit is defined to be a FLOAT, so it gets
returned as a FLOAT. Perhaps you wanted it to be returned as an
INTEGER? You could try something like this:

SELECT cast(kermit as INTEGER) FROM Muppet;
 
  



I wanted it to return the same format as SQLite does in Windows CE or
any other SQL engine does. Consistency? If someone enters 100 then it
could be a rounded value but if someone enters 100.0 (or 100.1) then it
usually isn't rounded. So now I need two fields, one with the value and
one that says how many decimals the user entered and then format the
output? Or make every field as varchars? Seems a little stupid to me.

If this is a part of the roadmap and not a bug then the next question
would be, is version 3.2.8 stable as that was the last version that
worked as I wanted in winxp?



 

This behavior was changed as a result of this bug report 
http://www.sqlite.org/cvstrac/tktview?tn=1362


I think the current behavior is correct.

If you have a column where you want to store exactly the data the user 
entered, you should use a text column. You can convert that text to 
numeric data by whatever means you deem appropriate whenever you want 
to. You should only use a floating point column if you want to store the 
numeric value that the user entered regardless of how he entered it. The 
database should not make a distinction between 1.0 and 1.000 for a 
floating point value, but these are not the same thing when you 
interpret the extra decimal places as increased accuracy.


This is very similar to the use of leading zeros on integer numbers. As 
integers, 001 and 1 are the same thing. If you need to distinguish 
between the two, you should be using a text column.


Dennis Cote


Dennis' advice is well founded.  We use TEXT fields for decimal numbers, 
and give them a declared type of DECIMAL(P,S) where P is the precision 
and S the scale so that they are SQL compliant.  Sqlite permits this. 
These display format decimal numbers interface to a display format 
arithmetic package which links into the application and a few custom 
Sqlite functions so that arithmetic can be performed without going into 
floating point and risking the consequent loss of accuracy. 
Unfortunately you cannot use arithmetic within Sqlite nor the arithmetic 
aggregate functions.


One day I shall make a couple of patches into Sqlite so that the SUM and 
TOTAL aggregate functions and the +,-,* and / use the accurate decimal 
numbers.  A quick browse through the tightly written and well documented 
Sqlite suggests that the changes would be quite limited and use the 
existing data structures unchanged.  The lack of an accurate number 
system is a shortcoming with Sqlite.  A possible remedy would be to 
implement the decimal standard proposed by IBM recently.

http://www2.hursley.ibm.com/decimal/decarith.pdf

We use a set of display format fixed point arbitrary precision and scale 
arithmetic functions salvaged from one of our legacy language 
compiler/support packages and which was the reason users wanted the old 
language to live on - accurate, easy to use numeric handling.  The debit 
and credit columns always matched.


Despite the nuisance of having to fiddle with numbers we believe that 
the elegance in design and simplicity in use of Sqlite makes the effort 
to adapt it to our needs well worthwhile.  The application server we 
have constructed using it fits in as an Apache module, is small and fast 
and makes for easy to maintain, easy to install systems in what would 
otherwise be "computer hostile" environments.

Re: [sqlite] Most appropriate Web based database? (Newbie)


Thanks Dennis, that's great news - I shall give it a try :-)

--
View this message in context: 
http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3793176
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Structured or Object-Oriented?

2006-04-06 Thread John Newby

I think Object Oriented databases came in with the SQL-99 standard and
SQLite is the SQL-92 standard so it may be structured but I'm not sure at
all

On 06/04/06, Aaron Jones <[EMAIL PROTECTED]> wrote:
>
> Hi, I am doing a project for University where I am creating a
> cross-platform, open-source GUI to SQLite, I am going to be using mono to
> build it, do you think this will be ok or do you think I will encounter
> any
> problems along the way?  I've noticed there is a wrapper for mono, but
> where
> mono is unable to use windows.forms it uses GTK#, and I don't think there
> is
> a wrapper for GTK#, but I am not sure if this will be needed.
>
> Also, was SQLite created using a structured or object-oriented methodolgy
> so
> that I can design the interface in the same way.
>
> Any suggestions would be greatfully appreciated.
>
> Aaron
>
>

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread JP

Sounds like an interesting setup! Maybe off topic, but, would you care 
to elaborate on that topic? Server configuration, virtualization 
software running, etc.?



[EMAIL PROTECTED] wrote:

Lenster <[EMAIL PROTECTED]> wrote:

The application needs to be available to about twenty users on a daily 
basis, with most of those users making no more than five 'write' 
transactions a day, and around twenty 'read' transactions a day. 




The SQLite website is itself backed by SQLite.  It handles
between 5000 and 6000 users per day, with each user doing about
10 writes on average and dozens of queries.  This is all 
accomplished on a server that is a virtual machine (using 
User Mode Linux) that is one of 24 virtual machines on the 
physical server.  There are actually several other websites 
running on the same virtual machine, though the SQLite 
website takes most of the load.


The SQLite database that backs the SQLite website has no
difficulty handling this load. It could scale to much more
traffic simply by devoting more of the physical server to
the task.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Most appropriate Web based database? (Newbie)


Lenster wrote:


I would like to know whether - at some point in the future should it be
necessary - I could convert my entire SQLite databases including tables etc
to another RDMS such as MySQL? Is this straightforward or convoluted?

 

The .dump command in the sqlite shell will dump your entire database as 
standard SQL statements that you can execute to recreate the database 
using some other database engine.


HTH
Dennis Cote

Re: [sqlite] Most appropriate Web based database? (Newbie)


Thanks for your reply Richard 

After further Googling and reading the replies I've received here I am
coming around to the idea that SQLite probably is the best choice.

I would like to know whether - at some point in the future should it be
necessary - I could convert my entire SQLite databases including tables etc
to another RDMS such as MySQL? Is this straightforward or convoluted?

Regards
Len
--
View this message in context: 
http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3788822
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Slow performance - Unrealistic expectations?


Thom Ericson wrote:



   Marian Olteanu wrote:

   You should embed the inserts into a transaction. Otherwise every 
insert is a

   separate ACID transaction = 2 disk spins.

I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" 
did that.  Am I confused?



Thom,

You are correct about putting 128 inserts between the begin and commit. 
You may get even higher performance if you use a larger batch size of 
500 or 1000 inserts. This amortizes the journal file creation, data 
copies, and deletion over more inserts. Right now you are doing about 4 
transactions per second. With a batch size of 1000 you might get 1 
transaction per second, but 1000 inserts per second. Its easy to do, so 
its probably worth a try.


HTH
Dennis Cote

Re: [sqlite] Slow performance - Unrealistic expectations?

> You should embed the inserts into a transaction. Otherwise every insert 
> is a
> separate ACID transaction = 2 disk spins.
>
>
> I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" did
> that.  Am I confused?

No, what you did was correct.

Just for grins:

If you have a 7200rpm drive you should theoretically be able to get:
7200 rpm / 2 rotations per transaction = 3600 transactions / minute

3600 transactions per minute / 60 seconds per minute = 60 transactions
per second.

The limiting factor would seem to be how many inserts can you generate
and push through in 1/60th of a second.


>
> This may have been the secret (and what Jay was trying to tell me
> above).  I pulled out the 'KEY' and the 'NOT NULL' on str and parent,
> and added the PRIMARY KEY.  Performance started out somewhat higher,
> around 1000 inserts per second and seems to have leveled off at 500+
> inserts per second.  I'm already up to 500,000 entries in my test.  I'd
> like even better performance, but this is passable.  I have not started
> the testing of my query code to see if there are any new problems.

On very large loads it's much faster to turn off indexes, load the data,
then create the index you'll use to read it. You don't pay for the index tree
rebalancing over and over on all the inserts. You might not be able to
do that in your case since your data is inter-related.

Sorry if I was too opaque in my response!
Glad you got it fixed.

Re: [sqlite] Slow performance - Unrealistic expectations?

2006-04-06 Thread Thom Ericson


See in-line comments below
--

   Jay Sprenkle wrote:

   Did you put an index on the table/columns the select uses to lookup
   the rowids of the parents?

I though that was what declaring 'str' as KEY and 'parent' as KEY was 
supposed to do, but see Dennis Cote's response below:


   Marian Olteanu wrote:

   You should embed the inserts into a transaction. Otherwise every insert is a
   separate ACID transaction = 2 disk spins.
 

I thought putting 128 of the inserts between "BEGIN;" and "COMMIT;" did 
that.  Am I confused?


   Dennis Cote wrote:

   Try this table definition instead.

  CREATE TABLE xyz
  (
  str TEXT,
  parent INTEGER,
  PRIMARY KEY(parent, str)
  );

   This will create an index to greatly speed the lookups of the
   existing rows when you do your inserts.

This may have been the secret (and what Jay was trying to tell me 
above).  I pulled out the 'KEY' and the 'NOT NULL' on str and parent, 
and added the PRIMARY KEY.  Performance started out somewhat higher, 
around 1000 inserts per second and seems to have leveled off at 500+ 
inserts per second.  I'm already up to 500,000 entries in my test.  I'd 
like even better performance, but this is passable.  I have not started 
the testing of my query code to see if there are any new problems.


Thanks

T


On 4/6/06, Thom Ericson <[EMAIL PROTECTED]> wrote:
  

I am trying to pick a light weight database for a project.  SQLite, on
paper, seems like the right choice, but performance is rather
disappointing.  I hope it is just that I am doing something wrong.

I have built SQLite for Solaris and Win32 environments and I get
essentially the same results.  As I started adding more rows to my
single table the performance drops off (expected).  I start off getting
about 500 inserts per second, but after 180,000 rows, the performance
has dropped to around 3 inserts per second.  I had hoped to be able to
handle 180,000,000 rows in my largest installation (that's gonna take a
while).  The table is very simple:

sqlite3 testdb << EOF
CREATE TABLE xyz
(
str TEXT KEY NOT NULL,
parent INTEGER KEY NOT NULL
);

The "str" column is typically 10 to 20 characters and the "parent"
column is the ROWID of some pre-existing row in the table (except for
the first row, where the parent is zero).  For each new row added to the
table, there is, on average,  one select performed that produces the
rowid of another entry given specific values for str and parent.  I
enclose about 128 of these selects and insert within a
"BEGIN;"/"COMMIT;" block which increased performance, but going to
higher values does not seem to help much.

With the Solaris installation, I don't see much disk activity (possibly
all blocks are cached), on windows, I am seeing around 22,000 I/O Reads
per second.

Re: [sqlite] Slow performance - Unrealistic expectations?


Thom Ericson wrote:


I am trying to pick a light weight database for a project.  SQLite, on
paper, seems like the right choice, but performance is rather
disappointing.  I hope it is just that I am doing something wrong.

I have built SQLite for Solaris and Win32 environments and I get
essentially the same results.  As I started adding more rows to my
single table the performance drops off (expected).  I start off getting
about 500 inserts per second, but after 180,000 rows, the performance
has dropped to around 3 inserts per second.  I had hoped to be able to
handle 180,000,000 rows in my largest installation (that's gonna take a
while).  The table is very simple:

sqlite3 testdb << EOF
CREATE TABLE xyz
(
   str TEXT KEY NOT NULL,
   parent INTEGER KEY NOT NULL
);

The "str" column is typically 10 to 20 characters and the "parent"
column is the ROWID of some pre-existing row in the table (except for
the first row, where the parent is zero).  For each new row added to the
table, there is, on average,  one select performed that produces the
rowid of another entry given specific values for str and parent.  I
enclose about 128 of these selects and insert within a
"BEGIN;"/"COMMIT;" block which increased performance, but going to
higher values does not seem to help much.

With the Solaris installation, I don't see much disk activity (possibly
all blocks are cached), on windows, I am seeing around 22,000 I/O Reads
per second.

Any hints from anyone

T




Thom,

Try this table definition instead.

   CREATE TABLE xyz
   (
   str TEXT,
   parent INTEGER,
   PRIMARY KEY(parent, str)
   );

This will create an index to greatly speed the lookups of the existing 
rows when you do your inserts.


HTH
Dennis Cote

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.


m christensen wrote:




I don't know if it does or not.

I intended to do both, but...

I can not get any variant I tried of --select sqlite_version(); -- to 
execute.

It always complained about the statement in one way or another.


It does work with the current shell. I'm not sure when this function was 
added though I think it has been around for a long time.


C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> select sqlite_version();
3.3.5
sqlite>



BUT
Between your help and Mr. Jackson and the ldd tool I found the problem.

SQLite.so does indeed use libsqlite3.so.0,  in my case.

I installed both SQLite 3.2.7 AND the perl DBD library which should 
both contain all the code

and build the required libs.

libsqlite3.so was built and installed into /usr/local/lib

The problem is the fact it appears my OS came with a copy of SQLite 
preinstalled, in /usr/lib.


Dynamic Linking found that one first.

Problem solved, Thanks everyone.


Good to hear your problem is solved.

Re: [sqlite] segmentation fault error?

On 4/6/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> No big deal. I thought it was funny (well a little humorous anyway) that
> you did exactly the same thing in the sample code you posted regarding
> the prepare statement tutorial question (only a few minutes apart).

Yeah, I wrote that a while ago and forgot that little detail.
I'm getting old and stupid.

RE: [sqlite] Slow performance - Unrealistic expectations?

2006-04-06 Thread Marian Olteanu

You should embed the inserts into a transaction. Otherwise every insert is a
separate ACID transaction = 2 disk spins.

-Original Message-
From: Thom Ericson [mailto:[EMAIL PROTECTED] 
Sent: Thursday, April 06, 2006 10:18 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Slow performance - Unrealistic expectations?

I am trying to pick a light weight database for a project.  SQLite, on
paper, seems like the right choice, but performance is rather
disappointing.  I hope it is just that I am doing something wrong.

I have built SQLite for Solaris and Win32 environments and I get
essentially the same results.  As I started adding more rows to my
single table the performance drops off (expected).  I start off getting
about 500 inserts per second, but after 180,000 rows, the performance
has dropped to around 3 inserts per second.  I had hoped to be able to
handle 180,000,000 rows in my largest installation (that's gonna take a
while).  The table is very simple:

sqlite3 testdb << EOF
CREATE TABLE xyz
(
str TEXT KEY NOT NULL,
parent INTEGER KEY NOT NULL
);

The "str" column is typically 10 to 20 characters and the "parent"
column is the ROWID of some pre-existing row in the table (except for
the first row, where the parent is zero).  For each new row added to the
table, there is, on average,  one select performed that produces the
rowid of another entry given specific values for str and parent.  I
enclose about 128 of these selects and insert within a
"BEGIN;"/"COMMIT;" block which increased performance, but going to
higher values does not seem to help much.

With the Solaris installation, I don't see much disk activity (possibly
all blocks are cached), on windows, I am seeing around 22,000 I/O Reads
per second.

Any hints from anyone

T

Re: [sqlite] Slow performance - Unrealistic expectations?

2006-04-06 Thread Alex Chudnovsky


Thom Ericson wrote:

I had hoped to be able to handle 180,000,000 rows in my largest 
installation (that's gonna take a

while).  Any hints from anyone


IMHO 180 mln rows is no small deal even for a big database - especially 
considering you have got text column in it.


If you are trying to implement something like full-text search, then you 
are better off creating your own highly tuned for your purposes custom 
database.


Alex

Re: [sqlite] segmentation fault error?


Jay Sprenkle wrote:


On 4/6/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
 


Jay,

No, he has the open call correct. He has a local pointer, he passes the
address of that pointer to sqlite3_open() and it allocates the sqlite3
structure and sets his pointer to point to it.

No need to change this. Derrell has identified his problem.
   



You're right. Thanks Dennis :)

I think I'll quit trying to do anything serious early in the morning...

 


Jay,

No big deal. I thought it was funny (well a little humorous anyway) that 
you did exactly the same thing in the sample code you posted regarding 
the prepare statement tutorial question (only a few minutes apart).


Here's some c++:

   sqlite3*   db;
   sqlite3_stmt *pStmt;
   intrc;

   string str;
   string sql;

   sql = "SELECT Created, Subject, Body FROM News";
   sql += " WHERE Created > ?";
   sql += " ORDER BY Created LIMIT 6";

   // connect to database
   rc = sqlite3_open( DB,  );

Have another cup of coffee. ;)

Dennis Cote

Re: [sqlite] Slow performance - Unrealistic expectations?

On 4/6/06, Thom Ericson <[EMAIL PROTECTED]> wrote:
> I am trying to pick a light weight database for a project.  SQLite, on
> paper, seems like the right choice, but performance is rather
> disappointing.  I hope it is just that I am doing something wrong.
>
> I have built SQLite for Solaris and Win32 environments and I get
> essentially the same results.  As I started adding more rows to my
> single table the performance drops off (expected).  I start off getting
> about 500 inserts per second, but after 180,000 rows, the performance
> has dropped to around 3 inserts per second.  I had hoped to be able to
> handle 180,000,000 rows in my largest installation (that's gonna take a
> while).  The table is very simple:
>
> sqlite3 testdb << EOF
> CREATE TABLE xyz
> (
> str TEXT KEY NOT NULL,
> parent INTEGER KEY NOT NULL
> );
>
> The "str" column is typically 10 to 20 characters and the "parent"
> column is the ROWID of some pre-existing row in the table (except for
> the first row, where the parent is zero).  For each new row added to the
> table, there is, on average,  one select performed that produces the
> rowid of another entry given specific values for str and parent.  I
> enclose about 128 of these selects and insert within a
> "BEGIN;"/"COMMIT;" block which increased performance, but going to
> higher values does not seem to help much.
>
> With the Solaris installation, I don't see much disk activity (possibly
> all blocks are cached), on windows, I am seeing around 22,000 I/O Reads
> per second.

Did you put an index on the table/columns the select uses to lookup
the rowids of the parents?

[sqlite] Slow performance - Unrealistic expectations?

2006-04-06 Thread Thom Ericson


I am trying to pick a light weight database for a project.  SQLite, on
paper, seems like the right choice, but performance is rather
disappointing.  I hope it is just that I am doing something wrong.

I have built SQLite for Solaris and Win32 environments and I get
essentially the same results.  As I started adding more rows to my
single table the performance drops off (expected).  I start off getting
about 500 inserts per second, but after 180,000 rows, the performance
has dropped to around 3 inserts per second.  I had hoped to be able to
handle 180,000,000 rows in my largest installation (that's gonna take a
while).  The table is very simple:

sqlite3 testdb << EOF
CREATE TABLE xyz
(
   str TEXT KEY NOT NULL,
   parent INTEGER KEY NOT NULL
);

The "str" column is typically 10 to 20 characters and the "parent"
column is the ROWID of some pre-existing row in the table (except for
the first row, where the parent is zero).  For each new row added to the
table, there is, on average,  one select performed that produces the
rowid of another entry given specific values for str and parent.  I
enclose about 128 of these selects and insert within a
"BEGIN;"/"COMMIT;" block which increased performance, but going to
higher values does not seem to help much.

With the Solaris installation, I don't see much disk activity (possibly
all blocks are cached), on windows, I am seeing around 22,000 I/O Reads
per second.

Any hints from anyone

T

[sqlite] Slow performance - Unrealistic expectations?

2006-04-06 Thread Thom Ericson


I am trying to pick a light weight database for a project.  SQLite, on
paper, seems like the right choice, but performance is rather
disappointing.  I hope it is just that I am doing something wrong.

I have built SQLite for Solaris and Win32 environments and I get
essentially the same results.  As I started adding more rows to my
single table the performance drops off (expected).  I start off getting
about 500 inserts per second, but after 180,000 rows, the performance
has dropped to around 3 inserts per second.  I had hoped to be able to
handle 180,000,000 rows in my largest installation (that's gonna take a
while).  The table is very simple:

sqlite3 testdb << EOF
CREATE TABLE xyz
(
   str TEXT KEY NOT NULL,
   parent INTEGER KEY NOT NULL
);

The "str" column is typically 10 to 20 characters and the "parent"
column is the ROWID of some pre-existing row in the table (except for
the first row, where the parent is zero).  For each new row added to the
table, there is, on average,  one select performed that produces the
rowid of another entry given specific values for str and parent.  I
enclose about 128 of these selects and insert within a
"BEGIN;"/"COMMIT;" block which increased performance, but going to
higher values does not seem to help much.

With the Solaris installation, I don't see much disk activity (possibly
all blocks are cached), on windows, I am seeing around 22,000 I/O Reads
per second.

Any hints from anyone

T

Re: [sqlite] Sudden DISTINCT parsing failures with perl and sqlite DBD 1.11 / DBI 1.5 / sqlite 3.2.7 /CentOS 4.3 -or- RHEL 4 linux.

2006-04-06 Thread m christensen




Are you sure that $dbh0>(sqlite_version) returns the same thing as 
executing the following SQL:


   select sqlite_version();

I don't use perl, so I'm not sure how you actually execute the query.

One other possibility, you may have a wrapper that is statically 
linked to an older library and the SQLite.so dynamic library may not 
be used.


Finally, shouldn't the library name be sqlite3.so or libsqlite3.so?

Dennis Cote


I don't know if it does or not.

I intended to do both, but...

I can not get any variant I tried of --select sqlite_version(); -- to 
execute.

It always complained about the statement in one way or another.

BUT
Between your help and Mr. Jackson and the ldd tool I found the problem.

SQLite.so does indeed use libsqlite3.so.0,  in my case.

I installed both SQLite 3.2.7 AND the perl DBD library which should both 
contain all the code

and build the required libs.

libsqlite3.so was built and installed into /usr/local/lib

The problem is the fact it appears my OS came with a copy of SQLite 
preinstalled, in /usr/lib.


Dynamic Linking found that one first.

Problem solved, Thanks everyone.

Marc

Re: [sqlite] segmentation fault error?

On 4/6/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Jay,
>
> No, he has the open call correct. He has a local pointer, he passes the
> address of that pointer to sqlite3_open() and it allocates the sqlite3
> structure and sets his pointer to point to it.
>
> No need to change this. Derrell has identified his problem.

You're right. Thanks Dennis :)

I think I'll quit trying to do anything serious early in the morning...

Re: [sqlite] sqlite-3.3.4 and extra float decimals


Floppe wrote:


Will Leshner wrote:
 


On 4/5/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
 
   


Hello,

When using sqlite-3.3.4 with windows I get the following strange behaviour.

create table Muppet (Kermit float);
insert into Muppet values (100);
select * from Muppet;
100.0
   
 


That's not strange. Kermit is defined to be a FLOAT, so it gets
returned as a FLOAT. Perhaps you wanted it to be returned as an
INTEGER? You could try something like this:

SELECT cast(kermit as INTEGER) FROM Muppet;
 
   



I wanted it to return the same format as SQLite does in Windows CE or
any other SQL engine does. Consistency? If someone enters 100 then it
could be a rounded value but if someone enters 100.0 (or 100.1) then it
usually isn't rounded. So now I need two fields, one with the value and
one that says how many decimals the user entered and then format the
output? Or make every field as varchars? Seems a little stupid to me.

If this is a part of the roadmap and not a bug then the next question
would be, is version 3.2.8 stable as that was the last version that
worked as I wanted in winxp?



 

This behavior was changed as a result of this bug report 
http://www.sqlite.org/cvstrac/tktview?tn=1362


I think the current behavior is correct.

If you have a column where you want to store exactly the data the user 
entered, you should use a text column. You can convert that text to 
numeric data by whatever means you deem appropriate whenever you want 
to. You should only use a floating point column if you want to store the 
numeric value that the user entered regardless of how he entered it. The 
database should not make a distinction between 1.0 and 1.000 for a 
floating point value, but these are not the same thing when you 
interpret the extra decimal places as increased accuracy.


This is very similar to the use of leading zeros on integer numbers. As 
integers, 001 and 1 are the same thing. If you need to distinguish 
between the two, you should be using a text column.


Dennis Cote

Re: [sqlite] segmentation fault error?

Jay Sprenkle wrote:

> Here you pass the address of db, which is already a pointer.
>
>You've passed a pointer to a pointer but never allocated the structure used.
>
>I think you want to change this:
>  
>
>>  sqlite3 *db;
>>
>>
>to
>  
>
>>  sqlite3 db;
>>
>>
Jay,

No, he has the open call correct. He has a local pointer, he passes the
address of that pointer to sqlite3_open() and it allocates the sqlite3
structure and sets his pointer to point to it.

No need to change this. Derrell has identified his problem.

Dennis Cote

Re: [sqlite] PREPARE statement tutorial?


Olaf Beckman Lapré wrote:


Hi,

I assume that the sqlite3_prepare() / sqlite3_bind() combination results in 
faster performance than sqlite3_exec() for INSERT and UPDATE statements. But 
where can I find example code that uses prepare/bind? Googling didn't give any 
results.

Greetz,

Olaf
 


Olaf,

You could look at the source code for sqlite3_exec() in legacy.c at . It 
is implemented using sqlite3_prepare() and friends. The 
sqlite3_prepare() API functions are also used to implement many features 
of the sqlite shell. You can search for sqlite3_prepare in the file 
shell.c. The source files can be viewed at 
http://www.sqlite.org/cvstrac/dir?d=sqlite/src.


HTH
Dennis Cote

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread drh

Lenster <[EMAIL PROTECTED]> wrote:
> 
> The application needs to be available to about twenty users on a daily 
> basis, with most of those users making no more than five 'write' 
> transactions a day, and around twenty 'read' transactions a day. 
> 

The SQLite website is itself backed by SQLite.  It handles
between 5000 and 6000 users per day, with each user doing about
10 writes on average and dozens of queries.  This is all 
accomplished on a server that is a virtual machine (using 
User Mode Linux) that is one of 24 virtual machines on the 
physical server.  There are actually several other websites 
running on the same virtual machine, though the SQLite 
website takes most of the load.

The SQLite database that backs the SQLite website has no
difficulty handling this load. It could scale to much more
traffic simply by devoting more of the physical server to
the task.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Structured or Object-Oriented?

2006-04-06 Thread Aaron Jones

Hi, I am doing a project for University where I am creating a
cross-platform, open-source GUI to SQLite, I am going to be using mono to
build it, do you think this will be ok or do you think I will encounter any
problems along the way?  I've noticed there is a wrapper for mono, but where
mono is unable to use windows.forms it uses GTK#, and I don't think there is
a wrapper for GTK#, but I am not sure if this will be needed.

Also, was SQLite created using a structured or object-oriented methodolgy so
that I can design the interface in the same way.

Any suggestions would be greatfully appreciated.

Aaron

Re: [sqlite] segmentation fault error?

On 4/6/06, 杰 张 <[EMAIL PROTECTED]> wrote:
> Hi all,
>   I just want to get the values of a table.The result implemented is 
> "Open OK!
>   segmentation fault ". Why did I got this result ? The following is my code:
>
>   #include 
> #include 
> #include "sqlite3.h"
> main()
> {
> char **errmsg;
> int ret;
> int rc;
>   sqlite3 *db;
> char *sql = "SELECT * FROM light;";
> char ***resultp;
> int *nrow;
> int *ncolumn;
> ret = sqlite3_open("sensor.db",);

Here you pass the address of db, which is already a pointer.
You've passed a pointer to a pointer but never allocated the structure used.

I think you want to change this:
>   sqlite3 *db;
to
>   sqlite3 db;



---
SqliteImporter: Command line fixed and delimited text import.
http://www.reddawn.net/~jsprenkl/Sqlite

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread John Stanton

Sqlite works very well for web applications unless they are large and 
very busy.  It is easy to manage and backup and performs well.


For a larger scale operation PostgreSQL would be a better choice than 
MySql.  If you design your application with care you could switch from 
Sqlite to PostgreSQL if the scale of operation demanded it.

JS

Lenster wrote:

Thanks Guys

I appreciate the input.



Denis Sbragion
how critical and complex is the data stored?


Well the data IS critical but NOT complex



Gerhard Häring



From what you have said Gerhard it seems that SQLite is probably upto the

job, however MySQL or PostgreSQL will be more futureproof should I want to
expand the datasets, functionality and online support is greater. As far as
configuring the server being the main difference between SQLite and MySQL -
well I have already configured a test MySQL server and that seemed easy
enough.

I think I have convinced myself that MySQL would be the best choice for this
application. I rate MySQL over PostgreSQL for two reasons:

1) Googling PostgreSQL produced 52,700,000 hits, Googling MySQL produced
397,000,000 hits
2) I've already bought a book about MySQL  ;-)

Thanks again



--
View this message in context: 
http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3782823
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] segmentation fault error?

2006-04-06 Thread Derrell . Lipman

杰 张 <[EMAIL PROTECTED]> writes:

> 1.  (*) text/plain  
>
> Hi all,
>   I just want to get the values of a table.The result implemented is 
> "Open OK!
>   segmentation fault ". Why did I got this result ? The following is my code:

Just from a quick visual inspection, it appears that you need to make the
following changes.  You need to pass the ADDRESS of where to put the allocated
pointers and returned number of rows/columns.

>
>   #include 
> #include 
> #include "sqlite3.h"
> main()
> {

> char **errmsg;

char *errmsg;

> int ret;
> int rc;
>   sqlite3 *db;
> char *sql = "SELECT * FROM light;";

> char ***resultp;
> int *nrow;
> int *ncolumn;

char **resultp;
int nrow;
int ncolumn;

> ret = sqlite3_open("sensor.db",);
> if (ret)
> {
> fprintf(stderr, "Could not open database:%s\n", sqlite3_errmsg(db));
> exit (1);
> }
> else 
>  {  printf("Open OK!\n");

> rc = sqlite3_get_table(db,sql,resultp,nrow,ncolumn,errmsg);

rc = sqlite3_get_table(db,sql);

>   printf("rc=%d\n",rc);
> if (rc)
>  {
>  fprintf(stderr,"can't open the table:%s\n",sqlite3_errmsg(db));
>  exit(1);
>  }
> else printf("open the table ok");
>}
>  
> sqlite3_close(db);
> printf("Close OK!");
> }
>
>   Thank you so much!
>
>   zhangjie 
>
>
>   
> -
>  雅虎1G免费邮箱百分百防垃圾信
>  雅虎助手-搜索、杀毒、防骚扰

RE: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread Fred Williams

Then again only 10,000,000 hits for SQLite (Less information to wade through)
and NO book to buy! (zero out of pocket expense!)

Good luck!  You are most likely headed the right direction.

> -Original Message-
> From: Lenster [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 06, 2006 6:59 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Most appropriate Web based database? (Newbie)
> 
> 
> 
> Thanks Guys
> 
> I appreciate the input.
> 
> >Denis Sbragion
> >how critical and complex is the data stored?
> Well the data IS critical but NOT complex
> 
> >Gerhard Häring
> From what you have said Gerhard it seems that SQLite is 
> probably upto the
> job, however MySQL or PostgreSQL will be more futureproof 
> should I want to
> expand the datasets, functionality and online support is 
> greater. As far as
> configuring the server being the main difference between 
> SQLite and MySQL -
> well I have already configured a test MySQL server and that 
> seemed easy
> enough.
> 
> I think I have convinced myself that MySQL would be the best 
> choice for this
> application. I rate MySQL over PostgreSQL for two reasons:
> 
> 1) Googling PostgreSQL produced 52,700,000 hits, Googling 
> MySQL produced
> 397,000,000 hits
> 2) I've already bought a book about MySQL  ;-)
> 
> Thanks again
> 
...

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread Denis Sbragion

Hello Len,

On Thu, April 6, 2006 13:58, Lenster wrote:
> 1) Googling PostgreSQL produced 52,700,000 hits, Googling MySQL produced
> 397,000,000 hits
...

well, this is quite a poor comparison. On the Internet there are many articles
comparing MySQL vs PostgreSQL vs other database engines. As a starting point
take a look at this:

http://builder.com.com/5100-6388_14-1050671.html

If the data is critical I would choose PostgreSQL over MySQL. Furthermore
there are many useful features of PostgreSQL which are missing in MySQL. BTW,
it's your data. :)

Bye,

-- 
Denis Sbragion
InfoTecna
Tel: +39 0362 805396, Fax: +39 0362 805404
URL: http://www.infotecna.it

Re: [sqlite] segmentation fault error?

2006-04-06 Thread Ran

Find out yourself :-)

If you are using gcc, add -g flag when compiling, and then run it using gdb:

   gdb your-executable

and then

  > run

and then when it give the seg-fault, run:
  > backtrace

This will give you a good hint about what is wrong.

Ran

On 4/6/06, 杰 张 <[EMAIL PROTECTED]> wrote:
>
> Hi all,
>   I just want to get the values of a table.The result implemented is
> "Open OK!
>   segmentation fault ". Why did I got this result ? The following is my
> code:
>
>   #include 
> #include 
> #include "sqlite3.h"
> main()
> {
> char **errmsg;
> int ret;
> int rc;
>   sqlite3 *db;
> char *sql = "SELECT * FROM light;";
> char ***resultp;
> int *nrow;
> int *ncolumn;
> ret = sqlite3_open("sensor.db",);
> if (ret)
> {
> fprintf(stderr, "Could not open database:%s\n",
> sqlite3_errmsg(db));
> exit (1);
> }
> else
>  {  printf("Open OK!\n");
> rc = sqlite3_get_table(db,sql,resultp,nrow,ncolumn,errmsg);
>   printf("rc=%d\n",rc);
> if (rc)
>  {
>  fprintf(stderr,"can't open the
> table:%s\n",sqlite3_errmsg(db));
>  exit(1);
>  }
> else printf("open the table ok");
>}
>
> sqlite3_close(db);
> printf("Close OK!");
> }
>
>   Thank you so much!
>
>   zhangjie
>
>
>
> -
> 雅虎1G免费邮箱百分百防垃圾信
> 雅虎助手-搜索、杀毒、防骚扰
>

Re: [sqlite] Most appropriate Web based database? (Newbie)


Thanks Guys

I appreciate the input.

>Denis Sbragion
>how critical and complex is the data stored?
Well the data IS critical but NOT complex

>Gerhard Häring
>From what you have said Gerhard it seems that SQLite is probably upto the
job, however MySQL or PostgreSQL will be more futureproof should I want to
expand the datasets, functionality and online support is greater. As far as
configuring the server being the main difference between SQLite and MySQL -
well I have already configured a test MySQL server and that seemed easy
enough.

I think I have convinced myself that MySQL would be the best choice for this
application. I rate MySQL over PostgreSQL for two reasons:

1) Googling PostgreSQL produced 52,700,000 hits, Googling MySQL produced
397,000,000 hits
2) I've already bought a book about MySQL  ;-)

Thanks again



--
View this message in context: 
http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3782823
Sent from the SQLite forum at Nabble.com.

[sqlite] segmentation fault error?

2006-04-06 Thread 杰张

Hi all,
  I just want to get the values of a table.The result implemented is "Open 
OK!
  segmentation fault ". Why did I got this result ? The following is my code:
   
  #include 
#include 
#include "sqlite3.h"
main()
{
char **errmsg;
int ret;
int rc;
  sqlite3 *db;
char *sql = "SELECT * FROM light;";
char ***resultp;
int *nrow;
int *ncolumn;
ret = sqlite3_open("sensor.db",);
if (ret)
{
fprintf(stderr, "Could not open database:%s\n", sqlite3_errmsg(db));
exit (1);
}
else 
 {  printf("Open OK!\n");
rc = sqlite3_get_table(db,sql,resultp,nrow,ncolumn,errmsg);
  printf("rc=%d\n",rc);
if (rc)
 {
 fprintf(stderr,"can't open the table:%s\n",sqlite3_errmsg(db));
 exit(1);
 }
else printf("open the table ok");
   }
 
sqlite3_close(db);
printf("Close OK!");
}
   
  Thank you so much!

  zhangjie 
   


-
 雅虎1G免费邮箱百分百防垃圾信
 雅虎助手-搜索、杀毒、防骚扰

[sqlite] PREPARE statement tutorial?

2006-04-06 Thread Olaf Beckman Lapré

 Hi,

I assume that the sqlite3_prepare() / sqlite3_bind() combination results in 
faster performance than sqlite3_exec() for INSERT and UPDATE statements. But 
where can I find example code that uses prepare/bind? Googling didn't give any 
results.

Greetz,

Olaf

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread Gerhard Häring


Lenster wrote:
I am investigating which would be the most appropriate RDMS to use for 
a new Intranet based application. I have rounded down my choice to two 
candidates - SQLite and MySQL. 


Ok. I think that PostgreSQL and Firebird are almost always better 
choices than MySQL for a database system though.


The application needs to be available to about twenty users on a daily 
basis, with most of those users making no more than five 'write' 
transactions a day, and around twenty 'read' transactions a day. 
[...]


Sounds like soemething SQLite can easily handle. SQLite only gets 
inappropriate if concurrent writers happen often. But you have to stress 
a web application quite a lot to make that happen.


Depending upon its success the application may well develop to 
incorporate other datasets and users - so the RDMS needs to be able to 
develop without difficulty.


Unless you build a heavy-traffic web application or need advanced SQL 
features, SQLite should be appropriate.


IMHO SQLite looks as though it would be the fastest to develop and with 
the least resources required 


That's true. But after you have set up and configured a database server, 
I don't think there's a big difference between ease of development for 
SQLite vs. MySQL vs. PostgreSQL vs. Oracle vs. whatever.


The difference is that setting up and configuring a database server 
costs time, and with SQLite you just don't need to do it.


but after reading some SQLite 
documentation: 

"A good rule of thumb is that you should avoid using SQLite in 
situations where the same database will be accessed simultaneously from 
many computers over a network filesystem." [...]


That is irrelevant for a web application. Network filesystems are SMB 
(Windows shares), NFS, Netware shares, etc. So this paragraph is about 
multiple client applications accessing the same SQLite database file 
over a network filesystem. In the case of a web application, you only 
really have one application that accesses the SQLite database file. And 
the SQLite database file is locally accessed.


I am concerned that even though I expect light usage it is likely that 
multiple users will access the same database concurrently. [...]


That's abssolutely no problem for read operations ("select"). For write 
operations ("insert"/"update"/"delete"), SQLite uses locks, so only one 
connection can modify the database at any time. Just be sure to COMMIT 
or ROLLBACK as soon as you can.


HTH,

-- Gerhard

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread Denis Sbragion

Hello Len,

On Thu, April 6, 2006 10:49, Lenster wrote:
...
> Would I be correct in assuming that MySQL is a safer (longterm) bet?

how critical and complex is the data stored? If it's fairly critical and/or
complex (many tables with relations between them) PostgreSQL may be an even 
better choice.

Bye,

-- 
Denis Sbragion
InfoTecna
Tel: +39 0362 805396, Fax: +39 0362 805404
URL: http://www.infotecna.it

Re: [sqlite] Most appropriate Web based database? (Newbie)

2006-04-06 Thread Chris Gurtler

Hi Lenster,

For a Web application you are probably better off with MySQL, it runs as
a seperate process and handles multiple threads really well. I'm
probably going to offend the sqlite people out there, but sqlite is
fantastic for an embedded database, and a web app is not what I would
call embedded.

Regards,

Chris

Lenster wrote:

I am investigating which would be the most appropriate RDMS to use for
a new Intranet based application. I have rounded down my choice to two
candidates - SQLite and MySQL.

The application needs to be available to about twenty users on a daily
basis, with most of those users making no more than five 'write'
transactions a day, and around twenty 'read' transactions a day.

Depending upon its success the application may well develop to
incorporate other datasets and users - so the RDMS needs to be able to
develop without difficulty.

IMHO SQLite looks as though it would be the fastest to develop and with
the least resources required but after reading some SQLite
documentation:

"A good rule of thumb is that you should avoid using SQLite in
situations where the same database will be accessed simultaneously from
many computers over a network filesystem."

I am concerned that even though I expect light usage it is likely that
multiple users will access the same database concurrently.

Would I be correct in assuming that MySQL is a safer (longterm) bet?

Thanks for any and all responses

Len
--
View this message in context:
http://www.nabble.com/Most-appropriate-Web-based-database-%28Newbie%29-t1404628.html#a3780531
Sent from the SQLite forum at Nabble.com.

[sqlite] Most appropriate Web based database? (Newbie)