Repository: nodejs/node
Pull request: sqlite: enable common flags

This PR enables flags that are common to other sqlite players in Node.js ecosystem:

- https://github.com/TryGhost/node-sqlite3/blob/528e15ae605bac7aab8de60dd7c46e9fdc1fffd0/deps/sqlite3.gyp#L91;
- https://github.com/WiseLibs/better-sqlite3/blob/c12f1091db645d701c9ee9f37e9472fbe9087a80/deps/defines.gypi. 

This is related to https://github.com/nodejs/node/issues/56476, even though it does not enable the RBU extension.

I see this as a good step toward the stabilization since it will make it easier for people from other dependencies.

Changed files:
filename: deps/sqlite/sqlite.gyp
patch: @@ -15,9 +15,16 @@
       'defines': [
         'SQLITE_DEFAULT_MEMSTATUS=0',
         'SQLITE_ENABLE_COLUMN_METADATA',
+        'SQLITE_ENABLE_DBSTAT_VTAB',
+        'SQLITE_ENABLE_FTS3',
+        'SQLITE_ENABLE_FTS3_PARENTHESIS',
+        'SQLITE_ENABLE_FTS5',
+        'SQLITE_ENABLE_GEOPOLY',
         'SQLITE_ENABLE_MATH_FUNCTIONS',
+        'SQLITE_ENABLE_PREUPDATE_HOOK',
+        'SQLITE_ENABLE_RBU',
+        'SQLITE_ENABLE_RTREE',
         'SQLITE_ENABLE_SESSION',
-        'SQLITE_ENABLE_PREUPDATE_HOOK'
       ],
       'include_dirs': ['.'],
       'sources': [

filename: deps/sqlite/unofficial.gni
patch: @@ -9,9 +9,16 @@ template("sqlite_gn_build") {
     include_dirs = [ "." ]
     defines = [
       "SQLITE_ENABLE_COLUMN_METADATA",
+      "SQLITE_ENABLE_DBSTAT_VTAB",
+      "SQLITE_ENABLE_FTS3",
+      "SQLITE_ENABLE_FTS3_PARENTHESIS",
+      "SQLITE_ENABLE_FTS5",
+      "SQLITE_ENABLE_GEOPOLY",
       "SQLITE_ENABLE_MATH_FUNCTIONS",
-      "SQLITE_ENABLE_SESSION",
       "SQLITE_ENABLE_PREUPDATE_HOOK",
+      "SQLITE_ENABLE_RBU",
+      "SQLITE_ENABLE_RTREE",
+      "SQLITE_ENABLE_SESSION",
     ]
   }
 

filename: test/parallel/test-sqlite.js
patch: @@ -105,14 +105,6 @@ test('PRAGMAs are supported', (t) => {
   );
 });
 
-test('math functions are enabled', (t) => {
-  const db = new DatabaseSync(':memory:');
-  t.assert.deepStrictEqual(
-    db.prepare('SELECT PI() AS pi').get(),
-    { __proto__: null, pi: 3.141592653589793 },
-  );
-});
-
 test('Buffer is supported as the database path', (t) => {
   const db = new DatabaseSync(Buffer.from(nextDb()));
   t.after(() => { db.close(); });
@@ -142,7 +134,6 @@ test('URL is supported as the database path', (t) => {
   );
 });
 
-
 suite('URI query params', () => {
   const baseDbPath = nextDb();
   const baseDb = new DatabaseSync(baseDbPath);
@@ -210,3 +201,134 @@ suite('URI query params', () => {
     });
   });
 });
+
+suite('SQL APIs enabled at build time', () => {
+  test('math functions are enabled', (t) => {
+    const db = new DatabaseSync(':memory:');
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT PI() AS pi').get(),
+      { __proto__: null, pi: 3.141592653589793 },
+    );
+  });
+
+  test('dbstat is enabled', (t) => {
+    const db = new DatabaseSync(nextDb());
+    t.after(() => { db.close(); });
+    db.exec(`
+      CREATE TABLE t1 (key INTEGER PRIMARY KEY);
+    `);
+
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT * FROM dbstat WHERE name = \'t1\'').get(),
+      {
+        __proto__: null,
+        mx_payload: 0,
+        name: 't1',
+        ncell: 0,
+        pageno: 2,
+        pagetype: 'leaf',
+        path: '/',
+        payload: 0,
+        pgoffset: 4096,
+        pgsize: 4096,
+        unused: 4088
+      },
+    );
+  });
+
+  test('fts3 is enabled', (t) => {
+    const db = new DatabaseSync(':memory:');
+    db.exec(`
+      CREATE VIRTUAL TABLE t1 USING fts3(content TEXT);
+      INSERT INTO t1 (content) VALUES ('hello world');
+    `);
+
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT * FROM t1 WHERE t1 MATCH \'hello\'').all(),
+      [
+        { __proto__: null, content: 'hello world' },
+      ],
+    );
+  });
+
+  test('fts3 parenthesis', (t) => {
+    const db = new DatabaseSync(':memory:');
+    db.exec(`
+      CREATE VIRTUAL TABLE t1 USING fts3(content TEXT);
+      INSERT INTO t1 (content) VALUES ('hello world');
+    `);
+
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT * FROM t1 WHERE content MATCH \'(groupedterm1 OR groupedterm2) OR hello world\'').all(),
+      [
+        { __proto__: null, content: 'hello world' },
+      ],
+    );
+  });
+
+  test('fts4 is enabled', (t) => {
+    const db = new DatabaseSync(':memory:');
+    db.exec(`
+      CREATE VIRTUAL TABLE t1 USING fts4(content TEXT);
+      INSERT INTO t1 (content) VALUES ('hello world');
+    `);
+
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT * FROM t1 WHERE t1 MATCH \'hello\'').all(),
+      [
+        { __proto__: null, content: 'hello world' },
+      ],
+    );
+  });
+
+  test('fts5 is enabled', (t) => {
+    const db = new DatabaseSync(':memory:');
+    db.exec(`
+      CREATE VIRTUAL TABLE t1 USING fts5(content);
+      INSERT INTO t1 (content) VALUES ('hello world');
+    `);
+
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT * FROM t1 WHERE t1 MATCH \'hello\'').all(),
+      [
+        { __proto__: null, content: 'hello world' },
+      ],
+    );
+  });
+
+  test('rtree is enabled', (t) => {
+    const db = new DatabaseSync(':memory:');
+    db.exec(`
+      CREATE VIRTUAL TABLE t1 USING rtree(id, minX, maxX, minY, maxY);
+      INSERT INTO t1 (id, minX, maxX, minY, maxY) VALUES (1, 0, 1, 0, 1);
+    `);
+
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT * FROM t1 WHERE minX < 0.5').all(),
+      [
+        { __proto__: null, id: 1, minX: 0, maxX: 1, minY: 0, maxY: 1 },
+      ],
+    );
+  });
+
+  test('rbu is enabled', (t) => {
+    const db = new DatabaseSync(':memory:');
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT sqlite_compileoption_used(\'SQLITE_ENABLE_RBU\') as rbu_enabled;').get(),
+      { __proto__: null, rbu_enabled: 1 },
+    );
+  });
+
+  test('geopoly is enabled', (t) => {
+    const db = new DatabaseSync(':memory:');
+    db.exec(`
+      CREATE VIRTUAL TABLE t1 USING geopoly(a,b,c);
+      INSERT INTO t1(_shape) VALUES('[[0,0],[1,0],[0.5,1],[0,0]]');
+    `);
+
+    t.assert.deepStrictEqual(
+      db.prepare('SELECT rowid FROM t1 WHERE geopoly_contains_point(_shape, 0, 0)').get(),
+      { __proto__: null, rowid: 1 },
+    );
+  });
+});