Re: [sqlite] How to Handle BigInt
On 2 May 2018, at 5:22pm, dmp wrote: > Since I'm using Java and JDBC I was retrieving numeric fields in PostgreSQL > with getString(), handles all, then using Integer.parseInt(stringValue) for > BigInts in storing to SQLite. > > There lies the problem since BigInt values were exceeding the range of > Java Integer. My original solution was to store these as strings, will now > just use Long.parseLong(stringValue) and store has SQLite Integers > properly. Also note that if you store your numbers as strings, indexes on those values will order them as strings. In other words, searching and sorting will work incorrectly. It should be possible to get your numbers from a Java numeric variable to a database numeric value without passing them through a string at any point. And, of course, back out of the database into a numeric variable. If your database library does not allow this, you have a serious problem. As a solution purely about SQLite, SQLite has a "black box" type of BLOB. BLOB is used to store bytes, without putting any interpretation on those bytes. Although technically you can search and sort BLOBs, it's probably a sign of faulty thinking. If I was trying to store something in a database which I didn't want interpreted in any way, I'd use a BLOB. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Handle BigInt
> SQLite integers are all 64 bit - I don't about postgress, so unless > postgress allows integers bigger than 64 bit, and you use them, you should > be OK with your table definitions above. > Paul Hello, That really provides insight to the real issue, I was having and so therefore the question. Since I'm using Java and JDBC I was retrieving numeric fields in PostgreSQL with getString(), handles all, then using Integer.parseInt(stringValue) for BigInts in storing to SQLite. There lies the problem since BigInt values were exceeding the range of Java Integer. My original solution was to store these as strings, will now just use Long.parseLong(stringValue) and store has SQLite Integers properly. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] sqlite3 not support write and read at the same time?
Ad 2) Yes, this is expected. SQLite uses file locking to implement transactions. Ad 1) Using WAL mode, readers can read data as it was before the write transaction started. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von king3306 Gesendet: Mittwoch, 02. Mai 2018 17:14 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] sqlite3 not support write and read at the same time? HI every one i use sqlite3 3.22.0 verison on embeded arm9 system when i insert a lot of data like this begin_transaction sqlite3_prepare_v2(); for(i = 0;i<2040;i++) { sqlite3_bind_text(); sqlite3_bind_blob(); sqlite3_step(); sqlite3_reset(); sqlite3_clear_bindings(); } sqlite3_finalize(stmt); commit_transaction(); insert data about 15s but when i read read bettwen 15s ,i cant read data from database 1、how can i solve this problem? 2、Is this a normal behavior ? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 not support write and read at the same time?
HI every one i use sqlite3 3.22.0 verison on embeded arm9 system when i insert a lot of data like this begin_transaction sqlite3_prepare_v2(); for(i = 0;i<2040;i++) { sqlite3_bind_text(); sqlite3_bind_blob(); sqlite3_step(); sqlite3_reset(); sqlite3_clear_bindings(); } sqlite3_finalize(stmt); commit_transaction(); insert data about 15s but when i read read bettwen 15s ,i cant read data from database 1、how can i solve this problem? 2、Is this a normal behavior ? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in transitive closure extension?
Thank you so much. I'm always impressed by the responsiveness of the SQLite team. Very grateful for your help and hard work. On Wed, May 2, 2018 at 3:13 AM, Dan Kennedy wrote: > On 05/02/2018 03:50 AM, Charles Leifer wrote: > >> Hi all, >> >> I'm noticing a bug in the transitive closure extension in the latest >> version of SQLite. >> > > Thanks for reporting this. Should now be fixed here: > > http://www.sqlite.org/src/info/0c67150749cb3d06 > > Dan. > > > > >> Reproducing requires the closure extension, which I compiled: >> >> gcc -g -fPIC -shared -lsqlite3 closure.c -o closure.so >> >> SQL to populate db: >> >> .load closure >> -- create category table with self-referential parent fk. >> CREATE TABLE "category" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL, >> "parent_id" INTEGER, FOREIGN KEY ("parent_id") REFERENCES "category" >> ("id")); >> CREATE INDEX IF NOT EXISTS "category_parent_id" ON "category" >> ("parent_id"); >> >> -- create closure table vtable >> CREATE VIRTUAL TABLE IF NOT EXISTS "categoryclosure" USING >> transitive_closure (idcolumn=id, parentcolumn=parent_id, >> tablename=category); >> >> -- populate some data for a book catalog >> INSERT INTO "category" ("name", "parent_id") VALUES ('books', NULL); -- 1 >> INSERT INTO "category" ("name", "parent_id") VALUES ('fiction', 1); -- 2 >> INSERT INTO "category" ("name", "parent_id") VALUES ('scifi', 2); -- 3 >> INSERT INTO "category" ("name", "parent_id") VALUES ('hard scifi', 3); -- >> 4 >> INSERT INTO "category" ("name", "parent_id") VALUES ('dystopian', 3); -- 5 >> >> -- get hard scifi and all its parents: >> SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" >> FROM "category" AS "t1" >> INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") >> WHERE ("t2"."id" = 4); >> >> -- results: >> -- 1|books||3 >> -- 2|fiction|1|2 >> -- 3|scifi|2|1 >> -- 4|hard scifi|3|0 >> >> -- get relations of "hard scifi" specifying depth > 0. >> SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" >> FROM "category" AS "t1" >> INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") >> WHERE (("t2"."id" = 4) AND ("t2"."depth" > 0)); >> >> -- results: >> -- 1|books||3 >> -- 2|fiction|1|2 >> -- 3|scifi|2|1 >> >> >> -- THIS BREAKS with: "Error: categoryclosure.xBestIndex malfunction" >> -- get grand-parent category for hard-scifi (depth=2) >> SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" >> FROM "category" AS "t1" >> INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") >> WHERE (("t2"."id" = 4) AND ("t2"."depth" = 2)); >> >> It seems that when we query for equality on the categoryclosure id *and* >> depth, xBestIndex runs into trouble? >> >> Thanks, >> >> Charles Leifer >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in transitive closure extension?
On 05/02/2018 03:50 AM, Charles Leifer wrote: Hi all, I'm noticing a bug in the transitive closure extension in the latest version of SQLite. Thanks for reporting this. Should now be fixed here: http://www.sqlite.org/src/info/0c67150749cb3d06 Dan. Reproducing requires the closure extension, which I compiled: gcc -g -fPIC -shared -lsqlite3 closure.c -o closure.so SQL to populate db: .load closure -- create category table with self-referential parent fk. CREATE TABLE "category" ("id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL, "parent_id" INTEGER, FOREIGN KEY ("parent_id") REFERENCES "category" ("id")); CREATE INDEX IF NOT EXISTS "category_parent_id" ON "category" ("parent_id"); -- create closure table vtable CREATE VIRTUAL TABLE IF NOT EXISTS "categoryclosure" USING transitive_closure (idcolumn=id, parentcolumn=parent_id, tablename=category); -- populate some data for a book catalog INSERT INTO "category" ("name", "parent_id") VALUES ('books', NULL); -- 1 INSERT INTO "category" ("name", "parent_id") VALUES ('fiction', 1); -- 2 INSERT INTO "category" ("name", "parent_id") VALUES ('scifi', 2); -- 3 INSERT INTO "category" ("name", "parent_id") VALUES ('hard scifi', 3); -- 4 INSERT INTO "category" ("name", "parent_id") VALUES ('dystopian', 3); -- 5 -- get hard scifi and all its parents: SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" FROM "category" AS "t1" INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") WHERE ("t2"."id" = 4); -- results: -- 1|books||3 -- 2|fiction|1|2 -- 3|scifi|2|1 -- 4|hard scifi|3|0 -- get relations of "hard scifi" specifying depth > 0. SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" FROM "category" AS "t1" INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") WHERE (("t2"."id" = 4) AND ("t2"."depth" > 0)); -- results: -- 1|books||3 -- 2|fiction|1|2 -- 3|scifi|2|1 -- THIS BREAKS with: "Error: categoryclosure.xBestIndex malfunction" -- get grand-parent category for hard-scifi (depth=2) SELECT "t1"."id", "t1"."name", "t1"."parent_id", "t2"."depth" AS "depth" FROM "category" AS "t1" INNER JOIN "categoryclosure" AS "t2" ON ("t1"."id" = "t2"."root") WHERE (("t2"."id" = 4) AND ("t2"."depth" = 2)); It seems that when we query for equality on the categoryclosure id *and* depth, xBestIndex runs into trouble? Thanks, Charles Leifer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users