Re: [sqlite] Internal v. External BLOBs
On 1 Oct 2011, at 12:37am, Petite Abeille wrote: > On Sep 21, 2011, at 2:19 AM, Richard Hipp wrote: > >> If you are storing large BLOBs in SQLite, can you read them faster if they >> are stored directly in the database file, or can you get to them quicker if >> you store just a filename in the database and read the BLOB content from a >> separate file? > > Thanks for sharing. What about insert time (i.e. insert blob vs. write file)? This would be far more dependent on your combination of operating system and file system. Reading a file is pretty-much the same on everything. Creating a new one is done very differently. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Internal v. External BLOBs
On Sep 21, 2011, at 2:19 AM, Richard Hipp wrote: > If you are storing large BLOBs in SQLite, can you read them faster if they > are stored directly in the database file, or can you get to them quicker if > you store just a filename in the database and read the BLOB content from a > separate file? Thanks for sharing. What about insert time (i.e. insert blob vs. write file)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on armv7l GNU/Linux - JDBC issues -anysuggestions?!
As an addition to this post.
Once I was able to make JDBC connections to SQLite on my system I found any
volume of connections lead to intermittent errors in the pure java sqlite jdbc
providers I was using.
Things like:
org.ibex.nestedvm.Runtime$FaultException: java.lang.NullPointerException: at
(unknown)
at org.sqlite.SQLite._execute(build/SQLite.mips)
at org.ibex.nestedvm.Runtime.__execute(Runtime.java:506)
at org.ibex.nestedvm.Runtime.call(Runtime.java:678)
at org.ibex.nestedvm.Runtime.call(Runtime.java:647)
at org.sqlite.NestedDB.call(NestedDB.java:406)
at org.sqlite.NestedDB.call(NestedDB.java:389)
at org.sqlite.NestedDB.step(NestedDB.java:133)
at org.sqlite.DB.execute(DB.java:261)
at org.sqlite.Stmt.exec(Stmt.java:56)
at org.sqlite.Stmt.executeQuery(Stmt.java:90)
Using the native JCBC option and compiling the supporting library from here->
http://www.ch-werner.de/javasqlite/ fixed the issue.
Very pleased!
-Original Message-
From: Katie Blake
Sent: 27 September 2011 17:50
To: Katie Blake; 'General Discussion of SQLite Database'
Subject: RE: [sqlite] SQLite on armv7l GNU/Linux - JDBC issues -anysuggestions?!
Hello again,
Just to let you know that I am running the same code on the same OS but with
OpenJDK and my issue has now magically disappeared! Confirming Christian's
suspicions of the JVM being the root of the issue I think..
Best Regards,
Katie,
-Original Message-
From: Katie Blake
Sent: 26 September 2011 19:08
To: 'General Discussion of SQLite Database'
Subject: RE: [sqlite] SQLite on armv7l GNU/Linux - JDBC issues -anysuggestions?!
Thank you so much for this suggestion Michael.
I got JamVm for my platform and now see a different error - anyone any
thoughts?!
( Once again the line in my code which causes the error is conn =
DriverManager.getConnection("jdbc:sqlite:testdb.db"); )
Logging in to database
running in pure-java mode
running in pure-java mode
Got jdbc driver
java.lang.reflect.InvocationTargetException
at java.lang.reflect.VMMethod.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:327)
at jamvm.java.lang.JarLauncher.main(JarLauncher.java:50)
Caused by: java.lang.reflect.InvocationTargetException
at java.lang.reflect.VMMethod.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:327)
at
org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader.main(JarRsrcLoader.java:58)
at java.lang.reflect.VMMethod.invoke(Native Method)
...2 more
Caused by: java.lang.NullPointerException
at org.sqlite.NestedDB$CausedSQLException.fillInStackTrace(NestedDB.java:649)
at java.lang.Throwable.(Throwable.java:161)
at java.lang.Exception.(Exception.java:78)
at java.sql.SQLException.(SQLException.java:76)
at java.sql.SQLException.(SQLException.java:113)
at org.sqlite.NestedDB$CausedSQLException.(NestedDB.java:626)
at org.sqlite.NestedDB._open(NestedDB.java:63)
at org.sqlite.DB.open(DB.java:86)
at org.sqlite.Conn.open(Conn.java:140)
at org.sqlite.Conn.(Conn.java:57)
at org.sqlite.JDBC.createConnection(JDBC.java:77)
at org.sqlite.JDBC.connect(JDBC.java:64)
at java.sql.DriverManager.getConnection(DriverManager.java:165)
at java.sql.DriverManager.getConnection(DriverManager.java:204)
at DataTest.main(DataTest.java:27)
at java.lang.reflect.VMMethod.invoke(Native Method)
...5 more
Best Regards,
Katie
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Black, Michael (IS)
Sent: 26 September 2011 14:41
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite on armv7l GNU/Linux - JDBC issues -anysuggestions?!
I found an old post here that claims jamvm fixed their same (or similar?)
assertion problem with that jdbc driver.
https://evolvis.org/pipermail/jalimo-info/2009-March/000299.html
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Katie Blake [kbl...@sentec.co.uk]
Sent: Monday, September 26, 2011 6:39 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SQLite on armv7l GNU/Linux - JDBC issues
-anysuggestions?!
Christian! Hi !
What a small world it is :) Thank you for helping me out!
I have tried the zentus driver, and have sent the same issue. I am now looking
at getting the http://www.ch-werner.de/javasqlite/ compiled for my platform -
fingers crossed!
I don't see any difference if I run the test program with a precedingjava
-Djava.compiler=NONE ... am I doing something foolish?
Thanks so much again, very much hope that life is treating you well at the
moment.
Katie,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Katie Blake, Software Developer
Visit us at Metering Eur
Re: [sqlite] binary key, blob or text?
On Sep 30, 2011, at 2:34 PM, Igor Tandetnik wrote: >> Any gotchas in using blob as keys (unique or otherwise)? > > None that I know of. Thanks Igor :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with vacuum when count_changes=1
Filip Navara writes: > > On Fri, Sep 30, 2011 at 2:21 PM, Reg Server wrote: > [snip] > > I don't have Mac but I tried mentioned steps using sqlite3.exe using both > > versions 3.7.8 and 3.7.5. It crashed every time. > > > > I'm don't know where to get older versions to investigate where the crash > > started happening. I just know for sure that ancient version 3.6.6.1 we were > > using before did not crash. > > It's fixed now, see http://www.sqlite.org/src/info/c48d99d690 > > Thanks, Dan! > > Best regards, > Filip Navara > Thanks, really fast resolving of the issue! :) I'll try it in the next released version. Best regards, RS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with vacuum when count_changes=1
On Fri, Sep 30, 2011 at 2:21 PM, Reg Server wrote: [snip] > I don't have Mac but I tried mentioned steps using sqlite3.exe using both > versions 3.7.8 and 3.7.5. It crashed every time. > > I'm don't know where to get older versions to investigate where the crash > started happening. I just know for sure that ancient version 3.6.6.1 we were > using before did not crash. It's fixed now, see http://www.sqlite.org/src/info/c48d99d690 Thanks, Dan! Best regards, Filip Navara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] binary key, blob or text?
Petite Abeille wrote: > From an efficiency point of view, would one be better off storing that key as > a blob or text? In other words, store the raw byte > sequence directly or use a hex text representation of it? Blob > So, binary: > >hashblob not null collate binary, -- as the raw byte sequence Collation doesn't apply to blobs - they are always compared as binary. > Any gotchas in using blob as keys (unique or otherwise)? None that I know of. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with vacuum when count_changes=1
Simon Slavin writes: > > > On 30 Sep 2011, at 11:09am, Filip Navara wrote: > > > Reproduced with the following steps: > > > >> sqlite3 test.db > > pragma count_changes=1; > > create table a (a); > > vacuum; > > > > (SQLite 3.7.8 on Windows) > > On my Mac > > SQLite version 3.7.5 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> pragma count_changes=1; > sqlite> create table a (b); > sqlite> vacuum; > sqlite> > > Apparently no problem. Don't know if that helps anybody. > > Simon. > I don't have Mac but I tried mentioned steps using sqlite3.exe using both versions 3.7.8 and 3.7.5. It crashed every time. I'm don't know where to get older versions to investigate where the crash started happening. I just know for sure that ancient version 3.6.6.1 we were using before did not crash. Best regards, RS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with vacuum when count_changes=1
On 30 Sep 2011, at 11:09am, Filip Navara wrote: > Reproduced with the following steps: > >> sqlite3 test.db > pragma count_changes=1; > create table a (a); > vacuum; > > (SQLite 3.7.8 on Windows) On my Mac SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> pragma count_changes=1; sqlite> create table a (b); sqlite> vacuum; sqlite> Apparently no problem. Don't know if that helps anybody. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with vacuum when count_changes=1
Reproduced with the following steps: > sqlite3 test.db pragma count_changes=1; create table a (a); vacuum; (SQLite 3.7.8 on Windows) Best regards, Filip Navara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with vacuum when count_changes=1
Hi all, I am trying to update our application to use latest SQLite library. In many places of the code we were using data returned from INSERT, UPDATE, DELETE commands when count_changes pragma is set to 1. First thing I noticed is that this pragma is now deprecated. But, it still works correctly. Problems started when i tried to vacuum the database. In the scenario when count_changes=1, executing vacuum fails with "Attempted to read or write protected memory. This is often an indication that other memory is corrupt." on sqlite3_step function. As we were using our own .NET wrapper, I also decided to give System.Data.SQLite a try. But the results are the same. Error is still there. One workaround might be to disable pragma before executing vacuum, and enable it afterwards. But I'm sure something like that could be broken in multithreaded or multiuser scenario (doing insert right after pragma is disabled and relying on returned data). So maybe this could be fixed in library? Best regards, RS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS management
Hello. I've always got great help in this list so I thank in advance who posts here and who will answer to my question. I've started to use FTS in a web site for search thru a table of sites. Given a main table containing, among others, fields "id", "url", "nome" (title) and "descrizione" (description), I keep the fts table updated in this way: CREATE TRIGGER "upd_sito" AFTER UPDATE ON "siti" BEGIN UPDATE fts3_siti SET nome=new.nome, url=new.url, descrizione=new.descrizione WHERE docid=new.id; END; CREATE TRIGGER "del_sito" AFTER DELETE ON "siti" BEGIN DELETE FROM fts3_siti WHERE docid=old.id; END; CREATE TRIGGER "ins_sito" AFTER INSERT ON "siti" BEGIN INSERT INTO fts3_siti(docid,nome,url,descrizione) VALUES(new.id,new.nome,new.url,new.descrizione); END; The FTS virtual table is defined as: CREATE VIRTUAL TABLE "fts3_siti" USING fts3 ( nome TEXT, url TEXT, descrizione TEXT); I use fts3 since my host doesn't yet offer PHP 5.3.8 which contains a SQLite version that includes FTS4. Before extending this feature to other sections of the site I need to know if this is an efficient way to keep the FTS table updated. Also, is there any pragma or so to tell FTS to automatically search for parts of words, instead of requiring users to add *? As said, thanks for any help and sorry for my bad english. -- Saluti da Gabriele Favrin http://www.favrin.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] binary key, blob or text?
Hello, Say one has a hash value as an indexed key for a table. >From an efficiency point of view, would one be better off storing that key as >a blob or text? In other words, store the raw byte sequence directly or use a >hex text representation of it? So, binary: hashblob not null collate binary, -- as the raw byte sequence Or text: hashtext not null collate binary, -- as hex encoded And then: create index if not exists foo_hash on foo( hash ) >From a storage perspective, using the blob seems to be more straightforward >(no encoding overhead), so, say, given a SHA1 hash, 20 bytes raw vs. 40 bytes >hex encoded. Any gotchas in using blob as keys (unique or otherwise)? Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
