Re: [sqlite] Re: db design mixing different entities in the same table

[Andrew Finkenstadt]

On 6/1/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:


On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:
>
> On 5/31/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> > On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > >
> > > I realize that I didn't explain the problem well enough (thus is the
> > > peril of being too close to it for a long time).
> > >
> > > I can have an (org <--> org) relationship or an (org <--> person)
> > > relationship as well as a (person <--> person) relationship. To make
> > > matters worse, a person or an org can have multiple relationships
> > > based a role. Here is an example.
> > >
> > > John is the Director of Forestry Dept.
> > > Jane is the Head of the Economics Dept.
> > > Joe is a Scientist at the Forestry Dept.
> > >
> > > rel1: John (Director: Forestry) has a joint project with Jane (Head:
>
> > > Economics)
> > > rel2: John (Director: Forestry) is the boss of Joe (Scientist:
> Forestry)
> > > rel3: John (Person: null) is a friend of Joe (Person: null)
> > > rel4: John (Person: null) goes to the same church as Jane (Person:
> null)
> > >
> > > and so on.
> > >
> > > Well, I really have three "vectors" per "node" -- person, role,
> > > organization. In each node, either person or organization can be
> null
> > > (obviously, role will never be null because every node or entity has
> a
> > > purpose). Each of these vectors can be defined by a finite number of
> > > meaningful properties -- for example, a person has a name, nickname,
>
> > > address, contact information, and so on. Even the role can have a
> > > level of importance -- a "secretary" at the Forestry department is
> not
> > > as important as the "Director," so the role can have a weight, and
> so
> > > on.
> > >
> > > This is the problem that I have to model. I am trying to devise a
> > > storage structure for something like this in SQLite.
> > >
> > >
> >
> >
> > Been there, done that.  About 15 years ago.
>
> oh, how I hate you ;-)
>
> > We called the model CAPPR:  Company, Address, Person, Phone, and
> Roles.
> ..
> >
> > So bottom line, you're going to need your ENTITY tables, and your
> > RELATIONSHIP tables, and your ROLE tables.  You may also need "lookup
> > tables" to help enforce data cleanliness by reducing spelling errors
> or
> > similar issues.
> ..
>
> So, you are telling me that you did it, but I really want to know
> "how" to do it. I am sure this is not a unique problem I am trying to
> solve. I just am getting all knotted up trying to figure out a sane db
> structure to model this within so I can put data in and take data out
> without major contortions and yet have it be flexible and scalable for
> different kinds of relationships.
>
> So, the real CAPPR will be if you give me a few db design guidelines
> here.
>
> ;-)
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
>


The database was rather simple in pictures, but complex by the time you
added everything in to help the database help your program stay out of
trouble.

We chose to identify a person by various sub-fields, and since we were not
internationalization-aware, we didn't have to have our innocence sullied by
the different positions of family names, given names, surnames, and
alternate names based on who you were in relationship to.  (Japanese and
asian language names, germanic, russian, spanish, etc.)  So our sub-fields
were honorific (Sir, Don, The Honorable), title (Mr. Mrs. Dr. Reverend,
etc), first name, middle name, last name, and something we could never
decide on a name for:  Post-name (the III, Junior, etc), and the degreed
position ( Ph.D., M.S.C.A, etc).

Data entry of this name could be by-field (preferred) or free text, which
the software then parsed into the separate fields for correction, and easy
searching later.  A little bit of human effort here helped increase the
utility of their contact management activities later.

We identified address the same way the post office does: street number,
pre-direction, street name, street type, post-direction, suite/apartment
qualififier, suite/apartment number, city, state, zip, zip+4.  The software
used address correction software certified for bulk mailing purposes with
the USPS.

Company name was almost the simplest.  There was a freetext data entry
field, and then the actual database field.  Abbreviations in the freetext
were expanded to the full name in the database field.

Phone numbers had country codes, area codes, telephone numbers,
extensions, passcodes, and special dialing instructions.

Roles were multi-level.  Roles were identified by a unique ID number, and
then split into appropriateness (which relationship it could describe
[person-company, company-address, person-address, person-phone,
company-phone, address-phone), the directionality description (left to
right, right to left), and then into categories.

Next, 

Re: [sqlite] Re: db design mixing different entities in the same table

[Andrew Finkenstadt]

On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:


On 5/31/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:
> On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:
> >
> > I realize that I didn't explain the problem well enough (thus is the
> > peril of being too close to it for a long time).
> >
> > I can have an (org <--> org) relationship or an (org <--> person)
> > relationship as well as a (person <--> person) relationship. To make
> > matters worse, a person or an org can have multiple relationships
> > based a role. Here is an example.
> >
> > John is the Director of Forestry Dept.
> > Jane is the Head of the Economics Dept.
> > Joe is a Scientist at the Forestry Dept.
> >
> > rel1: John (Director: Forestry) has a joint project with Jane (Head:
> > Economics)
> > rel2: John (Director: Forestry) is the boss of Joe (Scientist:
Forestry)
> > rel3: John (Person: null) is a friend of Joe (Person: null)
> > rel4: John (Person: null) goes to the same church as Jane (Person:
null)
> >
> > and so on.
> >
> > Well, I really have three "vectors" per "node" -- person, role,
> > organization. In each node, either person or organization can be null
> > (obviously, role will never be null because every node or entity has a
> > purpose). Each of these vectors can be defined by a finite number of
> > meaningful properties -- for example, a person has a name, nickname,
> > address, contact information, and so on. Even the role can have a
> > level of importance -- a "secretary" at the Forestry department is not
> > as important as the "Director," so the role can have a weight, and so
> > on.
> >
> > This is the problem that I have to model. I am trying to devise a
> > storage structure for something like this in SQLite.
> >
> >
>
>
> Been there, done that.  About 15 years ago.

oh, how I hate you ;-)

> We called the model CAPPR:  Company, Address, Person, Phone, and Roles.
..
>
> So bottom line, you're going to need your ENTITY tables, and your
> RELATIONSHIP tables, and your ROLE tables.  You may also need "lookup
> tables" to help enforce data cleanliness by reducing spelling errors or
> similar issues.
..

So, you are telling me that you did it, but I really want to know
"how" to do it. I am sure this is not a unique problem I am trying to
solve. I just am getting all knotted up trying to figure out a sane db
structure to model this within so I can put data in and take data out
without major contortions and yet have it be flexible and scalable for
different kinds of relationships.

So, the real CAPPR will be if you give me a few db design guidelines here.

;-)

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/




The database was rather simple in pictures, but complex by the time you
added everything in to help the database help your program stay out of
trouble.

We chose to identify a person by various sub-fields, and since we were not
internationalization-aware, we didn't have to have our innocence sullied by
the different positions of family names, given names, surnames, and
alternate names based on who you were in relationship to.  (Japanese and
asian language names, germanic, russian, spanish, etc.)  So our sub-fields
were honorific (Sir, Don, The Honorable), title (Mr. Mrs. Dr. Reverend,
etc), first name, middle name, last name, and something we could never
decide on a name for:  Post-name (the III, Junior, etc), and the degreed
position (Ph.D., M.S.C.A, etc).

Data entry of this name could be by-field (preferred) or free text, which
the software then parsed into the separate fields for correction, and easy
searching later.  A little bit of human effort here helped increase the
utility of their contact management activities later.

We identified address the same way the post office does: street number,
pre-direction, street name, street type, post-direction, suite/apartment
qualififier, suite/apartment number, city, state, zip, zip+4.  The software
used address correction software certified for bulk mailing purposes with
the USPS.

Company name was almost the simplest.  There was a freetext data entry
field, and then the actual database field.  Abbreviations in the freetext
were expanded to the full name in the database field.

Phone numbers had country codes, area codes, telephone numbers, extensions,
passcodes, and special dialing instructions.

Roles were multi-level.  Roles were identified by a unique ID number, and
then split into appropriateness (which relationship it could describe
[person-company, company-address, person-address, person-phone,
company-phone, address-phone), the directionality description (left to
right, right to left), and then into categories.

Next, since there were 4 entity tables, there were 6 relationship tables
whose foreign keys were the corresponding entries in the entity tables & the
role table, plus a free-text field for notes or comments.

Each 

Re: [sqlite] Re: db design mixing different entities in the same table

[P Kishor]

On 5/31/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote:

On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:
>
> I realize that I didn't explain the problem well enough (thus is the
> peril of being too close to it for a long time).
>
> I can have an (org <--> org) relationship or an (org <--> person)
> relationship as well as a (person <--> person) relationship. To make
> matters worse, a person or an org can have multiple relationships
> based a role. Here is an example.
>
> John is the Director of Forestry Dept.
> Jane is the Head of the Economics Dept.
> Joe is a Scientist at the Forestry Dept.
>
> rel1: John (Director: Forestry) has a joint project with Jane (Head:
> Economics)
> rel2: John (Director: Forestry) is the boss of Joe (Scientist: Forestry)
> rel3: John (Person: null) is a friend of Joe (Person: null)
> rel4: John (Person: null) goes to the same church as Jane (Person: null)
>
> and so on.
>
> Well, I really have three "vectors" per "node" -- person, role,
> organization. In each node, either person or organization can be null
> (obviously, role will never be null because every node or entity has a
> purpose). Each of these vectors can be defined by a finite number of
> meaningful properties -- for example, a person has a name, nickname,
> address, contact information, and so on. Even the role can have a
> level of importance -- a "secretary" at the Forestry department is not
> as important as the "Director," so the role can have a weight, and so
> on.
>
> This is the problem that I have to model. I am trying to devise a
> storage structure for something like this in SQLite.
>
>


Been there, done that.  About 15 years ago.


oh, how I hate you ;-)


We called the model CAPPR:  Company, Address, Person, Phone, and Roles.

..


So bottom line, you're going to need your ENTITY tables, and your
RELATIONSHIP tables, and your ROLE tables.  You may also need "lookup
tables" to help enforce data cleanliness by reducing spelling errors or
similar issues.

..

So, you are telling me that you did it, but I really want to know
"how" to do it. I am sure this is not a unique problem I am trying to
solve. I just am getting all knotted up trying to figure out a sane db
structure to model this within so I can put data in and take data out
without major contortions and yet have it be flexible and scalable for
different kinds of relationships.

So, the real CAPPR will be if you give me a few db design guidelines here.

;-)

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: db design mixing different entities in the same table

[Andrew Finkenstadt]

On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:


I realize that I didn't explain the problem well enough (thus is the
peril of being too close to it for a long time).

I can have an (org <--> org) relationship or an (org <--> person)
relationship as well as a (person <--> person) relationship. To make
matters worse, a person or an org can have multiple relationships
based a role. Here is an example.

John is the Director of Forestry Dept.
Jane is the Head of the Economics Dept.
Joe is a Scientist at the Forestry Dept.

rel1: John (Director: Forestry) has a joint project with Jane (Head:
Economics)
rel2: John (Director: Forestry) is the boss of Joe (Scientist: Forestry)
rel3: John (Person: null) is a friend of Joe (Person: null)
rel4: John (Person: null) goes to the same church as Jane (Person: null)

and so on.

Well, I really have three "vectors" per "node" -- person, role,
organization. In each node, either person or organization can be null
(obviously, role will never be null because every node or entity has a
purpose). Each of these vectors can be defined by a finite number of
meaningful properties -- for example, a person has a name, nickname,
address, contact information, and so on. Even the role can have a
level of importance -- a "secretary" at the Forestry department is not
as important as the "Director," so the role can have a weight, and so
on.

This is the problem that I have to model. I am trying to devise a
storage structure for something like this in SQLite.





Been there, done that.  About 15 years ago.

At the time I worked for Homes and Land Publishing in Tallahassee, and we
built customizable software for the associate publishers who owned the
magazine franchises in various areas.  This software was used in their
offices to manage their contacts with brokerages, advertisers, and so on.

We called the model CAPPR:  Company, Address, Person, Phone, and Roles.  And
it was a many-to-many network between each of those entity types:  COMPANY
had zero or more ADDRESSES.  PERSON had zero or more ADDRESSES.  COMPANY (or
PERSON) had zero or more PHONE numbers, each with a different purpose.
COMPANY had zero or more PERSONS known to be associated with them, each with
one or more ROLES.

It worked very, very well, as it modeled the real world accurately.

One of the interesting things it let them determine was who gave out
non-unique addresses for different companies, helping them to reduce fraud
and to "know your customer" better.  (This was before the advent of
well-known databases of private mailbox companies like MAILBOXES ETC.)

So bottom line, you're going to need your ENTITY tables, and your
RELATIONSHIP tables, and your ROLE tables.  You may also need "lookup
tables" to help enforce data cleanliness by reducing spelling errors or
similar issues.

In our case we built our model on Oracle RDBMS with full referential
integrity enforced, which prevented a boatload of common programming errors
from messing up the relationships between persons, companies, addresses, and
phone numbers by deleting records that were still in use.  I don't recall if
SQLite enforces referential integrity, or merely accepts  the syntax against
a future update that will.

--andy

Re: [sqlite] Re: db design mixing different entities in the same table

[P Kishor]

On 5/31/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

P Kishor <[EMAIL PROTECTED]> wrote:
> I am modeling entities and their relationships to each other. A
> classic network digraph kind of stuff. The entities are organizations
> or persons. A person may  be associated with none (org type
> "unassigned") or one organization, and, of course, an organization may
> have none or more persons in it.
>
> I started with
>
> CREATE TABLE orgs (
>  org_id INTEGER PRIMARY KEY,
>  .. bunch of org stuff ..
> );
>
> CREATE TABLE persons (
>  person_id INTEGER PRIMARY KEY,
>  org_idINTEGER   -- FK to orgs(org_id)
>  .. bunch of person stuff ..
> );
>
> CREATE TABLE relationships (
>  rel_id   INTEGER PRIMARY KEY,
>  from_id  INTEGER, -- person_id or org_id
>  to_idINTEGER, -- person_id or org_id
>  entity_type  INTEGER DEFAULT 0,   -- 0 is "org", 1 is "person"
>  relationship TEXT
> );

Why do you feel you need this last table at all? All the information is
already available from persons.org_id.



I realize that I didn't explain the problem well enough (thus is the
peril of being too close to it for a long time).

I can have an (org <--> org) relationship or an (org <--> person)
relationship as well as a (person <--> person) relationship. To make
matters worse, a person or an org can have multiple relationships
based a role. Here is an example.

John is the Director of Forestry Dept.
Jane is the Head of the Economics Dept.
Joe is a Scientist at the Forestry Dept.

rel1: John (Director: Forestry) has a joint project with Jane (Head: Economics)
rel2: John (Director: Forestry) is the boss of Joe (Scientist: Forestry)
rel3: John (Person: null) is a friend of Joe (Person: null)
rel4: John (Person: null) goes to the same church as Jane (Person: null)

and so on.

Well, I really have three "vectors" per "node" -- person, role,
organization. In each node, either person or organization can be null
(obviously, role will never be null because every node or entity has a
purpose). Each of these vectors can be defined by a finite number of
meaningful properties -- for example, a person has a name, nickname,
address, contact information, and so on. Even the role can have a
level of importance -- a "secretary" at the Forestry department is not
as important as the "Director," so the role can have a weight, and so
on.

This is the problem that I have to model. I am trying to devise a
storage structure for something like this in SQLite.


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] [Wiki] How to register, and actually add a page?

[Gilles Ganault]

Hello

I'd like to contribute to the wiki, but I didn't find...
1. How to register: There's no Register link in the Login page
2. How to actually add a page once I added a Camel-formatted word in a page.

Thank you
Gilles.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 31, 2007 4:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You have explained the problem, which is .NET not Sqlite.  You have
apparently done a fine job marrying the two but it might be more
logical
to suggest that .NET be made more flexible.

As for flexibility, programs in C or Assembler are only inflexible at
the level of the underlying machine or operating system.  They are a
tool to use to build an environment.

Initially the typing rules in Sqlite appeared to be a nusisance but on
deeper thought their utility became apparent and it was possible to use
them to great advantage.  To bloat or impair that advantage just to
match a particular concept like .NET would be a tragedy.

You might consider developing an SQL engine ideally adapted to .NET.



I don't really know how we got here :) I think SQLite is a fantastic engine,
fast, free and flexible -- I wouldn't have spent the last 2 years
maintaining an ADO.NET provider for it if I didn't love it.  I'm certainly
not going to throw the baby out with the bath water and try and roll my own
SQL engine -- it misses the point entirely.  I'm here to support SQLite.

I'm not complaining about SQLite's lack of typing in general.  Could I use a
little help in making SQLite fit more seamlessly into these typed
environments?  Sure, the more help I get the better!

I'd love it if I could issue a CAST([MyCol] AS HAIRYLLAMA) and have some way
to yank HAIRYLLAMA out of the statement's metadata so I could tell what
specific type that column was CAST to.

Heck, I'd love it even more if there was an additional parameter in the
user-def function callback to provide SQLite a string name of a type
returned from a function so we could have more descriptive typing in there
as well.

The problem isn't .NET.  The problem is that every other database on the
planet enforces type restrictions, and hence every generic database access
layer (ODBC, OLEDB, ADO, ADO.NET) is designed around that concept.  "Don't
use a wrapper" doesn't work for every circumstance, and I can't change the
ADO.NET spec -- though I do get around it where I can to support SQLite's
typelessness.

My opinion is: Since SQLite is typeless, then it should probably have a few
more functions dedicated to type description.  Preserving the destination
typename on a CAST as well as in a userdef function would go a long way
toward accomplishing that.  Or even better, some kind of extensible type
system that enabled us to have type-specific comparison callbacks.

However -- we're not there, and may never get there, so I'll continue to
hack.  That's what open source is for, afterall :)

Robert

There was a lot of fuss on the financial news tonight concerning Google 
Gears and its impact.  Sergey Brin was interviewed saying guilelessly 
that Google doesn't think of other companies, only users but the 
resident experts were saying that this is a blow at Microsoft.


Its is significant that the Sqlite based Google Gears seems to take 
advantage of typeless storage to produce a more general storage model. 
That was the point of my comments, Sqlite lets us advance our storage 
capabilities into a more flexible world.


The way we have chosen to use Sqlite has many parallels with the Google 
approach, presumably a logical development of the storage concepts.


We use PostgreSQL to map into environments requiring its capabilities 
and Sqlite where it fits well, such as in an application specific 
language we developed and with Javascript.  We don't use TCL but I 
understand it matches beautifully with Sqlite, by design.


If we were to rigidly lock into existing technologies we would still be 
using punch cards or perhaps clay tablets.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Age calculation on literal

[RB Smissaert]

> How are you running this query?

I am running this from Excel VBA with a free wrapper from Olaf Schmidt,
dhSQLite, based on 3.3.17. I will check my code, but can't think of a way
why it should add 100 with literals and not on table fields.
Must admit I have been wrong before with these kind of things ...

RBS



-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when 
> date('2002-01-01', '+' || (strftime('%Y', 'now') - 
> strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
> then 
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
> end
>
> Why is this?
>
>   
This works for me in the sqlite shell as shown below:

C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
   ...> case when
   ...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
   ...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
   ...> then
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
   ...> else
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
   ...> end
   ...> ;
5

How are you running this query?

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Age calculation on literal

[RB Smissaert]

Sorry, forget about this, it was something in the VBA code, so nil to do
with SQLite.

Could I ask you how I would get the age in months? I can see it will be
along similar lines, but maybe you have worked it out already.
I need it to be full calendar months, so, if current date is 2007-05-31 then
DOB  Age in months
--
2007-05-01   0
2007-04-30   1
2007-01-01   4
Etc.

RBS


-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 31 May 2007 22:17
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Age calculation on literal

RB Smissaert wrote:
> Thanks to Dennis Cote I got a nice way to get the age from the date in the
> form '-nmm-dd'. It works fine when I run it on a field, but when I run
> it on a literal date it gives me 100 too much:
>
> select
> case when 
> date('2002-01-01', '+' || (strftime('%Y', 'now') - 
> strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
> then 
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
> else
> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
> end
>
> Why is this?
>
>   
This works for me in the sqlite shell as shown below:

C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
   ...> case when
   ...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
   ...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
   ...> then
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
   ...> else
   ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
   ...> end
   ...> ;
5

How are you running this query?

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Age calculation on literal

[Dennis Cote]

RB Smissaert wrote:

Thanks to Dennis Cote I got a nice way to get the age from the date in the
form '-nmm-dd'. It works fine when I run it on a field, but when I run
it on a literal date it gives me 100 too much:

select
case when 
date('2002-01-01', '+' || (strftime('%Y', 'now') - 
strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
then 
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
else
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
end


Why is this?

  

This works for me in the sqlite shell as shown below:

C:\Documents and Settings\DennisC>sqlite3
SQLite version 3.3.15
Enter ".help" for instructions
sqlite>
sqlite> select
  ...> case when
  ...> date('2002-01-01', '+' || (strftime('%Y', 'now') -
  ...> strftime('%Y', '2002-01-01')) || ' years') <= date('now')
  ...> then
  ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01')
  ...> else
  ...> strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1
  ...> end
  ...> ;
5

How are you running this query?

Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Age calculation on literal

[RB Smissaert]

Thanks to Dennis Cote I got a nice way to get the age from the date in the
form '-nmm-dd'. It works fine when I run it on a field, but when I run
it on a literal date it gives me 100 too much:

select
case when 
date('2002-01-01', '+' || (strftime('%Y', 'now') - 
strftime('%Y', '2002-01-01')) || ' years') <= date('now') 
then 
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') 
else
strftime('%Y', 'now') - strftime('%Y', '2002-01-01') - 1 
end

Why is this?

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Inserting text string with quotes gives an error - php

[Igor Tandetnik]

Sabot <[EMAIL PROTECTED]> wrote:

// so query looks like:
INSERT INTO version (date,active,category,entry) VALUES ('2007-05-31
18:50','x','NOTE','test 'test' test');


The problem is of course with the last field, 'test 'test' test' . It's 
not valid SQL. If you want to put a single quote into a string literal, 
you need to double it, as in 'here''s a ''quoted'' word' .  In PHP, see 
sqlite_escape_string: http://us.php.net/sqlite-escape-string


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Inserting text string with quotes gives an error - php

[Trey Mack]

Inside string literals, escape single quotes with 2 single quotes

'test 'test' test' ==> 'test ''test'' test'

When I try to insert text containg single quotes via php it gives me 
syntax

error:


...

// so query looks like:
INSERT INTO version (date,active,category,entry) VALUES ('2007-05-31
18:50','x','NOTE','test 'test' test');



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Inserting text string with quotes gives an error - php

[Sabot]

Hi, I'm trying to find solution for my problem, searched archive but no
results so posting here:

When I try to insert text containg single quotes via php it gives me syntax
error:

$db = new SQLiteDatabase("notebook.sdb");  
$query = "INSERT INTO version (date,active,category,entry) VALUES
('".$date."','".$_POST['active']."','".$_POST['category']."','".$_POST['entry']."');";
 
$query = stripslashes($query); 
$db->query($query);  

// so query looks like: 
INSERT INTO version (date,active,category,entry) VALUES ('2007-05-31
18:50','x','NOTE','test 'test' test'); 

Is there any simple way to come around this? There is no such problem with
mysql :(

Regards
-- 
View this message in context: 
http://www.nabble.com/Inserting-text-string-with-quotes-gives-an-error---php-tf3848232.html#a10899753
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: CAST

[Robert Simpson]

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 31, 2007 4:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Re: CAST
> 
> You have explained the problem, which is .NET not Sqlite.  You have
> apparently done a fine job marrying the two but it might be more
> logical
> to suggest that .NET be made more flexible.
> 
> As for flexibility, programs in C or Assembler are only inflexible at
> the level of the underlying machine or operating system.  They are a
> tool to use to build an environment.
> 
> Initially the typing rules in Sqlite appeared to be a nusisance but on
> deeper thought their utility became apparent and it was possible to use
> them to great advantage.  To bloat or impair that advantage just to
> match a particular concept like .NET would be a tragedy.
> 
> You might consider developing an SQL engine ideally adapted to .NET.

I don't really know how we got here :) I think SQLite is a fantastic engine,
fast, free and flexible -- I wouldn't have spent the last 2 years
maintaining an ADO.NET provider for it if I didn't love it.  I'm certainly
not going to throw the baby out with the bath water and try and roll my own
SQL engine -- it misses the point entirely.  I'm here to support SQLite.

I'm not complaining about SQLite's lack of typing in general.  Could I use a
little help in making SQLite fit more seamlessly into these typed
environments?  Sure, the more help I get the better!

I'd love it if I could issue a CAST([MyCol] AS HAIRYLLAMA) and have some way
to yank HAIRYLLAMA out of the statement's metadata so I could tell what
specific type that column was CAST to.

Heck, I'd love it even more if there was an additional parameter in the
user-def function callback to provide SQLite a string name of a type
returned from a function so we could have more descriptive typing in there
as well.

The problem isn't .NET.  The problem is that every other database on the
planet enforces type restrictions, and hence every generic database access
layer (ODBC, OLEDB, ADO, ADO.NET) is designed around that concept.  "Don't
use a wrapper" doesn't work for every circumstance, and I can't change the
ADO.NET spec -- though I do get around it where I can to support SQLite's
typelessness.

My opinion is: Since SQLite is typeless, then it should probably have a few
more functions dedicated to type description.  Preserving the destination
typename on a CAST as well as in a userdef function would go a long way
toward accomplishing that.  Or even better, some kind of extensible type
system that enabled us to have type-specific comparison callbacks.

However -- we're not there, and may never get there, so I'll continue to
hack.  That's what open source is for, afterall :)

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] db design mixing different entities in the same table

[Ken]

Does a person ever belong to more than one organization? If so then use the 
following:
  Create table orgs  ( org_id integer, org stuff);
  Create table Persons(person_id, person stuff);
  Create table persons_orgs(person_id, orig_id);
   
  The persons_orgs is a mapping table that allows one to place a person in one 
or more organizations. 
   
  Orgs <-> persons_orgs  <->  persons 
   
  But if a person never belongs to more than one org: then just
  A special org_id = 0 could be created called "unassigned".. Thus, every 
person is then in an organization.
  Create table orgs  ( org_id integer, org stuff);
  Create table Persons(person_id, org_id, person stuff);
   
  
P Kishor <[EMAIL PROTECTED]> wrote:
  Not a SQLite-specific question per se, but a (SQLite) db design question.

I am modeling entities and their relationships to each other. A
classic network digraph kind of stuff. The entities are organizations
or persons. A person may be associated with none (org type
"unassigned") or one organization, and, of course, an organization may
have none or more persons in it.

I started with

CREATE TABLE orgs (
org_id INTEGER PRIMARY KEY,
.. bunch of org stuff ..
);

CREATE TABLE persons (
person_id INTEGER PRIMARY KEY,
org_id INTEGER -- FK to orgs(org_id)
.. bunch of person stuff ..
);

CREATE TABLE relationships (
rel_id INTEGER PRIMARY KEY,
from_id INTEGER, -- person_id or org_id
to_id INTEGER, -- person_id or org_id
entity_type INTEGER DEFAULT 0, -- 0 is "org", 1 is "person"
relationship TEXT
);

But the above doesn't seem very good to me because of the from_id and
to_id that are ambiguous depending on the entity_type. I am mixing
entity types in the same table.

Alternatively, I could have a single entities table which holds both
orgs and persons

CREATE TABLE entities (
entity_id INTEGER PRIMARY KEY,
.. bunch of org stuff if org ..
.. bunch of person stuff if person ..
);

and then follow with just the relationships table. But that would be
mixing two different types of entities in the same table, would leave
a lot empty slots depending on the entity type, and would have to have
some kind of recursive relation (persons belonging to orgs).

Ultimately, I want to grab the relationships out and model them using
a network digraph display algorigthm, but that is another problem.

Many thanks in advance for guidance.

-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[Ken]

One needs to make use of CAS or LL/SC hardware instructions. These can be used 
to implement lock-free synchronization.
  

Nuno Lucas <[EMAIL PROTECTED]> wrote:
  On 5/31/07, Eduardo Morras wrote:
> At 23:25 30/05/2007, you wrote:
> >Setting and reading individual bytes (u8 in sqlite-speak) are not
> >threadsafe either. Only reading/setting entire entire words
> >are threadsafe on most architectures.
>
> Using a uint32 for store the flags is threadsafe. There are less than 32 
> true/false values and read/set is simple. I see no difference doing
>
> if (uint8==0){ // read/test bit
> uint8=1; // set bit
> whatever more
> }

Not atomic, so not thread-safe.
You have a race condition waiting to happen.

> and
>
> if (uint32&){ // read/test bit
> uint32&&=MASK; // set bit
> whatever
> }

Also not atomic, so not thread-safe.

> in speed, and a compiler should not make worse code on last one. So say
>
> >> Also, my take on bitfields is that they are not thread/multi processor 
> >> friendly (there is no
> >> atomic "set bit"), and also compilers typically don't optimize well with 
> >> that (so before
> >> applying this patch, I would test on other platforms than gcc linux x86).
>
> is not true.

It's true not all CPUs have an atomic "set bit" operation.


Regards,
~Nuno Lucas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: db design mixing different entities in the same table

[Igor Tandetnik]

P Kishor <[EMAIL PROTECTED]> wrote:

I am modeling entities and their relationships to each other. A
classic network digraph kind of stuff. The entities are organizations
or persons. A person may  be associated with none (org type
"unassigned") or one organization, and, of course, an organization may
have none or more persons in it.

I started with

CREATE TABLE orgs (
 org_id INTEGER PRIMARY KEY,
 .. bunch of org stuff ..
);

CREATE TABLE persons (
 person_id INTEGER PRIMARY KEY,
 org_idINTEGER   -- FK to orgs(org_id)
 .. bunch of person stuff ..
);

CREATE TABLE relationships (
 rel_id   INTEGER PRIMARY KEY,
 from_id  INTEGER, -- person_id or org_id
 to_idINTEGER, -- person_id or org_id
 entity_type  INTEGER DEFAULT 0,   -- 0 is "org", 1 is "person"
 relationship TEXT
);


Why do you feel you need this last table at all? All the information is 
already available from persons.org_id.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite internal structs don't make use of C bitfields?

[Nuno Lucas]

On 5/31/07, Eduardo Morras <[EMAIL PROTECTED]> wrote:

At 23:25 30/05/2007, you wrote:
>Setting and reading individual bytes (u8 in sqlite-speak) are not
>threadsafe either. Only reading/setting entire entire words
>are threadsafe on most architectures.

Using a uint32 for store the flags is threadsafe. There are less than 32 
true/false values and read/set is simple. I see no difference doing

if (uint8==0){   // read/test bit
uint8=1; // set bit
whatever more
}


Not atomic, so not thread-safe.
You have a race condition waiting to happen.


and

if (uint32&){ // read/test bit
uint32&&=MASK;// set bit
whatever
}


Also not atomic, so not thread-safe.


in speed, and a compiler should not make worse code on last one. So say

>> Also, my take on bitfields is that they are not thread/multi processor 
friendly (there is no
>> atomic "set bit"), and also compilers typically don't optimize well with 
that (so before
>> applying this patch, I would test on other platforms than gcc linux x86).

is not true.


It's true not all CPUs have an atomic "set bit" operation.


Regards,
~Nuno Lucas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Changing Schema On The Fly

[Mitchell Vincent]

Thanks Joe! I store "date" values as noon on the given day and toss
out the time part to keep things like this from effecting my software.
I appreciate the information though!

On 5/31/07, Joe Wilson <[EMAIL PROTECTED]> wrote:

Be aware of a Windows OS bug that prevents correct conversion of epoch
integers to local date/time due to the recent US DST change:

  http://www.sqlite.org/cvstrac/tktview?tn=2322

Assuming you've applied the Windows OS DST patch, epoch-converted
times can be off by an hour for pre-2007 dates in the time periods
that used to not be be in daylight savings time, but are now as of
2007.

SQLite relies on the underlying OS to handle these timezone/DST issues.
In Windows versions prior to Vista, Windows only keeps one DST record
per timezone - regardless of different past DST periods.

--- Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> The issue isn't with SQLite at all, actually, but with the ODBC
> driver. I guess the ODBC driver "trusts" SQLite's data type
> description because I have a field called "timestamp" that actually
> stores an epoch integer in it and ODBC-aware applications see the
> datatype as "DateTime" and refuse to pass through the data. So I
> really just need to change the type description inside SQLite so it
> will report integer to the ODBC driver. And since there are a *lot* of
> databases with this problem I'd love to be able to just issue some
> sort of update through SQLite to make that happen.



  

Shape Yahoo! in your own image.  Join our Network Research Panel today!   
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7



-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] db design mixing different entities in the same table

[P Kishor]

Not a SQLite-specific question per se, but a (SQLite) db design question.

I am modeling entities and their relationships to each other. A
classic network digraph kind of stuff. The entities are organizations
or persons. A person may  be associated with none (org type
"unassigned") or one organization, and, of course, an organization may
have none or more persons in it.

I started with

CREATE TABLE orgs (
 org_id INTEGER PRIMARY KEY,
 .. bunch of org stuff ..
);

CREATE TABLE persons (
 person_id INTEGER PRIMARY KEY,
 org_idINTEGER   -- FK to orgs(org_id)
 .. bunch of person stuff ..
);

CREATE TABLE relationships (
 rel_id   INTEGER PRIMARY KEY,
 from_id  INTEGER, -- person_id or org_id
 to_idINTEGER, -- person_id or org_id
 entity_type  INTEGER DEFAULT 0,   -- 0 is "org", 1 is "person"
 relationship TEXT
);

But the above doesn't seem very good to me because of the from_id and
to_id that are ambiguous depending on the entity_type. I am mixing
entity types in the same table.

Alternatively, I could have a single entities table which holds both
orgs and persons

CREATE TABLE entities (
 entity_id INTEGER PRIMARY KEY,
 .. bunch of org stuff if org ..
 .. bunch of person stuff if person ..
);

and then follow with just the relationships table. But that would be
mixing two different types of entities in the same table, would leave
a lot empty slots depending on the entity type, and would have to have
some kind of recursive relation (persons belonging to orgs).

Ultimately, I want to grab the relationships out and model them using
a network digraph display algorigthm, but that is another problem.

Many thanks in advance for guidance.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Joe Wilson]

--- John Stanton <[EMAIL PROTECTED]> wrote:
> Sqlite lets you put in anything as the declared type.  "DEAD PARROT", 
> "MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared 
> types.  Sqlite makes the underlying type TEXT if it is not obviously 
> numeric.

The default affinity type is SQLITE_AFF_NUMERIC if SQLite cannot determine 
the type:

  SQLite version 3.3.17
  Enter ".help" for instructions
  sqlite> create table t1(a GODZILLA);
  sqlite> insert into t1 values(3);
  sqlite> insert into t1 values('duck');
  sqlite> insert into t1 values('007');
  sqlite> insert into t1 values('0004.56');
  sqlite> select a, typeof(a) from t1;
  3|integer
  duck|text
  7|integer
  4.56|real

Note, if a column has no type specified, then its affinity is none:

  SQLite version 3.3.17
  Enter ".help" for instructions
  sqlite> create table n1(a);
  sqlite> insert into n1 values('009');
  sqlite> select a, typeof(a) from n1;
  009|text

But it's up to your program or sqlite wrapper to decide how to read each 
column with the appropriate sqlite3_column_* function.

/*
** Scan the column type name zType (length nType) and return the
** associated affinity type.
**
** This routine does a case-independent search of zType for the
** substrings in the following table. If one of the substrings is
** found, the corresponding affinity is returned. If zType contains
** more than one of the substrings, entries toward the top of
** the table take priority. For example, if zType is 'BLOBINT',
** SQLITE_AFF_INTEGER is returned.
**
** Substring | Affinity
** 
** 'INT' | SQLITE_AFF_INTEGER
** 'CHAR'| SQLITE_AFF_TEXT
** 'CLOB'| SQLITE_AFF_TEXT
** 'TEXT'| SQLITE_AFF_TEXT
** 'BLOB'| SQLITE_AFF_NONE
** 'REAL'| SQLITE_AFF_REAL
** 'FLOA'| SQLITE_AFF_REAL
** 'DOUB'| SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const Token *pType){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const unsigned char *zIn = pType->z;
  const unsigned char *zEnd = >z[pType->n];

  while( zIn!=zEnd ){
h = (h<<8) + sqlite3UpperToLower[*zIn];
zIn++;
if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){ /* CHAR */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){   /* CLOB */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){   /* TEXT */
  aff = SQLITE_AFF_TEXT;
}else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')  /* BLOB */
&& (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
  aff = SQLITE_AFF_NONE;
#ifndef SQLITE_OMIT_FLOATING_POINT
}else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')  /* REAL */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
}else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')  /* FLOA */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
}else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b')  /* DOUB */
&& aff==SQLITE_AFF_NUMERIC ){
  aff = SQLITE_AFF_REAL;
#endif
}else if( (h&0x00FF)==(('i'<<16)+('n'<<8)+'t') ){/* INT */
  aff = SQLITE_AFF_INTEGER;
  break;
}
  }

  return aff;
}



   

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, 
photos & more. 
http://mobile.yahoo.com/go?refer=1GNXIC

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Changing Schema On The Fly

[Joe Wilson]

Be aware of a Windows OS bug that prevents correct conversion of epoch
integers to local date/time due to the recent US DST change:

  http://www.sqlite.org/cvstrac/tktview?tn=2322

Assuming you've applied the Windows OS DST patch, epoch-converted 
times can be off by an hour for pre-2007 dates in the time periods 
that used to not be be in daylight savings time, but are now as of 
2007.

SQLite relies on the underlying OS to handle these timezone/DST issues.
In Windows versions prior to Vista, Windows only keeps one DST record
per timezone - regardless of different past DST periods.

--- Mitchell Vincent <[EMAIL PROTECTED]> wrote:
> The issue isn't with SQLite at all, actually, but with the ODBC
> driver. I guess the ODBC driver "trusts" SQLite's data type
> description because I have a field called "timestamp" that actually
> stores an epoch integer in it and ODBC-aware applications see the
> datatype as "DateTime" and refuse to pass through the data. So I
> really just need to change the type description inside SQLite so it
> will report integer to the ODBC driver. And since there are a *lot* of
> databases with this problem I'd love to be able to just issue some
> sort of update through SQLite to make that happen.



  

Shape Yahoo! in your own image.  Join our Network Research Panel today!   
http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Changing Schema On The Fly

[Mitchell Vincent]

Yes, I've read that.

The issue isn't with SQLite at all, actually, but with the ODBC
driver. I guess the ODBC driver "trusts" SQLite's data type
description because I have a field called "timestamp" that actually
stores an epoch integer in it and ODBC-aware applications see the
datatype as "DateTime" and refuse to pass through the data. So I
really just need to change the type description inside SQLite so it
will report integer to the ODBC driver. And since there are a *lot* of
databases with this problem I'd love to be able to just issue some
sort of update through SQLite to make that happen.

Thanks for the help!

On 5/31/07, P Kishor <[EMAIL PROTECTED]> wrote:

If you read  you will see that,
"Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:



--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Elrick]

John Stanton wrote:

Sqlite lets you put in anything as the declared type.  "DEAD PARROT", 
"MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared 
types.  Sqlite makes the underlying type TEXT if it is not obviously 
numeric.



Thanks for the clarification.  I wasn't aware of that.


John

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

John Elrick wrote:

John Stanton wrote:


John Elrick wrote:




SNIP

Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user 
has that available to enforce type restrictions or to direct type 
conversions.



My apologies, I meant "any type" as in types which are not defined by 
Sqlite, such as DATE, TIMESTAMP, ADDRESS, LEFT_HANDED_SPANNER, or 
DEAD_PARROT. 


John

Sqlite lets you put in anything as the declared type.  "DEAD PARROT", 
"MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared 
types.  Sqlite makes the underlying type TEXT if it is not obviously 
numeric.
- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Changing Schema On The Fly

[P Kishor]

If you read  you will see that,
"Each value stored in an SQLite database (or manipulated by the
database engine) has one of the following storage classes:

NULL. The value is a NULL value.

INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8
bytes depending on the magnitude of the value.

REAL. The value is a floating point value, stored as an 8-byte IEEE
floating point number.

TEXT. The value is a text string, stored using the database encoding
(UTF-8, UTF-16BE or UTF-16-LE).

BLOB. The value is a blob of data, stored exactly as it was input."

All dates are stored as strings, however, useful datetime functions
are provided to manipulate those strings. Alternatively, you could
store the timestamp as seconds since the epoch. In that case, they
would be an integer and you could always convert them into date
strings.

To summarize, all you have is NULL, INTEGER, REAL, TEXT, and BLOB, and
probably some of the purists would say that is 5 types too many.


On 5/31/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote:

I have a set of databases that contain a date type called "timestamp".
I need to make those "integer" so they come through the ODBC driver
the right way. Is there any way to change all of that through queries
on-the-fly? I'd like to avoid re-creating all the databases if
possible..

Thanks!

--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net


--
Puneet Kishor http://punkish.eidesis.org/
Ph.D. Program, Nelson Institute, UW-Madison http://www.nelson.wisc.edu/
Vice-President, Open Source Geospatial Foundation http://www.osgeo.org/
S Policy Fellow, National Academy of Sciences http://www.nas.edu/
---
collaborate, communicate, compete
===

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Michael Schlenker]

John Elrick schrieb:

John Stanton wrote:

John Elrick wrote:



SNIP
Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user 
has that available to enforce type restrictions or to direct type 
conversions.


My apologies, I meant "any type" as in types which are not defined by 
Sqlite, such as DATE, TIMESTAMP, ADDRESS, LEFT_HANDED_SPANNER, or 
DEAD_PARROT.


Which is what John Stanton explained. 'any type' may be the 'declared type' for 
create table and is registered
in the master table.

But those declared types are not used for deriving types in expressions, so if 
any non trivial expression
is used in the query you only get the predefined SQLite types (most often 
Strings).

Michael

--
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Elrick]

John Stanton wrote:

John Elrick wrote:



SNIP
Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user 
has that available to enforce type restrictions or to direct type 
conversions.


My apologies, I meant "any type" as in types which are not defined by 
Sqlite, such as DATE, TIMESTAMP, ADDRESS, LEFT_HANDED_SPANNER, or 
DEAD_PARROT.




John

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Stanton]

John Elrick wrote:

Michael Schlenker wrote:


A. Pagaltzis schrieb:


* Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]:


SQLite's typelessness is an asset if you work only with SQLite
but in any application that uses multiple database engines of
which SQLite is only one supported engine, the non-standard
typelessness is something that has to be worked around.



Can you give an example of such a case? I work with several
different DBMSs, myself, and I have yet to run into trouble with
SQLite’s approach. Can you give a reallife example?



Start by already having a wrapper that allows type introspection (
e.g. DESCRIBE on oracle and reading the result set to find out what 
types are returned)

such a feature is basically non-portable to SQLite.



Although it would require some work, a thought that comes to mind would 
be to build a wrapper for create table which would permit you define any 
types you wish and then store the information in a separate table which 
in some way mirrors sqlite_master.


Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

CREATE TABLE already stores the type as its declared type.  The user has 
that available to enforce type restrictions or to direct type conversions.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Changing Schema On The Fly

[Mitchell Vincent]

I have a set of databases that contain a date type called "timestamp".
I need to make those "integer" so they come through the ODBC driver
the right way. Is there any way to change all of that through queries
on-the-fly? I'd like to avoid re-creating all the databases if
possible..

Thanks!

--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[John Elrick]

Michael Schlenker wrote:

A. Pagaltzis schrieb:

* Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]:

SQLite's typelessness is an asset if you work only with SQLite
but in any application that uses multiple database engines of
which SQLite is only one supported engine, the non-standard
typelessness is something that has to be worked around.


Can you give an example of such a case? I work with several
different DBMSs, myself, and I have yet to run into trouble with
SQLite’s approach. Can you give a reallife example?


Start by already having a wrapper that allows type introspection (
e.g. DESCRIBE on oracle and reading the result set to find out what 
types are returned)

such a feature is basically non-portable to SQLite.


Although it would require some work, a thought that comes to mind would 
be to build a wrapper for create table which would permit you define any 
types you wish and then store the information in a separate table which 
in some way mirrors sqlite_master.


Introspection would occur via this mechanism and would even move all 
introspection for any given system behind a common interface.


Just a thought.


John Elrick

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Equal distribution from random rows

[John Elrick]

Alex Teslik wrote:

On Wed, 30 May 2007 15:18:18 -0400, John Elrick wrote

  
After running a simple test, I confirmed a suspicion.  VACUUM 
doesn't reorder the ROWIDs, so you still have breaks.



My tests show otherwise:


  

SNIP

did I do something incorrectly?

  


Not incorrectly, just differently.  In my test I unintentionally used an 
INTEGER PRIMARY KEY:


CREATE TABLE FOO(
 ID INTEGER PRIMARY KEY,
 MYSTUFF TEXT
);

If you retrieve the ROWID from this test (full Ruby program at end):

CREATE TABLE FOO(
 ID INTEGER INTEGER PRIMARY KEY,
 MYSTUFF TEXT
);

INSERT INTO FOO VALUES (1, 'One');
INSERT INTO FOO VALUES (2, 'Two');
INSERT INTO FOO VALUES (3, 'Three');


You get this result:

["rowid", "ID", "MYSTUFF"]
["1", "1", "One"]
["2", "2", "Two"]
["3", "3", "Three"]
["rowid", "ID", "MYSTUFF"]
["1", "1", "One"]
["3", "3", "Three"]

I have confirmed that removing the PRIMARY KEY designator permits VACUUM 
to reorder the ROWID.  So I would amend my statement:


"After running a simple test, I confirmed a suspicion.  VACUUM may not 
reorder the ROWIDs if you use an INTEGER PRIMARY KEY, so you would still 
have breaks under those conditions."


Thanks for running your test.  I wouldn't have thought there would be a 
difference.



John


Full program:
-
require 'sqlite3'

FILENAME = ":memory:"
$db = SQLite3::Database.new( FILENAME )
$db.execute_batch(<

[sqlite] SQLite used by Google Gears

[Michael Ruck]

This will cause SQLite to spread to even more desktops around the world:
Google integrated SQLite into its new Google Gears tool, which allows web
applications to work offline. It provides several services, one of which is
a local client database.

Read more: http://gears.google.com/

Mike


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

[John Stanton]

Your design trade-off is memory against development time.  It obviously 
depends upon your product.  If you are making millions of them spend the 
time and save memory, otherwise add memory.


If I used the method described earlier I would memory map it to a disk 
file if the underlying OS supports that.  That would leave you with a 
power fail strategy which could be journalling.  If you have unreliable 
hardware which corrupts data you might look to better equipment rather 
than use error correcting algorithms.


Kalyani Tummala wrote:

Hi John,

My main memory is very limited but I have large disk to keep the
database. I need to serialize the data when the device is in switch off
mode or in a different application mode where database is not required.
I need to take care of power failure, data corruption etc.,

I consider your advice but how extensible and flexible it is for future
modifications? 


Regards
Kalyani

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 9:25 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?

In your case we would not use Sqlite and instead use a much simpler 
storage method.  Since your storage appears to be RAM resident that 
approach is indicated a fortiori.


We have had success with using storage based on AVL trees.  It is very 
fast and remains so despite repeated insertions and deletions.  The code


footprint is tiny (10K) and there is no heap usage so memory leakage can

never be a problem.  You do not have SQL in that environment but it 
would appear that you are not using it anyway.  Since your data is 
memory resident ACID compliance and logging are not an issue.


Even with quite detailed data manipulation you would be hard pressed to 
have a footprint greater than 30K.  You could cut that down by defining 
code like VDBE with a high information density and using a simple engine


to interpret that metacode.  We have successfully used that approach at 
times.


Kalyani Tummala wrote:


Hi John,
I could not understand your query properly. Let me tell you my
application scenario. 


I am planning to use sqlite as a database for storing and retrieving
media data of about 5-10k records in a device whose main memory is
extremely small. A sequence of insert statements increasing the heap
usage to nearly 70K(almost saturating point) which is crashing my
application. I want to restrict this to 30K. 

I tried closing database and reopen after some inserts but of no use. 


I have observed that, when I open the database with about 1K to 2K
records in it, inserts and updates take more heap and also gradually
increase than a a database with less than 1k records in it. 


My objective is to reduce the peak heap usage during inserts, updates
and also deletes with little or no performance degradation.

Please suggest me if I can do anything to do so.

Thank you in advance
Kalyani



 


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 29, 2007 6:51 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] How to restrict the peak heap usage during
multiple inserts and updates?

Since you are only using part of Sqlite have you considered using a


much


smaller footprint storage system which only implements the functions


you


are using?

Kalyani Tummala wrote:



Hi joe,

Thanks for your response. 


In order to reduce the footprint size, I have bypassed parser


completely



and using byte codes directly as my schema and queries are almost
compile time fixed. Hence I am not using sqlite3_prepare(). 

The following is the schema and inserts I am using. 
CREATE TABLE OBJECT(


PUOIINTEGER  PRIMARY KEY,
Storage_Id  INTEGER,
Object_Format		INTEGER, 
Protection_Status	INTEGER,

Object_Size INTEGER,
Parent_Object   INTEGER,
Non_Consumable  INTEGER,
Object_file_nameTEXT,
NameTEXT,
File_Path   TEXT
);

CREATE TABLE AUDIO(

PUOIINTEGER PRIMARY KEY,
Use_Count   INTEGER,
Audio_Bit_Rate  INTEGER,
Sample_Rate INTEGER,
Audio_Codec_TypeINTEGER,
Number_of_Channels  INTEGER,
Track   INTEGER,
Artist  TEXT,
Title   TEXT,
Genre   TEXT,
Album_Name  TEXT,
File_Path   TEXT
);

INSERT INTO OBJECT VALUES (
7, 65537, 12297, 0,
475805, 6, 0, 
'ANJANEYASTOTRAM.mp3', NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);


INSERT INTO AUDIO VALUES (
7, 6, 144100, 0,
0, 0, 6, 
NULL, NULL, NULL, NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3'
);

INSERT INTO OBJECT VALUES (
8, 65537, 12297, 0,
387406, 6, 0, 
'BHADRAM.mp3', NULL,

'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3'
);


INSERT INTO AUDIO VALUES (
8, 6, 144100, 0,
0, 0, 6, 
NULL, 

Re: [sqlite] Re: CAST

[John Stanton]

You have explained the problem, which is .NET not Sqlite.  You have 
apparently done a fine job marrying the two but it might be more logical 
to suggest that .NET be made more flexible.


As for flexibility, programs in C or Assembler are only inflexible at 
the level of the underlying machine or operating system.  They are a 
tool to use to build an environment.


Initially the typing rules in Sqlite appeared to be a nusisance but on 
deeper thought their utility became apparent and it was possible to use 
them to great advantage.  To bloat or impair that advantage just to 
match a particular concept like .NET would be a tragedy.


You might consider developing an SQL engine ideally adapted to .NET.

Robert Simpson wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 29, 2007 3:56 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Re: CAST

You are looking for a fit to one particular restrictive, proprietary
environment.  Our approach has been to work with the spirit of Sqlite
and to its strengths and to that end we designed out environment
accordingly.  Sqlite's typing has become a major asset, not a
difficulty.



All environments, proprietary or not, are restrictive in one way or another
- including C.  SQLite is flexible and adaptable, and capable of being
wedged into quite a few places -- which is what makes it a great little
engine.

For you that means captializing on SQLite's strengths and using its
typelessness as an asset.  For me, it means bringing SQLite to a mass of
.NET folks (Mono and MS) who would otherwise pass it by.  If that means
kludging a type system together to hide SQLite's typelessness, so be it.
I'd rather blunt the edge than throw the whole knife out.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: CAST

[Michael Schlenker]

A. Pagaltzis schrieb:

* Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]:

SQLite's typelessness is an asset if you work only with SQLite
but in any application that uses multiple database engines of
which SQLite is only one supported engine, the non-standard
typelessness is something that has to be worked around.


Can you give an example of such a case? I work with several
different DBMSs, myself, and I have yet to run into trouble with
SQLite’s approach. Can you give a reallife example?


Start by already having a wrapper that allows type introspection (
e.g. DESCRIBE on oracle and reading the result set to find out what types are 
returned)
such a feature is basically non-portable to SQLite.
This works fine if one gets the data direct from a table with declared types, 
which are
introspectable, but it breaks as soon as computed results (aggregates or other) 
are
included.
Basically user code fires any odd SQL at the database and expects to get a 
table structure with data
cast correctly to the 'wrapper native' datatypes. Declaring the expected types 
for the resulting row
would work, but if the wrapper only supports the introspectiv result binding 
and you have some 100k
lines of code using the wrapper you have a problem with the sqlite approach.
(its a problem with the wrapper APIs/usage, not really with SQLite)

Michael

--
Michael Schlenker
Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: [EMAIL PROTECTED]

Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215

-
To unsubscribe, send email to [EMAIL PROTECTED]
-