Zompist Bboard Again

Nortaneous wrote: ↑Tue May 26, 2020 11:04 pm Setting up Postgres is a pain in the ass, but we have sqlite for that now.

God I love sqlite. No setup and it's actually faster than a traditional DB for datasets up to several GB in single threaded / read dominated applications because there's no network latency involved.

My approach for most things now is to built it using the zero effort sqlite solution then migrate it at the end to a "proper" SQL db if it's going to sit on a server. And for a client-side application there's no need to migrate at all for many applications.

Oh, and if you like using pandas you can get pandas to do the reading and writing for you, but the result will be much better for random access from other code.

Ryusenshi wrote: ↑Wed May 27, 2020 1:12 amI did look into SQL databases for another project, but got kinda lost between all possible versions (SQLite? MySQL? PostgreSQL? MariaDB? LibreOffice Base?). Especially the part when you have to set up a server and a client, even if you're only running on a single computer.

I hate how people tend to write over-complicated tutorials, presumably assuming that people want to learn to use complex tools in professional ways from day 1. I assume that's why you seem confused anyway.

There is a subset of basic SQL that is the same (or at least accepted) by all those engines. Then the engines also have extra capabilities, sometimes many.

This does mean that if you change your database engine, you may have to rewrite all your statements ("queries") again. This is why some swear by limiting themselves (and, more importantly, their co-workers) with libraries like PDO for PHP, which force people to use a subset of capabilities that work across databases and also leave the query details to the library maintainers. This is also why some put all custom queries in their own separate DAO (data access object), so that changing the database engine means changing the code of the DAO without changing the rest.

The words "server" and "client" can simply refer to a program offering a service to another program, like here. If everything is inside one computer, then the engine (MariaDB, PostgreSQL...) is the "server" and your program (in C, Python, PHP... or LibreOffice Base, the GUI program you wrote in Python...) is the "client".



Regarding engines:
- SQLite is the lovable stupid simple engine where the data is stored in a single file that you can just copy-paste elsewhere or share around.
- PostgreSQL and MySQL are much more powerful engines that are more complex to understand but generally function a lot faster and more efficiently when large amounts of data are involved (and appropriately structured, like having a consistent length even if it means adding null space).
- MariaDB is a very popular fork of MySQL made after Oracle (may God judge them harshly) bought the legal rights to MySQL, and MariaDB remains (almost) 100% compatible with it to encourage people to abandon it. However, the compatibility also means that people keep referring to MariaDB as "MySQL".
- Microsoft SQL Server is that company's proprietary professional engine, usable by Microsoft Access besides C# and many other languages through libraries.
- Microsoft Access uses its own little internal engine making .accdb files that can be shared around like SQLite files can. In the old days it used a different one that created .mdb files.
- Since 2019 LibreOffice Base is a client that uses the Firebird engine by default inside its files, which can then be easily copied and shared around. Before it used HSQL. That program can also connect to many other engines, including PostgreSQL and MariaDB.

chris_notts wrote: ↑Wed May 27, 2020 2:20 ambut the result will be much better for random access from other code.

What do you mean by this?

Ryusenshi wrote: ↑Wed May 27, 2020 1:12 am I did look into SQL databases for another project, but got kinda lost between all possible versions (SQLite? MySQL? PostgreSQL? MariaDB? LibreOffice Base?).

SQLite unless you're writing a web app, in which case Postgres.

Especially the part when you have to set up a server and a client, even if you're only running on a single computer.

The point of using SQLite is that you don't have to do that.

Ser wrote: ↑Wed May 27, 2020 3:14 am There is a subset of basic SQL that is the same (or at least accepted) by all those engines. Then the engines also have extra capabilities, sometimes many.

This does mean that if you change your database engine, you may have to rewrite all your statements ("queries") again.

I sure do love technology. It's been four years since I was working with SQL Server and half the time I still reach for SQL Server syntax rather than Oracle. (and recently I had to convert some stuff from Oracle to--vom--Teradata, which was a fun exercise in hating my life) The problem is that they're all fundamentally similar enough to lull you in a false sense of security before they wallop you over the head with a brand-new way to format dates.

alynnidalar wrote: ↑Wed May 27, 2020 8:13 amhttps://imgs.xkcd.com/comics/standards.png

I sure do love technology. It's been four years since I was working with SQL Server and half the time I still reach for SQL Server syntax rather than Oracle. (and recently I had to convert some stuff from Oracle to--vom--Teradata, which was a fun exercise in hating my life) The problem is that they're all fundamentally similar enough to lull you in a false sense of security before they wallop you over the head with a brand-new way to format dates.

I don't think this is a case of proliferating standards, but rather the competitors genuinely trying to get ahead the others and prevent cross-database movement by doing things very differently.

And yeah, database migration to another engine is a terrible pain. Changing the common ISO 8601 datetime format 2020-05-27T09:50:00 to their own special thing seems like a particular dick move. Sympathies.

Nortaneous wrote: ↑Wed May 27, 2020 8:00 am

Ryusenshi wrote: ↑Wed May 27, 2020 1:12 amI did look into SQL databases for another project, but got kinda lost between all possible versions (SQLite? MySQL? PostgreSQL? MariaDB? LibreOffice Base?).

SQLite unless you're writing a web app, in which case Postgres.

I also get behind this. Use SQLite for all your internal single-computer needs, and Postgres when it's a server that randoms generate requests to.

Ser wrote: ↑Wed May 27, 2020 3:14 am

chris_notts wrote: ↑Wed May 27, 2020 2:20 ambut the result will be much better for random access from other code.

What do you mean by this?

Google defines "random access" as:

the process of transferring information to or from memory in which every memory location can be accessed directly rather than being accessed in a fixed sequence.

If you store a large dataset in a CSV file, doing anything but reading the whole file is messy, and if you have a large dataset at some point you're probably going to want to access a subset of it. Sure, you can loop over it line by line, and if you make all the lines fixed width you could jump to specific line(s) you want, but before you know it you're reinventing a database only worse. Pandas can partially hide this problem, but since it does all its filtering in memory you still end up potentially reading in lots of stuff you don't really want.

On the other hand, if you use something like sqlite and design your database right (choose the right primary key, add indices if necessary) then you can quickly read a subset of the data almost for free and without wasting time and memory scanning the whole file. Random access is built in to a database engine in a way that it's not into most CSV reading libraries.

chris_notts wrote: ↑Wed May 27, 2020 2:32 pmIf you store a large dataset in a CSV file, doing anything but reading the whole file is messy, and if you have a large dataset at some point you're probably going to want to access a subset of it. Sure, you can loop over it line by line, and if you make all the lines fixed width you could jump to specific line(s) you want, but before you know it you're reinventing a database only worse. Pandas can partially hide this problem, but since it does all its filtering in memory you still end up potentially reading in lots of stuff you don't really want.

On the other hand, if you use something like sqlite and design your database right (choose the right primary key, add indices if necessary) then you can quickly read a subset of the data almost for free and without wasting time and memory scanning the whole file. Random access is built in to a database engine in a way that it's not into most CSV reading libraries.

I already understand all that, so I guess my problem is not the concept, but it has to do with 1) not knowing what pandas does exactly (so I lack context) and 2) not understanding your English correctly.

Regarding 2), maybe when you wrote:
- "Oh, and if you like using pandas you can get pandas to do the reading and writing for you, but the result will be much better for random access from other code."
you meant the equivalent of:
- "Oh, and if you like using pandas you can get pandas to do the reading and writing for you, but the result would be much better for random access if you code it differently."
?

Ser wrote: ↑Wed May 27, 2020 2:43 pm Regarding 2), maybe when you wrote:
- "Oh, and if you like using pandas you can get pandas to do the reading and writing for you, but the result will be much better for random access from other code."
you meant the equivalent of:
- "Oh, and if you like using pandas you can get pandas to do the reading and writing for you, but the result would be much better for random access if you code it differently."
?

Ok, sorry. To explain, pandas can automatically read and write tabular data from both CSV files and databases, and having read it, it can duplicate many common database operations like joins and filtering in memory. But if you choose to use pandas in combination with a database (which is almost as easy as using it with a CSV file) then you will get some gains even in your pandas code by avoiding in-memory operations, but any code that doesn't use pandas will get massive gains if it needs random access to the data.

Ryusenshi wrote: ↑Wed May 27, 2020 1:12 am I did look into SQL databases for another project, but got kinda lost between all possible versions (SQLite? MySQL? PostgreSQL? MariaDB? LibreOffice Base?).

Nortaneous wrote: ↑Wed May 27, 2020 8:00 amEspecially the part when you have to set up a server and a client, even if you're only running on a single computer.

Fortunately, it's much less of an issue now. Just get the Postgres docker image running and you're good to go.

I've been having this big inflammation in the top right gums for a few days now. And just a little while ago I noticed that that there was something stuck between a molar and the gum. I finally went to the bathroom mirror with a toothpick and got it out. A lenght of at least 5 mm's of it had been lodged in the gum. Now I'm wondering what that thing was, so that I can avoid eating it again. I did eat popcorn many, many weeks ago. Could it really have been from a popcorn kernel?

At my job I deal with both SQL Server and Oracle, and the code I work on is expected to work with both seamlessly. Aside from some things which are consistently different, such as NUMBER versus NUMERIC and NVARCHAR versus VARCHAR2, the two are mostly intercompatible - except for details which one has to remember which you deal with less often, such as how to limit the number of entries in a query or how to choose an alternative value if a value is NULL.

Ser wrote: ↑Wed May 27, 2020 11:49 am I don't think this is a case of proliferating standards, but rather the competitors genuinely trying to get ahead the others and prevent cross-database movement by doing things very differently.

Well, certainly--but that's the reason for most incompatible standards. You have a bunch of competitors who believe their version is superior to everyone else's, and want people who start with them to find it inconvenient and difficult to switch to anybody else.

Travis B. wrote: ↑Wed May 27, 2020 5:22 pmsuch as how to limit the number of entries in a query or how to choose an alternative value if a value is NULL.

I miss TOP.

I have a trivial question about one small aspect of the ZBB's software and web design.

When I visit the ZBB on my cellphone and hold my cellphone in portrait mode, I get to see a slightly stripped-down version of most or all pages, while, when I hold my cellphone in landscape mode, I get to see what appears to be the full version that I can also see when I visit the ZBB on my computer.

This makes me curious about how all that works under the hood. Is the additional information that's only visible on computers and in landscape mode always there in my cellphone, or is it only fetched when I switch to landscape mode? Since there doesn't seem to be any reloading when I move my cellphone into landscape mode, I guess it's the former, but I'm not completely sure.

I believe so. Try making your PC's browser window small and see if the mobile interface appears.

Thank you!


--------------------------


Unrelated: What are the ZBB rules or guidelines about bumping posts? There doesn't seem to be anything about the topic in the Rules and For New Members threads.

Raphael wrote:Unrelated: What are the ZBB rules or guidelines about bumping posts? There doesn't seem to be anything about the topic in the Rules and For New Members threads.

I don't know that it's ever been enough of an issue that we've had to formulate any guidelines. We trust y'all not to go wildly bumpilicious.

Ah, thank you.

I'd google this, except that I'm not sure how to: have there been human cultures that had pottery but not agriculture?

The wikipedia article on pottery says so, but without the sort of detail you'd probably want.

Pottery has been found as far back as 20000 B.C. in China and 14500 B.C. in Japan.... it's believed they needed it for cooking but they did not have rice or any other cultivated plants yet. See enwp.org/jomon_period