[sqlite] Need sql query help
Hi, I was hoping someone could tell me if it was possible to select all words containing ceratin letters. Eg If i had a table wit a word column that had a huge list of words and i wanted to select every word that contained all these letters qdsa. Then it would return the words: quads quidas ect but wouldn't return queen because queen does not contain all letters specified. Would it be something like this: select * from word where word = qsda; THat doesn't work by the way :) Any help would be great. Thanks -- View this message in context: http://www.nabble.com/Need-sql-query-help-t1844399.html#a5034347 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] problem with creating a table
Bijan Farhoudi [EMAIL PROTECTED] writes:
I am trying to create table and I would like to name one of the columns
order, but pysqlite does not like it. I do not want to have order__ or any
thing like that. for example the following command does not work:
cur.execute('create table foo(i integer, order integer)')
You're working on dangerous ground when you use reserved words as your column
names. 'order' is a reserved word, as it is used for the ORDER BY clause.
You can do it, though, like this:
create table foo(i integer, [order] integer)
Placing column names in square brackets lets you use reserved words as column
names.
Derrell
[sqlite] Where is the sqlite3.h file?
Hi, I am trying to use the .dll with c but it doesn't come with the eader file. Also, what is the.def file that comes with it? Thanks for any info -- View this message in context: http://www.nabble.com/Where-is-the-sqlite3.h-file--t1844421.html#a5034422 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] problem with creating a table
[EMAIL PROTECTED] wrote:
Bijan Farhoudi [EMAIL PROTECTED] writes:
I am trying to create table and I would like to name one of the columns
order, but pysqlite does not like it. I do not want to have order__ or any
thing like that. for example the following command does not work:
cur.execute('create table foo(i integer, order integer)')
You're working on dangerous ground when you use reserved words as your column
names. 'order' is a reserved word, as it is used for the ORDER BY clause.
You can do it, though, like this:
create table foo(i integer, [order] integer)
Placing column names in square brackets lets you use reserved words as column
names.
Derrell
Thanks for your answer but still I am getting an error message:
sqlite create table foo(i integer, [order] integer);
sqlite .sch
CREATE TABLE foo(i integer, [order] integer);
sqlite insert into foo values(1,2);
sqlite select order from foo
... ;
SQL error: near order: syntax error
Any other idea?
Cheers,
Bijan
Re: [sqlite] Sqlite crashes when i imort huge list
Aha, sorry Richard, it seems you may have been right. I downloaded a different gui call SQLite Administrator and it is importing now. The first gui i used, used 100% of my cpu and crashes, this new one doesn't use much but it is a sloow process.Have been running it for about 10 mins and am only at 3%. Might go to bed now and hopefully it willl be done by the morning. Cheers -- View this message in context: http://www.nabble.com/Sqlite-crashes-when-i-imort-huge-list-t1842991.html#a5034613 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Need sql query help
* onemind [EMAIL PROTECTED] [2006-06-25 16:05]: If i had a table wit a word column that had a huge list of words and i wanted to select every word that contained all these letters qdsa. SELECT * FROM words WHERE word LIKE '%q%' AND word LIKE '%d%' AND word LIKE '%s%' AND word LIKE '%a%' And that’s going to be slow like molasses. It’s not something SQL is well suited to. If you need to do this a lot, I suggest precomputing the kinds of facts about each word that you’ll want to query and storing them in a column or dependent table so you can create indices and query them quickly. Of course if the performance of the simpleminded approach is sufficient for you, then all the better. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/
Re: [sqlite] problem with creating a table
* Bijan Farhoudi [EMAIL PROTECTED] [2006-06-25 16:35]: Thanks for your answer but still I am getting an error message: sqlite create table foo(i integer, [order] integer); sqlite .sch CREATE TABLE foo(i integer, [order] integer); sqlite insert into foo values(1,2); sqlite select order from foo ... ; SQL error: near order: syntax error Any other idea? .headers on SELECT [order] FROM foo Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/
Re: [sqlite] Need sql query help
Thanks, The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. What technology would be best suited for this task? I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Thanks again. -- View this message in context: http://www.nabble.com/Need-sql-query-help-t1844399.html#a5034782 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] problem with creating a table
A. Pagaltzis wrote: * Bijan Farhoudi [EMAIL PROTECTED] [2006-06-25 16:35]: Thanks for your answer but still I am getting an error message: sqlite create table foo(i integer, [order] integer); sqlite .sch CREATE TABLE foo(i integer, [order] integer); sqlite insert into foo values(1,2); sqlite select order from foo ... ; SQL error: near order: syntax error Any other idea? .headers on SELECT [order] FROM foo But how would you know the name of the col is order not [order]?
[sqlite] Re: problem with creating a table
Bijan Farhoudi farhoudi-RazJlWb3c/[EMAIL PROTECTED] wrote: A. Pagaltzis wrote: SELECT [order] FROM foo But how would you know the name of the col is order not [order]? Square brackets are not part of the name. They are delimiters that indicate that whatever's inside is to be treated as an identifier (just as in string literal 'x', quotes are not part of the string but are delimiters indicating that it is indeed a string literal). This syntax allows identifiers that would otherwise be reserved words, as well as identifiers that otherwise would not be valid (e.g. having spaces or punctuation in them, e.g. [this is a valid column name] ). Igor Tandetnik
Re: [sqlite] problem with creating a table
Bijan Farhoudi [EMAIL PROTECTED] writes: A. Pagaltzis wrote: * Bijan Farhoudi [EMAIL PROTECTED] [2006-06-25 16:35]: Thanks for your answer but still I am getting an error message: sqlite create table foo(i integer, [order] integer); sqlite .sch CREATE TABLE foo(i integer, [order] integer); sqlite insert into foo values(1,2); sqlite select order from foo ... ; SQL error: near order: syntax error Any other idea? .headers on SELECT [order] FROM foo But how would you know the name of the col is order not [order]? Surround ALL table and column names in square brackets when you reference them, and that should eliminate the confusion. sqlite CREATE TABLE [foo] ([i] INTEGER, [order] INTEGER); sqlite INSERT INTO [foo] VALUES (1,2); sqlite .mode line sqlite SELECT [order] FROM [foo]; order = 2 sqlite SELECT * FROM [foo]; i = 1 order = 2 sqlite Note that the square brackets are not displayed because they are not part of the column name. They are delimiters to say, Hey, I really, really know what I'm doing; I know that my column names may be reserved words and I want to do this anyway. Since the square brackets let you put nearly any character in table and column names, you can do hard to comprehend stuff, like this: sqlite CREATE TABLE [[x] (i integer); sqlite select * from sqlite_master where type = 'table'; type = table name = foo tbl_name = foo rootpage = 2 sql = CREATE TABLE [foo] ([i] INTEGER, [order] INTEGER) type = table name = [x tbl_name = [x rootpage = 3 sql = CREATE TABLE [[x] (i integer) sqlite Note that the new table name is [x (a square bracket followed by 'x'). Like I said in my first reply to you, using reserved words for column names is dangerous. You should generally try to avoid it. Derrell
RE: [sqlite] Re: problem with creating a table
-Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Sunday, June 25, 2006 10:19 AM To: SQLite Subject: [sqlite] Re: problem with creating a table Bijan Farhoudi farhoudi-RazJlWb3c/[EMAIL PROTECTED] wrote: A. Pagaltzis wrote: SELECT [order] FROM foo But how would you know the name of the col is order not [order]? Square brackets are not part of the name. They are delimiters that indicate that whatever's inside is to be treated as an identifier (just as in string literal 'x', quotes are not part of the string but are delimiters indicating that it is indeed a string literal). This syntax allows identifiers that would otherwise be reserved words, as well as identifiers that otherwise would not be valid (e.g. having spaces or punctuation in them, e.g. [this is a valid column name] ). Igor Tandetnik If brackets are a visual distraction, more common Quote Marks may be used as well.
Re: [sqlite] Need sql query help
onemind [EMAIL PROTECTED] writes: What technology would be best suited for this task? I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Take a look at the Controllable Regex Mutilator, CRM114, http://crm114.sourceforge.net. It has mechanisms for detecting missing letters, extra letters, changed letters, etc. You'll likely find what you're looking for there or at least get some good ideas from it. (One of its purposes is a spam filter, and it's the best at that of anything I've found!) Derrell
Re: [sqlite] Need sql query help
onemind wrote: Thanks, The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. What technology would be best suited for this task? I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Thanks again. Derrel Lipman's recent post may answer your question better, but here's a sketch of a solution that involves SQLite. 1) Find a suitable regular expression library, say, PCRE (Perl Compatible Regular Expressions) -- www.prce.org 2) Write a C function to be used from within SQLite, using the instructions found at: http://www.sqlite.org/capi3ref.html#sqlite3_create_function The C function might be a custom one that, given a string of letters, searched for all letters (AND) or any letters (OR), possibly using the RegEx library. 3) Recompile SQLite with said regex library added into the SQLite code, as well as your C function. 4) Register your C function with SQLite using the above API 5) Use the function with the regex '[spqd]' to search for words containing the letters s, p, q, OR d. Doing it for all letters (AND) may be doable with a single regex, but if not, you can always, in your custom function, search for all the letters, mark them off one by one as you find them, and return the appropriate value when all have been found, otherwise, if you get to the end of the string, then return another appropriate value. Another poster mentioned that you should really test the straightforward, simple-minded approach that he mentioned, first. If it is fast enough, then why bother doing it the hard way. The above probably also won't use an index, so it is also an O(n) approach, like the simple-minded approach of doing several LIKE's probably is. 200,000 words does not sound like a whole lot. The first query might be a little slow, but if your table fits in memory, then your operating system's cache will probably make subsequent queries rather fast. Having said all this, the fastest way would probably be to use an in-memory datastructure, and simply query that in-memory. One possible -- and very simple -- solution would be to have a hash-map for every character you wished to be able to search, then store pointers to the strings of the words in each hash-map. That would make your lookup-times be O(m), where m is the number of letters to search for, rather than O(n), where n is the number of words. HTH Ulrik Petersen
[sqlite] The fastest way to change the data of a table?
Hi, Basically, I need to do the following: given a big-big table, I need to iterate through all its rows and change a column in about half the rows. The contents of the column to change depends on other columns, but only partially, so triggers won't work here. What would be the fastest way to do it? I assume that you cannot just do prepare/step and do updates while iterating over the table, right? Remembering rowid-s for the rows to change can potentially eat all my (well, computer's) memory, so it doesn't look perfect either. This task is solved pretty trivially and efficiently in dbf-based simple databases (FoxPro, Codebase), but it seems to me that I'm just missing something very basic about SQLite features. Yes?? -- Alexei Alexandrov
Re: [sqlite] problem with creating a table
Bijan Farhoudi wrote:
Hi,
I am trying to create table and I would like to name one of the columns
order, but pysqlite does not like it. I do not want to have order__ or
any thing like that.
for example the following command does not work:
cur.execute('create table foo(i integer, order integer)')
How can I fix this problem?
Regards,
Bijan
By only using ORDER as an SQL keyword.
Re: [sqlite] Which is most appropriate encoding ?
While developing Win32/MFC Application (with Visual C++ 6.0) - Application uses SQLite DB for it's data storage - Application must run on most windows (Windows 98, ME, NT, XP, 2000) - User should be able to copy Database from one PC to another PC (one PC may be running Windows 98 and another one Windows 2000 or XP) In this case, which encoding should be used for SQLite database ? Default encoding, UTF-8 or UTF-16 FWIW, if Windows is your only target, UTF-16 is the best choice for you, because wchar_t is in UTF-16 on Windows and you can avoid some string conversion in this case, while still be able to enable the stuff for other OSes as soon as there is a need for it. -- Alexei Alexandrov
Re: [sqlite] Need sql query help
onemind wrote: Hi, I was hoping someone could tell me if it was possible to select all words containing ceratin letters. Eg If i had a table wit a word column that had a huge list of words and i wanted to select every word that contained all these letters qdsa. Then it would return the words: quads quidas ect but wouldn't return queen because queen does not contain all letters specified. Would it be something like this: select * from word where word = qsda; THat doesn't work by the way :) Any help would be great. Thanks -- View this message in context: http://www.nabble.com/Need-sql-query-help-t1844399.html#a5034347 Sent from the SQLite forum at Nabble.com. Try LIKE
Re: [sqlite] Need sql query help
A. Pagaltzis wrote: * onemind [EMAIL PROTECTED] [2006-06-25 16:05]: If i had a table wit a word column that had a huge list of words and i wanted to select every word that contained all these letters qdsa. SELECT * FROM words WHERE word LIKE '%q%' AND word LIKE '%d%' AND word LIKE '%s%' AND word LIKE '%a%' And that’s going to be slow like molasses. It’s not something SQL is well suited to. If you need to do this a lot, I suggest precomputing the kinds of facts about each word that you’ll want to query and storing them in a column or dependent table so you can create indices and query them quickly. Of course if the performance of the simpleminded approach is sufficient for you, then all the better. Regards, Writing a function which does the string match would be a more efficient approach.
Re: [sqlite] Need sql query help
onemind wrote: Thanks, The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. What technology would be best suited for this task? I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Thanks again. -- View this message in context: http://www.nabble.com/Need-sql-query-help-t1844399.html#a5034782 Sent from the SQLite forum at Nabble.com. A regular expression search on a flat file with only 200,000 words would be fast and most likely achieve your objective. Run some trials with grep.
Re: [sqlite] problem with creating a table
* Bijan Farhoudi [EMAIL PROTECTED] [2006-06-25 17:05]: A. Pagaltzis wrote: .headers on SELECT [order] FROM foo But how would you know the name of the col is order not [order]? That’s what `.headers on` was supposed to demonstrate. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/
Re: [sqlite] Need sql query help
* Ulrik Petersen [EMAIL PROTECTED] [2006-06-25 17:55]: 5) Use the function with the regex '[spqd]' to search for words containing the letters s, p, q, OR d. Doing it for all letters (AND) may be doable with a single regex, It is doable with an NFA engine like PCRE, but it’s complicated to express and will incur so much backtracking that it’ll run much slower than doing four separate matches. With DFA engine such as egrep’s you can’t express it in a single pattern at all. For ultimate performance on strings, you’ll need to walk the string using a loop in a machine-oriented language like C and check characters directly. If you need to go even faster, then you’ll need an inverted index on letters for the whole dataset. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/
Re: [sqlite] Need sql query help
* onemind [EMAIL PROTECTED] [2006-06-25 17:00]: The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. 200,000 words is nothing. If they’re 5 letters on average, that’s some 1.1MB of data. You can grep that in milliseconds. What technology would be best suited for this task? Put the lot into a flat textfile, read it into memory, and do a string scan. I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Because you’re not indexing any of the facts you query. You’re just doing a scan across all of the table, doing string matches on one column in each row. There’s no point in using a database for that. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/
Re: [sqlite] Need sql query help
Ulrik Petersen wrote: Hi, responding to myself... Ulrik Petersen wrote: onemind wrote: Thanks, The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. What technology would be best suited for this task? I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Thanks again. [Snip] Having said all this, the fastest way would probably be to use an in-memory datastructure, and simply query that in-memory. One possible -- and very simple -- solution would be to have a hash-map for every character you wished to be able to search, then store pointers to the strings of the words in each hash-map. That would make your lookup-times be O(m), where m is the number of letters to search for, rather than O(n), where n is the number of words. What I said above is complete and utter BS. Sorry. You might want to look into bitsets, or finger-prints, as Information Retrieval specialists like to call them. The basic idea is that you make a bitset out of each word, with one bit for each of the features you want to be on or off for that word. For your purposes, probably you want each bit in the bitset to represent the presence or absence of one letter. If you only target the 26 letters of the English alphabet, a 32 bit integer will suffice. You can store such a bitset in a column in SQLite, say, fingerprint. Compute this as you insert the word. Then use the operator (bitwise-and) of SQLite's language to filter out those that you don't want. Say you are interested in those words which do contain a and b, and c. Say that a is bit 1, and b is bit 2, c is bit 3. Then you OR these together (1|2|4=7), giving the value 7 to the -operator: SELECT * FROM words WHERE fingerprint 7; HTH Ulrik Petersen This is a very fast method you could use for an SQL lookup. We use it in a text search product and it is effective. You might use a 64 bit word as a bitmap of the alphanumeric character occurrence in the string in the database and have that as a column in your table. Make it an index and then you can find all string occurrences very quickly with simple SQL, but at the cost of having to have a function to generate the bitmap on an insertion. That function can be activated by a trigger. You would use a version of the function to generate your search key from your chosen search string. The encoding can basically be performed efficiently by a table lookup and an OR.
Re: [sqlite] Need sql query help
A. Pagaltzis wrote: * onemind [EMAIL PROTECTED] [2006-06-25 17:00]: The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. 200,000 words is nothing. If they’re 5 letters on average, that’s some 1.1MB of data. You can grep that in milliseconds. What technology would be best suited for this task? Put the lot into a flat textfile, read it into memory, and do a string scan. I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Because you’re not indexing any of the facts you query. You’re just doing a scan across all of the table, doing string matches on one column in each row. There’s no point in using a database for that. Regards, When we have a problem like this we would mmap a flat file and use a fast string search algorithm like Boyer-Moore. It is about as fast as it gets if you are looking for something ad hoc and cannot use an index.
Re: [sqlite] The fastest way to change the data of a table?
Alexei Alexandrov a écrit : Hi, Basically, I need to do the following: given a big-big table, I need to iterate through all its rows and change a column in about half the rows. The contents of the column to change depends on other columns, but only partially, so triggers won't work here. Well, this mainly depends on your database structure. If the column to change is indexed, a simple UPDATE some_table SET some_column=some_value_or_function_result WHERE some_condition can be quite fast. Otherwise you should use the EXPLAIN statement on the query to see what happens and is a time consumer. What would be the fastest way to do it? I assume that you cannot just do prepare/step and do updates while iterating over the table, right? Remembering rowid-s for the rows to change can potentially eat all my (well, computer's) memory, so it doesn't look perfect either. This also depends on what interface you use. Will it be C, C++ or PHP, Perl, Python ? PHP PDO_SQLite interface uses an object oriented syntax which allows pure SQL queries without thinking about prepare/step paradigm. It's very efficient, even on huge tables (more than 10 million records). This task is solved pretty trivially and efficiently in dbf-based simple databases (FoxPro, Codebase), but it seems to me that I'm just missing something very basic about SQLite features. Dbf based databases have different paradigms than pure SQL based ones. When I deal with dbase syntax, I usually now use Clip, a Clipper compatible free system which allows SQL xBase queries.
Re: [sqlite] problem with creating a table
A. Pagaltzis a écrit : * Bijan Farhoudi [EMAIL PROTECTED] [2006-06-25 17:05]: A. Pagaltzis wrote: .headers on SELECT [order] FROM foo But how would you know the name of the col is order not [order]? That’s what `.headers on` was supposed to demonstrate. Regards, BTW, this just demonstrate one should never use standard SQL reserved words as column or table names :) Wouldn't it be a better strategy to rename this order column to something not reserved such as ordering, sorting or anything else not being a resreved word.
Re: [sqlite] Need sql query help
On Sun, Jun 25, 2006 at 07:54:13AM -0700, onemind wrote: The thing is, i am going to need to use different letters each time to search through over 200,000 words in a database and it needs to be fast. The quick and dirty way to do this using a sqlite would be to keep a separate column from the work column with the letters in the word sorted. You would have to keep this column up to date yourself though. I do not believe there is an out-of-the-box way to populate this column with sqlite. To do so would involve a trigger and a user defined function. In other words you would have: sqlite CREATE TABLE words (word text, sorted text); And inserting into the table : sqlite insert into words(word,sorted) values (quads,adqsu); sqlite insert into words(word,sorted) values (quidas,adiqsu); sqlite insert into words(word,sorted) values (queen,eenqu); Then your searches would be in the vein of: sqlite select word from words where sorted like %a%d%q%s%; quads quidas But, this would result in a full table scan for all searches and as a result be O(N) and would not be very efficient. Additionally it would probably be easier to just write a script that would do the same thing with a textfile of the words and search through it. Although... sounds like a fun little project... % ./init-db Creating words.db Inserting words from /usr/share/dict/words... Inserted 483523 words into db in 58.900854 seconds. % ./search-db aqds Searching for aqds - SELECT word FROM words WHERE sorted LIKE '%a%d%q%s%' found 754 results in 1.521026 seconds I still wouldn't suggest this method, but it is a fun exercise. Each search in this manner is a full table scan. What technology would be best suited for this task? I just assumed that a databse would be ideal, why do you say sql isn't suited for this and what is? Others have given good suggestions, Ulrick's bitset is particularly nice. Well, its a lazy sunday, lets see what happens. using the same approach as above, but using a 'bitset' column instead of 'sorted'. Also an index is created on the bitset column. % ./init-db-bitset Creating words-bitset.db Inserting words from /usr/share/dict/words... Inserted 483523 words into db in 75.368803 seconds. % ./search-db-bitset aqds Searching for words aqds - SELECT word FROM words WHERE (bitset 327689) = 327689 found 754 results in 0.902595 seconds Although I believe it should still have to do a table scan here. But bitwise and is faster than a string comparison in any case. Since this also does a full table scan, you'll probably want something more along the lines of an inverted index of the words by letter in some sort of dedicated data structure. I started playing with this using sqlite to try and do an inverted index by letter here, but it didn't get close to the performance of what the bitset was doing. Ahh, what a fun way to spend part a Sunday :-) enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED]
[sqlite] SQLite Path Problem On Fedora Core 5
For a long time, I've been playing with the Sqlite product from http://www.sqlite.org . I started doing this before Fedora Core 4, and would compile the source code to the default install directories of /usr/local/bin and /usr/local/lib, etc. This worked great until Fedora Core started packaging Sqlite and installing it by default, I think mainly because yum uses it. And for good reason too. This is where I got in trouble. The Fedora Core rpm package installs to /usr/bin and /usr/lib and /usr/include. However, on Fedora Core 5, my path is set so that objects on /usr/local/bin are found before those on /usr/bin. I'm not sure how this is happening; perhaps /etc/profile? The result seems to be that even if sqlite 3.3.3 was installed by yum, executing /usr/bin/sqlite3 will yeild a command line stating its version is 3.2.7 which is one of the versions I believe I installed to /usr/local myself. currently, /etc/ld.so.conf points at /usr/local/lib. Yum seems to work fine. How do I fix my sqlite version mess so that I can have multiple installed versions: the one used by yum (and possibly other modules) and the latest and greatest release, which I want to link into PHP for my own purposes. (Yes I compile PHP on my own.) Thanks Bob Cochran Maryland, USA
Re: [sqlite] Need sql query help
Thanks for all of the great ideas :) Plently of techniques to work on there. Just incase your interested, i woke up this morning and all the words finally made it into sqlite :) It took over 8 hours, so if anyone could tell me a text command that would do this same task of importing a txt file into a table through the sqlite3 command line that would be great. It must be the gui slowing it down somehow. Anyway, i ran the like query using heaps of different combinations of letters and the results are returned instantly :) More than fast enough for my purposes but i will definately check out some of the other methods just for fun :) It seems we under estimated sqlites speed for this task. Anyway, thanks again for all your replies, now i have to figure out how to sort through the resulted word list and score each letter with certain points to find the highest scoring words. Incase you haven't guessed, it is for a scrabble AI project. Am trying to make an AI player in scrabble that finds the highest scoring word with given tiles and pattern recognition of the words that are on the board. In other words, i am trying to clone this site: http://www.scrabblewordfinder.com/ Cheers :) -- View this message in context: http://www.nabble.com/Need-sql-query-help-t1844399.html#a5040314 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Need sql query help
On Sun, 25 Jun 2006 18:24:50 -0700 (PDT), onemind wrote:
It took over 8 hours, so if anyone could tell me a text command that would
do this same task of importing a txt file into a table through the sqlite3
command line that would be great. It must be the gui slowing it down
somehow.
Repeating details from my earlier post:
--
create table wordlist (word text);
select current_time;
begin transaction;
insert into wordlist values (test0);
insert into wordlist values (test1);
...
insert into wordlist values (test18);
insert into wordlist values (test19);
commit transaction;
select current_time;
select count(*) from wordlist;
--
It took 8 SECONDS, not 8 HOURS.
The above lines were taken directly from the
text file that I had created and then imported
into the sqlite3 command line utility by typing
the command:
.read word.lst
The only change for this email was to delete
the 199,996 intermediate text lines that would
just get in the way of this example.
The suggestion made to use a bitset is an
excellent one. Put an index on the bitset field.
The sql AND operator will find all combinations
very quickly.
Here is sample code to create the content of
the bitset field from the characters in each word.
If you wrap this into a small program that reads
the word list and creates the above insert
statements then you can also insert the bitset
value as a second field in each insert statement.
unsigned long int
GetBitSetOf (char * InWord)
int J, K;
unsigned long int BitSet; // must be at least 26 bits wide
K = strlen(InWord);
BitSet = 0;
for (J = 0; J K; J++) {
BitSet |= 1 (InWord[J] - 65);// assumes all letters are uppercase
only
}
return (BitSet);
} // GetBitSetOf
Re: [sqlite] SQLite Path Problem On Fedora Core 5
Robert L Cochran a écrit : However, on Fedora Core 5, my path is set so that objects on /usr/local/bin are found before those on /usr/bin. I'm not sure how this is happening; perhaps /etc/profile? The result seems to be that even if sqlite 3.3.3 was installed by yum, executing /usr/bin/sqlite3 will yeild a command line stating its version is 3.2.7 which is one of the versions I believe I installed to /usr/local myself. Yes FC5 uses the following default path: /usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin which means your binaries in /usr/local/bin will be accessed before those in /usr/bin. How do I fix my sqlite version mess so that I can have multiple installed versions: the one used by yum (and possibly other modules) and the latest and greatest release, which I want to link into PHP for my own purposes. (Yes I compile PHP on my own.) With an up to date yum install, FC5 default SQlite is 3.3.3 You can clean up thethings in the following way. Instaed of using /usr/local for prefix when configuring a sqlite build you could pass: ./configure --prefix=/usr/local/sqlite-3.3.6 (for 3.3.6 version) This will install each of your multiple sqlite versions (except de default used by yum) in its own envronment. To build an application with a particular sqlite build, you'll have to add, ie for PHP 5.1.4: --with-sqlite=/usr/local/sqlite-3.3.6 --with-pdo-sqlite=/usr/local/sqlite-3.3.6 As PHP configuration system uses the --rpath which tells the binaries the full path of used libraries, this will work. For applications where you're not sure --rpath is used, you can add it to configure options.
Re: [sqlite] Preferred way to copy/flush new memory db to disk db ?
On Sun, 25 Jun 2006 01:58:10 -0700 (PDT), RohitPatel wrote: Intial database will have about 30+ tables, very few records in each of these tables, one or two indices on some tables. For such a small database, why not create it directly on disk? The time required should be just a one second or two. Remember to start the command sequence with begn transaction and finish with commit transaction. Chris
[sqlite] Call sqlite3_reset multiple times
Hello, sqltie3_stmt *stmt; sqlite3_prepare(...stmt...); sqlite3_reset(stmt); sqlite3_reset(stmt); // Is this OK? it looks sqlite3_prepare allocates resource and sqlite3_reset deallocates them, is it ok to call sqlite_reset on the same statement pointer multiple times without preparing it in between? Would it cause undefined behaviour or memory leaking ... ? Br,Kai
Re: [sqlite] Virtual Table: xRowID shortcommings
2. In case the virtual table implementation needs to allocate memory in order to uniquely describe a row/item, this memory needs to be freed when no longer used. As I see it, there is no such method in the Virtual Table implementation. Maybe the transaction part of the virtual table API is useful in this context (xBegin/xSync/xCommit/xRollback). SQLite will only store values retrieved via xRowid for the duration of a transaction. So if you need to create a mapping between the integer id's used by SQLite and the complex identifiers, you can throw the table away at the end of the transaction. A linked list and a willingness to cast pointers to 64-bit integers seems like it would do the trick :) This willingness is certainly there, but with many thousand modifications in a single transaction, this would accumulate a couple thousant memory allocations and free them only after the transaction is committed. Very ineffective, in my opinion :( Of course that won't help with sqlite apps that expect the rowid field to remain persistent between queries that span multiple transactions (i.e. MS Access style GUIs etc.). Correct. Quite a few DBMS simply do not use and support the concept of integer rowids. The current xRowID implementation would not allow to access those as virtual tables from SQLite. To support those as well, I believe that the SQLite type integer rowids could be made optional for virtual tables and another, more flexible approach could instead be made available (as proposed in my previous mailing). This would of course mean that those virtual tables would not have a rowid column and SQLite would return the usual 'No such solumn: rowid'. But I do not see any problems to this from a user's perspective.
Re: [sqlite] Sqlite crashes when i imort huge list
On Sat, 24 Jun 2006 20:10:58 -0700 (PDT), onemind wrote: I am using the sqlite gui and click import table from csv. I select a txt file that contain over 200,000 words in a list. Sqlite works fine with a smaller list of 200-300 words but when i import my big list, it hangs for ages or completely crashes my computer. Does anyone know how i can import this list into a table successfully? The following text file was created by a small script: create table wordlist (word text); select current_time; begin transaction; insert into wordlist values (test0); insert into wordlist values (test1); ... insert into wordlist values (test18); insert into wordlist values (test19); commit transaction; select current_time; select count(*) from wordlist; This text file was executed within the sqlite3 command line utility by typing the command: .read word.txt The resulting screen output was 11:20:40 11:20:48 20 indicating the insertion of 200,000 words into a simple database required 8 seconds of clock time on a 1.5 ghz AMD Sempron cpu running MS-Windows XP. The use of sequential numbers as the trailing part of the 'word' results in a continual rebalancing of the b-tree with each insertion. For performances reasons, this is probably the worst kind of data to insert into a database. Random words inserted result in many leaves that are partially filled and fewer rebalance acts. Eight seconds to insert the lot is pretty good. You could create a similar text file from the CSV file using your editor. Chris
Re: [sqlite] Call sqlite3_reset multiple times
Kai Wu [EMAIL PROTECTED] wrote: Hello, sqltie3_stmt *stmt; sqlite3_prepare(...stmt...); sqlite3_reset(stmt); sqlite3_reset(stmt); // Is this OK? This is OK. A statement is created by sqlite3_prepare and is destroyed by sqlite3_finalize. sqlite3_reset can be called as many times as you like in between. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Sqlite crashes when i imort huge list
There was a small error in my previous post. The 'begin transaction;' line was missed when I copied the text out of the script and editted it to remove the code around the text. On Sat, 24 Jun 2006 20:10:58 -0700 (PDT), onemind wrote: I am using the sqlite gui and click import table from csv. I select a txt file that contain over 200,000 words in a list. Sqlite works fine with a smaller list of 200-300 words but when i import my big list, it hangs for ages or completely crashes my computer. Does anyone know how i can import this list into a table successfully? The following text file, word.txt, was created by a small script: create table wordlist (word text); begin transaction; select current_time; insert into wordlist values (test0); insert into wordlist values (test1); ... insert into wordlist values (test18); insert into wordlist values (test19); commit transaction; select current_time; select count(*) from wordlist; This text file was executed within the sqlite3 command line utility by typing the command: .read word.txt The resulting screen output was 11:20:40 11:20:48 20 indicating the insertion of 200,000 words into a simple database required 8 seconds of clock time on a 1.5 ghz AMD Sempron cpu running MS-Windows XP. The use of sequential numbers as the trailing part of the 'word' results in a continual rebalancing of the b-tree with each insertion. For performances reasons, this is probably the worst kind of data to insert into a database. Random words inserted result in many leaves that are partially filled and fewer rebalance acts. Eight seconds to insert the lot is pretty good. You could create a similar text file from the CSV file using your editor. Chris
Re: [sqlite] Dumping Memory-DB to File
Hi I need some help on this. I need to create a new SQLite database with all necessary tables, records and indices. Database file must be removed from disk if any error while creating/copying tables, records or indices. Other application or other instance of same app must not be able to access the database, till database is not ready with necessary minimum tables and records. Question is: How to maintain exclusive access to disk db file till creation/copying from memory db is finished ? Thanks Rohit -- View this message in context: http://www.nabble.com/RE%3A-Dumping-Memory-DB-to-File-t1601385.html#a5033569 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] Call sqlite3_reset multiple times
Hello, Thanks for your prompt reply! Then, should sqlite3_finalize be called for a sqlite3_stmt pointer before it gets prepared for another sql statement? in another word, can the same sqlite3_stmt pointer get prepared multiple times and executed afterwards without a sqlite3_finalize in between? string sql; sqlite3_stmt *stmt; sql=select ... ; sqlite3_prepare(...sql.c_str()...stmt...); sqlite3_step(stmt); sqlite3_finalize(stmt); // Is this needed ?? sql=delete ... ; sqlite3_prepare(...sql.c_str()...stmt...); sqlite3_step(stmt); Br,Kai On 6/25/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Kai Wu [EMAIL PROTECTED] wrote: Hello, sqltie3_stmt *stmt; sqlite3_prepare(...stmt...); sqlite3_reset(stmt); sqlite3_reset(stmt); // Is this OK? This is OK. A statement is created by sqlite3_prepare and is destroyed by sqlite3_finalize. sqlite3_reset can be called as many times as you like in between. -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Dumping Memory-DB to File
A very simple way is use an exclusively opened file as a lock. RohitPatel wrote: Hi I need some help on this. I need to create a new SQLite database with all necessary tables, records and indices. Database file must be removed from disk if any error while creating/copying tables, records or indices. Other application or other instance of same app must not be able to access the database, till database is not ready with necessary minimum tables and records. Question is: How to maintain exclusive access to disk db file till creation/copying from memory db is finished ? Thanks Rohit -- View this message in context: http://www.nabble.com/RE%3A-Dumping-Memory-DB-to-File-t1601385.html#a5033569 Sent from the SQLite forum at Nabble.com.
[sqlite] Re: Call sqlite3_reset multiple times
Kai Wu [EMAIL PROTECTED] wrote: Then, should sqlite3_finalize be called for a sqlite3_stmt pointer before it gets prepared for another sql statement? This question is meaningless. sqlite3_prepare does not take an existing statement handle and modify it - it creates and returns a brand new one. If you store it in the same variable, the new handle overwrites the old handle, and you have a leak since you now can't finalize the old statement. Igor Tandetnik
[sqlite] newcomer getting started with VB
Hello out there, I've just started learning how to use sqlite and the com dll on Win XP in a VB Environment with MS Visual Studio 2003. The only Information available to me is the online help coming with the entire EzTools package and the examples as well. As I'm new to .NET programming I'd first like to ask if there is any configuration of XP, VB or the Visual Studio required in order to get access to the dll's. Actually I'm following the VB example ... dim db as SQLITEPLUS35.SqliteDB dim ds as SQLITEPLUS35.Dataset dim col as SQLITEPLUS35.Column db.Init( dll version, licence key, path to lic file ) ... however compilation fails at the very beginning and complains that the Module ws not found in the dim db .. statement. Can you please help me getting my db up and runnig. Kind regards Martin Martin Knirsch Germany
[sqlite] problem with creating a table
Hi,
I am trying to create table and I would like to name one of the columns
order, but pysqlite does not like it. I do not want to have order__ or
any thing like that.
for example the following command does not work:
cur.execute('create table foo(i integer, order integer)')
How can I fix this problem?
Regards,
Bijan
Re: [sqlite] Sqlite crashes when i imort huge list
Thanks guys, Richard: The reason i didn't mention the software is because all guis just create the text commands anyway so they all do the same thing. I doubt it is a problem with the gui i used which was SQLite database browser. Chris: Thanks for that but i dont know how that helps me. Are you suggesting that i write a script that inserts one word at a time to avoid crashing or was it just an example? How would i write a command that would import from a text file without the gui? Is it the .import command? Any more help would be great. Here is a small sample of the file that i am trying to insert: AA AAH AAHED AAHING AAHS AAL AALII AALIIS AALS AARDVARK AARDVARKS AARDWOLF AARDWOLVES AARGH AARRGH AARRGHH AAS AASVOGEL AASVOGELS AB ABA ABACA ABACAS ABACI ABACK ABACTERIAL ABACUS ABACUSES ABAFT ABAKA ABAKAS ABALONE ABALONES ABAMP ABAMPERE ABAMPERES ABAMPS ABANDON ABANDONED ABANDONER ABANDONERS ABANDONING ABANDONMENT ABANDONMENTS ABANDONS ABAPICAL ABAS ABASE ABASED ABASEDLY ABASEMENT ABASEMENTS ABASER ABASERS ABASES ABASH ABASHED ABASHEDLY ABASHES ABASHING ABASHMENT ABASHMENTS ABASIA ABASIAS ABASING ABATABLE ABATE ABATED ABATEMENT ABATEMENTS ABATER ABATERS ABATES ABATING ABATIS ABATISES ABATOR ABATORS ABATTIS ABATTISES ABATTOIR ABATTOIRS ABAXIAL ABAXILE ABAYA ABAYAS ABBA ABBACIES ABBACY ABBAS ABBATIAL ABBE ABBES ABBESS ABBESSES ABBEY ABBEYS ABBOT ABBOTCIES ABBOTCY ABBOTS ABBOTSHIP ABBOTSHIPS ABBREVIATE ABBREVIATED ABBREVIATES ABBREVIATING ABBREVIATION ABBREVIATIONS ABBREVIATOR ABBREVIATORS ABCOULOMB ABCOULOMBS ABDICABLE ABDICATE ABDICATED ABDICATES ABDICATING ABDICATION ABDICATIONS ABDICATOR ABDICATORS ABDOMEN ABDOMENS ABDOMINA ABDOMINAL ABDOMINALLY ABDOMINALS ABDUCE ABDUCED ABDUCENS ABDUCENT ABDUCENTES ABDUCES ABDUCING ABDUCT ABDUCTED ABDUCTEE ABDUCTEES ABDUCTING ABDUCTION ABDUCTIONS ABDUCTOR ABDUCTORES ABDUCTORS ABDUCTS ABEAM ABECEDARIAN Thanks :) -- View this message in context: http://www.nabble.com/Sqlite-crashes-when-i-imort-huge-list-t1842991.html#a5034321 Sent from the SQLite forum at Nabble.com.
