Re: [sqlite] Date and time

[Lloyd]

Thanks Craig Morrison. Now I got the idea clearly.

On Wed, 2006-11-08 at 00:55 -0500, Craig Morrison wrote:
> Lloyd wrote:
> >  select datetime(1162961284,'unixepoch','localtime');
> > 
> >  2006-11-08 10:18:04
> > 
> > The result is correct.
> > 
> > I would like to know how sqlite is performing the localtime correction. 
> 
> timestamps are in relation to UTC.. When you use the localtime modifier, 
> you are instructing the code to adjust the timestamp display in 
> accordance with your local settings. IOW, the offset from UTC of your 
> system is used in the calculation of the displayed date.
> 
> Google: "strftime", "asctime" and "localtime" for a better explanation.
> 


__
Scanned and protected by Email scanner

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

Re: [sqlite] Date and time

[Craig Morrison]

Lloyd wrote:

 select datetime(1162961284,'unixepoch','localtime');

 2006-11-08 10:18:04

The result is correct.

I would like to know how sqlite is performing the localtime correction. 


timestamps are in relation to UTC.. When you use the localtime modifier, 
you are instructing the code to adjust the timestamp display in 
accordance with your local settings. IOW, the offset from UTC of your 
system is used in the calculation of the displayed date.


Google: "strftime", "asctime" and "localtime" for a better explanation.

--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

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

Re: [sqlite] Date data type

[Craig Morrison]

Lloyd wrote:

Thanks gg and Craig Morrison for your informative reply.

I would like to know one more thing, can I use these date and time
functions with comparison operators? Will they return the correct result
or as per the string comparison rules?


Same rules apply, if it smells like a string.. :-)

You can always cast() the result and then do your comparison.

--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

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

[sqlite] Date and time

[Lloyd]

Hi,

I felt sqlite's date and time functions are very useful and suits my
needs.

But still am not clear about one thing. I gave the following query

select datetime(1162961284,'unixepoch');

2006-11-08 04:48:04

The date is correct but the time is 5 hours lagging. So for local time
correction I gave the following query

 select datetime(1162961284,'unixepoch','localtime');

 2006-11-08 10:18:04

The result is correct.

I would like to know how sqlite is performing the localtime correction. 

My problem is, I am getting these time stamps from network packets, And
I want to  display it accurately. (as in the localtime).

Which way can I prefer ?

Thanks,
  Lloyd.



__
Scanned and protected by Email scanner

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

Re: [sqlite] Loading a table into memory

[Jay Sprenkle]

On 11/7/06, chetana bhargav <[EMAIL PROTECTED]> wrote:

I am trying for loading only that table one into memory. Beacuse in my DB I 
have two or more three table which are quite large and I don't want to waste 
memory.  Is it possible that way.



I believe you can attach the database ":memory:" to your existing database and
use simple sql to copy it from one database to another (this would "load it into
memory"). I'm not sure that is what you want though.

You are aware the operating system will probably cache the database in memory
automatically (if it will fit) anyway? Doing it manually may not help any

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

Re: [sqlite] Question on Sqlite DB

[John Stanton]

Yes

[EMAIL PROTECTED] wrote:

Hi,




can SQLite receive as input a file containing SQL instructions?

Thank you 


Benedetta Turchi
Engineering Tools
Engineering Tools Website 





-
***
*** Symbian Software Ltd is a company registered in England and
Wales with registered number 4190020 and registered office at 2-6
Boundary Row, Southwark, London,  SE1 8HP, UK. This message is
intended only for use by the named addressee and may contain
privileged and/or confidential information. If you are not the
named addressee you should not disseminate, copy or take any action
in reliance on it. If you have received this message in error
please notify [EMAIL PROTECTED] and delete the message and any
attachments accompanying it immediately. Neither Symbian nor any of
its Affiliates accepts liability for any corruption, interception,
amendment, tampering or viruses occurring to this message in
transit or for any message sent by its employees which is not in
compliance with Symbian corporate policy. *
*




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

Re: [sqlite] Q about new SQLite API

[Peter Bierman]

At 1:17 PM + 11/7/06, [EMAIL PROTECTED] wrote:

QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?



I like sqlite3_prepare_v2. It lets me know that it's very similar to 
sqlite3_prepare, so lots of existing documentation still applies. It 
lets me know it's different, so I have to be aware that some docs may 
not. It leaves room for future changes (v3) in a way that similar 
constructs (ng) do not. And it follows the same naming pattern as the 
'sqlite3' prefix.


Something totally different, like sqlite3_compile, would work if 
sqlite3_prepare was removed from the library, but if they're both 
there but named differently like that, it will take new devs longer 
to discover which they should use and why.


-pmb

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

Re: [sqlite] Q about new SQLite API

[John Stanton]

[EMAIL PROTECTED] wrote:

I'm working on a new API routine for SQLite and I have
questions for the community.

The working name of the new api is sqlite3_prepare_v2().
sqlite3_prepare_v2() works like sqlite3_prepare() in that
it generates a prepared statement in an sqlite3_stmt
structure.  The differences is in the behavior of the
resulting sqlite3_stmt and in particular a difference in
the way sqlite3_step() responds to the sqlite3_stmt.  The
differences are these:

  * You never get an SQLITE_SCHEMA error.  sqlite3_prepare_v2
retains the original SQL and automatically reprepares and
rebinds it following a schema change.

  * sqlite3_step() returns the correct error code right
away, rather than just returning SQLITE_ERROR and making
you call sqlite3_reset() to find the true reason for the
error.

In this way, I am hoping that sqlite3_prepare_v2() will work
around two of the most visible warts in the current API.

QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?
Some possibilities include:

sqlite3_prepare_ex1
sqlite3_prepare_ng
sqlite3_new_prepare
sqlite3_compile

QUESTION 2: Are there any other API warts that need to be
worked around that can be fixed by this same change?

QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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


Nice change.

1.  sqlite3_compile  More descriptive of what is happening

2. ...

3. SQL_COMPILE_ERROR  To unambiguousloy indicate the the SQL is now 
wrong and won't compile.



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

Re: [sqlite] How sqlite works? Parsing SQL ect

[Roger Binns]

Peter Michaux wrote:

 Does anyone have a pointer to a link or book that
explains the process at a higher level then source code?


http://sqlite.org/arch.html

Roger

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

[sqlite] How sqlite works? Parsing SQL ect

[Peter Michaux]

Hi,

I would like to build a very light SQL database in javascript. I would
like to learn how sqlite parses an SQL statement and then acts on this
parsed information. Does anyone have a pointer to a link or book that
explains the process at a higher level then source code?

Thank you,
Peter

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

Re: [sqlite] Date data type

[Rob Sciuk]

On Tue, 7 Nov 2006, Me wrote:

> -MM-DD HH:MM:SS
> behaves as expected on queries
> no manipulation necessary
>
> you can set a validation mask
> CREATE TABLE [tEnforceTimestamp2] (
> [cTimestamp] TEXT CHECK (cTimestamp LIKE'-__-__ __:__:__')
> )
>  or
> CREATE TABLE [tEnforceTimestamp2] (
> [cTimestamp] TEXT CHECK(cTimestamp > '1900-01-01' AND cTimestamp <
> '2099-01-01' AND cTimestamp LIKE'-__-__ __:__:__')
> )
>

In keeping with the spirit of D.R. Hipp's "blessing", and specifically for
those who use Tcl/SQLite, but hopefully not limited to those, I've
released an old (ie: well used) relatively portable C library of Julian
date routines, and a Tcl binding for it under an open source license. Man
pages are provided for the Tcl bindings, and but the C source will have to
suffice for the C library documentation.

The makefile cross builds the .dll using MingW32 (though I have not tested
that .dll in quite some time), and builds both the Julian.DLL and
Julian.so files for both Windows and most Unix like variants.  It will
require a reasonably recent version of gnu make, and some twiddling to get
working right on your platform, unless you use FreeBSD, and then it
should just work.

While admittedly, this is not a panacea for the problems expressed in this
thread, and is not a SQLite datatype extension, it may prove to be of some
small utility to some subset of users.

http://www.controlq.com/OpenSource/Tcl_Julian.tgz

Enjoy.
Rob Sciuk

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

Re: [sqlite] Q about new SQLite API

[Roger Binns]

[EMAIL PROTECTED] wrote:

  * sqlite3_step() returns the correct error code right
away, rather than just returning SQLITE_ERROR and making
you call sqlite3_reset() to find the true reason for the
error.


I actually prefer the existing behaviour.  If you do start returning the 
error code, then what happens to the underlying statement?  Is it reset 
or finalized?  That is important distinction since the former can have 
bindings transferred and the latter not.  Also the connection can be 
closed if the latter happens but not the former. ie reset or finalize is 
going to have be called anyway to get the statement into a known state.



sqlite3_prepare_ex1


I would just go with sqlite_prepare_ex as it is a common practise and 
will also be sequential in the doc as Robert pointed out.



QUESTION 2: Are there any other API warts that need to be
worked around that can be fixed by this same change?


http://article.gmane.org/gmane.comp.db.sqlite.general/9222

Unicode:

  I guess that depends where you want the burden of byte order and 
UTF-32/16/8 conversion.  Currently they are pushed into the wrapper. 
That is ok by me or could be put in SQLite with an additional API.


SQLITE_TRANSIENT:

  Currently only takes a pointer to a free() function.  Would be more 
useful if it can also take a void* as well since I need to call the free 
function on a container object, not the char* passed in.


Duplicate code:

  An API addition in 3.3.7 removed one of the duplicate code cases. 
Another remains.


Replacing registered functions:

  Currently it is hard to do garbage collection on function and 
collation names because I can't tell if they have been replaced.  eg if 
function "Foo" is registered and then later function "fOo" is 
registered, how does the wrapper know the first one can be freed?  And 
since UTF8 is used for the API, I can't even begin to guess what case 
conversion rules are in use.  At the moment I restrict the names to pure 
alpha-numeric and upper case them.  That way I can tell in my own list 
of registered function/collation names if it is overwriting an existing 
name.


Constant names/values:

  I'd love these to be non-overlapping and uniquely prefixed.


QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?


I think it should return whatever would have happened anyway.  Users 
should not have to know or care about when particular operations happen 
under the hood.  Their statement references a table that doesn't exist 
which is an error.  The fact that it did exist 50ms ago when the prepare 
API was originally called is immaterial.


Roger

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

Re: [sqlite] Question on Sqlite DB

[Dennis Cote]

[EMAIL PROTECTED] wrote:


can SQLite receive as input a file containing SQL instructions?

  

Benedetta,

The SQLite library can't do that itself, but the command shell program 
that comes with it can using the ".read" command.


   sqlite3 mydb.db3 ".read mysql.txt"

This will open (or create) the database file and then execute all the 
SQL statements in the file mysql.txt.


HTH
Dennis Cote


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

Re: [sqlite] Re: Q about new SQLite API

[Will Leshner]

On 11/7/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:


In general, there is no rowid associated with a row returned by select.
A row may be constructed out of data taken from multiple rows of
multiple tables, or be manufactured without reference to any table at
all. If you want a rowid from a particular table, why not just retrieve
it explicitly, as a column in select statement?


That works fine if you control all the SQL. But if you are the author
of a wrapper, and your users are going to be creating all the SQL,
then you have to do tricks to get the rowid for any records in query
results. Or, you have to require users to add 'rowid' to all of their
queries. Without the rowid, it makes it impossible to provide a
high-level API for editing records.

Obviously, there are an infinite number of queries for which a rowid
doesn't exist. For those queries, a sqlite3_rowid call should just
return an error.

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

[sqlite] Re: Date data type

[A. Pagaltzis]

* Clark Christensen <[EMAIL PROTECTED]> [2006-11-07 18:05]:
> If I had it to do over, I would probably store my datetimes as
> -MM-DD HH:MM:SS strings.

Make that a “T” instead of a space, and add timezone offset info
(either “Z” for UTC or “+HHMM” for an offset), then you have RFC
3339 datetime notation (itself a constrained subset of ISO 8601).
It’s a very sensible idea to store datetimes this way.

Regards,
-- 
Aristotle Pagaltzis // 

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

Re: [sqlite] Retrieving date

[Derrell . Lipman]

Lloyd <[EMAIL PROTECTED]> writes:

> Hi,
>   I stored a unix epoch (32 bit integer) date in the sqlite data base. I
> want to retrieve it in the readable date format. For that I use the
> following query
>
> select datetime(sdate,'unixepoch') from mytab;
>
> It shows a formatted date, but there is some changes in the hours.
>
> How can I retrieve the accurately converted date and time ?

You may want:

  select datetime(sdate, 'unixepoch', 'localtime') from mytab;

Derrell

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

Re: [sqlite] Question on Sqlite DB

[Benedetta . Turchi]

Hi,

can SQLite receive as input a file containing SQL instructions?

Thank you 

Benedetta Turchi
Engineering Tools
Engineering Tools Website 




-
***
*** Symbian Software Ltd is a company registered in England and
Wales with registered number 4190020 and registered office at 2-6
Boundary Row, Southwark, London,  SE1 8HP, UK. This message is
intended only for use by the named addressee and may contain
privileged and/or confidential information. If you are not the
named addressee you should not disseminate, copy or take any action
in reliance on it. If you have received this message in error
please notify [EMAIL PROTECTED] and delete the message and any
attachments accompanying it immediately. Neither Symbian nor any of
its Affiliates accepts liability for any corruption, interception,
amendment, tampering or viruses occurring to this message in
transit or for any message sent by its employees which is not in
compliance with Symbian corporate policy. *
*

Re: [sqlite] Q about new SQLite API

[Clark Christensen]

Q1:  sqlite3_prepare_ex
Q3: SQLITE_SCHEMA

I don't currently use the APIs directly (though I have a project in mind), but 
these seem to make the most sense.

 -Clark

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 7, 2006 5:17:37 AM
Subject: [sqlite] Q about new SQLite API

I'm working on a new API routine for SQLite and I have
questions for the community.

The working name of the new api is sqlite3_prepare_v2().
sqlite3_prepare_v2() works like sqlite3_prepare() in that
it generates a prepared statement in an sqlite3_stmt
structure.  The differences is in the behavior of the
resulting sqlite3_stmt and in particular a difference in
the way sqlite3_step() responds to the sqlite3_stmt.  The
differences are these:

  * You never get an SQLITE_SCHEMA error.  sqlite3_prepare_v2
retains the original SQL and automatically reprepares and
rebinds it following a schema change.

  * sqlite3_step() returns the correct error code right
away, rather than just returning SQLITE_ERROR and making
you call sqlite3_reset() to find the true reason for the
error.

In this way, I am hoping that sqlite3_prepare_v2() will work
around two of the most visible warts in the current API.

QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?
Some possibilities include:

sqlite3_prepare_ex1
sqlite3_prepare_ng
sqlite3_new_prepare
sqlite3_compile

QUESTION 2: Are there any other API warts that need to be
worked around that can be fixed by this same change?

QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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





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

Re: [sqlite] Date data type

[Clark Christensen]

IMO, dates are a pain.  I spent considerable time trying to decide how best to 
store dates in my app(s), and eventually chose to use Unix times (integers).  
It seemed an easy choice as I program in Perl and JavaScript.

Lately, I've begun to regret the choice I made.  Every ad-hoc query I need to 
do (select * from mytable...) becomes an exercise in using SQLite date 
functions.  If I had it to do over, I would probably store my datetimes as 
-MM-DD HH:MM:SS strings.

I thought about storing as julians, too, but it's the same issue when you need 
human-readable dates in ad-hoc queries.

YMMV.  My apps are all web-based, and I frequently need to look at stored data 
using ad-hoc queries.

 -Clark

- Original Message 
From: Lloyd <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 7, 2006 2:30:34 AM
Subject: Re: [sqlite] Date data type

Thanks gg and Craig Morrison for your informative reply.

I would like to know one more thing, can I use these date and time
functions with comparison operators? Will they return the correct result
or as per the string comparison rules?

Thanks again,
  Lloyd.

On Tue, 2006-11-07 at 04:25 -0500, Craig Morrison wrote:
> Lloyd wrote:
> > Hi,
> >   How can I manage date and time using sqlite? [Do I have to do it
> > outside sqlite?]
> 
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> 


__
Scanned and protected by Email scanner

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





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

Re: [sqlite] Q about new SQLite API

[Chris Hanson]

On Nov 7, 2006, at 5:17 AM, [EMAIL PROTECTED] wrote:


QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?


What about introducing an options mechanism in place of a second  
prepare mechanism?  Do you foresee any situations where a developer  
may want to use both the existing and the enhanced prepare/step  
mechanism on the same database handle?


Here's a rough cut at what I'd expect an options mechanism to look like.

  /* in sqlite3.h */
  struct sqlite3_options {
  int option;
  void *parameter;
  };

  #define SQLITE3_OPTION_ENHANCED_PREPARE 1 /* enhanced prepare/step  
behavior */


  /* in user code */

  struct sqlite3_options options = {
  SQLITE3_OPTION_ENHANCED_PREPARE,
  (void *) 1 /* turn the option on */
  };

  err = sqlite3_set_options(, 1, );

  /* After this point the new prepare and step behavior is in force  
for the
 database handle db.  The option could be turned off if  
necessary, but

 it always applies to all operations on the database handle db. */


QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?


I think SQLITE_SCHEMA would not be unreasonable, especially since it  
would represent a much more significant situation than SQLITE_SCHEMA  
as returned by sqlite3_prepare used to.


  -- Chris


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

Re: [sqlite] String --> Integer date formatting

[Emmanuel]

Dennis Cote wrote:
> Emmanuel,
>
> Check out the functions on this page
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
>
> In particular the function julianday(datestring) will return a julian
> day number for a suitably formatted date string. The supported date
> formats are from ISO-8601, or -MM-DD, so you will have to use
> substr and concatenation (||) to rearrange your dates.
>
> HTH
> Dennis Cote

Thanks Denis this was a very good suggestion. Here is the part of my
query that converts "DD/MM/" to "-MM-DD", it works like a charm
with julianday()

substr(c.value,-4,4) || "-" || (CASE WHEN substr(c.value,-7,1) = '/'
THEN "0" || substr(c.value,-6,1) ELSE substr(c.value,-7,2) END) || "-"
|| (CASE WHEN substr(c.value,2,1) = '/' THEN "0" || substr(c.value,1,1)
ELSE substr(c.value,1,2) END)

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

Re: [sqlite] Q about new SQLite API

[Kervin L. Pierre]

Hello,

Thanks for the improvements!

Q1
Any name would be find, though the
'ex' extension seems to be popular
for that sought of thing.

Q2
A non-blocking resultset API? :)
Sorry, had to try.

Q3
I think this should be a new error
for the caution's sake.  But overall
I prefer more, finer grain, errors
than less.  The API user can always
deal with them en masse with a switch.

Best regards,
Kervin


--- [EMAIL PROTECTED] wrote:

> I'm working on a new API routine for SQLite and I
> have
> questions for the community.
> 
> The working name of the new api is
> sqlite3_prepare_v2().
> sqlite3_prepare_v2() works like sqlite3_prepare() in
> that
> it generates a prepared statement in an sqlite3_stmt
> structure.  The differences is in the behavior of
> the
> resulting sqlite3_stmt and in particular a
> difference in
> the way sqlite3_step() responds to the sqlite3_stmt.
>  The
> differences are these:
> 
>   * You never get an SQLITE_SCHEMA error. 
> sqlite3_prepare_v2
> retains the original SQL and automatically
> reprepares and
> rebinds it following a schema change.
> 
>   * sqlite3_step() returns the correct error code
> right
> away, rather than just returning SQLITE_ERROR
> and making
> you call sqlite3_reset() to find the true reason
> for the
> error.
> 
> In this way, I am hoping that sqlite3_prepare_v2()
> will work
> around two of the most visible warts in the current
> API.
> 
> QUESTION 1: sqlite3_prepare_v2 is the merely the
> working name
> for the new function.  What should the official name
> be?
> Some possibilities include:
> 
> sqlite3_prepare_ex1
> sqlite3_prepare_ng
> sqlite3_new_prepare
> sqlite3_compile
> 
> QUESTION 2: Are there any other API warts that need
> to be
> worked around that can be fixed by this same change?
> 
> QUESTION 3: Suppose there is a schema change and the
> SQL
> statement is automatically reprepared. But the
> schema change
> is such that the SQL is no longer valid.  (Perhaps
> one of the
> tables mentioned in a SELECT statement was dropped.)
> What
> error code should sqlite3_step() return in that
> case?
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
>
-
> To unsubscribe, send email to
> [EMAIL PROTECTED]
>
-
> 
> 


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

RE: [sqlite] Re: Q about new SQLite API

[Robert Simpson]

> -Original Message-
> From: Igor Tandetnik [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 07, 2006 8:15 AM
> To: SQLite
> Subject: [sqlite] Re: Q about new SQLite API
> 
> Marco Bambini <[EMAIL PROTECTED]> wrote:
> > 2. maybe with the new sqlite3_compile routine there should also be a
> > way to retrieve the rowid of the current row (NULL is no valid rowid
> > is found), a possible API could be sqlite3_rowid to call after each
> > sqlite3_step only if it returns an SQLITE_ROW is returned.
> 
> In general, there is no rowid associated with a row returned 
> by select. 
> A row may be constructed out of data taken from multiple rows of 
> multiple tables, or be manufactured without reference to any table at 
> all. If you want a rowid from a particular table, why not 
> just retrieve 
> it explicitly, as a column in select statement?

I had actually written some add-on funcs to do something similar, but the
code is experimental:


// Exclude btree.c from the project and include this file instead
#include "src/btree.c"
#include "src/vdbeint.h"

// Before using these two API calls, you must do these steps:
// Step 1:  Prepare any SQL statement
// Step 2:  Get the metadata for each column returned in the statement
// Step 3:  For each column, get the database and table (if any) for that
column.
// Step 4:  For each database, record the rowid (database index) of the
database
// Step 5:  For each table referenced, get the root page of that table


// Given a statement, an index of the database and the table's root page,
return (if any) the index of the cursor the
// statement is using on that table
__declspec(dllexport) int WINAPI sqlite3_table_cursor(sqlite3_stmt *pstmt,
int iDb, Pgno tableRootPage)
{
  Vdbe *p = (Vdbe *)pstmt;
  int n;

  for (n = 0; n < p->nCursor && p->apCsr[n] != NULL; n++)
  {
if (p->apCsr[n]->isTable == FALSE) continue;
if (p->apCsr[n]->iDb != iDb) continue;
if (p->apCsr[n]->pCursor->pgnoRoot == tableRootPage)
  return n;
  }
  return -1;
}

// Try and fetch the rowid for a given cursor.  The cursor is obtained from
sqlite3_table_cursor()
__declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt *pstmt,
int cursor, sqlite_int64 *prowid)
{
  Vdbe *p = (Vdbe *)pstmt;
  int rc = 0;
  Cursor *pC;

  if (cursor < 0 || cursor >= p->nCursor) return SQLITE_ERROR;
  if (p->apCsr[cursor] == NULL) return SQLITE_ERROR;
  pC = p->apCsr[cursor];

  rc = sqlite3VdbeCursorMoveto(pC);
  if( rc ) return rc;

  if( pC->rowidIsValid )
  {
*prowid = pC->lastRowid;
  }
  else if(pC->pseudoTable )
  {
*prowid = keyToInt(pC->iKey);
  }
  else if(pC->nullRow || pC->pCursor==0)
  {
return SQLITE_ERROR;
  }
  else
  {
if (pC->pCursor == NULL) return SQLITE_ERROR;
sqlite3BtreeKeySize(pC->pCursor, prowid);
*prowid = keyToInt(*prowid);
  }
  return 0;
}



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

Re: [sqlite] Q about new SQLite API

[Scott Hess]

On 11/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

  * You never get an SQLITE_SCHEMA error.  sqlite3_prepare_v2
   retains the original SQL and automatically reprepares and
   rebinds it following a schema change.


Does this part of the change require a new API at all?


  * sqlite3_step() returns the correct error code right
away, rather than just returning SQLITE_ERROR and making
you call sqlite3_reset() to find the true reason for the
error.


I've been thinking a way to work around this would be to turn on
extended error reporting, and stuff the SQLITE_SCHEMA error up there.
It's still a wart, but not so complicated as now.  Alternately, there
could be a config setting on the order of "return SQLITE_SCHEMA from
sqlite3_step()" which people could call to indicate what they prefer.

Obviously, neither of those options gets to a world where the default
expectation becomes the basic error-handling.  But at this point, it's
going to be confusing no matter what, even with sqlite3_prepare_v2(),
the sqlite3_step() documentation has to clarify that you could get
different sets of error codes depending on how you prepared the
statement.


QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?


If the answers above were "No" and "That would work", then no new name
would be required...

[I should note that I don't care if there's a new name, I'm just being
contrary!]


QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?


It would be interesting if there were fine-grained locks on the
schema, which would cause the schema change itself to error out if any
prepared statement would be broken.  Probably impossible in light of
multiple database connections, though.

-scott

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

Re: [sqlite] Retrieving date

[Markus Hoenicka]

UCT vs. local time, perchance?

Markus

Lloyd <[EMAIL PROTECTED]> was heard to say:

> Hi,
>   I stored a unix epoch (32 bit integer) date in the sqlite data base. I
> want to retrieve it in the readable date format. For that I use the
> following query
>
> select datetime(sdate,'unixepoch') from mytab;
>
> It shows a formatted date, but there is some changes in the hours.
>
> How can I retrieve the accurately converted date and time ?
>
> Thanks,
>  Lloyd.
>
>
> __
> Scanned and protected by Email scanner
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de


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

[sqlite] Retrieving date

[Lloyd]

Hi,
  I stored a unix epoch (32 bit integer) date in the sqlite data base. I
want to retrieve it in the readable date format. For that I use the
following query

select datetime(sdate,'unixepoch') from mytab;

It shows a formatted date, but there is some changes in the hours.

How can I retrieve the accurately converted date and time ?

Thanks,
 Lloyd.


__
Scanned and protected by Email scanner

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

Re: [sqlite] Re: Q about new SQLite API

[Marco Bambini]

I know that there are many times where a rowid could not be returned  
(this is why I suggested to return NULL or -1 for example).
A lot of times we don't have access to the sql issued by end users  
and a way to retrieve the rowid (if there is one) could simplify a  
lot all our code (and I am sure this is true for a lot of other  
developers).


---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/



On Nov 7, 2006, at 4:14 PM, Igor Tandetnik wrote:


Marco Bambini <[EMAIL PROTECTED]> wrote:

2. maybe with the new sqlite3_compile routine there should also be a
way to retrieve the rowid of the current row (NULL is no valid rowid
is found), a possible API could be sqlite3_rowid to call after each
sqlite3_step only if it returns an SQLITE_ROW is returned.


In general, there is no rowid associated with a row returned by  
select. A row may be constructed out of data taken from multiple  
rows of multiple tables, or be manufactured without reference to  
any table at all. If you want a rowid from a particular table, why  
not just retrieve it explicitly, as a column in select statement?


Igor Tandetnik

-- 
---

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





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

RE: [sqlite] Q about new SQLite API

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 07, 2006 6:18 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Q about new SQLite API
> 
> I'm working on a new API routine for SQLite and I have
> questions for the community.
[snip] 
> QUESTION 1: sqlite3_prepare_v2 is the merely the working name
> for the new function.  What should the official name be?

I prefer a prepare_ex() or something with the word "prepare" in it, because:
1.  It will appear next to the original prepare() in the docs alphabetically
so your eyes will be more inclined to fall on it
2.  People will immediately think "oh, its an extension to the prepare()
call"

> QUESTION 2: Are there any other API warts that need to be
> worked around that can be fixed by this same change?

This one doesn't really fit into this category, but I'll spit it out anyway:
I've been trying to figure out a way to mitigate starvation issues in
multi-threaded scenarios.  One idea was to latch into the busy hook, commit
hook and rollback hook, but they don't currently tell me which database of
any attached databases the hooks are being raised against.  To be effective,
I'd also need an additional hook when a transaction begins on a specific
database, too.  :)

> QUESTION 3: Suppose there is a schema change and the SQL
> statement is automatically reprepared. But the schema change
> is such that the SQL is no longer valid.  (Perhaps one of the
> tables mentioned in a SELECT statement was dropped.) What
> error code should sqlite3_step() return in that case?

Pass back whatever error you get when your internal code attempts to
recompile the statement and fails.

Robert



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

[sqlite] Re: Q about new SQLite API

[Igor Tandetnik]

Marco Bambini <[EMAIL PROTECTED]> wrote:

2. maybe with the new sqlite3_compile routine there should also be a
way to retrieve the rowid of the current row (NULL is no valid rowid
is found), a possible API could be sqlite3_rowid to call after each
sqlite3_step only if it returns an SQLITE_ROW is returned.


In general, there is no rowid associated with a row returned by select. 
A row may be constructed out of data taken from multiple rows of 
multiple tables, or be manufactured without reference to any table at 
all. If you want a rowid from a particular table, why not just retrieve 
it explicitly, as a column in select statement?


Igor Tandetnik 



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

Re: [sqlite] Q about new SQLite API

[drh]

"Klemens Friedl" <[EMAIL PROTECTED]> wrote:
> 
> Btw. are there plans to implement grouping operator (i.e. parentheses)
> and a real "NOT" (not just "exclude") features in "Fts Two" ?
> 

Maybe and no.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Q about new SQLite API

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> QUESTION 1: sqlite3_prepare_v2 is the merely the working name
> for the new function.  What should the official name be?
> Some possibilities include:
>
> sqlite3_prepare_ex1
> sqlite3_prepare_ng
> sqlite3_new_prepare
> sqlite3_compile

sqlite3_compile works for me.

> QUESTION 2: Are there any other API warts that need to be
> worked around that can be fixed by this same change?

I'll continue to think about it, but none that I can think of off hand.

> QUESTION 3: Suppose there is a schema change and the SQL
> statement is automatically reprepared. But the schema change
> is such that the SQL is no longer valid.  (Perhaps one of the
> tables mentioned in a SELECT statement was dropped.) What
> error code should sqlite3_step() return in that case?

This seems an appropriate use of SQLITE_SCHEMA.

Cheers,

Derrell

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

Re: [sqlite] Q about new SQLite API

[Marco Bambini]

This is a very good news:

1. sqlite3_compile
2. maybe with the new sqlite3_compile routine there should also be a  
way to retrieve the rowid of the current row (NULL is no valid rowid  
is found), a possible API could be sqlite3_rowid to call after each  
sqlite3_step only if it returns an SQLITE_ROW is returned.

3. SQLITE_SCHEMA

---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/




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

Re: [sqlite] Q about new SQLite API

[Ran]

1. sqlite3_re_prepare or simply sqlite3_reprepare


On 11/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:



QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?
Some possibilities include:

sqlite3_prepare_ex1
sqlite3_prepare_ng
sqlite3_new_prepare
sqlite3_compile

Re: [sqlite] Q about new SQLite API

[Dennis Jenkins]

[EMAIL PROTECTED] wrote:


QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?

  


I always liked it when errno would be "E_NOCLUE".  Not a posix standard 
though...


Seriously though...  I think that it should return whichever error would 
be returned if the statement were "prepared" using the current API and 
the table did not exist (or other error occured).


Even tough you don't currently support "alter table ... drop column" 
right now, you might add it some day.  Whatever error code 
implementation you choose now should be forward compatible (with in 
reason) with API changes like "alter table". 

If the reprepare fails for some ambiguous reason, maybe fall back to 
returning "SQLITE_SCHEMA" ?




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

Re: [sqlite] Q about new SQLite API

[Klemens Friedl]

2006/11/7, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:

QUESTION 1:


"sqlite3_prepare_ex"
(or "sqlite3_compile")


QUESTION 3:


maybe: "SQLITE_INVALID_SQL"



Btw. are there plans to implement grouping operator (i.e. parentheses)
and a real "NOT" (not just "exclude") features in "Fts Two" ?


Best regards,
Klemens Friedl

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

RE: [sqlite] Q about new SQLite API

[Michael Brehm]

Sounds great, it would certainly clean up a lot of my code :) 

1: How about sqlite3_prepare_statement()
2: Not really, most of my suggestions will be for virtual table error
handling 
3: I vote for SQLITE_SCHEMA.

Thanks!

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, November 07, 2006 8:18 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Q about new SQLite API

I'm working on a new API routine for SQLite and I have questions for the
community.

The working name of the new api is sqlite3_prepare_v2().
sqlite3_prepare_v2() works like sqlite3_prepare() in that it generates a
prepared statement in an sqlite3_stmt structure.  The differences is in the
behavior of the resulting sqlite3_stmt and in particular a difference in the
way sqlite3_step() responds to the sqlite3_stmt.  The differences are these:

  * You never get an SQLITE_SCHEMA error.  sqlite3_prepare_v2
retains the original SQL and automatically reprepares and
rebinds it following a schema change.

  * sqlite3_step() returns the correct error code right
away, rather than just returning SQLITE_ERROR and making
you call sqlite3_reset() to find the true reason for the
error.

In this way, I am hoping that sqlite3_prepare_v2() will work around two of
the most visible warts in the current API.

QUESTION 1: sqlite3_prepare_v2 is the merely the working name for the new
function.  What should the official name be?
Some possibilities include:

sqlite3_prepare_ex1
sqlite3_prepare_ng
sqlite3_new_prepare
sqlite3_compile

QUESTION 2: Are there any other API warts that need to be worked around that
can be fixed by this same change?

QUESTION 3: Suppose there is a schema change and the SQL statement is
automatically reprepared. But the schema change is such that the SQL is no
longer valid.  (Perhaps one of the tables mentioned in a SELECT statement
was dropped.) What error code should sqlite3_step() return in that case?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

Re: [sqlite] Loading a table into memory

[chetana bhargav]

I am trying for loading only that table one into memory. Beacuse in my DB I 
have two or more three table which are quite large and I don't want to waste 
memory.  Is it possible that way.

...
Chetana.


- Original Message 
From: Jay Sprenkle <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 7, 2006 10:56:07 AM
Subject: Re: [sqlite] Loading a table into memory


you can use a database named  :memory:
Is that what you were looking for?


On 11/6/06, chetana bhargav <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a read only table, which is very small(Bytes of storage 
> consumed. 1024 from the analyzer). I am wondering is there any 
> way to load this table in memory and unload it later.
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

[sqlite] Q about new SQLite API

[drh]

I'm working on a new API routine for SQLite and I have
questions for the community.

The working name of the new api is sqlite3_prepare_v2().
sqlite3_prepare_v2() works like sqlite3_prepare() in that
it generates a prepared statement in an sqlite3_stmt
structure.  The differences is in the behavior of the
resulting sqlite3_stmt and in particular a difference in
the way sqlite3_step() responds to the sqlite3_stmt.  The
differences are these:

  * You never get an SQLITE_SCHEMA error.  sqlite3_prepare_v2
retains the original SQL and automatically reprepares and
rebinds it following a schema change.

  * sqlite3_step() returns the correct error code right
away, rather than just returning SQLITE_ERROR and making
you call sqlite3_reset() to find the true reason for the
error.

In this way, I am hoping that sqlite3_prepare_v2() will work
around two of the most visible warts in the current API.

QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?
Some possibilities include:

sqlite3_prepare_ex1
sqlite3_prepare_ng
sqlite3_new_prepare
sqlite3_compile

QUESTION 2: Are there any other API warts that need to be
worked around that can be fixed by this same change?

QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Date data type

[Lloyd]

Thanks gg and Craig Morrison for your informative reply.

I would like to know one more thing, can I use these date and time
functions with comparison operators? Will they return the correct result
or as per the string comparison rules?

Thanks again,
  Lloyd.

On Tue, 2006-11-07 at 04:25 -0500, Craig Morrison wrote:
> Lloyd wrote:
> > Hi,
> >   How can I manage date and time using sqlite? [Do I have to do it
> > outside sqlite?]
> 
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> 


__
Scanned and protected by Email scanner

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

Re: [sqlite] Date data type

[Craig Morrison]

Lloyd wrote:

Hi,
  How can I manage date and time using sqlite? [Do I have to do it
outside sqlite?]


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

--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

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

Re: [sqlite] Date data type

[Günter Greschenz]

hi,

i use numeric values and the sqlite date-functions.
example:
   create table if not exists online (ip integer primary key, date number)
   insert or replace into online values (12345, julianday('now'))
   select id, datetime(date, 'localtime') date from online order by 
date desc limit 100


cu, gg


Lloyd schrieb:

Hi,
  How can I manage date and time using sqlite? [Do I have to do it
outside sqlite?]

Thanks,
  Lloyd. 



__
Scanned and protected by Email scanner

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


  


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

[sqlite] Date data type

[Lloyd]

Hi,
  How can I manage date and time using sqlite? [Do I have to do it
outside sqlite?]

Thanks,
  Lloyd. 


__
Scanned and protected by Email scanner

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