[sqlite] typo in documentation
On the web page at http://sqlite.org/csv.html the following text appears: The CVS virtual table is not built into the SQLite amalgamation. The acronym CVS should be CSV. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite4 documentation error
Hi, I noticed an error in the SQLite4 documentation at http://sqlite.org/src4/doc/trunk/www/key_encoding.wiki in the section on numeric encoding. It says: "0x0d is also smaller than 0x0e, the initial byte of a text value" I believe that it should say: "0x23 is also smaller than 0x24, the initial byte of a text value" HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation clarification
Hi, On the page http://www.sqlite.org/lang_createtable.html The following text appears: * If the default value of a column is CURRENT_TIME, CURRENT_DATE or CURRENT_DATETIME, then the value used in the new row is a text representation of the current UTC date and/or time. For CURRENT_TIME, the format of the value is "HH:MM:SS". For CURRENT_DATE, "-MM-DD". The format for CURRENT_TIMESTAMP is "-MM-DD HH:MM:SS". The first sentence uses the name CURRENT_DATETIME (hooray), the fourth uses the name CURRENT_TIMESTAMP (booo) for what I believe are supposed to be the same thing. Which of these is the correct name, or are they equivalent? -- Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trademark issue? - (was Re: Announcing the Madis project)
On 10-03-09 8:47 PM, Darren Duncan wrote: > > (Incidentally, I *have* registered my trademark. But that is a non-issue > here.) > > Darren, Aren't you required to put the registered trademark symbol, ®, on each use of your trademarked name or logo? I didn't see it (or the ™symbol for an unregistered trademark for that matter) on your website, so I assumed you hadn't registered the trademark. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] trademark issue? - (was Re: Announcing the Madis project)
On 10-02-23 3:23 PM, Darren Duncan wrote: > Elefterios Stamatogiannakis wrote: > >> Madis is a extensible relational database system built upon the SQLite >> database and with extensions written in Python (via APSW SQLite >> wrapper). Its is developed at: >> >> http://madis.googlecode.com >> >> >> > I am concerned with your choice of project name, "Madis", because there may be > reasonable grounds for confusion between your project and my "Muldis" > projects, > which I have been releasing and promoting for about 3 years now (and for which > I've had domain names for about 6 years). > > > I also welcome any third party comments in regards to whether I have > reasonable > grounds to think there may be confusion between the 2 projects that could > affect > trademark matters. > > Hi Darren, I don't think there is any reasonable grounds to expect confusion between these two products. To me the names are only vaguely similar. Furthermore, if you have an issue with his use of "madis" being too similar to your products name, then you probably have an similar issue (with roles reversed) concerning Borland's (now Embarcadero) database middle ware product "midas" which was announced in April of 1997. Perhaps "multics" is another example of a product name that is quite similar to yours, and who's use pre-dates yours considerably. One of its claimed novelties was that "Multics implemented a single level store for data access" which could easily be confused with database functionality. I'm not a lawyer, but I don't think you would have much ground to stand on in trademark dispute, especially if you have not registered your trademark. Just my two cents. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] structure question
On 10-02-26 2:25 PM, Francisco Azevedo wrote: > Hi all, > > I want to create a "publish/undo system" for some tables but i don't > know what is the best approach to do it. > Imagine i have a table with columns id (auto-inc), data (text) then i > want to edit table data (eg: create 2 new rows now, delete one tomorrow, > update 3 rows tomorow too) and then decide if i want to preserve that > modification or reverse it to the state it was before start that > modifications. > > Hi Francisco, You should read this page in the wiki http://www.sqlite.org/cvstrac/wiki?p=UndoRedo. It explains how to do this sort of undo system using triggers. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG - Documentation
On the website page at http://www.sqlite.org/custombuild.html The following sentence appears: "This object is somewhat misnamed since it is really an interface to whole underlying operating system, just the filesystem." I think it should be changed to: "This object is somewhat misnamed since it is really an interface to whole underlying operating system, *NOT* just the filesystem." Or something similar. Maybe the NOT doesn't need that much emphasis, but it should be there. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG - Documentation
On the website page at http://www.sqlite.org/custombuild.html The following sentence appears: "Disability the mutexes as compile-time is a recommended optimization for applications were it makes sense." I think it should be changed to: "Disabling the mutexes at compile-time is a recommended optimization for applications were it makes sense." HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Firefox SQLite Manager extension troubles.
Ted Rolle wrote: > > I've done as you said many times, but SQLite Manager still looks for > the old database, reports that it's not available. Ted, Try turning off the option to open the last used database. In SQLite Manager Menu -> Tools -> Options then select Main tab and uncheck Open the Last Used Database. That may clear the saved database name. After that you can try re-enabling the option after opening the new database file. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query by Day
Rick Ratchford wrote:
> So what I need to do then is to make the return of strftime of type INT.
>
> Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is there
> another way?
>
>
Rick,
You could use a cast (with correct syntax) as you have suggested
CAST(strftime('%d', Date) AS INTEGER)
Or you could simply provide the day you are checking for as a string. To
do this, surround the number with single quotes to turn it into a string
literal.
SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
IsSwingBtm1 as Btms " & _
"FROM TmpTable WHERE Day = '11'"
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re bind Statement
Indiff3rence wrote: > My question is: why is necessary to reset the statement before I can rebind > it? > It should work only if I clear the old bind, no? > > You need to reset the statement so that it can be executed again from the beginning (see section 3.0 of http://www.sqlite.org/cintro.html for more details). You do not need to clear the old bindings if you are going to bind new values for each of the variables before you execute the command again. The clear binding call basically binds a NULL to each variable, which you are overwriting anyway. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column headers of result
BareFeet wrote: > > >>> How can I get just the column headers without all the result rows? >>> >> Turn headers on, then perform a search which gives no results. >> > > Unfortunately, the sqlite3 command line tool does not show the headers > when there are no result rows. > > You can change that behavior with the pragma empty_result_callbacks (see http://www.sqlite.org/pragma.html). SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a,b); sqlite> pragma empty_result_callbacks=1; sqlite> .header on sqlite> select * from t; a|b HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How can I specify that a column is equal to another?
Yuzem wrote: > Is there any way to specify that movies.id is equal to user.id so I can use > just id in my query? > Thanks in advance! > Not with a left join, but with an inner join you can use the USING clause or a NATURAL join. See http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join for more details. Note that SQLite does not report an error when you use a table name qualifier for a column named in a using clause or paired off in a natural join as it should. This may cause compatibility issues with other database programs. Using your example, you could use either of the following queries. select title,my_rating from movies join user using(id) where id = 'tt0426459'; select title,my_rating from movies natural join user where id = 'tt0426459'; According to the SQL standard, these should all produce an error since the qualified column doesn't exist in the join's result table. select title,my_rating from movies join user using(id) where movies.id = 'tt0426459'; select title,my_rating from movies join user using(id) where user.id = 'tt0426459'; select title,my_rating from movies natural join user where movies.id = 'tt0426459'; select title,my_rating from movies natural join user where user.id = 'tt0426459'; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and MinGWSys
ArbolOne wrote: > I have downloaded the latest version of SQLite as well as the make file > in the ticket #931 <http://www.sqlite.org/cvstrac/tktview?tn=931> > However, after typing 'make' I get a message that saying ' No rule to > make target 'src/sqlite.h.in', how do I solve this problem? > > Read the recommendations at http://www.sqlite.org/download.html and don't use a 4 year old makefile for code that was released last week. Download this http://www.sqlite.org/sqlite-amalgamation-3_6_16.zip and extract the files. You will have a single c file that you can add to your sqlite based project, or compile as a static library and link to your project. You can download a precompiled dll library or a precompiled copy of the command line sqlite3 program if you need those. If you are trying to build the command line program yourself, you can get the source file shell.c from http://www.sqlite.org/sqlite-source-3_6_16.zip. Compile and link shell.c and sqlite3.c from the amalgamation together like this (untested): gcc sqlite3.c shell.c -o sqlite3.exe HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer primary key autoincrement & sqlite_sequence
Oliver Peters wrote: > > I want the "normal" user only identify himself by putting his id into > the field identity and afterwards let the system decide in what field to > put his id (INSERT = creator, UPDATE = editor). Doing this for every > record I can show who created it and who was the last editor. > Are the users entering the SQL directly? If not, it still seems to me that your application can take the value from the Identity field in your UI and assign it to the creator field when inserting a new record. The users are seeing a different UI for insertions and update aren't they? Your application knows whether it is doing an insert or an update, so it can execute the appropriate SQL Statement. > This task could be accomplished by a combination of INSERT and an AFTER > INSERT Trigger > > /* Code */ > > INSERT INTO a(code,name,identity) > VALUES(new."code",new."name",new."identity"); > > CREATE TRIGGER IF NOT EXISTS test > AFTER INSERT ON "a" > BEGIN > UPDATE a SET creator = identity, identity = NULL; -- NULL to empty it for > other possible editors > END; > > Based on this I would guess that your update trigger does something like this. BEGIN UPDATE a SET editor = identity, identity = NULL; -- NULL to empty it for other possible editors END; What is the purpose of the identity field if it is always NULL after an insert or update? > But because I log every insert/update/delete into a separate table too the > combination of INSERT and AFTER INSERT trigger would lead to 2 log records > (1. INSERT, 2. UPDATE) - and that's what I'd like to avoid. > > If you insist on your current approach, you could drop the insert trigger and use a field specific update trigger to log the updates done by the insert and update triggers. If the log entries are different for the two types of changes, then you could use two different triggers, or use a select case... conditional to build the appropriate log entry. CREATE TRIGGER IF NOT EXISTS log_entry AFTER UPDATE OF creator, editor ON "a" BEGIN --Insert log record for newly inserted or updated record INSERT INTO log ... END; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] integer primary key autoincrement & sqlite_sequence
Oliver Peters wrote:
> sorry: my code wasn't completely what I wanted so here again:
>
> CREATE TRIGGER IF NOT EXISTS test
> BEFORE INSERT ON "a"
> BEGIN
> INSERT INTO a(code,name,creator)
> VALUES(new."code",new."name",new."identity");
> SELECT RAISE(IGNORE);
> END;
>
> the difference is that I put new."identity" into the field "creator". This is
> the way I chose to differ between creation and altering/updating of a record
> (In case of an UPDATE I've another trigger that shoots new."identity" into
> another field. The result is a kind of record-logging the "normal" user
> should see.
>
> On the other hand I've a complete logging that writes every
> inserted/updated/deleted record in a special table. If I use a trigger in
> your suggested way
>
> CREATE TRIGGER IF NOT EXISTS test
> AFTER INSERT ON "a"
> BEGIN
> INSERT INTO a(code,name,identity)
> VALUES(new."code",new."name",new."identity");
> UPDATE a SET creator = identity, identity = NULL;
> END;
>
> I'd get 2 entries per record in the log - I'd like to avoid this. Are there
> other possibilities? Maybe I'm only a little balky? ;-)
>
Oliver,
I didn't follow your description of your problem.
If you are going to replace the value of the identity field with NULL in
your "after insert" trigger, why bother inserting it at all? Why not
simply insert the same values into the desired columns?
Instead of:
INSERT INTO a(code,name,identity) VALUES('V','abc',1);
why don't you do this:
INSERT INTO a(code,name,creator) VALUES('V','abc',1);
I think you may need to expand on your description of "shooting
new.identity into another field" to clarify what you are trying to
accomplish.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message from RAISE just plain text ?
Simon Slavin wrote: > On 15 Jun 2009, at 2:37am, Simon Slavin wrote: > > >> The examples for the error text I've found are all simple text >> strings, for instance >> >> RAISE(ROLLBACK, 'delete on table "foo" violates foreign key constraint >> "fk_foo_id"') >> >> What I want to do is more like >> >> RAISE(ROLLBACK, 'Attempt to delete author '||old.name||' who has >> books.') >> >> but that doesn't work. >> > > No responses to this ? Can someone confirm for me that error messages > from 'RAISE' have to be just pre-assigned text strings and can't be > expressions ? > > Simon. > Simon, From the parser you can see the allowed raise function syntax. expr ::= RAISE LP raisetype COMMA nm RP Where nm is the error message. The nm nonterminal can be replaced by the following: nm ::= id nm ::= STRING nm ::= JOIN_KW and the id can bereplaced by; id ::= ID id ::= INDEXED So you can see that the parser is basically designed to only accept a STRING at that position, not an expression which is what would be required to allow concatenation and other function calls. I assume that the other possible values (join keyword etc) would be caught and reported as errors. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database inserts gradually slowing down
Jens Páll Hafsteinsson wrote: > Here's the code I'm using to run the test (it includes the schema). I've been > running this code for the last few days and am a bit baffled about my recent > results, since I'm not seeing the gradual slowing anymore. This happened > after I changed to use version 3.6.15 of sqlite and even if I change back to > 3.6.14 it still behaves very consistently, that is, doing the insert and > delete in constant time. > > Jens, I get similar constant time results using equivalent SQL scripts and the command line SQLite program. I noticed tat you are using the sqlite3_exec() API in your tests. If you are concerned about performance, as you seem to be based on the testing you are doing, you should look at switching to the preferred prepare/bind/step API functions (see http://www.sqlite.org/cintro.html and http://www.sqlite.org/cvstrac/wiki?p=SimpleCode for info and samples). Your test code is probably spending as much time compiling the same insert statement over and over again as it is on doing the actual inserts into the database. Using the alternate API you would prepare the statement once, then bind the values to be used for each insert, execute the insert, then reset the statement to be run again for the next insert. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [noob] merge statement equivalent?
James Gregurich wrote: > on that update statement, is the SQL optimizer smart enough to not > rerun that select statement for each column in the update's set > clause? Is it going to run a single select statement to get , > , etc. or is it going to run one for each column in the > update statement? > > James, No, I don't believe the optimizer is that smart. SQLite will execute multiple queries. If you are concerned that the matches table is large you could add an index on the the row1 column of the matches table to speed up the row2 lookups. The lookups in table2 using the rowid should be very fast, and once the page with the required record has been read into the cache the subsequent value lookup queries should execute very quickly as well. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database inserts gradually slowing down
Jens Páll Hafsteinsson wrote: > Closing and opening again did not speed up steps 1-4, it actually slowed > things down even more. The curve from the beginning is a bit similar to a > slightly flattened log curve. When I closed the database and started the test > again, a similar curve appeared again, but now starting from where the first > run left off. > > I've been running the same 3.6.15 since this afternoon (the previous test was > using 3.6.14) and it seems to flatten out fairly quickly but it is > significantly slower (2.3 to 1.3 times slower, depending on where you measure > it using the data I have). I'm not that worried about that for the time > being; I'm just hoping it will stay flat. > > JP > > > > On 16 Jun 2009, at 1:06pm, Jens Páll Hafsteinsson wrote: > > >> 1. start a transaction >> >> 2. insert 1000 records >> >> 3. commit >> >> 4. repeat steps 1-3 100 times >> >> 5. delete everything from the table >> >> 6. Start again at step 1 >> > > Jens, Can you post the schema for your table and the index (i.e. the actual schema you are using for the test), and also the code that shows how you are assigning values to the columns in your table when you do the inserts? I suspect that you may be using a autoincrement id field and then running into the extra work (both CPU load and increased disk space) needed to handle the variable sized integer storage method used by SQLite. This would lead to the type of logarithmic growth you are seeing. The first few iterations used short single byte integer values, the next bunch use 2 byte integer values, etc. The autoincrement field would cause SQLite to continue at the same speed after restarting the application as you have described, since the next field values used would continue from where it left off at the end of the previous run. I would have expected the time to stabilize on 3 byte values fairly qucikly, and then only change again when switching to values that required 4 bytes. This may be a part of the answer even if it is not the complete answer. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Shortest time interval? [Was: Re: repeating events?]
Allen Fowler wrote:
>
>
>
>> You could be storing event duration, not stop time. Or perhaps store
>> both.
>>
>>
>
> Here is what I have so far:
>
> sqlite> create table events (id INTEGER PRIMARY KEY AUTOINCREMENT, name,
> kind, start, end);
>
> # Now add some events for "tomorrow"
> sqlite>
> insert into events values (null, 'tom', 'hour', datetime('now', '+1
> day','start of day', '+11 hours'), datetime('now', '+1 day','start of
> day', '+12 hours'));
> sqlite> insert into events values (null,
> 'tom', 'hour', datetime('now', '+1 day','start of day', '+9 hours'),
> datetime('now', '+1 day','start of day', '+10 hours'));
> sqlite>
> insert into events values (null, 'joe', 'hour', datetime('now', '+1
> day','start of day', '+9 hours'), datetime('now', '+1 day','start of
> day', '+10 hours'));
>
> # Now add an all-day event for tomorrow. (It overlaps a couple of above
> events.)
> sqlite> insert into events values (null,
> 'tom', 'day', datetime('now', '+1 day','start of day'), datetime('now',
> '+1 day','start of day', '+1 day'));
>
> # Show all events and duration:
> sqlite> select *, (strftime('%s', end) - strftime('%s', start)) as length
> from events;
> idname kind start
> end length
>
> --
> 1 tom hour 2009-06-13
> 11:00:00 2009-06-13 12:00:00 3600
> 2 tom hour 2009-06-13
> 09:00:00 2009-06-13 10:00:00 3600
> 3 joe hour 2009-06-13
> 09:00:00 2009-06-13 10:00:00 3600
> 4 tom day 2009-06-13
> 00:00:00 2009-06-14 00:00:00 86400
>
>
> #
> # And now, I want to get a result table with one row per user showing the
> "shortest active at 9:30 AM event" for each user.
>
> sqlite>
> select *, min((strftime('%s', end) - strftime('%s', start))) as length
> from
>...> events where
>...> start < datetime('now', '+1 day','start of day',
> '+9 hours','+30 minutes')
>...> and end > datetime('now', '+1 day','start
> of day', '+9 hours','+30 minutes')
>...> group by name;
>
> idname kind start
> end length
>
> --
> 3 joe hour 2009-06-13
> 09:00:00 2009-06-13 10:00:00 3600
> 4 tom day 2009-06-13
> 00:00:00 2009-06-14 00:00:00 3600
> sqlite>
>
> However this result returned is very wrong.The length col is correct but
> the other cols for "tom" are wrong. (It should be "2 | tom | hour |
> 2009-06-13 09:00:00 | 2009-06-13 10:00:00 | 3600" )
>
> What am I missing here? Am I doing the query wrong?
>
>
Allen,
Yes, you are doing something wrong. It is probably the most common
mistake made when using grouping in SQL.
When grouping, keep in mind that all columns that appear in your SELECT
column list, that are not aggregated (used along with one of the SQL
aggregate functions), have to appear in the GROUP BY clause too.
The only valid columns in your output (i.e. with a select *, min()) is
the column named in the group by clause (i.e. the name) and the
aggregate value (i.e. the min()). You have determined the length of the
minimum event for each name that meets your other conditions and nothing
else. You do not know its id, what kind it is, or when it starts or
ends. The values shown for those other fields are randomly selected from
the set of rows in the matching group.
SQLite and some other database programs don't complain when you to break
this rule because it is sometimes useful to get a randomly selected
value for a column in the group. Usually it just leads to the confusion
you are seeing.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "not an error" error inserting data trough a view on fts3 table
Jay A. Kreibich wrote: > > you can create a trigger that fires on an attempt to > DELETE, INSERT, or UPDATE a view and do what you need > in the body of the trigger. > > Jay, this is exactly what the OP did. He has two tables and a view, and inserts into the two tables from within an "instead of" trigger on the view. I suspect the error is due to the fact that one of the tables is an FTS3 virtual table rather than a real table. I haven't used the FTS3 module much so I can't offer any further insight, but I see nothing wrong with the code as posted. Perhaps the OP should file a bug report using this example. If there is a bug it will probably be fixed in short order. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Db design question (so. like a tree)
Jay A. Kreibich wrote: > > You can't with just SQL. This is the whole issue with adjacency lists. > Most basic operations, like finding ancestor lists, counting tree depths, > finding a list of all children or descendants, etc., require some > kind of loop. > > I have posted about the "materialized path" approach to trees in SQL which I have used very successfully. See http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html for the original post (also see http://www.mail-archive.com/sqlite-users@sqlite.org/msg19507.html for some additional ideas about formatting reports). This is a variation of the adjacency list technique that also stores a path through the tree to each node (hence the name "materialized path" as opposed to a virtual path stored as a sequence of links in the adjacency list). This path is maintained automatically by triggers. Having the paths available turns many common tree queries into pattern matches against the path using like conditions on standard SQL queries. Unless your tees are very large I find this method to be a very good alternative to simple adjacency lists or nested sets. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
robinsmathew wrote: > hey thanx for the reply... u leave the things happening inside.. wat i jus > wanna do is i wanna insert a new row to a table > the table will be like this > stock_id PKproduct_id FK quantitystock_date > 1 10001028-05-2009 > 10001 1001 527-05-2009 > > and wen i insert a new row with values NULL, 1000, 15,30-05-2009 > > i dont want want it as a new recorde i jus want to update the first row coz > its also having the same product id i jus want set the quantity = 10+15 and > the date new date that is 30-05-2009 > and suppose if i insert row with different product_id it should be inserted > as it is.. > > Martin Engelschalk wrote: > >> Hi, >> >> what language is this? it certainly is not SQL or a "query". >> When I saw this I though "What language is this? It's certainly not English." :-) It seems to me that robinsmathew should investigate the shift key, and the spell check functions in his email client. Is it just me, or do others find jibberish like "wat, jus, wanna, i, wen, etc..." to be very distracting and not the least bit "cool"? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Types for strings, non-expert question
Roger Binns wrote: > I assume you are talking about a major release (ie SQLite v4 not 3.7). > Yes, that's what I'm talking about. I would expect v3 and v4 to be maintained in parallel until most users have updated their code to work with v4. Of course users could continue to use v3 indefinitely, but as some point it would go into feature freeze and all new development would take place in the v4 branch. That wouldn't mean that v3 suddenly stops working. This is basically what happen with the transition from v2 to v3. In fact there are still some users happily using v2. I suspect the transition would actually be quite quick, though existing versions of v3 would continue to be used for many years. > > My list of good changes to make are:... > Those are exactly the kinds of changes that I think should be collected and published so they can be vetted by users and the developers before the changes are implemented. I think it could help to avoid future errors like the sqlite_column_text return type issue. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Types for strings, non-expert question
D. Richard Hipp wrote: > On May 27, 2009, at 9:36 AM, Maurí cio wrote: > > >> Hi, >> >> I see that in most functions strings are typed as >> >> char * >> >> while in 'sqlite_column_text' and 'sqlite_value_text' >> they are typed as >> >> unsigned char * >> >> > > That was just bad design on my part. I don't know what I was thinking > when I did that. It has been a pain for everyone to deal with ever > since. But we cannot change it now without breaking compatibility. > > On the other hand, as long as you use the characters as characters and > not as integers, does it really matter if they are signed or > unsigned? Just cast the result to whatever you need and it will work. > > > Richard, Do you have a list of such changes that should be implemented in the next breaking release of SQLite? I'm thinking of things like renaming the _v2 API functions (in particular prepare_v2) to drop the suffix, and changing the name of the CURRENT_TIMESTAMP default value to CURRENT_DATETIME so that CURRENT_TIMESTAMP can be used for an julian day number, etc. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about insert into select
Wenton Thomas wrote: > What's the execution sequence about " insert ino A select from B "? > I means,which is correct prescription in the following: > (1) select all rows from B at first, then insert all the result into > table A; > (2) select a row from B ,then insert the row into table A immediately, repeat > the precess. > > When in doubt ask sqlite. :-) SQLite version 3.6.14 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .explain on sqlite> create table t1(a,b); sqlite> create table t2(c,d); sqlite> explain insert into t2 select * from t1; addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 13000 2 Noop 0 0 000 3 OpenWrite 1 3 0 2 00 4 OpenRead 0 2 0 2 00 5 Rewind 0 10000 6 NewRowid 1 2 000 7 RowData0 1 000 8 Insert 1 1 2 t2 0b 9 Next 0 6 000 10Close 0 0 000 11Close 1 0 000 12Halt 0 0 000 13Transaction0 1 000 14VerifyCookie 0 2 000 15TableLock 0 3 1 t2 00 16TableLock 0 2 0 t1 00 17Goto 0 2 000 sqlite> From this it's easy (relatively) to see that inserts each row as it find them while executing the select. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite as a FIFO buffer?
Allen Fowler wrote: > I have several CGI and cron scripts and that I would like coordinate via a > "First In > / First Out" style buffer.That is, some processes are adding work > units, and some take the oldest and start work on them. > > Could SQLite be used for this? > > It would seem very complex to use SQL for just a FIFO, but then again, SQLite > would take acre of all ACID / concurrency issues. > > Has this been done before? > > You can use simple triggers to manage a fifo in a table. See http://www.mail-archive.com/sqlite-users@sqlite.org/msg12121.html for an example. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use the result of a query, as a variable name in another query ?
Stef Mientki wrote: > to test complex queries, I want to use the command line utility ( or an > equivalent that remembers what I type). > The sqlite command line program sqlite3.c will remember what you type if you build your own executable from the source. The command line will use the readline or editline libraries if they are available when building the executable. These libraries provide full command line history functionality. These libraries are not used in the precompiled executables that you can download because of licensing issues. SQLite's public domain license is not compatible with the GPL and BSD license used by these libraries, so they can't be linked into the executables that are distributed by Richard. I always build my own command line sqlite from source to ensure I get the history functions provided by these very useful libraries. > Now I've a table which contains the names of a set of other tables. > In Python I can easily create the SQL string (containing the field name) > and commit it to the database. > > Is there a way realize this in the command line ? > So I need to get the result of a query into a variable and then use that > variable in a new query. > If I understand your question (which seems a little vague to me), there is no way to build and execute SQL queries that include the result of other queries using the command line. You can use one query to build the text of another query, but there is no way to execute the resulting query using the command line. The best you could do would be to pass the SQL query text result from one invocation of the command line as input to a second invocation of the command line where it would be executed. In effect you are using your command shell to store the result of the first query and pass it back into a second sqlite command. You would be replacing Python programming with bash shell scripting. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLl question
Evan Burkitt wrote: > This isn't a Sqlite question per se, but I know there are some SQL gurus > here who might have some insight into this problem. I apologize for > being off-topic; I can be shameless when I need help. :)> > > I have three tables, N, P and E. N contains the fields id and name. The > other two each contain the fields id, type and addr. P holds phone > numbers, E email addresses. In P, the type field is always 'phone'; in > the P it is always 'email'. They are all related on id. > > I want to build a single query that will return a result set consisting > of N.name, P/E.type and P/E.addr. That is, it contains the like-named > fields of both P and E. For example: > > -name -type--- -addr- > "John Smith", "phone", "123-555-1212" > "John Smith", "email", "john.sm...@domain.com" > "Bill Jones", "phone", "123-555-1213" > "Jane Johnson", "email", "j...@anotherdomain.com" > > and so forth. The order of the names and types is not important. > > Is this possible? > > This (untested) SQL should do what you want. select N.name as name, C.type as type, C.addr as addr from N join (select id, type, addr from P union select id, type, addr from E) as C on C.id = N.id order by N.name, C.type; The union combines all the data from your P and E tables so they can be joined to the N table using the id filed. The result is sorted by the order by clause which you could drop if you really don't care about the order of the results. Your database would be simpler if you simply combined these two tables into a single table in the first place. It would eliminate the need to combine them for this type of query. The tables already have a type field to distinguish the email adresses from the phone numbers, so there is no need to put them in separate tables. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepared statements must be generated inside your transaction
Joanne Pham wrote: > I have read one of the performance document and it stated that "prepared > statements must be generated inside transaction". Is that correct. > > So I have to do this: > begin transaction > prepared statement >.. > end transaction. > > I though the prepare statement must be outside of the transaction. Can any > one confirm this? > I believe that used to be the case with early versions of sqlite 3. It is no longer true. You can see that sqlite 3.6.14 generates exactly the same opcodes when it prepares a statement either inside or outside a transaction using the explain command. SQLite version 3.6.14 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a,b); sqlite> .explain on sqlite> explain insert into t values(1,2); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 10000 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 000 4 Integer1 3 000 5 Integer2 4 000 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 000 9 Halt 0 0 000 10Transaction0 1 000 11VerifyCookie 0 1 000 12TableLock 0 2 1 t 00 13Goto 0 2 000 sqlite> begin; sqlite> explain insert into t values(1,2); addr opcode p1p2p3p4 p5 comment - - -- - 0 Trace 0 0 000 1 Goto 0 10000 2 OpenWrite 0 2 0 2 00 3 NewRowid 0 2 000 4 Integer1 3 000 5 Integer2 4 000 6 MakeRecord 3 2 5 bb 00 7 Insert 0 5 2 t 1b 8 Close 0 0 000 9 Halt 0 0 000 10Transaction0 1 000 11VerifyCookie 0 1 000 12TableLock 0 2 1 t 00 13Goto 0 2 000 sqlite> The older versions of sqlite generated different code in these two cases. If a statement was to be executed inside a transaction it was necessary to compile (i.e. prepare) it inside a transaction (thought not necessarily the same transaction that it was to be executed in) in order for sqlite to generate the correct code. If my memory serves me correctly, I seem to recall it added some kind of a COMMIT opcode to the end of a statement when it was compiled outside a transaction. This opcode would incorrectly close the transaction when executed inside a transaction. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] listing registered SQL functions (aggregate or scalar)
ddevienne wrote: > I can't find a way to list registered functions (the built-in ones and the > ones added programmatically) via an API call nor via a built-in table like > there is for tables in sqlite_master, which I could query. > > How does one get this list? Or asked differently, how would a user know > which SQL functions she can use unless there's a way to list them in SQL? > > We have a UI which allows to aggregate rows by selecting a column and an > aggregate function, but we must hard code the list of aggregate functions > instead of introspecting the connection for all available aggregate > functions. > > In general, I'd also like to be able to tell the cardinality of the function > as passed as nArg to sqlite3_create_function. Is that also possible? > > Thanks, --DD > No, I don't think there is any way to do that for functions. You can get a list of registered collations using the "pragma collation_list", but there is no equivalent for functions. SQLite seems to assume that only the application that creates a function will ever use the function, so there is no need for this information. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating unique data takes many hours, help
mrobi...@cs.fiu.edu wrote: > this is how I create the db, table and index. > > > rc = sqlite3_exec(db, "CREATE TABLE probes (probe STRING unique);", > > rc = sqlite3_exec(db, "CREATE INDEX probe on probes (probe); > ", NULL, > In addition to what the others have said you should also see a substantial speedup, and reduction in your database size, if you eliminate the redundant probe index. The unique constraint on the probe column in your table causes sqlite to automatically create an index nearly identical to the one you are adding manually. It uses that index to efficiently enforce the uniqueness constraint. As it is, your database contains one table and two indexes, each of which contains a copy of every probe string your table (i.e. three times several million strings). If you know your data doesn't contain any duplicate strings, the fastest approach would be to remove the unique constraint from the table. Then add all your data to the table (inside one or more transactions), and finally create a unique index on the strings after all the inserts. CREATE TABLE probes (probe STRING); BEGIN; INSERT ... INSERT ... COMMIT; CREATE UNIQUE INDEX probe on probes (probe); Of course this won't work if you are relying on the unique constraint to eliminate duplicate strings in your data. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] gentle intro to including sqlite in another program
P Kishor wrote: > so, I have read the tutes on the website, but just wanted to confirm... > > I want to take baby steps to including sqlite's capabilities in my > modeling program. Do I just include sqlite3.h and compile my program > and magic will happen, or do I need to do something else? > There will be no magic unless you also link a previously compiled sqlite library, or also include the sqlite3.c almagamation source code file in your project. > Using Xcode. The model itself has about 30 or 40 different .c files > and about a dozen or so .h files. Just add the sqlite3.c file to your project and include sqlite3.h in your source files that call sqlite functions. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Step Query
vinod1 wrote:
> I am new to sqlite and C.
>
> I have not been able to write a code which would read row by row using
> sqlite3_step.
>
> Could anybody guide me please.
>
>
Hi,
This code is equivalent to the very old callback style code shown at
http://www.sqlite.org/quickstart.html.
It should provide the same results using the newer prepare/step/finalize
set of calls that are discussed at http://www.sqlite.org/cintro.html.
Hopefully it provides a complete, if somewhat basic, intro to the use of
the preferred C API functions.
#include
#include
int main(int argc, const char *argv[]){
sqlite3 *db;
sqlite3_stmt *stmt;
int rc = 0;
int col, cols;
if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
}else{
// open the database file
rc = sqlite3_open(argv[1], );
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
}else{
// prepare the SQL statement from the command line
rc = sqlite3_prepare_v2(db, argv[2], -1, , 0);
if( rc ){
fprintf(stderr, "SQL error: %d : %s\n", rc, sqlite3_errmsg(db));
}else{
cols = sqlite3_column_count(stmt);
// execute the statement
do{
rc = sqlite3_step(stmt);
switch( rc ){
case SQLITE_DONE:
break;
case SQLITE_ROW:
// print results for this row
for( col=0; col<cols; col++){
const char *txt = (const char*)sqlite3_column_text(stmt,
col);
printf("%s = %s\n", sqlite3_column_name(stmt, col), txt
? txt : "NULL");
}
break;
default:
fprintf(stderr, "Error: %d : %s\n", rc, sqlite3_errmsg(db));
break;
}
}while( rc==SQLITE_ROW );
// finalize the statement to release resources
sqlite3_finalize(stmt);
}
// close the database file
sqlite3_close(db);
}
}
return rc!=SQLITE_DONE;
}
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to get the previous row before rows matching a where clause...
sorka wrote: > I have a table of events that have a title, start time, and end time. > > The start time is guaranteed unique, so I've made it my primary integer key. > > I need all events that overlap the a window of time between say windowstart > and windowend. Currently, the statement > > SELECT title FROM event WHERE startTime < windowEnd AND endTime > > windowStart. > > I've indexed the end time and the query is pretty fast, but it could be a > lot faster if I only had to use the integer primary key. > > If instead I do > > SELECT title from event WHERE startTime > windowStart AND startTime < > windowEnd > > this will get me almost the same thing except that it will be missing the > first event that overlaps the windowStart because it's startTime is at or > before startTime. > > In this case, if I can get exactly the previous row added to what is > returned in the results above, I'll have exactly what I need. > > So the question is, when a WHERE clause returns a set of rows, is there a > way to also get the row at the ROWID that comes just before the row that is > returned from above with the lowest ROWID? > > Another way of putting it, if I take the lowest ROWID that is returned in my > second example and get the next lowest ROW, the one that is less than the > lowest ROWID I got but closest to it .i.e, the one right before it, then it > would be complete. > > > This should give you the result you want, all your current results and the row with the immediately prior starttime. select title from event where starttime > windowstart and starttime < windowend union select title from event where starttime = (select max(starttime) from event where starttime <= windowstart ); This is an alternate version that should perform better in case the optimizer doesn't optimize the max() function. select title from event where starttime > windowstart and starttime < windowend union select title from event where starttime = (select starttime from event where starttime <= windowstart order by starttime desc limit 1); HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Vacuum" command is failing with "SQL Error:Database or disk is full"
manohar s wrote: > Yes, the solution you suggested is working fine. But can't we change this > through SQLite? > > You can set the location SQLite will use for temporary files using pragma commands. See pragma_temp_store and pragma_temp_store_directory at http://www.sqlite.org/pragma.html#modify. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] starting INTEGER PRIMARY KEY at 0
P Kishor wrote: > > compatibility. And, as 'they' say, 0 is a perfectly fine number. Why > let it go waste. > > Real people always start counting from 1. Only programmers (and the occasional hardware engineer) start counting from 0. We see it so often it starts to seem normal, but it really is strange to the vast majority of the worlds population. :-) Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table metadata
P Kishor wrote: > On Sun, Mar 15, 2009 at 11:56 AM, P Kishor <punk.k...@gmail.com> wrote: > >> I am designing a database for carbon modeling. Many of the parameters >> to be stored in the db have very long names... it is kinda >> inconvenient to have column names such as >> 'new_live_wood_Carbon_to_new_total_wood_Carbon', but I hate column >> names such as 'nlivwdc2ntotwdc'. I may as well just call that column >> 'a' and then have a lookup table which describes 'a' => 'Ratio of new >> live wood Carbon to new total wood Carbon' along with possibly other >> descriptors (some columns are a flag, that is, 0 or 1, while others >> are integers or fractions, and so on). >> >> Any suggestions on how to embed this metadata in the table? Is the >> following the best way -- >> >> CREATE TABLE foo ( >> a REAL, -- Ratio of new live wood Carbon to new total wood Carbon >> b INTEGER -- (minimum interval between disturbances) >> ); >> >> And, how do I store metadata about the table itself? The following >> doesn't stick in the schema -- >> >> -- The table 'foo' is blah blah >> CREATE TABLE foo (); >> >> > > > The following seems to work > > CREATE TABLE foo ( > --- > -- the table foo is about blah blah > --- > a, -- blah > b -- blah > ); > > The other question about column metadata is still looking for a suggestion. > > > > > One way to handle columns with restricted data type is to use a "domain table", basically a table that stores the allowed values for a column in another table. The values stored in the first table are the keys to rows in the domain table and referential integrity checks can be used to ensure only legal values are stored. The domain table can also hold other "meta" data about the column, such as a text description of the meaning of the value. Simple cases such as 0 or 1 can be handled with a check constraint on the table column which may be simpler to read. create table t ( id integer primary key, flag integer check (flag = 0 or flag = 1), size integer references sizes(id), data text ); create table sizes( id integer primary key, size text ); insert into sizes values (1, 'small'); insert into sizes values (2, 'medium'); insert into sizes values (3, 'large'); insert into t values (null, 1, (select id from sizes where size = 'medium')); If you create referential integrity triggers on the database, those triggers will prevent inserting illegal values into your table. You can then display the restricted data values by joining the main table with the domain table(s), possibly using a view. create view tv as select id, flag, s.size as size, data from t join sizes as s on t.size=s.id; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Proposal for SQLite and non pure ASCII letters
Roger Binns wrote: > Jean-Christophe Deschamps wrote: > >> I'd like to have the group opinion about a feature I would find utterly >> useful in _standard_ SQLite. >> > > You are aware that "standard" SQLite is used in devices with a few > kilobytes of memory through workstations and servers with gigabytes of it! > > Whether he is aware of that or not is largely irrelevant, though I suspect he is aware of this fact. He is asking for support for a mechanism that will provide most, if not all, the benefits of the ICU extension using much less memory and far fewer CPU cycles. This would benefit the users of small devices more than those using workstations, but it could be beneficial to all. It would be almost universally beneficial if it could be omitted using a compiler define. Then even the small additional overhead of his proposal wouldn't impact those users who have no need for anything beyond ASCII. > As far as I can tell you want some extra "standard" collation sequences > and propose shortcuts that will get them mostly right. And you want > someone else to write the code! > No, he is asking for standard support for "user defined" collating sequences. And, yes, he wants some else to write the code since it (like many other facilities) is not nearly as useful if it is not included in the standard SQLite released by Richard. He has even offered to pay to have it developed. > SQLite makes it very easy to have extensions and to register them. For > example see http://sqlite.org/c3ref/auto_extension.html > > Generally the best approach would be to produce the code as an > extension, document and test it well and then add to the contributions > page at http://sqlite.org/contrib - once enough developers have used it > and vouched for its utility then it would be far easier to lobby for > incorporation into the "standard" SQLite. > The problem with extensions is precisely that they are not universally available. If my application uses an extension to provide collation sequences, then I can not safely use any of the many GUI database browsers to manipulate the data since the GUI browser won't have access to the extension functions. > For you to convince me of the utility of the code, you'd need to list > which locales it gets right and which it gets wrong. Software can seem > pretty dumb to users almost getting some things right. > > His proposal doesn't rely on locales. It uses "user defined" strings to define a collating sequence. It will be right where you can define a collating sequence using a string, and a suitable string is defined. He has already said that it won't be suitable for multiple byte characters or many other languages. For those cases where it is not suitable, a user could continue to use the ICU extension just as they can now. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE : Constraint question
REPKA_Maxime_NeufBox wrote:
> ->> Why is it possible to change data not defined in the constraint :
> Exemple : enter TEXT if the column is INTERGER ??
> enter 25 caracters if column is declared VARCHAR(15) ??
> I thought i will get an error return
> See exemple below :
>
>
As Martin has already pointed out, this is expected behavior due to
SQLite's more flexible manifest data typing extensions to SQL.
However, you can explicitly add the equivalent constraints to your table
definitions if you really want them. For example:
create table t (
a varchar(15) check (length(a) <= 15),
b integer check (typeof(b) = 'integer')
);
insert into t values('one', 1);
insert into t values('two', 'three');
insert into t values('one hundred twenty three million...', 123456789);
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested SELECTS using UNION and INTERSECT syntax problems....
sorka wrote:
> I can't for the life of me figure this out. I'm trying to do a nested select
> like this:
>
> SELECT x FROM (( UNION ) INTERSECT ( UNION
> )) WHERE X=
>
> Each of the select a through d statements all return the same column x. If I
> remove the inner parentheses, it executes just fine but of course the
> results are wrong because C UNION D was not executed prior to the INTERSECT.
>
> Also, in each sub select case, a, b, c, and d, are all selecting on
> different FTS3 tables using MATCH so I can't take advantage of FTS3's newer
> nested parentheses with AND OR NOT hence the nested selects since MATCH can
> only be used once per select.
>
> Any ideas? I really don't want to have to resort to using temporary tables.
>
You need to use a separate select for each compound operator. Something
like this should work as you expect.
select x from
(
select x from
(select x from a
union
select x from b)
as ab
intersect
select x from
(select x from c
union
select x from d)
as cd
) as abcd
where x > ?;
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Wiki page on Management Tools - should it explicitely state Mac OS X support?
BareFeet wrote: > > I have a page of SQLite GUI apps listed and compared at: > http://www.tandb.com.au/sqlite/compare/?ml > > If you know of any more applications or would like to see another > feature compared, reply here in this forum and I'll see what I can do. > > Tom, You should add a column for the Spatialite GUI which can be found at http://www.gaia-gis.it/spatialite/ It is a free open source Mac OS X native GUI DB management tool. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_value
Ondrej Filip wrote: > I have problem with sqlite3_column_value function. I'm porting one project > using sqlite to version 3.3.6 but I'm not able to find when this function > were added to sqlite. > > This function was in the source file vdbeapi.c, but eliminated by the preprocessor (#if 0 ... #endif) until checkin 3234 on 2006-06-14. This was about a week after version 3.3.6 was released on 2006-06-06 (An interesting date 06-06-06, or without leading zeros, 6-6-6, or 666 "the number of the beast". Probably doesn't mean anything though. :-)). You will have to upgrade to a newer version to get access to that function. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database path in widows
Jibin Scaria wrote: > I am facing problem with spaces in the database path, able to open database > but queries are returning "no such table: table name". > > > If your table names contain embedded spaces you must quote the name in your SQL queries select * from "table name"; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange behavior with sum
D. Richard Hipp wrote: > On Jan 29, 2009, at 10:40 AM, matkinson wrote: > > >> Hi, >> >> I'm not replying with an answer, but with a similar problem. I am >> using a >> TRAC database on top of a SQLite DB and I want to provide a >> percentage of >> the sum total hours/sum estimated hours. Here's what I'm seeing. >> - when only one value (a natural number) is summed and divided, the >> answer >> is 0 (should be 73/100=73%). >> - when multiple lines are summed and divided && the some of the >> lines have >> fractions (like 7.25, 1.33, etc.), then the answer is correct. >> - when multiple lines are summed and divided && the lines are all >> natural >> numbers, then the answer is 0. >> >> My query is below. Does this make any sense? >> > > No, it makes no sense. But it does conform to the SQL standard. If > you want standards compliance use SUM(). If, on the other hand, you > want a sensible answer, use the TOTAL() function instead of SUM(). > > > > Sure, it makes perfect sense. :-) Sqlite does integer division with integer arguments, and floating point division if either of the arguments are a floating point value. sqlite> select 1/3; 0 sqlite> select 1.0/3; 0.333 The SUM function produces an exact integer result if all its arguments are exact integers. If any of the arguments to SUM are approximate (i.e. floating point) values then SUM produces an approximate (i.e. floating point) result. sqlite> select SUM(1); 1 sqlite> select SUM(1.0); 1.0 When these two behaviors are combined you get the results you are seeing. sqlite> select SUM(1)/3; 0 sqlite> select SUM(1.0)/3; 0.333 To get the results you expect, you can use the non-standard TOTAL function as Richard suggested. It is the same as SUM except it always returns an approximate floating point result, and hence results in floating point division. sqlite> select TOTAL(1); 1.0 sqlite> select TOTAL(1)/3; 0.333 Alternatively, if you want to use SQL that is more portable, you could also cast the result of the SUM function to a floating point value to ensure that a floating point division is done. sqlite> select cast(SUM(1) as real)/3; 0.333 HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] defalut value of col
Antoine Caron wrote:
>
> I was expecting -1 instead of NULL as default value, can anyone explain me
> that ?
>
That looks like a bug in the table_info pragma. You should probably
report it.
The following test script shows that the correct default value is used
even though the an incorrect null value is displayed by the pragma.
sqlite> CREATE TABLE IF NOT EXISTS test_table
...> (test_field INT NOT NULL DEFAULT -1, two text);
sqlite> .mode column
sqlite> .header on
sqlite> PRAGMA table_info (test_table);
cid nametypenotnull dflt_value pk
-- -- -- -- -- --
0 test_field INT 99 0
1 two text0 0
sqlite> insert into test_table(two) values('one');
sqlite> select * from test_table;
test_field two
-- --
-1 one
You can report the bug at
http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Index] Listing 6001 after 601 and not after 801?
On Sun, Oct 5, 2008 at 7:59 AM, Gilles Ganault <[EMAIL PROTECTED]>wrote: > > This is for an accounting program: As an exemple, all accounts > starting with 6 must be listed together, which means that eg. 6001 > must come after 601, and not after, say, 801. It sounds like you want the account numbers to be sorted as text rather than numerically, so cast the values to text in the order by clause. ... order by cast(account_number as text) ... HTH Denis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Duplicated primary key error
Mariano Martinez Peck wrote: > > This is my first post in this list! I am very newbie with Sqlite. This is > the first time I am trying to use it. I am using Sqlite3 trough C interface. > The problem I have is this: I have a table created, just like this: > > CREATE TABLE materia( > codigo INTEGER PRIMARY KEY, > nombre CHARACTER VARYING(50), > observaciones CHARACTER VARYING(255), > ) > > Then I do 2 inserts one after the other, with the same data. For example: > > INSERT INTO materia(codigo, nombre, observaciones) VALUES (55, ''TADP'', > ''Nothing") > > After doing this, I thought the second query ( i am using sqlite3_step() > function ) will returns me a > SQLITE_ERROR<http://www.sqlite.org/c3ref/c_abort.html>. > However, it SQLITE_IOERR_BLOCKED. > > Is this correct? what should sqlite3_step returns me in this case? > Are you resetting the query with sqlite3_reset before you execute the sqlite3_step function the second time? This should also generate an different error (possibly SQLITE_MISUSE) but I just want to be sure what you are doing when you get the IOERR return. It would be best if you could post the code you are using to prepare and execute the query. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The old bug strikes back
Shane Harrelson wrote: > This was my fault. http://www.sqlite.org/cvstrac/chngview?cn=5654 > strcasecmp() isn't available on all platforms, and I naively assumed > sqlite3StrICmp() would be (it's not in this case do to the way you > are compiling/linking). I'll review the issue and see what I can do. > I see this was addressed by checkin [5735] at http://www.sqlite.org/cvstrac/chngview?cn=5735. It seems to me there may be an oversight here. The original function was strcasecmp() not strcmp(). The code in question was doing case insensitive string comparisons, but it is now doing case sensitive comparisons. This may lead to a change in behavior if the string zConflict is not constrained to be lower case elsewhere in the code. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac file locking
P Kishor wrote: > > Still, you have a point, and maybe DRH will expound and enlighten us > on his reticence to enable locking style equal to one. > It seems he isn't so reticent after all. See checkin [5737] from this morning at http://www.sqlite.org/cvstrac/chngview?cn=5737. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] tracking table row counts
P Kishor wrote: > So, I am creating a new database, and want to set up triggers to track > row counts in each table as rows are inserted or deleted. > > A couple of questions -- > > 1. Do I have to create a separate pair of AFTER INSERT and AFTER > DELETE triggers for each table that I want to track, Yes. > or is there a way > to create a generic trigger that fires whenever any table is touched > and returns the table name and the number of rows affected? > No. > 2. Doing a trigger like so is fine > > sqlite> create trigger del_foo after delete on foo >...> begin >...> update counts set rows = rows - 1 where tablename = 'foo'; >...> end; > > however, what if I > > DELETE FROM foo WHERE msg LIKE 'sqlite%' > > How do I get the number of rows that were deleted by the above DELETE? > Do I first do a SELECT to find out the number of matches before doing > the DELETE? You don't need to the trigger fires for each row that is deleted, so you always delete 1. > > 3. When doing a batch of INSERTs in a transaction, will the trigger > fire after the transaction is committed, or on every update? I am > assuming the former, but, in that case, how will know how many rows > were inserted? > Same for the insert trigger. It executes after each row is inserted. Always add 1. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement help
Andrew Drummond wrote:
>
> for each element1 in (select address.* from numbers,address where
> numbers.number = "12345678" and numbers.address_id = address.address_id)
> {
> for each element2 in (select numbers.* from numbers where address_id =
> element1.address_id LIMIT 20)
> return element1.* , elemen2.number
> }
>
>
> the output would be
>
> 1Peter12345678
> 1Peter09876654
> 2Paul 12345678
>
The following query will produce the output above, but it does not
implement the limit of 20 numbers per address that is shown in your
pseudo code.
select a.address_id, a.name, n.number
from address as a
join numbers as n on n.address_id = a.address_id
where a.address_id in
(select address_id from numbers where number = '12345678')
order by a.address_id;
I am still working on a complete query, but I have managed to trigger a
crash in SQLite in the process.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backticks in Column Names
Gavin Kistner wrote: > > Not only that...look at the values! > Oh yeah! I missed that. For the benefit of others. SQLite version 3.6.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t (`a`, ```a```); sqlite> insert into t values (1,2); sqlite> .headers on sqlite> .mode column sqlite> select * from t; a a -- -- 1 1 sqlite> pragma table_info(t); cid nametypenotnull dflt_value pk -- -- -- -- -- -- 0 a 0 0 1 `a` 0 0 From the table_info you can see that SQLite knows the correct name of the second column, but it doesn't display the correct name, or the correct value for the select * query. >> Have you filed a bug report to see in any of these can be resolved? > I will file a bug report for this one. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backticks in Column Names
Gavin Kistner wrote:
>> My point can perhaps better be described by example:
>> (written on my phone; please replace all • with backticks)
>> sqlite> create table perverse (•select• text, •••select••• text);
>> sqlite> insert into perverse values ('a','b');
>> sqlite> select •select•, •••select••• from perverse;
>> select|•select•
>> a|b
>>
>> In the above, "•select•" is very different from "select".
>>
Yes, you are correct, using identifier quoting you can embed quotes and
other special characters (like spaces) into an identifier name. I missed
your point.
create table t ("a `col ] with [ various quote""s `embedded`'");
>> (As an aside, here's a fun bug: try "select * from perverse" and see
>> if you can guess what will be shown in sqlite.)
>>
I get column names of
select|select
Yes, that would be another bug. The second columns name should include
the back ticks that were escaped by doubling them inside the outer back
tick quotes. SQLite seems to get confused because the quotes appear at
the ends of the identifier string.
Have you filed a bug report to see in any of these can be resolved?
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert the MAC address from integer to characters.
Joanne Pham wrote:
>
> Can we convert these sql statement to function/store procedure so we can pass
> in the number and the return value back the character format.
> For example : ConvertMAC(29672054730752 ) and the return value back :
> 00:30:48:90:FC:1A
No, you can't create user defined functions in SQL, and SQLite does not
support stored procedures.
You could create a view that returns the same columns as the base table
with the mac address column converted to a string using this SQL
expression.
Given
create table t (id, mac integer, data text);
You could create a view like this
create view tv as
select id,
substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
as mac_addr,
data
from t;
Now you can use the view in all your queries and get the string form of
the mac address when ever you need it.
You could also do a join to the view using the id column whenever you
want do get the mac address string in a query that still needs to use
the original mac address as an integer.
select data, mac_addr
from t
join tv on tv.id = t.id
where t.mac in (select ...)
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert the MAC address from integer to characters.
Joanne Pham wrote:
> Sorry! the conversion is correct but it is in reverse order.
> The select statement return :
> 1A:FC:90:48:30:00
>
> and I checked the MAC Address:
>
> 00:30:48:90:fc:1a
> How to change it to correct order or may be the number 29672054730752 needs
> to be reverse.
> Once again thanks for the help,
Simply rearrange the order of the byte pairs.
select
substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
as 'MAC Address'
from t;
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert the MAC address from integer to characters.
Joanne Pham wrote:
> Thanks a lot for quick respond.
> I would like to have the format as : 00:15:C5:F1:1D:45
> Please help me how to convert this number 224577687400448 to
> this format 00:15:C5:F1:1D:45
This should do the trick. It's not pretty in SQL, and it might make more
sense to do it in your application's programming language, but it does work.
select
substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
':' ||
substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1)
as 'MAC Address'
from t;
This assumes that the table t has an integer column mac that hods the
mac address to be displayed.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert the MAC address from integer to characters.
Joanne Pham wrote: > I have this MAC Address as integer 224577687400448. Is there any > buildin function in SQLite to convert this MAC Address from integer > to character format (IP Address format) as > ... No, there is not, but you can do it using a simple (well maybe not so simple) expression using bit manipulation and concatenation. Note, you say you have a MAC address (i.e. 48 bits) which are usually displayed as a set of 6 hex bytes (i.e. XX-XX-XX-XX-XX-XX) not in the dotted quad format used for IP addresses (which are only 32 bits in IPv4). Which do you really have, and what format do you really want to use to display it? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backticks in Column Names
Gavin Kistner wrote:
> Given that it's possible (if moderately insane) and legal(?) to have a
> column name with a backtick in it, then displaying "`foo`" as the
> column name is semantically different from displaying "foo".
The quotes (of whatever kind) are not part of the identifier, they
surround the identifier. They are not "in it".
> For us
> humans, it's not as bad as displaying "Grape Ape"; our poor computers,
> however, have lesser powers of reasoning.
>
> As you say, using an AS clause avoids the problem:
>
>sqlite> .headers on
>sqlite> create table bar (`select` text);
>sqlite> insert into bar values ('a');
>sqlite> select `select` from bar;
>select
>a
>sqlite> select `select`, count(`select`) from bar;
>`select`|count(`select`)
>a|1
This looks like a bug to me. The back tick quoting extension for
identifiers was added for MYSQL compatibility. The same thing also
happens when using SQL standard double quotes to select a column and an
aggregate function of the column at the same time.
select "select", sum("select") ...
returns the column names
"select" and sum("select")
but using an non aggregate function such as length()
select "select", length("select")
returns different columns names, namely
select and length("select")
Changing the type of function that is used to produce the second column
of the result set should not effect the name that is returned for the
first column in the result set.
I would suggest that the name of the result column or expression should
always be returned without any surrounding quotes. As is done for all
but the case with a aggregate function of the column.
>sqlite> select `select` as `select`, count(`select`) as hits from
> bar;
>select|hits
>a|1
>
This is also a bug if you ask me, but it is also by design. The second
`select` in the above statement should actually be a literal string not
an identifier, but SQLite allows identifier quoting for literal strings.
It falls back to using the identifier as a literal string if only a
literal string can be used in that location.
Again it behaves the same way if the as clause literal name is quoted
using SQL standard double quotes, or the MSSQL compatibility extension
square bracket quotes.
select "select" as "select" ...
select [select] as [select] ...
select `select` as `select` ...
all produce the same column name
select
without any surrounding quote characters.
All three should generate a syntax error message since an identifier is
not allowed as the alias name in an as clause.
> Now I just have to convince the author of the ORM library I'm using to
> put AS statements on every column in every SELECT, and either hope
> that this causes no performance impact on any of the supported RDBMS,
> or convince him to branch code for SQLite. (Assuming that placing AS
> clauses for every column in every SELECT does not noticeably impact
> SQLite.)
>
There is no standard for the display of result column names (at least
that I am aware of), so this sort of code is inherently database engine
specific.
> I also hope, however, that you may see the current behavior as
> undesirably inconsistent, even if it is justifiable by your design
> philosophy.
>
You should perhaps file a bug report to see if this can be corrected.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert not 'taking' but receive SQLITE_OK and SQLITE_DONE
Rob Belics wrote:
> I use the C interface and have been reading data from this database just
> fine for quite a while. Now that I'm writing data to it, it seems
> unreliable but I'm sure it's me doing something wrong.
>
> Using sqlite's program, I can do this:
> INSERT into CUSTOMER_ORDER(customer_id,order_num,style,color)
> values('1234',3,0,0);
>
> Writing the same thing using the C api works immediately before that for
> entering the 'customer_id' in a different table. But I immediately prep
> the above statement and 'step' it and it does not do anything but the
> return code is SQLITE_OK for the prepare command and SQLITE_DONE for the
> step command. (or vice-versa. Forget now)
>
> I do notice, on my test server, the sqlite journal. From reading
> elsewhere on this mailing list, it's an indication I have not ended the
> transaction or committed it. Since "insert" should do a "begin
> transaction" automatically, I "end transaction" and "step" it but there
> is no change.
>
> Could someone please straighten me out on this. Thanks.
>
If you step an insert statement, and it returns SQLITE_DONE, then it
completed and hence would have closed any transaction it opened in auto
commit mode. If you still have a journal file then you must have a
manual transaction open.
You will probably get better, more detailed, help if you post the code
you are using to write to the database.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concatenation question
Clark Christensen wrote: > > Long setup for a simple question: Is null the expected result when > one column of a concatenation operation is null? > Yes, that is the result required by the SQL standard. The result of a concatenation operator is NULL if either argument is NULL. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Efficient query of 2 related tables
Thomas DILIGENT wrote:
> I have 2 tables:
>
> A: _ID autoinc primary key
>name text
>b integer foreign key to table B
>
> B: _ID autoinc primary key
>name text
>
> In sql:
> CREATE TABLE "A" ("_ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name"
> TEXT, "b" INTEGER NOT NULL DEFAULT '0')
> CREATE TABLE "B" ("_ID" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name"
> TEXT)
>
> I would like to select A records with regards to some criteria, and then B
> records matching the A records.
> I do not want to make a left join query because,
> First, in a concrete example, I may have several relations and this may lead
> to a very complex query.
> And secondly, the code that reads the result is generated and extracting
> records from a single result may be very difficult to implement (I don't
> event want to know if it's feasible).
>
> My first idea was to perform:
> 1) SELECT * FROM A WHERE name LIKE 'foo*'
> 2) SELECT * FROM B WHERE _ID IN (SELECT _ID FROM A WHERE name LIKE 'foo*')
Do you really mean this?
SELECT * FROM B WHERE _ID IN
(SELECT b FROM A WHERE name LIKE 'foo%');
This uses your foreign key column b to refer to the records in the B
table. Also, the like operator uses % as a wildcard, not *.
>
> So my question is:
> What is the most efficient to perform such queries ?
Using a join will be more efficient. This will give the same results.
select B.* from A join B on B._ID = A.b where A.name = 'foo%';
This query will be quite efficient if you have an index on the name
column of table A.
> Is there any kind of cache that could avoid re-performing the query on A
> records ?
There is no need for a cache if you use a join.
If you still want to use multiple statements you can create your own
cache using a temporary table.
create temp table cache as SELECT * FROM A WHERE name LIKE 'foo*';
SELECT * FROM B WHERE _ID IN (SELECT b FROM cache);
This temp table can be resued as often as needed. When you are done with
it simply drop the table (or close the database, since all temp tables
are dropped when a database is closed).
> Is that the purpose of views ?
In some ways. You can use a view to hide the complexity of a query.
create view foo_B as
select B.* from A join B on B._ID = A.b where A.name = 'foo%';
With this view defined you can now get the same results using a simpler
query.
select * from foo_B;
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Considerations with in-memory SQLite3
Mohit Sindhwani wrote:
>
> Since the database is to be created and deleted in a thread itself, I
> think I may need to do something like:
> * Create in-memory database (":memory:")
> * Attach the main database (from file) as 'mt'
> * Create the temporary table for id_list
> * Insert the user entered IDs
> * Create an index on it
> * Join and get the records of interest (between idlist.id and mt.table.id)
> * Do the processing
>
> Then, when I close the database, the in-memory stuff is all gone. Does
> that sound right?
>
That looks right to me.
There is no need to create an index on the temporary id_list, since you
are going to be doing a full table scan of that table anyway.
select *
from id_list
join mt.table on mt.table.id = id_list.id
order by id_list.id
The only benefit of an index would be if you want the results returned
in id order, then the index would be used to optimize the order by
clause. If that is the case you can get the same effect by declaring the
id column as "integer primary key" in the id_list table. This will
eliminate the index and its redundant duplicate storage of the id_list data.
For fastest operation the mt.table.id should also be an "integer primary
key" column as this will eliminate a rowid lookup operation if it is an
indexed column.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote: > Am 10.09.2008 um 17:37 schrieb Dennis Cote: > >> Lothar Behrens wrote: >>> What is the function to rollback a transaction or commit ? >>> I want also to break into these functions. If there is no way I try >>> to implement the rollback and commit callbacks. >>> Also the closing of the database would be interesting, or analysing >>> the data in the jornal. >> The journal file is closed when a transaction ends. This is done by >> the function pager_end_transaction() at line 28880 of the >> amalgamation. Note this function is called for both a rollback or a >> commit. >> > > Hi, > > I now have seen that many of my simple select statements automatically > does a rollback on behalv of OP_Halt. > > Also I have seen that an insert, update or delete statement does > automatically a commit in some circumstances as: > > * One VDBE is running only > * the statement hits an ON FAIL and have to commit in that case > * other circumstances I do not understand yet > > If I do understand all this correctly I have one case I may stuck into: > > A select statement (not readonly) is still open (having sqlite3_step() > returning SQLITE_ROW) and then > I have created an insert statement that is committed but the outer > transaction as of the select statement > does a rollback if closed later. > > Thus, this results in readable (just inserted) data but loses these > data because the outer rollback occurs. > > Right ? > > If so, then I have to redesign something as of this may be the case in > my usage of the database API :-) > > My database form opens a statement to select some data and navigates > to one (the first, the next or any other) and > leaves the statement open in a transaction I think (form A, database > A) as of a call to sqlite3_step() returning SQLITE_ROW. > > Then I open another database form (form B, database A) and try to add > some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or > rollback (sqlite3BTreeRollback), so I assume the running transaction > from form A is causing this. > > Then when I close my application the transaction (form A, database A) > is rolled back and this loses my data changes. > > Right ? That is correct. You are in autocommit mode, so each SQL statement executes in its own transaction. The outer select starts a transaction. The insert does not start a transaction, since a transaction is already open (sqlite only has a single transaction open at any time). The application can see all the changes to the database (i.e. it can see the uncommitted data). If you now close the database before you reset or finalize the select query (which will commit the transaction it started), then the open transaction will be rolled back and the changes will be lost. > > So my solution would be this: > > Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try > to finish until SQLITE_DONE to close the transaction. > > I can do this because: > > * I mostly read only the primary keys of a table (there it is done > automatically) to prepare for lazy load (pattern). > * I read the full data row for a specific primary key as of any cursor > activity. (That way I have simulated full cursor support) > > I hope with that I get solved this problem. > > Please comment, If there is something still wrong in my understanding. > That should work fine. You don't have to let the select run to completion if you don't want to. If you reset or finalize the select after it returns the last desired row, it will also close the transaction. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Optimization
Mitchell Vincent wrote: > SELECT customer_id FROM customers WHERE cust_balance != (select > coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND > invoice.customer_id = customers.customer_id) > > The above query is used to determine if any stored balances are out of > date. It works very well but is *really* slow when the customer and > invoice tables get into the thousands of rows. Is there a better way > to accomplish the same thing, or some combination of indexes I can > create to help speed that query up? Currently indexes are on the > customer_id columns of both tables as well as the cust_balance field > in customers. > The index on cust_balance does no good for this query, sqlite must do a complete table scan of the customer table anyway. You could speed up the sub-select somewhat by replacing the index on invoice.customer_id with a compound index on invoice.customer_id and invoice.status. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, );
Joanne Pham wrote: > Any idea about these error messages: > 0xb6f67ca5 in enterMem () at ../src/mem1.c:66 > 66sqlite3_mutex_enter(mem.mutex); > Current language: auto; currently c What version of sqlite are you using? Line 66 in mem.c is a comment in the current version. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trim everything that is entered into database
Josh Millstein wrote: > On 9/9/08 11:46 AM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >> Josh Millstein <[EMAIL PROTECTED]> >> wrote: >>> Is there anyway to perform a trim to everything that is entered into >>> a table instead of trimming before I put data in? >> >> update mytable set myfield=trim(myfield); >> > > Yeah, but can you do that automatically on each insert into the db. Trim > the whitespace, that is?> Yes. Simply do these updates in triggers. You will need to add two triggers, one that executes after each insert, and one that executes after each update. create trigger mytab_in after insert on mytable begin update mytable set myfield = trim(myfield) where rowid = new.rowid; end; create trigger mytab_in after update of myfield on mytable begin update mytable set myfield = trim(myfield) where rowid = new.rowid; end; Now your application can insert untrimmed data, but the database will only store trimmed data, and therefore you will only ever retrieve trimmed data. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error A0A
[EMAIL PROTECTED] wrote: > Do you think this is causing my problem? No, not your immediate problem. > I've added reset call there because without that this error was more frequent. > With the reset call in place the sqlite_step will re-execute the entire statement from the beginning after a busy return. This will probably cause problems if you use this to execute a query. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] char to int conversion
jerry wrote: > I have a CHAR field which is usually an integer. I would like to sort > this field as if it is an integer so that 1a 5b 10c 12xxx does not get > sorted as 10c 12xxx 1a 5b. I have successfully used something like > "ORDER BY CHARFIELDNAME - 0" which seems to convert the expression to > an integer the same way that atoi would. This is exactly what I want. I > would like to know if this is an accident or it is behavior that I can > count on for future versions of sqlite. Thank you. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > I would suggest that you use a cast expression to force the data to be an integer. For strings such as your examples the cast will only use the initial characters that form a valid integer. order by cast(somefield as integer) The behavior you are seeing is well defined, but I think a little less clear. The subtraction operator requires numeric arguments so sqlite will coerce the string field into a numeric value before it is passed to the subtraction operator. This coercion works in the same way as cast operator, ie it ignores any non numeric suffix. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote: > > But when you say, that, if jornal files are open, transactions are > opened, I would set a > breakpoint at the line of code the transaction opens these jornal file > and I could look > arount there from who the transaction comes. > > Is that an option ? > > What function in the sqlite library does this ? > Yes, that is an option if you are using a source code version of SQLite, either the individual source files or the amalgamation file, sqlite3.c. The journal file is opened by the function pager_open_journal() at line 30868 in the amalgamation source for version 3.6.2. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote:
>
> I have added this function right after sqlite3_step, that does the
> prepared insert statement.
>
> int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
>
> int autocommit = sqlite3_get_autocommit(m_pDatabase);
>
> if (autocommit == 0) {
> wxLogError(_("Warning: Database is not in autocommit mode.\n"));
> }
>
> autocommit is always 1. Also I have thested the following:
>
> Open the application and opening the form to display first row -> no
> jornal file is opened, because no write is yet done.
>
> Adding some rows and navigating forward and backbackward -> jornal
> file is opened and I can see my data in the application.
>
The fact that a journal file exists at this point implies that you are
still in a transaction. If you close the database without committing
this transaction, the changes that you can see in your application will
be rolled back and lost (see H12019 at
http://www.sqlite.org/c3ref/close.html).
Can you add a function to check the auto commit status in your main line
code (i.e. where you are navigating and viewing the data)?
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Jay A. Kreibich wrote: > > Everything you describe sounds exactly as if a transaction has been > started, but is not committed. When you close the database, the > transaction is automatically (and correctly) rolled back. This will > also delete the journal file. > > I know you said you weren't trying to start a transaction, but you > might double check that. Set a breakpoint right after the INSERT is > finished and check to see if you have a journal file or not. You > could also try issuing a "BEGIN" right after the INSERT. If you get > an error, you're already inside a transaction. > An easier and more accurate way to check may be to add a call to sqlite3_get_autocommit() after your insert is complete. It will return zero if there is an active transaction, and 1 if there is not (i.e. it it in autocommit mode). See http://www.sqlite.org/c3ref/get_autocommit.html for details. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Lothar Behrens wrote: > > Does someone have any more ideas how to narrow the problem ? > (After the insert statement until to closing of that file) > Try executing "pragma database_list;" after the insert. Double check the file name and path shown for the main database and ensure that is the same file you are looking at with your database browser. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bigger table and query optimization
Bruno Moreira Guedes wrote:
>
> I can split my 'something' in st0, st1, st2, stN... I have a '.'
> betwen this values. But the number of "st"s is undefined. I tried to
> do "the necessary" number comparsions, appending tokens:
>
> SELECT fields FROM sometable WHERE field = 'st0.st1.st2.st3' OR field
> = 'st1.st2.st3' OR field = 'st2.st3' OR field = 'st3';
>
You could try this
select fields from sometable
where substr(field, -length(:somestring)) = :somestring;
This will still require a full table scan and will not use an index, but
the overhead of testing if the field ends with the appropriate string
should be as small as possible.
Your main problem is there is no way to use an index to match the end of
a string. If this is a common operation for you database, you may want
to add a field that stores the strings in reverse order. You can then
add an index on that string. What used to be the end of the string is
now the beginning of the reversed field, and can be searched quickly
using an index.
You will need to create a user defined function to reverse the
characters of a string.
reverse('string') => 'gnirts'
With this function you could add a new field to your database and create
a suitable index on that field.
alter table sometable add column reversed text;
update sometable set reversed = reverse(field);
create index field_reversed_idx on sometable(reversed);
Now you can use the same function to reverse the string you are trying
to match and use a like comparison to locate the strings quickly using
the index (since the search string is now the prefix of string).
select field from sometable
where reversed like reverse(:somestring) || '%';
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error A0A
[EMAIL PROTECTED] wrote:
> I'm using some wrapper but modified.
> Here is my Exec method:
>
>
> if ( rc == SQLITE_BUSY)
> {
> Sleep(0);
> rc = _sqlite3_reset(m_stmt);
> continue;
> }
>
You should not be resetting the prepared statement on a busy return. You
should simply sleep and then continue to retry.
If you want to ensure you don't loop forever, you could add a retry
counter and do a reset and return if the retry limit is exceeded.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use PRIMARY KEY AUTOINCREMENT when modelling weakentities?
Andreas Ntaflos wrote: > > But it seems I misunderstood the point of AUTOINCREMENT. I am looking for > something like PostgreSQL's SERIAL data type [1] so when creating new rooms I > don't have to manually specify the roomID. Instead the next possible roomID > should be chosen automatically when INSERTing. > > What is the correct SQLite-way of doing this? > Well you could use a select to find the largest roomID the already exists in the building you are inserting into. insert into room values ( (select max(roomID) from room where buildingID = :theBuilding) + 1, :theBuilding); Now you only need to specify the building and sqlite will calculate the lowest unused room number in that building. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to use PRIMARY KEY AUTOINCREMENT when modelling weak entities?
Andreas Ntaflos wrote: > > Naturally a room cannot be identified without a building so it is a weak > entity (this seems to be the canonical example in all database books I've > seen). > That is only true if you allow the same roomID to be used in multiple buildings (i.e. there exists a room 101 in building A, and a room 101 in building B). In this case, you do *not* want to auto increment the roomID values. Now you require the combination of the roomID and the buildingID to identify a particular room. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Broken indexes ...
Jordan Hayes wrote: > > Where would the file be? It would be "beside" the database file, i.e. in the same directory as the database file. The journal only exists while sqlite is modifying the database. When a change has been completed successfully, the journal file is deleted. > I don't see a file. I didn't write the > application, but it runs in Windows. Under Windows does it get put > somewhere "special" ...? > When I restarted the application it didn't > give me any notice that anything was wrong; Normally it wouldn't give you any indication it found a journal. When the application opened the database file, the sqlite library would see the journal file (if it existed) and use it to restore the database file to the exact state it was in before the last change (i.e. transaction) started, and then delete the journal file. If there is no journal file, the library assumes the database file is intact and simply opens it for use. If the application was making changes and hence a journal file existed at the time of the power failure, the database file would be in an inconsistent state. The journal file has the information needed to restore the state. If you, or your application, deleted this journal file (thinking it was a temporary file left over from the crash) before the sqlite library gets a chance to see it (i.e. before the database is opened again) your database file is left in the inconsistent state. Some applications perform an integrity check on database files immediately after they are opened. These applications may report the problem if a "hot" journal file is deleted, since the database may have been left in an inconsistent state by a partially complete change. An application could check for the existence of the journal file before opening the database, and report that the incomplete transaction will be rolled back as the database is opened. I doubt if many applications do this however. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Broken indexes ...
Jordan Hayes wrote: > I had a power failure yesterday and an active database of mine is now > trashed. The output of "pragma integrity_check" includes lots of > > rowid missing from index MyIndexName > > and > > wrong # of entries in index OtherIndexName > > messages. > > Plus this: > > On tree page 4 cell 17: 2nd reference > On tree page 4 cell 17: Child page depth differs > On tree page 4 cell 18: Child page depth differs > > Is there something simple I can do to fix this? It seems like it would > be useful to have a command that would drop-and-regenerate an index; I > looked in the documentation and couldn't find one. So I dropped them by > hand and rebuilt them and now all those errors go away, but I'm still > left with the last three. > > Am I just out of luck? > I'm curious how you got to this point. If the power failed during a transaction there should have been a rollback journal file beside the database file which the sqlite library would discover and use to undo all the changes in the partially complete transaction the next time that database was opened. Did you perhaps delete the journal file before starting sqlite or your application after the power failure? I'm sorry I can't help you with recovering your database, except to suggest going back to your last backup, and redoing the changes since then. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem using random() in queries
Igor Tandetnik wrote:
>
> It looks like random() is run twice for each row - once in WHERE clause
> and again in the SELECT clause. This looks like a bug.
>
I agree, this looks like a bug. This is a simpler query that shows the
same problem.
sqlite> create table t (id, a);
sqlite> select a, random() as b from t where b < 1000;
sqlite> explain select a, random() as b from t where b < 1000;
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Trace 0 0 0 explain select a, random() as b
from t wh
ere b < 1000; 00
1 Integer1000 1 000
2 Goto 0 14000
3 SetNumColumns 0 2 000
4 OpenRead 0 3 000
5 Rewind 0 12000
6 Function 0 0 2 random(-1) 00
7 Ge 1 1126a
8 Column 0 1 400
9 Function 0 0 5 random(-1) 00
10ResultRow 4 2 000
11Next 0 6 000
12Close 0 0 000
13Halt 0 0 000
14Transaction0 0 000
15VerifyCookie 0 2 000
16TableLock 0 3 0 t 00
17Goto 0 3 000
sqlite>
It seems to me that SQLite should be doing a CSE (common subexpression
elimination) optimization anyway. The value of b should be calculated
only once and the result should be saved and reused in the where clause.
It only causes problems with functions that return different results
each time they are called (such as random or time('now')), but it still
inefficient for other functions and expressions. A better example of the
inefficiency is given below where the length function is substituted for
the random function.
sqlite> explain select a, length(a) as b from t where b < 1000;
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Trace 0 0 0 explain select a, length(a) as b
from t w
here b < 1000; 00
1 Integer1000 1 000
2 Goto 0 16000
3 SetNumColumns 0 2 000
4 OpenRead 0 3 000
5 Rewind 0 14000
6 Column 0 1 300
7 Function 0 3 2 length(1) 01
8 Ge 1 1326a
9 Column 0 1 500
10SCopy 5 7 000
11Function 0 7 6 length(1) 01
12ResultRow 5 2 000
13Next 0 6 000
14Close 0 0 000
15Halt 0 0 000
16Transaction0 0 000
17VerifyCookie 0 2 000
18TableLock 0 3 0 t 00
19Goto 0 3 000
sqlite>
There is no reason to evaluate the length function twice.
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem using random() in queries
0 8 random(-1) 00 10Divide 1 8 700 11Add2 7 600 12Divide 3 6 500 13Lt 4 235 collseq(BINARY) 6a 14Column 1 0 10 00 15Function 0 0 7 random(-1) 00 16Real 0 8 0 9.223372036854778e+18 00 17Divide 8 7 600 18Real 0 8 0 1 00 19Add8 6 500 20Real 0 8 0 2 00 21Divide 8 5 11 00 22ResultRow 102 000 23Next 1 9 000 24Close 1 0 000 25Halt 0 0 000 26Transaction0 0 000 27VerifyCookie 0 1 000 28TableLock 0 2 0 names 00 29Goto 0 6 000 sqlite> You could try this instead. -- create temp table with random numbers create temp table rnd(id integer primary key, RNDValue real); insert into rnd select rowid, (random() / 9223372036854775807.0 + 1.0) / 2.0 as RNDValue from names; -- select name based on associated random numbers select name, RNDValue from names join rnd on names.rowid = rnd.id where rnd.RNDValue < 0.99; -- delete temp table drop table rnd; HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
D. Richard Hipp wrote:> > I was going to guess the opposite - that manifest typing reduces > overhead. (But as Dan pointed out - nobody will know until somebody > generates a version of SQLite that uses static typing and compares the > performance.) > I agree with Dan on this point. > The reason I think static typing would make things slower is that with > static typing, there has to be a bunch of checking during processing > to verify the specified datatype is in use. With the current database > file format, this checking must be done at query run-time. And there > is no savings in not having to track the types of each data item at > run-time because the current file format allows dynamic typing. So > any "strict affinity" mode would likely be slower than the current > SQLite. > > If you designed a new file format that did not allow dynamic typing at > the file format layer, then you could perhaps do away with tracking of > types at query run-time. But if you go with a completely new file > format, you really wouldn't be dealing with SQLite any more. So I'm > not sure the comparison would be valid. > > Note that if you really, really want to do static typing in SQLite you > can implement it using CHECK constraints: > > CREATE TABLE ex(a INTEGER CHECK( typeof(a)='integer' )); > > A "strict affinity" mode in SQLite would amount to adding these check > constraints automatically. If you look at it from this point of view, > it seems likely that strict affinity would slow down performance due > to the added cost of checking type constraints at each step. > I think the benefit of a static typing system is that those checks are not done at all at run time. They are done once when the statement is compiled. After that the code can be execute many millions of times (i.e. for millions of rows) without the need for any type checking at runtime because the compiler did the necessary checks. There is no need for a check constraint as you have shown, since the compiler would only generate code to insert integer values into integer columns. If only integer values can be inserted, there is no need to check the type of the values when they are retrieved (even if the file format supports dynamic typing). Now, data pulled from the tables can be assumed to be of the expected type and used directly. This may simplify subsequent processing. The trade off is that the compiler may become more complex and the compilation step may take longer. There may still be a net benefit if the compilation time is only a small percentage of the statement's execution time (i.e complex long running queries on large tables). Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
Hardy, Andrew wrote: > Is there any way to log conversions to highlite any issues that would > have been hilighted by failure with strict typing? > Not that I am aware of. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manifest Typing performance impact?
Hardy, Andrew wrote: > > Is there a performance hit assosiated with manifest typing? > I'm sure there is since sqlite must track the type of each data item as well as its value. But in reality this overhead is quite small. > Is it right that although info on the sqlite site suggests there is an > avilable mode that supports strict typring, this is not infact the case? > That is correct, the strict affinity mode does not exist. > If there is a performance hit, what are the best ways to minimise this? > And is there any way to at least log conversions to highlite any issues > that would have been hilighted by failure with strict typing? > The best way to minimize the conversion overhead is to store the data in suitably typed columns (i.e. that match the type of the data stored in the column). This will avoid any unnecessary conversions when storing, loading, or comparing the values. See http://www.sqlite.org/datatype3.html for the column type affinity deduction rules. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Derek Developer wrote: > Thanks for the link. Unfortunatly its a little expensive and probably > 50% slower than my implementation. > On what basis do you make that claim? > No offense, but C is a language that a lot of us tolerate and is not > the panacea that some C developers like to believe it is. Reading C > is like reading Chinese. ASM may not be a high level language, but it > certainly is fast. > C is the lingua franca of the computer world. Every programmer should be able to read it comfortably. I find reading well written C code, such as SQLite, to be pleasant, and certainly much easier than assembler code. Some languages, such as APL (which I liked) and Perl (which I really don't know very well) are inscrutable to the casual reader. C is not and should not be in that category. Assembly language has two major drawbacks, it is not portable and it is very verbose. The first means that any code you write for one platform has to be completely rewritten for another. The second often leads users to adopt the shortest, simplest, code sequence to accomplish their goal. This is often not the fastest way to accomplish that task. Usually, selecting a better algorithm will do far more to speed up code than rewriting it in assembler. Studies have consistently shown that a good compiler can produce code that is nearly as good as the best hand crafted assembly. There is almost never a reason to write anything except the core inner loops of a CPU intensive operation (such as encryption or decryption) in assembler. It is quite simply a waste of time to do otherwise. The only effective way to write assembly code is in conjunction with good measurement tools. Write the code in a high level language with a good optimizing compiler, like C. Then measure the code to determine where the program actually spends its time. Next, review the code generated by the compiler for the inner most loops in those sections, and replace with hand written assembly code only if you believe your assembly code will be faster than that produced by the compiler. Finally, measure the resulting code and see if it is in fact any faster than the code the compiler generated. In this day of out of order and speculative execution of instructions, and the critical dependency of the CPU on the performance of the memory caching system, it is very difficult to guesstimate the execution speed of a sequence of code especially assembly code. Modern compilers often do a much better job of this than any developer can. Assembly can be used to write faster code snippets, but it is often slower when used to write large applications because the difficulty in writing higher level, more complex, algorithms in assembler often leads to the use of simpler slower algorithms. In short, writing in assembly language does not guarantee that the resulting program will be fast. Assembler can be fast, but it is by no means certain that it is fast. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS, snippet & Unicode?
Alexey Pechnikov wrote: > > Is it included to 3.6.1 or 3.6.2 version? > No, it is not included in either version. The patch was submitted by the mozilla group, but it has not been checked in to SQLite. You can of course apply the patch to your own customized version of SQLite. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] create table if not exists & virtual table?
Petite Abeille wrote: > > Is it possible to use 'if not exists' in conjunction with the creation > DDL for a virtual table? > No, its not possible. The syntax of a "create table" statement is shown here http://www.sqlite.org/lang_createtable.html and that for a "create virtual table" statement is shown here http://www.sqlite.org/lang_createvtab.html. The virtual table statement does not allow the optional "if not exists" clause. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Igor Tandetnik wrote: > > Since I'm not entirely clear of the set of premises you refer to as > "that", I'm not sure whether they happen to be the case or not. But > since I know the conclusion you arrived at is false, I can only assume > that one or more of those premises are incorrect, and/or the logical > deduction from the premises to the conclusion is flawed. > Classic. :-) Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] equality searches and range searches with encrypteddata
Derek Developer wrote: > appologies, "master database" should read "MASTER TABLE" This is > where the index is stored I assume? > No, the index is stored in a separate Btree. The master table simply stores the page number of that btree's root page. With that information SQLite can read and decrypt the index's root page and begin a O(log N) search for the first matching record, reading in and decrypting more pages as required. > > If that is the case then clearly an Index is not a viable solution > and each page will have to be decrypted to perform and equlity/range > search. > No, an index will work securely and efficiently for such a search in an encrypted database. > > Before I implement this, I wanted to make sure I understand the > implementation at the page level. Obviously a single Row can take up > more than one page, but I am still not clear if a single page can > ever contain more than one Row? > Yes, a table page can contain multiple rows, and an index page can contain multiple index entries. You may want to review http://www.sqlite.org/arch.html. The encryption and decryption is done between the pager and the OS interface layers. Nothing else changes, and all the data is stored securely encrypted in the pages of the file. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > Interesting, I just tried that in my test application and Dennis's and I > get access violations during the vacuum command execution when trying to > resize the pages from 1k to 4k with my database or Dennis's test > database. > Daniel, I have found that sqlite works correctly if your main database is a file, but crashes when you try to vacuum with a :memory: database as your main database. The vacuum command does not work on attached databases, so you must open the file to be vacuumed as your main database. This crash is a bug that should probably be reported at http://www.sqlite.org/cvstrac/captcha?nxp=/cvstrac/tktnew HTH Denis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting using random rowids
Susan Ottwell wrote: > How would one insert rows randomly within a range of 1000 rows? I can > easily enough do this in the script that calls the insert function, > but it would be more efficient if I could use an sqlite function or > feature to do this. > I'm not sure if this is what you are asking or not, but you can use the random function to generate the rowid for a table. The modulo operator can be used to restrict the range of values produced. create table t(id integer primary key, data text); insert into t values(random() % 1000, 'some data'); This will insert a row with a rowid somewhere between 0 and 999. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > I just ran Dennis's test databases through the test application and > we're getting similar results: > 1k Pages (17.4 MB) used 18102 KB High 20416 KB > 4k Pages (12.2 MB) used 18102 KB, High 26416 KB (not sure why > the high is higher?) > My test database however with the same test application produces the > following: > 1k Pages (7.46 MB) used 22735 KB, High 25138 KB. > Its good to see you are getting the same results as me using my databases. That rules out your build of sqlite and the build tolls. It does look like your issue has to do with your data. > So it looks my issue could be data related if my test database going > through the same app is coming out so large, Dennis's database is > expanding to about 101.6% of its original size but mine is expanding to > 297.6% of its original size. This begs the question is the 3rd party > tool (SQLite Analyzer) I'm using to import from an excel file causing > this expansion with bad data type choices? And is there any other way > to import table structure and contents from xls (or csv) to sqlite? > You could write one yourself in Python using the csv reader and the pysqlite modules to read CSV files saved from excel and save the data into an sqlite database. I doubt that will change your data much though. Can you publish the schema of your database, and some typical data? It may be a case of storing integers as text or something similar that is causing the unexpectedly large expansion. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details of error messages. Was: "unable to open database file" on DROP
D. Richard Hipp wrote: > > Consider what happens if an interaction with the library contains two > or more errors. Only a single error message and error code can be > returned. Consequently, if the interaction contains error A we cannot > guarantee that the code and message returned will refer to A, since it > might instead refer to error B. We can guarantee that some kind of > error will be returned. We just cannot guarantee what the error code > and message content will be since that depends on what other errors > might be present. > While that may be a concern in the most general sense, I don't think it is a valid reason not to document the error that is returned when a specific rule is broken. Take this rule for example: H42334: The preparation of a CREATE TABLE statement shall fail with an error if the the databasename references a database that is not attached to the same database connection. This is a very specific rule. There should be a well documented error that will be returned if this rule is broken. With these definitions in place, a user could search through the documentation and find all the possible causes of that particular error (heck, you might even want to create an cross reference list to make this step easier). This would often help them to locate the cause of the problem or suggest possible causes that they might not otherwise consider. The fact that multiple rules might be broken by a statement isn't really an issue since you do not (and should not) specify the order that the rules are checked. If a statement has multiple problems they may need to be fixed one at a time until they are all resolved. Giving users the best possible guidance at each step is important and valuable. Furthermore, there are other, non-rule based issues, such as "out of memory" or "I/O error" that may cause an API function to fail. The mere existence of such possibilities should not be grounds to avoid documenting the errors that are returned when any of the many rule based errors conditions occur. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close
Joanne Pham wrote:
> Hi Igor,
> I used SQLite versio n 3.5.9.
> I read the SQLite online document and the suggession that we need to finalize
> all the prepare statement associated with database connection before closing
> the connection as below
>
> while( (pStmt = sqlite3_next_stmt(pDb, 0))!=0 ){
> sqlite3_finalize(pStmt);
> }
> sqlSt= sqlite3_close(pDb);
> but the codes didn't return the syntax for sqlite3_next_stmt. Is
> sqlite3_next_stmt is valid command in SQLite 3.5.9
Yes, it is, see http://www.sqlite.org/c3ref/next_stmt.html for details.
It was introduced in 3.6.0 (see http://www.sqlite.org/changes.html), so
it was not present in 3.5.9.
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > Ok so after reading your feedback I tried: > 1. "PRAGMA cache_size =10" no change in memory usage. > 2. "PRAGMA page_size = 4096" no change in memory usage. > > I'm doing both those queries (in C++) after the 'sqlite3_open( > ":memory:", _pDataBase );' in my test but before the database file is > attached or anything is copied or created. The rebuilt database file is > 7.46 MB and the memory usage I'm seeing is now: 22.20 MB with a high > water of 24.55 MB as reported by the sqlite_memory_* functions. > I believe each database has its own cache. You need to set the cache size for the file database after it is attached. attach 'file.db' as file_db; pragma file_db.cache.size = 100; In my tests I am setting the cache size for both the memory database (right after the open call) and the file database. > I'm not using the amalgamation version of the pre-processed source; I'm > using the individual source files of 3.6.1 on Windows XP with Visual > Studio 2005. I'm afraid I can't give you a copy of the test database as > it's a drop from a live product, could it be using the third party > SQLite Analyzer application to import from excel be the issue? I doubt it, but anything is possible. > Are > there any other tools from importing from a .xls to a SQLite database > (converting each sheet to a table)? > I don't know. > I just tried a "vacuum" after I detach the database from file and that > didn't reduce the memory usage either but it did double the high water > mark which after reading the documentation sounds about right for making > a temporary copy. > > How do I rebuild a database file for another page size or did the pragma > do that already? > I used you test program to do that for my database. :-) I simply changed the filename of the output database from :memory: to my new filename in the open call, and then executed a "pragma page_size=4096" immediately after the open. The rest of your code copied all the tables in the test database out to the new database file with the new page size. This database doesn't use any named (i.e, non-automatic) indexes or triggers, so there was nothing else to be copied. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Brown, Daniel wrote: > 2. And the other thing to try would be if anyone has a fairly meaty test > database they don't mind sharing that I could fling at my test > application to try and rule out the data? > Daniel, I can send you copies of the databases I am using for my testing, both the version with the 1K page size (17.4 MB) and the one with the 4K page size (12.2 MB). Where would you like me to send them? The zipped versions are each about 1.3 MB in size. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Nicolas Williams wrote: > > I thought the DB was 9MB; forgive me for wasting your time then. If > it's 17.4MB then the memory usage seems a lot more reasonable. Daniel, the OP's, database is 9 MB. I don't have his database file, but I do have his test code. I used a database of my own that is a similar size along with his test code to do my tests. In my tests sqlite behaves as expected. Daniel is seeing much higher memory usage reported from sqlite itself using the same version of sqlite, the same test code, and the same OS. I see a memory usage of about 18 MB for a database copied from a file that is 17.4 MB (1K pages) or 12.2 MB (4K pages). I get an expansion factor of 1.03 or 1.48. Daniel is seeing memory usage of 22.2 MB for a database copied from a file that is 9 MB. Daniel gets an expansion factor of 2.47. This seems high. Since the major difference seems to be the database file we are copying, I would like to repeat his test with his database file if possible. If not possible (perhaps the data is proprietary or personal), then it might make sense to see what factors effect this memory expansion ratio. I was surprised by the magnitude of the change in the size of my database file by simply changing the page size. I also tried to change the page size used for the memory database, but that had no effect (Which is not what I expected, perhaps the page size pragma is ignored for memory databases). Changing the cache size reduced the highwater memory requirement, but didn't change the memory required to hold the database after the copy was completed. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Nicolas Williams wrote: > > I wonder too, what does the page cache do when doing full table scans? > If the cache has an LRU/LFU page eviction algorithm then full table > scans should not be a big deal. Ideally it should not allow pages read > during a full table scan to push out other pages, but if the cache is > cold then a full table scan just might fill the cache. > > In this case we have full table scans in the process of copying on-disk > tables to a memory DB. And it looks like the cache is cold in this > case. > > The default cache size is 2000 pages (there's a way to persist a cache > size). The default page size is 1024 bytes, but it's hard to tell > exactly what it is in the OP's case. > > So it looks like the cache size should be ~20MB. And the DB size is > ~9MB. The cache is plenty large enough to hold a copy of the on-disk > DB. > > So we have: 9MB will be consumed in the page cache, and 9MB will be > consumed by the memory DB (I assume memory DB pages aren't cached). Add > in the overhead per-page, which seems to be .5KB, and you have > > 18MB + 9MB / 2 = 22.5MB > > That seems close to what the OP claimed. > But my testing using the same code, but a different database file, uses only slightly more memory than required to hold the database file. This code uses the default cache size of 2000 pages. I built a copy of my test database using a 4096 byte page size and it reduced the database file size from 17.5 MB to 12.2 MB. When I repeat the tests using this database file I get the same 18102 KB of memory used, but a slightly higher highwater mark of 26418 KB used. Again this was all done with the default page cache size. Note, I also tried to vacuum the original file to see if there were a lot of unused pages in the original 17.5 MB file. After the vacuum it was reduced to only 17.4 MB, so there were very few free pages in the database. This database just fits much better on the larger 4K pages. Changing the cache size from the default 2000 to a much smaller 100 pages reduced the highwater mark to 18544 KB, which is only slightly higher than the 18102 KB of memory used after the table is built. The actual memory used is exactly the same (as expected since it is storing the same tables). Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing SQLite Memory footprint(!)
Jeffrey Becker wrote: > Just out of curiosity what happens if you call > "PRAGMA page_size=4096" > before running the import? > As I expected, it has no effect. The page size pragma only effects the :memory: database he is copying into. The page size of the database file was set when it was created. Daniel, can you run a "pragam page_size;" query on youyr database and let us know the results? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
