Open Side Menu Go to the Top

04-09-2016 , 12:03 PM
Quote:
Originally Posted by Mavoor
I'm familiar with SQL syntax but don't quite understand everything about databases, like what is the difference between say SQLite and MySQL. So bonus points for anyone who can explain that
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.

Quote:
Perhaps this was just a problem with his computer/compiler/whatever, and we should try it on another computer?
The .file extension is worrying. Sounds like you aren't properly doing backup and restore:

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

Code:
sqlite3 sample.db .dump > sample.bak
Code:
sqlite3 sample.db < sample.bak
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.
Programming homework and newbie help thread Quote
Programming homework and newbie help thread
$25m Guaranteed WPM on CoinPoker
Join the action now
Daily Rewards • Splash Pots • CoinRaces
Programming homework and newbie help thread
04-09-2016 , 12:38 PM
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?

Last edited by coon74; 04-09-2016 at 12:50 PM.
Programming homework and newbie help thread Quote
04-09-2016 , 01:10 PM
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.
Programming homework and newbie help thread Quote
04-09-2016 , 03:31 PM
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.
Programming homework and newbie help thread Quote
04-09-2016 , 04:12 PM
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.

Last edited by _dave_; 04-09-2016 at 04:31 PM.
Programming homework and newbie help thread Quote
04-09-2016 , 05:31 PM
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.

Last edited by coon74; 04-09-2016 at 05:49 PM.
Programming homework and newbie help thread Quote
04-09-2016 , 06:23 PM
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.
Programming homework and newbie help thread Quote
04-09-2016 , 06:27 PM
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:

Quote:
If you include the MySQL server with an application that is not licensed under the GPL or GPL-compatible license, you need a commercial license for the MySQL server.
OK, we will put it on the user to download MySQL themselves, we won't include it.

Quote:
If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries.
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.

Last edited by _dave_; 04-09-2016 at 06:33 PM.
Programming homework and newbie help thread Quote
04-09-2016 , 06:54 PM
Quote:
Originally Posted by MySQL AB, 2005
When your application is not licensed under either the GPL-compatible Free Software License as defined by the Free Software Foundation or approved by OSI, and you intend to or you may distribute MySQL software, you must first obtain a commercial license to the MySQL product.
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

Last edited by coon74; 04-09-2016 at 07:19 PM.
Programming homework and newbie help thread Quote
04-09-2016 , 07:23 PM
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.
Programming homework and newbie help thread Quote
04-09-2016 , 07:32 PM
Quote:
Originally Posted by _dave_
The coding the wheel article can be found in the archive.org, still an interesting read: http://web.archive.org/web/201301310...ker-postgresql
Quote:
And it's fascinating from a software development standpoint, because as software developers we've been instilled with a healthy fear of exposing databases to the end-user. I can hear it now.
Quote:
Product Manager: So wait, let me get this straight. You want us to force our users, who are some of the laziest people on the planet, to install a full-fledged relational database management system??? On their home computer??? Like what, they're going to become DBAs? And you're calling that a feature? Well, why stop there? Why not just ship them our source code directly and force them to compile it on the COMMAND LINE? Every user is a programmer, right? Well? ARE YOU OUT OF YOUR F--KING MIND??
Actually LOLing, that's great! Thanks for the link.
Programming homework and newbie help thread Quote
04-09-2016 , 08:11 PM
Another random JS q: When referencing member data in callbacks that I pass elsewhere, I've been doing a lot of this:

Code:
  var timeout = setTimeout((function () {
    this.doSomething();
  }).bind(this), delay);
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.
Programming homework and newbie help thread Quote
04-09-2016 , 08:26 PM
Quote:
Originally Posted by goofyballer
Actually LOLing, that's great! Thanks for the link.
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.
Programming homework and newbie help thread Quote
04-09-2016 , 08:40 PM
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.
Programming homework and newbie help thread Quote
04-09-2016 , 08:56 PM
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.
Programming homework and newbie help thread Quote
04-09-2016 , 09:02 PM
Quote:
Originally Posted by coon74
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)
Programming homework and newbie help thread Quote
04-09-2016 , 09:15 PM
Yeah, I imagine the hassle

Quote:
You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).
(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).

Last edited by coon74; 04-09-2016 at 09:24 PM.
Programming homework and newbie help thread Quote
04-09-2016 , 09:20 PM
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)
Programming homework and newbie help thread Quote
04-09-2016 , 09:35 PM
Quote:
Originally Posted by RustyBrooks
sqlite supports the use of bind variables (or at least a bind-variable-type syntax), right?
It does
Programming homework and newbie help thread Quote
04-09-2016 , 10:24 PM
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.
Programming homework and newbie help thread Quote
04-10-2016 , 09:19 AM
****74, why aren't you using PgAdmin or psql?

(nice handle, won't even show up, lol)
Programming homework and newbie help thread Quote
04-10-2016 , 09:23 AM
Quote:
Originally Posted by coon74
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).
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.
Programming homework and newbie help thread Quote
04-10-2016 , 10:52 AM
Quote:
Originally Posted by daveT
****74, why aren't you using PgAdmin or psql?
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!

Quote:
Originally Posted by daveT
(nice handle, won't even show up, lol)
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.
Programming homework and newbie help thread Quote
04-10-2016 , 11:27 AM
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.
Programming homework and newbie help thread Quote
04-10-2016 , 12:55 PM
Quote:
Originally Posted by daveT
I'm not sure if that was sarcasm. I was only suggesting to use PgAdmin to write the joins out, but I digress.
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:
Spoiler:
There are tables tourney_hand_summary - each of whose rows stores some generic (unrelated to players' actions) info about a hand - plus tourney_hand_player_statistics each of whose rows stores info about the actions of a particular player in a hand.

There are a few flag fields answering the questions like 'Did the player face anyone's raise in the hand?'. But there are no fields that reference the IDs of the opponents of the player (nor is there a list of player IDs by position in tourney_hand_summary).

So, to select the rows containing info about the hands of a certain player where I was his primary opponent, I'd have to subselect from tourney_hand_player_statistics because the custom stat interface doesn't allow to join the table on itself (using the equality of id_hand in the join-predicate) and it would be problematic to integrate pgAdmin with the HUD. But a subquery slows the tracker down so much that such a stat would be unsuitable for a HUD.
Quote:
Originally Posted by daveT
Whatever you wrote sounds kind of strange. Why would you have a step to generate .sql files?
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

Quote:
Originally Posted by daveT
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.
Noted!

Last edited by coon74; 04-10-2016 at 01:18 PM.
Programming homework and newbie help thread Quote
Programming homework and newbie help thread
$25m Guaranteed WPM on CoinPoker
Join the action now
Daily Rewards • Splash Pots • CoinRaces
Programming homework and newbie help thread

      
m