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

[Lynn Fredricks]

> This scenario has played out multiple times.  
> 
> Say what you want about DeviceSQL the product, but one thing 
> is undeniable: their sales presentations are top-notch.  The 
> first remark of yours that I quoted above shows this to be the case.

Yes - but a company that sells technical products has to also do that, too.

> But impressive sales talks do not necessarily translate into 
> impressive products.  In fact, a management-oriented sales 
> presentation, such as provided by Encirq, can be a put-off 
> for technical people.  The engineers and programmers I 
> normally deal with are much more attracted to the droll, 
> just-the-facts type of product that they see and get with 
> SQLite.  Flashly sales talks that are low in technical 
> detail, such as those offered in the past by Encirq (I 
> haven't seen the "webinar") tend to frighten many technical people.

That's true. A lot of those kinds of sales presentations are correctly
targeted at decision makers that make financial decisions. I don't consider
it a bad thing - it's really a necessity to be competitive. The bear in the
woods isnt evil, he's just hungry like the other bears :-)

> When engineers contacted me with help in defending SQLite, it 
> was not because they didn't understand SQLite.  It was 
> because they recognized that their management did not 
> understand SQLite, and that they had no hope of communicating 
> as effectively as the Encirq sales team, and that they were 
> desparate for any kind of help they could get.  Sadly, they 
> got little help from me since I, like they, am hopelessly 
> outclassed by the Encirq sales people when it comes to giving 
> impressive talks.  On no occasion have I told the engineers 
> anything they didn't already know, though I might have helped 
> them to organize their thoughts a little.

I think what you are seeing is evolution of the software industry. It really
isnt necessary for there to be such an extreme split between engineering and
management  - and by evolution I mean that engineering has to adapt to a
tighter relationship with management, or they are destined to have their
roles outsourced. Noone should know the product than its own engineers, and
its those who can bridge that divide that will be running the engineering
and IT departments.

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] DeviceSQL

[Nicolas Williams]

On Fri, Dec 14, 2007 at 04:28:07PM -0600, John Stanton wrote:
> Samuel R. Neff wrote:
> >I'm not sure I see added value in incorporating procedural SQL in an
> >embedded database like SQLite or DeviceSQL.  Isn't the easily extensible
> >mechanism that SQLite has for creating custom functions in the host 
> >language [...]
> 
> The difference is if you are pursuing the DeviceSQL approach and 
> compiling a library of data manipulation functions.  The partitioning is 
> cleaner if procedural logic can be included in those functions.
> 
> In our Sqlite applications we have implemented Javascript as a DB 
> procedural language by adding an Sqlite class.  You can store it in the 
> DB and execute it.  You can also run Javascript scripts which manipulate 
> an Sqlite DB.  When using Sqlite we see the value of a DB procedural 
> language in the dynamic storage of rules rather than having rules 
> chiselled in stone in the application code.  Multiple rules in 
> Javascript can be integrated and compiled into one executable procedure, 
> giving great flexibility in building systems in the "expert" style.
> 
> We chose Javascript for practical reasons.  It is universally used in 
> WWW pages so the developers do not have any new language to learn.

SQL is very expressive, and procedural SQL is too.  Much more so that C.
So if you're writing an embedded app in C then procedural SQL might come
in handy (also, the VDBE code will probably be smaller than the
corresponding object code).

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

Re: [sqlite] DeviceSQL

[John Stanton]

Samuel R. Neff wrote:

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 14, 2007 3:55 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL

... is it not 
directly comparable to DeviceSQL unless the external compiler handles 
not only SQL but also PL/SQL.  The addition of the command language 
allows for creating a library of data manipulation functions rather than 
just embedded SQL.  ...




John,

I'm not sure I see added value in incorporating procedural SQL in an
embedded database like SQLite or DeviceSQL.  Isn't the easily extensible
mechanism that SQLite has for creating custom functions in the host language
(C, C#, ActionScript, whatever) a fully valid replacement for whatever
procedure language could be provided through VDBE?  Also doesn't the fact
that SQLite (and any embedded db) runs in-process negate the importance of
SQL procedural code?  In my experience, stored procedures are usually used
to offload more work to the DB server and thus perform DB intensive code
closer to where the DB data is, which is not necessary when the DB is
already in-process?  


After moving from MSSQL to SQLite for our application, which previously used
stored procedures, we've never missed the functionality of TSQL and found
creating UDFs in C# for SQLite much easier and more powerful that using TSQL
UDFs, especially 'cause they run in-process and thus have full access to the
host application's objects and code.

Sam

The difference is if you are pursuing the DeviceSQL approach and 
compiling a library of data manipulation functions.  The partitioning is 
cleaner if procedural logic can be included in those functions.


In our Sqlite applications we have implemented Javascript as a DB 
procedural language by adding an Sqlite class.  You can store it in the 
DB and execute it.  You can also run Javascript scripts which manipulate 
an Sqlite DB.  When using Sqlite we see the value of a DB procedural 
language in the dynamic storage of rules rather than having rules 
chiselled in stone in the application code.  Multiple rules in 
Javascript can be integrated and compiled into one executable procedure, 
giving great flexibility in building systems in the "expert" style.


We chose Javascript for practical reasons.  It is universally used in 
WWW pages so the developers do not have any new language to learn.


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 



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




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

Re: [sqlite] Re: [Linux + PHP] Recommended way to access SQLite?

[Scott Baker]

Kees Nuyt wrote:
>> So the options are:
>> 1. use the old SQLite2 sqlite_() functions (or some class that turns this 
>> into OO)
>> 2. PDO to use the SQLite3 linked-in library
>> 3. PDO to access the SQLite3 DLL
>>
>> ... with 2 being the recommended choice.
> 
> yes,
> - 2 for speed and reliability,
> - 3 if one needs the latest SQLite

I highly recommend PDO for any and all PHP database access that
needs doing. It's very full featured, fast, and easy to work with.
It's not worth learning the proprietary commands for PHP has for
each DBMS.

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

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

Re: [sqlite] Re: [Linux + PHP] Recommended way to access SQLite?

[Kees Nuyt]

On Fri, 14 Dec 2007 03:15:17 +0100, Gilles Ganault
<[EMAIL PROTECTED]> wrote:

>On Mon, 10 Dec 2007 16:35:48 +0100, Kees Nuyt  <[EMAIL PROTECTED]> wrote:
> >So I activated php_pdo_sqlite_external instead and copied
> >sqlite3.dll v3.5.3 to %serverroot%/bin .
>
>Thanks for the infos.
>
>So the options are:
>1. use the old SQLite2 sqlite_() functions (or some class that turns this 
>into OO)
>2. PDO to use the SQLite3 linked-in library
>3. PDO to access the SQLite3 DLL
>
>... with 2 being the recommended choice.

yes,
- 2 for speed and reliability,
- 3 if one needs the latest SQLite
-- 
  (  Kees Nuyt
  )
c[_]

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

RE: [sqlite] DeviceSQL

[Samuel R. Neff]

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 14, 2007 3:55 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL

... is it not 
directly comparable to DeviceSQL unless the external compiler handles 
not only SQL but also PL/SQL.  The addition of the command language 
allows for creating a library of data manipulation functions rather than 
just embedded SQL.  ...



John,

I'm not sure I see added value in incorporating procedural SQL in an
embedded database like SQLite or DeviceSQL.  Isn't the easily extensible
mechanism that SQLite has for creating custom functions in the host language
(C, C#, ActionScript, whatever) a fully valid replacement for whatever
procedure language could be provided through VDBE?  Also doesn't the fact
that SQLite (and any embedded db) runs in-process negate the importance of
SQL procedural code?  In my experience, stored procedures are usually used
to offload more work to the DB server and thus perform DB intensive code
closer to where the DB data is, which is not necessary when the DB is
already in-process?  

After moving from MSSQL to SQLite for our application, which previously used
stored procedures, we've never missed the functionality of TSQL and found
creating UDFs in C# for SQLite much easier and more powerful that using TSQL
UDFs, especially 'cause they run in-process and thus have full access to the
host application's objects and code.

Sam


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 


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

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

[John Stanton]

If you make your date and time columns numeric (floating point) you use 
8 bytes to store a date and time.  You then use the embedded Sqlite date 
and time functions to present date and time as your application chooses.


I believe that if you declare the column as DATE Sqlite will give it an 
underlying type of numeric.  Look at Sqlite's "manifest typing" to see 
how it ignores the SQL data types and stores information in the way it 
chooses as most appropriate.


Look at date.c to get an idea of the inbuilt Sqlite date and time support.

Joanne Pham wrote:

Hi All,
Should I create the column in DATETIME or the INTEGER to store the time.
DATETIME has the value of GMT time. So I store this value as INTEGER then I 
need to convert datetime format but it will be use less space if I use the 
INTEGER. Please give me an advice.
Thanks
Joanne



- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 3:05:52 PM
Subject: Re: [sqlite] create table with datatype = DATE

The type DATE is a declared type, not an actual type and has no effect u 
nless your code specifically picks it out as a declared type.


To do what you want use a trigger on insert and update the date field 
with datetime('now');


Joanne Pham 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]
-

Re: [sqlite] DeviceSQL

[John Stanton]

We did that with our products which used byte code.  Byte code compiled 
on earlier versions would run on later ones, but new code with the extra 
opcodes would not run on old interpreters.  It protected customers who 
had lost their source code or were afraid to recompile after an upgrade 
because of version control blunders.


Noah Hart wrote:

Why can't new OP codes be simply added to the end, so that the
serializations will always work?

Noah 


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 14, 2007 8:20 AM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL
Importance: High

Case in point:  A new opcode has been added for the pending 3.5.4
release.  And this caused many of the other opcodes to be
automatically renumbered.  A serialization created for 3.5.3
has little hope of running on 3.5.4.

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




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.





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




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

Re: [sqlite] DeviceSQL

[John Stanton]

Dennis, your application may not be a microwave oven but it does appear 
to be "deeply embedded" as opposed to a general purpose DB server which 
handles generic queries.


I was aware of the runtime only version of Sqlite, but is it not 
directly comparable to DeviceSQL unless the external compiler handles 
not only SQL but also PL/SQL.  The addition of the command language 
allows for creating a library of data manipulation functions rather than 
just embedded SQL.  My point was that the DeviceSQL approach could be 
used with Sqlite to produce a smaller footprint embedded product and 
that such a product has the potential of being less memory hungry than 
DeviceSQL simply by virtue of the higher information density of the VDBE 
target code compared to native machine instructions.


Dennis Cote wrote:

John Stanton wrote:


DeviceSQL is not suitable for general purpose SQL processing, unlike 
Sqlite, and should only be compared as an alternative in deeply 
embedded applications so the only useful comparison is one which looks 
like a cell phone, microwave oven or a TV set top box.




The application doesn't necessarily have to look like a microwave. :-)

It could be any application that uses a fixed set of predetermined SQL 
statements to perform its operations. My primary application does 
exactly that using SQLite with all statements prepared and cached as 
needed. It runs on a standard PC under Windows. We have no need for 
executing arbitrary, runtime generated, or user supplied SQL as many 
other applications do. Every SQL statement that can be executed is known 
before the application starts.


I can imagine that a version of Sqlite which does not include its SQL 
compiler and which uses precompiled VDBE code would provide similar 
functionality to DeviceSQL, particularly if the Sqlite compiler were 
extended to generate VDBE from PL/SQl.  I can imagine that the higher 
information density of the VDBE code could deliver the advantage =of a 
smaller memory footprint.


I thought that this already existed as a commercial product that Richard 
offered, but I can't find any reference to it on the paid support page 
at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was 
an offline compiler that ran on a PC and generated VDBE code to execute 
SQL statements, along with source code for a runtime execution engine 
that would execute those pre-compiled statements. The execution engine 
could be built for any target, and was much smaller than SQLite because 
it eliminated the parser and code generator functionality. Does this 
exist, or was I just imagining it?


Dennis Cote

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






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

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

[drh]

"Lynn Fredricks" <[EMAIL PROTECTED]> wrote:
> 
> I don't know anything about DeviceSQL but their
> presentation is enough to get my respect :-)
> 
[...]
> 
> It seems to me that if the engineers are coming to you to defend their
> selection of SQLite, then they didnt know SQLite as well as they should

This scenario has played out multiple times.  

Say what you want about DeviceSQL the product, but
one thing is undeniable: their sales presentations
are top-notch.  The first remark of yours that I
quoted above shows this to be the case.

But impressive sales talks do not necessarily translate
into impressive products.  In fact, a management-oriented
sales presentation, such as provided by Encirq, can be
a put-off for technical people.  The engineers and 
programmers I normally deal with are much more
attracted to the droll, just-the-facts type of product
that they see and get with SQLite.  Flashly sales
talks that are low in technical detail, such as
those offered in the past by Encirq (I haven't seen
the "webinar") tend to frighten many technical people.

When engineers contacted me with help in defending
SQLite, it was not because they didn't understand
SQLite.  It was because they recognized that their
management did not understand SQLite, and that they
had no hope of communicating as effectively as the
Encirq sales team, and that they were desparate for
any kind of help they could get.  Sadly, they got
little help from me since I, like they, am hopelessly
outclassed by the Encirq sales people when it comes
to giving impressive talks.  On no occasion have I
told the engineers anything they didn't already know,
though I might have helped them to organize their
thoughts a little.

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


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

Re: [sqlite] DeviceSQL

[Steven Fisher]

On 14-Dec-2007, at 5:41 AM, Clay Dowling wrote:


I have to say, this discussion has been very informative, although
probably not in a way that would make mr Weick happy.  I've certainly
learned a lot about encirq that tells me what I need to know about  
doing

business with them.


Same thought here. Kudos to D. Richard Hipp for keeping his cool under  
provocation.


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

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

[Lynn Fredricks]

> > August? We start to discuss about DeviceSQL some days ago, or I am 
> > wrong?
> > 
> 
> I have several support customer in Europe who have been 
> visited by the Encirq sales rep there, trying to get them to 
> abandon SQLite in favor of DeviceSQL.  The way this normally 
> happens is that a sales talk is given to the management.  
> Then the management goes to their engineers asking for a 
> comparison of DeviceSQL and SQLite.  The engineers then come 
> to me for help in defending SQLite.
> I respond with a letter outlining the strengths and 
> weaknesses of each product as known to me.  I am always very 
> careful to outline the limitations of my knowledge in these 
> cases and to attempt to give as fair and as balanced of a 
> comparison as I can.
> 
> In one recent episode (prehaps the one that Steve is 
> referring to) my reply was forwarded to the Encirq sales rep. 
>  This provoked a vigorous response from Encirq in which they 
> attempted a point-by-point rebuttal of my letter.

While Im not in the habit of defending the competition, Id like to toss my
2-cents in on this. I don't know anything about DeviceSQL but their
presentation is enough to get my respect :-)

The database market is very mature and if you do not have a set of special
features (in the actual engineering of the product, deployment or in its
licensing) that is compeling to a certain customer segment, you are dead
meat. Understanding those compeling reasons is one part engineering and one
part management. Engineering should understand technical
limitations/advantages and needs to be able to convey them convincingly to
management to the best of their understanding of product strategy. Likewise
management also makes decisions not always based on engineers understanding
or lack of understanding of the direction of the business (let along execs
jockeying against each other ;-)). And no matter how you couch or caveat a
statement, one isnt always present to know that those caveats are also
passed along  -  you may get little difference out the other end between
"God told me..." and "I witnessed it myself."

It seems to me that if the engineers are coming to you to defend their
selection of SQLite, then they didnt know SQLite as well as they should
because - it seems they havent made a very informed choice for using SQLite
(or any db) to begin with. The informed one might not be with the company
any more. But if a sales guy from DeviceSQL can pinpoint the needs of an
organization better than its own engineers, then its even worse (or better
if you are the DeviceSQL sales rep!).

Are you sure your customer is in Europe and not the US federal government?
:-)

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] Default Encoding In Sqlite

[Trevor Talbot]

I wrote:

> The default storage encoding on disk is UTF-8, but it can be changed
> to UTF-16 with a PRAGMA.

As Igor reminds me, if you create the database file using
sqlite3_open16() the default will be UTF-16 instead. You can still set
it explicitly via a PRAGMA if you wish.

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

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

[Dennis Cote]

Joanne Pham wrote:

Hi All,
Should I create the column in DATETIME or the INTEGER to store the time.
DATETIME has the value of GMT time. So I store this value as INTEGER then I 
need to convert datetime format but it will be use less space if I use the 
INTEGER. Please give me an advice.
  


See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions where it 
says "The julian day number is the preferred internal representation of 
dates."


A Julian day number is a floating point number that can be easily 
compared and converted to data and time strings for display.


HTH
Dennis Cote

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

Re: [sqlite] Default Encoding In Sqlite

[Trevor Talbot]

On 12/14/07, Kalyani Phadke <[EMAIL PROTECTED]> wrote:

> Whats default encoding in SQLite3 ? How does SQLite3 handles storing
> Japanese/Chinese text in database?  I know in SQL express/sql server  I
> have to use nVarchar/nchar/ntext datatypes to store Japanese/Chinese
> text in database. It seems that in SQLite3 column having text datatype
> can also store chinese characters..

SQLite assumes TEXT data is Unicode. You can work with it in either
UTF-8 by using the *_text() APIs, or UTF-16 using the *_text16()
calls. It will convert between the two encodings as necessary. The
default storage encoding on disk is UTF-8, but it can be changed to
UTF-16 with a PRAGMA.

Note that I said it "assumes" the data is in that form. SQLite does
not validate the encoding, so it is possible to store text data in
some other encoding, like SJIS. You will just get strange results when
asking SQLite to convert the data, such as when storing it with
*_text(), but retrieving it with *_text16().

The sqlite3 shell is intended to work with UTF-8, but because of the
way different platforms handle the terminal/console encodings, it can
be difficult to use properly.

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

[sqlite] Re: Default Encoding In Sqlite

[Igor Tandetnik]

Kalyani Phadke
 wrote:

Whats default encoding in SQLite3 ?


Either UTF-8 or UTF-16, depending on whether the database file was 
originally created with sqlite3_open or sqlite3_open16.



How does SQLite3 handles storing
Japanese/Chinese text in database?


With aplomb and flair.

Igor Tandetnik 



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

Re: [sqlite] DeviceSQL

[Dennis Cote]

[EMAIL PROTECTED] wrote:

Dennis Cote <[EMAIL PROTECTED]> wrote:
  
It could be any application that uses a fixed set of predetermined SQL 
statements to perform its operations. My primary application does 
exactly that using SQLite with all statements prepared and cached as 
needed. It runs on a standard PC under Windows. We have no need for 
executing arbitrary, runtime generated, or user supplied SQL as many 
other applications do. Every SQL statement that can be executed is known 
before the application starts.





Your application uses a fixed set of SQL statements now.  But
that set of statements might change in the next release. Or you
might change or modify a table, or add an index.  


A key feature SQLite is that these changes preserve the file
format.  With other systems, when you change the schema the
file format changes with it.
  


Yes, of course. We can also generally use SQL to "upgrade" existing 
database files to the new schema quite easily using SQLite.


One of the ideas I try to push is SQLite as an Application File 
Format.  The idea is that you put your information in an SQLite 
database file and it is then readable by diverse, general-purpose 
tools, and across multiple releases.  Sure, the schema might

change from one release to the next, but the data is still
easily accessible.

  


The biggest problem I see with this concept right now is that if you use 
any custom functions or collations you really can't work with your 
database using diverse, general purpose tools. If you have triggers or 
views that call these custom functions then you have difficulties 
accessing the data from any other application, especially third party 
database browsers etc.


That is the principal reason I think it would be very useful to extend 
the base set of functions and collations that are universally available 
to all applications using SQLite to access a database file. Things like 
graphical browser applications, where code size really isn't an issue, 
would always include the full set of functions. Users on space 
restricted embedded systems could easily eliminate the functions they 
aren't using to minimize the code space for their targets. The size of 
the SQLite shared library or DLL is normally not a concern on 
workstations with ram measured in GB and disks that are on the order of TB.


Custom functions are a great idea, but it would be better to have a 
wider selection of standard functions so that users only needed to 
resort to custom functions in rare cases (or at least less often).


In order to promote this idea, we are very careful to make sure 
that the SQLite file format does not change in ways that are 
not backwards compatible.  Our goal is to make sure that the

information you put into an SQLite database today is still
easily accessible using general-purpose tools after 20 or 30 
years.


Further to this goal, watch for detailed specficiations of the
SQLite file format to appear in 2008.
  


I'm glad to hear that, and look forward to seeing it.

Dennis Cote


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

[sqlite] Default Encoding In Sqlite

[Kalyani Phadke]

 
Whats default encoding in SQLite3 ? How does SQLite3 handles storing
Japanese/Chinese text in database?  I know in SQL express/sql server  I
have to use nVarchar/nchar/ntext datatypes to store Japanese/Chinese
text in database. It seems that in SQLite3 column having text datatype
can also store chinese characters..

Thanks,
-Kalyani

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

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

[Joanne Pham]

Hi All,
Should I create the column in DATETIME or the INTEGER to store the time.
DATETIME has the value of GMT time. So I store this value as INTEGER then I 
need to convert datetime format but it will be use less space if I use the 
INTEGER. Please give me an advice.
Thanks
Joanne



- Original Message 
From: John Stanton <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 13, 2007 3:05:52 PM
Subject: Re: [sqlite] create table with datatype = DATE

The type DATE is a declared type, not an actual type and has no effect u 
nless your code specifically picks it out as a declared type.

To do what you want use a trigger on insert and update the date field 
with datetime('now');

Joanne Pham 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] DeviceSQL

[drh]

"Noah Hart" <[EMAIL PROTECTED]> wrote:
> Why can't new OP codes be simply added to the end, so that the
> serializations will always work?
> 

We do not often add new opcodes.  The usual change is to
alter the meaning and operation of existing opcodes.  We
also delete existing opcodes about as often as we add new
ones.

Furthermore, the opcode numbers are not chosen haphazardly.  
The opcode numbers are in many cases carefully aligned with
token numbers in the parser in order to make the code
generate smaller.  And opcodes that are often used together
within switch statements are grouped together numerically
so that the jump tables used to implement the switch()
statements are smaller - resulting in a smaller executable.

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


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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Zbigniew Baniewski]

3.5.4
-- 
pozdrawiam / regards

Zbigniew Baniewski

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

Re: [sqlite] DeviceSQL

[Nicolas Williams]

On Fri, Dec 14, 2007 at 04:20:15PM +, [EMAIL PROTECTED] wrote:
> The EXPLAIN option generates human-readable assembly.  We
> use EXPLAIN extensively when debugging and enhancing.

Yes, but there's no assembler for EXPLAIN output, is there?

Also, the only way to deal with EXPLAIN is to step through its results
-- there's no way to directly insert its output rows into a temporary
table and then use a select using group_concat() to generate a single
string consisting of the VDBE assembly.  It is, however, possible to use
.mode insert and explain to generate INSERT statements that can then be
used to insert the VDBE program into a table, and then one can do more
interesting things (I suppose one could even write an assembler in SQL).

> I should point out that while we work very hard to keep the
> file format for SQLite stable, we make no such effort with
> the VDBE.  The opcodes can and do change significantly between
> point releases.  Hence the serializations generated by one
> version of SQLite can only be interpreted by that exact same
> version of SQLite.

Right.

Nico
-- 

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

RE: [sqlite] DeviceSQL

[Noah Hart]

Why can't new OP codes be simply added to the end, so that the
serializations will always work?

Noah 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 14, 2007 8:20 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DeviceSQL
Importance: High

Case in point:  A new opcode has been added for the pending 3.5.4
release.  And this caused many of the other opcodes to be
automatically renumbered.  A serialization created for 3.5.3
has little hope of running on 3.5.4.

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




CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.




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

Re: [sqlite] DeviceSQL

[drh]

Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote:
> > That would be the Serialized Statement Extension, SSE.
> > The SSE provides the programmer with two new APIs:
> 
> Would it be useful to generate human-readable VDBE "assemply"?
> 
> Or, how do you develop parser changes that involve new VDBE code
> patterns?
> 

The EXPLAIN option generates human-readable assembly.  We
use EXPLAIN extensively when debugging and enhancing.

I should point out that while we work very hard to keep the
file format for SQLite stable, we make no such effort with
the VDBE.  The opcodes can and do change significantly between
point releases.  Hence the serializations generated by one
version of SQLite can only be interpreted by that exact same
version of SQLite.

Case in point:  A new opcode has been added for the pending 3.5.4
release.  And this caused many of the other opcodes to be
automatically renumbered.  A serialization created for 3.5.3
has little hope of running on 3.5.4.

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


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

Re: [sqlite] DeviceSQL

[Nicolas Williams]

On Fri, Dec 14, 2007 at 10:10:04AM -0600, Nicolas Williams wrote:
> On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote:
> > That would be the Serialized Statement Extension, SSE.
> > The SSE provides the programmer with two new APIs:
> 
> Would it be useful to generate human-readable VDBE "assemply"?

I meant "assembly".

> Or, how do you develop parser changes that involve new VDBE code
> patterns?

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

Re: [sqlite] DeviceSQL

[Nicolas Williams]

On Fri, Dec 14, 2007 at 03:38:17PM +, [EMAIL PROTECTED] wrote:
> That would be the Serialized Statement Extension, SSE.
> The SSE provides the programmer with two new APIs:

Would it be useful to generate human-readable VDBE "assemply"?

Or, how do you develop parser changes that involve new VDBE code
patterns?

Just curious,

Nico
-- 

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

Re: [sqlite] DeviceSQL

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> 
> It could be any application that uses a fixed set of predetermined SQL 
> statements to perform its operations. My primary application does 
> exactly that using SQLite with all statements prepared and cached as 
> needed. It runs on a standard PC under Windows. We have no need for 
> executing arbitrary, runtime generated, or user supplied SQL as many 
> other applications do. Every SQL statement that can be executed is known 
> before the application starts.
> 

Your application uses a fixed set of SQL statements now.  But
that set of statements might change in the next release. Or you
might change or modify a table, or add an index.  

A key feature SQLite is that these changes preserve the file
format.  With other systems, when you change the schema the
file format changes with it.

One of the ideas I try to push is SQLite as an Application File 
Format.  The idea is that you put your information in an SQLite 
database file and it is then readable by diverse, general-purpose 
tools, and across multiple releases.  Sure, the schema might
change from one release to the next, but the data is still
easily accessible.

In order to promote this idea, we are very careful to make sure 
that the SQLite file format does not change in ways that are 
not backwards compatible.  Our goal is to make sure that the
information you put into an SQLite database today is still
easily accessible using general-purpose tools after 20 or 30 
years.

Further to this goal, watch for detailed specficiations of the
SQLite file format to appear in 2008.

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


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

Re: [sqlite] DeviceSQL

[Shawn Wilsher]

On Dec 14, 2007 10:38 AM,  <[EMAIL PROTECTED]> wrote:
> That would be the Serialized Statement Extension, SSE.
> The SSE provides the programmer with two new APIs:
>
>int sqlite3_serialize(sqlite3_stmt*, void**, int*);
>int sqlite3_deserialize(sqlite3*, void*, int, sqlite3_stmt**);
>
> The first routine takes an SQL statement that was generated by
> sqlite3_prepare() and converts it into a form that can be stored
> on disk or compiled into a program.  The second routine does the
> reverse; it takes the serialization of a statement and converts it
> back into a working SQL statement that can be used just like any
> other statement created by sqlite3_prepare().
>
> You compile SQLite normally on your development workstation, but
> for you embedded target you add -DSQLITE_OMIT_PARSER to leave off
> the parser.  By omitting other optional features (date/time functions,
> views, triggers) you can get the size of the library down to the 70KiB
> range or less.
>
> On a workstation, you can sqlite3_prepare() statements, then hand
> them to sqlite3_serialize().  The results can be hard coded into
> C programs to be manually deserialized later, if you like, though
> that is a lot of work.  A simpler approach is to use the special
> sqlite_statement table:
>
>CREATE TABLE sqlite_statement(
>   id INTEGER PRIMARY KEY,
>   sql TEXT,
>   serial BLOB
>);
>
> A new API is available that will automatically extract and deserialize
> an SQL statement from the sqlite_statement table given its id number:
>
>int sqlite3_fetch_statement(sqlite3*, int id, sqlite3_stmt**);
>
> The idea here is that the SQL statements needed by an application can
> be inserted as plain text into the sqlite_statement table.   For
> example:
>
>INSERT INTO sqlite_statement(sql) VALUES('SELECT * FROM table1');
>
> After many such statements are inserted, they can all be serialized
> as follows:
>
>UPDATE sqlite_statement SET serial = sqlite_serialize(sql,id);
>
> Then the complete database can be moved from the development platform
> over to the embedded device and the embedded device can use the
> sqlite3_fetch_statement() API to extract the statements it needs to
> execute.
>
> To be useful, your precompiled statements will normally contain
> parameters (ex: "INSERT INTO tx VALUES(?,?,?)") and the embedded
> application will using sqlite3_bind_xxx() interfaces to attach
> values to these parameter prior to invoking sqlite3_step().
>
> The SSE has not been kept current with the base SQLite.  But if there
> is interest, we could resurrect it easily enough.

I think that Mozilla may find that useful.  I think it came up about a
month ago with some places code (new bookmark back-end).

Seth, I think it was you and Mano that were talking about this.  Would
this be useful for us?

Cheers,

Shawn

>
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
>
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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

Re: [sqlite] Should the next release be 3.5.4 or 3.6.0?

[Dennis Cote]

Dr Gerard Hammond wrote:

(2)  If an ORDER BY term is a simple identifer
 (like "x", not "x.y" and not "x.y.z") and if
 there if the k-th column uses that same identifer
 as an AS alias, the sort by the k-th column.

CREATE TABLE a(x,y);
 INSERT INTO a VALUES(1,8);
 INSERT INTO a VALUES(9,2);

 SELECT x AS y FROM a ORDER BY y;




I don't understand.  If I say "ORDER BY y" aren't I saying sort the 
result set based on the column " as y"  of the result set, not the 
table "a.y"?

ie they should come out

y
9
1



Your description is correct, but your output is in the wrong order. The 
default sort order is ascending so the 9 will sort after the 1.


Y
1
9

Dennis Cote

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

Re: [sqlite] DeviceSQL

[drh]

Dennis Cote <[EMAIL PROTECTED]> wrote:
> 
> > I can imagine that a version of Sqlite which does not include its SQL 
> > compiler and which uses precompiled VDBE code would provide similar 
> > functionality to DeviceSQL, particularly if the Sqlite compiler were 
> > extended to generate VDBE from PL/SQl.  I can imagine that the higher 
> > information density of the VDBE code could deliver the advantage =of a 
> > smaller memory footprint.
> 
> I thought that this already existed as a commercial product that Richard 
> offered, but I can't find any reference to it on the paid support page 
> at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was 
> an offline compiler that ran on a PC and generated VDBE code to execute 
> SQL statements, along with source code for a runtime execution engine 
> that would execute those pre-compiled statements. The execution engine 
> could be built for any target, and was much smaller than SQLite because 
> it eliminated the parser and code generator functionality. Does this 
> exist, or was I just imagining it?
> 

That would be the Serialized Statement Extension, SSE.
The SSE provides the programmer with two new APIs:

   int sqlite3_serialize(sqlite3_stmt*, void**, int*);
   int sqlite3_deserialize(sqlite3*, void*, int, sqlite3_stmt**);

The first routine takes an SQL statement that was generated by
sqlite3_prepare() and converts it into a form that can be stored
on disk or compiled into a program.  The second routine does the
reverse; it takes the serialization of a statement and converts it
back into a working SQL statement that can be used just like any
other statement created by sqlite3_prepare().

You compile SQLite normally on your development workstation, but
for you embedded target you add -DSQLITE_OMIT_PARSER to leave off
the parser.  By omitting other optional features (date/time functions,
views, triggers) you can get the size of the library down to the 70KiB
range or less.

On a workstation, you can sqlite3_prepare() statements, then hand
them to sqlite3_serialize().  The results can be hard coded into
C programs to be manually deserialized later, if you like, though
that is a lot of work.  A simpler approach is to use the special
sqlite_statement table:

   CREATE TABLE sqlite_statement(
  id INTEGER PRIMARY KEY,
  sql TEXT,
  serial BLOB
   );

A new API is available that will automatically extract and deserialize
an SQL statement from the sqlite_statement table given its id number:

   int sqlite3_fetch_statement(sqlite3*, int id, sqlite3_stmt**);

The idea here is that the SQL statements needed by an application can
be inserted as plain text into the sqlite_statement table.   For
example:

   INSERT INTO sqlite_statement(sql) VALUES('SELECT * FROM table1');

After many such statements are inserted, they can all be serialized
as follows:

   UPDATE sqlite_statement SET serial = sqlite_serialize(sql,id);

Then the complete database can be moved from the development platform
over to the embedded device and the embedded device can use the
sqlite3_fetch_statement() API to extract the statements it needs to
execute.

To be useful, your precompiled statements will normally contain
parameters (ex: "INSERT INTO tx VALUES(?,?,?)") and the embedded
application will using sqlite3_bind_xxx() interfaces to attach
values to these parameter prior to invoking sqlite3_step().

The SSE has not been kept current with the base SQLite.  But if there
is interest, we could resurrect it easily enough.

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


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

Re: [sqlite] DeviceSQL

[Dennis Cote]

John Stanton wrote:


DeviceSQL is not suitable for general purpose SQL processing, unlike 
Sqlite, and should only be compared as an alternative in deeply 
embedded applications so the only useful comparison is one which looks 
like a cell phone, microwave oven or a TV set top box.




The application doesn't necessarily have to look like a microwave. :-)

It could be any application that uses a fixed set of predetermined SQL 
statements to perform its operations. My primary application does 
exactly that using SQLite with all statements prepared and cached as 
needed. It runs on a standard PC under Windows. We have no need for 
executing arbitrary, runtime generated, or user supplied SQL as many 
other applications do. Every SQL statement that can be executed is known 
before the application starts.


I can imagine that a version of Sqlite which does not include its SQL 
compiler and which uses precompiled VDBE code would provide similar 
functionality to DeviceSQL, particularly if the Sqlite compiler were 
extended to generate VDBE from PL/SQl.  I can imagine that the higher 
information density of the VDBE code could deliver the advantage =of a 
smaller memory footprint.


I thought that this already existed as a commercial product that Richard 
offered, but I can't find any reference to it on the paid support page 
at http://www.hwaci.com/sw/sqlite/prosupport.html. My recollection was 
an offline compiler that ran on a PC and generated VDBE code to execute 
SQL statements, along with source code for a runtime execution engine 
that would execute those pre-compiled statements. The execution engine 
could be built for any target, and was much smaller than SQLite because 
it eliminated the parser and code generator functionality. Does this 
exist, or was I just imagining it?


Dennis Cote

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

Re: [sqlite] SQLite and Columnar Databases

[Michael Scharf]

This is probably do to the cache locality problem.  We know how
to fix this, Joe.  Would you like to have a go at it?


What is the fix to the "cache locality problem"?


Michael
--
http://MichaelScharf.blogspot.com/


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

RE: [sqlite] SQLite and Columnar Databases

[Tom Briggs]

 
> This model is completely removed from how the data is physically 
> stored, eg whether in rows first or in columns first, and the 
> physical store is determined just by the DBMS behind the scenes, and 
> hence is an implementation detail.  The DBMS can arrange how it likes 
> in order to satisfy the logical model in a way that performs well. 
> The users do not contort their schemas beyond what is logically clean 
> in order to gain performance; having a clean schema will let the DBMS 
> infer this automatically.

   This may be true in theory, but if it were really true in practice,
why would anyone bother building databases with different storage
models?  How a database does what it does impacts everything from where
you should use it to how you design your schema to how you write your
queries.

   And if you think that no one contorts their schemas to make their
queries run faster then, umm... Well, I don't think I need to say
anything else about that.

> According to some comments, Vertica (a column-store maker) is making 
> the same case that I am, which is just to have a logical clean 
> schema, and performance benefits will automatically follow from that.

   They say that because their system performs better when your schema
is simpler.  It's not just a philosophical belief that simpler is
better.  

> I will also note that a column-based store essentially works like a 
> heavily indexed row-based store, in which there is an index on every 
> key or every column, and so all searches, which includes those on 
> which joins are performed, can/do look in what is otherwise indexes. 

   Conceptually I agree, though I do not think you could build an
equally-effective system by indexing a row store.

> This is potentially slower for updates (or maybe not), but can be 
> faster for queries, depending on circumstances.

   But... But... How the data is stored is an implementation detail that
users don't need to worry about, right?  So who cares if updates are
slower?  The logical model is the same, right?

   Thank you for making my point. :)

   -T



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

Re: [sqlite] DeviceSQL

[Clay Dowling]

I have to say, this discussion has been very informative, although
probably not in a way that would make mr Weick happy.  I've certainly
learned a lot about encirq that tells me what I need to know about doing
business with them.

Clay

steveweick wrote:
>
> Good  idea... I'll pass it along to the right folks. Meanwhile, if anyone
> has
> further questions or comments, please feel free to write me here (if they
> think the group would be interested) or at [EMAIL PROTECTED]
>
> Steve
>
> I would like to recommend that Encriq create a forum or mailing list of
> their own for those who are interesting in learning more.  For me, what
> might be an interesting product is quickly being overshadowed by this
> thread.
>
>
> --
> View this message in context:
> http://www.nabble.com/DeviceSQL-tp14297970p14329799.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Lazarus Registration
http://www.lazarusid.com/registration.shtml


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

Re: Re[2]: [sqlite] Problems Insert with Date and Time values

[Giuliano]

Thank you again for all you work..

Giuliano

- Original Message - 
From: "Ion Silvestru" <[EMAIL PROTECTED]>

To: "Giuliano" 
Sent: Friday, December 14, 2007 10:08 AM
Subject: Re[2]: [sqlite] Problems Insert with Date and Time values



It seems that "BETWEEN" in SQLite is treated as "BETWEEN..AND selects
fields that are between and including the test values", so it can be
used instead of ">=" and "<=".



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

Re[3]: [sqlite] Problems Insert with Date and Time values

[Ion Silvestru]

>It seems that "BETWEEN" in SQLite is treated as "BETWEEN..AND selects
>fields that are between and including the test values", so it can be
>used instead of ">=" and "<=".
I am sorry, but it seems I am partially correct.
I tested again and concluded:

1. "BETWEEN" in SQLite is treated as "BETWEEN..AND selects
fields that are between and including the test values",
so it can be used instead of ">=" and "<=",
ONLY if you specify COMPLETE test values, example "20071001" AND
"20071131".

2. If you specify PARTIAL test values, example "200710" AND "200711",
then "BETWEEN" in SQLite is treated as "BETWEEN..AND selects
fields between the test values, including the first test value and
excluding the last test value".

3. It is not reliable to use "BETWEEN" for now (and not only in
SQLite, but also in other DBMS), better use >= and <=.




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

Re[2]: [sqlite] Problems Insert with Date and Time values

[Ion Silvestru]

It seems that "BETWEEN" in SQLite is treated as "BETWEEN..AND selects
fields that are between and including the test values", so it can be
used instead of ">=" and "<=".


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

Re: [sqlite] Problems Insert with Date and Time values

[Giuliano]

Thanks a lot!
any suggestion for a query then that read and compare dates, for ex.:

SELECT somefiled FROM sometable WHERE datefield1 BETWEEN ' '? AND  ' '?

How can I query then the string values as date?

Thanks again

Giuliano


- Original Message - 
From: "Ion Silvestru" <[EMAIL PROTECTED]>

To: "Giuliano" 
Sent: Friday, December 14, 2007 8:55 AM
Subject: Re: [sqlite] Problems Insert with Date and Time values


>is it possible (how?) to insert into 2 different fields (date) the 
>following

values:



09:30:00(only a time value...)
14/07/07   (only year value, 14th of december 2007)


Just store date and/or time as string: "20070714" or "070714",
"093000". It is compact, you can sort them easily etc. You can store
them also as Integer: 20070714, 93000, this will save space in
database, as integers are stored in variable-length format (VarInt).


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





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