Re: sqlite database
On Mon 02 Jul 2018 at 20:39:33 (+0100), Joe wrote: > On Mon, 2 Jul 2018 14:02:31 -0500 > David Wright wrote: > > > On Mon 02 Jul 2018 at 19:50:35 (+0200), deloptes wrote: > > > David Wright wrote: > > > > > > > Well, it's always difficult to know what the OP¹ is really after. > > > > The ?first time this came up (17 months ago), "minimal" was in > > > > the subject line, sqlite was looked at favourably, but the fact > > > > that it had a web interface was seen as a downer, perhaps because > > > > the word "web" appeared to contradict "no networking". > > > > > > But sqlite does have only CLI interface - the rest is not > > > mandatory. > > > > I agree, and the OP¹ has frequently said that CLIs are ok. > > But the OP¹ voraciously consults HOWTOs, and said: > > > > "Many presume a WEB-SERVER is involved." > > > > I don't know what others glean from this, but to me it sounds as if > > the mention of "web" taints the software somehow, perhaps > > subconciously. > > Rightly so. If you don't currently run a web server, then needing to do > so just to administer an unrelated application would seem illogical, > not to mention annoying. I wrote "any *mention* of web" might put off the OP¹. Nothing about *needing to*. The fact that many *want to* makes it a popular topic for HOWTOs, but it's not a need. > Today, the vast majority of database applications also involve a web > server, so there's usually no extra overhead in using it for database > administration. But that's not true for everyone. Virtually every PC runs a browser of some sort, and almost all browsers contain a database application. But most people aren't running a web server on their PC. Now, many electronic devices of all varieties are sold with extremely perfunctory built-in interfaces and require an external browser to exploit their full functionality (see below). Most of us use one: our router. But the web *server* is in the device, not in people's PC. > And if you have an application for a database management system of some > kind, then that application itself is surely the main method of > manipulating the DBMS. You need very little extra administration, > particularly in Debian where databases, users and privileges are > generally set up automagically. This is why I'm not particularly fluent > with the mariadb command line: I might use it two or three times a > year, and I always have to read up on it again when I do. With sqlite, the commandline is not something that you're forced to use to administrate the DB (there's no such concept anyway); it's a choice, just one method of accessing it. You could use Debian's sqlitebrowser exclusively, or one of the language bindings, according to preference. The fact is that the model: Device in a clean shiny boxPC/Mobile _ __ || WiFi || | functionality ↔ sqlite db ↔ web server | ←→ |Browser | |||| is popping up everywhere nowadays, so it's hardly surprising that some tutorials focus on this, and the same holds for models involving client/server DBs too, so more tutorials on that. It's popular, but it's not a requirement. I googled simple sqlite example and one of the top hits was http://www.sqlitetutorial.net/ Looking that over, I can't see anthing to put the OP¹ off². For someone coming from dBASEII and wanting to learn about modern Relational DataBase Management Systems, it would seem a perfect fit (to repeat myself). I can't see the desirability of the client/server model in the OP¹'s situation as it just adds a layer of administrative complexity for no payback. Perhaps I'm reading the wrong meaning of their "minimal". Still awaiting counterexamples. ² OTOH for this post, the diagram above might be offputting. Cheers, David.
Re: sqlite database
On Mon, 2 Jul 2018 14:02:31 -0500 David Wright wrote: > On Mon 02 Jul 2018 at 19:50:35 (+0200), deloptes wrote: > > David Wright wrote: > > > > > Well, it's always difficult to know what the OP¹ is really after. > > > The ?first time this came up (17 months ago), "minimal" was in > > > the subject line, sqlite was looked at favourably, but the fact > > > that it had a web interface was seen as a downer, perhaps because > > > the word "web" appeared to contradict "no networking". > > > > But sqlite does have only CLI interface - the rest is not > > mandatory. > > I agree, and the OP¹ has frequently said that CLIs are ok. > But the OP¹ voraciously consults HOWTOs, and said: > > "Many presume a WEB-SERVER is involved." > > I don't know what others glean from this, but to me it sounds as if > the mention of "web" taints the software somehow, perhaps > subconciously. Rightly so. If you don't currently run a web server, then needing to do so just to administer an unrelated application would seem illogical, not to mention annoying. Today, the vast majority of database applications also involve a web server, so there's usually no extra overhead in using it for database administration. But that's not true for everyone. And if you have an application for a database management system of some kind, then that application itself is surely the main method of manipulating the DBMS. You need very little extra administration, particularly in Debian where databases, users and privileges are generally set up automagically. This is why I'm not particularly fluent with the mariadb command line: I might use it two or three times a year, and I always have to read up on it again when I do. -- Joe
Re: sqlite database
On Mon, Jul 02, 2018 at 02:02:31PM -0500, David Wright wrote: > But the OP¹ voraciously consults HOWTOs, and said: > > "Many presume a WEB-SERVER is involved." > > I don't know what others glean from this, but to me it sounds as if > the mention of "web" taints the software somehow, perhaps subconciously. If it's the person I think it is, he lacks a fundamental understanding of the most basic concepts, and could probably benefit from a simplified explanation. Unfortunately, instead of asking for explanations, he assumes something which is incorrect, and then launches into a misplaced tirade against his imaginary strawman. Repeatedly. On every single topic. I don't know whether he can see this or not, but here are some basic computer concepts which seem to be unclear, based on snippets of text in this thread: data -- information stored in a computer-readable form database -- an organized collection of data, or the software which is designed to manage said collection server -- a process which responds to requests for data, or a computer which is running such a process client -- a process which sends requests to a server, and receives its responses So, these are pretty broad terms, yes? Let's get a bit more specific. "Database" covers a really wide range of things. Basically, any way that you can conceive to organize information storage and retrieval could be called a "database". Some people consider a directory full of files to be a "database", because you can store information in it, and retrieve information from it, and each piece of information has a "key" that lets you get that one piece of information and no other (that "key" is the file name). Another kind of database is a single file with "records" stored inside it, and a way to retrieve or write specific records without having to copy the entire file. A text file is NOT a database of this kind. In order to qualify, the file has to have some internal structures that allow fast retrieval and storage. SQLite3 IS a database of this kind. The database is stored in a single file, and programs using the SQLite3 libraries can store and retrieve information from it. (Other examples of this kind of database include the various Berkeley database libraries that have been used for things like NIS, as well as Daniel Bernstein's "cdb", and GNU's "gdbm".) But the kind of database that MOST people will first think of is the client/server kind, of which two common examples in the free software world are MySQL and PostgreSQL. With this kind of database, physical storage is typically in multiple files in multiple directories somewhere on disk, and this is managed by a server process. If you want to store or retrieve information from the database, you send a request to the server, and it sends back a response. The process making the request is a client, and may be written in any of several different languages, using the language-specific libraries or interfaces designed to talk to that database server. The server may be on the same machine as the client, or it may be on a different machine. Network-based communication between client and server may be optional, and may need to be configured on the server side by the adminstrator. There may be some authentication of the client required before the server will be willing to accept requests, or not. Now, integration between a database server and a web server is a wholly different step. In that configuration, the web server runs an application which contains the database client code, so the web application can store and retrieve information from the database, format it into pages, and present those pages to the web client (browser). But that's not a core part of the database at all. It's simply another application, albeit a very common one.
Re: sqlite database
On Mon 02 Jul 2018 at 19:50:35 (+0200), deloptes wrote: > David Wright wrote: > > > Well, it's always difficult to know what the OP¹ is really after. The > > ?first time this came up (17 months ago), "minimal" was in the subject > > line, sqlite was looked at favourably, but the fact that it had a web > > interface was seen as a downer, perhaps because the word "web" > > appeared to contradict "no networking". > > But sqlite does have only CLI interface - the rest is not mandatory. I agree, and the OP¹ has frequently said that CLIs are ok. But the OP¹ voraciously consults HOWTOs, and said: "Many presume a WEB-SERVER is involved." I don't know what others glean from this, but to me it sounds as if the mention of "web" taints the software somehow, perhaps subconciously. Cheers, David.
Re: sqlite database
On Mon 02 Jul 2018 at 18:45:31 (+0100), Joe wrote: > On Mon, 2 Jul 2018 12:05:25 -0500 > David Wright wrote: > > > On Mon 02 Jul 2018 at 10:20:00 (+0100), Joe wrote: > > > On Sun, 1 Jul 2018 18:04:15 -0500 > > > David Wright wrote: > > > > > > > On Sun 01 Jul 2018 at 21:36:00 (+0100), Joe wrote: > > > > > On Sun, 01 Jul 2018 22:21:11 +0200 > > > > > deloptes wrote: > > > > > > > > > > > Cousin Stanley wrote: > > > > > > > > > > > > > As an alternative to client-server database managers, > > > > > > > such as mysql, mariadb, postgresql, etc. , > > > > > > > the sqlite embedded database manager is very > > > > > > > useful > > > > > > > > > > > > Not sure what thread you write to, but can you do concurrent > > > > > > reads/writes to sqlite3 DB? > > > > > > > > > > > > I think it is not possible as the process that writes, locks > > > > > > the file, so just keep in mind that there are severe > > > > > > limitations in the use of sqlite3 databases. Correct me if I > > > > > > am wrong > > > > > > > > > > And the single file is vulnerable to network issues. Basically > > > > > the same limitations as MS Access when used as a single-file > > > > > database. Sqlite is the right answer for most single-user > > > > > non-network applications, > > > > > > > > … which sounds like a perfect fit for the recent thread on > > > > MariaDB. > > > > > > Not if you're trying to learn client-server databases. > > > > Well, it's always difficult to know what the OP¹ is really after. The > > ?first time this came up (17 months ago), "minimal" was in the subject > > line, sqlite was looked at favourably, but the fact that it had a web > > interface was seen as a downer, perhaps because the word "web" > > appeared to contradict "no networking". > > There is a program called sqlitebrowser, which does not obviously use a > web interface. Difficult to say, as I wanted to use it recently but it > is currently uninstallable on [my] sid. I believe I've used it in the > distant past. I had a data issue with Calibre, which used sqlite then > and probably still does. > > I understood the recent web reference to be concerning mariadb, and > hence phpmyadmin or adminer. I'm making no criticism of that. I'm merely recalling the intervening threads from OP¹ and wondering whether mariadb was chosen or stumbled upon by avoiding anything that was associated with certain "trigger" words like "web interface". > But both sqlite and mariadb/MySQL can be > driven by command, and no seasoned database admin (I'm not) would dream > of using anything as wimpy as a GUI DB manager. Hand-hacked perl would > be more likely... I don't think that OP¹ falls into that category. > > Whether a client-server model is now required depends on how one > > interprets https://lists.debian.org/debian-user/2018/06/msg00757.html > > > > "The [HOWTOs] I find are written assuming things that are never > > identified or use some distro specific hack. Many presume a > > WEB-SERVER is involved. I understand that MySQL's definition of > > server is somehow different." > > > > Again, that word "web" seems to be perceived as a problem. Now I would > > hate to prejudge the OP¹'s desires in terms of *learning*, but using > > their own expressed situation as a guide (the courses): > > > > . no networking, > > . single user (so likely no simultaneous writes), > > . no evidence of big (TB) data, > > > > most software selectors would choose sqlite as more appropriate than > > a client-server database system (the horses). > > > > > > https://lists.debian.org/debian-user/2018/06/msg00757.html > > > > > > > > > but > > > > > not for anything more demanding. Horses for courses, as > > > > > ever. > > > > > > > > … but I'm not sure whether we were given the use case in that > > > > thread. > > > > It did occur to me while writing this that the OP¹ has asked here > > about PDAs running Debian for data collection, and about transferring > > files between devices by using their USB ports. If the use case is the > > accumulation of said data, I don't see any pressing need for a > > client-server model, do you? > > > > ¹ OP of the thread I referred to. > > No, but he has frequently expressed a desire for education, and his > recent correspondence was explicitly about mariadb. Apologies for thinking outside the box. I still stand by my "perfect fit" statement. Perhaps we shall see counter-examples. Cheers, David.
Re: sqlite database
David Wright wrote: > Well, it's always difficult to know what the OP¹ is really after. The > ?first time this came up (17 months ago), "minimal" was in the subject > line, sqlite was looked at favourably, but the fact that it had a web > interface was seen as a downer, perhaps because the word "web" > appeared to contradict "no networking". But sqlite does have only CLI interface - the rest is not mandatory.
Re: sqlite database
On Mon, 2 Jul 2018 12:05:25 -0500 David Wright wrote: > On Mon 02 Jul 2018 at 10:20:00 (+0100), Joe wrote: > > On Sun, 1 Jul 2018 18:04:15 -0500 > > David Wright wrote: > > > > > On Sun 01 Jul 2018 at 21:36:00 (+0100), Joe wrote: > > > > On Sun, 01 Jul 2018 22:21:11 +0200 > > > > deloptes wrote: > > > > > > > > > Cousin Stanley wrote: > > > > > > > > > > > As an alternative to client-server database managers, > > > > > > such as mysql, mariadb, postgresql, etc. , > > > > > > the sqlite embedded database manager is very > > > > > > useful > > > > > > > > > > Not sure what thread you write to, but can you do concurrent > > > > > reads/writes to sqlite3 DB? > > > > > > > > > > I think it is not possible as the process that writes, locks > > > > > the file, so just keep in mind that there are severe > > > > > limitations in the use of sqlite3 databases. Correct me if I > > > > > am wrong > > > > > > > > And the single file is vulnerable to network issues. Basically > > > > the same limitations as MS Access when used as a single-file > > > > database. Sqlite is the right answer for most single-user > > > > non-network applications, > > > > > > … which sounds like a perfect fit for the recent thread on > > > MariaDB. > > > > Not if you're trying to learn client-server databases. > > Well, it's always difficult to know what the OP¹ is really after. The > ?first time this came up (17 months ago), "minimal" was in the subject > line, sqlite was looked at favourably, but the fact that it had a web > interface was seen as a downer, perhaps because the word "web" > appeared to contradict "no networking". There is a program called sqlitebrowser, which does not obviously use a web interface. Difficult to say, as I wanted to use it recently but it is currently uninstallable on [my] sid. I believe I've used it in the distant past. I had a data issue with Calibre, which used sqlite then and probably still does. I understood the recent web reference to be concerning mariadb, and hence phpmyadmin or adminer. But both sqlite and mariadb/MySQL can be driven by command, and no seasoned database admin (I'm not) would dream of using anything as wimpy as a GUI DB manager. Hand-hacked perl would be more likely... > > Whether a client-server model is now required depends on how one > interprets https://lists.debian.org/debian-user/2018/06/msg00757.html > > "The [HOWTOs] I find are written assuming things that are never > identified or use some distro specific hack. Many presume a > WEB-SERVER is involved. I understand that MySQL's definition of > server is somehow different." > > Again, that word "web" seems to be perceived as a problem. Now I would > hate to prejudge the OP¹'s desires in terms of *learning*, but using > their own expressed situation as a guide (the courses): > > . no networking, > . single user (so likely no simultaneous writes), > . no evidence of big (TB) data, > > most software selectors would choose sqlite as more appropriate than > a client-server database system (the horses). > > > > https://lists.debian.org/debian-user/2018/06/msg00757.html > > > > > > > but > > > > not for anything more demanding. Horses for courses, as > > > > ever. > > > > > > … but I'm not sure whether we were given the use case in that > > > thread. > > It did occur to me while writing this that the OP¹ has asked here > about PDAs running Debian for data collection, and about transferring > files between devices by using their USB ports. If the use case is the > accumulation of said data, I don't see any pressing need for a > client-server model, do you? > > ¹ OP of the thread I referred to. No, but he has frequently expressed a desire for education, and his recent correspondence was explicitly about mariadb. -- Joe
Re: sqlite database
On Mon 02 Jul 2018 at 10:20:00 (+0100), Joe wrote: > On Sun, 1 Jul 2018 18:04:15 -0500 > David Wright wrote: > > > On Sun 01 Jul 2018 at 21:36:00 (+0100), Joe wrote: > > > On Sun, 01 Jul 2018 22:21:11 +0200 > > > deloptes wrote: > > > > > > > Cousin Stanley wrote: > > > > > > > > > As an alternative to client-server database managers, > > > > > such as mysql, mariadb, postgresql, etc. , > > > > > the sqlite embedded database manager is very useful > > > > > > > > Not sure what thread you write to, but can you do concurrent > > > > reads/writes to sqlite3 DB? > > > > > > > > I think it is not possible as the process that writes, locks the > > > > file, so just keep in mind that there are severe limitations in > > > > the use of sqlite3 databases. Correct me if I am wrong > > > > > > > > > > And the single file is vulnerable to network issues. Basically the > > > same limitations as MS Access when used as a single-file database. > > > Sqlite is the right answer for most single-user non-network > > > applications, > > > > … which sounds like a perfect fit for the recent thread on MariaDB. > > Not if you're trying to learn client-server databases. Well, it's always difficult to know what the OP¹ is really after. The ?first time this came up (17 months ago), "minimal" was in the subject line, sqlite was looked at favourably, but the fact that it had a web interface was seen as a downer, perhaps because the word "web" appeared to contradict "no networking". Whether a client-server model is now required depends on how one interprets https://lists.debian.org/debian-user/2018/06/msg00757.html "The [HOWTOs] I find are written assuming things that are never identified or use some distro specific hack. Many presume a WEB-SERVER is involved. I understand that MySQL's definition of server is somehow different." Again, that word "web" seems to be perceived as a problem. Now I would hate to prejudge the OP¹'s desires in terms of *learning*, but using their own expressed situation as a guide (the courses): . no networking, . single user (so likely no simultaneous writes), . no evidence of big (TB) data, most software selectors would choose sqlite as more appropriate than a client-server database system (the horses). > > https://lists.debian.org/debian-user/2018/06/msg00757.html > > > > > but > > > not for anything more demanding. Horses for courses, as ever. > > > > … but I'm not sure whether we were given the use case in that thread. It did occur to me while writing this that the OP¹ has asked here about PDAs running Debian for data collection, and about transferring files between devices by using their USB ports. If the use case is the accumulation of said data, I don't see any pressing need for a client-server model, do you? ¹ OP of the thread I referred to. Cheers, David.
Re: sqlite database
On Sun, 1 Jul 2018 18:04:15 -0500 David Wright wrote: > On Sun 01 Jul 2018 at 21:36:00 (+0100), Joe wrote: > > On Sun, 01 Jul 2018 22:21:11 +0200 > > deloptes wrote: > > > > > Cousin Stanley wrote: > > > > > > > As an alternative to client-server database managers, > > > > such as mysql, mariadb, postgresql, etc. , > > > > the sqlite embedded database manager is very useful > > > > > > Not sure what thread you write to, but can you do concurrent > > > reads/writes to sqlite3 DB? > > > > > > I think it is not possible as the process that writes, locks the > > > file, so just keep in mind that there are severe limitations in > > > the use of sqlite3 databases. Correct me if I am wrong > > > > > > > And the single file is vulnerable to network issues. Basically the > > same limitations as MS Access when used as a single-file database. > > Sqlite is the right answer for most single-user non-network > > applications, > > … which sounds like a perfect fit for the recent thread on MariaDB. Not if you're trying to learn client-server databases. > > https://lists.debian.org/debian-user/2018/06/msg00757.html > > > but > > not for anything more demanding. Horses for courses, as ever. > > … but I'm not sure whether we were given the use case in that thread. -- Joe
Re: sqlite database
On Sun 01 Jul 2018 at 21:36:00 (+0100), Joe wrote: > On Sun, 01 Jul 2018 22:21:11 +0200 > deloptes wrote: > > > Cousin Stanley wrote: > > > > > As an alternative to client-server database managers, > > > such as mysql, mariadb, postgresql, etc. , > > > the sqlite embedded database manager is very useful > > > > Not sure what thread you write to, but can you do concurrent > > reads/writes to sqlite3 DB? > > > > I think it is not possible as the process that writes, locks the > > file, so just keep in mind that there are severe limitations in the > > use of sqlite3 databases. Correct me if I am wrong > > > > And the single file is vulnerable to network issues. Basically the same > limitations as MS Access when used as a single-file database. Sqlite is > the right answer for most single-user non-network applications, … which sounds like a perfect fit for the recent thread on MariaDB. https://lists.debian.org/debian-user/2018/06/msg00757.html > but > not for anything more demanding. Horses for courses, as ever. … but I'm not sure whether we were given the use case in that thread. Cheers, David.
Re: sqlite database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sun, Jul 01, 2018 at 10:21:11PM +0200, deloptes wrote: > Cousin Stanley wrote: > > > As an alternative to client-server database managers, > > such as mysql, mariadb, postgresql, etc. , > > the sqlite embedded database manager is very useful > > Not sure what thread you write to, but can you do concurrent reads/writes to > sqlite3 DB? > > I think it is not possible as the process that writes, locks the file, so > just keep in mind that there are severe limitations in the use of sqlite3 > databases. Correct me if I am wrong Yes, SQLite can do concurrent. Of course, some locking/WAL scheme is used (modern SQLite can do both). There are some limitations, and for databases with many clients and things running over the network, classical client/ server DBs may have an edge: https://sqlite.org/lockingv3.html https://sqlite.org/wal.html SQLite is an awesome piece of software, arguably the one with the most complete test suite (excepting perhaps some specialized domains). Cheers - -- t -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAls5O7cACgkQBcgs9XrR2kZlSgCeP6SmpDSLDeIHOGPLCPnS/ufM CMYAnA0jJog5vLsuuDfJmi3z+wDiZpGg =IzaS -END PGP SIGNATURE-
Re: sqlite database
On Sun, 01 Jul 2018 22:21:11 +0200 deloptes wrote: > Cousin Stanley wrote: > > > As an alternative to client-server database managers, > > such as mysql, mariadb, postgresql, etc. , > > the sqlite embedded database manager is very useful > > Not sure what thread you write to, but can you do concurrent > reads/writes to sqlite3 DB? > > I think it is not possible as the process that writes, locks the > file, so just keep in mind that there are severe limitations in the > use of sqlite3 databases. Correct me if I am wrong > And the single file is vulnerable to network issues. Basically the same limitations as MS Access when used as a single-file database. Sqlite is the right answer for most single-user non-network applications, but not for anything more demanding. Horses for courses, as ever. -- Joe
Re: sqlite database
Cousin Stanley wrote: > As an alternative to client-server database managers, > such as mysql, mariadb, postgresql, etc. , > the sqlite embedded database manager is very useful Not sure what thread you write to, but can you do concurrent reads/writes to sqlite3 DB? I think it is not possible as the process that writes, locks the file, so just keep in mind that there are severe limitations in the use of sqlite3 databases. Correct me if I am wrong regards
