Friday, March 21, 2008

The Anatomy of a yum repository - a look under the hood of the Skype repo for i586

pencil icon, that"s clickable to start editing the post

For a long time I've been a happy user of both up2date and later yum without having any deeper understanding of how it works. Inspired by a couple of comments on my posting Sweetxml: Updating Skype with yum - updated repository location I decided to have a closer look at it. The yum repository that I wrote about was for Skype with the baseurl http://download.skype.com/linux/repos/fedora/updates/i586/. Making a visit to that URL gives an 403:

$ wget -S http://download.skype.com/linux/repos/fedora/updates/i586/
--23:30:03--  http://download.skype.com/linux/repos/fedora/updates/i586/
Resolving download.skype.com... 194.192.199.202, 194.192.199.201
Connecting to download.skype.com|194.192.199.202|:80... connected.
HTTP request sent, awaiting response...
  HTTP/1.1 403 Forbidden
  Server: nginx/0.5.20
  Date: Tue, 18 Mar 2008 23:01:25 GMT
  Content-Type: text/html
  Content-Length: 1292
  Connection: keep-alive
23:30:03 ERROR 403: Forbidden.

Which could be interpreted as there was something wrong with the repository, but that would be a mis-conclusion. The 403 contains a /html/head/meta[@http-equiv="refresh"] that sendes the browser to http://www.skype.com/go/download

<meta http-equiv="refresh" content="0;url=http://www.skype.com/go/download" />

which will send you through further a couple of 302's :

$ wget -S http://www.skype.com/go/download
--23:36:57--  http://www.skype.com/go/download
Resolving www.skype.com... 204.9.163.136
Connecting to www.skype.com|204.9.163.136|:80... connected.
HTTP request sent, awaiting response...
  HTTP/1.1 302 Found
  Date: Tue, 18 Mar 2008 22:36:57 GMT
  Server: Apache
  Location: http://www.skype.com/download/redirect
  Vary: Accept-Encoding,User-Agent
  Content-Length: 0
  Keep-Alive: timeout=5, max=1200
  Connection: Keep-Alive
  Content-Type: text/html; charset=utf-8
  Content-Language: en
Location: http://www.skype.com/download/redirect [following]
--23:36:57--  http://www.skype.com/download/redirect
Reusing existing connection to www.skype.com:80.
HTTP request sent, awaiting response...
  HTTP/1.1 302 Found
  Date: Tue, 18 Mar 2008 22:36:57 GMT
  Server: Apache
  Location: http://www.skype.com/intl/da/download/skype/windows/
  Pragma: no-cache
  Cache-Control: no-store, no-cache, must-revalidate
  Cache-Control: post-check=0, pre-check=0
  Vary: Accept-Encoding,User-Agent
  Content-Length: 780
  Keep-Alive: timeout=5, max=1199
  Connection: Keep-Alive
  Content-Type: text/html; charset=utf-8
  Content-Language: en
Location: http://www.skype.com/intl/da/download/skype/windows/ [following]
--23:36:58--  http://www.skype.com/intl/da/download/skype/windows/
Reusing existing connection to www.skype.com:80.
HTTP request sent, awaiting response...
  HTTP/1.1 200 OK
  Date: Tue, 18 Mar 2008 22:36:58 GMT
  Server: Apache
  Accept-Ranges: bytes
  Vary: Accept-Encoding,User-Agent
  Connection: close
  Content-Type: text/html; charset=utf-8
  Content-Language: en
Length: unspecified [text/html]

Here ending up on the windows download page (probaly the default, since wget doesn't give enogh OS information).

Looking for some in depth information about yum repositories I had to do more seaching that I had expected, but I finally ended at Duke University Yellow dog Updater, Modified (yum) project and especially createrepo that has a short description of the anatomy of a yum repository in the Explanations section.

The metadata file (for the metadata files) for the repository is located relatively in repodata/repomd.xml which for this repository becomes: http://download.skype.com/linux/repos/fedora/updates/i586/repodata/repomd.xml:

    1 <?xml version="1.0" encoding="UTF-8"?>
    2 <repomd xmlns="http://linux.duke.edu/metadata/repo">
    3   <data type="other">
    4     <location href="repodata/other.xml.gz" />
    5     <checksum type="sha">289da3573161f83988b15dc74db0a1efa8ef9781</checksum>
    6     <timestamp>1191408972</timestamp>
    7     <open-checksum type="sha">c709dc7b0f3f7c0f38c767ebda5d33979eb2a943</open-checksum>
    8   </data>
    9   <data type="filelists">
   10     <location href="repodata/filelists.xml.gz" />
   11     <checksum type="sha">5c9fa749066ef5799f2ea9159a2963502644ecaa</checksum>
   12     <timestamp>1191408972</timestamp>
   13     <open-checksum type="sha">6eb325e10d4e8fcb091e292c27dd09176cd3aa4d</open-checksum>
   14   </data>
   15   <data type="primary">
   16     <location href="repodata/primary.xml.gz" />
   17     <checksum type="sha">68df664b42a6f6b97baa9f0022a5d2e89f5b95e1</checksum>
   18     <timestamp>1191408972</timestamp>
   19     <open-checksum type="sha">97ef94dac5f6f6a6b610eca8ed48f806d4eaac2d</open-checksum>
   20   </data>
   21 </repomd>

So from this it's clear that this is an active repository, though direct browsing the baseurl could give you the idea that it wasn't.

Taking it in reverse order the primary metadata is in the file repodata/primary.xml.gz, as described on the Duke website:

this file stores the primary metadata information. This includes information such as:

  • name, epoch, version, release, architecture
  • file size, file location, description, summary, format, checksums header byte-ranges, etc.
  • dependencies, provides, conflicts, obsoletes, suggests, recommends
  • file lists for the package for CERTAIN files - specifically files matching: /etc*, *bin/*, /usr/lib/sendmail
  • it also includes a 'format' element which allows for format specific information to be stored. The formats known about right now are deb and rpm

This file (when gunzip'ed) looks like:

    1 <?xml version="1.0" encoding="UTF-8"?>
    2 <metadata
    3   xmlns="http://linux.duke.edu/metadata/common"
    4   xmlns:rpm="http://linux.duke.edu/metadata/rpm"
    5   packages="1">
    6   <package type="rpm">
    7     <name>skype</name>
    8     <arch>i586</arch>
    9     <version
   10       epoch="0"
   11       ver="1.4.0.118"
   12       rel="fc5" />
   13     <checksum
   14       type="sha"
   15       pkgid="YES">
   16       35d0bb0ed0738c4392827b2cd3d6ad5b1f098506
   17     </checksum>
   18     <summary>Skype is free Internet telephony that just works</summary>
   19     <description>
   20       Skype - Take a deep breath
   21 
   22       Skype is a little piece of software that lets you make free calls to anyone else
   23       on Skype, anywhere in the world. And even though the calls are free, they are really
   24       excellent quality.
   25 
   26       * Make free Skype-to-Skype calls to anyone else, anywhere in the world.
   27       * Call ordinary phones and mobiles at pretty cheap rates per minute.
   28       * Group chat with up to 100 people or conference call with up to nine others.
   29       * Free to download.
   30     </description>
   31     <packager />
   32     <url>http://www.skype.com/products/skype/linux/</url>
   33     <time
   34       file="1191408970"
   35       build="1190911586" />
   36     <size
   37       package="13410121"
   38       installed="16209093"
   39       archive="16225988" />
   40     <location href="skype-1.4.0.118-fc.i586.rpm" />
   41     <format>
   42       <rpm:license>Commercial</rpm:license>
   43       <rpm:vendor />
   44       <rpm:group>Applications/Internet</rpm:group>
   45       <rpm:buildhost>linux-build.skype.net</rpm:buildhost>
   46       <rpm:sourcerpm>skype-1.4.0.118-fc5.src.rpm</rpm:sourcerpm>
   47       <rpm:header-range
   48         start="440"
   49         end="25645" />
   50       <rpm:provides>
   51         <rpm:entry
   52           name="config(skype)"
   53           flags="EQ"
   54           epoch="0"
   55           ver="1.4.0.118"
   56           rel="fc5" />
   57         <rpm:entry
   58           name="skype"
   59           flags="EQ"
   60           epoch="0"
   61           ver="1.4.0.118"
   62           rel="fc5" />
   63       </rpm:provides>
   64       <rpm:requires>
   65         <rpm:entry name="libstdc++.so.6(CXXABI_1.3)" />
   66         <rpm:entry name="libc.so.6(GLIBC_2.1.3)" />
   67         <rpm:entry name="libasound.so.2(ALSA_0.9)" />
   68         <rpm:entry
   69           name="config(skype)"
   70           flags="EQ"
   71           epoch="0"
   72           ver="1.4.0.118"
   73           rel="fc5" />
   74         <rpm:entry name="libgcc_s.so.1(GCC_3.0)" />
   75         <rpm:entry name="libX11.so.6" />
   76         <rpm:entry name="libc.so.6(GLIBC_2.3)" />
   77         <rpm:entry name="libpthread.so.0(GLIBC_2.3.2)" />
   78         <rpm:entry name="libc.so.6(GLIBC_2.1)" />
   79         <rpm:entry
   80           name="rpmlib(PayloadFilesHavePrefix)"
   81           flags="LE"
   82           epoch="0"
   83           ver="4.0"
   84           rel="1"
   85           pre="1" />
   86         <rpm:entry
   87           name="rpmlib(CompressedFileNames)"
   88           flags="LE"
   89           epoch="0"
   90           ver="3.0.4"
   91           rel="1"
   92           pre="1" />
   93         <rpm:entry name="libpthread.so.0" />
   94         <rpm:entry name="libpthread.so.0(GLIBC_2.0)" />
   95         <rpm:entry name="libstdc++.so.6(CXXABI_1.3.1)" />
   96         <rpm:entry name="libstdc++.so.6(GLIBCXX_3.4)" />
   97         <rpm:entry name="libpthread.so.0(GLIBC_2.1)" />
   98         <rpm:entry name="libQtDBus.so.4" />
   99         <rpm:entry name="libgcc_s.so.1" />
  100         <rpm:entry name="libm.so.6" />
  101         <rpm:entry name="libm.so.6(GLIBC_2.0)" />
  102         <rpm:entry name="libasound.so.2(ALSA_0.9.0rc4)" />
  103         <rpm:entry name="libQtNetwork.so.4" />
  104         <rpm:entry name="libstdc++.so.6(GLIBCXX_3.4.4)" />
  105         <rpm:entry name="libasound.so.2" />
  106         <rpm:entry name="libstdc++.so.6" />
  107         <rpm:entry name="libgcc_s.so.1(GLIBC_2.0)" />
  108         <rpm:entry name="libc.so.6(GLIBC_2.0)" />
  109         <rpm:entry name="libc.so.6(GLIBC_2.4)" />
  110         <rpm:entry name="librt.so.1" />
  111         <rpm:entry name="libQtGui.so.4" />
  112         <rpm:entry name="libc.so.6(GLIBC_2.3.4)" />
  113         <rpm:entry
  114           name="/bin/sh"
  115           pre="1" />
  116         <rpm:entry
  117           name="qt4-x11"
  118           flags="GE"
  119           epoch="0"
  120           ver="4.2" />
  121         <rpm:entry name="libQtCore.so.4" />
  122         <rpm:entry name="libpthread.so.0(GLIBC_2.2)" />
  123         <rpm:entry name="libc.so.6" />
  124         <rpm:entry name="libc.so.6(GLIBC_2.2)" />
  125         <rpm:entry name="libsigc-2.0.so.0" />
  126       </rpm:requires>
  127       <file>/etc/dbus-1/system.d/skype.conf</file>
  128       <file>/usr/bin/skype</file>
  129     </format>
  130   </package>
  131 </metadata>

The format partly defined in the DTD for package and DTD for rpm metadata.

The repodata/filelists.xml.gz, described as this file stores the complete file and directory listings for the packages. The package is identified by: name, epoch, version, release, architecture and package checksum id contains:

    1 <?xml version="1.0" encoding="UTF-8"?>
    2 <filelists
    3   xmlns="http://linux.duke.edu/metadata/filelists"
    4   packages="1">
    5   <package
    6     pkgid="97ff8fb3b6eb70289a73545ea39c02fcce4f6b4a"
    7     name="skype"
    8     arch="i586">
    9     <version
   10       epoch="0"
   11       ver="2.0.0.63"
   12       rel="fc5" />
   13     <file>/etc/dbus-1/system.d/skype.conf</file>
   14     <file>/usr/bin/skype</file>
   15     <file>/usr/share/applications/skype.desktop</file>
   16     <file>/usr/share/doc/skype-2.0.0.63/LICENSE</file>
   17     <file>/usr/share/doc/skype-2.0.0.63/README</file>
   18     <file>/usr/share/icons/skype.png</file>
   19     <file>/usr/share/pixmaps/skype.png</file>
   20     <file>/usr/share/skype/lang/skype_bg.qm</file>
   21     <file>/usr/share/skype/lang/skype_bg.ts</file>
   22     <file>/usr/share/skype/lang/skype_de.qm</file>
   23     <file>/usr/share/skype/lang/skype_de.ts</file>
   24     <file>/usr/share/skype/lang/skype_en.qm</file>
   25     <file>/usr/share/skype/lang/skype_en.ts</file>
   26     <file>/usr/share/skype/lang/skype_es.qm</file>
   27     <file>/usr/share/skype/lang/skype_es.ts</file>
   28     <file>/usr/share/skype/lang/skype_et.qm</file>
   29     <file>/usr/share/skype/lang/skype_et.ts</file>
   30     <file>/usr/share/skype/lang/skype_fr.qm</file>
   31     <file>/usr/share/skype/lang/skype_fr.ts</file>
   32     <file>/usr/share/skype/lang/skype_it.qm</file>
   33     <file>/usr/share/skype/lang/skype_it.ts</file>
   34     <file>/usr/share/skype/lang/skype_ja.qm</file>
   35     <file>/usr/share/skype/lang/skype_ja.ts</file>
   36     <file>/usr/share/skype/lang/skype_ko.qm</file>
   37     <file>/usr/share/skype/lang/skype_ko.ts</file>
   38     <file>/usr/share/skype/lang/skype_lt.qm</file>
   39     <file>/usr/share/skype/lang/skype_lt.ts</file>
   40     <file>/usr/share/skype/lang/skype_lv.qm</file>
   41     <file>/usr/share/skype/lang/skype_lv.ts</file>
   42     <file>/usr/share/skype/lang/skype_pl.qm</file>
   43     <file>/usr/share/skype/lang/skype_pl.ts</file>
   44     <file>/usr/share/skype/lang/skype_pt_br.qm</file>
   45     <file>/usr/share/skype/lang/skype_pt_br.ts</file>
   46     <file>/usr/share/skype/lang/skype_pt_pt.qm</file>
   47     <file>/usr/share/skype/lang/skype_pt_pt.ts</file>
   48     <file>/usr/share/skype/lang/skype_ro.qm</file>
   49     <file>/usr/share/skype/lang/skype_ro.ts</file>
   50     <file>/usr/share/skype/lang/skype_ru.qm</file>
   51     <file>/usr/share/skype/lang/skype_ru.ts</file>
   52     <file>/usr/share/skype/lang/skype_th.qm</file>
   53     <file>/usr/share/skype/lang/skype_th.ts</file>
   54     <file>/usr/share/skype/lang/skype_tr.qm</file>
   55     <file>/usr/share/skype/lang/skype_tr.ts</file>
   56     <file>/usr/share/skype/lang/skype_zh_s.qm</file>
   57     <file>/usr/share/skype/lang/skype_zh_s.ts</file>
   58     <file>/usr/share/skype/lang/skype_zh_t.qm</file>
   59     <file>/usr/share/skype/lang/skype_zh_t.ts</file>
   60     <file>/usr/share/skype/sounds/CallBusy.wav</file>
   61     <file>/usr/share/skype/sounds/CallConnecting.wav</file>
   62     <file>/usr/share/skype/sounds/CallFailed.wav</file>
   63     <file>/usr/share/skype/sounds/CallHangup.wav</file>
   64     <file>/usr/share/skype/sounds/CallHold.wav</file>
   65     <file>/usr/share/skype/sounds/CallRemoteHangup.wav</file>
   66     <file>/usr/share/skype/sounds/CallResume.wav</file>
   67     <file>/usr/share/skype/sounds/CallRingingIn.wav</file>
   68     <file>/usr/share/skype/sounds/CallRingingOut.wav</file>
   69     <file>/usr/share/skype/sounds/ChatIncoming.wav</file>
   70     <file>/usr/share/skype/sounds/ChatIncomingInitial.wav</file>
   71     <file>/usr/share/skype/sounds/ChatOutgoing.wav</file>
   72     <file>/usr/share/skype/sounds/ContactAdded.wav</file>
   73     <file>/usr/share/skype/sounds/ContactAuthRequest.wav</file>
   74     <file>/usr/share/skype/sounds/ContactOffline.wav</file>
   75     <file>/usr/share/skype/sounds/ContactOnline.wav</file>
   76     <file>/usr/share/skype/sounds/SkypeLogin.wav</file>
   77     <file>/usr/share/skype/sounds/SkypeLogout.wav</file>
   78     <file>/usr/share/skype/sounds/TransferComplete.wav</file>
   79     <file>/usr/share/skype/sounds/TransferFailed.wav</file>
   80     <file>/usr/share/skype/sounds/TransferRequest.wav</file>
   81     <file>/usr/share/skype/sounds/VoicemailReceived.wav</file>
   82     <file type="dir">/usr/share/doc/skype-2.0.0.63</file>
   83   </package>
   84 
   85 </filelists>

And finally repodata/other.xml.gz, with the description this file currently only stores the changelog data from packages. However, this file could be used for any other additional metadata that could be useful for clients contains:

    1 <?xml version="1.0" encoding="UTF-8"?>
    2 <otherdata
    3   xmlns="http://linux.duke.edu/metadata/other"
    4   packages="1">
    5   <package
    6     pkgid="97ff8fb3b6eb70289a73545ea39c02fcce4f6b4a"
    7     name="skype"
    8     arch="i586">
    9     <version
   10       epoch="0"
   11       ver="2.0.0.63"
   12       rel="fc5" />
   13     <changelog
   14       author="Skype Technologies S.A. &lt;info@skype.net&gt; 2.0.0.63-fc5"
   15       date="1204704000">
   16 - feature: Add call cost per minute to 1:1 SkypeOut calls.
   17 - feature: Add UI detection of Slovakian, Farsi (Persian) and Vietnamese language files.
   18 - feature: Added privacy option to disable the sending of the number of contacts you have to your contacts.
   19 - feature: Implement HashLinkURL to provide easy links to #12345 references in chats.
   20 - feature: Shadows on user avatars on contact card.
   21 - feature: Show an indicator when the user is on an embedded device such as a Skype phone.
   22 - feature API: BTN_PRESSED/BTN_RELEASED are now implemented.
   23 - feature API: SEARCH ACTIVECHATS is now implemented.
   24 - bugfix: 1:1 chats should not have Leave Chat button.
   25 - bugfix: Add a blinking window effect for some windows which are already open when we get a new chat message.
   26 - bugfix: Add a workaround for a KWin issue preventing closed minimized chats from being reopened.
   27 - bugfix: Add bubbles from mood message to avatars on contact card.
   28 - bugfix: Add right-click context menu to chat window for copying text/links.
   29 - bugfix: Avoid status text being truncated on status button in profile editor.
   30 - bugfix: BTN_PRESSED YES should activate Call functionality regardless of double-click action.
   31 - bugfix: Be sure the chat window doesn't reappear after we leave the chat.
   32 - bugfix: Call Ringing sound shouldn't be audible during Do Not Disturb status mode.
   33 - bugfix: Can't add a contact to a chat using drag and drop.
   34 - bugfix: Change Call Hold/Resume button at the correct times (even when changing hold/resume during fullscreen video)
   35 - bugfix: Change file transfer filename to saved filename if different from original filename in transfer window.
   36 - bugfix: Compare chats in recent chats/etc menus correctly to check if they are unread.
   37 - bugfix: Contacts shouldn't disappear when they're moved between columns when receiving contacts.
   38 - bugfix: Copy URLs to both X selection and Clipboard, as users may want to middle-click the copied link into their browser.
   39 - bugfix: Crash after 2 minutes of signing in if we are signed out at that time.
   40 - bugfix: Crash after signing in with incorrect details after signing out.
   41 - bugfix: Display video in full-screen at correct aspect ratio regardless of monitor dimensions.
   42 - bugfix: Do not always put new chat windows at top left corner if no previous setting found, let window manager determine position.
   43 - bugfix: Do not freeze when opening options dialog
   44 - bugfix: Do not initialise /dev/video0 whenever we start a video call (could prevent video from working when video0 fails)
   45 - bugfix: Do not instantly close Voicemail events when clicking the play button in new events panel.
   46 - bugfix: Do not let Voicemail messages continue to play in the background after closing them.
   47 - bugfix: Do not show authorisation options in context menu for events if we already authorised the user.
   48 - bugfix: Do not unnecessarily init device twice when starting video (speeds up video init by 50%)
   49 - bugfix: Downscaling for Myself View / Oversized Video on X11 (non-accelerated).
   50 - bugfix: echo123 should be called Skype Test Call and should not have an 'Request Authorization' option.
   51 - bugfix: Ending an incoming conference call should be allowed to redirect to Voicemail also.
   52 - bugfix: Escape should close send contacts window before we attempt to send contacts.
   53 - bugfix: Fix for chat being stuck in unread state after becoming multichat.
   54 - bugfix: Fix for video output when not using XShm extension.
   55 - bugfix: Fix locking up the client for up to 30 seconds randomly sometimes for heavy Skype users.
   56 - bugfix: If main window is thinner than login window and right-aligned, it would move to the left after restart.
   57 - bugfix: Incoming calls should be redirected to Voicemail if configured to do so and another call is active.
   58 - bugfix: Make 'Invite selected contacts to conference call' option work in contact list multiple selections menu.
   59 - bugfix: Make contact list even more responsive whilst loading historical calls.
   60 - bugfix: Make login/startup much faster and cleaner.
   61 - bugfix: Make missed event text for birthdays clearer.
   62 - bugfix: Many bugfixes to video in call window to handle window updates during paused video calls.
   63 - bugfix: Only move main window if we have a previously saved position.
   64 - bugfix: Only play chat message sent sound for actual sent messages and emotive actions.
   65 - bugfix: Only redirect incoming calls to Voicemail if we actually have Voicemail capability.
   66 - bugfix: Properly remember unread chats as unread after a Skype restart.
   67 - bugfix: Receive Contacts should close when Escape is pressed.
   68 - bugfix: Remember 'Show Myself View' setting during the video call (when switching to fullscreen and back, etc).
   69 - bugfix: Remember chat splitter arrangement per user for 1:1 chats.
   70 - bugfix: Remember chat window position per user for 1:1 chats.
   71 - bugfix: Remember show/hide chat member list setting per user for 1:1 chats.
   72 - bugfix: Remove grey contact card background.
   73 - bugfix: Reordered Voicemail Options dialog to keep individual Voicemail features independent.
   74 - bugfix: Send Contacts should cancel and/or close when Escape is pressed.
   75 - bugfix: Shortened SkypeIn and SkypeOut call error messages.
   76 - bugfix: Show correct saving filename in child transfer widget when receiving multiple files.
   77 - bugfix: Shrink myself view so it scales better on Xv and properly on X11.
   78 - bugfix: Starting a conference call with a MySpace contact would result in a single call being made to 'myspace'.
   79 - bugfix: Stop window from moving lower and lower on each restart.
   80 - bugfix: Update chat window area size correctly after clearing to avoid mystery scrollbar returning.
   81 - bugfix: Video call window is out of shape if the contact has a really long name.
   82 - bugfix API: SET CHATMESSAGE [id] SEEN should remove now-read chats from events panel.
   83     </changelog>
   84   </package>
   85 </otherdata>

On howtoforge.com there's an article How To Set Up A Local Yum Repository On Fedora 8 which could be a very good idea if I someday had to develop (and maintain) an application for Linux.

Read more

Friday, February 29, 2008

A quick peek at XML formats for Spreadsheets

pencil icon, that"s clickable to start editing the post

In this post I'll have peek at which XML formats there's available for spreadsheets, here primarily the Microsoft variant in Excel and secondary in Open Office. This is NOT an entry in the flame war between ODF and EOOXML, since i neither have the knowledge nor insight to contribute in that debate.

Lately I've been using Excel for several tasks and since the launch of Microsoft Office 2007 there's been a massive campaign about the support for XML in MS Office 2007 - but hey, theres been support for if for a long time, as I'll show in this post. Now it's not necessarily so easy to define support for XML, since this can potentially be a wide array of features and probably the ways you can use XML with MS Office 2007 has increased since MS Office XP and 2003.

Throughout this post I'll use the simplest examples possible - that's empty ones. I could had inserted a hello world but figured it would not matter.

Excel 2002 - MS Office XP

Excel 2002 which came with MS Office XP (In 2001 accordin to wikipedia, that's seven years ago). Note: what went wrong with the naming here?. From back then it was possible to save (and load) as XML, and the empty example would look like:

    1 <?xml version="1.0"?>
    2 <Workbook
    3   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    4   xmlns:o="urn:schemas-microsoft-com:office:office"
    5   xmlns:x="urn:schemas-microsoft-com:office:excel"
    6   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    7   xmlns:html="http://www.w3.org/TR/REC-html40">
    8   <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    9     <Author>Demo Boy</Author>
   10     <LastAuthor>Demo Boy</LastAuthor>
   11     <Created>2008-02-23T10:04:48Z</Created>
   12     <LastSaved>2008-02-23T11:11:05Z</LastSaved>
   13     <Company>Sweetxml</Company>
   14     <Version>10.6830</Version>
   15   </DocumentProperties>
   16   <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
   17     <DownloadComponents />
   18     <LocationOfComponents HRef="file:///\\" />
   19   </OfficeDocumentSettings>
   20   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
   21     <WindowHeight>12525</WindowHeight>
   22     <WindowWidth>17115</WindowWidth>
   23     <WindowTopX>360</WindowTopX>
   24     <WindowTopY>120</WindowTopY>
   25     <ProtectStructure>False</ProtectStructure>
   26     <ProtectWindows>False</ProtectWindows>
   27   </ExcelWorkbook>
   28   <Styles>
   29     <Style
   30       ss:ID="Default"
   31       ss:Name="Normal">
   32       <Alignment ss:Vertical="Bottom" />
   33       <Borders />
   34       <Font />
   35       <Interior />
   36       <NumberFormat />
   37       <Protection />
   38     </Style>
   39   </Styles>
   40   <Worksheet ss:Name="Ark1">
   41     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   42       <PageSetup>
   43         <Header x:Margin="0" />
   44         <Footer x:Margin="0" />
   45         <PageMargins
   46           x:Bottom="0.984251969"
   47           x:Left="0.78740157499999996"
   48           x:Right="0.78740157499999996"
   49           x:Top="0.984251969" />
   50       </PageSetup>
   51       <Selected />
   52       <ProtectObjects>False</ProtectObjects>
   53       <ProtectScenarios>False</ProtectScenarios>
   54     </WorksheetOptions>
   55   </Worksheet>
   56   <Worksheet ss:Name="Ark2">
   57     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   58       <PageSetup>
   59         <Header x:Margin="0" />
   60         <Footer x:Margin="0" />
   61         <PageMargins
   62           x:Bottom="0.984251969"
   63           x:Left="0.78740157499999996"
   64           x:Right="0.78740157499999996"
   65           x:Top="0.984251969" />
   66       </PageSetup>
   67       <ProtectObjects>False</ProtectObjects>
   68       <ProtectScenarios>False</ProtectScenarios>
   69     </WorksheetOptions>
   70   </Worksheet>
   71   <Worksheet ss:Name="Ark3">
   72     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   73       <PageSetup>
   74         <Header x:Margin="0" />
   75         <Footer x:Margin="0" />
   76         <PageMargins
   77           x:Bottom="0.984251969"
   78           x:Left="0.78740157499999996"
   79           x:Right="0.78740157499999996"
   80           x:Top="0.984251969" />
   81       </PageSetup>
   82       <ProtectObjects>False</ProtectObjects>
   83       <ProtectScenarios>False</ProtectScenarios>
   84     </WorksheetOptions>
   85   </Worksheet>
   86 </Workbook>

I'm just observing the impressive preciseness like with x:Bottom="0.984251969".

Excel 2003 - MS Office 2003

These much more logic to the names here since it's all 2003. As I recall it there was quite some talk about the XML support in MS Office 2003, and some of the schemas was published in the InfoStructureBase (more on that later). Here'e the possibility to save as XML data, but that's based on some schema setup I've never tried, but there's also the same option to save as XML like:

    1 <?xml version="1.0"?>
    2 <?mso-application progid="Excel.Sheet"?>
    3 <Workbook
    4   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    5   xmlns:o="urn:schemas-microsoft-com:office:office"
    6   xmlns:x="urn:schemas-microsoft-com:office:excel"
    7   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    8   xmlns:html="http://www.w3.org/TR/REC-html40">
    9   <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
   10     <Author>Demo Boy</Author>
   11     <LastAuthor>Demo Boy</LastAuthor>
   12     <Created>2008-02-24T19:35:19Z</Created>
   13     <LastSaved>2008-02-24T19:35:38Z</LastSaved>
   14     <Company>Sweetxml</Company>
   15     <Version>11.9999</Version>
   16   </DocumentProperties>
   17   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
   18     <WindowHeight>12525</WindowHeight>
   19     <WindowWidth>15195</WindowWidth>
   20     <WindowTopX>480</WindowTopX>
   21     <WindowTopY>120</WindowTopY>
   22     <ProtectStructure>False</ProtectStructure>
   23     <ProtectWindows>False</ProtectWindows>
   24   </ExcelWorkbook>
   25   <Styles>
   26     <Style
   27       ss:ID="Default"
   28       ss:Name="Normal">
   29       <Alignment ss:Vertical="Bottom" />
   30       <Borders />
   31       <Font />
   32       <Interior />
   33       <NumberFormat />
   34       <Protection />
   35     </Style>
   36   </Styles>
   37   <Worksheet ss:Name="Sheet1">
   38     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   39       <Selected />
   40       <ProtectObjects>False</ProtectObjects>
   41       <ProtectScenarios>False</ProtectScenarios>
   42     </WorksheetOptions>
   43   </Worksheet>
   44   <Worksheet ss:Name="Sheet2">
   45     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   46       <ProtectObjects>False</ProtectObjects>
   47       <ProtectScenarios>False</ProtectScenarios>
   48     </WorksheetOptions>
   49   </Worksheet>
   50   <Worksheet ss:Name="Sheet3">
   51     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   52       <ProtectObjects>False</ProtectObjects>
   53       <ProtectScenarios>False</ProtectScenarios>
   54     </WorksheetOptions>
   55   </Worksheet>
   56 </Workbook>

Excel 2007 - MS Office 2007

With Excel 2007 you can still use the old XML format, and it would now look like:

    1 <?xml version="1.0"?>
    2 <?mso-application progid="Excel.Sheet"?>
    3 <Workbook
    4   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    5   xmlns:o="urn:schemas-microsoft-com:office:office"
    6   xmlns:x="urn:schemas-microsoft-com:office:excel"
    7   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    8   xmlns:html="http://www.w3.org/TR/REC-html40">
    9   <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
   10     <Author>Demo Boy</Author>
   11     <LastAuthor>Demo Boy</LastAuthor>
   12     <Created>2008-02-24T19:55:23Z</Created>
   13     <LastSaved>2008-02-24T19:56:21Z</LastSaved>
   14     <Company>Sweetxml</Company>
   15     <Version>12.00</Version>
   16   </DocumentProperties>
   17   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
   18     <WindowHeight>11955</WindowHeight>
   19     <WindowWidth>18975</WindowWidth>
   20     <WindowTopX>120</WindowTopX>
   21     <WindowTopY>45</WindowTopY>
   22     <ProtectStructure>False</ProtectStructure>
   23     <ProtectWindows>False</ProtectWindows>
   24   </ExcelWorkbook>
   25   <Styles>
   26     <Style
   27       ss:ID="Default"
   28       ss:Name="Normal">
   29       <Alignment ss:Vertical="Bottom" />
   30       <Borders />
   31       <Font
   32         ss:FontName="Calibri"
   33         x:Family="Swiss"
   34         ss:Size="11"
   35         ss:Color="#000000" />
   36       <Interior />
   37       <NumberFormat />
   38       <Protection />
   39     </Style>
   40   </Styles>
   41   <Worksheet ss:Name="Sheet1">
   42     <Table
   43       ss:ExpandedColumnCount="1"
   44       ss:ExpandedRowCount="1"
   45       x:FullColumns="1"
   46       x:FullRows="1"
   47       ss:DefaultRowHeight="15">
   48     </Table>
   49     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   50       <PageSetup>
   51         <Header x:Margin="0.3" />
   52         <Footer x:Margin="0.3" />
   53         <PageMargins
   54           x:Bottom="0.75"
   55           x:Left="0.7"
   56           x:Right="0.7"
   57           x:Top="0.75" />
   58       </PageSetup>
   59       <Selected />
   60       <ProtectObjects>False</ProtectObjects>
   61       <ProtectScenarios>False</ProtectScenarios>
   62     </WorksheetOptions>
   63   </Worksheet>
   64   <Worksheet ss:Name="Sheet2">
   65     <Table
   66       ss:ExpandedColumnCount="1"
   67       ss:ExpandedRowCount="1"
   68       x:FullColumns="1"
   69       x:FullRows="1"
   70       ss:DefaultRowHeight="15">
   71     </Table>
   72     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   73       <PageSetup>
   74         <Header x:Margin="0.3" />
   75         <Footer x:Margin="0.3" />
   76         <PageMargins
   77           x:Bottom="0.75"
   78           x:Left="0.7"
   79           x:Right="0.7"
   80           x:Top="0.75" />
   81       </PageSetup>
   82       <ProtectObjects>False</ProtectObjects>
   83       <ProtectScenarios>False</ProtectScenarios>
   84     </WorksheetOptions>
   85   </Worksheet>
   86   <Worksheet ss:Name="Sheet3">
   87     <Table
   88       ss:ExpandedColumnCount="1"
   89       ss:ExpandedRowCount="1"
   90       x:FullColumns="1"
   91       x:FullRows="1"
   92       ss:DefaultRowHeight="15">
   93     </Table>
   94     <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   95       <PageSetup>
   96         <Header x:Margin="0.3" />
   97         <Footer x:Margin="0.3" />
   98         <PageMargins
   99           x:Bottom="0.75"
  100           x:Left="0.7"
  101           x:Right="0.7"
  102           x:Top="0.75" />
  103       </PageSetup>
  104       <ProtectObjects>False</ProtectObjects>
  105       <ProtectScenarios>False</ProtectScenarios>
  106     </WorksheetOptions>
  107   </Worksheet>
  108 </Workbook>

but more prominently you could use EOOXML for spreadsheets (or how it's coined). This is an all different beast since it's an assembly that's zipped, by default containing the following files and with the suffix/extension .xlsx:

$ unzip Book1.xlsx
Archive:  Book1.xlsx
  inflating: [Content_Types].xml
  inflating: _rels/.rels
  inflating: xl/_rels/workbook.xml.rels
  inflating: xl/workbook.xml
  inflating: xl/theme/theme1.xml
  inflating: xl/styles.xml
  inflating: xl/worksheets/sheet3.xml
  inflating: xl/worksheets/sheet2.xml
  inflating: xl/worksheets/sheet1.xml
  inflating: docProps/core.xml
  inflating: docProps/app.xml

Nothing less than 11 seperate files, that I've yet to figure out exactly how works but one of the central files is xl/workbook.xml:

    1 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    2 <workbook
    3   xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    4   xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    5   <fileVersion
    6     appName="xl"
    7     lastEdited="4"
    8     lowestEdited="4"
    9     rupBuild="4505" />
   10   <workbookPr defaultThemeVersion="124226" />
   11   <bookViews>
   12     <workbookView
   13       xWindow="120"
   14       yWindow="45"
   15       windowWidth="18975"
   16       windowHeight="11955" />
   17   </bookViews>
   18   <sheets>
   19     <sheet
   20       name="Sheet1"
   21       sheetId="1"
   22       r:id="rId1" />
   23     <sheet
   24       name="Sheet2"
   25       sheetId="2"
   26       r:id="rId2" />
   27     <sheet
   28       name="Sheet3"
   29       sheetId="3"
   30       r:id="rId3" />
   31   </sheets>
   32   <calcPr calcId="125725" />
   33 </workbook>

Calc - Open Office 2

Calc that comes with Open Office 2 support the OpenDocument format (ODF, ISO/IEC 26300, full name: OASIS Open Document Format for Office Applications) but also the MS Office 2003 XML format, as shown with this examples:

    1 <?xml version="1.0" encoding="UTF-8"?>
    2 <?mso-application progid="Excel.Sheet"?>
    3 <Workbook
    4   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    5   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    6   xmlns:x="urn:schemas-microsoft-com:office:excel"
    7   xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"
    8   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    9   xmlns:o="urn:schemas-microsoft-com:office:office"
   10   xmlns:html="http://www.w3.org/TR/REC-html40"
   11   xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">
   12   <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
   13     <Colors>
   14       <Color>
   15         <Index>3</Index>
   16         <RGB>#c0c0c0</RGB>
   17       </Color>
   18       <Color>
   19         <Index>4</Index>
   20         <RGB>#ff0000</RGB>
   21       </Color>
   22     </Colors>
   23   </OfficeDocumentSettings>
   24   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
   25     <WindowHeight>9000</WindowHeight>
   26     <WindowWidth>13860</WindowWidth>
   27     <WindowTopX>240</WindowTopX>
   28     <WindowTopY>75</WindowTopY>
   29     <ProtectStructure>False</ProtectStructure>
   30     <ProtectWindows>False</ProtectWindows>
   31   </ExcelWorkbook>
   32   <Styles>
   33     <Style
   34       ss:ID="Default"
   35       ss:Name="Default" />
   36     <Style
   37       ss:ID="Result"
   38       ss:Name="Result">
   39       <Font
   40         ss:Bold="1"
   41         ss:Italic="1"
   42         ss:Underline="Single" />
   43     </Style>
   44     <Style
   45       ss:ID="Result2"
   46       ss:Name="Result2">
   47       <Font
   48         ss:Bold="1"
   49         ss:Italic="1"
   50         ss:Underline="Single" />
   51       <NumberFormat ss:Format="Currency" />
   52     </Style>
   53     <Style
   54       ss:ID="Heading"
   55       ss:Name="Heading">
   56       <Alignment ss:Horizontal="Center" />
   57       <Font
   58         ss:Bold="1"
   59         ss:Italic="1"
   60         ss:Size="16" />
   61     </Style>
   62     <Style
   63       ss:ID="Heading1"
   64       ss:Name="Heading1">
   65       <Alignment
   66         ss:Horizontal="Center"
   67         ss:Rotate="90" />
   68       <Font
   69         ss:Bold="1"
   70         ss:Italic="1"
   71         ss:Size="16" />
   72     </Style>
   73     <Style ss:ID="co1" />
   74     <Style ss:ID="ta1" />
   75   </Styles>
   76   <ss:Worksheet ss:Name="Sheet1">
   77     <Table ss:StyleID="ta1">
   78       <Column
   79         ss:StyleID="Default"
   80         ss:Width="64.26" />
   81       <Row ss:Height="12.1896">
   82         <Cell ss:Index="1" />
   83       </Row>
   84     </Table>
   85     <x:WorksheetOptions />
   86   </ss:Worksheet>
   87   <ss:Worksheet ss:Name="Sheet2">
   88     <Table ss:StyleID="ta1">
   89       <Column
   90         ss:StyleID="Default"
   91         ss:Width="64.26" />
   92       <Row ss:Height="12.1896">
   93         <Cell ss:Index="1" />
   94       </Row>
   95     </Table>
   96     <x:WorksheetOptions />
   97   </ss:Worksheet>
   98   <ss:Worksheet ss:Name="Sheet3">
   99     <Table ss:StyleID="ta1">
  100       <Column
  101         ss:StyleID="Default"
  102         ss:Width="64.26" />
  103       <Row ss:Height="12.1896">
  104         <Cell ss:Index="1" />
  105       </Row>
  106     </Table>
  107     <x:WorksheetOptions />
  108   </ss:Worksheet>
  109 </Workbook>

The ODF format is in the form of .ods files which again are an assembly like the one for EOOXML:

$ unzip mappe1.ods
Archive:  mappe1.ods
 extracting: mimetype
   creating: Configurations2/statusbar/
  inflating: Configurations2/accelerator/current.xml
   creating: Configurations2/floater/
   creating: Configurations2/popupmenu/
   creating: Configurations2/progressbar/
   creating: Configurations2/menubar/
   creating: Configurations2/toolbar/
   creating: Configurations2/images/Bitmaps/
  inflating: content.xml
  inflating: styles.xml
 extracting: meta.xml
  inflating: Thumbnails/thumbnail.png
  inflating: settings.xml
  inflating: META-INF/manifest.xml

So spreadsheets can be loaded/saved as XML file formats and for the pragmatic programmer it looks like the XML format that came with MS Office 2003 can be used as an interoperable format. The only gotchas that I know of is that very often functions or macros are involved with the use of spreadsheets and here interoperability is not to be expected in general, and in general following a specified format in terms of XML Schema still leaves room for lack of interoperability.

There's a couple of MSDN articles about the format:

The Schemas can be downloaded, they'rea called Office 2003: XML Reference Schemas (it's an .exe file but just as a zip arhcive). As mentioned earlier you can also find the schemas in the ISB under XML Spreadsheet 2002. Here's an alternative representation of the Introduction to the Microsoft Office 2003 Reference Schemas:

urn:schemas-microsoft-com:office:component:spreadsheet [Spreadsheet component]

uuid:C2F41010-65B3-11d1-A29F-00AA00C14882 [Data Type]

This namespace contains the data types that can be used for columns in PivotTables.

http://schemas.microsoft.com/office/excel/2003/xml [XML Spreadsheet 2003]

This namespace contains functionality added in Microsoft Office Excel 2003, which mainly includes mapping XML documents

  • urn:schemas-microsoft-com:office:excel [XML Spreadsheet 2003]
  • This namespace defines elements and attributes used to describe more complex features of Excel, such as PivotTables, worksheet options, and validation.

    urn:schemas-microsoft-com:office:office [Common Properties]

    This namespace defines the shared office schema and document properties collection (such as author, creation date) and document statistics (such as word count).

    #RowsetSchema [Model Row]

    This namespace provides a description of a generalized row element that is used as a model for validation purposes when using PivotTables.

  • urn:schemas-microsoft-com:rowset [Rowset]
  • This namespace is used to describe the attibutes of the row element for PivotTables.

    uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882 [Rowset Definition]

    This namespace contains elements and attributes which describe the row elements for PivotTables.

    http://schemas.microsoft.com/data/udc [Universal Data Connection]

    This namespace contains elements and attributes which describe Universal Data Connections that are used in Microsoft Office Excel 2003 to connect to external data sources.

    http://schemas.microsoft.com/data/udc/soap [Universal Data Connection SOAP]

    This namespace defines external data connections when using the SOAP protocol.

    http://schemas.microsoft.com/data/udc/xmlfile [Universal Data Connection XML File]

    This namespace defines only one element, File, which specifies the pathname of an XML file that is used by UDC.

    urn:schemas-microsoft-com:vml [Vector Markup Language]

    VML is an application of Extensible Markup Language (XML) 1.0 which defines a format for the encoding of vector information together with additional markup to describe how that information may be displayed and edited.

    Read more

    Sunday, February 24, 2008

    W3C draft on a 'policy' to allow webapps to selectively loosen the same-origin restriction

    pencil icon, that"s clickable to start editing the post

    The news feed from w3c has been thick since new year and i still haven't had time to catch up with it all. On of news items from a fourth night ago is on Access Control for Cross-site Requests. It's from the Web Application Formats Working Group (first time for me) that has published a W3C Working Draft on Access Control for Cross-site Requests (14 February 2008). In the introduction it's revealed what it's all about:

    Web application technologies commonly apply same-origin restrictions to network requests. These restrictions prevent a Web application running from one origin from obtaining data retrieved from another origin, and also limit the amount of unsafe HTTP requests that can be automatically launched toward destinations that differ from the running application's origin.

    It's sounds truly great if this can be brought up to speed with current use and needs, though I'll admit I'm a bit skeptic on advances on these kinds of specifications and not the least the following implementation. Later it says:

    This specification is a building block for other specifications, so-called hosting specifications, which will define the precise model by which this specification is used. Among others, such specifications are likely to include XMLHttpRequest Level 2, XBL 2.0, and HTML 5 (for its server-sent events feature).

    Sooooo, this could take some time to get in common use as P3P, XHTML 5 (how about just XHTML 1.0 browser support). Some things just take time and the requirements seem solid enough.

    Read more