CarManager: a car management database

[Mad Ax 9325]

Here's a thing I've briefly mentioned in a few posts on here, something I've been working on for a couple of years.

Back at the end of 2017 I hired as a software developer by a company with an office in Bath, but for the first few months they didn't have any work for me to do, so I was put on the bench with a few other new starters and told: Developer, develop thyself.

And so I started on a period of self-development.  That's not to say I refactored myself from carbon-based human into digitised neural simulation via the medium of C# (I'm not sure a Dell laptop with a Core i7 is capable of simulating even my pathetic brain) - instead I spent several weeks watching Pluralsight videos and teaching myself Angular.

The result is a database for RC cars and parts.

Initially I wanted to catalogue not just every car I owned, but also all the parts fitted (like servos, receivers and motors) so I could always find things, and so I could see at-a-glance what was in a running condition if an event came up, but as I've got more into working on projects I've found I use it much more to track what jobs I've got to do on specific cars against upcoming events and what parts I need to buy to get them done.  As a result, it's been hacked and twisted and refactored multiple times and there are now parts of the code that I daren't venture into because I have no idea what I'll find there, but it has been stable enough for the past few months that I actually use it at least every weekend to see what's on my list and mark things off as completed.

Over the next few weeks I'll drip-feed some screenshots of what I'm using it for, and I'll try to get a better version uploaded to Github in case anyone wants to take a look or run their own version.

This here is the New Task view.  IP address visible so all you filthy h@xx0rs know the layout of my subnet.

[Mad Ax 9325]

New Task is a key part of the app.  I can choose a car and/or project (a project could be, for example my ramps, or my paint room, which have independent tasks that need doing like fitting carpet or wiring in new pluge sockets) or it could be an event like Revival, so I can group Revival-specific tasks on the task view.  Flags isn't visible but that's things like House, Workshop, Paint Room, Requires Purchase and Blocked.  I have some pre-defined Tasks Lists (basically just hardcoded filters for the task list view) so I can quickly see all the Workshop jobs, or all the House jobs, or all the Paint Room jobs.  That means if I've got a long day in the workshop I can find all the jobs that need the use of saws or power tools, if I want to sit in the house and have the TV on I can find all the simple nut-and-bolt jobs or decalling jobs.  If the weather's good and I want to paint something I can see all the Paint jobs.  Tasks that are Blocked or Requires Purchase don't appear on the pre-set tasks lists, so the list doesn't get cluttered with things I can't actually do right now.  And of course if I've got something like Revival on the horizon, I can pull up all the Revival jobs to see what I still have to do before the big day.

There's a very similar list for purchases too - I can list all the parts I need so when a bit of cash comes in I can sort them by required date and buy the parts I'll need sooner before I spend money on projects than can wait.

[Mad Ax 9325]

This is the Cars view with the car edit page open, showing the Collections checkboxes.  This was an idea I had years ago and the app has allowed me to make it real.  Essentially, every car belongs to one or more collections.  You can see here that the FF01 Accord goes in FWD, Honda, JDM and Touring Cars.  I could probably also put it in a Motorsport collection (any car that has a motorsport livery, real or fake) or White (for cars with mostly white liveries).  The limit is the imagination.  Here you can see all my other current collections too, and the option to add new collections while I'm adding or editing a car.

See also Displayable and Runnable flags - so I can quickly find all the cars that are capable of running, or at least are complete enough to go on display.

[Mad Ax 9325]

Now here is where the interesting bit comes in.  This is the Active Displays list, complete with the Suggested Display view underneath.  As you can see, I currently have Scale Trucks collection on active display - that means those cars have been selected to go on the display shelves.

Display Details is where it gets interesting.  The code will select a random Collection which is not currently on display that has a minimum of 3 Displayable cars.  I can then choose from the list which of those cars I actually want to put on display.  My current display area has 5 rows of 4 columns, so it isn't practical to display 5 cars from one collection (at least not if I display them longthways).

In this case, this time I chose not to display the Celica as part of the JDM collection.

[Mad Ax 9325]

As it was the first weekend of the month, it was time for a full refresh of my display, so I kept spinning the wheel until I'd filled up all my display space.

As you can see here, there are some duplicates.  At first I thought this was a bug, but then I remember it was actually a feature - perhaps it should be an option to allow or disallow duplicates (i.e. if a car is already on display, do not count it as a displayable car in another collection and therefore exclude collections with less than 3 cars which are displayable and not actually on display already).  The reason this is a feature is because my old display area had one very long shelf which could fit around 12 touring cars side-by-side, nose-on to the front.  I might, for example, spin JDM and Motorsport together - then I could put all the JDM cars at one end slanted one way, and all the Motorsport-liveried cars at the other slanted the other way, and if I had a car which is both JDM and motorsport, that could go in the middle, straight-on to the viewer.  Or, if I had a Yellow collection and a Black collection, and I had a yellow-and-black car that was in both collections, I could put all the yellow cars on one side and all the black cars on the other side and the black and yellow car in the middle.

Anyway - this is how the wheel span for June's display.  I now have so many shelves that unless I buy more cars, I predict my display will look pretty much the same every month.  Also note empty shelf because I either don't have enough vintage buggies in displayable condition, or I don't have enough 2wd buggies in displayable condition.

 

[Mad Ax 9325]

With all that done, I was able to pull out all the boxes for all these cars and get them up on the shelves.

[yogi-bear 2321]

that looks awesome, and I'll definitely be checking it out when you upload it to GitHub/   I've been looking for something like this and I'm not too keen on coding my own. I'm currently using a very basic excel spreadsheet. and its very limited. I would imagine there are a number of others in the same boat.

One thing I would like/try to add, is pics, so you can have a record of the build process. But that adds complexity and database space!

[RichieRich 685]

This is fantastic. As you may or not know, I've recently moved. As I've been unpacking, I've been cataloging everything in a few Google docs. Cars, servos, ESCs, radios with the intent of doing something like what you are. I'd like to mess around with this too!

[Mad Ax 9325]

9 hours ago, yogi-bear said:

One thing I would like/try to add, is pics, so you can have a record of the build process. But that adds complexity and database space!

I considered adding pics, but then I remembered I wasn't trying to replicate Tamiyaclub.  I already have an online showroom for adding my best pics too, plus TCPhotos for storing and documenting build threads.

I did add a "notes" page so I can jot down things I want to remember, but I had no intentions of it becoming an online repository of all my photos.  Partly because I use TC for that and partly because the hosting costs would get huge.  Currently this is hosted under Visual Studio 2019's own web host, with a batch file that exposes it to the local network so I can access it from my phone and tablet while I'm working.  My long-term plan is to host it locally on a Raspberry Pi so I don't have to start the IDE and put my laptop into Always On mode (it's so annoying when I'm in the workshop with fingers covered in grease and trying to add a new Purchase with my mucky old tablet and the laptop has gone into hibernate so it all stops working).

All my photos are cataloged on my hard drive under RadioControl\Projects\[projectname]\[date-and-description] so it's dead easy for me to find all the photos belonging to one particular car.  And about a decade ago I build a photo catalog application which allows me to add all my photos, group them into albums and tag them so for example I could easily find all the Action shots or all the FF01 shots.  But I never actually bothered to load my RC photos in there because it's cumbersome and buggy and needs a complete overhaul, which I might have time for sometime in 2027, if I don't buy and more cars, do any more races or have any more children :p  

However the Car table does have columns for BuildThreadUrl and ShowroomUrl, so it's easy to link a car to its online presence.  I was thinking of adding a new column for TcPhotosUrl (every car has its own album on tcphotos) and ProjectFolder, which links to the folder with all the photos (plus other files like PDF manual, custom decal files and anything else I see fit to put in there) - basically just to save me time every time I want to update my build threads.

That said - once I've got the latest version up in Github, you're pretty much free to do as you please with it and submit PRs if you think you've got something worth going into the master branch.  As long as any new features are optional and don't force the user down a given path, it's all good  

[acprc 1443]

Looks great and I run a few speadsheets myself to keep track of things like Engines, engine components, Tamiya nitro kits etc. Would like to add my collection but I shudder a bit at the scale of the task! 

[yogi-bear 2321]

20 hours ago, Mad Ax said:

I considered adding pics, but then I remembered I wasn't trying to replicate Tamiyaclub.  I already have an online showroom for adding my best pics too, plus TCPhotos for storing and documenting build threads.

that's a good point. I am personally thinking more along the lines of cataloguing a project, which I just is just the same as a build thread, but without the comments.

20 hours ago, Mad Ax said:

I did add a "notes" page so I can jot down things I want to remember, but I had no intentions of it becoming an online repository of all my photos.  Partly because I use TC for that and partly because the hosting costs would get huge.  Currently this is hosted under Visual Studio 2019's own web host, with a batch file that exposes it to the local network so I can access it from my phone and tablet while I'm working.  My long-term plan is to host it locally on a Raspberry Pi so I don't have to start the IDE and put my laptop into Always On mode (it's so annoying when I'm in the workshop with fingers covered in grease and trying to add a new Purchase with my mucky old tablet and the laptop has gone into hibernate so it all stops working).

the notes pages sounds like a top idea. I hadn't thought about access from other devices, that's not a bad idea either!

20 hours ago, Mad Ax said:

All my photos are cataloged on my hard drive under RadioControl\Projects\[projectname]\[date-and-description] so it's dead easy for me to find all the photos belonging to one particular car. 

yeah, I do something very similar, and for me, the idea of using photos just gives me a quick visual on what car I am looking at as I have a lot of duplicates. Plus I'd like to visually catalogue what upgrades I do etc. They could almost just be thumbnails. I have to be careful though and not put too much into it, it'll quickly become bloated.

20 hours ago, Mad Ax said:

And about a decade ago I build a photo catalog application which allows me to add all my photos, group them into albums and tag them so for example I could easily find all the Action shots or all the FF01 shots.  But I never actually bothered to load my RC photos in there because it's cumbersome and buggy and needs a complete overhaul, which I might have time for sometime in 2027, if I don't buy and more cars, do any more races or have any more children :p  

Impressive!

20 hours ago, Mad Ax said:

However the Car table does have columns for BuildThreadUrl and ShowroomUrl, so it's easy to link a car to its online presence.  I was thinking of adding a new column for TcPhotosUrl (every car has its own album on tcphotos) and ProjectFolder, which links to the folder with all the photos (plus other files like PDF manual, custom decal files and anything else I see fit to put in there) - basically just to save me time every time I want to update my build threads.

that sounds very handy too.

20 hours ago, Mad Ax said:

That said - once I've got the latest version up in Github, you're pretty much free to do as you please with it and submit PRs if you think you've got something worth going into the master branch.  As long as any new features are optional and don't force the user down a given path, it's all good  

I'm super keen to have a play, but for when I can get to it, who knows  . Agreed too, features should be optional, hopefully I can add something to it that is worthy of including. What did language did you build it with?

[Mad Ax 9325]

It started life with an older SPA template for dotnet core.  I think the API is now in dotnet core 2.1 and written in c#, the UI is an older version of Angular.  I've never bothered to upgrade it since I really don't need to.

The database is Sqlite and I use Dapper and DapperExtensions for CRUD.  I think I'm using an older (pre-core) version of the Sqlite driver and Dapper, so I get some warnings about versions not being entirely compatible.  Recently I refactored one of my other little tools (which I call DapperWrapper and puts a handy generic repository layer on top of DapperExtensions) to use dotnet core 3 and core-happy versions of Sqlite and Dapper, it was more work than I wanted it to be as there were some syntax changes (including a completely different vendor for DapperExtensions, IIRC, as the original repository has never been updated for dotnet core) but gives me a good starting point for getting CarManager into a tidier state.

I'd also be happy with more unit testing - these days I do virtually everything TDD but back then I was less confident with the overall design and started hacking code in just to prove the concept, and of course it escalated from there so now there are a few untestable methods and some dodgy areas that need attention.

The one thing that's really bugging me is the DB - I never bothered to keep change scripts so I'll have to generate an all new DB creation script for the next commit.  I'd like to use something like AliaSQL to keep everyone updated with DB changes but I'm not sure if it works with Sqlite or how easy it would be to change (I haven't tested it yet).

The entire thing is so generic that it could probably be grossly simplified anyway.  I've got a couple of days off work coming up so I might put a few hours into making it tidy and posting the latest update on Github.

[yogi-bear 2321]

hmm, looks like I am going to learn some dot net! and Maybe sqlite, which looks interesting, an embedded database in a file! that would be handy. I am more knowledgeable with php, mysql etc, so in the long run, with your permission, may end up rewriting it for as a plugin for Wordpress. Mind you, with the amount of free time I have, that will take me ages to get to anyway! But I get ahead of myself, once you've uploaded, I'm keen to check it out.

[Cynan 520]

On 6/11/2021 at 8:50 AM, Mad Ax said:

The one thing that's really bugging me is the DB - I never bothered to keep change scripts so I'll have to generate an all new DB creation script for the next commit.  I'd like to use something like AliaSQL to keep everyone updated with DB changes but I'm not sure if it works with Sqlite or how easy it would be to change (I haven't tested it yet).

Couple of years ago I came across JPA (I'm more in the Java world than .NET at the moment) which got rid of the need for the creation & change scripts completely. You annotate your entity classes and then JPA creates & updates the database for you upon start up.

There must be something similar in .NET you could potentially use.

EDIT: So this might do the job if you wanted Code First with Entity Framework 6

[Mad Ax 9325]

22 hours ago, Cynan said:

So this might do the job if you wanted Code First with Entity Framework 6

Yes, I have used Code First before and it's very good at what it does, especially for a project of this size.  I'm not sure if it works for Sqlite, as there are some fundamental differences, although there is a single-file version of MS-SQL that does pretty much what Sqlite does, so maybe that would work.  The downside would mean me moving to Entity Framework - not that I have a problem with it in general (I've used it a lot in enterprise solutions and it would speed up development) but it would also mean refactoring all the repositories to use EF.  Even if I just refactored to use EF Codefirst for DB changes and DapperExtensions for CRUD, I'd still have to refactor many of the scripts because Sqlite has its own dialect.

If I was starting from scratch today then I probably would use the MS file-only SQL DB and EF Codefirst, I only went down this path because it's evolved from much older solutions when either the MS-SQL file-only version didn't exist, or I didn't know about it.  For years I was developing my own solutions with SQLExpress, which is s horrible resource hog on a laptop used for music production, and maximum overkill for what I do at home.

[Mad Ax 9325]

Just thought I'd add an update here, as there hasn't been one for ages, and I've been doing a lot of work on this recently.  Most of this post will be about techie stuff, not about CarManager features or usage, so if you're not into software dev or IoT then this may not interest you.  Also no pics or screenshots to share yet.  You have been warned!

So - my long-term plan for CarManager was to have it hosted somewhere where I could always get at it, at least while I'm at home.  Although the idea of having it hosted on a publicly-available webserver is neat (like, I could access it via my mobile when I'm trackside), it also opens it up to outside attack and means I have to start thinking about it in terms of a secure multi-user app.  That's something I never implemented when I started the app, and something that would require a fair bit of work to implement now.

The alternative is to have it always-on at home.  Up until February, if I wanted to use CarManager at home, I had to fire up my laptop, open Visual Studio, debug the app, run a console command to make the visual studio webserver available on the network, then change the laptop power settings so it didn't hibernate.  Only then could I access CarManager from my mobile or tablet or another computer at home.  While that was OK-ish for Workshop Sunday, it's a faff and a fiddle for any other day, especially if I happen to be at my desk in the house or chilling on the sofa and I want to add a task or check something, and I have to walk up the garden to the studio to fire up the laptop, start visual studio and build the app in dev mode.  By that time, it's just as quick to do whatever I want to do directly on the laptop, shut it down, and go back inside again.  Then remember I wanted to add something else, repeat ad nauseum.

The solution to this tricky problem is to host the app on a small, inexpensive web server.  It doesn't need much power or storage space.  At first I considered using an old laptop, but I figured the hassle of keeping an old version of Windows updated and running with later versions of .Net wasn't worth the bother, and even the running costs would be a concern for something that will probably not get used that much.

Back in January I was looking at the future of my career and wondering if a switch to embedded devices would add a new challenge, and during an IoT conference hosted by Microsoft there was quite a lot of discussion about Raspberry Pi.  Now strictly a Pi is overkill for a typical embedded device unless you need a lot of computational power, and don't really count for much in terms of embedded device programming, but they are cheap, and powerful enough to run a .Net web server.  And they're fully customisable and very versatile.

With a bit of cash spare in February, I decided a Raspberry Pi would be a useful addition to my arsenal of retraining tools, and as a bonus, I could use it to host CarManager.  With money spent and hardware delivered, I got to work building CarManager for deployment on linux-arm.

And that's where the problems started.

[Mad Ax 9325]

I mentioned this in a few other random threads, but I'll review it here for anyone specifically interested in following this project.

I had originally started this app using an old .Net SPA template, back at the end of 2017, and since then it's had minimal upgrades.  It uses Angular for the front-end, and Angular is one of those frameworks that gets big updates very often, and if an app isn't kept updated, it quickly falls into a hole from which it's hard to recover.  I didn't know much about Angular back then, and I was installing lots of different components such as modal forms, pagination and date pickers.  I remember having lots of trouble with components that I couldn't get to work as advertised, and for a very long time the app had no date picker at all.  Finally getting it working with my-date-picker was a huge milestone.

my-date-picker would come back to bite me when I tried to build the project for linux-arm.  I was getting tons of errors about version incompatibility.  The frustrating part was not that components were incompatible with my older version of Angular, but that they worked just fine in dev mode.  So begin several arduous weeks as I grappled with upgrading Angular, upgrading .Net, upgrading components and other libraries, each time getting an even longer list of errors and build failures until I literally ran into an Ax.InsufficientTeddiesException while pram.Throw(teddies.Next());

Unable to get CarManager to upgrade, I diverted my attention to at least get the Pi hosting something.  Something being a really simple .Net / Angular SPA demo app which displays dummy weather data.  After following some tutorials online, was at least able to host this on the Pi and configure nginx so I could access it from my laptop.  As a bonus, I updated the demo app to read from a dummy Sqlite database and had that working too.  So, at least the basic principle was proved - I can host a .Net / Angular web app on a Pi, read from a Sqlite database, and access it from other devices on the network.

Principle proved, I felt more confident moving back to my CarManager woes.  This time, I had a functional .Net / Angular app.  So, what if I copied the code from my old, version-mismatched CarManager repo, into the new demo app, controller by controller, component by component?

[Mad Ax 9325]

And that's pretty much what I've been doing for the last few weeks.  If I've got a bit of spare time here or there, during long meetings or waiting for test runs to finish, I copy over another component from my old codebase into the new one and mash the keyboard until it works.  I've introduced a few new bugs, which is to be expected with such a major upgrade (from Angular 4 to Angular 12, .Net Core 1 to .Net 6) but I've got some other stuff working that I always struggled with before - async data access and date pickers, to name the two critical ones.  Overall the codebase is tidier too.

Actually getting it to work on the Pi proved challenging, because for a time it just wouldn't work and wouldn't tell me why, but earlier this week there was a major breakthrough - it looks like I wasn't running the linux-arm builds properly and/or wasn't copying the right files over to the Pi, so it was able to build and launch but couldn't serve anything.  After rebuilding again from inside Visual Studio and emptying all the folders on the Pi, it all suddenly sprang into life.  Hitting Refresh for the 90th time and expecting to see yet another 404, it was a surprise and joy to see the loading... form and then, with sheer delight, a list of tasks that I entered at the end of last summer and haven't touched since.

There's still tonnes of work to do, loads of components still to convert, some new bugs to resolve (particularly a couple that are corrupting data that need to be fixed before I start using the app on the live database) and some old bugs that really should be fixed,  some styling that needs to be sorted since upgrading to bootstrap 5, some things to learn about using HTTPS across a home network, and possibly even some hacking of my router to make the Pi visible under a nice domain name instead of an IP address, but there is a light at the end of the tunnel - the app builds and runs on the Pi!

All this is great news for anyone who wants to collaborate, too.  Collaborating on an unsupported version of .Net with an ancient version of Angular was never going to be fun for anyone, but now everything is current and all the libraries are supported.  Once I've merged the upgrade branch back in to master, I'll make the repository available on GitHub!

Stay tuned for further updates.

[yogi-bear 2321]

glad to see you are still working on this!

[Mad Ax 9325]

SUCCESS!!

It's a pretty dodgy photo, but this is CarManager, showing me my project list on the laptop screen, and my car list on the Android tablet.  Running on a Raspberry Pi 4, using .Net 6 and Angular 12, and sitting behind an nginx proxy.

There's still a few things to fix, like implementing HTTPS (which is totally unnecessary for a standalone system with no password protection on a home network, but modern browsers don't like unsecure sites) and properly documenting my installation and configuration process for the readme files, and there's hundreds of small but annoying bugs that I could fix (including one or two that are causing data corruption and which really should take priority) but the main thing is, it works!

I can even access it from my works laptop, as long as I'm on the same wifi network as the Pi.  There's probably a way to access it from the other two wireless networks in the house, but I expect that would involve doing something DNS-related on the routers and I'm not sure it's worth the effort.

So, this week I've been mostly pruning off some really old ToDos, adding a few new cars and updating all the stuff I need to buy to finish my projects.

The drawback of all this is I can now see, clearly, in one place, how much it's going to cost to finish all these cars :facepalm: 

[yogi-bear 2321]

exciting to see this moving forward. More exciting is I never realised that the 240z also came as a wagon!

[Mad Ax 9325]

On 7/3/2022 at 9:41 PM, yogi-bear said:

More exciting is I never realised that the 240z also came as a wagon!

It doesn't...  yet  just wait until I do a cut-and-shut with the Volvo 850 body

Also this is probably a British-ism (or maybe even a Westcountry-ism) - wagon can have the non-specific meaning of "car", as well as its more specific body-style term.  Not sure if you use the term "estate" on your side of the planet?  That's what we call wagons, unless some manufacturer's marketing department starts getting all posh and calling it a shooting brake.

[AshRC 213]

That's awesome. Couple of suggestions from me ... as well as "Build Thread URL" and "Showroom URL" I would add a "Photo URL" field with the URL of your favourite photo (on TamiyaClub or elsewhere). Saves you hosting the image yourself, and you could then use that image in the Active Displays page, to mock-up how the shelves will look.

Secondly, I would use migration files for the DB changes. Anyone can then commit changes and you'll keep up-to-date.

[Mad Ax 9325]

On 7/8/2022 at 12:17 PM, AshRC said:

That's awesome. Couple of suggestions from me ... as well as "Build Thread URL" and "Showroom URL" I would add a "Photo URL" field with the URL of your favourite photo (on TamiyaClub or elsewhere). Saves you hosting the image yourself, and you could then use that image in the Active Displays page, to mock-up how the shelves will look.

Secondly, I would use migration files for the DB changes. Anyone can then commit changes and you'll keep up-to-date.

Neat idea on the photo URL!  Should be fairly quick to add that.  Although I've just been moved onto an all-new team at work and our deadlines are tight, I have a feeling this is how it's going to roll from now on, so I'll get less time to work on side-projects like this.

On the plus-side, last week I managed to fix a whole load of little bugs that had been bothering me since the first install, and also added the FluentMigrator library to keep DB changes up to date automatically.  That's less important when developing locally but it's a real pain to access and update the DB manually when deploying an update to the Pi.  I use Sqlite because it's so incredibly lightweight, but FluentMigrator doesn't properly support Sqlite with its native fluent syntax, so I'm just using plain old SQL scripts for the migrations, stored inside a forward-only migration file.  So anybody will be able to commit DB updates, but I'll have to be careful about DB conflicts when merging PRs.

If I get any time off later this year I'll get the latest big update merged back into the Master branch and open it up for collaborators.

[yogi-bear 2321]

On 7/7/2022 at 11:48 PM, Mad Ax said:

It doesn't...  yet  just wait until I do a cut-and-shut with the Volvo 850 body

Also this is probably a British-ism (or maybe even a Westcountry-ism) - wagon can have the non-specific meaning of "car", as well as its more specific body-style term.  Not sure if you use the term "estate" on your side of the planet?  That's what we call wagons, unless some manufacturer's marketing department starts getting all posh and calling it a shooting brake.

station wagon is the general term here, although they are less popular now (and Ford and Holden no longer make them). SUVs seem to be taking over these days.  And it took an episode of Top Gear several years ago to see that an Estate is just the Station Wagon equivalent.

I've never seen a 1:1 Datsun 240z station wagon either! I know with some brands, like Subaru, we don't get all the models down under, too small a market I guess. Good luck with the project though, there doesn't seem to be a whole lot of estate bodies to choose from as a donor.

@AshRC - top idea on the photo links too!