Creating SQLite User Defined Function

[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

[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.

Edited November 5, 2008 by PsaltyDS

[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

[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.

[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

[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.

[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.

[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.

@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.

[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 March 2, 2010 by jchd

[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?

[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.

[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?

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

Thanks Any way

[jchd]

@H1T1,

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