Announcement

Collapse
No announcement yet.

SQL challenge/problem

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL challenge/problem

    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:

    Code:
    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?
    Bernard Ertl
    InterPlan Systems

  • #2
    Not sure what you want but think of:

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

    2) UNION ALL
    hellobasic

    Comment


    • #3
      Originally posted by Edwin Knoppert View Post
      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.
      Bernard Ertl
      InterPlan Systems

      Comment


      • #4
        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?
        Bernard Ertl
        InterPlan Systems

        Comment


        • #5
          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

          Comment


          • #6
            Originally posted by Michael Mattias View Post
            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...
            Bernard Ertl
            InterPlan Systems

            Comment


            • #7
              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.
              Bernard Ertl
              InterPlan Systems

              Comment


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

                Code:
                '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                '	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
                Don Dickinson
                www.greatwebdivide.com

                Comment


                • #9
                  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?h...reate_function

                  Sample callbacks for an aggregate function:
                  Code:
                  /*
                  ** 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?h...reate_function

                  and hope to be able to work something out based upon their example.
                  Bernard Ertl
                  InterPlan Systems

                  Comment


                  • #10
                    I came up with the following:
                    Code:
                    %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.
                    Last edited by Bern Ertl; 28 Apr 2009, 09:35 AM.
                    Bernard Ertl
                    InterPlan Systems

                    Comment


                    • #11
                      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-Guid.../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.
                      Paul Squires
                      FireFly Visual Designer (for PowerBASIC Windows 10+)
                      Version 3 now available.
                      http://www.planetsquires.com

                      Comment


                      • #12
                        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:
                        Code:
                        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
                        Last edited by Bern Ertl; 28 Apr 2009, 10:28 AM.
                        Bernard Ertl
                        InterPlan Systems

                        Comment


                        • #13
                          Jose already has these translated. Check out: http://www.jose.it-berater.org/smffo...p?topic=2895.0
                          Paul Squires
                          FireFly Visual Designer (for PowerBASIC Windows 10+)
                          Version 3 now available.
                          http://www.planetsquires.com

                          Comment


                          • #14
                            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.
                            Paul Squires
                            FireFly Visual Designer (for PowerBASIC Windows 10+)
                            Version 3 now available.
                            http://www.planetsquires.com

                            Comment


                            • #15
                              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.
                              Bernard Ertl
                              InterPlan Systems

                              Comment


                              • #16
                                I need a fresh pair of eyes to look at this. I've implemented the following code in a SQLiteningProcsA DLL:
                                Code:
                                %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.
                                Last edited by Bern Ertl; 1 May 2009, 08:27 AM.
                                Bernard Ertl
                                InterPlan Systems

                                Comment


                                • #17
                                  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.
                                  Bernard Ertl
                                  InterPlan Systems

                                  Comment

                                  Working...
                                  X