On Wed, Oct 16, 2013 at 07:32:08AM -0500, John McKown scratched on the wall:
> I'm not truly against such a thing. But we need to remember the main use
> for SQLite is to be small, fast, and "embedded". At least as best as I can
> tell, it is not meant to compete with MariaDB (nee MySQL) or PostgreSQL. It
> doesn't appear to be _committed_ to being 100% SQL compliant (as in exactly
> matching the ANSI/ISO requirements).
> I can't say for sure, but would somebody want to store 20 million rows
> in a SQLite data base? Why?
Overall I agree with your point... SQLite is not trying to be-- and
shouldn't be-- everything to everyone. It is not trying to compete
with or replace large client/server systems. In fact, I think
SQLite's success is largely driven by the fact it compliments such
products so well. Developers have learned the power and usefulness
of RDBMS systems, and want to use that power, experience, and
feature set in environments where a full client/server system would
be impractical.
That said, I don't think it is fair to compare feature set to
database size. The complexity of the database system has little to
do with the size of the dataset it is expected to handle. The "Lite"
in SQLite generally refers to the design, feature set, and "weight"
of the overall database engine, not the size of the data it is
expected to handle. People choose SQLite for where they need to run
it, not what they put into it (except in the extreme degree).
So while I agree with your feeling that this kind of feature may be
getting into a realm that is outside of SQLite's core use-case and market,
that has more to do with how SQLite is used, the class of application
that uses it, and the life-cycle of data stored in SQLite. None of
these things have to do with the *amount* of data stored in a database.
The SQLite team takes some pride (and rightfully so) in how well SQLite
scales to extremely large data sets, even with extremely tight memory
constraints. When things get really big, generally the biggest
performance limitation is the underlying filesystem, not the SQLite
engine.
And for what it's worth, yes-- I have put 20 million rows in an SQLite
database. Considerably more, actually. Some six years ago I imported
all the English text portions of Wikipedia into an SQLite database so
that I could access all the data on an embedded device that was designed
to operate without constant network access. Back then, with some very
carefully tuned string compression, you could just squeeze the whole
thing onto a 4GB SD card. It was actually that project (and my
frustrations with the older SQLite API docs) that lead to me write the
"Using SQLite" book.
Since then I've done some SQLite related consulting work and fire
fighting, and seen SQLite deployed in situations that it really shouldn't
have been... like the center of extremely busy and popular websites.
While it was the wrong choice for the situation, I have to give
SQLite a lot of credit for struggling through the situation. Performance
wasn't great, but the simple fact it worked at all was very impressive.
> I really am curious. Perhaps I'm "out of touch" (won't be the first time).
> My use for SQLite is for storing smallish amount of data which is
> dedicated to a single application. My "large" data base needs are
> relegated to PostgreSQL data bases.
One must remember that different people have radically different
definitions of "big" and "small". As data gets bigger and simple
off-the-shelf desktops and server systems get more powerful, you
also have the situation summed up by one of the data scientists at
Strata last year: "'Big' is getting smaller and 'small' is getting
bigger." Our core application runs databases in the ~2 TB range
(PostgreSQL), which we consider on the smallish side compared to other
companies in the same industry. We often export data into SQLite
files for personal work or analysis, and at any time I've got a dozen
or so SQLite files on my laptop in the 30 to 80 GB range. You can
do an amazing amount of data work, on some pretty big data sets,
with nothing more than SQLite and a semi-descent laptop (especially
if it has an SSD!).
> IMO, the "proper" way to do this is just what you outlined. It is a "one
> shot" and should not take long to run in most cases. Unless those million
> row SQLite data bases are more prevalent than that I had ever thought
Returning to the question at hand, yes, that might be the most proper
way, but there are still concerns. There are a lot of interdependencies
between database objects... tables, views, indexes, foreign keys, and
triggers all need to find each other. ALTER TABLE...RENAME catches
some of these, but not all of them. So going through this process
isn't going to carry over indexes (they'll need to be recreated) but
should, in the end, resolve other triggers that refer back to the
table.
To be honest, for something as simple as adding a NOT NULL
constraint, I would just make sure the column has no NULL entries,
set "pragma writeable_schema = 1", modify the CREATE TABLE
statement in the sqlite_master table, restart everything, and be done
with it. Not exactly proper, but fast and easy.
...and a bit dangerous. Make sure you have a backup first... modifying
sqlite_master lets you play a lot of tricks, but it only takes one minor
mistake to trash the database.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
