@dbpath/oracle - npm Package Compare versions

Comparing version 0.2.18 to 0.2.19

dist/src/oracleDal.d.ts

@@ -1,8 +0,8 @@
-import { Dal, DalDialect, DalQueryFn } from "@dbpath/dal";
+import { Dal, DalQueryFn } from "@dbpath/dal";
 import { OracleEnv } from "./oracleEnv";
-import { Connection } from "oracledb";
+import { Connection, ResultSet } from "oracledb";
 import { NameAndValidator } from "@dbpath/utils";
+export declare function hackDrainStart(sql: string, rs: ResultSet<any>): Promise<void>;
 export declare const oracleDalQuery: (connection: Connection) => DalQueryFn;
-export declare const oracleSqlDialect: DalDialect;
 export declare function oracleDal(env: OracleEnv): Promise<Dal>;
 export declare const oracleEnvValidator: NameAndValidator<OracleEnv>;

dist/src/oracleDal.js

@@ -12,6 +12,21 @@ "use strict";
 Object.defineProperty(exports, "__esModule", { value: true });
-exports.oracleEnvValidator = exports.oracleDal = exports.oracleSqlDialect = exports.oracleDalQuery = void 0;
+exports.oracleEnvValidator = exports.oracleDal = exports.oracleDalQuery = exports.hackDrainStart = void 0;
 const utils_1 = require("@dbpath/utils");
+const limitFn_hack_1 = require("./limitFn.hack");
 const oracledb = require('oracledb');
 const checkSql = (sql, addSemiColon) => addSemiColon && !sql.endsWith(';') ? sql + ';' : sql;
+function hackDrainStart(sql, rs) {
+    return __awaiter(this, void 0, void 0, function* () {
+        const match = sql.match(/--(\d+)$/);
+        if (!match || match.length === 0)
+            return;
+        const size = Number.parseInt(match[1]);
+        let i = 1;
+        while (i++ < size) {
+            if (!(yield rs.getRow()))
+                return;
+        }
+    });
+}
+exports.hackDrainStart = hackDrainStart;
 const oracleDalQuery = (connection) => (sql, params) => __awaiter(void 0, void 0, void 0, function* () {
@@ -25,7 +40,13 @@ let safeParams = (0, utils_1.safeArray)(params);
         let row;
+        yield hackDrainStart(sql, rs);
         while ((row = yield rs.getRow())) {
-            rows.push((0, utils_1.fromEntries)(...(0, utils_1.mapEntries)(row, (t, name) => [name.toLowerCase(), t])));
+            let r = (0, utils_1.fromEntries)(...(0, utils_1.mapEntries)(row, (t, name) => [name.toLowerCase(), t]));
+            (0, limitFn_hack_1.hackDeleteRowForPaging)(r);
+            rows.push(r);
         }
-        const meta = { columns: result.metaData.map(md => ({ name: md.name.toLowerCase() })) };
-        return { rows, meta };
+        const meta = { columns: result.metaData
+                .map(md => ({ name: md.name.toLowerCase() }))
+                .filter(limitFn_hack_1.hackFilterColumnNames) };
+        let r = { rows, meta };
+        return r;
     }
@@ -56,9 +77,2 @@ catch (e) {
 };
-exports.oracleSqlDialect = {
-    limitFn: (pageNum, pageSize, s) => {
-        const offset = (pageNum - 1) * pageSize;
-        return [...s, `LIMIT ${pageSize} OFFSET ${offset}`];
-    },
-    safeQuery: 'SELECT 1'
-};
 function findTableNames(connection, schema) {
@@ -65,0 +79,0 @@ return __awaiter(this, void 0, void 0, function* () {

dist/src/oracleDal.spec.js

@@ -12,2 +12,3 @@ "use strict";
 Object.defineProperty(exports, "__esModule", { value: true });
+const oracleEnv_1 = require("./oracleEnv");
 const oracleDal_1 = require("./oracleDal");
@@ -117,1 +118,32 @@ const fixtures_1 = require("@dbpath/fixtures");
 });
+describe("oracle limitFn", () => {
+    const limitFn = oracleEnv_1.oracleDalDialect.limitFn;
+    describe("one table (no where)", () => {
+        const someSql = ["select * from table", "order by id"];
+        it("should modify sql by adding limit", () => {
+            expect(limitFn(1, 3, someSql)).toEqual([
+                "select /*+ FIRST_ROWS(3) */ rownum as dbautorownum,* from table",
+                "where rownum<=3 order by id",
+                "--1"
+            ]);
+            expect(limitFn(2, 3, someSql)).toEqual([
+                "select /*+ FIRST_ROWS(6) */ rownum as dbautorownum,* from table",
+                "where rownum <= 6 order by id",
+                "--4"
+            ]);
+            expect(limitFn(2, 6, someSql)).toEqual([
+                "select /*+ FIRST_ROWS(12) */ rownum as dbautorownum,* from table",
+                "where rownum <=12 order by id",
+                "--7"
+            ]);
+        });
+    });
+    describe("two tables - with where", () => {
+        const someSql = ['select * from sometable', ' where x = 11'];
+        expect(limitFn(2, 6, someSql)).toEqual([
+            "select /*+ FIRST_ROWS(12) */ rownum as dbautorownum,* from sometable",
+            " where rownum <=12 and x = 11",
+            "--7"
+        ]);
+    });
+});

dist/src/oracleEnv.js

 "use strict";
 Object.defineProperty(exports, "__esModule", { value: true });
 exports.oracleDalDialect = void 0;
+const limitFn_hack_1 = require("./limitFn.hack");
 exports.oracleDalDialect = {
-    limitFn: (pageNum, pageSize, s) => s,
+    limitFn: limitFn_hack_1.hackLimitFnForPaging,
     safeQuery: 'SELECT 1 FROM DUAL'
 };

package.json

 {
   "name": "@dbpath/oracle",
   "description": "",
-  "version": "0.2.18",
+  "version": "0.2.19",
   "main": "dist/index",
@@ -21,4 +21,4 @@ "types": "dist/index",
     "oracledb": "^5.5.0",
-    "@dbpath/utils": "0.2.18",
-    "@dbpath/dal": "0.2.18"
+    "@dbpath/utils": "0.2.19",
+    "@dbpath/dal": "0.2.19"
   },
@@ -25,0 +25,0 @@ "devDependencies": {

README.md

 Oracle Database Abstraction Layer for dbpath
 
+# LimitFn
+In order to do simple pagings in versions of Oracle before 12 it was ... _incredibly_ ... painful to implement 
+paging. Effectively impossible to do automatically for any reasonably complex select statement
 
+Thus we have a 'bodge' in place for these older versions:
+* We add the rownum to each column
+* We limit to rownum < the max we want (with a specific fieldname)
+* We throw away the rows we don't want
+* We remove the rownum filed name
+
+It is especially 'hacky' how we sort out the 'throw away the rows we don't want. We include that in comments 
+in the select statement
+
+For later versions (still to be implemented) we will just use `limit`
+
+
+
+
+
+
+
 ```sql
@@ -5,0 +25,0 @@

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

26193

increased by19.24%

Number of package files

15

increased by15.38%

Lines of code

467

increased by21.61%

Number of lines in readme file

45

increased by80%

Dependency changes

• + Added@dbpath/config@0.2.19(transitive)

• + Added@dbpath/dal@0.2.19(transitive)

• + Added@dbpath/fixtures@0.2.19(transitive)

• + Added@dbpath/types@0.2.19(transitive)

• + Added@dbpath/utils@0.2.19(transitive)

• - Removed@dbpath/config@0.2.18(transitive)

• - Removed@dbpath/dal@0.2.18(transitive)

• - Removed@dbpath/fixtures@0.2.18(transitive)

• - Removed@dbpath/types@0.2.18(transitive)

• - Removed@dbpath/utils@0.2.18(transitive)

• Updated@dbpath/dal@0.2.19

• Updated@dbpath/utils@0.2.19