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

87.24

/lib/db/sqliteQueryBuilder.ts

import { DBValue } from './dbValue';
import { GeoPackageException } from '../geoPackageException';
import { SQLUtils } from './sqlUtils';

/**
 * Utility class to build sql queries
 * @class
 */
export class SqliteQueryBuilder {
  /**
   * Replaces all whitespace in a column name with underscores
   * @param  {string} columnName column name to fix
   * @return {string}
   */
  static fixColumnName(columnName: string): string {
    return columnName.replace(/\W+/g, '_');
  }

  /**
   * Builds a query
   * @param  {Boolean} distinct whether query should be distinct or not
   * @param  {string} tables   table names to query, added to the query from clause
   * @param  {string[]} [columns]  columns to query for
   * @param  {string} [where]    where clause
   * @param  {string} [join]     join clause
   * @param  {string} [groupBy]  group by clause
   * @param  {string} [having]   having clause
   * @param  {string} [orderBy]  order by clause
   * @param  {Number} [limit]    limit
   * @param  {Number} [offset]   offset
   * @return {string}
   */
  static buildQuery(
    distinct: boolean,
    tables: string,
    columns?: string[],
    where?: string,
    join?: string,
    groupBy?: string,
    having?: string,
    orderBy?: string,
    limit?: number,
    offset?: number,
  ): string {
    let query = '';
    if (SqliteQueryBuilder.isEmpty(groupBy) && !SqliteQueryBuilder.isEmpty(having)) {
      throw new GeoPackageException('Illegal Arguments: having clauses require a groupBy clause');
    }

    query += 'select ';
    if (distinct) {
      query += 'distinct ';
    }
    if (columns && columns.length) {
      query = SqliteQueryBuilder.appendColumnsToString(columns, query);
    } else {
      query += '* ';
    }

    query += 'from ' + tables;
    if (join) {
      query += ' ' + join;
    }
    query = SqliteQueryBuilder.appendClauseToString(query, ' where ', where);
    query = SqliteQueryBuilder.appendClauseToString(query, ' group by ', groupBy);
    query = SqliteQueryBuilder.appendClauseToString(query, ' having ', having);
    query = SqliteQueryBuilder.appendClauseToString(query, ' order by ', orderBy);
    query = SqliteQueryBuilder.appendClauseToString(query, ' limit ', limit);
    query = SqliteQueryBuilder.appendClauseToString(query, ' offset ', offset);

    return query;
  }

  /**
   * Builds a count statement
   * @param  {string} tables table names to query for
   * @param  {string} [where]  where clause
   * @return {string} count statement
   */
  static buildCount(tables: string, where?: string): string {
    let query = 'select count(*) as count from ' + tables;
    query = SqliteQueryBuilder.appendClauseToString(query, ' where ', where);
    return query;
  }

  /**
   * Builds an insert statement using the properties of the object
   * @param  {string} table  table to insert into
   * @param  {Object} object object to insert
   * @return {string} insert statement
   */
  static buildInsert(table: string, object: any): string {
    if (object.columnNames) {
      return SqliteQueryBuilder.buildInsertFromColumnNames(table, object);
    }
    let insert = 'insert into ' + SQLUtils.quoteWrap(table) + ' (';
    let keys = '';
    let values = '';
    let first = true;
    for (const key in object) {
      if (Object.prototype.hasOwnProperty.call(object, key) && object[key] !== undefined) {
        if (!first) {
          keys += ', ';
          values += ', ';
        }
        first = false;
        keys += key;
        values += '$' + SqliteQueryBuilder.fixColumnName(key);
      }
    }

    insert += keys + ') values (' + values + ')';
    return insert;
  }

  /**
   * Builds an insert statement from the object.getColumnNames method
   * @param  {string} table  table to insert into
   * @param  {Object} object object with a getColumnNames method
   * @return {string} insert statement
   */
  static buildInsertFromColumnNames(table: string, object: any): string {
    let insert = 'insert into ' + SQLUtils.quoteWrap(table) + ' (';
    let keys = '';
    let values = '';
    let first = true;
    const columnNames = object.columnNames;
    for (let i = 0; i < columnNames.length; i++) {
      const key = columnNames[i];
      if (!first) {
        keys += ', ';
        values += ', ';
      }
      first = false;
      keys += '"' + key + '"';
      values += '$' + SqliteQueryBuilder.fixColumnName(key);
    }

    insert += keys + ') values (' + values + ')';
    return insert;
  }

  /**
   * Builds an update or insert object to bind to a statement
   * @param  {Object} object object to create bind parameters from
   * @return {Object} bind parameters
   */
  static buildUpdateOrInsertObject(object: any): any {
    const insertOrUpdate: { [key: string]: DBValue } = {};
    if (object.columnNames) {
      const columnNames = object.columnNames;
      for (let i = 0; i < columnNames.length; i++) {
        insertOrUpdate[SqliteQueryBuilder.fixColumnName(columnNames[i])] = object.toDatabaseValue(columnNames[i]);
      }
    } else {
      for (const key in object) {
        if (Object.prototype.hasOwnProperty.call(object, key) && object[key] !== undefined) {
          if (object.toDatabaseValue) {
            insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = object.toDatabaseValue(key);
          } else {
            if (typeof object[key] === 'boolean') {
              insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = object[key] ? 1 : 0;
            } else if (object[key] instanceof Date) {
              insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = new Date(object[key]).toISOString();
            } else {
              insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = object[key];
            }
          }
        }
      }
    }
    return insertOrUpdate;
  }

  /**
   * Builds an update statement
   * @param  {string} table     table to update
   * @param  {Object} values    object with values to update
   * @param  {string} [where]     where clause
   * @param  {Array|Object} [whereArgs] where bind parameters
   * @return {Object} object with a sql property containing the update statement and an args property with bind arguments
   */
  static buildUpdate(
    table: string,
    values: Record<string, DBValue>,
    where: string,
    whereArgs: DBValue[] | DBValue,
  ): { sql: string; args: DBValue[] } {
    const args: DBValue[] = [];
    let update = 'update ' + table + ' set ';
    let first = true;
    for (const columnName in values) {
      if (!first) {
        update += ', ';
      }
      first = false;
      update += '"' + columnName + '"';
      args.push(values[columnName]);
      update += '=?';
    }
    if (whereArgs) {
      if (whereArgs instanceof Array) {
        for (let i = 0; i < whereArgs.length; i++) {
          args.push(whereArgs[i]);
        }
      } else {
        args.push(whereArgs);
      }
    }
    if (where) {
      update += ' where ';
      update += where;
    }
    return {
      sql: update,
      args: args,
    };
  }

  /**
   * Builds an update from an object
   * @param  {string} table  table name to update
   * @param  {Object} object object with values to update
   * @return {string} update statement
   */
  static buildObjectUpdate(table: string, object: any): string {
    let update = 'update ' + table + ' set ';
    let first = true;
    if (object.columnNames) {
      const columnNames = object.columnNames;

      for (let i = 0; i < columnNames.length; i++) {
        const key = columnNames[i];
        if (!first) {
          update += ', ';
        }
        first = false;
        update += '"' + key + '"=';
        update += '$' + SqliteQueryBuilder.fixColumnName(key);
      }
    } else {
      for (const prop in object) {
        if (Object.prototype.hasOwnProperty.call(object, prop)) {
          if (!first) {
            update += ', ';
          }
          first = false;
          update += '"' + prop + '"=';
          update += '$' + SqliteQueryBuilder.fixColumnName(prop);
        }
      }
    }

    return update;
  }

  private static appendClauseToString(string: string, name: string, clause?: DBValue): string {
    if (clause) {
      string += name + clause;
    }
    return string;
  }

  private static appendColumnsToString(columns: string[], string: string): string {
    if (!columns || !columns.length) return string;
    string += SqliteQueryBuilder.columnToAppend(columns[0]);
    for (let i = 1; i < columns.length; i++) {
      string += ', ' + SqliteQueryBuilder.columnToAppend(columns[i]);
    }
    string += ' ';
    return string;
  }

  private static columnToAppend(column: string): string {
    return column.indexOf('*') !== -1 ? column : '"' + column + '"';
  }

  private static isEmpty(string: string | undefined): boolean {
    return !string || string.length === 0;
  }
}

1	import { DBValue } from './dbValue';
2	import { GeoPackageException } from '../geoPackageException';	1✔
3	import { SQLUtils } from './sqlUtils';	1✔
4
5	/**
6	* Utility class to build sql queries
7	* @class
8	*/
9	export class SqliteQueryBuilder {	1✔
10	/**
11	* Replaces all whitespace in a column name with underscores
12	* @param {string} columnName column name to fix
13	* @return {string}
14	*/
15	static fixColumnName(columnName: string): string {	1✔
16	return columnName.replace(/\W+/g, '_');	23,787✔
17	}
18
19	/**
20	* Builds a query
21	* @param {Boolean} distinct whether query should be distinct or not
22	* @param {string} tables table names to query, added to the query from clause
23	* @param {string[]} [columns] columns to query for
24	* @param {string} [where] where clause
25	* @param {string} [join] join clause
26	* @param {string} [groupBy] group by clause
27	* @param {string} [having] having clause
28	* @param {string} [orderBy] order by clause
29	* @param {Number} [limit] limit
30	* @param {Number} [offset] offset
31	* @return {string}
32	*/
33	static buildQuery(	1✔
34	distinct: boolean,
35	tables: string,
36	columns?: string[],
37	where?: string,
38	join?: string,
39	groupBy?: string,
40	having?: string,
41	orderBy?: string,
42	limit?: number,
43	offset?: number,
44	): string {
45	let query = '';	20,326✔
46	if (SqliteQueryBuilder.isEmpty(groupBy) && !SqliteQueryBuilder.isEmpty(having)) {	20,326!
47	throw new GeoPackageException('Illegal Arguments: having clauses require a groupBy clause');	×
48	}
49
50	query += 'select ';	20,326✔
51	if (distinct) {	20,326✔
52	query += 'distinct ';	3✔
53	}
54	if (columns && columns.length) {	20,326✔
55	query = SqliteQueryBuilder.appendColumnsToString(columns, query);	2,581✔
56	} else {
57	query += '* ';	17,745✔
58	}
59
60	query += 'from ' + tables;	20,326✔
61	if (join) {	20,326✔
62	query += ' ' + join;	3✔
63	}
64	query = SqliteQueryBuilder.appendClauseToString(query, ' where ', where);	20,326✔
65	query = SqliteQueryBuilder.appendClauseToString(query, ' group by ', groupBy);	20,326✔
66	query = SqliteQueryBuilder.appendClauseToString(query, ' having ', having);	20,326✔
67	query = SqliteQueryBuilder.appendClauseToString(query, ' order by ', orderBy);	20,326✔
68	query = SqliteQueryBuilder.appendClauseToString(query, ' limit ', limit);	20,326✔
69	query = SqliteQueryBuilder.appendClauseToString(query, ' offset ', offset);	20,326✔
70
71	return query;	20,326✔
72	}
73
74	/**
75	* Builds a count statement
76	* @param {string} tables table names to query for
77	* @param {string} [where] where clause
78	* @return {string} count statement
79	*/
80	static buildCount(tables: string, where?: string): string {	1✔
81	let query = 'select count(*) as count from ' + tables;	43✔
82	query = SqliteQueryBuilder.appendClauseToString(query, ' where ', where);	43✔
83	return query;	43✔
84	}
85
86	/**
87	* Builds an insert statement using the properties of the object
88	* @param {string} table table to insert into
89	* @param {Object} object object to insert
90	* @return {string} insert statement
91	*/
92	static buildInsert(table: string, object: any): string {	1✔
93	if (object.columnNames) {	2,024✔
94	return SqliteQueryBuilder.buildInsertFromColumnNames(table, object);	1✔
95	}
96	let insert = 'insert into ' + SQLUtils.quoteWrap(table) + ' (';	2,023✔
97	let keys = '';	2,023✔
98	let values = '';	2,023✔
99	let first = true;	2,023✔
100	for (const key in object) {	2,023✔
101	if (Object.prototype.hasOwnProperty.call(object, key) && object[key] !== undefined) {	50,033✔
102	if (!first) {	11,588✔
103	keys += ', ';	9,565✔
104	values += ', ';	9,565✔
105	}
106	first = false;	11,588✔
107	keys += key;	11,588✔
108	values += '$' + SqliteQueryBuilder.fixColumnName(key);	11,588✔
109	}
110	}
111
112	insert += keys + ') values (' + values + ')';	2,023✔
113	return insert;	2,023✔
114	}
115
116	/**
117	* Builds an insert statement from the object.getColumnNames method
118	* @param {string} table table to insert into
119	* @param {Object} object object with a getColumnNames method
120	* @return {string} insert statement
121	*/
122	static buildInsertFromColumnNames(table: string, object: any): string {	1✔
123	let insert = 'insert into ' + SQLUtils.quoteWrap(table) + ' (';	1✔
124	let keys = '';	1✔
125	let values = '';	1✔
126	let first = true;	1✔
127	const columnNames = object.columnNames;	1✔
128	for (let i = 0; i < columnNames.length; i++) {	1✔
129	const key = columnNames[i];	5✔
130	if (!first) {	5✔
131	keys += ', ';	4✔
132	values += ', ';	4✔
133	}
134	first = false;	5✔
135	keys += '"' + key + '"';	5✔
136	values += '$' + SqliteQueryBuilder.fixColumnName(key);	5✔
137	}
138
139	insert += keys + ') values (' + values + ')';	1✔
140	return insert;	1✔
141	}
142
143	/**
144	* Builds an update or insert object to bind to a statement
145	* @param {Object} object object to create bind parameters from
146	* @return {Object} bind parameters
147	*/
148	static buildUpdateOrInsertObject(object: any): any {	1✔
149	const insertOrUpdate: { [key: string]: DBValue } = {};	2,095✔
150	if (object.columnNames) {	2,095!
151	const columnNames = object.columnNames;	×
152	for (let i = 0; i < columnNames.length; i++) {	×
153	insertOrUpdate[SqliteQueryBuilder.fixColumnName(columnNames[i])] = object.toDatabaseValue(columnNames[i]);	×
154	}
155	} else {
156	for (const key in object) {	2,095✔
157	if (Object.prototype.hasOwnProperty.call(object, key) && object[key] !== undefined) {	50,984✔
158	if (object.toDatabaseValue) {	11,810✔
159	insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = object.toDatabaseValue(key);	1,344✔
160	} else {
161	if (typeof object[key] === 'boolean') {	10,466✔
162	insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = object[key] ? 1 : 0;	6!
163	} else if (object[key] instanceof Date) {	10,460✔
164	insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = new Date(object[key]).toISOString();	40✔
165	} else {
166	insertOrUpdate[SqliteQueryBuilder.fixColumnName(key)] = object[key];	10,420✔
167	}
168	}
169	}
170	}
171	}
172	return insertOrUpdate;	2,095✔
173	}
174
175	/**
176	* Builds an update statement
177	* @param {string} table table to update
178	* @param {Object} values object with values to update
179	* @param {string} [where] where clause
180	* @param {Array\|Object} [whereArgs] where bind parameters
181	* @return {Object} object with a sql property containing the update statement and an args property with bind arguments
182	*/
183	static buildUpdate(	1✔
184	table: string,
185	values: Record<string, DBValue>,
186	where: string,
187	whereArgs: DBValue[] \| DBValue,
188	): { sql: string; args: DBValue[] } {
189	const args: DBValue[] = [];	1✔
190	let update = 'update ' + table + ' set ';	1✔
191	let first = true;	1✔
192	for (const columnName in values) {	1✔
193	if (!first) {	1!
194	update += ', ';	×
195	}
196	first = false;	1✔
197	update += '"' + columnName + '"';	1✔
198	args.push(values[columnName]);	1✔
199	update += '=?';	1✔
200	}
201	if (whereArgs) {	1!
202	if (whereArgs instanceof Array) {	1!
203	for (let i = 0; i < whereArgs.length; i++) {	1✔
204	args.push(whereArgs[i]);	1✔
205	}
206	} else {
207	args.push(whereArgs);	×
208	}
209	}
210	if (where) {	1!
211	update += ' where ';	1✔
212	update += where;	1✔
213	}
214	return {	1✔
215	sql: update,
216	args: args,
217	};
218	}
219
220	/**
221	* Builds an update from an object
222	* @param {string} table table name to update
223	* @param {Object} object object with values to update
224	* @return {string} update statement
225	*/
226	static buildObjectUpdate(table: string, object: any): string {	1✔
227	let update = 'update ' + table + ' set ';	72✔
228	let first = true;	72✔
229	if (object.columnNames) {	72!
230	const columnNames = object.columnNames;	×
231
232	for (let i = 0; i < columnNames.length; i++) {	×
233	const key = columnNames[i];	×
234	if (!first) {	×
235	update += ', ';	×
236	}
237	first = false;	×
238	update += '"' + key + '"=';	×
239	update += '$' + SqliteQueryBuilder.fixColumnName(key);	×
240	}
241	} else {
242	for (const prop in object) {	72✔
243	if (Object.prototype.hasOwnProperty.call(object, prop)) {	951✔
244	if (!first) {	222✔
245	update += ', ';	150✔
246	}
247	first = false;	222✔
248	update += '"' + prop + '"=';	222✔
249	update += '$' + SqliteQueryBuilder.fixColumnName(prop);	222✔
250	}
251	}
252	}
253
254	return update;	72✔
255	}
256
257	private static appendClauseToString(string: string, name: string, clause?: DBValue): string {	1✔
258	if (clause) {	121,999✔
259	string += name + clause;	20,776✔
260	}
261	return string;	121,999✔
262	}
263
264	private static appendColumnsToString(columns: string[], string: string): string {	1✔
265	if (!columns \|\| !columns.length) return string;	2,581!
266	string += SqliteQueryBuilder.columnToAppend(columns[0]);	2,581✔
267	for (let i = 1; i < columns.length; i++) {	2,581✔
268	string += ', ' + SqliteQueryBuilder.columnToAppend(columns[i]);	10,512✔
269	}
270	string += ' ';	2,581✔
271	return string;	2,581✔
272	}
273
274	private static columnToAppend(column: string): string {	1✔
275	return column.indexOf('*') !== -1 ? column : '"' + column + '"';	13,093✔
276	}
277
278	private static isEmpty(string: string \| undefined): boolean {	1✔
279	return !string \|\| string.length === 0;	40,649✔
280	}
281	}	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