Base Table

Iain Elder's data development blog

Moved to elder.io

My new blog is at elder.io

All the old stuff can stay here for now.

See you at the new place!

What’s that object?

Good article from Thomas LaRock on why he tibbles. Read the comments, especially from Gail Shaw.

If you do everything through the SSMS GUI, then maybe you won’t think of it. But this works just fine.

SELECT [name], [type_desc]
 FROM sys.objects
 WHERE [object_id] = OBJECT_ID(N'dbo.WhatTheObject');

Is that what Karen Lopez means by ‘try harder’? It’s not even hard, just tedious.

It’s not pleasant, but sys.objects works for me.

Anything else is a bonus. I think we all agree we need better tools.

Calling tables or views Dim or Fact is applying Hungarian notation to make up for a deficient type system.

The database engine doesn’t have Dimension types or Fact types, just tables. The objects have the same interface, but the semantics are different.

Making them look different is the best you can do without a tool to leverage extended properties.

Phil Factor blames migrations from Visual Basic for its popularity in the SQL Server world, but I think he may be confusing Hungarian notation with plain old tibbling.

I’m too young to know, but thanks for the legacy all the same!

Roll on HTML video

So I no longer have to put up with this.

flash_install_in_progress

Without warning, Adobe is trying to foist McAfee on me.

Because I’m on “Enterprise” Windows, I dodged the bullet.

flash_install_mcafee_fail

Would I get I choice if I was just a home user?

Flash is crapware. Roll on HTML video so I can watch Youtube in peace.

Mounting a Windows Server 2012 backup

Today my laptop took three power-ons to get to the login screen. In between it either spontaneously powered off or showed the streaky window of death.

I freaked out at the thought of losing all my data again, so I borrowed my flatmate’s Seagate Freeagent drive (1.5TB, external power supply) and used Windows Server Backup to make a bare-metal recovery backup.

Writing the backup with Windows Server backup was pretty easy. Took about two hours to write 107GB over USB. Enough time to see Tottenham beat Sherriff at football.

Reading the backup was harder. When you click to mount the drive…

mount-backup

You get this scary error:

03-couldnt-mount-file

“The disk image isn’t initialized, contains partitions that aren’t recognizable, or contains volumes that haven’t been assigned drive letters. Please use the Disk Management snap-in to make sure that the disk, partitions, and volumes are in a usable state.”

Is my backup borked?

No, it’s just that Windows Server 2012 is too lazy to assign a driver letter for you.

Don’t bother searching for ‘Disk Management’ in the Start screen. It’s not listed there.

Bernardo Arocho of BJTechNews explained in Youtube short that you can run Disk Management from the WIN+X menu or by running diskmgmt.msc:

04-run-diskmgmt

In Disk Management, the blue disk is the one that lacks a drive letter.

disk-management-blue-disk

Right click on the disk and choose ‘change drive letter’ to open another dialog:

06-change-drive-letter-dialog

Click add to open another dialog:

07-add-drive-letter-dialog

This is the useful part. For me, the tool has chosen the first free drive letter (D:). Check and change if necessary. I just clicked OK to accept it.

Now the drive is visible in Explorer. Success!

mount-success

But why can’t it just work?

Disabling a Destructive Shortcut in Firefox

From Mozilla Support (emphasis mine):

Several times over the last couple weeks, I type up a very well thought out response on Facebook in political conversations with friends. An hour or two into my work, I hit backspace with the intent of editing what I wrote (maybe after I scrolled or something, so maybe I unintentionally took the edit window out of focus). The navigator takes me back a page and I lose my two hours of work. Infuriating! Shortcuts with the ability to wipe out two hours of works should be disable-able.

I feel your pain, buddy.

Shortcuts with the ability to wipe out two hours of work should not be included, period.

Who thought it was a good idea to use backspace (which deletes text) as a navigation button?

The problem is not just that the backspace button is dangerously overloaded. It’s that going forward again does not restore your work! There is no undo for this stupid feature.

By the way, to disable this in Firefox, enter about:config into the address bar, search for browser.backspace_action, double-click on the setting, and enter 2 in the modal dialog. Clear as mud.

I’ve asked Mozilla to disable backspace as a navigation button by default. I know it’s breaking with convention, but this convention is broken.

Firefox is back

I started using Firefox again. It’s the only mainstream browser right now that meets my needs.

Too many sites are broken in Opera 12.

Opera Next doesn’t have bookmarks. (Probably the first since Mosaic!)

Chrome still doesn’t support MRU tab switching. People have been asking for a long time.

Internet Explorer 10 has all these features. It even has nice accessibility features like ‘clear textfield’ and ‘show password’. But it works only on Windows. I might switch to Linux soon.

I think I first picked up on Firefox in version 0.8, when it was blazing fast and super cool, at least compared to IE 6. It got bloaty and slow around the same time I found Opera. Apparently it’s faster now.

Let’s see!

HTSQL

HTSQL

HTSQL is a comprehensive navigational query language for relational databases.

Advanced Query Language

HTSQL is a complete query language featuring automated linking, aggregation, projections, filters, macros, a compositional syntax, and a full set of data types & functions.

Relational Database Gateway

HTSQL requests are translated to efficient SQL queries. HTSQL supports different SQL dialects including SQLite, PostgreSQL, MySQL, Oracle, and Microsoft SQL Server.

Web Service Integration

HTSQL is a web service that accepts queries as URLs, returning results formatted as HTML, JSON, CSV or XML. With HTSQL, databases can be accessed, secured, cached, and integrated using standard web technologies.

Embedded Reporting

HTSQL is a backend framework supporting visual dashboard and reporting tools. HTSQL can be included in client-side Javascript or server-side Python applications. HTSQL plugins can provide domain specific customizations.

Development Environment

HTSQL includes a command line and web based query editor with syntax highlighting, context-sensitive completion, and error messages with tips and suggestions.

Communication Tool

HTSQL is used for collaboration among business users, data analysts, and application developers. HTSQL queries can be emailed, embedded in reports, and included in feature requests.

Where does SocialSafe store its backups?

Today I used SocialSafe to backup my Facebook profile.

It does a good job of backing up all the content created by me, but it’s not so good at backing up what people have shared with me.

It saves a large version of all the photos I have posted to Facebook, but only thumbnail versions of those shared with me. I really hope they improve on that, because it’s a very promising tool.

The SocialSafe app doesn’t make it clear where it stores your backups, and I can’t find any documentation on their website.

SocialSafe lets you export your backup to files in a place you choose, so why do I want to know?

The export doesn’t contain all the data that SocialSafe has downloaded. Even if it is only thumbnails, I want to see some evidence of the photos that people have shared with me.

SocialSafe must be reading and writing all that data somewhere, so it must be opening files. Maybe we can use handle to find them!

Handle should already be on your path for this to work.

Open a new command prompt and issue this command:

handle -p socialsafe

It will produce one line of output for every file SocialSafe has open.

The full output is about 60 lines. Here’s the interesting part:

3EC: File  (RW-)  D:\Users\iain\AppData\Roaming\com.1minus1.socialsafe.D675411CF670AA3EFAC13BDD847989BEDE2115E2.1\Local Store\Facebook.db

A file called Facebook.db. Looks like a sqlite database file!

Let’s open it in the sqlite shell:

sqlite3 "D:\Users\iain\AppData\Roaming\com.1minus1.socialsafe.D675411CF670AA3EFAC13BDD847989BEDE2115E2.1\Local Store\Facebook.db"

sqlite produces a prompt, so we know it’s a proper sqlite database:

SQLite version 3.8.0.2 2013-09-03 17:11:13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

Use the .tables command to list all the tables in the database:

sqlite> .tables
album                    linkedincertification    searchlog
attachment               linkedincomment          searchlogservice
backup                   linkedincompany          searchlogserviceitem
backupstatistic          linkedincompanylocation  tags
changetrack              linkedincompanyproduct   thread
checkin                  linkedinconnection       threadrecipients
checkintag               linkedineducation        twittercursor
comment                  linkedinimaccount        twitterfavourite
configuration            linkedinlanguage         twitterfollower
diaryviewfilter          linkedinlike             twitterfriend
eventmember              linkedinnetworkupdate    twittermedia
facebookevent            linkedinpatent           twittermention
firstdownload            linkedinphonenumber      twittermessage
friend                   linkedinposition         twitterpage
frontcoveruser           linkedinprivatecontacts  twitterprofile
googleplusactivity       linkedinprofile          twittertweet
googleplusassociation    linkedinpublication      twitteruser
googleplusattachment     linkedinrecommendation   user
googlepluscomment        linkedinskill            viadeocareer
googleplusemail          linkedintwitteraccount   viadeocomments
googlepluslanguage       linkedinurlresource      viadeocontact
googleplusorganisation   linkedinuser             viadeoeducation
googleplusperson         message                  viadeoforum
googleplusplacelived     note                     viadeoforummember
googleplusprofile        noteimage                viadeoforumpost
googleplusurl            page                     viadeolanguage
googleplususer           pageadmin                viadeolikes
instagramcomment         pagefancount             viadeomessages
instagramlike            patches                  viadeonewsitems
instagramlocation        photo                    viadeoprofile
instagrampeople          post                     viadeosmarttag
instagramphoto           profile                  viadeosmarttagblacklist
instagramprofile         rss                      viadeouser
instagramtag             rssfeed                  viadeovisits
instagramuser            rssfeeditem
likes                    rssimage

That’s a lot of tables, most of which are not exported. The friend, post, photo, and profile tables probably are, because that data is included in the export.

The attachment table contains post attachments. The icon column contains the URL of the thumbnail of the post:

sqlite> SELECT icon FROM attachment WHERE icon <> '' LIMIT 1;

https://fbexternal-a.akamaihd.net/safe_image.php?d=AQCaGVgO8DXpi4fD&w=154&h=154&url=http%3A%2F%2Fi2.wp.com%2Fstokboet.files.wordpress.com%2F2013%2F09%2Fmg_0004.jpg%3Ffit%3D1000%252C1000

You can fetch using a web browser or wget.

I haven’t checked to see whether SocialSafe is fetching those thumbnails when you browse the journal, or if it actually stores the image data somewhere else.

How do you extract an archive using the shell?

Quick! How do you extract an archive using the shell?

Most of the time, you just want to extract everything to a new directory and then explore.

Unless you’re crusty, remembering how involves a man page, a sigh, and a Google search.

Screw that. If you’re on Debian, just install unp.

unp is a Perl script that makes extraction just work. None of that alphabet soup nonsense from tar!

Install it like this:

iain@turing:~$ sudo apt-get install unp
Reading package lists... Done
Building dependency tree
Reading state information... Done
Suggested packages:
bzip2 unrar unrar-free xdms p7zip p7zip-full cabextract orange
The following NEW packages will be installed:
unp
0 upgraded, 1 newly installed, 0 to remove and 20 not upgraded.
Need to get 0 B/13.6 kB of archives.
After this operation, 102 kB of additional disk space will be used.
Selecting previously deselected package unp.
(Reading database ... 22070 files and directories currently installed.)
Unpacking unp (from .../archives/unp_2.0~pre4_all.deb) ...
Processing triggers for man-db ...
Setting up unp (2.0~pre4) ...

Use it like this to extract the neo4j archive you downloaded earlier:

root@turing:/home/iain# unp neo4j-community-1.9.1-unix.tar.gz
neo4j-community-1.9.1/
[...]
neo4j-community-1.9.1/doc/manual/UPGRADE.txt

I’ve truncated the output here to just the first and last line because this particular archive contains several hundred files.

But it’s as simple as that!

How do you download Neo4j using the shell?

The Neo4j Server Installation documentation has minimal advice:

Download the latest release from http://neo4j.org/download
select the appropriate version for your platform

The website offers this download link for the current stable version of Neo4j on Linux: http://download.neo4j.org/artifact?edition=community&version=1.9.1&distribution=tarball

This works fine in a web browser but not in my shell.

I used wget to fetch the package using that link:

root@turing:/home/iain# wget http://download.neo4j.org/artifact?edition=community&version=1.9.1&distribution=tarball
[1] 14951
[2] 14952
root@turing:/home/iain# --2013-07-04 17:07:12-- http://download.neo4j.org/artifact?edition=community
Resolving download.neo4j.org... 198.101.198.70
Connecting to download.neo4j.org|198.101.198.70|:80... connected.
HTTP request sent, awaiting response... 404 Not Found
2013-07-04 17:07:13 ERROR 404: Not Found.
^C
[1]- Exit 8 wget http://download.neo4j.org/artifact?edition=community
[2]+ Done version=1.9.1

After reporting a 404 error, wget hangs until I kill it with CTRL + C.

A website called Geil Things has a Neo4j wiki article that contains the real download link.

The link is hidden in a shell script. The important line looks like this:

wget http://dist.neo4j.org/neo4j-community-${NEWVERSION}-unix.tar.gz

If you replace ${NEWVERSION} with 1.9.1 you get the link the current stable version of Neo4j.

The complete wget command looks like this:

wget http://dist.neo4j.org/neo4j-community-1.9.1-unix.tar.gz

If you run the command in the shell you should see output like this:

root@turing:/home/iain# wget http://dist.neo4j.org/neo4j-community-1.9.1-unix.tar.gz
--2013-07-04 17:21:52-- http://dist.neo4j.org/neo4j-community-1.9.1-unix.tar.gz
Resolving dist.neo4j.org... 178.236.4.60
Connecting to dist.neo4j.org|178.236.4.60|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 37512748 (36M) [application/x-tar]
Saving to: “neo4j-community-1.9.1-unix.tar.gz”
100%[======================================>] 37,512,748 30.5M/s in 1.2s
2013-07-04 17:21:53 (30.5 MB/s) - “neo4j-community-1.9.1-unix.tar.gz” saved [37512748/37512748]

Now you’ve downloaded the archive. You can continue to follow the installation instructions in the documentation.

Follow

Get every new post delivered to your Inbox.

Join 275 other followers