Re: [sqlite] how to get result of eval as list of lists?
Kurt Welgehausen wrote:
proc lpartition {recsize data} {
set ret {}
set datalen [llength $data]
for {set i 0; set j [expr {$recsize-1}]} \
{$i < $datalen} {incr i $recsize; incr j $recsize} {
lappend ret [lrange $data $i $j]
}
set ret}
So is there another way to determine the number of columns/or results of
a query to calculate recsize?
A query can be quite complicate like combined SELECTs or something like
{SELECT*,rowid from...}
db eval {SELECT*,rowid from t1} data {set columns $data(*); lappend
records }]
set recsize [llength ${columns}]
set recordslist [lpartition ${recsize} ${records}]
This still requires the eval script and has the drawback duplicating the
used memory.
There seems to be only two simple and fast solutions for me.
- First one is creating another db subcommand (e.g.: db evallist ...}
- Second one would be easier (but slower?) creating another element (a
pedant to data(*)) holding exactly one row of the result array
data(*)= a b rowid
data(a)= 3
data(b)= howdy!
data(rowid)= 3
data(typeof:a) = text
data(typeof:b) = text
data(typeof:rowid) = INTEGER
proposed:
data(_) = {3 howdy! 3}
TIA
Stefan
[sqlite] Read only text widget
Hi all, I have a text widget which successfully fetches data from a backend sqlite database and displays it in the text widget. My requirement is that I want to make the text widget readonly so that no one can edit the fetched data. I use '-state disabled' but that hides the data that is displayed in the text widget. Kindly help. The relevant portion of the code is undergiven: text .f2.t1 -bd 1 -highlightbackground black -highlightthickness 1 -font $fnt9 -state disabled .f2.t1 insert end [string trim $nm] place .f2.t1 -x 65 -y 25 -width 170 -height 16 Thanxs in advance. Anirban Sarkar
[sqlite] re: java jbdc test SOLVED
hi- i am going to write down everything i did today (if i can remember). i ran the test file and everything is ok. thanks, jim
[sqlite] re: java jdbc test problems
hi- i am a little further along (i hope). C:\sqlite-2.8.15>make test MAKE Version 5.2 Copyright (c) 1987, 1998 Inprise Corp. "C:\Program Files\Java\jdk1.5.0_01"\bin\javac test.java C:\sqlite-2.8.15> and when i go to look at the file test there is a test.class file now as well as the test.java class. i will keep trying things out. thanks, jim
[sqlite] java sqlite problems with test
hi-
i am attempting to learn java. i tried to install the sqlite jdbc and
ran into these messages.
can someone who knows java better (especially all the make and
configuration stuff) offer some advice
as to what might be going wrong?
C:\sqlite-2.8.15>make test
MAKE Version 5.2 Copyright (c) 1987, 1998 Inprise Corp.
"C:\Program Files\Java\jdk1.5.0_01"\bin\javac test.java
test.java:62: cannot find symbol
symbol : variable Constants
location: class test
return Constants.SQLITE_OK;
^
test.java:84: cannot find symbol
symbol : variable Constants
location: class test
db.function_type("myregfunc", Constants.SQLITE_TEXT);
^
test.java:86: cannot find symbol
symbol : variable Constants
location: class test
db.function_type("myaggfunc", Constants.SQLITE_TEXT);
^
3 errors
** error 1 ** deleting test
C:\sqlite-2.8.15>java test
Exception in thread "main" java.lang.NoClassDefFoundError: test
C:\sqlite-2.8.15>
Re: [sqlite] how to get result of eval as list of lists?
> Is there a fast way to get ... list of lists ... ?
proc lpartition {recsize data} {
set ret {}
set datalen [llength $data]
for {set i 0; set j [expr {$recsize-1}]} \
{$i < $datalen} {incr i $recsize; incr j $recsize} {
lappend ret [lrange $data $i $j]
}
set ret
}
or for a fixed record size:
proc lpartition3 data {
set ret {}
foreach {col1 col2 col3} $data {
lappend ret [list $col1 $col2 $col3]
}
set ret
}
The second version should be somewhat faster, but of course,
it's not very flexible.
Regards
RE: [sqlite] Determining the primary key of a table?
The pk column in the table_info pragma is populated, but as I said, the index_list pragma doesn't mention an index. --Ned. http://nedbatchelder.com -Original Message- From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: Sunday, January 23, 2005 3:34 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Determining the primary key of a table? On Sun, 23 Jan 2005 13:18:27 -0500, Ned Batchelder <[EMAIL PROTECTED]> wrote: > One more twist I just discovered: > > If the primary key is a single column that auto-increments, there is no > information in the pragma index_list about the primary key at all: Are you sure? I'm pretty sure I use the 'pk' value from the table_info results in my own SQLite manager application, and it appears to be able to detect the primary key column from the table created by your schema.
Re: [sqlite] Case-Insensitive Searches
On Sun, 2005-01-23 at 10:17 -0800, David Wheeler wrote: > What's the best way to do case-insensitive searches in SQLite, given > that LOWER() may not work properly with UTF-8 characters? Is LOWER() > the best way in general, anyhow? And if so, will it use indexes? > Use sqlite3_create_collation() to register a collating sequence that ignores case in UTF-8 characters. Then put "COLLATE " after the declaration of the column you want to be caseless.
Re: [sqlite] Determining the primary key of a table?
On Sun, 23 Jan 2005 13:18:27 -0500, Ned Batchelder <[EMAIL PROTECTED]> wrote: > One more twist I just discovered: > > If the primary key is a single column that auto-increments, there is no > information in the pragma index_list about the primary key at all: Are you sure? I'm pretty sure I use the 'pk' value from the table_info results in my own SQLite manager application, and it appears to be able to detect the primary key column from the table created by your schema.
Re: [sqlite] Version 3.1.0
The one aspect that I find usefull is for accessing a larger memory space. For example on your typical linux (rh9 32bit) you start to run out of mem (per process) at about 2gig. I can immediatly take the app an run on a 64bit machine and get at least an additional gig of ram from the allocators. This is just due to the O/S implementation and some other stuff the 64 bit o/s does. Now compile under 64bit native and I can allocate a much (technical term) Huger chunk of memory. So I would hope that if can use :memory: with SQLite on a 64bit system (or huge cache) with enough ram to stuff the entire db in ram. An example is the MonetDB (IMDB) which on a 32 bit system can't deal with DB's lager then available ram. On a 64bit system it can go past ram, and then let the vmm do the swapping (not ideal, but can do db's larger then ram). They also have options for trying to keep things 32bit sized when necessary, as the 64bit transition can cost as well, ptrs, etc are now double in size. Other folks might have other reasons, but for me it is address space :-) Sandy > On Fri, 2005-01-21 at 20:56 -0500, Robert L Cochran wrote: >> Does this version take advantage of 64-bit cpu's like the AMD Athlon 64? > > I don't know. What do you mean by "take advantage of"? > What does a 64-bit CPU do that you can't do with a 32-bit CPU? > > There are a few limits in SQLite that are based on the number of > bits in an integer. For example, you can't have more than 32 > tables in a join. Except on a 64-bit machine where you can have > 64 tables in a join. Is that what you mean by taking advantage > of a 64-bit CPU? But note that we could get 32-bit CPUs to > provide 64 tables in a join just by changing a single line of > code, namely changing > > typedef unsigned int Bitmask; > > into > > typedef unsigned long long int Bitmask; > > So how does that really "take advantage of" the 64-bitness of > some CPUs? > > I hear a lot of excitement about 64-bit CPUs which I really > do not understand. Please explain this to me. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] Case-Insensitive Searches
David Wheeler <[EMAIL PROTECTED]> writes: > What's the best way to do case-insensitive searches in SQLite, given that > LOWER() may not work properly with UTF-8 characters? Is LOWER() the best way > in general, anyhow? And if so, will it use indexes? Function calls are not indexed in SQLite 2.8, so using lower() would not use an index. One option for you, if you can afford the space to duplicate your data with the duplicate stored as lower case, is to add a column of lower-case data, and triggers to update it automatically. CREATE TABLE x(tAsEntered TEXT PRIMARY KEY, tLowerCase TEXT); CREATE TRIGGER x_insert_tr AFTER INSERT ON x FOR EACH ROW BEGIN UPDATE x SET tLowerCase = utf8ToLower(new.tAsEntered); END; CREATE TRIGGER x_update_tr AFTER UPDATE OF tAsEntered ON x FOR EACH ROW BEGIN UPDATE x SET tLowerCase = utf8ToLower(new.tAsEntered); END; CREATE INDEX x_tLowerCase_idx ON x(tLowerCase); With this, you'd be able to search on tLowerCase for a case-insensitive search, or on tAsEntered for a case-sensitive search. You'll need to provide the utf8ToLower() function used here, in your native language. In C, you'd do this by calling sqlite_create_function(). (In SQLite 3.0, it's probably called sqlite3_create_function(), but check the docs.) SQLite 3.0 provides some COLLATE features which may allow you to do this more conveniently. Derrell
RE: [sqlite] Determining the primary key of a table?
One more twist I just discovered: If the primary key is a single column that auto-increments, there is no information in the pragma index_list about the primary key at all: sqlite> create table foo(a int, b integer primary key, c int); sqlite> pramga table_info(foo); cid nametypenotnull dflt_value pk -- -- -- -- -- -- 0 a int 0 0 1 b integer 0 1 2 c int 0 0 sqlite> pragma index_list(foo); sqlite> Maybe parsing the SQL from sqlite_master is the way to go after all.. :-( --Ned. http://nedbatchelder.com -Original Message- From: Ned Batchelder [mailto:[EMAIL PROTECTED] Sent: Sunday, January 23, 2005 12:55 PM To: sqlite-users@sqlite.org Subject: [sqlite] Determining the primary key of a table? I need to examine the schema of a SQLite database programmatically. I've managed to find everything I need in the various pragmas for querying the schema, except: the order of the columns in the primary key. pragma table_info tells me which columns are in the primary key, but not their order in the key. pragma index_info tells me the order of columns in the index, but not which index is the primary key. pragma index_list tells me all the indexes on a table, but not which index is the primary key. It looks like the first index in index_list which is named "sqlite_autoindex_*", and is unique, is the primary key, but can I be guaranteed of that? What would be ideal is if the pk column in pragma table_info was not just 0 or 1, but was 0 for columns not in the primary key, and 1 through n for the columns in the primary key, with the value determining their ordering. I understand that represents a slight backward compatibility problem. Is there something I've missed? Does anyone have a better way (other than parsing the table SQL) to determine the primary key? --Ned. http://nedbatchelder.com
[sqlite] Case-Insensitive Searches
Hi All, Apologies for the newbie questions... What's the best way to do case-insensitive searches in SQLite, given that LOWER() may not work properly with UTF-8 characters? Is LOWER() the best way in general, anyhow? And if so, will it use indexes? Many TIA, David
[sqlite] Determining the primary key of a table?
I need to examine the schema of a SQLite database programmatically. I've managed to find everything I need in the various pragmas for querying the schema, except: the order of the columns in the primary key. pragma table_info tells me which columns are in the primary key, but not their order in the key. pragma index_info tells me the order of columns in the index, but not which index is the primary key. pragma index_list tells me all the indexes on a table, but not which index is the primary key. It looks like the first index in index_list which is named "sqlite_autoindex_*", and is unique, is the primary key, but can I be guaranteed of that? What would be ideal is if the pk column in pragma table_info was not just 0 or 1, but was 0 for columns not in the primary key, and 1 through n for the columns in the primary key, with the value determining their ordering. I understand that represents a slight backward compatibility problem. Is there something I've missed? Does anyone have a better way (other than parsing the table SQL) to determine the primary key? --Ned. http://nedbatchelder.com
Re: [sqlite] few questions...
On Jan 23, 2005, at 8:11 AM, Jason Morehouse wrote: We are currently using mysql. What is the comparison to opening a database with sqlite vs connecting to the daemon with mysql? Our current box has seen 300+ connections to the sql server at at once. Can we expect that having 300 databases open with sqlite wont be a problem? SQLite and MySQL are at opposite ends of the spectrum when it comes to how multiple connections are managed. MySQL uses a central daemon that arbitrates all connections. By doing so, it can manage the connections on a relatively fine grained level, allowing multiple simultaneous readers and writers (as long as, I would assume, the writers aren't all scribbling on the same table). With this flexibility comes considerable complexity in that you have to administrate yet another service on the computer and manage connection information, etc... SQLite takes the very simple approach of equating opening a database connection with opening a file. As such, it is extremely efficient in that no data has to pass "over the wire" and very simple in that there is no administrative overhead.There is significantly less overhead in opening a SQLite database file than there is in opening a client/server connection. Since there isn't a connection, there isn't really a notion of multiple connections either. Instead, SQLite allows multiple clients-- threads or processes-- to open the database file. SQLite arbitrates read/write access through the use of BSD level advisory locks. As such, SQLite allows multiple simultaneous readers and only one writer. When a writer is actively writing to the database, it blocks all other readers. Unless you redesign your application to not be focused on a multiple connection model, it is unlikely that you will see any benefit-- performance or otherwise-- to moving to SQLite. I am assuming that your inquiry was related to performance issues that you are currently experiencing with MySQL? Can you give us an idea of how large of a working set (i.e. how much data is in active play, in general) you have, what the transaction rate is, and how large the overall data set is? b.bum
Re: [sqlite] few questions...
Jason Morehouse wrote: We are currently using mysql. What is the comparison to opening a database with sqlite vs connecting to the daemon with mysql? Our current box has seen 300+ connections to the sql server at at once. Can we expect that having 300 databases open with sqlite wont be a problem? While I suspect that SQLite could handle the large number of connections, in general a database server will handle high levels of concurrency better than a disk based database. A lot will depend on the specific usage though. If it's a high percentage of reads, you'll probably be okay with SQLite, just because it's fast and very efficient. That will probably change if most of the clients are writing, or even if a high enough percentage are writing. Because of SQLite's very coarse-grained locking, writes on a busy database can be problematic. Clay Dowling
[sqlite] few questions...
Hello all, I've been playing around with sqlite for the past while, and am thinking of using it in a upcoming project. I have a few questions, if anyone is able to offer some insight. What happens when a database file is opened? Is the structure & indexes of all the tables read into memory? Is there any benefit in grouping tables in different files? For instance, we have user tables that get used regularly, and stats tables that grow daily, yet much of the information over 30 days old is rarely used. Is it recommended we store this in an archive database, or is there no affect on resources till the table is actually called on. We are currently using mysql. What is the comparison to opening a database with sqlite vs connecting to the daemon with mysql? Our current box has seen 300+ connections to the sql server at at once. Can we expect that having 300 databases open with sqlite wont be a problem? And finally, is there any more news on sqlite3 for php5? Regards, -Jason
RE: AW: [sqlite] sqlite3_column_tablename
What do you want if the user types "SELECT (t0.x || t1.x) FROM table0, table1 ."? I would like to second that request. Ok, sure, there are cases where the info is meaningless (like above), and should be blank. But in most cases, it IS meaningful, and extremely useful for tools (not so much perhaps for hard-coded situations, but do consider A report generator, or a visual query builder, for example)
Re: [sqlite] XML and SQLite?
I think I have a clear idea of what to do now. Thanks. On Sat, 22 Jan 2005 23:56:48 -0800, Darren Duncan <[EMAIL PROTECTED]> wrote: > At 11:13 PM -0800 1/22/05, Jeff wrote: > >I'm making something that requires XML. (ooh, secretness) > >It would also be nice to use SQLite with it, but I am confused about > >how much things I need to put into the database and how much that > >needs to stay on the file system. > >You probably didn't understand that. My describing skills suck. > >Here is the question: Should I store XML files in a database, or > >should I just put it on the file system. Which would be faster? > > I would say that it depends on their quantity or use. If you are > having thousands of little XML files, then store all of them in a > SQLite database, each one in a SQLite record, where the XML is all in > a large text field, and other fields in the row contain a few details > you would look it up by. If you have just a few large XML files, > then store them in the file system. A database is also useful if you > want to look up your XML document by multiple criteria rather than > just by one. -- Darren Duncan > -- Jeff
