When you have a home server, you may not have the chance to be connected to the Internet using a static IP address, instead your IP address is a lease from your Internet Service Provider and may change every day depending on the setup.

So when you are in the outside world, it is not possible to guess the IP address of your computer, how could you connect to it if you wanted to?  The solution is to use a dynamic DNS forwarder like DynDNS.org.  Talking about DynDNS, many standard firmwares (Netgear, Linksys, etc.) support updating your IP when it changes.  Since I use Tomato firmware, I will explain how to configure it.

• First, you must register on DynDNS.org by creating an account.
• Then go into the “My Services / Host services” section of the site and add a new host name
• The free service offers you to choose any sub-domain name from their available list.
  (ex: myhostname.getmyip.com, myhostname.kick-ass.net)
• So you have to enter:
  • the name you want (myhostname)
  • the domain name (kick-ass.net)
  • if you want to enable wildcards
    (if enabled, anything.myhostname.kick-ass.net will be forwarded as well)
  • the service type: Host with IP address
  • leave the IP address, Tomato will update it automatically
  • leave the mail router checkbox unchecked

  
• create that host…

• Now you have to configure Tomato
• Navigate to the router’s web administration
• Open up the page under Basic / DDNS.
• Tomato offers to configure up to 2 host names, let’s fill the first one:
  • IP Address: Use WAN IP xxx.xxx.xxx.xxx (recommended)
  • Service: DynDNS – Dynamic
  • Username: your account name
  • Password: your account password
  • host name: myhostname.kick-ass.net

  

17 comments

Here is an interesting serie of blog posts from Tobias to start learning Microsoft PowerShell:

• Day 1: Getting Organized
• Day 2: Writing Scripts and Translating VBScript
• Day 3: Discovering objects (COM, WMI & ADSI)
• Day 4: Ins and Outs of the Windows Registry
• Day 5: Using WMI
• Day 6: ADSI Connecting to Domains/Computers and Binding to Objects
• Day 7: Manage Users

No comments

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: 100: Custom list 101: Document library 103: Link list 104: Annoucement list 105: Contact list 107: Task list 109: Picture library
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**;

17 comments

Situation:

I’ve been put in charge of an application running on the Sharepoint 2003 plateform. The application composed of many webparts and stores its data directly within lists and document libraries. The website is split by language, one subsite by language. For example:

• /root/English
• /root/French
• /root/Spanish
• …

The purpose of this application is to store and search files with custom metadata attached to it. So we find a customized document library in each language subsite.

The problem:
Overall, there is about 1 000 000 records in those libraries (the biggest library is english with about 400 000 records) and searching through this using custom CAML queries is a pain!

At some point in the application we need to know in which language a document is available. For this we query each document libraries using a filter on a metadata that contains our unique identifier.

<where>
<fieldref name="MyUniqueID">
<value type="Number">00000</value>
</where>


The tracing revealed an increasing time of processing. We currently have 7 languages and the processing time goes like that:

• 1st language: less than 1 second
• 2nd language: about 2 seconds
• 3rd language: about 5 seconds
• 4th language: about 10 seconds
• 5th language: about 20 seconds
• 6th language: about 20 seconds
• 7th language: about 20 seconds

Work Around:
First of all, the design of the application is not the best but that’s not the purpose of this post.
Some would say, get that data out of Sharepoint! Ya, that’s what we’re doing (for many reasons…) but in the meantime, the system must continue to be up and running.

Metadata is dynamic (it varies from list to list) and Sharepoint is not optimized to search using this. But Sharepoint recognize when you search using its internal identifier and it goes very fast. So if we could map our identifier to the sharepoint internal identier, it should be faster. I found out that doing this 2 steps operation is actually faster (in my scenario it reduced the total from 77 seconds to 2 seconds average):

1. Run an ugly SQL statement directly on the Sharepoint database to find the Sharepoint internal identifier based on our own unique identifier.
2. Query Sharepoint API (using CAML) with Sharepoint internal identifier.

the SQL statement looks like this:

SELECT tp_ID FROM UserData WHERE tp_ListID = '...' AND Float1 = ...


Float1 stores our unique identifier. I determine it at run-time by looking at the Fields property (not really sure by heart of the property name…). It contains the Sharepoint column Name / SQL column name mappings.

To get the list identifier (a GUID), just take the ID property of the SPList object.

That’s about it… I could talk about it for hours but I think I said the important points.

Additional notes:
Maybe the following hotfix would have correct the problem:

http://support.microsoft.com/?id=900929

See this explanation from Keith Richie’s blog:

http://blogs.msdn.com/krichie/archive/2006/07/20/673197.aspx

No comments