Re: [sqlite] Memory Usage

[John Stanton]

What happens when you write a simple test program to open the DB?  You 
can certainly run Valgrind on that or even put in your own debug 
statements to examine the heap usage.


This would be the first thing to do in indentifying the problem.  It 
will tell you whether to look at Sqlite or your application.


Ben Clewett wrote:

Hi Numo and others,

I am very glad to hear the consensus is that there is nothing wrong with 
 libsqlite3.so.0.8.6.


However the fact is that the 'open' still acquires 16MB of memory.

Immediately Before:

  VmSize:   8572 kB
  VmLck:   0 kB
  VmRSS:2252 kB
  VmData:484 kB
  VmStk:  88 kB
  VmExe:  20 kB
  VmLib:6772 kB
  VmPTE:  20 kB

Immediately After:

sqlite3_open(sDatabaseFile, ) (= SQLITE_OK)

  VmSize:  24960 kB
  VmLck:   0 kB
  VmRSS:2368 kB
  VmData:  16872 kB
  VmStk:  88 kB
  VmExe:  20 kB
  VmLib:6772 kB
  VmPTE:  24 kB

I can't use valgrind as this is a TCP daemon, so I will have to build 
some form of simulator to investigate further


Thanks,

Ben


Nuno Lucas wrote:


On 10/27/06, Ben Clewett <[EMAIL PROTECTED]> wrote:


I am linking to libsqlite3.so.0.8.6.  After calling sqlite3_open(...) I
find my programs data memory jumps by 16392 Kb.

This seems a lot.  The database I am opening is only 26K in size.



There are many different ways of memory "jump" (like linking with a
lot of dynamic libraries), but one thing is certain: sqlite is not
responsible for that.


I have a similar process opening about 90 times.  This obviously
consumes a very large amount of memory, 1.4G with 90 processes.



It's a memory leak in your program, for sure. Run some memory leak
tool (e.g. valgrind).


May I ask if this is what would be expected, and whether there is
anything I can do to lower this loading?



Unless you decided to mess with sqlite internals, it's not expected in 
any way.



Thanks for your help,

Ben.



Best regards,
~Nuno Lucas

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









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

Re: [sqlite] Using SQLite in threadly Web server applications

[John Stanton]

At some point shared usage of a single resource involves some form of 
synchronization.  If you use Sqlite the you need to add that capability 
somehow.  Sqlite lets you use file locks fairly simply for that purpose 
if that suits your application.


An alternative is to use something like PostgreSQL or Oracle which 
incorporate the extra layers of software but, ipso facto, lack the 
simplicity of Sqlite.


The problem is not so strange, as Dr Hipp points out.  Using Sqlite as a 
shared resource is just like using a file as a shared resource.  If you 
take his advice that Sqlite is not a replacement for DB/2 or Oracle but 
a replacement for fopen you understand the problem.


We find Sqlite to be a remarkable tool because it implements either a 
small embedded database or becomes part of a lightweight implementation 
of a special purpose multiple user database where the resource sharing 
is tailored to the application and the overhead of an enterprise level 
DBMS is thereby avoided.


David Gewirtz wrote:
 
Those suggestions are great. For something like log analysis, there's no

problem either reading in log file tails ever so often or queuing up a
single thread to give more real-time access. I think I'm leaning towards a
more real-time view, but I might take the tail option if it codes easier.

On the topic of threads, though, this does open a pile of other "running
behind a Web server" sort of operations:

* To keep a db of user authentication data in an SQLite db, users will need
some level of real-time response to logins

* To keep a db of, say, a discussion forum, user posts will need to be
written and made available in realish-time

* To update Web pages dynamically with information populated from a user's
preference or users' usage patterns, the page will need to be generated in
real time

And each of these interactions with the server will occur with different
user sessions in different threads. Can SQLite be used to build applications
that do the operations above? Do I need to do some single-thread queue
structure for each approach?

I'd love some guiding thoughts on these things to help understand how to
really approach the problem.

Thanks!

-- David



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




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

Re: [sqlite] Using SQLite to record Web log file data (a threading question)

[John Stanton]

[EMAIL PROTECTED] wrote:

"David Gewirtz" <[EMAIL PROTECTED]> wrote:


I've been exploring SQLite for a number of applications, but one I'd like to
embark on soon is using SQLite to record Web site log file data, so I can
perform SQL-based analysis on my logs, rather than using some thing like
Analog.

Unfortunately, each Web access on the server is likely to be in its own
thread. The scenario I'd probably have to build might go something like
this:

* Web server launches
* SQLite issues sqlite3_open to log db, gets log_ID
* Web server handles user 1 in thread 1, which writes to log db
* Web server handles user 2 in thread 2, which writes to log db
* Web server handles user n in thread n, which writes to log db
* Web server handles admin request for log analysis, which reads from log db
* Web server begins shutdown, closes log db
* Web server shuts down


From my reading, it's just not clear to me whether this is bad behavior for

SQLite 3.3.7. Can SQLite handle this sort of sequence reliably. If not, any
suggestions about how I might proceed or how I should think about it?




The way I handle this at www.sqlite.org is that web log data just
gets appended to an ordinary text file.  Then when I want to do
analysis, I make a copy of the text file and import it into an
SQLite database.  I think do queries against the SQLite database
to extract the information I want to know.

You could perhaps automate this so that a background task took
the unprocessed tail of your log file and added it an SQLite
database every 10 minutes or so.  Or every 30 seconds.  Just 
rig it so that you only have one process trying to write at

a time and so that you do not have to take transaction overhead
for every single web hit.

SQLite itself is not the best tool for doing multiple concurrent
writes from different threads or processes.

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


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

I made Sqlite accept log data and analysis from a web server.  From a 
multi-threaded web server I used a seperate thread to handle the DB 
insertion.  It is driven from a queue of log transactions posted by the 
active threads.  That way it is single streamed and has no contention 
problems and the active threads have a very fast mechanism to post their 
log data, just queue a pointer to the buffer used by the thread.  Each 
time a thread activates it gets a freed buffer from a pool.  When the 
log thread finishes a transaction it drops the buffer onto the free list.


The advantage of this approach is that it gives real time log analysis. 
 To that end some frequently accessed summaries are maintained.  Just 
to maintain the log in a simple form in an Sqlite database offers 
little, if any, advantage over keeping a text file and analysing it from 
time to time.


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

Re: [sqlite] Re: Regarding sqlite3_exec

[John Stanton]

Nuno Lucas wrote:

On 10/25/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


Nuno Lucas wrote:
>
> There is another alternative if you don't mind to have the overhead of
> having an automatic row count (which sqlite avoids by design). It's by
> having a trigger that will update the table row count on each
> insert/delete.
>
Nuno,

This technique only works if you never use conditions on your queries.
If your query returns a subset of the rows in a table this carefully
maintained count of all the rows in the table is useless.



Sure, but I wasn't trying to solve the general issue. The only
solution for that is to run the query to full length, whatever the SQL
engine is (even if they hide it from the user/programmer).

The trigger(s) could be elaborated to specific queries, off course,
and that would solve the GUI issue for fixed queries, but the generic
case will never have a solution other than the full scan.

Anyway, I never used this solution, just trying to show it can be
solved for the specific cases of most GUI's, if the trigger overhead
is not a problem.

In my modest opinion, if one really needs to have an accurate
scrollbar, one should show the rows by pages, with buttons to go to
the next/prev page (and the scrollbar will be correct per page). No
need to know the total rows in the view (unless we know the table
doesn't grow that much that a "select count(*)", or a full select into
memory, doesn't add much to the window rendering, which is most
times).


Regards,
~Nuno Lucas




Dennis Cote.



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



Some possible solutions to the problem of defining a result set size 
without using much extra memory, disk space or machine overhead.


If you want to know the size of your result set in advance and then 
select pages from that set an efficient way you could execute the query 
and build some form of index to the returned rows, using the rowid as 
the unique ID.  Then you can traverse that index at leisure, reading 
data columns as required.  You could organize your index so that a 
pre-order traversal gives you the sequence you want later.  That avoids 
the need for an ORDER BY.


The effect would be like having a CURSOR.  It involves a little 
programming, but then nothing is free.


A somewhat heavier duty, but simpler, alternative is just to write the 
result set to a temporary table, index it on the access key then use it 
for output.


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

Re: [sqlite] Re: Regarding sqlite3_exec

[John Stanton]

There is no magic in data retrieval.  Google use the same physical laws 
as us ordinary mortals.


I see no reason to ever perform a dataabase search twice.

Da Martian wrote:
Yes but google doesnt us an RDMS, its all propriatary to support there 
speed

and huge volumes. Its anyones guess (excpet google themselves) what exactly
they do, and rumours abound, but I have done many apps which require custom
data handling to achieve some end that doesnt fit with RDBM Systems.

But yes paging and using LIMIT and OFFSET is also a solution. Again not as
efficent though, cause of all the repeated queris :-)


On 10/25/06, Martin Jenkins <[EMAIL PROTECTED]> wrote:



Da Martian wrote:

> But to return all the rows just to count them requires N calls to
> step. If the data set is large you only want to return a subset to
> start with. So you wouldnt know the count. If you dont know the
> count, you cant update GUI type things etc..

I haven't been following this thread closely, but isn't this exactly the
problem that Google "solves" by returning

  "Results 1 - 10 of about 9,940,000 for sqlite. (0.11 seconds)"

for a query with a very large result set? If Google can't do it with all
the resources they have...

Martin


- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 









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

Re: [sqlite] blob vs. string in bound parameters

[John Stanton]

Actually I was talking about an application language which lets users
incorporate their own SQL and which binds to the SQL at run time rather
than being like embedded SQL.

It doesn't need to parse SQL, it just uses the existing SQL API which 
provides all the necessary capability.


The application language compiler does not need to parse the SQL.
When I was writing the compiler I thought of looking at the SQL but soon 
realized that it was not necessary, the Sqlite API had enough

functionality to make that unecessary.

As I said before, the users of my application language declare Sqlite
columns to be anything their application would like to see.  The binding 
logic retrieves that declared type (e.g. DECIMAL(5,2)) and gets the 
actual type (which may be TEXT) and performs the appropriate move, in 
that case a TEXT string of digits into a fixed point, display format 
number.


Sqlite's "manifest typing" has been carefully thought through and is 
almost all things for all people.


Andy Ross wrote:

John Stanton wrote:
 > The method I wrote about earlier is part of a language binding.
 > I can show you the code if you are interested.

I think we must be talking about different things, then.  A language
binding that allows the user to write their own SQL cannot
automatically insert cast() calls into the queries without parsing the
SQL and making a runtime determination about the context in which each
bound parameter will be evaluated.  And that's the halting problem. :)

Maybe you're talking about something like rails, which abstracts away
the SQL from the user API and generates its own queries?  Then sure.
But that's not the level of abstraction I'm working with.

But regardless: sure, I'd be curious to see how you are dealing with
the same issue.  What I've done for now is just change the default
from bind_blob() to bind_text(), and add some language to the docs
warning users that they need to add a cast-to-text when writing
expressions involving blob columns.  Since comparing blob values is
obviously rare, this seems to me like an acceptable compromise.

Andy

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] blob vs. string in bound parameters

[John Stanton]

The method I wrote about earlier is part of a language binding.

I can show you the code if you are interested.

Andy Ross wrote:

Dennis Cote wrote:
 > The following log shows that sqlite does indeed distinguish
 > between a text field and a blob with the same content. It also
 > shows you a workaround. You simply need to cast your fields to
 > blobs before you compare them to a variable that is bound to a
 > blob.

But I can't do that.  I'm writing a language binding, not an
application*, so changes to the query text are outside the realm
of what I can play with.  But the fact that it exists is actually
really helpful, because I can just write it into the docs as a
known gotcha without chucking my design entirely, and especially
because it focuses my earlier question better:

If [1] SQLite automatically casts between types as needed in most
cases (e.g. real to/from string) and [2] the cast() syntax
already works to convert between strings and blobs in
the "obvious" way, then: why is the cast automatic for numberic
values, but not for strings/blobs?  Isn't that a
non-orthogonality design flaw?

Andy

* ..er, well, I'm writing a language binding *and* an app at the
  moment.  But I'm much more concerned about the problems with
  the bindings.  As I mentioned earlier, I can already work
  around the issue by making sure all columns that will be
  queried with parameters are specified as blobs.

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] blob vs. string in bound parameters

[John Stanton]

Sqlite has declared types and actual types.  Both can be accessed 
through the API.


What I do is look at the declared type, which defines the data and then 
look at the actual type to determine how to process it.


Youn can declare the type to be anything you want.  A name such a GEORGE 
or INVOICE-DATE are each accpetable.


Andy Ross wrote:

I'm working with the sqlite3 bindings to my "nasal" languages
(http://plausible.org/nasal, if anyone is curious) and I'm having a
problem with type conversion.

Take a look at the attached sample code.  What it basically does is to
try to extract a row from a table with a bound parameter:

  "select val from tab where val = ?"

The val column in the table is declared as a "text" column.  But
because I'm working in a language that doesn't distinguish between
strings and byte arrays, I have to do the binding with
sqlite3_bind_blob() instead of sqlite3_bind_text().

Problem is, the *blob* value of "testval" does not, apparently, equal
the *text* value of "testval" in the database.

The workaround right now is to always define the columns as type blob,
never text.  But this strikes me as pretty unambiguously a bug.
Clearly an ASCII string should be equal in either representation: what
possible blob value of "testval" could there be except a 7 byte
string: {'t','e','s','t','v','a','l'}?

Any ideas, or have I misunderstood something?

Andy




#include 
#include 
#include 

// Initialize with: (note column "val" has type "text"):
//
//   rm -f test.db
//   echo "create table tab (val text);" | sqlite3 test.db
//   echo "insert into tab values ('testval');" | sqlite3 test.db
//
// Validate: (prints 'testval' as expected)
//
//   echo "select val from tab where val = 'testval';" | sqlite3 test.db
//
// Test: (note column "val" is bound with sqlite3_bind_blob()):
//
//   gcc -o test test.c -lsqlite3 && ./test
//
// Result:
//
// The blob apparently tests as not equal to the identitcal string,
// and the query returns zero rows.

#define DB "test.db"
#define QUERY "select val from tab where val = ?"
#define FIELD "testval"

#define PERR(msg) { printf(msg); printf("%s\n", sqlite3_errmsg(db)); }

int main()
{
int stat, cols, i;
sqlite3 *db;
sqlite3_stmt *stmt;
const char *tail;

if(sqlite3_open(DB, )) {
PERR("open failure\n");
return 1;
}

if(sqlite3_prepare(db, QUERY, strlen(QUERY), , )) {
PERR("prepare failure\n");
return 1;
}

// Calling bind_text() here works, bind_blob() does not:
if(sqlite3_bind_blob(stmt, 1, FIELD, strlen(FIELD), SQLITE_TRANSIENT)) {
PERR("bind failure\n");
return 1;
}

while((stat = sqlite3_step(stmt)) != SQLITE_DONE) {
cols = sqlite3_column_count(stmt);
for(i=0; i

Re: [sqlite] Re: bind with select?

[John Stanton]

Use sqlite3_step.

Dave Dyer wrote:

You don't appear to be using BIND in the manner I was hoping for.  You're
using BIND to replace variables in the query.  I want to use BIND 
(or something like it) eliminate the need for callback functions

to consume the results of a select.

--

At 05:42 PM 10/20/2006, Jay Sprenkle wrote:


On 10/16/06, Dave Dyer <[EMAIL PROTECTED]> wrote:


I can't find an example, but it seems like there ought to be
syntax to use bind to inline selection variables, instead of
having to have a callback function.

Something like:

char *forval,*barval;
sqlite_prepare(db,"select ?foo,?bar from table");
sqlite_bind("?foo",);
sqlite_bind("?bar",);

while () { sqlite_step()
  // foo and var bound to current values
  }

can someone point me to an example, or good documentation?




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




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

Re: [sqlite] any data access and retrieval engine?

[John Stanton]

Sarah, email me off forum at [EMAIL PROTECTED]

BTW, all my programs are ANSI standard plain vanilla C.

Regards,

John S

Sarah wrote:
> Hi, John Stanton
> 
> I really really appreciate your warm help.
> That's great if you can send me the codes of B tree and B+ tree. 
> Many thanks in advance.
> 
> My requirements for data access are as follows:
> -all the data are stored in non-volatile memory instead of volatile memory
> -the footprint of the DARE should be better less than 100KB
> -when executing, the memory occupation should be better less than 20KB
> -no need for relational access, just key-value retrieval is ok
> -all the create, insert, update work can be done outside, however, pretty 
> fast retrieval is needed
> 
> If there is some open-source DARE(as excellent as SQLite) suitable for my 
> platform, that will be great.
> Orelse, I would try to write a simple one.
> 
> Sarah
> 
> 
> - Original Message - 
> From: "John Stanton" <[EMAIL PROTECTED]>
> To: <sqlite-users@sqlite.org>
> Sent: Monday, October 23, 2006 5:54 AM
> Subject: Re: [sqlite] any data access and retrieval engine?
> 
> 
> 
>>Clay Dowling wrote:
>>
>>>Sarah wrote:
>>>
>>>
>>>>Hi,all
>>>>
>>>>First of all, I want to thank all the guys on this mailing list for their 
>>>>warm help.
>>>>
>>>>After 1 more month of work, I finally make SQLite work on my embedded 
>>>>environment. SQLite is really great! Many thanks,Drh.
>>>>
>>>>But, due to the extremely heavy hardware constraints, I have to give up 
>>>>SQLite finally. 
>>>>
>>>>So I'm trying to find a much simpler data access and retrieval engine. 
>>>>
>>>>Could anyone give me some help on this issue?(some guidance on how to make 
>>>>a DARE or is there any open-source one available?) 
>>>>
>>>>thanks in advance. 
>>>
>>>
>>>The Berkeley DB engine and it's related engines might be suitable for
>>>your situation.  They don't give relational access, but they do give
>>>fast key=>value retreival and that might be suitable.  The SleepyCat DB
>>>engine from SleepyCat Software is probably the best, but for a
>>>commercial application the licensing fees mean that you have to be well
>>>funded and expect a good return on the product.
>>>
>>>Clay Dowling
>>
>>Berkely DB is still quite bloated.  What do you require for data access?
>>For an embedded system you might find something which matches your
>>needs very well and has a tiny footprint.
>>
>>I can give you some B* Tree code which is suitable for a high
>>performance simple and lightweight embedded application or some AVL tree
>>code which would suit a simpler smaller scale memory resident embedded
>>data access application.  You would have to adapt it to your
>>application, but could expect to get your database access in 20K or less
>>of executable image.  Of course you have no SQL.
>>
>>-
>>To unsubscribe, send email to [EMAIL PROTECTED]
>>-
> 
>>


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

Re: [sqlite] SQLite Performance Issues

[John Stanton]

I built and use an application server which embeds Sqlite and processes 
web traffic.  It is multi-threaded and can handle very many connections. 
 It is very fast because it uses no IPC channels or process creation. 
It caches threads and reuses them rather than creating and killing them. 
 It can use sendfile/TransmitFile access to the internet with maximum 
efficiency.  It sounds to be similar to what you propose.


The simplicity of Sqlite does not come free.  You have to be aware of
the way it single streams access.  Only one user can write to the 
database at a time, but many users can read.  You have to design your 
application and synchronization skilfully to handle that constraint. 
You may consider breaking up your data into seperate databases.


If your design handles the synchronization well your emedded Sqlite 
database will handily outperform MySql or PostgreSQL.  If you don't want 
to be involved at that design level, use PostgreSQl.


James Mills wrote:

Hi Folks,

I'm wanting to use SQLite in an embedded web application
that will serve as a proxy and possible serve up many
connections at once. I'm talking here of high-traffic
through this web app.

Question is, how will SQLite perform under these kinds
of conditions ? I've been speaking to a few of the Trac
developers, and they inform me that SQLite uses a global
writer meaning that only a single thread can write at
any one point in time. This would explain why trac-hacks.org
is so slow at times to load up.

Thoughts/Comments ?

(btw) I really don't want to be using a server-client
rdbms such as MySQL or Postgresql because of the small
overheads in having a server.

cheers
James




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

Re: [sqlite] any data access and retrieval engine?

[John Stanton]

Clay Dowling wrote:
> Sarah wrote:
> 
>>Hi,all
>>
>>First of all, I want to thank all the guys on this mailing list for their 
>>warm help.
>>
>>After 1 more month of work, I finally make SQLite work on my embedded 
>>environment. SQLite is really great! Many thanks,Drh.
>>
>>But, due to the extremely heavy hardware constraints, I have to give up 
>>SQLite finally. 
>>
>>So I'm trying to find a much simpler data access and retrieval engine. 
>>
>>Could anyone give me some help on this issue?(some guidance on how to make a 
>>DARE or is there any open-source one available?) 
>>
>>thanks in advance. 
> 
> 
> The Berkeley DB engine and it's related engines might be suitable for
> your situation.  They don't give relational access, but they do give
> fast key=>value retreival and that might be suitable.  The SleepyCat DB
> engine from SleepyCat Software is probably the best, but for a
> commercial application the licensing fees mean that you have to be well
> funded and expect a good return on the product.
> 
> Clay Dowling
Berkely DB is still quite bloated.  What do you require for data access?
 For an embedded system you might find something which matches your
needs very well and has a tiny footprint.

I can give you some B* Tree code which is suitable for a high
performance simple and lightweight embedded application or some AVL tree
code which would suit a simpler smaller scale memory resident embedded
data access application.  You would have to adapt it to your
application, but could expect to get your database access in 20K or less
of executable image.  Of course you have no SQL.

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

Re: [sqlite] sqlite-locked error

[John Stanton]

Does a virus checker have the file open?

Dave Dyer wrote:
I've got a database that is permanantly locked, presumably due to 
some error.  Any attempt to modify it results in a "sqlite_locked"

error.  This error persists even if all users close the database.

sqlite> insert into user (name) values ('fred');
SQL error: database is locked

-- How do I clear this error?  


-- I'm pretty sure I know how this error became embedded in the
database - I punted out of a sqlite transaction without calling
end_transaction using the C api.  It seems inelegant that a 
program error would leave the database unusable.



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




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

Re: [sqlite] problem with sqlite3 in lazarus

[John Stanton]

Sqlite does not use VARCHAR(n) or NUMERIC(s,p).  Read up about manifest 
typing in the documentation and just declare columns as underlying types 
such as TEXT.


You can declare the column as anything you like, but Sqlite makes its 
own decision as to what it will be.


Carlos Avogaro wrote:

Hi,
  I have sqLite version 3.3.6, and I'm using with lazarus in windows.
  With sqlite3.exe, I created this table
   
  create table product (cod varchar(6), ean varchar(13), 
  desc varchar(30), pvp numeric(10,2))
   
  from lazarus I did 4000 insert, like this:
   
  insert into product values ('0001','12312345','test test tes', 4500.34)
   
  then I check the result with sqlite3.exe and everything is ok!
   
  but when I open a recorsed SELECT * FROM PRODUCT from lazarus I received this messages, FIELD TYPE VARCHAR(6) NOT RECOGNIZED.
   
  then I created the table in this way:
   
  create table product (cod varchar, ean varchar, 
  desc varchar, pvp numeric(10,2))
   
  and when I open the recorsed SELECT * FROM PRODUCT from lazarus I received this messages, FIELD TYPE NUMERIC(10,2) NOT RECOGNIZED.
   
  Please help me, I need to finish my proyect.

  Thank you


-
Get your email and more, right on the  new Yahoo.com 



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

Re: [sqlite] Question - Multiple Users, Multiple files

[John Stanton]

I would judge that your proposed application is an ideal one for Sqlite. 
 It should work very well, and you should be able to support a large 
number of simultaneous users.


Jason Abayomi wrote:

Thanks guys for all the replies I appreciate it.

I'm leaning more and more towards using sqlite after your comments for 
these

reasons.

1. Each user will have his/her own database and so will likely only be
accessed at any one time by one user.  To reduce concurrency issues as well
as for better application design. It is unlikely that there will be cross
user access.

2.  Aside from the initial shema design, most operations from users will be
read and update operations, I don't forsee too many create table statements
very often.

3.  There is in my opinion a similar overhead with doing operations on
in-memory xml data

I considered using a good ol client server setup like mysql or postgre/sql
and didn't think it better to have instead a database with thousands of
tables, each created per user with some of the users having more than one
table by the way.

I'm always open for more suggestions.


On 10/13/06, John Stanton <[EMAIL PROTECTED]> wrote:



Jason Abayomi wrote:
> Sorry about the previous mail, I hit tab in a gmail and it 
automatically

> sent it.
>
> Anyway, back to my question
> I'm planning out a program right now - web based - that would require
each
> user of the application to be able to create their own independent data
> store with its own schema and modifications etc.  In a perfect world,
> sqlite
> would be great for this as I could just give each individual user their
own
> sqlite database and let them manipulate tables and schema as they see
fit
> before they start to store information into the db.
>
> My concern is scalability and the performance hit of having let's say
5000
> users potentially, accessing 5000 databases at the same time on the
server.
> Based on my readings around the web there are ways to optimize sql
queries
> (transactions, in memory dbs and all that) but even with that, would it
be
> feasible or better yet advisable?
>
> The main issue here is the ability of the users to create and modify
their
> own schemas which may leave my only other option to be xml
>
> Thanks.
>
> Jason
>
With Sqlite that is basically only 5,000 files.  not a giant load on
your server.


- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 









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

Re: [sqlite] Question - Multiple Users, Multiple files

[John Stanton]

Jason Abayomi wrote:

Sorry about the previous mail, I hit tab in a gmail and it automatically
sent it.

Anyway, back to my question
I'm planning out a program right now - web based - that would require each
user of the application to be able to create their own independent data
store with its own schema and modifications etc.  In a perfect world, 
sqlite

would be great for this as I could just give each individual user their own
sqlite database and let them manipulate tables and schema as they see fit
before they start to store information into the db.

My concern is scalability and the performance hit of having let's say 5000
users potentially, accessing 5000 databases at the same time on the server.
Based on my readings around the web there are ways to optimize sql queries
(transactions, in memory dbs and all that) but even with that, would it be
feasible or better yet advisable?

The main issue here is the ability of the users to create and modify their
own schemas which may leave my only other option to be xml

Thanks.

Jason

With Sqlite that is basically only 5,000 files.  not a giant load on 
your server.


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

Re: [sqlite] new sqlite-based webserver

[John Stanton]

Gunter, I finally managed to get to your server and look at what you 
have done.  It is interesting and ingenious to embed Javascript as an 
application server language and to integrate it with Sqlite.  Your son 
has something to get his teeth into.


I haven't looked closely enough at your code to see if you do this, but 
I defined callback functions which could be triggered by Sqlite 
successfully retrieving a row.  The result is the ability to dynamically 
create not only JS arrays but also HTML tables and even PostScript 
components.


I built a header parser with an efficient lexical analyser to drive a 
state machine that takes care of POST and GET and their variants.


My goal is a highly scalable, high throughput application server.  That 
is why I use a byte-code compiled metalanguage rather than interpretive. 
 I am currently looking at ways of making it optimally utilize the 
multiple processors on our P-Series server.


Günter Greschenz wrote:

Hi,

the reasons why i did this:
1) i wanted to have a simple webserver to get my son involved in 
programming an online game, and he knows already a little bit javascript
2) my hobby is programming in c++ and javascript, but at work i have to 
code in c#, so i just wanted to have some fun :-)


i think its no a new idea, ive seen some webservers using javascript as 
backend language, but everything i found was to complicated to 
install/use or to complicated to port to a new hw-platform (i want to 
run it on my linksys-nslu2 with ftpd to my topfield hd-vcr to program 
the recordings via web).

and i like sqlite very much and no webserver i found had this combination.
if you look at my code, you see that i just use
*) sqlite
*) the javascript-engine from mozilla (very advanced: js v1.7 including 
xml support...),
*) some glue code to access sqlite and a simple http server from js 
(only one file: gas.cpp)


the trick between js and sqlite is like yours: every sql statement 
executed returns a 2-dim array...
one thing is maybe iteresting: the class "DBItem". its a kind of (very 
simple) or-mapper.
it maps the properties of a javascript-object to the columns of a table. 
here is a code example:


   var db = new Database("user.sdb");
   db.exec("create table if not exists user (id integer primary key 
autoincrement, name varchar unique, pwd varchar, test varchar)");

   var user = new DBItem(db, "user");
   user._name = "Guenter";
   user._pwd = "FooBar1234567890";
   user._test = "blubb";
   user.flush();
   var id = user._id;
   var user = new DBItem(db, "user", id);
   print("name="+user._name+"\n");
   //db.exec("delete from user where id="+id);

if you are still curious (or anyone else ?), i can send you the actual 
source by mail. :-)


btw: i had a look into your http-source: looks nice, but its maybe to 
complicated to implement my features like http-multipart-posts.


cu, gg





John Stanton schrieb:

The multi-threaded application/www server I described requires no 
threading involvement from the application programmer.  That 
programmer uses SQL, HTML, Javascript and the application language we 
call MUV. Think about it, when you use Apache you don't have to be 
aware of its internal threading.


The only significant synchronisation element is to do with multiple 
users of Sqlite, and that is handled transparently to the application 
programmer.


What I was curious about was your statement that you used Javascript 
as a backend.  Do you have a novel idea there?  For example my 
application language will, inter alia, populate Javascript arrays from 
an SQL statement.  Do you have any such features or something more 
ingenious? (I still cannot get access to your server).


My application server runs on Win98, Win2000/XP, Linux, AIX 4.2 and 
5.3.  The executive, fileserving, compression and CGI components are 
realised in less than 20K of code.  HTML V2 is implemented.


There are some old fragments of this program at 
http://www.viacognis.com/muvm.  If you are interested I can dig out 
the current code and let you have it.



Günter Greschenz wrote:


Hi,

normally i like multithreading, but in this case i like the simple 
approach (at least for rendering the pages), because as application 
programmer (the guy who will develop the apps in javascript) its much 
easier if you dont have to think about locks, racing conditions and 
other pitfalls. and the rendering itself is (depending on what you 
do, of course) fast enough for me (simple pages need <10ms !). I want 
to multithread the upload to the client after all work is done.
in my current implementation the complete server is blocked until the 
download is finished. in my logfiles i have entries with 25 minutes 
(see "http://greschenz.dyndns.org/logsByTime.html;) !!! thats really 
unacceptable !
could you mail me some samples howto multithread portable (linux & 
win32) ?

Re: [sqlite] new sqlite-based webserver

[John Stanton]

The multi-threaded application/www server I described requires no 
threading involvement from the application programmer.  That programmer 
uses SQL, HTML, Javascript and the application language we call MUV. 
Think about it, when you use Apache you don't have to be aware of its 
internal threading.


The only significant synchronisation element is to do with multiple 
users of Sqlite, and that is handled transparently to the application 
programmer.


What I was curious about was your statement that you used Javascript as 
a backend.  Do you have a novel idea there?  For example my application 
language will, inter alia, populate Javascript arrays from an SQL 
statement.  Do you have any such features or something more ingenious? 
(I still cannot get access to your server).


My application server runs on Win98, Win2000/XP, Linux, AIX 4.2 and 5.3. 
 The executive, fileserving, compression and CGI components are 
realised in less than 20K of code.  HTML V2 is implemented.


There are some old fragments of this program at 
http://www.viacognis.com/muvm.  If you are interested I can dig out the 
current code and let you have it.



Günter Greschenz wrote:

Hi,

normally i like multithreading, but in this case i like the simple 
approach (at least for rendering the pages), because as application 
programmer (the guy who will develop the apps in javascript) its much 
easier if you dont have to think about locks, racing conditions and 
other pitfalls. and the rendering itself is (depending on what you do, 
of course) fast enough for me (simple pages need <10ms !). I want to 
multithread the upload to the client after all work is done.
in my current implementation the complete server is blocked until the 
download is finished. in my logfiles i have entries with 25 minutes (see 
"http://greschenz.dyndns.org/logsByTime.html;) !!! thats really 
unacceptable !

could you mail me some samples howto multithread portable (linux & win32) ?

cu, gg


John Stanton schrieb:


Gunter,

I recently wrote a multi-threaded portable web server in simpl ANSI C. 
It uses Win32 threads or pthreads.  It caches threads and re-uses them 
on a most recently used basis.  Efficiency is obtained by using 
TransmitFile (Win32) or sendfile (Unix).


The logic is simple for an efficient, multi-threaded www server.

An added bonus of mutli-threading is that it takes advantage of the 
ability of a browser to open multiple concurrent connections and 
persistent connections.


My WWW server is set up as an application server, with embedded Sqlite 
and an embedded byte-coded metalanguage used to define DHTML pages.  
An associated compiler produces the byte code.  By using compiled byte 
code the overhead of interpreting a script-type language is avoided.


Günter Greschenz wrote:


Hi,

yes i know, the problem is, its single-threaded (because of 
protability), so if anyone starts a download with a slow connection, 
the server is blocked for other connections.. im still thinking about 
this problem... single-threading whle rendering th pages and 
multithreading for sending them to the clients, or maybe async socket 
writes (is this possible in linux ?)
but i've seen (in the logs :-) a lot of people are interested. its 
funny to sit at the console at home and see the log messages when 
anyone comes by...
at the moment its just an alpha-version... but if i have more time 
(or maybe anyone helps me) it will improve !
the server itself is an i386-linux at ~ 300mhz, so dont expect too 
much ...



Fred Williams schrieb:


What'ch got it running on, a 286?  Tried three different times and got
tired waiting all three times :-(

Not going to stir much interest with response times like that!


 


-Original Message-
From: Günter Greschenz [mailto:[EMAIL PROTECTED]
Sent: Monday, October 09, 2006 12:04 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] new sqlite-based webserver


hi,

i dont know if anyone is interested in my new open source project...
i implemented a little webserver with javascript as backend-language
(1.7 from mozilla 2.0.rc1) and sqlite as datastorage (3.3.7)
the server is serving himself on "http://greschenz.dyndns.org; :-)
the source can be downloaded at
"http://greschenz.dyndns.org/download/gas/;
its compilable with gcc(linux) and vs2005(win32)

please send me any comments / ideas...

cu, gg



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







- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 




  







- 


To unsubscribe, send ema

Re: [sqlite] new sqlite-based webserver

[John Stanton]

Martin Jenkins wrote:

John Stanton wrote:


Martin Jenkins wrote:


Seems ok now. Quite fast even.


It is still dead.  Address 62.194.135.186 doesn't respond.
Traceroute fails.



Odd. It's still working here (UK). DNSStuff reports a different address 
though (62.104.138.84) and a traceroute from there is fine (13 hops). I 
get the same address from my local box but traceroute times out after 
linx2.mcbone.net.


Martin

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



No joy from here (US Midwest).  Traceroute times out on mcbone.net. 
Ping fails.


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

Re: [sqlite] new sqlite-based webserver

[John Stanton]

Gunter,

I recently wrote a multi-threaded portable web server in simpl ANSI C. 
It uses Win32 threads or pthreads.  It caches threads and re-uses them 
on a most recently used basis.  Efficiency is obtained by using 
TransmitFile (Win32) or sendfile (Unix).


The logic is simple for an efficient, multi-threaded www server.

An added bonus of mutli-threading is that it takes advantage of the 
ability of a browser to open multiple concurrent connections and 
persistent connections.


My WWW server is set up as an application server, with embedded Sqlite 
and an embedded byte-coded metalanguage used to define DHTML pages.  An 
associated compiler produces the byte code.  By using compiled byte code 
the overhead of interpreting a script-type language is avoided.


Günter Greschenz wrote:

Hi,

yes i know, the problem is, its single-threaded (because of 
protability), so if anyone starts a download with a slow connection, the 
server is blocked for other connections.. im still thinking about this 
problem... single-threading whle rendering th pages and multithreading 
for sending them to the clients, or maybe async socket writes (is this 
possible in linux ?)
but i've seen (in the logs :-) a lot of people are interested. its funny 
to sit at the console at home and see the log messages when anyone comes 
by...
at the moment its just an alpha-version... but if i have more time (or 
maybe anyone helps me) it will improve !

the server itself is an i386-linux at ~ 300mhz, so dont expect too much ...


Fred Williams schrieb:


What'ch got it running on, a 286?  Tried three different times and got
tired waiting all three times :-(

Not going to stir much interest with response times like that!


 


-Original Message-
From: Günter Greschenz [mailto:[EMAIL PROTECTED]
Sent: Monday, October 09, 2006 12:04 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] new sqlite-based webserver


hi,

i dont know if anyone is interested in my new open source project...
i implemented a little webserver with javascript as backend-language
(1.7 from mozilla 2.0.rc1) and sqlite as datastorage (3.3.7)
the server is serving himself on "http://greschenz.dyndns.org; :-)
the source can be downloaded at
"http://greschenz.dyndns.org/download/gas/;
its compilable with gcc(linux) and vs2005(win32)

please send me any comments / ideas...

cu, gg



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






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 




  






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

Re: [sqlite] new sqlite-based webserver

[John Stanton]

Martin Jenkins wrote:

Fred Williams wrote:


What'ch got it running on, a 286?  Tried three different times and
got tired waiting all three times :-(



Seems ok now. Quite fast even.

mj

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



It is still dead.  Address 62.194.135.186 doesn't respond.  Traceroute 
fails.


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

Re: [sqlite] Re: SQLite Order BY

[John Stanton]

A. Pagaltzis wrote:

* John Stanton <[EMAIL PROTECTED]> [2006-10-09 19:35]:


Sorting data is time consuming, a physical law is involved. At
best it is an nlog(n) process.



Only when you sort by comparing elements with each other. Bucket
sort runs in O(n), f.ex. And quantum sort is O(1). ;-) Algorithms
that run faster than O(n log n) are very rarely practical,
however.

Regards,
Congratulations, you have your next project, modifying Sqlite to use a 
sort performing better than nlog(n).  We look forward to it and wish you 
well.


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

Re: [sqlite] new sqlite-based webserver

[John Stanton]

Gunter,
I cannot access your web site.  The address is resolved, then the server 
hangs.  It would be interesting to look at your www server.


Günter Greschenz wrote:

hi,

i dont know if anyone is interested in my new open source project...
i implemented a little webserver with javascript as backend-language
(1.7 from mozilla 2.0.rc1) and sqlite as datastorage (3.3.7)
the server is serving himself on "http://greschenz.dyndns.org; :-)
the source can be downloaded at "http://greschenz.dyndns.org/download/gas/;
its compilable with gcc(linux) and vs2005(win32)

please send me any comments / ideas...

cu, gg



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] SQLite Order BY

[John Stanton]

Sorting data is time consuming, a physical law is involved.  At best it 
is an nlog(n) process.  Sorting a result set or sorting the keys to 
build an index invokes the nlog(n) timing.


If you cannot tolerate time spent sorting, you need to redesign your 
database to avoid sorts, or to maintain reference data in sorted 
sequence via an index and use that sequence repeatedly.


This is all IT101, nothing to do with Sqlite.

Manzoor Ilahi wrote:

Thanks shiming
we have tried this method as u suggested , but creating an index on a 5 
million records

take a long time, nearly 4 seconds.

And in our case the Database contain 5 Million to 30 or 50 million 
records. so if I try to create additional indices on these tables it 
takes too much time.


any other suggestions if u have please.
Best regards,
Manzoor Ilahi

//-- 



You can try creating two additional indexes, one on hvh5m.Column4 and 
the other on hvh5m.Column8. They should speed things up.


Best regards,
He Shiming

From: "He Shiming" <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: 
Subject: Re: [sqlite] SQLite Order BY Date: Mon, 9 Oct 2006 11:59:46 
+0800


//-- 




Dar All,

Can someone guide me, why the ORDER BY in SQLite is too slow. we 
observed that if the   change in data values is small then ORDER BY 
works better . but if the change is big then the performance is very 
slow. Even sometime I can not finish a query execution, and wait and 
may be sometime need to kill the process.


also another point that i observed is ,  when ..order by fieldName , 
if fieldName  is varchar,the excutiion is faster ,and if fieldName is 
float,the execution can't be finished!



Any Idea Please

thanks

Manzoor Ilahi

Queries that we tested and the Schema is given as under.


//-- 


select * from hvh5m,itm where hvh5m.Column4=itm.Icol1 order by Column8

this SQl can't be finished

//-- 



select * from hvh5m,itm where hvh5m.column4=itm.Icol1 order by 
itm.Icol1;


this sql can be finished!

//-- 



hvh5m: column1 varchar(8 );
column2 varchar(4);
Column3 varchar(2);
Column4 varchar(4)£»
column5 varchar(7);
column6 varchar(8 );
column7 varchar(1);
Column8float(10£¬2);

itm£º Icol1 varchar(4);
itemname varchar(20);
Icol3 varchar(1);
Icol4 varchar(1);
Icol5 varchar(1);
Icol6 varchar(1);
Icol7 varchar(1);
Icol8 varchar(1);
Icol 9 varchar(1);
Icol 10 varchar(1);
Icol 11 varchar(1);
Icol 12 varchar(1);
Icol 13 varchar(1);
Icol 14 varchar(1);
Icol 15 varchar(1);
Icol 16 varchar(1);

There is one index on itm.Icol1.



You can try creating two additional indexes, one on hvh5m.Column4 and 
the other on hvh5m.Column8. They should speed things up.


Best regards,
He Shiming


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





_
Be seen and heard with Windows Live Messenger and Microsoft LifeCams 
http://clk.atdmt.com/MSN/go/msnnkwme002001msn/direct/01/?href=http://www.microsoft.com/hardware/digitalcommunication/default.mspx?locale=en-us=hmtagline 




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Can anyone recommend some ISAM db to me?

[John Stanton]

All the involvement of SQL makes no sense in a simple embedded
application which is performing associative lookups or ISAM type access.
 Look at perhaps using DBM or its derivative or something like C-Tree or
another B-Tree method.

In mimimal applications we have used AVL trees with success.  The code
footprint is tiny, the performance lightning fast and the application
very robust.  I can give you some code if you are interested.

Simple is better.

Clay Dowling wrote:
> Sarah wrote:
> 
>>Hi, all
>>After trying SQLite on my embedded platform, I feel that it's a little too 
>>complicated and time-consuming to my platform, especially the parsing.
>>So, could someone recommend several ISAM ones to me?(I'm a newbie of 
>>database*^_^*)
> 
> 
> I can recommend Berkeley DB from Sleepycat Software.  It's very high
> quality, however it's not cheap to license for a commercially sold
> application, and it doesn't pretend to have any relational features.
> 
> If you're just looking for a way to read key-value pairs from a
> database, I have a nice little library I could share with you which sits
> on top of an SQLite database.
> 
> Clay Dowling


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

Re: [sqlite] Optimize performance - reading from multiple databas e files, processing and writing to separate results database file?

[John Stanton]

Make several large DBs as your input in that case and do -

  select from DB1
  select  from DB2
  ...
  select from DBn
  insert into output

Serena Lien wrote:

No, I am not copying anything to an in-memory database, this was just an
alternative option I was suggesting, so please ignore the whole in-memory
database thing from now on..

I have more databases to attach than the maximum number allowed, so no, I
cannot attach my input databases to make one large DB.

I believe my only option is to attach databases one at a time, and use
multiple transactions around  the attach/detach commands, since I cannot
attach databases within a transaction.

Serena.




>
Why7 copy to an im memory database?  Why not just create a regular
database as your output.  The is no reason you cannot have more than one
DB connection current in any process or thread.

How about -
   Attach your input databases to make one large DB
   select your output data
   insert output data into the output DB







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

Re: [sqlite] Optimize performance - reading from multiple databas e files, processing and writing to separate results database file?

[John Stanton]

Serena Lien wrote:

Thanks for the response.

-- 1 Gbyte is not at all too big for Sqlite to handle in one file.


True, but I think too big for an in-memory database - I was considering
copying all my source data from separate databases into one table in an
in-memory database so that I could access it easier later. I don't really
know what the size I need to copy would be, it would depend on some sort of
search to work out what input files to use.

Eg Say each input database corresponds to some experimental data associated
with one subject. I may have thousands of these. In the application I am
writing, I may want to do a query to find all subjects with some sort of
common condition - so this may return a lot of database files, and/or 
have a

lot of data (size on disk) associated with it.


-- Some portion (large??) of the performance advantages of transactions 
will




be lost in opening/closing files, even if transactions were allowed 
across

multiple files.



The transactions are hopefully for optimizing the insertion of data into my
results database file, not for reading data from my multiple source 
files, I

don't need to write to the sources.


For these reasons, might you consider using a single database rather than


multiple files if you're up against performance bottlenecks?

Perhaps - I thought if I needed to work with lots and lots of data in the


future, rather than having everything in one database, it would be 
easier to

manage in separate files, because I wouldn't always need to access all the
experimental data all the time.

Serena.

Why7 copy to an im memory database?  Why not just create a regular 
database as your output.  The is no reason you cannot have more than one 
DB connection current in any process or thread.


How about -
  Attach your input databases to make one large DB
  select your output data
  insert output data into the output DB

Of course you want to use transactions, and to limit the transaction 
size to a reasonable number.


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

Re: AW: [sqlite] Memory mapped db

[John Stanton]

Trevor Talbot wrote:

Michael is referring to a direct map from disk pages to memory pages
(OS notion of pages, not sqlite's), using something like mmap() on
unix or MapViewOfFile() on Windows.  This way memory is directly
backed by the file it refers to, instead of copying the data to
entirely new pages (possibly backed by swap).  It removes one level of
(redundant) cache.

The complications tend to come in when considering I/O control and
locking.  OS pages don't necessarily map to sqlite pages, so there can
be some "odd" boundaries there.  This would be most noticable when
trying to flush data to disk.  (The typical mmap abstraction requires
you force dirty OS pages to disk.  Interactions between file maps
(often copy-on-write) and underlying OS caches can be weird.).

You're also bounded by VM space when trying to map large files.  Most
mapping abstractions use "windows" intended to map several sequential
OS pages, and since sqlite randomly accesses pages, it would probably
be too much overhead when trying to handle files larger than the VM
space you're willing to sacrifice to the map.

In the general case I don't see it paying off, but in some specific
cases it could be a win.  I'd be interested to see experiments.

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



A well reasoned explanation.  We use mmap'ing with great success, but 
not for very large files or databases, for the reasons described above.


You definitely get a performance lift with mmap'd I/O largely because a 
level of buffer shadowing is removed.


On a B-Tree index access I measured a speed improvement of about 40% by 
changing from reads and local cacheing to mmap'd access.


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

Re: [sqlite] Memory mapped db

[John Stanton]

Michael Wohlwend wrote:

Has anyone tested an sqlite which memory-maps the db-file into ram? Is this
an old  (maybe bad idea :-) ? I've looked over the source and it seems that
read and write operations are used through a singled interface, so it maybe
possible to implement it without too much trouble...

Any comments are welcome :-)

 Michael

It is not a bad idea, but would impose some limitations.  In general 
memory mapping removes one layer of buffer shadowing and is beneficial.


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

Re: [sqlite] Re: The term "flat-file" as applied to sqlite

[John Stanton]

A. Pagaltzis wrote:

* Griggs, Donald <[EMAIL PROTECTED]> [2006-09-25 22:10]:


2) If instead, I'm unaware of another popular use of the term
"flat file" -- then I'd be glad to learn this.



I think there's another explanation for how this term came about.
Dr. Hipp has asserted many timed that SQLite should be thought of
as a replacement not for Oracle, but for `fopen()`. That casts
the term "flatfile database" as a somewhat misleading way to say
that SQLite is a database that you can use just like you would a
flatfile.

Regards,


The term "flat file" came to be used many years ago to distinguish 
between a file of sequential records (a list) and a file organized for 
indexed access, like an ISAM file (a tree).  The CODASYL DBMS structure 
with its linked records was also regarded as not "flat".


Sqlite is definitely not a "flat file".


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

Re: [sqlite] Please help me locking/transaction? logic !

[John Stanton]

If you do not rely on Posix file locks at all and use a mutex then all 
your problems are gone.  Sqlite users are always getting their knickers 
in a knot using file locking.


Martin Alfredsson wrote:

Sorry, no.

The problem is that BEGIN TRANSACTION or _prepare/_step does
NOT set a SHARED lock when the SELECT statement is run.

I think this is a bug (since the doc says it should) so I have
added a ticket. There is a workaround though but since SQLite
does not support nested transactions it might be hard to use.

/Martin


 >From: John Stanton <[EMAIL PROTECTED]>
 >Subject: Re: Please help me locking/transaction? logic !
 >Newsgroups: gmane.comp.db.sqlite.general
 >Date: 2006-09-25 20:32:48 GMT (18 minutes ago)

 >Only one thread or process may be writing, but two of them have a lock.

 >What I do to make writing efficient and the logic simple is to use a
 >mutex to synchronize the writes.

 >Martin Alfredsson wrote:
 >> Hi !
 >>
 >> Only one process IS writing at once. The problem is that if
 >> the writing is able to happen after _step and before _finalize
 >> it locks the database,
 >>
 >> What my code does:
 >> sqlite3_exec(... "BEGIN TRANSACTION" ...)
 >> sqlite3_prepare(... "SELECT * FROM A_TABLE" ...)
 >> sqlite3_step(...)
 >> <= Another process does an UPDATE
 >> sqlite3_finalize(...)
 >> sqlite3_exec(... "COMMIT TRANSACTION" ...)
 >>



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] Please help me locking/transaction? logic !

[John Stanton]

Only one thread or process may be writing, but two of them have a lock.

What I do to make writing efficient and the logic simple is to use a 
mutex to synchronize the writes.


Martin Alfredsson wrote:

Hi !

Only one process IS writing at once. The problem is that if
the writing is able to happen after _step and before _finalize
it locks the database,

What my code does:
sqlite3_exec(... "BEGIN TRANSACTION" ...)
sqlite3_prepare(... "SELECT * FROM A_TABLE" ...)
sqlite3_step(...)
<= Another process does an UPDATE
sqlite3_finalize(...)
sqlite3_exec(... "COMMIT TRANSACTION" ...)

Reading the documentation make me think this should not happen:

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

SHARED The database may be read but not written. Any number of 
processes can hold SHARED locks at the same time, hence there can be 
many simultaneous readers. But no other thread or process is allowed to 
write to the database file while one or more SHARED locks are active.


The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is 
optional) is used to take SQLite out of autocommit mode. Note that the 
BEGIN command does not acquire any locks on the database. After a BEGIN 
command, a SHARED lock will be acquired when the first SELECT statement 
is executed. A RESERVED lock will be acquired when the first INSERT, 
UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired 
until either the memory cache fills up and must be spilled to disk or 
until the transaction commits. In this way, the system delays blocking 
read access to the file file until the last possible moment.



 >Only one user can write to an Sqlite database at any time.  The logic >of
 >your application has to enforce that "serialization" or "single
 >streaming" in some way.


 >From: John Stanton <[EMAIL PROTECTED]>
 >Subject: Re: Please help me locking/transaction? logic !
 >Newsgroups: gmane.comp.db.sqlite.general
 >Date: 2006-09-25 19:52:57 GMT (14 minutes ago)

 >Martin Alfredsson wrote:
 >> Is this correct ?
 >>
 >> My program calls:
 >> sqlite3_prepare(...)
 >> sqlite3_step(...)
 >>
 >> Here another process writes to the database sqlite3_exec(...)
 >>
 >> sqlite3_finalize(...)
 >>
 >> Now my program will fail with SQLITE_BUSY (5) when
 >> accessing the database.
 >>
 >> If I dont terminate the other process restaring my program
 >> will not help since it will get SQLITE_BUSY (5) all the time.
 >>
 >> Is there a way to avoid this,
 >> Can I stop another process from being able to write to the
 >> db while my program is between a _step/_finalize ?
 >> (Avoiding "long" SELECTs is hard due to lots of data).
 >>
 >> /Martin
 >> ma1999ATjmaDOTse
 >


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

Re: [sqlite] The term "flat-file" as applied to sqlite

[John Stanton]

Griggs, Donald wrote:
 
I've noticed that more than one contributor to this list has referred to

sqlite as a "flat file database."  I had always thought of a flat file as a
file composed of single table of records, with records defined either by
fixed-width allocations or by some sort of delimiter (e.g., comma-separated
files).

The article below seems to agree, though a more "broad" definition would
also include simple tables with no relationships.

http://en.wikipedia.org/wiki/Flat-file

By either definition, it would seem that an sqlite database file is far, far
from flat.  


My purpose is not to argue terms for their own sake, but instead:
   1) If I'm correct, then it could really confuse those new to sqlite into
thinking it very different from the relational, b-tree indexed database that
it is, or
   2) If instead, I'm unaware of another popular use of the term "flat file"
-- then I'd be glad to learn this.



[Opinions are my own, not those of my company]

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

"Single File" would be a more appropriate description, although that is 
not strictly correct since there are also journal files associated.


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

Re: [sqlite] Please help me locking/transaction? logic !

[John Stanton]

Martin Alfredsson wrote:

Is this correct ?

My program calls:
sqlite3_prepare(...)
sqlite3_step(...)

Here another process writes to the database sqlite3_exec(...)

sqlite3_finalize(...)

Now my program will fail with SQLITE_BUSY (5) when
accessing the database.

If I dont terminate the other process restaring my program
will not help since it will get SQLITE_BUSY (5) all the time.

Is there a way to avoid this,
Can I stop another process from being able to write to the
db while my program is between a _step/_finalize ?
(Avoiding "long" SELECTs is hard due to lots of data).

/Martin
ma1999ATjmaDOTse

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



Only one user can write to an Sqlite database at any time.  The logic of 
your application has to enforce that "serialization" or "single 
streaming" in some way.


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

Re: [sqlite] Using SQLite on networked drive

[John Stanton]

RohitPatel wrote:

I have read articles and understood that it is not safe to access SQLite
database file on network drive. (on all windows). 


But what about Windows 2000 (Server) ???  i.e. If SQLite (3.3.4 or 3.3.6)
database file resides on disk drive of Windows 2000 Server, then is it safe
to access that SQLite database via network ?

Any thoughts...Any one's experience ?

Thanks for any help. 
Rohit
It is safe to access an Sqlite file across a network.  Where you have to 
be careful is multiple users and locking across a network.  You should 
also be aware that performance will suffer if the file is accessed 
across a netwrok for simple bandwidth reasons.


If you are setting up a multi-user, multi workstation DBMS Sqlite may 
not be your best choice.  If you have an embedded DBMS then Sqlite is an 
excellent choice.


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

Re: [sqlite] how sqlite works?

[John Stanton]

I suggest that you get Donald Knuth's books (Fundamental Algorithms, 
Sorting and Seaching and Semi-Numerical Algorithms).  They have 
everything you need to know, and plenty more.  Knuth explains various 
type of B-Tree and plenty of merging algorithms.


Cesar David Rodas Maldonado wrote:

I meen, If SQLite has two index and very large Index (about 10.000.000 each
one) how do i merge it, I mean (index1 = index2 for every one and limit it
in thousand).

Understand?


On 9/15/06, Dennis Cote <[EMAIL PROTECTED]> wrote:



Cesar David Rodas Maldonado wrote:
> If there a document of how SQLite Virtual Machine Works ( papers )? I
> would
> like do something similar with B-Tree, B+ Tree but i dont know how to
> merge
> a select with tow Index? Understand my question??
>
> Please answer me
>
see the links VDBE Tutorial and VDBE Opcodes near he bottom of the
documentation page http://www.sqlite.org/docs.html

Dennis Cote


- 


To unsubscribe, send email to [EMAIL PROTECTED]

- 









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

Re: [sqlite] Re: SQLite vs MS Access

[John Stanton]

George Ionescu wrote:

Helo Allan,



This would be used in place of an MS Access database on a local/network


disk. I believe that SQLite should be quicker for both 


transactions and queries than Access. The one draw back that comes to mind


maybe portability (i.e. accessing data outside of the 


application), although the data would be portable across machines (PC,


Mac, Unix, etc) should we ever need it to be in the future.



Is there any webpage, or does anyone have any information comparing the


benefits of the two. I can only find comparisons between 


MySQL and PostgreSQL. This information would aid us greatly in deciding


whether to use SQLite or stick with Access.



Any help/advice will be gratefully received.



Trying to compare sqlite to MySQL or PostgreSQL is (a little) like comparing
apples and oranges: they have different targets. While sqlite was made for
embedding into an application, it is not a database server (like the other
two you mentioned). This doesn't necessarily mean it won't be faster in some
situations. It just means that it won't handle exceptionally well the
concurrent access to your database.

Putting the database on a network share and accessing it from there is
definitively something to be avoided. I've had two customers (for our
SQLiteDb product) which experienced 'database is locked' errors with only
two clients accessing a single db on a network. This happens because of the
faulty implementation of file locking on NFS. We do have a product (a
multithreaded TCP/IP server, which is currently in beta stage) for accessing
sqlite databases over TCP/IP. It won't boost performance to handle hundreds
of users but we have succesfully tested against 10 clients. And, if your
queries are written right, the server should be able to handle more
concurent users.

As far as performance, sqlite is definitively a winner here. If you're
interested in some benchmarks, check out
http://www.terrainformatica.com/sqlitedb/downloads.whtm. You don't have to
purchase SQLiteDb: just download the demo and you will find there a direct
comparison between JET (accessed via ADO) and SQLiteDb.

Accessing sqlite via ODBC is an option. However, the ADO dependecy you're
stuck with is something that should be avoided.

Regards,
George Ionescu

Adding to George's comments, we built a multi-threaded application 
server which embeds Sqlite and uses HTTP and that works much better in 
some cases than having a DBMS server like PostgreSQL connected to a 
regular WWW server.  The reason is that everything runs in one process 
and the threads are carefully synchronized.  It does not have IPC 
overheads and effectively hooks Sqlite to its clients using the 
efficient "sendfile/TransmitFile" interface, resulting in performance of 
 the highest level.


Be very wary if you want to use Sqlite as a shared file.  It is not a 
server so you have to take care of contentions in your application.


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




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

Re: [sqlite] SQLite vs MS Access

[John Stanton]

Sqlite is far more portable than Access.  There is also an ODBC 
interface available so that you can use the standard SQL/CLI API.


Just be wary of the fact that Sqlite does not have a server like 
PostgreSQL so you may run into some locking situations if you have your 
database distributed across machines.


Allan, Mark wrote:

Hi,

After successfully using SQLite on an embedded device, we are now thinking of 
using SQLite in a PC application.

This would be used in place of an MS Access database on a local/network disk. I 
believe that SQLite should be quicker for both transactions and queries than 
Access. The one draw back that comes to mind maybe portability (i.e. accessing 
data outside of the application), although the data would be portable across 
machines (PC, Mac, Unix, etc) should we ever need it to be in the future.

Is there any webpage, or does anyone have any information comparing the 
benefits of the two. I can only find comparisons between MySQL and PostgreSQL. 
This information would aid us greatly in deciding whether to use SQLite or 
stick with Access.

Any help/advice will be gratefully received.

Mark


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.




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

Re: [sqlite] Database on usbstick

[John Stanton]

eWobbuh wrote:


For a database that is write once, read mostly, you probably wouldn't 
have a lot of trouble, If I were doing it, I'd try to have most of the 
sorting and other manipulations done in  regular RAM or on the hard 
drive to minimize repeated changes on the stick itself. 




The idea is to read the database, make some changes and once in a while i
write the database to the usbstick. But how can i do the manipulations in
the RAM?


Leave it to Sqlite by doing it in a transaction.  Ideally you would want 
the journal files to be on your hard disk to get minimum read/write cycles.


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

Re: [sqlite] Tool to find out the memory usage of a program

[John Stanton]

Anish Enos Mathew wrote:

Thanks for the reply. Earlier i tried using top and ps. But i
dropped it when i came to know that top and ps doent give the accurate
memory  usage. So I tried with valgring, memcheck etc. But that too
didn't give me the desired result. The result of valgrind was more over
surprising. The memory comsumption of a program which inserts 100,000
records each of 15 bytes was more than one which inserts 100,000 each of
1k . First progam took 10,555,478 bytes where as the second one took
only 3,143,402 bytes.

(the data base used was Berkely DB). I would like to get a tool which
gives me an accurate result.

Anish.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

Sent: Wednesday, September 06, 2006 3:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Tool to find out the memory usage of a program


Sergio 'OKreZ' Agosti <[EMAIL PROTECTED]> wrote:


see options of ps (man ps)



I want to find out the total and the peak memory
usage of a program(in Linux)





or try with 'top'





For measuring memory usage (and finding memory leaks)
valgrind generally works much better than either ps or top.
--
D. Richard Hipp   <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of the company. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused, directly or 
indirectly, by any virus transmitted in this email.

www.aztecsoft.com

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

How did you establish that Valgrind was inaccurate?  Use whatever tool 
you used to invalidate the Valgrind result.


Alternatively you could put a patch in the code and track the malloc() 
usage.  That should satisfy a sceptic.


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

Re: [sqlite] Stripping a newline character

[John Stanton]

What language are you using?

Rob Richardson wrote:

Greetings!

 


I am using a serial communications package that includes a readline()
method to talk to a bar-code scanner.   Readline() gives me a string
whose last character is a newline ('\n').  I am trying to get rid of the
newline.  MyString.strip('\n') isn't working.  It has no effect.  How
should I do this?

 


Thanks very much!

 


Rob Richardson

RAD-CON INC.





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

Re: [sqlite] Database on usbstick

[John Stanton]

eWobbuh wrote:

Ok thanks. Ill figure it out then. Do you have any idea of it is possible for
a small database to be on a usb stick? Or is this is gonna be horrible slow?
It will not be stunningly fast, but if you use transactions wisely you 
will probably find that it can run well.


Sqlite is just a file.  If regular file access speed is acceptable on 
your USB drive, then so will Sqlite.  Sqlite uses cacheing and 
consequently can be somewhat independent of file system speed.


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

Re: [sqlite] Database on usbstick

[John Stanton]

eWobbuh wrote:

I still cant find it. Cant find anything about where to look for a database.
Only thing i cant find is the open function, but there you only give the
name of the database. plz help this n00b
You have it.  The sqlite3_open is how how access the database.  Use its 
pathname.


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

Re: [sqlite] Database on usbstick

[John Stanton]

eWobbuh wrote:

Havent try it yet, just wondering if its possible. Do you know how you tell
sqlite where to find a database? havent worked before with it.. only with
mysql
Just use the pathname of the single file which contains the entire 
Sqlite DB.


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

Re: [sqlite] pre-compiling prepared statements

[John Stanton]

chetana bhargav wrote:

It does make a difference with embedded deivces, where both speed and memory 
constraints matter a lot.
   
  -Chetan.


Jay Sprenkle <[EMAIL PROTECTED]> wrote:
  On 9/1/06, John Stanton wrote:



I believe that Dr Hipp has available a special version of Sqlite which
stores prepared statements. It has restrictions which may make it
unsuitable for general purpose applications, but could be the answer
this user is looking for.

For the benefit of the user, sqlite3_prepare compiles an Sqlite
statement but the compilation is only valid for the life of the
process and while the schema is not altered. It also requires that
the raw SQL be in memory at some stage.



What's the benefit there?
Isn't preparation time so minimal as to be insignificant?
If the few milliseconds your program will save are significant you probably
should be using something other than sql to store the data.

The benefit is that the Sqlite does not need to have the SQL compiler 
included and can have a much smaller footprint.



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




-
Stay in the know. Pulse on the new Yahoo.com.  Check it out. 



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

Re: [sqlite] pre-compiling prepared statements

[John Stanton]

Jay Sprenkle wrote:

On 9/1/06, chetana bhargav <[EMAIL PROTECTED]> wrote:


Hi,

  Is there any way to pre compile some of the prepared statements 
during compile time. I am having 4 tables of which two tables doesn't 
create any triggers/joins. I am basically trying to speed up the 
queries on these tables (as they are most frequently used). I am 
looking for ways so that I can keep them prepared always, not in 
memory though as that would be too much.



Certainly!
http://sqlite.org/capi3ref.html#sqlite3_prepare


I believe that Dr Hipp has available a special version of Sqlite which
stores prepared statements.  It has restrictions which may make it
unsuitable for general purpose applications, but could be the answer
this user is looking for.

For the benefit of the user, sqlite3_prepare compiles an Sqlite
statement but the compilation is only valid for the life of the
process and while the schema is not altered.  It also requires that
the raw SQL be in memory at some stage.





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

Re: [sqlite] count(gid) takes too long

[John Stanton]

Sripathi Raj wrote:

Hi,

I have a table with 500,000 records. The following is the schema of that
table:

CREATE TABLE ES_SRC_MEDIA_INFO (GID INTEGER PRIMARY KEY AUTOINCREMENT,
MEDIAPATH VARCHAR(256) NOT NULL UNIQUE,
BYTES_USED LONG,
BYTES_ON_DISK LONG,
MTIME LONG,
CTIME LONG,
TYPE VARCHAR(20),
CATEGORY VARCHAR(20),
TIMESTAMP LONG,
JOBID VARCHAR(30)

Main question: Using DBD-SQLite, select count(gid) from es_src_media_info
takes 130 secs. What gives?

I added an unique index on GID and tried it select count(gid) from
es_src_media_info where gid >= 1. It took around 90 seconds this time. Is
there any way to speed this up other than adding a trigger?

Small question: Does sqlite take the column types and length into
consideration while creating the table?

Thanks,

Raj

With or without an index Sqlite has to access every row to get a count. 
 If you want a rapidly accessed count keep a running total.


Sqlite ignores the text field sizes and makes them all of type TEXT.  In 
stores integers as 65 bit signed integers and other numbers as 64 bit 
floating point.  Read up on Sqlite "manifest typing" to learn more on 
how it stores data according to actual type rather than declared type.


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

Re: [sqlite] sqlite shared-cache mode usage

[John Stanton]

Jay Sprenkle wrote:

On 8/29/06, John Stanton <[EMAIL PROTECTED]> wrote:


Thankyou.  The Firefox people have merely removed their dependance upon
an unreliable resource, cross OS file locking.  A prudent design choice.

If they come up with an elegant distributed lock protocol it would be
worth propagating universally in the light of the success of Firefox and
its consequent broad distribution.  I see that it has already grabbed
perhaps 30% of browser users.




I agree as long as they don't replace it with something that is less 
robust.
IMHO something that's broken as simply as running two instances doesn't 
seem

robust or elegant. I hope I'm wrong about it though

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 

As long as I can remember poorly conceived and implemented file locking 
mechanisms have been a nosebleed in IT.  If you were ever involved in 
porting to multiple OS's you quickly got burned.  On that basis I hope 
the Firefox people develop something elegant, but wouldn't bet on it.


As you can tell from this forum, locking and synchronization is the area 
where there is least intuitive understanding among users and is the most 
consistent source of problems.  There must be a deep psychological reason.


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

Re: [sqlite] sqlite shared-cache mode usage

[John Stanton]

Thankyou.  The Firefox people have merely removed their dependance upon 
an unreliable resource, cross OS file locking.  A prudent design choice.


If they come up with an elegant distributed lock protocol it would be 
worth propagating universally in the light of the success of Firefox and 
its consequent broad distribution.  I see that it has already grabbed 
perhaps 30% of browser users.


[EMAIL PROTECTED] wrote:

"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:


If you run two instances of firefox you trash
your own database.



No, you didn't read what I said.  Firefox implements their
own locking mechanism, so two instances of firefox will
play nicely together.  The problem is when some other
application, that does not follow firefox's locking protocol,
tries to access the database while firefox is running.
--
D. Richard Hipp   <[EMAIL PROTECTED]>


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




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

Re: [sqlite] sqlite shared-cache mode usage

[John Stanton]

Jay Sprenkle wrote:

The problem is that they have to work on broken operating
systems.  I don't know of another way to patch around the
problem.  Do you?



nope.

If it breaks because of something you did, then YOU are the bum.
If it's broken because of the operating system THEY are the bums.

Having someone to blame still doesn't make it work.  They are have made 
the rational decision, based on building a product which runs everywhere.


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

Re: [sqlite] List of web hosting providers who provide/support SQLite?

[John Stanton]

If they offer PHP then they offer Sqlite, since it is embedded.

Clark Christensen wrote:

I've been thinking about that myself.  Then I started to wonder if the more 
common MySQL or PostgreSQL wouldn't be just as good (or better) for websites - 
particularly remote-hosted ones.

I see there are a lot of hosting companies out there that offer the traditional 
LAMP stuff, as well as CPanel or other admin tools to help you administer your 
site and your DBs.  I signed-up with Vizaweb for my condo association's site.  
They offer Linux, Apache, MySQL, PG, PHP, Perl, and a bunch of other stuff.  
Though they've expressed their openness to installing more stuff, I think if I 
were to do a DB-backed web app there, I'd probably just go with the My or PG db 
platform they offer.

 -Clark

- Original Message 
From: Louis P. Santillan <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, August 25, 2006 12:33:36 PM
Subject: [sqlite] List of web hosting providers who provide/support SQLite?

This is probably a good topic for a wiki page...

Does anybody have a list (actually a matrix would be
even better) of web hosting providers who have SQLite
available to their customers?  I decided to look for
some cheap web hosting service for a personal project
using LASP (Linux, Apache, SQLite, PHP) and realized a
list of providers would probably be ideal.  A matrix
of common features would be good also (Windows, Linux,
Apache, IIS, PHP, Perl, Python, Price, etc.)

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 


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





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




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

Re: [sqlite] SQLite with Index

[John Stanton]

Sqlite creates B-Tree indices.  It is a form of self balancing tree index.

Manzoor Ilahi Tamimy wrote:


Dear All,

I have question about Index.

When we create an index  on a table then how SQLite manage it. Whether SQLite 
uses Hashing or some other technique to handle index.


Can some one guide me where can I find the details working process of SQLIte 
when we create Index. 



Best regards, 


Manzoor Ilahi Tamimy



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




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

Re: [sqlite] implementing busy_handler callback

[John Stanton]

I would endorse that.  It makes the logic simpler and execution more 
efficient compared with the various polling strategies used to look at 
file locks.  It is particularly the case if you multi-use is due to 
threading in a single process.


Ritesh Kapoor wrote:

I think a good way of using sqlite in a multithreaded app is to have
your own mutex lock for the DB.

Everytime you need to execute a query just call the lock() function for
your mutex and then proceed with sqlite3_exec() calls.  Followed by a
unlock() call.

Your mutex wouldn't return if the another thread is executing queries. 
The only thing you need to do is to remember to lock and then unlock the

mutex.  You could again put all this in a function and call that
function whenever you need to execute a query.

However if you really insist on using the busy handlers then I guess
someone on this mailing list would help you.  However my experience with
these handlers was that they made a mess of the code and later on the
code was incomprehensible to someone looking at the code for the first
time.





Subject:
[sqlite] implementing busy_handler callback
From:
[EMAIL PROTECTED]
Date:
Wed, 23 Aug 2006 10:17:58 +0530
To:
sqlite-users@sqlite.org

To:
sqlite-users@sqlite.org
CC:
[EMAIL PROTECTED], 
[EMAIL PROTECTED], 
[EMAIL PROTECTED], [EMAIL PROTECTED]



Hi All,

I am trying to integrate SQLite in a multithreaded C++ application which 
runs on Linux.  I have gone through the SQLIte documentation, but it's not 
clear whether the sqlite3_exec() will retry the query when the busy 
handler callback is implemented and returns a non-zero value.


From the sqlite3_busy_handler(sqlite3*, int(*)(void*,int), void*) 
definition it says "This routine identifies a callback function that might 
be invoked whenever an attempt is made to open a database table that 
another thread or process has locked. If the busy callback is NULL, then 
SQLITE_BUSY is returned immediately upon encountering the lock. If the 
busy callback is not NULL, then the callback will be invoked with two 
arguments. The second argument is the number of prior calls to the busy 
callback for the same lock. If the busy callback returns 0, then no 
additional attempts are made to access the database and SQLITE_BUSY is 
returned. If the callback returns non-zero, then another attempt is made 
to open the database for reading and the cycle repeats.


Does it mean the sqlite3_exec() internally takes care of invoking the 
query multiple times until the callback returns 0?  Can someone provide 
sample implementation for busy_handler callback and how the query is 
retried?


thanks,
Vadivel




***  FSS- Confidential   ***

***  FSS- Confidential   ***
"DISCLAIMER: This message is proprietary to Flextronics Software Systems (FSS) and is intended solely for the use of 
the individual to whom it is addressed. It may contain privileged or confidential information and should not be 
circulated or used for any purpose other than for what it is intended. If you have received this message in error, 
please notify the originator immediately. If you are not the intended recipient, you are notified that you are strictly
prohibited from using, copying, altering, or disclosing the contents of this message. FSS accepts no responsibility for 
loss or damage arising from the use of the information transmitted by this email including damage from virus."






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



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

Re: [sqlite] Using Wrong Date Format

[John Stanton]

Sqlite has functions date() and strftime() which can do what you want.

Eoin Collins wrote:

Hi,


The database I'm currently using has a field Date Of Birth, and all enteries
in are in dd-mm- format.

I need them in -mm-dd format.

Can anyone help me with this, please?

Regards,
Eoin



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

Re: [sqlite] Re: fault tolerance

[John Stanton]

Your aim appears to be to have data recovery from hardware and operating 
system failures.  That is beyond the power of Sqlite and needs to be 
designed into your system or the hardware.  Logginjg or replication onto 
different media would provide the redundancy and diversity you seek.


Olaf Beckman Lapré wrote:

It depends on what gets corrupted. I found that alterring the data of the
tables itself didn't result in crashes but SQLite is very sensitive to
corrupted system tables and file headers. So the data is ussually
recoverable as long as the database can be brought into a readable state.
This means that the system headers should be repaired (ussually by looking
at other data in the file). If the system tables are damaged the application
could simply create a new database and start transferring data from the
tables into the new database.

There are many parent-child relationships in an e-mal client and a
relational database lends itself perfectly for this, apart from the
convenience of only having to manipulate a single file (both from a
developer and user perspective).

Olaf

- Original Message - 
From: "Dave Dyer" <[EMAIL PROTECTED]>

To: ; 
Sent: Thursday, August 17, 2006 7:45 PM
Subject: [sqlite] Re: fault tolerance




Most of us use email clients that store our mail as simple text files. An


SQLite database is no more likely to get corrupted than any other file.


I would stipulate that sqlite data is less likely to be corrupted due to


bugs in sqlite itself, or due to power failure or gross program crashes.
Good engineering is great, but it doesn't help the shuttle when a chunk of
foam hits the wing.


A sqlite database is more likely to be corrupted by bugs in the overall


application.  Using sqlite is a lot more complex than just appending to a
text file, and because sqlite's data structures share address space with the
main application, they are vulnerable to whatever bugs are present in the
main application.  I would also include program bugs which result in rogue
queries as corruption - one "update" with a poorly constructed "where"
clause can ruin your whole database.


Once a sqlite database is corrupted, you've got a mess with no clear path


to recovery, other than to revert to some previous backup of the entire
database.


On the other hand, in a file based application (such as a mail reader), In


any conceivable crash, I've got backups of individual files which I can
mix-n-match with whatever debris seems recoverable, and the individual mail
files, even if damaged, are intelligible.






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




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

Re: [sqlite] sqlite3_step() vs. get_tables() - CPU-Usage

[John Stanton]

A. Klitzing wrote:

Hello,

I need some information about the sqlite3_step() function.
How much CPU-usage does it need in comparison with sqlite3_get_tables()?

I have to implement functions like next(), prev(), last(), first(), 
relative(int) and absolute(int).


#1 Way - sqlite3_step()
I can only step forward with sqlite3_step() and use sqlite3_reset() to get 
first() and iterate through the result to the position for relative() and so on.

#2 Way - sqlite3_get_tables()
I can use get_tables() instead and so I can set my cursor without problems to 
every relative position but I loose nice features that I have with sqlite3_stmt.


So the question is - needs sqlite3_step() a few performance so it is "ok" that I will 
iterate "dirty" through the result everytime?

Regards,
André Klitzing

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

Sqlite3_step is how sqlite3_get_tables functions.  If you use step 
directly you will get more eficient processing, but if you intend to 
move all your selected rows into memory before processing them, then 
get_table is appropriate.  Just be wary when you use get_table that you 
could be consuming a large chunk of memory and choking your application 
unless you control it with a LIMIT or similar mechanism.


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

Re: [sqlite] starting with unicode

[John Stanton]

If you have no variables in your SQL (like SELECT * FROM ...) then you 
don't bind.


If you have "SELECT * FROM junk WHERE name=?" then you need to bind like 
this.  The bind is good until you do a reset.


char that_name = "Harry";
...
rc = sqlite3_bind_text(xek, 1, that_name, -1, SQLITE_STATIC);
if (rc == SQLITE_OK) return(FALSE);

/*Could return with an error, but have errcode OK.*/
err = sqlite3_errcode(db);
if (err != SQLITE_OK) {
  errormet("908", (char *)sqlite3_errmsg(db), (char *)fnm);  /*Fatal DB 
Error.*/

  return(TRUE);
}  /*if*/
return(FALSE);

Sqlite will see - SELECT * FROM junk WHERE name="Harry".

Mark Wyszomierski wrote:

I suppose this is correct:

strSql.Format(_T("SELECT * FROM test"));

char szSomething[500];
int nTest = sqlite3_bind_text(pStmt, 1, szSomething, 500, SQLITE_STATIC);
if (nTest != SQLITE_OK) {
   TRACE("sqlite3_bind_text fails!! [%i] [%s]\n", nTest, 
sqlite3_errmsg(db));

}

but can I use the string "SELECT * FROM test", if not how do I use
those question marks or AAA variable identifiers in the string itself
to achieve binding?

Thanks,
Mark

On 8/14/06, Mark Wyszomierski <[EMAIL PROTECTED]> wrote:


John, Cory, thank you very much. I got execute plain statements ok by
modifying my earlier posting a bit. I was able to create a table using
the prepare statement.

Previously I was using sqlite3_exec() to execute my statements and I
could pass it a callback function which I could use to fetch data for
SELECT statements. How do we do that now with the prepare() and step()
statements? Just some pseudocode like before would be fine. Here in my
test table I have two dummy records and the while loop correctly
iterates over both of them - but how to get the values in each row?

   // Table test looks like:

   // something | something_else
   // --
   //   hello   |   there
   //   bye|   guy

   strSql.Format(_T("SELECT * FROM test"));

   sqlite3_stmt *pStmt;
   const char *pszTailPointer;
   int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
, );
   if (nRetVal != SQLITE_OK) {
   TRACE("prepare fails!! [%i] [%s]\n", nRetVal, sqlite3_errmsg(db));
   return false;
   }

   nRetVal = sqlite3_step(pStmt);
   while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
   Sleep(100);
   // Try again.
   nRetVal = sqlite3_step(pStmt);

   // How do I get the information out of this returned record?

   // By the way, why would we want a reset() in here?
 //  sqlite3_reset(pStmt);
   }
   switch (nRetVal) {
   case SQLITE_DONE:
   TRACE("Done ok\n");
   break;
   case SQLITE_ERROR:
   TRACE("ERROR\n");
   break;
   case SQLITE_MISUSE:
   TRACE("MISUSE\n");
   break;
   default:
   break;
   }
   sqlite3_finalize(pStmt);


Thanks,
Mark



On 8/14/06, John Stanton <[EMAIL PROTECTED]> wrote:
> Mark Wyszomierski wrote:
> > Hi Cory,
> >
> > Alright I gave it a shot from the docs but I'm not handling the
> > prepare statement correctly. I'm trying the ASCI version first. The
> > prepare statement returns an error. Here is the code snippet I'm
> > trying:
> >
> >
> > strSql.Format("CREATE TABLE test (something TEXT, something_else 
TEXT,

> > primary key(something))");
> >
> >sqlite3_stmt *pStmt;
> >const char *pszTailPointer;
> >int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
> > , );
> >while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
> >Sleep(100);
> >// Try again.
> >nRetVal = sqlite3_step(pStmt);
> >TRACE("ret val was [%i]\n", nRetVal);
> >}
> >switch (nRetVal) {
> >case SQLITE_DONE:
> >TRACE("Done ok\n");
> >break;
> >case SQLITE_ERROR:
> >TRACE("ERROR\n");
> >break;
> >case SQLITE_MISUSE:
> >TRACE("MISUSE\n");
> >break;
> >default:
> >break;
> >}
> >sqlite3_finalize(pStmt);
> >return true;
> >
> > Any hints?
> >
> > Thanks,
> > Mark
> >
> >
> > On 8/13/06, Cory Nelson <[EMAIL PROTECTED]> wrote:
> >
> >> On 8/13/06, Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
> >> > Hi,
> >> >
> >> > I have been using sqlite on windows for a few months, it is 
great. I
> >> > need to switch over to unicode support now though, and I am 
confused

> >> > how to do this with sqlite.
> >

Re: [sqlite] starting with unicode

[John Stanton]

Mark Wyszomierski wrote:

John, Cory, thank you very much. I got execute plain statements ok by
modifying my earlier posting a bit. I was able to create a table using
the prepare statement.

Previously I was using sqlite3_exec() to execute my statements and I
could pass it a callback function which I could use to fetch data for
SELECT statements. How do we do that now with the prepare() and step()
statements? Just some pseudocode like before would be fine. Here in my
test table I have two dummy records and the while loop correctly
iterates over both of them - but how to get the values in each row?

   // Table test looks like:

   // something | something_else
   // --
   //   hello   |   there
   //   bye|   guy

   strSql.Format(_T("SELECT * FROM test"));

   sqlite3_stmt *pStmt;
   const char *pszTailPointer;
   int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
, );
   if (nRetVal != SQLITE_OK) {
   TRACE("prepare fails!! [%i] [%s]\n", nRetVal, sqlite3_errmsg(db));
   return false;
   }

   nRetVal = sqlite3_step(pStmt);
   while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
   Sleep(100);
   // Try again.
   nRetVal = sqlite3_step(pStmt);

   // How do I get the information out of this returned record?

   // By the way, why would we want a reset() in here?
 //  sqlite3_reset(pStmt);
   }
   switch (nRetVal) {
   case SQLITE_DONE:
   TRACE("Done ok\n");
   break;
   case SQLITE_ERROR:
   TRACE("ERROR\n");
   break;
   case SQLITE_MISUSE:
   TRACE("MISUSE\n");
   break;
   default:
   break;
   }
   sqlite3_finalize(pStmt);


Thanks,
Mark



On 8/14/06, John Stanton <[EMAIL PROTECTED]> wrote:


Mark Wyszomierski wrote:
> Hi Cory,
>
> Alright I gave it a shot from the docs but I'm not handling the
> prepare statement correctly. I'm trying the ASCI version first. The
> prepare statement returns an error. Here is the code snippet I'm
> trying:
>
>
> strSql.Format("CREATE TABLE test (something TEXT, something_else TEXT,
> primary key(something))");
>
>sqlite3_stmt *pStmt;
>const char *pszTailPointer;
>int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
> , );
>while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
>Sleep(100);
>// Try again.
>nRetVal = sqlite3_step(pStmt);
>TRACE("ret val was [%i]\n", nRetVal);
>}
>switch (nRetVal) {
>case SQLITE_DONE:
>TRACE("Done ok\n");
>break;
>case SQLITE_ERROR:
>TRACE("ERROR\n");
>break;
>case SQLITE_MISUSE:
>TRACE("MISUSE\n");
>break;
>default:
>break;
>}
>sqlite3_finalize(pStmt);
>return true;
>
> Any hints?
>
> Thanks,
> Mark
>
>
> On 8/13/06, Cory Nelson <[EMAIL PROTECTED]> wrote:
>
>> On 8/13/06, Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
>> > Hi,
>> >
>> > I have been using sqlite on windows for a few months, it is great. I
>> > need to switch over to unicode support now though, and I am confused
>> > how to do this with sqlite.
>> >
>> > 1) First, when I compiled the original sqlite project, I have the
>> > character set to MBCS. Should I switch this to Unicode and 
recompile?

>>
>> I think SQLite explicitly calls CreateFileW but I could be wrong.
>> Might as well compile as Unicode anyway.
>>
>> > 2) I have been using sqlite3_exec() to execute my sql statements, 
but

>> > I see that there is no sqlite3_exec16() equivalent to take a unicode
>> > string. I think I'm supposed to use sqlite3_prepare16() but I 
have no

>> > idea what the last two parameters of that function are?
>>
>> Check the docs, they explain how to use prepared statements.
>>
>> > 3) To escape my sql statements I was using sqlite3_mprintf() - is
>> > there a unicode equivalent?
>>
>> With prepared statements you put placeholders like "?" into your sql
>> and bind data to the placeholders - no escaping required.
>>
>> > Thanks for any information,
>> > Mark
>> >
>> >
>> 
- 


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


>>
>> >
>> >
>>
>>
>> --
>> Cory Nelson

Re: [sqlite] linking C program

[John Stanton]

Michael Alperovitch wrote:

Hi,

I am new SQLite user.

How I can link the simple C Program with Sqlite static or shared
library.

I tried to link to libsqlite3.a and it cannot find sqlite3_open(),
sqllite3_close(), sqllite3_exec()  functions.

I got the same result linking with libsqlite3.so shared library.

 


Thanks for any help.

 


Michael Alperovitch

Tricipher Inc.



What is your link command?

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

Re: [sqlite] starting with unicode

[John Stanton]

Mark Wyszomierski wrote:

Hi Cory,

Alright I gave it a shot from the docs but I'm not handling the
prepare statement correctly. I'm trying the ASCI version first. The
prepare statement returns an error. Here is the code snippet I'm
trying:


strSql.Format("CREATE TABLE test (something TEXT, something_else TEXT,
primary key(something))");

   sqlite3_stmt *pStmt;
   const char *pszTailPointer;
   int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
, );
   while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
   Sleep(100);
   // Try again.
   nRetVal = sqlite3_step(pStmt);
   TRACE("ret val was [%i]\n", nRetVal);
   }
   switch (nRetVal) {
   case SQLITE_DONE:
   TRACE("Done ok\n");
   break;
   case SQLITE_ERROR:
   TRACE("ERROR\n");
   break;
   case SQLITE_MISUSE:
   TRACE("MISUSE\n");
   break;
   default:
   break;
   }
   sqlite3_finalize(pStmt);
   return true;

Any hints?

Thanks,
Mark


On 8/13/06, Cory Nelson <[EMAIL PROTECTED]> wrote:


On 8/13/06, Mark Wyszomierski <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have been using sqlite on windows for a few months, it is great. I
> need to switch over to unicode support now though, and I am confused
> how to do this with sqlite.
>
> 1) First, when I compiled the original sqlite project, I have the
> character set to MBCS. Should I switch this to Unicode and recompile?

I think SQLite explicitly calls CreateFileW but I could be wrong.
Might as well compile as Unicode anyway.

> 2) I have been using sqlite3_exec() to execute my sql statements, but
> I see that there is no sqlite3_exec16() equivalent to take a unicode
> string. I think I'm supposed to use sqlite3_prepare16() but I have no
> idea what the last two parameters of that function are?

Check the docs, they explain how to use prepared statements.

> 3) To escape my sql statements I was using sqlite3_mprintf() - is
> there a unicode equivalent?

With prepared statements you put placeholders like "?" into your sql
and bind data to the placeholders - no escaping required.

> Thanks for any information,
> Mark
>
> 
- 


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


>
>


--
Cory Nelson
http://www.int64.org

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



A "prepare" just compiles the statment, and does not get busy.  You do 
it to get ready for your execution loop.


You execute the compiled statement with "step" and then call "reset" to 
check for errors and intialize the compiled statement ready for the next 
"step".  At the end of your processing you "finalize" the statement to 
tidy up and let you close the database.


   open
   prepare
   loop
   step
   reset
   repeat
   finalize
   close

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

Re: [sqlite] concurent writes and locks

[John Stanton]

Why not make your application simple and use a mutex to synchronize your 
threads and serialize the database access?


[EMAIL PROTECTED] wrote:

I have two threads heavily writing to the db. Hence, I get some
SQLITE_BUSY return values.

If I get it from sqlite3_step(), I wait a few ms and call sqlite3_step()
again etc. This happens in one thread, thread A.

The other thread (thread B) however, is calling the registered busy
handler while executing a commit with an sqlite3_exec() call. And this is
not going away either. even if I let thread A wait forever (so don't do
anything there) thread B is getting SQLITE_BUSY (in commit with
sqlite3_exec()). Both threads are not progressing any more...
Of course, both pieces of code run fine single-threaded :-)

Btw sqlite does not detect it is going into a deadlock since I added a log
indicating this in sqlite3BtreeBeginTrans() when it returns SQLITE_BUSY
without calling the handler, and this log is never appears.

So, I realy don't understand why thread B is calling the busy handler and
that the lock is never going away.
And is the procedure in thread A correct: just wait and recall the
sqlite3_step(). Maybe this is the reason of the behaviour we see in thread
B? How to overcome that situation then?

Eric

Re: [sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?

[John Stanton]

It depends upon your application.  For it to function optimally you 
should make each transaction on your application an SQL transaction, 
commit it on success or rollback if there is a problem.  In that way you 
make each transaction atomic and maintain the integrity of your database.


Since Sqlite locks the entire database when it is being modified, 
transactions which modify the database can be made exclusive.


RohitPatel wrote:

Thanks for clearing doubt.

Now question is...

While using SQLite dll Version 3.3.4 on Windows 
- Multiple threads/processes access SQLite database, 
- Each thread does some SELECTs, INSERTs or UPDATEs. 

Wrapping all read-only SELECEs with BEGIN TRANSACTION 
and using BEGIN EXCLUSIVE to wrap all UPDATEs or INSERTs (including their

related SELECTs),

Are their possibilities of writer starvation ? 
And if yes, what is the preferable solution ? (I mean what is the better

approach to handle that)

Thanks again.
Rohit

Re: [sqlite] Foreign Keys

[John Stanton]

Parsing is a language term.  You might recall being taught to parse a 
sentence into subject and predicate, then phrases, verbs nouns, articles 
etc.  A computer language parser does the same thing, lexical analysis 
to extract the words and syntactical analysis to match to the grammar.


That apparently is as far as Sqlite goes with foreign keys.

John Newby wrote:

FOREIGN KEY constraints are parsed but are not enforced.

Hi, what does the above statement on the SQLite website mean?

Call me stupid but I do not understand the word "parsed", I was thinking it
meant, it recognises them if you put them in your create table statement 
but

it does not enforce them, is this correct?

I looked it up in a dictionary but it confused me more, this is what it 
said

for the word parsed :-

1. To break (a sentence) down into its component parts of speech with an
explanation of the form, function, and syntactical relationship of each
part.
2. To describe (a word) by stating its part of speech, form, and 
syntactical

relationships in a sentence.
3.
  a. To examine closely or subject to detailed analysis, especially by
breaking up into components: "What are we missing by parsing the 
behavior of
chimpanzees into the conventional categories recognized largely from our 
own

behavior?" (Stephen Jay Gould).
  b. To make sense of; comprehend: I simply couldn't parse what you just
said.
4. Computer Science. To analyze or separate (input, for example) into more
easily processed components.

Re: [sqlite] Announce: SQL Maestro Group SQLite tools introduction

[John Stanton]

SQL Maestro Team wrote:

Hi All,

Two small notes first:

1. SQL Maestro Group is NOT linked with SQLight team, which messages you 
could read here last days.
2. It's our first announce and we promise to community to NOT send such 
messages too often - unfortunately, we don't release a new version every 
day. :-)


Let's continue. So the SQL Maestro Group is happy to introduce to you 
two new tools for the SQLite server.

http://www.sqlmaestro.com/products/sqlite/

The first product named SQLite Maestro. It is a software for efficient 
SQLite management, control and development, i.e. creating, editing, 
copying, extracting and dropping all the database objects, building 
queries visually, executing queries and SQL scripts, viewing and editing 
data (including BLOBs), representing data as diagrams, exporting and 
importing data to/from most popular file formats, etc. The main features 
of this product are as follows:


- Support of SQLite 2.8/3.3.6, including latest features such as check 
constraints and ASC/DESC indexes.
- Possibility of creating and editing of all schema objects, which is 
implemented as a number of convenient wizards and non-modal editors.
- Conception of database profiles gives you the opportunity to connect 
to databases in one touch and work with the selected databases only.
- SQL Editor with syntax highlighting, code completion and executing 
several queries at a time in separate threads.

- Visual Query Builder with SQL parser.
- Some other useful tools such as SQL Script Editor, BLOB Viewer/Editor, 
Diagram Viewer, Extract Database Wizard, etc.
- and much more. Non-commercial license costs as low as $49. Fully 
functional 30-day evaluation version is available at

http://www.sqlmaestro.com/products/sqlite/maestro/downloads/

The second software is SQLite Data Wizard. 
(http://www.sqlmaestro.com/products/sqlite/datawizard/) It is a utility, 
which provides you with a number of easy-to-use wizards for performing 
the required data manipulation easily and quickly. Currently SQLite Data 
Wizard includes the following features:


- PHP Generator Wizard: a powerful tool for generating PHP scripts from 
SQLite tables and queries. PHP Generator admits full customization of 
the resulting HTML appearance, customization of the resulting script, 
protection of your scripts with optional security settings.
- Data Pump Wizard: allows you to transfer databases (both structure and 
data) from such sources as Microsoft SQL Server or MS Access to your 
SQLite database.
- Data Export Wizard: powerful tools for exporting data from SQLite 
tables and queries to most popular formats (MS Excel, MS Access, MS 
Word, HTML, XML, PDF) are at your disposal.
- Data Import Wizard: you can import data from MS Excel, MS Access, XML, 
DBF, TXT, CSV formats to your SQLite database.
- Task Scheduler: one of the distinguishing features of SQLite Data 
Wizard consists in the ability to schedule tasks for executing them 
(once or periodically) later. A scheduled task starts a wizard with its 
template and does not require any actions from the user during execution.
- Of course you can also try this software without any trial 
limitations. Feel free to download an evaluation version at

http://www.sqlmaestro.com/products/sqlite/datawizard/download/

Hope you will like our products.
Sorry for such long message and thank you very much for your attention 
and patience.


Best Regards,
SQL Maestro Group Team
http://www.sqlmaestro.com
To Maestros and others.  Please announce your new work as often as you 
choose and continue to share it with others.  We are all the better for it.

Re: [sqlite] Problems opening db in win9x and utf8 filename

[John Stanton]

Obviously one has to have file names which do not clash with the rules 
of the underlying file system.  If you need to map a name to suit the OS 
you can detect the Windows OS version in your application and enforce 
compatibility by having a lookup table or by mangling.  As the old 
saying goes "In computer science any problem can be solved by yet 
another level of indirection".


Costas Stergiou wrote:

Hi John,
Have you tried to use sqlite3_open with a path that contains non-ascii chars
and make it work at the same time in Win9x and win2K? 
The 2 apps I mentioned before (sqLiteExplorer and SQLiteSpy) both fail the

above test (and for a good reason)
Costas

P.S. As I said, you can make an app work on both of these OSs, but with
external manipulation. 




-Original Message-----
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 08, 2006 11:13 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems opening db in win9x and utf8 filename

Our Sqlite applications work not only on Win98 and Win2000 but also on
Linux, AIX and Solaris.  Where did we go wrong?

Costas Stergiou wrote:


Hi all,
I saw no more comments on this suggestion. It is very simple to program
around this issue in user code, but I would like to see it fixed in the
library level. Unless someone has made this work-around in his code, an
application cannot work at the same time in Win9x and Win2k if there is


any


ansii char in the filepath.
Costas





-Original Message-
From: Costas Stergiou [mailto:[EMAIL PROTECTED]
Sent: Saturday, August 05, 2006 11:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems opening db in win9x and utf8 filename





I no longer have a win98 system to test with, but based on my
understanding...

os_win.c attempts to convert the filename from UTF-8 to UTF-16.  If it
succeeds, it calls CreateFileW;


Actually, there is a flag there that caused the convertion to UTF-16 to
'fail' (it doesn't really fail, just that the utf8ToUnicode returns 0).




if it fails, it calls CreateFileA with
the original string.


Exactly




CreateFileW is a nonfunctional stub on win98, so
when you pass a UTF-8 filename sqlite takes that codepath and fails.
An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is
called with the ANSI string.


Actually, in Win98 it will pass the conversion, but as I said above, the
function fails by a check: "if (!isNT())"




That doesn't necessarily explain win2k though.  Perhaps the current
user locale does not match the ANSI encoding of the filename you're
passing in?  Internally win2k only uses the Unicode version, so
CreateFileA must do an implict conversion to Unicode using the current
user codepage.


Now that I checked the code, it actually does.
Unfortunately, the way the code is setup makes it necessary for the


caller


to check in which OS it runs and either use UTF8 paths or ansii ones. I
think this is not a good technique (and not actually intended from what


I


have read in the docs) since the sqlite3_open does not give a truly
uniform
interface to the caller.

My suggestion is this:
The sqlite3_open should always expect a utf8 path (as the docs say). If


in


win2k everything works fine. If in win98 it should convert the path to
utf16
and THEN convert it to ansii using the CP_ACP (current ansii code page).
This will work for 99.9% cases since in non-English win9x OS, almost


99.9%


ansii strings are in the system's locale.
I think this is also the expected behavior (and what I have programmed


my


app to do, until I tested it in win98).

To make these changes, all the logic of os_win.c should change to
accommodate the above. I would certainly say that the way it currently
works
is wrong (bug).
Of course, there is the problem of breaking existing code (since many
win9x
user will not have read the docs, or else someone would have mentioned
this
behavior looong time agoe).
To maintain compatibility (e.g. to accept ansii non-utf8 paths), a check
can
be made on whether the supplied path is in utf8 (heuristically this has
almost 100% success) and then do the above.

Costas






MSLU does provide a functional CreateFileW wrapper for win9x, but I
don't believe the stock sqlite binaries are built with it.


On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote:




I think you will never succeed using UTF-8 encoded filenames on those
systems. I don't know how it can be done programmatically, but each
file or directory name has its 8.3 name as well, i.e. "Program Files"
would be "progra~1". I think this is the safest way how to pass
filenames to SQLite. It should work on Win 9x as well as 2K and XP.


NTFS can have 8.3 shortname creation disabled.  Systems running
without it are not common but do exist, so you should avoid relying on
them if at all possible.

Re: [sqlite] Problems opening db in win9x and utf8 filename

[John Stanton]

Our Sqlite applications work not only on Win98 and Win2000 but also on 
Linux, AIX and Solaris.  Where did we go wrong?


Costas Stergiou wrote:

Hi all,
I saw no more comments on this suggestion. It is very simple to program
around this issue in user code, but I would like to see it fixed in the
library level. Unless someone has made this work-around in his code, an
application cannot work at the same time in Win9x and Win2k if there is any
ansii char in the filepath.
Costas




-Original Message-
From: Costas Stergiou [mailto:[EMAIL PROTECTED]
Sent: Saturday, August 05, 2006 11:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems opening db in win9x and utf8 filename




I no longer have a win98 system to test with, but based on my
understanding...

os_win.c attempts to convert the filename from UTF-8 to UTF-16.  If it
succeeds, it calls CreateFileW;


Actually, there is a flag there that caused the convertion to UTF-16 to
'fail' (it doesn't really fail, just that the utf8ToUnicode returns 0).



if it fails, it calls CreateFileA with
the original string.


Exactly



CreateFileW is a nonfunctional stub on win98, so
when you pass a UTF-8 filename sqlite takes that codepath and fails.
An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is
called with the ANSI string.


Actually, in Win98 it will pass the conversion, but as I said above, the
function fails by a check: "if (!isNT())"



That doesn't necessarily explain win2k though.  Perhaps the current
user locale does not match the ANSI encoding of the filename you're
passing in?  Internally win2k only uses the Unicode version, so
CreateFileA must do an implict conversion to Unicode using the current
user codepage.


Now that I checked the code, it actually does.
Unfortunately, the way the code is setup makes it necessary for the caller
to check in which OS it runs and either use UTF8 paths or ansii ones. I
think this is not a good technique (and not actually intended from what I
have read in the docs) since the sqlite3_open does not give a truly
uniform
interface to the caller.

My suggestion is this:
The sqlite3_open should always expect a utf8 path (as the docs say). If in
win2k everything works fine. If in win98 it should convert the path to
utf16
and THEN convert it to ansii using the CP_ACP (current ansii code page).
This will work for 99.9% cases since in non-English win9x OS, almost 99.9%
ansii strings are in the system's locale.
I think this is also the expected behavior (and what I have programmed my
app to do, until I tested it in win98).

To make these changes, all the logic of os_win.c should change to
accommodate the above. I would certainly say that the way it currently
works
is wrong (bug).
Of course, there is the problem of breaking existing code (since many
win9x
user will not have read the docs, or else someone would have mentioned
this
behavior looong time agoe).
To maintain compatibility (e.g. to accept ansii non-utf8 paths), a check
can
be made on whether the supplied path is in utf8 (heuristically this has
almost 100% success) and then do the above.

Costas





MSLU does provide a functional CreateFileW wrapper for win9x, but I
don't believe the stock sqlite binaries are built with it.


On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote:



I think you will never succeed using UTF-8 encoded filenames on those
systems. I don't know how it can be done programmatically, but each
file or directory name has its 8.3 name as well, i.e. "Program Files"
would be "progra~1". I think this is the safest way how to pass
filenames to SQLite. It should work on Win 9x as well as 2K and XP.


NTFS can have 8.3 shortname creation disabled.  Systems running
without it are not common but do exist, so you should avoid relying on
them if at all possible.

Re: [sqlite] Insert delay!

[John Stanton]

Cesar David Rodas Maldonado wrote:

Yeah John I am studying Math and computer science (but i am in the first
year, and this is very boring) in National University Of Paraguay.
When I finish my idea i will share my code.

Thanks For understand my English John!


On 8/5/06, John Stanton <[EMAIL PROTECTED]> wrote:



Cesar David Rodas Maldonado wrote:
> I was thinking a lot in the next month in how can I do the delay insert
and
> I found something for do that, is basic because I don't have a lot of
> knowledge, I'm just start the University.
>
> OK, I need with SQLite to select all the time as possible, and delay 
the
> Insert, I don't care  the time  that took  insert something. These 
is my

> needs with SQLite. Is something Interesting on my idea? If there is one
I
> will share my idea.
>
Your English is not very clear, but as I understand your idea it could
be realized by performing your INSERTs in a thread, ideally set to a low
priority and fed from a FIFO queue.  You need to be aware of the locking
constraints.

Have success with your studies.  Are you studying Math and Computer
Science?



You are welcome.  I look forward to seeing your code.  Math and CS can 
be very exciting, so stick at it.  I have a daughter who has been 
studying Math and Computer Science and is now in an cutting edge PhD 
program and very happy.  The best is yet to come for you.

Re: [sqlite] what tier architecture?

[John Stanton]

John Newby wrote:

Hi, this is probably a stupid question and it has nothing to do with SQlite
per se so I apologise in advance for hijacking the emails withmy question
plus I don't even know if this is the right place to ask the question.

I have created a GUI to SQLite using VB.Net for my University project 
and it

interfaces with the SQLite .dll file.

I have to now write about the architecture of my product but I am unsure as
what it would be, after reading this website I am even more confused :-

http://www.javaworld.com/javaworld/jw-01-2000/jw-01-ssj-tiers.html

Could it be one tier as both the DBMS and GUI are as one

or could it be two tier as the GUI could be the client and the DBMS the
server

or could it be three tier as the GUI could be the client and the DBMS the
server and database?

I'm really confused, so any help or information on this subject would be
really appreciated.

Many thanks

John

I would regard what you have developed as one tier.  You do not have a 
server and consequently do not realise the two or three tier model.


A two tier model has "fat" client and a server whereas a three tier has 
a "thin" client, and application server and a dbms server.


The way you describe your application is that it is integrated and stand 
alone.  If you have intercept locks within in to resolve contentions 
between multiple users of the database you can describe it as having 
multi-user capability.

Re: [sqlite] Insert delay!

[John Stanton]

Cesar David Rodas Maldonado wrote:

I was thinking a lot in the next month in how can I do the delay insert and
I found something for do that, is basic because I don't have a lot of
knowledge, I'm just start the University.

OK, I need with SQLite to select all the time as possible, and delay the
Insert, I don't care  the time  that took  insert something. These is my
needs with SQLite. Is something Interesting on my idea? If there is one I
will share my idea.

Your English is not very clear, but as I understand your idea it could 
be realized by performing your INSERTs in a thread, ideally set to a low 
priority and fed from a FIFO queue.  You need to be aware of the locking 
constraints.


Have success with your studies.  Are you studying Math and Computer Science?

Re: [sqlite] date data types

[John Stanton]

Will Leshner wrote:

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


Adding DATE and TIMEINTERVAL types to SQLite would require an
incompatible file format change.  And it would go against the
basic philosophy of SQLite.



I wonder if it might not be useful to be able to ask SQLite if a value
is a date or time. I'm assuming that at some point SQLite has to
figure out if a value in a field is a date or time, and perhaps that
logic could be exposed as an API call somehow.


You can declare it to be a date or a time or both.  Sqlite lets you make 
the declared type of a column anything you want it to be.  It is smart 
enough to maintain the underlying storage type a float.


What we do with dates is add a couple extra functions into Sqlite 
(simple things like a month number) and lift the date handling logic out 
of Sqlite and place it is a support library for use in applications. 
The way Sqlite cleverly uses a FLOAT for a date and time type and 
relates it to the theoretically correct epoch makes for an excellent 
date processing system.  If you need it you can add functions to resolve 
not just Gregorian dates but Arabic, Hebrew, and various oriental dates.


Timestamping is made simple and efficient.

Since your application or a function can access the declared type from 
Sqlite your application (or a custom function) can decide on how to 
interpret the FP date/time stamp.

Re: [sqlite] Return value of sqlite3_last_insert_rowid()?

[John Stanton]

Olaf Beckman Lapré wrote:

What I'm not clear about is that the documentation says that rowid is an
alias for the primary key column of type INTEGER PRIMARY KEY. How is the
sqlite_int64 converted to a 'normal' C long?

I'm using the sqlite3_last_insert_rowid() call to retrieve the key of the
row I just inserted (so I can refer to it when I do UPDATE and DELETE).

Olaf
- Original Message - 
From: "Olaf Beckman Lapré" <[EMAIL PROTECTED]>

To: "SQLite Mailing List" 
Sent: Wednesday, August 02, 2006 8:30 PM
Subject: [sqlite] Return value of sqlite3_last_insert_rowid()?


Hi,long long int sqlite3_last_insert_rowid(sqlite3*);The documentation
states that the return value is of type 'long long int'. How do I use this
type?Olaf

You can do a move with a type cast to get it from 64 bits to 32 bits, 
but that could give you truncation problems with a large table.  "long 
long int" is a normal C type for a 64 bit integer.

Re: [sqlite] IO Access Time

[John Stanton]

Christian Smith wrote:

John Stanton uttered:

In general you must expect Sqlite to use more of all resources 
compared to a flat file.  After all Sqlite is a flat file with 
additional logic.




Except updates and selective reads will be cheaper in general, as less 
IO is required due to additional logic.


Storage will go up, however. SQLite trades CPU cycles for IO compared to 
flat file access.


Christian


Sqlite is more than one flat file.  It is impossible for it to use less 
resources than one flat file.


The only advantage you would gain from Sqlite is in the case where you 
use the flat file inefficiently.  Sqlite is useful when you want data 
management and SQL access implemented in a very elegant and simple way.



--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] IO Access Time

[John Stanton]

Aseem Rastogi wrote:

Hi All,

I am planning to migrate to SQLite for my embedded system application.

Currently we have a flat text file which we read and write using normal 
C++ routines. Whole of the configuration is written everytime some 
change happens. We do it using ofstream. For reading any object, we keep 
all the objects in memory and return from there (to prevent file read 
every time). This takes up a lot of memory but makes it faster.


I am a bit concerned about following issues while upgrading to SQLite :

1. How does SQLite behave as compared to our current approach regarding 
I/O access time ?

2. How much disk space does SQLite take as compared to flat text file ?

If somebody has some benchmarking data for these issues, it will be 
really helpful if he can enlighten me on this.


Thanks in advance,

Regards,
Aseem.

In general you must expect Sqlite to use more of all resources compared 
to a flat file.  After all Sqlite is a flat file with additional logic.

Re: [sqlite] how to use the sqlite command-line specific commands with the dll version

[John Stanton]

John Newby wrote:

Hi, I was wondering how I could use the commands specific to the
command-line interface with the dll version.

The commands I am wanting to use are as follows :-

.dump
.import
.output
.read

Any help would be greatly appreciated

Many thanks

John

These are part of the Sqlite3.exe program.  Lift code out of that and 
place it in your application.

Re: [sqlite] retrieving integers and long

[John Stanton]

Wyan wrote:
I'm calling sqlite3_column_type() to get the type, and, for a number 
(20), it returns SQLITE_INTEGER.  I'm guessing this covers int, long,  
and long long.  Is there any way to distinguish which one it is, so I  
can know to call the right sqlite3_column_() function?  I've got some  
higher level code over this, and I'd like to be able to get the type  
that's actually in the database.


Thanks,

-- wyan
You can also retrieve the declared type, which (thanks to manifest 
typing) is not necessarily the same as the actual stored type.  If you 
are interfacing to another system you are very likely to need the 
declared type and sometimes perform conversions to reverse out what 
Sqlite decided to do based on the actual data.

Re: [sqlite] Reading the same table from two threads

[John Stanton]

Jay Sprenkle wrote:

On 7/26/06, John Stanton <[EMAIL PROTECTED]> wrote:


>
> Almost a "plug-in" serialization organization. I don't think I've ever
> seen anyone do it that way, but that might be really valuable.
> The embedded software guys would just love it.

We had an ISAM product which worked after that fashion.  It replaced a
server-type implementation which in unfamiliar hands was more trouble
than a bag full of monkeys.



The isam product was barrel of monkeys fun, or the server implementation?

The only thing I can see that would be a problem would be chosing the wrong
model and ending up with corruption because you aren't serialized.

--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com
The server was a constant source of problems because it had to be 
installed on the host machine and integrated with the startup, not 
always easy in an earlier era of departmental computers and junkyard dog 
system administrators.  If it stopped for some reason it generated 
service calls.  An ISAM manager integrated with the the applications was 
much less trouble, and I can see that situation recurring with Sqlite a 
fortiori.


Your point about problems due to the wrong model is very pertinent. 
Perhaps there could be a check compile mode, like compiling in range 
checking in a Pascal program, to intercept at run time gross 
misconfigurations.

Re: [sqlite] Reading the same table from two threads

[John Stanton]

Jay Sprenkle wrote:

On 7/26/06, John Stanton <[EMAIL PROTECTED]> wrote:


Jay Sprenkle wrote:
>> > Will the mutex replace file locking for database access control?
>> >
>>
>> No.  A mutex only works within a single process.
>
>
> I was thinking it might make a good compile time option.
> If you aren't going to access the database from multiple machines then
> the mutex could replace file locking as an access control mechanism.
> My guess was it would be much faster,
> but would only work correctly in that special case.

If the mutex capability were layered then there could be an init call
which would let the user choose a thread-type mutex for multi-threaded
single process, a semaphore-type kernel structure for multiple processes
and some form of lock manager (as I remember using on VMS) for a network.



Almost a "plug-in" serialization organization. I don't think I've ever
seen anyone do it that way, but that might be really valuable.
The embedded software guys would just love it.


We had an ISAM product which worked after that fashion.  It replaced a 
server-type implementation which in unfamiliar hands was more trouble 
than a bag full of monkeys.

Re: [sqlite] Reading the same table from two threads

[John Stanton]

Jay Sprenkle wrote:

> Will the mutex replace file locking for database access control?
>

No.  A mutex only works within a single process.



I was thinking it might make a good compile time option.
If you aren't going to access the database from multiple machines then
the mutex could replace file locking as an access control mechanism.
My guess was it would be much faster,
but would only work correctly in that special case.


If the mutex capability were layered then there could be an init call 
which would let the user choose a thread-type mutex for multi-threaded 
single process, a semaphore-type kernel structure for multiple processes 
and some form of lock manager (as I remember using on VMS) for a network.

Re: [sqlite] Reading the same table from two threads

[John Stanton]

[EMAIL PROTECTED] wrote:

"Rob Richardson" <[EMAIL PROTECTED]> wrote:


I found the spot where I was telling the two threads to use the same
database pointer instead of running on separate ones.  Once I fixed
that, it works.




It has been suggested that I add a mutex to every SQLite
database connection.  This would cause access to a database
connection to automatically serialize even when two or more
threads try to use that connection at once, thus preventing
problems such as the above.

The downside is the (minimal) additional overhead of acquiring 
and releasing a mutex on each API call.  There will also be 
some changes to the OS-layer backend which will break 
private ports to unusual OSes.


Thoughts?

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

I would regard that as detrimental for a library which is LITE and 
better left up to the application designer.  However a set of 
synchronizing API calls using a mutex or similar could be a nice 
addition and remove what is a persistent, nagging issue for many users, 
serialization from their own code.


A set of layered API calls with inbuilt access arbitration which using 
Sqlite look like access to a server would be intuitive to many potential 
users.


If the mutex path were taken then the file locks could be removed 
entirely, with the benefit of decoupling Sqlite from file locking 
implementation woes and possible performance improvements but at the 
cost of implementing some form of lock manager for remote file systems 
and breaking backwards compatibility.

Re: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison

[John Stanton]

Eduardo wrote:

At 01:38 26/07/2006, you wrote:


Nuno Lucas wrote:


On 7/22/06, Eduardo <[EMAIL PROTECTED]> wrote:


Sorry, but i must disagree. He uses VC6, a compiler from

you use a compiler from pre-altivec era or non altivec optimized
libraries (including libc), your code will be a lot slower.



While I agree with you on the general, I must note that would be for a
CPU-bound application. Most sqlite applications tend to be IO-bound
(unless you have enough memory and is all cached), so the differences
will not manifest so much.



I usually have enough memory ;) and must agree with your note, I/O to 
disk or network is a real bottleneck. But using a better compiler which 
make faster code ( or better, do the same work in less CPU cycles ) 
allows the principal application or other threads work faster also. So 
this affect not only sqlite, but the whole app.



As an aside, there are applications that have better performance with
-Os (optimized for size) than with any -O, because can incur in dramatically less cache misses (which is
several times slower than a cache hit and can also imply scheduling
decisions against the process/thread).

Regards,
~Nuno Lucas



Nuno has a very valid point that we certainly observe.  Programs which 
run all the time, expecially interpreters like the Sqlite engine, run 
very much better when they are small so that the regularly used 
components fit in the processor cache.  Having compact data structures 
can be a big help in keeping the working set cached.



Well, i never tried to compile optimizing size, it's very curious that 
doing so we can get faster apps. Must try it.



Alien.org contacted...waiting for Ripley
Your processor runs much faster than your memory, so when it has to 
access from memory it has to wait.  That is why it has cache, to cut 
back on waits for frequently accessed items.  Minimizing checkerboarding 
in your programs makes for faster execution.

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

[John Stanton]

John Newby wrote:

Hi, I've took the following quote from the SQLite website stating that
"Tables names that begin with "*sqlite_*" are reserved for use by the
engine"  and if you try to create a table beginning with this it lets you
know about it, but what about tables called "TABLE", this throws back an
error stating "SQL Error: near 'Table': Syntax error"

Does anyone know any reason why SQLite doesnt like tables called "Table" or
is this a standard SQL thing?

Many thanks

John


TABLE is a reserved word.

Re: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison

[John Stanton]

Nuno Lucas wrote:

On 7/22/06, Eduardo <[EMAIL PROTECTED]> wrote:


Sorry, but i must disagree. He uses VC6, a compiler from
pre-alot-of-processor-advances. So, the compiler can't, not only
compile for a more modern and different processor (different
pipeline, sse, new processor modes, etc..) but also use old libraries
that were made for older processors. For example, in Delphi 6 (if i
remember well) code written in pascal run faster than assembly
optimized libraries, but written for 386!!!. In powerpc area (what i
know), using altivec libraries, boost sort code up to x16,
memory2memory copy, assign, move, etc... up to x4 and so on, but if
you use a compiler from pre-altivec era or non altivec optimized
libraries (including libc), your code will be a lot slower.



While I agree with you on the general, I must note that would be for a
CPU-bound application. Most sqlite applications tend to be IO-bound
(unless you have enough memory and is all cached), so the differences
will not manifest so much.

As an aside, there are applications that have better performance with
-Os (optimized for size) than with any -O, because can incur in dramatically less cache misses (which is
several times slower than a cache hit and can also imply scheduling
decisions against the process/thread).


Regards,
~Nuno Lucas


Nuno has a very valid point that we certainly observe.  Programs which 
run all the time, expecially interpreters like the Sqlite engine, run 
very much better when they are small so that the regularly used 
components fit in the processor cache.  Having compact data structures 
can be a big help in keeping the working set cached.

Re: [sqlite] Sqlite Write and read a the same time

[John Stanton]

If you want to use that POSIX record locking, use a flat file.  Then you 
can lock a section of it.  A DBMS like Sqlite is a web of linked records 
and shares the structures which maintain the links.  Row and table 
locking requires other structures.


Since Sqlite is a library, not a server, and you link it into your 
application, there is nothing to stop you using locking and 
synchronization primitives like mutexes, events and semaphores to 
establish your own co-operative locking.


Cesar David Rodas Maldonado wrote:

So, is imposible to read alway and write one per time... ?

On 7/25/06, Cesar David Rodas Maldonado <[EMAIL PROTECTED]> wrote:



I just want to read always and write one time.. understand? is that
imposible?


On 7/25/06, Robert Simpson <[EMAIL PROTECTED] > wrote:
>
> > -Original Message-
> > From: Cesar David Rodas Maldonado [mailto: [EMAIL PROTECTED]
> > Sent: Tuesday, July 25, 2006 8:07 AM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] Sqlite Write and read a the same time
> >
> > Hello!
> >
> > The weekend I was reading a book "Programming in Linux" and I found
> > something for me very cool!. I so that you could Lock a File
> > for write but
> > just a part of the file. And I am wondering if in Linux
> > SQLite would be able
> > to lock just a Page of the B-tree when you are doing the
> > insert and the
> > update, that will be better I think that Lock all the File 
(database).

> >
> > The function is in the header < fcntl.h> and the function is:
> >
> > int fcntl(int fd, int cmd, struct flock *lock_type);
> >
>
> The problem is that when SQLite is updating, it needs to lock a lot 
more

> than just the page its updating.
>
> When pages are moved, rewritten, added, etc, all the references need to
> be
> written.  The master pages need updating.  You can't update all those
> other
> pages during a write op and expect readers to continue to be able to
> read in
> a consistent state.
>
> Robert
>
>
>

Re: [sqlite] locked implies exception?

[John Stanton]

Jay Sprenkle wrote:

On 7/25/06, John Stanton <[EMAIL PROTECTED]> wrote:


Martin Jenkins wrote:
> John Stanton wrote:
>
>> I used to have one but I had to shoot it when it went feral.
>
> As a puppy?
>

No, not until instead of just chasing cars it started catching and
eating them.



Wouldn't that be 'cacheing' them and eating them?


Ouch!  Quit now.  Database design is not pun.

Re: [sqlite] locked implies exception?

[John Stanton]

Martin Jenkins wrote:

John Stanton wrote:


I used to have one but I had to shoot it when it went feral.



As a puppy?

mj


No, not until instead of just chasing cars it started catching and 
eating them.



Martin Jenkins wrote:


Jay Sprenkle wrote:


what's a CRUD?




I wondered that too. http://en.wikipedia.org/wiki/CRUD_(acronym)

Martin

Re: [sqlite] Getting callback with an INSERT

[John Stanton]

Olaf Beckman Lapré wrote:

No, that's not the case since I'm using an AUTOINCREMENT key and I can't
know the value of the key until the INSERT is completed.

Olaf

- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Monday, July 24, 2006 5:30 PM
Subject: Re: [sqlite] Getting callback with an INSERT




It hardly seems necessary to make it a callback since you could just
call your function concurrently with the INSERT SQL.

Olaf Beckman Lapré wrote:


Is it possible to get a callback when doing an INSERT on a table similar


to a SELECT callback? The callback would then contain the same parameters
but only contain the row(s) being inserted.


The reason I'm asking is that this may be usefull in GUI applications


where you insert table rows into a listcontrol. Instead of emptying the
listcontrol and using the SELECT callback to fill it again, one could simply
add the row being added from the INSERT callback.


Olaf





There is an API call sqlite3_last_insert_rowid(..) which gives you the 
rowid after the insert.  Here is a code fragment using it.


  
  if (bind_txt_arg(dbcmp_bchreg_ins, 7, bchr->bch_type)) return(TRUE);

  /*Now we execute the SQL statement in a single step.*/
  sqlite3_step(dbcmp_bchreg_ins);   /*Executes compiled SQL.*/
  rc = sqlite3_reset(dbcmp_bchreg_ins); /*Ready for next access.*/
  if (rc == SQLITE_DONE) bchr->rowid = sqlite3_last_insert_rowid(db);
  else {
sqlite_step_check(rc, fnm);
rollback_db_trans(trnm);
SetEvent(db_sync);
return(-1);
  }  /*if/else*/
  

Re: [sqlite] locked implies exception?

[John Stanton]

I used to have one but I had to shoot it when it went feral.

Martin Jenkins wrote:

Jay Sprenkle wrote:


what's a CRUD?



I wondered that too. http://en.wikipedia.org/wiki/CRUD_(acronym)

Martin

Re: [sqlite] Getting callback with an INSERT

[John Stanton]

It hardly seems necessary to make it a callback since you could just 
call your function concurrently with the INSERT SQL.


Olaf Beckman Lapré wrote:
Is it possible to get a callback when doing an INSERT on a table similar to a SELECT callback? The callback would then contain the same parameters but only contain the row(s) being inserted. 


The reason I'm asking is that this may be usefull in GUI applications where you 
insert table rows into a listcontrol. Instead of emptying the listcontrol and 
using the SELECT callback to fill it again, one could simply add the row being 
added from the INSERT callback.

Olaf

Re: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison

[John Stanton]

Michael,

The guy who produced Sqlitespy is a member of this forum so he can 
confirm or debunk my theory as to why you are getting a big difference 
in execution time.  I suspect that Sqlitespy might be storing the SQL in 
its compiled (from sqlite3_prepare) form and when you run it you skip 
the compile phase.  It is hard to imagine that compile optimization 
makes a fourfold difference in executions speed, particularly to a well 
written program like Sqlite.


We do a similar thing in our Sqlite applications, compile the SQL when 
the program loads then use the compiled VDBE code as input to 
sqlite3_bind and sqlite3_step at execution time.  It kicks up 
performance no end and also traps any schema mismatches before any 
processing starts.


Note that if you use the sqlite3_exec function you cannot have 
pre-compiled SQL because sqlite3_exec is a wrapper around 
sqlite3_prepare/step/reset/finalize.  It is a big waste of processing 
time to be compiling the same statment thousands of times unnecessarily.


If you use sqlite3_exec I suggest that you replace it.  It is only there 
for compatibility with legacy programs.


If you use gcc you can use option -finline-functions to get some extra 
speed at the cost of a larger executable, although Sqlite is not written 
with thousands of one line functions so the improvement will not be great.


michael cuthbertson wrote:

Thanks to Christian and John for the pointers regarding compilers.
I have not compiled the sqlite sources myself but have used the supplied
binary.
Could either one you give me some tips for compiling the sqlite sources for
either vs 6 or 8?
John, I will follow your advice on inline functions.
The absolute last worry I have is the size of my exe.
My concerns are speed (1) and runtime memory (2), i.e., in-mem db size.
BTW, here are some times to demonstrate what I am facing:
A table with 14 columns, 8 indexes, only 4k rows.
This is a secondary(subset) table - not a view - that I created since
running this
query against my complete table of 440k rows was impossibly slow.
The following query takes 75ms in my code - just the SQL_exec and
callbacks -
while SQLiteSpy takes 20ms, including display.

select * from (select f1, f2, f3, f4, f5
 from table where f6 = 2563351070 and f2 <='2006-01-01' and f2 >=
  '2004-01-01')
  order by f1 limit 32 offset 855;

This qry is used to refresh a scrolling display where any of the constants
are actually variables.
The subquery returns about 1000 rows.
Note that a LIMIT = 1 is only marginally faster than 32.
Also, as the offset increases, the exec. time increases about 50% as fast,
which I do not understand,
since this would merely seem to be an index into the result set that should
be low cost.

Re: [sqlite] A littel question...

[John Stanton]

Cesar David Rodas Maldonado wrote:

I have not a substring, I have a list of words (stemmed words of several
languages) and i just want to get the Id. The word is unique

In that case the sqlite B-Tree index is about as good as you will get. 
just make sure that the word is an index.

Re: [sqlite] A littel question...

[John Stanton]

Cesar David Rodas Maldonado wrote:

Hello to everybody

If I  have a table with 100.000 unique words I am wondering if SQLite 
select

if faster an cheaper (RAM, Processor, etc), or If i have to help SQLite
using a Hash function, and what could be that Hash function?

Thanks.

Do you want to select on whole words, first few characters in the word 
or on sub-strings?

Re: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison

[John Stanton]

It is possible to resolve the issue by using the traditional C profiler. 
 Compile the SQL library with profiling on the different compilers and 
measure where the time is spent during execution.


You can also compile some test programs and look at the assembler output 
to get an idea of the efficiency of the optimizer.  A good optimizer can 
make big chunks of code disappear.


We find that thoughtfully written C runs well with or without compile 
optimisation.  Carelessly written programs benefit strongly from 
optimisation to remove common sub-expressions etc.  We also notice that 
a compiler which is specialized for a particular architecture so that it 
makes use of the full register file produces the best code for that 
machine.  An example of that is the IBM XLC which produces code running 
40% better than GCC on the Power machines.  Someone may have a similar 
comparison for some Intel compilers compared to GCC as a benchmark.


For speed unrolling loops and inlining functions creates a bigger but 
noticeably faster executable.  Since function calls are expensive in 
execution time, inlining them can be a big win.


Shields, Daniel wrote:
 


Daniel:
Thanks for the suggestion.
I wasn't aware that the prepare statement gained you that 
much for one-table select queries.
I use it for multi-100k inserts (along with trans.) and it 
saves quite a bit of time.

This is my sql for the present problem:

select * from (select f1, f2, f3, f4,
f5  from Table where f2 = 'abc' and f3 = 2563351070 and f4 >= 
'2004-01-01'and f4  <='2006-01-01' ) order by f1 limit 32 offset 900;


Do you think that prepare would be helpful here?
Regards,
Michael




Michael,

You're right, for a single query the pre-prepared statement
will save no time. If performance is important you may get 
some mileage out of an optimising compiler.


http://www.intel.com/cd/software/products/asmo-na/eng/compilers/284527.htm

Daniel.

==
Please access the attached hyperlink for an important electronic communications disclaimer: 


http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==

Re: [sqlite] SQLite NFS Problem compared to Other Db's

[John Stanton]

Over NFS you are limited to the bandwidth of your network, probably 1-10 
Mb/s.  Compare that to the disk speed on your host server, one or two 
orders of magnitude better.  The NFS link could be up to 50 times slower.


If you want better distributed performance use a DBMS server like 
PostgreSQL.


Ritesh Kapoor wrote:

Hi,

Most of us are aware that SQLite on Linux has problems with NFS mounted
volumes - basically the file locking issue.

If there is a single thread accessing the SQLite DB - and this is
guaranteed - then there is no need to have file locking.  So I modified
the code for SQLite and removed all locking mechanism.

However, the performance of SQLite insert/delete of rows varies a lot
when the DB file is local or accessed over NFS.

I've also removed the Synch-mechanism and increased the SQLite page size
as well as the number of pages to hold in cache.

I understand that all this can cause data loss if the system crashes but
that is tolerable.

What I can't figure out is that the performance over NFS is still
horrible.  My application requires inserting one row at a time, many
times in a single run.  I can't use transactions when inserting but I've
used them for deletion.

Can anyone give me some more ideas to work with.  Does this performance
problem happen with the other DB's available as well? 

Re: [sqlite] Importing Oracle 8.1.7 data into SQLite

[John Stanton]

Write it out in SQL and read in the SQL.  A bit clumsy, but simple.

Vinod Inamdar wrote:

Dear All,

I am a newbie to SQlite and I require the above
mentioned functionality in the subject line for a
specific project. Is it possible to import Oracle
8.1.7 data into SQLite.

Also is it possible to export data from SQLite to
Oracle 8.1.7

Regards,
Vinod Inamdar

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] sqlite

[John Stanton]

Sqlite is driven by SQL, so you just use SQL like any other SQL database.

sandhya wrote:

Hi,

Is there any possibility to import files from the local file system to the
sqlite DB.And Is there any export option is there just to check whether the
loaded file and exported file consists of same data or not.
Is it possible in sqlite?
If possible,How it will stores files in tables?In which format?
Please explain me how can i do it.

Thank you
Sandhya

Re: [sqlite] Resources required and Lock & recovery mechanisms

[John Stanton]

Sqlite requires few resources.  Locking is achieved through regular file 
locks which lock the entire database since it is a file.


Flow control is not applicable.

You may use semaphores etc in your application for synchronization, but 
they are not used by Sqlite.


Maintenance of an Sqlite database uses regular SQL.  Backups and loading 
are just file copies since an Sqlite database is a single file.


Vivek R wrote:

Hi Everybody,
I have the following doubt...

1. what are the resources required by SQLLite - they can be RAM/ROM,
semaphores, mail boxes, task requirements;
2. How do we have flow control?
3. what are the Lock mechanisms provided by the engine (row lock, table
lock..)? Any additional lock mechanism we need to build.

3. How to create a service component that creates these tables on HDD ( 
Hard

disk on Consumer products like DVD or Set top box ) before it leave the
factory.
4. recovery mechanisms (in case DB crash how do we recover/reconstruct
data?)


Thanks and Regards,
 Vivek R

Re: [sqlite] Compress function

[John Stanton]

Cesar David Rodas Maldonado wrote:

I compile SQLITE 3 source into my APP, but i will like to use like mysql
uses ( the COMPRESS() function into the sql), understand?


I understand.  Unfortunately I haven't implemented that.

Re: [sqlite] Compress function

[John Stanton]

Cesar David Rodas Maldonado wrote:
I need a funcion from compress a row with Zlib and  I am wondering if 
SQLite

support or if someone did it  and want to share him or her code.

Thanks to all

Do you want to have it as an Sqlite function or as a function in your 
application?


In general you just download zlib and compile the library on your 
machine and use the examples in the zlib release as a template.

Re: [sqlite] reg:sqlite usage

[John Stanton]

The way we use Sqlite in web applications is as part of the web server, 
creating an application server.  We wrote our own webserver and 
application specific language processor and use Sqlite for storage. 
Sqlite is a great component for such a project and means that one 
multi-threaded process runs everything instead of having web servers, 
cgi processes and DBMS servers linked by interprocess communication.


We also created CGI processes which perform web activities and which 
have embedded Sqlite and XML.


Another way for you to use Sqlite is to use it embedded in something 
like PHP (which comes supplied with embedded Sqlite) or to use a wrapper 
around Sqlite for your favorite high level language.  We don't use it 
that way so others can give you better advice if you go that way.


sandhya wrote:

John,
 If  u don't mind can you please explain me is it possible if i load /store
any files as Large objects inside DB and open it via a webserver instead of
storing it in a local system and opening.In that case what i have to do.Like
my DB whether i should place in the Webserver applcaition and access it or
how can i do it?




- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 19, 2006 6:04 PM
Subject: Re: [sqlite] reg:sqlite usage




Sqlite is a wonderful tool.  We use it with great success embedded in a
custom application server for web applications, embedded in CGI
processes and in some industrial process control applications.  It is
robust and very simple to use, and since it places all tables in one
file, very easy to maintain.

sandhya wrote:


ya..Thank you ver much John.Right now i am using Postgresql only.But


just i


want to find out the information abt Sqlite as i heard that it is having
very small footprint and its good for Embedded systems app.
Now i got it,Thanks a lot.

-Sandhya

- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 19, 2006 5:53 PM
Subject: Re: [sqlite] reg:sqlite usage





Sandhya,

You use Sqlite the same way you use a file in your application.  You
link in the runtime library containing the file handling API when you
create your executable.  Sqlite gives an embedded application the
capability of using SQL for data management.

If you are building a client server model you might want to look at
using something like PostgreSQL or Mysql which are DBMS servers.  There
is no Sqlite server.

Sqlite is used as an embedded DBMS in programs such as the Firefox
browser, LCC IDE and similar.

sandhya wrote:



You link Sqlite into your application...May i know what it mean?
Please explain me.
Also you wanna want to say that we shouldn't use this in Client /Server
applications.Like,Connecting to the Sqlite server through the


application



and performing all the operations through(application) it and updating


the



server.
Really i am totally confused with this...If you don't mind can you


just



explain me where we can use this SQLITE.pls

Thank you,
Sandhya



- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 19, 2006 5:10 PM
Subject: Re: [sqlite] reg:sqlite usage






Sandhya,

You have not grasped the concept of Sqlite.  It is a RDBMS LIBRARY,


not


a server.  You link Sqlite into your application.  As Dr Hipp points


out


it it an alternative to fopen, not Oracle.

sandhya wrote:




Hi,
I am very new to SQLITE.I have downloaded and installed Sqlite 3 in


my



windows system.I built lib and dll too.
I tried the sample given in the documetation.The connection info i


have


given the DB name, the table name and the query.But the database name


what




ever i am giving it is getting stored in my current application.Will


it


be




like that.
Is there any interface to see the tables i have created in using the


sample




given there?
Where can i manually enter the queries?
Can i perform Client-server application kind of a thing.
I mean,With the available API , i will be writing the coding and


which


will




perform all the operations mentined like creating table,DB,inserting


etc



etc.Which should update in server directly.If i want to do


so.Where


i




need to mention the IP of my server.
Please guide me how to proceed for Client/Server kind of


applications.


Thank you.
Regards
Sandhya R

Re: [sqlite] reg:sqlite usage

[John Stanton]

Sqlite is a wonderful tool.  We use it with great success embedded in a 
custom application server for web applications, embedded in CGI 
processes and in some industrial process control applications.  It is 
robust and very simple to use, and since it places all tables in one 
file, very easy to maintain.


sandhya wrote:

ya..Thank you ver much John.Right now i am using Postgresql only.But just i
want to find out the information abt Sqlite as i heard that it is having
very small footprint and its good for Embedded systems app.
Now i got it,Thanks a lot.

-Sandhya

- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 19, 2006 5:53 PM
Subject: Re: [sqlite] reg:sqlite usage




Sandhya,

You use Sqlite the same way you use a file in your application.  You
link in the runtime library containing the file handling API when you
create your executable.  Sqlite gives an embedded application the
capability of using SQL for data management.

If you are building a client server model you might want to look at
using something like PostgreSQL or Mysql which are DBMS servers.  There
is no Sqlite server.

Sqlite is used as an embedded DBMS in programs such as the Firefox
browser, LCC IDE and similar.

sandhya wrote:


You link Sqlite into your application...May i know what it mean?
Please explain me.
Also you wanna want to say that we shouldn't use this in Client /Server
applications.Like,Connecting to the Sqlite server through the


application


and performing all the operations through(application) it and updating


the


server.
Really i am totally confused with this...If you don't mind can you


just


explain me where we can use this SQLITE.pls

Thank you,
Sandhya



- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Wednesday, July 19, 2006 5:10 PM
Subject: Re: [sqlite] reg:sqlite usage





Sandhya,

You have not grasped the concept of Sqlite.  It is a RDBMS LIBRARY, not
a server.  You link Sqlite into your application.  As Dr Hipp points out
it it an alternative to fopen, not Oracle.

sandhya wrote:



Hi,
I am very new to SQLITE.I have downloaded and installed Sqlite 3 in


my


windows system.I built lib and dll too.
I tried the sample given in the documetation.The connection info i have
given the DB name, the table name and the query.But the database name


what



ever i am giving it is getting stored in my current application.Will it


be



like that.
Is there any interface to see the tables i have created in using the


sample



given there?
Where can i manually enter the queries?
Can i perform Client-server application kind of a thing.
I mean,With the available API , i will be writing the coding and which


will



perform all the operations mentined like creating table,DB,inserting


etc


etc.Which should update in server directly.If i want to do so.Where


i



need to mention the IP of my server.
Please guide me how to proceed for Client/Server kind of applications.

Thank you.
Regards
Sandhya R