https://www.digitalocean.com/communi...gement-systems

It's an extremely complex question, but for what probably matters for you:

The gist is that SQLite is a file system more than a database system, but it supplies you with SQL syntax. You don't have ports and you can't have multiple connections. SQLite is best for small apps or embedded software, not for large systems with many R/Ws.

MySQL, in contrast, has ports and allows for connection pools, which may or may not make a difference in your application.

You should *never* greenfield a system with MySQL. The MariaDB fork is far more dependably maintained now that Oracle own MySQL.

It really depends on how much you want to interact with the database system itself. If your goal is to simply store data and use some SQL syntax, then it doesn't really matter which system you use outside of the mentioned differences. To be certain, I would never greenfield a system with MariaDB either and would just use PostgreSQL instead, if for no other reason than MariaDB has had memory leak bugs that haven't been fixed for about 5 years.

The .file extension is worrying. Sounds like you aren't properly doing backup and restore:

http://www.ibiblio.org/elemental/how...te-backup.html

If you want a load script in your system:

create the .sql files(s) that have the table creation / build scripts.
save to some place in your system
read and parse the .sql files directly into the system.

Speaking of, is licensing the main reason why poker tracking software uses PostgreSQL, or is the latter generally more convenient for offline desktop applications than MySQL / MariaDB?

there was an interesting article on that topic here, but apparently the entire site is dead now:

http://www.codingthewheel.com/archiv...ker-postgresql

From the comments:

https://news.ycombinator.com/item?id=693692

http://pgsql-general.postgresql.nark...esql-and-poker

Looks like licensing may have been the initial killer for MySQL. Not that licensing ever seems to stop other companies from not releasing source to end-users, so at least HEM and PT were following the rules. I suspect that there had to be many other reasons, as other db options were available at that time and PostgreSQL didn't have huge mind share back in those days, I don't think.

Thanks for the links!

I guess that SQLite was dismissed because the HM and PT teams wanted their products to write hands fast into really big DBs (of millions of hands, incl. datamined ones).

However, the Microgaming poker client stores hands (up to 30 days old) in an SQLite DB, and FPDB also uses SQLite as the default DBMS, recommends MySQL for larger DBs, and iirc, the PostgreSQL option wasn't even supported by FPDB a couple of years ago.

The coding the wheel article can be found in the archive.org, still an interesting read: http://web.archive.org/web/201301310...ker-postgresql

I was there at the time, and vaguely remember posting in favour of Postgres in terms of it actually working properly when it came to referential integrity and date fields and other basic DB tasks (pretty sure at the time, 31-Feb was a valid MySQL date...). But yeah as I remember licensing was the big issue. You can see in archive.org on the old PT forum there is a thread "MySQL Release CANCELLED" from early 2006, so it must have been considered pretty strongly at some point. Archive.org doesn't have the thread contents, unfortunately

Then of course HM came along, and not only was Postgres a top quality database and a good choice anyway - 90% of your initial customers have it installed already!

edit: SQLite is dismissed because it has similar (worse!) issues than MS Access - it locks the entire DB for each query. Next to useless for a large busy poker DB. Pretty sure Postgresql was in FPDB from right near the start, likely before SQLite - but I'm no expert there.

Well, as far as I understand, the C client library of MySQL was under LGPL prior to the acquisition by Oracle (at least, that's what the license page of MariaDB hints at, 'The new MariaDB client libraries for C and Java are distributed under LGPL. The same license that was used for the older version of the MySQL client library'). Right?

So it's possible to use MariaDB (or MySQL back then) to power a commercial service with an open-source server side and a desktop client with closed (except the libraries) code, which would be enough to set up a paywall. (MariaDB's code is under GPL v.2 except the 'new' C and Java client libraries that are under LGPL 2.1.)

I guess, server space was too expensive back then to consider cloud storage as an option, so the 'server' and the DB would be stored on users' computers.

Derp - that LGPL 'MySQL client library' was released on August 30, 2010, as a result of the acquisition and the forking.

However, even before it, GPL wasn't mandatory for software that merely connected to MySQL even if the vendor had no proprietary MySQL license.

Take a look at these licensing terms from 2005

https://web.archive.org/web/20050320...l-license.html

even if you can roundabout some way of making it so you aren't breaching their very clear examples, such as:

OK, we will put it on the user to download MySQL themselves, we won't include it.

Bah, foiled! OK, we'll include MS Acess /mdb as the default, therefore MySQL is no longer a "must"! gotcha! (however, this isn't at all clear-cut, and could still be considered "must" I am sure)

Why run the risk of getting caught up in licensing troubles when there was a likely superior alternative that was also free of licensing issues available at the time.

LGPL is compatible with GPL and, besides, there was (and is) a FOSS exception (at the bottom of the licensing page) that allows software licensed under LGPL 2.0/2.1 (and 3.0 nowadays) to use MySQL for free.

A pitfall is that GPL drivers can't be used to connect a non-GPL client to MySQL (it would violate GPL), but there were and are some connectors under LGPL

So, as far as I see, no commercial MySQL license is required if the server is installed centrally (not on users' computers), an LGPL 'middle man' is used locally to connect with MySQL, and a separate non-GPL-compatible application interacts with that LGPL program.

Anyway, sorry for the derail about licensing that is not really homework-related

Yes, as I said it might have been possible to somehow wrangle your way to using MySQL in PokerTracker (they decided it was not, or it was too much trouble), but with PostgreSQL available what would be the point?

Even today it's a pretty poor option to use centralised cloud storage for a poker database (for performance reasons alone, nevermind security and privacy), 11 years ago it would have been insane.

Actually LOLing, that's great! Thanks for the link.

Another random JS q: When referencing member data in callbacks that I pass elsewhere, I've been doing a lot of this:

This feels more natural to me, but in a lot of the tutorials/examples I've read, people instead assign 'this' to a variable like 'this_' or something and then use that proxy in their callback. Is one particularly better than the other? I guess the latter has the benefit that you do it once and it persists through nested callbacks, while the binding thing has to be done at each level.

It would be OK if PT4 provided me with a more functional IDE for custom columns... instead, the software only gives an opportunity to write a column expression that it inserts into an automatically generated SELECT query (as witnessed by the log file).

The most extreme hack I've made was the use of a nested SELECT in custom 'vs Villain' stats - instead of a self-join that isn't supported - which certainly slowed the report down severely.

Well, it's better than nothing but leaves an uneasy feeling of constraint.

I'm all for them 'making me a DBA' if they give me adequate tools; otherwise, the need to install and sometimes troubleshoot PostgreSQL as the end user is annoying.

I used PT for quite some time, before making my own tracker/hud many many years ago (it's now defunct, I haven't kept up with it, and there was not really that much interest, it's a long story)

Anyway, in the process of creating it, I of course did a lot of reverse engineering from PT. If you want something special from PT, that you can't get through the interface, you can always get it from the postgres tables themselves. That's one of the benefits of them using a bog-standard SQL database for storage instead of something home grown. In PT3 (I think? Or PT2 maybe), you could click a few times on a column heading and it would cough up the SQL for that report. I used that more than once to figure out odd stuff I was seeing in their reports.

Yes, of course I can look the PT4 log files up and figure out the DB structure and the queries into it written there; what's tilting is that the built-in interface for custom queries is so crippled.

Speaking as someone who worked on a product that offered a gui to create custom queries of this kind... it's a hard problem. And actually, the SQL syntax makes it more difficult than it really should. It's a kind of crappy query language if you are building queries programmatically (it's not soooo bad if you're typing them in)

Yeah, I imagine the hassle

(quoted from the sqlite3 documentation).

That's one of the reasons why I'd prefer a statically typed language for this purpose (if the input were supposed to be mainly numbers).

Hm, I think that specific injunction is to prevent you from embedding values in your string. sqlite supports the use of bind variables (or at least a bind-variable-type syntax), right? In which case constructing queries is OK as long as you're not using user input as table or column names or something like that.

(Actually in PTs case, anything goes, because it's your own database. If you want to write an injection attack against a database you have admin privileges to, well, go ahead)

It does

I actually really hate mongo's query syntax whenever I am trying to hand-construct a query, but it's not so bad if you're building one in code, because the structure is extremely regular (it's a json structure). The metaphor becomes a bit strained at times because everything has to be represented in json, but in the end it's super easy to nest and combine queries because of it.

The other day I had a simple situation where I wanted to either insert or update a row in a table. Under some circumstances, one of the columns would not be included in the insert or the update. Making that work without doing some kind of gross string manipulation was hard, so I just gave up and did
insert or update
if condition: update specialcol to specialval

There really is no damn reason for SQL to have such different syntax for inserts and updates. So annoying. At least some databases have upserts now.

****74, why aren't you using PgAdmin or psql?

(nice handle, won't even show up, lol)

And to be clear on this you should NEVER EVER EVER use string concatenation or standard string operations for SQL queries, no matter what your language is. This is not an issue of static -vs- dynamic at all.

ugh, even so, PL/pgSQL is a strongly typed programming language, and you would NEVER EVER EVER use string concatenation to build queries.

So, for the purpose of extracting hundreds of values from files of a non-standard format daily and updating a DB with the values, do you suggest to write a parser generating an .sql file, check manually if all the queries there are syntactically correct, and then feed the .sql file to psql? That's actually a decent idea, thanks!

All the derivatives of the c-word do show up, though (it suffices to add any symbol), e.g. icoon, coons, coon74. It's common to replace one or two letters by 0 as in c00n. There are also more exquisite ways to imitate the word coоn such as using the Russian letter 'о' instead of the English one.

By tradition, 2+2 mods don't regard any of the above methods as profanity filter circumvention when it's clear from the context that the poster is addressing me.

I'm not sure if that was sarcasm. I was only suggesting to use PgAdmin to write the joins out, but I digress. Whatever you wrote sounds kind of strange. Why would you have a step to generate .sql files?

If you must do reads and push to a database, I'd suggest using Python or Ruby for both file imports and connecting to PostgreSQL.

The query-generating tools for Python aren't very good, so I'd suggest using PL/pgSQL, which is very simple to use, as long as you understand how format() is used for both strings and entities. You can write the stored procedures that generate your queries as needed and then call then directly from the db connectors.

As for the joins, pgAdmin doesn't really automate the querying process.

An example of a situation where I'd like to do automated joins is display of per-opponent stats telling e.g. how often each of them reraised after I (not anyone else) raised. Because the PT4 DB schema is optimised to keep DBs as small as possible, it suffers from a lack of cross-references. With few exceptions, stats that refer to more than one player's actions in a hand require a subquery or a join. The problem is that it's not comfortable for me to use pgAdmin for this in real time when I have to be focused on poker tables

A digressive TMI moan about the PT4 DB structure: I was afraid that binding variables and query sanitisation would be difficult to learn, and that it would be safer for me to build queries by string concatenation and check them visually before connecting to the DB, but I'm likely seeing ghosts

Noted!