Re: [sqlite] Most wanted features of SQLite ?
P Kishor wrote: > On Sat, Sep 19, 2009 at 9:21 AM, John Stanton wrote: >> Alexey Pechnikov wrote: >>> Hello! >>> >>> On Saturday 19 September 2009 00:43:18 Noah Hart wrote: >>> Stored Procedures >>> There are Tiny C compiler extension and realization of >>> stored procedures for SQLite 2 and Lua extension and other. >>> So you can use one or all of these. >>> >>> Best regards, Alexey Pechnikov. >>> http://pechnikov.tel/ >>> >> There is a PL/SQL implementation available and we use Javascript as a >> stored procedure capability in Sqlite. It integrates nicely with WWW >> applications. > > > well, can you share the above with the rest of the community? > I distributed it a while ago. I shall hunt it down again and put up a webpage. Ibasically used Spidermonkey and wrote an Sqlite interface. The Javascript plus Sqlite can run stand alone or from a Javascript extension to Sqlite. It is very handy because an algorithm in Javascript can run server or client side in WWW applications. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Alexey Pechnikov wrote: > Hello! > > On Saturday 19 September 2009 18:21:22 John Stanton wrote: >> There is a PL/SQL implementation available and we use Javascript as a >> stored procedure capability in Sqlite. It integrates nicely with WWW >> applications. > > I don't know this. Can you show link to docs and examples? Thx. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > I don't have a link but the developer has recent email on this list. > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite?: Stored procedures
On 20/09/2009, at 6:57 AM, Simon Slavin wrote: > > Ah. Okay, so in SQLite3 you can emulate stored procedures using > triggers. Just define a trigger to operate on something that > doesn't matter to you. For instance inserting a record in a table > that you never bother reading. Every so often you delete all rows > in the table just to keep it from taking up pointless space. Unfortunately triggers can't fill the need of stored procedures. A trigger can only perform certain SQL tasks, specifically update, insert, delete, select raise(error). For full procedures, we need the full SQL syntax, such as create temp table, pragma, create temp index, attach etc. Currently, I store procedures as text in a "Procedures" table in my database. But to execute them, I have to use SQL to copy out the text to my code, reinject it into SQL, pull out any results back into my code, potentially reinject into another SQL statement etc. Proper procedures would facilitate one call to SQLite, syntax checking of the procedure, precompiled optimizations etc. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite?: Access to database structure
I would like to see some more access to the database structure via SQL calls. For instance, we can currently get the list of tables, views, indexes and/or triggers via queries to SQLite_Master. For instance, to get the name and SQL of all triggers that are initiated by MyTable, we can query: select Name, SQL from SQLite_Master were Tbl_Name = 'MyTable' and Type = 'trigger'; And we can get the name and declared type of all columns of a table or view via: pragma table_info(MyTable); But that's about all we can do. I'd like to see similar SQL syntax available to get properties such as: 1. The constraints of a table. 2. The default value, and constraints of a table. Perhaps as an extension of the current pragma table_info() function, but preferably as a select query on a dynamic table (perhaps SQLite_Tables) so that we can feed the output into a larger query within SQL. 3. The members of a constraint, one row for each. 4. Improve the declared_type result of a column to show a declared type for an expression in a view. The declared_type C call and the pragma table_info() SQL call currently return the type of a column in a view if the column is simply referring to a column in a table. But if the column in the view is an expression, it returns null. I would like to see it return the declared type of the outer function of the expression, since most functions have a known result type. For instance, if a column in a view is defined as cast(mycomplexfunction() as integer), then the declared type is known to be an integer. If the function is abs(), the type is real. If the function is length(), the type is integer. Obviously a few functions such as coalesce() have varied types so they would still have to show a declared_type of null, but the cast() function gives the SQL author the chance to explicitly declare the type of such a column. 5. The components of a create view select statement, that would provide access to the parsed parameters: distinct/all, column expressions and names, from, where, group by, having, order by, etc. 6. Parsing of triggers into before/after/instead of, delete/insert/ update/update of, when, steps etc. I have written routines to do all this in my own code, but it would be far more accessible, especially for new SQLite programmers, to have access to this via SQL. It also seems to me that the SQLite code must already be parsing out these parameters internally so it would be relatively simple to make them available, and far more consistent than us re-inventing the wheel. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 21 Sep 2009, at 9:36pm, Fred Williams wrote: > would SQLite not work as the "back end" > for a client server (wrapper) implementation that did the multi > process (or > whatever) lock management up one level so to speak? Great question. I don't know. It requires either in-depth knowledge of how SQLite works, or specialised understanding of how such database engines work and the common faults with them. I don't have either. Don't forget: if you're going to use SQLite as a back end system you can implement transactions-across-TCP/IP and many of those other things people want too. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > >> I definitely don't agree here as we're talking about these additional >> locks existing _only_ in memory, not on disk. > > Which requires client/server architecture. Which SQLite3 doesn't > have. Once you require concurrent access features in your DBMS (i.e. > multi-user, lots of locking) the things you nned to implement start to > be easier with a client/server architecture, whether it's a standalone > client application that must be launched manually or just a unix-style > daemon running in the background which is launched automatically when > needed and quits when nothing has used it in a while. Uhh, no it doesn't. Unless your definition of client/server is completely different than mine in the context of what we're talking about here. In this context, the 'client and server' would share the same address space (they're the same process!), hence there is no client/server separation. It would mean the exact same amount of process separation as SQLite currently employs. It would just have additional code to optimize for concurrent writes by multiple threads just as sqlite3_enable_shared_cache() does for reads: http://sqlite.org/c3ref/enable_shared_cache.html http://sqlite.org/sharedcache.html What I'm suggesting would be an extension of that shared cache, but for managing access for writes. We're just talking finer-grained locks here ... we're not talking some elaborate scheme which requires IPC and client/server communication. It doesn't need to spawn off any other daemon process here, that would be just plain stupid to do within the context of what I'm talking about. I'm just wondering if you're confused on the difference between a thread and a process One of these days I just need to write a patch to do this and see if it gets accepted. Unfortunately, my spare time these days is around zilch. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Simon Slavin Sent: Monday, September 21, 2009 3:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? On 21 Sep 2009, at 6:53pm, Brad House wrote: > I definitely don't agree here as we're talking about these additional > locks existing _only_ in memory, not on disk. Which requires client/server architecture. Which SQLite3 doesn't have. Once you require concurrent access features in your DBMS (i.e. multi-user, lots of locking) the things you nned to implement start to be easier with a client/server architecture, whether it's a standalone client application that must be launched manually or just a unix-style daemon running in the background which is launched automatically when needed and quits when nothing has used it in a while. On 21 Sep 2009, at 6:44pm, Pavel Ivanov wrote: > Interesting point, Simon. Are you saying that all developers of big > database engines that implemented row-level locks are just idiots > because there's no benefit from it at all? They had to implement just > database-level locks and all users would be a lot happier because > they'd received a significant performance boost? Nope. They chose to implement a big database engine and that meant they chose client/server architecture, which makes locking (and all other things that require centralised control) less difficult. It's just that this is not how SQLite works. Simon. With "optional" fine grained locking would SQLite not work as the "back end" for a client server (wrapper) implementation that did the multi process (or whatever) lock management up one level so to speak? Sybase bought a small DB company a while back. The company was known as "Advantage DB" prior to the acquisition. They, and Sybase still does I believe, offered a "Local" and "Server" implementation of their DB. That is what I'm really thinking of here, with SQLite.dll used as the "Local" and SQLite.dll + as the "Server." Fact is Advantage was what I was using for my "Local" DB implementations before discovering SQLite. Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 21 Sep 2009, at 6:53pm, Brad House wrote: > I definitely don't agree here as we're talking about these additional > locks existing _only_ in memory, not on disk. Which requires client/server architecture. Which SQLite3 doesn't have. Once you require concurrent access features in your DBMS (i.e. multi-user, lots of locking) the things you nned to implement start to be easier with a client/server architecture, whether it's a standalone client application that must be launched manually or just a unix-style daemon running in the background which is launched automatically when needed and quits when nothing has used it in a while. On 21 Sep 2009, at 6:44pm, Pavel Ivanov wrote: > Interesting point, Simon. Are you saying that all developers of big > database engines that implemented row-level locks are just idiots > because there's no benefit from it at all? They had to implement just > database-level locks and all users would be a lot happier because > they'd received a significant performance boost? Nope. They chose to implement a big database engine and that meant they chose client/server architecture, which makes locking (and all other things that require centralised control) less difficult. It's just that this is not how SQLite works. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Not really... Natural joins require that the column names be equivalent on each table. In my current database schema, the common practice is to use as the column name of the FK. (i.e. "CarType" to reference the "Type" column of a "Cars" table) But yea, what I'm looking for is a form of NATURAL JOIN just..more natural :) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Monday, September 21, 2009 3:25 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Most wanted features of SQLite ? Shaun Seckman (Firaxis) wrote: > * I really would like some SQL syntax sugar that will > automatically include rows referenced by foreign keys in a table. I > haven't seen any other SQL database do this as all expect you to > explicitly include the conditions and joins which can become quite the > hassle. Does NATURAL JOIN help? > * My second awesome feature request would be support for storing > prepared statements into the database file to be used at later times. http://www.mail-archive.com/sqlite-users@sqlite.org/msg30073.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Shaun Seckman (Firaxis) wrote: > * I really would like some SQL syntax sugar that will > automatically include rows referenced by foreign keys in a table. I > haven't seen any other SQL database do this as all expect you to > explicitly include the conditions and joins which can become quite the > hassle. Does NATURAL JOIN help? > * My second awesome feature request would be support for storing > prepared statements into the database file to be used at later times. http://www.mail-archive.com/sqlite-users@sqlite.org/msg30073.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Here are my most wanted features and they don't even require locking or threading fu ;) * I really would like some SQL syntax sugar that will automatically include rows referenced by foreign keys in a table. I haven't seen any other SQL database do this as all expect you to explicitly include the conditions and joins which can become quite the hassle. * My second awesome feature request would be support for storing prepared statements into the database file to be used at later times. It's been documented that the creation of statements is a performance hit so it would be nice if my application doesn't have to recreate these statements every time it's executed. This would also cut down on the amount of hard-coded SQL I have scattered throughout my C++ application as I could instead use some sort of preprocessing tool to generate the statements and merely reference those statements by name or some other identifier in my code. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Brad House Sent: Monday, September 21, 2009 1:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? >> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere. I definitely don't agree here as we're talking about these additional locks existing _only_ in memory, not on disk. There'd be no reason to implement on-disk locking or even notifying the OS of sections of the file which are locked since we're only talking about multiple threads in the same process. Any other process would hit the OS file lock and be forced to wait. The overhead of in-memory locking going to be extremely minimal, and only affect those who specifically enable this fine-grained locking. That said, I do think the on-disk journal file format might need to change to accomplish even this, and I think that is probably the biggest show stopper. I'm not suggesting that this would be easy to implement either, and yes, you'd need to figure out if SQLite will block on a lock, or return BUSY, but by limiting the implementation scope to multithreaded applications, it at least makes the implementation feasible, and would provide great benefit to many users of SQLite. In our own synthetic benchmark of our application, which is extremely write-heavy, we see roughly 15 txns/sec with SQLite, but 1000 txns/sec with multiple connections to MySQL. Biggest difference here is MySQL allows multiple writers. (That said, I need to actually try to benchmark MySQL with only 1 connection to be able to normalize those numbers a bit). Typically though, those with large transaction volumes are going to go to some other database besides SQLite for other features of a server-based engine, like replication. Not complaining here though, SQLite definitely fits the bill for the default database of our application. What it does, it does well! -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
>> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere. I definitely don't agree here as we're talking about these additional locks existing _only_ in memory, not on disk. There'd be no reason to implement on-disk locking or even notifying the OS of sections of the file which are locked since we're only talking about multiple threads in the same process. Any other process would hit the OS file lock and be forced to wait. The overhead of in-memory locking going to be extremely minimal, and only affect those who specifically enable this fine-grained locking. That said, I do think the on-disk journal file format might need to change to accomplish even this, and I think that is probably the biggest show stopper. I'm not suggesting that this would be easy to implement either, and yes, you'd need to figure out if SQLite will block on a lock, or return BUSY, but by limiting the implementation scope to multithreaded applications, it at least makes the implementation feasible, and would provide great benefit to many users of SQLite. In our own synthetic benchmark of our application, which is extremely write-heavy, we see roughly 15 txns/sec with SQLite, but 1000 txns/sec with multiple connections to MySQL. Biggest difference here is MySQL allows multiple writers. (That said, I need to actually try to benchmark MySQL with only 1 connection to be able to normalize those numbers a bit). Typically though, those with large transaction volumes are going to go to some other database besides SQLite for other features of a server-based engine, like replication. Not complaining here though, SQLite definitely fits the bill for the default database of our application. What it does, it does well! -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Interesting point, Simon. Are you saying that all developers of big database engines that implemented row-level locks are just idiots because there's no benefit from it at all? They had to implement just database-level locks and all users would be a lot happier because they'd received a significant performance boost? Pavel On Mon, Sep 21, 2009 at 1:23 PM, Simon Slavin wrote: > > On 21 Sep 2009, at 5:38pm, Brad House wrote: > >> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 21 Sep 2009, at 5:38pm, Brad House wrote: > It could probably benefit a large number of integrations to > have finer grained locking even if it could not be implemented for all > integration types. It makes the system a great deal slower, since you need to keep checking all the levels of lock you have implemented. For instance, UPDATE props SET colour='black',condition='poor' WHERE description='fake sword' needs to check for locks on the file, the record, and three fields. That's five operations before you can even start to modify the data. Could easily double the amount of time it takes to perform the update. And if you implement column locks there are even more. And implementing fine-grain locks leads to lock-contention: if someone locks a record and you try to lock a field in that record, what should happen ? Now before trying to modify data and having locks interfere, you're trying to modify locks and having lock-interaction interfere. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
>> Fine for me. It seems to be everybody else that wants their favorite >> feature imbedded in the core :-) >> >> Fine grained locking would be a great "asset" I feel. Notice I did >> not >> request a "feature." > > > On my to-do list is to write a paper that explains why fine-grain > locking is not practical without either (1) a dedicated server process > to manage the locks or (2) enhancements to OS locking primitives that > are not currently available on any OS that I am aware of. There is a > widely held belief that since OSes provide byte-level locking of files > it should be a simple matter to provide row-level locking in a > serverless database engine. The proposed paper will explain why that > belief is incorrect. It could probably benefit a large number of integrations to have finer grained locking even if it could not be implemented for all integration types. I could see the implementation of a per-process lock (full DB lock), but with finer-grained row-level locking on a per-thread basis within the same application. Probably enabled through the use of sqlite3_enable_shared_cache(). This would actually be of great benefit to our use of SQLite which is strictly from a single multi-threaded process, no other application or process would touch the database simultaneously, just multiple threads from a single process would interact through multiple 'connections'. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Dr. Hip, As I stated before, I do not have a detailed understanding of the core source. Therefore I do not know how difficult or even possible adding a pragma controlled(?) finer grained locking "hook" or related API calls for those who might wish to "wrap" a server layer around the core. SQLite has proven to be a very fast, small, reliable, and bullet proof DB engine, for a single instance. I prefer to keep it true to its heritage. My real motive for the locking solution and API's would be to take the multi user pressure off the core. Leave us that want it to remain small, fast, and tight, a core that can either be used as is or embedded in a bigger picture. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of D. Richard Hipp Sent: Monday, September 21, 2009 8:14 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? On Sep 21, 2009, at 8:58 AM, Fred Williams wrote: > > Fine for me. It seems to be everybody else that wants their favorite > feature imbedded in the core :-) > > Fine grained locking would be a great "asset" I feel. Notice I did > not > request a "feature." On my to-do list is to write a paper that explains why fine-grain locking is not practical without either (1) a dedicated server process to manage the locks or (2) enhancements to OS locking primitives that are not currently available on any OS that I am aware of. There is a widely held belief that since OSes provide byte-level locking of files it should be a simple matter to provide row-level locking in a serverless database engine. The proposed paper will explain why that belief is incorrect. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sep 21, 2009, at 8:58 AM, Fred Williams wrote: > > Fine for me. It seems to be everybody else that wants their favorite > feature imbedded in the core :-) > > Fine grained locking would be a great "asset" I feel. Notice I did > not > request a "feature." On my to-do list is to write a paper that explains why fine-grain locking is not practical without either (1) a dedicated server process to manage the locks or (2) enhancements to OS locking primitives that are not currently available on any OS that I am aware of. There is a widely held belief that since OSes provide byte-level locking of files it should be a simple matter to provide row-level locking in a serverless database engine. The proposed paper will explain why that belief is incorrect. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Fine for me. It seems to be everybody else that wants their favorite feature imbedded in the core :-) Fine grained locking would be a great "asset" I feel. Notice I did not request a "feature." Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Alexey Pechnikov Sent: Monday, September 21, 2009 1:54 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? Hello! On Monday 21 September 2009 01:45:07 Fred Williams wrote: > With the background of the never ending drumbeat of "feature, feature, > feature..." on this list as a reason, I wonder if the structure of > SQLite could be "enhanced" to better support the "plug-in" concept, aka: > as with Firefox, IGoogle, and the like for instance. SQLite does have the best extensibility of known to me DBMS. You can bind function or collation from any programming lang. And you can easy write extension with a new functions and collations. You can use SQLite functions in any programming lang and create wrappers. Is it not enought for you?! Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 14:33:30 Grzegorz Wierzchowski wrote: > BTW while we are at subject of SQLite extensions. > I'm very new on this e-mail list but already saw here and there in mails > several links to places around the web with some extensions. There are a lot of extensions in defferent places. As example the compress extension functions are publicated by DRH in mail list, see comments in the sources. Yes, it is. Now you can see my archive http://mobigroup.ru/files/sqlite-ext/ Of cource I will glad to know about other extensions repositories. > I think it could be quite helpfull "first check place" if anybody is looking > for something like virtual table which stores data in csv files, or so, or > opposite - have wrote something general, and want to share. Please see VirtualText extension from Spatialite project http://mobigroup.ru/files/sqlite-ext/virtualtext/ I did pack this as single extension. NB: in multitheaded env is more better to make copy of date in SQLite with the extension and after operate with the copy. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
You mean like http://www.sqlite.org/contrib ? I agree though there's much to improve in that area... Itamar. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Grzegorz Wierzchowski Sent: Monday, September 21, 2009 1:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? BTW while we are at subject of SQLite extensions. I'm very new on this e-mail list but already saw here and there in mails several links to places around the web with some extensions. What would you say guys for creating some centalized list of (known/recommended ...) extensions somewhere on official Sqlite wiki. I found: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools , but this seems to be mainly about external programs like GUI or Management tools. I mean similar list but with links to general purposes code (open source licensed) for things like: - virtual tables modules - some usefull expression functions not implemented in mainline - special collating functions usable for wider audience, etc. I think it could be quite helpfull "first check place" if anybody is looking for something like virtual table which stores data in csv files, or so, or opposite - have wrote something general, and want to share. Best Regards, Grzegorz W. Monday 21 of September 2009 09:36:15 Roger Binns napisał(a): > Alexey Pechnikov wrote: > > SQLite does have the best extensibility of known to me DBMS. > > Also not mentioned is that it is available under a public domain > license and hence anyone has the right to use it in any way they deem > fit, make changes, distribute changes, charge anything they want, keep > everything public, private or anything else. Some of the alternatives > are open source but more restrictive (GPL). I've never read the > Oracle license agreement but there are many claims on the Internet > that its license agreement forbids publishing of benchmarks! > > Many of the feature requests are ultimately asking (or hoping :-) that > someone else will do the work. If something is that important then it > isn't unreasonable to pay DRH and team to do it. They also provide > support: > > http://www.sqlite.org/support.html > > Note "modest fee"! The extensions are also very cheap and liberally > licensed. No matter how you look at it, SQLite is a bargain. > > Roger > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
BTW while we are at subject of SQLite extensions. I'm very new on this e-mail list but already saw here and there in mails several links to places around the web with some extensions. What would you say guys for creating some centalized list of (known/recommended ...) extensions somewhere on official Sqlite wiki. I found: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools , but this seems to be mainly about external programs like GUI or Management tools. I mean similar list but with links to general purposes code (open source licensed) for things like: - virtual tables modules - some usefull expression functions not implemented in mainline - special collating functions usable for wider audience, etc. I think it could be quite helpfull "first check place" if anybody is looking for something like virtual table which stores data in csv files, or so, or opposite - have wrote something general, and want to share. Best Regards, Grzegorz W. Monday 21 of September 2009 09:36:15 Roger Binns napisał(a): > Alexey Pechnikov wrote: > > SQLite does have the best extensibility of known to me DBMS. > > Also not mentioned is that it is available under a public domain license > and hence anyone has the right to use it in any way they deem fit, make > changes, distribute changes, charge anything they want, keep everything > public, private or anything else. Some of the alternatives are open source > but more restrictive (GPL). I've never read the Oracle license agreement > but there are many claims on the Internet that its license agreement > forbids publishing of benchmarks! > > Many of the feature requests are ultimately asking (or hoping :-) that > someone else will do the work. If something is that important then it > isn't unreasonable to pay DRH and team to do it. They also provide > support: > > http://www.sqlite.org/support.html > > Note "modest fee"! The extensions are also very cheap and liberally > licensed. No matter how you look at it, SQLite is a bargain. > > Roger > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 11:36:15 Roger Binns wrote: > Also not mentioned is that it is available under a public domain license and > hence anyone has the right to use it in any way they deem fit, make changes, > distribute changes, charge anything they want, keep everything public, > private or anything else. And last few years every month are released new important features. About five years ago I did start to use SQLite as RDBMS for PDA and as helper storage for server but now I have some projects translated to SQLite from PostgreSQL and Oracle. That's great! Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexey Pechnikov wrote: > SQLite does have the best extensibility of known to me DBMS. Also not mentioned is that it is available under a public domain license and hence anyone has the right to use it in any way they deem fit, make changes, distribute changes, charge anything they want, keep everything public, private or anything else. Some of the alternatives are open source but more restrictive (GPL). I've never read the Oracle license agreement but there are many claims on the Internet that its license agreement forbids publishing of benchmarks! Many of the feature requests are ultimately asking (or hoping :-) that someone else will do the work. If something is that important then it isn't unreasonable to pay DRH and team to do it. They also provide support: http://www.sqlite.org/support.html Note "modest fee"! The extensions are also very cheap and liberally licensed. No matter how you look at it, SQLite is a bargain. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq3LOsACgkQmOOfHg372QRrWgCgrgqcqMEIMs6GgQpWv8WUzcCb XEUAnRqpChAc1qSPocYYxUdlE7ni08Oo =OGd1 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 01:45:07 Fred Williams wrote: > With the background of the never ending drumbeat of "feature, feature, > feature..." on this list as a reason, I wonder if the structure of > SQLite could be "enhanced" to better support the "plug-in" concept, aka: > as with Firefox, IGoogle, and the like for instance. SQLite does have the best extensibility of known to me DBMS. You can bind function or collation from any programming lang. And you can easy write extension with a new functions and collations. You can use SQLite functions in any programming lang and create wrappers. Is it not enought for you?! Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Can you say Oracle? :-) Along this same line, and based on a few years of using SQLite and following the messages on this list, here's my two cents worth. First a disclaimer: I have no knowledge of the detailed implementation and architecture of SQLite, other than by inference from discussions on this list. I did look at the source once long, long ago, and quickly decided, life is too short. With the background of the never ending drumbeat of "feature, feature, feature..." on this list as a reason, I wonder if the structure of SQLite could be "enhanced" to better support the "plug-in" concept, aka: as with Firefox, IGoogle, and the like for instance. This would allow SQLite (the module) to remain true to its roots (i.e. the "three choices") but better support those who desire to enhance SQLite through the use of both plug-ins and wrappers. This could better address a broader range of applications and allow the developer to selectively "bloat" the final result with only those features required for the project at hand. One thing that would greatly enhance concurrency (the most heard feature request?) would be to implement a finer grained locking structure in the base module. Currently you can either lock or unlock the database, period. All modern database implementations lock to, at a minimum, the the "record" (row) level. The implications of record level locking is pretty obvious related to concurrency. With finer grained locking implmenting multi user "server" wrappers and plug-ins would be better facilitated and allow the base SQLite module to remain true to the three choices. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of sub sk79 Sent: Sunday, September 20, 2009 9:16 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? ... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
StepSqlite PL/SQL compiler for SQLite is certainly not for all. It is simply aimed at what we believe is a majority. http://www.metatranz.com/stepsqlite/ regards, -sk >>But I write stored procedures and triggers for PostgreSQL on Tcl. PL/pgSQL or >>PL/SQL is not the best solution to all. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Having to support a dedicated language for stored-preocedures sounds to me like an overkill, PL/SQL or not. IMHO having the ability to store complex queries in the standard TSQL syntax already supported today for queries, plus basic extra stuff only like loops, and have their compiled version executed, would be just enough (since as someone else has mentioned before most reasons to use SPs are irrelevant with most SQLite uses). If that's already an existing feature (afaik it isn't), then I have nothing else to ask for in this subject. Itamar. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Alexey Pechnikov Sent: Sunday, September 20, 2009 7:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? Hello! On Sunday 20 September 2009 18:16:19 sub sk79 wrote: > PL/SQL has a very wide user-base and a huge repository of existing > code-base in the world. Using StepSqlite PL/SQL compiler this huge > base can use SQLite by reusing their code as well as reusing their > skills - no learning curve for this set of users. But I write stored procedures and triggers for PostgreSQL on Tcl. You can write it on perl, java, etc. PL/pgSQL or PL/SQL is not the best solution to all. IMHO is more interesting any open source lang than proprietary PL/SQL. Oracle has a lot of a non-standart extensions which are not exists in SQLite. And Oracle ideology is very different. You may not replace Oracle to SQLite with the same application architecture. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
As Igor mentioned in a previous post, Hebrew has no capital and lower case versions of a letter. It has only what's called Mantzpach, which are 5 letters which look different if in the end of a word; IIRC this is never an issue since they are ordered right after the original letter, and have their own character value (unlike capital/lower letters in English). Niqqud (the diacritical signs) are considered characters on their own, so you should either strip them (a common solution), or take that into account; here I'm not sure what is the default behavior. Also, since Hebrew letters are only used in Hebrew texts (unlike latin characters), sorting is hardly an issue if you have a Unicode representation of the character (usually a two byte one). The issues ICU is more likely to help you with are logical to visual conversion and the other way around and BiDi stuff. That's my own grasp of things, never had to use ICU myself. Itamar. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Saturday, September 19, 2009 6:44 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? On 19 Sep 2009, at 3:07am, Igor Tandetnik wrote: > Simon Slavin wrote: >> Thanks to you and Jay for explanations. I hadn't encountered ICU at >> all before. Your descriptions make perfect sense and are very >> interesting since ICU is a good attempt to get around one of the >> fundamental problems of Unicode. > > Out of curiosity - what do you consider a fundamental problem of > Unicode? The fact that different people may prefer their strings > sorted differently? Only in that it's a fundamental problem with the way Unicode was defined. I completely recognise that the question of sorting cannot be answered at the level of characters for the reasons we discussed: different alphabets have different meanings for the same characters, and Unicode has just one entry for the character. It might have made more sense to define two levels of character definitions: one which says what 'c with a hat on' looks like, and another that defines alphabets, character alternatives, and where 'c with a hat on' comes in various alphabets. The problem I was referring to is that there's no consistent way of picking up which characters are variants of other characters. In the Roman alphabet, it would be very useful to be able to look at the codes for 'l' and capital 'L' and realise that they're somehow the same. In Hebrew it would be useful to be able match not only capital and lower-case characters, but also the variants used when a character occurs at the end of a word. ICU is a great way to approach these problems and similar ones. I have no problem with it. On 19 Sep 2009, at 3:17am, Roger Binns wrote: > Errr, this is not the fault of Unicode. Your reaction to my post is amusingly similar to my reaction when people assume that database synchronisation is simple. Sorry to have irritated you. I understand Unicode in more detail than we've discussed here. I do not consider these things to be 'the fault of Unicode' rather, in the words I used, 'problems with Unicode'. And I do consider Unicode to be far superior to the mess of code pages we used to have to implement before it became popular. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 18:16:19 sub sk79 wrote: > PL/SQL has a very wide user-base and a huge repository of existing > code-base in the world. Using StepSqlite PL/SQL compiler this huge > base can use SQLite by reusing their code as well as reusing their > skills - no learning curve for this set of users. But I write stored procedures and triggers for PostgreSQL on Tcl. You can write it on perl, java, etc. PL/pgSQL or PL/SQL is not the best solution to all. IMHO is more interesting any open source lang than proprietary PL/SQL. Oracle has a lot of a non-standart extensions which are not exists in SQLite. And Oracle ideology is very different. You may not replace Oracle to SQLite with the same application architecture. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Can you say Oracle? :-) Along this same line, and based on a few years of using SQLite and following the messages on this list, here's my two cents worth. First a disclaimer: I have no knowledge of the detailed implementation and architecture of SQLite, other than by inference from discussions on this list. I did look at the source once long, long ago, and quickly decided, life is too short. With the background of the never ending drumbeat of "feature, feature, feature..." on this list as a reason, I wonder if the structure of SQLite could be "enhanced" to better support the "plug-in" concept, aka: as with Firefox, IGoogle, and the like for instance. This would allow SQLite (the module) to remain true to its roots (i.e. the "three choices") but better support those who desire to enhance SQLite through the use of both plug-ins and wrappers. This could better address a broader range of applications and allow the developer to selectively "bloat" the final result with only those features required for the project at hand. One thing that would greatly enhance concurrency (the most heard feature request?) would be to implement a finer grained locking structure in the base module. Currently you can either lock or unlock the database, period. All modern database implementations lock to, at a minimum, the the "record" (row) level. The implications of record level locking is pretty obvious related to concurrency. With finer grained locking implmenting multi user "server" wrappers and plug-ins would be better facilitated and allow the base SQLite module to remain true to the three choices. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of sub sk79 Sent: Sunday, September 20, 2009 9:16 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? >> 1. Generate a loadable SQLite extension and distribute it with DB. > > We can write C extension without any wrapper. Why we need to learn > your extension for getting the same result and possible new bugs? PL/SQL has a very wide user-base and a huge repository of existing code-base in the world. Using StepSqlite PL/SQL compiler this huge base can use SQLite by reusing their code as well as reusing their skills - no learning curve for this set of users. For those who do need to learn PL/SQL, it is still worthwhile to utilize StepSqlite because: PL/SQL is specially designed for and very suitable for handling database operations, while other general purpose languages like TCL, lua, C , C++ etc are very suitable indeed for application programming. StepSqlite enables combining the respective power of these two sets of languages in writing apps with SQLite back-ends by using the concept of Stored Procedures/Functions and Packages. All big databases operate this way as well: for ex, Oracle lets apps written in C++ to call stored procedures written in PL/SQL. StepSqlite is committed to bringing this power and convenience from the big database world to the small database world while preserving the small, fast and reliable. More benefits of StepSqlite described here: http://www.metatranz.com/stepsqlite/benefits.html http://www.metatranz.com/stepsqlite regards, -sk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Guillermo Varona Silupú wrote: > I want to insert this text: > > 1' equivale a 12" > > cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a > 12"") What language is that? ':=' suggests Pascal, but string literals in Pascal use single quotes. In SQL, string literals are surrounded by single quotes: if you need a single quote as part of the string, you enter two of them in a row. Double quotes don't require any special handling inside a string literal. INSERT INTO Tabla1 (Code,Equiv) VALUES (10, '1'' equivale a 12"') Now that you have a valid SQL statement, figure out how to represent it as a string literal in whatever host language you are using. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
>> 1. Generate a loadable SQLite extension and distribute it with DB. > > We can write C extension without any wrapper. Why we need to learn > your extension for getting the same result and possible new bugs? PL/SQL has a very wide user-base and a huge repository of existing code-base in the world. Using StepSqlite PL/SQL compiler this huge base can use SQLite by reusing their code as well as reusing their skills - no learning curve for this set of users. For those who do need to learn PL/SQL, it is still worthwhile to utilize StepSqlite because: PL/SQL is specially designed for and very suitable for handling database operations, while other general purpose languages like TCL, lua, C , C++ etc are very suitable indeed for application programming. StepSqlite enables combining the respective power of these two sets of languages in writing apps with SQLite back-ends by using the concept of Stored Procedures/Functions and Packages. All big databases operate this way as well: for ex, Oracle lets apps written in C++ to call stored procedures written in PL/SQL. StepSqlite is committed to bringing this power and convenience from the big database world to the small database world while preserving the small, fast and reliable. More benefits of StepSqlite described here: http://www.metatranz.com/stepsqlite/benefits.html http://www.metatranz.com/stepsqlite regards, -sk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hi I want to insert this text: 1' equivale a 12" cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a 12"") TIA Best Regards GVS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 08:05:04 Darren Duncan wrote: > The more general solution here to the duplicate column name problem is to be > stricter than the SQL standard and treat attempts to return duplicate column > names as a syntax or constraint error. For example, if you had 2 tables > 'foo' > and 'bar' with columns named (a, b) and (b, c), then a plain "select * from > foo > inner join bar on ..." should throw an exception because there would be two > 'b' > in the result. And so, proper NATURAL or USING behavior is one way to say > "select * from foo inner join bar ..." with success, and spelling out the > result > column list rather than using "*" is another way. But you have to deal with > it > explicitly or the SQL will refuse to run, is what the DBMS should do, or the > DBMS should be customizable so it can be thusly strict. It's interesting. The new pragma "unique_column_names" may be helpful for a lot of situations same as the "indexed by" condition for selects. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 02:42:30 Darren Duncan wrote: > As for Tcl, well one solution there is to create a new singleton TCL type and > use its one value to correspond to NULL, and so then empty string will > continue > to just mean empty string, as it should. Not being able to distinguish > known-to-be-an-empty-string from unknown-or-N/A value is a bad thing. In human interface can be used 'not defined' or 'overload' or any other value and all of these together in dependence of the situation. SQLite can store 'undef' and 'overload' and other values into integer or fload fields and this behaviour is fine. Application can operate with correct data by checking a typeof(). For single undefined value we can do more simple. Empty string or 'undefined' values are equal because have the same typeof()=text. I don't see any reasons to use a new singleton TCL type inside of empty string. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 02:45:32 sub sk79 wrote: > 1. Generate a loadable SQLite extension and distribute it with DB. We can write C extension without any wrapper. Why we need to learn your extension for getting the same result and possible new bugs? > 2. If distributing loadable extensions is a concern (security or > otherwise), StepSqlite also has an option to generate a regular C++ > library instead. This library can be linked into the user's > application code And how about Tcl, Python, etc? We need to recompile it? :-) Application code (Tcl, Python, etc.) can be stored into database and executed by application. C code can be compiled once and executed with Tiny C compiler. I'm sorry but I don't see any helpful features of your wrapper. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Jay A. Kreibich wrote: > On Sat, Sep 19, 2009 at 01:14:56PM -0700, Darren Duncan scratched on the wall: >> 3c. I would like to have the option for SQLite to never have duplicate >> unqualified column names; for example, if one said "foo NATURAL INNER >> JOIN bar" then only a single column with the common data would be in >> the result, rather than 2; > > Umm... it does work that way. That's part of the SQL standard. > > NATURAL JOINs and JOIN ... USING( ) will only return one copy of > each column pair used in the JOIN. My memory must be rusty then, because while I believe that is what should happen, I recalled using SQL DBMSs that behaved differently (which is, NATURAL or USING just controlled what records joined with what records, and that all of the non-distinct input columns were still output); I did not check recently though. >> likewise for inner joins with explicit join conditions of "foo.a = >> bar.a" would just return a single "a" in the result. > > This goes against the SQL standard and, in this case, I think > this would be a *very* Bad Idea. You're taking a command format that > is designed to take an arbitrary expression and changing the output > format based off the particulars of that expression. That strikes me > as extremely dangerous. For example, if someone has the first line > of code and changes it to something like the second line of code, > suddenly their output changes! > > ...t1 JOIN t2 ON t1.a = t2.a... => ..., a, ... > ...t1 JOIN t2 ON toupper(t1.a) = toupper(t2.a)... => ..., t1.a, t2.a, ... > > If you only want one column because you're using a straight equality, > use NATURAL or USING. USING is particularly useful to JOIN across a > sub-set of the commonly named columns. The more general solution here to the duplicate column name problem is to be stricter than the SQL standard and treat attempts to return duplicate column names as a syntax or constraint error. For example, if you had 2 tables 'foo' and 'bar' with columns named (a, b) and (b, c), then a plain "select * from foo inner join bar on ..." should throw an exception because there would be two 'b' in the result. And so, proper NATURAL or USING behavior is one way to say "select * from foo inner join bar ..." with success, and spelling out the result column list rather than using "*" is another way. But you have to deal with it explicitly or the SQL will refuse to run, is what the DBMS should do, or the DBMS should be customizable so it can be thusly strict. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 01:14:56PM -0700, Darren Duncan scratched on the wall: > 3c. I would like to have the option for SQLite to never have duplicate > unqualified column names; for example, if one said "foo NATURAL INNER > JOIN bar" then only a single column with the common data would be in > the result, rather than 2; Umm... it does work that way. That's part of the SQL standard. NATURAL JOINs and JOIN ... USING( ) will only return one copy of each column pair used in the JOIN. > likewise for inner joins with explicit join conditions of "foo.a = > bar.a" would just return a single "a" in the result. This goes against the SQL standard and, in this case, I think this would be a *very* Bad Idea. You're taking a command format that is designed to take an arbitrary expression and changing the output format based off the particulars of that expression. That strikes me as extremely dangerous. For example, if someone has the first line of code and changes it to something like the second line of code, suddenly their output changes! ...t1 JOIN t2 ON t1.a = t2.a... => ..., a, ... ...t1 JOIN t2 ON toupper(t1.a) = toupper(t2.a)... => ..., t1.a, t2.a, ... If you only want one column because you're using a straight equality, use NATURAL or USING. USING is particularly useful to JOIN across a sub-set of the commonly named columns. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
The word 'Stored Procedures ' when used in the context of DBMS is used to refer to several meanings: 1. Efficiency - compile once when 'stored' and run multiple-times. 2. Data Encapsulation & Access control for DB - DB owns and controls access to its API 'stored' in it. 3. Client-server design - server 'stores' the procedures that any client can then use. 4. Procedural language - a language which supports procedural statements (in addition to the declarative ones provided by SQL): loops, conditionals, variables etc. 5. Physically residing in DB - API resides and moves with the DB. This is the meaning most people are familiar with. StepSqlite satisfies #1, #2, #3 and #4 today and looks forward to support for #5 being implemented in future versions of SQLite. As for the question about distributing libs, StepSqlite gives users two ways to integrate the compiled PL/SQL code into their SQLite applications: 1. Generate a loadable SQLite extension and distribute it with DB. 2. If distributing loadable extensions is a concern (security or otherwise), StepSqlite also has an option to generate a regular C++ library instead. This library can be linked into the user's application code and thus becomes part of the user's code just like any other library would. Wherever the app goes the stored procedures/functions go too. http://www.metatranz.com/stepsqlite Regards, -sk On Sat, Sep 19, 2009 at 10:33 AM, Alexey Pechnikov wrote: > Hello! > > On Saturday 19 September 2009 02:17:39 Subsk79 wrote: >> StepSqlite brings powerful Stored Procedure support with full power of >> PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere >> 'wrapper' so it generates much more efficient code than any wrapper >> could ever achieve - for instance, it pre-compiles all SQL in your >> code right when the lib is loaded - no compile-overload at runtime - >> this is exactly what one expects from a true 'Stored' Procedure. > > Do you have support for compiled extension stored into database table? > It's not good way to distribute external libs. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Alexey Pechnikov wrote: > Hello! > > On Sunday 20 September 2009 00:14:56 Darren Duncan wrote: >> 3b. I would like to have the option for SQLite to always operate using >> 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply >> treated >> as another value of its own singleton type that is disjoint from all other >> types >> same as Integer, Numeric, Text, Blob are disjoint. And so, one could then >> just >> use ordinary equality or not-equality tests to check for NULL, and NULL >> would >> equal NULL but not equal anything else, and boolean tests would always >> return >> true or false, not null. Once again, this would mean that behavior is more >> like >> what users actually expect and bugs can be avoided, and the query optimizer >> can >> be more efficient again, allowing more reorganization knowing at answers >> wouldn't change due to this. > > NULL is the old RDBMS problem. And SQLite Tcl interface has no equal > availability > for NULL values because we can't operate with non-defined variables. So we > can > translate NULL values into empty Tcl strings but not vice versa. Well we could also ditch NULL entirely in the database as the relational model doesn't actually require it and it is simply a convenient way to say we know we don't have normal data somewhere. Though NULL is also deficient in that way because it doesn't say *why* we don't have normal data (eg, not applicable versus applicable but unknown). I would argue for the elimination of NULL entirely and just let people design their databases to explicitly say "this point is missing for this reason", but what I proposed above was meant to be a softer intermediate approach to let NULL-depending people down easier. As for Tcl, well one solution there is to create a new singleton TCL type and use its one value to correspond to NULL, and so then empty string will continue to just mean empty string, as it should. Not being able to distinguish known-to-be-an-empty-string from unknown-or-N/A value is a *bad* thing. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Sunday 20 September 2009 00:14:56 Darren Duncan wrote: > 3b. I would like to have the option for SQLite to always operate using > 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply > treated > as another value of its own singleton type that is disjoint from all other > types > same as Integer, Numeric, Text, Blob are disjoint. And so, one could then > just > use ordinary equality or not-equality tests to check for NULL, and NULL would > equal NULL but not equal anything else, and boolean tests would always return > true or false, not null. Once again, this would mean that behavior is more > like > what users actually expect and bugs can be avoided, and the query optimizer > can > be more efficient again, allowing more reorganization knowing at answers > wouldn't change due to this. NULL is the old RDBMS problem. And SQLite Tcl interface has no equal availability for NULL values because we can't operate with non-defined variables. So we can translate NULL values into empty Tcl strings but not vice versa. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, 19 Sep 2009 21:57:13 +0100, Simon Slavin wrote: > >On 19 Sep 2009, at 9:14pm, Darren Duncan wrote: > >> Simon Slavin wrote: >>> On 18 Sep 2009, at 9:43pm, Noah Hart wrote: Stored Procedures >>> >>> How do those differ from what can be done with triggers ? >> >> A stored procedure is an arbitrary-sized named sequence of >> statements to >> execute, which is stored in the database as data (same as table or >> view or >> trigger definitions), and which generally is explicitly invoked as a >> statement. >> >> A trigger is a stimulus-response rule that says when a particular >> event happens >> then a particular stored procedure is to be executed automatically. >> In the >> general case, this is like an event handler in a typical application >> that >> responds to mouse clicks or network connections or whatever. Some >> DBMSs support >> this in the more general sense of "do this when this happens" but >> most DBMSs >> that support "triggers" just handler more limited situations, such >> as "do this >> before/after a record is inserted/updated/deleted in this table". > >Ah. Okay, so in SQLite3 you can emulate stored procedures using >triggers. Just define a trigger to operate on something that doesn't >matter to you. For instance inserting a record in a table that you >never bother reading. Every so often you delete all rows in the table >just to keep it from taking up pointless space. Yes, or UPDATE a VIEW which has an INSTEAD OF trigger defined for it. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 19 Sep 2009, at 9:14pm, Darren Duncan wrote: > Simon Slavin wrote: >> On 18 Sep 2009, at 9:43pm, Noah Hart wrote: >>> Stored Procedures >> >> How do those differ from what can be done with triggers ? > > A stored procedure is an arbitrary-sized named sequence of > statements to > execute, which is stored in the database as data (same as table or > view or > trigger definitions), and which generally is explicitly invoked as a > statement. > > A trigger is a stimulus-response rule that says when a particular > event happens > then a particular stored procedure is to be executed automatically. > In the > general case, this is like an event handler in a typical application > that > responds to mouse clicks or network connections or whatever. Some > DBMSs support > this in the more general sense of "do this when this happens" but > most DBMSs > that support "triggers" just handler more limited situations, such > as "do this > before/after a record is inserted/updated/deleted in this table". Ah. Okay, so in SQLite3 you can emulate stored procedures using triggers. Just define a trigger to operate on something that doesn't matter to you. For instance inserting a record in a table that you never bother reading. Every so often you delete all rows in the table just to keep it from taking up pointless space. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > On 18 Sep 2009, at 9:43pm, Noah Hart wrote: >> Stored Procedures > > How do those differ from what can be done with triggers ? A stored procedure is an arbitrary-sized named sequence of statements to execute, which is stored in the database as data (same as table or view or trigger definitions), and which generally is explicitly invoked as a statement. A trigger is a stimulus-response rule that says when a particular event happens then a particular stored procedure is to be executed automatically. In the general case, this is like an event handler in a typical application that responds to mouse clicks or network connections or whatever. Some DBMSs support this in the more general sense of "do this when this happens" but most DBMSs that support "triggers" just handler more limited situations, such as "do this before/after a record is inserted/updated/deleted in this table". -- As for my own wishlist, well I'll name a few items. 1. If I were asked a year ago I would say top of the list is support for child transactions, but these were already delivered in 3.6.8 so my greatest wish is already met. 2. While stored procedures would be very valuable, I do not see them necessary for SQLite itself to implement, since these at least can be effectively done at the user level, especially when you consider that you would want to have parameters and variables so to either use the same user input with multiple statements in the procedure or feed results of one statement to input to another, and you'd probably want conditionals or loops etc, which host languages already provide for your use. 3. I would like to see a pragma and/or compile-time option (or several for finer graining) that subtly alters some SQL semantics or allowed syntax when in use, to deal with some SQL mis-features. 3a. I would like to have the option for SQLite to always operate using set semantics rather than bag semantics, automatically, so that for example any join or union or select-list or group or count() or aggregate etc would always just return unique rows and never treat duplicates as being distinct. Using this mode would first of all be more likely to give the results that users actually want, avoiding common bugs, and also allow for the SQLite query optimizer to be much more efficient as it could safely make more rearrangements of the query without worry that doing so would change the answer in the presence of duplicates. 3b. I would like to have the option for SQLite to always operate using 2-valued-logic rather than 3-valued-logic, meaning that NULL is simply treated as another value of its own singleton type that is disjoint from all other types same as Integer, Numeric, Text, Blob are disjoint. And so, one could then just use ordinary equality or not-equality tests to check for NULL, and NULL would equal NULL but not equal anything else, and boolean tests would always return true or false, not null. Once again, this would mean that behavior is more like what users actually expect and bugs can be avoided, and the query optimizer can be more efficient again, allowing more reorganization knowing at answers wouldn't change due to this. 3c. I would like to have the option for SQLite to never have duplicate unqualified column names; for example, if one said "foo NATURAL INNER JOIN bar" then only a single column with the common data would be in the result, rather than 2; likewise for inner joins with explicit join conditions of "foo.a = bar.a" would just return a single "a" in the result. Such things as this, especially 3a,3b, *are* best implemented at the internal guts level of SQLite, for what is hopefully obvious reasons. Now writing SQL that targets these semantics may not be fully portable, but it would be a lot more correct and trouble-free for people just using SQLite, or other DBMSs that support those semantics I proposed. And a point is that an implementation of what I proposed would be *simpler*/*liter* than what is required to implement the standard SQL semantics that support duplicates and 3VL, and it generally does what people actually want. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 18.09.2009 21:56 CE(S)T, Simon Slavin wrote: > * Support for multiple concurrent clients/processes Doesn't that already work? You need common file system access, right, but then it should work afaik. What I'd like to see is foreign key integrity enforcement. You can already do it with triggers but it would be way easier if the system did that for me. -- Yves Goergen "LonelyPixel" Visit my web laboratory at http://beta.unclassified.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 02:32:03 Roger Binns wrote: > If you are using the C api then use > sqlite3_auto_extension - http://www.sqlite.org/c3ref/auto_extension.html - > to register a callback that is called whenever a new db is opened. For "autoload" extension we must call this function? It's very strange. I think this code in openDatabase function is more useful for statically linked extensions: #ifdef SQLITE_ENABLE_RTREE if( !db->mallocFailed && rc==SQLITE_OK){ rc = sqlite3RtreeInit(db); } #endif More interesting may be loading extensions from database table but it's not realised now, really? Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 9:21 AM, John Stanton wrote: > Alexey Pechnikov wrote: >> Hello! >> >> On Saturday 19 September 2009 00:43:18 Noah Hart wrote: >> >>> Stored Procedures >>> >> >> There are Tiny C compiler extension and realization of >> stored procedures for SQLite 2 and Lua extension and other. >> So you can use one or all of these. >> >> Best regards, Alexey Pechnikov. >> http://pechnikov.tel/ >> > There is a PL/SQL implementation available and we use Javascript as a > stored procedure capability in Sqlite. It integrates nicely with WWW > applications. well, can you share the above with the rest of the community? -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu --- Assertions are politics; backing up assertions with evidence is science === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 02:17:39 Subsk79 wrote: > StepSqlite brings powerful Stored Procedure support with full power of > PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere > 'wrapper' so it generates much more efficient code than any wrapper > could ever achieve - for instance, it pre-compiles all SQL in your > code right when the lib is loaded - no compile-overload at runtime - > this is exactly what one expects from a true 'Stored' Procedure. Do you have support for compiled extension stored into database table? It's not good way to distribute external libs. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 18:21:22 John Stanton wrote: > There is a PL/SQL implementation available and we use Javascript as a > stored procedure capability in Sqlite. It integrates nicely with WWW > applications. I don't know this. Can you show link to docs and examples? Thx. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Alexey Pechnikov wrote: > Hello! > > On Saturday 19 September 2009 00:43:18 Noah Hart wrote: > >> Stored Procedures >> > > There are Tiny C compiler extension and realization of > stored procedures for SQLite 2 and Lua extension and other. > So you can use one or all of these. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > There is a PL/SQL implementation available and we use Javascript as a stored procedure capability in Sqlite. It integrates nicely with WWW applications. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 19 Sep 2009, at 3:07am, Igor Tandetnik wrote: > Simon Slavin wrote: >> Thanks to you and Jay for explanations. I hadn't encountered ICU at >> all before. Your descriptions make perfect sense and are very >> interesting since ICU is a good attempt to get around one of the >> fundamental problems of Unicode. > > Out of curiosity - what do you consider a fundamental problem of > Unicode? The fact that different people may prefer their strings > sorted > differently? Only in that it's a fundamental problem with the way Unicode was defined. I completely recognise that the question of sorting cannot be answered at the level of characters for the reasons we discussed: different alphabets have different meanings for the same characters, and Unicode has just one entry for the character. It might have made more sense to define two levels of character definitions: one which says what 'c with a hat on' looks like, and another that defines alphabets, character alternatives, and where 'c with a hat on' comes in various alphabets. The problem I was referring to is that there's no consistent way of picking up which characters are variants of other characters. In the Roman alphabet, it would be very useful to be able to look at the codes for 'l' and capital 'L' and realise that they're somehow the same. In Hebrew it would be useful to be able match not only capital and lower-case characters, but also the variants used when a character occurs at the end of a word. ICU is a great way to approach these problems and similar ones. I have no problem with it. On 19 Sep 2009, at 3:17am, Roger Binns wrote: > Errr, this is not the fault of Unicode. Your reaction to my post is amusingly similar to my reaction when people assume that database synchronisation is simple. Sorry to have irritated you. I understand Unicode in more detail than we've discussed here. I do not consider these things to be 'the fault of Unicode' rather, in the words I used, 'problems with Unicode'. And I do consider Unicode to be far superior to the mess of code pages we used to have to implement before it became popular. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 3:24 AM, Roger Binns wrote: > > Hamish Allan wrote: >> Not sure whether this would need a rewrite, but for debug purposes I'd >> love to be able to view the SQL for a prepared statement with its >> values bound. > > I am always confused by requests like this. Your code called prepare and > your code called the various bind methods. Consequently your code can store > that information with as much detail and context as is useful to you. Like I say, it's for debug purposes. Sometimes my code doesn't behave as I would expect it to; perhaps there's something wrong with my string escaping, or perhaps my query is awry, and I would like to see if it works from the command line. I should note that I am a relative beginner using the C interface, which is perhaps not the most forgiving combination. Hamish ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hamish Allan wrote: > Not sure whether this would need a rewrite, but for debug purposes I'd > love to be able to view the SQL for a prepared statement with its > values bound. I am always confused by requests like this. Your code called prepare and your code called the various bind methods. Consequently your code can store that information with as much detail and context as is useful to you. For my Python wrapper (APSW) I even provide a tool where it will show all SQL executed (including bindings), results returned, timings, statistical information etc. Your own code doesn't even have to be touched or modified in any way: http://apsw.googlecode.com/svn/publish/execution.html#apsw-trace Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq0QMQACgkQmOOfHg372QQ50QCgyxF4NNwxrtFpVtSs6qnsXJIL 2Z8An2l/nw7I39LIMjHMgQ6rfzmAvPVY =t8XG -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > Your descriptions make perfect sense and are very > interesting since ICU is a good attempt to get around one of the > fundamental problems of Unicode. Errr, this is not the fault of Unicode. It is the fault of people! Unicode lets you represent the majority of the world's past and present characters using the same character set. Note that there is a lot of debate over exactly what constitutes a character, ways they combine code points, the same code point being used for different native character sets, dealing with older text where the character depiction matters even if it the "same" as a modern character. Unicode is a reasonable compromise. See http://en.wikipedia.org/wiki/Unicode#Issues Sorting and comparing strings are hard. For example someone in the US or UK would consider cafe and café to be equivalent. German has a different ordering for looking in a phonebook versus a dictionary. What do you do about a German user having a Swedish name in their phonebook? Is it sorted using Swedish rules or German rules? Unicode is not required to sort and compare strings, but it is a lot nicer place to start. And then the folks at the Unicode consortium who have been thinking about this for a very long time have come up with an algorithm that works (with locale specific adjustments) called the Unicode Collation Algorithm. Their report gives you a good idea of the complexity and issues involved. Section 1.8 is enlightening. http://www.unicode.org/unicode/reports/tr10/ ICU is a programming library implementing UCA plus a few other things. It is large and slow because of people, needing all sorts of builtin tables such as how each locale sorts things like accents and combining characters as well as ordinary codepoints commonly used across multiple locales: http://en.wikipedia.org/wiki/International_Components_for_Unicode You likely didn't intend your comment to be taken as condescending towards Unicode/UCA/ICU but I did want to make it *very* clear that they make life considerably easier for us as programmers dealing with human text and provide solutions to collation/case etc that we frequently need. It is far more than a "good attempt", closer to a very good solution. There aren't any alternatives that come *remotely* close as using the examples in the UCA report will show you. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq0P0QACgkQmOOfHg372QSz9ACggmw5kaLKwL90nggbr0GaTxkZ SNMAn17gWLmy3SdbzZVMI6fSoUtTVmYS =jOGK -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > Thanks to you and Jay for explanations. I hadn't encountered ICU at > all before. Your descriptions make perfect sense and are very > interesting since ICU is a good attempt to get around one of the > fundamental problems of Unicode. Out of curiosity - what do you consider a fundamental problem of Unicode? The fact that different people may prefer their strings sorted differently? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 19 Sep 2009, at 12:55am, Igor Tandetnik wrote: > Using ICU extension does require certain discipline. You must run > icu_load_collation soon after opening the database, and all users of > the > database must agree to map the same identifiers to the same locales > (the > best way to achieve that is probably to make collation name the same > as > locale name: SELECT icu_load_collation('he_IL', 'he_IL'); ). Mapping > the same collation identifier to different locales may indeed result > in > corrupted indexes. > > For more details, see > http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt Thanks to you and Jay for explanations. I hadn't encountered ICU at all before. Your descriptions make perfect sense and are very interesting since ICU is a good attempt to get around one of the fundamental problems of Unicode. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Sat, Sep 19, 2009 at 12:14:37AM +0100, Simon Slavin scratched on the wall: > > On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > > > Simon Slavin > > wrote: > >> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > >> > >>> Simon Slavin wrote: > * Unicode support from the ground up > >>> > >>> SQLite already has "unicode support from the ground up". Try using > >>> non-Unicode strings and you'll see! > >> > >> SQLite's indexing correctly understands how to order Unicode > >> strings ? > > > > With ICU extension enabled and correct collation specified, yes. Note > > that the correct ordering of Unicode strings is locale-dependent. > > Okay. So I create an indexed database in one locale. I have a > thousand records in there. The indexes are created using the locale I > set. I then send a copy of this database to a client in another > place, and the client has different locale settings. The client adds > another thousand records with their locale settings. What happens > when I use WHERE clauses with '<' or '>' ? Does the system vaguely > work, or does it get a mess ? You get a mess. The locale is what defines the "rules" of the language and the meaning of the symbols that make up the string. This example is no different than if you added a bunch of records under NOCASE, then somehow changed the collation to be case sensitive and added a bunch more. Your indexes are likely bogus because you changed the underlying assumptions. And that's exactly correct. If you have one language where 'k' comes before 'm' and another language where it doesn't, what exactly do you expect to happen when you ask for a "sorted" column of strings? Does 'k' come before 'm' or not? You have to pick a set of rules-- i.e. a locale. I suppose you could record the locale for each specific string, essentially extending the "type" of the string to a string-locale. But all that buys you is the ability to group similar locales together, in the same way that SQLite sorts integers, floats, strings, and BLOB types together, sorting them internally but considering them overwise unmixable. You still need to pick some arbitrary ordering of those types, and you're likely to get some very odd results if you do that with locales. Of course, this has nothing to do with Unicode. Unicode is just an encoding system for strings that maps characters-codes to bytes. That's it. What those character-codes represent and the glyph that is associated with them is up to the locale. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > >> Simon Slavin >> wrote: >>> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: >>> Simon Slavin wrote: > * Unicode support from the ground up SQLite already has "unicode support from the ground up". Try using non-Unicode strings and you'll see! >>> >>> SQLite's indexing correctly understands how to order Unicode >>> strings ? >> >> With ICU extension enabled and correct collation specified, yes. Note >> that the correct ordering of Unicode strings is locale-dependent. > > Okay. So I create an indexed database in one locale. I have a > thousand records in there. The indexes are created using the locale I > set. I then send a copy of this database to a client in another > place, and the client has different locale settings. The client adds > another thousand records with their locale settings. What happens > when I use WHERE clauses with '<' or '>' ? Does the system vaguely > work, or does it get a mess ? I'm not sure what you mean by "the client has locale settings". Has them where, and how are these settings supposed to affect SQLite database? When you create a table or an index, you may explicitly specify the collation each field should use. Like this: SELECT icu_load_collation('he_IL', 'hebrew'); create table myTable(myField text collate hebrew); -- and/or create index myIndex on myTable(myField collate hebrew); Using ICU extension does require certain discipline. You must run icu_load_collation soon after opening the database, and all users of the database must agree to map the same identifiers to the same locales (the best way to achieve that is probably to make collation name the same as locale name: SELECT icu_load_collation('he_IL', 'he_IL'); ). Mapping the same collation identifier to different locales may indeed result in corrupted indexes. For more details, see http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote: > Simon Slavin > wrote: >> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: >> >>> Simon Slavin wrote: * Unicode support from the ground up >>> >>> SQLite already has "unicode support from the ground up". Try using >>> non-Unicode strings and you'll see! >> >> SQLite's indexing correctly understands how to order Unicode >> strings ? > > With ICU extension enabled and correct collation specified, yes. Note > that the correct ordering of Unicode strings is locale-dependent. Okay. So I create an indexed database in one locale. I have a thousand records in there. The indexes are created using the locale I set. I then send a copy of this database to a client in another place, and the client has different locale settings. The client adds another thousand records with their locale settings. What happens when I use WHERE clauses with '<' or '>' ? Does the system vaguely work, or does it get a mess ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 18 Sep 2009, at 9:43pm, Noah Hart wrote: > Stored Procedures How do those differ from what can be done with triggers ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On Fri, Sep 18, 2009 at 8:56 PM, Simon Slavin wrote: > So if you had a team of programmers to write something like SQLite > which didn't have the drawbacks SQLite has, which drawbacks would you > identify ? I'm asking not about minor faults with specific SQLite > library calls, but about the sort of things which require rewriting > from the ground up. Not sure whether this would need a rewrite, but for debug purposes I'd love to be able to view the SQL for a prepared statement with its values bound. Hamish ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Noah Hart wrote: > Stored Procedures Stored procedures don't make sense as a core part of SQLite because there is no one solution that fits all. For example what language would you write them in, and how would you deal with security (blindly loading anything that is there wouldn't be a good idea is most situations)? To implement stored procedures, write them in the language of your choice and store them in the database with a schema and form of your own choosing (text/bytecode/binary). If you are using the C api then use sqlite3_auto_extension - http://www.sqlite.org/c3ref/auto_extension.html - to register a callback that is called whenever a new db is opened. In that callback you can then grab the stored procedures out of the database, apply whatever security rules you want and then register/execute them. If you are not using C then most of the language bindings provide a way of doing something substantially similar. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq0CmAACgkQmOOfHg372QSIzACgywwsjWKAaxFISkyxFioNSVom LFgAoN/ywQiICU4bwlUfws2+QY/DZx8z =wdx9 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
StepSqlite brings powerful Stored Procedure support with full power of PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere 'wrapper' so it generates much more efficient code than any wrapper could ever achieve - for instance, it pre-compiles all SQL in your code right when the lib is loaded - no compile-overload at runtime - this is exactly what one expects from a true 'Stored' Procedure. -sk Sent from my iPhone On Sep 18, 2009, at 4:54 PM, Alexey Pechnikov wrote: > Hello! > > On Saturday 19 September 2009 00:43:18 Noah Hart wrote: >> Stored Procedures > > There are Tiny C compiler extension and realization of > stored procedures for SQLite 2 and Lua extension and other. > So you can use one or all of these. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
We don't really need that "SQLite." We already have it. It is commonly called MySQL. It take well over 150MB of disk space and major management efforts to maintain any level of performance. Just what the client/server guys love to play with. SQLite is way too small and Bring to catch their eye. If it ain't big and overly complex it must be a toy. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Simon Slavin Sent: Friday, September 18, 2009 2:56 PM To: General Discussion of SQLite Database Subject: [sqlite] Most wanted features of SQLite ? So if you had a team of programmers to write something like SQLite which didn't have the drawbacks SQLite has, which drawbacks would you identify ? I'm asking not about minor faults with specific SQLite library calls, but about the sort of things which require rewriting from the ground up. The ones that seem to come up most often here are * Some sort of synchronisation support * Support for multiple concurrent clients/processes * Unicode support from the ground up Please note: I am not suggesting that any of these problems are easy to solve. I'm just interested in what problems people want solved. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
> On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > > > Simon Slavin wrote: > >> * Unicode support from the ground up > > > > SQLite already has "unicode support from the ground up". Try using > > non-Unicode strings and you'll see! > > SQLite's indexing correctly understands how to order Unicode > strings ? It can do upper- and lower-case ordering in languages like > Hebrew ? Like he said: > > The issue some developers have is that they also want collations, case > > comparisons etc but are not prepared to use the ICU extension which provides > > about the most correct implementation of those. ie they want something less > > accurate and smaller/quicker. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > >> Simon Slavin wrote: >>> * Unicode support from the ground up >> >> SQLite already has "unicode support from the ground up". Try using >> non-Unicode strings and you'll see! > > SQLite's indexing correctly understands how to order Unicode > strings ? With ICU extension enabled and correct collation specified, yes. Note that the correct ordering of Unicode strings is locale-dependent. > It can do upper- and lower-case ordering in languages like > Hebrew ? To the best of my knowledge, Hebrew doesn't have the notion of case. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Saturday 19 September 2009 00:43:18 Noah Hart wrote: > Stored Procedures There are Tiny C compiler extension and realization of stored procedures for SQLite 2 and Lua extension and other. So you can use one or all of these. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Stored Procedures Noah Simon Slavin-2 wrote: > > So if you had a team of programmers to write something like SQLite > which didn't have the drawbacks SQLite has, which drawbacks would you > identify ? I'm asking not about minor faults with specific SQLite > library calls, but about the sort of things which require rewriting > from the ground up. The ones that seem to come up most often here are > > * Some sort of synchronisation support > * Support for multiple concurrent clients/processes > * Unicode support from the ground up > > Please note: I am not suggesting that any of these problems are easy > to solve. I'm just interested in what problems people want solved. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Most-wanted-features-of-SQLite---tp25514570p25515213.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > Simon Slavin wrote: >> * Unicode support from the ground up > > SQLite already has "unicode support from the ground up". Try using > non-Unicode strings and you'll see! SQLite's indexing correctly understands how to order Unicode strings ? It can do upper- and lower-case ordering in languages like Hebrew ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: > * Unicode support from the ground up SQLite already has "unicode support from the ground up". Try using non-Unicode strings and you'll see! The issue some developers have is that they also want collations, case comparisons etc but are not prepared to use the ICU extension which provides about the most correct implementation of those. ie they want something less accurate and smaller/quicker. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqz6HYACgkQmOOfHg372QQLCgCgnl8qDTaX4GExUyH5hJdCypGL m2wAmwRHO9Qig+yLW8WeORNhogn8sO6R =lEfE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Most wanted features of SQLite ?
So if you had a team of programmers to write something like SQLite which didn't have the drawbacks SQLite has, which drawbacks would you identify ? I'm asking not about minor faults with specific SQLite library calls, but about the sort of things which require rewriting from the ground up. The ones that seem to come up most often here are * Some sort of synchronisation support * Support for multiple concurrent clients/processes * Unicode support from the ground up Please note: I am not suggesting that any of these problems are easy to solve. I'm just interested in what problems people want solved. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users