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

Reply via email to