[sqlite] Copying the content of a table to another table

2007-06-04 Thread Kirrthana M
Hi all,
I have created a table with two columns c1 and c2 and inserted some
values into,in future i may include some additional columns c3,c4. to
the same table.In tat case i should not loose my data in the current
table,so i should be able to copy the content of the current table(values of
c1 and c2) to the new table and start working with the new table and delete
the existing one,could anybody give me a suggestion for doing this.

Regards
Kirrthana


Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton
OK Sqlite doesn't have a date type, period.  Just as it doesn't have 
other types, but don't let it it stop you from using dates very 
successfully with Sqlite by defining a date type and using the 
functionally built into the product.


The Sqlite storage model plus its style of declared types is ingenious 
and permits the user to embed it in neatly into a broad range of 
environments from untyped to flexibly typed to strongly typed.  For 
example we recently integrated Sqlite with Javascript and were gratified 
at how well these two tools mapped together.  We also use it integrated 
into business applications were it supports a fixed point decimal type 
with defined precision and scale.


Joe Wilson wrote:

--- John Stanton <[EMAIL PROTECTED]> wrote:

Sqlite does have a date format, it is physically a 64 bit floating point 
number.  There are functions to transform in and out of that format to 
present dates as required by the user.  The Sqlite date format uses a 
magib epoch which matches all of the major internaional date systems.



SQLite does not have a date type. Period.

What you're describing is not a true DATE type, but your own 
programming convention when dealing with dates. Your programming 
convention relies on functions, date strings and epoch-based floating 
point numbers.


If you were correct and SQLite currently supported a DATE type,
then why was this Proposed Incompatible Changes entry added to 
the SQLite wiki by DRH?


 "Support A DATE Type" 
 http://www.sqlite.org/cvstrac/wiki?p=ProposedIncompatibleChanges


Because rehashing the old arguments is pointless, here's the old thread:

 http://www.mail-archive.com/sqlite-users@sqlite.org/msg20589.html



P Kishor wrote:


There is no "DATE" format in SQLite. Dates are stored as strings. The
only formats SQLite knows and understands are TEXT, REAL, INTEGER,
BLOB, and NULL (see the link on datatypes). On the other hand, there
are built-in functions that can act on your date strings and convert
them back and forth, manipulate them, etc. Once again, see the link on
working with dates on the SQLite wiki.





 

We won't tell. Get more on shows you hate to love 
(and love to hate): Yahoo! TV's Guilty Pleasures list.
http://tv.yahoo.com/collections/265 


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




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



[sqlite] Hashed Index proposed

2007-06-04 Thread Andrew Finkenstadt

I updated the Incompatible Changes page with an idea for using hashed-key
indexes instead of b-tree indexes.  exact-match retrievals could reduce the
number of disk pages necessary to read to find the rowid from an average of
Log(B)(n) (where B is the number of buckets per b-tree) to Log(2^B)(n).


Re: [sqlite] baffled by dates

2007-06-04 Thread Joe Wilson
--- John Stanton <[EMAIL PROTECTED]> wrote:
> Sqlite does have a date format, it is physically a 64 bit floating point 
> number.  There are functions to transform in and out of that format to 
> present dates as required by the user.  The Sqlite date format uses a 
> magib epoch which matches all of the major internaional date systems.

SQLite does not have a date type. Period.

What you're describing is not a true DATE type, but your own 
programming convention when dealing with dates. Your programming 
convention relies on functions, date strings and epoch-based floating 
point numbers.

If you were correct and SQLite currently supported a DATE type,
then why was this Proposed Incompatible Changes entry added to 
the SQLite wiki by DRH?

 "Support A DATE Type" 
 http://www.sqlite.org/cvstrac/wiki?p=ProposedIncompatibleChanges

Because rehashing the old arguments is pointless, here's the old thread:

 http://www.mail-archive.com/sqlite-users@sqlite.org/msg20589.html

> 
> P Kishor wrote:
> > There is no "DATE" format in SQLite. Dates are stored as strings. The
> > only formats SQLite knows and understands are TEXT, REAL, INTEGER,
> > BLOB, and NULL (see the link on datatypes). On the other hand, there
> > are built-in functions that can act on your date strings and convert
> > them back and forth, manipulate them, etc. Once again, see the link on
> > working with dates on the SQLite wiki.



 

We won't tell. Get more on shows you hate to love 
(and love to hate): Yahoo! TV's Guilty Pleasures list.
http://tv.yahoo.com/collections/265 

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




[sqlite] SQLite and multiple process behavior

2007-06-04 Thread Jeff Nokes
Hi,
I'm a little new to SQLite, but have been using it successfully for about 8 
months, as a read only repository for application configuration parameters in 
my mod_perl based web application.  I have recently added functionality to 
update the configuration tables I have, via the web interface.  Here is my 
stack:

RedHat Linux 7.2
apache 1.3.x, prefork mode
mod_perl 1.29
DBI  1.51
DBD::SQLite  1.13

Single database file, with 3 simple configuration tables in name => value 
format.
Mulitple apache processes each with their own connection, sharing that single 
file.
No threading.

In my unit testing of this, I tried updating the the table values from multiple 
approaches, and found that I was seeing strange behavior.

Approaches:

(1)  Updated a single row, in one table, in a copy of the DB file on a build 
host, using the `sqlite3` command line tool.   I then uploaded that modified DB 
file to the application host which was already running apache, and all of my 
reads from the table still reflected the "old" row value as if it had never 
changed.

(2)  Updated a single row, in one table, in the existing DB file on the 
application host, using the `sqlite3` command line tool, while apache still 
running, and still, the web application kept reading the "old" row value.

In either of the two former scenarios, if I stopped/started apache again, the 
web application would then see the correct value in the configuration table, 
from all child processes.

(3)  Updated a single row, in one table, in the existing DB file on the 
application host, using the web interface.  This time, all apache child 
processes saw the new value immediately; each process has it's own DB 
connection that is not shared.

So, I thought that the issue with approaches 1 & 2 above could be due to memory 
caching/paging by SQLite or Linux, especially since I was modifying the DB file 
while apache had current handles connected to it from other processes (i.e. 
apache).  But when you think about it, the prefork model of apache is the same 
thing, with each child process being a separate process that has a handle with 
the DB file.

My question has multiple parts:

(Q1)  First, why would what seems to be identical multi-process interaction 
with the DB file achieve different results when attempting to view the updated 
record?

(Q2)  I have a business need to perform both small, infrequent updates, and 
larger more frequent updates to this DB file.  I am hoping to be able to not 
only perform these updates through the web interface of the application, but 
also to be able to just deploy a new DB file to all our production hosts, 
without bouncing apache, and have the updates take effect either way.  How can 
I achieve this with the behavior I have mentioned above?

Thanks in advance for any help you can offer,
- Jeff




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



Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton
Look at the date functions, the file date.c is self explanatory and 
lists the reference for the date type.  The underlying type for a date 
is a float, so that may be how you missed the date details.


P Kishor wrote:

On 6/4/07, John Stanton <[EMAIL PROTECTED]> wrote:


Sqlite does have a date format, it is physically a 64 bit floating point
number.



If that is the case, I would love to see the documentation on that. As
far as I can see, there are only TEXT, REAL, INTEGER, BLOB, NULL types
supported.


There are functions to transform in and out of that format to
present dates as required by the user.  The Sqlite date format uses a
magib epoch which matches all of the major internaional date systems.

P Kishor wrote:
> There is no "DATE" format in SQLite. Dates are stored as strings. The
> only formats SQLite knows and understands are TEXT, REAL, INTEGER,
> BLOB, and NULL (see the link on datatypes). On the other hand, there
> are built-in functions that can act on your date strings and convert
> them back and forth, manipulate them, etc. Once again, see the link on
> working with dates on the SQLite wiki.
>
> On 6/4/07, Chris Fonnesbeck <[EMAIL PROTECTED]> wrote:
>
>> I'm at a complete loss about how to work with dates in SQLite. The
>> documentation doesnt seem to be helping me. I have a table with 
some date

>> fields, in the proper -mm-dd format:
>>
>> sqlite> select First_Capture from PIT_manatees limit 5;
>> 1997-6-17
>> 1998-5-6
>> 1997-6-24
>> 1998-5-6
>> 2000-3-15
>>
>> however, it does not respond properly to comparisons to dates:
>>
>> sqlite> select First_Capture from PIT_manatees where
>> First_Capture<2000-1-1
>> limit 5;
>> sqlite>
>>
>> this doesnt work either:
>>
>> sqlite> select First_Capture from PIT_manatees where
>> First_Capture> sqlite>
>>
>> note that these columns are in the 'date' format.
>>
>> Yet more strangeness:
>>
>>
>> sqlite> select date(First_Capture) from PIT_manatees limit 10;
>>
>>
>>
>>
>>
>>
>>
>> 1999-10-13
>>
>>
>> sqlite>
>>
>> Not sure what that is all about -- all the results are blank except 
one,

>> ewven though every record is in exactly the same format.
>>
>> I'm totally perplexed by date handling in SQLite, so any help is most
>> appreciated.
>>
>> Thanks,
>> --
>> Christopher J. Fonnesbeck
>>
>> Population Ecologist, Marine Mammal Section
>> Fish & Wildlife Research Institute (FWC)
>> St. Petersburg, FL
>>
>> Adjunct Assistant Professor
>> Warnell School of Forestry and Natural Resources
>> University of Georgia
>> Athens, GA
>>
>> T: 727.235.5570
>> E: Chris dot Fonnesbeck at MyFWC dot com
>>
>
>


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 










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



Re: [sqlite] sqlite function list?

2007-06-04 Thread John Stanton

Scott Baker wrote:

Is there a list somewhere (I can't find it on the wiki) of all the
functions (specifically math) functions that sqlite understands?

I'm thinking things like: int, round, floor, ceil, sqrt etc.

You have the source.  They are all presented there and you can add more 
if you choose.


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



Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton

Chris Fonnesbeck wrote:

On 6/4/07, P Kishor <[EMAIL PROTECTED]> wrote:



There is no "DATE" format in SQLite. Dates are stored as strings. The
only formats SQLite knows and understands are TEXT, REAL, INTEGER,
BLOB, and NULL (see the link on datatypes). On the other hand, there
are built-in functions that can act on your date strings and convert
them back and forth, manipulate them, etc. Once again, see the link on
working with dates on the SQLite wiki.





Wow, thanks. So, why does this table creation work then, and not give an
error?

CREATE TABLE PIT_manatees(Manatee_Log INTEGER, Name text, First_Capture
date, Recovered date, Coast text)

Because Sqlite stores any type you choose as the declared type.  Read 
more about type affinity and manifest typring.


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



Re: [sqlite] baffled by dates

2007-06-04 Thread P Kishor

On 6/4/07, John Stanton <[EMAIL PROTECTED]> wrote:

Sqlite does have a date format, it is physically a 64 bit floating point
number.


If that is the case, I would love to see the documentation on that. As
far as I can see, there are only TEXT, REAL, INTEGER, BLOB, NULL types
supported.


There are functions to transform in and out of that format to
present dates as required by the user.  The Sqlite date format uses a
magib epoch which matches all of the major internaional date systems.

P Kishor wrote:
> There is no "DATE" format in SQLite. Dates are stored as strings. The
> only formats SQLite knows and understands are TEXT, REAL, INTEGER,
> BLOB, and NULL (see the link on datatypes). On the other hand, there
> are built-in functions that can act on your date strings and convert
> them back and forth, manipulate them, etc. Once again, see the link on
> working with dates on the SQLite wiki.
>
> On 6/4/07, Chris Fonnesbeck <[EMAIL PROTECTED]> wrote:
>
>> I'm at a complete loss about how to work with dates in SQLite. The
>> documentation doesnt seem to be helping me. I have a table with some date
>> fields, in the proper -mm-dd format:
>>
>> sqlite> select First_Capture from PIT_manatees limit 5;
>> 1997-6-17
>> 1998-5-6
>> 1997-6-24
>> 1998-5-6
>> 2000-3-15
>>
>> however, it does not respond properly to comparisons to dates:
>>
>> sqlite> select First_Capture from PIT_manatees where
>> First_Capture<2000-1-1
>> limit 5;
>> sqlite>
>>
>> this doesnt work either:
>>
>> sqlite> select First_Capture from PIT_manatees where
>> First_Capture> sqlite>
>>
>> note that these columns are in the 'date' format.
>>
>> Yet more strangeness:
>>
>>
>> sqlite> select date(First_Capture) from PIT_manatees limit 10;
>>
>>
>>
>>
>>
>>
>>
>> 1999-10-13
>>
>>
>> sqlite>
>>
>> Not sure what that is all about -- all the results are blank except one,
>> ewven though every record is in exactly the same format.
>>
>> I'm totally perplexed by date handling in SQLite, so any help is most
>> appreciated.
>>
>> Thanks,
>> --
>> Christopher J. Fonnesbeck
>>
>> Population Ecologist, Marine Mammal Section
>> Fish & Wildlife Research Institute (FWC)
>> St. Petersburg, FL
>>
>> Adjunct Assistant Professor
>> Warnell School of Forestry and Natural Resources
>> University of Georgia
>> Athens, GA
>>
>> T: 727.235.5570
>> E: Chris dot Fonnesbeck at MyFWC dot com
>>
>
>


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





--
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] extracting and comparing dates

2007-06-04 Thread John Stanton
We use declared types of DATE, TIMESTAMP and DATETIME and store dates as 
floating point using the Sqlite date conversion functions.  The 
applications get dates formatted as ISO8601 or according to the declared 
locale.  Functions do date artithmetic.


Samuel R. Neff wrote:

SQLite doesn't have any internal notion of a date, only numbers or strings.
You can choose to store dates as numbers and SQLite has some conversion
functions to help you work with dates.  Personally I store dates as strings.

These are not properly formatted dates:

1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

should be

1997-06-17
1998-05-06
1997-06-24
1998-05-06
2000-03-15 


this

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1

should be

sqlite> select First_Capture from PIT_manatees where
First_Capture<'2000-01-01'

and this

sqlite> select First_Capture from PIT_manatees where
First_Capture select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

How does one extract years, months, dates from tables in SQLite? The
'extract' function also seems not to work.

Any help most appreciated.


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




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



Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton
Sqlite does have a date format, it is physically a 64 bit floating point 
number.  There are functions to transform in and out of that format to 
present dates as required by the user.  The Sqlite date format uses a 
magib epoch which matches all of the major internaional date systems.


P Kishor wrote:

There is no "DATE" format in SQLite. Dates are stored as strings. The
only formats SQLite knows and understands are TEXT, REAL, INTEGER,
BLOB, and NULL (see the link on datatypes). On the other hand, there
are built-in functions that can act on your date strings and convert
them back and forth, manipulate them, etc. Once again, see the link on
working with dates on the SQLite wiki.

On 6/4/07, Chris Fonnesbeck <[EMAIL PROTECTED]> wrote:


I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper -mm-dd format:

sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where 
First_Capture<2000-1-1

limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

Yet more strangeness:


sqlite> select date(First_Capture) from PIT_manatees limit 10;







1999-10-13


sqlite>

Not sure what that is all about -- all the results are blank except one,
ewven though every record is in exactly the same format.

I'm totally perplexed by date handling in SQLite, so any help is most
appreciated.

Thanks,
--
Christopher J. Fonnesbeck

Population Ecologist, Marine Mammal Section
Fish & Wildlife Research Institute (FWC)
St. Petersburg, FL

Adjunct Assistant Professor
Warnell School of Forestry and Natural Resources
University of Georgia
Athens, GA

T: 727.235.5570
E: Chris dot Fonnesbeck at MyFWC dot com







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



Re: [sqlite] extracting and comparing dates

2007-06-04 Thread John Stanton

Chris Fonnesbeck wrote:

I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper -mm-dd format:

sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

How does one extract years, months, dates from tables in SQLite? The
'extract' function also seems not to work.

Any help most appreciated.


Store the dates in the internal Sqlite julian mode.

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



Re: [sqlite] Re: Dropping updates after exiting application

2007-06-04 Thread Omar Eljumaily
OK, I think I found the problem.  What I was doing was calling the 
following inappropriately (for the wrong statement):


   sqlite3_finalize(_stmt);

I was doing it previous to the update calls.  There was nothing wrong 
with the update calls.  The problematic finalize was for a lookup.


The details of what was going on are a bit complex.  I'm trying to 
develop a generic database front end API.  Debugging these things can be 
a bit convoluted.  I know there are lots of other APIs that do this, but 
not quite in the same manner as I'd like.


http://www.dbleaper.com/  - definitely alpha at this point, so don't try 
anything important with it.


Thanks,

Omar


Igor Tandetnik wrote:

Will Leshner <[EMAIL PROTECTED]>
wrote:

On 6/4/07, Omar Eljumaily
 wrote:

qlite3_prepare_v2(con->_db, text.c_str(), text.size(),  &_stmt,
); sqlite3_bind_text(_stmt, which, s.c_str(), s.size(),
SQLITE_TRANSIENT); sqlite3_step(_stmt);


Is one step enough to finish the operation?


One step is always enough for INSERT, UPDATE and DELETE as they don't 
produce a resultset. Only SELECT requires multiple steps, one for each 
row.


Igor Tandetnik

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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



Re: [sqlite] Re: Dropping updates after exiting application

2007-06-04 Thread Will Leshner

On 6/4/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


One step is always enough for INSERT, UPDATE and DELETE as they don't
produce a resultset. Only SELECT requires multiple steps, one for each
row.


I thought that might be the case. Thanks.

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



[sqlite] Re: Dropping updates after exiting application

2007-06-04 Thread Igor Tandetnik

Will Leshner <[EMAIL PROTECTED]>
wrote:

On 6/4/07, Omar Eljumaily
 wrote:

qlite3_prepare_v2(con->_db, text.c_str(), text.size(),  &_stmt,
); sqlite3_bind_text(_stmt, which, s.c_str(), s.size(),
SQLITE_TRANSIENT); sqlite3_step(_stmt);


Is one step enough to finish the operation?


One step is always enough for INSERT, UPDATE and DELETE as they don't 
produce a resultset. Only SELECT requires multiple steps, one for each 
row.


Igor Tandetnik 



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



Re: [sqlite] Dropping updates after exiting application

2007-06-04 Thread Will Leshner

On 6/4/07, Omar Eljumaily <[EMAIL PROTECTED]> wrote:


qlite3_prepare_v2(con->_db, text.c_str(), text.size(),  &_stmt, );
sqlite3_bind_text(_stmt, which, s.c_str(), s.size(), SQLITE_TRANSIENT);
sqlite3_step(_stmt);


Is one step enough to finish the operation?

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



Re: [sqlite] Re: Dropping updates after exiting application

2007-06-04 Thread Omar Eljumaily

Igor Tandetnik wrote:


Do you, by any chance, begin a transaction, perform the updates, then 
close the DB connection without committing the transaction (e.g. when 
exiting the app)? If you do this or something similar, the transaction 
gets rolled back.


Igor Tandetnik

This is what I have suspected, but I have checked to make sure this 
isn't going on.  There is no transaction control in my app.  In fact if 
I do the following:


   int i = sqlite3_get_autocommit(_con->_db);
   sqlite3_step(_stmt);
   i = sqlite3_get_autocommit(_con->_db);

i returns 1 both times.



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



[sqlite] Re: Dropping updates after exiting application

2007-06-04 Thread Igor Tandetnik

Omar Eljumaily  wrote:

"update mytable set name = 'newname' where id = 1"

would propogate for the life of the application, then disappear after
the app exits.


Do you, by any chance, begin a transaction, perform the updates, then 
close the DB connection without committing the transaction (e.g. when 
exiting the app)? If you do this or something similar, the transaction 
gets rolled back.


Igor Tandetnik 



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



[sqlite] Dropping updates after exiting application

2007-06-04 Thread Omar Eljumaily
I have this problem where my application is is dropping updates after 
exiting my application.


create mytable
{
int id primary key,
name text
};

Something like:

"insert into mytable(name) values('myname')"

would update properly.

However

"update mytable set name = 'newname' where id = 1"

would propogate for the life of the application, then disappear after 
the app exits.


I've checked the autocommit state and that's set to 1.

I'm using roughly:

qlite3_prepare_v2(con->_db, text.c_str(), text.size(),  &_stmt, );
sqlite3_bind_text(_stmt, which, s.c_str(), s.size(), SQLITE_TRANSIENT);
sqlite3_step(_stmt);

If I view the file in sqlite3.exe, I don't see the changes.  The update 
changes only appear in my app, then don't reappear the next time I load 
my app.


Thanks.


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



Re: [sqlite] sqlite function list?

2007-06-04 Thread Trey Mack

Included functions:
http://www.sqlite.org/lang_expr.html
See the section "Core Functions" near the bottom of the page for the builtin 
functions.


To add more:
http://www.sqlite.org/contrib
extension-functions.tgz  for details on adding functions like ceil.

- TM

- Original Message - 
From: "Scott Baker" <[EMAIL PROTECTED]>

To: 
Sent: Monday, June 04, 2007 3:43 PM
Subject: [sqlite] sqlite function list?



Is there a list somewhere (I can't find it on the wiki) of all the
functions (specifically math) functions that sqlite understands?

I'm thinking things like: int, round, floor, ceil, sqrt etc.

--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

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





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



RE: [sqlite] baffled by dates

2007-06-04 Thread Griggs, Donald
Hi Chris,

I think you've seen Rich Shepard's reply, and emails crossed.  

But just in case you didn't:  The sqlite date/time functions require
input dates to contain a preceeding zero for month and day-of-month
where needed to make them two digits.  E.g.,
'2007-6-4'   bad
'2007-06-04' good

Regarding: CREATE TABLE PIT_manatees(Manatee_Log INTEGER, Name text,
First_Capture date, Recovered date, Coast text)

Remember that SQLITE does not prevent you from putting most ANYTHING as
a data type.  E.g.,
CREATE TABLE manatees ( myData  BIG_GREY_SLIPPERY_BEASTIES_TYPE);
Works quite fine.

Be sure to see: http://www.sqlite.org/datatype3.html



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Chris Fonnesbeck
Sent: Monday, June 04, 2007 3:24 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] baffled by dates


Wow, thanks. So, why does this table creation work then, and not give an
error?

CREATE TABLE PIT_manatees(Manatee_Log INTEGER, Name text, First_Capture
date, Recovered date, Coast text)

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



[sqlite] Re: baffled by dates

2007-06-04 Thread Igor Tandetnik

Chris Fonnesbeck
 wrote:

On 6/4/07, P Kishor <[EMAIL PROTECTED]>
wrote:


There is no "DATE" format in SQLite.


Wow, thanks. So, why does this table creation work then, and not give
an error?

CREATE TABLE PIT_manatees(Manatee_Log INTEGER, Name text,
First_Capture date, Recovered date, Coast text)


SQLite will accept pretty much anything as a column type. For details, 
see


http://sqlite.org/datatype3.html

Igor Tandetnik 



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



[sqlite] sqlite function list?

2007-06-04 Thread Scott Baker
Is there a list somewhere (I can't find it on the wiki) of all the
functions (specifically math) functions that sqlite understands?

I'm thinking things like: int, round, floor, ceil, sqrt etc.

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

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



Re: [sqlite] baffled by dates

2007-06-04 Thread Scott Baker
What you want is here:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

I'm thinking you want:

select First_Capture from PIT_manatees where date(First_Capture) <
'2000-1-1' limit 5;

- Scott

Chris Fonnesbeck wrote:
> I'm at a complete loss about how to work with dates in SQLite. The
> documentation doesnt seem to be helping me. I have a table with some date
> fields, in the proper -mm-dd format:
> 
> sqlite> select First_Capture from PIT_manatees limit 5;
> 1997-6-17
> 1998-5-6
> 1997-6-24
> 1998-5-6
> 2000-3-15
> 
> however, it does not respond properly to comparisons to dates:
> 
> sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
> limit 5;
> sqlite>
> 
> this doesnt work either:
> 
> sqlite> select First_Capture from PIT_manatees where
> First_Capture sqlite>
> 
> note that these columns are in the 'date' format.
> 
> Yet more strangeness:
> 
> 
> sqlite> select date(First_Capture) from PIT_manatees limit 10;
> 
> 
> 
> 
> 
> 
> 
> 1999-10-13
> 
> 
> sqlite>
> 
> Not sure what that is all about -- all the results are blank except one,
> ewven though every record is in exactly the same format.
> 
> I'm totally perplexed by date handling in SQLite, so any help is most
> appreciated.
> 
> Thanks,

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

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



Re: [sqlite] baffled by dates

2007-06-04 Thread Chris Fonnesbeck

On 6/4/07, P Kishor <[EMAIL PROTECTED]> wrote:


There is no "DATE" format in SQLite. Dates are stored as strings. The
only formats SQLite knows and understands are TEXT, REAL, INTEGER,
BLOB, and NULL (see the link on datatypes). On the other hand, there
are built-in functions that can act on your date strings and convert
them back and forth, manipulate them, etc. Once again, see the link on
working with dates on the SQLite wiki.




Wow, thanks. So, why does this table creation work then, and not give an
error?

CREATE TABLE PIT_manatees(Manatee_Log INTEGER, Name text, First_Capture
date, Recovered date, Coast text)


Re: [sqlite] extracting and comparing dates

2007-06-04 Thread Rich Shepard

On Mon, 4 Jun 2007, Chris Fonnesbeck wrote:


I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper -mm-dd format:

sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15



Any help most appreciated.


Chris,

  I cannot tell you the answer with certainty, but two things jump out at me
about your data:

  1) They are not in -mm-dd format, but in -m(m)-d(d) format. I
always enter dates with a leading zero; e.g., 1997-06-17.

  2) Dates are strings (see  for
SQLite3 data storage classes). I put them between single quotes for
comparison purposes as text strings.

HTH,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

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



RE: [sqlite] extracting and comparing dates

2007-06-04 Thread Samuel R. Neff

SQLite doesn't have any internal notion of a date, only numbers or strings.
You can choose to store dates as numbers and SQLite has some conversion
functions to help you work with dates.  Personally I store dates as strings.

These are not properly formatted dates:

1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

should be

1997-06-17
1998-05-06
1997-06-24
1998-05-06
2000-03-15 

this

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1

should be

sqlite> select First_Capture from PIT_manatees where
First_Capture<'2000-01-01'

and this

sqlite> select First_Capture from PIT_manatees where
First_Capture select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

How does one extract years, months, dates from tables in SQLite? The
'extract' function also seems not to work.

Any help most appreciated.


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



Re: [sqlite] baffled by dates

2007-06-04 Thread P Kishor

There is no "DATE" format in SQLite. Dates are stored as strings. The
only formats SQLite knows and understands are TEXT, REAL, INTEGER,
BLOB, and NULL (see the link on datatypes). On the other hand, there
are built-in functions that can act on your date strings and convert
them back and forth, manipulate them, etc. Once again, see the link on
working with dates on the SQLite wiki.

On 6/4/07, Chris Fonnesbeck <[EMAIL PROTECTED]> wrote:

I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper -mm-dd format:

sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

Yet more strangeness:


sqlite> select date(First_Capture) from PIT_manatees limit 10;







1999-10-13


sqlite>

Not sure what that is all about -- all the results are blank except one,
ewven though every record is in exactly the same format.

I'm totally perplexed by date handling in SQLite, so any help is most
appreciated.

Thanks,
--
Christopher J. Fonnesbeck

Population Ecologist, Marine Mammal Section
Fish & Wildlife Research Institute (FWC)
St. Petersburg, FL

Adjunct Assistant Professor
Warnell School of Forestry and Natural Resources
University of Georgia
Athens, GA

T: 727.235.5570
E: Chris dot Fonnesbeck at MyFWC dot com




--
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] baffled by dates

2007-06-04 Thread Chris Fonnesbeck

I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper -mm-dd format:

sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

Yet more strangeness:


sqlite> select date(First_Capture) from PIT_manatees limit 10;







1999-10-13


sqlite>

Not sure what that is all about -- all the results are blank except one,
ewven though every record is in exactly the same format.

I'm totally perplexed by date handling in SQLite, so any help is most
appreciated.

Thanks,
--
Christopher J. Fonnesbeck

Population Ecologist, Marine Mammal Section
Fish & Wildlife Research Institute (FWC)
St. Petersburg, FL

Adjunct Assistant Professor
Warnell School of Forestry and Natural Resources
University of Georgia
Athens, GA

T: 727.235.5570
E: Chris dot Fonnesbeck at MyFWC dot com


[sqlite] extracting and comparing dates

2007-06-04 Thread Chris Fonnesbeck

I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper -mm-dd format:

sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15

however, it does not respond properly to comparisons to dates:

sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>

this doesnt work either:

sqlite> select First_Capture from PIT_manatees where
First_Capture

note that these columns are in the 'date' format.

How does one extract years, months, dates from tables in SQLite? The
'extract' function also seems not to work.

Any help most appreciated.


Re: [sqlite] No space left on device?

2007-06-04 Thread Nuno Lucas

On 6/4/07, Alberto Simões <[EMAIL PROTECTED]> wrote:

What happens when no space is left on device? Does the process die, or
does it try to store as much as it can using main memory?


SQLite should return an error indicating it failed to write to disk
and the reason indicates there is no space left. It's up to your
application to handle the error gracefully.


I'm asking this because I have a process dying (being killed) because
it exauted main memory.


That is probably a memory leak in you application (maybe some bug in
the error path).

Regards.
~Nuno Lucas


Cheers
Alberto
--
Alberto Simões


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



[sqlite] No space left on device?

2007-06-04 Thread Alberto Simões

Hi

What happens when no space is left on device? Does the process die, or
does it try to store as much as it can using main memory?

I'm asking this because I have a process dying (being killed) because
it exauted main memory.

Cheers
Alberto
--
Alberto Simões

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