On Saturday, February 19, 2011 at 10:17 AM, Black, Michael (IS) wrote:
> sqlite> create virtual table data using fts4(content text);
> sqlite> insert into data values('one two');
> sqlite> insert or replace into data(content) select content||' three four'
> from data where docid=1;
>
> The
On Sunday, February 13, 2011 at 4:15 PM, Jay A. Kreibich wrote:
> I have a simple table like so
> >
> > CREATE TABLE points (
> > id INTEGER PRIMARY KEY,
> > lon REAL,
> > lat REAL,
> > tile INTEGER
> > );
> >
> > It has about 13.25 million rows. I want to be able to return rows
> > given a
TABLE points_rtree USING rtree (
id, min_lon, min_lat, max_lon, max_lat
);
and then querying like so
SELECT p.id, tile
FROM points p JOIN points_rtree pr ON p.id = pr.id
WHERE min_lon >= -91 AND max_lon <= -89 AND min_lat >= 43 AND max_lat <= 45;
--
P
On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard wrote:
> Hi,
>
> I'm sorry Pavel, I think you've got me wrong.
>
> > It's not "buggy". Name of the column in result set is not defined
> > unless you use "as".
>
Pavel is not wrong. SQLite is not buggy. Your expectation of what it
probably wasting time re-running ANALYZE.
..
--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Igor Tandetnik wrote:
> On 2/3/2011 12:26 PM, Puneet Kishor wrote:
>> On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote:
>>> INSERT INTO Customers VALUES (NULL, 1239, 'Banana');
>> Your EntryID is INTEGER PRIMARY KEY, yet you are inserting NULLs.
>
&
On Thursday, February 3, 2011 at 11:10 AM, Scott Baker wrote:
> If I have the following (highly simplified) customer table how do I:
>
> #1) Query for customers who *ONLY* bought apples
> #2) Query for customers who bought apples *AND* bananas
> #3) Query for customers who bought exactly 2
On Tuesday, February 1, 2011 at 10:30 AM, Ian Hardingham wrote:
> Hi Igor, thankyou.
>
> If I wish to make this modification now, what steps would I need to
> take? And in your opinion what % of the optimisation of doing it with
> integers would this provide?
>
ALTER TABLE MyTable RENAME TO
way, sqlite doesn't have to
plow through useless pages to find stuff that would fit in a single page but
doesn't because of the intervening blob.
--
Puneet Kishor
Sent with Sparrow
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
hat you want. Don't be lulled
into thinking you are always gonna get the right results just because you
happen to get the right results in a few cases.
--
Puneet Kishor
Sent with Sparrow
___
sqlite-users mailing list
sqlite-users@sqlite.org
htt
CT OrderPrice FROM Orders ORDER BY DESC OrderDate LIMIT 1;
Hope this helps.
>
>
> From: Puneet Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Sat, January 29, 2011 10:47:44 PM
> Subject:
A function
acts on a column or an expression for every row in the result set. It doesn't
modify the number of rows in a result set. On the other hand, the LIMIT clause
does nothing to the entries that have been retrieved. Instead, it throttles the
size of the result set, that is, it controls t
John Delacour wrote:
> At 11:08 -0600 28/01/2011, Puneet Kishor wrote:
>
>> Can't really say what you are doing wrong, but you code is needlessly
>> complicated; all those tildes are making my eyes swim.
>
> I also have difficulty interpreting escaped toothpicks, and I d
ing
my $q = "Delete c10";
$q =~ s/(\d+)//;
my $sth = $dbh->prepare('DELETE FROM contacts WHERE rowid = ?');
my $rows_affected = $sth->execute($q);
--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Ian Hardingham wrote:
> Hey guys.
>
> I have the following table:
>
> ratingsTable (id INTEGER PRIMARY KEY AUTOINCREMENT, mtId INTEGER, user
> TEXT, rating INTEGER);
>
> mtId links to another table's primary key
>
> I wish to have a query which gives me the mtId which is represented most
> often
On Sunday, January 16, 2011 at 9:55 AM, Navaneeth.K.N wrote:
> Hello,
>
> I have a database with a table named "symbols". This has the following schema.
>
> CREATE TABLE symbols (type TEXT, pattern TEXT, value1 TEXT, value2
> TEXT, children INTEGER);
>
> I am using the C API of Sqlite and my
quot;I'm not a programmer, so just _bear_ with me please."
--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> I can drop the jokes next time if solemnity is required here.
>
Good heavens no. Lord knows we need some levity. SQL is not a very
scintillating topic by itself. That said, making jokes does open you up
for being judged on your sensayuma, so better make them top-notch.
Anyway, thanks f
g an error saying something like "blah_type: Invalid storage
> class".
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Puneet Kishor
ery "select name from mytable order by name asc;" lists data in this
> order [##, 5th user, Anand, Bhaskar, anand, bhaskar], which is not correct.
> Any ideas on this? All thoughts are welcome.
> -Thanks
> Harish
--
Puneet Kishor http://punkish.org
Carbon Model http://carbo
Scott Hess wrote:
> On Mon, Dec 13, 2010 at 1:27 PM, Puneet Kishor<punk.k...@gmail.com> wrote:
>> Wols Lists wrote:
>>> On 13/12/10 01:38, Darren Duncan wrote:
>>>> Darren Duncan wrote:
>>>>> Wols Lists wrote:
>>>>>
y, I would love to read more about this as I am interested in
storage technologies for gridded data (think cells in a remote sensing
image). For now, all I have is the image of Dick Pick hanging upside
down in his anti-gravity shoes burned in my brain.
--
Puneet Kishor http://punkish.
create a style, and stick to it.
> On Mon, Dec 13, 2010 at 6:25 PM, Puneet Kishor<punk.k...@gmail.com>
> wrote:
>
>>
>> steve mtangoo wrote:
>>> No problem.
>>> Now only If I could get the ID of the limits (that is ID for Book 1
>> Chapter
&g
pted file is encrypted, and an in memory file is in memory. They
are mutually exclusive. One could be the other or not.
>
> Looking forward to hearing from you.
>
> Thanks,
> Srihari
> ___
> sqlite-users ma
<#randomnumber52> + 20 and
>>> x4_y4>=<#randomnumber55> and
>>> x4_y4<=<#randomnumber55> + 20 and
>>> x4_y5>=<#randomnumber58> and
>>> x4_y5<=<#randomnumber58> + 20 and
>>> x5_y1&
On 13 Dec 2010, at 4:07pm, Duquette, William H (316H) wrote:
..
>> 4. If table A references itself, and I have two rows that
>> reference each other (double-ugh!), the table might not
>> be importable at all.
>
Ouroboros aka chicken-eg
> Thanks for helping!
>
> On Mon, Dec 13, 2010 at 6:13 PM, Puneet Kishor<punk.k...@gmail.com> wrote:
>
>> Yes, of course, you are correct. Goes on to show that mapping what one
>> thinks in the mind (a logical query) to SQL (a set query) can be nastily
>&g
number 1.
SELECT *
FROM Bible
WHERE (
book > 1 OR ... and so on
)
>
> On Mon, Dec 13, 2010 at 6:11 PM, Puneet Kishor<punk.k...@gmail.com> wrote:
>
>>
>> steve mtangoo wrote:
>>> Hi Igor,
>>> would you explain what are bookLow and chapterHigh?
Igor Tandetnik wrote:
> Puneet Kishor<punk.k...@gmail.com> wrote:
>> steve mtangoo wrote:
>>>I have a script that is supposed to query the Bible scriptures between
>>> two
>>> intervals. My table is named Bible and have columns: ID (int)
y, Scripture text),
>> and the query becomes simply
>>
>> select * from Bible where VerseNumber between :VerseLow and :VerseHigh;
>>
>> --
>> Igor Tandetnik
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> h
__
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ew to using SQL outside of the "drag-and-drop" Access method, so
> this might be a silly question.
>
> If this can't be done I suppose I could add one value as a SELECT
> statement, then try to locate that row in the table and do an UPDATE
jason d wrote:
> Hello everyone,
> I have search the web and the mailing list as much as I can for this
> error. Now I come to you for help like always
>
> In my case I am using shell with Sqlite2 and I have double...triple
> checked permissions.
> all other DB files open but there is one that
john darnell wrote:
> Thanks, Simon. I'll take your advice.
>
> Oh and BTW, I apologize for the "Mac truck" remark--I meant no offense to my
> friends who code for the Mac (grin).
My Mac is Peterbilt.
> R,
> John
>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>>
thinks I'm comparing 'slag' to the column
'rr'..
use single quotes
WHERE slag = 'RR'
--
Puneet Kishor
you did to make autoincrement work,
how you know it doesn't work,
what error messages you received, if any,
what platform you are working on.
No one can guess these things for you, so how about disclosing them
right up-front.
--
Puneet Kishor
Duncan said a while back, perhaps a second div
operator (\) could be considered, so
a/2 = 2.5
a\2 = 2
b/2 = 2.5
b\2 = 2.5
but why? The above can already be achieved. So, keep the lite burning
brite in SQL.
--
Puneet Kishor
don't quite understand what you imply by the above. Obviously
this discussion stems from the fact that you can't take that for
granted, at least not without paying some cost for it. Because I don't
want to tie up the db doing double queries, I just do it in the
application.
-
ed
--- Puneet
On Oct 28, 2005, at 7:20 PM, SRS wrote:
Edward Wilson wrote:
The idea of issuing two selects is, well, a hack, and knowing how
many records one has in a
result-set is a powerful feature
Are you needing a progress bar for the search (ie the query?) Or some
action based on the result set?
Igor Tandetnik wrote:
Alfredo Cole <[EMAIL PROTECTED]> wrote:
In order to update a progress bar, I need to know the total number of
rows returned by a query, similar to MySQL's mysql_num_rows. Is there
a function like that in the C API? I may have overlooked it, but have
not found it so far.
Igor Tandetnik wrote:
I seem to have stumbled on a bug in SQLite.
create table Campaigns (id integer primary key, accountId integer, name
varchar);
create table CampaignStats (id integer primary key, campaignId integer);
insert into Campaigns values(1, 1, 'A');
insert into Campaigns values(2,
On Oct 25, 2005, at 6:08 PM, Alfredo Cole wrote:
What is the equivalent of:
select concat(col1,col2,col3) from table
in SQlite?
SELECT col1 || col2 || col3 FROM table
should do, no?
--
Puneet Kishor
y does 1,2,3 return as
text,integer,integer and not integer,integer,integer?
--
Puneet Kishor
On Oct 10, 2005, at 8:03 AM, Cyril Scetbon wrote:
Puneet Kishor wrote:
On Oct 10, 2005, at 7:35 AM, [EMAIL PROTECTED] wrote:
Sorry the precedent message was in french :-(
I encountered a weird error using DBD::SQLite.
This code works good :
use DBI;
$dbh= DBI->conn
uot;SELECT nlpurge FROM settings" is returning a single value or not.
However, you say the DELETE statement works fine directly in SQL. So,
now it boils down to what error you are getting. Please report that.
--
Puneet Kishor
doesnt do it:
SELECT * from users where login LIKE '%_%';
SELECT * FROM users WHERE login LIKE '%\_%' ESCAPE '\'
note: you can use an escape char of your choice.
--
Puneet Kishor
seconds
select from Field A contains 32.0833 / 55 seconds found 4322 records
out of 9,337,681
Still have found no sql program yet, that can beat Panorama in speed.
Regards-
Richard Nagle
CMS
--
Puneet Kishor
again.
sqlite> select * from T;
-180,90,NaN
sqlite>
There's a few more hundred thousands.
is there suppose to be some kinda of loop statement
like repeat again..
TKS-
Richard
--
Puneet Kishor
eady have the data in another format?
csv?, excel?, space delimited?
just about any consistent format can be handled via one of many of
the scripting languages.
--
Puneet Kishor
Alain Bertrand wrote:
Puneet Kishor a écrit :
without seeing the data, a good guess would be, "Yes."
What do you mean ? That my query is syntaxically wrong ?
Your query is syntactically correct in that it returns a result without
the SQL engine generating a syntax error... it ju
Kurt Welgehausen wrote:
I want the title, the MAX(speed) for each title group, and the date
that occurred. In case, ...
I haven't tried this, but I think it's correct. Let me
know if it's not.
select distinct table.title, table.date, t2.maxsp
from table,
(select title ttl,
l the rows with values from the left table and
either matching values or NULL from the right table.
--
Puneet Kishor
guidance will be much
appreciated...
--
Puneet Kishor
algorithm. Thanks for drawing my attention to it...
it should do the trick.
--
Puneet Kishor
be
appreciated.
--
Puneet Kishor
nformation no
matter what... most of the folks won't ever notice it, and everyone
would marvel at how quickly COUNT(*) was returning the results.
--
Puneet Kishor
ory. After
that, consult the basic SQLite documentation... look at the help files,
and the wiki.
Looking for a intro, that would show commands, to list
databases & tables that you just created... or allow you to see,
into other folders.
--
Puneet Kishor
nload the source and build it themselves?
cd ~
mkdir sqlite_src
cd sqlite_src
curl http://www.sqlite.org/sqlite-3.2.5.tar.gz -o "sqlite-3.2.5.tar.gz"
tar xzf sqlite-3.2.5.tar.gz
./configure
make
sudo make install
make doc
cp -R doc ~/Sites/
You are done.
--
Puneet Kishor
by calling it with its full path
/usr/local/bin/sqlite3
or by including the path in your environment (consult the docs for your
shell)
or by setting up an alias to the above in your environment.
--
Puneet Kishor
g them.
--
Puneet Kishor
On Sep 12, 2005, at 8:43 AM, Jay Sprenkle wrote:
On 9/12/05, Puneet Kishor <[EMAIL PROTECTED]> wrote:
my table is
name (VARCHAR), created_on (DATETIME DEFAULT CURRENT_TIMESTAMP),
modified_on (DATETIME)
When a new record is created, it gets a value in the created_on col,
me that "ORDER BY should come after UNION" except
if I do that I won't get the last 10 records in each sub-query.
There must be a better way to query, or perhaps, even modding my table
structure to better accomplish such a query. Any suggestions?
--
Puneet Kishor
all NON
NULL values)
COUNT(of all NULL OR some NULL and some NON NULL values) = count of all
elements in the set, null or not
I say, yes, as it makes sense to me.
--
Puneet Kishor
y different from "I sold $0 worth of things" or "I didn't sell
anything."
--
Puneet Kishor
w much
did you make in October?" the answer is not "I can't tell."
oh yes, it is the correct answer. "I can't tell because I don't have
the data." Therein lies the problem... if we contrive concrete
examples, we can go both ways -- the way of NULL or the way of 0. The
a "presence of value."
The only exception should be COUNT as it would return the number of
elements in the set without peeking in at the values of the members of
the set, hence never even encountering the absence or presence of
values of them.
--
Puneet Kishor
astMAC:~ rn$ man wget
No manual entry for wget
FastMAC:~ rn$ wget --version
-bash: wget: command not found
FastMAC:~ rn$
I don't have a wget command..
Richard
--
Puneet Kishor
the above from firsthand experience.
The latest version of MySQL has built-in spatial capabilities, but I
haven't seen them in action, only in docs, and if you Google, you can
also find a custom MySQL-based spatial data engine that someone wrote.
--
Puneet Kishor
wiw, 3.2.1 compiled flawlessly on my computer, with nary an error. It
even found the existing Readline 5.0 (which also compiled flawless with
nary an error). You might do yourself a big favor by solving what is
causing the hiccup on your machine, because there shouldn't be any at
all.
--
Puneet Kishor
D. Richard Hipp wrote:
I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right. In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.
Can I get
D. Richard Hipp wrote:
I'm rewriting the aggregate function processing in SQLite
(so that it runs faster and uses less memory) and I want to
make sure I get it right. In particular, I want to make
sure that SQLite handles NULLs in GROUP BY values the same
as other database engines.
Can I get
will automatically find readline.
--
Puneet Kishor
version 320, I think... well, definitely not 321). Upgrading on Windows
settled everything.
Weird.
--
Puneet Kishor
want, then to kick-start the TRIGGER. Hence, simply doing a
UPDATE table
SET last_upd_ts = ''
WHERE condition
is not that revolutionary.
--
Puneet Kishor
IGGERs above will only happen after INSERT,
and therefore, will insert exactly the same time in both insert_ts and
last_upd_ts defeating the intended purpose of the two fields.
Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
TRIGGER.
Although why, I still don't understand.
--
Greg Stark wrote:
On Aug 19, 2005, at 9:37 AM, Puneet Kishor wrote:
Greg Stark wrote:
Tom,
I appreciate you're help but I'm pretty sure that the semicolon has
to be there (well. at least it's required in all the other
statements I've written to date -- this one statement is part
Greg Stark wrote:
Tom,
I appreciate you're help but I'm pretty sure that the semicolon has to
be there (well. at least it's required in all the other statements I've
written to date -- this one statement is part of a much large program
that's working fine other than for this one ATTACH).
Puneet Kishor wrote:
I am jumping in the middle here, but...
Mark de Vries wrote:
On Thu, 18 Aug 2005, Kurt Welgehausen wrote:
Is it possible to change the values of certain rows that
are inserted into the database? ...
I think everything you need is explained at
<h
I am jumping in the middle here, but...
Mark de Vries wrote:
On Thu, 18 Aug 2005, Kurt Welgehausen wrote:
Is it possible to change the values of certain rows that
are inserted into the database? ...
I think everything you need is explained at
Slawomir Nowaczyk wrote:
On Wed, 17 Aug 2005 17:27:17 -0500
Puneet Kishor <[EMAIL PROTECTED]> wrote:
#> Maintaining the integrity of state is important when modifying.
#> While reading, why would you want to read something that is
#> possibly stale.
I don't think "stale&quo
Slawomir Nowaczyk wrote:
On Wed, 17 Aug 2005 08:35:08 -0500
Puneet Kishor <[EMAIL PROTECTED]> wrote:
#> Why are you wrapping a SELECT statement inside a transaction? What
#> purpose does that serve? Transactions are meant for statements that
#> modify the table,
Not
simple beginTransation, the for cycle ends in
8 seconds which is a good time.
Even 8 seconds is way too much for a simple SELECT. In fact, 8 seconds
is way too much even for an INSERT. There is something else going on
here.
--
Puneet Kishor
Henry Miller wrote:
On 8/1/2005 at 08:41 Puneet Kishor wrote:
I am curious about this as well... not about the various functions, but
what is, if at all, a better way to store the values -- as '-MM-DD
HH:MM:SS' strings (are they not stored internally as just strings?) or
as unixepoch
) faster than the former ('-MM-DD
HH:MM:SS' strings)?
Is one more malleable than the other for conversion into various other
display forms as well as for DATETIME calculations?
--
Puneet Kishor
version of windows/sqlite can I be confident?
others may weigh in with a more knowledgeable response, however, as far
as I can see, if you are only reading from a db, it should be safe no
matter what conditions you use it in.
--
Puneet Kishor
Pierre Aubert wrote:
Hello,
I wrote a small full text search engine for sqlite3.
c'est très bon
I rolled my own using an inverted index, the /usr/share/dict that comes
with the OS, and Perl. It works fine for my need, but having something
like this written in C++ would be very fast.
..
will benefit.
Comparing two databases is pointless because they all do things so
differently internally even though we as users expect the same external
results.
--
Puneet Kishor
not be as friendly as MS-Access to the end-user.
Would it be fair for me to say, "I still think it is a bug that
MS-Access doesn't run on my Mac!"?
--
Puneet Kishor
s an ext3...and I guess the File Size limitation on ext3
is 4TB.
On 7/20/05, F.W.A. van Leeuwen <[EMAIL PROTECTED]> wrote:
t the same as PARTIT
--
Puneet Kishor
On Jul 20, 2005, at 12:33 AM, chan wilson wrote:
Hi Puneet Kishor ,
Yes, you are right. I have to check out whether "DELETE FROM table;"
will really set the auto imcrement primary key seed back zero.
Yes, it will. Here you go --
sqlite> create table t (a integer primary ke
'identity' you refer to the auto-incrementing primary
key. If so, yes, SQLite will do the same thing.
Fwiw, I believe TRUNCATE TABLE does a bit more than what you are
implying above. It also truncates the logs and whatnot. But, that may
be neither here nor there.
--
Puneet Kishor
code:
Update sys_list set pass = 'abcdf;456' where
sys_id = 'testboxa' and user_id = 'testuserid';
--- Puneet Kishor <[EMAIL PROTECTED]> wrote:
On Jul 18, 2005, at 10:57 AM, Nicholas Choate wrote:
Sorry forgot the SQL on the previous email.
Update sys_list set pass = &
ahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
Puneet Kishor
ers-return-6304-sohara=pivotal-
[EMAIL PROTECTED]
rg]On Behalf Of Puneet Kishor
Sent: 30 June 2005 14:09
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Insert all rows from old table into new table but
in sorted order
On Jun 30, 2005, at 7:21 AM, Ajay wrote:
Yaa that's what I wanted to do ,
BLE
select * from NEWTABLE order by no desc
PaulVPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED] http://www.pscs.co.uk/
--
Puneet Kishor
.
--
Puneet Kishor
seconds. To give you an idea, I once de-duped a
file with 320 million rows of duplicate email addresses in about 120
seconds on an ancient, creaking iBook. A million records should be a
piece of cake.
--
Puneet Kishor
On Jun 15, 2005, at 11:56 AM, Jonathan H N Chin wrote:
Thanks for the suggestions.
DRH's workaround, while it would work, seems very ugly.
I would be interested to know what version of DBD::SQLite Puneet Kishor
is using, since I believe I have tracked the issue to a test
[EMAIL PROTECTED] wrote:
I have textual data that may look like integers (eg. "0325763213").
On insertion, any leading "0" will vanish. How do I prevent this
and make the data be inserted verbatim?
Simple illustration:
sqlite3 test 'create table t ( k text unique, v text);'
perl -e
Rats... itchy fingers on the send button...
On Jun 11, 2005, at 12:32 PM, Puneet Kishor wrote:
tbl1(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with 200k+ records
tbl2(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with a few k records
values in col(b) in tbl1 are not common with the values
101 - 200 of 237 matches
Mail list logo
