Re: [sqlite] rationale for private archives of the SQLite mailing list and suppressed reply-to
Are there specific Mailman CVEs you can refer towards such that these settings could not be opened up? The read-only archives appear to be static files, so there is no additional security issue that isn't already presented by the existing cgi-bin already open for public access. Similarly for the reply-to issue, only email addresses present in messages that have been approved to be part of the list would be subject to an automatic reply. As a project maintainer myself, I wouldn't want to link these simple improvements to a wholesale replacement of the mailing list system. GNU Mailman is still very widely used and IMO does the job very well, and if the archives were opened up then they would be indexable by search engines such as Google. On Fri, Oct 11, 2019, at 11:56 AM, Brannon King wrote: > I agree that Mailman is archaic. I worry about the security on it. I don't > enjoy using 3rd-party mirrors for searching it. I'd like to propose that we > upgrade to something more modern and secure like Sympa or mlmmj, or even a > more drastic system upgrade to something like Redmine -- a project > tracker + forum. > > On Fri, Oct 11, 2019 at 9:41 AM Mike Bayer wrote: > > > ...I would ask them to consider that making the mailing list opaque leads > > to user questions that are entirely related to SQLite and nothing else > > being posted in downstream project communities instead, which pushes the > > community response burden downwards. > > > ___ > 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] rationale for private archives of the SQLite mailing list and suppressed reply-to
Apologies if this has been asked before but what is the reason that the SQLite mailing list archives, linked at http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users, are private for members only in order to be read? The archives can be viewed at a mirror such as http://sqlite.1065341.n5.nabble.com/ so there is no confidential information to be concealed in any case. Additionally, it appears that the SQLite Mailman server is configured to suppress "reply-to" to the sender of an email. I've had a confused user waiting for two weeks to receive a reply to their message that was approved and replied to within a day, but since they did not subscribe and the reply was not copied to the original sender as is common for Mailman mailing lists, they never saw it. I have a suspicion that the SQLite community has some rationales for these decisions however I would ask them to consider that making the mailing list opaque leads to user questions that are entirely related to SQLite and nothing else being posted in downstream project communities instead, which pushes the community response burden downwards. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Formal specification for sqlite3 file format
The British National Archives has registered SQLite3 among its identified file formats (see PRONOM at http://www.nationalarchives.gov.uk/pronom/Default.aspx) and given it the following PUID: fmt/729. The U.S. Library of Congress has included SQLite3 in its Recommended Formats Statement for the digital preservation of datasets (http://www.loc.gov/preservation/resources/rfs/data.html). Hope this helps. Am 08.07.2016 um 04:17 schrieb Richard Hipp: On 7/7/16, Henry Chan wrote: Dear all, I'm wondering if there is any "formal" specification for the sqlite3 application format, as in standardized as an ISO/IEC standard, ECMA standard or RFC? How does one go about getting a long-established file format such as SQLite "formalized"? This is similar to .DOCX being registered as both ISO/IEC 29500 and ECMA-376; JSON being RFC 7159 and ECMA-404, XML being a W3C specification with various parts as RFC. Without a "formal" specification, it makes it (unnecessarily) "hard" for governments to adopt SQLite3's database file as an application format as an official formats for information exchange. Such a "formal" specification would simply be a stripped down version of Section 1 of http://www.sqlite.org/fileformat2.html, where all the reasons for quirks or definite behavior are stripped and all values could be described in the database' committed state. Yours, Henry ___ 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] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/06/2016 10:33 AM, Richard Hipp wrote: > On 4/4/16, Mike Bayer wrote: >> The "type" column in PRAGMA table_info() is now a blank string when the >> target object is a view in 3.12.0. In 3.11.0 and prior versions, the >> typing information is returned, >> > > I think the problem is fixed by this checkin: > https://www.sqlite.org/src/info/fb555c3c2af7f5e6 > > Please confirm. Can confirm my user's issue is resolved by version 3.12.1 thanks for the quick turnaround! >
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/06/2016 02:13 AM, Hick Gunter wrote: > You are hopefully aware of the fact that SQLite associates type with the > actual values and not the containers(columns) used to hold these values? This > means that a data object of any type may be held/returned in a column, > irrespective of the declared type (which, for expressions, is NULL). > > What would your date routine do with the string 12.17.9.17.15? it would raise an exception on the invalid date format, not any different from the string->date converters that take effect when you enter a date on a web form. Even though SQLite allows any kind of data to be stored in any row regardless of type affinity, in practice, applications typically remain faithful to the type of data they plan to store within columns. That is, even though SQLite has a dynamic per-row typing model, most of the world very much wants it to behave as a drop-in for MySQL or Postgresql and their applications assume schemas have fixed datatypes just like all other relational databases. > > -Urspr?ngliche Nachricht- > Von: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Mike > Bayer > Gesendet: Dienstag, 05. April 2016 21:46 > An: sqlite-users at mailinglists.sqlite.org > Betreff: Re: [sqlite] regression in 3.12.0 vs. 3.11.0, column type > information in PRAGMA missing > > > > On 04/05/2016 01:19 PM, Richard Hipp wrote: >> On 4/4/16, Mike Bayer wrote: >>> The "type" column in PRAGMA table_info() is now a blank string when >>> the target object is a view in 3.12.0. In 3.11.0 and prior versions, >>> the typing information is returned, >>> >> >> This could easily be considered a bug fix rather than a regression. >> Please explain why you think it is important to know the "type" of a >> column in a view? > > As others have noted, in the application space we often use these names as > suggestions for how data from such a column is to be handled once > transmitted outside of the SQLite layer. The most prominent example is > date values, where we apply converters on both sides of the data to convert > between language-specific date objects and a string representation on the > SQLite side. > > For example, here is the Python standard library SQLite database adapter: > > https://docs.python.org/2/library/sqlite3.html#converting-sqlite-values-to-custom-python-types > > In this example, we see the use of the constant sqlite3.PARSE_DECLTYPES, > which indicates "parse the name of the declared type delivered by SQLite > within a result set, in order to apply a converter". This specific > implementation is parsing the type affinity as delivered in the result set, > so is not impacted by this change. However, other database abstraction > systems rely upon the use of so-called "table metadata" > gathered up front about tables and views in order to know about the datatypes > that are expected from particular column names; on SQLite this > relies upon "pragma table_info()" to collect that information.If > it's no longer present, such systems would require the user to explicitly > state datatypes in the case of views on the application side, or to be > modified to rely upon typing information when a result set is received rather > than based on the schema of the constructs themselves. >But like in so many other cases, database-agnostic systems are designed > around the way all other relational databases behave, which in this area is > that of the "fixed type per-column" model where there's never been an issue > knowing the types that will be received from a table or view. That SQLite > also delivers these fields via "pragma table_info()" just made it that much > more possible for abstraction layers to emulate similar behavior in SQLite. > > > >> >> There are further inconsistencies here. Example: >> >> CREATE TABLE t1(x INTEGER); >> CREATE VIEW v1 AS SELECT x FROM t1; >> CREATE VIEW v2(x) AS SELECT x FROM t1; >> PRAGMA table_info('v1'); >> PRAGMA table_info('v2'); >> >> As of version 3.12.0, the two pragma's give the same answer. but in >> version 3.11.0, they were different. Which of the two answers >> returned by 3.11.0 is correct? > > Looking from the perspective of the consuming application, ultimately the > INTEGER affinity value is what's delivered so in an ideal world people would > expect the answer to be INTEGER. > > However I would reiterate the point of my original email, which is that if > this chang
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/05/2016 01:19 PM, Richard Hipp wrote: > On 4/4/16, Mike Bayer wrote: >> The "type" column in PRAGMA table_info() is now a blank string when the >> target object is a view in 3.12.0. In 3.11.0 and prior versions, the >> typing information is returned, >> > > This could easily be considered a bug fix rather than a regression. > Please explain why you think it is important to know the "type" of a > column in a view? As others have noted, in the application space we often use these names as suggestions for how data from such a column is to be handled once transmitted outside of the SQLite layer. The most prominent example is date values, where we apply converters on both sides of the data to convert between language-specific date objects and a string representation on the SQLite side. For example, here is the Python standard library SQLite database adapter: https://docs.python.org/2/library/sqlite3.html#converting-sqlite-values-to-custom-python-types In this example, we see the use of the constant sqlite3.PARSE_DECLTYPES, which indicates "parse the name of the declared type delivered by SQLite within a result set, in order to apply a converter". This specific implementation is parsing the type affinity as delivered in the result set, so is not impacted by this change. However, other database abstraction systems rely upon the use of so-called "table metadata" gathered up front about tables and views in order to know about the datatypes that are expected from particular column names; on SQLite this relies upon "pragma table_info()" to collect that information.If it's no longer present, such systems would require the user to explicitly state datatypes in the case of views on the application side, or to be modified to rely upon typing information when a result set is received rather than based on the schema of the constructs themselves. But like in so many other cases, database-agnostic systems are designed around the way all other relational databases behave, which in this area is that of the "fixed type per-column" model where there's never been an issue knowing the types that will be received from a table or view. That SQLite also delivers these fields via "pragma table_info()" just made it that much more possible for abstraction layers to emulate similar behavior in SQLite. > > There are further inconsistencies here. Example: > > CREATE TABLE t1(x INTEGER); > CREATE VIEW v1 AS SELECT x FROM t1; > CREATE VIEW v2(x) AS SELECT x FROM t1; > PRAGMA table_info('v1'); > PRAGMA table_info('v2'); > > As of version 3.12.0, the two pragma's give the same answer. but in > version 3.11.0, they were different. Which of the two answers > returned by 3.11.0 is correct? Looking from the perspective of the consuming application, ultimately the INTEGER affinity value is what's delivered so in an ideal world people would expect the answer to be INTEGER. However I would reiterate the point of my original email, which is that if this change in behavior is intentional, it should be documented in the change notes at http://sqlite.org/releaselog/3_12_0.html ; I've looked through every line item and see none that suggest a change in how PRAGMA behaves with regards to views. I also notice that there's now a category of change called "Potentially Disruptive Change"; I hope to illustrate here that whether or not the 3.11 or 3.12 behavior is chosen, this change is definitely "potentially disruptive" and should be noted as such.Or even that this area of behavior could be explicitly described at http://sqlite.org/datatype3.html ("type affinities do not transfer to views at the view definition level"). > > Or, consider this situation: > > CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL); > CREATE VIEW v3 AS SELECT w+x+y+z FROM t2; > > What should "PRAGMA table_info('v3')" report as the column type? In the case of views propagating type affinities at the definition level, ideally we'd take the summation of the type affinities themselves assuming each column is populated with a type-compliant data value and return that, which in this case would be NUMERIC, since all math operators coerce their values to NUMERIC first. > > It seems to me that the most consistent answer is that the "type" of > columns in a VIEW should always be an empty string. So to give some background where I'm coming from on this, I don't actually need SQLite to behave either way, I'm the creator and maintainer of one of many database abstraction layers that provides a SQLite translation layer and it's *my* users who will come to me with
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
On 04/05/2016 10:43 AM, Cezary H. Noweta wrote: > > IMHO, this described by you behavior can be considered as a bug in > ``PRAGMA table_info'', which takes column's affinity from ``CREATE > TABLE'' command only as for now. OK...so *that* is a change. Is *that* by design, and if so should that be described here http://sqlite.org/releaselog/3_12_0.html ? Otherwise if this is an unexpected regression then I hope a ticket can be filed. > > -- best regards > > Cezary H. Noweta > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing
The "type" column in PRAGMA table_info() is now a blank string when the target object is a view in 3.12.0. In 3.11.0 and prior versions, the typing information is returned, Version 3.11.0: SQLite version 3.11.0 2016-02-15 17:29:24 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE users ( ...> user_id INTEGER NOT NULL ...> ); sqlite> CREATE VIEW users_v AS SELECT * FROM users; sqlite> PRAGMA table_info("users_v"); 0|user_id|INTEGER|0||0 Version 3.12.0: SQLite version 3.12.0 2016-03-29 10:14:15 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE users ( user_id INTEGER NOT NULL ) ...>...>...> ; sqlite> CREATE VIEW users_v AS SELECT * FROM users; sqlite> PRAGMA table_info("users_v"); 0|user_id||0||0 if it is by design that views no longer carry typing information, this is a major behavioral change and no mention of it occurs in the release notes for 3.12.0, so at the very least this change should be documented if expected.
[sqlite] sqlite max query parameters differs on Snow Leopard?
All - I posted this query on stackoverflow, but I'm probably more likely to get an answer here. http://stackoverflow.com/questions/10332668/sqlite-max-query-parameters-differs-on-snow-leopard An overgeneralization in some code I'm writing revealed some differing behavior between the Python sqlite bindings on Windows and on MacOS X Snow Leopard. The issue relates to the maximum permitted number of query parameters. sqlite has a hard limit on the number of query parameters, of 999. This is set in headers which are not part of the public header file, and the runtime gives you the ability to lower the limit, but not exceed the hard limit. The maximum value in the Python sqlite3 module on Snow Leopard (and Lion as well, apparently) is not 999, but 500,000, and I can't figure out for the life of me how this is possible. I considered the possibility that the Python wrapper is doing the parameter substitution itself and passing full statements to sqlite3; but as far as I can tell from the source code, that's not happening. I also considered the possibility that the implementation of sqlite3 on Snow Leopard was compiled from slightly different source code; but it turns out that Apple publishes its open-source modifications (see http://opensource.apple.com/source/SQLite/SQLite-74.11/) and the parameter limit in their source is identical to the default. Does anyone have any ideas about how this might be happening? As an added bonus, if any of the sqlite developers are listening, it would be excellent if these max limits were programmatically inspectable (e.g., sqlite3_get_hard_parameter_limit() or something like that). Thanks in advance - Sam Bayer The MITRE Corporation s...@mitre.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users