4078143969

Build Type

push

github

Committed by Christopher Caldwell

Commit Message

bump version

Run Details

3593 of 8015 branches covered (44.83%)

Branch coverage included in aggregate %.

15102 of 20471 relevant lines covered (73.77%)

1564.55 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

86.21

/lib/db/sqliteAdapter.ts

import { DBAdapter } from './dbAdapter';
import { DBValue } from '../db/dbValue';
import fs from 'fs';
import path from 'path';
import http from 'http';
import os from 'os';
import { GeoPackageUtilities } from '../io/geoPackageUtilities';
import { ResultSet } from './resultSet';

/**
 * This adapter uses better-sqlite3 to execute queries against the GeoPackage database
 * @see {@link https://github.com/JoshuaWise/better-sqlite3|better-sqlite3}
 */
export class SqliteAdapter implements DBAdapter {
  filePath: string | Buffer | Uint8Array;
  db: any;

  /**
   * Returns a Promise which, when resolved, returns a DBAdapter which has connected to the GeoPackage database file
   */
  async initialize(): Promise<this> {
    try {
      // eslint-disable-next-line @typescript-eslint/no-var-requires
      const Database = require('better-sqlite3');
      if (this.filePath && typeof this.filePath === 'string') {
        if (this.filePath.indexOf('http') === 0) {
          const url: string = this.filePath as string;
          return new Promise((resolve, reject) => {
            http
              .get(url, (response) => {
                if (response.statusCode !== 200) {
                  reject(new Error('Unable to reach url: ' + this.filePath));
                }
                const tmpPath = path.join(os.tmpdir(), Date.now() + Math.floor(Math.random() * 100) + '.gpkg');
                const writeStream = fs.createWriteStream(tmpPath);
                response.pipe(writeStream);
                writeStream.on('close', () => {
                  try {
                    this.db = new Database(tmpPath);
                    // verify that this is an actual database
                    this.db.pragma('journal_mode = WAL');
                    this.filePath = tmpPath;
                    resolve(this);
                  } catch (err) {
                    reject(err);
                  }
                });
              })
              .on('error', (e) => {
                reject(e);
              });
          });
        } else {
          this.db = new Database(this.filePath);
          return this;
        }
      } else if (this.filePath) {
        // write this byte array to a file then open it
        const byteArray = this.filePath;
        const tmpPath = path.join(os.tmpdir(), Date.now() + '.gpkg');
        return new Promise((resolve, reject) => {
          fs.writeFile(tmpPath, byteArray, () => {
            this.db = new Database(tmpPath);
            // verify that this is an actual database
            try {
              this.db.pragma('journal_mode = WAL');
            } catch (err) {
              reject(err);
            }
            this.filePath = tmpPath;
            resolve(this);
          });
        });
      } else {
        console.log('create in memory');
        this.db = new Database(':memory:');
        return this;
      }
    } catch (err) {
      console.log('Error opening database', err);
      throw err;
    }
  }

  /**
   *
   * @param filePath
   */
  constructor(filePath?: string | Buffer | Uint8Array) {
    this.filePath = filePath;
  }

  /**
   * Returns the size in bytes
   */
  public size(): number {
    if (typeof this.filePath === 'string') {
      const stats = fs.statSync(this.filePath);
      return stats.size;
    }
  }

  /**
   * Returns the size in bytes
   */
  public readableSize(): string {
    if (typeof this.filePath === 'string') {
      const stats = fs.statSync(this.filePath);
      return GeoPackageUtilities.formatBytes(stats.size);
    }
  }

  /**
   * Closes the connection to the GeoPackage
   */
  close(): void {
    this.db.pragma('wal_autocheckpoint=0');
    this.db.pragma('wal_checkpoint(RESTART)');
    this.db.close();
  }
  /**
   * Get the connection to the database file
   * @return {*}
   */
  getDBConnection(): any {
    return this.db;
  }

  getFunctionList(): any[] {
    return this.db.pragma('function_list');
  }

  /**
   * Returns a Buffer containing the contents of the database as a file
   */
  async export(): Promise<Uint8Array> {
    return new Promise((resolve) => {
      return fs.readFile(this.filePath as string, (err, data) => {
        resolve(data);
      });
    });
  }
  /**
   * Registers the given function so that it can be used by SQL statements
   * @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#registeroptions-function---this|better-sqlite3 register}
   * @param  {string} name               name of function to register
   * @param  {Function} functionDefinition function to register
   * @return {Adapter} this
   */
  registerFunction(name: string, functionDefinition: Function): this {
    this.db.function(name, functionDefinition);
    return this;
  }
  /**
   * Gets one row of results from the statement
   * @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#getbindparameters---row|better-sqlite3 get}
   * @param  {string} sql    statement to run
   * @param  {Array|Object} [params] bind parameters
   * @return {Object}
   */
  get(sql: string, params?: [] | Record<string, DBValue>): Record<string, DBValue> {
    const statement = this.db.prepare(sql);
    if (params) {
      return statement.get(params);
    } else {
      return statement.get();
    }
  }
  /**
   * Determines if a tableName exists in the database
   * @param {String} tableName
   * @returns {Boolean}
   */
  isTableExists(tableName: string): boolean {
    let statement = this.db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name=:name");
    const result = statement.get({ name: tableName });
    statement = null;
    return !!result;
  }
  /**
   * Gets all results from the statement in an array
   * @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#allbindparameters---array-of-rows|better-sqlite3 all}
   * @param  {String} sql    statement to run
   * @param  {Array|Object} [params] bind parameters
   * @return {Object[]}
   */
  all(sql: string, params?: [] | Record<string, DBValue>): Record<string, DBValue>[] {
    const statement = this.db.prepare(sql);
    if (params) {
      return statement.all(params);
    } else {
      return statement.all();
    }
  }
  /**
   * Returns an `Iterable` with results from the query
   * @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#iteratebindparameters---iterator|better-sqlite3 iterate}
   * @param  {String} sql    statement to run
   * @param  {Object|Array} [params] bind parameters
   * @return {Iterable.<Object>}
   */
  each(sql: string, params?: [] | Record<string, DBValue>): IterableIterator<Record<string, DBValue>> {
    const statement = this.db.prepare(sql);
    if (params) {
      return statement.iterate(params);
    } else {
      return statement.iterate();
    }
  }
  /**
   * Run the given statement, returning information about what changed.
   *
   * @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#runbindparameters---object|better-sqlite3}
   * @param  {String} sql    statement to run
   * @param  {Object|Array} [params] bind parameters
   * @return {{changes: number, lastInsertROWID: number}} object: `{ "changes": number, "lastInsertROWID": number }`
   * * `changes`: number of rows the statement changed
   * * `lastInsertROWID`: ID of the last inserted row
   */
  run(sql: string, params?: [] | Record<string, DBValue>): { changes: number; lastInsertRowid: number } {
    const statement = this.db.prepare(sql);
    if (params) {
      return statement.run(params);
    } else {
      return statement.run();
    }
  }
  /**
   * Runs the specified insert statement and returns the last inserted id or undefined if no insert happened
   * @param  {String} sql    statement to run
   * @param  {Object|Array} [params] bind parameters
   * @return {Number} last inserted row id
   */
  insert(sql: string, params?: [] | Record<string, DBValue>): number {
    const statement = this.db.prepare(sql);
    return statement.run(params).lastInsertRowid;
  }
  /**
   * Prepares a SQL statement
   * @param sql
   */
  prepareStatement(sql: string): any {
    return this.db.prepare(sql);
  }
  /**
   * Runs an insert statement with the parameters provided
   * @param  {any} statement  statement to run
   * @param  {Object|Array} [params] bind parameters
   * @return {Number} last inserted row id
   */
  bindAndInsert(statement: any, params?: [] | Record<string, DBValue>): number {
    return statement.run(params).lastInsertRowid;
  }
  /**
   * Closes a prepared statement
   * @param statement
   */
  closeStatement(statement: any): void {
    // eslint-disable-next-line @typescript-eslint/no-unused-vars
    statement = null;
  }
  /**
   * Runs the specified delete statement and returns the number of deleted rows
   * @param  {String} sql    statement to run
   * @param  {Object|Array} params bind parameters
   * @return {Number} deleted rows
   */
  delete(sql: string, params?: [] | Record<string, DBValue>): number {
    const statement = this.db.prepare(sql);
    if (params != null) {
      return statement.run(params).changes;
    } else {
      return statement.run().changes;
    }
  }
  /**
   * Drops the table
   * @param  {String} table table name
   * @return {Boolean} indicates if the table was dropped
   */
  dropTable(table: string): boolean {
    try {
      const statement = this.db.prepare('DROP TABLE IF EXISTS "' + table + '"');
      const result = statement.run();
      const vacuum = this.db.prepare('VACUUM');
      vacuum.run();
      return result.changes === 0;
    } catch (e) {
      console.error('Drop Table Error', e);
      return false;
    }
  }
  /**
   * Counts rows that match the query
   * @param  {string} tableName table name from which to count
   * @param  {string} [where]     where clause
   * @param  {Object|Array} [whereArgs] where args
   * @return {Number} count
   */
  count(tableName: string, where?: string, whereArgs?: [] | Record<string, DBValue>): number {
    let sql = 'SELECT COUNT(*) as count FROM "' + tableName + '"';
    if (where) {
      sql += ' where ' + where;
    }
    const statement = this.db.prepare(sql);
    if (whereArgs) {
      return statement.get(whereArgs).count;
    } else {
      return statement.get().count;
    }
  }

  transaction(func: Function): void {
    this.db.transaction(func)();
  }

  /**
   * Returns a result set for the given query
   */
  query(sql: string, params?: [] | Record<string, DBValue>): ResultSet {
    let statement = this.db.prepare(sql);
    let iterator;
    if (params) {
      iterator = statement.iterate(params);
    } else {
      iterator = statement.iterate();
    }
    const close = (): void => {
      if (iterator != null) {
        iterator.return();
        iterator = null;
      }
      statement = null;
    };
    return new ResultSet(iterator, { close }, this);
  }

  /**
   * Enable or disable unsafe mode
   * @param enabled
   */
  unsafe(enabled: boolean): void {
    this.db.unsafeMode(enabled);
  }
}

1	import { DBAdapter } from './dbAdapter';
2	import { DBValue } from '../db/dbValue';
3	import fs from 'fs';	1✔
4	import path from 'path';	1✔
5	import http from 'http';	1✔
6	import os from 'os';	1✔
7	import { GeoPackageUtilities } from '../io/geoPackageUtilities';	1✔
8	import { ResultSet } from './resultSet';	1✔
9
10	/**
11	* This adapter uses better-sqlite3 to execute queries against the GeoPackage database
12	* @see {@link https://github.com/JoshuaWise/better-sqlite3\|better-sqlite3}
13	*/
14	export class SqliteAdapter implements DBAdapter {	1✔
15	filePath: string \| Buffer \| Uint8Array;
16	db: any;
17
18	/**
19	* Returns a Promise which, when resolved, returns a DBAdapter which has connected to the GeoPackage database file
20	*/
21	async initialize(): Promise<this> {	1✔
22	try {	378✔
23	// eslint-disable-next-line @typescript-eslint/no-var-requires
24	const Database = require('better-sqlite3');	378✔
25	if (this.filePath && typeof this.filePath === 'string') {	378✔
26	if (this.filePath.indexOf('http') === 0) {	375✔
27	const url: string = this.filePath as string;	3✔
28	return new Promise((resolve, reject) => {	3✔
29	http	3✔
30	.get(url, (response) => {
31	if (response.statusCode !== 200) {	2✔
32	reject(new Error('Unable to reach url: ' + this.filePath));	1✔
33	}
34	const tmpPath = path.join(os.tmpdir(), Date.now() + Math.floor(Math.random() * 100) + '.gpkg');	2✔
35	const writeStream = fs.createWriteStream(tmpPath);	2✔
36	response.pipe(writeStream);	2✔
37	writeStream.on('close', () => {	2✔
38	try {	2✔
39	this.db = new Database(tmpPath);	2✔
40	// verify that this is an actual database
41	this.db.pragma('journal_mode = WAL');	2✔
42	this.filePath = tmpPath;	2✔
43	resolve(this);	2✔
44	} catch (err) {
45	reject(err);	×
46	}
47	});
48	})
49	.on('error', (e) => {
50	reject(e);	1✔
51	});
52	});
53	} else {
54	this.db = new Database(this.filePath);	372✔
55	return this;	372✔
56	}
57	} else if (this.filePath) {	3✔
58	// write this byte array to a file then open it
59	const byteArray = this.filePath;	2✔
60	const tmpPath = path.join(os.tmpdir(), Date.now() + '.gpkg');	2✔
61	return new Promise((resolve, reject) => {	2✔
62	fs.writeFile(tmpPath, byteArray, () => {	2✔
63	this.db = new Database(tmpPath);	2✔
64	// verify that this is an actual database
65	try {	2✔
66	this.db.pragma('journal_mode = WAL');	2✔
67	} catch (err) {
68	reject(err);	1✔
69	}
70	this.filePath = tmpPath;	2✔
71	resolve(this);	2✔
72	});
73	});
74	} else {
75	console.log('create in memory');	1✔
76	this.db = new Database(':memory:');	1✔
77	return this;	1✔
78	}
79	} catch (err) {
80	console.log('Error opening database', err);	×
81	throw err;	×
82	}
83	}
84
85	/**
86	*
87	* @param filePath
88	*/
89	constructor(filePath?: string \| Buffer \| Uint8Array) {
90	this.filePath = filePath;	378✔
91	}
92
93	/**
94	* Returns the size in bytes
95	*/
96	public size(): number {	1✔
97	if (typeof this.filePath === 'string') {	×
98	const stats = fs.statSync(this.filePath);	×
99	return stats.size;	×
100	}
101	}
102
103	/**
104	* Returns the size in bytes
105	*/
106	public readableSize(): string {	1✔
107	if (typeof this.filePath === 'string') {	×
108	const stats = fs.statSync(this.filePath);	×
109	return GeoPackageUtilities.formatBytes(stats.size);	×
110	}
111	}
112
113	/**
114	* Closes the connection to the GeoPackage
115	*/
116	close(): void {	1✔
117	this.db.pragma('wal_autocheckpoint=0');	346✔
118	this.db.pragma('wal_checkpoint(RESTART)');	344✔
119	this.db.close();	344✔
120	}
121	/**
122	* Get the connection to the database file
123	* @return {*}
124	*/
125	getDBConnection(): any {	1✔
126	return this.db;	×
127	}
128
129	getFunctionList(): any[] {	1✔
130	return this.db.pragma('function_list');	×
131	}
132
133	/**
134	* Returns a Buffer containing the contents of the database as a file
135	*/
136	async export(): Promise<Uint8Array> {	1✔
137	return new Promise((resolve) => {	1✔
138	return fs.readFile(this.filePath as string, (err, data) => {	1✔
139	resolve(data);	1✔
140	});
141	});
142	}
143	/**
144	* Registers the given function so that it can be used by SQL statements
145	* @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#registeroptions-function---this\|better-sqlite3 register}
146	* @param {string} name name of function to register
147	* @param {Function} functionDefinition function to register
148	* @return {Adapter} this
149	*/
150	registerFunction(name: string, functionDefinition: Function): this {	1✔
151	this.db.function(name, functionDefinition);	205✔
152	return this;	205✔
153	}
154	/**
155	* Gets one row of results from the statement
156	* @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#getbindparameters---row\|better-sqlite3 get}
157	* @param {string} sql statement to run
158	* @param {Array\|Object} [params] bind parameters
159	* @return {Object}
160	*/
161	get(sql: string, params?: [] \| Record<string, DBValue>): Record<string, DBValue> {	1✔
162	const statement = this.db.prepare(sql);	8,158✔
163	if (params) {	8,158✔
164	return statement.get(params);	8,134✔
165	} else {
166	return statement.get();	24✔
167	}
168	}
169	/**
170	* Determines if a tableName exists in the database
171	* @param {String} tableName
172	* @returns {Boolean}
173	*/
174	isTableExists(tableName: string): boolean {	1✔
175	let statement = this.db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name=:name");	11,501✔
176	const result = statement.get({ name: tableName });	11,501✔
177	statement = null;	11,501✔
178	return !!result;	11,501✔
179	}
180	/**
181	* Gets all results from the statement in an array
182	* @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#allbindparameters---array-of-rows\|better-sqlite3 all}
183	* @param {String} sql statement to run
184	* @param {Array\|Object} [params] bind parameters
185	* @return {Object[]}
186	*/
187	all(sql: string, params?: [] \| Record<string, DBValue>): Record<string, DBValue>[] {	1✔
188	const statement = this.db.prepare(sql);	25,656✔
189	if (params) {	25,656✔
190	return statement.all(params);	19,626✔
191	} else {
192	return statement.all();	6,030✔
193	}
194	}
195	/**
196	* Returns an `Iterable` with results from the query
197	* @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#iteratebindparameters---iterator\|better-sqlite3 iterate}
198	* @param {String} sql statement to run
199	* @param {Object\|Array} [params] bind parameters
200	* @return {Iterable.<Object>}
201	*/
202	each(sql: string, params?: [] \| Record<string, DBValue>): IterableIterator<Record<string, DBValue>> {	1✔
203	const statement = this.db.prepare(sql);	7,474✔
204	if (params) {	7,474✔
205	return statement.iterate(params);	7,472✔
206	} else {
207	return statement.iterate();	2✔
208	}
209	}
210	/**
211	* Run the given statement, returning information about what changed.
212	*
213	* @see {@link https://github.com/JoshuaWise/better-sqlite3/wiki/API#runbindparameters---object\|better-sqlite3}
214	* @param {String} sql statement to run
215	* @param {Object\|Array} [params] bind parameters
216	* @return {{changes: number, lastInsertROWID: number}} object: `{ "changes": number, "lastInsertROWID": number }`
217	* * `changes`: number of rows the statement changed
218	* * `lastInsertROWID`: ID of the last inserted row
219	*/
220	run(sql: string, params?: [] \| Record<string, DBValue>): { changes: number; lastInsertRowid: number } {	1✔
221	const statement = this.db.prepare(sql);	3,816✔
222	if (params) {	3,816✔
223	return statement.run(params);	91✔
224	} else {
225	return statement.run();	3,725✔
226	}
227	}
228	/**
229	* Runs the specified insert statement and returns the last inserted id or undefined if no insert happened
230	* @param {String} sql statement to run
231	* @param {Object\|Array} [params] bind parameters
232	* @return {Number} last inserted row id
233	*/
234	insert(sql: string, params?: [] \| Record<string, DBValue>): number {	1✔
235	const statement = this.db.prepare(sql);	3,071✔
236	return statement.run(params).lastInsertRowid;	3,071✔
237	}
238	/**
239	* Prepares a SQL statement
240	* @param sql
241	*/
242	prepareStatement(sql: string): any {	1✔
243	return this.db.prepare(sql);	1✔
244	}
245	/**
246	* Runs an insert statement with the parameters provided
247	* @param {any} statement statement to run
248	* @param {Object\|Array} [params] bind parameters
249	* @return {Number} last inserted row id
250	*/
251	bindAndInsert(statement: any, params?: [] \| Record<string, DBValue>): number {	1✔
252	return statement.run(params).lastInsertRowid;	100✔
253	}
254	/**
255	* Closes a prepared statement
256	* @param statement
257	*/
258	closeStatement(statement: any): void {	1✔
259	// eslint-disable-next-line @typescript-eslint/no-unused-vars
260	statement = null;	1✔
261	}
262	/**
263	* Runs the specified delete statement and returns the number of deleted rows
264	* @param {String} sql statement to run
265	* @param {Object\|Array} params bind parameters
266	* @return {Number} deleted rows
267	*/
268	delete(sql: string, params?: [] \| Record<string, DBValue>): number {	1✔
269	const statement = this.db.prepare(sql);	1,285✔
270	if (params != null) {	1,285✔
271	return statement.run(params).changes;	1,261✔
272	} else {
273	return statement.run().changes;	24✔
274	}
275	}
276	/**
277	* Drops the table
278	* @param {String} table table name
279	* @return {Boolean} indicates if the table was dropped
280	*/
281	dropTable(table: string): boolean {	1✔
282	try {	×
283	const statement = this.db.prepare('DROP TABLE IF EXISTS "' + table + '"');	×
284	const result = statement.run();	×
285	const vacuum = this.db.prepare('VACUUM');	×
286	vacuum.run();	×
287	return result.changes === 0;	×
288	} catch (e) {
289	console.error('Drop Table Error', e);	×
290	return false;	×
291	}
292	}
293	/**
294	* Counts rows that match the query
295	* @param {string} tableName table name from which to count
296	* @param {string} [where] where clause
297	* @param {Object\|Array} [whereArgs] where args
298	* @return {Number} count
299	*/
300	count(tableName: string, where?: string, whereArgs?: [] \| Record<string, DBValue>): number {	1✔
301	let sql = 'SELECT COUNT(*) as count FROM "' + tableName + '"';	1,397✔
302	if (where) {	1,397✔
303	sql += ' where ' + where;	952✔
304	}
305	const statement = this.db.prepare(sql);	1,397✔
306	if (whereArgs) {	1,397✔
307	return statement.get(whereArgs).count;	952✔
308	} else {
309	return statement.get().count;	445✔
310	}
311	}
312
313	transaction(func: Function): void {	1✔
314	this.db.transaction(func)();	76✔
315	}
316
317	/**
318	* Returns a result set for the given query
319	*/
320	query(sql: string, params?: [] \| Record<string, DBValue>): ResultSet {	1✔
321	let statement = this.db.prepare(sql);	4,076✔
322	let iterator;
323	if (params) {	4,076✔
324	iterator = statement.iterate(params);	3,583✔
325	} else {
326	iterator = statement.iterate();	493✔
327	}
328	const close = (): void => {	4,076✔
329	if (iterator != null) {	3,773!
330	iterator.return();	3,773✔
331	iterator = null;	3,773✔
332	}
333	statement = null;	3,773✔
334	};
335	return new ResultSet(iterator, { close }, this);	4,076✔
336	}
337
338	/**
339	* Enable or disable unsafe mode
340	* @param enabled
341	*/
342	unsafe(enabled: boolean): void {	1✔
343	this.db.unsafeMode(enabled);	104✔
344	}
345	}	1✔

ngageoint / geopackage-js / 4078143969

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous