Re: [sqlite] Mathematical "power" operator?
#ifndef SQLITE_OMIT_STORED_PROC_LANGUAGE --- John Stanton <[EMAIL PROTECTED]> wrote: > Well put. If Sqlite were turned into a junior Oracle, DB2 or PostgreSQL > then someone else would have to create a new Sqlite to handle the > lightweight embedded RDBMS role! #endif How is it different from any other optional build feature? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Yes, any standard SQLite stored proc language is better than no standard language, but Tcl is a hard sell because of [its {syntax}]. I used Tcl on a project several years ago and I still find myself going back to the reference book every time I'm working with arrays, loops and expressions. Also, Tcl is not well supported on Cygwin and a few other non-mainstream platforms. It also seems that the Tcl interpreter library is twice as large as SQLite itself, but perhaps some micro-Tcl build option exists. If you cannot have a PL/SQL or Transact-SQL style language, I'd vote for making Lua the standard SQLite stored proc language. Lua is the SQLite of embeddable scripting languages. The full interpreter is only 150KB. It's very small and fast, has an active user community and is extremely well supported on all sorts of odd hardware and OSes. Nothing is stopping anyone from creating such an extension except for the fact that if it is not in the SQLite distro, it will never be accepted as "the standard". --- Dennis Cote <[EMAIL PROTECTED]> wrote: > Joe Wilson wrote: > > One of the strengths of SQLite is having the flexability to > > hook it up to any language you like. But it would make life easier > > for everyone if there was some sort of standard built-in simple > > scripting language to create stored procedures and custom functions > > within the standard SQLite distro to avoid exactly this sort of > > compatability problem. Maybe some sort of simplififed PL/SQL or > > Transact/SQL with the scripts autoloaded from an sqlite system table. > > Or perhaps a standard SQLite convention to register a language with > > it and have stored procs and functions of that language autoload > > when the database is first attached. Anyone could easily write such > > an extension, but having it as a standard in the distro is the key. > > > > > Joe, > > I agree that having the ability to store procedures in the database > itself would be a great addition to sqlite. And, it would have to be > part of the standard distribution to be effective. > > I think that it is unlikely to happen for a couple of reasons. > > First, it would increase the size of the sqlite library and since many > of Richard paying customers are very concerned about the library > footprint, they would view this as a step backwards (but it could be > done as a compile time option that they could leave out when building > their minimalist versions). > > Second, I think Richard sees sqlite as a library that users should be > using from a scripting language, in particular TCL, rather than a > standalone database engine that should contain its own scripting > environment. If we all switch to TCL, which is widely available for most > platforms, then you can store your scripts in the database and execute > them as needed. These scripts can also create user defined functions. > There are more details on these ideas at > http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
On 12/22/06, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Thanks for the nudge. I never thought to check for the external DLL, but checking my SQLite Expert installation folder I see... sqlite3.dll Interestingly, the DLL has the same size and date/time stamp as the one that's available for download on the sqlite.org download page. Well, then you just need to modify src/func.c [1] so that it also "loads" your power function on startup. Maybe looking into how round() is implemented will help you start. Regards, ~Nuno Lucas [1] http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/func.c&v=1.134 Hmmm ;^) Jeff - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
- Original Message - From: "Nuno Lucas" <[EMAIL PROTECTED]> To: Sent: Thursday, December 21, 2006 5:45 PM Subject: Re: [sqlite] Mathematical "power" operator? Just wanted to add that if one of the sqlite GUI's out there uses an external sqlite dll (instead of static linking) it's quite easy to make your own dll with your own functions and replace the one that comes with the GUI application. Nuno, Thanks for the nudge. I never thought to check for the external DLL, but checking my SQLite Expert installation folder I see... sqlite3.dll Interestingly, the DLL has the same size and date/time stamp as the one that's available for download on the sqlite.org download page. Hmmm ;^) Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Tom Briggs wrote: If you want the Super SQLite GUI Tool and Swiss Army Knife 3000 to have a POWER function, contact the maintainer of said tool - it's an application problem. If you want your application to have the POWER function, roll your own build of SQLite. Those are application problems, not database problems. But they very quickly become a database problem if you use an user defined function in your database (i.e. in a trigger or view definition). The reference to the function is saved in the database, but the implementation is not. If you transport your database to a different platform (i.e. Win to Linux) or try to use a different application to access the database (perhaps the sqlite shell) your perfectly valid database won't operate correctly anymore. This basic problem is what prevents user defined extensions from being widely applied. User defined functions and collations can only be used effectively from one application. As a result the things you can do to that application's database files are limited to what that application implements (i.e. you lose the ability to do general browsing or one time maintenance changes using a general purpose database browser utility). If you need the the extra functionality you can use a user defined function, if you need to have ad-hoc access to the database you probably shouldn't use any user defined functions. Your suggestion above points out one of the problems of non-standard extensions. If I develop a power function (say one that calculates electrical power by multiplying two arguments, a voltage and current) and a GUI browser developer adds a different power function with the same name but which does exponentiation, their function will be used by my database triggers and views without complaint. And quite frankly I think the real problem in your example is static linking - If a 3rd party tool links in SQLite statically, then you're basically stuck with whatever version they choose, with whatever extensions they add - in other words, you're using *their* embedded database, not SQLite. Which is probably as it should be, but one shouldn't expect SQLite to solve problems for said application. I really don't see your point here. If the maintainer releases a new version statically linked to the latest SQLite version the day after it is released (which is often the case), then it is no different than using the sqlite shell (though it may be prettier and provide additional features). There are no problems with the said application. is the POWER function (or operator) standard SQL? No, to the best of my knowledge there is no power function in standard SQL. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Just wanted to add that if one of the sqlite GUI's out there uses an external sqlite dll (instead of static linking) it's quite easy to make your own dll with your own functions and replace the one that comes with the GUI application. This was discussed before, but don't know if any one of them actually does this (but an open source one will be easy to recompile that way). Regards, ~Nuno Lucas On 12/21/06, Jeff Godfrey <[EMAIL PROTECTED]> wrote: All, Thanks for the interesting responses. I think I now have a clear understanding of my options, and while not exactly what I was looking for, I can work within the prescribed limitations. As I mentioned, I am working from Tcl, where it's quite easy to write and register a new function with SQLite - which I've done. So, I now have a view containing references to my new "pow" (mathematical power) function, which works exacty as expected from within my own application. Unfortunately, that view now (obviously) causes problems when using 3rd party GUI db manager tools. From here, I think it's just a matter of reorienting my take on the whole problem. As someone already mentioned, SQLite is not intended to be a stand-alone database application. Once I get that fact drilled into my head, I'll be fine... ;^) I will mention that the author of "SQLite Expert" is looking into possible solutions to this specific issue (related to using the new "loadable extensions" feature of SQLite). If anything interesting comes from those discussions, I'll let the list know. Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical 'power' operator?
We have a rule which gets anyone who says "it would be easiest to..." is sent to the sin bin until they learn to way "the best way to do this is ...". The best way generally turns out to be the easiest, but the easiest never turns out to be the best. Tom Briggs wrote: I'm sure that anything is possible, but I do not think that would be a practical approach. :) What seems to "easiest" is relative - I find working directly with the SQLite codebase very, very easy. But I'm a C guy. The whole Tcl thing gives me the willies. So I wouldn't generalize things according to language anyway. -Tom -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 21, 2006 12:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Mathematical 'power' operator? Maybe a dumb queston, but: As it looks it is easiest to work with SQLite from Tcl, is it possible to code in Tcl and call that from VB/VBA? RBS Well put. If Sqlite were turned into a junior Oracle, DB2 or PostgreSQL then someone else would have to create a new Sqlite to handle the lightweight embedded RDBMS role! It is very simple to add functions to Sqlite, and since it is a library you link into your application there is no reason not to have your own Sqlite-local library which adds all the functions needed by your application. Many of the features people want to add to Sqlite are better added by the addition of a specifically targetted application layer. Those persons wanting the simplicity of Sqlite and all the functionality of PostgrSQL might do better to re-assess their goals and save time by using PostgreSQL and coming to terms the fact that the extra complexity is the price to pay for the added functionality. In our applications we have done just that and have the advantage of simple SQL, excellent performance and small footprint in our distributed applications. We use PostgreSQL where its enterprise features are necessary to handle large numbers of concurrent users. We thereby avoid underkill and overkill. The add-on functions, and application interfaces are better being contributed software than to bloat Sqlite distributions and be a boat anchor on its continued development. Tom Briggs wrote: In the case of SQLite, I (arguably) have to use a 3rd party management tool, for which my custom functions are no longer available. I'm curious how others handle this. A. You don't need or use any custom SQL functionality B. You don't use a 3rd party SQLite management tool C. Something else I haven't thought of? I think that the key point you're missing here is that SQLite is not intended to be standalone database system like the other products you mentioned (Access, Oracle, etc.) - it is an embeddable database library. It happens to have a convenient command line interface that allows it to be used as a standalone database, but that's just a shell (pun intended) that allows you to get to the library itself. The 3rd party "front ends" to which you refer are really application consumers of SQLite itself - not add-ons to or features of SQLite. In other words: it's a development tool, not a database. Now, as for a "power" function: we had exactly the same need when we first started using SQLite. Our solution: we added it. The source code is freely available, after all. Adding a new function to the code is shockingly straightforward; from there you simply compile your version of the library and use that in your application(s). Quick, simple and portable, both across platforms and applications using your version of the library. -Tom -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Mathematical "power" operator?
> This is quick simple and portable to your application only. > You can not > use a 3rd party GUI database browser that is statically linked to a > different SQLite library (such as the standard distribution) > to view or > modify your database. You must add specific support for any desired > operation to your application (or turn your application into > another GUI > database browser to be able to do everything the 3rd party > tools can do). To quote a local radio DJ: You're making my point! If you want the Super SQLite GUI Tool and Swiss Army Knife 3000 to have a POWER function, contact the maintainer of said tool - it's an application problem. If you want your application to have the POWER function, roll your own build of SQLite. Those are application problems, not database problems. And quite frankly I think the real problem in your example is static linking - If a 3rd party tool links in SQLite statically, then you're basically stuck with whatever version they choose, with whatever extensions they add - in other words, you're using *their* embedded database, not SQLite. Which is probably as it should be, but one shouldn't expect SQLite to solve problems for said application. One other thing I just thought of: is the POWER function (or operator) standard SQL? I'm honestly not sure, off the top of my head, but I'm guessing that that factors in here somewhere. -Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Mathematical 'power' operator?
I'm sure that anything is possible, but I do not think that would be a practical approach. :) What seems to "easiest" is relative - I find working directly with the SQLite codebase very, very easy. But I'm a C guy. The whole Tcl thing gives me the willies. So I wouldn't generalize things according to language anyway. -Tom > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 21, 2006 12:56 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Mathematical 'power' operator? > > Maybe a dumb queston, but: > As it looks it is easiest to work with SQLite from Tcl, is it possible > to code in Tcl and call that from VB/VBA? > > RBS > > > Well put. If Sqlite were turned into a junior Oracle, DB2 > or PostgreSQL > > then someone else would have to create a new Sqlite to handle the > > lightweight embedded RDBMS role! > > > > It is very simple to add functions to Sqlite, and since it > is a library > > you link into your application there is no reason not to > have your own > > Sqlite-local library which adds all the functions needed by your > > application. Many of the features people want to add to Sqlite are > > better added by the addition of a specifically targetted application > > layer. > > > > Those persons wanting the simplicity of Sqlite and all the > functionality > > of PostgrSQL might do better to re-assess their goals and > save time by > > using PostgreSQL and coming to terms the fact that the > extra complexity > > is the price to pay for the added functionality. > > > > In our applications we have done just that and have the advantage of > > simple SQL, excellent performance and small footprint in > our distributed > > applications. We use PostgreSQL where its enterprise features are > > necessary to handle large numbers of concurrent users. We > thereby avoid > > underkill and overkill. > > > > The add-on functions, and application interfaces are better being > > contributed software than to bloat Sqlite distributions and > be a boat > > anchor on its continued development. > > > > Tom Briggs wrote: > >> > >> > >> > >>>In the case of SQLite, I (arguably) have to use a 3rd party > >>>management > >>>tool, for which my custom functions are no longer available. I'm > >>>curious how others handle this. > >>> > >>>A. You don't need or use any custom SQL functionality > >>>B. You don't use a 3rd party SQLite management tool > >>>C. Something else I haven't thought of? > >> > >> > >>I think that the key point you're missing here is that > SQLite is not > >> intended to be standalone database system like the other > products you > >> mentioned (Access, Oracle, etc.) - it is an embeddable > database library. > >> It happens to have a convenient command line interface > that allows it to > >> be used as a standalone database, but that's just a shell > (pun intended) > >> that allows you to get to the library itself. The 3rd party "front > >> ends" to which you refer are really application consumers of SQLite > >> itself - not add-ons to or features of SQLite. In other > words: it's a > >> development tool, not a database. > >> > >>Now, as for a "power" function: we had exactly the same > need when we > >> first started using SQLite. Our solution: we added it. > The source code > >> is freely available, after all. Adding a new function to > the code is > >> shockingly straightforward; from there you simply compile > your version > >> of the library and use that in your application(s). > Quick, simple and > >> portable, both across platforms and applications using > your version of > >> the library. > >> > >>-Tom > >> > >> > >> > >> > >> > -- > --- > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> > -- > --- > >> > > > > > > > -- > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > > -- > --- > > > > > > > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical 'power' operator?
Maybe a dumb queston, but: As it looks it is easiest to work with SQLite from Tcl, is it possible to code in Tcl and call that from VB/VBA? RBS > Well put. If Sqlite were turned into a junior Oracle, DB2 or PostgreSQL > then someone else would have to create a new Sqlite to handle the > lightweight embedded RDBMS role! > > It is very simple to add functions to Sqlite, and since it is a library > you link into your application there is no reason not to have your own > Sqlite-local library which adds all the functions needed by your > application. Many of the features people want to add to Sqlite are > better added by the addition of a specifically targetted application > layer. > > Those persons wanting the simplicity of Sqlite and all the functionality > of PostgrSQL might do better to re-assess their goals and save time by > using PostgreSQL and coming to terms the fact that the extra complexity > is the price to pay for the added functionality. > > In our applications we have done just that and have the advantage of > simple SQL, excellent performance and small footprint in our distributed > applications. We use PostgreSQL where its enterprise features are > necessary to handle large numbers of concurrent users. We thereby avoid > underkill and overkill. > > The add-on functions, and application interfaces are better being > contributed software than to bloat Sqlite distributions and be a boat > anchor on its continued development. > > Tom Briggs wrote: >> >> >> >>>In the case of SQLite, I (arguably) have to use a 3rd party >>>management >>>tool, for which my custom functions are no longer available. I'm >>>curious how others handle this. >>> >>>A. You don't need or use any custom SQL functionality >>>B. You don't use a 3rd party SQLite management tool >>>C. Something else I haven't thought of? >> >> >>I think that the key point you're missing here is that SQLite is not >> intended to be standalone database system like the other products you >> mentioned (Access, Oracle, etc.) - it is an embeddable database library. >> It happens to have a convenient command line interface that allows it to >> be used as a standalone database, but that's just a shell (pun intended) >> that allows you to get to the library itself. The 3rd party "front >> ends" to which you refer are really application consumers of SQLite >> itself - not add-ons to or features of SQLite. In other words: it's a >> development tool, not a database. >> >>Now, as for a "power" function: we had exactly the same need when we >> first started using SQLite. Our solution: we added it. The source code >> is freely available, after all. Adding a new function to the code is >> shockingly straightforward; from there you simply compile your version >> of the library and use that in your application(s). Quick, simple and >> portable, both across platforms and applications using your version of >> the library. >> >>-Tom >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Well put. If Sqlite were turned into a junior Oracle, DB2 or PostgreSQL then someone else would have to create a new Sqlite to handle the lightweight embedded RDBMS role! It is very simple to add functions to Sqlite, and since it is a library you link into your application there is no reason not to have your own Sqlite-local library which adds all the functions needed by your application. Many of the features people want to add to Sqlite are better added by the addition of a specifically targetted application layer. Those persons wanting the simplicity of Sqlite and all the functionality of PostgrSQL might do better to re-assess their goals and save time by using PostgreSQL and coming to terms the fact that the extra complexity is the price to pay for the added functionality. In our applications we have done just that and have the advantage of simple SQL, excellent performance and small footprint in our distributed applications. We use PostgreSQL where its enterprise features are necessary to handle large numbers of concurrent users. We thereby avoid underkill and overkill. The add-on functions, and application interfaces are better being contributed software than to bloat Sqlite distributions and be a boat anchor on its continued development. Tom Briggs wrote: In the case of SQLite, I (arguably) have to use a 3rd party management tool, for which my custom functions are no longer available. I'm curious how others handle this. A. You don't need or use any custom SQL functionality B. You don't use a 3rd party SQLite management tool C. Something else I haven't thought of? I think that the key point you're missing here is that SQLite is not intended to be standalone database system like the other products you mentioned (Access, Oracle, etc.) - it is an embeddable database library. It happens to have a convenient command line interface that allows it to be used as a standalone database, but that's just a shell (pun intended) that allows you to get to the library itself. The 3rd party "front ends" to which you refer are really application consumers of SQLite itself - not add-ons to or features of SQLite. In other words: it's a development tool, not a database. Now, as for a "power" function: we had exactly the same need when we first started using SQLite. Our solution: we added it. The source code is freely available, after all. Adding a new function to the code is shockingly straightforward; from there you simply compile your version of the library and use that in your application(s). Quick, simple and portable, both across platforms and applications using your version of the library. -Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
All, Thanks for the interesting responses. I think I now have a clear understanding of my options, and while not exactly what I was looking for, I can work within the prescribed limitations. As I mentioned, I am working from Tcl, where it's quite easy to write and register a new function with SQLite - which I've done. So, I now have a view containing references to my new "pow" (mathematical power) function, which works exacty as expected from within my own application. Unfortunately, that view now (obviously) causes problems when using 3rd party GUI db manager tools. From here, I think it's just a matter of reorienting my take on the whole problem. As someone already mentioned, SQLite is not intended to be a stand-alone database application. Once I get that fact drilled into my head, I'll be fine... ;^) I will mention that the author of "SQLite Expert" is looking into possible solutions to this specific issue (related to using the new "loadable extensions" feature of SQLite). If anything interesting comes from those discussions, I'll let the list know. Thanks again. Jeff - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Joe Wilson wrote: One of the strengths of SQLite is having the flexability to hook it up to any language you like. But it would make life easier for everyone if there was some sort of standard built-in simple scripting language to create stored procedures and custom functions within the standard SQLite distro to avoid exactly this sort of compatability problem. Maybe some sort of simplififed PL/SQL or Transact/SQL with the scripts autoloaded from an sqlite system table. Or perhaps a standard SQLite convention to register a language with it and have stored procs and functions of that language autoload when the database is first attached. Anyone could easily write such an extension, but having it as a standard in the distro is the key. Joe, I agree that having the ability to store procedures in the database itself would be a great addition to sqlite. And, it would have to be part of the standard distribution to be effective. I think that it is unlikely to happen for a couple of reasons. First, it would increase the size of the sqlite library and since many of Richard paying customers are very concerned about the library footprint, they would view this as a step backwards (but it could be done as a compile time option that they could leave out when building their minimalist versions). Second, I think Richard sees sqlite as a library that users should be using from a scripting language, in particular TCL, rather than a standalone database engine that should contain its own scripting environment. If we all switch to TCL, which is widely available for most platforms, then you can store your scripts in the database and execute them as needed. These scripts can also create user defined functions. There are more details on these ideas at http://www.tcl.tk/community/tcl2004/Papers/D.RichardHipp/drh.html Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Tom Briggs wrote: Now, as for a "power" function: we had exactly the same need when we first started using SQLite. Our solution: we added it. The source code is freely available, after all. Adding a new function to the code is shockingly straightforward; from there you simply compile your version of the library and use that in your application(s). Quick, simple and portable, both across platforms and applications using your version of the library. Tom, This is quick simple and portable to your application only. You can not use a 3rd party GUI database browser that is statically linked to a different SQLite library (such as the standard distribution) to view or modify your database. You must add specific support for any desired operation to your application (or turn your application into another GUI database browser to be able to do everything the 3rd party tools can do). Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
mail_ma wrote: > I set the maillist is every one to send me ,so many mail is not good, > I want get a archive,what URL i can cancel it ? > please help me ??? > > > See the mailing list instructions at the top of http://www.sqlite.org/support.html HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
mail_ma wrote: > I set the maillist is every one to send me ,so many mail is not good, > I want get a archive,what URL i can cancel it ? > please help me ??? > Not sure what you're asking here. If you want to remove yourself from the list send an email to mailto:[EMAIL PROTECTED] If you want an archive of previous postings point a news reader at nntp://news.gmane.org/gmane.comp.db.sqlite.general Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re: [sqlite] Mathematical "power" operator?
I set the maillist is every one to send me ,so many mail is not good, I want get a archive,what URL i can cancel it ? please help me ??? mail_ma 2006-12-21 发件人: Jeff Godfrey 发送时间: 2006-12-20 05:47:20 收件人: sqlite-users@sqlite.org 抄送: 主题: Re: [sqlite] Mathematical "power" operator? Jes�s, Thanks for the response. As I mentioned in my original message, it seems that I can create my own replacement functions and register them with SQLite via the provided "function" method. While I assume that'll solve the issue when running my code, how does one go about using views containing custom functions in 3rd party tools (in my case "SQLite Expert Personal")? I assume the answer is " you don't, unless the 3rd part tool somehow allows for the inclusion of user-defined functions". I want to have my cake and eat it too... ;^) Now, I guess that brings me back to my initial thought, which was... With all the fancy functionality that's apparently supported by SQLite expressions, isn't the absence of a basic mathematical "power" operator a glaring omission? It seems to me that it is, though I admittedly have very little experience in this domain. Again, thanks for your input. Jeff - Original Message - From: "Jes�s L�pez" <[EMAIL PROTECTED] > To: Sent: Tuesday, December 19, 2006 3:08 PM Subject: RE: [sqlite] Mathematical "power" operator? Hi Jeff, I'm not a Tcl user. I use C# and Robert Simpson's SQLite ADO.NET 2.0 provider to access SQLite databases. SQLite allows you to define both scalar and aggregate user defined functions. I think the way to approach your issue is to define a scalar user defined function. I don't know how to do it with Tcl, I just know how to do it with c#. Regards: Jes�s L�pez -Mensaje original- De: Jeff Godfrey [mailto:[EMAIL PROTECTED] Enviado el: martes, 19 de diciembre de 2006 21:45 Para: sqlite-users@sqlite.org Asunto: [sqlite] Mathematical "power" operator? Hi All, New to SQLite, so bear with me... ;^) I'm trying to migrate an MS-Access database over to SQLite. I have a VIEW created from a SELECT statement that uses the mathematical "power" operator ("^") for both "square root" and "squared" operations. It seems that SQLite doesn't support the "^" operator, so I'm trying to find the best way around that. I'm using SQLite from Tcl. I know that I can create my own Tcl-based replacements for these functions and register them with SQLite via the "function" method, though that still seems to leave an issue. I am using a 3rd part tool (SQLite Expert Personal) to create, manipulate, and generally experiment with my SQLite database. Obviously, if I create a Tcl-based function replacement, I can use it from within my code, but it won't be recognized when I open up the VIEW query that uses it via the 3rd part tool. Also, I can fairly easily change the query to get by without the need for the "squared" function, though the "square root" function would seem to be a bit more tricky to "code around". So, a few questions: 1. Thoughts on my specific issue with the missing mathematical operator? 2. More generally, do people who "add" functions to SQLite just not use 3rd party tools to work with their data? Thanks for any insight... Jeff Godfrey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Mathematical "power" operator?
> In the case of SQLite, I (arguably) have to use a 3rd party > management > tool, for which my custom functions are no longer available. I'm > curious how others handle this. > > A. You don't need or use any custom SQL functionality > B. You don't use a 3rd party SQLite management tool > C. Something else I haven't thought of? I think that the key point you're missing here is that SQLite is not intended to be standalone database system like the other products you mentioned (Access, Oracle, etc.) - it is an embeddable database library. It happens to have a convenient command line interface that allows it to be used as a standalone database, but that's just a shell (pun intended) that allows you to get to the library itself. The 3rd party "front ends" to which you refer are really application consumers of SQLite itself - not add-ons to or features of SQLite. In other words: it's a development tool, not a database. Now, as for a "power" function: we had exactly the same need when we first started using SQLite. Our solution: we added it. The source code is freely available, after all. Adding a new function to the code is shockingly straightforward; from there you simply compile your version of the library and use that in your application(s). Quick, simple and portable, both across platforms and applications using your version of the library. -Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
One of the strengths of SQLite is having the flexability to hook it up to any language you like. But it would make life easier for everyone if there was some sort of standard built-in simple scripting language to create stored procedures and custom functions within the standard SQLite distro to avoid exactly this sort of compatability problem. Maybe some sort of simplififed PL/SQL or Transact/SQL with the scripts autoloaded from an sqlite system table. Or perhaps a standard SQLite convention to register a language with it and have stored procs and functions of that language autoload when the database is first attached. Anyone could easily write such an extension, but having it as a standard in the distro is the key. --- Dennis Cote <[EMAIL PROTECTED]> wrote: > Jeff Godfrey wrote: > > I'm curious how others handle this. > > > > A. You don't need or use any custom SQL functionality > > B. You don't use a 3rd party SQLite management tool > > C. Something else I haven't thought of? > > > Jeff, > > I think it is mostly A, or B, or at least it alternates between A and B > depending on the database. > > Part of the problem is that the loadable extensions are not portable > between platforms, so you can't count on having an extension available. > Without certainty that the extensions will be available, you can't use > them in the schema of your database (like trigger statements or views). > > For a custom application you can add code that creates the required > extensions when you open the database, so they will always be available > from within that application, but that precludes the use of 3rd party > tools since there is no standard way to tell a 3rd party tool which > extensions it should load when it opens a database. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Jeff Godfrey wrote: I'm curious how others handle this. A. You don't need or use any custom SQL functionality B. You don't use a 3rd party SQLite management tool C. Something else I haven't thought of? Jeff, I think it is mostly A, or B, or at least it alternates between A and B depending on the database. Part of the problem is that the loadable extensions are not portable between platforms, so you can't count on having an extension available. Without certainty that the extensions will be available, you can't use them in the schema of your database (like trigger statements or views). For a custom application you can add code that creates the required extensions when you open the database, so they will always be available from within that application, but that precludes the use of 3rd party tools since there is no standard way to tell a 3rd party tool which extensions it should load when it opens a database. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Julien, Thanks for the pointer. That does indeed look interesting, though I'm not sure it completely solves my problem (though, there may be no good solution to my problem). It would seem that, using the "loadable extension" feature, I could easily create a compiled version of my necessary "power" function. Further, it would seem my new function could be loaded via a SQL statement, which *might* make it available in a 3rd party GUI package that's compiled with the standard SQLite library. That said, there seems to be one potential hiccup... According to the mentioned docs, this functionality is turned *off* by default in the library, as there are some potential security concerns. So, unless the 3rd party tool is compiled with this functionality turned *on*, I don't see that I could load my function there anyway. There may be other things that would prevent me from loading an external function into a 3rd party app also - I don't know. So, I've opened a discussion with the author of my chosen SQLite GUI tool (SQLite Expert Personal) to see what my options are. Anyway, thanks for pointing me to another possible option. As I said earlier, I'm very new to SQLite. Most of my database work (though not extensive anyway) has been with commercial packages (MS Access, Oracle, SQL Server, etc). In those environments (generally), since the package already *has* a GUI-based management tool built-in, any custom SQL functions that are created are accessible to the GUI tool by default. In the case of SQLite, I (arguably) have to use a 3rd party management tool, for which my custom functions are no longer available. I'm curious how others handle this. A. You don't need or use any custom SQL functionality B. You don't use a 3rd party SQLite management tool C. Something else I haven't thought of? Thanks for any additional guidance. Jeff Godfrey - Original Message - From: "jt" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 20, 2006 9:23 AM Subject: Re: [sqlite] Mathematical "power" operator? Hi, Look at the new "loadable extension" feature: http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions It also describe an example that could be really easy to convert to your sqrt problem. -- Julien - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Hi, Look at the new "loadable extension" feature: http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions It also describe an example that could be really easy to convert to your sqrt problem. -- Julien - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical "power" operator?
Jesús, Thanks for the response. As I mentioned in my original message, it seems that I can create my own replacement functions and register them with SQLite via the provided "function" method. While I assume that'll solve the issue when running my code, how does one go about using views containing custom functions in 3rd party tools (in my case "SQLite Expert Personal")? I assume the answer is " you don't, unless the 3rd part tool somehow allows for the inclusion of user-defined functions". I want to have my cake and eat it too... ;^) Now, I guess that brings me back to my initial thought, which was... With all the fancy functionality that's apparently supported by SQLite expressions, isn't the absence of a basic mathematical "power" operator a glaring omission? It seems to me that it is, though I admittedly have very little experience in this domain. Again, thanks for your input. Jeff - Original Message - From: "Jesús López" <[EMAIL PROTECTED]> To: Sent: Tuesday, December 19, 2006 3:08 PM Subject: RE: [sqlite] Mathematical "power" operator? Hi Jeff, I'm not a Tcl user. I use C# and Robert Simpson's SQLite ADO.NET 2.0 provider to access SQLite databases. SQLite allows you to define both scalar and aggregate user defined functions. I think the way to approach your issue is to define a scalar user defined function. I don't know how to do it with Tcl, I just know how to do it with c#. Regards: Jesús López -Mensaje original- De: Jeff Godfrey [mailto:[EMAIL PROTECTED] Enviado el: martes, 19 de diciembre de 2006 21:45 Para: sqlite-users@sqlite.org Asunto: [sqlite] Mathematical "power" operator? Hi All, New to SQLite, so bear with me... ;^) I'm trying to migrate an MS-Access database over to SQLite. I have a VIEW created from a SELECT statement that uses the mathematical "power" operator ("^") for both "square root" and "squared" operations. It seems that SQLite doesn't support the "^" operator, so I'm trying to find the best way around that. I'm using SQLite from Tcl. I know that I can create my own Tcl-based replacements for these functions and register them with SQLite via the "function" method, though that still seems to leave an issue. I am using a 3rd part tool (SQLite Expert Personal) to create, manipulate, and generally experiment with my SQLite database. Obviously, if I create a Tcl-based function replacement, I can use it from within my code, but it won't be recognized when I open up the VIEW query that uses it via the 3rd part tool. Also, I can fairly easily change the query to get by without the need for the "squared" function, though the "square root" function would seem to be a bit more tricky to "code around". So, a few questions: 1. Thoughts on my specific issue with the missing mathematical operator? 2. More generally, do people who "add" functions to SQLite just not use 3rd party tools to work with their data? Thanks for any insight... Jeff Godfrey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Mathematical "power" operator?
Hi Jeff, I'm not a Tcl user. I use C# and Robert Simpson's SQLite ADO.NET 2.0 provider to access SQLite databases. SQLite allows you to define both scalar and aggregate user defined functions. I think the way to approach your issue is to define a scalar user defined function. I don't know how to do it with Tcl, I just know how to do it with c#. Regards: Jesús López -Mensaje original- De: Jeff Godfrey [mailto:[EMAIL PROTECTED] Enviado el: martes, 19 de diciembre de 2006 21:45 Para: sqlite-users@sqlite.org Asunto: [sqlite] Mathematical "power" operator? Hi All, New to SQLite, so bear with me... ;^) I'm trying to migrate an MS-Access database over to SQLite. I have a VIEW created from a SELECT statement that uses the mathematical "power" operator ("^") for both "square root" and "squared" operations. It seems that SQLite doesn't support the "^" operator, so I'm trying to find the best way around that. I'm using SQLite from Tcl. I know that I can create my own Tcl-based replacements for these functions and register them with SQLite via the "function" method, though that still seems to leave an issue. I am using a 3rd part tool (SQLite Expert Personal) to create, manipulate, and generally experiment with my SQLite database. Obviously, if I create a Tcl-based function replacement, I can use it from within my code, but it won't be recognized when I open up the VIEW query that uses it via the 3rd part tool. Also, I can fairly easily change the query to get by without the need for the "squared" function, though the "square root" function would seem to be a bit more tricky to "code around". So, a few questions: 1. Thoughts on my specific issue with the missing mathematical operator? 2. More generally, do people who "add" functions to SQLite just not use 3rd party tools to work with their data? Thanks for any insight... Jeff Godfrey - To unsubscribe, send email to [EMAIL PROTECTED] -