Re: [sqlite] Denormalisation

[Jay A. Kreibich]

On Mon, Jul 27, 2009 at 02:44:08PM -0700, CityDev scratched on the wall:
> 
> More recently the term 'denormalise' has been used
> instead. This is where you repeat foreign data in a table to avoid the
> overhead of joins at runtime. 

> Over the intervening years I can't ever remember denormalising data (even
> when dealing with eg 13 million insurance customers in a table). Is it OK
> nowadays to say always aim to be fully normalised - modern RDBMSs are
> usually powerful enough to cope with most anything?

  Normalization is a fundamental part of the Relational Model.  The
  first three Normal Forms were defined as part of the original
  research into the Relational Model.

  As you might remember, the basic idea behind normalization is to
  eliminate all duplicate data.  This is critical to data integrity,
  which is critical to the assumptions made by the Relational Model.
  A big part of normalization is making sure that each table (relation)
  is used to represent one and only one "thing."  A side effect of this
  is that when you normalize your design there is only one copy of any
  given atomic data unit.  If there is only one copy, it is impossible
  for that data to get out of sync, further forwarding the ideas of
  data integrity and consistency.

  It also tends to INCREASE performance for everything but SELECT
  statements.  In general, normalized databases tend to be smaller,
  thanks to all the duplicate elimination.  That means less I/O,
  which is a big performance hit for many database system.  If a
  smaller database can hold the same represented data, it also means
  a higher percentage of the unique data can fit into a given size
  cache or memory pool, further reducing the need for I/O.  It also
  means that if you need to update something (insert/update/delete)
  those changes tend to be extremely localized, making the change
  smaller and faster.

  The one trick is that if you split your data up into a bunch of
  tables, you need to spend time gathering it back up for most queries.
  That means JOINs.

  There are two problems with JOINs.  First, unless you're careful
  about your index placement and how the query is setup, they can become
  very expensive.  Normally they're fairly efficient, but it only takes
  a misplaced index to really screw things up.  So you have to
  understand what you're doing and pay attention.  Not a real big deal,
  but you need to have to know what is going on.
  
  The other issue (that plays strongly into the first) is that a lot of
  people fundamentally cannot wrap their heads around JOINs.  If you're
  uncomfortable with JOINs, you tend to design databases that don't
  require them.  And that's really too bad, as the JOIN is, in many
  ways, *The* fundamental Relational operator.


  So, in general, I would say yes... the goal should be 3NF (in nearly
  all cases, if you get to 3NF you get to 5NF... the last two are to
  cover a few somewhat unusual edge cases).  The normal forms really
  buy you a lot of practical benefits, and are a lot more than just a
  bunch of stuffy academic design rules.  
  
  I think it is fair to view denormalization as a last-resort performance
  optimization, but it should be seen as an optimization and it should
  be nearly last resort.  It shouldn't be done until you can prove the
  performance isn't there, that there are no other practical ways of
  getting it back (e.g. better index placement), and that you've
  measured and profiled (and NOT guessed) that the JOINs (or whatever
  the denormalization is going to get rid of) are the actual cause of
  the performance issues.


  The big exception to this, and where the most blatant denormalization
  takes place, is in data warehouses.  That's a pretty special case,
  however.  First, the data is basically write-once, read-many.  Once the
  data is imported, it is usually treated as read-only.  The lack of
  updates means there is less chance of inconsistencies being
  introduced-- assuming the original data is good.  Second, the data is
  usually dumped from another database that has higher normalization.
  That is, the data is "known good" due to the fact that it came from
  what was, in theory, a properly normalized and well formed database.
  This essentially turns the data warehouse into a series of "instanced
  views" where useful sets of data are "pre-queried" into common
  sub-parts of queries that are normally joined together from several
  tables for nearly every query you might want to run.  Essentially
  exactly the kind of thing you might use a view for, only you select
  it into a table.  Since the database is lacks updates except,
  typically, for nightly batch imports, this works out just fine as
  long as you remember to re-form the "views" each time you import new
  data.


  On the other hand, with the advent of the data driven website, many
  databases see a far greater number of reads than writes, so it makes
  some sense to push read 

[sqlite] How to dump cookies.sqlite

[Mutiullah Qureshi]

Hello friends.
I have downloaded sqlite3-3.6.16.bin.gz and extracted sqlite3-3.6.16.bin in 
fedora 9. Now I want to dump cookies.sqlite from mozila firefox cache to 
cookies.tmp. I have tried:

sqlite> .dump cookies.sqlite cookies.tmp
BEGIN TRANSACTION;
COMMIT;
sqlite> 

But this does not create any cookies.tmp. I am a newbie on this environment, 
just switched from Windows systems. PLease guide me.



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] hi - sqlite3 advantages

[An]

Hi !

SQLite is a really cool database...

I currently still use version 2... I would like the insight of whoever feels
like it of some advantages of version 3 over version 2 maybe i get
convinced on the importance to make the version evolution, or possible mayor
advantages ; )

Thank you,


An M
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Denormalisation

[Jim Showalter]

Try writing apps for cellphones and both space and time become 
important again!

- Original Message - 
From: "Simon Slavin" 
To: "General Discussion of SQLite Database" 
Sent: Monday, July 27, 2009 3:33 PM
Subject: Re: [sqlite] Denormalisation


>
> On 27 Jul 2009, at 10:44pm, CityDev wrote:
>
>> Over the intervening years I can't ever remember denormalising data
>> (even
>> when dealing with eg 13 million insurance customers in a table). Is
>> it OK
>> nowadays to say always aim to be fully normalised - modern RDBMSs 
>> are
>> usually powerful enough to cope with most anything?
>
> Performance/space/time/money payoff calculation.  The smallest 
> mundane
> laptop these days comes with a 120 Gig hard disk.  No real need to
> economise on file size, so if you need fast processing you might use
> lots of space.  On the other hand if you need to cart your entire
> dataset around on a USB Flash drive you might want to save space.
>
> The advantage is that most programmers don't have to care which way
> they go: they write the program any way it occurs to them.  The
> disadvantage of this is that when they hit the first job where the
> difference does matter, they don't know what to do about it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Denormalisation

[BareFeet]

> Is it OK nowadays to say always aim to be fully normalised - modern  
> RDBMSs are usually powerful enough to cope with most anything?


I suggest that aiming for fully normalised databases is the way to go,  
for the traditional and still relevant reasons of data integrity, file  
size, predictability, and generally only having to make data changes  
once. AFAIK database engines such as SQLite are optimised to cope with  
joins without suffering slowdowns and may even be faster, depending on  
the schema and data set. For instance an integer primary key if more  
efficiently searched than text.

> what people would say is normalising is the easy part; the skill  
> comes in 'collapsing'

I would agree that "collapsing" the normalized data into something  
humanly readable if probably the biggest difficulty. Joining tables to  
show related data is fairly trivial for an database designer worth  
their salt, but it can be difficult to facilitate updates to that  
data, where user input requires changing several related tables with  
related values.

I suggest that more fully exploiting "views" makes this easier to set  
up and maintain. In particular, using "instead of" triggers on the  
views greatly simplifies the design while shifting the burden of data  
integrity to the SQL engine, rather than creating pages of code in  
your own application. "Instead of" triggers cope with inserts, deletes  
and updates in a view, passing on the actions as desired to the  
underlying tables or raising exceptions for disallowed data etc. Your  
application (or even SQL command line) can then facilitate user  
changes to data in the views in the same way that it would to a  
denormalised table. It is fully portable from one application to  
another.

Tom
BareFeet

  --
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Simon Slavin]

On 27 Jul 2009, at 7:37pm, Paul Claessen wrote:

> So .. would anyone know a good book for seasoned programmers, who  
> are new to databases, that addresses all these issues?

If you're a seasoned programmer you probably don't need my advice.   
The problem is not databases, it's an understanding of how data in  
general can be handled: most RDBS systems are really just clever and  
intensive applications of balanced trees.  The archetypal books are

Knuth: _Art of Computer Programming_

once you've looked at those try

Aho, Ullman, Hopcroft: _Data Structures and Algorithms_

but these days you can learn a hell of a lot just Googling for  
'introduction theory RDBS' or variations on it.  Textbooks might be  
obsolete.  And I'm sorry but I learned this stuff so long ago I can no  
longer evaluate which books are good at teaching it: I can't put  
myself in the shoes of a learner.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] totally OT: debunking debunking SQL

[Simon Slavin]

On 27 Jul 2009, at 10:49pm, CityDev wrote:

> Codd had his 'extended relational model' and I think Chris Date has  
> got the
> Third Manifesto. Unfortunately people can't be satisfied they've  
> invented
> something really, really simple and just feel proud, they want to  
> become
> professors and write impenetrably clever papers that only their  
> colleagues
> can follow.

You don't get the big bucks for becoming an internationally-known  
inventor of an incredibly useful data-handling paradigm.  But a  
tenured professor can earn big bucks for years.  I work at a  
university and I meet this sort of person every day.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Still have high cpu usage when deleting old data with indices

[Simon Slavin]

On 27 Jul 2009, at 11:09pm, W.-H. Gu wrote:

>  In my C application, I create a table and insert 32 rows every  
> second.
> Each row has about 28KB. Every 5 seconds, it deletes old rows with  
> timestamp
> <= current time - PERIOD. I did create an index on the column  
> 'timestamp,'
> but I observed that every 5 seconds, cpu usage hits up to 9%.  
> Without index,
> cpu usage is just a bit more (10~11%). This doesn't happen when I  
> disable
> the delete operations.

First, I assume that this deletion is done with one DELETE command.

Second, percentage CPU figures don't mean much unless the computer is  
doing other CPU-intensive tasks.  If this SQL application is the only  
real task it's doing, a change of even 50% in CPU usage means  
nothing.  Look at CPU usage as a total, not as a percentage.

>  My question is: is there a discussion saying that delete in SQLite  
> may use
> more cpu usage than others like MySQL?

This may be an insert/delete payoff.  MySQL may do the bare minimum  
when you delete records, on the assumption that you want the DELETE to  
run as fast as possible.  SQLite may do some clean-up work when you  
delete records to make any subsequent INSERT command run as quickly as  
possible.  So you need to look at the total CPU usage over all  
transactions, not just the DELETE commands in isolation.

> is there a way to fix it?

First, make sure something is wrong.  Don't engage in premature  
optimisation.

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Darren Duncan]

Paul Claessen wrote:
> So .. would anyone know a good book for seasoned programmers, who are new to 
> databases, that addresses all these issues?

I suggest one of C. J. Date's latest works:

See http://oreilly.com/catalog/9780596523060/ .

SQL and Relational Theory
How to Write Accurate SQL Code
By C.J. Date
January 2009
Pages: 426
Series: Theory In Practice
ISBN 10: 0-596-52306-8 | ISBN 13: 978059652306

Description
Understanding SQL's underlying theory is the best way to guarantee that your 
SQL 
code is correct and your database schema is robust and maintainable. In SQL and 
Relational Theory, author C.J. Date demonstrates how you can apply relational 
theory directly to your use of SQL, with numerous examples and clear 
explanations of the reasoning behind them. Anyone with a modest to advanced 
background in SQL will benefit from the many insights in this book.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Denormalisation

[Simon Slavin]

On 27 Jul 2009, at 10:44pm, CityDev wrote:

> Over the intervening years I can't ever remember denormalising data  
> (even
> when dealing with eg 13 million insurance customers in a table). Is  
> it OK
> nowadays to say always aim to be fully normalised - modern RDBMSs are
> usually powerful enough to cope with most anything?

Performance/space/time/money payoff calculation.  The smallest mundane  
laptop these days comes with a 120 Gig hard disk.  No real need to  
economise on file size, so if you need fast processing you might use  
lots of space.  On the other hand if you need to cart your entire  
dataset around on a USB Flash drive you might want to save space.

The advantage is that most programmers don't have to care which way  
they go: they write the program any way it occurs to them.  The  
disadvantage of this is that when they hit the first job where the  
difference does matter, they don't know what to do about it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] totally OT: debunking debunking SQL

[Darren Duncan]

(Top-posting, sorry.)

What gives is that dbdebunk.com is effectively a fanboy site that has gone too 
far and it should generally be ignored; it is not official and has about as 
much 
to do with Date or The Third Manifesto as a rabid fan site for some TV show or 
sport has to do with the creators of that show or sport.

The *official* site of C.J.Date, and Hugh Darwen, is 
http://thethirdmanifesto.com/ .  It is just these two that co-authored The 
Third 
Manifesto.  This web site is reasonably civilized and is where you want to 
look. 
  (Also, all the electronic material there is free.)

Date and Darwen are very reasonable people; don't let Pascal drag them down.

On a tangent, C.J.Date continues to write books, which are quite useful.  For 
example, the latest one is "SQL and Relational Theory", 
http://oreilly.com/catalog/9780596523060/ , which among other things 
illustrates 
how to better make use of the SQL DBMSs we already have.

-- Darren Duncan

P Kishor wrote:
> from the recent thread on "what is a relation," I followed Jay's
> suggestion and started reading up on relational division (an article
> by Celko at http://www.dbazine.com/ofinterest/oi-articles/celko1).
> That led me to reading up more on CJ Date, to an interview of Date at
> http://www.oreillynet.com/pub/a/network/2005/07/29/cjdate.html which
> led me to Fabian Pascal's http://www.dbdebunk.com/index.html.
> 
> I came back less than overwhelmed. I found a lot of ranting on DB
> Debunk generally converging toward, "if you need to ask questions
> about SQL then you are an idiot and need to go back to the drawing
> board and read up some good, dry, humorless books" (I am
> paraphrasing). Most of it was bashing some poor woman named Dawn
> Wolthuis and even calling Celko an idiot.
> 
> I have no doubt that Date is a big man in SQL history. I am not so
> sure about Fabian Pascal's pedigree other than that he was an
> associate of Date and Codd. Nevertheless, I am not quite taken by
> their penchant to bandy the "idiot" label so liberally. I spent a
> couple of hours on dbdebunk.com website, which is horrible to read in
> its attractiveness and design, and frankly came back not very
> informed... for most things it seemed to offer a, "go back, read up,
> get a degree, then come and ask questions" kind of attitude. I am none
> the wiser about multivalue databases, trees, or n-normal form.
> 
> Give me Dennis Cote, Roger Binns, Kees Nuyt, and Jay Kreibich any
> day... or, just give me Igor Tantednik... over these other SQL "gods"
> (gods spelled in lowercase).
> 
> What gives?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[Rich Shepard]

On Mon, 27 Jul 2009, Rael Bauer wrote:

> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will
> the declared default value be stored as a string or real value?

Rael,

   What you have above is data type DATE. DATETIME includes the TIME
component.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[Rich Shepard]

On Mon, 27 Jul 2009, Rael Bauer wrote:

> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will
> the declared default value be stored as a string or real value?

Rael,

   String (the actual data storage type name is TEXT).

> Also, more generally, how can I find out what storage type field values
> have been stored in?

   Section 6.3 of Rick van der Lans's new "The SQL Guide to SQLite" covers
this topic. Use the 'typeof' command. Example:

SELECT typeof ('2009-07-27') as date;

   The returned result:

date

text

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Denormalisation

[Rich Shepard]

On Mon, 27 Jul 2009, CityDev wrote:

> Over the intervening years I can't ever remember denormalising data (even
> when dealing with eg 13 million insurance customers in a table). Is it OK
> nowadays to say always aim to be fully normalised - modern RDBMSs are
> usually powerful enough to cope with most anything?

   My opinion: _always_ go for 4th normal form. I believe that it was both
less robust RDBMSs and less powerful hardware that would justify
denormalizing for speed on rare occasions.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Still have high cpu usage when deleting old data with indices

[W.-H. Gu]

Hi,

  In my C application, I create a table and insert 32 rows every second.
Each row has about 28KB. Every 5 seconds, it deletes old rows with timestamp
<= current time - PERIOD. I did create an index on the column 'timestamp,'
but I observed that every 5 seconds, cpu usage hits up to 9%. Without index,
cpu usage is just a bit more (10~11%). This doesn't happen when I disable
the delete operations.

  My question is: is there a discussion saying that delete in SQLite may use
more cpu usage than others like MySQL? is there a way to fix it? or perhaps
I am just missing something?

  Thanks,

  WeiHsin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DATETIME and storage type

[P Kishor]

On Mon, Jul 27, 2009 at 4:45 PM, Rael Bauer wrote:
> Hi,
>
> If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table 
> "notes" add column "last_modified" DATETIME default "2001-01-01";) will the 
> declared default value be stored as a string or real value?
>

string

> Also, more generally, how can I find out what storage type field values have 
> been stored in?
>

typeof()


> Thanks
> Rael Bauer
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] totally OT: debunking debunking SQL

[CityDev]

Codd had his 'extended relational model' and I think Chris Date has got the
Third Manifesto. Unfortunately people can't be satisfied they've invented
something really, really simple and just feel proud, they want to become
professors and write impenetrably clever papers that only their colleagues
can follow. 
-- 
View this message in context: 
http://www.nabble.com/totally-OT%3A-debunking-debunking-SQL-tp24687789p24688728.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DATETIME and storage type

[Rael Bauer]

Hi,
 
If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table 
"notes" add column "last_modified" DATETIME default "2001-01-01";) will the 
declared default value be stored as a string or real value?
 
Also, more generally, how can I find out what storage type field values have 
been stored in?
 
Thanks
Rael Bauer


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Denormalisation

[CityDev]

I did a Computer Science MSc 30 years ago specialising in databases (the
relational model was only in prototypes). Of course normalisation was well
known, but what people would say is normalising is the easy part; the skill
comes in 'collapsing'. More recently the term 'denormalise' has been used
instead. This is where you repeat foreign data in a table to avoid the
overhead of joins at runtime. 

Over the intervening years I can't ever remember denormalising data (even
when dealing with eg 13 million insurance customers in a table). Is it OK
nowadays to say always aim to be fully normalised - modern RDBMSs are
usually powerful enough to cope with most anything?
-- 
View this message in context: 
http://www.nabble.com/Denormalisation-tp24688494p24688494.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Rich Shepard]

On Mon, 27 Jul 2009, Paul Claessen wrote:

> So .. would anyone know a good book for seasoned programmers, who are new
> to databases, that addresses all these issues?

Paul,

   Any of Joe Celko's books. His "SQL Programming Style" is particularly good
for an overview. The amazon.com listing lets you examine the ToC, among
other extracts.

   Also, Rick van der Lans's "Introduction to SQL, 4th Ed."

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] totally OT: debunking debunking SQL

[P Kishor]

caution: this thread has a very high probability of veering into the
subjective and the incoherent, and veer far away from SQLite,
nevertheless...

from the recent thread on "what is a relation," I followed Jay's
suggestion and started reading up on relational division (an article
by Celko at http://www.dbazine.com/ofinterest/oi-articles/celko1).
That led me to reading up more on CJ Date, to an interview of Date at
http://www.oreillynet.com/pub/a/network/2005/07/29/cjdate.html which
led me to Fabian Pascal's http://www.dbdebunk.com/index.html.

I came back less than overwhelmed. I found a lot of ranting on DB
Debunk generally converging toward, "if you need to ask questions
about SQL then you are an idiot and need to go back to the drawing
board and read up some good, dry, humorless books" (I am
paraphrasing). Most of it was bashing some poor woman named Dawn
Wolthuis and even calling Celko an idiot.

I have no doubt that Date is a big man in SQL history. I am not so
sure about Fabian Pascal's pedigree other than that he was an
associate of Date and Codd. Nevertheless, I am not quite taken by
their penchant to bandy the "idiot" label so liberally. I spent a
couple of hours on dbdebunk.com website, which is horrible to read in
its attractiveness and design, and frankly came back not very
informed... for most things it seemed to offer a, "go back, read up,
get a degree, then come and ask questions" kind of attitude. I am none
the wiser about multivalue databases, trees, or n-normal form.

Give me Dennis Cote, Roger Binns, Kees Nuyt, and Jay Kreibich any
day... or, just give me Igor Tantednik... over these other SQL "gods"
(gods spelled in lowercase).

What gives?



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[John Elrick]

Beau Wilkinson wrote:
>>> There are still people who just want
>>> a cursor to a chunk of data which they pull in and iterate over rather than
>>> use SQL's power to manage data a set-at-a-time
>>>   
>
> I am dealing with such a project now. The schema consists of time stamp plus 
> blob, where the blobs "map" directly to C++ structs. Of course, there are all 
> sorts of useful data items in those blobs, and many of the capabilities of 
> SQL are lost by reducing data to blobs. I think this is an extreme example of 
> what you describe. The architectural excuse given was, I think, performance.
>
>   
>>> Call me old fashioned but object-relational mappers worry me on this score
>>>   
>
> I agree, except that you're not old-fashioned. In fact, I think you're on the 
> cutting edge here. OO (including any attempt to apply it to database work) is 
> no longer trendy. At best, it is tolerated... perhaps many people still 
> haven't admitted that the proverbial emporer has no clothes, but no one's 
> bragging about his (i.e. OOP's) new jacket, either.
>
> Microsoft, for example, is moving rapidly to functional programming and 
> generic programming. Implementation inheritance has been lobotomized in C# 
> compared to C++, for example, and Microsoft's new "Linq" database client 
> technology is functional and generic.
>
> As for ORM, OODBMS (whatever that is), etc. proponents of such technologies 
> continue to hem and haw about "object-relational impedance." This is just  a 
> fancy way of describing the problems that result when a good (Relational) 
> model of reality must interface with a dysfunctional (OO) model. OO creates 
> problems (or "opportunities"...) because it doesn't work. MIT has been saying 
> this for 30 years. Few people are willing to consciously stick their necks 
> out and say, "you know, I've always thought OOP was a bunch of B.S" but the 
> sentiment's out there and it's justified.

I believe this is too broad a condemnation.  Every approach has 
limitations and areas where it is more or less useful; Object Oriented 
programming is no exception, and the fact that OO has been misused does 
not in and of itself support the assertion that it is "dysfunctional".

To highlight two examples, functional languages such as Haskell are 
elegant and pure...until you add petty, non-deterministic things such as 
I/O and user interactions.  Monads are a workaround, and even their 
physical and syntactic representation demonstrate the limitations of the 
pure functional approach.

Example two is an oft asked issue with SQL in general.  One of the most 
common needs of a system which interacts with a human being via a 
graphic interface is the ability to reference an object by its position 
within a given resultset.  Although there are many workarounds for this 
issue, workarounds they are and a strategy which is both fast and 
non-cumbersome for accessing the nth element in a given resultset via 
pure SQL continues to elude the profession.

The fact that monads are cumbersome and SQL cannot easily return results 
which can be accessed as though they were an array are not indictments 
of Haskell or SQL.  Likewise the obvious issues of attempting a simple 
mechanism for persisting in-memory objects to an auxiliary storage 
medium are limitations of the Object Oriented approach, and, in my 
opinion, should not be considered a condemnation of Object Oriented 
design as a whole.

Our organization has been using OO based languages for over a decade 
and, while we have encountered cases where other approaches are 
superior, we have found it in general to be an excellent method for 
solving our client's problems.

FWIW


John Elrick
Fenestra Technologies


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance tuning the Lookaside memory allocator

[Shaun Seckman (Firaxis)]

Hello,

I'm in the process of tuning my allocators such that the
fixed buffers will be enough to prevent any overflow from occuring.
I've been using the memory statistics to tweak the page cache, and
scratch buffers accordingly but found that there are no such mechanisms
for tweaking the lookaside memory allocator.  How can I determine what
the largest allocation is on the Lookaside allocator as well as whether
or not an overflow occurred either due to the allocation being to large
or not enough space is available.

 

-Shaun

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Darren Duncan]

Rich Shepard wrote:
> On Mon, 27 Jul 2009, Darren Duncan wrote:
>> Object orientation has nothing to do with all this per se, though objects
>> can easily be mapped to tuples.
> 
>A related issue is that object orientation is almost always used in the
> context of procedural languages (e.g., C++, Python, Ruby) while SQL is _not_
> a procedural language. SQL is a language for working with sets (tables, or
> relations).

I think I realized after I said it that I should have stated that last point 
better.  What I meant to say is that ...

Object orientation is just syntactic sugar for working with values, types, 
routines/functions, etc, and OO issues are orthogonal to the relational model 
of 
data; you can talk about relations/etc without talking about OO.

Also, given that in typical OO languages, an object can represent either an 
(immutable) value or a (mutable) variable, all of the concepts in the 
relational 
model or in SQL can be cleanly represented by objects.

Relations (rowsets) and tuples (rows) are *values*, and are fundamentally 
immutable, same as numbers/etc; they are like having array or hash-map values 
of 
a typical programming language.  You can give them as arguments to functions, 
return them from functions, assign them to variables, etc.  So SQL tables are 
essentially variables that hold relation values; when you make a change to a 
table, you are substituting its previous relation value for a new one, 
conceptually the same thing as when you change a numeric variable.

The relational types and operators can all be implemented in general purpose 
languages if one wanted to and integrated into a normal program.  For example, 
see http://search.cpan.org/dist/Set-Relation/ where I did just that; note that 
this is more of a demonstration, and I'm sure any SQL DBMS has it beat for 
performance.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Paul Claessen]

So .. would anyone know a good book for seasoned programmers, who are new to 
databases, that addresses all these issues?

~ Paul Claessen

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon
> Slavin
> Sent: Monday, July 27, 2009 12:34 PM
> To: j...@kreibi.ch; General Discussion of SQLite Database
> Subject: Re: [sqlite] What is a Relation?
> 
> 
> On 27 Jul 2009, at 2:49pm, Jay A. Kreibich wrote:
> 
> >  That's because most people are, unfortunately, taught SQL in a vacuum
> >  with none of the theory or background.
> 
> 
> Yes yes.  Hence the recent rash of people on this list who can't dry-
> run their software, don't understand what an index is, have no
> appreciation of how to guess him much work a computer is doing, etc..
> This is a big problem in the industry in general: they teach you how
> to declare an index without explaining why or what kind of index will
> help you.
> 
> I keep wanting to scream "If you don't grok the need you shouldn't be
> a programmer to start with.".  But it's not their fault, it's their
> bosses' fault.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to disable fsync() in SQLite

[Petite Abeille]

On Jul 27, 2009, at 8:31 PM, W.-H. Gu wrote:

> Is there a way to disable fsync()

pragma synchronous = off

http://www.sqlite.org/pragma.html#pragma_synchronous

--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to disable fsync() in SQLite

[Pavel Ivanov]

Execute

pragma synchronous = off;

after opening connection to database. More info:
http://www.sqlite.org/pragma.html#pragma_synchronous.


Pavel

On Mon, Jul 27, 2009 at 2:31 PM, W.-H. Gu wrote:
> Hi,
>
>   Is there a way to disable fsync() in my SQLite application on Linux? I
> saw a discussion of SQLite performance at
> http://www.sqlite.org/speed.htmland it has some results with option
> 'nosync.' I am wondering if I can do the
> same thing as there to see how fsync() effects the performance in my
> applicaion.
>
>   Thanks,
>
>            WeiHsin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to disable fsync() in SQLite

[W.-H. Gu]

Hi,

   Is there a way to disable fsync() in my SQLite application on Linux? I
saw a discussion of SQLite performance at
http://www.sqlite.org/speed.htmland it has some results with option
'nosync.' I am wondering if I can do the
same thing as there to see how fsync() effects the performance in my
applicaion.

   Thanks,

WeiHsin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite lock behavior in shared-cache mode

[Jim Showalter]

Does SQLite support MVCC 
(http://en.wikipedia.org/wiki/Multiversion_concurrency_control)? It 
sounds like it doesn't. Maybe it should--that's a very nice way to 
support unblocked reads while still getting mutex for writes.

- Original Message - 
From: "Dan" 
To: "General Discussion of SQLite Database" 
Sent: Monday, July 27, 2009 9:27 AM
Subject: Re: [sqlite] SQLite lock behavior in shared-cache mode


>
> While compiling any statement (sqlite3_prepare_v2()), or while 
> stepping
> (sqlite3_step()) a statement that accesses the main database, a 
> mutex
> associated with the in-memory cache of the main database will be 
> held.
>
> Dan.
>
>
>
> On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote:
>
>> Hi,
>>
>> I'm using SQLite latest version (3.6.16) with shared-cache enable 
>> in
>> a process that has around 5 threads. Database connections for each
>> thread are created with the same main database file. After that, 
>> each
>> connection is attached to a particular database file (one for each
>> thread) using the same schema name. Final structure is similar to 
>> the
>> following:
>>
>> Main database file: main.db
>> Thread-1 database file: thread1.db
>> Thread-2 database file: thread2.db
>> Thread-3 database file: thread3.db
>> Thread-4 database file: thread4.db
>> Thread-5 database file: thread5.db
>>
>> Thread-1 connection is opened with the main.db file and attaches 
>> the
>> thread1.db as "extradb" schema name;
>> Thread-2 connection is opened with the main.db file and attaches 
>> the
>> thread2.db as "extradb" schema name;
>> Thread-3 connection is opened with the main.db file and attaches 
>> the
>> thread3.db as "extradb" schema name;
>> Thread-4 connection is opened with the main.db file and attaches 
>> the
>> thread4.db as "extradb" schema name;
>> Thread-5 connection is opened with the main.db file and attaches 
>> the
>> thread5.db as "extradb" schema name;
>>
>> Every SQL statement submitted to the process and passed to one of
>> these threads can read global informations maintained in the 
>> main.db
>> database file ("main" schema) and write/read particular 
>> informations
>> in the "extradb" schema in such a way that one thread does not need 
>> to
>> wait for another thread to write its information, since each thread
>> has the "extradb" schema attached to a particular database file.
>>
>> Shared-cache is used for 2 reasons:
>> - to improve main.db database file data access; and,
>> - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to
>> avoid many SQLITE_LOCKED errors based on the code provided in 
>> http://www.sqlite.org/unlock_notify.html
>> .
>>
>> In my understanding, the expected behavior should be:
>> - If a SQL statement with only read (heavy) operations is passed to
>> Thread-1; and,
>> - Another SQL statement with a write (and some reads) operations is
>> passed to Thread-3;
>> - Both should run in parallel in a multi-core system.
>>
>> But, it seems that the Thread-3 is waiting for the Thread-1 to 
>> finish
>> its work before continue.
>> This behavior is turning the solution into a non-scalable solution.
>>
>> As far as I could debug (and understand) using Visual Studio 2005, 
>> it
>> seems that Thread-3 (in the above example) is waiting in for a lock 
>> in
>> the sqlite3BtreeEnterAll function. See the piece of the call stack
>> below:
>>
>>> sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8)  Line 
>>> 15159
>>  sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8)  Line
>> 36706 + 0x11 bytes
>>  sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const
>> char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1,
>> sqlite3_stmt * * ppStmt=0x, const char * * 
>> pzTail=0x0965f63c)
>> Line 9672
>>  sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char
>> * zSql=0x00c0, int nBytes=180, sqlite3_stmt * * 
>> ppStmt=0x013a9094,
>> const char * * pzTail=0x0965f63c)  Line 9747 + 0x1f bytes
>>  apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 *
>> db=0x, const char * zSql=, int nSql=, sqlite3_stmt * *
>> ppStmt=0x, const char * * pz=0x)  Line 247 + 0x10
>> bytes
>>  apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *
>> pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * *
>> results=0x0965f688, const char * query=0x00c0, int seek=0) 
>> Line
>> 307 + 0x33 bytes
>>  libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t *
>> driver=0x00a66270, apr_pool_t * pool=0x013a9050, apr_dbd_t *
>> handle=0x029cc040, apr_dbd_results_t * * res=0x0965f688, const char 
>> *
>> statement=0x00c0, int random=0)  Line 319 + 0x22 bytes
>>
>> While Thread-1 call stack looks like this:
>>
>>> sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x1c471318)  Line 52862
>>  sqlite3.dll!sqlite3Step(Vdbe * p=0x)  Line 49388 + 0x7 
>> bytes
>>  sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=)  Line 49449 + 0x7
>> bytes
>>  

Re: [sqlite] SQLite lock behavior in shared-cache mode

[Alessandro Merolli]

Thanks again Dan for the quick response.
I understood.
Is this also true for any other database file attached to these  
threads connections that, as the main.db file, are used for read-only  
operations but might have one or more threads reading from it at the  
same time?

Alessandro.

On 27/07/2009, at 13:27, Dan wrote:

>
> While compiling any statement (sqlite3_prepare_v2()), or while  
> stepping
> (sqlite3_step()) a statement that accesses the main database, a mutex
> associated with the in-memory cache of the main database will be held.
>
> Dan.
>
>
>
> On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote:
>
>> Hi,
>>
>>  I'm using SQLite latest version (3.6.16) with shared-cache enable in
>> a process that has around 5 threads. Database connections for each
>> thread are created with the same main database file. After that, each
>> connection is attached to a particular database file (one for each
>> thread) using the same schema name. Final structure is similar to the
>> following:
>>
>>  Main database file: main.db
>>  Thread-1 database file: thread1.db
>>  Thread-2 database file: thread2.db
>>  Thread-3 database file: thread3.db
>>  Thread-4 database file: thread4.db
>>  Thread-5 database file: thread5.db
>>
>>  Thread-1 connection is opened with the main.db file and attaches the
>> thread1.db as "extradb" schema name;
>>  Thread-2 connection is opened with the main.db file and attaches the
>> thread2.db as "extradb" schema name;
>>  Thread-3 connection is opened with the main.db file and attaches the
>> thread3.db as "extradb" schema name;
>>  Thread-4 connection is opened with the main.db file and attaches the
>> thread4.db as "extradb" schema name;
>>  Thread-5 connection is opened with the main.db file and attaches the
>> thread5.db as "extradb" schema name;
>>
>>  Every SQL statement submitted to the process and passed to one of
>> these threads can read global informations maintained in the main.db
>> database file ("main" schema) and write/read particular informations
>> in the "extradb" schema in such a way that one thread does not need  
>> to
>> wait for another thread to write its information, since each thread
>> has the "extradb" schema attached to a particular database file.
>>
>>  Shared-cache is used for 2 reasons:
>>  - to improve main.db database file data access; and,
>>  - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to
>> avoid many SQLITE_LOCKED errors based on the code provided in 
>> http://www.sqlite.org/unlock_notify.html
>> .
>>
>>  In my understanding, the expected behavior should be:
>>  - If a SQL statement with only read (heavy) operations is passed to
>> Thread-1; and,
>>  - Another SQL statement with a write (and some reads) operations is
>> passed to Thread-3;
>>  - Both should run in parallel in a multi-core system.
>>
>>  But, it seems that the Thread-3 is waiting for the Thread-1 to  
>> finish
>> its work before continue.
>>  This behavior is turning the solution into a non-scalable solution.
>>
>>  As far as I could debug (and understand) using Visual Studio 2005,  
>> it
>> seems that Thread-3 (in the above example) is waiting in for a lock  
>> in
>> the sqlite3BtreeEnterAll function. See the piece of the call stack
>> below:
>>
>>> sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8)  Line 15159
>>  sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8)  Line
>> 36706 + 0x11 bytes
>>  sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const
>> char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1,
>> sqlite3_stmt * * ppStmt=0x, const char * * pzTail=0x0965f63c)
>> Line 9672
>>  sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char
>> * zSql=0x00c0, int nBytes=180, sqlite3_stmt * *  
>> ppStmt=0x013a9094,
>> const char * * pzTail=0x0965f63c)  Line 9747 + 0x1f bytes
>>  apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 *
>> db=0x, const char * zSql=, int nSql=, sqlite3_stmt * *
>> ppStmt=0x, const char * * pz=0x)  Line 247 + 0x10
>> bytes
>>  apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *
>> pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * *
>> results=0x0965f688, const char * query=0x00c0, int seek=0)  Line
>> 307 + 0x33 bytes
>>  libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t *
>> driver=0x00a66270, apr_pool_t * pool=0x013a9050, apr_dbd_t *
>> handle=0x029cc040, apr_dbd_results_t * * res=0x0965f688, const char *
>> statement=0x00c0, int random=0)  Line 319 + 0x22 bytes
>>
>>  While Thread-1 call stack looks like this:
>>
>>> sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x1c471318)  Line 52862
>>  sqlite3.dll!sqlite3Step(Vdbe * p=0x)  Line 49388 + 0x7 bytes
>>  sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=)  Line 49449 + 0x7
>> bytes
>>  

Re: [sqlite] What is a Relation?

[Simon Slavin]

On 27 Jul 2009, at 2:49pm, Jay A. Kreibich wrote:

>  That's because most people are, unfortunately, taught SQL in a vacuum
>  with none of the theory or background.


Yes yes.  Hence the recent rash of people on this list who can't dry- 
run their software, don't understand what an index is, have no  
appreciation of how to guess him much work a computer is doing, etc..   
This is a big problem in the industry in general: they teach you how  
to declare an index without explaining why or what kind of index will  
help you.

I keep wanting to scream "If you don't grok the need you shouldn't be  
a programmer to start with.".  But it's not their fault, it's their  
bosses' fault.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite lock behavior in shared-cache mode

[Dan]

While compiling any statement (sqlite3_prepare_v2()), or while stepping
(sqlite3_step()) a statement that accesses the main database, a mutex
associated with the in-memory cache of the main database will be held.

Dan.



On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote:

> Hi,
>
>   I'm using SQLite latest version (3.6.16) with shared-cache enable in
> a process that has around 5 threads. Database connections for each
> thread are created with the same main database file. After that, each
> connection is attached to a particular database file (one for each
> thread) using the same schema name. Final structure is similar to the
> following:
>
>   Main database file: main.db
>   Thread-1 database file: thread1.db
>   Thread-2 database file: thread2.db
>   Thread-3 database file: thread3.db
>   Thread-4 database file: thread4.db
>   Thread-5 database file: thread5.db
>
>   Thread-1 connection is opened with the main.db file and attaches the
> thread1.db as "extradb" schema name;
>   Thread-2 connection is opened with the main.db file and attaches the
> thread2.db as "extradb" schema name;
>   Thread-3 connection is opened with the main.db file and attaches the
> thread3.db as "extradb" schema name;
>   Thread-4 connection is opened with the main.db file and attaches the
> thread4.db as "extradb" schema name;
>   Thread-5 connection is opened with the main.db file and attaches the
> thread5.db as "extradb" schema name;
>
>   Every SQL statement submitted to the process and passed to one of
> these threads can read global informations maintained in the main.db
> database file ("main" schema) and write/read particular informations
> in the "extradb" schema in such a way that one thread does not need to
> wait for another thread to write its information, since each thread
> has the "extradb" schema attached to a particular database file.
>
>   Shared-cache is used for 2 reasons:
>   - to improve main.db database file data access; and,
>   - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to
> avoid many SQLITE_LOCKED errors based on the code provided in 
> http://www.sqlite.org/unlock_notify.html
> .
>
>   In my understanding, the expected behavior should be:
>   - If a SQL statement with only read (heavy) operations is passed to
> Thread-1; and,
>   - Another SQL statement with a write (and some reads) operations is
> passed to Thread-3;
>   - Both should run in parallel in a multi-core system.
>
>   But, it seems that the Thread-3 is waiting for the Thread-1 to finish
> its work before continue.
>   This behavior is turning the solution into a non-scalable solution.
>
>   As far as I could debug (and understand) using Visual Studio 2005, it
> seems that Thread-3 (in the above example) is waiting in for a lock in
> the sqlite3BtreeEnterAll function. See the piece of the call stack
> below:
>
>>  sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8)  Line 15159
>   sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8)  Line
> 36706 + 0x11 bytes
>   sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const
> char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1,
> sqlite3_stmt * * ppStmt=0x, const char * * pzTail=0x0965f63c)
> Line 9672
>   sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char
> * zSql=0x00c0, int nBytes=180, sqlite3_stmt * * ppStmt=0x013a9094,
> const char * * pzTail=0x0965f63c)  Line 9747 + 0x1f bytes
>   apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 *
> db=0x, const char * zSql=, int nSql=, sqlite3_stmt * *
> ppStmt=0x, const char * * pz=0x)  Line 247 + 0x10  
> bytes
>   apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *
> pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * *
> results=0x0965f688, const char * query=0x00c0, int seek=0)  Line
> 307 + 0x33 bytes
>   libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t *
> driver=0x00a66270, apr_pool_t * pool=0x013a9050, apr_dbd_t *
> handle=0x029cc040, apr_dbd_results_t * * res=0x0965f688, const char *
> statement=0x00c0, int random=0)  Line 319 + 0x22 bytes
>
>   While Thread-1 call stack looks like this:
>
>>  sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x1c471318)  Line 52862
>   sqlite3.dll!sqlite3Step(Vdbe * p=0x)  Line 49388 + 0x7 bytes
>   sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=)  Line 49449 + 0x7
> bytes
>   apr_dbd_sqlite3.dll!sqlite3_blocking_step(sqlite3_stmt *
> pStmt=0x)  Line 220 + 0x6 bytes
>   apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *
> pool=0x01340958, apr_dbd_t * sql=0x012e1e28, apr_dbd_results_t * *
> results=0x09b5f688, const char * query=0x0139c1b8, int seek=0)  Line
> 324 + 0x5 bytes
>   libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t *
> driver=0x00a66270, apr_pool_t * pool=0x01340958, apr_dbd_t *
> handle=0x012e1e28, 

[sqlite] SQLite lock behavior in shared-cache mode

[Alessandro Merolli]

Hi,

I'm using SQLite latest version (3.6.16) with shared-cache enable in  
a process that has around 5 threads. Database connections for each  
thread are created with the same main database file. After that, each  
connection is attached to a particular database file (one for each  
thread) using the same schema name. Final structure is similar to the  
following:

Main database file: main.db
Thread-1 database file: thread1.db
Thread-2 database file: thread2.db
Thread-3 database file: thread3.db
Thread-4 database file: thread4.db
Thread-5 database file: thread5.db

Thread-1 connection is opened with the main.db file and attaches the  
thread1.db as "extradb" schema name;
Thread-2 connection is opened with the main.db file and attaches the  
thread2.db as "extradb" schema name;
Thread-3 connection is opened with the main.db file and attaches the  
thread3.db as "extradb" schema name;
Thread-4 connection is opened with the main.db file and attaches the  
thread4.db as "extradb" schema name;
Thread-5 connection is opened with the main.db file and attaches the  
thread5.db as "extradb" schema name;

Every SQL statement submitted to the process and passed to one of  
these threads can read global informations maintained in the main.db  
database file ("main" schema) and write/read particular informations  
in the "extradb" schema in such a way that one thread does not need to  
wait for another thread to write its information, since each thread  
has the "extradb" schema attached to a particular database file.

Shared-cache is used for 2 reasons:
- to improve main.db database file data access; and,
- use the "Unlock Notification Feature" (sqlite3_unlock_notify) to  
avoid many SQLITE_LOCKED errors based on the code provided in 
http://www.sqlite.org/unlock_notify.html 
.

In my understanding, the expected behavior should be:
- If a SQL statement with only read (heavy) operations is passed to  
Thread-1; and,
- Another SQL statement with a write (and some reads) operations is  
passed to Thread-3;
- Both should run in parallel in a multi-core system.

But, it seems that the Thread-3 is waiting for the Thread-1 to finish  
its work before continue.
This behavior is turning the solution into a non-scalable solution.

As far as I could debug (and understand) using Visual Studio 2005, it  
seems that Thread-3 (in the above example) is waiting in for a lock in  
the sqlite3BtreeEnterAll function. See the piece of the call stack  
below:

 >  sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8)  Line 15159
sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8)  Line  
36706 + 0x11 bytes
sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const  
char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1,  
sqlite3_stmt * * ppStmt=0x, const char * * pzTail=0x0965f63c)   
Line 9672
sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char  
* zSql=0x00c0, int nBytes=180, sqlite3_stmt * * ppStmt=0x013a9094,  
const char * * pzTail=0x0965f63c)  Line 9747 + 0x1f bytes
apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 *  
db=0x, const char * zSql=, int nSql=, sqlite3_stmt * *  
ppStmt=0x, const char * * pz=0x)  Line 247 + 0x10 bytes
apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *  
pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * *  
results=0x0965f688, const char * query=0x00c0, int seek=0)  Line  
307 + 0x33 bytes
libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t *  
driver=0x00a66270, apr_pool_t * pool=0x013a9050, apr_dbd_t *  
handle=0x029cc040, apr_dbd_results_t * * res=0x0965f688, const char *  
statement=0x00c0, int random=0)  Line 319 + 0x22 bytes

While Thread-1 call stack looks like this:

 >  sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x1c471318)  Line 52862
sqlite3.dll!sqlite3Step(Vdbe * p=0x)  Line 49388 + 0x7 bytes
sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=)  Line 49449 + 0x7  
bytes
apr_dbd_sqlite3.dll!sqlite3_blocking_step(sqlite3_stmt *  
pStmt=0x)  Line 220 + 0x6 bytes
apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *  
pool=0x01340958, apr_dbd_t * sql=0x012e1e28, apr_dbd_results_t * *  
results=0x09b5f688, const char * query=0x0139c1b8, int seek=0)  Line  
324 + 0x5 bytes
libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t *  
driver=0x00a66270, apr_pool_t * pool=0x01340958, apr_dbd_t *  
handle=0x012e1e28, apr_dbd_results_t * * res=0x09b5f688, const char *  
statement=0x0139c1b8, int random=0)  Line 319 + 0x22 bytes

Additional information about the SQLite library in use:
- Version 3.6.16 (amalgamation source)
- Preprocessor definitions used to build the library:
- 

Re: [sqlite] What is a Relation?

[Beau Wilkinson]

>> Rich Shepard wrote:
>>What is more unfortunate is when someone with greater knowledge takes over
>> a project but is prevented from re-doing it in a more efficient way because
>> someone else's ego will be bruised or the powers that be cannot appreciate
>> the need.

I think my knowledge is just broader than my predecessor on that project. He 
cultivated a really detailed knowledge of the Windows / Intel platform, which 
didn't seem to allow any time or brainpower to learn about more general 
concepts.

I try to be more of a generalist, although it can be unpopular. My colleagues 
seem to prefer to pick a single nascent technology and cling mightily to it 
until they perceive something better. Trying to discuss architecture with 
people who do that is like talking to someone in a cult... no matter what the 
topic or question is, the discussion eventually flows back to You Know What. 
The sad result is that I end up gravitating toward maintenance programming, 
simply because the arguments have already been had.

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Rich Shepard [rshep...@appl-ecosys.com]
Sent: Monday, July 27, 2009 10:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What is a Relation?

On Mon, 27 Jul 2009, Beau Wilkinson wrote:

> I am dealing with such a project now. The schema consists of time stamp
> plus blob, where the blobs "map" directly to C++ structs. Of course, there
> are all sorts of useful data items in those blobs, and many of the
> capabilities of SQL are lost by reducing data to blobs. I think this is an
> extreme example of what you describe. The architectural excuse given was,
> I think, performance.

   I'm not a professional coder or DBA, but I've been writing code and
building database applications for about 3 decades. The problem, I believe,
is shifting paradigms and the difficulties people have in doing so.

   The original spaghetti-code model I used with FORTRAN in the early 1970s
gave way to structured programming with C. Most of us still effectively use
structured programming with procedural languages, either compiled or
interpreted. While OO is a nice concept, and does have use in the
appropriate applications, it produces bloated binaries because all classes
in the hierarchy need to be compiled and included, even if one function is
all that's needed. However, even within the huge tribe of procedural
languages, there are different paradigms that one needs to understand to
most efficiently use a language. The differences between C and Common LISP
immediately come to mind.

   Regardless, it is very common to see those new to SQL take whatever
knowledge and experience they have with procedural languages and try to
apply it to working with sets. Your project summarized above seems to be an
example of this. Trying to apply the flow control and other structures of a
procedural language to SQL tends to result in a mess. Too few books or other
resources stress these differences so newcomers are taught to look at the
whole database manipulation language (principally the SELECT statement) in a
different way.

   What is more unfortunate is when someone with greater knowledge takes over
a project but is prevented from re-doing it in a more efficient way because
someone else's ego will be bruised or the powers that be cannot appreciate
the need.

Rich

--
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Rich Shepard]

On Mon, 27 Jul 2009, Beau Wilkinson wrote:

> I am dealing with such a project now. The schema consists of time stamp
> plus blob, where the blobs "map" directly to C++ structs. Of course, there
> are all sorts of useful data items in those blobs, and many of the
> capabilities of SQL are lost by reducing data to blobs. I think this is an
> extreme example of what you describe. The architectural excuse given was,
> I think, performance.

   I'm not a professional coder or DBA, but I've been writing code and
building database applications for about 3 decades. The problem, I believe,
is shifting paradigms and the difficulties people have in doing so.

   The original spaghetti-code model I used with FORTRAN in the early 1970s
gave way to structured programming with C. Most of us still effectively use
structured programming with procedural languages, either compiled or
interpreted. While OO is a nice concept, and does have use in the
appropriate applications, it produces bloated binaries because all classes
in the hierarchy need to be compiled and included, even if one function is
all that's needed. However, even within the huge tribe of procedural
languages, there are different paradigms that one needs to understand to
most efficiently use a language. The differences between C and Common LISP
immediately come to mind.

   Regardless, it is very common to see those new to SQL take whatever
knowledge and experience they have with procedural languages and try to
apply it to working with sets. Your project summarized above seems to be an
example of this. Trying to apply the flow control and other structures of a
procedural language to SQL tends to result in a mess. Too few books or other
resources stress these differences so newcomers are taught to look at the
whole database manipulation language (principally the SELECT statement) in a
different way.

   What is more unfortunate is when someone with greater knowledge takes over
a project but is prevented from re-doing it in a more efficient way because
someone else's ego will be bruised or the powers that be cannot appreciate
the need.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Modifier '%w' Does Not Behave as INTEGER

[Pavel Ivanov]

For me it's pretty obvious that strftime() returns text data type
because it returns your date formatted as you like. And SQLite have
never compared text and integer as equal even if they have the same
notation when converted to text:

sqlite> select 1 where '1' = 1;
sqlite> select 1 where '1' = '1';
1
sqlite> select typeof(strftime('%w', date()));
text
sqlite>


Pavel

On Mon, Jul 27, 2009 at 11:20 AM, Bill Welsh wrote:
> I find that I must cast the result of a '%w' modifier to INTEGER to use it
> in a select.
>
> I have a table of TV programs that has title and an integer representing the
> show's start time as unix epoch.
>
> This does not produce any results, though I have shows beginning on Monday:
>
> select title, datetime(start_time,'unixepoch','localtime') from show_list
> where strftime('%w',start_time,'unixepoch','localtime') = 1;
>
> I find if I create a temp table:
>
> create temp table foo as select title, start_time,
> strftime('%w',start_time,'unixepoch','localtime') dow from show_list;
>
> I get an odd schema where there is no data type for the dow column:
>
> .schema foo
>
> sqlite> .schema foo2
> CREATE TABLE foo2(title_long TEXT,start_time INTEGER,dow);
> sqlite>
>
> And that
>
> select * from foo where dow = 1;
>
> still gives me no results.
>
> However, if I
>
> create temp table foo2 as select title, start_time,
> cast(strftime('%w',start_time,'unixepoch','localtime') as integer) dow from
> show_list;
>
>
> I get results from the query for dow = 1 on foo2.
>
>
> Similarly, I can use the cast in the earlier query and get a result set.  I
> can also use the un-cast strftime('%w'...) in a group by clause, which gives
> me expected results.
>
> But this all seems odd, given how forgiving SQLite is regarding data types.
> It appears at first blush that because the result of the strftime('%w...)
> has no type, it's not comparing correctly with my INT constant in the
> query.
>
> So the question is: What's going on?  Is this expected behavior or a bug?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Beau Wilkinson]

>> There are still people who just want
>> a cursor to a chunk of data which they pull in and iterate over rather than
>> use SQL's power to manage data a set-at-a-time

I am dealing with such a project now. The schema consists of time stamp plus 
blob, where the blobs "map" directly to C++ structs. Of course, there are all 
sorts of useful data items in those blobs, and many of the capabilities of SQL 
are lost by reducing data to blobs. I think this is an extreme example of what 
you describe. The architectural excuse given was, I think, performance.

>> Call me old fashioned but object-relational mappers worry me on this score

I agree, except that you're not old-fashioned. In fact, I think you're on the 
cutting edge here. OO (including any attempt to apply it to database work) is 
no longer trendy. At best, it is tolerated... perhaps many people still haven't 
admitted that the proverbial emporer has no clothes, but no one's bragging 
about his (i.e. OOP's) new jacket, either.

Microsoft, for example, is moving rapidly to functional programming and generic 
programming. Implementation inheritance has been lobotomized in C# compared to 
C++, for example, and Microsoft's new "Linq" database client technology is 
functional and generic.

As for ORM, OODBMS (whatever that is), etc. proponents of such technologies 
continue to hem and haw about "object-relational impedance." This is just  a 
fancy way of describing the problems that result when a good (Relational) model 
of reality must interface with a dysfunctional (OO) model. OO creates problems 
(or "opportunities"...) because it doesn't work. MIT has been saying this for 
30 years. Few people are willing to consciously stick their necks out and say, 
"you know, I've always thought OOP was a bunch of B.S" but the sentiment's out 
there and it's justified.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of CityDev [nab...@recitel.net]
Sent: Monday, July 27, 2009 9:33 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is a Relation?

It's true that Codd and Date used the term 'relational' (They championed the
N-ary Relational Model - others were around at the same time) but it's not
easy to track the origin of the term in mathematics. Certainly the word
implies joining things together. I guess the joining refers to fields
(domains) within each row (n-tuple).

If you look at other forums you often see novices (and others) using the
word 'relate' as if it is some special way of joining information between
two tables. They also clearly feel that you need to declare foreign keys in
order to have a logical connection between tables. As you are no doubt
aware, one of the guiding principles of the relational model is there is
nothing that is not a data value so you are totally free to join anything to
anything as long as you feel it might make sense. Relationships between
tables are contingent - they can be there one day and gone the next. Any
persistent relationship information (eg foreign keys) is optional and there
for other purposes (eg documentation or referential integrity enforcement).

Another common conceptual misunderstanding I've seen over the years is that
the database is just a fancy filestore. There are still people who just want
a cursor to a chunk of data which they pull in and iterate over rather than
use SQL's power to manage data a set-at-a-time. Call me old fashioned but
object-relational mappers worry me on this score.

--
View this message in context: 
http://www.nabble.com/What-is-a-Relation--tp24674278p24681797.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

The information contained in this e-mail is privileged and confidential 
information intended only for the use of the individual or entity named.  If 
you are not the intended recipient, or the employee or agent responsible for 
delivering this message to the intended recipient, you are hereby notified that 
any disclosure, dissemination, distribution, or copying of this communication 
is strictly prohibited.  If you have received this e-mail in error, please 
immediately notify the sender and delete any copies from your system.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Date Modifier '%w' Does Not Behave as INTEGER

[Bill Welsh]

I find that I must cast the result of a '%w' modifier to INTEGER to use it
in a select.

I have a table of TV programs that has title and an integer representing the
show's start time as unix epoch.

This does not produce any results, though I have shows beginning on Monday:

select title, datetime(start_time,'unixepoch','localtime') from show_list
where strftime('%w',start_time,'unixepoch','localtime') = 1;

I find if I create a temp table:

create temp table foo as select title, start_time,
strftime('%w',start_time,'unixepoch','localtime') dow from show_list;

I get an odd schema where there is no data type for the dow column:

.schema foo

sqlite> .schema foo2
CREATE TABLE foo2(title_long TEXT,start_time INTEGER,dow);
sqlite>

And that

select * from foo where dow = 1;

still gives me no results.

However, if I

create temp table foo2 as select title, start_time,
cast(strftime('%w',start_time,'unixepoch','localtime') as integer) dow from
show_list;


I get results from the query for dow = 1 on foo2.


Similarly, I can use the cast in the earlier query and get a result set.  I
can also use the un-cast strftime('%w'...) in a group by clause, which gives
me expected results.

But this all seems odd, given how forgiving SQLite is regarding data types.
It appears at first blush that because the result of the strftime('%w...)
has no type, it's not comparing correctly with my INT constant in the
query.

So the question is: What's going on?  Is this expected behavior or a bug?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Doug Currie]

On Jul 27, 2009, at 10:33 AM, CityDev wrote:

> It's true that Codd and Date used the term 'relational' (They  
> championed the
> N-ary Relational Model - others were around at the same time) but  
> it's not
> easy to track the origin of the term in mathematics.

http://en.wikipedia.org/wiki/Relation_(mathematics)

e


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Jean-Denis Muys]

On 7/27/09 16:33 , "CityDev"  wrote:

> 
>  it's not
> easy to track the origin of the term in mathematics.

For what it's worth (ie probably not much), my formal mathematics training
in set theory taught me that a relation from a set A to a set B is a subset
R of the cartesian product AxB.

The special cases on A, B and R lead to further definitions and properties,
eventually leading to group and field theory. I would have to go back to
Evariste Galois' work to check whether he already used the term "relation"
though.

Jean-Denis



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[CityDev]

It's true that Codd and Date used the term 'relational' (They championed the
N-ary Relational Model - others were around at the same time) but it's not
easy to track the origin of the term in mathematics. Certainly the word
implies joining things together. I guess the joining refers to fields
(domains) within each row (n-tuple).

If you look at other forums you often see novices (and others) using the
word 'relate' as if it is some special way of joining information between
two tables. They also clearly feel that you need to declare foreign keys in
order to have a logical connection between tables. As you are no doubt
aware, one of the guiding principles of the relational model is there is
nothing that is not a data value so you are totally free to join anything to
anything as long as you feel it might make sense. Relationships between
tables are contingent - they can be there one day and gone the next. Any
persistent relationship information (eg foreign keys) is optional and there
for other purposes (eg documentation or referential integrity enforcement).

Another common conceptual misunderstanding I've seen over the years is that
the database is just a fancy filestore. There are still people who just want
a cursor to a chunk of data which they pull in and iterate over rather than
use SQL's power to manage data a set-at-a-time. Call me old fashioned but
object-relational mappers worry me on this score.

-- 
View this message in context: 
http://www.nabble.com/What-is-a-Relation--tp24674278p24681797.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Jay A. Kreibich]

On Sun, Jul 26, 2009 at 11:42:23PM -0700, CityDev scratched on the wall:
> 
> Just to kill time over coffee - what do you take the word to mean?

  http://en.wikipedia.org/wiki/Relational_model

  A "relation" is a data structure that anyone familiar with SQL would
  call a table.  It comes from the Relational Model, a formal
  mathematical system devised by E.F. Codd in the late 1960s, and is
  the basis for all modern SQL database systems.

> Chris Date was very specific that a relation was essentially a table. 

  C.J. Date is one of the people that worked with Codd.  Pick you
  cliche: He's forgotten more about formal database theory than most
  of us will every know, or how his little finger knows more about
  database theory than most of us, etc.  He's that kind of authority.

> Mainly however, people seem to use the word to describe the
> connections you can make by performing joins between tables.

  That's because most people are, unfortunately, taught SQL in a vacuum
  with none of the theory or background.  Since it is common to call
  the links between tables "relationships" (e.g. "one-to-many
  relationship"), people without any theory knowledge tend to assume
  this is what "relation" is all about.  That's only sorta-kinda true
  in a very abstract way.

  "What does the 'R' in RDBMS stand for?" makes a great DBA interview
  question.  If you're hiring a full-time DBA, it weeds out those that
  know and care about their craft from those that just know some SQL.
  I wouldn't expect that kind of theory and history knowledge from a
  programmer that was using a database, but I do expect it of a DBA
  that does nothing but manage and design databases.
  
   -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

Re: [sqlite] What is a Relation?

[Rich Shepard]

On Mon, 27 Jul 2009, Darren Duncan wrote:

> Object orientation has nothing to do with all this per se, though objects
> can easily be mapped to tuples.

Darren,

   A related issue is that object orientation is almost always used in the
context of procedural languages (e.g., C++, Python, Ruby) while SQL is _not_
a procedural language. SQL is a language for working with sets (tables, or
relations).

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generate scripts from SQLite database

[Jadranko Dragoje]

Thank you, it works!

On Mon, Jul 27, 2009 at 1:47 PM, Swithun Crowe <
swit...@swithun.servebeer.com> wrote:

> Hello
>
> JD Is there some tool to generate scripts from SQLite database? I need to
> JD create scripts (create database, create table, triggers etc.) from
> JD existing database.
>
> From the command line tool, the command .schema will output the commands
> used to create the database and all tables and triggers.
>
> Swithun.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Jadranko Dragoje
Adresa: Stjepana Radića 40, 88000 Mostar, BiH
Mobitel: +387 (0)63 892 852
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] select query problem in symbian c++

[rahul . makode]

i am executing this query to get UserNamePasswordHash and i am setting
value of variable   UserID


err = stmt.Prepare(db, _L("SELECT UserNamePasswordHash FROM User WHERE
UserID=:UserID"));

//TInt err = stmt.Prepare(database, _L("SELECT BinaryField FROM 
Tbl1"));

TInt columnIndex = stmt.ColumnIndex(_L("UserNamePasswordHash"));

while((err = stmt.Next()) == KSqlAtRow)
{

TInt size = stmt. ColumnSize(columnIndex);

HBufC8* buf = HBufC8::NewL(size);

TPtr8 bufferPtr1 (buf->Des());


err = stmt.ColumnBinary(columnIndex,bufferPtr1);


iChitDemoAppUi->BinaryToString(bufferPtr1,usernamePasswordHash);

iChitDemoAppUi->Log(bufferPtr1);

delete buf;

}

query is executing properly but i am not getting data in buf
if i used hardcoded then it is executed properly in symbian


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generate scripts from SQLite database

[Swithun Crowe]

Hello

JD Is there some tool to generate scripts from SQLite database? I need to 
JD create scripts (create database, create table, triggers etc.) from 
JD existing database.

>From the command line tool, the command .schema will output the commands 
used to create the database and all tables and triggers.

Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Generate scripts from SQLite database

[Jadranko Dragoje]

Hello,

Is there some tool to generate scripts from SQLite database?
I need to create scripts (create database, create table, triggers etc.) from
existing database.
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with -order by- clause

[Igor Tandetnik]

Martin.Engelschalk wrote:
> The sorting used by default in sqlite uses normal strcmp - like
> comparison.
>
> This problem has been discussed several times in the mailing list:
> perhaps you want to search the archive.
> You will have to write your own collation, perhaps using the ICU.
> See http://www.sqlite.org/capi3ref.html#sqlite3_create_collation
> http://www.mail-archive.com/sqlite-users%40sqlite.org/
> http://site.icu-project.org/

I'd like to also mention

http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with -order by- clause

[Martin.Engelschalk]

Hi,

The sorting used by default in sqlite uses normal strcmp - like comparison.

This problem has been discussed several times in the mailing list: 
perhaps you want to search the archive.
You will have to write your own collation, perhaps using the ICU.
See http://www.sqlite.org/capi3ref.html#sqlite3_create_collation
http://www.mail-archive.com/sqlite-users%40sqlite.org/
http://site.icu-project.org/

Martin

MartinRalf schrieb:
> Hello,
>
>  
>
> I’ve got a problem with sorting german ‚Umlaute’ eg. ’äöü’ (ae,oe,ue)
>
> Usually they are sorted prior to the corresponding Letter: ü before u
>
>  
>
> In SqLite with ‘Collate Locale’ these letters are sorted at the end after
> ‘z’
>
>  
>
> Is there a solution or do I have to ‘live’ with it?
>
>  
>
> Cheers
>
> Ralf
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with -order by- clause

[Ralf]

Hello,

 

I’ve got a problem with sorting german ‚Umlaute’ eg. ’äöü’ (ae,oe,ue)

Usually they are sorted prior to the corresponding Letter: ü before u

 

In SqLite with ‘Collate Locale’ these letters are sorted at the end after
‘z’

 

Is there a solution or do I have to ‘live’ with it?

 

Cheers

Ralf

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite db5 error for symbian

[Manasi Save]

 err = stmt.Prepare(db, _L("SELECT Col1 FROM tbl1 WHERE col2 = 
:var"));

TInt paramIndex = stmt.ParameterIndex(_L(":var"));

err = stmt.BindInt(paramIndex,user_id);

id.AppendNum(user_id);

iChitDemoAppUi->Log(id);

TInt columnIndex = stmt.ColumnIndex(_L("UserNamePasswordHash"));

TBuf8<100> data;

while((err = stmt.Next()) == KSqlAtRow)
{

//data.Zero();

data= stmt.ColumnBinaryL(columnIndex);

iChitDemoAppUi->Log(data);

RDebug::Print(_L("val=%d\n"), data);
//
}

it is not printing value in Log and debug also in symbian c++ for v9.1.

I want to get the value of col1 where col2 = bind variable value.

Can anyone provide any input on what i am missing in this.

Thanks in advance.
Regards,
Manasi

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is a Relation?

[Darren Duncan]

CityDev wrote:
> Just to kill time over coffee - what do you take the word to mean?
> 
> I've just been reading a 1991 James Martin book on Object Orientation and he
> was using it to talk about links between entities. Chris Date was very
> specific that a relation was essentially a table. Mainly however, people
> seem to use the word to describe the connections you can make by performing
> joins between tables. What do you think is 'correct'? How did the other
> meaning gain currency?

In the context of relational databases or mathematics, you want to use Chris 
Date's meaning, which comes from mathematics dating from before we had 
computers 
as we know them today.

A relation is a set of tuples where all tuples are of the same degree and have 
the same set of attribute names/types.  For example, you can have a "people" 
relation where each tuple represents a "person" and every tuple has 3 
attributes, ["name", "birthdate", "address"].

A relation gets its name from that, for every tuple in it, each attribute value 
is related to the other attribute values.  For example, in a "person" tuple 
["Joe", "Feb 17, 1989", "53 Cherry Dr."], the "Joe" is related to "53 Cherry 
Dr."] and so on.

A relational database is called that because it consists of a set of relations, 
each of which having a name.

SQL uses the terms ["rowset","row","column/field"] to refer to a 
["relation","tuple","attribute"], and the term "table" to refer to a 
relation-typed variable, such being what a persisting database consists of.

A relational join is an operation that takes several relation values (rowsets) 
as input and combines them to yield another relation value (rowset) as output, 
such that member tuples (rows) are matched up with each other and catenated 
into 
a new set of tuples (rows).

So the term relation refers both to the contents of a SQL table *and* to a 
process of connecting tables.

Object orientation has nothing to do with all this per se, though objects can 
easily be mapped to tuples.

-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What is a Relation?

[CityDev]

Just to kill time over coffee - what do you take the word to mean?

I've just been reading a 1991 James Martin book on Object Orientation and he
was using it to talk about links between entities. Chris Date was very
specific that a relation was essentially a table. Mainly however, people
seem to use the word to describe the connections you can make by performing
joins between tables. What do you think is 'correct'? How did the other
meaning gain currency?

I see James Martin owns an island in Bermuda and has been handing out
millions all over the place - a good incentive to start writing those books
you've been thinking about.
-- 
View this message in context: 
http://www.nabble.com/What-is-a-Relation--tp24674278p24674278.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users