Re: [sqlite] Statements that return a result set?
Robert Villanoa wrote: > I am new to SQLite, and I have a question: In SQLite, is there a > statement apart from SELECT statement that can return a result set? PRAGMA, EXPLAIN Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of Transaction with in memory db
Sharma, Gaurav wrote: > 1- Is there any significance of using transaction (whether auto > commit or manual) with in memory database. Yes. Transactions work on in-memory databases the same way they do for regular databases. > 2- If the answer of first question is "yes" then second question > would be if I wish to share an in memory db connection amongst thread > with mutex protection. Is there required any special treatment while > using with Transactions besides protecting them with mutex. Again, the same considerations as for regular databases. Transaction is per connection: if one thread issues a BEGIN statement, all subsequent operations by all threads go into the same transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT DISTINCT bug in SQLite 3.6.0?
Lennart Ramberg wrote:
> dim rs as RecordSet
> rs=dbEta.SQLSelect("SELECT DISTINCT V.resanr,C.namn"_
> +" FROM Voyages V,Category C WHERE C.kategorinr=V.kategorinr")
>
> dim namnstr as string
>
> namnstr=rs.Field("namn").StringValue 'I get a NilObjectException
> here in 3.6.0 ...
> namnstr=rs.Field("C.namn").StringValue '... but not here.
Unless explicitly specified with AS clause in the statement, column
names are implementation-defined and subject to change without notice. I
believe the algorithm did change between 3.6.0 and 3.6.3. But you
shouln't have been relying on them in the first place.
> Yes, at sqlite.org I read:
> "SQLite version 3.6.3 fixes a bug in SELECT DISTINCT that was
> introduced by the previous version."
> So that shouldn't be it, since it was introduced in 3.6.2, right?
I don't believe this issue has anything to do with DISTINCT. I'm pretty
sure the problem will remain if you remove DISTINCT.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE TABLE AS SELECT * FROM changes column definition --bug or feature?
P Kishor wrote:
> SQLite version 3.6.11
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, desc TEXT, num
> INTEGER DEFAULT 0);
> sqlite> INSERT INTO foo (desc) VALUES ('foo');
> sqlite> INSERT INTO foo (desc) VALUES ('bar');
> sqlite> INSERT INTO foo (desc) VALUES ('baz');
> sqlite> SELECT * FROM foo;
> id descnum
> -- -- --
> 1 foo 0
> 2 bar 0
> 3 baz 0
> sqlite> CREATE TABLE bar AS SELECT * FROM foo;
> sqlite> .s
> CREATE TABLE bar(id INTEGER,"desc" TEXT,num INTEGER);
> CREATE TABLE foo (id INTEGER PRIMARY KEY, desc TEXT, num INTEGER
> DEFAULT 0);
>
>
> Why did the definition of the column num change? Where did the
> DEFAULT 0 part go in the definition of num?
You don't make a copy of a table - you make a copy of the resultset of a
SELECT statement. Columns in said resultset don't carry attributes like
DEFAULT, even though columns in the underlying table may. Consider:
create table bar as
select id + 1, desc || 'xyz', num * id from foo;
What do you expect the definition of bar to be?
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CREATE TABLE AS SELECT * FROM changes columndefinition --bug or feature?
P Kishor wrote: > On Sun, Aug 2, 2009 at 10:39 PM, Igor > Tandetnik wrote: >> You don't make a copy of a table - you make a copy of the resultset >> of a SELECT statement. Columns in said resultset don't carry >> attributes like DEFAULT, even though columns in the underlying table >> may. > > Hmmm... now that you say so, I am reminded of this topic a short while > ago. Makes sense what you say Igor. However, if the "copy of the > resultset of a SELECT statement" was smart enough to bring over 'desc > TEXT', why was it not quite smart enough to bring over the 'PRIMARY > KEY' part of 'id' or the 'DEFAULT 0' part of 'num'? 'desc' and 'TEXT' are in fact properties of the resultset - see sqlite3_column_name, sqlite3_column_decltype Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of in memory db
Sharma, Gaurav wrote: > Please clear my one more doubt. Is it true that either using the > SQLITE_THREADSAFE=1 as compile time flag or using > SQLITE_OPEN_FULLMUTEX with sqlite3_open_v2 are same thing. Both can > be used interchangeably. Correct me if I am wrong. Once you compile with SQLITE_THREADSAFE=0, all threading and mutex related code is removed at compile time, and SQLite can only be used in single-threaded mode. If you compile with SQLITE_THREADSAFE=1 or 2, you can then switch between multi-threaded and serialized mode at run time, by specifying SQLITE_OPEN_NOMUTEX or SQLITE_OPEN_FULLMUTEX flags to sqlite3_open_v2. SQLITE_THREADSAFE determines the default setting when neither flag is specified. You can also change the default at run-time with sqlite3_config (but again, once you compile with SQLITE_THREADSAFE=0, there's no way to switch to any of the two thread-safe modes). See http://www.sqlite.org/threadsafe.html > Secondly, Is it possible by any mean that for shared connection > amongst thread, the insert on one thread does not become part of the > transaction on other thread. No. A transaction is a property of a connection, not that of a thread. You'd have to use separate connections, perhaps in shared-cache mode: http://www.sqlite.org/sharedcache.html > And one last thing just to confirm that it is not at all possible to > open different in memory db connection from different threads? Every time you open a connection to :memory:, a new in-memory db is created. So yes, it's possible to open multiple connections to in-memory databases (whether from multiple threads or otherwise) - but not to the same in-memory database. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Comparisons SQL
Rick Ratchford wrote: > The Date is being stored as -mm-dd. Note the "Format$(Date, > '-mm-dd') as Date" that assures this. The "Date" that appears in the WHERE clause is the value of the Date column in the table, not the value of the expression with the "Date" alias. You can't actually use aliases in the WHERE clause. You are confusing yourself by using the same identifier both for the column name and for the alias. You could write WHERE Format$(Date, '-mm-dd') < sDateTemp Or else, express sDateTemp in the same format that you have dates stored in the table - the format you get when you just run "SELECT Date from mytable". Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Week
helemacd wrote:
> anybody know how to return the start of the week and end of the
> week???
select (case strftime('%w', T) when '0' then T else date(T, 'weekday 0',
'-7 days') end)
from (select date('now') as T);
-- or
select date(T, '-' || strftime('%w', T) || ' days')
from (select date('now') as T);
This is start of the week, assuming the US convention of beginning the
week on Sundays. End of the week is left as an exercise for the reader.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
Rick Ratchford wrote: > SELECT Date FROM MyTable GROUP BY Year, Week > > This creates a recordset that groups all my prices into 'weekly' > prices. In other words, each row represents the High, Low, Close > prices for each week, and the date is the FRIDAY DATE of that week. If this happens, then only by accident. The value of Date reported for each group comes from an arbitrary row belonging to the group. There is no guarantee which row will be so chosen. > However, by adding WHERE Date < '" dStopDate "' prior to GROUP BY... > (and yes, assume Date and dStopDate are same format), my last record > returned is actually 07/27/2009 (the day before my dStopDate) rather > than my 'weekly' record of 07/24/2009. You've truncated the last group short, so a different row from that "incomplete" group accidentally happened to be chosen. Try SELECT count(*), max(Date) FROM MyTable GROUP BY Year, Week with and without WHERE clause. This might prove illuminating. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
Rick Ratchford wrote: > What I ended up with are the number of days per each week (row) and > the last > date for that week that had data. Yes, of course. What did you expect? > Here are the last few rows. > > count(*) max(Date) > = > 5 2009-06-26 > 4 2009-07-02 > 5 2009-07-10 > 5 2009-07-17 > 5 2009-07-24 > 5 2009-07-31 > 1 2009-08-03 > > Note that each of these dates belongs to the FRIDAY of that week, > except for 2009-07-02 and 2009-08-03. Is this surprising? > This is the problem. While week 2009-08-007 is not yet complete (it > is in > progress), the prior dates need to be FRIDAY dates even if the count > < 5. Try date(max(Date), 'weekday 5') For more details, see http://www.sqlite.org/lang_datefunc.html > I was aware of this when the data was displayed earlier. I'm not sure > what > is 'revealed' other than what is stated above. Well, you appeared surprised that you weren't getting all Friday dates. You claimed you couldn't understand why an addition of a WHERE clause changed the output the way it did. I hoped the demonstration of a "truncated" group would help you "put your finger on your error". In any case, you do seem to possess a clearer understanding of the issue at this time, whether due to, in spite of, or independently of my efforts. > Is there an answer to my problem somewhere in the above output? No, not directly. Teach a man to fish, and all that. > Is there perhaps some SQL command that based on 'count' if less than > 5 the > difference can be added to the date before returning it in the > recordset? Well, you could do something like date(max(Date), (5 - count(*)) || ' days') but that won't work right if you are missing, say, Tuesday data. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Cutoff Statement
Rick Ratchford wrote: > #>Try > #> > #>date(max(Date), 'weekday 5') > > It's likely I'm not using it correctly, because it returns nothing. :( I mean, replace "Date" in your statement with this expression. As in SELECT date(max(Date), 'weekday 5') FROM MyTable GROUP BY Year, Week; > #>> Is there an answer to my problem somewhere in the above output? > #> > #>No, not directly. Teach a man to fish, and all that. > > Completely understand. Just wish my fishing pole was a little more > forgiving. :) An interesting specimen of a double entendre here. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New group_split() function as inverse of group_concat()
Alexey Pechnikov wrote: > Can somebody to show example of the group_split() function? > PRAGMA can return rowset but how to do this in user functions? I'm pretty sure SQLite doesn't support that. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE 11 times slower than SELECT?
Nikolaus Rath wrote: > When running apswtrace on my Python program, I got the following > output: > > LONGEST RUNNING - AGGREGATE > > 16638 11.041 UPDATE inodes SET size=MAX(size,?), ctime=?, mtime=? > WHERE id=? 16638 0.938 SELECT s3key FROM inode_s3key WHERE inode=? > AND offset=? > > i.e. both statements were executed 16638 times, but execution of the > first took 11 seconds in total and execution of the second only 0.9 > seconds. Select likely gets data from in-memory cache. Update has to physically write to disk. Memory is much faster than disk. If at all possible, group write operations together into a transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT * from ?
Bill Welbourn wrote: > I am new to the forum and have a question regarding querying data > from a table, where the table name is random. For example, I have a > database possessing upwards of 41 tables named, TAB10, TAB11,..., > TAB50, and would like to query each table. Consider instead having a single table with an extra column containing values 10, 11, ... , 50. You'll have no end of trouble with your current design. > Question: Within the C programming environment, is it possible to > prepare a sqlite SELECT query with a random table name as a > variable/parameter? No. Which is precisely why you should reconsider your design. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error 14 - SQLITE_CANTOPEN
Otto Grunewald wrote: > When an error message is returned by sqlite3_exec the documentation > states that you have to call sqlite3_free (sqlite3_free(void*)). > > My question is the following: > > Where do I get the void pointer from? You do sqlite3_free(errmsg), of course. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can multi-column index optimize my operation?
Kermit Mei wrote: > The mostly useful SQL sentence is (MyDevId is known by user): > > SELECT * FROM KeyItem WHERE HomeDevId=MyDevId order by pageNum; > > Now, the items of this table may be more than 100,000 lines. If I use > the following Index, can it improve the previous query's speed? > > CREATE INDEX index_KeyItem_HP ON KeyItem(HomeDevId, pageNum ASC); Yes, this should help. > I use index firstly, so I don't know how to manipulate it? Just > create a index like this, or any more operation is needed? No, just creating the index should be enough. SQLite should use it automatically if it's beneficial for a particular query. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About the substr(x,y,z) core function
A.J.Millan wrote:
> May be of interest to add in the doc, that, when the second argument
> is negative, "the the first character of the substring is found by
> counting from the right rather than the left", but remember that the
> last actual character is -2, because -1 is just the ending NULL.
select substr('123', -1, 1);
returns '3'. The last character is NUL for you because you chose to
store NULs as part of your strings in the database - likely as a result
of a bug in your program. I bet length() also returns a result one
greater than what you'd expect just looking at your strings.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] leaving database connection open
Ricardo Ayres Severo wrote: > My question is if I would face any problem by opening the database > when my application starts and actually never closing it. I don't do > any access across threads. You shouldn't have any problems with this. It's a pretty common access pattern (except that most applications do close the connection when terminating). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Functions to convert dates with non-English month name?
Gilles Ganault wrote:
> Before I go ahead and write a script to loop through all the rows, I
> was wondering if SQLite supports functions to convert DD MM into
> the MySQL-friendly -MM-DD, and whether those functions are
> localized so that it understands month names in languages other than
> English?
SQLite supports such functions in the sense that you can write a custom
function that does anything you want, and use it in your statements.
> Here's an example:
>
> SELECT dateinscription, dateconnexion FROM membres LIMIT 1;
> 26 Mai 2007|17 Août 2009 - 09h20
>
> I'd like to update the row into "2007-05-26" and "2009-08-17 09:20",
> respectively.
If you need to do it once, you can do something like
update membres set dateinscription=
substr(dateinscription, -4) || '-' ||
(case substr(dateinscription, 4, length(dateinscription) - 8)
when 'January' then '01' when 'February' then '02' ...
when 'December' then '12' end) || '-' ||
substr(dateinscription, 1, 2);
Substitute correct month names. Converting dateconnexion is left as an
exercise for the reader.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_column_origin_name and AS Clause
Dinu Scheppelmann (DAISY) wrote: > Unfortunately when I get the column names by function > sqlite3_column_origin_name(), the result columns have the names > "DokId", "Name" and "Name" again - instead of "Id", "PatientName" and > "Name"!! You want sqlite3_column_name Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a value if constraint violated
andrew fabbro wrote: > What is the most efficient way to code either a trigger or the app so > that it increments a "count" field if the "id" field exists? > > For example, say you have a table with fields of "id" and "count". > > First row is an id of 3, so "count" is set to 1. > Next row is an id of 4, so "count" is set to 1. > Third row is an id of 3 again, so "count" is incremented to 2. insert or replace into mytable(id, count) values (:id, coalesce((select count from mytable where id=:id), 0) + 1); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Increment a value if constraint violated
Pavel Ivanov wrote: >> insert or replace into mytable(id, count) >> values (:id, coalesce((select count from mytable where id=:id), 0) + >> 1); > > I guess it doesn't work as OP intended: > > sqlite> select * from t; > id|count > 1|1 > 1|2 > 1|3 > 1|4 Are you sure that was the OP's intention? His pseudocode suggests to me that id is supposed to be unique, and the count should reflect how many times this particular id was inserted. He definitely mentions SQLITE_CONSTRAINT, while your table doesn't appear to be constrained in any way. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Copying an open db file
Angus March wrote: > I want to copy a db file while it is still open, and I'm wondering how > safe that is. It would go something like this: > > 1. Lock exclusively with PRAGMA locking_mode=EXCLUSIVE; Many process > are accessing the db afterall > 2. UPDATE a_table SET a_column=0; > 3. After finalizing (I'm using the C API) the queries in 1 and 2, > but before closing the connection to the db, I would copy the db > file. Why not just run BEGIN EXCLUSIVE before copying, and ROLLBACK (or COMMIT, doesn't matter since you made no changes) afterward? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Copying an open db file
Angus March wrote: > Igor Tandetnik wrote: >> Angus March wrote: >> >>> I want to copy a db file while it is still open, and I'm wondering >>> how safe that is. It would go something like this: >>> >>> 1. Lock exclusively with PRAGMA locking_mode=EXCLUSIVE; Many >>> process are accessing the db afterall >>> 2. UPDATE a_table SET a_column=0; >>> 3. After finalizing (I'm using the C API) the queries in 1 and 2, >>> but before closing the connection to the db, I would copy the >>> db file. >>> >> >> Why not just run BEGIN EXCLUSIVE before copying, and ROLLBACK (or >> COMMIT, doesn't matter since you made no changes) afterward? >> > >Who knows? SQLite might cache things in userspace, or making > important changes that only a connection close would call. If BEGIN EXCLUSIVE succeeds, it means there are no outstanding transcations on any other connection and all changes are committed to the disk surface. I'm not sure I understand the scenario you are concerned about. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] having problem in creating new database in MAC OS
shakeeb khan wrote: > i want to make a database in sqlite using MAC terminal but having > problem in creating.kinldy tell me a proper syntax > > i was trying : "sqlite>sqlite mydb.db " > > it shows me "-->" and hault the program kinldy tell me what is a > proper way of creating database in sqllite You first ran "sqlite" without parameters, so now you are within sqlite command line. Now you are trying to run "sqlite mydb.db" - but that's not a valid SQLite command, it's a shell command. So figure out what you want to do. From the shell, run "sqlite mydb.db". >From inside sqlite command line, run ".open mydb.db" Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date range in the WHERE clause
Angus March wrote: > I have a table where I need to record the date of each insert. > Sometime later I'll then delete all rows that were inserted more than > 90 days ago. Is it possible to do this w/out performing a table scan? Create an index on the date column. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date range in the WHERE clause
Angus March wrote:
> Igor Tandetnik wrote:
>> Angus March wrote:
>>
>>> I have a table where I need to record the date of each insert.
>>> Sometime later I'll then delete all rows that were inserted more
>>> than 90 days ago. Is it possible to do this w/out performing a
>>> table scan?
>>>
>>
>> Create an index on the date column.
>>
>
>You're sure that's all there is to it? I haven't been able to make
> use of the EXPLAIN results.
Have you tried EXPLAIN QUERY PLAN?
>Also, I don't know how to specify a date based on what would be an
> INTERVAL in MySQL.
SQLite doesn't have a dedicated data type for representing dates or
times. You can choose your own format. Some are better supported than
others by built-in date/time functions:
http://www.sqlite.org/lang_datefunc.html
For the sake of argument, let's assume you've chosen to represent your
dates as strings in the form -MM-DD. Note how the usual string
lexicographical comparison also orders dates correctly.
> How do you express dates that are older than 90
> days ago?
WHERE mydatecol < date('now', '-90 days')
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime comparison with custom format
t-master wrote:
> I have string in a table representing a DateTime.
> The format is 21.08.2009 00:25:00
I recommend you change the format. Yours is custom-designed to make your
life miserable.
> And I would like to compare it to "now"
select case when
substr(T, 7, 4)||'-'||substr(T, 4, 2)||'-'||substr(T,1,2)||substr(T,
11) > datetime('now')
then 'future' else 'past' end
from (select '21.08.2009 00:25:00' as T);
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reverse string comparison for searches
Shaun Seckman (Firaxis) wrote: >Currently, in my database I'm storing thousands of > strings that are formatted in such a way where they share similar > prefixes (i.e. TXT_KEY_FOO, TXT_KEY_BAR). Sadly, this format cannot > be changed so I was wondering if it would be possible and perhaps > faster to have SQLite perform reverse string comparisons for looking > up specific strings. You can write a custom collation that compares the strings the way you want, then build an index and perform searches based on that collation. See http://sqlite.org/c3ref/create_collation.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DateTime comparison with custom format
t-master wrote: > the problem is, this db is created by another program and I don't > have the access to change the format What do you mean, don't have access? Can't you just run an UPDATE statement once, that changes all the fields to use a different format? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can sqlite result be sorted by using "prepare-step" API
nick huang wrote: > For example, query statement is something like "select * from > sometable order by somefield;" and we call prepare followed by step. > And are all results returned from "step" in order of "somefield"? Of course. What would be the point of specifying "order by somefield" otherwise? > As I read the document, it seems the step will return the first > awailable row ASAP. That is why I wonder the sorting is not possible > as according to what we learned from books the sorting of dataset is > done at the last stage of SQL query when all result set is available. ASAP stands for "as soon as possible". For a query with ORDER BY clause, "possible" is after the whole resultset is retrieved and sorted (assuming the order cannot be satisfied using an index). > However, this also seems to contradictive to that all other query API > like "exec", "getTable" etc. which all support "sorting" are all > based on prepare-step. Therefore the only conclusion is that "exec", > "getTable" etc. retrieve dataset and sort by themselves after they > call "prepare-step". You can look at the source code for sqlite3_exec and sqlite3_get_table, and convince yourself that they do no such thing. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?
Atul_Vaidya wrote: > Hi, I have three tables, > 1. Table Entity_xdata containing following fields >> Entity_id|Layer|grpuid| > > 2. Table, group_xdata_pipe containing following fields >> grpuid|LNV| > > 3. Table group_id_vs_reggappname containing following fields >> grpuid|reggappname| > > Now, I need to Fire a query to SQlite where in I get the list of all > the distinct LNVs. Currently I achieve it by following two query > commands as follows > SELECT DISTINCT Entity_xData.grpuid from Entity_xdata INNER JOIN > group_id_vs_regappname ON(Entity_xdata.grpuid = > group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = > 'CPD1') > I get the grpuids using this command and then i use the grpuids that > i get from this query, as an input to my next query, something like > this ... > > SELECT DISTINCT Line_Number_View FROM (SELECT grpuid,line_number_view > FROM group_xdata_pipe WHERE grpuid = '%s' ORDER BY grpuid ASC)ORDER BY > Line_Number_View ASC",query_result[x]) > > My question is > Is it possible to combine both these querries into one ? SELECT DISTINCT Line_Number_View FROM group_xdata_pipe WHERE grpuid IN ( SELECT Entity_xData.grpuid FROM Entity_xdata INNER JOIN group_id_vs_regappname ON ( Entity_xdata.grpuid = group_id_vs_regappname.grpuid AND group_id_vs_regappname.reg_appname = 'CPD1') ) ORDER BY Line_Number_View ASC; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert into 2 tables
Kit wrote: > Hi there, > > I have single table: > CREATE TABLE booklist(author TEXT, book TEXT); > --- > Tolkien The Hobbit > Hemingway The Old Man and the Sea > VerneTwenty Thousand Leagues under > Hemingway A Farewell to Arms > Tolkien The Lord of the Rings > > I want split it into 2 tables: > CREATE TABLE authors(author TEXT); > CREATE TABLE books(authorID INT, book TEXT); > CREATE INDEX i_authors ON authors (author); > INSERT INTO authors SELECT DISTINCT author FROM booklist; > INSERT INTO books (authorID, book) > SELECT authors.rowid, booklist.book FROM authors,booklist > WHERE authors.author=booklist.author; > - > The algorithm run fine (maybe you know better), but when I want insert > next booklist with one the same author, author will be added twice. > Create unique index on authors block all adding rows. You could do this: INSERT INTO authors SELECT DISTINCT author FROM booklist where author not in (select author from authors); Or else, create the unique index on authors.author as originally planned, then use INSERT OR IGNORE to skip over errors. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] about journal file
Zhanjun You wrote: > I can not find how to implement the method to deal with journal > files What do you mean by "deal" here? What exactly are you trying to do with them, that you cannot achieve simply by beginning and then committing a transaction? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional triggers
Dennis Volodomanov wrote: > Is it possible to create such an AFTER INSERT trigger that updates > certain fields in a table based on the actual data being inserted? > > Let's say: > > CREATE TABLE abc(TypeID INTEGER) > CREATE TABLE abcCount(TypeCountA, TypeCountB) > > CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when abc.TypeID == 1 > */ BEGIN > UPDATE abcCount SET TypeCountA=TypeCountA+1; END > > CREATE TRIGGER CountTypeB AFTER INSERT ON abc /* when abc.TypeID == 2 > */ BEGIN > UPDATE abcCount SET TypeCountB=TypeCountB+1; END create trigger CountTypeA after insert on abc when new.TypeId=1 begin update abcCount set TypeCountA = TypeCountA + 1; end; create trigger CountTypeB after insert on abc when new.TypeId=2 begin update abcCount set TypeCountB = TypeCountB + 1; end; Or with a single trigger: create trigger CountType after insert on abc begin update abcCount set TypeCountA = TypeCountA + (new.TypeID = 1), TypeCountB = TypeCountB + (new.TypeID = 2); end; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looking for a w_char alternative for sqlite3_get_table
A.J.Millan wrote: > Because some complex manipulations in my dBase, and probably because > my weakly knowledge of the SQL, currently I'm using extensively the > sqlite3_get_table function. > > Now I'm embraced in porting the application to Unicode and need some > like a sqlite3_get_table16 version who let use a zero-terminated > UTF-16 string in its 2nd parameter. There ain't no such thing. Now would be a good time to switch to prepared statements - see sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_bind_*, sqlite3_column_* Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looking for a w_char alternative for sqlite3_get_table
A.J.Millan wrote: > In fact, actually I'm using the new style querys, and my question was > an attempt to: > > * Make sure there was no 16-bit version of the sqlite3_get_table at > function -perhaps it would be a good idea to include it in the > standard API. The reason is the same who advised include the current > version. sqlite3_get_table is included for backward compatibility, to ease porting applications written for SQLite 2.x. But SQLite 2.x didn't have sqlite3_get_table16, so there's no reason to add it now. New applications should use prepared statements instead. > * Check if someone had previously resolved the same problem for not > changing the logic of my application in the many points that I use the > function. If you believe you really need it for some reason, you can write your own. There's no black magic in sqlite3_get_table, it uses public API only. The source code is freely available in SQLite distribution. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search a Select in another Select
Terence Lorenzo wrote: > select K1.keyword > from emaildata as E1 > INNER JOIN keylocations AS L1 on L1.id_unique = E1.id_unique > INNER JOIN keywords as K1 on K1.id = L1.id_keyword > WHERE K1.keyword LIKE '%word%' or K1.keyword LIKE '%word2%' The second test is redundant. Everything that matches '%word2%' would also match '%word%' > This query finds all matching keywords > > select > E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset > from emaildata as E1 > left join subjects on id_subject = subjects.id > left join recipients as R1 on E1.id_from = R1.id > left join recipients as R2 on E1.id_to = R2.id > left join chunkinfo as C1 on E1.id_chunk = C1.id; > > this query gets all the info about an email > > > Now I'd like to mix the 2 queries; I want to limit the 2nd query > results with the 1st query, but I cant figure it out select E1.datetime,R1.email,R2.email,subject,E1.[size],E1.offset,C1.id_block,C1.[size],C1.compression,C1.offset from emaildata as E1 left join subjects on id_subject = subjects.id left join recipients as R1 on E1.id_from = R1.id left join recipients as R2 on E1.id_to = R2.id left join chunkinfo as C1 on E1.id_chunk = C1.id where E1.id_unique in ( select L1.id_unique from keylocations AS L1 INNER JOIN keywords as K1 on K1.id = L1.id_keyword WHERE K1.keyword LIKE '%word%' ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] update - select
Gerald Ebner
wrote:
> the given syntax is ANSI sql ("row-value constructors"),
> see also the discussion at
> http://sqlblog.com/blogs/hugo_kornelis/archive/2008/03/10/lets-deprecate-update-from.aspx
>
> Is it likely that row-value constructors will be implemented in the
> (near) future ?
I was curious as I've never heard of row-value constructors before. So
I've looked at SQL92
(http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt). The
document does define the concept of row value constructor - but does not
appear to allow their use in UPDATE statement the way you show:
::=
UPDATE
SET
[ WHERE ]
::=
[ { }... ]
::=
::=
::=
|
| DEFAULT
Perhaps this is changed in more recent editions of the standard (which
don't seem to be publicly available). If not, your proposed syntax
appears to be non-standard after all, cited article notwithstanding.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to Select using results from other selects in a single SQlite querry statement ?
Atul_Vaidya wrote: > I want to make this query faster, for this, i created > indexes for Entity_Id Entity_Id doesn't appear anywhere in the query. How is an index on it supposed to help? > and grpuid Which of them? There are three tables, all having a column named grpuid. Run the query with EXPLAIN QUERY PLAN in front. The output will tell you which indexes, if any, SQLite uses for each join. > and also used a pragmas : > PRAGMA journal_mode = MEMORY; > PRAGMA synchronous = OFF; > PRAGMA temp_store = MEMORY; > PRAGMA count_changes = OFF; > PRAGMA cache_size = 12000; These pragmas are mostly used when you are writing to the database. They do little to speed up SELECTs. > I also do Begin Transaction and End Transaction Same thing - this doesn't help SELECTs. > 2. Entity_ID is a Primary Integer Key Then there's already an index on it. No reason to create a second one. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Wilson, Ronald wrote: > Creating a new thread for this topic... I'm confused by affinity. > Here is my munging of Pavel's examples from another thread: > > --- sql.txt --- > .echo on > create table "d" (i); > create table "n" (i numeric); > create table "i" (i integer); > insert into "d" values (1); > insert into "n" values (1); > insert into "i" values (1); > select * from "d" where i=1; > select * from "n" where i=1; > select * from "i" where i=1; > select * from "d" where i='1'; > select * from "n" where i='1'; > select * from "i" where i='1'; > .echo off > > SQLite version 3.6.10 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .read sql.txt > create table "d" (i); > create table "n" (i numeric); > create table "i" (i integer); > insert into "d" values (1); > insert into "n" values (1); > insert into "i" values (1); > select * from "d" where i=1; > 1 > select * from "n" where i=1; > 1 > select * from "i" where i=1; > 1 > select * from "d" where i='1'; > select * from "n" where i='1'; > 1 > select * from "i" where i='1'; > 1 > .echo off > sqlite> > > Based on the documentation (http://www.sqlite.org/datatype3.html) > column affinity does not limit the types of data that can be stored, > it merely prefers types based on documented coercion rules. > > So I don't understand why *any* of the last 3 selects should return a > value at all. The answer is in the document you cite, section 3 "Comparison Expressions": SQLite may attempt to convert values between the numeric storage classes (INTEGER and REAL) and TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the nominal affinity assigned to the expressions on either side of the binary operator. Affinities are assigned to expressions in the following cases: - An expression that is a simple reference to a column value has the same affinity as the column it refers to. Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions. - An expression of the form "CAST( TO )" is assigned an affinity as if it were a reference to a column declared with type Conversions are applied before the comparison as described below. In the following bullet points, the two operands are refered to as expression A and expression B. Expressions A and B may appear as either the left or right operands - the following statements are true when considering both "A B" and "B A". - When two expressions are compared, if expression A has INTEGER or REAL or NUMERIC affinity and expression B does not, then NUMERIC affinity is applied to the value of expression B before the comparison takes place. - When two expressions are compared, if expression A has been assigned an affinity and expression B has not, then the affinity of expression A is applied to the value of expression B before the comparison takes place. - Otherwise, if neither of the above applies, no conversions occur. The results are compared as is. If a string is compared to a number, the number will always be less than the string. Expression '1' has no affinity. d.i has no affinity either, so in d.i='1' no conversion takes place; since d.i has a value of type integer, it is never equal to a string. On the other hand, n.i and i.i have NUMERIC and INTEGER affinity, correspondingly. So '1' is coerced to NUMERIC value of 1, and the equality test is satisfied. > If there is some type conversion going on in the > comparisons, why did the fourth select return no results? No affinity, no conversion. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Wilson, Ronald wrote: > Sigh, I thought I had read all the applicable sections. Thanks Igor. > > Though, now I'm curious what your opinion on this is: > >> SQLite version 3.6.10 >> Enter ".help" for instructions >> Enter SQL statements terminated with a ";" >> sqlite> create table none (i NONE); >> sqlite> insert into "none" values(1); >> sqlite> select * from "none" where i='1'; >> 1 >> sqlite> I don't have an opinion on this. Should I? Is there a question in there somewhere? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] confused about column affinity
Wilson, Ronald wrote: >>> Though, now I'm curious what your opinion on this is: >> >> I don't have an opinion on this. Should I? Is there a question in >> there somewhere? >> > The documentation refers to the column affinities TEXT, NUMERIC, > INTEGER, REAL, and NONE, but NONE is not identified as a column > affinity by the SQL parser, which treats it as NUMERIC. Column affinity is not part of the syntax - declared type is. The affinity is inferred from the type. Column affinity is a semantic, rather than a syntactic, concept, so it is meaningless to state whether or not a parser identifies one. It is true that a declared type of NONE is not treated in any way specially by SQLite, and results in a column with NUMERIC affinity - just like a declared type of, say, BLAH would. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE only if there's no error before
Oliver Peters wrote: > /* the following actions should be > > 1. INSERT > > 2. DELETE everything in table a if the INSERT worked fine > > */ Can't your application check the return code when running INSERT, and not perform a DELETE if INSERT failed? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] STL and SQLite
Atul_Vaidya wrote: >How to store a multimap in SQlite database ? > My requirement is that i want to store a multimap in a > column of the SQlite table That's a strange and unusual requirement to have. Why do you believe you have it? What are you really trying to achieve? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not able to properly inner join?
Aaron Drake wrote: > The column was originally populated with both numeric and NULL > values. I > did not specify what type of data would be in the column, preferring > to > let SQLite dynamically type it. However in this case it has come back > to > bite me since it appears that any column with both numeric and NULL > values will be considered as TEXT. The "type" of the column is not determined in any way by its contents. For details, see http://www.sqlite.org/datatype3.html I bet you inserted your data as text in the first place, as in INSERT INTO AlertErrors(..., subCode) VALUES (..., '0') or something equivalent. Try running this: update AlertErrors set subCode = cast(subCode as integer); then run your ogirinal query again. Once you get this working, examine the parts of your program where you populate AlertErrors table (and perhaps other tables), and make sure you insert integers, and not strings, wherever you expect to get integers back later. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DELETE only if there's no error before
Oliver Peters wrote: >> Can't your application check the return code when running INSERT, and >> not perform a DELETE if INSERT failed? > > don't know, if I can do this with OpenOffice (Base) - but I think I'd > to program this (Starbasic) and I'm not really fit in programming (I > only script from time to time in awk). So my first idea was to solve > the problem in SQL - I assume your answer is that there's no way to > do it only with sqlite? I'm not sure what you mean by "only with sqlite". SQLite doesn't do anything by itself, it requires a host application to drive it. How exactly do you run SQL statements? Is there no indication in your SQLite binding of whether the statement succeeded or failed? If your application ignores errors from INSERT and proceeds with DELETE anyway, there's nothing SQLite can do to stop you. It can only assume that you know what you are doing, and do what you tell it to do. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Redundant conditions - are they parsed out?
Dennis Volodomanov wrote: > I was wondering whether the SQLite parser will parse out redundant > conditions out of an SQL statement or will it process them as if they > are all unique? In my experience, SQLite doesn't eliminate such redundant clauses, nor remove trivial conditions (like 1=1 or 1 != 0), nor shortcircuit logical operations, nor hoist common subexpressions (those that don't depend on the current row) out of the loop. An expression is translated into VDBE program in a very literal manner. > I'm building dynamic SQL statements and sometimes I can't avoid > (well, without re-working a lot of code) things like: > > SELECT * FROM TABLEA WHERE COLUMNA=1 AND COLUMNA=1 AND COLUMNA=1 AND > ( COLUMNB=2 OR COLUMNC=3 ) > > Does it hurt performance having 3 COLUMNA statements like that in any > way? I suspect that any inefficiency introduced by that will be immeasurably small. But, if in doubt, test it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Redundant conditions - are they parsed out?
Dennis Volodomanov wrote: >> I suspect that any inefficiency introduced by that will be >> immeasurably small. But, if in doubt, test it. > > Not sure how to test it exactly at the moment You run the query with the redundancy, and the equivalent query without, and time them. Run the same query many times in a loop if a single execution is too fast to measure. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
Iker Arizmendi wrote: > The question is whether a client-server design is /necessary/ to > efficiently implement higher concurrency. It appears to be easier > to do so with a client-server model, but is such a model required? > Are there functions performed by a server process that cannot be > carried out at all without it? On a high, theoretical level, the advantage of a single server process is that it has more context. It knows intimate details about everything going on in the system, and can manage concurrent tasks more efficiently using this information (e.g. use fine-grained locks). On the other hand, multiple cooperating processes share only a limited amount of information; each process knows very little beyond what it itself is doing. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
Iker Arizmendi wrote: > A single server process can be used to track "global" information, but > shared memory mapped by unrelated processes might do as well. For > example, a common mmap'ed file might serve to track lock info for > each process. > Of course, such a scheme would have to support handling of crashed > processes without burdening the common case. But assuming it did, is > this the main obstacle? A better question may be - what's the benefit? What's the point of the exercise? What's the advantage over the traditional server architecture? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to get "One to many" in 1 row of a table ?
Stef Mientki wrote: > I'm just an amateur struggling with databases, > so forgive if I ask a question every database user should know. > > The situation is like this (simplified): > > I've a table with persons: name, address, phone, etc. > I've another table with vehicles: car_or_bike, brand, type, > build-year, etc. The 2 tables are bounded by bind table: person_ID, > vehicle_ID > > I know that every person has 1 car and 1 bike > > I want to show up a table, with 3 columns: > persons.name vehicle.brand[car] > vehicle.brand[bike] select p.name, v1.brand, v2.brand from persons p join bind b1 on (p.person_ID = b1.person_ID) join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and v1.car_or_bike = 'car') join bind b2 on (p.person_ID = b2.person_ID) join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and v2.car_or_bike = 'bike'); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How long should Insert take?
Rod Dav4is wrote: > *Insert rate*: 7/sec. Run batches of inserts within a single transaction. It's not the insert itself that's slow, it's the process of committing a transaction. By not starting an explicit transaction, you commit an implict one on every statement. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] One more SQL statement question
Dennis Volodomanov wrote: > I'm trying to create a trigger that would delete unreferenced rows > from a table. > > Let's say the schema is like this: > > TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER ); > TABLEB ( ID INTEGER PRIMARY KEY, DATA ); > > And let's say the data is like this: > > TABLEA: > 1|1 > > TABLEB: > 1|dataA > 2|dataB > > Now, I update the record in TABLEA and change REFB to be REFB=2 > > I've got the following trigger, which doesn't work: > > CREATE TRIGGER UpdateData AFTER UPDATE ON TABLEA > BEGIN > DELETE FROM TABLEB WHERE TABLEB.ID IN ( SELECT TABLEB.ID FROM TABLEB > LEFT JOIN TABLEA ON TABLEA.REFB=TABLEB.ID WHERE TABLEB.ID=OLD.REFB > AND TABLEB.ID NOT IN ( TABLEA.REFB ) ); > END This statement makes no sense to me. Why not simply delete from TABLEB where ID=old.REFB and ID != new.REFB; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing all columns as arguments to a function?
Jean-Denis Muys wrote: > Is it possible to pass all columns of a table as arguments to a > function in a implicit way? No. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: near "-": syntax error
rishabh wrote: > i have a stream of data coming in with constant table names and data. > one of the table names contains a '-' (minus sign). it gives error > while creating the table. > > SQL error: near "-": syntax error > something like Create table "t-t" ( a ); works but Create table t-t ( > a ); gives the above error. t-t is not a valid identifier. If you insist on naming your table (or column, or view, or trigger) this way, you must enclose the name in quotes. > similarly insert into "t-t" values (1); works, but dont know why using > mprintf it has a problem. :( What problem? How do you use mprintf? Show some code. > if i use double quotes around the table name, the table gets created, > but then insert on that table does not work. Show your insert statement. Define "doesn't work". > i am using C/C++ to do > the inserting, creating, updating etc. > if in mprintf ... i use \"%Q\" %Q adds a pair of apostrophes around the string. If you are using it for a table name, you end up with a statement like insert into "'t-t'" values (1); so the table name is 't-t' (including apostrophes), not t-t. > ... for the insert statement, it does > not it gives error saying that the table does not exist whereas the > table has been created. Table "t-t" has been created - but table "'t-t'" has not. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: near "-": syntax error
rishabh wrote: > i put a cout on the insert query string generated by mprintf. > it was actually putting the table name within quotes itself, so > putting the extra quotes was not required. %Q surrounds the name with the wrong type of quotes. %Q uses single quotes (as in 't-t'), as required in SQL for string literals. For names, you should use double quotes (as in "t-t"): %Q won't help you with that, use plain vanilla %s and put the quotes in yourself. > now the insert command is not giving any error, but the values did not > actually get inserted :( How do you determine this? I suggest you prepare and post a small sample program that reproduces your problem. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] composite PK Constraint: effects on query performance?
Tim Romano wrote: > Does SQLite 3.1.x tacitly create an index to implement composite PK > constraints, and is that index used, when possible, to enhance the > performance of joins and order by clauses? Yes and yes. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] dropping composite PK constraint
Tim Romano wrote: > I read in the archive > (http://www.mail-archive.com/sqlite-users@sqlite.org/msg31637.html) > > "You cannot create the primary key after the fact using ALTER TABLE." > > Is it possible to DROP a composite primary key constraint after the > fact > with ALTER TABLE? No. If for some reason you need to be able to add and remove unique constraint at will, don't specify the constraint in the table definition, but create a UNIQUE index on the appropriate columns instead. While you can't remove a table constraint, you can always drop an index. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a way to enter ascii hex string as a text without binding ?
yogibabu wrote: > i got some textual data where might be some disallowed characters > (php codes mostly), and I want to put it in a data base in one line, What do you mean by "disallowed characters"? Disallowed by whom? SQLite would accept any character in a string literal (single quotes require a bit of special handling). In any case, what's wrong with parameter binding? Why do you insist on not using it? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a way to enter ascii hex string as a text without binding ?
yogibabu wrote: > I was wondering if there is a hex() function why shouldn't be dehex() X'DEADBEEF' syntax is your "dehex". hex() takes a blob and produces a hex string. X'...' notation turns a hex string into a blob. If you want to convert a blob to text, there's CAST: select cast(X'303132' as text); 012 Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does SQLite have an Instr function?
RB Smissaert
wrote:
> Does SQLite have a string function that produces the first position
> of a string within another string?
> For example select Instr('abcd', 'c') would produce 3
Unfortunately, no. Of course, SQLite does provide a way for you to add
your own custom functions.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does SQLite have an Instr function?
Darren Duncan wrote:
> RB Smissaert wrote:
>> Does SQLite have a string function that produces the first position
>> of a string within another string?
>> For example select Instr('abcd', 'c') would produce 3
>> Looked in the documentation and the forum, but couldn't see it.
>
> This also isn't a simple problem since the answer would vary
> depending on your abstraction level in dealing with characters; for
> example, is a character a language-dependent grapheme, a
> language-independent grapheme, a Unicode codepoint, a byte, an
> integer, etc. If asked the length of a character string that has an
> accented letter, say, the answer would vary depending on which of the
> above abstractions they want the answer in, and for less abstracted
> answers, it is affected by what codepoints or bytes are used for the
> character, etc.
Well, there's substr() and length(), so some of these design choices
have already been made. Obviously, if instr is implemented, it should be
consistent with those. So that, if instr(a, b) == p, then substr(a, p,
length(b)) == b.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to speed up this SQL?
RB Smissaert wrote: > Have 2 tables with both one text field called term and need to run a > SQL like this, to count the records in table1 where the start of term > in table1 equals a term in table2: > > select > count(a.rowid) > from table1 a inner join table2 b on > (lower(b.term) = lower(substr(a.term,1,length(b.term Try writing the condition as a.term collate nocase between b.term and b.term || 'Z' Replace 'Z' with a character that compares above any characters that may appear in your strings. If in doubt, try using CAST(X'EFBFBF' as text) (this is U+ represented in UTF-8, and should compare greater than anything valid). Make sure there is an index with collate nocase on a.term (an index on b.term won't be helpful). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] get query from sqlite database with triggers
Sara1409 wrote:
> It is clear that those variables (new.contactnom, new.contactage,
> new.contactmobile, new.contactmail, new.contactadresse) are token as
> String, I tried many combinations (' + new.contactnom +', ' " +"
> new.contactnom "+"',:) to resolve this problem but I failed !
In SQL, string concatenation opeartor is || (two vertical bars).
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_blob_read declaration
Filipe Madureira wrote: > I have an error for sometime compiling the latest versions of SQLite > amalgamation on eVC++ 4 > error: "'Z' : use of class template requires template argument list" > > > The problem is in the declaration of "sqlite3_blob_read prototype". > It is declared in "sqlite3.h" and "sqlite3.c" as: > > SQLITE_API int sqlite3_blob_read(sqlite3_blob *, void *Z, int N, int > iOffset); You have a template class Z declared in some header that you include before sqlite3.h. And your compiler seems to be buggy - parameter names shouldn't be subject to name lookup, and thus shouldn't conflict with class names. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accessing bound variables in trigger
Rich Rattanni wrote: > Can a trigger reference the bound variables? I figure the answer is > NO. The answer is NO. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE with JOIN fails
Nightfox79 wrote: > I'm trying to update one table based on information in an other. I > have two tables, transactions (trans) and accounts (account). I want > to update the debit amount in the transaction table (trans) with vat > information from the accounts table (account) for a particular > account. Normally (ie. in other DBMS) it's no problem - the syntax is; > > UPDATE t > SET t.debit = trans_debit/a.acc_vat > FROM trans t,account a > WHERE a.acc_id=t.acc_id > > --ERROR : near ".": syntax error > > Why do I get this error? Does SQLite not support joined update > statements? No it doesn't. Make it UPDATE trans SET debit = trans_debit / ( SELECT a.acc_vat FROM account a WHERE a.acc_id = trans.acc_id ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs
Gert Cuykens wrote: > SELECT t.pid, > t.txt, > t.price, > t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' > FROM PRODUCTS t > LEFT JOIN (SELECT o.pid, > SUM(o.qty) 'qty_sold' > FROM ORDERS o) qs ON qs.pid = t.pid >WHERE t.pid = ? > > i have trouble running this statement on sqlite3 > It tels me qs.pid does not exist And indeed it doesn't. But there is a column named qs."o.pid". You may want to assign an alias to this column, just as you did with qs.qty_sold > when i rename it to pid it subtracts > the wrong values Because now it refers to t.pid, and the condition (t.pid = t.pid) is always true. Perhaps your query could be a bit clearer when written this way: select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid where t.pid = ?; or this way select t.pid, t.txt, t.price, t.qty - IFNULL( (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty from PRODUCTS t where t.pid = ?; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating custom function for recursive queries
Marcel Strittmatter
wrote:
> I implemented a custom function that returns a comma separated list of
> primary keys as a string by making recursive queries. This works well
> if I don't use subqueries. But I like to use subqueries like this
>
> SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));
>
> Is it possible to implement a custom function where the result can be
> used in a subquery like above?
No, not really. SQLite doesn't have table-valued functions. SQLite does
have the concept of a virtual table:
http://www.sqlite.org/c3ref/module.html
Maybe you could do something like what you want with those. I don't know
enough about them to comment further.
With the function as written, you can do something like this:
SELECT * FROM users WHERE
','||parents('relations', 3)||',' LIKE '%,'||id||',%';
I don't really recommend this monstrosity, just mention it for
completeness.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs
Gert Cuykens wrote: > On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik > wrote: >> Perhaps your query could be a bit clearer when written this way: >> >> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) >> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid > > This does not show me the new products that are not ordered yet Are you sure? I don't see why it wouldn't. >> or this way >> >> select t.pid, t.txt, t.price, t.qty - IFNULL( >> (select sum(o.qty) from ORDERS o where t.pid = o.pid), 0) onhand_qty >> from PRODUCTS t where t.pid = ?; >> > > I learned that this would be a performance issue doing it like that. > > http://stackoverflow.com/questions/1417889/sqlite3-get-product-onhand-quantity You "learned"? Do you mean, you measured and discovered this to be the case? The answer in that thread you are basing this claim on is largely nonsense, in my humble opinion. For one thing, your query only returns one row, so running a subselect "for every row returned" means running it once. For another, how does the poster believe joins are calculated - black magic? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs
Gert Cuykens wrote: > On Wed, Sep 16, 2009 at 8:26 PM, Igor Tandetnik > wrote: >> Gert Cuykens >> wrote: >>> On Wed, Sep 16, 2009 at 1:35 AM, Igor Tandetnik >>> wrote: >> >>>> Perhaps your query could be a bit clearer when written this way: >>>> >>>> select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) >>>> onhand_qty from PRODUCTS t left join ORDERS o on t.pid = o.pid >>> >>> This does not show me the new products that are not ordered yet >> >> Are you sure? I don't see why it wouldn't. > > select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty > from PRODUCTS t left join ORDERS o on t.pid = o.pid > > shows 1 product because I only have 1 order with that product This query is different from the one I posted. Mine had "where t.pid = ?;", yours doesn't. If you want a report for all products, add the following clause: group by t.pid; > SELECT t.pid, > t.txt, > t.price, > t.qty - IFNULL(qs.qty_sold, 0) "onhand_qty" >FROM PRODUCTS t > LEFT JOIN (SELECT o.pid, > SUM(o.qty) "qty_sold" >FROM ORDERS o) qs ON qs."o.pid" = t.pid > > shows all products Compare this statement with the one you mentioned in your original post. Lacking mind-reading abilities, I was trying to help you with the statement you actually asked about, not the one you were thinking about. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs
Gert Cuykens wrote: > select t.pid, t.txt, t.price, t.qty - IFNULL(sum(o.qty), 0) onhand_qty > from PRODUCTS t left join ORDERS o on t.pid = o.pid group by t.pid > > Works also thanks. > So you do not believe the following has a performance penalty ? > > SELECT pid, txt, price, qty-coalesce((SELECT sum(qty) FROM orders > WHERE orders.pid = products.pid),0) FROM products I don't see why it should, but when in doubt, test and measure. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date and age calculations
Craig Smith wrote:
> I am having difficulties getting precise returns using a SELECT that
> calculates the age of persons in my table. All dates are -MM-DD,
> here is my SELECT statement:
>
> SELECT db_id, Full_name, round((SELECT julianday('now') - julianday
> (birth))/365,1) FROM members WHERE date ('now') - birth > 12 AND date
> ('now') - birth < 24 AND married = 'Single' AND moved = 0;
Why the nested select? Why not just
round((julianday('now') - julianday(birth))/365,1)
> The problem is that anyone over the age of 12 does not show up in the
> return until their age is actually nearer to 12.5.
That would depend on when in the year the person was born. Your
condition "date ('now') - birth > 12" subtracts years while ignoring
months and days (the minus operator implicitly converts strings to
integers, and the conversion stops at the first non-digit character). So
the condition would be almost correct for a person born in December, but
will be one year off for someone born in January.
> However, when I
> change the SELECT to
>
> birth >= 12
>
> I pick up a few persons whose ages are between 11 and 12.
This condition is simply always true (strings compare greater than
integers, regardless of actual values). I predict if you drop it
completely you'll get the same results.
> Is there
> any way I can make the SELECT more precise?
Try this:
WHERE ( date('now') - birth - (substr(date('now'), 6) < substr(birth,
6)) )
BETWEEN 12 and 24
This expression calculates full years of age (in other words, it
increases by one each year on the person's birthday).
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serious locking/blocking issue
Angus March wrote: > I was under the impression that when a C API function attempts to get > a lock on the db that it cannot get, it blocks until it can get the > lock. Well it turns out that this isn't true. What gave you this impression in the first place? If this were true, what would be the purpose of sqlite3_busy_handler and sqlite3_busy_timeout? >Googling for 'sqlite locking block' has directed me to > http://www.sqlite.org/unlock_notify.html, which suggest that I should > be using the sqlite3_unlock_notify() function. Are you using shared cache mode? > However, it also > states that this is for multi-threaded applications. My project is a > multi-/process/ one. Then you are not using shared cache mode, and the article doesn't apply to you in any way. > Will sqlite3_unlock_notify() work for this No > or do I need to be doing something else? Yes. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serious locking/blocking issue
Angus March wrote: > What should > the callback that is passed to sqlite3_busy_handler() be doing? It should be deciding whether to continue waiting for the lock to clear, or to allow SQLite to report an error to the calling application. It should be conveying this decision via its return value. > Let's > say that sqlite3_reset() is called, which fails to achieve the lock. sqlite3_reset doesn't take any locks. > After the callback has finished, what will happen with that original > call to sqlite3_reset()? Since sqlite3_reset doesn't take any locks, a busy callback would never be invoked for it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serious locking/blocking issue
Angus March wrote: > Igor Tandetnik wrote: >> Angus March wrote: >>> After the callback has finished, what will happen with that original >>> call to sqlite3_reset()? >> >> Since sqlite3_reset doesn't take any locks, a busy callback would >> never be invoked for it. > >Well someone should tell the library on my machine that, because > it's returning locking errors I didn't say that sqlite3_reset can't return SQLITE_BUSY - only that it doesn't take locks and doesn't trigger a call to a busy handler. For historical reasons, for statements prepared with sqlite3_prepare[16], sqlite3_step reports all errors with a generic SQLITE_ERROR code. A subsequent call to sqlite3_reset or sqlite3_finalize returns the real error code (even though the reset or finalize itself doesn't fail). Use sqlite3_prepare[16]_v2 to avoid this behavior and have sqlite3_step return the real error code directly. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Angus March wrote: > Pavel Ivanov wrote: >>> Hell if I know why they use fcntl() for locks, and don't even give >>> you the option to block. >>> >> >> I think because they need to detect dead locks. BTW, I believe in >> case of dead lock even busy_handler will not be called, just >> SQLITE_BUSY is returned... >> >I guess that makes sense, in cases where multiple tables are > involved. It doesn't matter whether a single or multiple tables are involved - SQLite locks at the database level. The deadlock occurs when 1) transaction A starts to read, taking a SHARED lock; 2) transaction B prepares to write, taking a RESERVED lock; 3) transaction A now wants to write, too. At this point, transaction B is waiting for all readers (one of which is transaction A) to clear, while transaction A waits for the previous writer (which is transaction B) to complete. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Angus March wrote: >Yes, I see. So what is key to the problem is that someone tries to > change their read lock to a write lock. I guess I just thought that > the kernel that manages fcntl() would have a way of dealing with > this. Can this situation not be averted if at step 3, transaction A > releases its read lock before requesting a write lock? Then it wouldn't be much of a transaction, now would it? Imagine the classic example, where a transaction first verifies that the balance in a bank account is sufficient, then performs a withdrawal. If it relinquishes all locks between these two steps, then somebody else may record a withdrawal from that account, so that the write operation would then make the balance negative, thus violating an invariant. Of course, if that's what the application wants, it can simply perform the read and the write operations in two separate transactions. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > On 18 Sep 2009, at 9:07pm, Roger Binns wrote: > >> Simon Slavin wrote: >>> * Unicode support from the ground up >> >> SQLite already has "unicode support from the ground up". Try using >> non-Unicode strings and you'll see! > > SQLite's indexing correctly understands how to order Unicode > strings ? With ICU extension enabled and correct collation specified, yes. Note that the correct ordering of Unicode strings is locale-dependent. > It can do upper- and lower-case ordering in languages like > Hebrew ? To the best of my knowledge, Hebrew doesn't have the notion of case. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] full outer join questions
Stef Mientki wrote: > create table RT1 ( PID integer, V1 text ); > insert into RT1 values ( '684', 'aap' ); > insert into RT1 values ( '685', 'other empty' ); > create table RT2 ( PID integer, V2 text ); > insert into RT2 values ( '684', 'beer' ); > insert into RT2 values ( '686', 'other empty' ); > select RT1.*, RT2.* > from RT1 >left join RT2 on RT1.PID = RT2.PID > union >select RT1.*, RT2.* > from RT2 >left join RT1 on RT1.PID = RT2.PID >where RT1.PID IS NULL; > > Now I want to combine the columns PID, so the result would look like > > PID V1 V2 > 686from RT2 > 684 from RT1 from RT2 > 685 from RT1 select coalesce(RT1.PID, RT2.PID) as PID, RT1.V1, RT2.V2 ... Modifying the second select clause is left as an exercise for the reader. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin
wrote:
> On 18 Sep 2009, at 9:57pm, Igor Tandetnik wrote:
>
>> Simon Slavin
>> wrote:
>>> On 18 Sep 2009, at 9:07pm, Roger Binns wrote:
>>>
>>>> Simon Slavin wrote:
>>>>> * Unicode support from the ground up
>>>>
>>>> SQLite already has "unicode support from the ground up". Try using
>>>> non-Unicode strings and you'll see!
>>>
>>> SQLite's indexing correctly understands how to order Unicode
>>> strings ?
>>
>> With ICU extension enabled and correct collation specified, yes. Note
>> that the correct ordering of Unicode strings is locale-dependent.
>
> Okay. So I create an indexed database in one locale. I have a
> thousand records in there. The indexes are created using the locale I
> set. I then send a copy of this database to a client in another
> place, and the client has different locale settings. The client adds
> another thousand records with their locale settings. What happens
> when I use WHERE clauses with '<' or '>' ? Does the system vaguely
> work, or does it get a mess ?
I'm not sure what you mean by "the client has locale settings". Has them
where, and how are these settings supposed to affect SQLite database?
When you create a table or an index, you may explicitly specify the
collation each field should use. Like this:
SELECT icu_load_collation('he_IL', 'hebrew');
create table myTable(myField text collate hebrew);
-- and/or
create index myIndex on myTable(myField collate hebrew);
Using ICU extension does require certain discipline. You must run
icu_load_collation soon after opening the database, and all users of the
database must agree to map the same identifiers to the same locales (the
best way to achieve that is probably to make collation name the same as
locale name: SELECT icu_load_collation('he_IL', 'he_IL'); ). Mapping
the same collation identifier to different locales may indeed result in
corrupted indexes.
For more details, see
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > Thanks to you and Jay for explanations. I hadn't encountered ICU at > all before. Your descriptions make perfect sense and are very > interesting since ICU is a good attempt to get around one of the > fundamental problems of Unicode. Out of curiosity - what do you consider a fundamental problem of Unicode? The fact that different people may prefer their strings sorted differently? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Understanding ICU collations: Where needed?
Lukas Haase wrote: > When I use the ICU module and I create a collation, where is the > collation "stored"? The collation name is part of the database schema. The actual comparison algorithm is not represented in the database in any way: your application must ensure that all clients install compatible collations behind that name. > Do I have to call icu_create_collation everytime I > start up the database? Every time you open a connection, yes. > Or just one time? Or only when inserting data? > What happens if I omit the call? When you try to prepare a statement that needs the collation, you'll get an error "unknown collation". > This leads to the second question: If I only insert data on the Linux > side and I have an index on the column using the ICU collation, do I > need to have ICU support on client side? Yes, if you run SELECT statements that perform comparisons or sort on columns associated with this collation. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading "packed" data
Mohit Sindhwani wrote: > Hi! An embedded SQL-based database that we used earlier had a concept > of packed fetches - this would mean that we could create a certain > buffer for results, prepare a query, execute it and read back the > results in "groups" of 10 or 20 or 1000 (or "n") results per call.. > this was significantly faster than reading the results one at a time. > > Is there a parallel? No. With SQLite, it wouldn't be benefitial. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
Wenbo Zhao wrote: > I was talking about this example by > 2009/9/19 Igor Tandetnik > "Imagine the > classic example, where a transaction first verifies that the balance > in a bank account is sufficient, then performs a withdrawal. If it > relinquishes all locks between these two steps, then somebody else may > record a withdrawal from that account, so that the write operation > would then make the balance negative, thus violating an invariant." > > What I want to say is in this example, there should be only one step, > because the transaction knows it will do 'write'. Not necessarily. It won't do the write if it discovers in the first step that the balance is, in fact, insufficient. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Guillermo Varona Silupú wrote: > I want to insert this text: > > 1' equivale a 12" > > cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a > 12"") What language is that? ':=' suggests Pascal, but string literals in Pascal use single quotes. In SQL, string literals are surrounded by single quotes: if you need a single quote as part of the string, you enter two of them in a row. Double quotes don't require any special handling inside a string literal. INSERT INTO Tabla1 (Code,Equiv) VALUES (10, '1'' equivale a 12"') Now that you have a valid SQL statement, figure out how to represent it as a string literal in whatever host language you are using. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite date / time functions
Alexey Pechnikov wrote: > Please speak is now correct to store 'start of month' dates in > julianday format? Was it at some point "incorrect" to do so? Yes, you can store any date in julian day format, start of month or otherwise. > Does SQLite now uses internal reprezentation of > microseconds for the julianday and store it as long integer or float? Julian day format is a double, with date represented by the whole part and time by the fractional part. A double has 53 bits of mantissa, IIRC, of which some 22 bits are needed to represent dates in recent past and future, leaving 31 bits for time. This gives a resolution of about 40 microseconds - so no, the format is incapable of representing times accurate down to a microsecond. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite date / time functions
Alexey Pechnikov
wrote:
> On Monday 21 September 2009 15:59:47 Igor Tandetnik wrote:
>>> Please speak is now correct to store 'start of month' dates in
>>> julianday format?
>>
>> Was it at some point "incorrect" to do so? Yes, you can store any
>> date in julian day format, start of month or otherwise.
>
> Are correct selects like as
> SELECT * from tbl_name where date = julianday('now','start of month');
I see no reason why not. Note that the condition will only hold when
"date" column represents midnight on the corresponding day.
> Equal condition for dates is work now but is it safety?
In general, comparing double values for exact equality is risky.
However, fractions that are powers of two are represented exactly in a
double, so the comparison would work for values that represent, for
example, 3, 6, 9 and 12 o'clock (both AM and PM).
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Shaun Seckman (Firaxis) wrote: > * I really would like some SQL syntax sugar that will > automatically include rows referenced by foreign keys in a table. I > haven't seen any other SQL database do this as all expect you to > explicitly include the conditions and joins which can become quite the > hassle. Does NATURAL JOIN help? > * My second awesome feature request would be support for storing > prepared statements into the database file to be used at later times. http://www.mail-archive.com/sqlite-users@sqlite.org/msg30073.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date comparisons
Barton Torbert wrote:
> What you suggested sort of worked. The problem was that the two
> dates I were comparing were only 1 minute apart, but I had to put in
> '-1000 minutes' and '+1000 minutes' to get the test to work.
>
> My two data values are;
> 2009-04-13T12:19:00.000
> and
> 2009-04-13T12:18:00.000
datetime() produces the value without 'T' in the middle - like this:
2009-04-13 12:09:00. So, as long as the date part is the same, the value
with a space would alphabetically precede the one with 'T'.
In place of datetime, try
strftime('%Y-%m-%dT%H:%M:%f', t1.dateitem1, '-10 minutes')
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date comparisons
Barton Torbert wrote:
> Even though this is a bit more complicated, it does seem to work
> correctly.
>
> SELECT t1.realdate1,
> t2.realdate2
> from test_table_1 t1 left outer join test_table_2 t2 where
> strftime ('%s', t2.realdate2) between strftime ('%s',
> datetime(t1.realdate1, '-1 minutes') ) and strftime ('%s',
> datetime(t1.realdate1, '+1 minutes') )
This would lose any benefit of the index on realdate2, in case you have
one.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update vs Insert
Barton Torbert wrote: > I am having problems with the update command (through the interface > library). In most databases if you issue an update to a record that > does not exist, it simply adds a new record. No database I've ever heard of does that. It must be a feature of the library you are using. Contact its authros for support. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL language question
D. Richard Hipp wrote: > Consider the following SQL: > > CREATE TABLE t1(x integer); > INSERT INTO t1 VALUES(123); > CREATE TABLE t2(y integer REFERENCES t1 ON UPDATE SET NULL); > INSERT INTO t2 VALUES(123); > > UPDATE t1 SET x=x; -- key line: Is this considered an "update" > of t1.x? > > SELECT * FROM t2;-- did t2.y get nulled by the previous > statement? > > The question is this: Should the no-op UPDATE statement (x=x) cause > the ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or > not? Apparently not. From SQL 92 (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt): 11.8p6 If an is specified and a non-null value of a referenced column in the referenced table is updated to a value that is *distinct* from the current value of that column, then ... [Long descripton omitted. Emphasis mine.] Note: Otherwise, the is not performed. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem if IF NOT EXISTS
Andrew wrote: > I didn't see a way to search the mail archive, so hopefully this > hasn't been addressed repeatedly in the past... > > $ sqlite data.dat > SQLite version 2.8.17 > Enter ".help" for instructions > sqlite> .schema > sqlite> CREATE TABLE IF NOT EXISTS t ( foo int ); > SQL error: near "NOT": syntax error > sqlite> IF NOT EXISTS clause was introduced in some fairly recent SQLite 3.x version. SQLite 2.8.17 doesn't support it. > I don't know why this statement isn't working, as it appears in the > documentation. SQLite 2.x is several years old. Documentation reflects the capabilities of the most recent version. > Is there a workaround? Just do a regular CREATE TABLE. If the table already exists, you'll get an error; ignore it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite reading all column data on selects.
Adam Panayis wrote: > Hi, are there plans to stop sqlite3 from reading in all column data on > specific select queries? i.e I would like sqlite to ONLY read column > data for columns which are specified in the select and where clauses. What do you mean by "reading in" here? I'm not sure I understand precisely what behavior you find objectionable. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_column_type bad value
Sebastian Bermudez wrote: > sqlite3_open > sqlite3_prepare ( "select * from t1" ) > get sqlite3_column_count(stmt) > for 1 to colcount > sqlite3_column_type(stmt,index) "always return 5" > next You need to call sqlite3_step first. sqlite3_column_type examines actual data in an actual row, but you haven't fetched any rows yet. Perhaps you meant to use sqlite3_column_decltype[16] ? Also, columns are numbered from 0. You might be thinking about parameters (sqlite3_bind_*) which are numbered from 1. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ROWID bug in SELECT with JOIN?
sub sk79 wrote: > Select with two tables joined has no _rowid_ column in select columns. > I think this is a bug - just a quick check with the community before > filing a bug report. > > select * from names, friends where first_name = fname and _rowid_ < 2; > SQL error: no such column: _rowid_ So, which table's _rowid_ is this statement supposed to access? The statement is wrong and shouldn't run. The only issue here is that the error message is somewhat misleading - it could probably say something like "ambiguous column name". Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ROWID bug in SELECT with JOIN?
sub sk79 wrote: > select * from (select * from names, friends where first_name = fname) > where _rowid_ < 2; > > Above query works - which _rowid_ is this accessing? Well, define "works". In my experiments, _rowid_ is NULL for all rows in the subselect: select typeof(_rowid_) from (select * from names, friends where first_name = fname); But the fact that this statement compiles at all looks like a bug to me (though a very minor and unimportant one). Now, why do you care about these corner cases? Do you have a practical problem you are trying to solve? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_exec() encoding?
aj wrote: > what encoding does sqlite_exec() callback function receive data in ? > > some tests i've done seem to suggest UTF-8 > > other tests show chars U+80 to U+FF are returned as single chars with > values 128-255. (suggesting its not UTF-8) This means that these strings were inserted this way into the database. You are getting them back the way you put them in. SQLite functions that expect UTF-8 strings (e.g. sqlite3_bind_text) don't actually verify that the encoding is valid. It is your responsibility to pass a well-formed UTF-8 sequence. Apparently, you (or whoever filled the database with data) didn't. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
