Newbie - Trying for SqLite Integration


Author
Message
smallscript
smallscript
New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)
Group: Forum Members
Posts: 5, Visits: 15
Hello,

I am not sure how to access/enable using a SqLite data source.

I watched the brief video, and read the documentation area. I then went and read the connection-string required. for the "Data Sources:Add Database Connection:Connection String" section.

However, when I enter in a "Data Source=path-to-db.db3;" with "Table Name:" "aDg_dr" I get an "Invalid Settings" dialog with the message "Unable to establish database connection. Please correct the errors shown.".

The orange-warning-icon tooltip says "Unable to resolve provider for: SQLite".

The System.Data.SQLite packages are designed to be bundled/installed with the .NET APP (LogViewPlus) and not located in the GAC (global assembly cache).

In fact, onerously so. Wherein, powershell "nuget" or various other .NET tools look for and want your development project to install as part of the build. So I can't figure out how to solve the "provider" problem.

🦜Any help would be great, thank you.

P.S., I am looking to use these SqLite features which our log-tools and use of SQLite require/utilize:
- https://www.sqlite.org/gencol.html
- https://www.sqlite.org/json1.html
- https://www.sqlite.org/expridx.html

Which combine to store log records in a SINGLE COLUMN as a single JSON value , where all the other columns are "GENERATED" (via JSON expression implicit triggers) and INDEXES are (JSON1 based expression indexes).

This requires supporting SQLite recent versions after 2019 (2020, 2021 are all ok).
smallscript
smallscript
New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)
Group: Forum Members
Posts: 5, Visits: 15

Checking out these instructions NOW:
----
In this scenario, you will need to add the appropriate assemblies to the %AppData%/LogViewPlus/DbProviders directory. Alternatively, the assemblies can also be placed in the %ProgramData%/LogViewPlus/DbProviders directory. If assemblies are placed in either of these directories, they will only be loaded if the 'Allow Plugins' setting is enabled. Dynamic assembly loading should only be necessary if the target libraries cannot be found in the GAC.
----

Manually copying the assemblies.
Edited 3 Years Ago by LogViewPlus Support
smallscript
smallscript
New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)
Group: Forum Members
Posts: 5, Visits: 15

Enabling "SettingsTonguelugins:Allow Plugins" and placing the statically-linked zip contents of "sqlite-netFx46-static-binary-bundle-x64-2015-1.0.114.0.zip" in "%AppData%/LogViewPlus/DbProviders" solved that first hurdle.

Now it is saying "Invalid column settings. A minimum of two columns are required including a valid timestamp.".

Which makes sense as a requirement. The table "aDg_dr" has these three columns:
  • jdTime - julian-day-timestamp
  • jHdr - json-encoded metadata
  • jMsg - json-encoded event
That then fouled up after that "Invalid columns" message. Cascading into "Unable to establish database connection...". with error (-26). SQL Network interfaces error.

Restarting several times etc did not solve that. The file is fully accessible at its given file-path from other tools like DB Browser for SQLite so that's not a problem.

Our experience with the .NET SQLite providers has been historically poor. SQLite works rock-solidly when using direct native APIs (static or sqlite3.dll). SQLite requires NO server and .NET SQL abstractions incur significant overhead vs direct access. Providers appear to be  quite a few versions behind on tooling etc.Our experience with the .NET SQLite providers has been historically poor. SQLite works rock-solidly when using direct native APIs (static or sqlite3.dll). SQLite requires NO server and .NET SQL abstractions incur significant overhead vs direct access. Providers appear to be  quite a few versions behind on tooling etc.

We use the SQLite file-format for logging because it only requires a single-file and creating read/write is pretty universal on all platforms and handles multiple process-thread scenarios efficiently.We use the SQLite file-format for logging because it only requires a single-file and creating read/write is pretty universal on all platforms and handles multiple process-thread scenarios efficiently.


Now I am really stuck trying to figure out the next steps to testing/trying LogViewPlus with SQLite.

LogViewPlus looks like it would be awesome if I can get it working smoothly with SQLite files as data sources (somewhat like how you've integrated ZIP files).

Any help getting unstuck would be greatly appreciated 😀.

🦜 Meanwhile I will either test out the JSON provider-parsers, or look to see if I can externally TAIL/READ the SQLite entries and feed them to LogViewPlus using some other provider-parser.🦜 Meanwhile I will either test out the JSON provider-parsers, or look to see if I can externally TAIL/READ the SQLite entries and feed them to LogViewPlus using some other provider-parser.

SQLite has efficient mechanisms to detect when any records in the database file have changed; which the .NET providers do not.SQLite has efficient mechanisms to detect when any records in the database file have changed; which the .NET providers do not.


Thank you again!
Edited 3 Years Ago by LogViewPlus Support
LogViewPlus Support
LogViewPlus Support
Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)
Group: Moderators
Posts: 1.1K, Visits: 3.7K
Hi smallscript,

Thanks for reporting these issues.  I went ahead and edited the formatting of the posts above to be a little bit more clear - hope that is OK.  The forum software we use sometimes inserts threaded discussions which actually make the thread harder to follow.

Glad to hear you have already made progress on the first issue.

I suspect the remaining issue has something to do with the datatype of the columns you are trying to access.  I am not familiar with the JSON1 extension, but it looks like this is defining new column types which may not be supported by the .Net provider.  If you are in control of the table format, I think integration with other tools might be easier with more standard column types - such as string.

In any case, that is just a guess as to what might be causing the problem.  I will need to take a closer look at this issue and get back to you.

The comments concerning the .Net provider are interesting.  It is premature to consider rewriting the adapter, but if SQLite does not work with a standard interface then we may need to remove support.  The .Net API allows us to distribute code that is functionally compatible with several different systems while ignoring the licensing issues that would arise from distributing third party code.  So abandoning the .Net provider is probably not an option for us.

Thanks,

Toby
LogViewPlus Support
LogViewPlus Support
Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)
Group: Moderators
Posts: 1.1K, Visits: 3.7K
Hi smallscript,

I spent some time looking at this today and I think I was off track above.  It seems the JSON data is stored as plain text - so this should not be a problem for the .Net provider. 

Are you able to send me a sample SQLite db containing the schema or some test data?  I understand this is not always possible, but it would help give me a better understanding of the problem.  You can contact me via email if you want to send the files offline.

LogViewPlus does not understand Julian dates by default, so this could be the issue as well.  Although, this would not fully explain the error messages you are seeing above.

Toby
smallscript
smallscript
New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)New Member (19 reputation)
Group: Forum Members
Posts: 5, Visits: 15
I attached a file example.

The JulianDay format is a Float64 and is the default format for time/date in SqLite. It is also a pretty universal time format in many scientific systems, especially astronomy, etc.

I have simple (single-file not many methods) open-source javascript (efekt-core.js.st) library I authored for handling basic date/time conversions of JulianDay, UUID, epoch-time, etc.


Attachments
afm-d.dr.db3.zip (174 views, 1.00 KB)
LogViewPlus Support
LogViewPlus Support
Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)Prestige User (3.9K reputation)
Group: Moderators
Posts: 1.1K, Visits: 3.7K
Thanks for the sample log entries.  Apologies for the delay in getting back to you.  

When I tested, I was also unable to read the data.  This is because several pieces were missing from how we handle reading from a database - including decimal timestamps and Julian dates.  We have just released LogViewPlus v2.5.31 as a BETA release which should resolve these issues:



Note that the jdTime field needs to be explicitly set as the timestamp.


When reading the data, I used the System.Data.SQLite provider for .Net 4.6 x64 build.  No further libraries were used, but note that some characters in the JSON data were not read correctly.  This was a problem I noticed in my SQLite client as well and I suspect is a problem with the JSON type over the ADO.Net provider.  However, I think the data will be helpful as presented.

Hope that helps.  Please let me know if you have any further questions or issues.

Toby
Edited 3 Years Ago by LogViewPlus Support
GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Login

Explore
Messages
Mentions
Search