[sqlite] UNIQUE constraint failed: ip_addr.pid
Hi Petr, if you Google for "database table primary key" the first few results lead to quite good explanations. Also, the english wikipedia's article "Unique key" explains primary keys. HTH Martin Am 14.09.2015 22:25 schrieb Petr L?z?ovsk? : > > I had googled to verify such idea before, but have no luck. > > Thanks, L. > > > Hello Petr, > > > defining the column pid as INTEGER PRIMARY KEY you added an implicit > > contraint; a primary key means that only one record with a given value > > of pid can exist in the table. > > See https://www.sqlite.org/lang_createtable.html#rowid > > > Martin > > > Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: > >> Have following table: > > >> CREATE TABLE ip_addr > >> ( > >> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, > >> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, > >> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ > >> /*! Status: 1 - Allowed, Asigned to concrete customer */ > >> /*! Status: 2 - Disallowed, Assigned to blocked user */ > >> /*! Status: 3 - Disallowed, Assigned to history user */ > >> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, > >> /*! Type: 1 - Private */ > >> /*! Type: 2 - Public */ > >> /*! Type: 3 - IPv6 */ > >> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, > >> /*! Date of blocking %%e */ blocked_at INTEGER, > >> /*! Blocking note %%f */ blocking_note VARCHAR > >> ); > >> ) > > >> If inserting row containing PID already exist in table, sqlite generate > >> %subj% error. But there is not such constraint in database definition. Did > >> I miss something? > > >> L. > > >> ___ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE constraint failed: ip_addr.pid
I had googled to verify such idea before, but have no luck. Thanks, L. > Hello Petr, > defining the column pid as INTEGER PRIMARY KEY you added an implicit > contraint; a primary key means that only one record with a given value > of pid can exist in the table. > See https://www.sqlite.org/lang_createtable.html#rowid > Martin > Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: >> Have following table: >> CREATE TABLE ip_addr >> ( >> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, >> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, >> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ >> /*! Status: 1 - Allowed, Asigned to concrete customer */ >> /*! Status: 2 - Disallowed, Assigned to blocked user */ >> /*! Status: 3 - Disallowed, Assigned to history user */ >> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, >> /*! Type: 1 - Private */ >> /*! Type: 2 - Public */ >> /*! Type: 3 - IPv6 */ >> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, >> /*! Date of blocking %%e */ blocked_at INTEGER, >> /*! Blocking note %%f */ blocking_note VARCHAR >> ); >> ) >> If inserting row containing PID already exist in table, sqlite generate >> %subj% error. But there is not such constraint in database definition. Did I >> miss something? >> L. >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS5 stopwords
On 09/14/2015 09:13 PM, Abilio Marques wrote: > ?Hi, > > I know I'm a newcomer into the SQLite project, but I'm excited about what > FTS5 has to offer. To me it seems simple and powerful, and has some really > nice ideas. > > Is it possible for me to contribute on the module, or is it too late for > that? > > I would like to mention two new ideas I would offer to introduce. First, a > customizable list of stopwords: > > https://en.wikipedia.org/wiki/Stop_words > ? > (I didn't find anything similar to that in the documentation, am I missing > something?) > > I know I can add it via a custom tokenizer, but wouldn't it be useful to > have it straight out of the box? Hi, I think such a thing would be implemented using the custom tokenizer API even if it were shipped as part of FTS5. As a "wrapper tokenizer" similar to the built-in porter tokenizer perhaps. If we had code for a stop-words implementation that seemed like it would work for everybody and any licensing issues could be worked out then there's no reason something like that couldn't be made part of FTS5. Dan.
[sqlite] FTS5 stopwords
On 09/14/2015 09:13 PM, Abilio Marques wrote: > ?Hi, > > I know I'm a newcomer into the SQLite project, but I'm excited about what > FTS5 has to offer. To me it seems simple and powerful, and has some really > nice ideas. > > Is it possible for me to contribute on the module, or is it too late for > that? > > I would like to mention two new ideas I would offer to introduce. First, a > customizable list of stopwords: > > https://en.wikipedia.org/wiki/Stop_words > ? > (I didn't find anything similar to that in the documentation, am I missing > something?) > > I know I can add it via a custom tokenizer, but wouldn't it be useful to > have it straight out of the box? > > > Also, I would like to mention the usefulness of some statistics to create > more advanced ranking formulas. Things like: the Longest Common Subsequence > between query and document, number of unique matched keywords, etc. These > and other values are really useful in applications where bm25 is not > suitable or enough. Hi, From an FTS5 custom auxiliary function, there are two ways to find the token offset of every phrase match in the current document: The xInstCount()/xInst() allows random access to an array of matches - i.e. give me the phrase number, column and token offset of the Nth match: https://www.sqlite.org/draft/fts5.html#xInstCount And xPhraseFirst()/xPhraseNext() allow the user to iterate through the matches for a specific query phrase within the current document: https://www.sqlite.org/draft/fts5.html#xPhraseFirst xPhraseFirst/xPhraseNext is faster, but xInstCount/xInst can be easier to use. It should be possible to build the sorts of things you're talking about on top of one of those, no? The example matchinfo() code contains code to determine the longest common subsequence here: http://www.sqlite.org/src/artifact/e96be827aa8f5?ln=259-281 Feedback from anyone who actually tries to use this API much appreciated. Dan. > > I come from using an engine called Sphinx Search (used on huge things like > Craigslist), which offers such factors. Using them, they have defined > rankers that mix bm25 with proximity, and some other they call > SPH_RANK_SPH04, which includes a weighting boost for the result appearing > at the beginning of the text field, and a bigger boost if its an exact > match: > > http://sphinxsearch.com/docs/latest/builtin-rankers.html > > The formulas (in sphinx higher is better) for them are: > http://sphinxsearch.com/docs/latest/formulas-for-builtin-rankers.html > > And the list of supported factor is: > http://sphinxsearch.com/docs/latest/ranking-factors.html. > > Of course having all of them would be overkill, but if you find them > interesting, we can get the most useful ones, allowing people to build > rankers to their own needs. > > > ?Once again, you people are the experts and know if such ideas are feasible > and where is the right place to include them, so please tell me your > opinions. > > ? > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Third test of json and index expressions, now it works
On Sun, 13 Sep 2015 10:46:21 +0200 Clemens Ladisch wrote: > James K. Lowden wrote: > > select a, r from ( > > SELECT a, random() as r FROM a > > ) as R > > WHERE r <> r; > > > > will yield zero rows, every time. > > $ sqlite3 > SQLite version 3.8.12 2015-09-12 19:50:58 > ... > sqlite> create table a(a); > sqlite> insert into a values (1),(2),(3); > sqlite> select a, r from (SELECT a, random() as r FROM a) as R > sqlite> WHERE r <> r; > 1|-909199267849538533 > 2|8366540922678358399 > 3|-6124149463908475628 Oh, Jimminy Crickets. That's Just Wrong. Thank you for the correction viz SQLite. I hope we can look forward to seeing it fixed. R.r doesn't refer, semantically, to the RANDOM function. It refers to the product of RANDOM. Apparently, deep in the bowels of SQLite, the query evaluation logic doesn't recognize -- and deal correctly with -- nondeterministic functions. Afaik there are only 2: DATE(now) and RANDOM. And they've both caused problems. Deterministic functions are idempotent. No matter how often you call, say, ABS(x), you get the same result for the same x, every time The execution evaluation can invoke ABS as often as it likes; the worst that will happen is a little inefficiency. When it comes to nondeterministic functions, though, it matters a great deal how often they're called, else you get nonsensical answers like R.r not being equal to itself. --jkl
[sqlite] I don't understand how to use NOT EXISTS
On Monday, 14 September, 2015 21:07, Nicolas J?ger
said:
> hi,
> I have a table TAGS with idkey and two colums (NAME, COUNT):
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
> I want to check if some tag exist by checking if `NAME` is recorded
> in the table or not. If not, I want to add it;
> INSERT INTO TAGS ( NAME, COUNT ) SELECT 'magnetohydrodynamics', 1
> FROM TAGS WHERE NOT EXISTS (SELECT * FROM TAGS WHERE NAME =
> 'magnetohydrodynamics' );
The problem is that the query
SELECT 'magnetohydrodynamics', 1
FROM TAGS
WHERE NOT EXISTS (SELECT *
FROM TAGS
WHERE NAME = 'magnetohydrodynamics');
returns a row for each row in tags if there is no 'magnetohydrodynamics' in
tags.
If there should only be one entry for each name (and it is not case sensitive),
your best bet would be to declare that NAME is unique:
create table Tags
(
id integer primary key,
name text collate nocase unique,
count integer not null
);
Then when you want to insert you just do so, as in:
INSERT OR IGNORE INTO TAGS (name, count) VALUES ('magnetohydrodynamics', 0);
To increment a count you would do:
INSERT OR IGNORE INTO TAGS (name, count) VALUES ('magnetohydrodynamics', 0);
UPDATE TAGS SET count = count + 1 WHERE name = 'magnetohydrodynamics';
> then if I look up in the table I see:
>
> id|NAME|COUNT
> 53|black metal|3
> 55|occult rock|3
> 61|satanic smurfs|1
> 62|beer|0
> 63|pizza|0
> 64|magnetohydrodynamics|1
> 65|magnetohydrodynamics|1
> 66|magnetohydrodynamics|1
> 67|magnetohydrodynamics|1
> 68|magnetohydrodynamics|1
>
> could you tell me please where I did some mess ?
>
> regards,
> Nicolas J.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS5 documentation typo
On 09/14/2015 06:31 PM, Abilio Marques wrote: > While reading the documentation draft for FTS5, I spotted this on > section 4.3.3 . > I believe the first example is missing quotation marks around the word > 'porter': > > *-- Two ways to create an FTS5 table that uses the porter tokenizer to > -- stem the output of the default tokenizer (unicode61). * > CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = porter); > CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61'); Hi, I think it's Ok as is. An "option value" can be eitehr an FTS5 bareword or string literal. See the 4th paragraph here: https://www.sqlite.org/fts5.html#section_4 Dan.
[sqlite] sqlite3 file as database
> At 01:07 15/09/2015, you wrote: > >--- > >SQUISH was a database format for storing messages in FidoNet systems. > >--- > > Geez, I don't even recall my FidoNet node number aka address... Time > must have flown by faster than I thought. Hehehehe. Those were the good old days ... trying to get just a few more bps out of a serial modem link. I still have a couple each of the original National Semiconductor 16550A, 16550AF, and 16550AFN that I got as samples ... 1:148/218 1:250/714 1:250/702 1:250/703 1:250/750
[sqlite] UNIQUE constraint failed: ip_addr.pid
Hello Petr, defining the column pid as INTEGER PRIMARY KEY you added an implicit contraint; a primary key means that only one record with a given value of pid can exist in the table. See https://www.sqlite.org/lang_createtable.html#rowid Martin Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: > Have following table: > > CREATE TABLE ip_addr > ( > /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, > /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, > /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ > /*! Status: 1 - Allowed, Asigned to concrete customer */ > /*! Status: 2 - Disallowed, Assigned to blocked user */ > /*! Status: 3 - Disallowed, Assigned to history user */ > /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, > /*! Type: 1 - Private */ > /*! Type: 2 - Public */ > /*! Type: 3 - IPv6 */ > /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, > /*! Date of blocking %%e */ blocked_at INTEGER, > /*! Blocking note %%f */ blocking_note VARCHAR > ); > ) > > > If inserting row containing PID already exist in table, sqlite generate > %subj% error. But there is not such constraint in database definition. Did I > miss something? > > L. > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] UNIQUE constraint failed: ip_addr.pid
Have following table: CREATE TABLE ip_addr ( /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL, /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL, /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */ /*! Status: 1 - Allowed, Asigned to concrete customer */ /*! Status: 2 - Disallowed, Assigned to blocked user */ /*! Status: 3 - Disallowed, Assigned to history user */ /*! Status of IP address %%c */ ip_status INTEGER NOT NULL, /*! Type: 1 - Private */ /*! Type: 2 - Public */ /*! Type: 3 - IPv6 */ /*! Type of IP address %%d */ ip_type INTEGER NOT NULL, /*! Date of blocking %%e */ blocked_at INTEGER, /*! Blocking note %%f */ blocking_note VARCHAR ); ) If inserting row containing PID already exist in table, sqlite generate %subj% error. But there is not such constraint in database definition. Did I miss something? L.
[sqlite] sqlite3 file as database
Tim Streater wrote: > I don't use any extension at all for SQLite databases. With SQLite's habit of appending "-journal" (or "-wal"/"-shm") to the end of the file name, the extension would look weird. For this reason, I tend to use names like "some-data". (I also prefer to use journal_mode=truncate, so that users aren't surprised when a -journal file shows up, and try to do clever things to it.) Regards, Clemens
[sqlite] sqlite3 file as database
On 14 Sep 2015 at 19:29, Warren Young wrote: > On Sep 14, 2015, at 8:38 AM, Stephen Chrzanowski > wrote: >> >> There are many extensions of the same .. err.. >> name(?)...value(?)..structure(?) that are completely different things. > > It?s fairly bad in the electronics engineering world, where it seems like > half the tools use *.sch for schematics and *.brd/pcb for printed circuit > board layouts, but none of the tools agree on the format of the actual file > data. If you have two such apps installed, you have to make a hard choice > about which app becomes the default to open such files, and occasionally have > to fix it when updating the other app, as it takes over the extensions again. > > This widespread unwillingness to get beyond the 8.3 limits, particularly on > Windows, is annoying. We haven?t had to worry about compatibility with > 3-character file extensions since Windows NT 3.5 and Windows 95, two decades > ago now. Of course in a sensible world, OS providers would all have implemented a common metadata API, and no one would need or use extensions. -- Cheers -- Tim
[sqlite] sqlite3 file as database
On 2015-09-14 06:17 PM, Simon Slavin wrote: > On 14 Sep 2015, at 3:38pm, Stephen Chrzanowski wrote: > >> *.SQL appears to be a common thing for not only Structured Query >> Language, but also "Squish message base lastread pointers" -- > Whatever the heck that is. > > I use *.SQL for text files which contain SQL commands, including the ones the > SQLite tool generates from '.dump'. > > I don't have a good answer about what extension to use for SQLite databases. > At the moment I seem to favour using the extension '.sqlite' but I'd be hard > pressed to make an argument for it. I wonder what I'll do when SQLite4 comes > along. Yeah - I might add that sqlite is very often used as an application file format, which results in a myriad of extensions out there. There is no noticeable convention. I happen to see it lots because when developing the search functions, I use SQLitespeed and just point the search tool to the c:\users\xxx\appdata\ folder on any new system, enter *.* as the search text, tick "include sub-folders" and after about a minute I see a list of every SQLite database in the system. (Well, in Appdata anyway). There are usually loads, but I have never noticed any convention or extension being more prevalent than any other. It's usually just random - and where duplications occur, they usually belong to the same system. ".sqlite" is found somewhat, but if I had to pick one that seems most common, it would just be the: "*.db" (Security-win, I know) For reference, I just checked this pc (as described above) and these are all the valid SQLite DB's in Appdata with their extensions: (Your experience may differ) C:\Users\R.Smith\AppData\Local\Adobe\OOBE\opm.db C:\Users\R.Smith\AppData\Local\EvernoteNW\cookies C:\Users\R.Smith\AppData\Local\EvernoteNW\Local Storage\file__0.localstorage C:\Users\R.Smith\AppData\Local\EvernoteNW\Web Data C:\Users\R.Smith\AppData\Local\Evernote\Evernote\Databases\rsmith386.exb C:\Users\R.Smith\AppData\Local\Mozilla\Firefox\Profiles\tvs0coxp.default\OfflineCache\index.sqlite C:\Users\R.Smith\AppData\Local\Steam\htmlcache\Cookies C:\Users\R.Smith\AppData\Local\Steam\htmlcache\Local Storage\https_www.youtube.com_0.localstorage C:\Users\R.Smith\AppData\Local\Steam\htmlcache\Local Storage\http_steamcommunity.com_0.localstorage C:\Users\R.Smith\AppData\Local\Temp\Test1e499fd04-6213-4da6-97a3-209da4fd29eb.prmdc C:\Users\R.Smith\AppData\Roaming\Adobe\Bridge CS6\Cache\data\store C:\Users\R.Smith\AppData\Roaming\Atlantic\AIDB_SysData.cdb C:\Users\R.Smith\AppData\Roaming\Atlantic\AIDB_SysData_Backup.cdb C:\Users\R.Smith\AppData\Roaming\Dropbox\instance1\aggregation.dbx C:\Users\R.Smith\AppData\Roaming\Dropbox\instance1\config.db C:\Users\R.Smith\AppData\Roaming\FDM\SysData\FDM_LocData.rdb C:\Users\R.Smith\AppData\Roaming\FDM\SysData\FDM_Logs.rdb C:\Users\R.Smith\AppData\Roaming\FileZilla\queue.sqlite3 C:\Users\R.Smith\AppData\Roaming\IPView\SysData\Data\IPDB_CalcData.idb C:\Users\R.Smith\AppData\Roaming\IPView\SysData\Data\IPDB_ImptData.idb C:\Users\R.Smith\AppData\Roaming\IPView\SysData\Data\IPDB_ImptData_Old_Planners_Backup.idb C:\Users\R.Smith\AppData\Roaming\IPView\SysData\Data\IPDB_StdLists.idb C:\Users\R.Smith\AppData\Roaming\IPView\SysData\Data\IPDB_UserData.idb C:\Users\R.Smith\AppData\Roaming\IPView\Sys_Cache.cdb C:\Users\R.Smith\AppData\Roaming\Listary\UserData\History_v2.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\content-prefs.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\cookies.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\evernote_webclipper.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\formhistory.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\healthreport.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\permissions.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\places.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\reading-list.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\storage\default\fileC++Projects+Unity+Temp+TestProj1+tpWebGL+index.html\idb\2083995541%s2fFbid.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\storage\default\https+++google.github.io\idb\3823323449mbvd.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\storage\default\https+++uplay.ubi.com\idb\533021264u3pVlya.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\storage\default\https+++www.maxmind.com\idb\581034704_b_Dmsmwaip.sqlite C:\Users\R.Smith\AppData\Roaming\Mozilla\Firefox\Profiles\tvs0coxp.default\storage\permanent\chrome\idb\2918063365piupsah.sqlite
[sqlite] sqlite3 file as database
I've decided to use .sq3 ; I'm mainly under Windows where a dedicated extension is pretty handy to launch a DB manager and sq3 doesn't seem to collide with much things around and leaves ample room for sq4, sq5, ... -- jcd
[sqlite] sqlite3 file as database
On 2015-09-14 04:07 PM, Drago, William @ CSG - NARDA-MITEQ wrote: > Why do people use .db3 for sqlite database files? In my experience .db3 is > the file extension for dBase III database files. Might I add here that if I re-read the OP's question, it might actually be that he had a DBIII file which he wanted to export to SQlite, and maybe not a .db3 file that was already an sqlite file.
[sqlite] sqlite3 file as database
On 14 Sep 2015, at 5:48pm, Brian Willner wrote: > You > could argue the same applies to SQLite file naming conventions as well. May work for Windows. On the Mac (and other forms of Unix) the 'file' command looks at the file header and, thanks to SQLite's consistent file headers, will identify it for you: 178:~ simon$ sqlite3 ~/Desktop/test SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE a(b INT); sqlite> .quit 178:~ simon$ file ~/Desktop/test /Users/simon/Desktop/test: SQLite 3.x database 178:~ simon$ Simon.
[sqlite] sqlite3 file as database
On 14 Sep 2015, at 3:38pm, Stephen Chrzanowski wrote: > *.SQL appears to be a common thing for not only Structured Query > Language, but also "Squish message base lastread pointers" -- Whatever the heck that is. I use *.SQL for text files which contain SQL commands, including the ones the SQLite tool generates from '.dump'. I don't have a good answer about what extension to use for SQLite databases. At the moment I seem to favour using the extension '.sqlite' but I'd be hard pressed to make an argument for it. I wonder what I'll do when SQLite4 comes along. Simon.
[sqlite] sqlite3 file as database
On 14 Sep 2015 at 15:07, William Drago wrote: > Why do people use .db3 for sqlite database files? In my experience .db3 is the > file extension for dBase III database files. I don't use any extension at all for SQLite databases. In any case, for the majority of them, the user chooses the name and they have no need to know that it's an SQLite database. -- Cheers -- Tim
[sqlite] sqlite3 file as database
> > *.SQL appears to be a common thing for not only Structured Query > > Language, but also "Squish message base lastread pointers" -- > Whatever the heck that is. SQUISH was a database format for storing messages in FidoNet systems. Originally designed by Scott Dudley as part of Maximus, the format was eventually widely used because of its speed and relatively efficient design compared to other message-store formats. Squish was also an application of the same name for processing messages in and out of Squish message stores and a standard FidoNet inbound/outbound transport directory structure used by such applications as BinkleyTerm from BitBucket Software. BinkleyTerm had an unusual license -- it was Public Domain and you were pretty much free to do as you pleased with it -- with the only caveat being that if you broke it you owned both halves.
[sqlite] Bug in SQLite 3.8.11.1 source code
Hello ! This is a real simple bug fix but it seems that no one is caring about it !!! Cheers ! > Sat Sep 12 2015 8:52:04 pm CEST CEST from "chris0e3" >Subject: [sqlite] Bug in SQLite 3.8.11.1 source code > > Hello, > > I was just looking at updating to SQLite 3.8.11.1 when I spotted what >appears to be an error. > Here?s a patch to fix it: > > --- sqlite-amalgamation-3081101/sqlite3.c 2015-07-30 03:06:58.0 >+0100 > +++ sqlite3.c 2015-09-12 19:03:55.0 +0100 > @@ -92265,7 +92265,7 @@ > } > pParse->checkSchema = 1; > } > -#if SQLITE_USER_AUTHENICATION > +#if SQLITE_USER_AUTHENTICATION > else if( pParse->db->auth.authLevel sqlite3ErrorMsg(pParse, "user not authenticated"); > p = 0; > > > I think it?s pretty self explanatory. > > Regards, > > CHRIS > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] FTS5 stopwords
I?ve implemented a custom ranker in SQLite that is similar to SPH_RANK_SPH04 using FTS4 (BM25 + word distance and distance to beginning of text). The only thing that wasn?t possible out of the box using FTS4 was to get the distance between found matches as distance between them (how many words are between matches). FTS4 callback allows currently only to get this distance as byte offset, but not word distance. As far as I remember, there are internal data structures in FTS4 which would allow this. But these structures aren?t available to the callback. Anyways, it will be nice if FTS5 would have a feature to get the distance between matched words expressed as word / token distance. Cheers Ben Am 14.09.15 16:13 schrieb "sqlite-users-bounces at mailinglists.sqlite.org on behalf of Abilio Marques" unter : >SPH_RANK_SPH04
[sqlite] sqlite3 file as database
Why do people use .db3 for sqlite database files? In my experience .db3 is the file extension for dBase III database files. -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin > Sent: Saturday, September 12, 2015 11:50 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] sqlite3 file as database > > > On 12 Sep 2015, at 1:19pm, s.movaseghi at eramtec.ir wrote: > > > I have a database file as database.db3 but I have to use > database.sqlite3 . > > How can I convert the db3 file to sqlite3 file? > > If it is actually a SQLite database already then just rename the file. > SQLite does not care what the file is called. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
[sqlite] sqlite3 file as database
On Sep 14, 2015, at 1:02 PM, Tim Streater wrote: > > On 14 Sep 2015 at 19:29, Warren Young wrote: > >> We haven?t had to worry about compatibility with >> 3-character file extensions since Windows NT 3.5 and Windows 95, two decades >> ago now. > > Of course in a sensible world, OS providers would all have implemented a > common metadata API, and no one would need or use extensions. There have been many such APIs and file formats. HFS (creator+type code), IPTC/EXIF/XMP/Dublin Core, EDI (balkanized into EDIFACT, X12, ODETTE?), MARC records (similarly balkanized), etc. They?re all ?standards? in the XKCD sense: https://xkcd.com/927/
[sqlite] How stable is FTS5 considered?
On 09/14/2015 01:45 AM, Abilio Marques wrote: > Hi, > > > > I've been into this mailing list for a month now, and I think I've heard > FTS5 mentioned a couple of times. Back when I first saw it, I remember it > to be labeled with something close to beta or preliminary. > > > > Long story short, I've previously worked with a dedicated search engine > called Sphinx Search. One of the things people love about it, is it's > ability to be linked to Snowball (http://snowball.tartarus.org), which is a > project created by Dr. Martin Porter. This code includes stemmers in > several other languages (Spanish, French, Portuguese, Italian, German, > Dutch, Swedish, Norwegian, Danish, Russian, Finnish and even an improved > English version), which would be an upgrade over the present FTS5 condition: > > > > "The porter stemmer algorithm is designed for use with English language > terms only - using it with other languages may or may not improve search > utility." > > > > I'm thinking about a possible approach to get Snowball working with SQLite. > I believe an extension is the way to go, as Snowball is published under the > BSD license (and so I guess it cannot be mixed with public domain code). > > > > But I have no experience mixing BSD and public domain, so anyone with more > information can shed a light on that matter? > > > > Second, and the most important question for me is, can I consider FTS5 > stable enough to start working on the extension? I think so. The custom tokenizer API changed just recently in order to support synonyms: http://www.sqlite.org/src/info/0b7e4ab8abde3ae3 but I don't expect it to change again. The updated API is described here: http://sqlite.org/draft/fts5.html#section_7_1 For example code, see the built-in tokenizers: http://www.sqlite.org/src/artifact/f380f46f341af9c9 Dan.
[sqlite] sqlite3 file as database
Microsoft security best practices is never to name anything .db They recommend obfuscating the function of the file and putting some strange or random (.bob) file extension. This is for when you have an active intrusion, you are not handing them what to take on a silver platter. You could argue the same applies to SQLite file naming conventions as well. > Why do people use .db3 for sqlite database files? In my experience > .db3 is the file extension for dBase III database files.
[sqlite] sqlite3 file as database
On Sep 14, 2015, at 8:38 AM, Stephen Chrzanowski wrote: > > There are many extensions of the same .. err.. > name(?)...value(?)..structure(?) that are completely different things. It?s fairly bad in the electronics engineering world, where it seems like half the tools use *.sch for schematics and *.brd/pcb for printed circuit board layouts, but none of the tools agree on the format of the actual file data. If you have two such apps installed, you have to make a hard choice about which app becomes the default to open such files, and occasionally have to fix it when updating the other app, as it takes over the extensions again. This widespread unwillingness to get beyond the 8.3 limits, particularly on Windows, is annoying. We haven?t had to worry about compatibility with 3-character file extensions since Windows NT 3.5 and Windows 95, two decades ago now. Call your files *.myspiffyapp, or something completely unique, please. Just because the data inside is managed by SQLite doesn?t mean all SQLite-based apps would like to open your app?s files.
[sqlite] OT: Oracle functions for SQlite
On Sep 13, 2015, at 3:06 AM, Domingo Alvarez Duarte wrote: > > Due the way sqlite manages it's source code (with fossil-scm) I propose to > anyone that has any extension/custom sqlite code fork this project on github: Fossil allows anonymous clones, and the Fossil server on sqlite.org is configured not to allow checkins from anonymous users. Therefore, your local changes affect your personal repository only, just as with Github. The only difference is that your personal fork of the repository isn?t automatically shared with the entire world. When the time does come to share, Fossil has the concept of ?bundles,? which allows you to send a subset of your local repository to someone with permission to check it in, preserving all details of the change you made. Not just file content changes, but also checkin comments, branch points, merges, etc. Fossil bundles are far better than patch(1) files if your change is complicated enough to need more than a single checkin. http://fossil-scm.org/xfer/help?cmd=bundle The simplest option is ?export --branch?, since that isolates your local changes from any made to the main repo?s trunk since your initial clone.
[sqlite] user defined function returning a result set
Hello,
I think I have read on this mailing list that sqlite now has functions able
to return rows. (but cannot find it anymore)
I am interested about this new functionality. Would it be possible to see a
very basic sample of it?
Do you think we can implement a kind of CSV reader with this new function?
kind of: select * from CSVRead('/path/to/my/file.csv')
Please let me know.
Best regards,
Sylvain
[sqlite] sqlite3 file as database
I also should mention that before anyone harps about DB3 being reserved for DBaseIII, *.SQL appears to be a common thing for not only Structured Query Language, but also "Squish message base lastread pointers" -- https://en.wikipedia.org/wiki/Alphabetical_list_of_filename_extensions_%28S%E2%80%93Z%29 There are many extensions of the same .. err.. name(?)...value(?)..structure(?) that are completely different things. Look at .DAT files. On Mon, Sep 14, 2015 at 10:31 AM, Stephen Chrzanowski wrote: > That is the default extension for DBaseIII, but I've not heard any windows > or linux or mac system of the past 10 years have anything to do with a > DBaseIII files. There may be edge cases for old accounting applications, > but it is such old technology, I do doubt it is in use for anything of new > importance in a business situation. > > Anything I code with SQLite I do use DB3 as the file extension, simply > because it associates that it is a database file and that it is for SQLite > 3. Just personal convention. I typically mentally reserve *.SQL as text > files, and have my compiler build resource files based on the contents of > those text files. > > > On Mon, Sep 14, 2015 at 10:07 AM, Drago, William @ CSG - NARDA-MITEQ < > William.Drago at l-3com.com> wrote: > >> Why do people use .db3 for sqlite database files? In my experience .db3 >> is the file extension for dBase III database files. >> >> -- >> Bill Drago >> Staff Engineer >> L3 Narda-MITEQ >> 435 Moreland Road >> Hauppauge, NY 11788 >> 631-272-5947 / William.Drago at L-3COM.com >> > >
[sqlite] sqlite3 file as database
That is the default extension for DBaseIII, but I've not heard any windows or linux or mac system of the past 10 years have anything to do with a DBaseIII files. There may be edge cases for old accounting applications, but it is such old technology, I do doubt it is in use for anything of new importance in a business situation. Anything I code with SQLite I do use DB3 as the file extension, simply because it associates that it is a database file and that it is for SQLite 3. Just personal convention. I typically mentally reserve *.SQL as text files, and have my compiler build resource files based on the contents of those text files. On Mon, Sep 14, 2015 at 10:07 AM, Drago, William @ CSG - NARDA-MITEQ < William.Drago at l-3com.com> wrote: > Why do people use .db3 for sqlite database files? In my experience .db3 is > the file extension for dBase III database files. > > -- > Bill Drago > Staff Engineer > L3 Narda-MITEQ > 435 Moreland Road > Hauppauge, NY 11788 > 631-272-5947 / William.Drago at L-3COM.com >
[sqlite] FTS5 stopwords
?Hi, I know I'm a newcomer into the SQLite project, but I'm excited about what FTS5 has to offer. To me it seems simple and powerful, and has some really nice ideas. Is it possible for me to contribute on the module, or is it too late for that? I would like to mention two new ideas I would offer to introduce. First, a customizable list of stopwords: https://en.wikipedia.org/wiki/Stop_words ? (I didn't find anything similar to that in the documentation, am I missing something?) I know I can add it via a custom tokenizer, but wouldn't it be useful to have it straight out of the box? Also, I would like to mention the usefulness of some statistics to create more advanced ranking formulas. Things like: the Longest Common Subsequence between query and document, number of unique matched keywords, etc. These and other values are really useful in applications where bm25 is not suitable or enough. I come from using an engine called Sphinx Search (used on huge things like Craigslist), which offers such factors. Using them, they have defined rankers that mix bm25 with proximity, and some other they call SPH_RANK_SPH04, which includes a weighting boost for the result appearing at the beginning of the text field, and a bigger boost if its an exact match: http://sphinxsearch.com/docs/latest/builtin-rankers.html The formulas (in sphinx higher is better) for them are: http://sphinxsearch.com/docs/latest/formulas-for-builtin-rankers.html And the list of supported factor is: http://sphinxsearch.com/docs/latest/ranking-factors.html. Of course having all of them would be overkill, but if you find them interesting, we can get the most useful ones, allowing people to build rankers to their own needs. ?Once again, you people are the experts and know if such ideas are feasible and where is the right place to include them, so please tell me your opinions. ?
[sqlite] What is the best page cache size when the database is larger than system RAM?
David Barrett wrote: > If I have a database that is larger than the system's physical RAM, > am I correct in thinking I should actually set a very *small* page cache so > as to avoid "double caching" the same pages in both sqlite and the file > cache? The default setting (2048 pages) already is very small. Reading pages from the OS's file cache has more overhead than fetching them from SQLite's internal page cache, so the answer is "it depends". You have to try it out with both settings. Regards, Clemens
[sqlite] Bug in SQLite 3.8.11.1 source code
On Sep 14, 2015 6:48 AM, "Domingo Alvarez Duarte" wrote: > > Hello ! > > This is a real simple bug fix but it seems that no one is caring about it !!! It is Monday morning. The report is two days old and thus came in on a weekend. I think your overreaction may be premature. I'm sure someone will address it Real Soon Now if they haven't already. > > > Cheers ! > > Sat Sep 12 2015 8:52:04 pm CEST CEST from "chris0e3" < chris0e3 at gmail.com> > >Subject: [sqlite] Bug in SQLite 3.8.11.1 source code > > > > Hello, > > > > I was just looking at updating to SQLite 3.8.11.1 when I spotted what > >appears to be an error. > > Here?s a patch to fix it: > > > > --- sqlite-amalgamation-3081101/sqlite3.c 2015-07-30 03:06:58.0 > >+0100 > > +++ sqlite3.c 2015-09-12 19:03:55.0 +0100 > > @@ -92265,7 +92265,7 @@ > > } > > pParse->checkSchema = 1; > > } > > -#if SQLITE_USER_AUTHENICATION > > +#if SQLITE_USER_AUTHENTICATION > > else if( pParse->db->auth.authLevel > sqlite3ErrorMsg(pParse, "user not authenticated"); > > p = 0; > > > > > > I think it?s pretty self explanatory. > > > > Regards, > > > > CHRIS > > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS5 documentation typo
While reading the documentation draft for FTS5, I spotted this on section 4.3.3 . I believe the first example is missing quotation marks around the word 'porter': *-- Two ways to create an FTS5 table that uses the porter tokenizer to -- stem the output of the default tokenizer (unicode61). * CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = porter); CREATE VIRTUAL TABLE t1 USING fts5(x, tokenize = 'porter unicode61');
[sqlite] user defined function returning a result set
On 9/14/15, Sylvain Pointeau wrote:
> Hello,
>
> I think I have read on this mailing list that sqlite now has functions able
> to return rows. (but cannot find it anymore)
https://www.sqlite.org/draft/vtab.html#tabfunc2
>
> I am interested about this new functionality. Would it be possible to see a
> very basic sample of it?
>
> Do you think we can implement a kind of CSV reader with this new function?
> kind of: select * from CSVRead('/path/to/my/file.csv')
>
No. The table-valued function needs to return a predefined number of
columns, but a CSV file can have a varying number of columns.
To do this, you'd have to create a virtual table that actually gets
instantiated per CSV file:
CREATE VIRTUAL TABLE temp.file1 USING
csvFileReader('/path/to/my/file.csv');
SELECT * FROM file1;
DROP TABLE file1;
In the above, the xCreate method of the virtual table implementation
could peek at the CSV file to see how many columns it has before
declaring how many rows it intends to return. Which is what you need.
--
D. Richard Hipp
drh at sqlite.org
[sqlite] What is the best page cache size when the database is larger than system RAM?
On Sun, Sep 13, 2015 at 8:18 PM, David Barrett wrote: > Hello! If I have a database that is larger than the system's physical RAM, > am I correct in thinking I should actually set a very *small* page cache so > as to avoid "double caching" the same pages in both sqlite and the file > cache? > > To clarify, if the database is *smaller* than system RAM, I imagine the > best case scenario (for read performance at least) would be to set a page > cache size equal to or larger than the database size. This would ensure > there is always enough RAM set aside to store up to the entire database in > the page cache. (Some subset of the database would also be stored in the > file cache, but once the page cache is warmed up, you would never touch the > file cache.) > > However, if the database is *larger* than system RAM, even assigning all of > it to the page cache wouldn't enable it to store the whole database in RAM, > no matter how "warmed up" it gets. Furthermore, every time you "miss" the > page cache, you would always *also* miss the file system cache (because the > only way a page makes it into the page cache is by first reading it from > the file system). So every page read into the page cache requires not just > that memory, but the same amount of memory in the file system cache. So > the larger the ratio between database and system size, the more likely you > spend 2x the RAM for each page in the page cache. > > Accordingly, it would seem that the best configuration for a database > significantly larger than the system RAM (assuming a perfectly uniform > access pattern where every page is accessed with equal probability) would > be to disable the page cache entirely -- and thereby preserve as much RAM > as possible for the file cache. Yes, it would be better to have it in the > page cache than the file cache, but I imagine it's vastly better to read > from the file cache than to go to the disk. Is this right? > For a read-mostly database, enabling memory-mapped I/O would work well, if your system supports it. In that case, SQLite handles pages using a small wrapper structure which references the memory-mapped data which is in the filesystem buffers directly. If that isn't an option, you shouldn't disable the page cache entirely. SQLite uses b-trees to organize data, so even if every piece of data has equal likelihood for access, the b-tree interior node pages have _much_ greater likelihood of access than leaf-node pages. So your cache should be big enough to hold all/most of the interior pages, plus enough extra space to make sure they aren't being accidentally evicted too often. The OS may be able to figure out the right caching pattern for these pages, but SQLite certainly has more knowledge about how they're used. There is the opposite option, contrive to have the database opened with an O_DIRECT type access, so that the filesystem reads into SQLite's buffers and doesn't keep a cache. You'd still want to leave enough free memory for the filesystem to cache things like indirection nodes, but usually those are very efficiently packed. Then you can devote lots of memory to SQLite's cache. Of course, any of these are just fiddling while the Titanic sinks. When faced with a need to do literal random access to a file bigger than memory, it almost doesn't matter how you structure things, it's going to be painful. You'd be much better off to rearrange your access patterns to be more streaming in nature, then you can just allocate modest cache to SQLite and let the filesystem do appropriate read-ahead. -scott
