[sqlite] Filtering groups by non-grouped field.
The following code does not work, but gives an idea what I want to do: create table t (a, b); select group_concat(b) as list from t group by a having ?1 in (list); i.e. how to select only the groups that contain some value in the set of values in a column not specified in group by clause. The only way I was able to do it is by subquery. Something like this: select (select group_concat(b) from t t1 where t1.a = t2.a) as list from t t2 where b = ?1; -- John Found ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
Incidentally, Bedrock is built on a blockchain as well -- though I agree with the sentiment that blockchain isn't actually new at all, and not that big of a deal. More information is here: http://bedrockdb.com/blockchain.html Hope you enjoy it! -david On Thu, Oct 11, 2018 at 3:06 PM R Smith wrote: > > > WARNING: the following sentence will be claimed to be controversial: > > > > No database based on SQL is truly relational. > > LOL - who would claim that to be controversial? > > It doesn't spur controversy... > > It's worthy of a shrug at best, perhaps a "So what?". > > > It sounds like a deepity - much like any of these: > - Nothing is ever really True... > - Is reality even really real? > - No ice-cream machine ever makes TRUE ice-cream. > > An SQL database is deemed "Relational" when it can communicate mildly > relational data using mildly relational (but mathematically sound) > methods. It doesn't need to be (nor claim to be) the Almighty keeper of > all relationality, nor even simply conform to various specific > interpretations of the word "Relation". > > In case the point still eludes: We call an SSL hand-shake such because > it behaves by mutual agreement - much like a human hand-shake - but just > because we call it so, doesn't bestow upon it a necessity to behave in > every way like a literal hand-shake, lest some pipe-smoking mountain > wisdom gazes far in the distance while stroking grey beard slowly and > declaring: "no SSL hand-shake is truly hand-shaky". > > > > > PS: While I feel some ambivalence towards the subject, I was nodding in > agreement with most of your post, till that line appeared. :) > > PPS: Apologies for inventing some words there > > PPPS: Thumbs up for the Bedrock suggestion from another post - that > system really rocks. > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
WARNING: the following sentence will be claimed to be controversial: No database based on SQL is truly relational. LOL - who would claim that to be controversial? It doesn't spur controversy... It's worthy of a shrug at best, perhaps a "So what?". It sounds like a deepity - much like any of these: - Nothing is ever really True... - Is reality even really real? - No ice-cream machine ever makes TRUE ice-cream. An SQL database is deemed "Relational" when it can communicate mildly relational data using mildly relational (but mathematically sound) methods. It doesn't need to be (nor claim to be) the Almighty keeper of all relationality, nor even simply conform to various specific interpretations of the word "Relation". In case the point still eludes: We call an SSL hand-shake such because it behaves by mutual agreement - much like a human hand-shake - but just because we call it so, doesn't bestow upon it a necessity to behave in every way like a literal hand-shake, lest some pipe-smoking mountain wisdom gazes far in the distance while stroking grey beard slowly and declaring: "no SSL hand-shake is truly hand-shaky". PS: While I feel some ambivalence towards the subject, I was nodding in agreement with most of your post, till that line appeared. :) PPS: Apologies for inventing some words there PPPS: Thumbs up for the Bedrock suggestion from another post - that system really rocks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
On Oct 11, 2018, at 2:25 PM, Eric wrote: > > On Thu, 11 Oct 2018 10:20:08 -0600, Warren Young wrote: >> On Oct 11, 2018, at 12:26 AM, Darren Duncan wrote: > 8>< > >>> This makes me think that it would be useful, if it doesn't already, >>> for Fossil to have something analogous to a database replication feature. >> >> That's pretty much what Fossil *is*: a replicated database. [snip] > This is nothing like database replication as generally understood, which > is commonly done by applying redo (write-ahead) logs from the other side, > but it is exactly what Fossil needs. I agree that what Fossil does is not the same thing as general-purpose relational database replication, but it doesn’t need to be general-purpose. Fossil’s synchronization mechanism is custom-tailored to its specific purpose. If you were hoping to use Fossil as a general-purpose SQLite replication system, then yeah, it’s not going to work for you. You might want to look at Bedrock: http://bedrockdb.com/ > The interlocking of artifacts by cryptographic hashes does seem very much > like the same idea as blockchain Relevant: https://fossil-scm.org/index.html/doc/trunk/www/blockchain.md I prefer the term Merkle tree, as it gets you away from all the hype around cryptocurrencies, but drh prefers blockchain, so that’s what I use now when talking about Fossil. https://en.wikipedia.org/wiki/Merkle_tree ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find key,value duplicates but with differing values
You are correct. Value should/ could be inside count(), but not in group. Sent from my T-Mobile 4G LTE Device Original message From: R Smith Date: 10/11/18 4:29 PM (GMT-05:00) To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Find key,value duplicates but with differing values On 2018/10/11 9:53 PM, Roman Fleysher wrote: > It is hard for me to tell which is index, which is value and so forth in your > example, but how about this single select: > > SELECT DISTINCT key, value FROM theTable; > > This lists all distinct key-value possibilities. Or, > > SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) > GROUP BY key, value HAVING count() > 1; > > This lists all key-value pairs with more than one value for the key. If I may - this won't work directly as-is since the query: SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) GROUP BY key, value HAVING count() > 1; will group by Key,Value and then output which Key-value pairs exhibit more than one instance (count) - which is physically impossible since counting the duplicate values from a distinct set is like asking the number of genders among American presidents before 2018 The answer is always 1. If, in the outer query, you Select for (and group by) Key only, then it works. > > Roman > > > From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf > of David Raymond [david.raym...@tomtom.com] > Sent: Thursday, October 11, 2018 12:23 PM > To: SQLite mailing list > Subject: Re: [sqlite] Find key,value duplicates but with differing values > > Maybe > > ... > group by partId, name > having count(distinct xmd.value) > 1; > > ? > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Dominique Devienne > Sent: Thursday, October 11, 2018 12:00 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Find key,value duplicates but with differing values > > I can find duplicates fine: > > select xmd.partId, parts.title, xmd.name, > count(*) "#dupplicates", > group_concat(xmd.value) "values", > group_concat(xmd.idx) "indexes" >from extra_meta_data xmd >join parts on parts.id = xmd.partId >group by partId, name > having "#dupplicates" > 1; > > but most actual duplicates have the same value, so are harmless. > so I'd like to select only the xmd.name rows which have differing values. > > Knowing that there can be more than 2 duplicates, i.e. a slef-join is not > enough I think. > Any hints on how to go about this problem please? Thanks, --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
On Thu, 11 Oct 2018 14:37:47 -0600, "Keith Medcalf" wrote: > > Balderdash. > > > The interlocking of artifacts by cryptographic hashes does seem very much > > like the same idea as blockchain, which Wikipedia says was invented in > > 2008. It is interesting that the first Fossil checkin was 21 July, 2007 > > (and the first git checkin was 7 April, 2005). > > Hashed Double Linked Lists (blockchain) was invented in the 1950's. > Just many of you are too young to remember your history. I may actually be too old to remember! Do you happen to have a reference for it? My actual point was that Fossil is now described as blockchain when it predates what we (currently) call blockchain, and so do other similar things. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replication
Balderdash. > The interlocking of artifacts by cryptographic hashes does seem very much > like the same idea as blockchain, which Wikipedia says was invented in > 2008. It is interesting that the first Fossil checkin was 21 July, 2007 > (and the first git checkin was 7 April, 2005). Hashed Double Linked Lists (blockchain) was invented in the 1950's. Just many of you are too young to remember your history. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find key,value duplicates but with differing values
On 2018/10/11 9:53 PM, Roman Fleysher wrote: It is hard for me to tell which is index, which is value and so forth in your example, but how about this single select: SELECT DISTINCT key, value FROM theTable; This lists all distinct key-value possibilities. Or, SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) GROUP BY key, value HAVING count() > 1; This lists all key-value pairs with more than one value for the key. If I may - this won't work directly as-is since the query: SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) GROUP BY key, value HAVING count() > 1; will group by Key,Value and then output which Key-value pairs exhibit more than one instance (count) - which is physically impossible since counting the duplicate values from a distinct set is like asking the number of genders among American presidents before 2018 The answer is always 1. If, in the outer query, you Select for (and group by) Key only, then it works. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of David Raymond [david.raym...@tomtom.com] Sent: Thursday, October 11, 2018 12:23 PM To: SQLite mailing list Subject: Re: [sqlite] Find key,value duplicates but with differing values Maybe ... group by partId, name having count(distinct xmd.value) > 1; ? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, October 11, 2018 12:00 PM To: General Discussion of SQLite Database Subject: [sqlite] Find key,value duplicates but with differing values I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1; but most actual duplicates have the same value, so are harmless. so I'd like to select only the xmd.name rows which have differing values. Knowing that there can be more than 2 duplicates, i.e. a slef-join is not enough I think. Any hints on how to go about this problem please? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Replication
Picking up a couple of sentences from a different thread, just for a reference point really, please feel free to ignore ... On Thu, 11 Oct 2018 10:20:08 -0600, Warren Young wrote: > On Oct 11, 2018, at 12:26 AM, Darren Duncan wrote: 8>< >> This makes me think that it would be useful, if it doesn't already, >> for Fossil to have something analogous to a database replication feature. > > That's pretty much what Fossil *is*: a replicated database. Most of > it happens to be blockchain structured, rather than relational table > structured, but much of the table-structured data is also synchronized > between a clone and its parent. Database replication that could be applied to any SQLite database might be a very useful thing, but the Fossil synchronization algorithm is not much help for that, and that sort of database replication is not a lot of use for Fossil. A Fossil repository is a collection of immutable artifacts each named by a cryptographic hash of what it contains, which is metadata and/or content, the latter being a file, or a list of the artifacts in a checkin, or some other things. Artifacts are tied together by the metadata, which contains the names of other artifacts. There are no deletions, and no updates in place (well, there is shunning, but that is a special case for a special purpose). There are quite a few types of artifact. The artifacts are stored as blobs in a table in a SQLite database. There are other tables in the database, but almost all of them can be, and are, populated from the metadata in the artifacts. Fossil synchronization is essentially an exchange of artifacts, followed by each repository populating tables from the metadata from the newly-received artifacts. The "exchange" part of the synchronization knows nothing about tables, rows, and columns. The use of a "relational" database to store a Fossil repository is sort-of incidental. This is nothing like database replication as generally understood, which is commonly done by applying redo (write-ahead) logs from the other side, but it is exactly what Fossil needs. The interlocking of artifacts by cryptographic hashes does seem very much like the same idea as blockchain, which Wikipedia says was invented in 2008. It is interesting that the first Fossil checkin was 21 July, 2007 (and the first git checkin was 7 April, 2005). WARNING: the following sentence will be claimed to be controversial: No database based on SQL is truly relational. But if it was, you could treat a row as an artifact named by table-name+primary-key, ban deletions and updates-in-place, and use Fossil-style synchronization for database replication. Not realistic for most existing databases though. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find key,value duplicates but with differing values
It is hard for me to tell which is index, which is value and so forth in your example, but how about this single select: SELECT DISTINCT key, value FROM theTable; This lists all distinct key-value possibilities. Or, SELECT key, value FROM (SELECT DISTINCT key, value FROM theTable) GROUP BY key, value HAVING count() > 1; This lists all key-value pairs with more than one value for the key. Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of David Raymond [david.raym...@tomtom.com] Sent: Thursday, October 11, 2018 12:23 PM To: SQLite mailing list Subject: Re: [sqlite] Find key,value duplicates but with differing values Maybe ... group by partId, name having count(distinct xmd.value) > 1; ? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, October 11, 2018 12:00 PM To: General Discussion of SQLite Database Subject: [sqlite] Find key,value duplicates but with differing values I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1; but most actual duplicates have the same value, so are harmless. so I'd like to select only the xmd.name rows which have differing values. Knowing that there can be more than 2 duplicates, i.e. a slef-join is not enough I think. Any hints on how to go about this problem please? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] support for SHOW?
For interactive work in the CLI anyway there's the .tables command and the .schema command https://www.sqlite.org/cli.html#querying_the_database_schema The first will show all the tables and views The second will give you the SQL stored for the table and its indexes, etc. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz Sent: Thursday, October 11, 2018 3:11 PM To: SQLite mailing list Subject: [sqlite] support for SHOW? Hello, I'd like to ask whether it would be possible to add support for MySQL-style SHOW command, i.e. - SHOW TABLES [FROM db_name] - SHOW COLUMNS FROM table I know that this information can be retrieved in other ways, but imho SHOW is an easy-to-remember statement and could simplify things a bit. For SHOW TABLES e.g. an alias to "SELECT name FROM sqlite_master WHERE type='table'" would be sufficient. Kind regards, Thomas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] support for SHOW?
Hello, I'd like to ask whether it would be possible to add support for MySQL-style SHOW command, i.e. - SHOW TABLES [FROM db_name] - SHOW COLUMNS FROM table I know that this information can be retrieved in other ways, but imho SHOW is an easy-to-remember statement and could simplify things a bit. For SHOW TABLES e.g. an alias to "SELECT name FROM sqlite_master WHERE type='table'" would be sufficient. Kind regards, Thomas ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find key,value duplicates but with differing values
On 2018/10/11 5:59 PM, Dominique Devienne wrote: I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1; but most actual duplicates have the same value, so are harmless. so I'd like to select only the xmd.name rows which have differing values. Knowing that there can be more than 2 duplicates, i.e. a slef-join is not enough I think. Any hints on how to go about this problem please? Thanks, --DD 2 Ways to solve this that comes to mind initially... Option 1 - Self join on the Index field and dissimilar second field, and group, Option 2 - Use the fancy new Window functions to distill the real duplicates from the don't-matter duplicates and then count the non-distinct items. These two methods shown below: -- SQLite version 3.25.1 [ Release: 2018-09-18 ] on SQLitespeed version 2.1.1.37. -- CREATE TABLE t(a,b); INSERT INTO t(a,b) VALUES (1,300), (1,100), -- This is a real duplicate (1,300), (1,300), (2,500), (2,500), -- This duplicate does not matter (3,400), (3,500), -- This is again a real duplicate (3,400) ; -- This means we need to be told about Index 1 and 3 which contain real duplicates. -- Option 1: SELECT DISTINCT t1.a FROM t AS t1 JOIN t AS t2 ON t2.a=t1.a AND t2.b<>t1.b ; -- a -- -- 1 -- 3 -- Option 2: SELECT a FROM (SELECT DISTINCT a,MAX(b) OVER (PARTITION BY a,b) FROM t) GROUP BY a HAVING COUNT(*)>1 ; -- a -- -- 1 -- 3 HTH, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11
Amen. I'm about ready to unsubscribe from this list and not come back until it transitions to a forum (which is the interface I prefer anyways) because of all this nattering clogging up my inbox the last few days. I thought I was a grumpy old fart set in my ways, but I don't have anything on some of you. On Thu, Oct 11, 2018, 7:51 AM Balaji Ramanathan wrote: > > > >2. Re: SQLite mailing list > > > > > > The 1990's called and they want their mailing lists back. So, let us > switch to 21st century technology already. Count me in as an enthusiastic > YES vote for proper forums (including subforums - so that I can read what I > want and skip the rest instead of drowning in irrelevant emails with > hundreds or thousands of lines of included, quoted emails). Or maybe we can > have everybody fax everybody else? An entire mailing list with no > technical discussion except whether to retain the mailing list or not. I > can only shake my head in amazement . . . > > Balaji Ramanahan > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On Oct 11, 2018, at 12:06 AM, Random Coder wrote: > > On Wed, Oct 10, 2018 at 3:45 PM Warren Young wrote: >> The salt is the project code combined with the user ID, not a secret >> per-user salt. Both of those values are publicly visible, but it does >> defeat rainbow table attacks, which is the main point of salting. > > This does not prevent new rainbow tables from being generated Since part of the salt is the user name, that requires a “rainbow table” per user, which means it isn’t much of a rainbow table at all. Basically, it devolves to a brute-force hash attack. > You're using a single iteration of the (technically insecure) hash > function to generate the password If you take the faux “rainbow table” approach — which again is really just brute force in this case — the known weaknesses in SHA-1 don’t matter. You still have to generate half of the SHA-1 possibilities on average to brute force one of these passwords. In [one experiment][1] a single large GPU generated SHA-1 hashes at a rate of 160 million per second. To generate 2^80 hashes (half the SHA-1 space) you’d need 4.6x10^39 seconds, or about 1.45x10^32 years. Current top-end GPUs are about twice as fast as the one used in that experiment, and you could rack a bunch of those up, but even a time reduction of 1000x doesn’t make the attack practical. Instead, you might try to take advantage of the SHA-1 weaknesses to generate a second preimage, which in this case amounts to a password that happens to work even though it isn’t actually the password known to the legitimate user. I have no idea how difficult that is, but it’s probably still awfully difficult. To pull that off, you need a copy of the user table, which is not synchronized down to clones unless you’re the Fossil “setup” user, who is all-powerful and doesn’t need to resort to such things to break into the repository in the first place. An attacker without such privileges would have to break into the server hosting the Fossil instance, at which point he wouldn’t *need* to break the password hash: he could just modify the user table directly! [1]: https://security.stackexchange.com/a/3450 > creating a rainbow table is much > less computationally hard than if a modern password hashing function > had been used. Okay, so replace the algorithm with N-thousand rounds of PBKDF2 and whatever hash algo you like. How does it practically change what I’ve said above? > I'd be curious if someone's taken the effort to setup hashcat with > your rule set What rules? As I said, Fossil currently has no restrictions on the password. Fossil assumes you will use good passwords for purely selfish reasons, and don’t need to be forced to do so. And again, hashcat is a silly way to attack Fossil. If you have access to the Fossil user table, you can just edit the database and insert whatever hash you like. Or, insert content directly into the block chain. Or add users with arbitrary permissions. Or... The primary attack the Fossil password system fends off is the remote one, which is gated by the maximum number of password tries allowed per second. I don’t know of any internal limits on this in Fossil, so it amounts to the maximum number of HTTP connections the host machine can process per second, which will be orders of magnitude lower than the hash rates above. If that still bothers you, put Fossil behind an HTTP proxy and set up fail2ban on the access log. With 5 passwords allowed per user per second, an 8 character random password takes decades to break. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Find key,value duplicates but with differing values
Maybe ... group by partId, name having count(distinct xmd.value) > 1; ? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, October 11, 2018 12:00 PM To: General Discussion of SQLite Database Subject: [sqlite] Find key,value duplicates but with differing values I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1; but most actual duplicates have the same value, so are harmless. so I'd like to select only the xmd.name rows which have differing values. Knowing that there can be more than 2 duplicates, i.e. a slef-join is not enough I think. Any hints on how to go about this problem please? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11
On Thu, 11 Oct 2018 09:51:15 -0500, Balaji Ramanathan wrote: >> >>2. Re: SQLite mailing list >> > > The 1990's called and they want their mailing lists back. So, let us > switch to 21st century technology already. New is not necessarily better, old is not necessarily worse. Those who forget history are condemned to repeat it. > Count me in as an enthusiastic YES vote for proper forums (including > subforums - so that I can read what I want and skip the rest instead > of drowning in irrelevant emails with hundreds or thousands of lines of > included, quoted emails). And yet you read the digest! A very good way to be overwhelmed, one of the good things about mailing lists that you can see everything separately and only read the ones you are interested in. And you want sub-forums, so we'll all have to go through each sub-forum looking for things of interest. > Or maybe we can have everybody fax everybody else? Ha Ha. > An entire mailing list with no technical discussion except whether to > retain the mailing list or not. I can only shake my head in amazement > . . . That's just silly, if you got individual mails you could ignore the threads you don't want to read, there really is plenty of technical discussion. But since you replied to the digest your post won't be included in the right thread, and nor will this response - rgh! Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Find key, value duplicates but with differing values
Two nested selects The inner select groups by partId, name, value The outer select groups by partId, name -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Donnerstag, 11. Oktober 2018 18:00 An: General Discussion of SQLite Database Betreff: [EXTERNAL] [sqlite] Find key,value duplicates but with differing values I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1; but most actual duplicates have the same value, so are harmless. so I'd like to select only the xmd.name rows which have differing values. Knowing that there can be more than 2 duplicates, i.e. a slef-join is not enough I think. Any hints on how to go about this problem please? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On Oct 11, 2018, at 12:26 AM, Darren Duncan wrote: > > On 2018-10-10 1:36 PM, Eric wrote: >> Too much overhead, how often must I clone ... > > This makes me think that it would be useful, if it doesn't already, for > Fossil to have something analogous to a database replication feature. That’s pretty much what Fossil *is*: a replicated database. Most of it happens to be blockchain structured, rather than relational table structured, but much of the table-structured data is also synchronized between a clone and its parent. Fossil forum content is just more of the same of what Fossil already stores, so it syncs down to a clone just the same as anything else you’ve got stored in Fossil. The only differences between a Fossil repository clone and its parent are some local-only settings and security-sensitive information such as the user table. If you clone as a sufficiently privileged user, you can even pull down the user table. This is useful when replicating Fossil across multiple sites for backup and site fail-over purposes. The SQLite and Fossil project repos are currently replicated across 3 different hosts in this way. > A bit like a mailing list but that the sender and client are both instances > of Fossil. Fossil allows you to open your local repository in a browser and insert content into the forum locally, then sync the content up to the remote repository. That requires a user capability that had not been given to my user on the fossil-scm.org/forum instance, last I checked. I assume that no one else but drh can do this at the moment on that instance. However, I’ve done it on one of my own Fossil repositories, just now: https://tangentsoft.com/mysqlpp/forumpost/f8b7fc2ca9 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Find key,value duplicates but with differing values
I can find duplicates fine: select xmd.partId, parts.title, xmd.name, count(*) "#dupplicates", group_concat(xmd.value) "values", group_concat(xmd.idx) "indexes" from extra_meta_data xmd join parts on parts.id = xmd.partId group by partId, name having "#dupplicates" > 1; but most actual duplicates have the same value, so are harmless. so I'd like to select only the xmd.name rows which have differing values. Knowing that there can be more than 2 duplicates, i.e. a slef-join is not enough I think. Any hints on how to go about this problem please? Thanks, --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11
> >2. Re: SQLite mailing list > > The 1990's called and they want their mailing lists back. So, let us switch to 21st century technology already. Count me in as an enthusiastic YES vote for proper forums (including subforums - so that I can read what I want and skip the rest instead of drowning in irrelevant emails with hundreds or thousands of lines of included, quoted emails). Or maybe we can have everybody fax everybody else? An entire mailing list with no technical discussion except whether to retain the mailing list or not. I can only shake my head in amazement . . . Balaji Ramanahan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On Wed, 10 Oct 2018 16:00:02 -0600, Warren Young wrote: 8>< There comes a point in any written discussion where point-by-point answers become a risk to sanity. I think we are there. It seems that a Fossil forum will someday be able to behave like a mailing list. Good, but if the price is moderation for everything that comes in by email, then I won't be happy without seeing the moderation guidelines. I will still prefer mailing lists. In the post I am now answering you have told me several things I already know, and missed the point a couple of times. That is merely an observation, I can't claim to never miss the point. I contributed to Fossil in the early days, until Richard moved it out of GPL. I didn't object to that per se, but the then contributor agreement was something I could not sign. The current one is OK, but I'm doing too many other things now. > https://fossil-scm.org/forum/forumpost/ba1144bc9f Thanks for that link, I will read it properly. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
Darren Duncan wrote: > On 2018-10-10 10:51 AM, Chris Green wrote: > > Warren Young wrote: > >> Fossil forum email alerts include the full content of the message. > > That's great! Especially if the alert email subject includes the forum > thread > subject. > > That said, I consider it critical that these alert emails can also send my > own > posts in the forum and not just others. If they don't send for EVERY post, > the > emails aren't suitable for reading / backing up a thread in one place. > > > And can you then simply 'reply' from your E-Mail client? If not then > > it doesn't really help much. > > Actually it helps a lot. I think in practice most people using this forum > would > be reading a lot more than they post. So you can do your majority action of > reading in your email client with the forum alerts. In the rare situation > where > you want to reply, then you just switch over to the web forum. Yes, and there lies the rub, it's a window-swapping, mouse clicking hassle. If it was a mailing list I'd simply hit L[ist reply] and that would be it. -- Chris Green · ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On Thursday, 11 October, 2018 00:22, Darren Duncan wrote: >On 2018-10-10 12:26 PM, Keith Medcalf wrote: >> And if you think that I am going to create YET ANOTHER LOGIN and >> YET ANOTHER PASSWORD just to use some crappy forum software, you have >> another think coming. > What do you think password managers are for? You mean the equivalent of sticky-notes on the monitor? Sorry, do not use them, ever. > Proper security means having a different password everywhere that uses > passwords, and one presumably already has dozens or more of those, > so if they use a password manager, the SQLite forum is just another > one it automatically handles. -- Darren Duncan There are only a very small number of passwords that are to useful things. Those are remembered and not written down or stored anywhere. There are a lot of "junk" passwords which are merely generated. Many of the latter have "stupid" rules, but even so if they are compromised, who gives a crap? Having a requirement to "change" or other idiotic composition rules automatically puts the password in the latter camp. So do you intend the forum password to be one of the former or one of the latter? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On 2018-10-10 1:36 PM, Eric wrote: Too much overhead, how often must I clone ... This makes me think that it would be useful, if it doesn't already, for Fossil to have something analogous to a database replication feature. A bit like a mailing list but that the sender and client are both instances of Fossil. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On 2018-10-10 12:26 PM, Keith Medcalf wrote: And if you think that I am going to create YET ANOTHER LOGIN and YET ANOTHER PASSWORD just to use some crappy forum software, you have another think coming. What do you think password managers are for? Proper security means having a different password everywhere that uses passwords, and one presumably already has dozens or more of those, so if they use a password manager, the SQLite forum is just another one it automatically handles. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On 2018-10-10 10:51 AM, Chris Green wrote: Warren Young wrote: Fossil forum email alerts include the full content of the message. That's great! Especially if the alert email subject includes the forum thread subject. That said, I consider it critical that these alert emails can also send my own posts in the forum and not just others. If they don't send for EVERY post, the emails aren't suitable for reading / backing up a thread in one place. And can you then simply 'reply' from your E-Mail client? If not then it doesn't really help much. Actually it helps a lot. I think in practice most people using this forum would be reading a lot more than they post. So you can do your majority action of reading in your email client with the forum alerts. In the rare situation where you want to reply, then you just switch over to the web forum. (And assuming you then get a copy back in your email, so its like you wrote it with email.) -- Darren Duncan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]
On Wed, Oct 10, 2018 at 3:45 PM Warren Young wrote: > Fossil currently has no restrictions on password length[1] or content. The > input text is simply salted, hashed, and inserted into the user table: > [...] > The salt is the project code combined with the user ID, not a secret per-user > salt. Both of those values are publicly visible, but it does defeat rainbow > table attacks, which is the main point of salting. This does not prevent new rainbow tables from being generated, and since: https://fossil-scm.org/fossil/file?udc=1=436-441=src%2Fsha1.c You're using a single iteration of the (technically insecure) hash function to generate the password, creating a rainbow table is much less computationally hard than if a modern password hashing function had been used. I'd be curious if someone's taken the effort to setup hashcat with your rule set and see how quickly it can brute force it's way to the plaintext. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
