π₯³ Cruddiy now supports table relationships via foreign keys. π₯³
This means:
You can add new or delete existing table relations by picking columns that have a foreign key relation.
You can add specific actions for each table relation, like:
ON UPDATE : CASCADE / SET NULL / RESTRICT
ON DELETE: CASCADE / SET NULL / RESTRICT
Picking specific actions will result in different behavior. Please read up what these mean (mostly you want ON UPDATE CASCADE and ON DELETE CASCADE).
Having table relations in place wil have the following results for Cruddiy:
On the Create form the field will be populated with the correct key information.
On the Update form: the correct stored value will be preselected but you can still choose (update) different values from the select list.
Note 1: the table relationship builder is the first step in the Cruddiy CRUD creation process. However it is completely safe to ignore this step and move to the next step! I would even strongly advise doing so if you are not sure what you need to do, because it might break things. Also if you just want one or a couple of simple unrelated forms it is perfectly safe to skip this step.
Note 2: the table relationship builder is of course just a GUI for something you can also do in PHPMyAdmin or MySQL from the commandline. However, Cruddiy makes it visible and easier to work with table relations.
Some screenshots:
Table relation builder. One existing relation. And a couple of dropdown lists to create new ones. Feel free to skip this step by hitting ‘Continue CRUD Creation Process’.In the column form columns with foreign keys are indicated by the blue icon.A simple two field table. As you can see the second item is preselected (as it should be). However it is still possible to select a different value.
1. Encourage the development of (something, especially something desirable). “the teacher’s task is to foster learning”
TLDR: I made a personal bookshelf management web application and named it Foster and you can find it here. Here’s what I did — with gifs–, so you might build your own.
Name
I named it Foster. Because of *this* blog post — it accompanies the application, so it’s self-referential. And also, because I am currently reading David Foster Wallace‘s magnum opus Infinite Jest. And lastly, the word ‘foster’ makes a lot of sense otherwise, just read on π
Background
I like to read and I like to buy physical books — and keep them. Over the years I tracked both of these things in a spreadsheet. But this became unmanageable so I needed something else.
Something like Goodreads but self-hosted. So, preferably a web application where I could:
track my reading progress
keep track of my bookshelf
But I couldn’t find anything that fit, so I knew I probably had to roll my own. In simpler times MS Access could do this sort of thing in a heartbeat. But it’s 2019 and I wanted a web application. However I am not a web developer and certainly not a frontend developer.
But when I came across https://books.hansdezwart.nl/ I knew this was what I was looking for! So I emailed Hans. He was very kind in explaining his application was self-coded and not open-source, but he did provide some pointers. Thanks Hans! So with those tips I built my own application (front and back) from scratch. And I decided to pass the knowledge on, with this blog.
The Foster fronted (I am still adding books)
This is what the Foster frontend looks like. It’s pretty self-explanatory: I can search *my* books, track and see reading progress, track collections, click through to book details and see the activity feed (more on that later). Oh, and it’s fast! β₯
Frontend
The five different parts in the frontend are: ‘Search’, ‘Statistics’, ‘Currently reading’, ‘Collections’ and ‘Activity feed’. They are presented as Bootstrap cards. The frontend is just one index.php file with a layout of the cards. All cards (except ‘Search’) are dynamically filled with content expressed as a div class. The class content is generated by one JavaScript function per card, which in turn call a PHP file. And the PHP files just echo raw HTML.
Other than the index.php file there is one search.php file to make up the frontend. This file takes care of presenting the book details, search output, log and lists views (more on that later). So, most of what can be done and seen in the frontend is handled by the search.php file.
The frontend itself is of course nothing unique. It’s just a representation of the data. The backend is a bit more interesting!
Database
The frontend was the easy part. At least it was after I figured out the backend! I spent quite a bit of time thinking about the database design and what the backend would have to do. I thought the design for such a small application wouldn’t be too difficult. But I surprised myself with the number of changes I made to the design, to get it just right. And I wanted to get it right because:
General rule of thumb: when you start with a good design, everything else that comes after will be a lot easier.
Self-explanatory view of the database design
The multiple foreign-key relations between tables (on ids etc.) are not defined in the database. I choose to do this in the code and the JOIN queries.
It’s not hard to understand the database design. And yes, the design could be a little tighter — two or three tables — but let me explain!
Log, actions and states
One of the main things I spent time thinking about are the actions and their respective states.
I figured you can do one of five things with a book (actions):
You want a book
You get/buy/own the book
You start reading it
You finish reading it
You purge/remove/sell/give away the book
Makes sense right? You could even call it the ‘book life cycleproces‘. With one input and one output.
HOWEVER! Some books you already own without wanting them first. Or, you can read the same book more than once. Or, you can give a book to a friend, and buy it again for yourself. Or, you can finish reading a book, that you lent — from a friend or library — so it is not on your shelf anymore. All of these things happen. So actually the ‘life cycle’ actions are not a chronological fixed start-to-end tollgate process, it’s continuous and messy.
Book log
Every new action is added to the book log. In the frontend the last 25 entries to the book log are presented as the Activity feed. Every action has a timestamp when an action got logged and a date for that action. Which are two different things. So when I now add a book to my shelf that I acquired 4 years ago, the book log timestamp is now, but the date for the action is 4 years ago.
The Activity feed
With this log I can keep track of books even after I got rid of them (because selling/purging is just one of the action for a book). This is important because this way I don’t lose the log history of a book.
Also I can add books to my wanted list even if I have owned them before (maybe I gave them away etc.). And I can start/finish reading the same book more than once. It doesn’t matter, because it is just a log entry.
Now here’s the interesting thing. With all this log information I can generate four states:
Books I want
Books I own
Books I have read
Books I had
These states are generated by specific hardcoded queries per state. They are generated on the fly by what is in the log file, and where the most recent log records prevail to decide the current status.
And with all this:
Foster will track the complete history per book andat all times represent all books I want, own, have read or have owned, at that specific moment in time.
Lists
I could have defined these actions as a list: but lists are simpler. Let me explain.
I tend to collect and read specific genres of books, e.g. music, management and computer history books. So I tend to organize books like that. These descriptions/genres are all, of course, just lists.
Some books can be three of these things at the same time: part biography, part computer history part management. So one book can be a member of more than one list.
In the Foster backend I can add or delete books to and from as many lists as I like.
Easily adding/deleting books from a list with the same dropdown menu (click for a gif)
I can also easily create new lists. Say: a list of books that I want for my birthday, or books that are on loan, or books that are signed by the author etc. I just add one new list to my table, and the list will be instantly available in the backend and presented in the frontend.
Collections
In the frontend the action logstates and the different lists are grouped together under the Collections card. As stated the first 4 collections are populated from the log, and a book always has a last state. The others are just lists.
I can create or delete as many lists as I’d like, and it won’t affect the book log. This way I can organize my book collection far better than I could physically (a book can only have one spot on your physical shelf).
Adding books with the Bol.com API
This is where the magic happens! Bol.com — a large Dutch book retailer — has a very easy API you can use to query their book database. I use it to search and add books to my collection. With one click I can get most book details: title, ISBN (=EAN), image, description etc. And I can pull them all into my own database. Including the image, which I then copy and store locally. Like this:
Adding a book via bol.com API (click for a gif)
Of course I can also edit book details when necessary, or just enter a book by hand without the API. Sometimes Bol.com does not carry a book.
Backend
The bol.com search API is the start page of my backend. The other important backend page is an overview of all my books. Clicking on the titles brings up an edit view of a book. But most importantly I can quickly add or delete books from lists here AND add actions (started reading, finished).
I have defined jQuery actions on the <select option> dropdown menus, which provide a popup — where I can fill in a date if necessary — and which trigger database inserts (there definitely might be some security concerns here: but the backend is not public).
Security
The frontend is open for everyone to see. I don’t mind sharing (my podcast list is also public), also because I always enjoy reading other peoples lists or recommendations. The backend is just one .htaccess password protected directory. In my first database design I had a user table with accounts/passwords etc. But the .htaccess file seemed like the easiest/quicker solution for now.
Tech
I built Foster from scratch, no Symphony/Laravel or what have you. And I am a bit annoyed surprised there is still no MS Access RAD equivalent for the web in 2019 (i.e. a all in one tool: from DB design to logic to GUI design to runtime).
I know Django does most of the backend for you , so I briefly looked at it. But for Foster I still ended up using PHP / MariaDB / Bootstrap 4 / JavaScript / jQuery. It’s a familiar and very portable stack that you can mostly just drop and run anywhere (and most answers are on StackOverflow π€).
I’ve thought about using SQLite, but I am very familiar with MySQL/MariaDB so that made more sense. Also I learned more about Bootstrap than I actually cared about, but that’s alright. And I wrote my first serious piece of JavaScript code ever (for the dropdown select actions). So that was fun.
All in all: I spent a few days pondering the database design in the back of my mind. And 4 evenings programming front and backend. And now I am just polishing little things: which is a lot of fun!
Further development
Right now, I still have around 200 more books from my library to catalogue correctly — that’s why some dates are set to 0000-00-00. But here are a few possible new features I am thinking about:
RSS feed for the activity log? Now that I am bulk adding books the activity feed is not so relevant, but when things settle down, who knows, people might be interested. After I wrote a draft of this blog I implemented it!
Twitter integration? Posting the log to a dedicated Twitter feed.
Adding books by scanning the barcode / ISBN with your phone camera? If I can just get the ISBN I can automate bol.com API to do the rest. Might speed things up a bit (and might be useful when you run a secondhand bookstore π).I created an iOS shortcut that does exactly this! It scans the book barcode/ISBN/EAN and opens the Foster edit.php URL with this ISBN number and from there I can add the book by clicking ‘Add’ (all book details are available and prefilled by the Bol.com API). It’s great!
Storing/tracking more than books? CDs, DVDs, podcasts I listened too, movies I watched etc.
Multi-user? In the first database design there were multiple users that could access / add the books that were already in the database but still create their own log and lists. I think I could still add this to the current design.
As you can see in the database design, there is a remarks table. I haven’t used this table. A remark is a ‘blog’ (or a short self-written review) of a book, that can be presented with the book details. This is a one-to-many relationship, because you might want to make new remarks each time you reread a book. But, I currently blog about every book I read, so the remarks might be just an embedded blog link?
Just share the source already!
“Foster looks nice. Please share the code!” No, sorry, for several reasons.
I made Foster specifically for me. So chances that it will fit your needs are slim and you would probably still need to make changes. In this post I share my reasoning, but you should definitely try to build your own thing!
When Foster was almost done, I learned about prepared statements (did I mention I am not a web developer?)… so I had to redo the frontend. But I haven’t redone the backend (yet): so it’s not safe from SQL injections or other pretty bad coding standards. Open sourcing it can of course generate code improvements, but it would first make my site vulnerable.
But most importantly: Building a web application to scratch your own personal itch and learning new things can be one of the most fun and rewarding experiences you will have! And I hope this blog is useful to you, in achieving that goal.
Last weekend I built a personal ToDo app. Partly as an excuse to mess around a bit with all this ‘new and hip’ Web 2.0 technology (jQuery and Bootstrap) π
But mostly because I needed one, and I couldn’t find a decent one.
Yes, I designed the icon myself. Can you tell?
Decent?
Decent in my opinion would be:
Self hosted
Self contained
Use a plain text file
Mobile friendly
Able to track / see DONE items
And Gid does just that (and nothing more).
Any PHP enabled webserver will do.
No need for third party tools, everything you need is right here (Bootstrap and jQuery are included).
No database setup or connection is necessary. Gid writes to plain text files that can be moved and edited by hand if needed (like todotxt.org).
Works and looks decent on a smartphone.
The DONE items are still visible with a strike through.
I had fun, learned quite a few new things (web development is not my day job) and me and my wife now share our grocery list with this app!
The biggest headache was getting iOS to consistently and correctly handle input form submit events. Being a touch device, this is somehow still a thing in 2019. Thanks Stack Overflow! Anyway, this is what it looks like on my iPhone.
Web development
This was mainly an interesting exercise to try to understand how PHP, Javascript/jQuery and Bootstrap work together on a rather basic level and how with Ajax you are able to manipulate the DOM. I deliberately used an older tech stack, thinking a lot of problems would be solved, however (as explained) some things still seem to be a thing. Also, what I was trying to do is just very, very basic and still I feel somehow this should be way easier! There are a lot of different technologies involved that each have their own specifics and that all have to work together.
