Offloading NVivo files

Part 2

So now we have our NVivo file reloaded as an SQLite database file. For instance, here is NVivo's sample project 'Environmental Change Down East'. The easiest way to look at is is with an SQLite GUI browser, such as SQLiteBrowser available for Windows, Linux or Mac. But you can also use the command-line version of SQLite, which is what I will use to demonstrate a few things. Start it with:

$ sqlite3 <filename>
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.

Let's start by showing the list of tables:

sqlite> .tables
Annotation               Link                     ReportSearchFolderItems
Category                 Lock                     Role                   
CompoundSourceRegion     Matrix                   SearchFolder           
EventLog                 Member                   Source                 
ExtendedItem             Model                    Style                  
FrameworkMatrix          NodeReference            UserGroup              
Item                     Project                  UserProfile            
ItemAuthorisation        Query

The Project file seems an obivous place to start:

sqlite> .dump Project
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE [Project](
        [Title] [nvarchar](256) COLLATE BINARY NOT NULL,
        [Description] [nvarchar](512) COLLATE BINARY NOT NULL,
        [CreatedDate] [datetime] NOT NULL,
        [ModifiedDate] [datetime] NOT NULL,
        [CreatedBy] [uniqueidentifier] NOT NULL,
        [ModifiedBy] [uniqueidentifier] NOT NULL,
        [ReadPassword] [nvarchar](32) COLLATE BINARY NOT NULL,
        [WritePassword] [nvarchar](32) COLLATE BINARY NOT NULL,
        [ReadPasswordHint] [nvarchar](64) COLLATE BINARY NOT NULL,
        [WritePasswordHint] [nvarchar](64) COLLATE BINARY NOT NULL,
        [Version] [nvarchar](16) COLLATE BINARY NOT NULL,
        [CasebookLayout] nvarchar(9999) NULL,
        [UnassignedLabel] [nvarchar](64) COLLATE BINARY NOT NULL,
        [NotApplicableLabel] [nvarchar](64) COLLATE BINARY NOT NULL,
        [IndexLanguage] [int] NOT NULL,
        [EmbedSources] [bit] NOT NULL,
        [EmbeddedFileSizeLimitBytes] [int] NOT NULL,
        [Id] [uniqueidentifier] NOT NULL,
        [AllowGuestAccess] [bit] NOT NULL,
        [EventLogging] [bit] NOT NULL,
        [UserColors] [bit] NOT NULL DEFAULT ((0)),
        [StopWords] nvarchar(9999) NULL,
        [DateOfBirth] [bit] NOT NULL DEFAULT ((1)),
        [Gender] [bit] NOT NULL DEFAULT ((1)),
        [Religion] [bit] NOT NULL DEFAULT ((1)),
        [Location] [bit] NOT NULL DEFAULT ((1)),
        [RelationshipStatus] [bit] NOT NULL DEFAULT ((1)),
        [Bio] [bit] NOT NULL DEFAULT ((1)),
        [Web] [bit] NOT NULL DEFAULT ((1)),
CONSTRAINT [Project_PK_Constraint] PRIMARY KEY 
(
        [Id] ASC
)
);
INSERT INTO "Project" VALUES('μϥϭϠϩϦϥϤϜϥϫϘϣΗκϟϘϥϞϜΗλϦϮϥΗμϘϪϫ','ϋϟϠϪΗϠϪΗϘΗϪϘϤϧϣϜΗϧϩϦϡϜϚϫΣΗϮϟϠϚϟΗϠϪΗϛϠϪϫϩϠϙϬϫϜϛΗϮϠϫϟΗυύϠϭϦΗΨΧΗϪϦϝϫϮϘϩϜΥΗϋϟϠϪΗϪϫϬϛϰΗϜϯϘϤϠϥϜϛΗϧϜϩϚϜϧϫϠϦϥϪΗϘϙϦϬϫΗϜϥϭϠϩϦϥϤϜϥϫϘϣΗϚϟϘϥϞϜΗϘϤϦϥϞΗϩϜϪϠϛϜϥϫϪΗϦϝΗΨΪΗϚϦϤϤϬϥϠϫϠϜϪΗϠϥΗϘϥΗϘϩϜϘΗϦϝΗϚϦϘϪϫϘϣΗυϦϩϫϟΗκϘϩϦϣϠϥϘΗϚϘϣϣϜϛΗΙλϦϮϥΗμϘϪϫΥΙΗϋϟϜΗϪϫϬϛϰΗϛϦϚϬϤϜϥϫϜϛΗϫϟϜϪϜΗϧϜϩϚϜϧϫϠϦϥϪΣΗϙϩϦϬϞϟϫΗϫϦϞϜϫϟϜϩΗϪϫϘϢϜϟϦϣϛϜϩϪΗϦϥΗϘϣϣΗϪϠϛϜϪΗϦϝΗϫϟϜΗϠϪϪϬϜΣΗϘϥϛΗϝϘϚϠϣϠϫϘϫϜϛΗϧϬϙϣϠϚΗϛϠϘϣϦϞϬϜΗϘϙϦϬϫΗϫϟϜΗϘϩϜϘ⎐ϪΗϝϬϫϬϩϜΥΗϋϟϜΗϬϣϫϠϤϘϫϜΗϞϦϘϣΗϠϪΗϫϦΗϟϜϣϧΗϚϦϤϤϬϥϠϫϠϜϪΣΗϛϜϭϜϣϦϧϜϩϪΗϘϥϛΗϧϣϘϥϥϜϩϪΗϜϥϭϠϪϠϦϥΗϣϦϥϞΤϫϜϩϤΗϪϫϩϘϫϜϞϠϜϪΗϝϦϩΗϪϬϪϫϘϠϥϘϙϣϜΗϣϘϥϛΗϬϪϜΗϘϥϛΗϛϜϭϜϣϦϧϤϜϥϫΥ','2010-04-10T12:28:58.047','2013-07-01T05:22:01.090','2607C75F-F045-4C71-B109-E8E16460C834','2607C75F-F045-4C71-B109-E8E16460C834','','','','','10.0.338.0',NULL,'Unassigned','Not Applicable',2,1,20971520,'778EAE97-4A06-4BF5-B555-A757F4CE463B',0,0,0,NULL,1,1,1,1,1,1,1);

OK well that looks comprehensible enough, apart from those unexpected Greek and other letters in the title and description fields. It seems that the unicode characters from the Project Title and Description fields, and the Name and Description fields from the Item table have been messed with somehow.

So, that's all for today. Stay posted for Part 3.

Written on March 7, 2015