Re: [sqlite] ChangePassword method problem

[Farhan Husain]

Aah, ok. So, for all the methods that act on the database I should explicitly 
add conn.Close() within the using conn scope?

> From: sql...@mistachkin.com
> To: sqlite-users@sqlite.org
> Date: Sat, 22 Oct 2011 22:04:33 -0700
> Subject: Re: [sqlite] ChangePassword method problem
> 
> 
> One thing that could be a potential issue here is that all connections must
> be closed
> prior to changing the password on the database [except the connection used
> for the
> ChangePassword method call itself].
> 
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ChangePassword method problem

[Joe Mistachkin]

One thing that could be a potential issue here is that all connections must
be closed
prior to changing the password on the database [except the connection used
for the
ChangePassword method call itself].

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tables as ASCII - is it possible?

[Mr. Puneet Kishor]

On Oct 22, 2011, at 11:34 PM, Paul Linehan wrote:

> If I could go with
> a scripting language, it would be Python - vastly superior IMHO
> to Perl - YMMV.


Yup. My mileage does vary.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tables as ASCII - is it possible?

[Paul Linehan]

2011/10/23 Mr. Puneet Kishor :


>> I want to be able to run scripts against my data as well as use SQLite.

> Are you suggesting that you want to treat text data as a SQL data store?


No, I'm suggesting that the SQLite engine be able to have table data
available as text files - table1.txt, table2.txt 

I have to be able to send text files to a machine that doesn't have
SQLite and won't (very very conservative manager! :-( ).


> You might want to look at DBD::CSV [http://search.cpan.org/
> ~hmbrand/DBD-CSV-0.33/lib/DBD/CSV.pm]


Nope, Perl is out also - anyway, I wouldn't use it. If I could go with
a scripting language, it would be Python - vastly superior IMHO
to Perl - YMMV.

It's shell scripting that I want to be able to do - remember my manager?

Thanks for your input.


Paul...


> Puneet Kishor


-- 

lineh...@tcd.ie

Mob: 00 353 86 864 5772
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tables as ASCII - is it possible?

[Paul Linehan]

2011/10/23 Simon Slavin :


>> I want to be able to run scripts against my data as well as use SQLite.

> I recommend you script the sqlite3 shell tool to pipe whatever data you want,
> or to make a text file of it which you can then read:

Looks that this is the way to go alright!


>> If it does not exist, I respectfully suggest that it is worthy of 
>> consideration
>> as a feature - maybe along the lines of MySQL storage engines?

> My guess is that it would slow down SQLite by a factor of three or more.

I don't doubt for a second that the ASCII option would be slower than
the current one - only if the user chose it though.


> Feel free to write it, and contribute your code.


I consider anything more sophisticated than "Hello World" to
be advanced C! ;) Thanks for your input.


Paul...


> Simon.


lineh...@tcd.ie

Mob: 00 353 86 864 5772
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Pavel Ivanov]

> Anyway, the iostat output of my system is
>
> 2011 Oct 22 21:16:36,  load: 0.03,  disk_r:   2676 KB,  disk_w:      0 KB
>
>  UID    PID   PPID CMD              DEVICE  MAJ MIN D            BYTES
>    0      0      0                  ??       14   8              65536
>  503    732    730 sqlite3          ??       14  14 R          1323008
>  503    731    730 sqlite3          ??       14  14 R          1355776
>
> If I understand correctly, the IO load is only 3% when two sqlite3
> processes are running

No, 0.03 is load of your system CPU-wise. I'm sorry, I had a Linux's
iostat output format in mind. You have apparently something else (Mac
OS X?) and its iostat has completely different output.


Pavel


On Sat, Oct 22, 2011 at 11:05 PM, Bo Peng  wrote:
>> It's not only speed in KB/sec that matters. It's also disk system
>> usage as reported by iostat. If it's close to 100% then SQLite can't
>> do any better.
>
> A sad day.
>
> I copied the database to a faster driver with RAID 0, made another
> copy of the database (referred to as DB1), and ran another set of
> tests:
>
> test1: two sequential processes of sqlite count(*) table1 and table 2
> in DB1 --> 7m15s
>
> test2: two concurrent processes of sqlite count(*) table1 and table2
> in DB1 --> 5m22s
>
> test3: four concurrent processes of sqlite count(*) table 1, 2, 3, and
> 4 in DB1 --> 12m58s
>
> test4: two concurrent processes of sqlite count(*) table1 in DB1, and
> table1 in DB2 --> 9m51s.
>
> Although running two or more processes can save some time, the
> performance gain is not that big (tests 2 and 3), splitting the
> database into several smaller ones would not help either (test 4).
>
> Anyway, the iostat output of my system is
>
> 2011 Oct 22 21:16:36,  load: 0.03,  disk_r:   2676 KB,  disk_w:      0 KB
>
>  UID    PID   PPID CMD              DEVICE  MAJ MIN D            BYTES
>    0      0      0                  ??       14   8              65536
>  503    732    730 sqlite3          ??       14  14 R          1323008
>  503    731    730 sqlite3          ??       14  14 R          1355776
>
> If I understand correctly, the IO load is only 3% when two sqlite3
> processes are running, so perhaps I can still tweak sqlite3 to run
> faster. I will also copy the database around and see if other disks
> (SSD?), operating system (linux?), and file systems can provide better
> performance.
>
> Thanks again for all the help,
> Bo
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tables as ASCII - is it possible?

[Mr. Puneet Kishor]

On Oct 22, 2011, at 11:06 PM, Paul Linehan wrote:

> Hi all,
> 
> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
> 
> I want to be able to run scripts against my data as well as use SQLite.
> ..

Are you suggesting that you want to treat text data as a SQL data store? You 
might want to look at DBD::CSV 
[http://search.cpan.org/~hmbrand/DBD-CSV-0.33/lib/DBD/CSV.pm]

--
Puneet Kishor

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Tables as ASCII - is it possible?

[Simon Slavin]

On 23 Oct 2011, at 5:06am, Paul Linehan wrote:

> Is there a way of storing SQLite data (tables) as ASCII text rather
> than as binary data?
> 
> I want to be able to run scripts against my data as well as use SQLite.

I recommend you script the sqlite3 shell tool to pipe whatever data you want, 
or to make a text file of it which you can then read:

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

download it from here:

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

> If it's not available as a "normal" option, is there a patch on the interweb
> somewhere?
> 
> If it does not exist, I respectfully suggest that it is worthy of 
> consideration
> as a feature - maybe along the lines of MySQL storage engines?

My guess is that it would slow down SQLite by a factor of three or more.  Feel 
free to write it, and contribute your code.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Tables as ASCII - is it possible?

[Paul Linehan]

Hi all,

Is there a way of storing SQLite data (tables) as ASCII text rather
than as binary data?

I want to be able to run scripts against my data as well as use SQLite.

If it's not available as a "normal" option, is there a patch on the interweb
somewhere?

If it does not exist, I respectfully suggest that it is worthy of consideration
as a feature - maybe along the lines of MySQL storage engines?

I respect the fact that this is an Open Source project with
a Public Domain licence (i.e. volunteer coders)  and I'm not
demanding anything, merely suggesting that it might be a good idea.


Paul...


-- 

lineh...@tcd.ie

Mob: 00 353 86 864 5772
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ChangePassword method problem

[Farhan Husain]

Sure.
connectionString is in the "Data Source=xxx;Password=xxx;" format.
Here is a sample method that uses the connection string:
public int ExecuteNonQuery(string query){
using(SQLiteConnection conn = new SQLiteConnection(connectionString))   
 {conn.Open();using(SQLiteCommand cmd = new 
SQLiteCommand(query, conn)){int result = 
cmd.ExecuteNonQuery();conn.Close();
return result;}}}
It works perfectly when the database is initially created or subsequently 
accessed.
Here is the changepassword code:
// DOES NOT WORKpublic bool ChangePassword(string newPassword){ 
   try{using (SQLiteConnection conn = new 
SQLiteConnection(connectionString)){
conn.Open();conn.ChangePassword(newPassword); // Also tried 
null as String then another call to changepassword
conn.Close();}}catch (SQLiteException 
e){exceptionMessage = e.Message;
return false;}
return true;}
When a query is executed, it always uses current password. After the above code 
is called, neither the old or the new password work.
One temporary solution I have found is that I just create a new database with 
the desired password and transfer all the contents from the old database to it. 
After that, i simply delete the original database. The problem with this 
process is that the database file creation date changes every time the password 
changes.
Again, it is possible that I am still doing something wrong with the coding but 
at this point I am not sure.


> From: sql...@mistachkin.com
> To: sqlite-users@sqlite.org
> Date: Sat, 22 Oct 2011 20:39:09 -0700
> Subject: Re: [sqlite] ChangePassword method problem
> 
> 
> Farhan Husain wrote:
> >
> > I have tried opening all encrypted databases using both the SetPassword
> method
> > and the password property in the connection string. No matter what
> combination I
> > use, after the password is changed using ChangePassword method, the
> database
> > becomes unreadable using the updated or previous password. 
> > 
> 
> I've added some more unit tests to the test suite to verify that this
> feature works
> as documented and I'm not seeing any issues.  Could you post some simplified
> C# code
> that demonstrates the behavior you are seeing?
> 
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with binding parameters to LIKE

[Navaneeth.K.N]

Hello,

I am trying to use parameters in a LIKE query. I have the following
code which uses Sqlite C/C++ API.

const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%'
ORDER BY freq DESC LIMIT 10;";

int rc = sqlite3_prepare_v2 (db, sql, -1, , NULL);
if ( rc != SQLITE_OK )
 return false;

sqlite3_bind_text ( stmt, 1, data , -1, NULL );

Unfortunaltly, this won't work. Sqlite is executing the statement
successfully, but I am not getting the expected result. When I execute
the same statement after removing parameters it works perfectly.
Something like,

const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%'
ORDER BY freq DESC LIMIT 10;";

It looks like concatentation with parameters is not working for some
reason. To debug the issue, I hooked up sqlite3_trace and
sqlite3_profile and printed the SQL being executed. Unfortunatly,
these routines won't give the SQL with values bound to it.

I am running out of ideas and any help would be great to address the problem.

Thanks
-- 
-n
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Simon Slavin]

On 23 Oct 2011, at 4:05am, Bo Peng wrote:

> If I understand correctly, the IO load is only 3% when two sqlite3
> processes are running, so perhaps I can still tweak sqlite3 to run
> faster. I will also copy the database around and see if other disks
> (SSD?), operating system (linux?), and file systems can provide better
> performance.

Tweaking SQLite probably won't help.  The speed of your task is limited by the 
speed of your hard disk.  You could have 1 process or 10, but they all have to 
get at the database through the hard disk, as Igor wrote.  Adding more 
processes just means more processes queued up to wait until the hard disk 
decides to fetch the bit of the database they want next.

You could speed this task up by splitting your database into two and putting 
each part on a different hard disk, then using two processes to fetch the 
information.  Or you could use triggers to keep the figure you're trying to 
find constantly updated in another table.  Or you could use a faster caching 
database management system.  Or you could just live with it as it is now.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ChangePassword method problem

[Joe Mistachkin]

Farhan Husain wrote:
>
> I have tried opening all encrypted databases using both the SetPassword
method
> and the password property in the connection string. No matter what
combination I
> use, after the password is changed using ChangePassword method, the
database
> becomes unreadable using the updated or previous password. 
> 

I've added some more unit tests to the test suite to verify that this
feature works
as documented and I'm not seeing any issues.  Could you post some simplified
C# code
that demonstrates the behavior you are seeing?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ChangePassword method problem

[Farhan Husain]

Yup. Here's what I have tried doing:
1) Created a new encrypted database with a password2) After opening the 
connection, use ChangePassword to change the password, then close it3) Open the 
connection again using the new password
I have tried opening all encrypted databases using both the SetPassword method 
and the password property in the connection string. No matter what combination 
I use, after the password is changed using ChangePassword method, the database 
becomes unreadable using the updated or previous password. I've even tried 
creating an unencrypted database, then setting a password, then changing it, 
and creating an encrypted database, then removing the password using 
ChangePassword. Basically anytime ChangePassword is called the database becomes 
unreadable. Everything else seems to work perfectly fine.
I have tried this on several machines (32bit, 64bit, winxp and win7) just to 
make sure it had nothing to do with the development machine.


> From: sql...@mistachkin.com
> To: sqlite-users@sqlite.org
> Date: Sat, 22 Oct 2011 19:38:58 -0700
> Subject: Re: [sqlite] ChangePassword method problem
> 
> 
> Farhan Husain wrote:
> >
> > After the password is changed when I try to open the database again, I get
> the
> > "File is not..." error, as though you are opening an encrypted database
> with
> > the wrong password. 
> >
> 
> Before you tried to open the database again, did you call the SetPassword
> method
> on that connection object (or supply the password as plain-text in the
> connection
> string)?
>  
> --
> Joe Mistachkin
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Bo Peng]

> It's not only speed in KB/sec that matters. It's also disk system
> usage as reported by iostat. If it's close to 100% then SQLite can't
> do any better.

A sad day.

I copied the database to a faster driver with RAID 0, made another
copy of the database (referred to as DB1), and ran another set of
tests:

test1: two sequential processes of sqlite count(*) table1 and table 2
in DB1 --> 7m15s

test2: two concurrent processes of sqlite count(*) table1 and table2
in DB1 --> 5m22s

test3: four concurrent processes of sqlite count(*) table 1, 2, 3, and
4 in DB1 --> 12m58s

test4: two concurrent processes of sqlite count(*) table1 in DB1, and
table1 in DB2 --> 9m51s.

Although running two or more processes can save some time, the
performance gain is not that big (tests 2 and 3), splitting the
database into several smaller ones would not help either (test 4).

Anyway, the iostat output of my system is

2011 Oct 22 21:16:36,  load: 0.03,  disk_r:   2676 KB,  disk_w:  0 KB

  UIDPID   PPID CMD  DEVICE  MAJ MIN DBYTES
0  0  0  ??   14   8  65536
  503732730 sqlite3  ??   14  14 R  1323008
  503731730 sqlite3  ??   14  14 R  1355776

If I understand correctly, the IO load is only 3% when two sqlite3
processes are running, so perhaps I can still tweak sqlite3 to run
faster. I will also copy the database around and see if other disks
(SSD?), operating system (linux?), and file systems can provide better
performance.

Thanks again for all the help,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ChangePassword method problem

[Joe Mistachkin]

Farhan Husain wrote:
>
> After the password is changed when I try to open the database again, I get
the
> "File is not..." error, as though you are opening an encrypted database
with
> the wrong password. 
>

Before you tried to open the database again, did you call the SetPassword
method
on that connection object (or supply the password as plain-text in the
connection
string)?
 
--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Pavel Ivanov]

On Sat, Oct 22, 2011 at 6:02 PM, Bo Peng  wrote:
>> You may create multiple threads, but your hard drive only has one set of 
>> heads.
>
> I now realize this problem and is moving the data to a faster drive.
> However, when copying the data out, the activity monitor reports
> 40MB/sec read speed. I admit this is not a fast drive, but comparing
> to the peak 700KB/sec read speed when three instances of sqlite3 were
> running, there might still be room for improvement at the sqlite side.

It's not only speed in KB/sec that matters. It's also disk system
usage as reported by iostat. If it's close to 100% then SQLite can't
do any better.


Pavel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Simon Slavin]

On 22 Oct 2011, at 10:57pm, Igor Tandetnik wrote:

> You may create multiple threads, but your hard drive only has one set of 
> heads.

Right.  I use lots of Macs.  I also think you're I/O bound.  I think you're 
just better resign yourself to an overnight run.

If you're going to do this a lot, it would be good to merge all your tables.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Bo Peng]

> You may create multiple threads, but your hard drive only has one set of 
> heads.

I now realize this problem and is moving the data to a faster drive.
However, when copying the data out, the activity monitor reports
40MB/sec read speed. I admit this is not a fast drive, but comparing
to the peak 700KB/sec read speed when three instances of sqlite3 were
running, there might still be room for improvement at the sqlite side.

Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Igor Tandetnik]

Bo Peng  wrote:
> I have a database with about 5000 tables each with more than 1 million
> records. I needed to get some summary statistics of each table but
> find that it will take days to run 'SELECT count(*) FROM table_XX'
> (XX=1,...,5000) sequentially. I therefore created 10 threads, each
> having its own database connection (sqlite3.connect() from python). To
> my dismay, it appears that only one thread was allowed to access the
> database at any time so the overall speed was not improved.

You may create multiple threads, but your hard drive only has one set of heads.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Bo Peng]

On Sat, Oct 22, 2011 at 4:18 PM, Pavel Ivanov  wrote:
>> Using three tables (e.g. test.sh 111 112 113), the first command takes
>> 13m3s, the second command takes 12m45s. I am wondering if there is any
>> magic to make the second script finish in 5 minutes by executing the
>> query in parallel ...
>
> Try to execute "pragma cache_size = 100" before executing select.
> It should be executed in the same sqlite3 run to have any effect, so
> your argument to sqlite3 should look like "pragma cache_size =
> 100; SELECT COUNT(*) FROM table_$arg;". I think disk I/O kills
> your performance. iostat could prove you that.

The performance is even worse with this option (13m55s). However, I
notice that the disk activity monitor (I am running a mac) reports 700
KB/sec for all test scripts I ran, so it is very likely that your
suspicion (i/o kills performance) is right. I am moving the data to a
faster drive and will report back later.

Thanks,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Pavel Ivanov]

> Using three tables (e.g. test.sh 111 112 113), the first command takes
> 13m3s, the second command takes 12m45s. I am wondering if there is any
> magic to make the second script finish in 5 minutes by executing the
> query in parallel ...

Try to execute "pragma cache_size = 100" before executing select.
It should be executed in the same sqlite3 run to have any effect, so
your argument to sqlite3 should look like "pragma cache_size =
100; SELECT COUNT(*) FROM table_$arg;". I think disk I/O kills
your performance. iostat could prove you that.


Pavel


On Sat, Oct 22, 2011 at 5:11 PM, Bo Peng  wrote:
>>
>> Multithreaded mode allows SQLite to be accessed via multiple threads as long
>> as threads don't shared connection handles.  This is the what's sometimes
>> called the apartment model of multithreading.
>
> Thank you very much for your quick reply.
>
> Is there a way to enable multi-thread mode from command line? Before
> changing the source code of my program, I would like to see the effect
> of multi-threading from command line. More specifically, I have two
> scripts:
>
> #!/bin/bash
> for arg in $*
> do
>    echo "Handling " $arg
>    sqlite3  esp.DB "SELECT COUNT(*) FROM table_$arg;"
> done
>
> and
>
> #!/bin/bash
> for arg in $*
> do
>    echo "Handling " $arg
>    sqlite3  esp.DB "SELECT COUNT(*) FROM table_$arg;" &
> done
> wait
>
> Using three tables (e.g. test.sh 111 112 113), the first command takes
> 13m3s, the second command takes 12m45s. I am wondering if there is any
> magic to make the second script finish in 5 minutes by executing the
> query in parallel ...
>
> Bo
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Bo Peng]

>
> Multithreaded mode allows SQLite to be accessed via multiple threads as long
> as threads don't shared connection handles.  This is the what's sometimes
> called the apartment model of multithreading.

Thank you very much for your quick reply.

Is there a way to enable multi-thread mode from command line? Before
changing the source code of my program, I would like to see the effect
of multi-threading from command line. More specifically, I have two
scripts:

#!/bin/bash
for arg in $*
do
echo "Handling " $arg
sqlite3  esp.DB "SELECT COUNT(*) FROM table_$arg;"
done

and

#!/bin/bash
for arg in $*
do
echo "Handling " $arg
sqlite3  esp.DB "SELECT COUNT(*) FROM table_$arg;" &
done
wait

Using three tables (e.g. test.sh 111 112 113), the first command takes
13m3s, the second command takes 12m45s. I am wondering if there is any
magic to make the second script finish in 5 minutes by executing the
query in parallel ...

Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 22/10/11 12:52, Bo Peng wrote:
> I mean, is it possible, in theory, to read a sqlite database from
> multiple threads/processes each with performance comparable to a
> single thread/process?

Yes.  The details are explained here:

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

You'd also be far better off having fewer tables with a column
corresponding to the table name in a master table.  Also if you are doing
counts like that then you could maintain a table of the counts using
triggers to update it.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk6jJPMACgkQmOOfHg372QRFdwCgki4sfnummmja1WlB3aNiXPYI
DNkAniiOkT2XUTNF62G/RXg1DD/aLSle
=BmWY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent readonly access to a large database.

[Peter Aronson]

The default threading mode for SQLite can be Serialized, which means only
one thread at a time.  

See http://www.sqlite.org/threadsafe.html

However, you can change it to Multithreaded either at compile time or via a
call to sqlite3_config() (that's in the C API -- I don't know about Python,
but I presume it has a version of the call) which allows you to use to
select a different threading mode.  You can also use flags on the open call
as well.

Multithreaded mode allows SQLite to be accessed via multiple threads as long
as threads don't shared connection handles.  This is the what's sometimes
called the apartment model of multithreading.

Best,

Peter

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Bo Peng
> Sent: Saturday, October 22, 2011 12:53 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Concurrent readonly access to a large database.
> 
> Dear all,
> 
> I have a database with about 5000 tables each with more than 1 million
> records. I needed to get some summary statistics of each table but
> find that it will take days to run 'SELECT count(*) FROM table_XX'
> (XX=1,...,5000) sequentially. I therefore created 10 threads, each
> having its own database connection (sqlite3.connect() from python). To
> my dismay, it appears that only one thread was allowed to access the
> database at any time so the overall speed was not improved. I further
> created 10 processes, each running one query (e.g. 'sqlite3
> file:mydatabase?mode=ro "SELECT count(*) FROM table_XX"'), but I still
> could see any improvement in performance...
> 
> Because each thread/process took only 10M RAM and about 1% CPU,
> staring 10 threads/processes should not be a problem at all. Before I
> investigate further (e.g. programming error, bottleneck of disk I/O),
> can anyone tell me if I am going the right direction? I mean, is it
> possible, in theory, to read a sqlite database from multiple
> threads/processes each with performance comparable to a single
> thread/process? I understand writing to a database from multiple
> threads can be troublesome but I only need to read from it.
> 
> Many thanks in advance,
> Bo
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Concurrent readonly access to a large database.

[Bo Peng]

Dear all,

I have a database with about 5000 tables each with more than 1 million
records. I needed to get some summary statistics of each table but
find that it will take days to run 'SELECT count(*) FROM table_XX'
(XX=1,...,5000) sequentially. I therefore created 10 threads, each
having its own database connection (sqlite3.connect() from python). To
my dismay, it appears that only one thread was allowed to access the
database at any time so the overall speed was not improved. I further
created 10 processes, each running one query (e.g. 'sqlite3
file:mydatabase?mode=ro "SELECT count(*) FROM table_XX"'), but I still
could see any improvement in performance...

Because each thread/process took only 10M RAM and about 1% CPU,
staring 10 threads/processes should not be a problem at all. Before I
investigate further (e.g. programming error, bottleneck of disk I/O),
can anyone tell me if I am going the right direction? I mean, is it
possible, in theory, to read a sqlite database from multiple
threads/processes each with performance comparable to a single
thread/process? I understand writing to a database from multiple
threads can be troublesome but I only need to read from it.

Many thanks in advance,
Bo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] complete sqlite software

[Roger Andersson]

 On 10/22/11 19:25, saeed ahmed wrote:

  i want a software,something like microsoft's Access but no microsoft.a
software that can be used for making tables,queries and reports.

2011/10/22 gabriel.b...@gmail.com

Maybe you will find what you need on
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] complete sqlite software

[Tim Streater]

On 22 Oct 2011 at 18:25, saeed ahmed  wrote: 

> i want a software,something like microsoft's Access but no microsoft.a
> software that can be used for making tables,queries and reports.

Try Navicat for SQLite Lite (free).

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] complete sqlite software

[saeed ahmed]

 i want a software,something like microsoft's Access but no microsoft.a
software that can be used for making tables,queries and reports.

2011/10/22 gabriel.b...@gmail.com 

> Firefox uses SQLLite if this is what you mean ?
>
>
> 2011/10/22 saeed ahmed 
>
> > is there any software,having sqlite inside?i want to work on something
> > microsoft access.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ChangePassword method problem

[Farhan Husain]

Hi, I just want to make sure that I am not doing something wrong, but I have a 
simple application that opens an encrypted database using it's password and 
calls the ChangePassword method on it (as shown on numerous posts in this 
forum). After the password is changed when I try to open the database again, I 
get the "File is not..." error, as though you are opening an encrypted database 
with the wrong password. I have tried different combinations of ChangePassword, 
including setting the password to null and then setting a password again but 
the same issue keeps occurring. I am using C# with 
sqlite-netFx40-binary-bundle-Win32-2010-1.0.76.0, and NUnit for my tests. My 
development machine is Windows 7 64-bit, but I am compiling a 32-bit 
application (everything else with System.Data.SQLite works perfectly in my 
application except this). Any help would be greatly appreciated, thanks.
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question re use of column alias in SELECT

[Simon Davies]

On 22 October 2011 17:28, Pete  wrote:
> If I have a SELECT statement like:
>
> SELECT cola, colb, sum(tableb.colc * tableb.cold) AS Total FROM TableA GROUP
> BY cola ORDER BY Total

What is tableb?

>
> ...I get an error, I think because of referring to Total in the ORDER BY
> clause.  Is it not possible to refer to column aliases anywhere within a
> SELECT statement other than in the AS clause?  If not, is there any other
> way to achieve this without repeating the sum expression?

sqlite> create table tst( id integer primary key, cola integer, colb
integer, colc integer, cold integer );
sqlite> SELECT cola, colb, sum( colc * cold ) as total from tst group
by cola order by total;

works for me, so I don't think that the alias is your problem.

>
> Pete

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question re use of column alias in SELECT

[Pete]

If I have a SELECT statement like:

SELECT cola, colb, sum(tableb.colc * tableb.cold) AS Total FROM TableA GROUP
BY cola ORDER BY Total

...I get an error, I think because of referring to Total in the ORDER BY
clause.  Is it not possible to refer to column aliases anywhere within a
SELECT statement other than in the AS clause?  If not, is there any other
way to achieve this without repeating the sum expression?

Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] complete sqlite software

[gabriel.b...@gmail.com]

Firefox uses SQLLite if this is what you mean ?


2011/10/22 saeed ahmed 

> is there any software,having sqlite inside?i want to work on something
> microsoft access.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stand-Alone INDEX

[Kees Nuyt]

On Sat, 22 Oct 2011 15:17:23 +0200, Fabian 
wrote:

>I have a very simple table: it just contains one single (text) column with
>an index. This table contains million of hash-values, and because of the
>index, SQLite is storing all the data twice. Behind the scenes it creates a
>second table, containing all the same values in sorted order, causing the
>database size to double.
>
>Because all the data I need is also in this second index-table, is there
>some kind of way to get rid of my original table, and still be able to
>insert new items? My initial thought was to change the schema of my table so
>that it only has a TEXT PRIMARY KEY and no other columns, but SQLite
>internally still creates an INTEGER rowid, so the end-result made no
>difference.
>
>So is there some way to have a 'stand-alone index', which doesn't store
>everything twice?

If the hash is small enough to fit a 64bit signed integer, you
could store it as INTEGER. Make sure you define the table as

CREATE TABLE Hashes (hash INTEGER PRIMARY KEY NOT NULL);

In this case the hash is an alias for ROWID and the table BTree is
its own index, no extra index BTree is created.

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stand-Alone INDEX

[Petite Abeille]

On Oct 22, 2011, at 3:17 PM, Fabian wrote:

> So is there some way to have a 'stand-alone index', which doesn't store
> everything twice?

Not in SQLite, no.

Other databases (such as Oracle) sometime offer so-called Index Organized Table 
(IOT).

http://www.orafaq.com/wiki/Index-organized_table

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stand-Alone INDEX

[Simon Slavin]

On 22 Oct 2011, at 2:17pm, Fabian wrote:

> I have a very simple table: it just contains one single (text) column with
> an index. This table contains million of hash-values, and because of the
> index, SQLite is storing all the data twice. Behind the scenes it creates a
> second table, containing all the same values in sorted order, causing the
> database size to double.
> 
> Because all the data I need is also in this second index-table, is there
> some kind of way to get rid of my original table, and still be able to
> insert new items?

Nope.  That's the way an index works in a database.

However, your needs for this particular hash index are so simple, you may not 
actually need a proper database system.  Especially if you don't have to insert 
new rows in this giant list.  All you may need is a text file of the 
hash-values, sorted into order.  You could search this file using the binary 
chop method or some similar fast searching method.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] complete sqlite software

[saeed ahmed]

is there any software,having sqlite inside?i want to work on something
microsoft access.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Stand-Alone INDEX

[Fabian]

I have a very simple table: it just contains one single (text) column with
an index. This table contains million of hash-values, and because of the
index, SQLite is storing all the data twice. Behind the scenes it creates a
second table, containing all the same values in sorted order, causing the
database size to double.

Because all the data I need is also in this second index-table, is there
some kind of way to get rid of my original table, and still be able to
insert new items? My initial thought was to change the schema of my table so
that it only has a TEXT PRIMARY KEY and no other columns, but SQLite
internally still creates an INTEGER rowid, so the end-result made no
difference.

So is there some way to have a 'stand-alone index', which doesn't store
everything twice?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users