New SQL server plugin

[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 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

[Datamix]

Since I'm running VRS under mono too I'm also highly interested in an a mysql conncetion.

[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.

[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?

[Datamix]

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;

[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

[pavelkonir]

And is possible migration data from sqlite to sql server?

[pavelkonir]

I do not understand why there is no user and configuration table in that database.

[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.

[pavelkonir]

Yes, I have installed preview version server. Environment is Mono.

Could not be the user database in the sql server?