[sqlite] Re: SQLite and Columnar Databases

2007-12-17 Thread Darren Duncan

At 6:59 AM +0530 12/18/07, Yuvaraj Athur Raghuvir wrote:

Thanks for the interesting discussion. What I got so far is summarized
below:
1) Row based versus Column based storage is an implementation detail.
2) SQL used for access is independent of storage mechanism adopted.
3) Row based storage with indices on all columns reaches read performance of
column based storage.
4) Creating/updating indices  fast using new algorithms is a direction of
improvement for SQLite


The main way that this difference is an implementation detail is in 
the sense that your database schema and the DBMS API can be used 
unchanged with both.  However, the 2 have different performance 
characteristics, which is why one would pick one over the other.


If a DBMS is smart enough, it can automatically pick the best storage 
method for performance and you don't have to think about it.


However, many DBMS are not that smart and so typically users find 
themselves making explicit changes to their schemas, specifying the 
storage method explicitly, in order to compensate and/or give the 
DBMS hints.  In these typical situations, what should be an 
implementation detail is something that can have a lot of impact on 
your schema design.



Now, if the storage is an implementation detail, can the following scenario
be realized?
a) Given: Distributed highly-available system which is implemented as
maintaining replicas of data
b) The replicas of data have different storage mechanisms which is also
recorded in the (distributed) database coordinator.
c) This would, in essence, be a hybrid database - hybrid in the sense of
using different data storage strategies (row-based / column-based) in the
replicas.

This would allow for the database coordinator to intelligently respond to
the various operations on the database by redirecting the  original request
to the appropriate replica. The cost would be when the data changes and each
of the replicas have to be brought into sync. Here again, the intelligence
should be such that the storage schema that achieves the best performance
for that SQL statement should be used and the sync can happen in the back
ground.

My perspective is that progressively, the data storage (implementation)
strategies will pay an important role given that OLTP/OLAP requirements are
getting blurred.


That could all be made to work, but I don't know if anyone actually 
has implemented this yet ... or maybe that was your intention.


-- Darren Duncan

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



RE: [sqlite] Improving performance of SQLite. Anyone heard ofDevice SQL?

2007-12-17 Thread Fred Williams
Went south in a rush.  Agreed.  BTW, I can drive a forklift, and a back
hoe...

Fred

> -Original Message-
> From: James Steward [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 17, 2007 6:43 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard
> ofDevice SQL?
>
>
> On Mon, 2007-12-17 at 19:22 -0500, John Elrick wrote:
> > John Elrick wrote:
> >
> > SNIP
> > > When you can hire a forklift operator to program (well, that is),
> >
> >
> > To avoid a misunderstanding...I mean - right off the
> forklift.  I'm sure
> > anyone with the proper motivation can learn to program, but
> it took me
> > 25 years to realize how little I really knew.
>
> I didn't write the original about forklift operators.  It was Fred
> Williams, AFAIK.
>
> I am in agreement with you, and I have nothing against forklift
> operators either.
>
> IMHO, this has gone completely off topic, and I shall hence
> forth cease
> to contribute to this, and related threads.
>
> Regards,
> James.
>
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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



RE: [sqlite] Improving performance of SQLite. Anyone heard ofDevice SQL?

2007-12-17 Thread Fred Williams
It's the ones who never figure out how little they know that do all the
damage...

Fred

> -Original Message-
> From: John Elrick [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 17, 2007 6:22 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard
> ofDevice SQL?
>
>
> John Elrick wrote:
>
> SNIP
> > When you can hire a forklift operator to program (well, that is),
>
>
> To avoid a misunderstanding...I mean - right off the
> forklift.  I'm sure
> anyone with the proper motivation can learn to program, but
> it took me
> 25 years to realize how little I really knew.
>
>
> John
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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



RE: [sqlite] Improving performance of SQLite. Anyone heard ofDeviceSQL?

2007-12-17 Thread Fred Williams
There you go!  Thinking like an engineer (logical) and not management
:-)

I have met a few $100K+ programmers out there, but none were VB'ers.
Just a bunch of "namby pamby" C and C++ wizards :-)


Fred

> -Original Message-
> From: John Elrick [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 17, 2007 6:16 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard
> ofDeviceSQL?
>
>
> James Steward wrote:
> > On Mon, 2007-12-17 at 15:30 -0600, Fred Williams wrote:
> >
> >> A hundred or so Visual Basic programmers are cheaper to replace and
> >> "maintain" than one good Delphi/C++ programmer. ;-)  That
> is the reason
> >> management likes "Visual ."  Been there, learned that.
>  Hire the
> >> staff from the largest pool, not the most effective.
> Besides it's damn
> >> hard to be a prima donna, when your replacement is ready
> to jump off
> >> that forklift and learn a cushy job.
> >>
>
> For some reason your original never showed up in my mailbox.  Pity
>
> When you can hire a forklift operator to program (well, that is), you
> can just as likely have the computer program itself at zero
> labor cost.
>
> Oh (100 * $45,000/year = $450,000.  I'll be happy if someone
> would pay
> me that much  I should ask for a raise.)  It's also an exponential
> growth rate in management costs to manage one hundred people
> instead of
> one
>
>
> John
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread John Stanton

Joanne, there are no bad questions, only bad answers.

Use sqlite3_bind_double and your timestamp will be of type double (a 64 
bit FP number),


Joanne Pham wrote:

Hi John,
Thanks for the detail info.
I am still very new to SQLite3 and sorry for the question.
My application is used C++ to insert/select the data from this table. So if I 
defined it as
  create table mytable (
  
 createDate REAL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (remoteWXId)
);
 Then I can use sqlite3_bind_real to bind the column but what is the datatype 
that I should use in C++ code.Do you have any example code that work for C++ in 
this case.
Sorry for the question.
Thanks,
JP


- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 12:59:00 PM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.


Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
6. current_time
returns current time as HH:MM:SS
7. current_date
returns current date as -MM-DD
8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:

Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.

Thanks,
Joanne




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
   1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


   2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
   then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs


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


 

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.   http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 




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


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



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



[sqlite] Re: SQLite and Columnar Databases

2007-12-17 Thread Yuvaraj Athur Raghuvir
Hello,

Thanks for the interesting discussion. What I got so far is summarized
below:

1) Row based versus Column based storage is an implementation detail.
2) SQL used for access is independent of storage mechanism adopted.
3) Row based storage with indices on all columns reaches read performance of
column based storage.
4) Creating/updating indices  fast using new algorithms is a direction of
improvement for SQLite

Now, if the storage is an implementation detail, can the following scenario
be realized?
a) Given: Distributed highly-available system which is implemented as
maintaining replicas of data
b) The replicas of data have different storage mechanisms which is also
recorded in the (distributed) database coordinator.
c) This would, in essence, be a hybrid database - hybrid in the sense of
using different data storage strategies (row-based / column-based) in the
replicas.

This would allow for the database coordinator to intelligently respond to
the various operations on the database by redirecting the  original request
to the appropriate replica. The cost would be when the data changes and each
of the replicas have to be brought into sync. Here again, the intelligence
should be such that the storage schema that achieves the best performance
for that SQL statement should be used and the sync can happen in the back
ground.

My perspective is that progressively, the data storage (implementation)
strategies will pay an important role given that OLTP/OLAP requirements are
getting blurred.

Thoughts?

Regards,
Yuva



On Dec 13, 2007 4:42 AM, Yuvaraj Athur Raghuvir <[EMAIL PROTECTED]>
wrote:

> Hello,
>
> There seems to be a high interest in columnar databases recently.
>
> Is there any plan of supporting data organization as a columnar database
> in SQLite? What are the challenges here?
>
> Regards,
> Yuva
>


Re: [sqlite] Improving performance of SQLite. Anyone heardofDevice SQL?

2007-12-17 Thread John Elrick




I didn't write the original about forklift operators.  It was Fred
Williams, AFAIK.

  

It was.

SNIP

IMHO, this has gone completely off topic, and I shall hence forth cease
to contribute to this, and related threads.
  


I think you may be correct.


John

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



Re: [sqlite] Improving performance of SQLite. Anyone heard ofDevice SQL?

2007-12-17 Thread James Steward
On Mon, 2007-12-17 at 19:22 -0500, John Elrick wrote:
> John Elrick wrote:
> 
> SNIP
> > When you can hire a forklift operator to program (well, that is),
> 
> 
> To avoid a misunderstanding...I mean - right off the forklift.  I'm sure 
> anyone with the proper motivation can learn to program, but it took me 
> 25 years to realize how little I really knew.

I didn't write the original about forklift operators.  It was Fred
Williams, AFAIK.

I am in agreement with you, and I have nothing against forklift
operators either.

IMHO, this has gone completely off topic, and I shall hence forth cease
to contribute to this, and related threads.

Regards,
James.


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



Re: [sqlite] Improving performance of SQLite. Anyone heard ofDevice SQL?

2007-12-17 Thread John Elrick

John Elrick wrote:

SNIP

When you can hire a forklift operator to program (well, that is),



To avoid a misunderstanding...I mean - right off the forklift.  I'm sure 
anyone with the proper motivation can learn to program, but it took me 
25 years to realize how little I really knew.



John

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



Re: [sqlite] Improving performance of SQLite. Anyone heard ofDevic eSQL?

2007-12-17 Thread John Elrick

James Steward wrote:

On Mon, 2007-12-17 at 15:30 -0600, Fred Williams wrote:
  

A hundred or so Visual Basic programmers are cheaper to replace and
"maintain" than one good Delphi/C++ programmer. ;-)  That is the reason
management likes "Visual ."  Been there, learned that.  Hire the
staff from the largest pool, not the most effective.  Besides it's damn
hard to be a prima donna, when your replacement is ready to jump off
that forklift and learn a cushy job.



For some reason your original never showed up in my mailbox.  Pity

When you can hire a forklift operator to program (well, that is), you 
can just as likely have the computer program itself at zero labor cost. 

Oh (100 * $45,000/year = $450,000.  I'll be happy if someone would pay 
me that much  I should ask for a raise.)  It's also an exponential 
growth rate in management costs to manage one hundred people instead of 
one



John

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



RE: [sqlite] Improving performance of SQLite. Anyone heard of Devic eSQL?

2007-12-17 Thread Fred Williams
I doubt it, you are obviously aggressive enough to learn more than the
Mickysoft studio.

Honestly, how many Visual X programmers do you know that have come
from the "rank and file?"  As a consultant, I have seen a very large
proportion who have.  I admire anyone who has the desire and ambition to
better himself.  I just don't agree with management when they take the
least cost approach to their technical hiring.

But, that is one of the main reasons there are so many "place holders"
in companies.  Those same companies readily hire outside expertise to
accomplish their technical tasks, because internally they lack the
technical strength.

Fred

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 17, 2007 3:40 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Improving performance of SQLite. Anyone heard of
> Devic eSQL?
>
>
> Wow, I know this isn't really the point of your comment, but
> I always have to jump in when I see VB programmers
> "attacked".  So as a Delphi / C++ / VB programmer, where do I
> fit into your analogy?  Do I end up replacing myself :)
>
> --
> Eric Pankoke
> Founder / Lead Developer
> Point Of Light Software
> http://www.polsoftware.com/
>
>  -- Original message --
> From: "Fred Williams" <[EMAIL PROTECTED]>
> > A hundred or so Visual Basic programmers are cheaper to replace and
> > "maintain" than one good Delphi/C++ programmer. ;-)  That
> is the reason
> > management likes "Visual ."  Been there, learned that.  Hire the
> > staff from the largest pool, not the most effective.
> Besides it's damn
> > hard to be a prima donna, when your replacement is ready to jump off
> > that forklift and learn a cushy job.
> >
> > Fred
> >
> > > -Original Message-
> > > From: John Elrick [mailto:[EMAIL PROTECTED]
> > > Sent: Sunday, December 16, 2007 7:36 PM
> > > To: sqlite-users@sqlite.org
> > > Subject: Re: [sqlite] Improving performance of SQLite.
> Anyone heard of
> > > Devic eSQL?
> > >
> > >
> > > John Stanton wrote:
> > > > This also is an anecdote from some time back.  As we
> were signing a
> > > > fairly significant software contract with a large
> > > organization their
> > > > manager told us "You guys know nothing about marketing.  Your
> > > > presentation was unprofessional, no glossy brochures, no
> > > audio visuals
> > > > and we would not have bought except that you were the
> only ones who
> > > > convinced us you could do the job".  We just smiled and
> watched the
> > > > ink dry while we pondered "where did we go right?".
> > > >
> > > > The simple truth is that if you hype a product and sell
> it into an
> > > > area where it is inadequate your triumph is short lived and
> > > the scorn
> > > > and litigation enduring.  On the other hand if you deliver
> > > a solution
> > > > which works as well, or preferably better, than
> proposed you have
> > > > generated raving fans who will buy again and endorse your
> > > product to
> > > > all and sundry.  Which is the better model?
> > >
> > > To quote a former programs manager for Bank of America "the first
> > > solution which meets my business needs and performs the job
> > > adequately".  In this case, adequately can be defined as
> loosely as
> > > "doesn't crash too often" or as stringently as "positively no
> > > errors",
> > > depending on the business use.
> > >
> > > Keeping the discussion academic, "hype a product..." is a
> > > business model
> > > that apparently has been used to at least some degree by a company
> > > called Microsoft.  It tends to work because the model permits
> > > them such
> > > an early lead that even better products have difficulty
> catching up.
> > >
> > > I do most of my programming in Delphi, a Borland product
> > > which remains
> > > in my opinion, even in its shadow of former glory state,
> a far more
> > > straightforward and powerful product than Visual Studio.
> Borland has
> > > always been a technical company, not a market driven one and its
> > > flagship product is surviving only because it remains a more well
> > > rounded Windows solution than its competition.  However,
> it is only
> > > surviving and is unlikely to actually thrive ever again.
> > >
> > > So my suggested answer is, the proven model is "dominate
> the market
> > > early with an adequate product".  If your product is very
> > > good and even
> > > better than proposed, all the better.  But if you are "Johnny come
> > > lately", you will likely lose unless your product is very,
> > > very good.
> > > And, whether we like it or not, a big part of market
> domination is to
> > > convince all the decision makers (management) and
> decision breakers
> > > (engineers with influence) that yours is the safest
> choice to make.
> > >
> > > FWIW
> > >
> > >
> > > John Elrick
> > >
> > > --
> > > ---
> > > To unsubscribe, send email to 

[sqlite] RE: Visual Studio (was Improving performance of SQLite)

2007-12-17 Thread Robert Simpson
I was a die-hard Amiga programmer years ago and swore never to put a PC on
my desk at home.  Unfortunately they used them at work, so I was forced to
learn it.

After a few years of being a broke enthusiast on the Amiga, I wrote a
business app on the PC in Visual C and VB3.  It was my first Windows app,
and I sold it for 6 figures to a Fortune 100 company.

I quit that day job, started my first software company, and the PC has been
on my desk ever since.

Although I did abandon VB before VB4 came out (in favor of C/C++) I have
been using Visual development tools ever since.  So you guys can knock it
all you like ... I and thousands of others have made quite a pile of money
spinning Microsoft-driven products :)

Robert

> -Original Message-
> From: James Steward [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 17, 2007 3:16 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Improving performance of SQLite. Anyone 
> heard ofDevic eSQL?
> 
> On Mon, 2007-12-17 at 15:30 -0600, Fred Williams wrote:
> > A hundred or so Visual Basic programmers are cheaper to replace and
> > "maintain" than one good Delphi/C++ programmer. ;-)  That 
> is the reason
> > management likes "Visual ."  Been there, learned that.  Hire the
> > staff from the largest pool, not the most effective.  
> Besides it's damn
> > hard to be a prima donna, when your replacement is ready to jump off
> > that forklift and learn a cushy job.
> 
> Ouch.  Lucky Visual  is not a cross platform language.  Show the
> Visual  programmers a bit of Tcl/Tk and watch them wilt!
> 
> It looks like Xilinx wrote their entire ISE GUI in Tcl/Tk, and the
> backend apps are cross compiled.  I can run it on Linux as well as
> NoDose, and the GUI is identical.  Try that in Visual poop.
> 
> You might have cheap programmers today, but tomorrow they will be less
> useful.  Look out for Tux!
> 
> At every planning meeting I push open source, and cross platform
> solutions, because I know today the majority is still under Bill's
> spell, but the magic in Vista is fading...
> 
> 
> Ciao.
> James.
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 



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



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Dennis Cote

Joanne Pham wrote:

My application is used C++ to insert/select the data from this table. So if I 
defined it as
  create table mytable (
  
 createDate REAL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (remoteWXId)
);
 Then I can use sqlite3_bind_real to bind the column but what is the datatype 
that I should use in C++ code.Do you have any example code that work for C++ in 
this case.


Joanne,

If you define your table using the "default current_timestamp" clause, 
SQLite will insert the data and time as a string, not as a floating 
point real value.


sqlite> create table t(a int, b real default current_timestamp);
sqlite> insert into t(a) values(1);
sqlite> select a, b, typeof(b) from t;
a   btypeof(b)
--  ---  --
1   2007-12-17 21:56:48  text 
sqlite> insert into t values(2, julianday('now'));

sqlite> select a, b, typeof(b), datetime(b) from t;
a   btypeof(b)   datetime(b)
--  ---  --  ---
1   2007-12-17 21:56:48  text2007-12-17 21:56:48
2   2454452.42202395 real2007-12-17 22:07:42

SQLite calls it's internal floating point type REAL, but the C API 
function used to bind such values is sqlite3_bind_double(), and it binds 
a standard C or C++ double value. Some sample code is shown below. The 
first inserts a julian day number directly, the second uses the 
julianday() function to convert a text string inserted by the code into 
a julian day number in the database.


   sqlite3_stmt* s;
   sqlite3_prepare_v2(db, "insert into t values(:a, :b)", -1, , 0);

   double tomorrow = 2454453.5;
   sqlite3_bind_int(s, 1, 3);
   sqlite3_bind_double(s, 2, tomorrow);

   sqlite3_step(s);
   sqlite3_finalize(s);


   sqlite3_prepare_v2(db, "insert into t values(:a, julianday(:b))", 
-1, , 0);


   char* yesterday = "2007-12-16 00:00:00";
   sqlite3_bind_int(s, 1, 4);
   sqlite3_bind_text(s, 2, yesterday, -1, SQLITE_STATIC);

   sqlite3_step(s);
   sqlite3_finalize(s);


HTH
Dennis Cote


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



RE: [sqlite] Improving performance of SQLite. Anyone heard of Devic eSQL?

2007-12-17 Thread James Steward
On Mon, 2007-12-17 at 15:30 -0600, Fred Williams wrote:
> A hundred or so Visual Basic programmers are cheaper to replace and
> "maintain" than one good Delphi/C++ programmer. ;-)  That is the reason
> management likes "Visual ."  Been there, learned that.  Hire the
> staff from the largest pool, not the most effective.  Besides it's damn
> hard to be a prima donna, when your replacement is ready to jump off
> that forklift and learn a cushy job.

Ouch.  Lucky Visual  is not a cross platform language.  Show the
Visual  programmers a bit of Tcl/Tk and watch them wilt!

It looks like Xilinx wrote their entire ISE GUI in Tcl/Tk, and the
backend apps are cross compiled.  I can run it on Linux as well as
NoDose, and the GUI is identical.  Try that in Visual poop.

You might have cheap programmers today, but tomorrow they will be less
useful.  Look out for Tux!

At every planning meeting I push open source, and cross platform
solutions, because I know today the majority is still under Bill's
spell, but the magic in Vista is fading...


Ciao.
James.


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



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Joanne Pham
Hi John,
Thanks for the detail info.
I am still very new to SQLite3 and sorry for the question.
My application is used C++ to insert/select the data from this table. So if I 
defined it as
  create table mytable (
  
 createDate REAL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (remoteWXId)
);
 Then I can use sqlite3_bind_real to bind the column but what is the datatype 
that I should use in C++ code.Do you have any example code that work for C++ in 
this case.
Sorry for the question.
Thanks,
JP


- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 12:59:00 PM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.

Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
6. current_time
returns current time as HH:MM:SS
7. current_date
returns current date as -MM-DD
8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:
> Thanks John,
> " If you make it a REAL instead of DATETIME your code will 
> be clearer." 
> So you meant that I should make my datatype as REAL instead of DATETIME.
> Thanks,
> Joanne
> 
> 
> 
> 
> - Original Message 
> From: John Stanton <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Monday, December 17, 2007 10:00:11 AM
> Subject: Re: [sqlite] create table with datatype = DATE. SHould use as 
> DATETIME or INTEGER.
> 
> If you declared your date and time (timestamp) column DATETIME it will 
> be floating point and will store date and time in 8 bytes.  Use the FP 
> bind function.  If you make it a REAL instead of DATETIME your code will 
> be clearer.
> 
> Joanne Pham wrote:
>> Hi All,
>> I have two question regarding DATETIME column data type:
>>1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier 
>> if this column type is INTEGER vs DATETIME then 
>>do the conversion in the GUI code to convert from INTEGER TO DATETIME.
>>
>>2)  And if I store as DATETIME then What is the command to bind this 
>> column type as DATETIME.
>>  For the INTEGER the bind command is : sqlite3_bind_int but I don't know 
>> if the column is DATETIME 
>>then what is the command to bind this column.
>> Thanks,
>> JP
>>
>>
>> - Original Message 
>> From: P Kishor <[EMAIL PROTECTED]>
>> To: sqlite-users@sqlite.org
>> Sent: Thursday, December 13, 2007 2:48:26 PM
>> Subject: Re: [sqlite] create table with datatype = DATE
>>
>> CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)
>>
>> On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
>>> Hi All,
>>> I create the table as :
>>>create table test (name varchar(30), createDate DATE default 
>>> DATETIME('NOW'));
>>> but I got the error message. I want to have the default as now if it is not 
>>> specify.
>>> Thanks in advance,
>>> Joanne
>>>
>>>
>>>  
>>> 
>>> Never miss a thing.  Make Yahoo your home page.
>>> http://www.yahoo.com/r/hs
>>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
>  
> 
> Be a better friend, newshound, and 
> know-it-all with Yahoo! Mobile.  Try it now.   
> http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 
> 


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


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

RE: [sqlite] Improving performance of SQLite. Anyone heard of Devic eSQL?

2007-12-17 Thread epankoke
Wow, I know this isn't really the point of your comment, but I always have to 
jump in when I see VB programmers "attacked".  So as a Delphi / C++ / VB 
programmer, where do I fit into your analogy?  Do I end up replacing myself :)

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "Fred Williams" <[EMAIL PROTECTED]>
> A hundred or so Visual Basic programmers are cheaper to replace and
> "maintain" than one good Delphi/C++ programmer. ;-)  That is the reason
> management likes "Visual ."  Been there, learned that.  Hire the
> staff from the largest pool, not the most effective.  Besides it's damn
> hard to be a prima donna, when your replacement is ready to jump off
> that forklift and learn a cushy job.
> 
> Fred
> 
> > -Original Message-
> > From: John Elrick [mailto:[EMAIL PROTECTED]
> > Sent: Sunday, December 16, 2007 7:36 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard of
> > Devic eSQL?
> >
> >
> > John Stanton wrote:
> > > This also is an anecdote from some time back.  As we were signing a
> > > fairly significant software contract with a large
> > organization their
> > > manager told us "You guys know nothing about marketing.  Your
> > > presentation was unprofessional, no glossy brochures, no
> > audio visuals
> > > and we would not have bought except that you were the only ones who
> > > convinced us you could do the job".  We just smiled and watched the
> > > ink dry while we pondered "where did we go right?".
> > >
> > > The simple truth is that if you hype a product and sell it into an
> > > area where it is inadequate your triumph is short lived and
> > the scorn
> > > and litigation enduring.  On the other hand if you deliver
> > a solution
> > > which works as well, or preferably better, than proposed you have
> > > generated raving fans who will buy again and endorse your
> > product to
> > > all and sundry.  Which is the better model?
> >
> > To quote a former programs manager for Bank of America "the first
> > solution which meets my business needs and performs the job
> > adequately".  In this case, adequately can be defined as loosely as
> > "doesn't crash too often" or as stringently as "positively no
> > errors",
> > depending on the business use.
> >
> > Keeping the discussion academic, "hype a product..." is a
> > business model
> > that apparently has been used to at least some degree by a company
> > called Microsoft.  It tends to work because the model permits
> > them such
> > an early lead that even better products have difficulty catching up.
> >
> > I do most of my programming in Delphi, a Borland product
> > which remains
> > in my opinion, even in its shadow of former glory state, a far more
> > straightforward and powerful product than Visual Studio.  Borland has
> > always been a technical company, not a market driven one and its
> > flagship product is surviving only because it remains a more well
> > rounded Windows solution than its competition.  However, it is only
> > surviving and is unlikely to actually thrive ever again.
> >
> > So my suggested answer is, the proven model is "dominate the market
> > early with an adequate product".  If your product is very
> > good and even
> > better than proposed, all the better.  But if you are "Johnny come
> > lately", you will likely lose unless your product is very,
> > very good.
> > And, whether we like it or not, a big part of market domination is to
> > convince all the decision makers (management) and decision breakers
> > (engineers with influence) that yours is the safest choice to make.
> >
> > FWIW
> >
> >
> > John Elrick
> >
> > --
> > ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > --
> > ---
> >
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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



Re: [sqlite] table.* excludes ID field when using natural join ?

2007-12-17 Thread Dennis Cote

Samuel R. Neff wrote:

I would expect "SELECT T.*" to always return all fields from table T.
However this seems not to be the case when using natural join.


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table a(aid integer);
sqlite> create table b(bid integer, aid integer);
sqlite> insert into a values (1);
sqlite> insert into b values (2, 1);
sqlite> select * from a natural join b;
aid bid
--  --
1   2
sqlite> select b.* from a natural join b;
bid
--
2
sqlite>


I realize that in the natural join the field "aid" from the first "select *"
query is not considered to belong to either table a or table b, but still in
the second query, shouldn't "select b.*" include all fields from table b?

  


Sam,

You have stated the answer yourself. The result of the natural join does 
not contain the columns from either table when there is a column with 
the same name in both tables. If you imagine the natural join columns 
coming from a pseudo table named j then the result has columns j.aid and 
b.bid. When you select b.* from this result there is only one column 
from table b, the one sqlite returns.


HTH
Dennis Cote



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



RE: [sqlite] Improving performance of SQLite. Anyone heard of Devic eSQL?

2007-12-17 Thread Fred Williams
A hundred or so Visual Basic programmers are cheaper to replace and
"maintain" than one good Delphi/C++ programmer. ;-)  That is the reason
management likes "Visual ."  Been there, learned that.  Hire the
staff from the largest pool, not the most effective.  Besides it's damn
hard to be a prima donna, when your replacement is ready to jump off
that forklift and learn a cushy job.

Fred

> -Original Message-
> From: John Elrick [mailto:[EMAIL PROTECTED]
> Sent: Sunday, December 16, 2007 7:36 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Improving performance of SQLite. Anyone heard of
> Devic eSQL?
>
>
> John Stanton wrote:
> > This also is an anecdote from some time back.  As we were signing a
> > fairly significant software contract with a large
> organization their
> > manager told us "You guys know nothing about marketing.  Your
> > presentation was unprofessional, no glossy brochures, no
> audio visuals
> > and we would not have bought except that you were the only ones who
> > convinced us you could do the job".  We just smiled and watched the
> > ink dry while we pondered "where did we go right?".
> >
> > The simple truth is that if you hype a product and sell it into an
> > area where it is inadequate your triumph is short lived and
> the scorn
> > and litigation enduring.  On the other hand if you deliver
> a solution
> > which works as well, or preferably better, than proposed you have
> > generated raving fans who will buy again and endorse your
> product to
> > all and sundry.  Which is the better model?
>
> To quote a former programs manager for Bank of America "the first
> solution which meets my business needs and performs the job
> adequately".  In this case, adequately can be defined as loosely as
> "doesn't crash too often" or as stringently as "positively no
> errors",
> depending on the business use.
>
> Keeping the discussion academic, "hype a product..." is a
> business model
> that apparently has been used to at least some degree by a company
> called Microsoft.  It tends to work because the model permits
> them such
> an early lead that even better products have difficulty catching up.
>
> I do most of my programming in Delphi, a Borland product
> which remains
> in my opinion, even in its shadow of former glory state, a far more
> straightforward and powerful product than Visual Studio.  Borland has
> always been a technical company, not a market driven one and its
> flagship product is surviving only because it remains a more well
> rounded Windows solution than its competition.  However, it is only
> surviving and is unlikely to actually thrive ever again.
>
> So my suggested answer is, the proven model is "dominate the market
> early with an adequate product".  If your product is very
> good and even
> better than proposed, all the better.  But if you are "Johnny come
> lately", you will likely lose unless your product is very,
> very good.
> And, whether we like it or not, a big part of market domination is to
> convince all the decision makers (management) and decision breakers
> (engineers with influence) that yours is the safest choice to make.
>
> FWIW
>
>
> John Elrick
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread John Stanton
Sqlite stores a date and time as a REAL so instead of trusting to 
manifest typing to make it a REAL your code will be easier to follow if 
you declare it a REAL.


Here is a simple list of the date and time functions embedded in Sqlite
1. date( timestring, modifier, modifier, ...)
returns date as -MM-DD
   2. time( timestring, modifier, modifier, ...)
returns time as HH:MM:SS
   3. datetime( timestring, modifier, modifier, ...)
returns datetime as -MM-DD HH:MM:SS
   4. julianday( timestring, modifier, modifier, ...)
returns julian day, which is a float-point number
counting the number of days since 4714 B.C.
   5. strftime( format, timestring, modifier, modifier, ...)
returns a string in the desired format (like printf)
   6. current_time
returns current time as HH:MM:SS
   7. current_date
returns current date as -MM-DD
   8. current_timestamp
returns current timestamp as -MM-DD HH:MM:SS
ttp://www.somacon.com/p370.php

Joanne Pham wrote:

Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.

Thanks,
Joanne




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
   1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


   2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
   then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs





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


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 




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



[sqlite] table.* excludes ID field when using natural join ?

2007-12-17 Thread Samuel R. Neff

I would expect "SELECT T.*" to always return all fields from table T.
However this seems not to be the case when using natural join.


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>sqlite3
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> create table a(aid integer);
sqlite> create table b(bid integer, aid integer);
sqlite> insert into a values (1);
sqlite> insert into b values (2, 1);
sqlite> select * from a natural join b;
aid bid
--  --
1   2
sqlite> select b.* from a natural join b;
bid
--
2
sqlite>


I realize that in the natural join the field "aid" from the first "select *"
query is not considered to belong to either table a or table b, but still in
the second query, shouldn't "select b.*" include all fields from table b?

Thanks,

Sam




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



[sqlite] Sqlite in Python on Bluehost Web hosting

2007-12-17 Thread Shawn Milochik
I searched the archives, but couldn't find a way to fix this on my own.

I use Bluehost.com for Web hosting. I like Python. I like Sqlite. I'm trying
to use the three together.

Bluehost supports Python, and sqlite is installed. I have ssh access.
However, there is no pysqlite2 or apsw support built in to their Python
installation. I tried installing pysqlite2 myself (my preferred method), and
also apsw, but they error out for dependency reasons that I can't fix.

I have sent a help desk ticket into Bluehost asking for them to install
pysqlite2, but I'm not terribly hopeful. Does anyone know how to get sqlite
support in Python working within a home directory on Linux without having
root access?

Thanks,
Shawn


Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Dennis Cote

Joanne Pham wrote:

I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
then what is the command to bind this column.
  

Joanne,

You need to familiarize yourself with SQLite's type system. See 
http://www.sqlite.org/datatype3.html for the fundamental data types that 
SQLite supports, and how it handles them.


A column with a declared type of DATETIME, has an column affinity of 
NUMERIC. And such a column can store any of the supported datatypes.


The preferred method of storing time and date information in SQLite is 
using a Julian Day number (see http://en.wikipedia.org/wiki/Julian_Day) 
which is floating point number where the integral part represents the 
date, and the fractional part represents the time. SQLite can use this 
format to efficiently order and compare dates and times in SQL queries.  
You can use the builtin date and time functions (see 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions) to convert 
the stored Julian Day numbers to standard (at least nearly ISO-8601 
standard) date and time strings for display or manipulation by the 
language you are using to drive SQLite.


If you use Julian Day numbers you can declare your timetsamp column REAL 
or FLOAT (or possibly JULIAN or even DATETIME if you wish), and then use 
the sqlite3_bind_double() and sqlite3_column_double() to insert and 
extract the raw floating point values, or sqlite3_column_text() to 
extract a time or date strings returned by the builtin conversion functions.


   create table test (id integer primary key, data text, created julian);

Note you can't use the "default current_timestamp" clause because it 
will insert a string version of the date and time. If you want automatic 
inserts of a Julian Day number you need to use a trigger.


   create trigger set_created after insert on test begin
   update test set created = julianday('now') where rowid = new.rowid;
   end;

If you add an index on the created column you can quickly search for 
dates and times, or date or time ranges.


   create index created_idx on test(created);

You can now easily search for ranges and convert the result for display.

   select id, data from test where created > julianday(date('now', '-30 
days'));


   select id, data, date(created)  from test order by created desc limit 1;

Most of the same things can be accomplished if you store the ISO-8601 
string verisons of the date and time in the database, but that requires 
19 or more bytes per timestamp (and the timestamp value is duplicated in 
each index as well), whereas the Julian Day number requires only 8 bytes 
per timestamp.


HTH
Dennis Cote

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



RE: Re[2]: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

2007-12-17 Thread Tom Briggs
 

> As my father was fond of saying; "Money talks and BS walks."  

   Unfortunately, that rule is decidedly invalid in the commercial
software world.

   Bear in mind that DRH doesn't sell software, which is part of the
reason why he and Encirq go about doing things differently.

   -T

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



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Joanne Pham
Thanks John,
" If you make it a REAL instead of DATETIME your code will 
be clearer." 
So you meant that I should make my datatype as REAL instead of DATETIME.
Thanks,
Joanne




- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, December 17, 2007 10:00:11 AM
Subject: Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME 
or INTEGER.

If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.

Joanne Pham wrote:
> Hi All,
> I have two question regarding DATETIME column data type:
>1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier 
> if this column type is INTEGER vs DATETIME then 
>do the conversion in the GUI code to convert from INTEGER TO DATETIME.
> 
>2)  And if I store as DATETIME then What is the command to bind this 
> column type as DATETIME.
>  For the INTEGER the bind command is : sqlite3_bind_int but I don't know 
> if the column is DATETIME 
>then what is the command to bind this column.
> Thanks,
> JP
> 
> 
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Thursday, December 13, 2007 2:48:26 PM
> Subject: Re: [sqlite] create table with datatype = DATE
> 
> CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)
> 
> On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
>> Hi All,
>> I create the table as :
>>create table test (name varchar(30), createDate DATE default 
>> DATETIME('NOW'));
>> but I got the error message. I want to have the default as now if it is not 
>> specify.
>> Thanks in advance,
>> Joanne
>>
>>
>>  
>> 
>> Never miss a thing.  Make Yahoo your home page.
>> http://www.yahoo.com/r/hs
> 
> 


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


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 


Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread John Stanton
If you declared your date and time (timestamp) column DATETIME it will 
be floating point and will store date and time in 8 bytes.  Use the FP 
bind function.  If you make it a REAL instead of DATETIME your code will 
be clearer.


Joanne Pham wrote:

Hi All,
I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.


2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if the column is DATETIME 
then what is the command to bind this column.

Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:

Hi All,
I create the table as :
   create table test (name varchar(30), createDate DATE default 
DATETIME('NOW'));
but I got the error message. I want to have the default as now if it is not 
specify.
Thanks in advance,
Joanne


 

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs






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



RE: [sqlite] Improving performance of SQLite. Anyone heard of Devic eSQL?

2007-12-17 Thread Lynn Fredricks
> Keeping the discussion academic, "hype a product..." is a 
> business model that apparently has been used to at least some 
> degree by a company called Microsoft.  It tends to work 
> because the model permits them such an early lead that even 
> better products have difficulty catching up.
> 
> I do most of my programming in Delphi, a Borland product 
> which remains in my opinion, even in its shadow of former 
> glory state, a far more straightforward and powerful product 
> than Visual Studio.  Borland has always been a technical 
> company, not a market driven one and its flagship product is 
> surviving only because it remains a more well rounded Windows 
> solution than its competition.  However, it is only surviving 
> and is unlikely to actually thrive ever again.

There's room for everyone - just how much room, that's the question (then
mergers and acquisitions).

Penetration isnt always the same based on territory. Delphi enjoyed a
disproportionate amount of influence in Europe, for example. Lack of good
2-byte support plus other market factors also made Asian market penetration
a bit different. Big, well financed companies with a strong lead in North
America can play catch up later if its in their interest.

And we are talking Windows only development here anyway. MS doesn't have
much of a lock on the Linux, Mac OS or Unix markets. While I wouldn't want
to depend 100% on any one of these other markets, being there can make a
significant difference.

Best regards,

Lynn Fredricks
President
Paradigma Software
http://www.paradigmasoft.com

Valentina SQL Server: The Ultra-fast, Royalty Free Database Server 


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



Re: [sqlite] create table with datatype = DATE. SHould use as DATETIME or INTEGER.

2007-12-17 Thread Joanne Pham
Hi All,
I have two question regarding DATETIME column data type:
1 ) Should I store my COLUMN as INTEGER instead of DATETIME. Is it easier 
if this column type is INTEGER vs DATETIME then 
   do the conversion in the GUI code to convert from INTEGER TO DATETIME.

2)  And if I store as DATETIME then What is the command to bind this column 
type as DATETIME.
 For the INTEGER the bind command is : sqlite3_bind_int but I don't know if 
the column is DATETIME 
then what is the command to bind this column.
Thanks,
JP


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 2:48:26 PM
Subject: Re: [sqlite] create table with datatype = DATE

CREATE TABLE test (.. createData DATETIME DEFAULT CURRENT_TIMESTAMP)

On 12/13/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi All,
> I create the table as :
>create table test (name varchar(30), createDate DATE default 
> DATETIME('NOW'));
> but I got the error message. I want to have the default as now if it is not 
> specify.
> Thanks in advance,
> Joanne
>
>
>  
> 
> Never miss a thing.  Make Yahoo your home page.
> http://www.yahoo.com/r/hs


-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

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


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs

Re: Re[2]: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?

2007-12-17 Thread Ken
Ion, 

I second your comments. 

Personally, I think Encirq is wasting everyones time cluttering the sqlite 
mailing list with propaganda. Not an ideal way to gain trust or customers. I 
preffer the open and honest style of DRH. I also think its rude that they do 
this on a sqlite mailing list.

Just because deviceSql uses a precompiler, doesn't mean its more reliable, 
faster or any of the other claims. Whereas with sqlite, it is easily tested and 
provable that sqlite meets its claims.

Steve/Encirq, publish your api and make your product openly available for 
testing and evaluation. Then you might be taken more seriously.  

As my father was fond of saying; "Money talks and BS walks."  

Regards,
Ken


Ion Silvestru <[EMAIL PROTECTED]> wrote: >SW: Richard,  We have written to you 
directly before to ask you to stop the
>FUD and incorrect statements, and you have chosen to continue. I suggest you
>not waste everyone's time by circulating deliberately misleading
>information.
I think you are very aggressive and I think you must apologise to, not
only Richard, but to us (just see previous messages about DeviceSQL,
full of suppositions).

These were no "FUD and incorrect statements", nor "misleading
information", these were only suppositions, and this is because it's
hard to find real technical information or specifications on DeviceSQL, only
marketing information. Maybe DeviceSQL is a good product, but absence
of real info and abundance of marketing make us think and suppose
various things (just see previous messages).

All of us are waiting for what Richard stated:
"If you view their web presentation and/or try out Encirq's
products, I would be very interested to hear your impressions.
Even better would be if you could blog about it."

Even better if all of us can have access to this web presentation, to
find out maybe more technical info about DeviceSQL.

Any way, thank you.


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




[sqlite] Multiple connections and triggers

2007-12-17 Thread Prakash Reddy Bande
Hi,

 

I am in a situation where-in there are multiple connections to a single
sqlite database. When one connection updates the database other
connections would like to get the information about the change
(asynchronously, for example when user wants he will use some refresh
like command of the application). I tried it by doing the following.

1. Create a table

create table updatelog (cid text)

2. The I create the trigger:

create trigger updateaddresstrigger update on address

begin

insert into updatelog values (NEW.cid);

end;

The table address has a column cid.

On refresh I query the updatelog to get the cid of all changed
addresses.

 

The problem here is when do I delete the data from updatelog, since if I
don't do so then at every refresh, I will be updating the cid address
which is already updated. What I want to make sure that a cid entry is
removed only for the connection which has refreshed but is still
available for connections that have not yet refreshed.

 

I basically want to create trigger, and then associate the action from
the application which connects to the database. Something like in my
application I would do:

 

sqlite3_open();

And run a command like this one

create temp table updatalog (cid text)

attach trigger updateaddresstrigger update on address (not a valid
command but I just want to express what kind of solution I am looking
for)

begin

insert into updatelog values (NEW.cid);

end;

 

The on refresh I would like to `

delete from updatelog

 

Doing this, I can have every connection have its own updatelog table 

 

Prakash Reddy Bande

Altair Engg. Inc,

Troy, MI

 



Re: [sqlite] Heap Memory usage in Sqlite

2007-12-17 Thread Kees Nuyt
On Mon, 17 Dec 2007 17:34:26 +0530, "Sreedhar.a"
<[EMAIL PROTECTED]> wrote:

>Hi,
> 
>I am working with the database of 40k records.
>My database table contains the metadata information of audio files.
> 
>When I searched for the first 50 records, the heap usage is small.
>when I searched for the last 50 records, the heap usage is almost equal to
>searching the entire database table.
> 
>Is it the same way the sqlite behaves? or I am doing anything wrong.

Apparently the page cache is growing in memory.
It all depends on the schema and the select statement how SQLite
behaves. Apart from the architecture and optimization pages on
the SQLite website, you may want to study

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

>Thanks & Best Regards,
>A.Sreedhar.
 
-- 
  (  Kees Nuyt
  )
c[_]

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



Re: [sqlite] Running multiple SQLite processes

2007-12-17 Thread kenmor

Hi Everyone,

The problem was a mix of processes running SQLite 3.5.3 and 3.3.12. When all
processes ran 3.5.3 the problem disappeared and we got a consistent high
performance (<4ms per select).

Regards

Ken


kenmor wrote:
> 
> 
> Hi Everyone,
> 
> We are seeing the following strange behaviour with SQLite.
> 
> Our environment is as follows
> 
> 
> 1. SQLite 3.5.3
> 2. Using MEMORY DB
> 3. Many databases
> 4. Sun Solaris machine T2000 (32 cores and 16G memory)
> 5. A single DB table with only 2 entries (so size is not an issue here!)
> 6. A production - NOT a debug version of the library.
> 
> 
> TEST 1
> 
> A single process. We continually hammer the database with the same query.
> We find a row entry in an average of 3ms.
> The max response time is approx. 4ms to 5ms.
> 
> TEST 2
> 
> We run 8 processes (each has its own DB). 
> The average response time on ALL of the databases increases to 5ms
> However the max reponse time has increased to 30ms to 40ms.
> 
> 
> It appears that some resource is being shared between the processes.
> 
> Can anyone advise us on what resources could be shared and how the problem
> could be avoided.
> 
> Any help would be gratefully appreciated
> 
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Running-multiple-SQLite-processes-tp14320370p14370260.html
Sent from the SQLite mailing list archive at Nabble.com.


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



Re: [sqlite] Skype client using SQLite?

2007-12-17 Thread Kees Nuyt
On Tue, 28 Aug 2007 15:13:47 +, [EMAIL PROTECTED] wrote:

>In reference to
>
>   http://www.sqlite.org/cvstrac/tktview?tn=2592
>
>This is the first public indication we have had that
>Skype is using SQLite in their windows clients.  However,
>the person who wrote the ticket seems to be a bit confused.
>Can any able hackers in the SQLite community confirm that
>the Skype windows client is using SQLite?  It would be
>nice to add them to the page of high-profile users.

I don't know if they qualify for
http://www.sqlite.org/famous.html ,
but Comodo Firewall Pro v3.0 (an internet software firewall for
MS Windows) uses SQLite in the logviewer.

The schema is quite simple:
CREATE TABLE FWLog (
ProcessName VARCHAR(255),
Action  INT,
Pid INT,
LogDate DOUBLE,
SourceAdd   DOUBLE,
DestinationAdd  DOUBLE,
ProtocolINT,
Direction   INT,
SourcePort  INT,
DestinationPort INT
);
CREATE TABLE HipsLog (
FlagINT,
LogDate DOUBLE,
Parent  VARCHAR(255),
Target  VARCHAR(255)
);

http://www.personalfirewall.comodo.com/
-- 
  (  Kees Nuyt
  )
c[_]

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



[sqlite] Heap Memory usage in Sqlite

2007-12-17 Thread Sreedhar.a
Hi,
 
I am working with the database of 40k records.
My database table contains the metadata information of audio files.
 
When I searched for the first 50 records, the heap usage is small.
when I searched for the last 50 records, the heap usage is almost equal to
searching the entire database table.
 
Is it the same way the sqlite behaves? or I am doing anything wrong.
 
Thanks & Best Regards,
A.Sreedhar.