• What’s new in SQLite 3.35
  • Original author: Anton Zhiyanov
  • The Nuggets translation Project
  • Permanent link to this article: github.com/xitu/gold-m…
  • Translator: Hoarfroster
  • Proofread by: Kamly

What’s new in SQLite version 3.35?

SQLite developers generally prefer to implement the core components of the database, and to an outside observer, nothing really changes. The year 2020 is a pleasant exception — SQLite offers many nice features for the average user, such as auto-generate columns, UPDATE FROM, and the excellent.mode box feature in the Shell.

2021 will most likely continue in this vein, and here’s what’s available in version 3.35:

  • Built-in SQL mathematical functions before were added ️ ️
  • Added toALTER TABLE DROP COLUMNSupport. ❗
  • inDELETE,INSERT 和 UPDATEAdd pairs to statementsRETURNINGClause support.
  • CTE common table expressions are supportedMATERIALIZED 和 NOT_MATERIALIZEDPrompt.

Below are some details about each feature.

Mathematical function

Over the years, numerous users have petitioned SQLite developers to include mathematically basic functions such as SQRT (), log(), and pow(). And the response was always the same:

SQLite is called Lite for a reason. If you need these functions, add them yourself.

This is indeed an understandable position. But refuse to add square roots? But does it also implement window functions, recursive queries, and other advanced SQL features? Are you serious?

Perhaps SQLite developers prefer to focus on features that big customers are willing to pay for. Anyway, 20 years later, we now have mathematical functions!

Here’s the full list:

acos(X)
acosh(X)
asin(X)
asinh(X)
atan(X)
atan2(X,Y)
ceil(X)
ceiling(X)
cos(X)
cosh(X)
degrees(X)
exp(X)
floor(X)
ln(X)
log(B,X)
log(X)
log10(X)
log2(X)
mod(X,Y)
pi()
pow(X,Y)
power(X,Y)
radians(X)
sin(X)
sinh(X)
sqrt(X)
tan(X)
tanh(X)
trunc(X)
Copy the code

Remove the Column

Probably the second biggest source of user misery. We are free to create as many columns as we want, but we can’t delete them, which is really annoying. You said you want to delete the column? Make a copy of the table that does not have this column, then delete the old table.

Now, at last, the pain is gone! ALTER TABLE DROP COLUMN, we have been waiting for you so hard.

To delete columns, however, SQLite must completely rewrite the table contents — so it’s not fast. It’s definitely better than not having this feature!

RETURNING statement

DELETE, INSERT, and UPDATE queries now support returning rows they deleted, added, or changed separately.

For example, return the ID of the new record:

create table users (
  id integer primary key,
  first_name text,
  last_name text
);

insert into users (first_name, last_name)
values ('Jane'.'Doe')
returning id;
Copy the code

Or return an item with a price increase:

update products set price = price 1.10
where price < = 99.99
returning name, price as new_price;
Copy the code

Materialized CTE common table expression

Ctes (common table expressions) are a great way to make queries more expressive. For example, count the number of cities built per century:

create table city(
  city text,
  timezone text,
  geo_lat real,
  geo_lon real,
  population integer,
  foundation_year integer
);

-- insert data ...

with history as (
  select
    city,
    (foundation_year/100)+1 as century
  from city
)
select
  century || ' century' as dates,
  count(*) as city_count
from history
group by century
order by century desc;
Copy the code

If the same CTE statement appears more than once in the query, SQLite evaluates the CTE multiple times. For larger tables, this operation can be slow.

For instantiated Ctes, SQLite executes the query only once, writes down the results, and does not recalculate in the same query:

with history as materialized (
  select...).select.from history where.except
select.from history where. ;Copy the code

Four major functions, one-time satisfaction, no needle prick for Halloween

SQLite 3.35.0 Release Notice (Draft) | Download the latest version of SQLite

If you find any mistakes in your translation or other areas that need to be improved, you are welcome to the Nuggets Translation Program to revise and PR your translation, and you can also get the corresponding reward points. The permanent link to this article at the beginning of this article is the MarkDown link to this article on GitHub.

The Nuggets Translation Project is a community that translates quality Internet technical articles from English sharing articles on nuggets. The content covers Android, iOS, front-end, back-end, blockchain, products, design, artificial intelligence and other fields. If you want to see more high-quality translation, please continue to pay attention to the Translation plan of Digging Gold, the official Weibo, Zhihu column.