Re: [sqlite] Huge performance drop when using prepared statement
Hello, think I got it, but it is disappointingly simple, see below. Ed.
Markus Gritsch wrote:
Even more strange:
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('tes*',))
takes less than 1ms but
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('test',))
takes several hundred ms.
The execute in Python includes prepare (or get from cache), bind and
the first step.
The answer must be that the wait time lies in the first step.
The engine is doing a full scan and it all depends how far in the
table it needs to go to find the first match.
So the bind values with * just come across a match sooner.
Wilhelm Braun wrote:
I just tried for fun:
start = time.time()
SQLString=('''SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB '%s'
''' % "hui*")
c.execute(SQLString)
and it is as fast as your first one - seems a pysqlite problem to me
I know they say this is not a secure way to do it -- well.
This seems the only solution after all.
But it floods the wonderful pysqlite statement cache, with new SQL
statements for each new bind value.
Preferably, only the operator is substituted in the SQL, for "GLOB"
or just "=", depending on the actual bind value.
That leaves just two different statements.
But I don't know if the result is the same as I don't know GLOB very
well.
If it is affected by the case_sensitive_like pragma my idea is too
simple.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Problem using mem1.c/mem3.c with v3.5.3
Hello, Now I'm using SQLite v3.4.2 in my Mobile Device(ARM9-200Mhz, 8MB SRAM) and it works fine. But when I updated to SQLitev3.5.3 this time I am facing problem. Please let me know why it is happening. This is my test source in MS's VisualC++ 6.0 (Console application). http://cfs7.blog.daum.net/upload_control/download.blog?fhandle=MElCMXRAZ nM3LmJsb2cuZGF1bS5uZXQ6L0lNQUdFLzAvMC56aXA=&filename=0.zip&filename=SQL3 53_VC6.zip Problem 1. when I use "mem1.c" and use "Order By", it require more memory than I expected. It seems has same problem when I use "mem3.c". I want use less than 1MB. So I set "SQLITE_DEFAULT_CACHE_SIZE=800" and "SQLITE_DEFAULT_TEMP_CACHE_SIZE=200". Problem 2. When I use "mem3.c" and set "SQLITE_MEMORY_SIZE = 1024000", "insert" was failed. Please help am I setting ANY WRONG PARAMETERS. Best Regards, Jang
Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?
On Sun, 9 Dec 2007 23:34:44 +0100, DJ Anubis <[EMAIL PROTECTED]> wrote: >Le dimanche 9 décembre 2007, Gilles Ganault a écrit : >> It seems like I have two options: >> - calling the SQLite library >> - going through the PDO interface, and its SQLite module. >> >> Which of the two would you recomend? Are there other options I >> should know about? > >I would recommend using PDO interface, as this is the standard >PHP5 API. I agree. My experience with php_pdo_sqlite is positive, and I think it is the easiest way to use sqlite3 in PHP. I didn't try php_pdo_sqlite_external yet, it seems to call a self-supplied sqlite3.dll, so one would be able to use the latest SQLite3 version. >Don't worry about PHP4, as this old version will no more be >supported soon... -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?
At 23:34 09/12/2007 +0100, DJ Anubis wrote: I would recommend using PDO interface, as this is the standard PHP5 API. Don't worry about PHP4, as this old version will no more be supported soon... Thanks for the tip. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?
Le dimanche 9 décembre 2007, Gilles Ganault a écrit : > It seems like I have two options: > - calling the SQLite library > - going through the PDO interface, and its SQLite module. > > Which of the two would you recomend? Are there other options I > should know about? I would recommend using PDO interface, as this is the standard PHP5 API. Don't worry about PHP4, as this old version will no more be supported soon... - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] [Linux + PHP] Recommended way to access SQLite?
Hello I'm not a PHP expert, and need to work with SQLite from PHP scripts on a CentOS 5.1 server (from the command line, and web apps in FastCGI). It seems like I have two options: - calling the SQLite library - going through the PDO interface, and its SQLite module. Which of the two would you recomend? Are there other options I should know about? Thank you. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Cannot build VC++ VS2005 SQLite3vb
Hello, someone could please help me... I try to build SQLite3VB, following exactly the instruction on the page: http://www.tannertech.net/sqlite3vb/ but at the end of the build process, I get allways the same error, like this: Linking... sqlite3.def : error LNK2001: unresolved external symbol sqlite3_apis sqlite3.def : error LNK2001: unresolved external symbol sqlite3_auto_extension .. for about 48 times... I think is something missing in my configuration, maybe the .def file. help...please.. Giuliano - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Thanks, I thought that this might properly a bigger thing. Well, I found a solution which fits my purpose at the moment. ( SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) ) I do not use selection of max() or min() very often - it seems it is the best suiting solution (effort - result) at the moment. regards W.Braun John Stanton wrote: We built a fixed point arithmetic library using text strings. The format stored is right justified, leading space filled decimal numbers with embedded decimal points and leading sign. The purpose of that is not for arithmetic efficiency but so that they can be directly output into a printed page or HTML document. The algorithms we use are essentially from Knuth's Semi Numerical Algorithms volume. Functions exist for the common arithmetic operations plus moves and comparisons. Rounding is implemented using the algorithm which minimizes skew. These functions are also added into Sqlite as custom functions so that the decimal numbers can be used from SQL. We define the decimal numbers using standard SQL with precision and scale assigned in the type declaration. Sqlite's ability to store declared types makes the integration possible. This is not a simple fix, but it does let us produce accurate financial reports. Mag. Wilhelm Braun wrote: Thanks Stanton, could you elaborate a bit on that - I'm not sure if I get exactly what you mean. if you have a smallish example would be great. the help from 'Igor Tandetnik' with cast is a good starting point - but on selections with max or min I still get the incorrect rounded numbers back. EXAMPLE:column txt "0.2009" "10.200899" "4.0" "300.2009" and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test it returns returns 300.2008 so the only solution till now seems to make a sub-query like: SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) not sure how messy that might get in complex queries. anyway for any suggestion I'm more than grateful regards W.Braun John Stanton wrote: Our approach to that problem was to write a library of ASCII decimal arithmetic functions, store the data as underlying type TEXT but give them a declared type of DECIMAL(n,m) and have added functions which understand that declared type. With that addition Sqlite becomes useful for accounting and other such activities requiring arithmetic accuracy. For a simple display interface we use display format, fixed point decimal numbers, right justified. Mag. Wilhelm Braun wrote: Dear all, I use sqlite to store numerical text strings. Why do I use text type: because of the float problem of incorrection.example in numeric Columns: 3.2009returns as 3.2008 which is not what I want. Column Type=TEXT is there a way to do comparison of text in a numerical way. EXAMPLE rows:Column txt "0.200899" "1.2009" "113.2008999" "4.0" "3.1" "3.2009" SELECT max(txt) FROM test should return "113.2008999" and not "4.0" ALSO: SELECT * FROM test WHERE txt>10.0 should just return "113.2008999" and not "113.2008999" "4.0" "3.1" so my question is there a way to do that correctly? Thanks for any helpful hints regards W.Braun by the way: I use pysqlite. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
We built a fixed point arithmetic library using text strings. The format stored is right justified, leading space filled decimal numbers with embedded decimal points and leading sign. The purpose of that is not for arithmetic efficiency but so that they can be directly output into a printed page or HTML document. The algorithms we use are essentially from Knuth's Semi Numerical Algorithms volume. Functions exist for the common arithmetic operations plus moves and comparisons. Rounding is implemented using the algorithm which minimizes skew. These functions are also added into Sqlite as custom functions so that the decimal numbers can be used from SQL. We define the decimal numbers using standard SQL with precision and scale assigned in the type declaration. Sqlite's ability to store declared types makes the integration possible. This is not a simple fix, but it does let us produce accurate financial reports. Mag. Wilhelm Braun wrote: Thanks Stanton, could you elaborate a bit on that - I'm not sure if I get exactly what you mean. if you have a smallish example would be great. the help from 'Igor Tandetnik' with cast is a good starting point - but on selections with max or min I still get the incorrect rounded numbers back. EXAMPLE:column txt "0.2009" "10.200899" "4.0" "300.2009" and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test it returns returns 300.2008 so the only solution till now seems to make a sub-query like: SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) not sure how messy that might get in complex queries. anyway for any suggestion I'm more than grateful regards W.Braun John Stanton wrote: Our approach to that problem was to write a library of ASCII decimal arithmetic functions, store the data as underlying type TEXT but give them a declared type of DECIMAL(n,m) and have added functions which understand that declared type. With that addition Sqlite becomes useful for accounting and other such activities requiring arithmetic accuracy. For a simple display interface we use display format, fixed point decimal numbers, right justified. Mag. Wilhelm Braun wrote: Dear all, I use sqlite to store numerical text strings. Why do I use text type: because of the float problem of incorrection.example in numeric Columns: 3.2009returns as 3.2008 which is not what I want. Column Type=TEXT is there a way to do comparison of text in a numerical way. EXAMPLE rows:Column txt "0.200899" "1.2009" "113.2008999" "4.0" "3.1" "3.2009" SELECT max(txt) FROM test should return "113.2008999" and not "4.0" ALSO: SELECT * FROM test WHERE txt>10.0 should just return "113.2008999" and not "113.2008999" "4.0" "3.1" so my question is there a way to do that correctly? Thanks for any helpful hints regards W.Braun by the way: I use pysqlite. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Huge performance drop when using prepared statement
On 09/12/2007, Markus Gritsch <[EMAIL PROTECTED]> wrote:
> On 09/12/2007, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> >
> > SQLite will optimize a GLOB where the right parameter is
> > a literal string. It will not do so if the right parameter is a
> > parameter. http://www.sqlite.org/optoverview.html#like_opt
>
> Hmm, if I replace 'hui*' by 'hu*'
>
> c.execute("""SELECT * FROM entry, word, word_entry WHERE
> entry.id = word_entry.entry_id AND
> word.id = word_entry.word_id AND
> word.word GLOB ?
> """, ('hu*',))
>
> the query using bind variables also takes an unmeasurable short time period.
Even more strange:
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('tes*',))
takes less than 1ms but
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('test',))
takes several hundred ms.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Huge performance drop when using prepared statement
On 09/12/2007, Mag. Wilhelm Braun <[EMAIL PROTECTED]> wrote:
> hi Markus,
Hi Wilhelm
> I just tried for fun:
>
> start = time.time()
> SQLString=('''SELECT * FROM entry, word, word_entry WHERE
> entry.id = word_entry.entry_id AND
> word.id = word_entry.word_id AND
> word.word GLOB '%s'
> ''' % "hui*")
>
> c.execute(SQLString)
>
> and it is as fast as your first one - seems a pysqlite problem to me
>
> I know they say this is not a secure way to do it -- well.
Your version does not use a prepared statement / bind variables. It
just forges the string and passes it to SQLite a one string, being
essentially the exact same query as the first one in my example.
Markus
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Huge performance drop when using prepared statement
On 09/12/2007, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> On Dec 9, 2007, at 5:27 AM, Kees Nuyt wrote:
>
> > Problematic SELECT:
> >> c.execute("""SELECT * FROM entry, word, word_entry WHERE
> >> entry.id = word_entry.entry_id AND
> >> word.id = word_entry.word_id AND
> >> word.word GLOB ?
> >> """, ('hui*',))
> >
>
> SQLite will optimize a GLOB where the right parameter is
> a literal string. It will not do so if the right parameter is a
> parameter. http://www.sqlite.org/optoverview.html#like_opt
Hmm, if I replace 'hui*' by 'hu*'
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('hu*',))
the query using bind variables also takes an unmeasurable short time period.
Markus
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Huge performance drop when using prepared statement
hi Markus,
I just tried for fun:
start = time.time()
SQLString=('''SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB '%s'
''' % "hui*")
c.execute(SQLString)
and it is as fast as your first one - seems a pysqlite problem to me
I know they say this is not a secure way to do it -- well.
Kind regards,
W.Braun
Markus Gritsch wrote:
Hi,
when using bind variables I get a huge performace drop compared to
using a plain string. The query is demonstrated in the attached file
"problematic_query.py".
The database used can be downloaded from
http://xile.org/le/prepared_statement.zip (1.75 MB)
or generated by using the attached file "create_test_db.py".
Kind regards,
Markus
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Huge performance drop when using prepared statement
On Dec 9, 2007, at 5:27 AM, Kees Nuyt wrote:
Problematic SELECT:
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('hui*',))
SQLite will optimize a GLOB where the right parameter is
a literal string. It will not do so if the right parameter is a
parameter. http://www.sqlite.org/optoverview.html#like_opt
D. Richard Hipp
[EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Is there a way to do comparison of text in a numerical way.
Thanks Stanton, could you elaborate a bit on that - I'm not sure if I get exactly what you mean. if you have a smallish example would be great. the help from 'Igor Tandetnik' with cast is a good starting point - but on selections with max or min I still get the incorrect rounded numbers back. EXAMPLE:column txt "0.2009" "10.200899" "4.0" "300.2009" and I do a selection: SELECT max(CAST(txt AS REAL)) FROM test it returns returns 300.2008 so the only solution till now seems to make a sub-query like: SELECT txt FROM test WHERE txt=(SELECT max(CAST(txt AS REAL)) from test) not sure how messy that might get in complex queries. anyway for any suggestion I'm more than grateful regards W.Braun John Stanton wrote: Our approach to that problem was to write a library of ASCII decimal arithmetic functions, store the data as underlying type TEXT but give them a declared type of DECIMAL(n,m) and have added functions which understand that declared type. With that addition Sqlite becomes useful for accounting and other such activities requiring arithmetic accuracy. For a simple display interface we use display format, fixed point decimal numbers, right justified. Mag. Wilhelm Braun wrote: Dear all, I use sqlite to store numerical text strings. Why do I use text type: because of the float problem of incorrection.example in numeric Columns: 3.2009returns as 3.2008 which is not what I want. Column Type=TEXT is there a way to do comparison of text in a numerical way. EXAMPLE rows:Column txt "0.200899" "1.2009" "113.2008999" "4.0" "3.1" "3.2009" SELECT max(txt) FROM test should return "113.2008999" and not "4.0" ALSO: SELECT * FROM test WHERE txt>10.0 should just return "113.2008999" and not "113.2008999" "4.0" "3.1" so my question is there a way to do that correctly? Thanks for any helpful hints regards W.Braun by the way: I use pysqlite. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Does SQLite support modifying date through views?
It does not, but you can attach triggers to a view to achieve the same effect. > -Ursprüngliche Nachricht- > Von: Robert Smith [mailto:[EMAIL PROTECTED] > Gesendet: Sonntag, 9. Dezember 2007 08:31 > An: sqlite-users@sqlite.org > Betreff: [sqlite] Does SQLite support modifying date through views? > > > I am trying to implement an embedded application using > SQLite. I need to > modify data through views. The update, insert, or delete > operations may > refer to a computed column or a built-in function in a view. > I experienced > problems. I am wondering if SQLite can support features of > modifying date > through views. > > Thanks, > Robert > -- > View this message in context: > http://www.nabble.com/Does-SQLite-support-modifying-date-throu gh-views--tp14236459p14236459.html > Sent from the SQLite mailing list archive at Nabble.com. > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] using lemon to create a c++ parser class
Wilson, Ron uttered: It has been a very long time since I have tinkered with lex/yacc but my current project requires a parser. I'm thinking of learning lemon. Frankly, the sqlite code base is far more complex than what I will implement. Is anyone willing to share a lemon parse.y code example for something less complex than SQL? There are tutorials on the net that might be worth looking at, for example: http://freshmeat.net/articles/view/1270/ Also, i'm looking for advice on using lemon to make a c++ parser class instead of a global c parser function. Is it as simple as declaring the following? %name MyParserClass::Parse I'm pretty sure I can create the right c++ preamble with %include. Also, is there a cheap way to make lemon output a .cpp file besides renaming the output file? Feel free to tell me I'm on a foolish quest if I am. The C++ quest might be unnecassary, but I wouldn't say foolish. There is no problem linking C and C++ code. I'd say just leave the Parse function as a C function. You might even be able to make it static, thus limiting it's scope, and wrapping that static function in a class, but why bother? RW Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Huge performance drop when using prepared statement
On 09/12/2007, Kees Nuyt <[EMAIL PROTECTED]> wrote:
> On Sun, 9 Dec 2007 10:55:16 +0100, "Markus Gritsch"
> <[EMAIL PROTECTED]> wrote:
>
> >Hi,
> >
> >when using bind variables I get a huge performace drop compared to
> >using a plain string. The query is demonstrated in the attached file
> >"problematic_query.py".
>
> Problematic SELECT:
> > c.execute("""SELECT * FROM entry, word, word_entry WHERE
> > entry.id = word_entry.entry_id AND
> > word.id = word_entry.word_id AND
> > word.word GLOB ?
> > """, ('hui*',))
>
> I must admit I never use Python, but, considering the docs in
> http://docs.python.org/lib/module-sqlite3.html, shouldn't the
> last line be:
>
> """, ('hui*'))
>
> (without the extra comma)?
No, the extra comma is necessary to make ('hui*',) a tuple, however
this is totally unrelated to the problem.
Markus
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Huge performance drop when using prepared statement
On Sun, 9 Dec 2007 10:55:16 +0100, "Markus Gritsch"
<[EMAIL PROTECTED]> wrote:
>Hi,
>
>when using bind variables I get a huge performace drop compared to
>using a plain string. The query is demonstrated in the attached file
>"problematic_query.py".
Problematic SELECT:
> c.execute("""SELECT * FROM entry, word, word_entry WHERE
> entry.id = word_entry.entry_id AND
> word.id = word_entry.word_id AND
> word.word GLOB ?
> """, ('hui*',))
I must admit I never use Python, but, considering the docs in
http://docs.python.org/lib/module-sqlite3.html, shouldn't the
last line be:
""", ('hui*'))
(without the extra comma)?
>The database used can be downloaded from
> http://xile.org/le/prepared_statement.zip (1.75 MB)
>
>or generated by using the attached file "create_test_db.py".
>
>Kind regards,
>Markus
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Huge performance drop when using prepared statement
Hi,
when using bind variables I get a huge performace drop compared to
using a plain string. The query is demonstrated in the attached file
"problematic_query.py".
The database used can be downloaded from
http://xile.org/le/prepared_statement.zip (1.75 MB)
or generated by using the attached file "create_test_db.py".
Kind regards,
Markus
# -*- coding: utf-8 -*-
import time
from pysqlite2 import dbapi2 as sqlite
print 'pysqlite %s, sqlite %s' % (sqlite.version, sqlite.sqlite_version)
x = sqlite.connect('test.db3')
c = x.cursor()
# get all entries which contain a specific word
start = time.time()
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB 'hui*'
""")
print '%.3f seconds' % (time.time() - start) # 0.000 seconds
start = time.time()
c.execute("""SELECT * FROM entry, word, word_entry WHERE
entry.id = word_entry.entry_id AND
word.id = word_entry.word_id AND
word.word GLOB ?
""", ('hui*',))
print '%.3f seconds' % (time.time() - start) # 0.297 seconds
c.close()
x.close()
# -*- coding: utf-8 -*-
from random import choice
import re
from pysqlite2 import dbapi2 as sqlite
##x = sqlite.connect(':memory:')
x = sqlite.connect('test.db3')
c = x.cursor()
def generate_words():
words = []
for count in range(20):
words.append(''.join([choice('aeiou' if i%2 else 'bcdfghklmnprstw') for i in range(4)]))
return ' '.join(words)
# schema
c.execute("""CREATE TABLE entry (
id INTEGER PRIMARY KEY,
note LONGTEXT NOT NULL
)""")
c.execute("""CREATE TABLE word (
id INTEGER PRIMARY KEY,
word VARCHAR(40) NOT NULL UNIQUE
)""")
c.execute("""CREATE TABLE word_entry (
id INTEGER PRIMARY KEY,
word_id INT NOT NULL CONSTRAINT word_id_exists REFERENCES word(id) ,
entry_id INT NOT NULL CONSTRAINT entry_id_exists REFERENCES entry(id)
)""")
c.execute("""CREATE INDEX word_entry_entryIndex ON word_entry (entry_id)""")
c.execute("""CREATE UNIQUE INDEX word_entry_wordEntryIndex ON word_entry (word_id, entry_id)""")
# fill 'entry' table
for count in range(1, 5001):
c.execute('INSERT INTO entry (id, note) VALUES (?, ?)', (count, generate_words()))
# build index
regexp = re.compile('\w+', re.UNICODE)
wordDict = {}
id = 1
c.execute('SELECT id, note FROM entry')
for entry_id, note in c.fetchall():
for word in set(regexp.findall(note.lower())):
if len(word) <= 40:
if word in wordDict:
word_id = wordDict[word]
else:
word_id = id
c.execute('INSERT INTO word (id, word) VALUES (?, ?)', (id, word))
wordDict[word] = id
id += 1
c.execute('INSERT INTO word_entry (word_id, entry_id) VALUES (?, ?)', (word_id, entry_id))
c.close()
x.commit()
x.close()
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Does SQLite support modifying date through views?
On Sat, 8 Dec 2007 23:31:16 -0800 (PST), Robert Smith <[EMAIL PROTECTED]> wrote: >I am trying to implement an embedded application using SQLite. I need to >modify data through views. The update, insert, or delete operations may >refer to a computed column or a built-in function in a view. I experienced >problems. I am wondering if SQLite can support features of modifying date >through views. You can't update a view directly: http://www.sqlite.org/lang_createview.html "You cannot COPY, DELETE, INSERT or UPDATE a view. Views are read-only in SQLite. However, in many cases you can use a TRIGGER on the view to accomplish the same thing." The triggers meant are INSTEAD OF triggers: http://www.sqlite.org/lang_createtrigger.html sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] trigger-name INSTEAD OF database-event ON [database-name .] view-name trigger-action Your trigger-action can do about anything. >Thanks, >Robert HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
