/// <summary>
/// Returns all entities.
/// </summary>
/// <returns></returns>
public IEnumerable <Route> Get()
{
#if DEBUG
var stopwatch = Stopwatch.StartNew();
Console.Write($"Fetching routes...");
#endif
var routes = new List <Route>();
using (var reader = _connection.BeginBinaryExport("COPY route TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
routes.Add(new Route()
{
Id = reader.ReadStringSafe(),
AgencyId = reader.ReadStringSafe(),
ShortName = reader.ReadStringSafe(),
LongName = reader.ReadStringSafe(),
Description = reader.ReadStringSafe(),
Type = (RouteTypeExtended)reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer),
Url = reader.ReadStringSafe(),
Color = reader.ReadIntSafe(),
TextColor = reader.ReadIntSafe(),
VehicleCapacity = reader.ReadIntSafe()
});
}
}
#if DEBUG
stopwatch.Stop();
Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms");
#endif
return(routes);
}
/// <summary>
/// Returns all entities.
/// </summary>
/// <returns></returns>
public IEnumerable <Shape> Get()
{
#if DEBUG
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
Console.Write($"Fetching shapes...");
#endif
var shapePoints = new List <Shape>();
using (var reader = _connection.BeginBinaryExport("COPY shape TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
shapePoints.Add(new Shape()
{
Id = reader.ReadStringSafe(),
Latitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real),
Longitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real),
Sequence = (uint)reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer),
DistanceTravelled = reader.ReadDoubleSafe()
});
}
}
shapePoints = shapePoints.OrderBy(x => x.Id).ThenBy(x => x.Sequence).ToList();
#if DEBUG
stopwatch.Stop();
Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms");
#endif
return(shapePoints);
}
/// <summary>
/// Returns all entities.
/// </summary>
/// <returns></returns>
public IEnumerable <Frequency> Get()
{
#if DEBUG
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
#endif
var frequencies = new List <Frequency>();
using (var reader = _connection.BeginBinaryExport("COPY frequency TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
frequencies.Add(new Frequency()
{
TripId = reader.ReadStringSafe(),
StartTime = reader.ReadStringSafe(),
EndTime = reader.ReadStringSafe(),
HeadwaySecs = reader.ReadStringSafe(),
ExactTimes = reader.ReadIntSafe() == 1
});
}
}
#if DEBUG
stopwatch.Stop();
Console.WriteLine($"Fetch frequencies: {stopwatch.ElapsedMilliseconds} ms");
#endif
return(frequencies);
}
/// <summary>
/// Returns all entities.
/// </summary>
/// <returns></returns>
public IEnumerable <FareRule> Get()
{
#if DEBUG
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
#endif
var fareRules = new List <FareRule>();
using (var reader = _connection.BeginBinaryExport("COPY fare_rule TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
fareRules.Add(new FareRule()
{
FareId = reader.ReadStringSafe(),
RouteId = reader.ReadStringSafe(),
OriginId = reader.ReadStringSafe(),
DestinationId = reader.ReadStringSafe(),
ContainsId = reader.ReadStringSafe()
});
}
}
#if DEBUG
stopwatch.Stop();
Console.WriteLine($"Fetch farerules: {stopwatch.ElapsedMilliseconds} ms");
#endif
return(fareRules);
}
public IEnumerable <Trip> Get()
{
#if DEBUG
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
Console.Write($"Fetching trips...");
#endif
var trips = new List <Trip>();
using (var reader = _connection.BeginBinaryExport("COPY trip TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
trips.Add(new Trip()
{
Id = reader.ReadStringSafe(),
RouteId = reader.ReadStringSafe(),
ServiceId = reader.ReadStringSafe(),
Headsign = reader.ReadStringSafe(),
ShortName = reader.ReadStringSafe(),
Direction = (DirectionType?)reader.ReadIntSafe(),
BlockId = reader.ReadStringSafe(),
ShapeId = reader.ReadStringSafe(),
AccessibilityType = (WheelchairAccessibilityType?)reader.ReadIntSafe()
});
}
}
#if DEBUG
stopwatch.Stop();
Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms");
#endif
return(trips);
}
public void readListFromDatabase(string connectionstring)
{
using (var conn = new NpgsqlConnection(connectionstring))
{
conn.Open();
conn.TypeMapper.UseNetTopologySuite();
CMapTileList aMapTileList = new CMapTileList();
// note that it is overkill to do bulk import for two objects, but as example...
using (var reader = conn.BeginBinaryExport("COPY public.tiletest(id_tile_250, the_geom ) TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
long aMapId = reader.Read <long>(NpgsqlDbType.Bigint);
Polygon aPolygon = reader.Read <Polygon>(NpgsqlDbType.Geometry);
Coordinate aCoordinate = aPolygon.Coordinates[0];
CMapTile aCmapTile = new CMapTile((long)aCoordinate.X, (long)aCoordinate.Y);
aCmapTile.TileID = aMapId;
aMapTileList.theMapList.Add(aCmapTile);
}
reader.Cancel();
}
conn.Close();
}
}
/// <summary>
/// Returns all entities.
/// </summary>
/// <returns></returns>
public IEnumerable <FareAttribute> Get()
{
#if DEBUG
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
#endif
var fareAttributes = new List <FareAttribute>();
using (var reader = _connection.BeginBinaryExport("COPY fare_attribute TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
fareAttributes.Add(new FareAttribute()
{
FareId = reader.ReadStringSafe(),
Price = reader.ReadStringSafe(),
CurrencyType = reader.ReadStringSafe(),
PaymentMethod = (PaymentMethodType)reader.ReadIntSafe(),
Transfers = (uint?)reader.ReadIntSafe(),
TransferDuration = reader.ReadStringSafe(),
AgencyId = reader.ReadStringSafe()
});
}
}
#if DEBUG
stopwatch.Stop();
Console.WriteLine($"Fetch fareattributes: {stopwatch.ElapsedMilliseconds} ms");
#endif
return(fareAttributes);
}
public List <NetworkNode> GetNodes(List <long> nodeIds = null)
{
var nodes = new List <NetworkNode>();
string commandText = nodeIds == null
? $"COPY {SchemaPrefix}nodes (id, latitude, longitude) TO STDOUT (FORMAT BINARY)"
: $"COPY (select id, latitude, longitude from {SchemaPrefix}nodes where id in ({string.Join(",", nodeIds)})) TO STDOUT (FORMAT BINARY)";
using (var reader = _conn.BeginBinaryExport(commandText))
{
while (reader.StartRow() > -1)
{
var node = default(NetworkNode);
node.Id = reader.Read <long>();
node.Latitude = reader.Read <double>();
node.Longitude = reader.Read <double>();
nodes.Add(node);
}
}
return(nodes);
}
public int Export()
{
var sum = 0;
unchecked
{
using (var exporter = _conn.BeginBinaryExport("COPY data TO STDOUT (FORMAT BINARY)"))
while (exporter.StartRow() != -1)
{
for (var col = 0; col < 10; col++)
{
sum += exporter.Read <int>(NpgsqlDbType.Integer);
}
}
}
return(sum);
}
public void CloseDuringCopy()
{
// TODO: Check no broken connections were returned to the pool
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginBinaryImport("COPY data (field_text, field_int4) FROM STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginBinaryExport("COPY data (field_text, field_int2) TO STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) FROM STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginRawBinaryCopy("COPY data (field_text, field_int4) TO STDIN BINARY");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginTextImport("COPY data (field_text, field_int4) FROM STDIN");
}
using (var conn = new NpgsqlConnection(ConnectionString)) {
conn.Open();
ExecuteNonQuery("CREATE TEMP TABLE data (field_text TEXT, field_int2 SMALLINT, field_int4 INTEGER)", conn);
conn.BeginTextExport("COPY data (field_text, field_int4) TO STDIN");
}
}
/// <summary>
/// Returns all stop times.
/// </summary>
/// <returns></returns>
public IEnumerable <StopTime> Get()
{
#if DEBUG
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
#endif
var stopTimes = new List <StopTime>();
using (var reader = _connection.BeginBinaryExport("COPY stop_time TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
stopTimes.Add(new StopTime()
{
TripId = reader.ReadStringSafe(),
ArrivalTime = TimeOfDay.FromTotalSeconds(reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer)),
DepartureTime = TimeOfDay.FromTotalSeconds(reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer)),
StopId = reader.ReadStringSafe(),
StopSequence = (uint)reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer),
StopHeadsign = reader.ReadStringSafe(),
PickupType = (PickupType?)reader.ReadIntSafe(),
DropOffType = (DropOffType?)reader.ReadIntSafe(),
ShapeDistTravelled = reader.ReadStringSafe(),
PassengerBoarding = (int?)reader.ReadIntSafe(),
PassengerAlighting = (int?)reader.ReadIntSafe(),
ThroughPassengers = (int?)reader.ReadIntSafe(),
TotalPassengers = (int?)reader.ReadIntSafe(),
ContinuousPickup = (ContinuousPickup?)reader.ReadIntSafe(),
ContinuousDropOff = (ContinuousDropOff?)reader.ReadIntSafe(),
});
}
}
stopTimes = stopTimes.OrderBy(x => x.TripId).ThenBy(x => x.StopSequence).ToList();
#if DEBUG
stopwatch.Stop();
Console.WriteLine($"Fetch stoptimes: {stopwatch.ElapsedMilliseconds} ms");
#endif
return(stopTimes);
}
/// <summary>
/// Returns all entities.
/// </summary>
/// <returns></returns>
public IEnumerable <Stop> Get()
{
#if DEBUG
var stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
Console.Write($"Fetching stops...");
#endif
var stops = new List <Stop>();
using (var reader = _connection.BeginBinaryExport("COPY stop TO STDOUT (FORMAT BINARY)"))
{
while (reader.StartRow() > 0)
{
var feedId = reader.Read <int>(NpgsqlTypes.NpgsqlDbType.Integer);
stops.Add(new Stop()
{
Id = reader.ReadStringSafe(),
Code = reader.ReadStringSafe(),
Name = reader.ReadStringSafe(),
Description = reader.ReadStringSafe(),
Latitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real),
Longitude = reader.Read <double>(NpgsqlTypes.NpgsqlDbType.Real),
Zone = reader.ReadStringSafe(),
Url = reader.ReadStringSafe(),
LocationType = (LocationType?)reader.ReadIntSafe(),
ParentStation = reader.ReadStringSafe(),
Timezone = reader.ReadStringSafe(),
WheelchairBoarding = reader.ReadStringSafe(),
LevelId = reader.ReadStringSafe(),
PlatformCode = reader.ReadStringSafe()
});
}
}
#if DEBUG
stopwatch.Stop();
Console.WriteLine($" {stopwatch.ElapsedMilliseconds} ms");
#endif
return(stops);
}
