Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson wrote: > On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote: >> >> Thank you for your detailed explanation! >> First, can you please tell me how to purge the cache in Windows 7? This >> could be very useful for

Re: [sqlite] Patch to allow DISTINCT in multi-argument aggregatefunctions

On Fri, Jan 21, 2011 at 5:51 AM, smart sl wrote: > Good news. I've asked a few days earlier that how could I use DISTINCT in > group_concat with seperator specified meanwhile. It's lovely. I know -- reading that thread is what inspired the patch. -- -- Stevie-O Real

[sqlite] Patch to allow DISTINCT in multi-argument aggregate functions

Good news, everyone! I've taught SQLite to feel love! No, wait, that's not it. I spotted the thread 'how to use group_concat uniquely' today, and felt inspired, so I wrote a quick patch. The patch allows a multi-argument aggregate function to be passed DISTINCT, under the restriction that every

Re: [sqlite] returning smaller subset of columns: index vs trigger

On Sat, Sep 11, 2010 at 2:24 PM, Max Vlasov <max.vla...@gmail.com> wrote: > On Sat, Sep 11, 2010 at 8:38 PM, Stephen Oberholtzer < > oliverkloz...@gmail.com> wrote: > >> 2. They contain only a subset of the columns in the main table, so >> they are smaller -- so

Re: [sqlite] returning smaller subset of columns: index vs trigger

On Sat, Sep 11, 2010 at 7:52 AM, Max Vlasov wrote: > Cory, thanks, just wondering, is the nature of the index internals is a part > of SQL standard or just a common sense. For example, I can imagine real > example when the space used is not a big problem so index could

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

> If you make PRAGMA freelist_count writable as you suggest, I would > expect the database to reserve space once, and not use a larger pice of > the disk every time ist has to be expanded in the future. > > Martin To throw in my $0.02, I would suggest a *different* name for the pragma, something

Re: [sqlite] partial index?

On Thu, Aug 19, 2010 at 5:53 PM, Kees Nuyt wrote: > On Thu, 19 Aug 2010 17:39:14 -0400, Eric Smith > wrote: > >>Am I missing something? > > You could add a "deleted" column with value range (0,1) and > create an index on it if benchmarks show that makes it >

Re: [sqlite] NOT NULL asymetry between CREATE TABLE and ALTER TABLE, why?

The difference is that when you issue an ALTER TABLE, the table already exists. When you create a new table, it is known that there are no rows in it. But you can issue an ALTER TABLE to a table with rows in it. If so, what value should you put for the existing rows? Granted, in this specific

Re: [sqlite] ORDER BY and NULL values

select * from MyTable order by case when b is null then 0 else 1 end, b On Sun, Apr 19, 2009 at 3:53 PM, January Weiner wrote: > Hello, > > default sorting with ORDER BY puts the NULL values at the beginning. > How can I change this behaviour? I.e., instead of > > a

Re: [sqlite] selecting top entry from each day

On Sun, Nov 9, 2008 at 1:40 PM, Tejas <[EMAIL PROTECTED]> wrote: > Hi all, > > I have a table 'stats' with following schema > date varchar (25), utilization int, os_name varchar(25) ... > > I am recording host utilization for windows and linux operating systems > (os_names) at various times in a

Re: [sqlite] Intermittent SQLITE_CANTOPEN on Windows

On Mon, Oct 13, 2008 at 12:52 PM, Doug <[EMAIL PROTECTED]> wrote: > I'm using SQLite 3.5.6 on Windows and intermittently get SQLITE_CANTOPEN > when doing an insert. When that fails, I can use the debugger to go back > up > and step through the same lines again (using the same database handle - >

Re: [sqlite] Between And statement too much slow

On Fri, Sep 19, 2008 at 12:36 PM, Giuseppe Costanzi <[EMAIL PROTECTED]>wrote: > > How could improve my code? Go into the sqlite3 command line and issue EXPLAIN QUERY PLAN You'll have to fill in some values for the ?s, but that'll give you some hints. Also: How long is "too slow"? Several

Re: [sqlite] Bigger table and query optimization

On Tue, Sep 9, 2008 at 10:18 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Stephen Oberholtzer" > <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > Idea: Submit a patch that allows LIKE expressions that start

Re: [sqlite] Bigger table and query optimization

On Tue, Sep 9, 2008 at 9:04 AM, Bruno Moreira Guedes <[EMAIL PROTECTED]>wrote: > 2008/9/9 P Kishor <[EMAIL PROTECTED]>: > > On 9/9/08, Bruno Moreira Guedes <[EMAIL PROTECTED]> wrote: > >> Hi people!! > >> > >> I'm getting problems with a bigger table. I'm doing a query like this: > >> > >>

Re: [sqlite] Inserting using random rowids

On Sun, Aug 24, 2008 at 6:18 PM, Susan Ottwell <[EMAIL PROTECTED]>wrote: > How would one insert rows randomly within a range of 1000 rows? I can > easily enough do this in the script that calls the insert function, > but it would be more efficient if I could use an sqlite function or > feature to

Re: [sqlite] SQL statement to get min/max values

On Thu, Aug 14, 2008 at 9:45 PM, Dennis Volodomanov < [EMAIL PROTECTED]> wrote: > > Seems to work ok for me. What values were you expecting? > > Yes, that works. Bad example on my part, sorry. > > What doesn't work is this: > > 1|2|-7 > 2|2|-5 > 3|2|-20 > 4|2|-5 > 5|2|-2 > > SELECT max(Data) FROM

Re: [sqlite] System function with Sqlite

What does strace reveal? -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: malformed database schema - near ")"

On Fri, Aug 8, 2008 at 5:21 PM, Mark Easton <[EMAIL PROTECTED]> wrote: > Hi, > > We are using SQLite version 3.3.8 on Debian linux. Every now and again we > are getting corrupted databases. We suspect it is because the computer is > being turned off mid database operation. > > We have given full

Re: [sqlite] Proposed removal of (mis-)feature

On Thu, Aug 7, 2008 at 1:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > String literals in SQL are suppose to be enclosed in single-quotes - > Pascal-style. Double-quotes are used around table and/or column names > in cases where the name would otherwise be a keyword or when the name >

Re: [sqlite] Loading a existing database 100% into memory

On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote: > Good afternoon list, > > I would like to load my current database file completely into memory, > mostly as an experiment to check SQLite's maximum memory footprint, > however searching through the documentation I can only

Re: [sqlite] Realtime Backup of Database

On Tue, Aug 5, 2008 at 4:36 PM, Till Steinbach <[EMAIL PROTECTED]> wrote: > Hi Ingo! > Although I'm limited to C-Code your code would be really useful for > me. The triggers will be the same for me. When I have the right > triggers the most difficult part is done. The idea with the seperate >

Re: [sqlite] Ignoring "The"

On Wed, Jul 23, 2008 at 9:11 AM, Andrew Gatt <[EMAIL PROTECTED]> wrote: > I have a table of music artist names which i'd like to output in order. > Normally i just use: > > select * from artists order by artist_name; > > What i'd really like to do is order the artists by name but ignore any >

Re: [sqlite] Bad UPDATE Problems in Mobile6 device

On Mon, Jul 7, 2008 at 7:26 AM, Bob Dennis <[EMAIL PROTECTED]> wrote: > Hi > I am getting odd results with a simple update query in a Mobile 6 device. > It only works sometimes , but never reports an error. > The same code works fine in PocketPC and Mobile5 devices. > > UPDATE 'CommentList' SET

Re: [sqlite] DDL statements in transactions

On Wed, Jun 11, 2008 at 3:55 PM, Robert Lehr <[EMAIL PROTECTED]> wrote: > Oracle does not allow DDL statements to be executed in transactions, > i.e., it does but the statements are COMMITted as they are executed, > thus cannot be rolled back. > > PostgreSQL does allow DDL statements to be

Re: [sqlite] Best Practices

On Tue, Jun 10, 2008 at 1:29 PM, A. H. Ongun <[EMAIL PROTECTED]> wrote: > We have an application requirement for the following: > 1) Maintain an in memory database of real time data. The main table mimics > a Modbus register address scheme and once the table is created and records > are

Re: [sqlite] What is quicker?

One of the things that people fail to understand is that floating point numbers are stored in *binary*. In fact, I bet a number of people who understand the exact binary formatting of integers don't understand that the technique translates pretty much directly into floating point: a floating

Re: [sqlite] How to speed up my queries?

On Wed, Jun 4, 2008 at 7:12 AM, Christophe Leske <[EMAIL PROTECTED]> wrote: > Wilson, Ron P schrieb: > > I'm not a guru yet, but I think you are not using the latlon index in > > your query. Perhaps if you index on lat and lon separately your query > > will use those indices. I think the lines

Re: [sqlite] Bind arguments for insert and not null columns with default values

On Tue, Jun 3, 2008 at 5:09 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > It would be nice to be able to revert back to the default value for a > column. I don't think SQLite support this right now. > The closest thing I found is "pragma table_info(foo)". If you prepare this > and then grab the

Re: [sqlite] How to speed up my queries?

On Tue, Jun 3, 2008 at 1:27 PM, Christophe Leske <[EMAIL PROTECTED]> wrote: > Hi, > > i am a new member of this list and interested in speeding up my sqlite > queries. > > I am using SQlite in a 3d environment which is close to Google Earth or > Nasa WorldWind. > > We have a city database that is

Re: [sqlite] Multitable query question

On Thu, May 29, 2008 at 1:09 AM, <[EMAIL PROTECTED]> wrote: > I've implemented this behaviour in my program. I was just curious whether > it was possible in sql when I learnt about the Left Join (as there are more > values in one column than the other). > > I guess it isn't or is not really the

Re: [sqlite] Multitable query question

On Wed, May 28, 2008 at 11:15 PM, beejayzed <[EMAIL PROTECTED]> wrote: > > I have two tables, one which has each visit(call) on a house logged, > another > which has each letter sent to a house logged. > I would like to create a query which has two columns, Letters.Date and > Calls.Date, for a

Re: [sqlite] How to add a column with CURRENT_DATETIME?

On Wed, May 28, 2008 at 1:31 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > Another possibility would be to use the TIMESTAMP name for an integer > unix epoch timestamp, and JULIANDAY for the floating point julian day > number, giving five default value codes. > > Name

Re: [sqlite] Query help?

What if you took a slightly different tack? CREATE TABLE FinishedWork ( EventTime INTEGER NOT NULL, FileName TEXT NOT NULL, ProcessID INTEGER NOT NULL, BytesProcessed INTEGER NOT NULL, isDuplicate integer-- tri-state: 0=not duplicate 1=duplicate null=unknown ); And then periodically run

Re: [sqlite] SQLite Step Function

On Wed, May 28, 2008 at 12:32 AM, sqlite <[EMAIL PROTECTED]> wrote: > > Dear Stephen, > > Thanks for the reply. As you said we checked the EXPLAIN QUERY PLAN with > our > query and it has shown that all the four tables we use in the query are > using their indexes and there is no ORDER BY class

Re: [sqlite] Cross compiling sqlite3.c, anamolies.

On Tue, May 27, 2008 at 3:59 PM, Richard Klein <[EMAIL PROTECTED]> wrote: > > On May 26, 2008, at 3:24 PM, A. H. Ongun wrote: > >> Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I > >> get hundreds of error messages. > >> > >> I am puzzled to see why this is so. > > > > My

Re: [sqlite] Can't create table from a trigger

On Tue, May 27, 2008 at 4:15 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > >De: [EMAIL PROTECTED] > >Fecha: 27/05/2008 19:56 > > > >It's not supposed to, according to > >http://sqlite.org/lang_createtrigger.html . The syntax > >only allows select, insert, update and delete statements. > >

Re: [sqlite] 'insert or ignore' vs self join?

On Tue, May 27, 2008 at 2:41 PM, Petite Abeille <[EMAIL PROTECTED]> wrote: > Hello, > > % sqlite3 -version > 3.5.9 > > I'm trying to figure out a frugal way to handle a unique key > constrain... > > I tried using both 'insert or ignore' and a self join. The self join > seems to be noticeably

Re: [sqlite] schema optimization question

On Fri, May 23, 2008 at 3:01 PM, Jeff Gibson <[EMAIL PROTECTED]> wrote: > I'm sorry if this is an obvious question - I'm new to databases. I have > an application where the database is used to log a large number of > simulation events. The database is written once and read many times > (i.e.,

Re: [sqlite] How to use "IN" keyword for multi-column index

On Thu, May 22, 2008 at 2:02 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > IN only works on a single column. The closest you can get to this is > something like > > SELECT map.* > FROM map join ( > select 1 x, 1 y > union all > select 1 x, 2 y > union all > select 1 x, 3 y)

Re: [sqlite] How to use "IN" keyword for multi-column index

On Thu, May 22, 2008 at 1:41 PM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote: > Hello All, > For example we have table like this: > > CREATE TABLE map ( > name text, > x integer, > y integer > ); > CREATE INDEX map_xy ON map(x,y); > > How to query this table with "IN" keyword? > Query like

Re: [sqlite] deleting 100,000 entries

Actually, I seem to recall a discussion that revealed that "DELETE FROM Foo" *does* truncate the table. The discussion came up because someone complained that "ON DELETE" triggers were not being fired; the explanation was that 'DELETE FROM Foo" simply drops and re-creates Foo. The solution was

Re: [sqlite] Malformed database schema with SQLite version > 3.5.x

On Fri, Mar 21, 2008 at 10:12 AM, Noah Hart <[EMAIL PROTECTED]> wrote: > Suggestion to SQLite Developers ... Have PRAGMA integrity_check reparse > the SQL in sqlite_master, looking for errors. > > Regards, > > Noah I don't think that would actually help. It seems that this problem was caused

Re: [sqlite] Why did 3.5.7 pass the following test in alter2

> sqlite> CREATE TABLE abc2(a, b, c); > > sqlite> CREATE VIEW abc2_v AS SELECT * FROM abc2; > sqlite> SELECT * FROM abc2_v; > > sqlite> ALTER TABLE abc2 ADD COLUMN d; > > > I would expect the view to give 4 columns after the

Re: [sqlite] Can't get concat operator to work

On Mon, Mar 10, 2008 at 7:36 PM, <[EMAIL PROTECTED]> wrote: > > > I'm trying to get the concat operator to work with my user-defined > function. This works fine: > > SELECT UPPER(FirstName) || ' ' || UPPER(LastName) FROM Employees > > But this doesn't work: > > SELECT

Re: [sqlite] Alternative to * to SELECT most columns?

On Sat, Mar 1, 2008 at 6:13 PM, Gilles Ganault <[EMAIL PROTECTED]> wrote: > On Sat, 1 Mar 2008 18:04:12 -0500, "Stephen Oberholtzer" > <[EMAIL PROTECTED]> wrote: > >> INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); > >> IN

Re: [sqlite] Compiling sources for Coldfire embedded platform

On Fri, Feb 29, 2008 at 11:01 PM, <[EMAIL PROTECTED]> wrote: > > > I've downloaded and built the sources for the standard Intel Linux > platform but I want to run this on Linux on a Coldfire (MCF5484) platform. > How do I modify the compiler the build uses (short of just modifying the >

Re: [sqlite] Alternative to * to SELECT most columns?

> = > CREATE TABLE Table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, > table2id INTEGER); > CREATE TABLE Table2 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); > = > INSERT INTO Table2 VALUES (NULL,"Some text in Table2"); > INSERT INTO Table2 VALUES (NULL,"Some other text

Re: [sqlite] How To concatenate two fields in one

On Fri, Feb 29, 2008 at 11:52 AM, Alessio Forconi <[EMAIL PROTECTED]> wrote: > Hello everyone, > > SELECT IDStudent, Name + " - " + Surname AS Nominative FROM Students + is addition. You want ||. Also, you're using double-quotes (") when you should be using single-quotes ('). SELECT IDStudent,

Re: [sqlite] Update fail without ERRORS

On Fri, Feb 29, 2008 at 11:01 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > I'm working on a program using sqlite library, but I've got an issue > that I can't solve. > Suddenly, my program don't update the tables > I > don't understand whats matter because, if I write SQL

Re: [sqlite] Prepare Statement

On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m <[EMAIL PROTECTED]> wrote: > > > Hi, > My table looks like: > IdName > 1 1aaa > 2 01345 > 3 1asdf > > I want to bind unsigned short as text. i.e, If the Unsighed short

Re: [sqlite] IF...THEN constructs

On Wed, Feb 27, 2008 at 6:00 PM, Jason Salas <[EMAIL PROTECTED]> wrote: > Hi Igor, > > Thanks for the insight. I'm used to doing stored procedures for web > apps, which conditionally execute statements based on state and/or the > presence of variables. Consider this construct, which I built

Re: [sqlite] Improved sorting algorithm (was: indexing)

On Wed, Feb 13, 2008 at 2:12 PM, <[EMAIL PROTECTED]> wrote: > > A project on our to-do list is to implement a new sorter > that uses O(1) seeks. We know how to do this. It is just > finding time to do the implementation. Do you have a link to a page or pdf describing the algorithm involved?

Re: [sqlite] View update performance (was: Updatable views)

On Thu, Feb 14, 2008 at 10:17 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Stephen Oberholtzer wrote: > > Stephen, > > FYI, your ticket was fixed on Tuesday by checkin 4782 in case you hadn't > noticed. > > Dennis Cote > Wow, awesome! My claim to fame -- I su

Re: [sqlite] Optimizing Query

On Thu, Feb 14, 2008 at 2:26 PM, Nathan Biggs <[EMAIL PROTECTED]> wrote: > I was hoping that someone could help me with optimizing this query. > Basically I need to return totals from an large database (> 1million > records in the table). There needs to be two different totals one by > "a" and

Re: [sqlite] View update performance (was: Updatable views)

On Feb 11, 2008 1:53 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Stephen Oberholtzer wrote: > > I should note that there's a gross inefficiency when using triggers to > > handle updates or deletes against views; SQLite does the equivalent of > > this: > >

Re: [sqlite] Updatable views

I should note that there's a gross inefficiency when using triggers to handle updates or deletes against views; SQLite does the equivalent of this: For UPDATE ... WHERE , SQLite copies the entire source view into the temp table. SELECT * INTO FROM Then, it iterates over , looking for rows

Re: [sqlite] Update value from either database table?

On Feb 4, 2008 1:56 PM, Gussimulator <[EMAIL PROTECTED]> wrote: > I have a field I need to update given a condition, but I don't know wether > the condition occurs in table A or table B, how can I perform this query? > > I have 2 identical tables in design, but one contains system data and the >

Re: [sqlite] SQLite character comparisons

I feel compelled to throw in my $0.02 here. To everyone who thinks that SQLite should allow 'foo ' == 'foo': SQL was originally conceived as a query *language* -- a way for a human being to request a set of data from a database. It was specifically designed for ad-hoc queries. This little

[sqlite] [patch] Improve performance of UPDATE/DELETE against views

(I sent an e-mail to the list, and got a nice response, although it didn't really say where I should send enhancement patches. So I'm sending this one here, in the hopes that at least one person will comment on it.) Currently, the implementation of 'update' and 'delete' for views is such that

[sqlite] Submitting patches?

What's the best way to submit patches for SQLite? I looked around on the website and didn't find anything relevant; Google wasn't much help because all I got were pages for *other* projects that used SQLite to maintain their patch databases. -- -- Stevie-O Real programmers use COPY CON

Re: [sqlite] INSTEAD OF Trigger Question

On 4/23/07, Sylko Zschiedrich <[EMAIL PROTECTED]> wrote: Hi all, i have a question to "instead of triggers" on views. Following schema: h> That's my current implementation. But with this I can't update the View to 'null' values because the coalesce statement will return the old.values.

Re: [sqlite] SQL query, finding out which row a result is in

On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants. Let's say your resultset consists of 3 columns: memberid, lastname,