Categories
Programming

Using full observer pattern instead of events

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

Wikipedia (at the time of this writing) defines Observer pattern as following:
The Observer pattern (a subset of the asynchronous publish/subscribe pattern) is a software design pattern in which an object, called the subject, maintains a list of its dependents, called Observers, and notifies them automatically of any state changes, usually by calling one of their methods. It is mainly used to implement distributed event handling systems.

MSDN defines Events (I refer to it later as 'eventing system') as:
Events enable a class or object to notify other classes or objects when something of interest occurs. The class that sends (or raises) the event is called the publisher and the classes that receive (or handle) the event are called subscribers.

.NET eventing system is nothing else then an implementation of an Observer pattern. The subject (type that defines an event) raises the event to notify the Observer (class which adds a handler to the event) that something has occurred.
Having the Observer pattern integrated into the language is a great thing which enables the developer to easily add notifications about object changes. However there are cases where this kind of a system is not sufficient and may lead to poorly designed code if you use it.

The .NET eventing system has the following drawbacks:

  • You must define each 'change' as a separate event. Although there are some events which go around this by trying to be 'generic' (such as INotifyPropertyChanged.PropertyChanged) they are usually not elegant and seem more like a workaround.
  • You should define each event as EventHandler (or EventHandler<>). This is not mandatory as CLR allows you to define events of any method signature but if you plan on being CLS compliant and not getting compiler warnings then you usually should follow this convention.

In most cases these drawbacks are not serious and they do not affect your code quality that much. But there are some cases (especially when you have objects where there are lots of changes of various types) where the eventing system is just not good enough. For such cases you should build you own custom Observer pattern.

Building you own Observer pattern is quite simple and can be expressed in these few steps:

  1. Select the type which will be the subject (lets call it Subject)
  2. Define the changes that this type will expose
  3. Create a new interface which will define methods that represent the changes of the Subject (for example ISubjectObserver)
  4. Create a new interface method for each change (for example OnSubjectNameChanged, OnSubjectTypeChanged, OnSubjectDetailLineAdded etc.) with parameters which are suitable for each notification (old value, new value, added detail object etc.)
  5. Create a new type which will represent the Observer (lets call it Observer)
  6. Have it implement the new interface (public class Observer: ISubjectObserver)
  7. Implement the methods of the interface
  8. Add two methods to the Subject type which will be used to register/unregister an Observer (RegisterSubjectObserver(ISubject**Observer**),UnRegisterSubjectObserver(ISubjectObserver*)`

Looks simple, and it is. If you are reading this you are probably thinking "hey, I know how to do all this". Well that might be true but developers sometimes forget to think about alternate ways to do something and this might give you another way of looking at things when designing your applications.

Categories
Database Programming

Generic PL/SQL procedure to update target table data with source table data

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

Sometimes you might need to update data inside a table with fresh data from another table which has the same structure (but it might be in another schema). Usually this might occur if you have some sort of a 'master' schema where the 'global' data is located and you want to periodically transfer this data to some the same table in the local user schema.

Although it is relatively easy to create an update procedure/PLSQL block to do this type of thing, it is very tedious to do this every time for each table that you want to synchronize in this way The following procedure takes the name of the table and creates dynamic SQL which it then executes via EXECUTE IMMEDIATE. There are some restrictions in order for this to work:

  • Table must have a unique key
  • User must be able to query 'all_tab_cols' view
  • User must be able to execute selects against the source table (this one is understandable)

Here is the procedure:

procedure copy_from_source
(
      p_table_name in varchar2
)
as
  cursor cur_columns(c_v_table_name varchar2, c_v_source varchar2) is
    select column_name from all_tab_cols 
    where
    table_name = c_v_table_name and owner = c_v_source and column_id is not null;

  v_id_col varchar2(38) := 'ID';
  v_source_schema varchar2(38) := 'SOURCE';
  v_target_schema varchar2(38) := user;
  v_query varchar2(10000);
  v_columns varchar2(10000):= null;
  v_start number := 0;
begin     
  v_query := null;
  for v_col in cur_columns(p_table_name, v_source_schema) loop
      if v_query is null then
         v_query := 'update ' || v_target_schema || '.' || p_table_name || 
            ' l set ' || chr(13) || chr(10);

         v_query := v_query || '  ' || '"' || v_col.column_name || '"' || ' = (';
         v_query := v_query || 'select ' || '"' || v_col.column_name || '"' || 
            ' from ' || v_source_schema || '.' || p_table_name || ' g where g.' || 
            v_id_col || ' = l.' || v_id_col;

         v_query := v_query || ')';
      else
         v_query := v_query || ', ' || chr(13) || chr(10);
         v_query := v_query || '  ' || '"' || v_col.column_name || '"' || ' = (';
         v_query := v_query || 'select ' || '"' || v_col.column_name || '"' ||
            ' from ' || v_source_schema || '.' || p_table_name || ' g where g.' || 
            v_id_col || ' = l.' || v_id_col;

         v_query := v_query || ')';
      end if;
  end loop;      
  v_query := v_query || chr(13) || chr(10) || ' where ' || v_id_col || ' in ' || 
    chr(13) || chr(10);

  v_query := v_query || ' (select ' || v_id_col || ' from ' || v_source_schema || 
    '.' || p_table_name || ')';

  v_query := v_query || chr(13) || chr(10);
  -- dbms_output.putline(v_query);
  execute immediate v_query;

  v_query := null;
  for v_col in cur_columns(p_table_name, v_source_schema) loop
      if v_columns is null then
         v_columns := v_columns || '"' || v_col.column_name || '"';
      else
         v_columns := v_columns || ', ' || '"' || v_col.column_name || '"';
      end if;
  end loop;
  v_query := 'insert into ' || v_target_schema || '.' || p_table_name || 
    ' (' || v_columns || ')' || chr(13) || chr(10);
  v_query := v_query || ' select '  || v_columns  || ' from ' || v_source_schema || 
    '.' || p_table_name || ' where ' || v_id_col || ' not in ' || chr(13) || chr(10);
  v_query := v_query || ' (select ' || v_id_col || ' from ' || v_target_schema || 
    '.' || p_table_name || ')';
  -- dbms_output.putline(v_query);
  execute immediate v_query;
end;
Categories
Programming

Using regular expression search/replace in Visual Studio

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

In the previous post I have shown you how to do search/replace using .NET Regex. Visual Studio 'Find and Replace' dialogue (CTRL+H) also support regular expression search and replace and if you learn to use it (or better said, if you get into the habit of using it) you can do some pretty powerful stuff.

Visual Studio 2008 Search replace dialog

Note: Wherever you see me write 'enter "xyz" into some text field', it is assumed that you should enter it without the quotation marks
Note 2: Also all 'questions' here are simply a figment of my imagination, nobody asks me anything 🙁

Q: I have pasted a bunch of code from the web into Visual Studio but I get double newlines, is there a way to strip them?
A: Why of course. Open the Find and replace dialogue. Enter \n+ into "Find what" field and \n into "Replace with" field (do not forget to check the "Use: Regular expressions").
E: We say to VS: find any one or more continuous occurrences of newline character and replace it with just one newline character

The really interesting thing about regular expression search/replace is the usage of the grouping constructs to do a 'capture' and then use that capture in the replacement pattern. Great thing is that Visual Studio supports this! Unfortunately Visual Studio regular expression grouping constructs are not the same as .NET grouping constructs.
They are not difficult to learn but it is annoying that Microsoft did not settle for one standard (at least for them) way to use regular expression groupings. Instead of using parenthesis - (, ) to define capture groups you must use curly braces - {, } Instead of using dollar $ sign for using captures inside the replacement you must use the backslash \ character.

Q: I have a bunch of forms/web pages where I have methods where I first load data from an object to controls and then back from controls into an object. Do I have to type everything?
A: Of course not! We can copy the code we have and then use Find and replace to swap the assignment lines (something = somethingelse; into somethingelse = something;). For example we have the following method:

public static void FillControls(SomeObject o)
{
    txt1.Text = o.Property1;
    txt2.Text = o.Property2;
    txt3.Text = o.Property3;
    txt4.Text = o.Property4;
    txt5.Text = o.Property5;
    txt6.Text = o.Property6;
    txt7.Text = o.Property7;
    txt8.Text = o.Property8;
}

In order to create an 'oposite' method, we first make a copy of the first method and rename it:

public static void FillObject(SomeObject o)
{
    txt1.Text = o.Property1;
    txt2.Text = o.Property2;
    txt3.Text = o.Property3;
    txt4.Text = o.Property4;
    txt5.Text = o.Property5;
    txt6.Text = o.Property6;
    txt7.Text = o.Property7;
    txt8.Text = o.Property8;
}

Then we select the lines where we want to make a swap, hit CTRL+H to popup the Find and replace dialog, enter {.*} = {.*}; into the "Find what:" field, enter \2 = \1; into the "Replace with:" field, hit "Replace All" button and ... :

public static void FillObject(SomeObject o)
        {
o.Property1 =             txt1.Text;
o.Property2 =             txt2.Text;
o.Property3 =             txt3.Text;
o.Property4 =             txt4.Text;
o.Property5 =             txt5.Text;
o.Property6 =             txt6.Text;
o.Property7 =             txt7.Text;
o.Property8 =             txt8.Text;
        }

E: We say to VS: find a group of characters before the = and assign it to a group (1), then find a group of characters after the = and assign it to a group (2); in the replacement first place the contents of the group (2), then the = and then contents of group (1)

We might experiment with the regular expression and get a better formatted version but it is easier to just hit the "Format the whole document" button (CTRL+E, D) which will format our code nicely.

Categories
.NET Programming

Using System.Text.RegularExpressions.Regex to do search/replace

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

Google returns ~21.000.000 results (at the time of this writing) if you do a search for 'regular expressions' so you must be asking yourself why would somebody write something about it? Well sometimes you just need to use regular expressions to do some handy text processing, such as search/replace. I wrote this post just to give you some quick&dirty info on how to use the .NET Regex class to do substitutions.

First of, the simple search and replace. We search for a word and then replace it with another word:

public static void ReplaceSimple()
{
    string example = "I am a man.";
    string replaced = Regex.Replace(example, "man", "woman");
    Console.WriteLine(replaced);
    // Output: I am a woman.
    Console.ReadKey();
}

If you are using Regex for this kind of search/replace, then do yourself a favour and take a look at String.Replace method. Regular expressions get useful when you have some limitations on how you may do search and replace. Lets say that you wish to process some HTML (I am beating a dead cat, but who cares) and you would like to replace all <div> tags with <span> tags. One of the options is to use two replacements, one which replaces <div> with <span> and the other which replaces </div> with </span> (of course we could not just replace "div" with "span" because "div" might appear as a part of a HTML body text). But, doing it that way is not so interesting (and also makes this post pretty useless), so lets do something complex:

public static void ReplaceDiv()
{
    string example = "<div>Becomes span</div>";
    string replaced = Regex.Replace(example, @"<(/{0,1})div>", @"<$1span>");
    Console.WriteLine(replaced);
    // Output: <span>Becomes span</span>
    Console.ReadKey();
}

The secret to doing some complex search/replace in .NET (and I guess in many other regular expression implementations) is to define regular expression 'capture' groups inside the search pattern and then use them inside the replacement pattern. In the code above we are saying "give me all matches that start with <, have 0 or 1 / and end with div>; also group 0 or 1 /; then replace the found matches with <, followed by the first group and then followed by span>. The parenthesis (, ) characters serve as grouping constructs which we are using to 'capture' an appearance of 0 or 1 / character.

Any time that we use the parenthesis we are creating a group 'capture' which can be used in the replacement pattern by using the dollar ($) character. Using parenthesis for capture groups is simple but may lead to regular expression search patterns which are not immediately obvious (as if any useful regular expression patterns are obvious :)). To make it easier you may use named grouping constructs:

public static void ReplaceDivNamedGroup()
{
    string example = "<div>Becomes span</div>";
    string replaced = Regex.Replace(example, @"<(?<slash>/{0,1})div>", @"<${slash}span>");
    Console.WriteLine(replaced);
    // Output: <span>Becomes span</span>
    Console.ReadKey();
}

Naming the grouping is simple, just add ?<name> after the parenthesis start. You may then use the name of that group with syntax ${name} inside the replacement pattern.

As a bizarre fact, you may use the grouping constructs inside the search pattern. Example:

public static void EhThatRegex()
{
    string example = "We are searching for Yoda speak, searching are we";
    var match = Regex.Match(example, @"(\w+)\s+(\w+)\s+.*\2\s+\1");
    Console.WriteLine(match.Success );
    // Output: true
    Console.ReadKey();
}

Here we go, I've posted a non-obvious regular expression. But it can not be helped. The grouping 'capture' can be used inside the search pattern, we just may not use the $1 or ${xxx} but rather \1 and \k<xxx>. Of the top of my head I can not think where such a pattern would be useful but you might.

Here is the explanation of the search: (\w+)\s+(\w+)\s+.*\2\s+\1 search for a sequence of characters ending with space character (\w+)\s+ (basically we are searching for a complete word) and capture it in group 1; search again for another word and capture it in group 2; skip anything until you reach a point where the second word, followed by the first word appears. I am sure that some regular expression guru could use this in some way to check if the sentence is a palindrome, but that is beyond my abilities.

Categories
Database Programming

Creating semi-automated database update scripts for Oracle

Note: I have transferred this blog from my old blog site: http://dcarapic.blogspot.com/ so I am reposting most of the stuff from there

If you have ever created applications that use a database to store data you must have run into a problem how to do updates on the database once the application goes into production and you need to update the production database structure and/or data with new functionality or bug fixes made on your development database. Some database tools (Oracle SQL developer) help you in this by having an integration with SVN. This means that you have a history of various database versions that you have been working on, but it takes some work to use that data to create update scripts which you can apply on your production system.

If you are using Oracle as your database solution then this problem becomes much easier because Oracle supports DDL triggers. DDL triggers enable you to do some work once a DDL command is executed. Combining this trigger with some logging can enable you to quickly produce database update scripts. Note: Everything done on the "SYS" schema is done as a SYS user. Everything else is done by 'normal' db users.To start with, we are going to need a database table to log our changes:

CREATE TABLE "SYS"."DDL_LOG" (   
    "ID" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
    "USER_NAME" VARCHAR2(30 BYTE),
    "DDL_DATE" DATE,
    "DDL_TYPE" VARCHAR2(30 BYTE),
    "OBJECT_TYPE" VARCHAR2(18 BYTE),
    "OWNER" VARCHAR2(30 BYTE),
    "OBJECT_NAME" VARCHAR2(128 BYTE),
    "SQL_TEXT" CLOB,
    "EXECUTE_TEXT" CLOB
);

Here is the explanation of the columns:

  • ID - id of the log (I'll explain later why we need it)
  • USER_NAME - name of the Oracle user that made the DDL command
  • DDL_DATE - date and time of the modification
  • DDL_TYPE - type of modification (ALTER, CREATE etc.)
  • OBJECT_TYPE - type of object on which the modification was made (TABLE, TRIGGER etc.)
  • OWNER - schema owner
  • OBJECT_NAME - name of the object that was modified (created, altered dropped etc.)
  • SQL_TEXT- actual command that was executed
  • UPDATE_TEXT -I'll explain this later

Here is the actual trigger that will do the logging:

CREATE OR REPLACE TRIGGER "SYS"."DDL_TRIG"
    AFTER DDL
ON DATABASE
DECLARE
    v_date varchar2(100);
    v_id number;
    v_stmt clob;
BEGIN
    IF ora_dict_obj_owner = 'DEVELOPMENT' THEN
        v_date := TO_CHAR(sysdate, 'YYYY-MM-DD');
        SELECT ddl_log_seq.nextval INTO v_id FROM dual;
        v_stmt := REPLACE(last_sql_text(), '''', '''''');

       INSERT INTO DDL_LOG (
        id,
        user_name,
        ddl_date,
        ddl_type,
        object_type,
        owner,
        object_name,
        sql_text,
        execute_text
        ) VALUES (
        v_id,
        ora_login_user,
        SYSDATE,
        ora_sysevent,
        ora_dict_obj_type,
        ora_dict_obj_owner,
        ora_dict_obj_name,
        last_sql_text(),
        'execute_if_not_executed(''' || ora_login_user || ''', ''' || v_date
        || ''', ' || v_id || ', ''' || v_stmt || ''');'
        );
    END IF;
END;

The trigger is pretty straightforward, it just fills the DDL_LOG table if the DDL commands were made on the DEVELOPMENT schema. What is interesting are the fields ID and EXECUTE_TEXT.

Why do we need ID field? This is because, later on, we need to create some sort of a script from the records in the DDL_LOG table. This script should have all the commands executed in the same order as the developers have executed them on the development database. While we could use DDL_DATE field for this, it is not a good solution when you are using database management tools.

The tools (such as PL/SQL developer or Oracle SQL developer) usually execute DDL commands in a batch so you get a lot of commands within the same millisecond. Ordering by DDL_DATE field does not help you much in those cases, but if you have ID then you can be certain of the order in which the DDL commands were executed.

UPDATE_TEXT field also serves to help us create an update script. This field will be filled with a procedure call ('execute_if_not_executed') that can be executed on the production server. Why would we need such an elaborate system? Why not just take the SQL_TEXT and run it on the production? This is certainly possible, but one of the things that might occur (and does occur) on the production database (and even more so if you have multiple production databases) is that a DDL command or a group of commands never get executed!

You may simply overlook a system because you forgot about it, or the guy responsible for updates got sick and someone else does a bad job out of it. If such an error occurs frequently it would be nice to have a log of updates (DDL commands) that have been made on the database. It would be even nicer if you could automatically check if an update was executed and execute it if necessary. This is exactly what the 'execute_if_not_executed' function does: Checks if we have already executed a command If not then executes it Records that the update has been made so that future calls will not do an update again The update records are kept in a database table for each production database.

CREATE TABLE "PRODUCTION"."SCHEMA_VERSION" (
    "VERSION_USER" VARCHAR2(50 BYTE) NOT NULL ENABLE,
    "VERSION_DATE" DATE NOT NULL ENABLE,
    "VERSION_NUMBER" NUMBER NOT NULL ENABLE,
    CONSTRAINT "SCHEMA_VERSION_UK" UNIQUE
    ("VERSION_USER", "VERSION_DATE", "VERSION_NUMBER") ENABLE
);

Here is the EXECUTE_IF_NOT_EXECUTED procedure.

CREATE OR REPLACE PROCEDURE "PRODUCTION"."EXECUTE_IF_NOT_EXECUTED"
(
    p_user in VARCHAR2,
    p_date in VARCHAR2,
    p_order_no in NUMBER,
    p_command in VARCHAR2
)
    AUTHID CURRENT_USER
AS
BEGIN
    IF EXISTS_VERSION(p_user, p_date, p_order_no) = 0 then
        EXECUTE IMMEDIATE p_command;

        insert_version(p_user, p_date, p_order_no);
        commit;
    end if;
END;

The EXECUTE_IF_NOT_EXECUTED procedure is relatively simple. It takes parameters which serve to uniquely identify the update command (user, date and id) and the actual DDL command text. One thing that is specific is that the procedure has AUTHID CURRENT_USER option set. This gives the procedure will execute the DDL commands with the same rights as the user that is executing the procedure. You may remove this option which means that the rights of the user that created the procedure will be used when executing the DDL commands.
This depends on how you have set up your update process. Once you have everything in place, all DDL commands that you are making on developer database will be recorded into the DDL_LOG table. When you want to create an update script just browse the table and copy/paste the update commands into a file, surround them with begin/end; and you are good to go.

Appendix: Several various functions and procedures have also been used but I will not explain them since they are pretty much straightforward. I am just going to list them here.

CREATE OR REPLACE FUNCTION "PRODUCTION"."LAST_SQL_TEXT"
    RETURN CLOB IS
    v_sql_text ORA_NAME_LIST_T;
    v_stmt CLOB;
    n INTEGER;
    i INTEGER;
BEGIN
    n := ORA_SQL_TXT(v_sql_text);
    FOR i IN 1..n LOOP
        v_stmt := v_stmt || REPLACE(SQL_TEXT(i), chr(0));
    END LOOP;
    RETURN v_stmt;
END;

CREATE OR REPLACE FUNCTION "PRODUCTION"."EXISTS_VERSION"
(
    p_username in VARCHAR2,
    p_isoDate in VARCHAR2,
    p_versionNum in NUMBER
)
    RETURN NUMBER
    IS
    v_counter NUMBER;
BEGIN
    SELECT COUNT(1) INTO v_counter FROM SCHEMA_VERSION
    WHERE VERSION_USER = p_username AND
    VERSION_DATE = parse_iso_date(p_isoDate) AND
    VERSION_NUMBER = p_versionNum;
    IF counter = 0 then
        RETURN 0;
    ELSE
        RETURN 1;
    END IF;
END;

CREATE OR REPLACE PROCEDURE "PRODUCTION"."INSERT_VERSION"
(
    p_username in VARCHAR2,
    p_isoDate in VARCHAR2,
    p_versionNum in NUMBER
)
    IS
BEGIN
    INSERT INTO SCHEMA_VERSION (VERSION_USER, VERSION_DATE, VERSION_NUMBER)
    values (p_username, parse_iso_date(p_isoDate), p_versionNum);
END;


CREATE OR REPLACE FUNCTION "PRODUCTION"."PARSE_ISO_DATE"
(
    p_isoDate in varchar2
)
    RETURN DATE
    IS
BEGIN
    RETURN TO_DATE(p_isoDate, 'YYYY-MM-DD');
END;