date:20180502

Re: [sqlite] How to Handle BigInt

2018-05-02 Thread Simon Slavin

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

2018-05-02 Thread dmp

> 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?

2018-05-02 Thread Hick Gunter

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?

2018-05-02 Thread king3306

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?

2018-05-02 Thread Charles Leifer

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?

2018-05-02 Thread Dan Kennedy


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

Re: [sqlite] [EXTERNAL] Re: probably recursive?

2018-05-02 Thread Hick Gunter

Counterexample:

List of Tuples: (1,1), (1,3),(3,1),(3,3),(2,2) with nX = nY = 2

(2,2) is within the "square" but needs to be removed

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Barry Smith
Gesendet: Mittwoch, 02. Mai 2018 00:12
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] probably recursive?

Well those constraints simplify your problem.

In the resultant dataset, the largest X and Y values will be equal, and the 
largest X will have and entry for every coordinate from (X, 1) to (X, X). 
Likewise the largest Y will have an entry for every coordinate from (1, Y) to 
(Y, Y). Basically you'll have two lines from the axes, drawing a square. All 
points outside that square will be culled, all points on and inside the square 
will be kept.

Since you know that, you now have a one dimensional problem to solve. It still 
seems a little recursive to me, but it should be easier because you only need 
to find a single number (which you can then plug into a delete statement).

If my statement about the square is not obvious to prove in your head I can try 
write a proof for that but I'm not much good at proofs.

> On 2 May 2018, at 7:27 am, Roman Fleysher  
> wrote:
>
> Pairs (x,y) do not repeat.
>
> Actual x and y are positive integers, but I do not see how being positive can 
> be relevant. Integer is important for sorting/comparison.
>
>
> Roman
>
> 
> From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf 
> of Barry Smith [smith.bar...@gmail.com]
> Sent: Tuesday, May 01, 2018 5:23 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] probably recursive?
>
> Is there a uniqueness constraint on your initial data? Can the same 
> coordinate be listed multiple times?
>
> Is there a requirement that X > 0 and Y > 0?
>
>>> On 2 May 2018, at 3:35 am, Simon Slavin  wrote:
>>>
>>> On 1 May 2018, at 6:28pm, Simon Slavin  wrote:
>>>
>>> I just realised that
>>
>> That was intended to be personal email.  Apologies, everyone.
>>
>> Simon.
>> ___
>> 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
___
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

Re: [sqlite] How to Handle BigInt

Re: [sqlite] How to Handle BigInt

Re: [sqlite] [EXTERNAL] sqlite3 not support write and read at the same time?

[sqlite] sqlite3 not support write and read at the same time?

Re: [sqlite] Bug in transitive closure extension?

Re: [sqlite] Bug in transitive closure extension?

Re: [sqlite] [EXTERNAL] Re: probably recursive?

7 matches

Site Navigation

Mail list logo

Footer information