Re: [sqlite] How to find the primary key ?
<[EMAIL PROTECTED]> wrote : > Bertrand Mansion wrote: >> Hi, >> >> I have been through the archives on yahoo groups and found a mail that said >> the current way to find the primary key in sqlite is to write your own sql >> parser. This was back in May 2003. >> >> Is this still the only way ? >> This looks tough to achieve, there seems to be so many things to take into >> consideration. Any pointers or sample code would be appreciated. :) >> > > The 6th column of the result of the TABLE_INFO pragma is true > for primary key columns and false for other columns. Thanks Richard, that worked :) It might be a good idea to add that in the documentation or in a changelog (a bit late now...) as I was actually using 2.8.6 and it didn't have this feature. Unless I missed something of course... Happy holidays, Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] UNIQUE vs PRIMARY KEY
<[EMAIL PROTECTED]> wrote : > Will Leshner wrote: >> >> On Dec 31, 2003, at 4:56 AM, D. Richard Hipp wrote: >> >>> 1. There can only be one PRIMARY KEY, but multiple UNIQUE constraints >>> are allowed. >>> >>> >> >> Ah. This one I didn't know. I thought that multiple columns could >> participate in the PRIMARY KEY. But I just tested it and sure enough I >> get an error when I try to make more than one column be the PRIMARY KEY. >> > > You CAN have a multi-column primary key. You just have to do it all > at once using a "PRIMARY KEY" clause, not the PRIMARY KEY attribute on > the column. Ex: > > CREATE TABLE ex1( > a TEXT, > b VARCHAR(10), > c FLOAT, > PRIMARY KEY(b,a) > ); > > In the example above, the primary key consists of columns b and a, > in that order. Richard, Is the order important for sqlite ? What if (a, b) is used instead of (b, a) ? Thanks, Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Full text search implementation
<[EMAIL PROTECTED]> wrote :
>
>
>> -Original Message-
>> From: David Morel [mailto:[EMAIL PROTECTED]
>> Sent: 16 January 2004 17:32
>> To: Brad Campbell
>> Cc: George Ionescu; [EMAIL PROTECTED]
>> Subject: Re: [sqlite] Full text search implementation
>>
>>
>>> My regex patch should do that
>>>
>>> SELECT * FROM Categories WHERE CategoryDescription RLIKE
>> 'Beverages" and CategoryDescription NOT
>>> RLIKE 'Whiskey';
>>>
>>
>> In such a simple string matching I suspect a regex search is totally
>> overkill... that's ok for a db containing 1000 rows, but try it on
>> 700,000 rows (390Mb) like the one i have here ;-)
>>
> I don't think that your LIKE version will perform much better - SQLite
> doesn't use indexes when doing LIKE comparisons.
>
>>> -
>>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>>> For additional commands, e-mail: [EMAIL PROTECTED]
>> --
>> ***
>> [EMAIL PROTECTED]
>> OpenPGP public key: http://www.amakuru.net/dmorel.asc
>> 28192ef126bc871757cb7d97f4a44536
>>
>>
>>
>
> Using LIKE as a means of doing a full text search is virtually useless in
> the real world of text retrieval. The query take no account of context,
> which is essential when dealing with intelligent text queries.
> A full-on full text engine (BASIS, BRS etc) has to maintain a set of
> meta-data for each text column that can be searched i.e.
>
> When data is added to a text column, the text must be parsed to split it up
> into searchable words using a break character list.
> These words must then be reduced to their searchable stem (pluralisation,
> inflexions, Porter stemming etc) and insignificant words ('a', 'and', 'the'
> etc (stop words)) removed.
> The words are then added to the column index - the posting in the index
> contains the row ID, the start character position of the word and the
> original length of the word. It may also contain grammatical context info
> such as the sentence/paragraph number.
> At this point, some systems may also add into the index other variants of
> the words (common mispellings, morphs etc) to improve recall.
>
> Now, when you do a search on that column, the system has to parse your query
> terms, stem them and weed out stop words in the same way as when data was
> added. It then looks up the words in the column index and collates the
> proximity of the words.
> There's not normally much point in searching for 'SQLite' and 'document' if
> you can't tell the system to find them with the same sentence, paragraph, or
> adjacent.
>
> As you can see, a proper full text search engine is considerably more work
> than it first looks. Add onto this all the complexities of applying this to
> different languages and you have a pretty major coding effort on your hands.
>
> I have a working prototype of such a beast using SQLite that I'd be
> interested in sharing the devlopment of, if anyone is interested?
I agree. You just forgot about the scoring algorithm, a full text query
should also be able to return a score.
IMO, search engines (with tokenizer, indexer, stemmer, stopwords,
substrings, fuzzy, binary converter...) offer a good choice in terms of
features when it comes to full text search. It seems that Mnogosearch
(http://www.mnogosearch.org) has included sqlite as their default db for
their search engine software. So this might be a good companion to sqlite.
The best solution would be IMO to have all this optionally integrated into
the database engine. That's what Oracle does with Context and last time I
have used it, it was working very well (it was with Oracle 9i). But that
might also make the engine too heavy.
Mysql offers fulltext but it is only available AFAIK on the MyISAM table
format. So you can't have foreign key constraints (available on InnoDB
format) and full text at the same time.
Bertrand Mansion
Mamasam
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Temporary indexes
Hi, Could someone please explain me how temporary indexes work ? I have tried the query "CREATE TEMP INDEX tmpidx ON table(field)" without success. Same thing if the table is also temporary itself. Same thing with TEMPORARY UNIQUE indexes and same thing when I use TEMP instead of TEMPORARY. The error I get is : 'near "INDEX": syntax error' The query works fine when I remove TEMP. Is this feature disabled ? Do I have to enable it at compile time ? I am using 2.8.11 on Mac OS X. Thanks for your help, Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Primary key and index
Hi, I have another question about indexes in Sqlite. Does the declaration of an INTEGER PRIMARY KEY implies the creation of an index ? I am asking because I have noticed a performance boost when I create an index on an INTEGER PRIMARY KEY when sorting rows with the primary key column. More generally, does declaring any column primary key implies that this column will be indexed ? I am not sure about that because when I do a PRAGMA index_list(), there is no index shown for the primary key. Thanks for any hints, Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Primary key and index
<[EMAIL PROTECTED]> wrote : > Try > >select * from sqlite_master >where tbl_name='yourTableName' and type='index' > > This is all explained in the Create Table section of > lang.html and in the FAQ. Hi, Did you read my question ? I am afraid your answer is totally out of scope. Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Primary key and index
<[EMAIL PROTECTED]> wrote : > Bertrand, > > This is from the doc on the web > > Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary > key. However, if primary key is on a single column that has datatype INTEGER, > then that column is used internally as the actual key of the B-Tree for the > table. This means that the column may only hold unique integer values. (Except > for this one case, SQLite ignores the datatype specification of columns and > allows any kind of data to be put in a column regardless of its declared > datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the > B-Tree key will be a automatically generated integer. The B-Tree key for a row > can always be accessed using one of the special names "ROWID", "OID", or > "_ROWID_". This is true regardless of whether or not there is an INTEGER > PRIMARY KEY. > > So adding the Primary Key does create an index. > > you can verfiy this by doing > > create table a (b primary key,c); > select * from sqlite_master; Thank you, I wasn't sure this info was accurate as there seems to be a lot of things that have changed since the documentation was written. Still, if INTEGER PRIMARY KEY actually creates an UNIQUE index as stated in the doc, then why doesn't it show in PRAGMA index_list() ? Unless I am still missing something, I think the function would be more helpful if it could return the exhaustive list of indexes, including the ones created as INTEGER PRIMARY KEY. Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Re: [inbox] Re: [sqlite] Primary key and index
<[EMAIL PROTECTED]> wrote : > Greg Obleshchuk wrote: >> >> So in these cases there is no benefit from creating an index on a column >> that is INTEGER PRIMARY KEY? >> > > Putting an index on an INTEGER PRIMARY KEY will make INSERT, > DELETE, and UPDATE slower since the index must be maintained. > But no SELECT will ever use the index. So adding an index > to an INTEGER PRIMARY KEY is less than no benefit - it hurts. > > See ticket #292. If you say "UNIQUE PRIMARY KEY" (as some > users want to do) SQLite will create two identical indices > Only one index will ever be used - the other justs wastes > CPU time and disk space. I'll get around to fixing that > someday. Probably at the same time I should rig it so that > attempts to create named indices on PRIMARY KEY are ignored > too. Once that happens, you can create indices on your > INTEGER PRIMARY KEY all you want - SQLite will ignore your > attempts - and everything will work at maximum efficiency > regardless of what you try to do. Thanks for the info, I'll have to review my performance tests to find were they are wrong then. Sorry about the confusion it created. What about the other question on how to know that there is such an internal index for the table ? PRAGMA index_list() does not return this index as it seems hidden. At the moment, I have to do a PRAGMA table_info(), find the primary key, then check if it is not present in PRAGMA index_list(). I can live with that and I guess changing index_list() would be a BC break. But I would be interested to know if there are other more intuitive ways, other than parsing the CREATE TABLE sql query. Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Adding SQL commands
<[EMAIL PROTECTED]> wrote : > I have a similar problem with character encoding in the current SQLite > versions, by the way. I store UTF-8 encoded data from a Python program > using PySQLite using a prepackaged SQLite, but since the prepackaged > libsqlite is built without SQLITE_UTF8, I can't use LIKE et.al. in the > way I want to. It's good to hear that this will be improved in SQLite > 3.0. How will this be improved in sqlite 3.0 ? Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Encodings question
Hi,
I am a bit of a newbie with encodings...
I know that sqlite supports 2 kinds of encodings natively at the moment.
These encodings are ISO-8859-1 and UTF8. The choice of encoding is set at
compile time.
As far as I understand, UTF-8 will read 8859-1 without problem but
ISO-8859-1 will not be able to read UTF-8, unless everything in the UTF8
string uses only 8859-1 codes.
So, the best choice for compatibility and portability seems to be UTF8.
Unfortunately, PHP for example, ships a version of sqlite that is 8859-1
compiled, this means that a lot of people are going to use sqlite with this
charset, without knowing they could benefit from UTF8. So at the moment, I
prefer to stick with ISO-8859-1 in my desktop application.
I have tried to insert the euro symbol in a column and it came out as '?'
Do you have any idea about what is causing this ?
I have read that the euro symbol was supported in an extension of the 8859-1
charset (), is it also supported by sqlite or do I have to switch to UTF8,
something I would like to avoid at the moment (waiting for Sqlite 3 ;) ).
Can you confirm that you don't have any problems with the euro symbol in
your own applications, using 8859-1.
BTW, my desktop app runs on MACOSX so I first convert the user input from
MacRoman to ISO-8859-1, which works fine with accents, then runs it through
sqlite_mprintf("%q", myCString) to escape the string. Before displaying
again the string, I convert it back from 8859-1 to MacRoman.
Thanks for any advices,
Bertrand Mansion
Mamasam
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Encodings question
<[EMAIL PROTECTED]> wrote : > Bertrand Mansion wrote: > >> As far as I understand, UTF-8 will read 8859-1 without problem but >> ISO-8859-1 will not be able to read UTF-8, unless everything in the UTF8 >> string uses only 8859-1 codes. > > You're wrong, I think. > > UTF-8 is a variable length encoding of character codes of the unicode > code page. Iso8869-1 is a definition of a code page, each character is > encoded in exactly one byte. > > Unicode itself is a code page with much more characters than iso8859-1. > > Unicode, iso8859-1 and ASCII code pages share following properties: > > a.) character codes 0 upto 127 in unicode are equal to ASCII codes. > b.) character codes 128 upto 255 in unicode are equal to the iso8859-1 > codes. > > Please note: A 'character code' is _not_ a byte! It's the number of the > position of that character in a code page. The code page in iso8859-1 is > only 8 bits wide and has 256 entries. The unicode code page is 21 bits > wide, and not all positions are assigned to characters. > > In iso8859-1 all 256 character codes are encoded using simply one byte. > The value of the byte is the character position in the code page. > > In UTF-8 character codes 0 upto 127 are encoded in one byte and > character codes above 127 are encoded in _two_ bytes! > > That means the byte value of encoded character codes 0 upto 127 are > equal in UTF-8 and iso8859-1, but character codes above 127 takes two > bytes in UTF-8 and one byte in iso8859-1. > > In iso8859-1 the byte value is always the character code. In UTF-8 this > is only true for character codes 0 upto 127. > > However, in UTF-8 (the unicode code page encoding) you can encode > character codes upto 31 bits wide, using 6 bytes. Thanks for the clear explanations :) Does this mean that as long as I only use ASCII in an UTF8 compiled sqlite library, the db will be also usable with a ISO-8859-1 compiled version of the library, but if I use for instance accentuated characters, it won't be compatible anymore ? I am asking because I once created a 8859-1 db and it could be read and modified in the UTF8 version of the library. I haven't tested the other way though. What will happen if I update fields with accentuated characters in my application compiled with the UTF8 and then try to open the db with let's say PHP sqlite extension ? I'll try to see what happens. On the php site, they warn users: The default PHP distribution builds libsqlite in ISO-8859-1 encoding mode. However, this is a misnomer; rather than handling ISO-8859-1, it operates according to your current locale settings for string comparisons and sort ordering. So, rather than ISO-8859-1, you should think of it as being '8-bit' instead. I am not sure what this means ? It is not recommended that you use PHP in a web-server configuration with a version of the SQLite library compiled with UTF-8 support, since libsqlite will abort the process if it detects a problem with the UTF-8 encoding. So, it looks like it is recommended not to use UTF8. But how then can I deal with characters like the euro symbol ? I guess that I am stuck ? Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] ISO 8859
Greg Obleshchuk wrote: >Hello, >I had this guy email me about a problem about using ISO8859 character . He >has this in the DB >Handhilfsbetätigungssatz > >but when queried using my wrapper it returns Handhilfsbet..tigungssatz > >When using SQLITE.EXE the results are displayed fine. When I debug the code >the char * returned in the call-back event for this column has this >character as value -124 . Which I assume is an overflowed 7 bit value. Can >anyone help me understand how to fix this in the source? I am not an expert in encodings but just a guess, this might be related to the fact that the "Handhilfsbetätigungssatz" word was typed on another machine using another encoding ? For example, if your wrapper does not enforce the use of ISO-8859-1, then if the user is on a Mac, the characters inserted will use the Mac Roman encoding. Then, if you display it back on Windows, the characters will show differently if you don't convert them. Of course, I might be all wrong here as I am trying to figure that out as well myself :) In my application, I think I will let the user choose which encoding she wants to use (through Preferences for example). The safe way might be to use UTF-8. But this might make the databases bigger ? And there are apparently problems using the UTF-8 version of sqlite with PHP (although I still don't understand what the problems can be ???). Bertrand Mansion Mamasam - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re[2]: [sqlite] PHP 5.0.2 / Win sqlite_query problem
Radek Hulán wrote: >>> When I try to execute the same query using sqlitecc.exe it works just fine. >>> > >DRH> This fact suggests that the problem is in PHP or the application program, >DRH> not in SQLite. > >There can hardly be any problem with sqlite_query($db,$query) application >code... Is SQLite in PHP 5.0.2 supposed to run correctly? I'm pretty sure this >is not my bug... > >Any success stories using PHP 5.0.2 and SQLite? > >Anyway, will try the same code on a Linux box, maybe it will work better.. There is no problem with PHP sqlite code, the problem must be in your code or your environment. Are you sure the database is open ? Are you sure it is writable ? Can your webserver user/group write to the directory of your database ? Bertrand Mansion Mamasam
Re: [sqlite] SQLite Book
Tiago Dionizio wrote: >I just found that there is a book about SQLite and thought you would >like to know. > >link to page: http://www.quepublishing.com/title/067232685X > >Book Description > >SQLite is a small, fast, embeddable database. What makes it popular is >the combination of the database engine and interface into a single >library as well as the ability to store all the data in a single file. >Its functionality lies between MySQL and PostgreSQL, however it is >faster than both databases. > >In SQLite, author Chris Newman provides a thorough, practical guide to >using, administering and programming this up-and-coming database. If >you want to learn about SQLite or about its use in conjunction with >PHP this is the book for you. I prefer to wait for DR Hipp's book... :) Bertrand Mansion Mamasam
Re: [sqlite] OS X/PHP5/SQLite Build> fatal error: no input grammar
spartian wrote: > In this process I'm building mod_php from the /usr/local/src/php-5.0.2 >directory. My configuration line is as follows: > > sudo ./configure --with-apache=../apache_1.3.33 \ > --with-mysql=/usr/local/mysql --enable-track-vars Here is my config for PHP 5.0.2 under OSX: ./configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-apxs --enable-cli --with-zlib-dir=/usr --with-xml --enable-ftp --enable-mbstring --enable-sockets --with-config-file-path=/etc --with-mysql=/usr/local/mysql --with-gd --with-jpeg-dir=/usr/local/lib --with-png-dir=/usr/local/lib --enable-gd-native-ttf --with-freetype-dir=/usr/local/lib I have no problem with sqlite (still 2.8.14 BTW). I wouldn't recommend you to install another version of Apache on OSX than the one in Panther. I would recommend you to use apxs, it makes PHP upgrades easier. That's my development plateform. I never used Fink, nor needed it so your experience may differ. You might also need to install iconv which is straightforward. Good luck, Bertrand Mansion Mamasam
Re: [sqlite] Any way of using an implicit BTree ordering in SQLite?
Andrew Piskorski wrote: >On Sun, Dec 19, 2004 at 12:09:08PM +0100, Bertrand Mansion wrote: > >> For web applications (sqlite being now the default database for PHP5), >> COUNT(*) >> performance is more important than INSERTs and DELETEs performance. The >> obvious > >That sounds VERY much like a matter of opinion. I strongly suspect I >could find plenty of high-powered database backed website developers >who would argue the exact opposite. That's not an opinion, that's the way it is for the large majority of web applications. I am not talking about the few exceptions that do INSERTs on every pages instead of using log files... Bertrand Mansion Mamasam
RE: [sqlite] Proposal: limit the number of columns in a table to 2000.
[EMAIL PROTECTED] wrote: >Darn, I was going to have 500,000 columns in my table. The, each column >would be named like this: Record1, Record2, Record3, and so on up to >Record500. Each column type was going to be varchar and I was going to >store my first record in xml format in my Record1 column of the first row, >and so on down the line. That way I could stored all 500,000 of my records >in a 1 row table, What do you guys think? Please wait April 1st for such jokes... Bertrand Mansion Mamasam
