Re: [sqlite] FTS3 Question
What I'm trying to do is get the query strings that are stored in category executed against the text stored in data. Category is essentially a fixed set of content, whilst data changes. I could just step through category and execute each query individually, but I was looking for a way to do it in a single operation. Basically ' which contain _any_ of the 'query' items from 'category'? ' -Original Message- On Behalf Of Scott Hess Should the 'data' table be joinable with the 'category' table in some way? Are you trying to match rows in 'data' which contain _all_ of the 'query' items from 'category', or which contain _any_ of the 'query' items from 'category'? Do you mean to have a WHERE clause or anything on what you're pulling in from 'category'? As presented, you've got "I do this, it doesn't work", which I can agree with. But I can't quite figure out what your intention for "works" is :-). -scott On Sat, May 17, 2008 at 12:49 AM, Mike Marshall <[EMAIL PROTECTED]> wrote: > I have an FTS3 table created as follows > > > > CREATE VIRTUAL TABLE data USING fts3(guid, text) > > > > And a standard table created thus > > > > CREATE TABLE category (label, query) > > > > > > What I would like to be able to do is an SQL query of the form > > > > SELECT guid FROM data WHERE text MATCH SELECT query FROM category > > > > But I can't seem to get it to work. > > > > Should it work? And if it should can someone point out what I am doing > wrong. > > > > Thanks > > > > Mike > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] text datatype and referential integrity
Thanks Barefeet! I thought that it could be done with triggers, but I am knew and had difficult time finding good tutorials on triggers in SQLite. Thanks for your detailed code below. I always saw SQLite as trying to be the database to beat out MS Access for the desktop user. And due to this belief I thought that referential integrity would have been the one of the first fundamental things that would have been created from version 1.0, as wells as support for the basic fundamental types of joins. I hope these things will be coming in the next release. If this is done, in my opinion SQLite will have won over MS Access!!! I am waiting for that GLORIOUS day to come!!! Palmer > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Tue, 20 May 2008 09:22:59 +1000 > Subject: Re: [sqlite] text datatype and referential integrity > > Hi Palmer, > > >> Say one defines a column to be of text type, but you want only > >> 'text' to contain only alphabetic characters, no numeric > >> characters, how would one create this rule and enforce it in SQLite > >> SQL or does one have to use triggers. > > > > Perhaps something like this: > > > > create table t(x check (ltrim(x, 'ABC...Zabc...z') = '')); > > > > (with full alphabet in place of ellipsis, of course). > > Personally, I'm more inclined to implement the constraint as triggers, > since you can change the trigger as needed. In contrast, altering a > constraint in the table requires dropping and recreating the whole > table and data. As far as I know there's no disadvantage in > implementing constraints in triggers, but I'd like to know if that's > false. > > something like: > > create table MyTable( MyTextColumn ) > ; > create trigger MyTriggerInsert before insert on MyTable > begin > select raise(abort, 'MyTextColumn must contain only alphabetic > characters.') > where ltrim(new.MyTextColumn, > 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') != ''; > end > ; > create trigger MyTriggerUpdate before update of MyTextColumn on MyTable > begin > select raise(abort, 'MyTextColumn must contain only alphabetic > characters.') > where ltrim(new.MyTextColumn, > 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') != ''; > end > ; > > >> Another question I have is, has proper referential integrity been > >> finally established and things like full joins and other joins. > > Not directly, but you can also do this via triggers. > > I'd also like to know if support for foreign keys (defined in the > table) is planned for SQLite down the track. > > Tom > BareFeet > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Change the world with e-mail. Join the i’m Initiative from Microsoft. http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ChangeWorld ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Thanks Puneet!!! > > SELECT '_' || startyear || '_' > FROM bsp_options > WHERE startyear LIKE '1990%' > > You results may look like so > > _1990 _ > > Notice the empty space after 1990? > > This was it! The client's csv file had an extra space at the end of the year. I didn't know this because I have no shell access to the server, so I can't get directly at the file. I can only write code based on the layout he gives me, column names and numbers. I called his sys admin and he says, "Well, I'm having dinner right now." B**s*ard! If I have to work as long as it takes to get this done, he can send me the @$%#$ csv file! And sure enough, there were trailing spaces in all the numeric fields! I just did a trim() when doing the INSERT. Thanks again all for all the help. I'm just sorry it turned out to be so simple. Also, I think being new to SQLite I'm probably focusing on it, thinking it must be working differently than other databases I have more experience with, and losing site of the raw data elements I'm working with. Live and learn, and a BIG thanks again to all. Now I can watch The Amazing Mrs. Pritchard and go to bed. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
D. Richard Hipp wrote: > On May 19, 2008, at 9:16 PM, Skip Evans wrote: > >> D. Richard Hipp wrote: >>> What does this show: >>> >>>SELECT DISTINCT typeof(startyear) FROM bsp_options; >>>SELECT DISTINCT typeof(endyear) FROM bsp_options; >>> >> I pasted those two statements into the SQL window >> and it returned: >> >> 2 query has been executed. 1.04 msec. >> 0 Line has been modified. >> > > That text didn't come from SQLite. > I'm using sqliteadmin. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
On May 19, 2008, at 9:16 PM, Skip Evans wrote: > D. Richard Hipp wrote: >> What does this show: >> >>SELECT DISTINCT typeof(startyear) FROM bsp_options; >>SELECT DISTINCT typeof(endyear) FROM bsp_options; >> > I pasted those two statements into the SQL window > and it returned: > > 2 query has been executed. 1.04 msec. > 0 Line has been modified. > That text didn't come from SQLite. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
On 5/19/08, Skip Evans <[EMAIL PROTECTED]> wrote: > Hey all, > > Here's the table definition: > > CREATE TABLE bsp_options ( > optionsID INTEGER NOT NULL PRIMARY KEY, > modelID INT(11) NOT NULL, > startyear INT(11) NOT NULL, > endyear INT(11) NOT NULL, > options TEXT NOT NULL, > productcodesize VARCHAR(10), > productdesc VARCHAR(200), > pattern VARCHAR(10) ); > > The insert statement that creates the records looks like this: > > $sql="INSERT INTO bsp_options > (modelID,startyear,endyear,options, > productcodesize,productdesc) VALUES > ($modelID, > ".$arr[3].", > ".$arr[4].", > > '".sqlite_escape_string($arr[5])."', > '".$arr[7]."', > '".$arr[6]."')"; > The above is too messy. How can you even read that code with all the quotes? Use bind params instead. They are much easier to manage and to read. > After this statement is used in a loop that inserts 14,000 records I can > see in SQLiteAdmin there are dozens of records that fit the criteria. > > SELECT * FROM bsp_options WHERE startyear=1990 > > Produces no results. > > SELECT * FROM bsp_options WHERE startyear='1990' > > Produces no results. > > SELECT * FROM bsp_options WHERE startyear like '1990%' > > Produces results!!! > > Is it treating the data like a string? Why is the like qualifier working??? Because you have something after 1990, most likely an empty space. Try the following statement -- SELECT '_' || startyear || '_' FROM bsp_options WHERE startyear LIKE '1990%' You results may look like so _1990 _ Notice the empty space after 1990? > -- > Skip Evans > Big Sky Penguin, LLC > 503 S Baldwin St, #1 > Madison, WI 53703 > 608-250-2720 > http://bigskypenguin.com > =-=-=-=-=-=-=-=-=-= > Check out PHPenguin, a lightweight and versatile > PHP/MySQL, AJAX & DHTML development framework. > http://phpenguin.bigskypenguin.com/ > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
I'm looking at the data in SQLiteAdmin, where I can just browse and see records that meet the criteria. I can search on modelID=351 to display the records, but then when I add "and startyear=1990 it returns no records, even though I can see with the first query records where this condition is in fact met. P Kishor wrote: > On 5/19/08, Skip Evans <[EMAIL PROTECTED]> wrote: >> Even this statement >> >> SELECT * FROM bsp_options WHERE modelID=351 and >> startyear=1990 >> >> Is returning no rows and I can clearly see dozens >> that meet the criteria! > > If you are not getting rows returned, how can you clearly see dozens > of rows that meet your criteria? What you are "seeing," from what I > can tell from your earlier emails, is what is being shown in whatever > program you are using to "look" at SQLite data. It could be that this > program that you are using is showing you something other than what is > really in the db. > > Since, in another email you mention that you don't have access to the > command line tool because you are accessing the database on someone > else's server, is it possible for you to download that database on to > your own computer where you can access it from the command line shell? > > -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hey all, Here's the table definition: CREATE TABLE bsp_options ( optionsID INTEGER NOT NULL PRIMARY KEY, modelID INT(11) NOT NULL, startyear INT(11) NOT NULL, endyear INT(11) NOT NULL, options TEXT NOT NULL, productcodesize VARCHAR(10), productdesc VARCHAR(200), pattern VARCHAR(10) ); The insert statement that creates the records looks like this: $sql="INSERT INTO bsp_options (modelID,startyear,endyear,options, productcodesize,productdesc) VALUES ($modelID, ".$arr[3].", ".$arr[4].", '".sqlite_escape_string($arr[5])."', '".$arr[7]."', '".$arr[6]."')"; After this statement is used in a loop that inserts 14,000 records I can see in SQLiteAdmin there are dozens of records that fit the criteria. SELECT * FROM bsp_options WHERE startyear=1990 Produces no results. SELECT * FROM bsp_options WHERE startyear='1990' Produces no results. SELECT * FROM bsp_options WHERE startyear like '1990%' Produces results!!! Is it treating the data like a string? Why is the like qualifier working??? -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
On 5/19/08, Skip Evans <[EMAIL PROTECTED]> wrote:
> Even this statement
>
> SELECT * FROM bsp_options WHERE modelID=351 and
> startyear=1990
>
> Is returning no rows and I can clearly see dozens
> that meet the criteria!
If you are not getting rows returned, how can you clearly see dozens
of rows that meet your criteria? What you are "seeing," from what I
can tell from your earlier emails, is what is being shown in whatever
program you are using to "look" at SQLite data. It could be that this
program that you are using is showing you something other than what is
really in the db.
Since, in another email you mention that you don't have access to the
command line tool because you are accessing the database on someone
else's server, is it possible for you to download that database on to
your own computer where you can access it from the command line shell?
>
> Here is the table def. I've recreated and
> repopulated them using int(11) for the year fields:
>
>
> CREATE TABLE bsp_options (
> optionsID INTEGER NOT NULL PRIMARY KEY,
> modelID INT(11) NOT NULL,
>
> startyear INT(11) NOT NULL,
> endyear INT(11) NOT NULL,
>
> options TEXT NOT NULL,
> productcodesize VARCHAR(10),
> productdesc VARCHAR(200),
> pattern VARCHAR(10) );
>
>
> AAAaaarrrgghhh!!! I'm eating my head from the
> inside out
nah. Don't do that. See below... you can insert pretty much anything
into any column. SQLite is reasonably smart about treating numbers and
strings accordingly. But you have something else going on, and you can
get much better diagnosis by looking at the data directly from the
command line rather than some third party tool.
sqlite> CREATE TABLE bsp_options (
...> optionsID INTEGER NOT NULL PRIMARY KEY,
...> modelID INT(11) NOT NULL,
...> startyear INT(11) NOT NULL,
...> endyear INT(11) NOT NULL,
...> options TEXT NOT NULL,
...> productcodesize VARCHAR(10),
...> productdesc VARCHAR(200),
...> pattern VARCHAR(10) );
sqlite> .s
CREATE TABLE bsp_options (
optionsID INTEGER NOT NULL PRIMARY KEY,
modelID INT(11) NOT NULL,
startyear INT(11) NOT NULL,
endyear INT(11) NOT NULL,
options TEXT NOT NULL,
productcodesize VARCHAR(10),
productdesc VARCHAR(200),
pattern VARCHAR(10) );
sqlite> insert into bsp_options (modelID, startyear, endyear, options,
productdesc) values ('', 'nineteen twenty eight', '2000', '', 23);
sqlite> select * from bsp_options;
1||nineteen twenty eight|2000|||23|
sqlite> select * from bsp_options where endyear > 1;
1||nineteen twenty eight|2000|||23|
sqlite> select typeof(startyear), typeof(endyear) from bsp_options
where optionsid = 1;
text|integer
sqlite>
>
> Skip--
>
> Skip Evans
> Big Sky Penguin, LLC
> 503 S Baldwin St, #1
> Madison, WI 53703
> 608-250-2720
> http://bigskypenguin.com
> =-=-=-=-=-=-=-=-=-=
> Check out PHPenguin, a lightweight and versatile
> PHP/MySQL, AJAX & DHTML development framework.
>
> http://phpenguin.bigskypenguin.com/
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
On 5/19/08, Skip Evans <[EMAIL PROTECTED]> wrote: > What happens if the field in the table is defined > as int(11) but the insert command wraps the values > with single quotes? What do you mean by "insert command wraps the values with single quotes"? You can't wrap values with single quotes in an integer, as far as I know. > > In MySQL this makes no difference. All the > operations still work the same. > > Is SQLite different? Here are a few suggestions for the long haul -- 1. Start here http://www.sqlite.org/datatype3.html 2. Read the rest of the documentation 3. Don't compare SQLite with xyz database. SQLite has its own strengths and weaknesses. Exploit its strengths. > > Thanks! > > Skip > > > -- > Skip Evans > Big Sky Penguin, LLC > 503 S Baldwin St, #1 > Madison, WI 53703 > 608-250-2720 > http://bigskypenguin.com > =-=-=-=-=-=-=-=-=-= > Check out PHPenguin, a lightweight and versatile > PHP/MySQL, AJAX & DHTML development framework. > > http://phpenguin.bigskypenguin.com/ > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
I don't have access to a command line tool. This server is at a client's hosting facility and I have no shell acess. BareFeet wrote: > Sorry, I left out a dot. It should be: > > .mode insert > select * from bsp_options where modelID=351; > > You enter tat using the command line tool. It will reveal the insert > statements used to create the data, which will show the years as > either 1990 (numeric) or '1990' (text). > > Tom > BareFeet > http://www.tandb.com.au/sqlite/compare/?ml > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Even this statement SELECT * FROM bsp_options WHERE modelID=351 and startyear=1990 Is returning no rows and I can clearly see dozens that meet the criteria! Here is the table def. I've recreated and repopulated them using int(11) for the year fields: CREATE TABLE bsp_options ( optionsID INTEGER NOT NULL PRIMARY KEY, modelID INT(11) NOT NULL, startyear INT(11) NOT NULL, endyear INT(11) NOT NULL, options TEXT NOT NULL, productcodesize VARCHAR(10), productdesc VARCHAR(200), pattern VARCHAR(10) ); AAAaaarrrgghhh!!! I'm eating my head from the inside out Skip-- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
What happens if the field in the table is defined as int(11) but the insert command wraps the values with single quotes? In MySQL this makes no difference. All the operations still work the same. Is SQLite different? Thanks! Skip -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
D. Richard Hipp wrote: > What does this show: > > SELECT DISTINCT typeof(startyear) FROM bsp_options; > SELECT DISTINCT typeof(endyear) FROM bsp_options; > I pasted those two statements into the SQL window and it returned: 2 query has been executed. 1.04 msec. 0 Line has been modified. But no data. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Hey Barefoot, Finally got it to work! I get how the syntax works now, you declare the columns and set values for them :) That's great, I can start creating my script now, 99,997 more entries to go hehe! Cheers! Carlo - Original Message From: BareFeet <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Tuesday, May 20, 2008 9:04:42 AM Subject: Re: [sqlite] Help with syntax Hi Carlo, > Well those original instructions are correct, as long as you unsmarten > the the quotes, so it reads: > > sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " I should clarify that I meant the syntax of your original instructions is correct, whereas what you are attempting is incorrect syntax. You seem to be just guessing and hoping it will work. Have you read through the syntax information on the SQLite site, such as the page I gave you for the insert statement syntax? > Got the following message: > > # sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " > SQL error: table list has no column named owner OK, lets look at the schema: > create table list (id integer primary key,owner_id integer not null > default 0,behavior integer not null default 1,entry text not > null,regex boolean not null default 0,timestamp_last timestamp not > null default 0,source integer not null default 0); As the error said, table list has no column named owner. There is however an owner_id column, so this should work: sqlite /var/local/database/dblist "insert into list (owner_id,behavior,entry) values(0,0,'newblacklistentry.com') " Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
On May 19, 2008, at 9:03 PM, Skip Evans wrote: > Hey D. Richard & all, > > Casting them as int's also did not work. Here's > the schema: > > CREATE TABLE bsp_options > ( > optionsID INTEGER NOT NULL PRIMARY KEY, > modelID INT(11) NOT NULL, > startyear VARCHAR(4) NOT NULL DEFAULT '0', > endyear VARCHAR(4) NOT NULL DEFAULT '0', > options TEXT NOT NULL, > productcodesize VARCHAR(10), > productdesc VARCHAR(200), > pattern VARCHAR(10) > ); What does this show: SELECT DISTINCT typeof(startyear) FROM bsp_options; SELECT DISTINCT typeof(endyear) FROM bsp_options; D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hi Skip, > I'm not sure what you mean by what is below: >> mode insert >> select * from bsp_options where modelID=351; >> > Is that two separate SQL statements? I'm testing all this using the > SQLiteAdmin tool, so do I paste > what you have above in the SQL window? I tried that and got an > "error near mode" back. Sorry, I left out a dot. It should be: .mode insert select * from bsp_options where modelID=351; You enter tat using the command line tool. It will reveal the insert statements used to create the data, which will show the years as either 1990 (numeric) or '1990' (text). Tom BareFeet http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Hi Carlo, > Well those original instructions are correct, as long as you unsmarten > the the quotes, so it reads: > > sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " I should clarify that I meant the syntax of your original instructions is correct, whereas what you are attempting is incorrect syntax. You seem to be just guessing and hoping it will work. Have you read through the syntax information on the SQLite site, such as the page I gave you for the insert statement syntax? > Got the following message: > > # sqlite /var/local/database/dblist "insert into list > (owner,behavior,entry) values(0,0,'newblacklistentry.com') " > SQL error: table list has no column named owner OK, lets look at the schema: > create table list (id integer primary key,owner_id integer not null > default 0,behavior integer not null default 1,entry text not > null,regex boolean not null default 0,timestamp_last timestamp not > null default 0,source integer not null default 0); As the error said, table list has no column named owner. There is however an owner_id column, so this should work: sqlite /var/local/database/dblist "insert into list (owner_id,behavior,entry) values(0,0,'newblacklistentry.com') " Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hey D. Richard & all, Casting them as int's also did not work. Here's the schema: CREATE TABLE bsp_options ( optionsID INTEGER NOT NULL PRIMARY KEY, modelID INT(11) NOT NULL, startyear VARCHAR(4) NOT NULL DEFAULT '0', endyear VARCHAR(4) NOT NULL DEFAULT '0', options TEXT NOT NULL, productcodesize VARCHAR(10), productdesc VARCHAR(200), pattern VARCHAR(10) ); D. Richard Hipp wrote: > On May 19, 2008, at 8:42 PM, Skip Evans wrote: > >> Hey all, >> >> For the life of me I can't figure out why the >> following statement returns no rows: >> >> SELECT productcodesize,options FROM bsp_options >> WHERE modelID=351 AND '1990' >= startyear AND >> '1990' <= endyear > > My guess is that you are storing endyear as an integer within a column > with no affinity. And an integer is always less than a string so > '1990'<=endyear is always false. What is your table schema? Have you > tried: > >WHERE modelID=351 AND CAST(startyear AS INT)<=1990 AND CAST(endyear > AS INT)>=1999 > >> >> I'm looking right now at a printout of records >> from the query: >> SELECT productcodesize,options FROM bsp_options >> WHERE modelID=351 >> >> Where the fields are: >> >> modelID: 351 startyear: 1990 endyear:1993 >> >> ..and there are a bunch of them. >> >> Can anyone spot anything wrong with that query??? >> >> Thanks! >> >> -- >> Skip Evans >> Big Sky Penguin, LLC >> 503 S Baldwin St, #1 >> Madison, WI 53703 >> 608-250-2720 >> http://bigskypenguin.com >> =-=-=-=-=-=-=-=-=-= >> Check out PHPenguin, a lightweight and versatile >> PHP/MySQL, AJAX & DHTML development framework. >> http://phpenguin.bigskypenguin.com/ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hey, I'm not sure what you mean by what is below: BareFeet wrote: > Try this: > > mode insert > select * from bsp_options where modelID=351; > Is that two separate SQL statements? I'm testing all this using the SQLiteAdmin tool, so do I paste what you have above in the SQL window? I tried that and got an "error near mode" back. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
From: BareFeet <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Tuesday, May 20, 2008 8:37:37 AM Subject: Re: [sqlite] Help with syntax Well those original instructions are correct, as long as you unsmarten the the quotes, so it reads: sqlite /var/local/database/dblist "insert into list (owner,behavior,entry) values(0,0,'newblacklistentry.com') " Barefoot, Got the following message: # sqlite /var/local/database/dblist "insert into list (owner,behavior,entry) values(0,0,'newblacklistentry.com') " SQL error: table list has no column named owner I put values inside the brackets based on the instructions given me: The value for behavior is 0 for blacklist and 1 for whitelist. Owner is always 0 (for system) in your tests. I typed in: sqlite /var/local/database/dblist "insert into list (0,0,newblacklistentry.com) values(0,0,'newblacklistentry.com') " And I get: SQL error: near "0": syntax error Carlo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Jay A. Kreibich wrote: > > Do you really mean for the years to be string literals and not numbers? > I've tried it both ways to no avail. -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
On May 19, 2008, at 8:42 PM, Skip Evans wrote: > Hey all, > > For the life of me I can't figure out why the > following statement returns no rows: > > SELECT productcodesize,options FROM bsp_options > WHERE modelID=351 AND '1990' >= startyear AND > '1990' <= endyear My guess is that you are storing endyear as an integer within a column with no affinity. And an integer is always less than a string so '1990'<=endyear is always false. What is your table schema? Have you tried: WHERE modelID=351 AND CAST(startyear AS INT)<=1990 AND CAST(endyear AS INT)>=1999 > > > I'm looking right now at a printout of records > from the query: > SELECT productcodesize,options FROM bsp_options > WHERE modelID=351 > > Where the fields are: > > modelID: 351 startyear: 1990 endyear:1993 > > ..and there are a bunch of them. > > Can anyone spot anything wrong with that query??? > > Thanks! > > -- > Skip Evans > Big Sky Penguin, LLC > 503 S Baldwin St, #1 > Madison, WI 53703 > 608-250-2720 > http://bigskypenguin.com > =-=-=-=-=-=-=-=-=-= > Check out PHPenguin, a lightweight and versatile > PHP/MySQL, AJAX & DHTML development framework. > http://phpenguin.bigskypenguin.com/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
Hi Skip, Try this: mode insert select * from bsp_options where modelID=351; That will show us detail of how the data is stored, such as whether startyear and endyear are stored as text or numeric. If they are stored as numeric, but your query treats them as text, then that's your problem. > Tom BareFeet http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Baffling SQLite statement
On Mon, May 19, 2008 at 07:42:36PM -0500, Skip Evans scratched on the wall: > Hey all, > > For the life of me I can't figure out why the > following statement returns no rows: > > SELECT productcodesize,options FROM bsp_options > WHERE modelID=351 AND '1990' >= startyear AND > '1990' <= endyear > > I'm looking right now at a printout of records > from the query: > SELECT productcodesize,options FROM bsp_options > WHERE modelID=351 > > Where the fields are: > > modelID: 351 startyear: 1990 endyear:1993 > > ..and there are a bunch of them. > > Can anyone spot anything wrong with that query??? Do you really mean for the years to be string literals and not numbers? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Baffling SQLite statement
Hey all, For the life of me I can't figure out why the following statement returns no rows: SELECT productcodesize,options FROM bsp_options WHERE modelID=351 AND '1990' >= startyear AND '1990' <= endyear I'm looking right now at a printout of records from the query: SELECT productcodesize,options FROM bsp_options WHERE modelID=351 Where the fields are: modelID: 351 startyear: 1990 endyear:1993 ..and there are a bunch of them. Can anyone spot anything wrong with that query??? Thanks! -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison, WI 53703 608-250-2720 http://bigskypenguin.com =-=-=-=-=-=-=-=-=-= Check out PHPenguin, a lightweight and versatile PHP/MySQL, AJAX & DHTML development framework. http://phpenguin.bigskypenguin.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Hi Carlo,
> Here are the instructions given me
> You can add entries with inserts from the command line. You should
> be able to do it like this:
>
> sqlite /var/local/database/dblist “insert into list
> (owner,behavior,entry) values(0,0,’newblacklistentry.com’) ”
Well those original instructions are correct, as long as you unsmarten
the the quotes, so it reads:
sqlite /var/local/database/dblist "insert into list
(owner,behavior,entry) values(0,0,'newblacklistentry.com') "
But what you were trying was incorrect:
>> sqlite /var/local/database/dblist "insert into list
>> ('0,0,newblacklistentry1,com') values
>> ('0,0,newblacklistentry1.com')"
>>
Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
@Igor: That's a good idea, taking the list out of a CVS formatted file and run
that command
@Barefoot: Here are the instructions given me, which includes the schema you
requested:
You get the schema of entry with:
.schema
sqlite> .schema
create table list (id integer primary key,owner_id integer not null default
0,behavior integer not null default 1,entry text not null,regex boolean not
null default 0,timestamp_last timestamp not null default 0,source integer not
null default 0);
create table owner (id integer primary key,owner text);
create index list_entry_idx on list(entry);
create index list_owner_idx on list(owner_id);
create index list_regex_idx on list(regex);
create index owner_owner_idx on owner(owner);
sqlite>
You can do queries with:
select * from list;
1|0|1|white.com|0|1210870781|0
2|0|0|black.com|0|0|0
You can add entries with inserts from the command line. You should be able to
do it like this:
sqlite /var/local/database/dblist “insert into list (owner,behavior,entry)
values(0,0,’newblacklistentry.com’) ”
The value for behavior is 0 for blacklist and 1 for whitelist. Owner is always
0 (for system) in your tests.
* Note the syntax he provided does not work straightaway. You can see how I
tried with my SQL knowledge (not that great obviously) to make something out of
the statement provided.
Thanks for the quick replies :) Appreciate it!
Carlo
- Original Message
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, May 20, 2008 7:27:08 AM
Subject: Re: [sqlite] Help with syntax
Carlo S. Marcelo <[EMAIL PROTECTED]>
wrote:
> Below is the syntax and error I received.
>
> [EMAIL PROTECTED] root]# sqlite
> /var/local/database/dblist "insert into
> list ('0,0,newblacklistentry1,com') values
> ('0,0,newblacklistentry1.com')"
> SQL error: table list has no column named
> 0,0,newblacklistentry1,com
This statement makes no sense. In the first pair of parens, you are
supposed to provide a list of column names. In the second, a list of
values. A new row is inserted, in which specified columns are set to
specified values (and columns that were not mentioned, if any, take on
their default values). Something like this:
insert into list(column1, column2, column3) values (0, 0,
'newblacklistentry1.com');
> What I am trying to do here is create a script that
> will populate the database with a hundred thousand
> entries (no duplicates).
Perhaps you can use .import directive supported by sqlite command line
shell. You need a file in CSV format, one row per record. Then just do
.import filename tablename
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 5, Issue 47
You have chosen to reply to a digest post you received, and as a
result, I have no idea what thread you are talking about. Please note
at least two things -- one, don't hijack threads because they break
conversation, digress, distract, and confuse; this includes not
replying to digests; and two, you don't have to cc your email to me --
remember, I am also subscribed to the list. When you cc to me, I get
two copies, and that simply hurts the rain forests.
On 5/19/08, Hildemaro Carrasquel <[EMAIL PROTECTED]> wrote:
> Thank you Punk and all.
>
> My question is because i want to make a application that it can have approx
> 10.000 registers, but there are many application are writing in data base
> sqlite, this write can be at the same time, Is it possible this?
As I said in response to your earlier email (I am sure I remember it
now -- it was about SQLite server or how many concurrent connections
it can support or something like that) -- SQLite will respond to each
request as it comes in. You have design concurrency in your
application.
>
> 2008/5/20 <[EMAIL PROTECTED]>:
> > Send sqlite-users mailing list submissions to
> >sqlite-users@sqlite.org
> >
> > To subscribe or unsubscribe via the World Wide Web, visit
> >
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > or, via email, send a message with subject or body 'help' to
> >[EMAIL PROTECTED]
> >
> > You can reach the person managing the list at
> >[EMAIL PROTECTED]
> >
> > When replying, please edit your Subject line so it is more specific
> > than "Re: Contents of sqlite-users digest..."
> >
> >
> > Today's Topics:
> >
> > 1. Re: design question / discussion (Rich Rattanni)
> > 2. Re: Calculating Difference between Time using SQLite (P Kishor)
> > 3. Sqlite3 (Hildemaro Carrasquel)
> > 4. Re: sqlite3_transfer_bindings obsolete? (Ralf Junker)
> > 5. Re: Sqlite3 (P Kishor)
> > 6. Re: Indexing virtual tables (Aladdin Lamp?)
> > 7. sqlite3_mprintf() best practice (Aladdin Lamp?)
> > 8. sqlite3Atoi64() and input string "0" (Aladdin Lamp?)
> > 9. FW: SQLite : text datatype and referential integrity
> > (palmer ristevski)
> >
> >
> >
> --
> >
> > Message: 1
> > Date: Sun, 18 May 2008 12:44:55 -0400
> > From: "Rich Rattanni" <[EMAIL PROTECTED]>
> > Subject: Re: [sqlite] design question / discussion
> > To: "General Discussion of SQLite Database"
> > Message-ID:
> >
> <[EMAIL PROTECTED]>
> > Content-Type: text/plain; charset=ISO-8859-1
> >
> > Thanks for your reply. I have done some quick timing tests on my
> > system; a vacuum can take 5 or more minutes (synchronous full), and a
> > delete and recreate is rougly 3 seconds. I think I did such a test
> > with a 30MB database. The database resides on a jffs2 file system
> > (compression off), which seems to have a constant time for deletions.
> >
> > I should have included I am using sqlite 3.4.0.
> >
> >
> > On Sun, May 18, 2008 at 4:45 AM, <[EMAIL PROTECTED]> wrote:
> > >> Hi I have a general design question. I have the following senario...
> > >
> > > IMHO your design sound reasonable. In relation with the vacuum question
> > > I suggest try to delete and re-create each table and watch both timings.
> > >
> > > HTH
> > >
> > > Adolfo
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > >
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> > --
> >
> > Message: 2
> > Date: Sun, 18 May 2008 17:24:50 -0400
> > From: "P Kishor" <[EMAIL PROTECTED]>
> > Subject: Re: [sqlite] Calculating Difference between Time using SQLite
> > To: "General Discussion of SQLite Database"
> > Message-ID:
> >
> <[EMAIL PROTECTED]>
> > Content-Type: text/plain; charset=ISO-8859-1
> >
> > On 5/16/08, P Kishor <[EMAIL PROTECTED]> wrote:
> > > On 5/16/08, Scott Baker <[EMAIL PROTECTED]> wrote:
> > > > Miguel wrote:
> > > > > Estimates,
> > > > > First of all, excuse my English, I recognise that it is not my
> strong.
> > > > > I need to do a query on a table and I return the difference in
> minutes
> > > > > between
> > > > > two times loaded in the table.
> > > > > Which would be the best way to make these differences.
> > > > > Since already thank you very much and greetings.
> > > >
> > > >
> > > > If you convert both dates to unixtime (seconds) and subtract you'll
> get
> > > > seconds between the two dates. Then divide by 60.
> > > >
> > > > SELECT (strftime('%s','now') - strftime('%s','2004-01-01 02:34:56'))
> / 60;
> > > >
> > > >
> > > >
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> > > >
> > > >
> > >
> > > well, no not really. The original question is about subtracting date
> > > in one row from the date in another row. This is an Igor-level
> > > question, but the following comes to my mind --
> > >
> > > Given the following
Re: [sqlite] Help with syntax
Carlo S. Marcelo <[EMAIL PROTECTED]>
wrote:
> Below is the syntax and error I received.
>
> [EMAIL PROTECTED] root]# sqlite
> /var/local/database/dblist "insert into
> list ('0,0,newblacklistentry1,com') values
> ('0,0,newblacklistentry1.com')"
> SQL error: table list has no column named
> 0,0,newblacklistentry1,com
This statement makes no sense. In the first pair of parens, you are
supposed to provide a list of column names. In the second, a list of
values. A new row is inserted, in which specified columns are set to
specified values (and columns that were not mentioned, if any, take on
their default values). Something like this:
insert into list(column1, column2, column3) values (0, 0,
'newblacklistentry1.com');
> What I am trying to do here is create a script that
> will populate the database with a hundred thousand
> entries (no duplicates).
Perhaps you can use .import directive supported by sqlite command line
shell. You need a file in CSV format, one row per record. Then just do
.import filename tablename
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with syntax
Hi Carlo,
> insert into list ('0,0,newblacklistentry1,com')
> values ('0,0,newblacklistentry1.com')"
> SQL error: table list has no column named 0,0,newblacklistentry1,com
Well, the error tells you the problem. You are asking SQLite to insert
a text value '0,0,newblacklistentry1.com' into a column called
'0,0,newblacklistentry1,com' in a table called list.
Does your table have a column called '0,0,newblacklistentry1,com' ?
I'm guessing not, so there's your problem.
If you're unfamiliar with the syntax required for an insert statement,
look here:
http://www.sqlite.org/lang_insert.html
If you still need help, please post the schema (ie the create table
statements) of your database and explain what you want inserted where.
Tom
BareFeet
http://www.tandb.com.au/sqlite/compare/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] text datatype and referential integrity
Hi Palmer, >> Say one defines a column to be of text type, but you want only >> 'text' to contain only alphabetic characters, no numeric >> characters, how would one create this rule and enforce it in SQLite >> SQL or does one have to use triggers. > > Perhaps something like this: > > create table t(x check (ltrim(x, 'ABC...Zabc...z') = '')); > > (with full alphabet in place of ellipsis, of course). Personally, I'm more inclined to implement the constraint as triggers, since you can change the trigger as needed. In contrast, altering a constraint in the table requires dropping and recreating the whole table and data. As far as I know there's no disadvantage in implementing constraints in triggers, but I'd like to know if that's false. something like: create table MyTable( MyTextColumn ) ; create trigger MyTriggerInsert before insert on MyTable begin select raise(abort, 'MyTextColumn must contain only alphabetic characters.') where ltrim(new.MyTextColumn, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') != ''; end ; create trigger MyTriggerUpdate before update of MyTextColumn on MyTable begin select raise(abort, 'MyTextColumn must contain only alphabetic characters.') where ltrim(new.MyTextColumn, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz') != ''; end ; >> Another question I have is, has proper referential integrity been >> finally established and things like full joins and other joins. Not directly, but you can also do this via triggers. I'd also like to know if support for foreign keys (defined in the table) is planned for SQLite down the track. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with syntax
Hi! I just subscribed to the mailing list as my work
now requires some sqlite stuff.
Below is the syntax and error I received.
[EMAIL PROTECTED] root]# sqlite
/var/local/database/dblist "insert into
list ('0,0,newblacklistentry1,com') values
('0,0,newblacklistentry1.com')"
SQL error: table list has no column named
0,0,newblacklistentry1,com
What I am trying to do here is create a script that
will populate the database with a hundred thousand
entries (no duplicates). Hope this information helps.
Cheers!
Ogoy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing virtual tables
It sounds like you're still trying to push your index outside the virtual table. My suggestion was to have your virtual table t internally create a regular SQLite table t_aux (or something of the sort) which replicates the data you want to index from your flat file, and puts an index on it. Then your virtual table xBestIndex implementation can use that table to find things more quickly, if the inputs are appropriate. It will be easiest if you just replicate the data to be indexed in a regular SQLite table that the virtual table owns, and use regular SQLite calls to set the index up. The only way you can currently have an index without the data in SQLite is to implement it yourself, which could be painful. It sounds like the size of the data you need in your index is much much smaller than the size of the entire dataset, so it may not be worth the effort to do more than use regular SQLite tables and indices in your virtual-table implementation. [Put another way, implement it and see how it works!] --- In terms of proving out whether the system can work, it may be easier to take the virtual table implementation you currently have, and build the INDEX1_SORT type of table you propose, then do joins. This is a bit clunky, but if this is not fast enough, or is using too much space or something, then it's likely that an implementation pushed into the virtual table will _also_ not be fast enough or will use too much space, or whatever the problem is. [Virtual tables aren't magic! They just let you rearrange things.] --- So far as optimal... virtual tables (and SQLite in general) are just tools for solving problems. I think you need to be determining if they can be used to create a good-enough solution to your problem, rather than an optimal solution. -scott On Sat, May 17, 2008 at 7:41 AM, Aladdin Lampé <[EMAIL PROTECTED]> wrote: > > Hi! Here is what I'm still trying to achieve: > - I have a custom file format having "records" and file offsets. > - Each record in that custom file format has the same number of fields, but > the records itself are variable length, that's why I need a file offset to > quickly locate a record. One other way (if you can wait for a very long > time...) is to walk sequentially the records list to get the desired record. > - I've implemented a working SQLite "virtual table" in order to be able to > read and query my custom file format through SQLite. > - Now, basically what I'd like would be to "CREATE INDEX" on a field of my > virtual table to take advantage of it in the xBestIndex callback. But the > documentation says that we cannot use "CREATE INDEX" on virtual tables. > > Let's say the data in the field "F1" of my virtual table "VFILE", and the > file offsets are the following: > F1 fileoffset > -- > a 10 > b 21 > z 34 > x 45 > a 51 > x 69 > z 73 > a 88 > x 94 > > I want to index the column F1, to be able to have a quick response to queries > like: > select * from VFILE where F1='x' > > At this point, I think I have only 3 possible strategies: > 1. Use SQLite tables to "fake" a standard index using SQLite tables > 2. Use internal SQLite B-Tree routines to implement my index > (sqlite3BtreeCreateTable and stuff) > 3. Implement my own B-Tree and sort algorithms to achieve this, externally to > SQLite > > Strategy 3 is precisely what I'm trying to avoid (too much work and testing > :-) ). > Strategy 2 is strongly discouraged by DRH. > > Then strategy 1 seems to be (like you've just said) the only way to go: > > a) Duplicate the data to be indexed (and the file offsets to use) > > create table INDEX1_SORT as select F1, fileoffset from VFILE order by F1, > fileoffset > > INDEX1_SORT: physical table > F1 fileoffset > -- > a 10 > a 51 > a 88 > b 21 > x 45 > x 69 > x 94 > z 34 > z 73 > > b) Create an index on that data > > OPTION 1: Use SQLite CREATE INDEX at this point. > b.1.1) create index on INDEX1_SORT(F1) > > OPTION 2: Fake index with custom tables > b.2.1) create table INDEX2_SUM as select F1, min(INDEX1_SORT.rowid) as > 'minrow', max(INDEX1_SORT.rowid) as 'maxrow' from INDEX1_SORT group by F1 > > INDEX2_SUM: physical table > F1 minrow maxrow > --- > a 1 3 > b 4 4 > x 5 7 > z 8 9 > > b.2.2) create index on INDEX_2_SUM(F1) > > > * > > Usage for option 2: > - Use INDEX2_SUM to fetch the requested value in the initial query (select * > from VFILE where F1='x') > - Get data from table INDEX1_SORT between rowid "minrow" (5)
Re: [sqlite] FTS3 Question
Should the 'data' table be joinable with the 'category' table in some way? Are you trying to match rows in 'data' which contain _all_ of the 'query' items from 'category', or which contain _any_ of the 'query' items from 'category'? Do you mean to have a WHERE clause or anything on what you're pulling in from 'category'? As presented, you've got "I do this, it doesn't work", which I can agree with. But I can't quite figure out what your intention for "works" is :-). -scott On Sat, May 17, 2008 at 12:49 AM, Mike Marshall <[EMAIL PROTECTED]> wrote: > I have an FTS3 table created as follows > > > > CREATE VIRTUAL TABLE data USING fts3(guid, text) > > > > And a standard table created thus > > > > CREATE TABLE category (label, query) > > > > > > What I would like to be able to do is an SQL query of the form > > > > SELECT guid FROM data WHERE text MATCH SELECT query FROM category > > > > But I can't seem to get it to work. > > > > Should it work? And if it should can someone point out what I am doing > wrong. > > > > Thanks > > > > Mike > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_ENABLE_FTS3
Hello: I want to build sqlite3.dll with fts3 support. I can build the dll but I don't know how to "switch on" or define SQLITE_ENABLE_FTS3. Obviously, I am not a c programmer, I just want the dll for powerbasic. Here is what I have: I have visual studio 2005. console project set. no precompiled headers set. NO_TCL has been added to preprocessor definitions. I am using the amalgamation. builds ok, lots of warnings but no errors dll seems to be ok. I found "#ifdef SQLITE_ENABLE_FTS3" by searching the code but I do not know how to "def" SQLITE_ENABLE_FTS3. I can guess by setting it to some value somewhere. Please tell me how to do this and I will go back to powerbasic and leave you guys alone. thanks, Paul Breen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite remote management tools?
We are in Beta with a windows tool: The RemSQL Library consists of standard dynamic link libraries (DLLs) that can be used in a wide variety of programming languages. Because most programming languages support calling functions exported from a DLL, the Library Edition provides the broadest compatibility with the various development tools available for the Windows platform. The Library Edition is ideal for languages such as C, Delphi, VB, FreeBasic, PowerBASIC etc . The RemSQL Library uses standard Windows DLLs just like SQLite.DLL (not COM activeX) which can be used by virtually any Windows programming language or scripting tool. RemSQL is a high-performance, self-contained, zero-configuration, transactional library for extending SQLite to a remote server via the internet. It is built around a fast, memory efficient wrapper for the SQLite library, capable of handling large amounts of INSERT/QUERY data/from a remote (or local) SQLite database. Like SQLite, RemSQL is a small (180k) standardized interface Dll that natively provides CGI functionality, Encryption, Hashing, Very secure HTTP/TCP communication, SQLite User Defined Functions, and Supplemental operations like password verification, client software feature authorization, File transfer, remote program launching, Gathering Server metrics and any other remote user operation requiring an efficient communication framework. RemSQL is an IPC (interprocess communication) utilizing HTTP via TCP (Transmission Control Protocol) for the Windows platform. Both Client and Server must be running windows and utlize the same Dll. On the server, a simple CGI script (CGI.exe) can used to communicate between HTTP and the Dll. This allows low cost commercial windows hosting solutions to be used as a server. Alternatively an ISAPI module can also be created. In keeping with the SQLite concept, RemSQL does not need to be "installed" before it is used. There is no "setup" procedure. There is no Service that needs to be started, stopped, or configured, no Firewall "issues" to deal with and no Router port forwarding to be done. There is no need for an administrator to create accounts or assign access permissions to users, and no incompatibility issues with each new release of the database. There are no configuration files or setup utilities or installers that "might take a minute or two". Nothing needs to be done to tell the system that RemSQL is running. There is no MS-Management-Console, DCOM Config or Proxy/Interface-Creation/Registration. The user will not have to deal with a constant barrage of error messages, warnings, and popups: DNS errors, transient network outages, ASP errors, Javascript problems, missing plugins, temporary server outages, incorrect or expired certificates, problems connecting to the MySQL backend (common on any slashdotted web site), and a whole host of other issues. You will not be needing 63MB of compressed runtime modules or any external libraries. Pre-releases of the WinFX Runtime Components 3.0, the WinFX SDK, the Platform SDK, the Windows SDK, the .NET Framework redistributable, Microsoft Visual Studio, and their dependencies will not interfere with or cause anything to "fail or break functionality". There is no Base64 encoding and the associated XML bloating to 300% or more (some WMV files) Just copy RemSQL3.Dll to the target machine, and RemSQL3CGI.exe to the Server, and begin work. RemSQL is released under the Apache 2.0 license and is therefore free for commercial use and distribution. If that is what you are looking for and would like to beta test this and/or convert the headers for your language, please contact me. Derek Richard Klein <[EMAIL PROTECTED]> wrote: Federico Granata wrote: >> I was hoping there might be a client/server management tool out there. >> I would need the source code, since the server part would need to be >> ported to my embedded device. >> > Maybe you haven't yet read this http://www.sqlite.org/serverless.html > There isn't a sqlite server so you can't have a sqlite client, local or > remote. Sqlite doesn't come with a server, but some enterprising tool developer could write one, right? :-) - Richard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite : text datatype and referential integrity
Igor, Thanks once again for your quick response. Sincerely, Palmer > To: sqlite-users@sqlite.org > From: [EMAIL PROTECTED] > Date: Mon, 19 May 2008 13:54:48 -0400 > Subject: Re: [sqlite] SQLite : text datatype and referential integrity > > palmer ristevski <[EMAIL PROTECTED]> > wrote: > > At first I thought that LTRIM you were refering to VB6, > > but then I noticed this is in a Create Table statement which then > > implied to me SQL stuff. > > I did a search and found that LTRIM is also a SQLite > > function/expression. Now is the expression 'check' also some > > predefined function in SQLite. > > Can you direct me to some resource on the web for this. > > http://sqlite.org/ > http://sqlite.org/docs.html > http://sqlite.org/lang.html > > Specifically, this is documentation on CREATE TABLE as implemented by > SQLite: > > http://sqlite.org/lang_createtable.html > > Note the CHECK constraint. Here's the list of built-in functions > supported by SQLite: > > http://sqlite.org/lang_corefunc.html > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ Keep your kids safer online with Windows Live Family Safety. http://www.windowslive.com/family_safety/overview.html?ocid=TXT_TAGLM_WL_Refresh_family_safety_052008 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite : text datatype and referential integrity
palmer ristevski <[EMAIL PROTECTED]> wrote: > At first I thought that LTRIM you were refering to VB6, > but then I noticed this is in a Create Table statement which then > implied to me SQL stuff. > I did a search and found that LTRIM is also a SQLite > function/expression. Now is the expression 'check' also some > predefined function in SQLite. > Can you direct me to some resource on the web for this. http://sqlite.org/ http://sqlite.org/docs.html http://sqlite.org/lang.html Specifically, this is documentation on CREATE TABLE as implemented by SQLite: http://sqlite.org/lang_createtable.html Note the CHECK constraint. Here's the list of built-in functions supported by SQLite: http://sqlite.org/lang_corefunc.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite : text datatype and referential integrity
Thanks Igor! At first I thought that LTRIM you were refering to VB6, but then I noticed this is in a Create Table statement which then implied to me SQL stuff. I did a search and found that LTRIM is also a SQLite function/expression. Now is the expression 'check' also some predefined function in SQLite. Can you direct me to some resource on the web for this. Sincerely, Palmer > To: sqlite-users@sqlite.org > From: [EMAIL PROTECTED] > Date: Mon, 19 May 2008 13:25:53 -0400 > Subject: Re: [sqlite] SQLite : text datatype and referential integrity > > palmer ristevski <[EMAIL PROTECTED]> > wrote: > > Say one defines a column to be of text type, > > but you want only 'text' to contain only alphabetic characters, > > no numeric characters, how would one create this rule and enforce it > > in SQLite SQL or does one have to use triggers. > > Perhaps something like this: > > create table t(x check (ltrim(x, 'ABC...Zabc...z') = '')); > > (with full alphabet in place of ellipsis, of course). > > > Another question I have is, has proper referential integrity been > > finally established > > and things like full joins and other joins. > > No. Still no foreign keys, and only left outer joins. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _ E-mail for the greater good. Join the i’m Initiative from Microsoft. http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ GreaterGood ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_mprintf() best practice
Aladdin Lampé <[EMAIL PROTECTED]> wrote: > What's the recommended usage and best practice for this function? It's best not to use it at all, but instead use a parameterized query and bind the user-provided untrusted string as a parameter. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite : text datatype and referential integrity
palmer ristevski <[EMAIL PROTECTED]> wrote: > Say one defines a column to be of text type, > but you want only 'text' to contain only alphabetic characters, > no numeric characters, how would one create this rule and enforce it > in SQLite SQL or does one have to use triggers. Perhaps something like this: create table t(x check (ltrim(x, 'ABC...Zabc...z') = '')); (with full alphabet in place of ellipsis, of course). > Another question I have is, has proper referential integrity been > finally established > and things like full joins and other joins. No. Still no foreign keys, and only left outer joins. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SEGFAULT using 3.5.9 reproducible!
Ticket 3127 created. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] design question / discussion
Rich, >From your design it appears you are writing to a seperate db while a >"download" is happening? I'm guessing that is to prevent a read/write lock >contention correct? It seems to me that any new data coming in will need to write and you are simply looking to read during a download operation and trying to avoid lock contention and delays correct? DownloadInfo table is used to keep track of the point where the last download completed successfully. data to download = last successful to max rowid. (ie a subset). One thought I had to avoid the contention is if this is a threaded application? you could enable the shared cache and read_uncommitted isolation. It might be a bit tricky in that you'll probably have to get the "committed" data in a txn, then set the uncomitted mode to read to avoid waiting for locks. Ken Rich Rattanni <[EMAIL PROTECTED]> wrote: Hi I have a general design question. I have the following senario... In an embedded system running linux 2.6.2x I have a sqlite database constantly being updated with data acquired by the system. I cant lose data (hence why I am using sqlite in the first place). However periodically I have download the data contain within the database to a central server. The system cannot stall during the download and must continue to record data. Also, after the download I need to shrink the database size, simply because if the database is allowed to grow to its max size (~50MB) then every download thereafter would be 50MB, which is unacceptable. I would simply vacuum the database, but this takes too much time and stalls the system. My solution is the following (still roughed out on scraps of paper and gray matter). have two databases on the system at all times (data.sqlite.(x) and data.sqlite.(x+1)) All data written into x. When a download is requested... Mark highest rowid in each table in database (x) in a table called DownloadInfo Begin logging data to (x+1) Download done (success or failure - downloads may be cancelled or timeout) Attach x+1 to x Begin transaction delete all data in x from tables equal to <= rowid saved in DownloadInfo move any data stored in x+1 to x if download was successful... mark in x that a download was successful in DownloadInfo At next powerup... Scan x.DownloadInfo, see if a download was successful... Yes Attach x+1 to x attach x+2 to x begin transaction Build new database x+2 Move data from x to x+1 Mark database has been deleted in DownloadInfo commit. delete (using os, unlink perhaps) No Do nothing. So its kinda complicated, but I think such things are necessary. For instance, a vacuum is out of the question, it just takes too long. Thats why the double database scheme works good for deleting old databases. I guess i want to stop here and leave some info out. That way I don't suppress any good ideas. And as always I really appreciate any help i can get. I tried to implement something similar, but I was copying an already prepared sqlite database which was not very reliable. Guess another question, maybe one that solves this one. has any improvements on auto-vacuum been made? Does anyone trust it or can anyone attest to its fault tolerance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: SQLite : text datatype and referential integrity
From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: SQLite : text datatype and referential integrity Date: Sat, 17 May 2008 17:09:25 + Hi, Say one defines a column to be of text type, but you want only 'text' to contain only alphabetic characters, no numeric characters, how would one create this rule and enforce it in SQLite SQL or does one have to use triggers. Another question I have is, has proper referential integrity been finally established and things like full joins and other joins. I am fairly new to SQLite and in researching things I would find things about older versions of SQLite and then hear rumors that they have been fixed in the newer version, but I am not sure. Palmer E-mail for the greater good. Join the i’m Initiative from Microsoft. _ E-mail for the greater good. Join the i’m Initiative from Microsoft. http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ GreaterGood ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3Atoi64() and input string "0"
Hi! Just wanted to say that the sqlite3Atoi64() function doesn't seem to work
properly when zNum = "0", because the while( zNum[0]=='0' ){ zNum++; } skips
it, leading to an empty string and i == 0. Then, the test "if( c!=0 || i==0 ||
i>19 )" always return 0 (false), meaning that the conversion did not succeed...
Is that the intended behaviour?
Bye,
Aladdin
SQLITE_PRIVATE int sqlite3Atoi64(const char *zNum, i64 *pNum){
i64 v = 0;
int neg;
int i, c;
while( isspace(*(u8*)zNum) ) zNum++;
if( *zNum=='-' ){
neg = 1;
zNum++;
}else if( *zNum=='+' ){
neg = 0;
zNum++;
}else{
neg = 0;
}
while( zNum[0]=='0' ){ zNum++; } /* Skip over leading zeros. Ticket #2454 */
for(i=0; (c=zNum[i])>='0' && c<='9'; i++){
v = v*10 + c - '0';
}
*pNum = neg ? -v : v;
if( c!=0 || i==0 || i>19 ){
/* zNum is empty or contains non-numeric text or is longer
** than 19 digits (thus guaranting that it is too large) */
return 0;
}else if( i
_
Retouchez, classez et partagez vos photos gratuitement avec le logiciel Galerie
de Photos !
http://www.windowslive.fr/galerie/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_mprintf() best practice
Peeking at the SQLite source code, I see different usage pattern or the
sqlite3_mprintf() function:
- sqlite3_mprintf("direct static string without %");
- sqlite3_mprintf("%s", zString);
What's the recommended usage and best practice for this function?
I think that using the sqlite3_mprintf(zString) function on an untrusted
string, could lead to a security problem (buffer overflow) in case zString
*could* contain some "%..." format strings, and the - normal - practice should
be:
- use sqlite3_mprintf("%s", zString); when the string could be provided by user
code (and may contain format strings)
- use sqlite3_mprintf("direct static string without %"); when we are absolutely
sure that the string cannot, in any situation, contain format strings.
Is that all or are there other considerations to take into account?
Thanks and have a nice day,
Aladdin
_
Caroline vient de mettre à jour son profil Messenger ! Connectez-vous !
http://login.live.com/login.srf?wa=wsignin1.0&rpsnv=10&ct=1198837564&rver=4.0.1534.0&wp=MBI&wreply=http:%2F%2Fhome.services.spaces.live.com%2F&lc=1036&id=73625
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing virtual tables
Nobody? Did I make myself clear or do you need more (or maybe less!) explanations? Thanks, Aladdin > From: [EMAIL PROTECTED] > To: sqlite-users@sqlite.org > Date: Sat, 17 May 2008 16:41:49 +0200 > Subject: [sqlite] Indexing virtual tables > > > Hi! Here is what I'm still trying to achieve: > - I have a custom file format having "records" and file offsets. > - Each record in that custom file format has the same number of fields, but > the records itself are variable length, that's why I need a file offset to > quickly locate a record. One other way (if you can wait for a very long > time...) is to walk sequentially the records list to get the desired record. > - I've implemented a working SQLite "virtual table" in order to be able to > read and query my custom file format through SQLite. > - Now, basically what I'd like would be to "CREATE INDEX" on a field of my > virtual table to take advantage of it in the xBestIndex callback. But the > documentation says that we cannot use "CREATE INDEX" on virtual tables. > > Let's say the data in the field "F1" of my virtual table "VFILE", and the > file offsets are the following: > F1 fileoffset > -- > a 10 > b 21 > z 34 > x 45 > a 51 > x 69 > z 73 > a 88 > x 94 > > I want to index the column F1, to be able to have a quick response to queries > like: > select * from VFILE where F1='x' > > At this point, I think I have only 3 possible strategies: > 1. Use SQLite tables to "fake" a standard index using SQLite tables > 2. Use internal SQLite B-Tree routines to implement my index > (sqlite3BtreeCreateTable and stuff) > 3. Implement my own B-Tree and sort algorithms to achieve this, externally to > SQLite > > Strategy 3 is precisely what I'm trying to avoid (too much work and testing > :-) ). > Strategy 2 is strongly discouraged by DRH. > > Then strategy 1 seems to be (like you've just said) the only way to go: > > a) Duplicate the data to be indexed (and the file offsets to use) > > create table INDEX1_SORT as select F1, fileoffset from VFILE order by F1, > fileoffset > > INDEX1_SORT: physical table > F1 fileoffset > -- > a 10 > a 51 > a 88 > b 21 > x 45 > x 69 > x 94 > z 34 > z 73 > > b) Create an index on that data > > OPTION 1: Use SQLite CREATE INDEX at this point. > b.1.1) create index on INDEX1_SORT(F1) > > OPTION 2: Fake index with custom tables > b.2.1) create table INDEX2_SUM as select F1, min(INDEX1_SORT.rowid) as > 'minrow', max(INDEX1_SORT.rowid) as 'maxrow' from INDEX1_SORT group by F1 > > INDEX2_SUM: physical table > F1 minrow maxrow > --- > a 1 3 > b 4 4 > x 5 7 > z 8 9 > > b.2.2) create index on INDEX_2_SUM(F1) > > > * > > Usage for option 2: > - Use INDEX2_SUM to fetch the requested value in the initial query (select * > from VFILE where F1='x') > - Get data from table INDEX1_SORT between rowid "minrow" (5) and "maxrow" (7) > - For each line, use the given file offset to locate the real data in the > custom file format file. > - Read 3 records at fileoffet = 45,69,94 and return them to SQLite. > > I really feel like all this is not very optimal. > What is the best strategy to achieve optimal speed and needed storage? > Am I missing a trivial point? > > Thank you for any help on that! > Aladdin > >> Date: Mon, 12 May 2008 15:37:22 -0700 >> From: [EMAIL PROTECTED] >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Indexing virtual tables >> >> I'm not quite clear on your question - why wouldn't you just create >> any indices you need within the virtual-table implementation itself? >> Sort of like how fts uses SQLite tables to implement data-storage for >> the full-text index. >> >> -scott >> >> >> On Mon, May 5, 2008 at 10:13 AM, Aladdin Lampé wrote: >>> >>> Just thinking again about indexing strategies on virtual tables, I'm >>> wondering why virtual tables could not be indexed using the "normal" SQLite >>> command "INDEX". Indeed, I just expected that the data inside the column of >>> the virtual table could be sequentially scanned (using the "xColumn" >>> callback), producing the same result as if it were a real table. Is that >>> way of seeing things flawed? >>> >>> Any hook allowing to use SQLite internal indexing techniques for virtual >>> tables? Maybe using direct b-tree manipulation (even if I know it's not >>> recommended)? I'm not very keen on developing my own from stratch. Dealing >>> with "big" tables that don't fit into memory does not seem so easy because >>> I'll have to use a temporary disk file... >>> >>> Some help would be greatly appreciated! >>> Aladdin >>> >>> _ >>> Faites vous de nouveaux amis grâce à l'annuaire des profils Messenger ! >>> http://home.services.spaces.live.com/search/?page=searchresults&ss=true&FormId=AdvPeopleSearch&form=SPXFRM&tp=3&sc=2&pg=0&Search.DisplayName=Nom+public&search.g
Re: [sqlite] Sqlite3
On 5/19/08, Hildemaro Carrasquel <[EMAIL PROTECTED]> wrote: > Hello.- > > I want to know, how many connections does Sqlite3 support? SQLite is not a server in a traditional sense. It is the client *and* the server. It is embedded in your application. In that sense, it can support as many connections as your application can support. Concurrent requests made to the same db are queued up and replied to as they happen. > > > -- > Ing. Hildemaro Carrasquel > Ingeniero de Proyectos > Cel.: 04164388917/04121832139 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_transfer_bindings obsolete?
>> Drat. It doesn't look like there's a way to see what's already been >> bound to a statement either, correct? See this thread for a previous disuccsion of the problem: http://www.mail-archive.com/sqlite-users@sqlite.org/msg28610.html Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite3
Hello.- I want to know, how many connections does Sqlite3 support? -- Ing. Hildemaro Carrasquel Ingeniero de Proyectos Cel.: 04164388917/04121832139 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
