Re: [sqlite] request for additions to sqlite 3.xx

[basil thomas]

> What's the objection to reading your SQL source out of the database and
> preparing it at program startup?

I have no objection to reading the sql on startup as that is what we are
currently doing. I just want all data access code inside of the database
instead of my source code. Does anyone else think that SQLite should have
stored procedures?? My assumption is that if you are creating views and
triggers, why not also create stored procedures aswell. Nearly every other
database that has triggers (embedded library or not) has stored procedures
aswell. Anyway, as I have pointed out before, I obviously can do without as
we are simulating stored procedures by puting them in resource files from
one large xml file that can be edited external to the source code. We do not
want out data access layer tied too much to SQLite and would prefer to have
all access code in the database itself so our data access layer can
accomadate (lesser obviously) databases on the market with little change
to the code. We can usually work with SQL Server code independant of the
application by using stored procedures as our sole access point into he
database. SQLite is definitely extremely fast and we have no plans of
abandoning due to lack of stored procedures but definitely would  be nice to
have...

RE: [sqlite] SQLite version 3 design question: '500'=500?

[basil . thomas]

Yep. basically our "type less" string fields should have user definable
operator overload functions. Sounds like a big change that I doubt DRH would
implement anytime soon but it would definitely solve some of these
integer/numeric/string/datetime/etc.. conversion/comparisons. We would also
have to agree on what would constitute a constant custom data type
declaration. 
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:41 AM
To: Thomas, Basil
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?







Not only comparison, but all expressions - arithmetic etc.



   
 [EMAIL PROTECTED] 
 com   
To 
 13/05/2004 16:33  [EMAIL PROTECTED],  
   [EMAIL PROTECTED]  
cc 
   [EMAIL PROTECTED] 
   Subject 
   RE: [sqlite] SQLite version 3   
   design question:  '500'=500?
   
   
   
   
   
   




I think the point the SQL assumes is that everything is a string unless you
tell it otherwise. How you tell it otherwise can be explicitly done or
implicitly done.
SQLite seems to be making implicit data conversions when in fact the user
may mean something else. Therefore each implicit data conversion should
also
have an explicit
data conversion to tell SQLite EXACTLY how to interpret this data type.
Most
relational systems have an enforced schema that dictates each allowable
data
type and how these
data types can be converted from one type to another. SQLite which is
basically type less, should allow us to add functions to overload the
comparison operators so that we could convert/compare explicitly for each
of
our custom data types since we know exactly what we want . This should be
optional and would probably impact performance but at least we would get
the
right answer whenever we queried for the custom data types that we have
defined.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Thursday, May 13, 2004 11:18 AM
To: Fred Williams
Cc: [EMAIL PROTECTED]
Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?







Not terribly interested in pursuing this, but that is precisely the point -
letting the engine handle the 'technical' things means it needs to know the
types of data in it. If the engine doesn't do it, the user has to do it,
adding complexity and producing less reliable systems.




 "Fred Williams"
 <[EMAIL PROTECTED]
 on.net>To
   <[EMAIL PROTECTED]>
 13/05/2004 15:12   cc
   <[EMAIL PROTECTED]>
   Subject
   RE: [sqlite] SQLite version 3
   design question:  '500'=500?










I guess you read different books than I.  The intent was to have the
language engine do the required type conversions freeing the user from
knowledge of such "technical" things.  Like all languages it evolved, and
has not remained "simple."

Fred

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> Sent: Thursday, May 13, 2004 8:51 AM
> To: Fred Williams
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] SQLite version 3 design question: '500'=500?
>
>
>
>
>
>
>
> Funny, I thought SQL was a more down to earth version of E. F. Codd's
> mathematical model 'Relational Algebra', with some concepts taken from
> Relational Calculus.
>
> To say types and strong typing weren't part of the original SQL concept
> isn't right. Typing gives data semantics.
>
> Vijay
>
...


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] vers 3.0 concurrency issues

[basil . thomas]

I really like this answer!!!
The Goldilocks approach to increased concurrency...
Hopefully DRH will read your answer and conclude this would be a good
approach
as the current take it or leave it answers are no help.
Abandoning SQLite COMPLETELY for higher concurrency does not make sense but 
a third solution for medium ground between the two current extreme views to
SQLite concurrency
would be much appreciated. 
I wish I could work on this locking solution but my hands are tied because 
SQLite currently runs quite well in our current application and therefore
many more features
have been requested...such as supporting more users!!!

-Original Message-
From: Andrew Piskorski [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 12:53 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] vers 3.0 concurrency issues


On Thu, May 06, 2004 at 09:54:24AM -0400, D. Richard Hipp wrote:

> Concurrency is not nearly as much an issue in reality
> as it is in many peoples imagination.  Concurrency
> probably is not an issue for a website. If concurrency
> really is an issue, you need a client/server database.

While that might be true [I take no position on whether it is or not],
to some real extent it may also be irrelevent.  Here's my theory:

People often don't know ahead of time, and aren't able to reasonably
predict, just how much concurrency they will need.  Also, their
concurrency needs may change over time, again in ways that are hard
for them to predict.

Therefore, picking a tool which they KNOW has serious concurrency
limitations introduces a significant risk, the risk that they may have
to drastically overhaul their underlying tools and architecture at
some unknown point in the future, due to hitting concurrency problems
and thus suddenly discovering that they're now using the wrong tool
for the job.

Since SQLite's concurrency support is very limited, potential users
are forced to pre-maturely optimize:  Either believe you will need high
concurrency, and go with PostgreSQL or Oracle, or believe you will
need no or low concurrency, and go with SQLite.  Premature
optimization is a bad thing.

There is a missing middle ground there: A medium-concurrency SQLite.

If a future version of SQLite - let's call it "SQLiteMC" - were able
to significantly increase concurrency, while at the same time
preserving all or most of SQLite's historical advantages (simplicity,
etc.), this would significantly expand the problem domain to which
SQLite is applicable.  That, in turn, would reduce the "wrong tool"
risk of using SQLite, and both of those effects would tend to make
SQLiteMC both more useful and more widely used than SQLite is now.

That's my little mental model anyway.  To me the direction of those
effects seems just about certain, but what I don't know - and can't
know, much less quantify - is the size of those effects - they could
be anywhere from zero to large.  And of course, that's assuming that
SQLiteMC succeeds in its technical goals.

And would SQLiteMC succeed in its technical goals?  How hard would it
be, and are there sufficient programmer resources available to make it
happen?  That I don't really know either, but together, Dr. Hipp plus
a few other experts on this list could probably come up with a
reasonably good idea...

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


This e-mail may be 
privileged and/or confidential, and the sender does not waive any related rights and 
obligations. Any distribution, use or copying of this e-mail or the information it 
contains by other than an intended recipient is unauthorized. If you received this 
e-mail in error, please advise me (by return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux 
droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le (les) 
destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par 
erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par 
un autre moyen.

RE: [sqlite] vers 3.0 concurrency issues

[basil . thomas]

Thanx
The main reason for file locking is to support win95/98/ME???...

I do have a server process running and have embedded sqlite to be used by
individual threads
that may be started. All writes currently go to one writer thread and this
seems to work fine.
The application we have developed basically creates a virtual xml storage
using SQLite as the backing store.
When documents are small, transactions do not slow concurrency that much but
once in a while we
may import a large document or update many small documents at once, we have
noticed a large
decrease in concurrency as all reader threads are blocked until the write
thread that is in a transaction completes. One way we have gotten around
this is to implement multiple collections of documents with each collection
stored in one database. Implementing each virtual document as a single
file/db is out of the question as we have thousands of documents stored
already in each collection.
All the other features in SQLite are too good for me to abandon just for
lack of higher concurrency.
Since I have read a lot of suggestions to improve concurrency for ver 3.0,
can you tell me what will be
changed if anything in version 3.0 as regards increased concurrency???

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 06, 2004 12:23 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] vers 3.0 concurrency issues


Thomas, Basil wrote:
 > I am no technical expert but...could not page locking at least be
implemented
 > by the pager module to increase concurrency(very naive...but better than
file
 > locking).
 >

Page-level locking will not help.  For one thing, we cannot do both
page-level
locking and reader/writer locks on win95/98/ME.  Presumably, reader/writer
locks are more desirable than page locks and we are not yet ready to
abandon win95/98/ME.  (You can do both on unix and winNT/2K/XP.)

But more importantly, locking is less than half the problem.  The hard
part is not locking but recovering from a program crash or OS crash or
power failure.  If we didn't have to deal with crashes and power failures,
doing page-level or row-level locking would be (relatively) easy.

In version 3.0, you will be able to ATTACH multiple databases and update
them all at once (and atomicially).  Then if you put each of your tables
in a separate database file and ATTACH them as needed, the end result
will be something very like table-level locking.  Without a central
server process to coordinate things, getting any more concurrency
than that is not a viable option, as far as I can determine.


-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



This e-mail may be privileged and/or confidential, and the sender does not waive any 
related rights and obligations. Any distribution, use or copying of this e-mail or the 
information it contains by other than an intended recipient is unauthorized. If you 
received this e-mail in error, please advise me (by return e-mail or otherwise) 
immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux 
droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le (les) 
destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par 
erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par 
un autre moyen.

 

[sqlite] vers 3.0 concurrency issues

[basil . thomas]

I would like to use SQLite on a web server or .net remoting and
multi-user/threads may become an issue
as locking is based at the finest granularity of file locking instead of
table/page/row locking. Will this issue be resolved from 3.x onwards so that
concurrency can be increased when multiple users
are connecting to the db?
SQLite is extremely fast in single process/thread mode and because of this
success, more users would like to access the application. File locking is
too similar to MS Access and we have had all kinds of problems
supporting multiple users on a network drive. Ideally I would first like to
implement SQLite as part of an ISAPI dll so it could be accessed using http
to an IIS web server. 
Any suggestions or solutions to running SQLite in multi-threaded environment
with potentially high concurrency???
B.Thomas

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 1:56 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] release 14


aducom wrote:
> Hi,
> 
> Is it possible to do a release 14 of the sqlite.dll with the support of 
> real blobs? (It's available in cvs?) Or will I have to wait for the 
> full new version?
> 

I don't know what you mean by "support for real blobs".  You'll need
to be much more specific.

Generally speaking, no new features will be added to SQLite 2.8.13.
What you see is what you get.  New features go into SQLite 3.0.0.
-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



This e-mail may be privileged and/or confidential, and the sender does not waive any 
related rights and obligations. Any distribution, use or copying of this e-mail or the 
information it contains by other than an intended recipient is unauthorized. If you 
received this e-mail in error, please advise me (by return e-mail or otherwise) 
immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux 
droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le (les) 
destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par 
erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par 
un autre moyen.

 

RE: [sqlite] MySQL / SQLite

[basil . thomas]

I totally agree...
Seems like when users say SQLite is slower than "xyz...", they are using
a high level driver based interface instead of using a "c" based driver
program to
really test what SQLite is doing.
I have written tests at the "c" level for both MySQL and SQLite
and SQLite is generally much faster. The MySQL overhead is probably due to
tcp/ip communication...
Now where SQLite seems to slow down is when the database contains over a
gigabyte of data.
Hopefully SQLite ver 3.xx will fix that...


-Original Message-
From: Jimmy Lantz [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 14, 2004 1:39 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] MySQL / SQLite


At 19:32 2004-04-14, you wrote:
>>Yes, but are mysql_query and sqlite_query really doing the same thing? It 
>>is quite possible that mysql_query doesn't actually perform the complete 
>>query, while sqlite_query does. I think a better test would be to do the 
>>query and then step through all of the results, doing something with each 
>>row. What is the time difference for that?
>
>Ok:
>
>$array = array();
>include("../cardsearch/setMySQL.php");
>$erg = mysql_query("SELECT * FROM speedtest WHERE text5 LIKE '%a%'");
>while($row=mysql_fetch_row($erg)) $array[] = $row;
>
>$array = array();
>$db = sqlite_open("speedtest.sqlite");
>$result = sqlite_query($db, "SELECT * FROM speedtest WHERE text5 LIKE
'%a%'");
>while($row=sqlite_fetch_array($result)) $array[] = $row;
>
>MySQL: 0.24748015403748
>SQLite: 0.68342804908752
>
>$array = array();
>$db = sqlite_open("speedtest.sqlite");
>$result = sqlite_unbuffered_query($db, "SELECT * FROM speedtest WHERE 
>text5 LIKE '%a%'");
>while($row=sqlite_fetch_array($result)) $array[] = $row;
>
>MySQL: 0.23681807518005
>SQLite: 0.64980888366699
>
>I ran it several times, of course.
I think there might be some work needed on the PHP implementation of SQLite 
(marked as experimental)
Whereas the MySQL implementation has had years to mature and improve.
Do your tests using the CLI instead and see if you get the same.
/ Jimmy

>-hannes
>
>
>
>
>-
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.

RE: [sqlite] Adding SQL commands

[basil . thomas]

Great...can you tell me if there will be any increased technical specs???
Ver 3.0 will have a 64-bit ROWID, what about:
- page size
- max table rows
- max database size
- max blob size
- any other tech limitations info...


-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 14, 2004 11:52 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Adding SQL commands


[EMAIL PROTECTED] wrote:
 > [I]f version 3.0 is a foundation move to implement enhanced language
 > functionality later,...

That is a correct assessment.  SQLite version 3.0.0 is an enhancement
to the foundation.  SQL Langauge enhancements can be made later and
in a backwards-compatible way.  The only thing that really has to go
into 3.0.0 is incompatible file format or API changes that will
facilitate future enhancments.

-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.

RE: [sqlite] Adding SQL commands

[basil . thomas]

Yes...It would be great if SQLite had control-flow statements and variables
just like
Transact-SQL(MS/Sybase) as it would allow one to put all the data
manipulation into
one script and run it like a stored procedure...
I do like the fine control that SQLite gives my application but I also think
declarative programming is far more productive for most of my data
manipulation tasks.
Are there any pans to add any more SQL commands or at least implement the
current SQL 
commands more fully such as CHECK, auto increment/identy, named parameters,
etc..???
I do use as many of the sqlite_... "c" function in my .net managed code as
needed but
I am increasingly using more SQL to rapidly implement solutions by calling
it as a script.
Any custom function I implement in "c" but over 95% of my data manipulation
problems
are solved easily in SQL.
SQLite Version 3.0 does not seem to implement any enhancements to the SQL
language
and I think that needs to be seriously looked at. But if version 3.0 is a
foundation move
to implement enhanced language functionality later, then this would be a
good step forward...
 

-Original Message-
From: Greg Obleshchuk [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 13, 2004 7:20 PM
To: Thomas, Basil; [EMAIL PROTECTED]
Subject: Re: [sqlite] Adding SQL commands


Hi Basil,
The first use of IF is same syntax as the case statement so I don't think it
is required and as IF isn't SQL92 I doubt it will be included
 
so your command is replaced by
 
case when (select count(*) from foo) = 100 then 'good' else 'bad' end 
 
I like the idea of exist but then again you can do this (in a query) count 
where (Select count(*) from foo where col1 ='a') = 1
 
but what I really think you are talking about is a command language like
TSQL for Microsoft or P/SQL for Oracle (is it P/SQL what ever?)  At the
moment you can't do that type of thing is SQLite.  I think the reason here
is the it is a Database system that is included in your application and
therefore you application can make these decisions far better that a SQL
language
 
Greg O
Don't for get www.SQL-Scripts.Com  
 

- Original Message - 
From: [EMAIL PROTECTED]   
To: [EMAIL PROTECTED]   
Sent: Wednesday, April 14, 2004 8:03 AM
Subject: [sqlite] Adding SQL commands

I would like to add some commands to SQLite to make my SQL(ite) programming
life easier!!!
The commands are:
IF - e.g IF ((select count(*) from foo) = 100)
select "good";
   ELSE
select "bad";
   END

EXISTS - e.g IF EXISTS(select * from sqlite_master where name =
'foo')
DROP TABLE foo;
END

local variables - e.g. DECLARE @var TEXT;
 select @var = name from foo;

I am not familiar with how compilers work but would be willing to write the
"c" code
to make these commands work.

Does anyone have a simple way to add a command using the lemon parser??? I
find the documentation
hard to understand and I would like to add these command s using the c++
compiler from Visual Studio.Net

B.Thomas



This e-mail may
be
privileged and/or confidential, and the sender does not waive any related
rights and obligations. Any distribution, use or copying of this e-mail or
the
information it contains by other than an intended recipient is unauthorized.
If you received this e-mail in error, please advise me (by return e-mail or
otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne
renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion,
utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.




This e-mail may be 
privileged and/or confidential, and the sender does not waive any related rights and 
obligations. Any distribution, use or copying of this e-mail or the information it 
contains by other than an intended recipient is unauthorized. If you received this 
e-mail in error, please advise me (by return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux 
droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le (les) 
destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par 
erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par 
un autre moyen.

[sqlite] Adding SQL commands

[basil . thomas]

I would like to add some commands to SQLite to make my SQL(ite) programming
life easier!!!
The commands are:
IF - e.g IF ((select count(*) from foo) = 100)
select "good";
   ELSE
select "bad";
   END

EXISTS - e.g IF EXISTS(select * from sqlite_master where name =
'foo')
DROP TABLE foo;
 END

local variables - e.g. DECLARE @var TEXT;
 select @var = name from foo;

I am not familiar with how compilers work but would be willing to write the
"c" code
to make these commands work.

Does anyone have a simple way to add a command using the lemon parser??? I
find the documentation
hard to understand and I would like to add these command s using the c++
compiler from Visual Studio.Net

B.Thomas



This e-mail may be
privileged and/or confidential, and the sender does not waive any related
rights and obligations. Any distribution, use or copying of this e-mail or the
information it contains by other than an intended recipient is unauthorized.
If you received this e-mail in error, please advise me (by return e-mail or
otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.

RE: [sqlite] A proposal for SQLite version 3.0

[basil . thomas]

The proposal for version 3.0 looks very promising and I hope all the major
changes 
will be implemented. Most of the enhancements seem to add flexibility and
scalability to
to the "C" based api. This is great as at the "C" api call level you can do
anything you want.
But I do not see any changes to add similar flexibility at the SQL language
level.
Declarative programming is much more productive than writing many lines of
"c" code to 
do any data manipulation tasks. The current SQL language implementation
excels at the
vast majority of data manipulation needs but falls short in being able to
implement a 
complete data management system at the SQL language level.

One example is creating a database. SQLite can only create a database at the
"c" api level.
This should be changed to be able to use a CREATE DATABASE  command.
The sqlite3_open function
should be implemented with one major change. Instead of passing the complete
file name of the database,
pass in the name of the directory where the master/system database will be
created/opened. Only the master/system database should be created
automatically if one is not located in that directory. A sqlite3_create
function as well as a CREATE DATABASE command would create a user database
in the same directory as the master/system database by default as the system
database would contain a list of all user database that have been created
under that system database. Once a database has been created, a USE 
command would open that database if it is found in the master/system
database and make this the current database. The DETACH command could then
delete the user database link into the master/system database and this user
database would then be free to ATTACH to another master/system database.
Once this functionality is implemented it should not be hard to implement a
sqlite_users table in the master/system database to holder user information
so the GRANT/REVOKE command could be implemented against any user database
listed in the master/system database.

All of the above is based on the simple command of creating a database at
the SQL language level. Many other enhancements to the SQL language
could/should be implemented and I would gladly make more suggestions if
any other current users of SQLite feel that this would be a good step
forward

B.Thomas




-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 07, 2004 9:22 AM
To: [EMAIL PROTECTED]
Subject: [sqlite] A proposal for SQLite version 3.0


A design proposal for SQLite version 3.0 can be found at:

 http://www.sqlite.org/prop2.html

Feedback from the user community is strongly encouraged.
An executive summary of the proposed changes follows:

*  Support for UTF-16
*  Better BLOB support
*  User-defined collating sequences (for better
   internationalization support)
*  Smaller and faster than 2.8.13.

The plan is to continue to support the 2.8.X series
indefinately and in parallel to the 3.X series.  But
the only changes to 2.8.X going forward will be bug
fixes.  New features will go into 3.X.  Beta releases
of version 3.X are expected within a few months.

I do not have much experience with UTF-16 and am
expecially interested in feedback on that area of
the design.
-- 
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.

[sqlite] SQLite 3.0...when???

[basil . thomas]

I have been using SQLite for over a year now and it is an excellent SQL
Library. Some more features from SQL92 would surely be welcome while keeping
SQLite fast and small. Suggestions for ver 3.0 include:

- Full referential integrity by implement check and foreign key
constraints
- multi-row value inserts just like MySQL (..copied the REPLACE from
MySQL..so copy INSERT as well)
- control-of-flow language such as IF-THEN-ELSE, BEGIN..END, WHILE,
PRINT, RAISERROR, etc
- use of local variables e.g. DECLARE @varName dataType
- system global variables e.g. @@error, @@identity
- stored procedures: if triggers can be implemented then so can
stored procs
- add alter TABLE, VIEW and TRIGGER
- CREATE, ALTER and DROP DATABASE: if we can attach and detach, we
should also be able to create
and delete them through DDL statements

I will be lucky if any of these would/could or should be implemented but if
most were then even for embedded applications,
being able to do almost all data manipulation using SQL statements only,
would be a great improvement...

B.Thomas



This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately. 

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce
pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation
ou copie de ce message ou des renseignements qu'il contient par une personne
autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez
ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par
retour de courrier électronique ou par un autre moyen.