Re: [sqlite] Order of rows in a VALUES select expression
I am not in front of a sqlite equipped system at the moment, but wouldn't something like this probably work? select * from (values ...) order by ... On Thu, Jan 30, 2014 at 1:28 PM, Zsbán Ambrus wrote: > The future release sqlite 3.8.3 adds VALUES statements as a shortcut > form of SELECT statements. This shortcut simply creates a results set > made of any number of rows, any number of (unnamed) columns in each, > and all the values given by separate explicit expressions. > > My question is whether sqlite guarantees that the order of result rows > from such a statement is the same as the order they are listed in the > statement. The docs at "http://www.sqlite.org/draft/lang_select.html"; > seem to imply that the order is undefined, the engine is free to > choose whatever order it wishes. Further, you can't even use an > explicit ORDER BY clause on a VALUES statement. If this is the case, > what's the easiest way to generate a set of fixed number of rows like > VALUES does but in an order of your choice? > > Ambrus > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Scott Robison ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Preloading and ASP.Net debugging
I'm not a ".net" guy but, I do use visual studio. One thing I do is have several post-build steps that copy files around to where I need them before I debug. You can define "post build" steps in the project options. Thursday, January 30, 2014, 7:16:55 PM, you wrote: JM> Eric Schultz wrote: >> >> When I debug in Visual Studio, IIS Express copies all of the >> Assemblies in my "bin" directory to a folder into the "AppData\Temporary >> ASP.Net Files" folder. Unfortunately, it does not copy the >> System.Data.SQLite.dll.config file or the x86 and amd64 subfolders of bin >> which contain my interop files. >> JM> I've seen this behavior before as well. This appears to be a limitation of JM> Visual Studio. Ideally, it *should* copy the configuration file and all the JM> sub-folders as well. >> >> Is there any way so the preloading base directory is from where the >> System.Data.SQLite.dll file originally came from when I can't set the >> environment variables or have a config file in the same folder as the >> executing assembly (which I can't in this case). >> JM> Why can't you set environment variables? The currently supported workaround JM> for this limitation of Visual Studio is to use the JM> "PreLoadSQLite_BaseDirectory" JM> environment variable (or configuration file setting). JM> Of course, I'm always open to alternative suggestions on how to work around JM> issues like these in a portable and robust way. JM> -- JM> Joe Mistachkin JM> ___ JM> sqlite-users mailing list JM> sqlite-users@sqlite.org JM> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
What version of SQLite and what indexes have you created? (and, if you have relevant indexes, have you run analyze?) >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of E. Timothy Uy >Sent: Thursday, 30 January, 2014 09:25 >To: General Discussion of SQLite Database >Subject: [sqlite] order of = in join > >#1 - f.term = t.term >SELECT m.term AS term, m.definition AS definition >FROM >(SELECT t.term, e.definition >FROM Terms_content t >INNER JOIN Favorites f ON f.term = t.term >LEFT JOIN TermEntries te ON te.termid = t.docid >LEFT JOIN Entries e ON e.docid = te.entryid) AS m >LEFT JOIN Favorites f ON f.term = m.term" > >#2 - t.term = f.term >SELECT m.term AS term, m.definition AS definition >FROM >(SELECT t.term, e.definition >FROM Terms_content t >INNER JOIN Favorites f ON t.term = f.term >LEFT JOIN TermEntries te ON te.termid = t.docid >LEFT JOIN Entries e ON e.docid = te.entryid) AS m >LEFT JOIN Favorites f ON f.term = m.term > > >Here the Terms_content table is big, maybe 300k rows, while the Favorites >table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 >ms. >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?
AUTOINCREMENT is not required unless you need to only automatically assign values to the primary key that are larger than any value ever used in the table. INTEGER PRIMARY KEY will assign a value max(column)+1 to the INTEGER PRIMARY KEY anytime a record is inserted where no value is provided for the INTEGER PRIMARY KEY column. AUTOINCREMENT is very rarely needed. http://www.sqlite.org/autoinc.html >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Stephen Chrzanowski >Sent: Thursday, 30 January, 2014 09:09 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] Keeping Track of Records of IDs in one table. >Possible? > >There is a problem in the first group of SQL statements. The coffee >hadn't >set in, and I always find crap on a reread-after-submission. Should read >as >follows: > >Previously: create table Tasks (TaskID INTEGER PRIMARY KEY AUTOINCREMENT, >fkProjectID integer, TaskID integer, TaskName char, Completed Bool, >DateCompleted DateTime); >Working: create table Tasks (TaskID INTEGER PRIMARY KEY AUTOINCREMENT, >fkProjectID integer, TaskName char, Completed Bool, DateCompleted >DateTime); > >I had put two field TaskIDs in. Told ya the compiler was buggy. :] > >Old: >create view IncompleteTasks as select ProjectID, ProjectName, TaskID, >TaskName from Projects join Tasks on Projects.ProjectID=Tasks. >fkProjectID; >create view CompletedTasks as select ProjectID, ProjectName, TaskID, >TaskName, DateCompleted from Projects join Tasks on >Projects.Project=Tasks.fkProjectID; > >New: >create view IncompleteTasks as select ProjectID, ProjectName, TaskID, >TaskName from Projects join Tasks on Projects.ProjectID=Tasks. >fkProjectID where isnull(Completed) or Completed=0; >create view CompletedTasks as select ProjectID, ProjectName, TaskID, >TaskName, DateCompleted from Projects join Tasks on >Projects.Project=Tasks.fkProjectID where !IsNull(Completed) or Completed >!=0; > >I use [ Completed != 0 ] as some software defines TRUE as 1 while others >define TRUE as -1. > >On Thu, Jan 30, 2014 at 10:53 AM, jose isaias cabrera >> wrote: > >> >> Wow! thanks for this. I have to read it slowly to capture the >> understanding of some of the syntax. But this is great! thanks. >> >> Stephen Chrzanowski" wrote... >> >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean and DataReader
Have you declared the column with integer affinity (before inserting the data)? >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Johnny >Sent: Thursday, 30 January, 2014 08:38 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Boolean and DataReader > >I'm not one of the sqlite dev'rs for sure, >but I immagine that the .net driver has been done with their cooperation >In any case the funny thing is that >(in a particular situation: after a "wrong" insert of "1" instead of 1, >fault of sqlite admin) >my Wrapper level - >can I say that the ADO .net System.Data.SQLite.dll is my Wrapper level? - >is making an "inconsistent" decision, not a "final" one: >GetType returns System.Boolean (why if sqlite typeof is saying text ?!?) >GetBoolean on the other side throws an exception ... > > > > >-- >View this message in context: >http://sqlite.1065341.n5.nabble.com/Boolean-and-DataReader- >tp73521p73542.html >Sent from the SQLite mailing list archive at Nabble.com. >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean and DataReader
On 1/30/2014 7:20 PM, James K. Lowden wrote:
or define a constraint
colname boolean check (colname in ('Y', 'N'))
Of course you can use 0 and 1, but there's no technical advantage
There is a tiny advantage. Values 0 and 1 are special-cased in SQLite's
file format, and occupy only one byte each. 'Y' and 'N' require two
bytes to represent.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean and DataReader
On Thu, 30 Jan 2014 10:49:39 -0500
Stephen Chrzanowski wrote:
> What I might suggest you do is instead of checking GetBoolean (Since
> it is extremely picky apparently) is use GetInteger != 0. If the
> result is ZERO this will return FALSE. (1 != 0 = TRUE; 0 != 0 =
> FALSE) If the result is anything else, you'll return TRUE.
or define a constraint
colname boolean check (colname in ('Y', 'N'))
Of course you can use 0 and 1, but there's no technical advantage, and
very often 'Y' and 'N' turn out to be handy for reporting purposes.
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite Preloading and ASP.Net debugging
Eric Schultz wrote: > > When I debug in Visual Studio, IIS Express copies all of the > Assemblies in my "bin" directory to a folder into the "AppData\Temporary > ASP.Net Files" folder. Unfortunately, it does not copy the > System.Data.SQLite.dll.config file or the x86 and amd64 subfolders of bin > which contain my interop files. > I've seen this behavior before as well. This appears to be a limitation of Visual Studio. Ideally, it *should* copy the configuration file and all the sub-folders as well. > > Is there any way so the preloading base directory is from where the > System.Data.SQLite.dll file originally came from when I can't set the > environment variables or have a config file in the same folder as the > executing assembly (which I can't in this case). > Why can't you set environment variables? The currently supported workaround for this limitation of Visual Studio is to use the "PreLoadSQLite_BaseDirectory" environment variable (or configuration file setting). Of course, I'm always open to alternative suggestions on how to work around issues like these in a portable and robust way. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation
This page is more than awseome : http://www.sqlite.org/draft/lang_select.html By comparison, this other page is more than outdated, and drags down the website global experience : http://www.sqlite.org/draft/speed.html ==> Wouldn't it be better to remove it, for the moment ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite Preloading and ASP.Net debugging
All, I'm adding SQLite support to the open source Orchard project (which is an ASP.Net CMS) and I'm running into a problem with System.Data.SQLite preloading. When I debug in Visual Studio, IIS Express copies all of the Assemblies in my "bin" directory to a folder into the "AppData\Temporary ASP.Net Files" folder. Unfortunately, it does not copy the System.Data.SQLite.dll.config file or the x86 and amd64 subfolders of bin which contain my interop files. This causes preloading to fail and I get a DLLNotFoundException. I can work around this by putting the x86 and amd64 folders in the root of the website but that's a bit messy looking. Is there any way so the preloading base directory is from where the System.Data.SQLite.dll file originally came from when I can't set the environment variables or have a config file in the same folder as the executing assembly (which I can't in this case). Thanks, Eric -- Eric Schultz, Developer Advocate, Outercurve Foundation http://www.outercurve.org eschu...@outercurve.org cell: 920-539-0404 skype: ericschultzwi @EricOutercurve ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
On Thu, Jan 30, 2014 at 3:07 PM, Zsbán Ambrus wrote: > On 1/30/14, Richard Hipp wrote: > > Thanks Kevin and Ambrus for the error reports. A revised copy of the > > documentation is up at http://www.sqlite.org/draft/lang_select.html - > > please let me know if you see any other problems. > > Great, but the diagram for table-or-subquery still has an error. The > branch where you use a parenthisized join clause as a > table-or-subquery still shows that wrong syntax that allows only an > odd number of terms. > > Fixed. Do you see any more problems? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Order of rows in a VALUES select expression
The future release sqlite 3.8.3 adds VALUES statements as a shortcut form of SELECT statements. This shortcut simply creates a results set made of any number of rows, any number of (unnamed) columns in each, and all the values given by separate explicit expressions. My question is whether sqlite guarantees that the order of result rows from such a statement is the same as the order they are listed in the statement. The docs at "http://www.sqlite.org/draft/lang_select.html"; seem to imply that the order is undefined, the engine is free to choose whatever order it wishes. Further, you can't even use an explicit ORDER BY clause on a VALUES statement. If this is the case, what's the easiest way to generate a set of fixed number of rows like VALUES does but in an order of your choice? Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
On 1/30/14, E. Timothy Uy wrote: > #1 - f.term = t.term > Query #1 takes 300 ms, and query #2 takes 30 ms. Can you show the schema for the tables? Is it possible that the two equals comparisons use different affinity or collation, which changes the semantics of your statement? Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] too many SQL variables
Hehe, you know it. On Thu, Jan 30, 2014 at 11:51 AM, RSmith wrote: > > Just for my edification, what is the limit on the number of SQL >> parameters? >> Today I hit "too may SQL variables" with about 1400... >> Just for our edification, which kind of statement was that? >> >> > The worst kind > > :) > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
On 1/30/14, Richard Hipp wrote: > Thanks Kevin and Ambrus for the error reports. A revised copy of the > documentation is up at http://www.sqlite.org/draft/lang_select.html - > please let me know if you see any other problems. Great, but the diagram for table-or-subquery still has an error. The branch where you use a parenthisized join clause as a table-or-subquery still shows that wrong syntax that allows only an odd number of terms. Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] too many SQL variables
Just for my edification, what is the limit on the number of SQL parameters? Today I hit "too may SQL variables" with about 1400… Just for our edification, which kind of statement was that? The worst kind :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
E. Timothy Uy wrote: > Here the Terms_content table is big, maybe 300k rows, while the Favorites > table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms. Which SQLite version? What is the output of EXPLAIN QUERY PLAN for both queries? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] too many SQL variables
On Jan 30, 2014, at 7:01 PM, E. Timothy Uy wrote: > Just for my edification, what is the limit on the number of SQL parameters? > Today I hit "too may SQL variables" with about 1400… Just for our edification, which kind of statement was that? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] too many SQL variables
Just for my edification, what is the limit on the number of SQL parameters? Today I hit "too may SQL variables" with about 1400... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] too many SQL variables
On 01/31/2014 01:01 AM, E. Timothy Uy wrote: Just for my edification, what is the limit on the number of SQL parameters? Today I hit "too may SQL variables" with about 1400... 999. http://www.sqlite.org/limits.html#max_variable_number ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
Another change to the select-stmt syntax diagram. I need to adjust the text to match, but I have to be away from my desk for a few minutes. More updates when I return. On Thu, Jan 30, 2014 at 12:39 PM, Richard Hipp wrote: > Thanks Kevin and Ambrus for the error reports. A revised copy of the > documentation is up at http://www.sqlite.org/draft/lang_select.html - > please let me know if you see any other problems. > > > -- > D. Richard Hipp > d...@sqlite.org > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
Thanks Kevin and Ambrus for the error reports. A revised copy of the documentation is up at http://www.sqlite.org/draft/lang_select.html - please let me know if you see any other problems. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Troubleshoot Disk I/O Error
Hello Akintoye, A common problem I ran into with the transition from XP to anything newer is that the user folders changed. So, what was a legal read/write folder in XP wasn't in Vista+. I had the folder embedded in the configuration file so, when they brought their old configurations forward, they were pointed at the wrong folder. Easy way to determine if this is the case it to right click the application and "Run as Administrator". If it starts working then you know the problem is permissions. For my users, the choice is to move the file, change the permissions, re-install or just "Run as Administrator" (which I suggested they not do). Thursday, January 30, 2014, 10:52:18 AM, you wrote: AOB7L> Thanks, will try this suggestions and post results. AOB7L> - Akintoye AOB7L> - Original Message - AOB7L> From: sqlite-users@sqlite.org AOB7L> To: sqlite-users@sqlite.org AOB7L> At: Jan 30 2014 10:44:45 AOB7L> On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: >> Hi, >> >> We have one client that recently upgraded from WinXP SP3 to Win7 sp1 >> (Japanese Language). With new OS, attempts to access read from our sqlite >> database fails with disk I/O error. >> >> Can anyone help with suggestions on how to troubleshoot disk I/O error ? >> Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional >> information. >> >> We have not been able to reproduce the error in-house.The user has HP ENVY >> laptop with hybrid drive. User has tried : >> >> 1. Install latest file system drivers >> 2. reformat the hard-drive & reinstall Win7 OS >> >> >> all to no avail. Client does not report problems with any other applications. AOB7L> Immediately after the error occurs, what value is returned by AOB7L> the sqlite3_extended_errcode() function? Often the extended error AOB7L> code identifies the specific IO operation that failed, which can AOB7L> shed light on the problem. AOB7L> Another thing to do is to try compiling with the following defines: AOB7L>-DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 AOB7L> This causes the Windows VFS module to print various messages to AOB7L> standard output that should help to figure out what is happening. AOB7L> Dan. AOB7L> ___ AOB7L> sqlite-users mailing list AOB7L> sqlite-users@sqlite.org AOB7L> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users AOB7L> ___ AOB7L> sqlite-users mailing list AOB7L> sqlite-users@sqlite.org AOB7L> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:t...@djii.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
On Thu, Jan 30, 2014 at 10:58 AM, Richard Hipp wrote: > Documentation on SELECT statements at > http://www.sqlite.org/draft/lang_select.html has now been updated and > amplified. > > > On Thu, Jan 30, 2014 at 5:52 AM, Zsbán Ambrus wrote: > > > Hi! > > > > I'm writing to you about the syntax diagram that appears on > > "http://sqlite.org/draft/lang_select.html";, and is a draft for the > > next version of sqlite (3.8.3). I find this diagram confusing, and > > would rather prefer to have something similar to > > "http://sqlite.org/lang_select.html";, only of course updated to show > > the syntax changes in 3.8.3. > > Minor documentation corrections for: http://www.sqlite.org/draft/lang_select.html IS than than SHOULD BE than * than than a result IS are the same are SHOULD BE are the same get * are the same are assigned IS optional SHOULD BE optionally * may be optional preceded by -- -- -- --Ô¿Ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
On 1/30/14, Richard Hipp wrote: > Documentation on SELECT statements at > http://www.sqlite.org/draft/lang_select.html has now been updated and > amplified. Thank you, that seems cleaner. However, there seems to be an error. From the diagram for select-stmt and also in the diagram for simple-select-stmt, it seems that a FROM clause always has to have an odd number of table-or-subquery parts, which is probably wrong. Further, as select statements can now start with VALUES instead of SELECT, I think it might help to add a VALUES entry to the list of commands "http://www.sqlite.org/draft/lang.html";, just like how there are already entries for REPLACE and WITH. This should lead to a short page which refers the reader to SELECT. Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of = in join
On 30 Jan 2014, at 4:24pm, E. Timothy Uy wrote: > Here the Terms_content table is big, maybe 300k rows, while the Favorites > table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms. Have you run ANALYZE on the database ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] order of = in join
#1 - f.term = t.term SELECT m.term AS term, m.definition AS definition FROM (SELECT t.term, e.definition FROM Terms_content t INNER JOIN Favorites f ON f.term = t.term LEFT JOIN TermEntries te ON te.termid = t.docid LEFT JOIN Entries e ON e.docid = te.entryid) AS m LEFT JOIN Favorites f ON f.term = m.term" #2 - t.term = f.term SELECT m.term AS term, m.definition AS definition FROM (SELECT t.term, e.definition FROM Terms_content t INNER JOIN Favorites f ON t.term = f.term LEFT JOIN TermEntries te ON te.termid = t.docid LEFT JOIN Entries e ON e.docid = te.entryid) AS m LEFT JOIN Favorites f ON f.term = m.term Here the Terms_content table is big, maybe 300k rows, while the Favorites table is small, <100 rows. Query #1 takes 300 ms, and query #2 takes 30 ms. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?
There is a problem in the first group of SQL statements. The coffee hadn't set in, and I always find crap on a reread-after-submission. Should read as follows: Previously: create table Tasks (TaskID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, TaskID integer, TaskName char, Completed Bool, DateCompleted DateTime); Working: create table Tasks (TaskID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, TaskName char, Completed Bool, DateCompleted DateTime); I had put two field TaskIDs in. Told ya the compiler was buggy. :] Old: create view IncompleteTasks as select ProjectID, ProjectName, TaskID, TaskName from Projects join Tasks on Projects.ProjectID=Tasks. fkProjectID; create view CompletedTasks as select ProjectID, ProjectName, TaskID, TaskName, DateCompleted from Projects join Tasks on Projects.Project=Tasks.fkProjectID; New: create view IncompleteTasks as select ProjectID, ProjectName, TaskID, TaskName from Projects join Tasks on Projects.ProjectID=Tasks. fkProjectID where isnull(Completed) or Completed=0; create view CompletedTasks as select ProjectID, ProjectName, TaskID, TaskName, DateCompleted from Projects join Tasks on Projects.Project=Tasks.fkProjectID where !IsNull(Completed) or Completed !=0; I use [ Completed != 0 ] as some software defines TRUE as 1 while others define TRUE as -1. On Thu, Jan 30, 2014 at 10:53 AM, jose isaias cabrera wrote: > > Wow! thanks for this. I have to read it slowly to capture the > understanding of some of the syntax. But this is great! thanks. > > Stephen Chrzanowski" wrote... > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
Documentation on SELECT statements at http://www.sqlite.org/draft/lang_select.html has now been updated and amplified. On Thu, Jan 30, 2014 at 5:52 AM, Zsbán Ambrus wrote: > Hi! > > I'm writing to you about the syntax diagram that appears on > "http://sqlite.org/draft/lang_select.html";, and is a draft for the > next version of sqlite (3.8.3). I find this diagram confusing, and > would rather prefer to have something similar to > "http://sqlite.org/lang_select.html";, only of course updated to show > the syntax changes in 3.8.3. > > I have two concrete problems with this diagram. > > 1. It seems to imply that LIMIT clauses are attached to each part of a > compound select. For example, in a select statement like this: > > SELECT col1 FROM tbl1 UNION ALL SELECT col2 FROM tbl2 ORDER BY 1 LIMIT > 5; > > does the LIMIT clause cause sqlite3 to limit the result of the whole > compound statement, or does it ask only to limit the tbl2 branch and > then take the union? The text of the page later appears to imply that > the LIMIT applies to the whole statement (correct me if I'm wrong > here), but the new syntax diagram really seems to show the opposite. > > 2. This diagram for select too big. The diagram in the released docs > is broken down to smaller parts, and I find that easier to read. > > In fact I don't like to read diagrams like this at all. I'd be > happier to read an alternate representation of the grammar without > images, using only a bnf-like text description. I'm not saying the > images should go away, only that a text representation should also be > available somewhere, possibly on a separate page like > "http://sqlite.org/draft/syntaxdiagrams.html"; if you don't want them > on the main pages. > > That said, there is at least one change I like in the new diagram. > Namely, the grammar rule single-source is renamed to > table-or-subquery, and I think the new name is clearer, so thank you > for that. > > Thank you for your work on improving sqlite3, > > Ambrus > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?
Wow! thanks for this. I have to read it slowly to capture the understanding of some of the syntax. But this is great! thanks. Stephen Chrzanowski" wrote... Untested and only from the SQL compiler in my brain -- This compiler is known to have a few bugs -- It may also be too late to go use this, so this might be something to look at if you plan on upgrading; This is the first way I'd do it; create table Projects (ProjectID INTEGER PRIMARY KEY AUTOINCREMENT, ProjectName char); create table Tasks (TaskID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, TaskID integer, TaskName char, Completed Bool, DateCompleted DateTime); create table ProjectNotes (NoteID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, fkTaskID integer, NoteInfo Char, PostDate CurrentTimeStamp); create index idxfkProjectID on Tasks (fkProjectID); create index idxfkDateCompleted on Tasks (DateCompleted); create index idxfkNoteProjectID on ProjectNotes (fkProjectID); create index idxfkNoteTaskID on ProjectNotes (fkTaskID); create index idxfkNotePostDate on ProjectNotes (PostDate); create view IncompleteTasks as select ProjectID, ProjectName, TaskID, TaskName from Projects join Tasks on Projects.ProjectID=Tasks.fkProjectID; create view CompletedTasks as select ProjectID, ProjectName, TaskID, TaskName, DateCompleted from Projects join Tasks on Projects.Project=Tasks.fkProjectID; create view ShowProjectNotes as select ProjectID, ProjectName, NoteID, NoteInfo, PostDate from ProjectNotes join Projects on Projects.ProjectID=ProjectNotes.fkProjectID where fkTaskID=0 or isnull(fkTaskID) order by PostDate; create view ShowTaskNotes as select TaskID, TaskName, NoteID, NoteInfo, PostDate from ProjectNotes join Tasks on Tasks.TaskID=ProjectNotes.fkTaskID where fkProjectID=0 or isnull(fkProjectID) order by PostDate; --- Caveats: - This method assumes that a task cannot have a subtask. A project can have multiple tasks, but tasks cannot have subtasks. - If implementing a search, there is no index on either project name or task name, so, on a search, you'll be doing a full table scan for LIKE '%TERM%'. - The ProjectNotes has a PK assigned simply because I hate updating fields based on a FLOAT value (Which is what PostDate is) and there would be no reliable way to update or delete a note if necessary. Not to mention, depending on how you're informing the user (IE: Listbox) you can more easily assign an integer to a row than you can a float. Notes: - Note no index created for the ProjectID or the TaskID. That is already handled by the PRIMARY KEY function - The AUTOINCREMENT keyword is used so when you do an insert a new ID is created for that project or task - Following the guidelines posted previously about something along the lines of "If you've got multiple tables with the same column definitions, its usually bad design", and although I can't QUITE get on board with that (Another topic), this table will store general notes about the project at hand, as well as notes done on each particular task, all in one table. When inserting a note, you just need to assign either the fkTaskID or the fkProjectID, as well as the comment, and thats it. --- Another way would be like this; create table Events (EventID integer primary key autoincrement, ParentID integer default 0, EntryName char, Completed Boolean, DateCompleted DateTime); create table EventNotes (NoteID integer primary key autoincrement, EventID integer, NoteText char, PostDate currenttimestamp); create index idxParentID on Events (ParentID); create index idxEventNote on EventNotes (EventID); --- Caveats: - This will introduce to have subtasks for a project. If Events.ParentID=0 then this is the primary project. Assigning the ParentID a value to an existing EventID will mean that it is, by your general definition, a task. But you can assign ParentID to another task. You can keep doing this over and over so your one project can have a theoretical unlimited number of subtasks. - Your code will need to be restructured to recursively get the all subtasks, or, at least an introduction of a new function to get the details for the next set of subtasks. --- Notes: - You'll need to figure out how to create the relationship between the EventID and ParentID so that if an event gets deleted, so will all the sub-events. Its simple enough to do, but my brain-compiler doesn't have the references to do that kind of magic at this time. ;) Look up CASCADE DELETE. Thanks everyone for the help. The problem that I have is that I have projects that have tasks. For example: project 1 task 1 - File Processing/language de task 2 - File Processing/language es task 3 - File Processing/language it task 4 - Translation/language de task 5 - Translation/language es task 6 - Translation/language it task 7 - Publishing/language de task 8 - Publishing/language es task 9 - Publishing/language it project 2 task 10 - File Processing/language de tas
Re: [sqlite] How to Troubleshoot Disk I/O Error
Error is-> SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ - Akintoye - Original Message - From: pontia...@gmail.com To: Akintoye Olorode (BLOOMBERG/ 731 LEXIN), sqlite-users@sqlite.org At: Jan 30 2014 10:38:53 What exact Disk I/O error are you getting? There are many different types. - Permissions to read are denied - Invalid characters in the filename - Corrupted database etc On Thu, Jan 30, 2014 at 10:19 AM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: Hi, We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese Language). With new OS, attempts to access read from our sqlite database fails with disk I/O error. Can anyone help with suggestions on how to troubleshoot disk I/O error ? Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional information. We have not been able to reproduce the error in-house.The user has HP ENVY laptop with hybrid drive. User has tried : 1. Install latest file system drivers 2. reformat the hard-drive & reinstall Win7 OS all to no avail. Client does not report problems with any other applications. Thank You, Akintoye ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Troubleshoot Disk I/O Error
Thanks, will try this suggestions and post results. - Akintoye - Original Message - From: sqlite-users@sqlite.org To: sqlite-users@sqlite.org At: Jan 30 2014 10:44:45 On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: > Hi, > > We have one client that recently upgraded from WinXP SP3 to Win7 sp1 > (Japanese Language). With new OS, attempts to access read from our sqlite > database fails with disk I/O error. > > Can anyone help with suggestions on how to troubleshoot disk I/O error ? > Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional > information. > > We have not been able to reproduce the error in-house.The user has HP ENVY > laptop with hybrid drive. User has tried : > > 1. Install latest file system drivers > 2. reformat the hard-drive & reinstall Win7 OS > > > all to no avail. Client does not report problems with any other applications. Immediately after the error occurs, what value is returned by the sqlite3_extended_errcode() function? Often the extended error code identifies the specific IO operation that failed, which can shed light on the problem. Another thing to do is to try compiling with the following defines: -DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 This causes the Windows VFS module to print various messages to standard output that should help to figure out what is happening. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean and DataReader
I don't use .NET anything, so I can't speak on it. However, with my wrapper in Delphi, if I store a value as 1 or '1', returning a .AsInteger will succeed. The wrapper is smart enough to decide if it is actually a number. I don't recall if it'll throw an exception if the value in the database is returned as a float (1.1) or if it will just return 1, either by rounding, floor, or ceil it. I also have run into boolean issues with my wrapper as well though. When I was designing the database in a different application, I was filling the database with, what I thought at the time, boolean values. However, the SQL IDE I was using at the time treated TRUE values as 1, where as my wrapper was expecting -1 as a TRUE. So since the consistency was FALSE = 0 for both, I modified the wrapper (Since I had the source code) to return FALSE if the result was zero, and true otherwise. The code previously was doing something strange with the comparison. It was a few years ago when I encountered this issue, so I don't remember the details exactly. What I might suggest you do is instead of checking GetBoolean (Since it is extremely picky apparently) is use GetInteger != 0. If the result is ZERO this will return FALSE. (1 != 0 = TRUE; 0 != 0 = FALSE) If the result is anything else, you'll return TRUE. On Thu, Jan 30, 2014 at 10:38 AM, Johnny wrote: > I'm not one of the sqlite dev'rs for sure, > but I immagine that the .net driver has been done with their cooperation > In any case the funny thing is that > (in a particular situation: after a "wrong" insert of "1" instead of 1, > fault of sqlite admin) > my Wrapper level - > can I say that the ADO .net System.Data.SQLite.dll is my Wrapper level? - > is making an "inconsistent" decision, not a "final" one: > GetType returns System.Boolean (why if sqlite typeof is saying text ?!?) > GetBoolean on the other side throws an exception ... > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Boolean-and-DataReader-tp73521p73542.html > Sent from the SQLite mailing list archive at Nabble.com. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Keeping Track of Records of IDs in one table. Possible?
Untested and only from the SQL compiler in my brain -- This compiler is known to have a few bugs -- It may also be too late to go use this, so this might be something to look at if you plan on upgrading; This is the first way I'd do it; create table Projects (ProjectID INTEGER PRIMARY KEY AUTOINCREMENT, ProjectName char); create table Tasks (TaskID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, TaskID integer, TaskName char, Completed Bool, DateCompleted DateTime); create table ProjectNotes (NoteID INTEGER PRIMARY KEY AUTOINCREMENT, fkProjectID integer, fkTaskID integer, NoteInfo Char, PostDate CurrentTimeStamp); create index idxfkProjectID on Tasks (fkProjectID); create index idxfkDateCompleted on Tasks (DateCompleted); create index idxfkNoteProjectID on ProjectNotes (fkProjectID); create index idxfkNoteTaskID on ProjectNotes (fkTaskID); create index idxfkNotePostDate on ProjectNotes (PostDate); create view IncompleteTasks as select ProjectID, ProjectName, TaskID, TaskName from Projects join Tasks on Projects.ProjectID=Tasks.fkProjectID; create view CompletedTasks as select ProjectID, ProjectName, TaskID, TaskName, DateCompleted from Projects join Tasks on Projects.Project=Tasks.fkProjectID; create view ShowProjectNotes as select ProjectID, ProjectName, NoteID, NoteInfo, PostDate from ProjectNotes join Projects on Projects.ProjectID=ProjectNotes.fkProjectID where fkTaskID=0 or isnull(fkTaskID) order by PostDate; create view ShowTaskNotes as select TaskID, TaskName, NoteID, NoteInfo, PostDate from ProjectNotes join Tasks on Tasks.TaskID=ProjectNotes.fkTaskID where fkProjectID=0 or isnull(fkProjectID) order by PostDate; --- Caveats: - This method assumes that a task cannot have a subtask. A project can have multiple tasks, but tasks cannot have subtasks. - If implementing a search, there is no index on either project name or task name, so, on a search, you'll be doing a full table scan for LIKE '%TERM%'. - The ProjectNotes has a PK assigned simply because I hate updating fields based on a FLOAT value (Which is what PostDate is) and there would be no reliable way to update or delete a note if necessary. Not to mention, depending on how you're informing the user (IE: Listbox) you can more easily assign an integer to a row than you can a float. Notes: - Note no index created for the ProjectID or the TaskID. That is already handled by the PRIMARY KEY function - The AUTOINCREMENT keyword is used so when you do an insert a new ID is created for that project or task - Following the guidelines posted previously about something along the lines of "If you've got multiple tables with the same column definitions, its usually bad design", and although I can't QUITE get on board with that (Another topic), this table will store general notes about the project at hand, as well as notes done on each particular task, all in one table. When inserting a note, you just need to assign either the fkTaskID or the fkProjectID, as well as the comment, and thats it. --- Another way would be like this; create table Events (EventID integer primary key autoincrement, ParentID integer default 0, EntryName char, Completed Boolean, DateCompleted DateTime); create table EventNotes (NoteID integer primary key autoincrement, EventID integer, NoteText char, PostDate currenttimestamp); create index idxParentID on Events (ParentID); create index idxEventNote on EventNotes (EventID); --- Caveats: - This will introduce to have subtasks for a project. If Events.ParentID=0 then this is the primary project. Assigning the ParentID a value to an existing EventID will mean that it is, by your general definition, a task. But you can assign ParentID to another task. You can keep doing this over and over so your one project can have a theoretical unlimited number of subtasks. - Your code will need to be restructured to recursively get the all subtasks, or, at least an introduction of a new function to get the details for the next set of subtasks. --- Notes: - You'll need to figure out how to create the relationship between the EventID and ParentID so that if an event gets deleted, so will all the sub-events. Its simple enough to do, but my brain-compiler doesn't have the references to do that kind of magic at this time. ;) Look up CASCADE DELETE. Thanks everyone for the help. The problem that I have is that I have > projects that have tasks. For example: > project 1 > task 1 - File Processing/language de >task 2 - File Processing/language es >task 3 - File Processing/language it >task 4 - Translation/language de >task 5 - Translation/language es >task 6 - Translation/language it >task 7 - Publishing/language de >task 8 - Publishing/language es >task 9 - Publishing/language it > project 2 >task 10 - File Processing/language de >task 11 - File Processing/language es >task 12 - File Processing/language it >task 13 - Translation/language de >task 14 - Translation/language e
Re: [sqlite] How to Troubleshoot Disk I/O Error
On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: Hi, We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese Language). With new OS, attempts to access read from our sqlite database fails with disk I/O error. Can anyone help with suggestions on how to troubleshoot disk I/O error ? Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional information. We have not been able to reproduce the error in-house.The user has HP ENVY laptop with hybrid drive. User has tried : 1. Install latest file system drivers 2. reformat the hard-drive & reinstall Win7 OS all to no avail. Client does not report problems with any other applications. Immediately after the error occurs, what value is returned by the sqlite3_extended_errcode() function? Often the extended error code identifies the specific IO operation that failed, which can shed light on the problem. Another thing to do is to try compiling with the following defines: -DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 This causes the Windows VFS module to print various messages to standard output that should help to figure out what is happening. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Troubleshoot Disk I/O Error
On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote: Hi, We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese Language). With new OS, attempts to access read from our sqlite database fails with disk I/O error. Can anyone help with suggestions on how to troubleshoot disk I/O error ? Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional information. We have not been able to reproduce the error in-house.The user has HP ENVY laptop with hybrid drive. User has tried : 1. Install latest file system drivers 2. reformat the hard-drive & reinstall Win7 OS all to no avail. Client does not report problems with any other applications. Immediately after the error occurs, what is value is returned by the sqlite3_extended_errcode() function? Often the extended error code identifies the specific IO operation that failed, which can shed light on the problem. Another thing to do is to try compiling with the following defines: -DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1 This causes the Windows VFS module to print various messages to standard output that should help to figure out what is happening. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean and DataReader
I'm not one of the sqlite dev'rs for sure, but I immagine that the .net driver has been done with their cooperation In any case the funny thing is that (in a particular situation: after a "wrong" insert of "1" instead of 1, fault of sqlite admin) my Wrapper level - can I say that the ADO .net System.Data.SQLite.dll is my Wrapper level? - is making an "inconsistent" decision, not a "final" one: GetType returns System.Boolean (why if sqlite typeof is saying text ?!?) GetBoolean on the other side throws an exception ... -- View this message in context: http://sqlite.1065341.n5.nabble.com/Boolean-and-DataReader-tp73521p73542.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to Troubleshoot Disk I/O Error
What exact Disk I/O error are you getting? There are many different types. - Permissions to read are denied - Invalid characters in the filename - Corrupted database etc On Thu, Jan 30, 2014 at 10:19 AM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) < aolor...@bloomberg.net> wrote: > Hi, > > We have one client that recently upgraded from WinXP SP3 to Win7 sp1 > (Japanese Language). With new OS, attempts to access read from our sqlite > database fails with disk I/O error. > > Can anyone help with suggestions on how to troubleshoot disk I/O error ? > Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no > additional information. > > We have not been able to reproduce the error in-house.The user has HP ENVY > laptop with hybrid drive. User has tried : > > 1. Install latest file system drivers > 2. reformat the hard-drive & reinstall Win7 OS > > > all to no avail. Client does not report problems with any other > applications. > > Thank You, > > Akintoye > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to Troubleshoot Disk I/O Error
Hi, We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese Language). With new OS, attempts to access read from our sqlite database fails with disk I/O error. Can anyone help with suggestions on how to troubleshoot disk I/O error ? Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional information. We have not been able to reproduce the error in-house.The user has HP ENVY laptop with hybrid drive. User has tried : 1. Install latest file system drivers 2. reformat the hard-drive & reinstall Win7 OS all to no avail. Client does not report problems with any other applications. Thank You, Akintoye ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean and DataReader
Just to make it crystal clear, to a developer actually calling the SQLite functions (I'm talking about [ PrepareSQL_v2 ] and such, not [ select * from table ]), SQLite is TYPELESS which means there is no data type that is kept track of for any field. This means that even if you define a column as NUMERIC, you're still able to put 'ABC' and get a successful insert. So a statement like this: create table Test1 (Field1, Field2); is going to be treated the same as create table Test1 (Field1 numeric, Field2 char); is going to be treated the same as create table Test1 (Field1 char, Field2 UberwonderfulTypeCast); At the Wrapper level, it doesn't matter what kind of type you define for a field. SQLite will essentially ignore the type and allow an insert of any type. You could put a BLOB in a BYTE type field and it'd happily take it with an insert. Now, at the point where the SQLite code is telling the OS to write data out, it internally knows the type of data you're putting out. So it knows that you're writing a number, a string, or a blob, and that is JUST for handling data efficiently, and you'll never have to concern yourself about that bit of data (Unless your one of the SQLite dev'rs) but above that layer, your wrapper is going to be making the final decision on what data you're pulling out is. So if you were trying to pull a boolean value out, and you were getting unexpected results when the field value was 1, 0, or -1, then your wrapper is internally calling a check on the field type and validating the expected return and failing if NUMERIC wasn't defined. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite access from ARM ?
Hi, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > Do you know some project to SQLite access from ARM embedded processors ? > I see something for mySQL using MBed plataform (ARM Cortex M3). > Thanks and best regards. As SQLite comes in the form of C source, it is independent of the CPU. If your OS or runtime environment is not yet supported, you can create your own platform abstraction layer. Best regards Markus Schaber CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH Inspiring Automation Solutions 3S-Smart Software Solutions GmbH Dipl.-Inf. Markus Schaber | Product Development Core Technology Memminger Str. 151 | 87439 Kempten | Germany Tel. +49-831-54031-979 | Fax +49-831-54031-50 E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: http://store.codesys.com CODESYS forum: http://forum.codesys.com Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite access from ARM ?
Do you know some project to SQLite access from ARM embedded processors ? I see something for mySQL using MBed plataform (ARM Cortex M3). Thanks and best regards. Renato / Brazil ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Boolean and DataReader
I just want to keep track of the issue as it is and of the complete solution. Problem was due to a wrong sqlite initialization of the Boolean as a text instead of integer when I used the boolean checkbox of the EditData tab in SQLite Administrator software. Fixing that and making DR.GetBoolean work is easy then :-) Thanks @Simon for suggesting the typeof(...) check -- View this message in context: http://sqlite.1065341.n5.nabble.com/Boolean-and-DataReader-tp73521p73537.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me
Hi! I'm writing to you about the syntax diagram that appears on "http://sqlite.org/draft/lang_select.html";, and is a draft for the next version of sqlite (3.8.3). I find this diagram confusing, and would rather prefer to have something similar to "http://sqlite.org/lang_select.html";, only of course updated to show the syntax changes in 3.8.3. I have two concrete problems with this diagram. 1. It seems to imply that LIMIT clauses are attached to each part of a compound select. For example, in a select statement like this: SELECT col1 FROM tbl1 UNION ALL SELECT col2 FROM tbl2 ORDER BY 1 LIMIT 5; does the LIMIT clause cause sqlite3 to limit the result of the whole compound statement, or does it ask only to limit the tbl2 branch and then take the union? The text of the page later appears to imply that the LIMIT applies to the whole statement (correct me if I'm wrong here), but the new syntax diagram really seems to show the opposite. 2. This diagram for select too big. The diagram in the released docs is broken down to smaller parts, and I find that easier to read. In fact I don't like to read diagrams like this at all. I'd be happier to read an alternate representation of the grammar without images, using only a bnf-like text description. I'm not saying the images should go away, only that a text representation should also be available somewhere, possibly on a separate page like "http://sqlite.org/draft/syntaxdiagrams.html"; if you don't want them on the main pages. That said, there is at least one change I like in the new diagram. Namely, the grammar rule single-source is renamed to table-or-subquery, and I think the new name is clearer, so thank you for that. Thank you for your work on improving sqlite3, Ambrus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "PRAGMA data_store_directory" inconsistancies
Noting that:
- In src/pragma.c line 1267 can be seen that this PRAGMA
is only expected to exist when SQLITE_OS_WIN==1
- In src/pragma.c line 149, the pragma is eliminated when
SQLITE_OMIT_PAGER_PRAGMAS is defined. This
looks wrong to me.
- On Cygwin, one of the functions exported from the
dll is "sqlite3_win32_set_directory". As this function
is only expected to work on win32, and is documented
to accept an Unicode win32 path, this is not logical.
Therefore, I suggest to correct the
SQLITE_OMIT_PAGER_PRAGMAS check,
and eliminate the Cygwin code which handles
this pragma: Since Cygwin is supposed to
behave like UNIX, even though it uses the "win32"
VFS, there is no reason at all to support this pragma
on Cygwin.
Cygwin's current SQLite 3.8.3-1 (beta) package
already contains those suggested modifications.
Suggested patch follows.
Regards,
Jan Nijtmans
Index: src/pragma.c
==
--- src/pragma.c
+++ src/pragma.c
@@ -144,11 +144,11 @@
{ /* zName: */ "count_changes",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlag: */ 0,
/* iArg: */ SQLITE_CountRows },
#endif
-#if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && SQLITE_OS_WIN
+#if SQLITE_OS_WIN && !defined(__CYGWIN__)
{ /* zName: */ "data_store_directory",
/* ePragTyp: */ PragTyp_DATA_STORE_DIRECTORY,
/* ePragFlag: */ 0,
/* iArg: */ 0 },
#endif
@@ -1262,10 +1262,10 @@
#endif /* SQLITE_OMIT_WSD */
}
break;
}
-#if SQLITE_OS_WIN
+#if SQLITE_OS_WIN && !defined(__CYGWIN__)
/*
** PRAGMA data_store_directory
** PRAGMA data_store_directory = ""|"directory_name"
**
Index: src/os_win.c
==
--- src/os_win.c
+++ src/os_win.c
@@ -1662,10 +1662,11 @@
zFilenameMbcs = winUnicodeToMbcs(zTmpWide);
sqlite3_free(zTmpWide);
return zFilenameMbcs;
}
+#if !defined(__CYGWIN__)
/*
** This function sets the data directory or the temporary directory based on
** the provided arguments. The type argument must be 1 in order to set the
** data directory or 2 in order to set the temporary directory. The zValue
** argument is the name of the directory to use. The return value will be
@@ -1698,10 +1699,11 @@
*ppDirectory = zValueUtf8;
return SQLITE_OK;
}
return SQLITE_ERROR;
}
+#endif /* __CYGWIN__ */
/*
** The return value of winGetLastErrorMsg
** is zero if the error message fits in the buffer, or non-zero
** otherwise (if the message was truncated).
@@ -4958,43 +4960,15 @@
int nFull,/* Size of output buffer in bytes */
char *zFull /* Output buffer */
){
#if defined(__CYGWIN__)
+ char *zOut;
SimulateIOError( return SQLITE_ERROR );
UNUSED_PARAMETER(nFull);
assert( nFull>=pVfs->mxPathname );
- if ( sqlite3_data_directory && !winIsVerbatimPathname(zRelative) ){
-/*
-** NOTE: We are dealing with a relative path name and the data
-** directory has been set. Therefore, use it as the basis
-** for converting the relative path name to an absolute
-** one by prepending the data directory and a slash.
-*/
-char *zOut = sqlite3MallocZero( pVfs->mxPathname+1 );
-if( !zOut ){
- return SQLITE_IOERR_NOMEM;
-}
-if( cygwin_conv_path(
-(osIsNT() ? CCP_POSIX_TO_WIN_W : CCP_POSIX_TO_WIN_A) |
-CCP_RELATIVE, zRelative, zOut, pVfs->mxPathname+1)<0 ){
- sqlite3_free(zOut);
- return winLogError(SQLITE_CANTOPEN_CONVPATH, (DWORD)errno,
- "winFullPathname1", zRelative);
-}else{
- char *zUtf8 = winConvertToUtf8Filename(zOut);
- if( !zUtf8 ){
-sqlite3_free(zOut);
-return SQLITE_IOERR_NOMEM;
- }
- sqlite3_snprintf(MIN(nFull, pVfs->mxPathname), zFull, "%s%c%s",
- sqlite3_data_directory, winGetDirSep(), zUtf8);
- sqlite3_free(zUtf8);
- sqlite3_free(zOut);
-}
- }else{
-char *zOut = sqlite3MallocZero( pVfs->mxPathname+1 );
+ zOut = sqlite3MallocZero( pVfs->mxPathname+1 );
if( !zOut ){
return SQLITE_IOERR_NOMEM;
}
if( cygwin_conv_path(
(osIsNT() ? CCP_POSIX_TO_WIN_W : CCP_POSIX_TO_WIN_A),
@@ -5010,11 +4984,10 @@
}
sqlite3_snprintf(MIN(nFull, pVfs->mxPathname), zFull, "%s", zUtf8);
sqlite3_free(zUtf8);
sqlite3_free(zOut);
}
- }
return SQLITE_OK;
#endif
#if (SQLITE_OS_WINCE || SQLITE_OS_WINRT) && !defined(__CYGWIN__)
SimulateIOError( return SQLITE_ERROR );
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
