[sqlite] Does a foreign key field need a separate index?

Hi, Is a foreign key field automatically indexed, or will it benefit from a separately created index? E.g. CREATE TABLE [content](   [note_id] INTEGER REFERENCES [notes]([id]) ON DELETE CASCADE,   [data] BLOB); Will note_id benefit from a separately created index? Thanks Rael

Re: [sqlite] what's wrong with this trigger

Thanks, that works. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] what's wrong with this trigger

Hi, CREATE TABLE [notebooks] (   [id] INTEGER,   [last_edit] DATETIME); CREATE TABLE [notes](   [id] INTEGER,   [caption] VARCHAR,   [notebook_id] INTEGER); When a new note is added, I want to update the "last_edit" field for the notebook that this note belongs to. CREATE TRIGGER notes_ai

[sqlite] storing blobs in a separate table

Hi, I am aware that in sqlite for a given "entity" one should generally store blobs in a separate table to the other standard fields (such as text/number etc..) So for maximum efficiency, in such a blob table, is there a problem storing multiple blob fields? E.g.  data1, data2, picture,

Re: [sqlite] Repair corrupt database results in 0KB file

@david: I am not corrupting the database myself. Users of my application have sent in corrupted databases (on very rare occassion). I do not have permission to send you corrupted database at this time. I can tell you the following: This problem starts with v3.18. It works ok with v3.17

Re: [sqlite] Repair corrupt database results in 0KB file

@Simon: I did not mention any sqlite3.dll. I am only talking about sqlite3.exe command line tool. The problem occurs using the command line. Yes, I tried your idea. The last line in the dump using the old version is: COMMIT; The last line in the dump using the latest version (3.23.1) is:

[sqlite] Repair corrupt database results in 0KB file

ld version of sqlite3.exe (sqlite-shell-win32-x86-3080403), and it has worked without problem. Now I updated to the latest version (3.23.1), and using the above method results in a 0 KB file. Is this a bug? Has the syntax changed? I'm on Windows 7 x64 Thanks

Re: [sqlite] Problem with compiled dll on Windows

@ Random Coder.: Thanks, that was the exact issue. Using a def file solved the problem. 1) Should the howtocompile.html webpage not include this in the "Building A Windows DLL" instructions? 2) What is the -Ox flag? I did not find what that was for. -Rael

[sqlite] Problem with compiled dll on Windows

Hi, I am trying to compile the latest amalgamation (3160200) as a dll on Windows 10 x64, using VS2015 x86 Native Tools Command Prompt. The dll compiles fine, however trying to use this dll in various tools (Delphi) results in the error "sqlite3_open not found". I tried compiling with: cl

[sqlite] fix rowid's of fts table

Hi, I have a normal table, and a fts table that are joined by rowid's. E.g. when I add a row to the normal table, I add some values into the fts table as well, and set the rowid based on the normal table rowid (as recommended). Now I have restructured the normal table (i.e. delete column) using

Re: [sqlite] Possible to get table size (in bytes)?

Thanks to the reference to the sqlite_analyzer. That is very interesting. (And the manual counting of bytes would also help me for my question..) The main question I have is how expensive will adding an fts4aux table be? I noticed the report for a FTS table e.g. NOTES_FTS only shows results

[sqlite] Possible to get table size (in bytes)?

Hi, Is it possible to get the information of how many bytes a table is taking up in the database? Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible to get list of terms in the Full Text Index?

Hi, Is it at all possible to get a listing of all terms in the Full Text Index and which rowid's they were found in? E.g. row1: one, two, three, three row2: one row3: three Listing: one [1,2] two [1] three [1,3] And with a score count would be even better.. Is such information available?

Re: [sqlite] slow select from table with data blob

@Jay: Thanks for the official information. >You'll see the slow down anytime you access anything "past" the BLOB. >To avoid that, put the BLOBs at the end of the rows and avoid "SELECT *" style >queries. Yes this is exactly what I found.. The problem with putting them at the end of the table

Re: [sqlite] slow select from table with data blob

The table contains an id field with a unique index and another field with a non-unique index. >So you got about 40 Meg of data in 1.5 seconds. Use your OS's copy command >(or some graphical equivalent) to duplicate that file. How long does it take >? You seemed to miss what I was saying:

[sqlite] slow select from table with data blob

le. So my questions: Is this standard behaviour for sql databases? (that I have only found out now). Is there actually some way to bypass this "problem" (e.g. database setting..)? Is it generally advisable to separate out blob fields into their own table to ensure fast sel

[sqlite] joining fts table with normal table

Hi, The documentation mentions the possibility of joining a fts table with a normal table via rowid. (e.g. select sender, subject from email join email_text on email.rowid = email_text.rowid where body match 'jam';) Can this join only be done via the rowid field? I tried using an "id" field

[sqlite] joining fts table with normal table

Hi, The documentation mentions the possibility of joining a fts table with a normal table via rowid. (e.g.   select sender, subject from email join email_text on email.rowid = email_text.rowid where body match 'jam';) Can this join only be done via the rowid field? I tried using an "id" field

[sqlite] Question on index efficiency

1) Is the efficiency (in terms of speed) of an index equal whether field is INT/REAL/TEXT? (Where all data is of storage type INTEGER for INT field, REAL for REAL, TEXT for TEXT)   I did some tests which showed that speed is equal. My real question is:   2) If an indexed field contains records

[sqlite] DATETIME and storage type

what storage type field values have been stored in? Thanks Rael Bauer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DATETIME and storage type

out what storage type field values have been stored in?   Thanks Rael Bauer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Partial search with fts

Previously someone advised that I use the "*" char to achieve partial search results with fts. eg ver* will match version. This works ok, but only for end parts of a word. Is there anyway to get partial matches for beginning or middle parts of a word? e.g. *sion - to match version or *si* to

[sqlite] Partial search with fts

Previously someone advised that I use the "*" char to achieve partial search results with fts. eg ver* will match version. This works ok, but only for end parts of a word.   Is there anyway to get partial matches for beginning or middle parts of a word?   e.g. *sion - to match version or *si*

[sqlite] partial search matches using FTS

Hi,   Is it possible to obtain partial matches to a search term using the fts search?   E.g. field data contains: apple Search term: app will return a match (optionally with match info).   If not, please add this as a feature request :).   Thanks Rael

Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?

>procedure TForm1.ASQLite3Table1phones_parentGetText(Sender: TField; > var Text: String; DisplayText: Boolean); >begin > //Text := qryContacts.FieldByName('MyField').AsString; > Text := ASQLite3Table1.F >end; This property doesn't come up by code completion - but it is there. >BTW, is

Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?

Hi, First - this relates to original question. (I'm replying from yahoo, and I don't know how to reply to a specific message from a thread. Perhaps someone can tell me how to do that :) ) I think the question relates to the dataset component. Try this to see if it solves

Re: [sqlite] since when was fts3 included in binary?

> According to the web page, 3.5.3. Which web page is that? -Rael - Never miss a thing. Make Yahoo your homepage. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] since when was fts3 included in binary?

Hi, It seems that fts3 is now (3.5.6) included in the windows binary .dll. I'd like to know since when was fts3 included in the binary? Also, since when did the amalgamation include the fts3 sources? Thank you Rael - Looking

[sqlite] how to select first n records

Hi, Can someone tell me how to select first n records from a query (for e.g. Interbase has syntax: "rows 1 to n") Thanks Rael - Looking for last minute shopping deals? Find them fast with Yahoo! Search.

[sqlite] Can't checkout from cvs

I did find the source of the problem in the end. besides the windows firewall, the dsl router had it's own firewall. when I disabled this, I was able to connect to cvs without problems. Rael - Be a better pen pal. Text or chat with friends

Re: [sqlite] Can't checkout from cvs

Hi, Yes that does seem to bypass the login prompt. It says "logging in to...". But still can't actually connect to server. windows has only recently been installed (winxp sp2). also get same problem with firewall turned off. Any other reason why I can't connect to server?

[sqlite] Can't checkout from cvs

Hi, I'm trying to checkout source from cvs but running into problems: At command prompt I type: (using cvs from www.nongnu.org/cvs/) cvs -d :pserver:[EMAIL PROTECTED]:/sqlite login then I get the line: cvs password: I try to type in "anonymous" but I can't type in any keys at all