_
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
Puneet Kishor
m:
[EMAIL PROTECTED]
[mailto:sqlite-users-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 wr
select * from NEWTABLE
do
Insert into NEWTABLE select * from OLDTABLE
select * from NEWTABLE order by no desc
PaulVPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED] http://www.pscs.co.uk/
--
Puneet Kishor
then use a regular text search engine such as
Swish-e, Lucene, or Plucene.
--
Puneet Kishor
in a few 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 i
[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 'u
is there a perf advantage to "creating" a
storedproc in SQLite? Does it pre-compile the storedproc? If there
isn't any perf advantage, I'd rather not even bother.
--
Puneet Kishor
>>> keyword, count the number of lines you got, and then throw away
the
>>> resulting data, but this seem to be a bit of waste of both
resources
and
>>> time... so I hope someone has a better solution :)
>>>
>>> Thanks in advance,
>>> Johan Trygg
>>>
PaulVPOP3 - Internet Email Server/Gateway
[EMAIL PROTECTED] http://www.pscs.co.uk/
--
Puneet Kishor
above seems
contradictory. After all, if more than one row can be NULL, then they
won't be UNIQUE! what gives?
--
Puneet Kishor
On Jun 11, 2005, at 2:03 PM, D. Richard Hipp wrote:
On Sat, 2005-06-11 at 12:32 -0500, 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
SELECT *
FROM ((SELECT * FROM tbl1
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
SELECT? Is there a better way?
--
Puneet Kishor
bout.
The main difference is in LIKE versus =.
Is there anything wrong that I am doing, or overlooking something? How
can I improve the performance of the LIKE selects?
--
Puneet Kishor
On Jun 9, 2005, at 2:30 PM, D. Richard Hipp wrote:
On Thu, 2005-06-09 at 14:29 -0500, Puneet Kishor wrote:
Thanks to those who responded. However, this thread is going away from
what I really asked... not how to quote a string, but to confirm
whether or not SQLite had any idiosyncrasies
set a = ?";
$string = "some long string; has many 'single quotes'";
$sth = $dbh->prepare($sql);
$rc = $sth->execute($string);
HTH
Dennis Cote
--
Puneet Kishor
to update/insert a
string like --
$text = 'Some long-winded text with lot''s of different things like:
* lists
* more lists
text with semi-colon; and even such: stuff.';
UPDATE tbl SET col = $text WHERE ...
causes error to effect that sql prepare failed (again, Perl/DBI
error message
to pop up telling me that the database is locked.
Haumph!
On 6/9/05, Puneet Kishor <[EMAIL PROTECTED]> wrote:
I've been a reading a lot on the "database locked" problem, but still
need guidance trying to locate the source of my problem.
environment:
race" that tells me what is
going on with SQLite?
Many thanks.
--
Puneet Kishor
Mitchell Vincent wrote:
So I'm sitting here in a pinch and my brain just refuses to work...
2 tables, a customer and an invoice table. What is the proper SQL to get
all customers records, plus the sum of a column in the invoice table
with a relation on the customer ID, but not all customers might
On Jun 2, 2004, at 7:31 AM, Jalil Vaidya wrote:
There is a PalmOS port of SQLite available. Mr. Wayne
on this list kindly sent me the code of his work on
the Palm port. I have not tested it on Palm Vx but I
do believe it works(Mr. Wayne can correct me if I am
wrong). I am working on Palm OS 6 port
On May 29, 2004, at 9:35 AM, Will Leshner wrote:
On May 29, 2004, at 7:31 AM, Tito Ciuro wrote:
SQLite stores the original SQL statement that created the table. This
statement can be retrieved, as you well suggested, querying
sqlite_master. The data is there, but SQLite doesn't do its homework
w
On May 26, 2004, at 1:30 PM, Tito Ciuro wrote:
Hello everybody,
Today I'm releasing QuickLite, a Cocoa wrapper for SQLite, the
embeddable SQL database engine.
Very nice Tito. I just downloaded this and started poking around. Wow.
This could be the start of something that might fill a nice void o
Fred Williams wrote:
I think what is being looked for here is information available from many
commercial databases. There are elaborate "System" tables containing the
entire database structure down to the finest detail including table formats,
relationships, triggers, stored procedures, and etc.
D. Richard Hipp wrote:
Puneet Kishor wrote:
Also, afaik, there doesn't seem to be any "easy" facility to
reconstruct the schema...
The schema is exactly reconstructed as follows:
SELECT sql FROM sqlite_master WHERE sql NOT NULL;
I guess I didn't phrase that properly. What
H. Wade Minter wrote:
..
Here's what I ended up with - it's not incredibly robust or portable,
but does what I need:
# Get the table schema information
my $sth = $dbh->table_info();
while ( my $row = $sth->fetchrow_hashref )
{
if (
Hi Randy,
On May 24, 2004, at 3:58 PM, Randy J. Ray wrote:
No, this won't work (not even with the correction, which is still not
correct code).
Do want to elaborate more? Why is the code not correct even with the
correction? We will all learn from your input. Btw, I do start with the
disclaimer
Corrected one obvious error...
Puneet Kishor wrote:
On May 24, 2004, at 8:34 AM, H. Wade Minter wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello,
I'm investigating migrating my Perl/Tk application from MySQL to
SQLite. I've got most of the conversion to DBD::SQLite done, but
On May 24, 2004, at 8:34 AM, H. Wade Minter wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello,
I'm investigating migrating my Perl/Tk application from MySQL to
SQLite. I've got most of the conversion to DBD::SQLite done, but there
are a couple of areas I'm stuck on.
My first issue is th
On May 22, 2004, at 1:17 PM, Mario Ruggier wrote:
On May 22, 2004, at 7:47 PM, Puneet Kishor wrote:
On May 22, 2004, at 12:31 PM, nathanvi wrote:
In my office we have to share a databse (a list of addresses).
A lot of people has a notebook.
So my idea was:
- a file.sqlite on the server in which
On May 22, 2004, at 12:31 PM, nathanvi wrote:
In my office we have to share a databse (a list of addresses).
A lot of people has a notebook.
So my idea was:
- a file.sqlite on the server in which all people can add, delete,
select contacts
- when someone have to go away, he can copy this fi
On May 22, 2004, at 2:25 AM, Darren Duncan wrote:
At 10:23 PM -0700 5/21/04, Sanford Selznick wrote:
I have Table1:
data1
data2
data3
nameID -> id of record in Table2
I have Table2
id
name1
name2
I'd like to select all records from table 1, and have them sorted by
name1.
What's the best way to do
Darren Duncan wrote:
At 7:25 AM -0700 5/17/04, Raymond Irving wrote:
I think SQLite should come standard with an odbc driver since ODBC is
an "open standard"
I disagree.
..
Finally, while ODBC is very common, it isn't the only protocol for
networking databases, and some people may prefer an alte
On May 14, 2004, at 10:08 AM, [EMAIL PROTECTED] wrote:
Hello Dr. Hipp,
Hello SQLite users,
I have two tables with data ( persons and products) and a
third table, that cople the Person.ID with the product.ID.
Now I want a left join between the persons and the products,
so I can show all people,
On May 9, 2004, at 6:54 AM, eno wrote:
Puneet Kishor wrote:
this is likely a common and easy answer, so my for not
being able to figure it out.
How do I test for the existence of a table or a view so I can do
something like...
if exists
then DROP
else CREATE ()...
what I do in such
this is likely a common and easy answer, so my for not being
able to figure it out.
How do I test for the existence of a table or a view so I can do
something like...
if exists
then DROP
else CREATE ()...
should I use CASE, or is there some other way?
And while we are at it... how do I loo
Christian Smith wrote:
On Thu, 6 May 2004, Puneet Kishor wrote:
Things that SQLite sucks at (if you pardon the expression) compared to
Access and FMPro -- ALTERing tables is a royal pain in the behind. I am
constantly in need of ALTERing the tables and queries (views) as I am
developing the
Shawn Anderson wrote:
Howdy,
I was wondering if anyone had any ideas on either of these options?
Basically I have about 300 mb of data that I want to insert in a database
using SQLite, and I am trying to find the most efficient way of doing it.
My thoughts are either to find a fast bulk insert met
On May 6, 2004, at 2:06 PM, Andrew Piskorski wrote:
On Thu, May 06, 2004 at 01:21:28PM -0500, Puneet Kishor wrote:
they are as real a database as one wants them to be. Sure, they don't
support ACID compliance, but I am not sure if they are created by
Ugh, that particular argument is one I s
Here are the results with the best on top.
INSERT 1,000,000
db wallclock usr sys cpu rate filesize
SQLite 115.00 110.021.75 111.77 89.47 76,736
Bdb 266 90.92 32.67 123.5
I conducted the following test using Perl, Benchmark.pm, and the
suitable db-related modules --
Created a table with two columns, id (number) and spellednumber
(varchar). Inserted 1 million rows, each row with id containing a serial
number starting at 1, and spellednumber containing the same nu
In the spirit of discussion --
On May 6, 2004, at 1:08 PM, Andrew Piskorski wrote:
On Thu, May 06, 2004 at 06:24:10PM +0100, Steve O'Hara wrote:
However, I'm wondering why we're comparing SQLite with kernel based
RDBMS
like Oracle etc, and not with it's more closely related cousins such
as
Acc
On May 6, 2004, at 12:24 PM, Steve O'Hara wrote:
I've been watching the discussion about concurrency with interest. I
find
I'm impressed by everybody's arguments.
I'd too would like to keep SQLite small and fast but equally, I'd like
to
have better concurrency. Even if this is just a safeguar
top posting... views of another "arm-chair critic/desirer of features"
follow --
I too, like Basil Thomas, like Andrew's writeup. Well-reasoned.
Like it or not... all programs start simple and evolve to a level
unsupportable complicatedness until the developer's either lose
interest or the pro
On May 2, 2004, at 6:52 PM, Kurt Welgehausen wrote:
... handicapped by not being able to use parens in a FROM clause.
It's not that you can't use parens anywhere in a FROM clause; you
just can't have the entire table-list enclosed in parens. You can
still do something like
select * from (t1 lef
On May 3, 2004, at 3:48 AM, eno wrote:
Puneet Kishor wrote:
Actually I am on Mac OS X 10.3. I have no idea what I need to do to
enable READLINE. I just downloaded the source and did the
./configure, make, make install dance and got no READLINE. It must be
somewhere on my system because the
On May 2, 2004, at 4:40 PM, [EMAIL PROTECTED] wrote:
Puneet Kishor <[EMAIL PROTECTED]> writes:
does anyone else miss the lack of a .desc command
in this
otherwise wonderful program? I find the .schema to be cumbersome, and
it is
particularly difficult to get a nice list of colum
On May 2, 2004, at 4:22 PM, Kurt Welgehausen wrote:
I think it is called READLINE support, no? ...
Yes, the SQLite shell uses readline by default under Linux,
so you must be using something else. Readline has been
ported to MS Windows, but I don't know what would be involved
in rebuilding the she
On May 2, 2004, at 4:07 PM, Kurt Welgehausen wrote:
FROM (contacts AS c LEFT JOIN ...
This has come up several times on this list. This behavior is
entirely consistent with the grammar for select statements
given in lang.html. The (partial) syntax is "FROM table-list";
"FROM (table-list)" is
apologies for this flurry of emails from a usually quiet lurker...
does anyone else miss the lack of a .desc command in
this otherwise wonderful program? I find the .schema to be cumbersome,
and it is particularly difficult to get a nice list of columns and
columntypes for a given table. Unles
Is there a way to enable some kind of command history in the SQLite
shell? You know, press the up arrow to get the previous command... I
think it is called READLINE support, no?
Its a pain in the derierre hitting the up arrow and getting all the
^]]A kind of junk on the screen, but bad habits d
On May 2, 2004, at 1:28 PM, D. Richard Hipp wrote:
Puneet Kishor wrote:
I am getting seriously hassled trying to do the following simple
thing...
SELECT
(CASE
WHEN
c.firstname ISNULL AND c.lastname ISNULL
THEN
'unnamed'
I am getting seriously hassled trying to do the following simple
thing...
SELECT
(CASE
WHEN
c.firstname ISNULL AND c.lastname ISNULL
THEN
'unnamed'
ELSE
c.firstname & ' ' & c.lastname
END) AS fullname,
FROM contacts
On Apr 21, 2004, at 8:37 AM, [EMAIL PROTECTED] wrote:
Puneet Kishor wrote:
my guess is because it can be done other ways (see the docs on this
specifically), and the idea is to keep SQLite as simple as possible.
The
more "conveniences" that are added to it, the more complicated it w
Mateusz Łoskot wrote:
On 4/20/2004 11:49 PM, Eli Wheaton wrote:
I'm new to sqlite but as far as I can tell it does not support ALTER
TABLE
command. I could find that useful in future versions.
That's right.
ALTER TABLE would be very helpful.
Are there any reasons it is not implemented yet ?
Gr
On Apr 18, 2004, at 7:31 PM, Greg Obleshchuk wrote:
Hi Richard,
You know that is the first clear and concise explanation of why not to
store large blobs in a database that I have heard anywhere.
Indeed. But I wonder if most all databases do it the same way? Or do
all file-based dbs do it the sam
On Apr 18, 2004, at 12:25 PM, Richard wrote:
Hmm, in mysql its not so
ok,
So, if I move the test.db database,
into a another folder called db
Then start sqlite
./sqlite
and do this:
.database /db/
this should show the databases in the db folder?
or did I miss something..
Richard,
It doesn't
On Apr 18, 2004, at 9:15 AM, Richard wrote:
Well, I may of have spoken too, soon.
G4:/applications/sqlite rnagle$ ls
SQLite ReadMe.pdf libsqlite.a sqlite
sqlite.htest
G4:/applications/sqlite rnagle$ sqlite test.db
-bash: sqlite: command not foun
On Apr 18, 2004, at 8:56 AM, Richard wrote:
I think,
the problem is that, I did do that,
sqlite test.db
and did not get a correct reply...
Hence, wondering if I'm did right..
Inside the SQLITE folder, is the following:
libsqlite.a
sqlite
sqlite readme.pdf
sqlite.h
Hannes Roth wrote:
Hi.
I don't want to publish that table I used to make that benchmark. So I
created some random data:
http://dl.magiccards.info/speedtest.tar.bz2
$db = sqlite_open("speedtest.sqlite");
$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE
'%a%'");
include("My
Roy Black wrote:
Hello,
I am facing a problem with sqlite. I have no clue how to select records with
a column (DateTime) between two dates. Because sqlite doesn't have built-in
functions I have to select * from a_table and then using code to remove
records. I think this this way is not a good ide
Sorry to hear about your ISP problems.
I have none to suggest because I don't know what exactly you are looking
for... and I am sure with 5 minutes at Google you can find more than I can.
What is your budget?
What are your bandwidth requirements?
If you were using Linode, chances are you are loo
borivoj wrote:
In an attempt to generate more interest for TWS:
You can download windows version on
http://unicast.org/archives/000508.html, follow the link "installer"
While it is still not a single exe file, it works, and it is so easy to
install. I have installed it on a Compact Flash drive
On Mar 19, 2004, at 12:38 AM, Darren Duncan wrote:
On Fri, 19 Mar 2004, Fred Williams wrote:
Yes, about that c.firstname thing...
Do we need to turn in some kind'a formal "enhancement request?"
Seems the
feeling of those responding was that a configurable option was most
desired.
Perhaps a PRA
At 3:12 PM -0500 3/18/04, rich coco wrote:
I have a legacy app that uses mySQL as the embedded RDBMS.
The app makes explicit invocations to mysql library functions.
There is no ODBC layer being used at all.
What about existing SQL statements (explicit arguments to mysql
function calls) that use
Pierre-Yves Delens wrote:
Bonjour,
as a newbie I'm interested in knowing more about tools you seem to be using
:
- Data-Dumper
- DBI
- DBD-SQLite
(I already know about DbManager, SqLitePlus, SqLiteAnalyzer)
Thanks on forward
DBI: http://search.cpan.org/~timb/DBI-1.42/DBI.pm
DBD-SQLite:
http://s
Mateusz Łoskot wrote:
In 03/17/2004 07:30 PM, Will Leshner wrote:
Mateusz Łoskot wrote:
I've just moved one table from my "Big" database. There are about 11000
of records and as I see SQLite database takes about 2,7 MB.
Actually, I consider that pretty small, as databases go. I've been
workin
Andre Vehreschild wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
you're sure this command resulted not in an error. As far as I understand the
documentation the VIEW keyword is mandatory when creating a view. So this
should be:
CREATE VIEW qry_contacts AS
My apologie
First time post, so the appropriate advance apologies apply --
The following situation has me mystified. I created a view, say, like
so --
> CREATE qry_contacts AS
> SELECT c.contact_id, c.firstname, c.lastname,
> (CASE
>WHEN
>(c.firstname & c.lastname) ISNULL
>THEN
>
201 - 269 of 269 matches
Mail list logo
