On Fri, Jul 10, 2009 at 10:15:03PM +0100, Simon Slavin scratched on the wall: > > I don't understand why people keep trying to do these things inside > SQL when they're obviously ysing a programming language anyway.
Why wouldn't you? The data is in a database. SQL is how you manipulate data in a database. In the general case, do you really think it makes more sense to pull all that data out of the database, manipulate it, and write it all back? > If the solution was entirely without a programming language there would > be some point to it, but since you have your programming language, why > not take advantage of it? You're assume there is an "advantage" to a traditional programming language over SQL. I'm not sure I consider this fair, outside of the fact that, if you're a traditional programmer that dabbles in database, chances are you're more comfortable with traditional procedural languages over SQL's more set-based declarative style. If you've to a real project, a deadline, and limited experience, that might be the best approach, but since part of the OP's motivation appears to be learning about databases and SQL, it seems the best approach is to learn something about SQL. Also consider that these approaches can be done in the command line tool or any other database management tool. If you're writing a complex app, there is a huge advantage to being able to run and test your pipeline end-to-end in an interactive tool. > Keeping the previous value of a field in a > variable does not take lots of programming ! Every solution presented solved the whole update problem -- no matter what the table size -- in a single SQL command. Perhaps a slightly longer and slightly complex SQL command, but still a single command. They aren't even all that complex or hard to follow, considering some of the gems Igor throws out here. > Read the existing records from taxTable in date order. Compare the > value of 'tax' in this record with the one you remember from the > previous record. No need to write a new table. No need to write the > directions to disk at all, since you apparently only need them for > output. Nobody introduced a new table. Both solutions updated a column in the existing table. But if you want some real reasons, consider the fact that traditionally the link between client and database is considered to be quite expensive. If you have a real client/server system with a slow(er) network between the two it can be a big waste to pull all that data over and then push it all back. It would make a lot more sense to do all the manipulation where the data is, and that means doing it in SQL (or some stored procedure). While these types of limits don't apply to SQLite, and doubly don't apply to an in-memory DB, there is also a strong school of thought that anything that can't be reasonably done in SQL likely shouldn't be done. That is, anything that is difficult to express in SQL is very likely against the spirit of the Relational Model and the fundamental concepts of data integrity. Both of the solutions presented definitely fall into that category, IMHO. Igor hinted at that, pointing out that tables have no inherent ordering, and doing things like using RowID should be considered risky. While the solutions presented get the job done, consider that there isn't any real clean way to do data integrity checks. If a record were deleted so the "span" between two events changed, there is no real simple way to track all that down and fix it up without a fairly complex custom trigger. Making the system more relational where you define a span of events, rather than discrete events, would fix a lot of those issues. The SQL becomes simpler as well, which is always a good hint that you're getting closer to the relational way of thinking. But that's a different discussion for a different time. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
