[sqlite] Stand-Alone INDEX

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

[sqlite] complete sqlite software

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

Re: [sqlite] Stand-Alone INDEX

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,

Re: [sqlite] Stand-Alone INDEX

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

Re: [sqlite] Stand-Alone INDEX

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

Re: [sqlite] complete sqlite software

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

[sqlite] Question re use of column alias in SELECT

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

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

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

[sqlite] ChangePassword method problem

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

Re: [sqlite] complete sqlite software

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

Re: [sqlite] complete sqlite software

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

Re: [sqlite] complete sqlite software

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

[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

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

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

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

-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:

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

> > 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

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

> 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

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

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 >>

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

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

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

> 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

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

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,

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

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 >

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

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

> 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

Re: [sqlite] ChangePassword method problem

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

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

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

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

[sqlite] Problem with binding parameters to LIKE

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

Re: [sqlite] ChangePassword method problem

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)) {

[sqlite] Tables as ASCII - is it possible?

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

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

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,

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

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

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

> 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

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

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

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

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

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

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

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

Re: [sqlite] ChangePassword method problem

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