Sometimes, we want to change the format of a column in database, say from NUMBER(12,2) to NUMBER(15,5). The first number is precision (number of digits) and second is scale (number of decimal places), e.g 15,5 means 10 whole numbers and 5 decimal places. For more info, see Oracle documentation.

The precision is limited to 38 (or use * as synonym) and in our db I have seen disturbing number of declarations like WORKLOAD(38,2). Oracle makes it easy to increase scale, but only if you also can increase precision:

-- The column is NUMBER(12,2)
alter table T_TEST modify (WORKLOAD NUMBER(15,5))

If you use 38 or * as precision, it gets more complicated and you have to do the heavy lifting on your own, because there are data that could potentially be lost. Most common way is to create a second column, copy the data, drop original column and rename former column. The other way is to set the original column to null and then you can use the alter table from above (see Stack Overflow for more details).

That works, but there may be constraints and you can lose stuff, not to mention it is a lot of work, prone to error… it would be nice to create a procedure that allows user to change format of the column.

Limitations:

• Expects that column is a NUMBER
• Expects that PK is named id

Requirements:

• Inplace change, won’t change column ordering
• Must works even if column has some constraints
• Must not lose data, e.g. changing from NUMBER(7,3) to NUMBER(5,2) will work, but only if there is no row that has a column with number like 12.456 or 1234.
• Must work across schema

set serveroutput on
declare
  p_owner varchar2(64 char) := 'APPLICATION';
  p_table varchar2(64 char) := 'T_TEST';
  p_column varchar2(64 char) := 'NUM';
  p_format varchar2(64 char) := 'NUMBER(15,5)';
  l_sql varchar2(512 char);
  l_temp_col varchar2(64 char);
  l_diff_ids clob; -- there can be quite a lot of different ids
  
  procedure disable_constraints(p_owner varchar2, p_table varchar2, p_column varchar2) is
    l_sql varchar2(512 char);
  begin
    for l_constraint in (select owner, table_name, constraint_name from ALL_CONS_COLUMNS 
                                where owner = p_owner
                                  and table_name = p_table
                                  and column_name = p_column)
    loop
      -- disable constraints 
      l_sql := 'alter table ' || l_constraint.owner || '.' || l_constraint.table_name || ' disable constraint ' || l_constraint.constraint_name;
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
    end loop;
  end;

  procedure enable_constraints(p_owner varchar2, p_table varchar2, p_column varchar2) is
    l_sql varchar2(512 char);
  begin
    for l_constraint in (select owner, table_name, constraint_name from ALL_CONS_COLUMNS 
                                where owner = p_owner
                                  and table_name = p_table
                                  and column_name = p_column)
    loop
      -- disable constraints 
      l_sql := 'alter table ' || l_constraint.owner || '.' || l_constraint.table_name || ' enable constraint ' || l_constraint.constraint_name;
      dbms_output.put_line(l_sql);
      execute immediate l_sql;
    end loop;
  end;
  
begin
  dbms_output.put_line('Create temp column');
  
  -- beware oracle 30 char naming limit
  select 'TEMP_COLUMN_' || dbms_random.string('U', 10) into l_temp_col from dual;
  l_sql := 'alter table ' || p_owner || '.' || p_table || ' add ' || l_temp_col || ' ' || p_format;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;
  
  -- copy values to the new column, if values don't fit into new column, but only for upper bound (e.g. the whole number), I get ORA-01438
  -- for fractions, it doesn't work that way
  l_sql := 'update ' || p_owner || '.' || p_table || ' set ' || l_temp_col || ' = ' || p_column;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;

  -- validate that values are same in new format as were in old format
  l_sql := 'select listagg(id, '','') within group (order by id) from ' || p_owner || '.' || p_table || ' where ' || p_column || ' - ' || l_temp_col || ' != 0';
  execute immediate l_sql into l_diff_ids;
  if l_diff_ids is not null
  then
    -- drop temp column
    execute immediate 'alter table ' || p_owner || '.' || p_table || ' drop column ' || l_temp_col;
    raise_application_error(-20010, 'Values are different in the new format ' || l_diff_ids);
  end if;

  dbms_output.put_line('Disable constraints');
  disable_constraints(p_owner, p_table, p_column);
  
  -- set old to null
  l_sql := 'update ' || p_owner || '.' || p_table || ' set ' || p_column || ' = null';
  dbms_output.put_line('Change format of the column ' || p_column);
  dbms_output.put_line(l_sql);
  execute immediate l_sql;

  -- change format 
  l_sql := 'alter table ' || p_owner || '.' || p_table  || ' modify (' || p_column || ' ' || p_format || ')';
  dbms_output.put_line(l_sql);
  execute immediate l_sql;

  l_sql := 'update ' || p_owner || '.' || p_table || ' set ' || p_column || ' = ' || l_temp_col;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;
  
  -- drop temp column
  l_sql := 'alter table ' || p_owner || '.' || p_table || ' drop  column ' || l_temp_col;
  dbms_output.put_line(l_sql);
  execute immediate l_sql;
  
  dbms_output.put_line('Enable constraints');
  enable_constraints(p_owner, p_table, p_column);
end;
/

It is not the prettiest code in the world, but it works.

We have rather intranet WebForms app that is using WebForms and is WebSite application. I am converting it to the Web Application Project using a great walkthrough from Microsoft.

WebSite vs Web Application

What are the major differences between these two projects and what advantages do I expect? You can find talkative comparison on MSDN or use more succinct one on StackOverflow. Basically the WebSite is a bunch of aspx files compiled on the fly on the server, while Web Application is precompiled dlls.

• Faster build. I am not sure why, but when I build website to check for static errors, I have to rebuild everything, rebuild takes few minutes, while WPA converted one is in seconds.
• Easier dependencies. WebSite requires user to manually specify dlls it depends on. Wweb Application is a real project and NuGet is available.

There are few other, but WPA is simply better while WebSite looks like technically inferior solution from 2005.

How does it work?

Great! Simply follow the walkthrough and you will be OK. I had it converted in several hours:

1. Create  Empty Web Project Application
2. Copy all files (except the bin with *.refresh files and the publish file) from WebSite to the WPA
3. Include all copied files to the WPA
4. Right click on the project in Solution explorer, select Convert to Web Application, OK.
5. The automatic conversion moved the App_Code to Old_App_Code, because it is the only place where WPA dynamically compiles code, and should be empty. Rename it to something sutable, e.g. App_Start
6. Try to build the WPA, you will get a lot of errors because of dependencies. Add dependencies, preferably using NuGet.
7. In 2 cases, I couldn’t access the aspx controls from code behind and had to resort to …FormView.FindControl(“ControlId”).Method() instead of ControlId.Method().
8. You are done!

I have downloaded rather large site full of HTML and few PDF files and stored it on my Raspberry Pi (my constantly running linux toy). It is not too large (few GB and tens of thousands of files), but it is rather annoying to wait for MidnightCommander content search.

Since they are mostly HTML and PDF files, I thought that a search engine would be nice. My requirements were:

• Must have CLI interface, I don’t have a monitor attached and no desire to run remote desktop.
• Efficient and small, raspberry pi has something like 512 MB ff memory.

Quick googling reveleased few contestants: Sphinx search (its CLI is only for debugging purposes – Nope), Lucene and Tracker.  Lucene is Java-based, but with quite small memory footprint (1MB memory heap) with a lucli CLI interface. I kind of regret not choosing it. Anyway, I chose Tracker, poorly documented search engine with issues (mostly lack of documentation). It is supposed to be

Designed and built to run well on lower-memory systems with typically 128MB or 256MB memory. Typical RAM usage is 4-6 MB.

Installation

apt-get install --no-install-recommends tracker-utils tracker-miner-fs libglib2.0-bin

Everything is in packages, simply install it. The most important program is tracker-control that can start miners, reset them or give you status of the indexing. You need libglib2.0-bin for gsettings utility that allows user to change the gconfig from CLI.

Configuration

If you try to run tracker-control without X11, you get an error:

honza@pina ~ $ tracker-control -s
Starting miners…
Could not start miners, manager could not be created, Command line `dbus-launch --autolaunch=3b0e4b712f60d6b9547b25ae51c194dd --binary-syntax --close-stderr' exited with non-zero exit status 1: Autolaunch error: X11 initialization failed.

Somone else already encountered the problem, the solution is:

eval `dbus-launch --auto-syntax`

It is not pleasant, because you have to manually start the Tracker each time you log in, so you should put it into your login scripts.

You can see all configuration options of Tracker using

gsettings list-recursively | grep -i org.freedesktop.Tracker | sort | uniq

I was interested in searching only one directory, so I changed the index-recursive-directories

gsettings set org.freedesktop.Tracker.Miner.Files index-recursive-directories "['/home/pi/website-mirror']"

Starting the miners

You can start the miners using tracker-control

honza@pina ~ $ tracker-control -s
Starting miners…
  ✓ Applications
  ✓ File System

And after that check the progress using the status option

honza@pina ~ $ tracker-control -S
Store:
27 Jul 2014, 12:44:29:  ✓     Store                 - Idle

Miners:
27 Jul 2014, 12:44:31:  ✓     Applications          - Idle
27 Jul 2014, 12:44:33:   32%  File System           - Processing… 01h 03m 32s remaining

Once you have that, you can easily search for the term using tracker-search

honza@pina ~ $ tracker-search ping
Results:
  file:///home/pi/website-mirror/000458.html
  file:///home/pi/website-mirror/005495.html
  file:///home/pi/website-mirror/019534.html

I also have an application tracker that should find applications, but in default settings, it is probably limited to the Gnome desktop and not programs in /bin.

Logging

You can set logging either through the gsettings (for each component separately) or using tracker-control for all of them at once. The default level errors. Possible values are [debug|detailed|minimal|errors].

gsettings set org.freedesktop.Tracker.Miner.Files verbosity 'detailed'
// Or for all
tracker-control --set-log-verbosity=detailed

The logs are stored in $HOME/.local/share/tracker directory.

End notes

Someone else has also tried to his 5 minutes with Tracker, my observations are similar>

• Name is horrible, it was hard to google anything with such generic name. Even Tracker itself giver warning when trying to search for common words (Search term ‘index’ is a stop word. Stop words are common words which may be ignored during the indexing process.)
• It is not running without X out of the box. Rather annoying.
• Search works, but I would probably choose Lucene next time.

This is the very first time I got my own domain. I have never bought one before, mostly because I don’t feel comfortable with the all info in the WHOIS register. I’ve bought one from namesilo and I’ve got a WHOIS privacy guard included – the WHOIS registry has a see PrivacyGuardian.org as its registrant/admin/billing/tech, but if you really want, you can send email… that will probably end up in black hole of spam. I have sent email to the address in WHOIS registry and I have also used a form at the PrivacyGuradian webpage, not sure where it will turn up.

Update: I’ve got the email from the form on the PrivacyGuard page(my contact email from namesilo), but not from the WHOIS db.

I have no illusions about level of privacy granted by this arrangement, but a relative told me about one of her clients who was inpersonated by someone else (=a case of identity theft). It was not pretty and the bureaucracy took a while to stop. It required some time, effort, money and a lawyer. The less info is freely laying around the better.

Privacy Guard has some side-effects, the most glaring one is that who is in the WHOIS register is the domain owner. When one registrar went under, there were serious trouble with domain ownership.

I am running a WordPress, and while I am only scrating surface, it is quite troublesome to write a code snippets in the editor.

I have installed following plugins that alleviated my trouble:

• Prettify Code SyntaxPrettify GC Syntax Highlighter – The GC Syntax highlighter has a noquote class that makes switching between text and visual mode much easier (the PCS always escapes < and >, thus when user switches from text to visual, the elements like <some-tag> disappear).
• Tab Override
• Visual Editor Custom Buttons