[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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 AFTER INSERT ON notes
BEGIN
update notebooks set notebooks.last_edit = datetime('now')
where notebooks.id = new.notebook_id;
END;
SQLite Expert reports a near ".": syntax error. Is there something wrong
with the trigger syntax here, or is it some other problem?
Thanks
Rael
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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, etc.. or should the blob table only have 1 blob field? (perhaps with a second field indicating what is stored in the blob, or store different blobs in different tables?) Thanks Rael ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 Database 1 integrity check result: *** in database main *** On tree page 51 cell 1: invalid page number 302055426 Page 373: btreeInitPage() returns error code 11 Page 501: btreeInitPage() returns error code 11 Page 437: btreeInitPage() returns error code 11 Page 54 is never used Page 55 is never used Page 56 is never used Page 57 is never used Page 58 is never used Page 59 is never used Database 2 integrity check result: (no result) Error: database disk image is malformed. Database 3 integrity check: *** in database main *** On tree page 65213 cell 2: 9595 of 15116 pages missing from overflow list starting at 65188 Page 70081 is never used Page 70082 is never used Page 70083 is never used ... repeat up to Page 70179 is never used Despite these errors, using the .dump command mentioned in first post fixes the problem wonderfully in all 3 databases, and fails in all 3 in v3.18 If you have a pressing need for the database I can go to the trouble of contacting the user about it, but I obtained some a long time ago. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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: ROLLBACK; -- due to errors If I edit this to "COMMIT;", then it works ok if I read the dump file into a new database. Seems like a regression to me... -Rael ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Repair corrupt database results in 0KB file
Hello, I having been using the following method to repair corrupt databases using sqlite3.exe: Simply call: sqlite3 mydata.db ".dump" | sqlite3 new.db (source: https://stackoverflow.com/questions/18259692/how-to-recover-a-corrupt-sqlite3-database) I have been using an old 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 Rael Bauer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 sqlite3.c -link -dll -out:sqlite3.dll and cl sqlite3.c -DSQLITE_ENABLE_FTS4 -link -dll -out:sqlite3.dll But experienced the same problem. Any ideas what could be wrong? I have uploaded the dll's to: www.bauerapps.com/files/sqlite3_1.dll and www.bauerapps.com/files/sqlite3_2.dll Here is the command line log: C:\Program Files (x86)\Microsoft Visual Studio 14.0\VC>cd C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200 C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200>cl sqlite3.c -link -dll -out:sqlite3.dll Microsoft (R) C/C++ Optimizing Compiler Version 19.00.23918 for x86 Copyright (C) Microsoft Corporation. All rights reserved. sqlite3.c Microsoft (R) Incremental Linker Version 14.00.23918.0 Copyright (C) Microsoft Corporation. All rights reserved. /out:sqlite3.exe -dll -out:sqlite3.dll sqlite3.obj C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200>cl sqlite3.c -DSQLITE_ENABLE_FTS4 -link -dll -out:sqlite3.dll Microsoft (R) C/C++ Optimizing Compiler Version 19.00.23918 for x86 Copyright (C) Microsoft Corporation. All rights reserved. sqlite3.c Microsoft (R) Incremental Linker Version 14.00.23918.0 Copyright (C) Microsoft Corporation. All rights reserved. /out:sqlite3.exe -dll -out:sqlite3.dll sqlite3.obj C:\Downloads\Database\SQLite\sqlite-amalgamation-3160200> Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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 the algorithm mentioned here a couple of times (insert into normal select (columns) from normal_OLD, etc..) This "resets" the rowid's of the normal table. E.g. if the table started with rowid's like: 1 2 6 7 10 after restructuring they will be: 1 2 3 4 5 So the fts table is now out of sync with the normal table. Do i have to completely re-populate the fts table or is there a simpler way to "reset" the fts rowids? (If I tried to just reset the rowid's on the "main" fts table I imagine this would not work, since there are the aux fts tables as well..) Thanks Rael
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 for: NOTES_FTS_SEGMENTS NOTES_FTS_CONTENT NOTES_FTS_SEGDIR Why not for NOTES_FTS - is that just a view? Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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? Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 is that if you subsequently need to add other columns there is a problem... @Puneet: Thanks for the info.. >That is a known feature (issue). I am sure it is written up somewhere, but >definitely, on this list, Richard Hipp and others have emphasized this many >times -- search the mailing list archives. I did not come across this information so far. It should be included somewhere a bit more obvious... Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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: If the blob field is positioned in the middle of the columns then even If I don't include the blob field in the query the select is very slow (1.5 seconds). If the blob field is positioned at the end of the columns then if I don't include the blob field in the query the select is very fast (140 ms). If I do include the blob field, the select is about 400-500 ms. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] slow select from table with data blob
It seemed strange that a simple "select * from table" that I was doing was so slow. The table contained about 20 columns (fields) and 300 rows. The select took about 1.5 seconds. (using SQLite Expert). The table contained a blob field, with a "fair" amount of data spread over the rows (max was around 6 MB...). The sqlite file was about 40 MB. After some testing, I discovered that the problem was caused by the blob field being in the middle of the columns (e.g. column 8). Results were slow even if I didn't include the blob field in the select. If I moved this blob field to the end of the table (i.e. last column), then select was very fast if I didn't include the last field (140ms). If I did include the last field (i.e. the blob field), it was slower (400ms) but still significantly faster that results above, when blob field was in the middle of the table. 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 select speeds? Thanks Rael Bauer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 (my own id INTEGER field) - i.e when I insert the fts record, I set the rowid to id of the other table, and then tried join with e.g. email.rowid = email_text.id, but get an error. It seems this is not possible, but I'm not sure why there should be a distinction. Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 (my own id INTEGER field) - i.e when I insert the fts record, I set the rowid to id of the other table, and then tried join with e.g. email.rowid = email_text.id, but get an error. It seems this is not possible, but I'm not sure why there should be a distinction. Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 with a mix of storage classes (e.g. some records are stored as REAL, some as TEXT), does this decrease speed performance of the index in any way? Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] DATETIME and storage type
Hi, If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table "notes" add column "last_modified" DATETIME default "2001-01-01";) will the declared default value be stored as a string or real value? Also, more generally, how can I find 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] DATETIME and storage type
Hi, If I declare a field as DATETIME default "2001-01-01", ( e.g. alter table "notes" add column "last_modified" DATETIME default "2001-01-01";) will the declared default value be stored as a string or real value? Also, more generally, how can I find 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 match version Thanks Rael ___ 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 match version Thanks Rael ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 there a way to have this event called for all fields, so as
>not to have to copy/paste for every column?
This type of question may better be answered on borland forums.
Maybe this will work. all events point to this published method:
TForm1.MyGetText(Sender: TField;
var Text: String; DisplayText: Boolean);
begin
Text := ASQLite3Table1.Fields[Sender.Index].AsString;
end;
-
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 problem:
1. Add persistent fields to your dataset (right click -> fields editor -> add
all fields)
2. Each field has an event OnGetText.
3. Add code in this event like the following:
Text := qryContacts.FieldByName('MyField').AsString;
hth
Rael
-
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 for last minute shopping deals? Find them fast with Yahoo! Search. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 inside Yahoo! Mail. See how.
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? Thank Rael - Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now.
[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 to the console window (it's like the window freezes. only enter works once)! I wait until time out response. Any idea what could be causing this? Using winxp sp2. Is it possible to checkout using tortoisecvs? I've tried this but also doesn't work. (my settings are: cvsroot: :pserver:[EMAIL PROTECTED]:/sqlite server: www.sqlite.org repository folder: /sqlite username: anonymous module: sqlite) thanks Rael - Never miss a thing. Make Yahoo your homepage.
