Thank you for the information. I guess I assumed the Lite was also for "not too big". The uses I've seen on my Linux system all seem to be that. Oh, and I'm reading your book, off and on, as I get time. I have the Kindle edition. But I keep getting side tracked by silly people at work who keep pestering me to do something useful. <grin/>
On Wed, Oct 16, 2013 at 12:32 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > 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 > -- This is clearly another case of too many mad scientists, and not enough hunchbacks. Maranatha! <>< John McKown _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
