• Welcome, Guest. Please login.
 
July 13, 2020, 02:45:14 am

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Topics - Bern Ertl

21
I've spent the better part fo the day tweaking my code and observing a very strange phenomenon.  My code creates a database with ~20 tables and ~100 triggers.  Up until yesterday, performance when inserting a record in table T1 (in a brand new database with no records) was excellent.  Today, after adding 9 triggers, inserting into table T1 takes roughly 6 times longer per record (as measured using PowerBASIC's TIX statements).

What I found very odd is that none of the 9 new triggers are executed by inserting on T1.  They pertain to inserts/updates on another table (T2) and *updates* on T1. 

Further testing showed that the one trigger that should be executed upon an update to T1 is responsible for a 4x performance hit when I'm *inserting*.

I've tested to ensure that the trigger is not executing (added an unconditional update to a safe field) and it isn't being fired.

I thought perhaps it might have somthing to do with just the number of triggers or something, but the results are not conclusive yet - if I remove one of my older triggers (update on T1 like the 4x hit newer trigger) while leaving the new ones intact, the performance hit is not the same as if I remove the trigger responsible for the 4x hit while leaving the old ones intact.

Any ideas what could be causing this phenomenon?
22
I'm building a business application with a fairly complex database.  I wanted to implement triggers to calculate a value when one of the parameters in the formula changed.  In the process, I learned a few things that weren't explicit in the SQLite docs and I thought I'd share (because they are pretty cool and powerful).

First, I'll explain a little bit about the database layout I'm using (table => column<s>):

JobPlan => Manhour
Task => JobPlanID (index to JobPlan RowID), Duration
~~
ResourceUnits => RU_Type
Resources => RUID (index to ResourceUnits RowID), code info
ResourceAssignments => TaskID (index to Task RowID), ResID (index to Resources RowID), Quantity
~~
Scenarios => Inactive
Relationships => TaskID (index to Task RowID), ScenarioID (index to Scenario RowID - can be null)

A JobPlan can have any number of associated Tasks.  A Task can have zero or one associated Relationship.  A Relationship can have zero or one associated Scenario (which, if it exists, can indicate if the task is "active" or "inactive").  A task without a relationship to a scenario is considered active.

A Task can also have any number of Resource Assignments, which must be associated with a Resource.  A Resource may or may not be associated with a ResourceUnit (if it isn't, it's considered undefined and not included in the ManHour calculations).

To calculate the ManHour for a JobPlan, I need to sum up the Task.Duration * ResourceAssignments.Quantity for every valid Task/ResourceAssignment associated with the JobPlan.  Writing a TRIGGER to do this is complicated because of the many to one relationships and because some of the relationships are not guaranteed (ie. Relationships / Scenarios might be NULL).

When writing my first trigger, to calculate the JobPlan.Manhour field when a related Task.Duration is updated, I struggled a bit figuring out how to encompass the many to one aspect of the valid resource assignments.  Here's what I ended up with (code segments are part of a large BUILD$() statement, hence the formatting with commas and underscores):

            "CREATE TRIGGER fku_Calc_Mhs_Dur AFTER UPDATE OF CurDur ON Tasks FOR EACH ROW WHEN ", _
                                 "(SELECT Tasks.RowID FROM Tasks ", _
                                    "INNER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID ", _
                                    "INNER JOIN Scenarios ON Relationships.Scenario_ID = Scenarios.RowID ", _
                                    "WHERE Scenarios.Inactive = 1 AND Tasks.RowID = OLD.RowID) IS NULL ", _
                  "BEGIN ", _
                     "UPDATE JobPlans SET Cur_Mhs = (SELECT Total( CurDur * ", _
                                                                     "(SELECT TOTAL( ResAssignments.Quantity) FROM ResAssignments ", _
                                                                        "INNER JOIN Resources ON Resources.RowID = ResAssignments.Res_ID ", _
                                                                        "INNER JOIN ResUnits ON ResUnits.RowID = Resources.RU_Code_ID WHERE ResAssignments.Task_ID = RowID AND ", _
                                                                           "ResUnits.RU_Type = 0)) ", _
                                                    "FROM (SELECT CurDur, RowID FROM Tasks WHERE JobPlan_ID = OLD.JobPlan_ID EXCEPT SELECT Tasks.CurDur, Tasks.RowID FROM Tasks ", _
                                                                "INNER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID ", _
                                                                "INNER JOIN Scenarios ON Relationships.Scenario_ID = Scenarios.RowID ", _
                                                                "WHERE Scenarios.Inactive = 1 AND Tasks.JobPlan_ID = OLD.JobPlan_ID)) ", _
                        "WHERE RowID = OLD.JobPlan_ID; ", _
                  "END;", _


I had to make use of nested SELECT statements employing the TOTAL() aggregate function.  Additionally, I needed to use a compound SELECT with EXCEPT in the FROM portion of the main SELECT statement to obtain the correct set of valid tasks.

It took me a while to get the syntax right (not including table names all the time), but this works.

Next, I had to write a trigger to calculate the JobPlan.Manhour field when a related ResourceAssignments record is updated.  This one added a new twist - I don't have a direct reference to the JobPlan's RowID from the triggering event, so how do I limit the update to the correct record?  I ended up with:                        "WHERE RowID = (SELECT JobPlan_ID FROM Tasks WHERE Tasks.RowID = OLD.Task_ID); ", _
                  "END;", _


Finally, I needed a trigger to update the Manhours for all affected/related JobPlans associated with all affected Resources when a ResourceUnit changes type.  This one is interesting:            "CREATE TRIGGER fku_Calc_Mhs_Res_Type AFTER UPDATE OF RU_Type ON ResUnits FOR EACH ROW WHEN ", _
                                 "(SELECT ResAssignments.RowID FROM ResAssignments ", _
                                    "INNER JOIN Resources ON Resources.RowID = ResAssignments.Res_ID ", _
                                    "WHERE Resources.RU_Code_ID = OLD.RowID) IS NOT NULL ", _
                  "BEGIN ", _
                     "UPDATE JobPlans SET Cur_Mhs = (SELECT Total( CurDur * ", _
                                                                     "(SELECT TOTAL( ResAssignments.Quantity) FROM ResAssignments ", _
                                                                        "INNER JOIN Resources ON Resources.RowID = ResAssignments.Res_ID ", _
                                                                        "INNER JOIN ResUnits ON ResUnits.RowID = Resources.RU_Code_ID WHERE ResAssignments.Task_ID = RowID AND ", _
                                                                           "ResUnits.RU_Type = 0)) ", _
                                                    "FROM (SELECT CurDur, RowID FROM Tasks WHERE JobPlan_ID = JobPlans.RowID ", _
                                                             "EXCEPT SELECT Tasks.CurDur, Tasks.RowID FROM Tasks ", _
                                                                "INNER JOIN Relationships ON Relationships.Task_ID = Tasks.RowID ", _
                                                                "INNER JOIN Scenarios ON Relationships.Scenario_ID = Scenarios.RowID ", _
                                                                "WHERE Scenarios.Inactive = 1 AND Tasks.JobPlan_ID = JobPlans.RowID)) ", _
                        "; ", _
                  "END;", _


I thought there would be a problem tying the SELECT statements that calculate the TOTALs to a specific JobPlan (RowID), but you can reference the RowID for each JobPlan in the compound SELECT in the FROM portion of the main SELECT statement.  This was not obvious or intuitive to me, but I'm glad it works.

I'm not sure that I did a good explaining the issues and solutions I encountered while writing these triggers, but these examples do show some powerful concepts that are available with triggers.
23
I'm stuck in slow gear this afternoon.  I've got a DELETE operation that will be encompassing multiple records in one call.  I'm currently defining it like so:

DELETE FROM table WHERE RowID = 3 OR RowID = 6 OR RowID = 7

etc. Is there a better/preferred syntax for the WHERE clause in this statement?
24
I see in that version 1.3 introduced a new modchar:
Quote10. Added the following ModChars to slSel, slSelAry, and to the SelStatemnt of
    slSetRelNameLocks:
      Bn = Do a Begin Transaction before doing the Sel command. The type
           of Begin is controlled by the value of n as follows:
              0 = Deferred. This is the default if n is omitted.
              1 = Immediate.
              2 = Exclusive.
           This allows for database locking and selecting in one trip to the server.


It's not clear though if I SQLitening is ending the explicit transaction or if I need to add an slExe "COMMIT;" to free up the lock.
25
General Board / Congrats on moving to a dedicated forum
January 12, 2010, 02:50:45 pm
It's been too long since I was working on my project, but I'm getting back on it again.  I should be popping in and out more frequently now.  :)
26
If I try putting a MSGBOX in a function in an SQLiteningProc DLL, the function hangs/errors out when it reaches that point of the code (ie. the MSGBOX causes the hang/error).  I'm having to write code to exit the function with data in one of the string parameters to check client side as part of the debugging process, but it would be much easier if I could use a MSGBOX.  Any idea why it doesn't work?
27
I have written a function client side that did some processing of a couple database tables and later realized that the function should have been written as a server side proc since I didn't really need to transfer any of the results to the client app (just store the results in another table for later processing).

After testing out the function and ensuring it was working correctly, I went ahead and copied/modified it to execute as a server side function (SQLiteningProc DLL) and did some testing with PowerBASIC's TIX for benchmarking.

Using the same data, the server side function was executing in roughly 1/5 the time as the equivalent client side function.  It's not really relevant to any code any of you might be writing as the difference is likely dependent upon a number of factors (including how much data is being read/written), but I thought it was interesting none-the-less that the difference could be so large.
28
Similar to my BLOB storing question, I now find myself needing to test if a value is NULL or not in a Procs function using SQLite.  What is the SQLite equivalent of slIsFieldNull() ?
29
I've got a string of binary data that I want to read/write to the database from a SQLiteningProcs DLL (which requires accessing the db directly with SQLite instead of the SQLitening wrapper).  How can I do this?
30
If I develop a set of functions to implement a new data structure inside an SQLiteningProcs DLL, would setting the scope for the underlying base data as THREADED ensure that each connection to the database had it's own persistent copy of the data (persistent through numerous calls to various functions in the DLL), or is each function call to the SQLiteningProcs DLL being handled in a unique thread? Or are all function calls to the SQLiteningProcs DLL being handled in a single thread?

31
Quote from: Bern Ertl on March 03, 2009, 06:40:10 pm
... Looks like I will need to use sqlite3_create_function() to register my own ... function.  As SQLitening doesn't currently encapsulate sqlite3_create_function(), is it possible to call it directly on the client end, or will I need to add a server side proc to call it and access it via slRunProc?

Anyone know if it's possible to register a PB function with sqlite3_create_function()?


I've run into another situation where it would be very useful to take advantage of this feature.  I'd like to create a TRIGGER that builds a comma separated string representing records in a table.  I would use the group_concat() aggregate function, but I need to do more than just assemble values from a single column.  I need to encode data from 3 different columns for each value in the string.

I don't suppose anyone here has any experience writing custom aggregate functions with SQLite?

It would also be awesome if I could implement some PB string handling functions like PARSE$ and REMOVE$ for use within SQL statements using this mechanism.
32
If I use BEGIN EXCLUSIVE to lock out a database, begin processing, and need to close my app, do I need to explicitly send a COMMIT command to free up the locks on the database, or will SQLite/SQLitening automatically do that when the app ends and the connection is dropped?
33
It's not possible to use slFN to retreive data in scenarios like the following, correct?

SELECT T1.F1, T2.F1 FROM T1, T2

I would need to use slF with the column number, correct?  The column numbering is BASE 1, right?
34
I'm building an application that will open a dialog that contains a tab control.  Each tab has child windows/dialogs that will access SQLitening to retrieve data from the same database (at initialization - %WM_INITIALIZE).  I'm planning on using slGetUnusedSetNumber for each child window's callback functions where slSel will be called.  Is it necessary to wrap the slGetUnusedSetNumber + slSel calls inside a critical section to ensure that there are no conflicts with multi-threaded access (ie. tab #1 calls slGetUnusedSetNumber and tab #2 calls slGetUnusedSetNumber before tab #1 can call slSel)?  Or does SQLitening ensure that the Set number returned by slGetUnusedSetNumber is reserved until it's used?
35
What is the best way to manage multiple database handles in a function when you need to open a database (guaranteed not to be open at the time) and it's 50/50 odds that another database is already open?

What does the slPushDatabase function do if no database is open?  Assuming it doesn't push anything onto the stack, what happens if you call slPopDatabase with an empty stack?
36
I've got code equivalent to the following:

SELECT RowID from T1 WHERE F1 = 'VALUE' OR F2 = 'VALUE'

This searches the table to see if any fields in two columns match a given value.  This works, but only returns exact matches.  I would like to modify the statement so that it returns records if there is a partial match (ie. if 'VALUE' is contained within data stored in F1 or F2).  If it's possible to specify case insensitive matching, that would be great too.

I'm looking at the SQLite documentation and the WHERE clause of the SELECT statement expects an [exp] which is defined thusly:



It seems like there might be opportunity to use the MATCH or REGEXP features, but I can't find any documentation on them.  Help?
37
I have a table that includes a field for a "separator character" to be displayed when building an output string.  When saving the default character value to the db, I found that slBuildInsertOrUpdate does not quote encapsulate a one character string containing a period.  While I can see in hindsight that this is because slBuildInsertOrUpdate considers it a real/single number, it seems somewhat strange for the function to treat it as a number when there are no numerical digits accompanying it.

38
When I attempt to store a large integer value (QUAD) as:

FORMAT$( qQuadVar, 18)

and later retrieve it with

slFN( "QuadNumField" )

SQLitening is returning a string with the number in scientific notation. 
39
I'm assuming that to copy the contents of a table in one database file to another, I would need to open both and read from one and write same to the other sequentially (row by row).  Is there a better way?
40
Consider the following example:CREATE TABLE T1 (T1F1, T1F2);

CREATE TABLE T2 (T2F1 INTEGER CONSTRAINT fk_T1_RowID REFERENCES T1(RowID) ON DELETE CASCADE, T2F2);

-- Foreign Key Preventing insert
CREATE TRIGGER fki_T2_T2F1_T1_RowID
BEFORE INSERT ON [T2]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "T2" violates foreign key constraint "fki_T2_T2F1_T1_RowID"')
  WHERE NEW.T2F1 IS NOT NULL AND (SELECT RowID FROM T1 WHERE RowID = NEW.T2F1) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_T2_T2F1_T1_RowID
BEFORE UPDATE ON [T2]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "T2" violates foreign key constraint "fku_T2_T2F1_T1_RowID"')
      WHERE NEW.T2F1 IS NOT NULL AND (SELECT RowID FROM T1 WHERE RowID = NEW.T2F1) IS NULL;
END;

-- Cascading Delete
CREATE TRIGGER fkdc_T2_T2F1_T1_RowID
BEFORE DELETE ON T1
FOR EACH ROW BEGIN
    DELETE FROM T2 WHERE T2.T2F1 = OLD.RowID;
END;


What I'd like to do is modify the last trigger so that, instead of deleting rows in T2 where T2F1 matches the RowID of any record I'm deleting in T1, it simply sets the T2F1 field to NULL.  Any ideas if this is possible or how I might accomplish this?