0 users browsing Programming. | 1 guest  
Main » Programming » Ideas for coding a collection cataloging application
Pages: 1
Posted on 19-01-20, 19:19 (revision 2)
Not from my cellphone

Post: #125 of 739
Since: 10-30-18

Last post: 21 hours
Last view: 13 hours
Now that I've talked quite a bit lately about my two active collections on this board (banknotes and Touhou DVD/BDs), I've noticed that despite having a (mostly) ordered collection, I actually have no idea on what I may (or may not) have. For example, missing video releases or banknotes that I should replace with better condition pieces. If you ask me anything about what any specific piece of my collection, currently I have no option but to actually dive into my stuff and check every piece, one by one. For my Touhou stuff it isn't a big deal, but with my banknotes... well, the collection currently lives in a cardboard box and I would prefer to handle it as little as possible, to prevent damaging any of my pieces (at least until I can get access to better archiving methods).

A couple decades ago I had the idea of writing an application for a similar purpose (I used to collect phonecards too), but never went beyond the "make some tables in Access" design phase, and promptly forgot about it. But now I have the urgent need to know exactly what I have, considering that both of my current collections are growing at a slow but steady pace. For the banknotes, take a look at these pages to give you an idea of what information I need to record (no, I do not want Yet Another Money Catalog Website, rather 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.

Here are my current requirements for the frontend:
- Multiplatform, obviously.
- An ol' fashioned desktop application would be ideal, but I'm not opposed to the idea of a web application (I've been doing that for the last five years, so why not?). I don't care at all about cellphones/smartdevices/toys, so no "mobile first" UX wet dreams, sorry.
- Single user. This is for myself, and it's going to be installed on a couple PCs max (both connected to the same DB, but never using it at the same time), so things like concurrency control are not required (nor desirable) in this scenario.
- I want to keep the DB "decoupled" from the application, should I decide to repurpose it for something else, like a public exhibition of my collections on my website, or something. This means I'm not looking for self-contained solutions like an Access .MDB file. BTW, the PostgreSQL requirement is already set in stone - I'm not looking for alternative DBMSs (MSSQL/anything by Orrible/SQLite, or even worse, hipster junk like NoSQL)
- I've already grown used to ORMs so I would like to keep using one, if possible. A banknote is an object with attributes. A DVD ISO is still an object with attributes (in this case, a file), and coming up with hardcoded SQL sentences on your sourcecode is for ANIMALS. And last time I bothered checking myself, I was still an human being.

My language of choice is Java, but let's be clear: we're in 2019, Swing stopped being helpful like 10 years ago (and there are very good reasons of why nobody is using Swing for new projects nowadays), and while I could just come up with a JSF/JPA application, bringing up a full blown J2EE Web Profile AS/container is, like, overkill. Sure, I can setup a new WildFly instance in 10 minutes or so, but that's already on the bloaty side for my tastes. And while it seems you can use JPA on desktop applications, I don't really want to deal with Swing AT ALL (much less with things like NetBeans' utterly retarded form designers). I'm open to taking this as an excellent chance to learn something new I could tackle on my resumé, or something.

Platforms I've already ruled out, so don't try to be a smartass and bring reasons of why they're wonderful:
- Javascript: I've already said I'm not looking for hipster junk!!! If J2EE is overkill, having to deal with Node, Docker and friends is like asking the NASA Hacker News to dispatch one of their elite space scientists art school dropouts teams so they can scan my banknotes with electron microscopes bikeshed about the constant recoloring of banknotes in my country, or debate about Gensokyo lore...
- .NET: As much as I would like to learn C#, the "other platforms" bit is still too crude for my tastes. I want to catalog my shit, not being a unpaid betatester for Microsoft!
- Desktop database stuff (Access/LO Base). In the case of Access: EWWW! That's super lame, yo. In the case of LO Base: dealing with ODBC is not fun (particularly outside Windows), and while there has been native PostgreSQL support since the StarOffice SDBC days, every single time I try to interface with PG, things turn out too much to the "bugfest trainwreck" side for me :/
- C++: I know some of you bBoarders are going to tell me "just use hiro", but I have neither the desire nor the motivation to enter the joys of dealing with the oh-so-many C++ language subtleties. You guys enjoy having to bitch and moan about Visual Studio/Clang/GCC every single time a new C++ revision is released, all I want is to catalog my shit!

Please notice I'm not ruling out PHP: I still dislike it, people still consider it a terrible language, but I've worked with it in the past so I may use the chance to enjoy the latest shiny stuff which no shared webhost is going to ever deploy, while learning to do things The Right Way™ should my next job demands the use of PHP. Java is not out of the table either, despite the "overkill" concerns. But I'm still open to suggestions!

Licensed Pirate® since 2006, 100% Buttcoin™-free
Posted on 19-01-21, 09:48
Full mod

Post: #85 of 408
Since: 10-30-18

Last post: 8 days
Last view: 5 hours
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.

The ending of the words is ALMSIVI.
Posted on 19-01-24, 22:35 (revision 1)
Post: #17 of 134
Since: 11-24-18

Last post: 44 days
Last view: 2 days
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 on 19-01-25, 08:51 (revision 1)
Post: #1 of 1
Since: 01-23-19

Last post: 530 days
Last view: 529 days
Hi, I'm brand new to the forum and this is my first post, but I really like the small board you have here and I figure I'll engage. Nice to meet you!

I absolutely recommend going Python here. It has more than enough power to do this with the addition of a few really handy modules. In your case, PIL or Pillow is a must, BS4, and well, there's probably a lot I'd recommend, so I'll stop there. But, then your py-networker of choice and you'll finally have something. (Django or Jupyter or any of them, really.) This is the drawback of using modular languages like python or node but also the benefit, I suppose. Also, like wertigon suggested with the Pi, well then python should definitely be your number 1. The two go hand in hand in every way. (I just ordered two more, a zero and a zero w last night. The 1pp limit is probably for the best, because I've got the fever again...)

However, I'm going to suggest something outlandish, and I hope it doesn't come off as a terrible idea. I've done this myself before and it gets results fast, despite being very unorthodox.

Gamemaker Studio.

You could build this entire thing inside of a gamemaker application and either run it locally as an executable or export it with an HTML5 module and then use the standard mysql route to house it online. If you have no experience in gamemaker, you will be able to pick it up very easily just gauging your experience level. The syntax is very java-like and everything you need to prototype at the least is all there for you, no need to build engines in 3 different languages to connect the pieces : you just have to build 1. :)

If this is the most crap idea you've ever heard, I'm sorry, but I think it could actually work for your needs. If you like the idea but don't know the first thing about designing applications in a program typically used for game design - I can help in almost any area you could need! Let me know if you want to give it a shot and I'll make myself available to help you if you happen to need it.

EDIT: Accidentally pasted a link to this thread in the comment somehow, whoops!
Posted on 19-01-25, 09:18
Full mod

Post: #91 of 408
Since: 10-30-18

Last post: 8 days
Last view: 5 hours
Gamemaker has a database API?

Is that for high-score tables?

The ending of the words is ALMSIVI.
Posted on 19-01-25, 09:34
Custom title here

Post: #209 of 890
Since: 10-30-18

Last post: 7 days
Last view: 2 hours
Posted by theLibrarian

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

Not even in the top ten. Not even the worst idea in this thread, as there's already one suggestion that is a complete non-starter due to hardware expense.

You're gonna have to up your game if you wanna compete. Add some Visual Basic scripts, maybe a blockchain solution.



Also, hi. Welcome to the board.

--- In UTF-16, where available. ---
Posted on 19-01-25, 20:19 (revision 1)
Not from my cellphone

Post: #131 of 739
Since: 10-30-18

Last post: 21 hours
Last view: 13 hours
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.

That's... not very different than using Access or something as a frontend for your SQL tables. Or sticking to the spreadsheet approach, more likely.
If LO Base weren't that buggy (and with zero signs of improving, given that people is either happy with Access or willing to jump ship to a traditional application talking to a database over a wire), I would have pursued that way. Plus, there might be additional functionality I would like to implement (a good case with banknotes: tell if the serial number of your particular specimen is "fancy", and of what kind) would be much easier with an ordinary programming language.

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!

Your $20 Linux-in-a-stick approach sadly is not compatible with commie shitholes, sorry :/
I don't mind running a "full-stack" (or whatever fancy term the hipsters are using nowadays) solution on my laptops, performance is not of concern here so even my ancient P4s should deal with the workload of a single user filling forms and maybe uploading scans.
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...

> Gamemaker Studio
Let me check... oh, here are several reasons of why that ain't gonna fly with me:
- Not really free (they do have a free tier, but they're unclear about its limitations, and I'm not creating an account just to download their installer!)
- No Linux version of the IDE. Sorry, I don't develop on Windows. I don't mind about having to run the client from Windows, but I stopped writing code there years ago.
- How is their approach for integrating with a PostgreSQL database? ORM features? Or is their solution a lameloid "write a web services layer on another language and use that"?

Licensed Pirate® since 2006, 100% Buttcoin™-free
Posted on 19-01-25, 21:09
Post: #20 of 144
Since: 11-01-18

Last post: 7 days
Last view: 1 hour
Rust, Rocket, and sqlite
Posted on 19-01-26, 04:16
Full mod

Post: #92 of 408
Since: 10-30-18

Last post: 8 days
Last view: 5 hours
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?

I confess, I am not a fan of Django because it includes and promotes an ORM, where I'd be much more comfortable with raw SQL strings. However, you've said you're more comfortable with ORMs, and as far as these things go, Django's is pretty convenient. Make some classes that inherit from Table, add the fields you want, then invoke the function to write out an SQL schema. If you later change your classes, invoke the function again, and it will spit out a new schema, and a migration script you can edit, and there's another tool that you can point at a database, and have it automatically apply all the migration scripts required in the right order.

I guess my biggest issue with Django is that there's a Django Way for doing everything, from how to write your HTML templates, to how to set up forms, to how to talk to the database, to how to store and read configuration, to how to deploy. If you're trying to do the Simplest Thing That Could Possibly Work, all that extra stuff to learn and understand can be frustrating. On the other hand, if you already know it from a previous project, or you know for sure you're going to want more than about 80% of it, Django is a pretty slick, well-thought-out framework for making a database-backed website. It also has a huge community and excellent documentation (at least in English).

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...

The thing about Python is that runtime-reflection (which you may be familiar with from java.lang.reflect) is super easy, very powerful, and very tempting when you have a task with a lot of boilerplate like setting up URL routing or something like that. A bit of reflection magic, especially in a library, makes for very attractive "webserver in five lines" examples, but because such things break the rules of "regular" Python, it can be very difficult to understand what's really going on, and therefore very difficult to debug.

Django used to have a bunch of reflection magic in the pre-1.0 days, but eventually they ripped it out in favour of being a little bit more tedious and a lot more predictable.

The other big Python web framework is probably Flask, which is pretty much the opposite of Django. Where Django tries to give you one of everything, Flask is a "microframework" and pretty much only gives you URL routing and templating out of the box. Where Django has an extensive, magic-free API, Flask has a small, magic-intensive API. It still has a decent-sized community, and decent documentation, and makes it easy to get something up and running quickly, so maybe it'd work for you.

The ending of the words is ALMSIVI.
Posted on 19-01-26, 15:07
Stirrer of Shit
Post: #2 of 717
Since: 01-26-19

Last post: 166 days
Last view: 164 days
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.

Are you sure you aren't over-engineering this?
If you're willing to drop the PostgreSQL requirement, then DB Browser for SQLite looks like it might work for your use case.

If not, a PHP webapp would probably be the fastest to write since you wouldn't have to worry about SQL injections and the like.

There was a certain photograph about which you had a hallucination. You believed that you had actually held it in your hands. It was a photograph something like this.
Posted on 19-01-26, 19:06 (revision 1)
Not from my cellphone

Post: #132 of 739
Since: 10-30-18

Last post: 21 hours
Last view: 13 hours
Guys, the PostgreSQL requirement is set on stone. The server is already there, listening for connections while barely consuming system resources.

I want a real database on which I can trust blindly and that I can easily repurpose or scale should I really want to get serious (like actually uploading my galleries to a public website, or even turn those into a product I could offer to others for free/profit), despite the (initially) short scale. I already know how to deal with this stuff, it isn't like "hey, I got a hammer so let's hammer down some nails!". While I would want a ORM, that's for development comfort - I don't mind getting my hands wet with some raw SQL once in a while, but for the basic tasks (CRUD, basic queries) I prefer to stick to the object-oriented design.

Plus, there is the very narrow scope of projects like these: how many free/commercial applications you know aimed at collectors? Not that many, most of them are aimed at things like videos/music (no, I'm not talking about your media player, but about catalogs about your physical collections of shiny plastic discs), and certainly nothing aimed at the specifics of doujin releases ("Comiket 95" tells me more in this context rather than just a release date), and absolutely next to nothing with regards of more popular stuff like banknotes or coins (the few examples I've seen were about other collectors rolling their own so they can post a catalog to their websites)

So far, here are my 3 candidates - notice that all three lead to the same way: a web application (at this stage, I'll concede that ol' fashioned desktop software is going the way of the dodo)

- Java: I already know the platform (J2EE/JSF) so I could get something simple running up in a few days, but sadly it's on the bloaty side for such a small project like these ones.

- PHP: My PHP knowledge is a bit rusty, and I know everybody and his dog loves to hate on PHP (myself included), but hey, "simple" is the keyword here. Plus it would be a nice way to play with PHP7 (any ORM suggestions for PHP?), and deploying PHP webapps is far from the bloatfest of J2EE.

- Python: Two votes for Django here, on top of that learning a new (and possibly sane) language can be a good experience I can use for more important purposes in the future.

Licensed Pirate® since 2006, 100% Buttcoin™-free
Posted on 19-01-26, 23:20

Post: #21 of 49
Since: 10-29-18

Last post: 304 days
Last view: 189 days
Honestly, my bias is towards PHP given how long I've used it and how much experience I have with it and I also usually write PHP CRUDs from scratch rather than with some ORM library, but Python might indeed be the simpler solution to this. On the plus side, as long as your database is designed decently then it's totally possible to do both PHP and Python scripts to it to avoid Java bloat.
Posted on 19-01-27, 15:28
Post: #18 of 134
Since: 11-24-18

Last post: 44 days
Last view: 2 days
For PHP7, an old-newfangled ORM library already exists in the base distribution: PDO. If you are going to go with PHP, I suggest you look into that. The Zend engine is the next logical step if you are serious about PHP for more than a hobby project.
Posted on 19-01-28, 18:19
Not from my cellphone

Post: #133 of 739
Since: 10-30-18

Last post: 21 hours
Last view: 13 hours
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.

What I've found is that ORM libraries DO implement their stuff on top of PDO, that is, they're using it as the backend for interacting with the database (similar to what JPA does with JDBC - in theory you can use JPA without the need of a JDBC driver if you're connecting to something that isn't really a database). Speaking about PHP ORMs, here is what I've found so far:

- Propel: Looks pretty simple to get started, although you have to rely on extra tools to initialize configurations and mappings (it can easily RE your current schema to XML files, which... I don't really like that much). I could have some fun with this one.

- Doctrine: I like what I see here: the API looks very similar to JPA/Hibernate, which means a smoother learning curve for me. For whatever reason they offer YAML for doing your mappings (aside of XML and class annotations), but aside of that, this is an option I will look into with more detail.

- RedBeanPHP: It reaaaaaaaaaaally insists into being in control of your schema (like creating tables and the like), with no option to simply map your entities the ol' fashioned way. I can't see myself devoting time to this one.

- Fat-Free Framework: No explicit mappings -> magic™ -> not sure if want. Plus it comes with its full blown "lightweight" framework for templating and stuff, something I'm not actively looking for this time.

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.

Licensed Pirate® since 2006, 100% Buttcoin™-free
Posted on 19-01-28, 20:17
Post: #19 of 134
Since: 11-24-18

Last post: 44 days
Last view: 2 days
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.


Ah, the concept of ORM has evolved in the PHP world since I last looked at it, was it around 2010? My apologies.

I equate ORM with "API that allows you to deal with database rows as objects. Plus a few extra convenient functions." Which pretty much *is* PDO, but yeah. That's the brain damage you suffer when you grew up with the horrible horrible mysqli API of PHP4. ;)

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 on 19-01-28, 21:14
Stirrer of Shit
Post: #7 of 717
Since: 01-26-19

Last post: 166 days
Last view: 164 days
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.


Why do you need to have your hand on the power button? If it cuts out, it cuts out. Are you concerned about data loss?
Also, don't you have some old laptops? Their batteries would function as a UPS, and you might be able to fashion one for your desktop computer out of them.


There was a certain photograph about which you had a hallucination. You believed that you had actually held it in your hands. It was a photograph something like this.
Posted on 19-01-28, 21:35
Not from my cellphone

Post: #134 of 739
Since: 10-30-18

Last post: 21 hours
Last view: 13 hours
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... :)


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.

Licensed Pirate® since 2006, 100% Buttcoin™-free
Posted on 19-01-30, 17:24 (revision 2)
Post: #20 of 134
Since: 11-24-18

Last post: 44 days
Last view: 2 days
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.


Hmm, those specifics seems fairly straightforward. Sorting can be done with "ORDERED BY" statements, and filtering can be done with WHERE, like this:

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


The problem with most ORMs are that they simplify the SQL handling way too much, and yes, I'm being way too much of an optimisation nerd. ;)

[edit]To be clear, the situation I'm trying to avoid is something like this (forgive my PHP, haven't used it in ages):

$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)
}


Which saves on memory but is an atrocious SQL query hog. The other alternative would be:

$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);
}


Which is better but instead takes up a *lot* of memory. Of course, the best solution would simply be:

$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);
}


And yeah, when it comes to dynamic filtering, it does take a bit of thought, but SQL fortunately is great at that sort of stuff, and it's quite easy to insert PHP variables in the queries. Just make sure they are scrubbed first.[/edit]
Posted on 19-01-30, 18:19
Not from my cellphone

Post: #135 of 739
Since: 10-30-18

Last post: 21 hours
Last view: 13 hours
Yeah, I get your point, but as long as you know how to use your ORM query API/language, you should rely on your provider to generate optimal SQL queries at the backend. This also implies to be wise when picking your ORM solution to avoid the braindamaged ones. For example, on JPA, both EclipseLink and Hibernate are quite good when generating reasonably structured queries 99.9% of the times, and that's enough to blindly rely on them for your average application (although sometimes they become overly verbose, or generate a suboptimal JOIN once in a while, or completely lose their mind and generate syntactically wrong SQL if you get very creative with your query API calls)

I'll take the "use variable names on the queries" feature tho - the Bash-esque way to do it comes very handy at times no matter if we're dealing with SQL or with something else, and sadly that's one of the things you cannot do on languages like C-derivatives without resorting to horrible string concatenation operations (which can easily murder your performance in extreme cases). But then, people will tell you "that's what prepared statements are for, you dummy!" (ORMs basically work what way: their ways to perform queries are nothing but a very intrincate wrapper over prepared statements - turn debug mode on and you will notice it)

Licensed Pirate® since 2006, 100% Buttcoin™-free
Pages: 1
Main » Programming » Ideas for coding a collection cataloging application
you need to wake up michael