[sqlite] More WAL questions and concerns.

2010-07-09 Thread A. H. Ongun
A few questions that are my application specific that is an embedded 

I have two databases, the first one is a fixed size configuration database 
all writes are UPDATES, and the database does not get that many writes.  Does 
this mean that because of WAL the database itself will not get updated and 
changes will be residing in WAL for a very long time due to lack of traffic?

The second is an archive database that is written to once a second, and once a 
month (size grows to 335MB), the database is closed, renamed, a template 
database is copied over, and the process starts again.  Does this mean that I 
have to worry about renaming the WAL file as well now?

Thanks in advance.

sqlite-users mailing list

[sqlite] Best Practices

2008-06-10 Thread A. H. Ongun
We have  an application requirement for the following:
1) Maintain an in memory database of real time data.  The main table mimics a 
Modbus register address scheme and once the table is created and records are 
inserted to create the table at startup, all writes are updates for the state 
machine. (app. 1000 records).  Reads are through multiple Modbus TCP/IP 
protocol based devices which result in selects of up to 100 contiguous records 
at a time.
2) At periodic intervals based on events and time intervals a disk (SD card) 
database gets updated (inserts).  This contains system parameters, and various 
different tables.
3)  Occasionally the whole database on disk is written to an external media 
(USB) for backup purposes.
4)  Through a web interface user(s) can export portions of a table by 
specifying type of data (table) and start and end time in csv format. (cgi 
All of the above is implemented currently using a "custom" format, and is 
really a pain to expand and modify.
Items 1 & 2 are fairly straight forward to implement, they would be in the same 
executable as well.
My original thought was to use the sqlite3 shell interface to accomplish 4.  I 
am concerned a bit about items 3 and 4 though in regards to performance issues 
in that I can not afford not to service data requests that could come several 
times a second if database could be locked during operation of 3 and 4.  Size 
of the database can grow to 1-2.4GB.
I am open to any suggestions.
Thanks in advance.

sqlite-users mailing list

[sqlite] Updating same tables in attached databases.

2008-08-03 Thread A. H. Ongun
I have two databases, an in memory main database consisting of a single table, 
and a disk based database consisting of multiple tables one of which is also 
the same table as the one in memory database.

Let's say in memory database table "foo" consists of records 1-4000.  The "foo" 
table in attached database "db2"  consists of records 3000-4000.

All writes are updates after initializing the in-memory database.  What I want 
to do is execute "UPDATE foo set val=xxx, where recordid = ".

Now, I wish to write to both the attached database, and in-memory database when 
recordid  is between 3000-4000.

Is there a way to do this without executing the update statement twice, once 
for foo, and also for db2.foo (which means conditional logic, testing for 
recordid range)?

Thanks in advance.

sqlite-users mailing list

Re: [sqlite] best language match for SQLite?

2008-09-16 Thread A. H. Ongun
Best language to use is the one that is suited for the job.

I use Perl as a gluing language to connect various subsystems together, and as 
a swiss army knife.
C, C++ for embedded development on Linux.
C++ for Windows development.
Forth for embedded development on some specialized hardware.
SQL for Oracle, and MS SQL work.

and Vim to rule them all.  :)


"Message: 1
Date: Tue, 16 Sep 2008 11:50:40 -0400
From: Patrick <[EMAIL PROTECTED]>
Subject: [sqlite] best language match for SQLite?
To: General Discussion of SQLite Database 
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

I am a beginner to intermediate Python Programmer. I can use SQLite with 
it just fine but it is my understanding that relational database and 
object oriented programming our not the perfect marriage.

I was just wondering if anyone had an opinion on the most ideal language 
to use with SQLite?

I love Python but I LOVE SQLite, I would learn another language just to 
use it better-Patrick  "

sqlite-users mailing list

Re: [sqlite] Tedious CSV import question

2009-09-27 Thread A. H. Ongun
I recently had to import an Excel based database into an SQL database.  To 
complicate the matters data was all over the place in each file (3 separate 
tables in a single CSV file), and some numbers had commas (e.g. 8,253.45).

I used the Perl CSV module to read in each file (1500+) line at a time, get rid 
of the commas, and account for empty records/columns, and build the SQL insert 
statement for each table's data, and write all out to a single file of SQL 
statements.  Afterward, just ran the SQL statements against the database.

Using the CSV module to handle the heavy lifting made it trivial.  The most 
challenging part of the whole thing was to figure out the line numbers, and 
column numbers of where the data was, used Vim for that :).


sqlite-users mailing list