Re: [sqlite] Cross-database JOINs

[Jay Sprenkle]

This is related to my last message.  I'm trying to figure out how to do
something and maybe someone else has already figured this out.

I'm using the same code with both SQLite and MySQL by having
separate implementations of the database primitives.  I have already
written lots of queries that JOIN tables from multiple databases using
the MySQL syntax of database.table, and I'd like to be able to do
the same with SQLite.

It looks like the way to do this is to use ATTACH DATABASE.  However,
for performance reasons, I've enclosed all the queries in large
BEGIN TRANSACTION / COMMIT TRANSACTION blocks (these do
nothing in the MySQL implementation).  Since you can't do an ATTACH
DATABASE inside a transaction, I think I have these options:

1. Make the restriction that all the ATTACHes have to be done outside
the transactions.

2. Put the tables all in the same database for SQLite.

3. At the point the cross-database JOIN is needed, force an COMMIT TRANSACTION,
then do the ATTACH DATABASE, then do another BEGIN TRANSACTION.

These all have varying drawbacks.  Has anyone solved this problem a
different, better, way?


Nothing comes to mind, other than what you've already suggested.
Were you trying to get more performance by putting the tables on
different media?
Putting them all in the same database sounds easiest without knowing
your design.


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

Cthulhu Bucks!
http://www.cthulhubucks.com

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

Re: [sqlite] Bug in SQlite ?

[Jay Sprenkle]

On 9/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I am seeing an assert crash on my system here. I think it is caused by the
following code,

--select.c  (line 88)--

/*
** Delete the given Select structure and all of its substructures.
*/
void sqlite3SelectDelete(Select *p){
  if( p ){
clearSelect(p);
sqliteFree(p);
  }
}
--

I think it should be:

if ( *p )


Why do you think so? The other way seems a good way to check for a non null
pointer. "*p" is a structure. What does testing a structure for non-zero do?

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

Re: [sqlite] How to find errors ?

[drh]

Martin Alfredsson <[EMAIL PROTECTED]> wrote:
> 
> My questions are what will cause the "database is locked"
> 
> Also what is "library routine called out of sequence" ?

I have just now put together wiki pages that describe these errors.
See

   http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
   http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOutOfSequence

Other readers of this mailing list are encouraged to review and
extend my remarks on those pages.
--
D. Richard Hipp   <[EMAIL PROTECTED]>


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

[sqlite] Bug in SQlite ?

[Mario . Hebert]

I am seeing an assert crash on my system here. I think it is caused by the 
following code, 

--select.c  (line 88)--

/*
** Delete the given Select structure and all of its substructures.
*/
void sqlite3SelectDelete(Select *p){
  if( p ){
clearSelect(p);
sqliteFree(p);
  }
}
--

I think it should be:

if ( *p ) 



Mario Hebert 
Legerity

Re: [sqlite] SQLite vs MS Access

[Jay Sprenkle]

On 9/6/06, Allan, Mark <[EMAIL PROTECTED]> wrote:

Hi,


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



I've repeatedly seen database corruption with Access in multiuser mode
on network drives.
I would highly NOT recommend using it in that environment!


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

Cthulhu Bucks!
http://www.cthulhubucks.com

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

[sqlite] How to find errors ?

[Martin Alfredsson]

Hi !

I have an Windows application using sqlite that
gives me problem at a customer.

The customer lives far from me and he refuses
to have an internet connection at his office.

I try to log all SELECT/UPDATE/INSERT/DELETE
and I sometimes gets "database is locked".

I also get some "library routine called out of sequence".

My questions are what will cause the "database is locked"
- Is it enough that the table is opened in a SELECT ?
- How can I ensure that any other UPDATE/INSERT/DELETE
 is not locking the database, do I need transactions everywhere ?
 I only use one place in my code that does all sqlite3_exec() calls.


Also what is "library routine called out of sequence" ?
- I assume a sqlite3_exec() do not need any "closing"
- so its the sqlite3_prepare() or sqlite3_step() that misses a 
sqlite3_finalize() ?

- Any suggestion how to debug this ?

Any help would be great !!


John Martin Alfredsson
(mailATjmaDOTse)

Re: [sqlite] Prepared statements and temp ffiles

[A. Klitzing]

Now if you follow this strategy, notice that you never need
to keep pending read operations around.  The original query
builds the temp table in a single sqlite3_step() call.  To
access the results, you prepare a statement like this:

 SELECT * FROM result_set_1 WHERE rowid=?



Of course you don't need any pending cursors. But the problem is that 
OOo will call many functions for only one row to get informations. I 
tested that way and I got a 10 times slower run duration with that. If I 
can use an open stmt it will be fast because I don't need to run SELECT 
more than once for one cursor-movement in the resultset.


If we have a table with 10.000 rows, 5 columns and one column is a 
primary key.
OOo will SELECT * to get primary keys. After that it will use new SELECT 
for EVERY single row to get the type and the content. And of course it 
will get the content and type for every cell.


So you will have 50.001 SELECT-stmt only to get same information because 
I can't remember my cursor-position.



Regards,
André Klitzing



signature.asc
Description: OpenPGP digital signature

Re: [sqlite] Prepared statements and temp ffiles

[drh]

"G. Roderick Singleton" <[EMAIL PROTECTED]> wrote:
> The following issue, http://www.sqlite.org/cvstrac/tktview?tn=1959 has
> proved a bit of a stopper with the OpenOffice.org driver. I recall a
> discussion on prepared statements and how to manage them but cannot find
> the details. If I remember correctly, there is a way to handle temps via
> prepared statements but need some pointers, please.
>

The issue with ticket #1959 is that SQLite does not allow
you to DROP a table while simultaneously reading from a
different table in the database.  This is to prevent a DROP
from deleting a table or index out from under a reader.
I am disinclined to relax the constraint.

Notice, however, that you do not need this capability in order
to build a driver for OpenOffice.  

For the benefit of all readers, I will recap the situation.
The OpenOffice database interface wants to use random
access read/write cursors on queries.  So, for example, if
you have a query like:

   SELECT * FROM t1;

You would have a cursor into the result set which you could
move to any arbitrary entry in the result set.  And you could
go back and update entries as you moved about.

SQLite does not support random-access cursors.  It would
be easy enough to add cursor support for simple queries like
the one shown above, but cursor support becomes more
troublesome as the complexity of the query increases. 
Imagine trying to randomly address the result set of
this query:

   SELECT eqptid, roomid 
 FROM eqpt
WHERE typeid IN (
  SELECT typeid FROM typespec
WHERE attrid=(
  SELECT attrid FROM attribute WHERE name='autoactuate'
)
AND value=1
  INTERSECT
  SELECT typeid FROM typespec
WHERE attrid=(
  SELECT attrid FROM attribute WHERE name='algorithm'
)
AND value IN ('smokealarm','wetbulb')
)

The only way I know to provide some approximation of random
access cursors for queries like the above is to store the
complete result of the query in a temporary table.  You can 
always do so by prepending:

 CREATE TEMP TABLE result_set_1 AS

in front of the text of the query.  Then you can move about
inside the temporary table looking at results all you want.
Writing back to the original table is still a problem.  I
think you have to solve that on a case-by-case basis.  But
at least here you have random access to the result set.

Now if you follow this strategy, notice that you never need
to keep pending read operations around.  The original query
builds the temp table in a single sqlite3_step() call.  To
access the results, you prepare a statement like this:

 SELECT * FROM result_set_1 WHERE rowid=?

To get the 5th row, bind the value 5 to the ? parameter and
run the prepared statement.  Copy the results into variables 
and reset the statement.  To get the 6th row, bind the value 6 
and repeat. Notice that you never keep any query open for 
longer than a single call sqlite3_step().  So there are never
any pending reads laying around to interfere with DROP operations.

--
D. Richard Hipp   <[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]
-

[sqlite] Prepared statements and temp ffiles

[G. Roderick Singleton]

The following issue, http://www.sqlite.org/cvstrac/tktview?tn=1959 has
proved a bit of a stopper with the OpenOffice.org driver. I recall a
discussion on prepared statements and how to manage them but cannot find
the details. If I remember correctly, there is a way to handle temps via
prepared statements but need some pointers, please.
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech


smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] SQLite vs MS Access

[Allan Miller]

Hi Mark:

We briefly considered using JET (the Microsoft database engine for Access), but
went with SQLite in the end for a few reasons:

1. portability was important to us
2. open source with lots of applications (seems very well supported)
3. it seems to handle large databases very well

One of our deciding factors was that POPFile switched to SQLite and its author
was very pleased with it.

Also, if you ever have to look at the source code, it's a real pleasure to read,
compared to many open-source projects I've seen.

If you really need tight integration with Microsoft Office applications, you'll 
probably
be happier with JET, but otherwise, I think you'll find SQLite will exceed your 
expectations.

The only "weak point" of SQLite is its support for multiple processes accessing
the same database.  That leaves something to be desired, but to be fair, that's 
not
really what it was designed for.  JET isn't exactly stellar in that department 
either.

One thing to keep in mind is that if you write your own interface layer to the 
database
and make it fairly generic, and try to stay away from SQL extensions, you can 
minimize
the effort needed to switch to another database should that become necessary.

Also, we've never used it, but I know that there is an ODBC driver for SQLite, 
so
that may satisfy your needs for using the Access front-end, integrating with 
other
applications, etc.

Allan Miller


- Original Message -
From: "Allan, Mark" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, September 06, 2006 1:05 PM
Subject: [sqlite] SQLite vs MS Access


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] SQLite vs MS Access

[w b]

Mark,
  
  If you looking for accessibility to your SQLITE DB from within other  windows 
applications (That support ODBC)  then there is an ODBC  driver for SQLITE  
which is probably the most common manner to  access databases.
  
  Take a look at the following link. 
  
  http://www.ch-werner.de/sqliteodbc/
  
  Cheers
  
  Wayne

P Kishor <[EMAIL PROTECTED]> wrote:  On 9/6/06, 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.
>

well, you could write your application in a platform-neutral language,
and it will be portable everywhere. You could choose any of the
popular ones (Perl, Python, Tcl).

If you desire a gui, you could write a web application, and your gui
framework -- the browser -- would be automatically  pre-installed on
every personal (and not so personal) computer on the planet.

Or, you could cleanly decouple your backend from the gui, and rewrite
only the gui part in the OS-specific framework (beautiful Cocoa with
its SQLite-based core-data is already present on every Mac OS X 10.4.x
onward, and whatever it is that is used on PCs and non-Mac Unix).

-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/

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

Re: [sqlite] SQLite vs MS Access

[Clay Dowling]

What level of access is needed from outside of the application?  There are
nice GUIs available for SQLite that people could use.  There is in theory
ODBC access as well, although my experience with ODBC and SQlite was not
especially happy.

Clay

Allan, Mark said:
> 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.
>


-- 
Simple Content Management
http://www.ceamus.com


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

Re: [sqlite] SQLite vs MS Access

[P Kishor]

On 9/6/06, Allan, Mark <[EMAIL PROTECTED]> 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.



well, you could write your application in a platform-neutral language,
and it will be portable everywhere. You could choose any of the
popular ones (Perl, Python, Tcl).

If you desire a gui, you could write a web application, and your gui
framework -- the browser -- would be automatically  pre-installed on
every personal (and not so personal) computer on the planet.

Or, you could cleanly decouple your backend from the gui, and rewrite
only the gui part in the OS-specific framework (beautiful Cocoa with
its SQLite-based core-data is already present on every Mac OS X 10.4.x
onward, and whatever it is that is used on PCs and non-Mac Unix).

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.ies.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/

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

[sqlite] SQLite vs MS Access

[Allan, Mark]

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.

[sqlite] Cross-database JOINs

[Allan Miller]

Hi -

This is related to my last message.  I'm trying to figure out how to do
something and maybe someone else has already figured this out.

I'm using the same code with both SQLite and MySQL by having
separate implementations of the database primitives.  I have already
written lots of queries that JOIN tables from multiple databases using
the MySQL syntax of database.table, and I'd like to be able to do
the same with SQLite.

It looks like the way to do this is to use ATTACH DATABASE.  However,
for performance reasons, I've enclosed all the queries in large
BEGIN TRANSACTION / COMMIT TRANSACTION blocks (these do
nothing in the MySQL implementation).  Since you can't do an ATTACH
DATABASE inside a transaction, I think I have these options:

1. Make the restriction that all the ATTACHes have to be done outside
the transactions.

2. Put the tables all in the same database for SQLite.

3. At the point the cross-database JOIN is needed, force an COMMIT TRANSACTION,
then do the ATTACH DATABASE, then do another BEGIN TRANSACTION.

These all have varying drawbacks.  Has anyone solved this problem a
different, better, way?

Thanks!

Allan Miller





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

[sqlite] ATTACH DATABASE documentation

[Allan Miller]

Hi,

Just a helpful suggestion -- the documentation for the ATTACH DATABASE
command should indicate that it can't be used within a transaction.  This
turns out to be true, but unless I missed something, the only way you find
out about it is when you run into the error that is generated at runtime.

Thanks!

Allan Miller





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

RE: [sqlite] Stripping a newline character

[Rob Richardson]

Please forgive my idiocy.  I was more tired than I thought.  First, I
posted this message to the wrong mailing list (I wanted a Python list),
and second, I made a dumb, silly assumption about how the method worked.

RobR

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

Re: [sqlite] Best way to compare two databases

[Juan Perez]

Thanks to all for the answers.
I will analize then and work in it...

2006/9/5, John Stanton <[EMAIL PROTECTED]>:

You could use brute force and ignorance.  Keep a master copy of your
database in SQL form and do a diff on the SQL form of the new DB, then
export the changes as SQL to update the remote DBs.  You may have
deletions and update and would need to take them into account when you
are doing the diff.

Juan Perez wrote:
> 2006/9/5, JP <[EMAIL PROTECTED]>:
>
>> Assumming that the clients will NOT change the original database, I can
>> think of 2 methods:
>>
>> 1. Whole DB: drop all indices, then vacuum, then zip, then propagate. On
>> the clients (suscribers), unzip, re-create all indices.
>>
>> 2. Incremental, add a field to stamp the date on all your records, then
>> export to a separate database (zip it).  On clients, apply the changes.
>>
>> 500k records don't say much - how big (in MB) is your DB?  I can also
>> suggesst changing just the FTP site to another ISP who has unlimited (or
>> a very large GB monthly limit).
>
>
>   The database is downloaded zipped yet. The database Zipped has 100
> MB. The problem is that some commercials uses RTB connections, because
> there are in route. Download of 100 MB is too long...
>
>>
>> jp.
>>
>> Juan Perez wrote:
>> > (excuse me for the other mail, i clicked accidentally the button...)
>> >
>> > Hi:
>> >
>> > I think that i explained me bad.
>> > In my work i do next until now:
>> >
>> > Phase 1: I generate automatically a database from a CRM
>> > Phase 2: I put the database in the FTP for the commercials of the
>> > enterprise. They are located in different parts of the country.
>> > Phase 2: The commercials uses my applicaction with the database to
>> work.
>> >
>> > The problem is that now, the datasase is too big (and we pay the FTP
>> > to an ISP for the used size and the consumed wide of band). So, i now
>> > want to change the process to:
>> >
>> > Phase 1: I generate automatically a database from a CRM
>> >  Phase 2: As I already have the previous database, i will generate a
>> > diff file in the format i explained in the previous mail.
>> > Phase 3: I put the little diff file in the FTP ant the commercials
>> > downloades it..
>> > Phase 4: The commercials brings up to date the database using my
>> > application (it needs to be changed to do it).
>> > Phase 5: The commercials can use my applicaction with the new database
>> > to work.
>> >
>> > So, the question is ¿how to do, in the best way, the new phase 2?
>> >
>> > 2006/9/4, Juan Perez <[EMAIL PROTECTED]>:
>> >> Hi:
>> >>
>> >>   I think that i explained me bad.
>> >>   In my work i do next work:
>> >>
>> >>   I generate automatically a database from a CRM
>> >>
>> >> 2006/9/4, Paul Smith <[EMAIL PROTECTED]>:
>> >> > At 16:48 04/09/2006, you wrote:
>> >> > >Hi all:
>> >> > >
>> >> > >  I have developed a program that uses a sqlite database.
>> >> > >  Until now the users downloaded an entire new version  of the
>> >> > >database weekly from the FTP server.
>> >> > >  But now the database is too big (about 500.000 records) and i
>> want
>> >> > >to make a database actualization system.
>> >> > >  So, what is the best way  (having the old database and the new
>> one)
>> >> > >to obtain a file with the differences. Something like this:
>> >> >
>> >> > Hmm, I don't think I'd do it that way. If you do that, then you need
>> >> > to have a copy of the old & new database to compare.
>> >> >
>> >> > One way around it is to have a 'journal' table which just contains
>> >> > all the SQL queries which have been actioned (you have to take care
>> >> > if you use transactions) along with an incrementing serial number.
>> >> > Then, the user's software can say 'I have all journal entries up to
>> >> > 252376', and then you can just given them all the journal entries
>> >> > after that number, and they can run the SQL on their end. which will
>> >> give.
>> >> >
>> >> > You can make your routine which modifies the database just keep a
>> >> > copy of the SQL used whenever the action succeeds, and store that in
>> >> > the Journal table.
>> >> >
>> >> >
>> >> >
>> >> > PaulVPOP3 - Internet Email
>> Server/Gateway
>> >> > [EMAIL PROTECTED]  http://www.pscs.co.uk/
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> -
>>
>> >>
>> >> > To unsubscribe, send email to [EMAIL PROTECTED]
>> >> >
>> >>
>> -
>>
>> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>> -
>>
>> >
>> > To unsubscribe, send email to [EMAIL PROTECTED]
>> >
>> -
>>
>> >
>> >
>> >
>> >
>>
>>
>> -
>>
>> To unsubscribe, send email to [EMAIL 

Re: [sqlite] Database on usbstick

[eWobbuh]

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?


-- 
View this message in context: 
http://www.nabble.com/Database-on-usbstick-tf2219676.html#a6166339
Sent from the SQLite forum at Nabble.com.


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

Re: [sqlite] Enhancing decltype()?

[Sergio 'OKreZ' Agosti]

On 05/set/06, at 21:25, Robert Simpson wrote:

For the last couple months I've been working on the ADO.NET vNext  
provider
for SQLite with support for eSQL, LINQ, etc (aka the Entity  
Framework).

[...]


please don't open a new thread replying to an old message and  
changing the subject, as many email clients (like Apple Mail)  
organize messages in threads by 'In-Reply-To' header, not only by  
subject... thanks :)


---
Sergio 'OKreZ' Agosti
---
icq: 112421063
msn: [EMAIL PROTECTED]
skype:   sergio.agosti
iChat:   sergio.agosti
jabber:  sergio.agosti
---


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

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

[Roger Binns]

Anish Enos Mathew wrote:
> I would like to get a tool which gives me an accurate result.

There isn't one as there are too many variables.  Do you want to count
shared libraries?  The malloc() libraries generally don't return memory
to the operating system except for "larger" chunks (where "larger" is
defined by the library and possibly somewhat tunable).  Some operating
memory map files implicitly, explicitly or both.  Do you want those to
count?  If you use different data, you'll get different memory
fragmentation between runs.  What do you want to count then?

Tools like top and ps show you how the operating system accounts for the
process.  Even then they double count (shared libraries) and do various
different things for memory mapped files.

You never said what your ultimate goal is.  If you want deterministic
memory consumption then SQLite already has that.  See for example the
"PRAGMA cache_size" command (easy case) or the compile time define
SQLITE_ENABLE_MEMORY_MANAGEMENT (harder).  Of course SQLite still can't
control the behaviour of the malloc() or C library.

Roger

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