Page 1 of 2

New SQL server plugin

Posted: Mon Jan 22, 2018 2:09 pm
by Bhaal
Hi agw,

Just wondering about this plugin, is it for connecting to MS SQL or will it connect to MySQL for those people running under Mono? If it could, then another plugin could be to store the entire flight path for each flight tracked, which could be displayed in the reports instead of just Start and End locations :D I know others have done this directly from dump1090 to mysql via some php, I saw it over a year ago, never got around to implementing it myself though :( But would be cool if VRS could do this natively (upon request, I know it'd make the DB largish)

Thanks

Re: New SQL server plugin

Posted: Mon Jan 22, 2018 6:44 pm
by Datamix
Since I'm running VRS under mono too I'm also highly interested in an a mysql conncetion.

Re: New SQL server plugin

Posted: Tue Jan 23, 2018 12:10 am
by agw
It's just SQL Server. However there were changes that I had to make to the server to accommodate using a proper multithreading database engine rather than the single-threaded SQLite library, now that they've been implemented there's nothing stopping a similar plugin being written for MySQL.

FWIW you can get SQL Server 2017 for free on a couple of Linux distros (https://docs.microsoft.com/en-us/sql/li ... inux-setup). I've not yet tried it though.

Re: New SQL server plugin

Posted: Wed Feb 07, 2018 12:39 pm
by pavelkonir
I have installed SQL server 2017, server is running.

I set this connectin string: Server=localhost,1401;User Id=vrs; Password=vrs-password1;Database=master

Test connection return: A connection can be made with this connection string

But when I click on button "update schema" show error: The server reported an exception, see log for details: Exception has been thrown by the target of an invocation.

Is connection string correct?

Re: New SQL server plugin

Posted: Wed Feb 07, 2018 1:15 pm
by Datamix
pavelkonir wrote:
Wed Feb 07, 2018 12:39 pm
Server=localhost,1401;User Id=vrs; Password=vrs-password1;Database=master
Add a semicolon at the end

Like this:

Code: Select all

Server=localhost,1401;Database=master;Uid=vrs;Password=vrs-password1;

Re: New SQL server plugin

Posted: Wed Feb 07, 2018 1:16 pm
by pavelkonir
Connection is now ready, schema was created, but tables are empty. In log are errors.

[2018-02-07 13:13:43.328 UTC] [t19] Caught an exception on a fast tick heartbeat event: System.ArgumentException: The parameter data type of System.Data.DataTable is invalid.
at System.Data.SqlClient.SqlParameter.InferSqlType (System.Object value) [0x00049] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at System.Data.SqlClient.SqlParameter.set_Value (System.Object value) [0x00008] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at Dapper.TableValuedParameter.Set (System.Data.IDbDataParameter parameter, System.Data.DataTable table, System.String typeName) [0x00007] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.DataTableHandler.SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+TypeHandlerCache`1[T].SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at (wrapper dynamic-method) <>f__AnonymousType7`1[System.Data.DataTable]:ParamInfof0cf69b1-cbe6-4694-a5bc-894802d42ceb (System.Data.IDbCommand,object)
at Dapper.CommandDefinition.SetupCommand (System.Data.IDbConnection cnn, System.Action`2[T1,T2] paramReader) [0x000b4] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+<QueryImpl>d__136`1[T].MoveNext () [0x000ba] in <63a8cc06dfb842f1823bc51677815d4b>:0
at System.Collections.Generic.List`1[T]..ctor (System.Collections.Generic.IEnumerable`1[T] collection) [0x00077] in <c9f8153c41de4f8cbafd0e32f9bf6b28>:0
at System.Linq.Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00018] in <9da65c3aa2654e53b5f11b79677182e0>:0
at Dapper.SqlMapper.Query[T] (System.Data.IDbConnection cnn, System.String sql, System.Object param, System.Data.IDbTransaction transaction, System.Boolean buffered, System.Nullable`1[T] commandTimeout, System.Nullable`1[T] commandType) [0x0003c] in <63a8cc06dfb842f1823bc51677815d4b>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase+<>c__DisplayClass83_0.<GetManyAircraftAndFlightsCountByCode>b__0 (VirtualRadar.Plugin.SqlServer.ConnectionWrapper wrapper) [0x00042] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.PerformInConnection (System.Action`1[T] action) [0x0000f] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.GetManyAircraftAndFlightsCountByCode (System.Collections.Generic.IEnumerable`1[T] icao24s) [0x0001b] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Library.AircraftDetailFetcher.DoFetchManyAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x000d4] in <b7286c723c894270aa8da0ed53899e65>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].FetchAllAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x00011] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].Heartbeat_FastTimerTicked (System.Object sender, System.EventArgs args) [0x00095] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at (wrapper dynamic-method) System.Object:lambda_method (System.Runtime.CompilerServices.Closure,object,object,System.EventArgs)
at VirtualRadar.Interface.EventHelper.Raise[TEventArgs] (System.Delegate eventHandler, System.Object sender, System.Func`1[TResult] buildArgsCallback, System.Action`1[T] exceptionCallback, System.Boolean throwEventHelperException) [0x000f5] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
[2018-02-07 13:13:44.389 UTC] [t20] Caught an exception on a fast tick heartbeat event: System.ArgumentException: The parameter data type of System.Data.DataTable is invalid.
at System.Data.SqlClient.SqlParameter.InferSqlType (System.Object value) [0x00049] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at System.Data.SqlClient.SqlParameter.set_Value (System.Object value) [0x00008] in <9ec44cbcc9fa4c6b81867c576ebbc98b>:0
at Dapper.TableValuedParameter.Set (System.Data.IDbDataParameter parameter, System.Data.DataTable table, System.String typeName) [0x00007] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.DataTableHandler.SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+TypeHandlerCache`1[T].SetValue (System.Data.IDbDataParameter parameter, System.Object value) [0x00000] in <63a8cc06dfb842f1823bc51677815d4b>:0
at (wrapper dynamic-method) <>f__AnonymousType7`1[System.Data.DataTable]:ParamInfof0cf69b1-cbe6-4694-a5bc-894802d42ceb (System.Data.IDbCommand,object)
at Dapper.CommandDefinition.SetupCommand (System.Data.IDbConnection cnn, System.Action`2[T1,T2] paramReader) [0x000b4] in <63a8cc06dfb842f1823bc51677815d4b>:0
at Dapper.SqlMapper+<QueryImpl>d__136`1[T].MoveNext () [0x000ba] in <63a8cc06dfb842f1823bc51677815d4b>:0
at System.Collections.Generic.List`1[T]..ctor (System.Collections.Generic.IEnumerable`1[T] collection) [0x00077] in <c9f8153c41de4f8cbafd0e32f9bf6b28>:0
at System.Linq.Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00018] in <9da65c3aa2654e53b5f11b79677182e0>:0
at Dapper.SqlMapper.Query[T] (System.Data.IDbConnection cnn, System.String sql, System.Object param, System.Data.IDbTransaction transaction, System.Boolean buffered, System.Nullable`1[T] commandTimeout, System.Nullable`1[T] commandType) [0x0003c] in <63a8cc06dfb842f1823bc51677815d4b>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase+<>c__DisplayClass83_0.<GetManyAircraftAndFlightsCountByCode>b__0 (VirtualRadar.Plugin.SqlServer.ConnectionWrapper wrapper) [0x00042] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.PerformInConnection (System.Action`1[T] action) [0x0000f] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Plugin.SqlServer.BaseStationDatabase.GetManyAircraftAndFlightsCountByCode (System.Collections.Generic.IEnumerable`1[T] icao24s) [0x0001b] in <41efae8bf49945009e0487d3b50b2ebd>:0
at VirtualRadar.Library.AircraftDetailFetcher.DoFetchManyAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x000d4] in <b7286c723c894270aa8da0ed53899e65>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].FetchAllAircraft (System.Collections.Generic.IEnumerable`1[T] fetchedDetails) [0x00011] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at VirtualRadar.Interface.AircraftFetcher`2[TKey,TDetail].Heartbeat_FastTimerTicked (System.Object sender, System.EventArgs args) [0x00095] in <249e9c2d71db409a84e6c3a181bd8ef5>:0
at (wrapper dynamic-method) System.Object:lambda_method (System.Runtime.CompilerServices.Closure,object,object,System.EventArgs)
at VirtualRadar.Interface.EventHelper.Raise[TEventArgs] (System.Delegate eventHandler, System.Object sender, System.Func`1[TResult] buildArgsCallback, System.Action`1[T] exceptionCallback, System.Boolean throwEventHelperException) [0x000f5] in <249e9c2d71db409a84e6c3a181bd8ef5>:0

Re: New SQL server plugin

Posted: Wed Feb 07, 2018 1:29 pm
by pavelkonir
And is possible migration data from sqlite to sql server?

Re: New SQL server plugin

Posted: Wed Feb 07, 2018 1:35 pm
by pavelkonir
I do not understand why there is no user and configuration table in that database.

Re: New SQL server plugin

Posted: Thu Feb 08, 2018 1:12 am
by agw
Did you install the preview server as well as the SQL Server plugin? Is this running on Windows or Mono?

There is a command-line utility that ships with the plugin - it's called BaseStationImport and it is installed into the VRS program folder. If you run BaseStationImport on its own then it shows some help. Note that there is a known truncation issue in the current preview (see viewtopic.php?f=11&t=1533)

Configuration will not be stored in the database. I've not decided on whether to put the connection log in there yet... it's not a problem when there's just one site using the database but it gets tricky when there are lots. With BaseStation you just have one site writing and the rest reading, there's no problem there. WIth multiple sites all writing to the same connection tables things could get tricky, the connection log currently assumes that each instance has the log to itself. Also connection log writes are not a big performance hog, especially once BaseStation reads and writes aren't using SQLite.

Re: New SQL server plugin

Posted: Thu Feb 08, 2018 8:45 pm
by pavelkonir
Yes, I have installed preview version server. Environment is Mono.

Could not be the user database in the sql server?