Archive for April, 2007
Installing Ubuntu 7.04 (Feisty Fawn) on Dell Inspiron 8600
**** General Hardware Specifications of Inspiron 8600 ****
| Hardware Components | Status under Linux | Notes |
| Pentium M processor 1.6Ghz | Ok | |
| 15.4 WXGA+ (1680×1050) TFT Display | Ok | Default resolution 1024×768 — Didn’t detect native resolution! I had to manually edit xorg.conf. See configuration steps below. |
| ATI Radeon 9600 (Mobile) AGP video card | Ok | |
| 512MB + 256MB PC2700 | Ok | |
| 40 GB Ultra ATA Hard Drive 7200 RPM (Toshiba) | Ok | |
| Integrated Network Card | I don’t know | I only used the wireless card for now |
| Intel Pro Wireless 2915ABG | Ok | I just had to setup the WiFi SSID and WEP key. |
| Internal 56k Modem | I don’t know… | I never tried the modem |
| DVD-ROW/CD-RW | Ok partially tested |
I’ve been able to read DVD/CDs, but I haven’t tried burning CDs with it. |
| 59 WHr Lithium-Ion Battery | Ok | |
| Integrated sound card | Ok | |
| IEEE-1394 (Firewire) | I don’t know | I have no firewire device to try it. |
| ALPS GlidePoint | Ok | Mouse navigation, Tap for click, Vertical scrolling are working. Horizontal scrolling is not working. |
| PC Speaker | Ok | |
| Keyboard – Canadian French Layout | Ok | |
| Logitech Wireless Mouse | Ok | Some special buttons are not working, but that’s not a big deal for me. |
| 4GB Patriot USB Key | Ok | Just plug it into the USB port and it is automatically detected. An icon is provided on the desktop. |
**** Special Keyboard Keys ****
| Volume Up / Down | Ok | |
| Mute | Ok | |
| Media Play/Pause toggle | I don’t know | |
| Media Stop | I don’t know | |
| Media Skip Next | I don’t know | |
| Media Skip Previous | I don’t know | |
| Power button | I don’t know | |
| Fn + Volume Up / Down | Ok | |
| Fn + Mute | Ok | |
| Fn + Brigthness Up / Down | Ok | |
| Fn + Numeric keypad | Ok | Behave correctly depending if NumLock is On or Off |
| Fn + CRT/LCD | I don’t know | |
| Fn + Suspend | I don’t know | |
| Fn + Scroll Lock | I don’t know | |
| Fn + SysRq | I don’t know | |
| Fn + Wireless (on/off) | Ok | But there is no visual indication that wireless on/off has been toggled. ifconfig still show an ip address when wireless is off (maybe by design… I’m too used to ipconfig from Windows  ). You need to use iwconfig to see if the radio is on/off. |
**** Installation Steps ****
- Plug the laptop into the power outlet.
Yes I experienced issues in the past when installing OS (Windows and Linux) while running on battery power. So now I don’t take the chance, its too long to have to reinstall… - Boot with CD
- Choose ‘Start or install Ubuntu’ on boot menu
- When GNOME appears, start the installation by clicking the ‘Install’ icon
- Choose the installer’s language: English, [Forward]
- Choose the time zone: Montreal, [Forward]
- Choose the keyboard layout: Canada / Canada for standard canadian french layout, [Forward]
- Partition the hard drive: manual because I have a special dual-boot with Windows XP, [Forward]
- Migrate Documents and Settings for an existing operating system: none, [Forward]
- Enter name, login, password and computer name, [Forward]
- INSTALL! (a 20 minutes step)
- Restart the computer, don’t forget to remove the CD when asked to
- Boot Ubuntu 7.04 for the first time
**** Configuration Steps ****
Install Ubuntu Updates
|
Setup Wireless Connection
|
Set Screen Resolution
|
Turn off annoying PC Speaker “beeps”
|
**** Additional Software Installation ****
| Automatix2 | follow these steps in a terminal window (taken from http://www.getautomatix.com/wiki/index.php?title=Installation): echo "deb http://www.getautomatix.com/apt feisty main" | sudo tee -a /etc/apt/sources.list |
| Multimedia codecs (using Automatix) |
Codecs and Plugins section / Multimedia codecsAfter I installed this, I’ve been able to listen to WMV (Windows Media Movies) files. |
| KeePassX | This is a password manager that is multi-platform. Works well. Installed using Synaptic from the universe repository. |
| Firefox Extension: Foxmarks Bookmarks Synchronizer |
Bookmark synchronizer I setup to connect to my own WebDAV server. It as its problems (not linked to Ubuntu) but works well when synchronizing manually. |
**** Additional Notes ****
| * | I have the feeling the font settings were better with my laptop using the previous versions of Ubuntu. Text looked nicer and was easier to read. Maybe this is a side effect because it didn’t detect my monitor native resolution correctly at first. |
| * | Next steps will be to give a try to the new composite window manager, Compiz… |
Sharepoint 2003 – Database Internal
When I wrote the Sharepoint Database Exporter, I learned a lot about how Sharepoint stores the data internally. I also noticed that my blog is often hit with google searches from people looking for how to query a Sharepoint database.
So here are some information…
This information is about the storage of WSS websites in either a Sharepoint Portal Server or a Windows Sharepoint Services. But its not about the storage of Sharepoint Portal Areas (my guess is it should look similar).
So if your Query Analyser connection are opened and ready to query … the place to start looking is the database that usually terminates by “_SITE” suffix.
Sites
This table stores all the top-level websites. For each entry found here, there is at least one entry on the Webs table.
Webs
This table stores all the websites information for a Sharepoint installation.
| Id | Identifier of the website |
| SiteId | Identifier of the top-level website id (Refers to the Site table) |
| ParentWebId | Identifier of the parent website. For a top-level website, this column contains NULL. |
| Title | The name of the website. |
Lists
This table stores the lists of a website. You can join this table to the Webs table using the tp_WebId.
| tp_WebId | Identifier of the list’s owner website. |
| tp_ID | Unique Identifier of the list. |
| tp_Title | Name of the list. |
| tp_BaseType | This field indicate the basics of the list. Based on the value of this column, the storage and handling may differ.For example, a base type of ‘1′ is used for document and picture libraries. |
| tp_ServerTemplate | This field indicate which type of Sharepoint listitem object will be usedhere are some of the types I am aware of:
|
| tp_RootFolder | This field stores the root item for list attachments. It may be used to link the UserData table with the Docs table when the base type is not ‘1′ (Document/Picture Library). (see the Docs description below) |
| tp_Fields | This fields is a really important one because it is the glue between the physical column name (in table UserData) and the logical field name (defined in Sharepoint).The content of this field is an xml fragment of ‘Field’ and ‘FieldRef’ elements. These elements contains a lot of details like the formula for calculated fields, the datatype, etc. But the most important parts are the ‘Name’ and ‘ColName’ attributes. The ‘Name’ attribute stores the Sharepoint defined name and ‘ColName’ attribute stores the SQL Server column name of the UserData table.Now, the catch 22… This field is filled __ONLY__ when the list’s server template is customized. So if you created a Task list and you didn’t customized by adding an additional field or modifying an existing one, the ‘Fields’ column will be empty. I guess the definition of standard list are ‘Hardcoded’ into Sharepoint so the content of the column is interpreted only when needed. |
UserData
UserData is THE interesting table… It contains every items (records) stored in any list. Yes every list stores its data in the same table.
Additional metadata (fields that you added if you customized a list) is stored in one of the multiple generic column name (int1, int2, int3, in4, …, nvarchar1, nvarchar2, nvarchar3, …). Now you see the importance of the Fields column of the Lists table…
| tp_ID | Identifier of the list item. This is an auto-increment number managed by Sharepoint (not an IDENTITY column) |
| tp_ListId | The identifier of the list owner. |
| bit1, bit2, bitN, int1, int2, intN, float1, float2, floatN, … | The data columns |
Docs
Another interesting table. This table stores the files of a document/picture library and the attachments of a list item.
The way to find an attachment is different than the way to find a file of a document library.
For a document library:
- Use the value from Lists.tp_ID with the Docs.ListId column.
- and use the value from UserData.tp_ID with the Docs.DocLibRowId column
For a list’s attachment, its more complicated:
- The list itself map to a root folder. It is simple to retrieve the root folder from the Docs table by using Lists.tp_RootFolder with Docs.Id.The result is something like:
DirName /MyRootWebsite/MyWebsite/Lists/
LeafName MyList - To get all the attachment of a list, then you need to concatenate the DirName and LeafName of the previous result with ‘Attachments’ constant and search for it.Ex: Search for DirName = /MyRootWebsite/MyWebsite/Lists/MyList/AttachmentsThe result is something like:
DirName: /MyRootWebsite/MyWebsite/Lists/MyList/Attachments
LeafName: list identifier (ex: 1) - Now to finally get to the attachment, we need an additional concatenation: DirName + LeafNameEx: Search for DirName = /MyRootWebsite/MyWebsite/Lists/MyList/Attachments/1
The result is something like:
DirName: /MyRootWebsite/MyWebsite/Lists/MyList/Attachments/1
LeafName: MyFile.zip
Content: the content of the file.
| Id | Unique identifier of a file |
| DirName | The full name of the folder where the file is stored. |
| LeafName | The name of the file or folder depending what kind of data the row contains. |
| Type | Indicate if the row contains a file or a folder. 1 = folder, 0 = file. |
| ListId | The list identifier that owns the file |
Typical/Helper Queries
It’s not really easy to explain the structure, so it will surely not be easy to understand it correctly (misinterpretation, etc.). When we’re talking about database, sometimes nothing explain better than sample queries…
Note: in the queries, information in bold should be replace by actual values.
Retrieve a specific website based on its name:
SELECT * FROM Webs WHERE Title = ‘**MyWebsite**’;
Retrieve all lists owned by a website:
SELECT * FROM Lists WHERE tp_WebId = ‘**MyWebsiteIdentifier**’;
Retrieve all records owned by a list:
SELECT * FROM UserData WHERE tp_ListId = ‘**MyListIdentifier**’;
Retrieve all attachments for all items of a list:
SELECT content.*
FROM Lists l
INNER JOIN Docs root ON (root.Id = l.tp_RootFolder)
INNER JOIN Docs content ON (content.ListId = root.ListId)
WHERE l.tp_ID = ‘**MyListIdentifier**’
AND content.DirName LIKE root.DirName + ‘/’ + root.LeafName + ‘/Attachments’ + ‘/%’;
Retrieve all attachments for a list item:
SELECT content.*
FROM Lists l
INNER JOIN Docs root ON (root.Id = l.tp_RootFolder)
INNER JOIN Docs content ON (content.ListId = root.ListId)
WHERE l.tp_ID = ‘**MyListIdentifier**’
AND content.DirName = root.DirName + ‘/’ + root.LeafName + ‘/Attachments’ + ‘/’ + ‘**MyListItemIdentifier**’;
Retrieve all files for a document library:
SELECT * FROM Docs WHERE ListId = ‘**MyListIdentifier**’;
Retrieve the file for a document library item:
SELECT * FROM Docs WHERE ListId = ‘**MyListIdentifier**’ AND DoclibRowId = **MyListItemIdentifier**;