[sqlite] Sparse matrix

[T]

Hi all,

OK, this one has me stumped. I've been staring at it too long.

I have a table with a column like this:

Code
a
a
a
a
b
b
c
c
c

I want to output two columns, one showing the Code, another showing  
the count of each code, but with a null wherever it was a repeat,  
like this:


Code   Count
a  4
.
.
.
b  2
.
c  3
.
.

(Note I've used . to show a null)

Any ideas?

I can simply do this to show the count of each, but it doesn't show  
the blank rows:


select Code, count(*) as Count from List group by Code;

Here's the test table:

create table List( Code text );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'a' );
insert into List values( 'b' );
insert into List values( 'b' );
insert into List values( 'c' );
insert into List values( 'c' );
insert into List values( 'c' );

Thanks,
Tom


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

Re: RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[RaghavendraK 70574]

Hi,

Sqlite uses pread and the performance with ramdisk and disk is the same
on Linux SuSE9.

regards
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: Lee Crain <[EMAIL PROTECTED]>
Date: Wednesday, August 22, 2007 3:31 am
Subject: RE: [sqlite] A Question About Creating and Accessing a SQLite Database 
in a RAM Drive

> Rich,
> 
> We're going to delete and rewrite ~109,369 records in 5 tables 
> every week.
> 
> 
> Hard drives are a minimum of 10,000 times slower than RAM. I'll 
> let you
> know if this process is not a lot faster than writing the records,
> individually, to a hard drive. 
> 
> Lee Crain
> 
> _
> 
> 
> -Original Message-
> From: Rich Shepard [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 21, 2007 11:15 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] A Question About Creating and Accessing a SQLite
> Database in a RAM Drive
> 
> On Tue, 21 Aug 2007, Lee Crain wrote:
> 
> > The approach I planned was a little different than what you 
> proposed.
>   That's fine, Lee.
> 
> > This technique for performing database updates offline and then 
> updating> the original database via a file copy operation has 
> worked very well on
> > hard drives. I am only considering using the RAM drive to 
> improve the
> > speed of the database updates.
> 
>   This was common in the early 1980s when drives and other 
> hardware were
> slow. I've not seen a situation any time recently when this was 
> necessarywith modern hardware and fast memory. When I was 
> capturing real-time data
> (lat/lon from the GPS receiver and depth from the sonar), I'd 
> write both
> to
> memory buffers, then write to disk on a regular basis. This let me use
> slower hardware (compared to the data flow) while writing to disk in
> chunks
> and ensuring that no data were lost.
> 
>   I'm confident that you can tune your database for speed in 
> other ways,
> but
> -- of course -- it's your choice.
> 
> Good luck with it,
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |The Environmental 
> PermittingApplied Ecosystem Services, Inc.|  
> Accelerator(TM) Voice: 503-667-
> 4517  Fax:
> 503-667-8863
> 
> ---
> ---
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> ---
> ---
> 
> 
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

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

Re: RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[RaghavendraK 70574]

Hi,

I have tried with a ram disk and ramfs. Read Performance is same as that of 
disk based system.
How did i evaluate?
wrote simple program
  create file on disk,ramfs
  do pread(...)
In fact sometimes the performance is low when using ramdisk or ramfs or dev/shm.

FileSize: 80MB
read at once: 20bytes
type of read: sequential,meaning in the do loop just increment the offset by 
20bytes.

Pls guide me how pread can be replaced for ramfs which is much more efficent.
Is it possible to avoid this pread system as this consumes significant cpu 
cycles when it switches from user to kernel mode.

regards
ragha


**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: Rich Shepard <[EMAIL PROTECTED]>
Date: Wednesday, August 22, 2007 1:14 am
Subject: RE: [sqlite] A Question About Creating and Accessing a SQLite Database 
in a RAM Drive

> On Tue, 21 Aug 2007, Lee Crain wrote:
> 
> > The approach I planned was a little different than what you 
> proposed.
>   That's fine, Lee.
> 
> > This technique for performing database updates offline and then 
> updating> the original database via a file copy operation has 
> worked very well on
> > hard drives. I am only considering using the RAM drive to 
> improve the
> > speed of the database updates.
> 
>   This was common in the early 1980s when drives and other 
> hardware were
> slow. I've not seen a situation any time recently when this was 
> necessarywith modern hardware and fast memory. When I was 
> capturing real-time data
> (lat/lon from the GPS receiver and depth from the sonar), I'd 
> write both to
> memory buffers, then write to disk on a regular basis. This let me use
> slower hardware (compared to the data flow) while writing to disk 
> in chunks
> and ensuring that no data were lost.
> 
>   I'm confident that you can tune your database for speed in 
> other ways, but
> -- of course -- it's your choice.
> 
> Good luck with it,
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |The Environmental 
> PermittingApplied Ecosystem Services, Inc.|  
> Accelerator(TM) Voice: 503-667-
> 4517  Fax: 503-667-8863
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

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

[sqlite] Re: Declaring variables

[Igor Tandetnik]

Shilpa Sheoran <[EMAIL PROTECTED]>
wrote: 

Does SQLite support declaring/using local variables in  a Trigger?


No.


If not what is the alternative?

CREATE TRIGGER trig1 AFTER INSERT ON table1
BEGIN
DECLARE num INTEGER;
SELECT COUNT(id) INTO num FROM table1;

INSERT INTO table2 (count) VALUES (num);
END;


insert into table2(count)
select cound(id) from table1;

Igor Tandetnik

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

Re: [sqlite] Declaring variables

[John Stanton]

Use a sub-select.

Shilpa Sheoran wrote:

Does SQLite support declaring/using local variables in  a Trigger?
If not what is the alternative?

CREATE TRIGGER trig1 AFTER INSERT ON table1
BEGIN
DECLARE num INTEGER;
SELECT COUNT(id) INTO num FROM table1;

INSERT INTO table2 (count) VALUES (num);
END;


Thanks
Shilpa

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




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

[sqlite] Declaring variables

[Shilpa Sheoran]

Does SQLite support declaring/using local variables in  a Trigger?
If not what is the alternative?

CREATE TRIGGER trig1 AFTER INSERT ON table1
BEGIN
DECLARE num INTEGER;
SELECT COUNT(id) INTO num FROM table1;

INSERT INTO table2 (count) VALUES (num);
END;


Thanks
Shilpa

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

Re: [sqlite] synchronous=OFF on multithreads

[drh]

=?ISO-8859-1?Q?Daniel_=D6nnerby?= <[EMAIL PROTECTED]> wrote:
> Hi all!
> 
> I have a multithreaded application where every thread (almost) has it's 
> own sqlite connection to the same database.
> The database is only accessed by this application and the application 
> always run on the same computer as the database.
> My question:
> Is it "safe" to set "PRAGMA synchronous=OFF"?
> 
> The database is not critical, so I'm not that worried if the database 
> gets corrupted on powerfailure. But the important thing is that, when 
> INSERTing in one thread, it should be able to be read by another thread 
> when transaction is done.
> 

Yes.  PRAGMA synchronous=ON only prevents damage that results
from power failures and/or operating system crashes.  Application
program crashes will not corrupt the database even if
PRAGMA synchronous=OFF.
--
D. Richard Hipp <[EMAIL PROTECTED]>


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

[sqlite] synchronous=OFF on multithreads

[Daniel Önnerby]

Hi all!

I have a multithreaded application where every thread (almost) has it's 
own sqlite connection to the same database.
The database is only accessed by this application and the application 
always run on the same computer as the database.

My question:
Is it "safe" to set "PRAGMA synchronous=OFF"?

The database is not critical, so I'm not that worried if the database 
gets corrupted on powerfailure. But the important thing is that, when 
INSERTing in one thread, it should be able to be read by another thread 
when transaction is done.


Best regards
Daniel  


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

Re: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[John Stanton]

Sqlite caches its data using available memory.  Using a memory resident 
database will consequently give you little advantage.  If you dod not 
require the psersistence of disk storage you can relax the ACID 
environment by using the non-synchronous pragma and get the best of both 
wolrds, memory resident speed and disk storage.


Lee Crain wrote:

Rich,

We're going to delete and rewrite ~109,369 records in 5 tables every week.


Hard drives are a minimum of 10,000 times slower than RAM. I'll let you
know if this process is not a lot faster than writing the records,
individually, to a hard drive. 


Lee Crain

_


-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 21, 2007 11:15 AM

To: sqlite-users@sqlite.org
Subject: RE: [sqlite] A Question About Creating and Accessing a SQLite
Database in a RAM Drive

On Tue, 21 Aug 2007, Lee Crain wrote:



The approach I planned was a little different than what you proposed.



   That's fine, Lee.



This technique for performing database updates offline and then updating
the original database via a file copy operation has worked very well on
hard drives. I am only considering using the RAM drive to improve the
speed of the database updates.



   This was common in the early 1980s when drives and other hardware were
slow. I've not seen a situation any time recently when this was necessary
with modern hardware and fast memory. When I was capturing real-time data
(lat/lon from the GPS receiver and depth from the sonar), I'd write both
to
memory buffers, then write to disk on a regular basis. This let me use
slower hardware (compared to the data flow) while writing to disk in
chunks
and ensuring that no data were lost.

   I'm confident that you can tune your database for speed in other ways,
but
-- of course -- it's your choice.

Good luck with it,

Rich




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

Re: [sqlite] Problems Getting Started

[John Stanton]

Sqlite creates an empty DB if your pathname does not point to one. 
Sqlite just passes the pathname you use in the sqlite3_open call to the 
underlying OS API.


PokerAce wrote:

I thought of that.  I did a search of my harddrive and it resulted in no
duplicate files.  In my code, I check if the file exists before trying to
open it.  Also, I debugged the application stepping through it line by line
and the directory path is fine.  Is there a problem in SQLite 3.4.2 with
long file names?


On 8/21/07, John Stanton <[EMAIL PROTECTED]> wrote:


PokerAce wrote:


I'm using SQLite version 3.4.2 and for some reason, I cannot read from


any


SQLite databases.

When I create the database, all the create and insert statements execute
with no problems, and by using SQLite Database Browser, I can see that


the


tables are there and that they are populated.  However, whenever I try


to


run a select statement, I get the error "no such table: ".
Again, looking in the browser, the table is there, it's just not visible


to


my application.

I did some investigating and found that a "select * from sqlite_master"
returns 0 rows.  However, doing that in the browser, once again, returns


all


the information you'd expect.  I've spent hours searching for the


solution


to this, stepping through each line of code, but everything seems to be


in


proper order.  I've even tried connecting to SQLite databases created by
other applications and get the same result when selecting from the
sqlite_master table.

Here's my connection code:

int sqlErr = sqlite3_open(filename, _conn);
if( sqlErr != SQLITE_OK ) {
   return (false);
}


Here is my prepare code:

sqlite3_stmt* sqlStmt;
const char* sqlTail = 0;

if( sqlite3_prepare(m_conn, query, queryLength, , ) !=
SQLITE_OK ) {
   return (false);
}

Here is my step code:

bool loop = true;
while(loop) {
   switch( sqlite3_step(rs) )
   {
   case SQLITE_DONE:
   loop = false;
   break;

   case SQLITE_ROW:
   // process row stuff here
   break;

   case SQLITE_ERROR:
   // handle...
   loop = false;
   break;

   case SQLITE_MISUSE:
   // handle...
   loop = false;
   break;
   }
}

I've cut some unnecessary code from the snippets, but that's the


important


stuff.  If anyone has any idea what is going on here, please let me


know.


Josh



Your filename is not the correct pathname for your existing database and
your program is creating a new, empty database.


-
To unsubscribe, send email to [EMAIL PROTECTED]

-








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

Re: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[Scott Hess]

If you're already willing to use a ram drive, you should benchmark
SQLite against a disk-backed file, with 'PRAGMA synchronous = off;'.
For most modern operating systems, this shouldn't provide
significantly different performance than using a RAM disk.

Note that you said 'Data persistence is not required.'  My suggestion
would be to treat such a database as described above exactly as you'd
treat a database on a RAM disk.  With synchronous off, the OS is free
to reorder things pretty arbitrarily, and data may not be written out
for a surprisingly long period, and it may not be written in any
internally-consistent order.

-scott


On 8/21/07, Lee Crain <[EMAIL PROTECTED]> wrote:
> Rich,
>
> We're going to delete and rewrite ~109,369 records in 5 tables every week.
>
>
> Hard drives are a minimum of 10,000 times slower than RAM. I'll let you
> know if this process is not a lot faster than writing the records,
> individually, to a hard drive.
>
> Lee Crain
>
> _
>
>
> -Original Message-
> From: Rich Shepard [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 21, 2007 11:15 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] A Question About Creating and Accessing a SQLite
> Database in a RAM Drive
>
> On Tue, 21 Aug 2007, Lee Crain wrote:
>
> > The approach I planned was a little different than what you proposed.
>
>That's fine, Lee.
>
> > This technique for performing database updates offline and then updating
> > the original database via a file copy operation has worked very well on
> > hard drives. I am only considering using the RAM drive to improve the
> > speed of the database updates.
>
>This was common in the early 1980s when drives and other hardware were
> slow. I've not seen a situation any time recently when this was necessary
> with modern hardware and fast memory. When I was capturing real-time data
> (lat/lon from the GPS receiver and depth from the sonar), I'd write both
> to
> memory buffers, then write to disk on a regular basis. This let me use
> slower hardware (compared to the data flow) while writing to disk in
> chunks
> and ensuring that no data were lost.
>
>I'm confident that you can tune your database for speed in other ways,
> but
> -- of course -- it's your choice.
>
> Good luck with it,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D.   |The Environmental Permitting
> Applied Ecosystem Services, Inc.|  Accelerator(TM)
>  Voice: 503-667-4517  Fax:
> 503-667-8863
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[Lee Crain]

Rich,

We're going to delete and rewrite ~109,369 records in 5 tables every week.


Hard drives are a minimum of 10,000 times slower than RAM. I'll let you
know if this process is not a lot faster than writing the records,
individually, to a hard drive. 

Lee Crain

_


-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 21, 2007 11:15 AM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] A Question About Creating and Accessing a SQLite
Database in a RAM Drive

On Tue, 21 Aug 2007, Lee Crain wrote:

> The approach I planned was a little different than what you proposed.

   That's fine, Lee.

> This technique for performing database updates offline and then updating
> the original database via a file copy operation has worked very well on
> hard drives. I am only considering using the RAM drive to improve the
> speed of the database updates.

   This was common in the early 1980s when drives and other hardware were
slow. I've not seen a situation any time recently when this was necessary
with modern hardware and fast memory. When I was capturing real-time data
(lat/lon from the GPS receiver and depth from the sonar), I'd write both
to
memory buffers, then write to disk on a regular basis. This let me use
slower hardware (compared to the data flow) while writing to disk in
chunks
and ensuring that no data were lost.

   I'm confident that you can tune your database for speed in other ways,
but
-- of course -- it's your choice.

Good luck with it,

Rich

-- 
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax:
503-667-8863

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



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

Re: [sqlite] Problems Getting Started

[PokerAce]

I guess I should've stepped through again.  Since having the initial
problem, I added addition code, one of which caused the filename to be an
empty string just prior to calling the function.  It's working now.  Sorry
about that and thanks for the help.


On 8/21/07, PokerAce <[EMAIL PROTECTED]> wrote:
>
> I thought of that.  I did a search of my harddrive and it resulted in no
> duplicate files.  In my code, I check if the file exists before trying to
> open it.  Also, I debugged the application stepping through it line by line
> and the directory path is fine.  Is there a problem in SQLite 3.4.2 with
> long file names?
>
>
> On 8/21/07, John Stanton <[EMAIL PROTECTED]> wrote:
> >
> > PokerAce wrote:
> > > I'm using SQLite version 3.4.2 and for some reason, I cannot read from
> > any
> > > SQLite databases.
> > >
> > > When I create the database, all the create and insert statements
> > execute
> > > with no problems, and by using SQLite Database Browser, I can see that
> > the
> > > tables are there and that they are populated.  However, whenever I try
> > to
> > > run a select statement, I get the error "no such table: ".
> > > Again, looking in the browser, the table is there, it's just not
> > visible to
> > > my application.
> > >
> > > I did some investigating and found that a "select * from
> > sqlite_master"
> > > returns 0 rows.  However, doing that in the browser, once again,
> > returns all
> > > the information you'd expect.  I've spent hours searching for the
> > solution
> > > to this, stepping through each line of code, but everything seems to
> > be in
> > > proper order.  I've even tried connecting to SQLite databases created
> > by
> > > other applications and get the same result when selecting from the
> > > sqlite_master table.
> > >
> > > Here's my connection code:
> > >
> > > int sqlErr = sqlite3_open(filename, _conn);
> > > if( sqlErr != SQLITE_OK ) {
> > > return (false);
> > > }
> > >
> > >
> > > Here is my prepare code:
> > >
> > > sqlite3_stmt* sqlStmt;
> > > const char* sqlTail = 0;
> > >
> > > if( sqlite3_prepare(m_conn, query, queryLength, , ) !=
> >
> > > SQLITE_OK ) {
> > > return (false);
> > > }
> > >
> > > Here is my step code:
> > >
> > > bool loop = true;
> > > while(loop) {
> > > switch( sqlite3_step(rs) )
> > > {
> > > case SQLITE_DONE:
> > > loop = false;
> > > break;
> > >
> > > case SQLITE_ROW:
> > > // process row stuff here
> > > break;
> > >
> > > case SQLITE_ERROR:
> > > // handle...
> > > loop = false;
> > > break;
> > >
> > > case SQLITE_MISUSE:
> > > // handle...
> > > loop = false;
> > > break;
> > > }
> > > }
> > >
> > > I've cut some unnecessary code from the snippets, but that's the
> > important
> > > stuff.  If anyone has any idea what is going on here, please let me
> > know.
> > >
> > > Josh
> > >
> > Your filename is not the correct pathname for your existing database and
> > your program is creating a new, empty database.
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
> >
>

Re: [sqlite] Problems Getting Started

[PokerAce]

I thought of that.  I did a search of my harddrive and it resulted in no
duplicate files.  In my code, I check if the file exists before trying to
open it.  Also, I debugged the application stepping through it line by line
and the directory path is fine.  Is there a problem in SQLite 3.4.2 with
long file names?


On 8/21/07, John Stanton <[EMAIL PROTECTED]> wrote:
>
> PokerAce wrote:
> > I'm using SQLite version 3.4.2 and for some reason, I cannot read from
> any
> > SQLite databases.
> >
> > When I create the database, all the create and insert statements execute
> > with no problems, and by using SQLite Database Browser, I can see that
> the
> > tables are there and that they are populated.  However, whenever I try
> to
> > run a select statement, I get the error "no such table: ".
> > Again, looking in the browser, the table is there, it's just not visible
> to
> > my application.
> >
> > I did some investigating and found that a "select * from sqlite_master"
> > returns 0 rows.  However, doing that in the browser, once again, returns
> all
> > the information you'd expect.  I've spent hours searching for the
> solution
> > to this, stepping through each line of code, but everything seems to be
> in
> > proper order.  I've even tried connecting to SQLite databases created by
> > other applications and get the same result when selecting from the
> > sqlite_master table.
> >
> > Here's my connection code:
> >
> > int sqlErr = sqlite3_open(filename, _conn);
> > if( sqlErr != SQLITE_OK ) {
> > return (false);
> > }
> >
> >
> > Here is my prepare code:
> >
> > sqlite3_stmt* sqlStmt;
> > const char* sqlTail = 0;
> >
> > if( sqlite3_prepare(m_conn, query, queryLength, , ) !=
> > SQLITE_OK ) {
> > return (false);
> > }
> >
> > Here is my step code:
> >
> > bool loop = true;
> > while(loop) {
> > switch( sqlite3_step(rs) )
> > {
> > case SQLITE_DONE:
> > loop = false;
> > break;
> >
> > case SQLITE_ROW:
> > // process row stuff here
> > break;
> >
> > case SQLITE_ERROR:
> > // handle...
> > loop = false;
> > break;
> >
> > case SQLITE_MISUSE:
> > // handle...
> > loop = false;
> > break;
> > }
> > }
> >
> > I've cut some unnecessary code from the snippets, but that's the
> important
> > stuff.  If anyone has any idea what is going on here, please let me
> know.
> >
> > Josh
> >
> Your filename is not the correct pathname for your existing database and
> your program is creating a new, empty database.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

Re: [sqlite] example of import/copy for Windows?

[Dwight Ingersoll]

On 8/21/07, Preston, Brian <[EMAIL PROTECTED]> wrote:

<>


> Does anyone have an example of doing an import from a .sql file into an
> existing sqlite3 db?  I've tried:
>
> sqlite3 .import  
>
> sqlite3 .import  | 
>
> sqlite3   .import
>
> sqlite3  |  .import
>
> and various other permutations.  It usually give me an error saying
> 'syntax error at' the start of my 


<>

I'm assuming you're including the greater/less than signs in the above
commands.  These have special significance to the command shell and should
be omitted.  Also, you've reversed the parameters, so assuming that your
using your C: drive, your database is named TEST.DB in a directory called
DATABASE, and that your datafile is called INPUTDATA.SQL in a directory
called DATA, the command should look like:

sqlite3 C:\DATABASE\TEST.DB < C:\DATA\INPUTDATA.SQL

According to what I read, the .dump command includes both the DDL to create
your database schema as well as the data within the tables.  I don't have my
database far enough along to confirm this.

>From reading your message, it appears that you're not familiar with working
at the command line (in your example, it seems your confusing what commands
are available at the command line, and what commands are available once
you've invoked the sqlite3 utility).  I think it would be beneficial if you
read up on the subject on Google, or located a book on the subject.
O'Reilly books has an excellent book on the NT command shell, which hasn't
changed since that release, so it will still apply no matter what Windows OS
you're using.

Re: [sqlite] Problems Getting Started

[Gerry Snyder]

PokerAce wrote:

I'm using SQLite version 3.4.2 and for some reason, I cannot read from any
SQLite databases.
  

Josh,

Usually this symptom means that the Browser and your application are not 
looking at the same file.


Check paths and that sort of thing.

HTH,

Gerry


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

Re: [sqlite] Problems Getting Started

[John Stanton]

PokerAce wrote:

I'm using SQLite version 3.4.2 and for some reason, I cannot read from any
SQLite databases.

When I create the database, all the create and insert statements execute
with no problems, and by using SQLite Database Browser, I can see that the
tables are there and that they are populated.  However, whenever I try to
run a select statement, I get the error "no such table: ".
Again, looking in the browser, the table is there, it's just not visible to
my application.

I did some investigating and found that a "select * from sqlite_master"
returns 0 rows.  However, doing that in the browser, once again, returns all
the information you'd expect.  I've spent hours searching for the solution
to this, stepping through each line of code, but everything seems to be in
proper order.  I've even tried connecting to SQLite databases created by
other applications and get the same result when selecting from the
sqlite_master table.

Here's my connection code:

int sqlErr = sqlite3_open(filename, _conn);
if( sqlErr != SQLITE_OK ) {
return (false);
}


Here is my prepare code:

sqlite3_stmt* sqlStmt;
const char* sqlTail = 0;

if( sqlite3_prepare(m_conn, query, queryLength, , ) !=
SQLITE_OK ) {
return (false);
}

Here is my step code:

bool loop = true;
while(loop) {
switch( sqlite3_step(rs) )
{
case SQLITE_DONE:
loop = false;
break;

case SQLITE_ROW:
// process row stuff here
break;

case SQLITE_ERROR:
// handle...
loop = false;
break;

case SQLITE_MISUSE:
// handle...
loop = false;
break;
}
}

I've cut some unnecessary code from the snippets, but that's the important
stuff.  If anyone has any idea what is going on here, please let me know.

Josh

Your filename is not the correct pathname for your existing database and 
your program is creating a new, empty database.


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

[sqlite] Problems Getting Started

[PokerAce]

I'm using SQLite version 3.4.2 and for some reason, I cannot read from any
SQLite databases.

When I create the database, all the create and insert statements execute
with no problems, and by using SQLite Database Browser, I can see that the
tables are there and that they are populated.  However, whenever I try to
run a select statement, I get the error "no such table: ".
Again, looking in the browser, the table is there, it's just not visible to
my application.

I did some investigating and found that a "select * from sqlite_master"
returns 0 rows.  However, doing that in the browser, once again, returns all
the information you'd expect.  I've spent hours searching for the solution
to this, stepping through each line of code, but everything seems to be in
proper order.  I've even tried connecting to SQLite databases created by
other applications and get the same result when selecting from the
sqlite_master table.

Here's my connection code:

int sqlErr = sqlite3_open(filename, _conn);
if( sqlErr != SQLITE_OK ) {
return (false);
}


Here is my prepare code:

sqlite3_stmt* sqlStmt;
const char* sqlTail = 0;

if( sqlite3_prepare(m_conn, query, queryLength, , ) !=
SQLITE_OK ) {
return (false);
}

Here is my step code:

bool loop = true;
while(loop) {
switch( sqlite3_step(rs) )
{
case SQLITE_DONE:
loop = false;
break;

case SQLITE_ROW:
// process row stuff here
break;

case SQLITE_ERROR:
// handle...
loop = false;
break;

case SQLITE_MISUSE:
// handle...
loop = false;
break;
}
}

I've cut some unnecessary code from the snippets, but that's the important
stuff.  If anyone has any idea what is going on here, please let me know.

Josh

Re: [sqlite] Unique ids for each record

[Dwight Ingersoll]

On 8/20/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:

<>

>
> I want to restrict the Artist range to 100 to 199 so that with the id
> I can make my search fast and also I knew with unique id that I need to
> search for the Artist alone.


<>

Considering the small range of data it appears you want to deal with, SQLite
should be more than capable of quickly returning the results you desire,
especially since it appears that you're using a numeric field as primary
keys.

It would be helpful to understand why you chose this approach, and the
source of your data.  The reason I'm asking is because the project I'm
currently working on is an offline viewer which utilizes the data available
from the FreeDB Organization.

RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[Rich Shepard]

On Tue, 21 Aug 2007, Lee Crain wrote:


The approach I planned was a little different than what you proposed.


  That's fine, Lee.


This technique for performing database updates offline and then updating
the original database via a file copy operation has worked very well on
hard drives. I am only considering using the RAM drive to improve the
speed of the database updates.


  This was common in the early 1980s when drives and other hardware were
slow. I've not seen a situation any time recently when this was necessary
with modern hardware and fast memory. When I was capturing real-time data
(lat/lon from the GPS receiver and depth from the sonar), I'd write both to
memory buffers, then write to disk on a regular basis. This let me use
slower hardware (compared to the data flow) while writing to disk in chunks
and ensuring that no data were lost.

  I'm confident that you can tune your database for speed in other ways, but
-- of course -- it's your choice.

Good luck with it,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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

[sqlite] bigfile test

[Victor Secarin]

Having built sqlite 3.4.2 for Enterprise 3, 4, Fedora 5 and Solaris 8, 
both 32 and 64 bits, I then ran the fulltest on each. As it takes a long 
time and produces a large output, one looks first at the end, and if it 
says "0 errors" one thinks everything is as expected.


By chance, I later discovered the following:


> ./testfixture ../sqlite-3.4.2/test/bigfile.test
bigfile-1.1... Ok
 Unable to create a file larger than 4096 MB. *
Memory leaked: 0 bytes in 0 allocations

soft-heap-limit changed by this script from 0 to
Thread-specific data deallocated properly
0 errors out of 2 tests
Failures on these tests:
==

This happens on all my 32 bit builds. Not on the 64 bit ones.
In order to get large file functionality one has to configure with 
CFLAGS="-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64"


The problem is that "Unable to create a file larger than 4096 MB" is not 
considered a failure, so that people who only see the "0 errors out of 
409660 tests" at the end of the log file will not  know they have the 
problem.

It is really difficult to read the whole log file.
I suggest to
(a) make this a counted error,  or else
(b) add an extra test specially for this problem, or else
(c) categorize the messages, prefixing them with a small number of 
standard words, like "ERROR:" and "WARNING:" and "INFO:", which would 
enable one to use grep or search in an editor. Then  the problem above 
could be a WARNING rather than a counted ERROR.



yours truly,
Victor Secarin

---
This e-mail, including any attached files, may contain confidential and 
privileged information for the sole use of the intended recipient. Any review, 
use, distribution, or disclosure by others is strictly prohibited. If you are 
not the intended recipient (or authorized to receive information for the 
intended recipient), please contact the sender by reply e-mail and delete all 
copies of this message.



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

RE: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[Lee Crain]

Rich,

The approach I planned was a little different than what you proposed.

I've done some experimentation with updating a SQLite database by
overwriting it with an updated copy of the database. It's a simple file
overwrite, it's fast, and the database, with its new data contents, is
fully useable.

My plan:
1. Create a RAM drive.
1. Copy a hard drive based SQLite database from its hard drive to a RAM
drive. 
2. Perform updates to the SQLite database in the RAM drive (for speed). 
3. Copy the RAM based SQLite database back to the hard drive and overwrite
the original SQLite database. 
4. Delete the RAM drive.

This technique for performing database updates offline and then updating
the original database via a file copy operation has worked very well on
hard drives. I am only considering using the RAM drive to improve the
speed of the database updates.

Lee Crain




-Original Message-
From: Rich Shepard [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 20, 2007 5:36 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] A Question About Creating and Accessing a SQLite
Database in a RAM Drive

On Mon, 20 Aug 2007, Lee Crain wrote:

> Is there any reason why this would not be possible?

Lee,

   Not at all. You can create the databse in memory as long as you have
sufficient storage for it. See the web pages for syntax. If you do not
specify a filename when you invoke sqlite3, it's an in-memory database on
which you can perform all SQL operations.

Rich

-- 
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax:
503-667-8863

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



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

Re: [sqlite] example of import/copy for Windows?

[Dennis Cote]

Preston, Brian wrote:

I'm trying to import a .sql file into a db on Windows.  I'm doing a
migration.  I was able to create the .sql file from the old sqlite3 db
with the dump command.  But I cannot figure out the syntax/format of the
import command for Windows.  The examples on the sqlite3 site are for
*nix, and seem to use the 'zcat' command instead of import.  I searched
the archives for this and the only response said to look at the docs for
the 'copy' command, but I couldn't find any documentation for the copy
command.  Or the import command, for that matter.  The .help for .import
only says ".import FILE TABLE" but apparently that's not complete
either.
 
Does anyone have an example of doing an import from a .sql file into an

existing sqlite3 db?  I've tried:
 
sqlite3 .import  
 
sqlite3 .import  | 
 
sqlite3   .import 
 
sqlite3  |  .import 
 
and various other permutations.  It usually give me an error saying

'syntax error at' the start of my 
 

  

Try

   sqlite3 DBfile.db3 The .sql file contains SQL commands that must be executed by sqlite to 
create the database, not data to be imported into the database.


HTH
Dennis Cote


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

[sqlite] example of import/copy for Windows?

[Preston, Brian]

I'm trying to import a .sql file into a db on Windows.  I'm doing a
migration.  I was able to create the .sql file from the old sqlite3 db
with the dump command.  But I cannot figure out the syntax/format of the
import command for Windows.  The examples on the sqlite3 site are for
*nix, and seem to use the 'zcat' command instead of import.  I searched
the archives for this and the only response said to look at the docs for
the 'copy' command, but I couldn't find any documentation for the copy
command.  Or the import command, for that matter.  The .help for .import
only says ".import FILE TABLE" but apparently that's not complete
either.
 
Does anyone have an example of doing an import from a .sql file into an
existing sqlite3 db?  I've tried:
 
sqlite3 .import  
 
sqlite3 .import  | 
 
sqlite3   .import 
 
sqlite3  |  .import 
 
and various other permutations.  It usually give me an error saying
'syntax error at' the start of my 
 
Thanks
 
PS - sorry for the silly disclaimer 
-- 
This e-mail message is intended only for the personal use of the 
recipient(s) named above. If you are not an intended recipient,  
you may not review, copy or distribute this message. If you have  
received this communication in error, please notify First DataBank 
Support ([EMAIL PROTECTED]) immediately by e-mail  
and delete the original message. 
-- 

Re: [sqlite] Unique ids for each record

[John Elrick]

Eric Bohlman wrote:

Sreedhar.a wrote:


SNIP

You need artist identifiers to be unique (you could in fact use artist 
names, since copyright rules require that such names be unique). You 
also need album names to be unique, but only within artist groups 
(you're not going to have the *same* artist release two albums with 
the same name).


I agree with the thrust of your arguments, however, there is no business 
rule stating that the same artist must release albums with unique 
names.  Therefore, Peter Gabriel's example notwithstanding, it is a 
potentially dangerous assumption in any case.



John

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

Re: [sqlite] Looking for a cryptographic library

[Günter Greschenz]

ok, no problem...
as soon as i am at home again from my business trip, i will move the 
sources to my webserver so everybody can download it...

i will post a message with a link then.
nice holidays :-)
gg


[EMAIL PROTECTED] wrote:
Günter:  
 
Thanks for yours quick response.  
 
Really I'm interested in yours BlowFish  sqlite-functions to crypt, 
but in the next days (from 24 to 31) I'm going to have a rest, You 
know, my holiday period :-), so I can drop a letter to you in 
September to know about your functions and some other details.  
 
Thanks again  
 
Adolfo J. Millan  
ZATOR Systems  
  





No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.484 / Virus Database: 269.12.1/962 - Release Date: 20.08.2007 13:08
  

Re: [sqlite] Unique ids for each record

[Markus Hoenicka]

Quoting Eric Bohlman <[EMAIL PROTECTED]>:


(you're not going to have the *same* artist release two albums with the
same name). You need track names to be unique within artists, but not


Except for weird artists like Peter Gabriel. All solo albums released  
between 1977 and 1982 were self-titled and thus carry the same title.


regards,
Markus


--
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de


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

Re: [sqlite] Unique ids for each record

[Eric Bohlman]

Sreedhar.a wrote:

Hi Lokesh,

Thanks for your suggestions.

My proble is assigning a unique record for each record in a table.

The database table name MUSIC.

I have 3 columns Artist Album and Tracks.

Artist1 Album1  Track1
Artist2 Album2  Track2
Artist3 Album2  Track3

I need to assign a unique id value for each item in the row.


Your data model is extremely awkward because you're trying to model 
entities as columns rather than table rows. "Artist," "album" and 
"track" are all distinct entities and should each have their own tables.
You really need four tables: an "artist" table that contains a unique 
identifier for each artist, as well as any attributes that relate to the 
artist (e.g. year formed), an "album" table that contains a unique 
identifier for the album, along with any attributes that describe the 
album (date released, producer, etc.), a "tracks" table that describes 
each track, with a unique identifier and any track-specific information 
(was it done as a video? released as a single?) and, finally, a 
"tracklist" table that pairs up albums with tracks.


You need artist identifiers to be unique (you could in fact use artist 
names, since copyright rules require that such names be unique). You 
also need album names to be unique, but only within artist groups 
(you're not going to have the *same* artist release two albums with the 
same name). You need track names to be unique within artists, but not 
otherwise (quick, name two boybands whose first singles had the same 
names*).


You'll want to use joins to relate the four tables.

* Both the Jackson 5 and N'Sync had first singles titled "I Want You 
Back" Two completely different songs.


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

Re: [sqlite] A Question About Creating and Accessing a SQLite Database in a RAM Drive

[RaghavendraK 70574]

Hi,

But the performance is very bad in comparision with the file based DB,
any suggestion to improve the same.

regards
ragha
**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: Rich Shepard <[EMAIL PROTECTED]>
Date: Tuesday, August 21, 2007 7:35 am
Subject: Re: [sqlite] A Question About Creating and Accessing a SQLite Database 
in a RAM Drive

> On Mon, 20 Aug 2007, Lee Crain wrote:
> 
> > Is there any reason why this would not be possible?
> 
> Lee,
> 
>   Not at all. You can create the databse in memory as long as you 
> havesufficient storage for it. See the web pages for syntax. If 
> you do not
> specify a filename when you invoke sqlite3, it's an in-memory 
> database on
> which you can perform all SQL operations.
> 
> Rich
> 
> -- 
> Richard B. Shepard, Ph.D.   |The Environmental 
> PermittingApplied Ecosystem Services, Inc.|  
> Accelerator(TM) Voice: 503-667-
> 4517  Fax: 503-667-8863
> 
> ---
> --
> To unsubscribe, send email to [EMAIL PROTECTED]
> ---
> --
> 
> 

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

RE: [sqlite] FTS2 Question again Python + .NET

[Andre du Plessis]

Im using Finishar, cant remember where I got it or why I chose it, it
came up when I googled :)

It seems quite complete except for the extensions

-Original Message-
From: Samuel R. Neff [mailto:[EMAIL PROTECTED] 
Sent: 20 August 2007 06:23 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] FTS2 Question again Python + .NET


"
I'm having the same problem with .net, cant find a function which does
this... 
"

Which .NET wrapper are you using?

System.Data.SQLite has FTS2 precompiled.  http://sqlite.phxsoftware.com

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
Flex
based products. Position is in the Washington D.C. metro area. If
interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Andre du Plessis [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 20, 2007 11:50 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] FTS2 Question again Python + .NET




-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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