Jump to content
Sign in to follow this  
timbo

Creating SQLite User Defined Function

Recommended Posts

timbo

Hello All,

I've recently discovered the extremely useful SQLite functionality in AutoIt, but I'm now stumped when trying to create a User Defined Function in SQLite.

You may say I could create an AutoIt function which makes SQL queries and manipulates the data as needed, but I think a native SQL function would be significantly quicker.

I have looked through the AutoIt manual and the SQLite manual bundled with the AutoIt install, but I cannot find anything referring to creating a user defined function?

I'm I missing something, or is this feature not a necessity?

Many Thanks!

-Timbo

Share this post


Link to post
Share on other sites
PsaltyDS

Hello All,

I've recently discovered the extremely useful SQLite functionality in AutoIt, but I'm now stumped when trying to create a User Defined Function in SQLite.

You may say I could create an AutoIt function which makes SQL queries and manipulates the data as needed, but I think a native SQL function would be significantly quicker.

I have looked through the AutoIt manual and the SQLite manual bundled with the AutoIt install, but I cannot find anything referring to creating a user defined function?

I'm I missing something, or is this feature not a necessity?

Many Thanks!

-Timbo

Just put your code inside Func/EndFunc tags and call the function. If you have multiple such functions to include in multiple files, save them to and .au3 file and then just use #include to add them to any script. That's a UDF.

:mellow:

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
timbo

Thanks for the reply PsaltyDS, but that's not the UDF I was looking for.

Sorry it's taken me so long to reply. I got sick and the problem was null and void by the time I got back to work (and as a result, I completely forgot about my post).

Anyway, when programming in SQL you can usually create UDF's in the SQL server which will run during an SQL query. I was thinking if I could build a particular function into the SQLite (server instance), then it may be able to execute the function quicker than executing an SQL query, getting the results, performing the calculation, then building another SQL query based on the results. Best practice would be to build an SQL function to perform all that in one query.

I would still be interested in an answer, but it is no longer a pressing matter.

-Timbo

Share this post


Link to post
Share on other sites
PsaltyDS

Thanks for the reply PsaltyDS, but that's not the UDF I was looking for.

Sorry it's taken me so long to reply. I got sick and the problem was null and void by the time I got back to work (and as a result, I completely forgot about my post).

Anyway, when programming in SQL you can usually create UDF's in the SQL server which will run during an SQL query. I was thinking if I could build a particular function into the SQLite (server instance), then it may be able to execute the function quicker than executing an SQL query, getting the results, performing the calculation, then building another SQL query based on the results. Best practice would be to build an SQL function to perform all that in one query.

I would still be interested in an answer, but it is no longer a pressing matter.

-Timbo

You are talking about stored procedures, but SQLite() doesn't have those because they depend on a server service to handle them. There is essentially no such thing as an "SQLite Server", because each client's process is all there is. All the SQLite.au3 UDF is doing is making calls to the SQLite3.dll. There is not server being connected to, and no stored procedures to be accessed.

From the horse's mouth: Appropriate Uses For SQLite

Simplicity in a database engine can be either a strength or a weakness, depending on what you are trying to do. In order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth. If you need some of these features and do not mind the added complexity that they bring, then SQLite is probably not the database for you. SQLite is not intended to be an enterprise database engine. It is not designed to compete with Oracle or PostgreSQL.

In the AutoIt environment with SQLite, you implement stored procedures with your own functions as I described above. Nothing to prevent you from naming your functions things like "sp_AddUserToTable" though.

:mellow:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
timbo

I see...

Yeah, I probably should have said Stored Procedure but I thought SQLite called them User Defined Functions; apparently not.

Thanks for finding that though; I dug through soo many pages looking for an explanation but I guess your Googling skills are superior to mine.

Also, I think SQLite's reasoning is spot on, what I was trying to do with SQLite was much better suited to an SQL Server (MS SQL in this instance).

Many Thanks!

-Timbo

Share this post


Link to post
Share on other sites
BugFix

Sorry that i reactivate this thread after a long time.

But i've the same problem like timbo. And it must be possible to write user defind functions inside SQLite.

What sense has otherwise this:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator.

Best Regards BugFix  

Share this post


Link to post
Share on other sites
H1T1

Sorry that i reactivate this thread after a long time.

But i've the same problem like timbo. And it must be possible to write user defind functions inside SQLite.

What sense has otherwise this:

I have been looking at the same think and tough it was not possible until I installed :SQLite Manager on Firefox and saw that user-defined function can be created... I hope there would be away to do this with Autoit.

Share this post


Link to post
Share on other sites
PsaltyDS

@BugFix: There is a function sqlite3_create_function(), and sqlite3_create_function16(). The first uses UTF-8 encoding and the second UTF-16.

If this function can be called from the DLL interface (I don't know), then perhaps you could define your local AutoIt function as a callback with DllCallbackRegister(), and maybe pass a pointer to it from DllCallbackGetPtr() to the sqlite3_create_function() in SQLite3.dll. Big maybe.

Even if you did (or could do) all that, you would have created a run-time link to a local script function. The procedure would not have been stored in the database for use by other clients.

I think what you are describing is intended for languages like C++ where SQLite is included in the source as a library, not via the DLL API. But I couldn't prove it, as this is getting decidedly over my head. :mellow:

@H1T1: I'm not up to digging into the source of SQLite Manager for Firefox to see how the SQLite library was implemented for it. Wouldn't be via DLL would it? If you create a user-defined SQLite function in SQLite Manager on Firefox, is that procedure then stored in the database file where another client could find and use it? Or is it a local creation in your own client instance that only you can use?

Caveat: I'm in over my head here. :lol:

:(


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
jchd

Well, this thread comes right out of the grave, but anyway here's some of the information you're after.

What SQLite calls UDFs are SQLite extensions. It isn't possible to have SQLite extensions writen in Autoit and even if it would be possible, it would take ages for about anything.

AutoIt isn't practical for this.

SQlite extensions are written in C, or at least have a C-style interface. To preserve efficiency, you need to code them with care, or the performance is likely to suffer dramatically.

There are a number of types for these add-ons. An extension can be a VFS (virtual file system) to provide low-level access to any data storage form not supported by the standard SQLite (e.g. a VFS to read/write .CSV files, or to allow porting SQLite to a bizarre filesystem). Other extensions offer new functions that you can use in your SQL statements (scalar functions or collations). Yet other extensions bring enhanced charset support into SQLite (the most known is ICU). The possibilities are endless and that's why SQLite can be used successfully in that much different configurations like many (if not most) cellphones or datacenters with giant databases (up to 500Gb for some versions!).

I've written a number of scalar and collation functions in several extensions. I had the need to handle a significant range of Unicode characters and I found ICU to be a memory and cycles hog. Thus I decided to write my own Unicode support for string operations (casing, unaccenting, collation, ...), a function able to perform a fuzzy string compare and also wrote a Unicode locale-independant collation. I use these extensions daily for business-critical applications and I know people are using them as well in various countries worldwide with no problem.

So writing ad-hoc extensions is feasable without being a pure genius. But before contemplating writing an extension, users are welcome to check if what they need hasn't already been done! There are a number of well made extensions ready for use.

I intend to release some extensions of mine someday here if there is interest in that.

What function(s) would you like to see available?

EDIT: BTW, the extension for FireFox is somehow special and not up to par with current SQLite. There are by far better SQLite DB managers out there. I can only once again recommend SQLite Expert (Pro) which is the only one that covers well most aspects of SQLite and DB management. It also enjoys a premium support. Anyone serious in using SQLite under Windows should get the Pro version as it pays back in weeks by the time it saves.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
PsaltyDS

@jhcd: Thanks for the info! :(

The topic started out sounding like stored procedures, which would be pre-defined functions stored inside the DB file, and then available to any client that connected to that DB. (At least, that's my understanding.) Even with the extensions you describe, nothing like stored procedures, especially via the DLL, are available in SQLite... are they?

:mellow:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
jchd

No. Stored procs might well be in the devs head, but even that I somehow doubt. Stored procedures are typically a thing for large RDBMS, where specialized threads can deal with them and cooperate with the server layer.

SQlite approach is different as it's essentially an embedded RDBMS. It's typical use is when you have the SQLite code "near" your application (that it be statically or dynamically linked is not the key). There the engine outperforms most commercial products for typical business usage, provided there is such a thing like a typical usage!

If we devote a significant portion of the core code to execute some form of bytecode language for stored proc, how could this be more efficient that have the same functionality coded right into the applications, because that's where strored proc really belong. That's an excuse for pushing a portion of the complexity of applications into the DBMS, istead of leaving this one free to do its real job: access and store data. We see exactly the same trend here, where some people tend to ask for AutoIt featuritis, mainly for things that belong to UDFs or even to applications.

To get back to SQLite extensions, they can be statically or dynamically linked, transparently.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
H1T1

@jhcd: Thanks for the info! :(

The topic started out sounding like stored procedures, which would be pre-defined functions stored inside the DB file, and then available to any client that connected to that DB. (At least, that's my understanding.) Even with the extensions you describe, nothing like stored procedures, especially via the DLL, are available in SQLite... are they?

:mellow:

PsaltyDSm, you got it right. I was only hoping that this would have worked.

Thanks Any way

Share this post


Link to post
Share on other sites
jchd

@H1T1,

Just curious: do you have a short example where stored proedures are needed?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×