SQLite Forum

Doc enhancements for new release
Login

Doc enhancements for new release

(1) By Ryan Smith (cuz) on 2021-03-02 14:51:14 [link]

Firstly, this is shaping up to be a landmark release - well done and also thank you for the great work.

Some suggested documentation enhancements for the current DRAFT docs.

In [draft/lang_mathfunc.html](https://sqlite.org/draft/lang_mathfunc.html):

- In the preamble:  
  *Current*: "..or strings or blobs that looks like integers..."  
  *Suggest*: "..or strings or blobs that look like integers..."  
  *Reason* : Grammar

- ceil(x)  
  *Current*: "Return the next larger representable integer values above X."  
  *Suggest*: "Return the first representable integer value equal to or above X."  
  *Reason* : ceil(7) == 7 (not some next value above 7).  

- floor(x)
  *Current*: "Return the next representable integer value that is less than X."  
  *Suggest*: "Return the first representable integer value equal to or less than X."  
  *Reason* : floor(7) == 7 (not some next value below 7).  

- trunc(x)  
  *Current*: "Return the first representable integers on the line from X to 0.0. This is similar to ceiling(X) and floor(X) except that it always rounds toward zero."  
  *Suggest*: "Removes the fraction of a number leaving only the Integer part. This is similar to floor(X) for positive numbers, and ceiling(X) for negative numbers, in that it always rounds toward zero."  
  *Reason* : It feels more accurate to the math *and* to the meaning of "Truncate" where this function name is born from.  

- pow(X,Y)  
  *Current*: "Compute X raised to Y-th power."  
  *Suggest*: "Compute X raised to the power Y."  
  *Reason* : Style - Not really wrong, but every other reference to Y on that document is the pure form, like the suggested.  


In [draft/lang_altertable.html](https://sqlite.org/draft/lang_altertable.html#altertabdropcol):

- ALTER TABLE DROP COLUMN (In the reasons it may fail):  
  *Current*: "The column appears in a table CHECK constraint. However, the column being deleted can be used in a column CHECK constraint because the column CHECK constraint is dropped together with the column itself."  
  *Suggested*: "The column appears in a table CHECK constraint. However, the drop is allowed if the only such CHECK constraint is declared within the column itself, since such a constraint is dropped along with the column."  
  *Reason* : Clarity


Thanks,
Ryan

(2) By Larry Brasfield (larrybr) on 2021-03-02 16:39:22 in reply to 1 [link]

Ryan, I agree with your mathfunc pickups. However, on:

> ALTER TABLE DROP COLUMN (In the reasons it may fail):
Current: "The column appears in a table CHECK constraint. However, the column being deleted can be used in a column CHECK constraint because the column CHECK constraint is dropped together with the column itself."
Suggested: "The column appears in a table CHECK constraint. However, the drop is allowed if the only such CHECK constraint is declared within the column itself, since such a constraint is dropped along with the column."

I see no lack of clarity in the original. The original "However" clause could perhaps be parenthetical, or dropped altogether as redundant.  However, your "However" clause, ostensibly referring to "such CHECK constraint", is actually (or most obviously) referring to "a table CHECK constraint" and going on to say what may happen if **that** is declared within the column itself, (making it a column constraint rather than a table constraint.) For a qualifier that is not strictly necessary, that seems to be a distraction.

Care to propose a better qualifier?

(3) By Ryan Smith (cuz) on 2021-03-02 23:33:40 in reply to 2 [link]

> Care to propose a better qualifier?

Nope, I'm agreeing with your assessment - thank you for it.  

Here is the part I wanted to improve clarity on but failed miserably to do:

A TABLE constraint is declared after the columns and if it contains a reference to the column being dropped, then sure enough that is an error (the original text makes this part clear enough).

The original "However" text following that suggests: If the constraint referencing the column is a COLUMN constraint, i.e. declared as part of a column, then it is allowed to be dropped because the constraint will fizzle together with the column.

BUT there is a logic error - if the constraint *IS* a column constraint but as part of another column than the one being dropped, it will still FAIL even though it is a "COLUMN" constraint.  

By way of Example:

```
-- Declaring a table t with only 1 column constraint, no table constraints:

CREATE TABLE t(
  x INT NOT NULL,
  y INT CHECK (y > x)
);
```
Dropping column y - no problem.  
Dropping column x - problem - will FAIL.


So I think we should make clear that the drop will fail if:

- The column appears in ANY TABLE-constraint,  
  OR
- The column appears in ANY COLUMN-constraint when declared in another column than the one being dropped.

If the column is referenced in any constraint, it will succeed if, and only if, that constraint is a COLUMN constraint of specifically ITSELF.


Allow me to re-attempt a suitable description:


Original:  
"The column appears in a table CHECK constraint. However, the column being deleted can be used in a column CHECK constraint because the column CHECK constraint is dropped together with the column itself."

New Suggestion:  
"The column appears in a table CHECK constraint or a column CHECK constraint of another column. A CHECK constraint declared on the column itself will not prevent it since that constraint will be dropped with the column."


I'm still not loving it 100% - Perhaps someone here with better English skills than myself could assist with a better-sounding phrase that is both accurate and succinct.

(4) By Larry Brasfield (larrybr) on 2021-03-03 00:43:22 in reply to 3

Now that I see what you were trying to convey, I think this says it:

(among several causes of ALTER TABLE DROP COLUMN failures:)
\#. The column name appears in either a table CHECK constraint or any column CHECK constraint other than one for the column to be dropped.

There is no need to mention what succeeds; the item in question need only cover this one category of failure causes: Column is involved in a CHECK constraint not being dropped along with the column.

(5.1) By ddevienne on 2021-03-03 10:00:36 edited from 5.0 in reply to 4 [link]

I think it's really a pity CASCADE support is not added ATM.  
Hopefully it can be added later on, in a not too distant future.

Obviously SQLite already knows what dependents exists, otherwise  
RENAME COLUMN wouldn't work as it does, so deleting those as well  
when the DROP COLUMN is qualified with an explicit CASCADE, should  
have been fairly easy I suspect.

That's both standard behavior, but also very common behavior in my  
experience, to use CASCADE during drops. FWIW.

> PS: If SQLite had a pragma to list dependencies between objects of  
  the schema, then at least one would be able to programmatically  
  do the CASCADE, by deleting those first in topological order, but  
  w/o it, and w/o CASCADE, one has to be intimately aware of schema  
  intricacies, which in large schemas, or schemas you inherited and  
  didn't design yourself (or both!), is not always that easy, far from it.

(7) By Larry Brasfield (larrybr) on 2021-03-03 16:37:49 in reply to 5.1 [link]

There are issues that go beyond implementation difficulty.

As you suggest, intimate awareness of schema intricacy cannot be blithely assumed. Hence, just dropping indexes and constraints because they mention a dropped column may be a disfavor; they may be valued DB features. With the present behavior, those intricate dependencies are at least brought to the DBA's attention, who can then decide whether some preliminary DROP operations are needed. I would not want to argue the position that the DDL implementing code can reliably make a better judgment on such cascade actions than an alerted human being.

The idea of a table-like pragma listing drop-blocking dependencies is worth consideration. The library's evolution will undoubtedly be done carefully, with due consideration to the myriad use cases that can be anticipated.

(8) By ddevienne on 2021-03-03 17:00:29 in reply to 7 [link]

You seem to forget that most if not all RDBMSs support DROP CASCADE.  
Including the PostgreSQL *benchmark*.  Again, CASCADE is **opt-in**.   
If you don't like it, simply don't use it. But don't try to *save* us from ourselves, please :)

Of course, this is moot since there's no DROP CASCADE atm.

(6) By Ryan Smith (cuz) on 2021-03-03 10:07:08 in reply to 4 [link]

I like it - short, unambiguous, and 100% true. My favourite way for documentation to be.

Thanks!