bboard

Posted by theLibrarian

If this is the most crap idea you've ever heard

Posted by Screwtape
Honestly, if I just wanted to keep records for myself, not stick them up online or make them available to non-technical people, I'd probably just use my database's standard command-line front-end and write the SQL manually, Maybe pgcli or a GUI admin tool. Making a nice UI is a *lot* of hard work.

Posted by wertigon
I would probably go with Python, SQL and Django on a Raspberry Pi or similar SoC computer, which is then banned from access from communicating with anything other than your laptop. Do regular backups of (relevant parts of) the SD Card every so often, and you're golden.

Oh, and I probably should mention as terrible as PHP is, it has slowly improved over the years, PHP7 is almost decent.

Happy hacking!

Posted by tomman
As for Python, it's on my "would be nice to learn someday" list, so I may give it a try. Will definitely check Django, although from a first look at, looks kinda enterprisey (like J2EE, which I'm trying to avoid). How comfortable is it as an ORM? Any general opinions about it from people that have developed applications with it?

Any other beginner-friendly options I could consider? Really, my experience with Python is pretty much nil, and that's something I really have to address...

Posted by tomman
I do need to carry some basic information about any specific design variation ever issued for a particular series, what I do have, what I'm missing, and for those pieces I have, in which conditions -Uncirculated/Very Fine/Good/Fair/whatever-, and any extra attributes like fancy serials and the like). As for the Touhou collection, it isn't simply matter of recording, say, what releases of a particular circle I do have, but also carry basic information about each circle, and since most (if not all) releases are made for specific events, I do need to store that information too - it would be very useful to know, for example, which DVDs came out for any random Summer Comiket.

Now you get why an application WITH a real database is needed: most people just resort to Excel (or for the FOSS purists, LO Calc), but a simple spreadsheet won't cut it this time - it would become a mess in no time, plus I HATE spreadsheets for anyting beyond quick calculations or scratchpad uses. Coming up with the database schemas is the easy part - I've already settled on using PostgreSQL for the task: it's simple, robust, is compatible with all major platforms, I've been using it for over a decade, and It Just Works™, plus I already have some ideas in my mind about the schemas design. The hard part is settling on a platform for the frontend.

Posted by tomman
I thought PDO (which I've used in the past) was simply a better way to connect to databases and run queries while being able to catch errors as exceptions (seriously - I remember last time I used PDO was to replace someone else code which told nothing when things blew up - with PDO not only it was much easier to do stuff, I was able to actually know what went wrong). In fact, after reviewing the documentation, I see no ORM capabilities whatsoever.

SELECT
    car.model, car.year, car.license_plate, car.person, person.name
FROM
    car
  INNER JOIN
    person ON car.person=person.id

Posted by tomman

Progress on this has been very slow, due to constant power and Internet access outages in the last weeks over here (seriously, it's no fun to code with a hand on the keyboard and the other on the power button) - so far all I have is the schema for the Touhou stuff.

Posted by wertigon
Coincidentally, my dream database API interface is one which allows you to select external fields from the database using joins. Let's say I have two tables, car, and person. I would like to list the car model, year, license plate and the name of the owner. With SQL, I'd do something like

SELECT
    car.model, car.year, car.license_plate, car.person, person.name
FROM
    car
  INNER JOIN
    person ON car.person=person.id

With an ORM, I'd have to do two queries, one for car, and one for people, and then do the joining myself. It just feels so inefficient when you have a couple of fields and doesn't really care about the rest. But that's just me dreaming... :)

Posted by tomman

On JPA you have JPQL which is more or less the same as SQL, and actually that's what I use for most queries. But as soon as you need dynamic queries (say, I may or may not need cars filtered by year, while filtering out by license plate prefixes all the time, unless if I want only a list of all cars driven by Lil' Bobby Tables, on which case I don't care about specifics), it's better to switch to something else (like Criteria API) instead of concatenating SQL/JPQL/whatever weird SQL dialect is offered by your ORM.

SELECT
    car.model, car.year, car.license_plate, car.person, person.name
FROM
    car
  INNER JOIN
    person ON car.person=person.id
WHERE
    person.firstname="Bobby"
  AND
    person.lastname="Tables"
ORDERED BY car.year,car.model DESCENDING
LIMIT 10 OFFSET 20

$cars = get_rows('car', $filter);

foreach $cars as $r {
    $person = get_rows('person', array('id',$r->owner));
    $tbl_row = array($r->model, $r->year, $person->firstname, $person->lastname)
    print_table_row($tbl_row)
}

$cars = get_rows('car', $filter);
$persons = get_rows('person', NULL);

foreach $cars as $r {
    $tbl_row = array($r->model, $r->year, $persons[$r->owner]->firstname, $persons[$r->owner]->lastname);
    print_table_row($tbl_row);
}

$fields = array('cars.model', 'cars.year', 'person.firstname', 'person.lastname')
$tables = array('car' => '', 'person' => 'car.owner')

foreach get_rows($fields, $filter, $tables) as $r {
    print_table_row($r);
}