Navisworks embedded SQLite Database, reuse for other purpose?

[alexisDVJML]

Navisworks maintains a SQLITE database inside each Navisworks file.

This is super useful and can be accessed to store plugin specific stuff per document.

Navisworks API includes dedicated classes NavisworksConnection, NavisworksCommand etc. providing a reasonably good level of features and performance.

However there are cases where you would not want data stored in the actual navisworks file, for example cached information since:
- these can be regerenated for example at load

- this would grow .nwf file

- this would slow Navisworks file opeing since the embedded database is actually extracted as dedicated file (which is very good and allow for example to view and modify its content using tools like DBBrowser for SQLite!)

So I was thinking of having a separate, external DB file for such cached information.

I know I can add any 3rd party SQLite for C# library in our plugin however:
- increased dependencies
- different code vs Navisworks DB

Ideally I would prefer to just use Navisworks API.
I just haven found (yet) a way to create such a database or at least pass a file path to it to Navisworks API.

Did anyone already explore this?

Thanks.

Here the main entry points:

namespace Autodesk.Navisworks.Api.Data
{
	public class NavisworksConnection : DbConnection
	{
		public override ConnectionState State { get; }
		public override string ServerVersion { get; }
		public override string Database { get; }
		public override string DataSource { get; }
		public override string ConnectionString { get; set; }
		public override DbTransaction BeginDbTransaction(IsolationLevel A_0);
		//
		// Summary:
		//     Not implement
		public override void ChangeDatabase(string databaseName);
		//
		// Summary:
		//     Does nothing, always keep open.
		public override void Close();
		public NavisworksCommand CreateCommand();
		public override DataTable GetSchema(string collectionName, string[] restrictionValues);
		[EditorBrowsable(EditorBrowsableState.Never)]
		public LcUSQLiteConnection InternalGetConnection();
		//
		// Summary:
		//     Does nothing, connection is automatically opened by DocumentDatabase.
		public override void Open();
		protected override DbCommand CreateDbCommand();
	}
}

[alexisDVJML]

or those interested in using an external DB editor to see/edit said embedded database, here is my code to open it with DB Broser for SQLite, use and modify at will 😉

static public string GetEmbeddedDatabasePath(Document doc)
{
	using (NavisworksConnection nwDbConnection = doc.Database.Value)
	{
	    if (nwDbConnection.State != System.Data.ConnectionState.Open) return null;
		using (NavisworksCommand nwCmd = new NavisworksCommand("PRAGMA database_list;", nwDbConnection))
		using (NavisWorksDataReader nwDataReader = nwCmd.ExecuteReader())
		{
			if (!nwDataReader.HasRows) return null;
			nwDataReader.Read();
			Debug.Assert(nwDataReader.GetString(1) == "main");
			return nwDataReader.GetString(2);
		}
	}
}

static public ProcessExt BrowseDocumentDatabase(Document doc)
{
	string dbPath = GetEmbeddedDatabasePath(doc);
	if (String.IsNullOrEmpty(dbPath))
	{
		Debug.Assert(false);
		return null;
	}

	string editorpath = @"C:Program Files\DB Browser for SQLite\DB Browser for SQLite.exe";

	ProcessExt pa = new ProcessExt("\"" + editorpath + "\"", $"\"{dbPath}\"");
	pa.Start();
	return pa;
}

[alexisDVJML]

or those interested in using an external DB editor to see/edit said embedded database, here is my code to open it with DB Broser for SQLite, use and modify at will 😉

static public string GetEmbeddedDatabasePath(Document doc)
{
	using (NavisworksConnection nwDbConnection = doc.Database.Value)
	{
	    if (nwDbConnection.State != System.Data.ConnectionState.Open) return null;
		using (NavisworksCommand nwCmd = new NavisworksCommand("PRAGMA database_list;", nwDbConnection))
		using (NavisWorksDataReader nwDataReader = nwCmd.ExecuteReader())
		{
			if (!nwDataReader.HasRows) return null;
			nwDataReader.Read();
			Debug.Assert(nwDataReader.GetString(1) == "main");
			return nwDataReader.GetString(2);
		}
	}
}

static public ProcessExt BrowseDocumentDatabase(Document doc)
{
	string dbPath = GetEmbeddedDatabasePath(doc);
	if (String.IsNullOrEmpty(dbPath))
	{
		Debug.Assert(false);
		return null;
	}

	string editorpath = @"C:Program Files\DB Browser for SQLite\DB Browser for SQLite.exe";

	ProcessExt pa = new ProcessExt("\"" + editorpath + "\"", $"\"{dbPath}\"");
	pa.Start();
	return pa;
}

[alexisDVJML]

Sorry, double click = double post and can't find how to delete a post 😉

[swarali_kulkarni4KMYK]

What is ProcessExt here? can you also explain if we have to store string key and value on a document which methods do I have to override from the DbConnection class?

[alexisDVJML]

1/ ProcessExt is just to launch the external SQLite database viewer/editor. Very useful during development to check you correctly create/fill the databases.

2/ To store/load data, no need to override classes, just use NavisworksConnection, NavisworksCommand, NavisworksDataReader.
I don't have clean code to share but below a copy/pasted example

using (NavisworksConnection nwConnection = doc.Database)
				{
					if (nwConnection.State != System.Data.ConnectionState.Open)
						throw new DatabaseException(1, @"Navisworks Document Embedded database Connection NOT OPEN!");

					using (var nwCmd = new NavisworksCommand($"SELECT * FROM {PEEKEE_DATABASE_MAIN_TABLE_NAME} ORDER BY id", nwConnection))
					using (var nwReader = nwCmd.ExecuteReader())
					{
						Debug.Assert(nwReader.FieldCount == 4);

						if (!nwReader.HasRows)
							return null;

						int c = 0;
						var entries = new DataEntries();
						//int idx = 0;

						while (nwReader.Read())
						{
							int entryIdx = (int)nwReader.GetInt64(0);
							//Debug.Assert(entryIdx >= idx);

							c++;
							if (c > 1024)
								break;

							if (entryIdx > (int)DataField.LAST)
							{
								Log.Add(LogLevel.Warning, $"Extra Table Entry idx={entryIdx}", msgs);
								continue;
							}

							int idx = entries.PeekSlot(entryIdx, entryIdx);
							TryConvertFromTable(nwReader, ref entries[idx]);
						}

						return entries;
					}
				}