Gartner on the Sun / MySQL deal

Heh, who knew we'd see the day that Gartner would write this:

Recommendations for all enterprises
...
MySQL is capable of supporting clusters, delivering atomic, consistent, isolated and durable (ACID) functionality and handling some mission-critical applications. Evaluate MySQL as an alternative to IBM DB2, Oracle and other DBMSs.

Well, fact of the matter is, they did write it in a recent report on the MySQL / Sun deal. You can download the Gartner analysis right here free of charge.

MySQL Cluster Study Guide getting good comments

One of the major projects I worked on last year was the MySQL 5.1 Cluster DBA certification. I did the background research to come up with a sensible scope and outline of the exam, wrote most of the questions and actively looked for feedback from our cluster engineering and development team as well as consultants that are deploying MySQL Cluster in real-world situation.

For example, I got great feedback on the exam from MySQL Cluster Senior Software Engineer Stewart Smith

...Response from the exam is overwhelmingly positive and as it’s new, afterwards I discussed a few questions with Roland (in fact, all the ones I got wrong). The (good!?) news is that I legitimately brain-farted on some.

... this isn’t some trivial know-a-tiny-bit-and-score-really-high certification...this is a serious one).

I may have heard at some point one of our people saying they thought it was the best certification exam we have...

I guess that could've turned out worse, heh? ;-)


I was also involved in the preparation and coordination of the creation of the accompanying study guide, did a fair share of reviewing the content and eventually I authored almost 3 chapters.

All in all it was a big project, and I learned a great deal in the process. Unfortunately, due to a number of reasons it took a while before we could actually publish the book, but now that we did, we are getting a number of pretty good comments. Take for example, Alan Snelson ordered the book at lulu.com en judging from his blog he enjoyed it a lot:

I booked the exam a couple of weeks ago thinking I might need some incentive to fully digest MySQL Cluster details. I need not have worried, the book provides a great introduction to MySQL Cluster in easy-to-read chapters bringing you up to speed in no time, much more entertaining than the reference manual!

Thanks Alan for your kind words! I'm glad you passed the exam.

Are you curious about the MySQL Cluster Certification? You can read more about it in the candidate guide, and if you feel lucky, you can even take a free evaluation test before you sign up for the real thing.

Enjoy, and good luck with your Exams ;-)

TeProFoKATeMUR - News from The Project Formerly Known as The MySQL UDF Repository

Hi all - boy, have I got news for you!

I would like to announce that The MySQL UDF Repository is no more. The rumour is that its growing popularity has caused it to be noticed by some folks at MySQL AB's legal department, kindly requesting that the project's name be changed to something even cooler.

However, these are just rumours - this is only the internet, so relying on a first-hand source, I can safely confide in you that the project's name and website are being changed mainly out of religious conviction to offer even more and better free UDFs for the MySQL database.

During the transition, the project can be referred to using this unpronounceable symbol:



However, in some circles, people refer to this symbol as TeProFoKATeMUR, which is believed to be an acronym of ThE Project Formerly Known As "ThE MySQL UDF Repository". The real incrowd however prefers to call it simply The Symbol.

The latest news that I heard through the grape vine is that the Project's identity crisis may already be over as I am writing this. I hear people whispering that a new name for the project will be announced shortly, and although the final name is not yet released it will most likely be either one of "The Order of Valiant Knights In Defense of the Honour of Fast-performing Freely Available Functions for MySQL" or simply The UDF Repository For MySQL.

I wouldn't know whether it is good or bad that the project is abandoning it's name. What I do know is that the project's new website is looking way better than the old one.



It has got an easier to remember URL too: http://www.mysqludf.org/. The Project's google group has not moved though - it is still available at http://groups.google.com/group/mysql-udf-repository and welcoming new members all the time.

If this is indicative for the other things the project has to offer, then these changes sure are promising. In terms of last developments, it is believed that the group is working on a UDF library for MySQL that wraps around libcurl to enable internet access using MySQL functions:

mysql> select * from mysql.func;
+----------------------------+-----+----------------------+----------+
| name                       | ret | dl                   | type     |
+----------------------------+-----+----------------------+----------+
| http_request               |   0 | lib_mysqludf_curl.so | function |
| lib_mysqludf_curl_info     |   0 | lib_mysqludf_curl.so | function |
| http_version_1_0           |   2 | lib_mysqludf_curl.so | function |
| http_version_1_1           |   2 | lib_mysqludf_curl.so | function |
| http_method_get            |   2 | lib_mysqludf_curl.so | function |
| http_method_delete         |   2 | lib_mysqludf_curl.so | function |
| http_version_none          |   2 | lib_mysqludf_curl.so | function |
| http_method_head           |   2 | lib_mysqludf_curl.so | function |
| http_method_post           |   2 | lib_mysqludf_curl.so | function |
| http_method_put            |   2 | lib_mysqludf_curl.so | function |
| http_method_trace          |   2 | lib_mysqludf_curl.so | function |
| http_authtype_basic        |   2 | lib_mysqludf_curl.so | function |
| http_authtype_digest       |   2 | lib_mysqludf_curl.so | function |
| http_authtype_gssnegotiate |   2 | lib_mysqludf_curl.so | function |
| http_authtype_ntlm         |   2 | lib_mysqludf_curl.so | function |
| http_authtype_any          |   2 | lib_mysqludf_curl.so | function |
| http_authtype_anysafe      |   2 | lib_mysqludf_curl.so | function |
| url_encode                 |   0 | lib_mysqludf_curl.so | function |
| url_query                  |   0 | lib_mysqludf_curl.so | function |
| url_encode_component       |   0 | lib_mysqludf_curl.so | function |
+----------------------------+-----+----------------------+----------+
20 rows in set (0.00 sec)

mysql> select lib_mysqludf_curl_info();
+-----------------------------------------------------------------------------------+
| lib_mysqludf_curl_info()                                                          |
+-----------------------------------------------------------------------------------+
| lib_mysqludf_curl version 0.0.1/libcurl/7.16.4 GnuTLS/1.6.3 zlib/1.2.3 libidn/1.0 |
+-----------------------------------------------------------------------------------+
1 row in set (0.11 sec)

mysql> set @page := http_request('http://www.mysqludf.org/' as url);
Query OK, 0 rows affected (0.04 sec)

mysql> select substring_index(substring_index(@page,'</title>',1),'<title>',-1);
+-------------------------------------------------------------------+
| substring_index(substring_index(@page,'</title>',1),'<title>',-1) |
+-------------------------------------------------------------------+
| UDF Repository for MySQL - Home                                   |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

An interesting year ahead for people interested of MySQL UDFs....

Speaking at the 2008 MySQL Conference

Yes - it's that time again ;-)


The new year has just started which means the MySQL Conference and Expo is little more than 3 months away. I am very glad to have the opportunity to attend, and even more glad to be speaking.

I will be doing two sessions:

• Writing MySQL UDFs - A 3 hour tutorial for people that want to get started writing MySQL UDFs. PHP and/or Java skills recommended - No pre-existing C skills required.
• Grand tour of the Information Schema and its applications - a session that demonstrate how to make practical use of the information_schema

I'll provide some more details about my sessions shortly, but enough of that for now. Instead, I'd like to share a list of some of the sessions (in no particular order) I am looking forward to attend:

• Astronomy, Petabytes and MySQL, Kian-Tat Lim; Stanford Linear Accelerator Center:

  The Large Synoptic Survey Telescope (LSST) will produce more than 100 petabytes of data when it goes on-line in 2014. All the non-pixel data will be managed by a database. This database is expected to contain some 50 billion astronomical objects, 3 trillion detections of those objects, and all the project’s metadata; a single copy will amount to tens of petabytes.



• Architecture of Maria, the New Transactional Storage Engine for MySQL - Michael "Monty" Widenius; MySQL AB: Overall design of this new transactional (MVCC) storage engine.


• DBSlayer: A Simpler Way To Proxy, Derek Gottfrid; The New York Times:

  The DBAccessLayer or DBSlayer (http://code.nytimes.com/projects/dbslayer) for short is a lightweight, open source, web aware proxying and pooling layer for MySQL used for backend architecture at nytimes.com. Assembled out of a few open-source libraries, DBSlayer communicates with clients using HTTP as a protocol and JSON as an encoding, instead of the custom binary protocols common to other proxying mechanisms. This enables the DBSlayer to easily interoperate with any programming languages that speak HTTP and JSON.



• External Language Stored Procedures for MySQL, Antony Curtis and Eric Herman; MySQL AB:

  With version 5.0, MySQL delivered a stored procedure implementation and here we extend it to provide general support for external language stored procedures. We detail the philosophy and design of the external language stored procedure framework and its implementation within the server and libraries. We then also detail how an external language plug-in is written using the simple skeleton plug-in which exercises the basic interfaces between the server and external stored procedures.



• MySQL Cluster with Replication for Financial Transactions - Cicero Torteli; Paggo
• Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community - Farhan Mashraqi; Fotolog:

  Fotolog is a top 19 Internet destination with more than 12 million members, 315 million photos and more than 3 billion page views a month. Through modifications to its data architecture, Fotolog was able to serve four times the number of users using the same number of database servers. A non-conventional, hybrid presentation that conveys the importance of scalability, performance tuning and schema optimizations in a practical way.

• Geographical Information and OpenGIS on MySQL - Markus Franz, BF Blogform Search GmbH:

  In this presentation, Markus Franz shows how to efficiently handle geographical information in a MySQL based environment. The presentation covers setup of a structured database, calculation of distances between two or more points, social tagging for data and solutions to automatically guess geo tags from existing content. This session is highly recommended for those who look for powerfull ways to enhance existing business infrastructures with geographical information.

• MySQL Proxy Wizardry - Giuseppe Maxia; MySQL AB:

  Are you tired of the limiting stored routines language? Can you distribute queries to several servers transparently? Try the power of MySQL Proxy. You will see an apparently normal MySQL server do all the above, and much more, with the addition of MySQL Proxy, a lightweight application that extends the server features on the fly.



• The Lost Art of the Self Join Beat Vontobel, Meteonews AG:

  Can you solve a Sudoku with only one SELECT statement? No temporary tables, SQL extensions, stored procedures or functions, not even non-relational features such as ORDER or GROUP BY—just plain joins and predicates? You can! With a combination of (admittedly quite a few) self joins.



• Developing INFORMATION_SCHEMA Plugins, Mark Leith; MySQL AB:

  A walk through of a very basic ‘HELLO WORLD’ INFORMATION_SCHEMA table, showing a user the very bare minimum that is required to create a pluggable I_S table. Then, examples of making some more interesting plugins – such as including the SIGAR OS statistics gathering library to collect CPU, Memory, Disk, Network and OS Configuration data within I_S tables, and a general ‘df -h’ INFORMATION_SCHEMA table.

• EXPLAIN demystified, Baron Schwartz; The Rimm-Kaufman Group, LLC:

  I learned EXPLAIN by studying source code, documentation, and learning from the query optimizer team. I verified everything I learned with complex test cases. Along the way I discovered how to reverse-engineer EXPLAIN into a query execution plan. Finally, I wrote a program to do this—MySQL Visual Explain, the first of its kind. In this session I’ll teach EXPLAIN from the ground up.

And this is just the tip of the iceberg!

There is just so much good stuff, it's hard to single out a few. Anyway - take a look at the schedule yourself. Word is that you can get Early Bird discounts up to somewhere in Februari, and there is also a 15% Alumni discount for attendees of past conferences. Check out the details here.

CU @ UC!!!

Calculating the Financial Median in MySQL

I believe I found a new method to calculate the median in MySQL. I would not be surprised if this method has been figured out by somebody else already. However, I can't seem to find any resources on the internet describing this method, so for now I flatter myself by assuming the method is original.

(Please do post your comments to this blog to correct me on that should I be wrong so I have a chance to rectify.)

The method I'm describing is a one-pass, pure SQL method. It does not require subqueries, cursors or user variables. However, it does rely on the MySQL specific functions GROUP_CONCAT() and SUBSTRING_INDEX()

I'll be maintaining a snippet for this method at MySQL Forge.
If you want to know what the median is, and how my snippet works, read on.

Some background

Like the mean and the mode, the median is an important metric to characterize the distribution of values in a collection. If we have a ordered collection of (numerical) values, the median is the value for which the number of entries that has a value that is higher than the median is exactly equal to the number of entries that has a value that is lower than the median. If there is an odd number of entries in the collection, the value of the median corresponds to the value of the entry that lies exactly in the middle of the list. If there is an even number of entries, the median is calculated as the mean of the two middle values.

MySQL offers a number of aggregate functions. Unfortunately, MySQL does not offer a function to calculate the median.

Even though MySQL does not support a MEDIAN() function natively, it is still possible to calculate it. You can:

• install one of the numerous UDF's floating around on the internet


• Use pure SQL like suggested in Chapter 23 of Joe Celko's SQL for Smarties

The snippet

Here's a snippet that shows how to calculate the median replacement cost for a film in the sakila sample database:

select
        (
            substring_index(                          -- left median: max value in lower half:
                substring_index(
                    group_concat(                     --   list all values in ascending order
                        f.replacement_cost
                        order by f.replacement_cost
                    )
                ,   ','
                ,   ceiling(count(*)/2)               --   left half of the list 
                )
            ,   ','
            ,   -1                                    --   keep only the last value in list
            )
        +   substring_index(                          -- right median: min value in upper half:
                substring_index(
                    group_concat(                     --   list all values in ascending order
                        f.replacement_cost
                        order by f.replacement_cost
                    )
                ,   ','
                ,   -ceiling(count(*)/2)              --   right half of the list 
                )
            ,   ','
            ,   1                                     --   keep only the first value in list
            )
        ) / 2                                         -- average of left and right medians
        as median
from    sakila.film f;

(For the latest version, refer to MySQL Forge)

So, how does this snippet work? In the remainder of this post, I'll explain the inner workings of this method in a top-down fashion.

The mean of the left and right median

The method I'm describing always takes the mean of the 'left' and 'right' median.

select
        (
            left-median(f.replacement_cost)
        +   right-median(f.replacement_cost)
        ) / 2                                         -- average of left and right medians
        as median
from    sakila.film f;

(Note that the usage of left-median() and right-median() is just an explanation of the structure - in reality there are not two distinct functions by that name)

The terms 'left median' and 'right median' are not common so they need an explanation.

Let's visualize the process to determine the median. We can do this by imagining that we have an ordered list of values and that we point our left index finger to the lowest value in the list and our right index finger to the highest value in the list.

Now, we look at our fingers. If there is more than one entry between our right and left finger, we move or left finger one entry to the right and our right finger one entry to the left, and we keep doing that until there are no more entries between our left and right finger. Once we're there, the value of the entry that is pointed to by our left finger is the 'left median' and the value of the entry pointed to by our right finger is the 'right median'.

If we had an even number of entries, then the left and right median each correspond to distinct entries - if there was an odd number of entries then the left and right median correspond to one and the same entry.

At any rate, once we found the left and right median, it is clear that their mean is the true median. If we have an even number of entries, we have to calculate the mean of the two middle values anyway, and if there is an odd number of entries, taking the mean of two identical values results of course in that same value which does thus result in a correct value for the median.

GROUP_CONCAT: an ordered list of values

In the example, we use GROUP_CONCAT to generate a list of values in ascending order:

GROUP_CONCAT(                     --   list all values in ascending order
    f.replacement_cost
    ORDER BY f.replacement_cost
)

This gets us a string consisting of concatenated replacement_cost values in ascending order, separated by the default separator, which is a comma (',').

We use the same list in both the calculation of the left and the right median.

Note that the length of the concatenation result returned by GROUP_CONCAT() is limited. By default, it is as small as 1024 bytes. Personally I think this is way too small so I have it configured to be 64K by default. You can set the length at runtime too like this:

    SET group_concat_max_len := 65535

You can specify larger values than 65535 too, and I suspect that the maximum packet size is the practical maximum:

    SET group_concat_max_len := @@max_allowed_packet

To inspect the current value, you can do this:

    SELECT @@group_concat_max_len

Getting the 'left' half of the list

Once we have the list of values, we can split it in two halves with little effort. We do this using the SUBSTRING_INDEX() function.

The SUBSTRING_INDEX() function processes a string argument and gets a substring based on the position of a particular occurrence of another substring.

In this case, the comma ',' is the substring that separates the values in our ordered list. But of all the commas in the list, which occurrence of the comma do we need to find?

Suppose our list contains 4 values. Then, these values are separated by three comma's:

values: '1,2,3,4'
commas:   ^ ^ ^
          1 2 3

If we want to divide this list in two equal halves, then the second comma is the divisor between the left and right halves of our list. With SUBSTRING_INDEX() this expression would get us the left half of the this list:

    SUBSTRING_INDEX('1,2,3,4', ',', 2) -- the substring up to the 2nd occurrence of ','

and the result will be:

    '1,2'

So now we have the first half of the list, and by definition, the last entry in that list, '2' is the left median of the original list '1,2,3,4'.

Now what if we would've had an odd number of entries in our list? Supose our list would've been like this:

values: '1,2,3'
commas:   ^ ^
          1 2

In this case too, we need the second comma to end up with a left substring that has the left median as last entry in the list (which also happens to be the proper median because this is a list with an odd number of entries).

As it turns out, we can conveniently generalize the SUBSTRING_INDEX expression like this:

    SUBSTRING_INDEX(list, separator, CEILING(#entries/2))

In other words, if we divide the number of entries in our list by two, and then round to the nearest higher integer, this gives us the particular occurrence of the separator what we are looking for to halve our list as required. Of course, calculating the number of entries is simply a matter of using the COUNT() aggregate function.

Excising the 'left' median

To actually obtain the left median itself, we just need to excise the last value from the left half of the list of values. We do this by applying SUBSTRING_INDEX() again.

Again, we need to make a substring in terms of the occurrence of the comma that separates the values in our list. This time, we need to get the substring found directly after the last comma in the list. With SUBSTRING_INDEX() we can conveniently express this in the following manner:

    SUBSTRING_INDEX(list, separator, -1)

This means: search the list from right to left and find the first occurrence of the separator. Return the substring that appears after the separator (that is, the substring appearing on the right hand of the separator).

Getting the 'right' median

The process to obtain the right median is a mirror of obtaining the left median: instead of obtaining the last value in the left half of the ordered list of values, we now need to obtain the first value of the right half of the list. This is actually as simple as reversing the sign of the occurrence argument in the SUBSTRING_INDEX() calls:

    left half:   SUBSTRING_INDEX(list, separator,  CEILING(COUNT(*)/2))
    right half:  SUBSTRING_INDEX(list, separator, -CEILING(COUNT(*)/2))

    last entry:  SUBSTRING_INDEX(list, separator, -1)
    first entry: SUBSTRING_INDEX(list, separator,  1) 

A few remarks

I think that in many cases, this can be a fair method to calculate the median. The advantage of this method is that it is relatively fast because the query itself is relatively simple.

It would be interesting to see how this method behaves when handling millions of rows. Maybe I will run some benchmarks on that later on.

In the mean while, feel free to post your thoughts, suggstions or critique on this blog.

Arnold Daniels' lib_mysqludf_xql featured on MySQL devzone

Wohoo! Jon Stephens just published an excellent article on the possibilities for working with XML and MySQL on the MySQL devzone!

I'm very proud that he chose to highlight the lib_mysqludf_xql library of MySQL User Defined functions authored by Arnold Daniels. Some time ago, Arnold joined the MySQL UDF Repository and so far he's been the most active member of the group.

Arnold also took the initiative to set up a new website for the MySQL UDF Repository, and we will soon move all content and libraries from the old location to the new site. (The main reason it hasn't happened yet is all due to me - i need to make time to move the old content to the new site).

If you want to discuss lib_mysqludf_xql or if you want to joint the UDF repository, sponsor our work or contribute in any other way, just go to our google group and post your questions.

Hacking Open Office Writer Lists

Here's a tip I'd like to share. It's about Writer, the OpenOffice.org Word Processor, but all concepts and described functionality are available als in Microsoft Word.

This tip will show you how to create styles to create notes in your documents. Among the more interesting things, the style does not only take care of the formatting, it also takes care of the static text that is repeated for every note. All this achieved without using macro automation.

A Note

Everybody that has written a manual or a business report has at some point dealt with things like this:



Yup, a note. It is essentially a paragraph with a gray background, a black border, and some extra space on all sides. And, oh, yeah...there is a little bold, capitalized caption that reads NOTE:. That's all it is.

Now, I bet that most people won't have much of an issue getting the word processor to format a paragraph to make it look like this. If you do have some trouble with that, hang on - a recipe is coming up.

Of course, it gets a bit annoying when you have to repeat this markup. I have no way to back this up, but it is my hunch that most people solve this simply by copying the paragraph, pasting it, and then editing the text.

(Well? Is it true? Don't be ashamed, post a comment and confess ;-)

Some people might look ahead a bit farther and consolidate the style for reuse. Let's take a look how that works:

Styles and Formatting

First open the styles and formatting toolbox. You can access it via Menu: "Format" > "Styles And Formatting" or by hitting the F11 key on your keyboard.



The Styles and formatting toolbox appears. I usually dock it to the right side of the Writer application window. This makes it very easy to apply the styles to the text at the current cursor position.



Since we are styling a paragraph to look like a note, we need to click the first button on the little toolbar in the top of the Styles and Formatting toolbox to list the available paragraph styles.

In the list of paragraph styles, we will see that the current paragraph style is highlighted. Assuming your cursor was in a plain paragraph, this will be the "Default" style.

Now, we want to create a new paragraph style especially for our Note. To create a new style, right click in the list, and choose "New" in the context menu. This pops up the the paragraph style dialog:



First, we need to give it a proper, sensible Name. Here, we dubbed it "MyNote". By default, Writer assumes it must use the new style also as style for the Next paragraph. Here I deviate from the default, and choose the "Default" style again as Next Style.

One of the most powerful features of styles is that they may be Linked With other styles. The new style is based on the style to which it is linked, effectively inheriting its properties. In this example, it means that changing, say, the font-size of the "Default" style will also change the font-size of the "MyNote" style accordingly.

Of course, "MyNote" may locally change its own font-size, and then that property will be preserved even if the "Default" style changes the font-size. In other words, the new style can override the properties of the style it is linked with.

Finally, a quick note about the style category listbox. By default, a new style is placed in the Custom Styles category. The Hierarchical category is also useful, as it allows you to see which styles are linked to each other:



So, this is something to keep in mind when you plan to change a style, as this view allows you to quickly see which other styles will be affected by the change.

Ok - now that we dealt with these logical properties, we set the indents and spacing:



Then, we make sure that all text in one "MyNote" paragraph is not broken up at page breaks:



This is taken care of by the "Do not split paragraph" checkbox.

And we set the background color:



And finally the border style:



The "Merge with next paragraph style" ensures that the border does not separate two consecutive paragraphs that are of the same style. In other words, it allows us to create notes that span more than one paragraph.

When we are all done defining the style, we simply confirm the dialog and close it.

Applying the style

Now that we created the style, we can apply it. First, position the cursor at the desired paragraph:



Then, double click the style in the "Styles and Formatting" toolbox:



Alternatively, you can toggle the Fill Format Mode button. Your mouse pointer then changes into a little bucket. In this mode, single clicking on a paragraph applies the selected style.

I explained that I like to dock the "Styles and Formatting" toolbox to the right side of the Writer Application window so I can easily apply styles where needed.

Are we done yet?

So, are we happy now? Well, actually...no.

The note we created manually has a little bold capital NOTE: caption. Of course, the first time around we simply typed that text in the note paragraph and marked it up in an ad-hoc manner.

Can we do better this time? Well, kind of...

First, lets see if we can consolidate the markup we use for the NOTE: caption. This time, we need to create a Character Style, because this time, we want to markup a stretch of text that appears within a paragraph.



So, we create a character style linked to the "Default" character style. The procedure is completely comparable to creating a paragraph style. We first give it a sensible Name:



Like with paragraph styles, the character style is based on the style it is linked with. However, here, we cannot specify the next style. Instead, applying a character style automatically stays in effect until you switch to another character style.

We then make the font bold:



And finally, we choose a Capitals effect:



We can then confirm and close the dialog.

Now that we created the character style, we can apply it, and type text using this character style. We can either select a piece of text an change the selection to use the character style, or we can toggle the character style and type text in that style.

So, does this do the trick?

Well, it's better now than it was before. At least, now can ensure that all our little NOTE: captions look the same, and that their formatting is updated when we change the style.

However, the problem is that we still have to type this text all the time. It is not only tedious, it is also error prone. Ideally, we would like to somehow have the word processor treat this text as part of the paragraph style instead of it being just text.

As it turns out, there is a clean, reliable way to achieve this. Enter List styles.

List styles are normally used to create bulleted or numbered lists. Each list item is essentially a paragraph that is preceded by a bullet or a number which is generated automatically by the word processor.

This is the key to solving the problem. If we can somehow create a list style that inserts the text NOTE instead of a bullet or a number, we can use it to implement our note caption. So, let's try and create one:

Again, the first step of the process is very similar to creating a paragraph or a character style. We click the list style button, and right click in the "Styles and Formatting" toolbox:



The list style dialog opens, and we provide a name for the list style:



Note however that list styles cannot be linked to other list styles.

We then specify that this list style is a numbering style. This may not really make sense at this point, but we really do need this step. That is because numbering styles allow us to prefix and postfix the list number with a piece of text - this is what we will use to add our Note Caption.



Now that we specified this style as a numbering style rather than a list style, a number of options become available:



Here, we specify that although it is a numbering style, we actually don't want any numbers to be inserted. This is specified by choosing None for Numbering. We also specify that we want the text Note: to appear before the list number. (Note however that we did specify that the list number is to be displayed using "None" - in other words, no number will actually be displayed ;-). Finally, we also specify to use our custom character style for the list number text - it is the character style we just created for our NOTE: captions.

Note that this step is the actual hack that specifies that the static NOTE caption text should be generated by the word processor whenever this list style is applied.

The final step in defining the list style is to undo any automatic indent that is applied to the list 'number'.



Checking the Relative checkbox makes the indent be relative to the current position of this list item in the list hierarchy. But we are not interested in this type of indent at all - we have specified the indent for at the level of the "MyNote" paragraph style. So we want to get rid of any additional indenting. So, we check the checkbox and specify that we want zero indenting relative to the current position in the list hierarchy.

(It might not be clear at this moment, but this step really is essential in order to allow a note to span multiple paragraphs.)

Applying the list style

Applying a list style is different from applying character or paragraph styles. In order to apply a list style, it needs to be attached to a paragraph style. This ensures that the paragraph starts behaving as a list item.

Please observe that attaching a list style to a paragraph style is different from linking a paragraph style to a paragraph style. A link is configured in the Linked With property in the Organizer tab of the paragraph style dialog. Linking causes the paragraph style that links to the other style to inherit its properties. Attaching a list style is done in the Numbering tab of the paragraph style dialog by setting the Numbering style property. Specifying a numbering style makes the paragraph style behave like a list item but does not change the properties of the paragraph style itself.

We might be tempted at this point to simply attach the list style to our "MyNote" paragraph style. This will work: all "MyNote" paragraphs will be starting automatically with the Note: caption.

However, directly attaching the "MyNote" paragraph style to the list style is not a good idea beacause all "MyNote" paragraphs will then start with the Note: caption: even two consecutive paragraphs in the "MyNote" style that together make up one note would each have the NOTE: caption. So, specifying the numbering style in the "MyNote" paragraph style would prevent us from creating Notes with multiple paragraphs.

The solution to that problem is create a new paragraph style called "MyNoteFirstLine", linked to the original "MyNote" style:



To this new "MyNoteFirstLine" style we then attach our list style:



By linking it to the original "MyNote" style, we ensure that there is still a single place that defines all of the "normal" Note formatting. So, if we want to change, say, the background of our notes, we will only need to modify the "MyNote" style, and the changes will cascade through to the "MyNoteFirstLine" style. By Setting the Next Style also to the MyNote style, we ensure that only the first paragraph of our note gets the Note: caption, and that we can continue the note without the static Note caption being generated.

So, here's the final result. We apply the "MyNoteFirstLine" paragraph style to initiate a Note:



Then we type some text. Once we hit Enter, a new "MyNote" paragraph is created:



and the result is a note spanning two paragraphs, with only the first paragraph having the static NOTE: caption.

Obviously, there is now too much white space between the paragraphs within the note. That's because both the "MyNote" as well as the "MyNoteFirstLine" styles have the same settings for "Indents and spacing". However, it is trivial to tweak the settings to achieve a more normal spacing.

To do so, you simply have to modify the "MyNote" style and decrease the spacing that appears above the paragraph. That change will of course cascade through to the "MyNoteFirstLine" paragraph style. But we do want to make sure that the spacing occurs above all "MyNoteFirstLine" paragraphs in order to separate the Note from the normal paragraphs.

So, instead, we need to specify the spacing to appear above the "MyNoteFirstLine" paragraph style. You likely also want to decrease the spacing below the "MyNoteFirstLine" paragraph style in order to make it appear more closely to the remaining paragraphs in the note.

We will leave this as an exercise to te reader.

Summary

This might seem like a long post, but it an be quickly summarized: To create a note like this you:

• Create a generic "Note" style
• Base a "First" paragraph style off the "Note" style by linking to it, and make sure the Next style is the Note style.
• Create a character style for the Note caption.
• Create a list style for the Note caption and configure it to use the character style. Then, attach the list style to "First" paragraph style