SQL challenge/problem

[Bern Ertl]

I've got an SQLite database that contains 3 tables: JobPlans, Tasks, Relationships.

Each JobPlan can have multiple Tasks associated with it.

Each Task can have one Relationship associated with it (actually more, but for the purposes of this discussion we'll keep it simple).

I'm writing a function to assign corresponding Relationship records to the Tasks that do not already have one. The values assigned to the Relationship records are handled differently if all Tasks for a JobPlan are missing a corresponding Relationship record versus if any of them do have a corresponding Relationship record.

Ie. the very first time the function is called, no Tasks will have a Relationship record, so all will be processed using formula "A". Subsequent calls to the function will process tasks using formula "A" only if all Tasks for a given JobPlan are missing Relationship records. If at least one Task pertaining to a JobPlan has a Relationship record, the function must use formula "B" to assign Relationship records to Tasks for that JobPlan.

I can write a SELECT statement to find all Tasks that are missing a corresponding Relationship record:

SELECT JobPlans.Name, Tasks.RowID, Tasks.UID, Relationships.Task_ID FROM Tasks
   INNER JOIN JobPlans ON Tasks.JobPlan_ID = JobPlans.RowID
   LEFT OUTER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID
WHERE Relationships.Value IS NULL

but what I'd like to be able to do is write a single SELECT statement that filters that selection so that if any Tasks in a JobPlan have corresponding Relationships (Relationships.Value IS NOT NULL), then all Tasks associated with that JobPlan that are missing corresponding Relationships are filtered out (to be processed separately).

I'm guessing there is a way to do this, but I'm struggling to figure it out. Ideas?

[Edwin Knoppert]

Not sure what you want but think of:

1) SELECT ... FROM ( your current select here ) As T1 WHERE....

2) UNION ALL

[Bern Ertl]

Not sure what you want but think of:

1) SELECT ... FROM ( your current select here ) As T1 WHERE....

Thanks Edwin. FROM (SELECT ...) was the key.

[Bern Ertl]

Part 2...

OK, I got another challenging problem that I'd like to solve with SQL in SQLite.

I've got a field in a table that is a comma separated string of values. Each value in the comma separated string represents a record in another (secondary) table.

If I add a record in the secondary table, I must update the comma separated strings in the main table for all related records (appending a value to the existing string to represent the new record in the secondary table). I managed to do this with a trigger and it works fine.

Problem #1:
If I delete a record in the secondary table, I must update the comma separated strings in the main table for all related records (deleting a value from the existing string that represents the deleted record in the secondary table). Is there something like PB's REMOVE$ that can be used to return a main string minus some substring?

Problem #2:
If I add or delete a value to/from a string in the main table, I'd like to have triggers insert/update/delete records in the secondary table accordingly. However, I'm not seeing how I can parse the string or compare the OLD. and NEW. values to process any changes.

Any ideas?

[Michael Mattias]

Is there something like PB's REMOVE$ that can be used to return a main string minus some substring?

Depends on the specific DBMS. eg, Oracle has a REPLACE function you can use and also a SUBSTRING. (replace with NUL = REMOVE$)

I'm not aware of any 'universal' function for replace, but I'm pretty sure a substring function is standard.

A good place to learn some 'standard' SQL online is at W3 Schools.

This link comes right from my IE "Favorites" : http://www.w3schools.com/Sql/default.asp

MCM

[Bern Ertl]

Depends on the specific DBMS.

Thanks. I'm using SQLite. I checked their docs but I'm not finding anything useful.

...

On second thought, perhaps I can use group_concat() to rebuild the comma separated strings (problem #1 above). Off to experiment...

[Bern Ertl]

Does anyone here have any experience using sqlite3_create_function() to register custom functions or aggregates in SQLite? The group_concat() aggregate function is not flexible enough for my needs, but a modified version would work.

[Don Dickinson]

i've used sqlite's custom functions, but only in sqlite2 (the project was years ago). it worked very well.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'	sqlfuncUcase
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sub sqlfuncUcase cdecl _
		( byval pFunc as long, byval iArgs as long, ppAny as long )

	dim pzTemp as asciiz ptr
	dim zString as string

	pzTemp = ppAny
	if pzTemp then zString = ucase$(@pzTemp)
	sqlite_set_result_string pFunc, byval strptr(zString), -1

end sub

'- register the function with sqlite:
 if sqlite_create_function ( hSQLite, "ALLCAPS", 1, _
       codeptr(sqlfuncUcase), byval 0) then
  '- this is an error condition
 end if
'- after this you can reference it in your sql:
sSQL = "SELECT ALLCAPS(first_name) fn FROM t_name"

i don't know if the syntax has changed much with sqlite3, but perhaps it will get you started.

-don

[Bern Ertl]

Thanks Don.

I found these C code snippets:

Parameters for callback functions explained:

\code
int sqlite3_create_function(
opaque dbh, // DB handle
string funcName, // Function name (currently only UTF8)
int argCoung, // expected argument count, or -1 for any number
int encoding, // currently only supports the value SQLITE_UTF8
mixed userData, // arbitrary data to pass to the callback(s)
Function callback, // signature: f( sqlite3_context,int,Array-of-sqlite3_value )
OPTIONAL Function callbackStep = null, // signature: f( sqlite3_context,int,Array-of-sqlite3_value )
OPTIONAL Function callbackFinal = null // signature: f( sqlite3_context )
);
\endcode

http://www.google.com/codesearch/p?hl=en#NnTzP2O2qUA/trunk/src/lib/plugins/sqlite3/v8-sqlite3.h&q=sqlite3_create_function

Sample callbacks for an aggregate function:

/*
** During testing, the special md5sum() aggregate function is available.
** inside SQLite.  The following routines implement that function.
*/
static void md5step(sqlite3_context *context, int argc, sqlite3_value **argv){
  MD5Context *p;
  int i;
  if( argc<1 ) return;
  p = sqlite3_aggregate_context(context, sizeof(*p));
  if( p==0 ) return;
  if( !p->isInit ){
    MD5Init(p);
  }
  for(i=0; i<argc; i++){
    const char *zData = (char*)sqlite3_value_text(argv[i]);
    if( zData ){
      MD5Update(p, (unsigned char*)zData, strlen(zData));
    }
  }
}
static void md5finalize(sqlite3_context *context){
  MD5Context *p;
  unsigned char digest[16];
  char zBuf[33];
  p = sqlite3_aggregate_context(context, sizeof(*p));
  MD5Final(digest,p);
  DigestToBase16(digest, zBuf);
  sqlite3_result_text(context, zBuf, -1, SQLITE_TRANSIENT);
}
int Md5_Register(sqlite3 *db){
  return sqlite3_create_function(db, "md5sum", -1, SQLITE_UTF8, 0, 0,
                                 md5step, md5finalize);
}

http://www.google.com/codesearch/p?hl=en#sOgfkSDfqks/trunk/sqlite-3.5.4/src/test_md5.c&q=sqlite3_create_function

and hope to be able to work something out based upon their example.

[Bern Ertl]

I came up with the following:

%SQLITE_TRANSIENT = -1

SUB sqlFnRemove cdecl _
   ( hContext AS LONG, BYVAL iArgs AS LONG, pArgv AS ANY PTR)

   DIM sMain AS STRING, sMatch AS STRING, sResult AS STRING

   IF iArgs <> 2 THEN EXIT SUB

   sMatch = sqlite3_value_text( @pArgv[0])
   sMain = sqlite3_value_text( @pArgv[1])

   sResult = REMOVE$( sMain, sMatch)
   REPLACE ", , " WITH ", " in sResult
   sResult = TRIM$( sResult, ", ") + $NUL

   sqlite3_result_text( hContext, BYVAL STRPTR( sResult), -1, %SQLITE_TRANSIENT)

END SUB

but I'm not sure about the declaration/use of the pArgv parameter which is supposed to be an array of "SQLITE3_VALUE" (see definition of md5step in code sample above - any help translating that C code would be appreciated). I'm also not sure where to find out how SQLITE3_VALUE is defined.

[Paul Squires]

Hi Bern,

There is a whole chapter in my SQLite book at home regarding using functions with SQLite. IIRC, the Aggregate functions are a more specialized case of general functions and involve a couple of callbacks. The general functions do not need or use callbacks.

Here is the book that I am referring to:

http://www.amazon.ca/Definitive-Guide-SQLite-Mike-Owens/dp/1590596730

I think that it is Chapter 7 that talks about the functions.

I'll read it again tonight and try to post a working example for you.

[Bern Ertl]

Thanks Paul. I originally thought I'd implement a specialized aggregate function similar to the group_concat(), but I think implementing a REMOVE (non-aggregate) function will achieve the same ends and be simpler.

~~~

I'm looking in the header source for sqlite3 and all the C code is Greek to me.

I need to be able to translate the following into PB equivalients:

declares for functions:

const void *sqlite3_value_text16(sqlite3_value*);

void sqlite3_result_text(sqlite3_context*, const char*, int, void(*)(void*));

int sqlite3_create_function(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);

types/structs (make no sense to me):
typedef struct Mem sqlite3_value;
typedef struct sqlite3_context sqlite3_context;

My guesses:
DECLARE FUNCTION sqlite3_value_text16( sqlite3_value AS ANY) AS ASCIIZ

DECLARE SUB sqlite3_result_text( sqlite3_context AS ANY, zText AS ASCIIZ, BYVAL nBytes AS LONG, BYVAL lType AS LONG)

DECLARE FUNCTION sqlite3_create_function( hDB AS ANY, zFuncName AS ASCIIZ, BYVAL nArg AS LONG, BYVAL encoding AS LONG, BYVAL pUserData AS DWORD, pfnFunc AS DWORD, pfnStep AS DWORD, pfnFinal AS DWORD) AS LONG

[Paul Squires]

Jose already has these translated. Check out: http://www.jose.it-berater.org/smffo...p?topic=2895.0

[Paul Squires]

Hi Bern,

I know that you are using SQLitening... have you looked at the SQLiteningProcs code?

It is my understanding that Fred built that code so that user defined functions could be invoked (maybe even from your TRIGGER code).

I just took a quick look at the code and it seems like it could accomplish what you need. Of course, I could be wrong.

[Bern Ertl]

Paul, yes, I'm using SQLitening and SQLiteningProcs code for some processing, but the functions I'm discussing here need to be custom functions that can be processed within SQL statements in order to develop TRIGGERs that will auto-update some fields when others are updated. The advantage of having the code processed within a TRIGGER/SQL statement is that the entire process would be atomic.

[Bern Ertl]

I need a fresh pair of eyes to look at this. I've implemented the following code in a SQLiteningProcsA DLL:

%SQLITE_TRANSIENT = -1
%SQLITE_UTF8      = 1

DECLARE FUNCTION sqlite3_create_function CDECL LIB "sqlite3.dll" ALIAS "sqlite3_create_function" (BYVAL rhDab AS DWORD, zName AS ASCIIZ, BYVAL nArg AS LONG, BYVAL eTextRep AS LONG, _
										BYVAL pUserData AS DWORD, BYVAL pFunc AS DWORD, BYVAL pStep AS DWORD, BYVAL pFinal AS DWORD) AS LONG
DECLARE FUNCTION sqlite3_value_text CDECL LIB "sqlite3.dll" ALIAS "sqlite3_value_text" (BYVAL sqlite3_value AS DWORD) AS DWORD
DECLARE SUB      sqlite3_result_text CDECL LIB "sqlite3.dll" ALIAS "sqlite3_result_text" (BYVAL sqlite3_context AS DWORD, BYVAL pzText AS DWORD, BYVAL nBytes AS LONG, BYVAL DWORD)

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Custom SQL functions...
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SUB sqlFnRemove CDECL ( BYVAL hContext AS DWORD, BYVAL iArgs AS LONG, BYVAL pArgv AS DWORD PTR) EXPORT

	LOCAL sMain AS STRING, sMatch AS STRING, sResult AS STRING, pzText AS ASCIIZ PTR

	IF iArgs <> 2 THEN EXIT SUB

	pzText = sqlite3_value_text( @pArgv[0])
	sMatch = @pzText
	pzText = sqlite3_value_text( @pArgv[1])
	sMain = @pzText

	sResult = REMOVE$( sMain, sMatch)
	REPLACE ", , " WITH ", " IN sResult
	sResult = TRIM$( sResult, ", ") + $NUL

	sqlite3_result_text hContext, BYVAL STRPTR( sResult), -1, %SQLITE_TRANSIENT

END SUB

'============================<[ InitCustomSQLFns ]>=============================

FUNCTION ICF ALIAS "ICF" (BYVAL rhDab AS DWORD, _
			  BYVAL rlTcpFileNumber AS LONG, _
			  blParm1 AS LONG, _
			  blParm2 AS LONG, _
			  bsParm3 AS STRING, _
			  bsParm4 AS STRING) EXPORT AS LONG

	'ICF = Initialize Custom (SQL) Functions

	'In:  none

	'Out: FUNCTION = 0 for success, <>0 if error
	'     blParm1 = where in execution path SQLite error occurred (if any)
	'     bsParm3 = error message if error occurred

	LOCAL lResult AS LONG, zText AS ASCIIZ * 16

	zText = "RemoveXfromY"
	100 lResult = sqlite3_create_function( rhDab, zText, 2, %SQLITE_UTF8, 0, CODEPTR( sqlFnRemove), 0, 0)

	IF ISTRUE lResult THEN
		bsParm3 = "Failed to create RemoveXfromY"
		blParm1 = 100        ' Return "error line number"
		FUNCTION = lResult   ' Set return code
		EXIT FUNCTION
	END IF

END FUNCTION

The call to ICF returns successfully (ie. sqlite3_create_function() is returning a success code). When I try calling RemoveXfromY within a SQL statement, the SQLitening server shuts down with an error -18 (no matter what code is placed within the SUB - the code never gets executed). I'm guessing there is an issue with the declaration for sqlFnRemove, but I'm not seeing it.

[Bern Ertl]

I figured out the problem. The SQLiteningProcsA DLL is loaded and unloaded dynamically as it is called by slRunProc (without the 'u' modchar). When I was executing the SQL statement that references the RemoveXfromY function, the function was no longer loaded in memory.

I've added the 'u' modchar so the DLL is not unloaded and the code appears to be working now.