diff options
Diffstat (limited to 'src/Enmarcha.Experimental.ServiceViewer/Data')
20 files changed, 1920 insertions, 0 deletions
diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/AppDbContext.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/AppDbContext.cs new file mode 100644 index 0000000..bb3c6c8 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/AppDbContext.cs @@ -0,0 +1,59 @@ +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data; + +public class AppDbContext : DbContext +{ + public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) + { + } + + protected override void OnModelCreating(ModelBuilder modelBuilder) + { + // Route -> Agency + modelBuilder.Entity<GtfsRoute>() + .HasOne(r => r.Agency) + .WithMany() + .HasForeignKey(r => new { r.AgencyId, r.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + // Trip -> Route + modelBuilder.Entity<GtfsTrip>() + .HasOne(t => t.Route) + .WithMany() + .HasForeignKey(t => new { t.RouteId, t.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + // Relación StopTimes -> Trip + modelBuilder.Entity<GtfsStopTime>() + .HasOne(st => st.GtfsTrip) + .WithMany() + .HasForeignKey(st => new { st.TripId, st.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + // Relación StopTimes -> Stop + modelBuilder.Entity<GtfsStopTime>() + .HasOne(st => st.GtfsStop) + .WithMany() + .HasForeignKey(st => new { st.StopId, st.FeedId }) + .HasPrincipalKey(a => new { a.Id, a.FeedId }); + + modelBuilder.Entity<GtfsTrip>() + .Property(t => t.TripWheelchairAccessible) + .HasDefaultValue(TripWheelchairAccessible.Empty); + + modelBuilder.Entity<GtfsTrip>() + .Property(t => t.TripBikesAllowed) + .HasDefaultValue(TripBikesAllowed.Empty); + } + + public DbSet<GtfsAgency> Agencies { get; set; } + public DbSet<GtfsCalendar> Calendars { get; set; } + public DbSet<GtfsCalendarDate> CalendarDates { get; set; } + public DbSet<GtfsRoute> Routes { get; set; } + public DbSet<GtfsStop> Stops { get; set; } + public DbSet<GtfsStopTime> StopTimes { get; set; } + public DbSet<GtfsTrip> Trips { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Extensions/TimeExtensions.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Extensions/TimeExtensions.cs new file mode 100644 index 0000000..5f79b75 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Extensions/TimeExtensions.cs @@ -0,0 +1,20 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Extensions; + +public static class TimeExtensions +{ + extension(TimeSpan) { + public static TimeSpan FromGtfsTime(string gtfsTime) + { + var parts = gtfsTime.Split(":", 3); + + var hours = int.Parse(parts[0]); + var minutes = int.Parse(parts[1]); + var seconds = int.Parse(parts[2]); + + int days = hours / 24; + int leftoverHours = hours % 24; + + return new TimeSpan(days, leftoverHours, minutes, seconds); + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs new file mode 100644 index 0000000..6a41d1a --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/DirectionId.cs @@ -0,0 +1,7 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum DirectionId +{ + Outbound = 0, + Inbound = 1 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs new file mode 100644 index 0000000..02ab612 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/ExceptionType.cs @@ -0,0 +1,7 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum ExceptionType +{ + Added = 1, + Removed = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/RouteType.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/RouteType.cs new file mode 100644 index 0000000..e487c32 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/RouteType.cs @@ -0,0 +1,15 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum RouteType +{ + Tram = 0, + Subway = 1, + Rail = 2, + Bus = 3, + Ferry = 4, + CableTram = 5, + AerialLift = 6, + Funicular = 7, + Trolleybus = 11, + Monorail = 12 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs new file mode 100644 index 0000000..346b93a --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripBikesAllowed.cs @@ -0,0 +1,8 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum TripBikesAllowed +{ + Empty = 0, + CanAccommodate = 1, + NotAllowed = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs new file mode 100644 index 0000000..6bcdb22 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/TripWheelchairAccessible.cs @@ -0,0 +1,8 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum TripWheelchairAccessible +{ + Empty = 0, + CanAccommodate = 1, + NotAccessible = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs new file mode 100644 index 0000000..d62f60e --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Enums/WheelchairBoarding.cs @@ -0,0 +1,8 @@ +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; + +public enum WheelchairBoarding +{ + Unknown = 0, + SomeVehicles = 1, + NotPossible = 2 +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Feed.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Feed.cs new file mode 100644 index 0000000..db9b283 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/Feed.cs @@ -0,0 +1,21 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("feeds")] +public class Feed +{ + /// <summary> + /// Auto-incrementing ID value for each feed, to identify it and its version + /// </summary> + [Key] + public int Id { get; set; } + + [MaxLength(32)] public string ShortName { get; set; } + [MaxLength(32)] public string LongName { get; set; } + [MaxLength(255)] public string DownloadUrl { get; set; } + [MaxLength(32)] public string Etag { get; set; } + + public DateTime InsertedAt { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsAgency.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsAgency.cs new file mode 100644 index 0000000..8ce129c --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsAgency.cs @@ -0,0 +1,46 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_agencies")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsAgency +{ + [Key] + [Column("agency_id")] + [MaxLength(255)] + public required string Id { get; set; } + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("agency_name")] + [MaxLength(255)] + public string Name { get; set; } = string.Empty; + + [Column("agency_url")] + [MaxLength(255)] + public string Url { get; set; } = string.Empty; + + [Column("agency_timezone")] + [MaxLength(50)] + public string Timezone { get; set; } = string.Empty; + + [Column("agency_lang")] + [MaxLength(5)] + public string Language { get; set; } = string.Empty; + + [Column("agency_phone")] + [MaxLength(30)] + public string? Phone { get; set; } = string.Empty; + + [Column("agency_email")] + [MaxLength(255)] + public string? Email { get; set; } = string.Empty; + + [Column("agency_fare_url")] + [MaxLength(255)] + public string? FareUrl { get; set; } = string.Empty; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendar.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendar.cs new file mode 100644 index 0000000..bcc7c39 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendar.cs @@ -0,0 +1,45 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_calendar")] +[PrimaryKey(nameof(ServiceId), nameof(FeedId))] +public class GtfsCalendar +{ + [Key] + [Column("service_id")] + [MaxLength(32)] + public string ServiceId { get; set; } = null!; + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("monday")] + public bool Monday { get; set; } + + [Column("tuesday")] + public bool Tuesday { get; set; } + + [Column("wednesday")] + public bool Wednesday { get; set; } + + [Column("thursday")] + public bool Thursday { get; set; } + + [Column("friday")] + public bool Friday { get; set; } + + [Column("saturday")] + public bool Saturday { get; set; } + + [Column("sunday")] + public bool Sunday { get; set; } + + [Column("start_date")] + public DateOnly StartDate { get; set; } + + [Column("end_date")] + public DateOnly EndDate { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs new file mode 100644 index 0000000..e9b5a92 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsCalendarDate.cs @@ -0,0 +1,24 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_calendar_dates")] +[PrimaryKey(nameof(ServiceId), nameof(Date), nameof(FeedId))] +public class GtfsCalendarDate +{ + [Column("service_id")] + [MaxLength(32)] + public required string ServiceId { get; set; } + + [Column("date")] + public required DateTime Date { get; set; } + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("exception_type")] + public required ExceptionType ExceptionType { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsRoute.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsRoute.cs new file mode 100644 index 0000000..80ef38a --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsRoute.cs @@ -0,0 +1,70 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_routes")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsRoute +{ + [Column("route_id")] + [MaxLength(255)] + public required string Id { get; set; } + + public string SafeId => Id.Replace(" ", "_").Replace("-", "_"); + + [Column("feed_id")]public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("agency_id")] + [ForeignKey(nameof(Agency))] + [MaxLength(255)] + public required string AgencyId { get; set; } + + [ForeignKey(nameof(AgencyId))] + public GtfsAgency Agency { get; set; } = null!; + + /// <summary> + /// Short name of a route. Often a short, abstract identifier (e.g., "32", "100X", "Green") + /// that riders use to identify a route. Both route_short_name and route_long_name may be defined. + /// </summary> + [Column("route_short_name")] + [MaxLength(32)] + public string ShortName { get; set; } = string.Empty; + + /// <summary> + /// Full name of a route. This name is generally more descriptive than the route_short_name and often + /// includes the route's destination or stop. Both route_short_name and route_long_name may be defined. + /// </summary> + [Column("route_long_name")] + [MaxLength(255)] + public string LongName { get; set; } = string.Empty; + + [Column("route_desc")] + [MaxLength(255)] + public string? Description { get; set; } = string.Empty; + + [Column("route_type")] + public RouteType Type { get; set; } = RouteType.Bus; + + [Column("route_url")] + [MaxLength(255)] + public string? Url { get; set; } = string.Empty; + + [Column("route_color")] + [MaxLength(7)] + public string? Color { get; set; } = string.Empty; + + [Column("route_text_color")] + [MaxLength(7)] + public string? TextColor { get; set; } = string.Empty; + + /// <summary> + /// Orders the routes in a way which is ideal for presentation to customers. + /// Routes with smaller route_sort_order values should be displayed first. + /// </summary> + [Column("route_sort_order")] + public int SortOrder { get; set; } = 1; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStop.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStop.cs new file mode 100644 index 0000000..6198ffa --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStop.cs @@ -0,0 +1,51 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; +using NetTopologySuite.Geometries; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_stops")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsStop +{ + [Column("stop_id")] + [MaxLength(32)] + public required string Id { get; set; } + + [Column("feed_id")]public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("stop_code")] + [MaxLength(32)] + public string Code { get; set; } = string.Empty; + + [Column("stop_name")] + [MaxLength(255)] + public string Name { get; set; } = string.Empty; + + [Column("stop_desc")] + [MaxLength(255)] + public string? Description { get; set; } + + [Column("stop_pos")] + public Point? Position { get; set; } + + [Column("stop_url")] + [MaxLength(255)] + public string? Url { get; set; } + + [Column("stop_timezone")] + [MaxLength(50)] + public string? Timezone { get; set; } + + [Column("wheelchair_boarding")] + public WheelchairBoarding WheelchairBoarding { get; set; } = WheelchairBoarding.Unknown; + + // [Column("location_type")] + // public int LocationType { get; set; } + // + // [Column("parent_station")] + // public int? ParentStationId { get; set; } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStopTime.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStopTime.cs new file mode 100644 index 0000000..2bed623 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsStopTime.cs @@ -0,0 +1,44 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Extensions; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_stop_times")] +[PrimaryKey(nameof(TripId), nameof(StopSequence), nameof(FeedId))] +public class GtfsStopTime +{ + [Column("trip_id")] + [ForeignKey("TripId")] + [MaxLength(32)] + public string TripId { get; set; } = null!; + + [Column("feed_id")]public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [ForeignKey(nameof(TripId))] public GtfsTrip GtfsTrip { get; set; } = null!; + + [Column("arrival_time")] public string Arrival { get; set; } + public TimeSpan ArrivalTime => TimeSpan.FromGtfsTime(Arrival); + + [Column("departure_time")] public string Departure { get; set; } + public TimeSpan DepartureTime => TimeSpan.FromGtfsTime(Departure); + + [Column("stop_id")] + [ForeignKey(nameof(GtfsStop))] + [MaxLength(32)] + public required string StopId { get; set; } + + [ForeignKey(nameof(StopId))] public GtfsStop GtfsStop { get; set; } = null!; + + [Column("stop_sequence")] public int StopSequence { get; set; } = 0; + + // [Column("pickup_type")] + // public int? PickupType { get; set; } + // + // [Column("drop_off_type")] + // public int? DropOffType { get; set; } + + [Column("shape_dist_traveled")] public double? ShapeDistTraveled { get; set; } = null!; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsTrip.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsTrip.cs new file mode 100644 index 0000000..8dd5271 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Gtfs/GtfsTrip.cs @@ -0,0 +1,57 @@ +using System.ComponentModel.DataAnnotations; +using System.ComponentModel.DataAnnotations.Schema; +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Enums; +using Microsoft.EntityFrameworkCore; + +namespace Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +[Table("gtfs_trips")] +[PrimaryKey(nameof(Id), nameof(FeedId))] +public class GtfsTrip +{ + [Column("trip_id")] [MaxLength(32)] public string Id { get; set; } = null!; + + [Column("feed_id")] public int FeedId { get; set; } + [ForeignKey(nameof(FeedId))] public required Feed Feed { get; set; } + + [Column("route_id")] + [MaxLength(32)] + [ForeignKey(nameof(Route))] + public string RouteId { get; set; } = null!; + + [ForeignKey(nameof(RouteId))] public GtfsRoute Route { get; set; } = null!; + + [Column("service_id")] [MaxLength(32)] public string ServiceId { get; set; } = null!; + + [Column("trip_headsign")] + [MaxLength(255)] + public string? TripHeadsign { get; set; } + + [Column("trip_short_name")] + [MaxLength(255)] + public string? TripShortName { get; set; } + + [Column("direction_id")] public DirectionId DirectionId { get; set; } = DirectionId.Outbound; + + /// <summary> + /// Identifies the block to which the trip belongs. A block consists of a single trip or many + /// sequential trips made using the same vehicle, defined by shared service days and block_id. + /// A block_id may have trips with different service days, making distinct blocks. + /// </summary> + [Column("block_id")] + [MaxLength(32)] + public string? BlockId { get; set; } + + /// <summary> + /// Identifies a geospatial shape describing the vehicle travel path for a trip. + /// </summary> + /// <remarks>To be implemented: will be stored as a GeoJSON file instead of database records.</remarks> + [Column("shape_id")] + [MaxLength(32)] + public string? ShapeId { get; set; } + + [Column("trip_wheelchair_accessible")] + public TripWheelchairAccessible TripWheelchairAccessible { get; set; } = TripWheelchairAccessible.Empty; + + [Column("trip_bikes_allowed")] public TripBikesAllowed TripBikesAllowed { get; set; } = TripBikesAllowed.Empty; +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.Designer.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.Designer.cs new file mode 100644 index 0000000..af6f5d9 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.Designer.cs @@ -0,0 +1,547 @@ +// <auto-generated /> +using System; +using Enmarcha.Experimental.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Migrations; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; +using NetTopologySuite.Geometries; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; + +#nullable disable + +namespace Data.Migrations +{ + [DbContext(typeof(AppDbContext))] + [Migration("20251211153852_Initial")] + partial class Initial + { + /// <inheritdoc /> + protected override void BuildTargetModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "10.0.1") + .HasAnnotation("Relational:MaxIdentifierLength", 63); + + NpgsqlModelBuilderExtensions.HasPostgresExtension(modelBuilder, "postgis"); + NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("integer"); + + NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property<int>("Id")); + + b.Property<string>("DownloadUrl") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)"); + + b.Property<string>("Etag") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property<DateTime>("InsertedAt") + .HasColumnType("timestamp with time zone"); + + b.Property<string>("LongName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property<string>("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.HasKey("Id"); + + b.ToTable("feeds"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("Email") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_email"); + + b.Property<string>("FareUrl") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_fare_url"); + + b.Property<string>("Language") + .IsRequired() + .HasMaxLength(5) + .HasColumnType("character varying(5)") + .HasColumnName("agency_lang"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_name"); + + b.Property<string>("Phone") + .HasMaxLength(30) + .HasColumnType("character varying(30)") + .HasColumnName("agency_phone"); + + b.Property<string>("Timezone") + .IsRequired() + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("agency_timezone"); + + b.Property<string>("Url") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_agencies"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<DateOnly>("EndDate") + .HasColumnType("date") + .HasColumnName("end_date"); + + b.Property<bool>("Friday") + .HasColumnType("boolean") + .HasColumnName("friday"); + + b.Property<bool>("Monday") + .HasColumnType("boolean") + .HasColumnName("monday"); + + b.Property<bool>("Saturday") + .HasColumnType("boolean") + .HasColumnName("saturday"); + + b.Property<DateOnly>("StartDate") + .HasColumnType("date") + .HasColumnName("start_date"); + + b.Property<bool>("Sunday") + .HasColumnType("boolean") + .HasColumnName("sunday"); + + b.Property<bool>("Thursday") + .HasColumnType("boolean") + .HasColumnName("thursday"); + + b.Property<bool>("Tuesday") + .HasColumnType("boolean") + .HasColumnName("tuesday"); + + b.Property<bool>("Wednesday") + .HasColumnType("boolean") + .HasColumnName("wednesday"); + + b.HasKey("ServiceId", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property<DateTime>("Date") + .HasColumnType("timestamp with time zone") + .HasColumnName("date"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<int>("ExceptionType") + .HasColumnType("integer") + .HasColumnName("exception_type"); + + b.HasKey("ServiceId", "Date", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar_dates"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("AgencyId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property<string>("Color") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_color"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_desc"); + + b.Property<string>("LongName") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_long_name"); + + b.Property<string>("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_short_name"); + + b.Property<int>("SortOrder") + .HasColumnType("integer") + .HasColumnName("route_sort_order"); + + b.Property<string>("TextColor") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_text_color"); + + b.Property<int>("Type") + .HasColumnType("integer") + .HasColumnName("route_type"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("AgencyId", "FeedId"); + + b.ToTable("gtfs_routes"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.Property<string>("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("Code") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_code"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_desc"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_name"); + + b.Property<Point>("Position") + .HasColumnType("geometry") + .HasColumnName("stop_pos"); + + b.Property<string>("Timezone") + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("stop_timezone"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_url"); + + b.Property<int>("WheelchairBoarding") + .HasColumnType("integer") + .HasColumnName("wheelchair_boarding"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_stops"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.Property<string>("TripId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property<int>("StopSequence") + .HasColumnType("integer") + .HasColumnName("stop_sequence"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("Arrival") + .IsRequired() + .HasColumnType("text") + .HasColumnName("arrival_time"); + + b.Property<string>("Departure") + .IsRequired() + .HasColumnType("text") + .HasColumnName("departure_time"); + + b.Property<double?>("ShapeDistTraveled") + .HasColumnType("double precision") + .HasColumnName("shape_dist_traveled"); + + b.Property<string>("StopId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.HasKey("TripId", "StopSequence", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("StopId", "FeedId"); + + b.HasIndex("TripId", "FeedId"); + + b.ToTable("gtfs_stop_times"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.Property<string>("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("BlockId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("block_id"); + + b.Property<int>("DirectionId") + .HasColumnType("integer") + .HasColumnName("direction_id"); + + b.Property<string>("RouteId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_id"); + + b.Property<string>("ServiceId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property<string>("ShapeId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("shape_id"); + + b.Property<int>("TripBikesAllowed") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_bikes_allowed"); + + b.Property<string>("TripHeadsign") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_headsign"); + + b.Property<string>("TripShortName") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_short_name"); + + b.Property<int>("TripWheelchairAccessible") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_wheelchair_accessible"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("RouteId", "FeedId"); + + b.ToTable("gtfs_trips"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", "Agency") + .WithMany() + .HasForeignKey("AgencyId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Agency"); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", "GtfsStop") + .WithMany() + .HasForeignKey("StopId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", "GtfsTrip") + .WithMany() + .HasForeignKey("TripId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("GtfsStop"); + + b.Navigation("GtfsTrip"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", "Route") + .WithMany() + .HasForeignKey("RouteId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("Route"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.cs new file mode 100644 index 0000000..5a75d40 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/20251211153852_Initial.cs @@ -0,0 +1,318 @@ +using System; +using Microsoft.EntityFrameworkCore.Migrations; +using NetTopologySuite.Geometries; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; + +#nullable disable + +namespace Data.Migrations +{ + /// <inheritdoc /> + public partial class Initial : Migration + { + /// <inheritdoc /> + protected override void Up(MigrationBuilder migrationBuilder) + { + migrationBuilder.AlterDatabase() + .Annotation("Npgsql:PostgresExtension:postgis", ",,"); + + migrationBuilder.CreateTable( + name: "feeds", + columns: table => new + { + Id = table.Column<int>(type: "integer", nullable: false) + .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), + ShortName = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + LongName = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + DownloadUrl = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + Etag = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + InsertedAt = table.Column<DateTime>(type: "timestamp with time zone", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_feeds", x => x.Id); + }); + + migrationBuilder.CreateTable( + name: "gtfs_agencies", + columns: table => new + { + agency_id = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + feed_id = table.Column<int>(type: "integer", nullable: false), + agency_name = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + agency_url = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + agency_timezone = table.Column<string>(type: "character varying(50)", maxLength: 50, nullable: false), + agency_lang = table.Column<string>(type: "character varying(5)", maxLength: 5, nullable: false), + agency_phone = table.Column<string>(type: "character varying(30)", maxLength: 30, nullable: true), + agency_email = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true), + agency_fare_url = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_agencies", x => new { x.agency_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_agencies_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_calendar", + columns: table => new + { + service_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column<int>(type: "integer", nullable: false), + monday = table.Column<bool>(type: "boolean", nullable: false), + tuesday = table.Column<bool>(type: "boolean", nullable: false), + wednesday = table.Column<bool>(type: "boolean", nullable: false), + thursday = table.Column<bool>(type: "boolean", nullable: false), + friday = table.Column<bool>(type: "boolean", nullable: false), + saturday = table.Column<bool>(type: "boolean", nullable: false), + sunday = table.Column<bool>(type: "boolean", nullable: false), + start_date = table.Column<DateOnly>(type: "date", nullable: false), + end_date = table.Column<DateOnly>(type: "date", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_calendar", x => new { x.service_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_calendar_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_calendar_dates", + columns: table => new + { + service_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + date = table.Column<DateTime>(type: "timestamp with time zone", nullable: false), + feed_id = table.Column<int>(type: "integer", nullable: false), + exception_type = table.Column<int>(type: "integer", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_calendar_dates", x => new { x.service_id, x.date, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_calendar_dates_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_stops", + columns: table => new + { + stop_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column<int>(type: "integer", nullable: false), + stop_code = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + stop_name = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + stop_desc = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true), + stop_pos = table.Column<Point>(type: "geometry", nullable: true), + stop_url = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true), + stop_timezone = table.Column<string>(type: "character varying(50)", maxLength: 50, nullable: true), + wheelchair_boarding = table.Column<int>(type: "integer", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_stops", x => new { x.stop_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_stops_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_routes", + columns: table => new + { + route_id = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + feed_id = table.Column<int>(type: "integer", nullable: false), + agency_id = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + route_short_name = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + route_long_name = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: false), + route_desc = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true), + route_type = table.Column<int>(type: "integer", nullable: false), + route_url = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true), + route_color = table.Column<string>(type: "character varying(7)", maxLength: 7, nullable: true), + route_text_color = table.Column<string>(type: "character varying(7)", maxLength: 7, nullable: true), + route_sort_order = table.Column<int>(type: "integer", nullable: false) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_routes", x => new { x.route_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_routes_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_routes_gtfs_agencies_agency_id_feed_id", + columns: x => new { x.agency_id, x.feed_id }, + principalTable: "gtfs_agencies", + principalColumns: new[] { "agency_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_trips", + columns: table => new + { + trip_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column<int>(type: "integer", nullable: false), + route_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + service_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + trip_headsign = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true), + trip_short_name = table.Column<string>(type: "character varying(255)", maxLength: 255, nullable: true), + direction_id = table.Column<int>(type: "integer", nullable: false), + block_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: true), + shape_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: true), + trip_wheelchair_accessible = table.Column<int>(type: "integer", nullable: false, defaultValue: 0), + trip_bikes_allowed = table.Column<int>(type: "integer", nullable: false, defaultValue: 0) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_trips", x => new { x.trip_id, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_trips_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_trips_gtfs_routes_route_id_feed_id", + columns: x => new { x.route_id, x.feed_id }, + principalTable: "gtfs_routes", + principalColumns: new[] { "route_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateTable( + name: "gtfs_stop_times", + columns: table => new + { + trip_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + feed_id = table.Column<int>(type: "integer", nullable: false), + stop_sequence = table.Column<int>(type: "integer", nullable: false), + arrival_time = table.Column<string>(type: "text", nullable: false), + departure_time = table.Column<string>(type: "text", nullable: false), + stop_id = table.Column<string>(type: "character varying(32)", maxLength: 32, nullable: false), + shape_dist_traveled = table.Column<double>(type: "double precision", nullable: true) + }, + constraints: table => + { + table.PrimaryKey("PK_gtfs_stop_times", x => new { x.trip_id, x.stop_sequence, x.feed_id }); + table.ForeignKey( + name: "FK_gtfs_stop_times_feeds_feed_id", + column: x => x.feed_id, + principalTable: "feeds", + principalColumn: "Id", + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_stop_times_gtfs_stops_stop_id_feed_id", + columns: x => new { x.stop_id, x.feed_id }, + principalTable: "gtfs_stops", + principalColumns: new[] { "stop_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + table.ForeignKey( + name: "FK_gtfs_stop_times_gtfs_trips_trip_id_feed_id", + columns: x => new { x.trip_id, x.feed_id }, + principalTable: "gtfs_trips", + principalColumns: new[] { "trip_id", "feed_id" }, + onDelete: ReferentialAction.Cascade); + }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_agencies_feed_id", + table: "gtfs_agencies", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_calendar_feed_id", + table: "gtfs_calendar", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_calendar_dates_feed_id", + table: "gtfs_calendar_dates", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_routes_agency_id_feed_id", + table: "gtfs_routes", + columns: new[] { "agency_id", "feed_id" }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_routes_feed_id", + table: "gtfs_routes", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stop_times_feed_id", + table: "gtfs_stop_times", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stop_times_stop_id_feed_id", + table: "gtfs_stop_times", + columns: new[] { "stop_id", "feed_id" }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stop_times_trip_id_feed_id", + table: "gtfs_stop_times", + columns: new[] { "trip_id", "feed_id" }); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_stops_feed_id", + table: "gtfs_stops", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_trips_feed_id", + table: "gtfs_trips", + column: "feed_id"); + + migrationBuilder.CreateIndex( + name: "IX_gtfs_trips_route_id_feed_id", + table: "gtfs_trips", + columns: new[] { "route_id", "feed_id" }); + } + + /// <inheritdoc /> + protected override void Down(MigrationBuilder migrationBuilder) + { + migrationBuilder.DropTable( + name: "gtfs_calendar"); + + migrationBuilder.DropTable( + name: "gtfs_calendar_dates"); + + migrationBuilder.DropTable( + name: "gtfs_stop_times"); + + migrationBuilder.DropTable( + name: "gtfs_stops"); + + migrationBuilder.DropTable( + name: "gtfs_trips"); + + migrationBuilder.DropTable( + name: "gtfs_routes"); + + migrationBuilder.DropTable( + name: "gtfs_agencies"); + + migrationBuilder.DropTable( + name: "feeds"); + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs new file mode 100644 index 0000000..1932251 --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/Migrations/AppDbContextModelSnapshot.cs @@ -0,0 +1,544 @@ +// <auto-generated /> +using System; +using Enmarcha.Experimental.ServiceViewer.Data; +using Microsoft.EntityFrameworkCore; +using Microsoft.EntityFrameworkCore.Infrastructure; +using Microsoft.EntityFrameworkCore.Storage.ValueConversion; +using NetTopologySuite.Geometries; +using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; + +#nullable disable + +namespace Data.Migrations +{ + [DbContext(typeof(AppDbContext))] + partial class AppDbContextModelSnapshot : ModelSnapshot + { + protected override void BuildModel(ModelBuilder modelBuilder) + { +#pragma warning disable 612, 618 + modelBuilder + .HasAnnotation("ProductVersion", "10.0.1") + .HasAnnotation("Relational:MaxIdentifierLength", 63); + + NpgsqlModelBuilderExtensions.HasPostgresExtension(modelBuilder, "postgis"); + NpgsqlModelBuilderExtensions.UseIdentityByDefaultColumns(modelBuilder); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", b => + { + b.Property<int>("Id") + .ValueGeneratedOnAdd() + .HasColumnType("integer"); + + NpgsqlPropertyBuilderExtensions.UseIdentityByDefaultColumn(b.Property<int>("Id")); + + b.Property<string>("DownloadUrl") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)"); + + b.Property<string>("Etag") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property<DateTime>("InsertedAt") + .HasColumnType("timestamp with time zone"); + + b.Property<string>("LongName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.Property<string>("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)"); + + b.HasKey("Id"); + + b.ToTable("feeds"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("Email") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_email"); + + b.Property<string>("FareUrl") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_fare_url"); + + b.Property<string>("Language") + .IsRequired() + .HasMaxLength(5) + .HasColumnType("character varying(5)") + .HasColumnName("agency_lang"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_name"); + + b.Property<string>("Phone") + .HasMaxLength(30) + .HasColumnType("character varying(30)") + .HasColumnName("agency_phone"); + + b.Property<string>("Timezone") + .IsRequired() + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("agency_timezone"); + + b.Property<string>("Url") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_agencies"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<DateOnly>("EndDate") + .HasColumnType("date") + .HasColumnName("end_date"); + + b.Property<bool>("Friday") + .HasColumnType("boolean") + .HasColumnName("friday"); + + b.Property<bool>("Monday") + .HasColumnType("boolean") + .HasColumnName("monday"); + + b.Property<bool>("Saturday") + .HasColumnType("boolean") + .HasColumnName("saturday"); + + b.Property<DateOnly>("StartDate") + .HasColumnType("date") + .HasColumnName("start_date"); + + b.Property<bool>("Sunday") + .HasColumnType("boolean") + .HasColumnName("sunday"); + + b.Property<bool>("Thursday") + .HasColumnType("boolean") + .HasColumnName("thursday"); + + b.Property<bool>("Tuesday") + .HasColumnType("boolean") + .HasColumnName("tuesday"); + + b.Property<bool>("Wednesday") + .HasColumnType("boolean") + .HasColumnName("wednesday"); + + b.HasKey("ServiceId", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.Property<string>("ServiceId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property<DateTime>("Date") + .HasColumnType("timestamp with time zone") + .HasColumnName("date"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<int>("ExceptionType") + .HasColumnType("integer") + .HasColumnName("exception_type"); + + b.HasKey("ServiceId", "Date", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_calendar_dates"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.Property<string>("Id") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("AgencyId") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("agency_id"); + + b.Property<string>("Color") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_color"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_desc"); + + b.Property<string>("LongName") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_long_name"); + + b.Property<string>("ShortName") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_short_name"); + + b.Property<int>("SortOrder") + .HasColumnType("integer") + .HasColumnName("route_sort_order"); + + b.Property<string>("TextColor") + .HasMaxLength(7) + .HasColumnType("character varying(7)") + .HasColumnName("route_text_color"); + + b.Property<int>("Type") + .HasColumnType("integer") + .HasColumnName("route_type"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("route_url"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("AgencyId", "FeedId"); + + b.ToTable("gtfs_routes"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.Property<string>("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("Code") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_code"); + + b.Property<string>("Description") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_desc"); + + b.Property<string>("Name") + .IsRequired() + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_name"); + + b.Property<Point>("Position") + .HasColumnType("geometry") + .HasColumnName("stop_pos"); + + b.Property<string>("Timezone") + .HasMaxLength(50) + .HasColumnType("character varying(50)") + .HasColumnName("stop_timezone"); + + b.Property<string>("Url") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("stop_url"); + + b.Property<int>("WheelchairBoarding") + .HasColumnType("integer") + .HasColumnName("wheelchair_boarding"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.ToTable("gtfs_stops"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.Property<string>("TripId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property<int>("StopSequence") + .HasColumnType("integer") + .HasColumnName("stop_sequence"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("Arrival") + .IsRequired() + .HasColumnType("text") + .HasColumnName("arrival_time"); + + b.Property<string>("Departure") + .IsRequired() + .HasColumnType("text") + .HasColumnName("departure_time"); + + b.Property<double?>("ShapeDistTraveled") + .HasColumnType("double precision") + .HasColumnName("shape_dist_traveled"); + + b.Property<string>("StopId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("stop_id"); + + b.HasKey("TripId", "StopSequence", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("StopId", "FeedId"); + + b.HasIndex("TripId", "FeedId"); + + b.ToTable("gtfs_stop_times"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.Property<string>("Id") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("trip_id"); + + b.Property<int>("FeedId") + .HasColumnType("integer") + .HasColumnName("feed_id"); + + b.Property<string>("BlockId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("block_id"); + + b.Property<int>("DirectionId") + .HasColumnType("integer") + .HasColumnName("direction_id"); + + b.Property<string>("RouteId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("route_id"); + + b.Property<string>("ServiceId") + .IsRequired() + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("service_id"); + + b.Property<string>("ShapeId") + .HasMaxLength(32) + .HasColumnType("character varying(32)") + .HasColumnName("shape_id"); + + b.Property<int>("TripBikesAllowed") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_bikes_allowed"); + + b.Property<string>("TripHeadsign") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_headsign"); + + b.Property<string>("TripShortName") + .HasMaxLength(255) + .HasColumnType("character varying(255)") + .HasColumnName("trip_short_name"); + + b.Property<int>("TripWheelchairAccessible") + .ValueGeneratedOnAdd() + .HasColumnType("integer") + .HasDefaultValue(0) + .HasColumnName("trip_wheelchair_accessible"); + + b.HasKey("Id", "FeedId"); + + b.HasIndex("FeedId"); + + b.HasIndex("RouteId", "FeedId"); + + b.ToTable("gtfs_trips"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendar", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsCalendarDate", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsAgency", "Agency") + .WithMany() + .HasForeignKey("AgencyId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Agency"); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStopTime", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsStop", "GtfsStop") + .WithMany() + .HasForeignKey("StopId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", "GtfsTrip") + .WithMany() + .HasForeignKey("TripId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("GtfsStop"); + + b.Navigation("GtfsTrip"); + }); + + modelBuilder.Entity("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsTrip", b => + { + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.Feed", "Feed") + .WithMany() + .HasForeignKey("FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.HasOne("Enmarcha.Experimental.ServiceViewer.Data.Gtfs.GtfsRoute", "Route") + .WithMany() + .HasForeignKey("RouteId", "FeedId") + .OnDelete(DeleteBehavior.Cascade) + .IsRequired(); + + b.Navigation("Feed"); + + b.Navigation("Route"); + }); +#pragma warning restore 612, 618 + } + } +} diff --git a/src/Enmarcha.Experimental.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs b/src/Enmarcha.Experimental.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs new file mode 100644 index 0000000..5de48ce --- /dev/null +++ b/src/Enmarcha.Experimental.ServiceViewer/Data/QueryExtensions/GtfsCalendarQueryExtensions.cs @@ -0,0 +1,21 @@ +using Enmarcha.Experimental.ServiceViewer.Data.Gtfs; + +namespace Enmarcha.Experimental.ServiceViewer.Data.QueryExtensions; + +public static class GtfsCalendarQueryExtensions +{ + public static IQueryable<GtfsCalendar> WhereDayOfWeek(this IQueryable<GtfsCalendar> query, DayOfWeek dayOfWeek) + { + return dayOfWeek switch + { + DayOfWeek.Monday => query.Where(c => c.Monday), + DayOfWeek.Tuesday => query.Where(c => c.Tuesday), + DayOfWeek.Wednesday => query.Where(c => c.Wednesday), + DayOfWeek.Thursday => query.Where(c => c.Thursday), + DayOfWeek.Friday => query.Where(c => c.Friday), + DayOfWeek.Saturday => query.Where(c => c.Saturday), + DayOfWeek.Sunday => query.Where(c => c.Sunday), + _ => query + }; + } +} |