[sqlite] locking details
Is there any ways to find out locking details after getting the message? sqlite3_get_table(db, ... errmsg=database is locked Dusan Gibarac ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS memory leak : During lock / unlock operations
* Dennis Cote: > The OP said they were measuring an excess of lock calls. That would > imply that SQLite is locking files it has already locked. Is that > possible with the POSIX APIs? SQLite uses fcntl-based locks, which keep a separate lock for each byte in a file (or, more precisely, any byte that you can address with off_t). You can lock and unlock ranges, and if the sizes do not match (which isn't a requirement), the number of calls do not need to match. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.2.2
Thanks Trey and Dennis,
I guess it is just confusing to me because these things appear to be
"optional", but actually have very specific rules for how to use them or NOT
use them. The only one that seems mandatory are the single quote literals. I
try not to use field names with spaces or unusual characters in them, so the
double quotes seem to be only optional in my case.
Thanks again.
Mike
On Feb 8, 2008 9:37 AM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Mike McGonagle wrote:
> >
> > Could you site a reference on this? I have been looking for
> documentation on
> > how SQL deals with quoted things for a while now, but have not found
> > anything.
> >
>
> Mike,
>
> Standard SQL is defined by several different standards; SQL-92,
> SQL:1999, and SQL:2003. Ultimately they define how quoting is supposed
> to be done. In standard SQL the single quote character is used to
> enclose a literal string. The double quote character is used to enclose
> an identifier (table or column name for example) that may contain
> special characters like a space that could confuse the parser.
>
> SQLite uses this basic scheme as described at
> http://www.sqlite.org/lang_expr.html. From that page:
>
> > A string constant is formed by enclosing the string in single quotes
> ('). A single quote within the string can be encoded by putting two single
> quotes in a row - as in Pascal. C-style escapes using the backslash
> character are not supported because they are not standard SQL.
>
> SQLite also has several extensions to this syntax for better
> compatibility with other database software that had previously adopted
> non-standard quoting. It allows square bracket instead of double quotes
> to enclose identifiers (as used by Microsoft products like Access and
> MS-SQL Server). I also allows double quotes around literal strings (as
> used by mySQL). This last case is complicated by the fact that the
> literal can also be an identifier. If the literal value is a valid
> identifier and an identifier is legal at that location, it is assumed to
> be an identifier with standard double quote quoting.
>
> These rules are described at http://www.sqlite.org/lang_keywords.html
>
> > If you want to use a keyword as a name, you need to quote it. There are
> three ways of quoting keywords in SQLite:
> >
> > 'keyword' A keyword in single quotes is interpreted as a
> literal string if it occurs in a context where a string literal is allowed,
> otherwise it is understood as an identifier.
> > "keyword" A keyword in double-quotes is interpreted as an
> identifier if it matches a known identifier. Otherwise it is interpreted as
> a string literal.
> > [keyword] A keyword enclosed in square brackets is always
> understood as an identifier. This is not standard SQL. This quoting
> mechanism is used by MS Access and SQL Server and is included in SQLite for
> compatibility.
> >
>
> This also says that SQLite will accept a single quoted literal as an
> identifier in certain situations. I'm not aware of any other database
> that used single quotes that way, but I'm sure there was one somewhere
> along the line.
>
> Aren't standards wonderful, especially when everyone has their own. :-)
>
> HTH
> Dennis Cote
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Peace may sound simple—one beautiful word— but it requires everything we
have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes?Attention: DRH
I found that it was a fairly simple change to Sqlite to stop it changing formats and causing grief. Out of the box it merges nicely with scripting environments like Javascript, Python and TCL but can be a pain in other places. Fortunately the changes needed where format changes are detrimental are tiny. Fowler, Jeff wrote: > I agree. After many years with SQL Server and Oracle (but new to > SQLite), the concept of storing different datatypes within the same > field is something I've had difficulty grasping. I'm not saying it's a > bad thing, but from a business perspective I can't think of a situation > where we would not want strict affinity. So if it becomes an option > we'll use it throughout our application. > > - Jeff > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff > Sent: Friday, February 08, 2008 11:52 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] How to select Strict Affinity or No Affinity > modes?Attention: DRH > > I would like to have strict affinity mode too. In our schemas we use > check constraints to enforce strict affinity. Unless you're working in > a dynamic typed environment, I can't imagine why you would want to have > inconsistent data within a single database field. Also for consistency > with (every?) other database engine out there, a strict affinity mode > would be good. > Strict affinity will also benefit all wrapper writers who write wrappers > following a framework that assumes strict field typing (which I think is > pretty much all of them since all other db's have strongly typed > fields). > > Thanks, > > Sam > > > On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote: > >> I second the strict affinity mode as an optional feature, for the same > >> reasons as Lee. >> >>A while back I ran into a problem while using the bit and feature >> of sqlite and got unexpected results because sqlite changed the type >> from a 64bit integer into a real. (I think)... In this case it would >> have been simpler to debug, if there had been a type conversion > warning or a failure. >> Regards, >> Ken >> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
"P Kishor" wrote... > On 2/8/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> "P Kishor" replied... >> >> >> > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> >> >> "P Kishor" asked... >> >> >> >> >> >> >I don't understand your question at all. >> >> >> >> I will answer it at the end. >> >> >> >> > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> >> >> >> >> Greetings. >> >> >> >> >> >> I would like to have the results of a select be returned sorted in >> >> >> an >> >> >> specific way. Let me show you what I mean: >> >> >> >> >> >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 >> >> >> GROUP >> >> >> BY >> >> > >> >> > But, first... why are you using GROUP BY above when you have no >> >> > aggregate function (Count, Max, Min, Sum, etc.) in your query? >> >> >> >> Because of this: >> >> >> >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190; >> >> Pre-Proc >> >> Post-Proc >> >> DOC-Trans >> >> DTP >> >> Pre-Proc >> >> Pre-Proc >> >> Pre-Proc >> >> Pre-Proc >> >> Pre-Proc >> >> Post-Proc >> >> Post-Proc >> >> Post-Proc >> >> Post-Proc >> >> Post-Proc >> >> DOC-Trans >> >> DOC-Trans >> >> DOC-Trans >> >> DOC-Trans >> >> DOC-Trans >> >> DTP >> >> DTP >> >> DTP >> >> DTP >> >> DTP >> >> PM >> >> PM >> >> PM >> >> PM >> >> PM >> >> PM >> >> sqlite> >> > >> > In that case you need SELECT DISTINCT, not GROUP BY >> >> Oh, ok, thanks for the help. Weird how the ORDER BY works... I know >> what >> each of these (ORDER BY and DISTINT) do, however is the usage of one of >> these faster than the other? >> >> > >> > >> >> >> >> >> >> > >> >> >> PSubClass; >> >> >> DOC-Trans >> >> >> DTP >> >> >> PM >> >> >> Post-Proc >> >> >> Pre-Proc >> >> >> sqlite> >> >> >> >> >> >> What I would like is to have the SELECT result be, >> >> >> >> >> >> Pre-Proc >> >> >> Post-Proc >> >> >> DOC-Trans >> >> >> DTP >> >> >> PM >> >> >> >> >> >> is this possible? Yes, I know I can sort it in the program, but >> >> >> how >> >> >> can >> >> >> I >> >> >> get this special sort from the DB? >> >> >> >> >> > >> >> > What is the logic above? Without you telling us why Pre-Doc is >> >> > before >> >> > Post-Proc is before DOC-Trans, and so on, how can we guess what your >> >> > logic is for the sort? In other words, please explain what makes the >> >> > above sort "special"? >> >> >> >> Aaah, ok. I am preparing an html file and in it I will list these >> >> PSubClasses in this special logical display sequence: >> >> 1. Pre-Proc >> >> 2. Post-Proc >> >> 3. DOC-Trans >> >> 4. DTP >> >> 5. PM >> >> >> >> I hope this explains it... >> >> >> > >> > Nope, it doesn't at all. You logic is not clear to me, and even if it >> > is, it won't be clear to the poor program that you want to write. If >> > you want a specific order which is not dependent on a collation >> > sequence, alpha order, etc., then enter your prescribed ordinal-ity in >> > a separate column and ORDER BY that column. >> >> Ok Punkish, no problem. Thanks for trying. You have helped me many >> times >> and I am thankful. Sometimes I am very hard to understand. However, >> other >> folks in the list were able to understand what I meant and they have >> provided a few solutions for me. Thanks for the help. And the poor >> program >> understands me correctly and it is working as desired .-). It is a good >> thing computers understand me. Which I can not say the same for >> people... >> .-) > > > Hi José, I apologize if my email came off as brusque and rude > sounding. That was not the intent at all. I really mean what I say > above, and what I suggest above is exactly what has been suggested by > others you have replied to you as well. The logic that may be very > clear in our brain has to be explicit for the program. Using ORDER BY > depends on alphabetical sort or a numeric sort. If you want something > else (for example, importance of a job title), you have to convert > that hierarchy into something that the ORDER BY mechanism can > understand. But, you have to do that explicitly. > > Remember, there is no inherent concept of order in an rdbms. Each row > is independent of each other. Order is imposed *after* your result set > has been extracted, and it only changes the presentation of the result > set, not the original data. There is no inherent concept of "previous > row" or "next row" unlike a spreadsheet. Puneet, once again, you hit the spot. You are a good teacher. Thanks. You are correct. I should have been more specific and I being under time constraint I did not realized my vagueness, and for that I apologize. I tried googleling how to sort in a "special" way with sqlite, but was not successful. Don't worry about the brusque and rude sounding. Knowing you, through your post, I thought you were joking with me. .-) Thanks again. josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/list
Re: [sqlite] How to select Strict Affinity or No Affinity modes?Attention: DRH
I agree. After many years with SQL Server and Oracle (but new to SQLite), the concept of storing different datatypes within the same field is something I've had difficulty grasping. I'm not saying it's a bad thing, but from a business perspective I can't think of a situation where we would not want strict affinity. So if it becomes an option we'll use it throughout our application. - Jeff -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Samuel Neff Sent: Friday, February 08, 2008 11:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes?Attention: DRH I would like to have strict affinity mode too. In our schemas we use check constraints to enforce strict affinity. Unless you're working in a dynamic typed environment, I can't imagine why you would want to have inconsistent data within a single database field. Also for consistency with (every?) other database engine out there, a strict affinity mode would be good. Strict affinity will also benefit all wrapper writers who write wrappers following a framework that assumes strict field typing (which I think is pretty much all of them since all other db's have strongly typed fields). Thanks, Sam On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote: > I second the strict affinity mode as an optional feature, for the same > reasons as Lee. > >A while back I ran into a problem while using the bit and feature > of sqlite and got unexpected results because sqlite changed the type > from a 64bit integer into a real. (I think)... In this case it would > have been simpler to debug, if there had been a type conversion warning or a failure. > > Regards, > Ken > > ___ 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 select Strict Affinity or No Affinity modes?
Yes choice is good, Either way I'm greatful for an Excellent Tool, I can workaround and Live without strict Affinity. But the option to enable it would be a welcome feature. Would there be any performance implications of Strict Affinity, either positive or negative? Samuel Neff <[EMAIL PROTECTED]> wrote: But the important point is that no matter how much discussion we have, we will never all agree that untyped is better than typed or that typed is better than typed. That's why an option so individual developers can choose is good. We don't have to agree, with an option we can agree to disagree. Sam On Feb 7, 2008 11:46 PM, Roger Binns wrote: > > There are also a whole school of people who believe that dynamic typing > as used in SQLite is far more productive and results in less code. For > example the Python programming language is typed in a similar way. > > http://en.wikipedia.org/wiki/Duck_typing > > There are many sites with many discussion threads containing opinions, > anecdotes and evidence and code supporting all the positions on this > issue. Feel free to discuss the merits there. > > Roger > > ___ 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 select Strict Affinity or No Affinity modes?
But the important point is that no matter how much discussion we have, we will never all agree that untyped is better than typed or that typed is better than typed. That's why an option so individual developers can choose is good. We don't have to agree, with an option we can agree to disagree. Sam On Feb 7, 2008 11:46 PM, Roger Binns <[EMAIL PROTECTED]> wrote: > > There are also a whole school of people who believe that dynamic typing > as used in SQLite is far more productive and results in less code. For > example the Python programming language is typed in a similar way. > > http://en.wikipedia.org/wiki/Duck_typing > > There are many sites with many discussion threads containing opinions, > anecdotes and evidence and code supporting all the positions on this > issue. Feel free to discuss the merits there. > > Roger > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH
I would like to have strict affinity mode too. In our schemas we use check constraints to enforce strict affinity. Unless you're working in a dynamic typed environment, I can't imagine why you would want to have inconsistent data within a single database field. Also for consistency with (every?) other database engine out there, a strict affinity mode would be good. Strict affinity will also benefit all wrapper writers who write wrappers following a framework that assumes strict field typing (which I think is pretty much all of them since all other db's have strongly typed fields). Thanks, Sam On Feb 8, 2008 11:09 AM, Ken <[EMAIL PROTECTED]> wrote: > I second the strict affinity mode as an optional feature, for the same > reasons as Lee. > >A while back I ran into a problem while using the bit and feature of > sqlite and got unexpected results because sqlite changed the type from a > 64bit integer into a real. (I think)... In this case it would have been > simpler to debug, if there had been a type conversion warning or a failure. > > Regards, > Ken > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] custom sql functions
Nathan Biggs wrote: > Does anyone know where there is information on adding custom functions > to sqlite? I have been using the CreateAggregate function to define my > custom function, but just wanted to see if it was faster by re-compiling > sqlite with the function in it already. I have the sqlite source code > and have been able to compile it without issues. I am just looking for > where do I add my custom function in the code. > Nathan, You would do that by adding a CreateAggregate call in func.c, the same as SQLite uses for its builtin aggregate functions. It will be no faster than what you have now. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] custom sql functions
Does anyone know where there is information on adding custom functions to sqlite? I have been using the CreateAggregate function to define my custom function, but just wanted to see if it was faster by re-compiling sqlite with the function in it already. I have the sqlite source code and have been able to compile it without issues. I am just looking for where do I add my custom function in the code. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to select Strict Affinity or No Affinity modes? Attention: DRH
I second the strict affinity mode as an optional feature, for the same reasons as Lee. A while back I ran into a problem while using the bit and feature of sqlite and got unexpected results because sqlite changed the type from a 64bit integer into a real. (I think)... In this case it would have been simpler to debug, if there had been a type conversion warning or a failure. Regards, Ken Lee Crain <[EMAIL PROTECTED]> wrote: DRH, ">> Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in." My response to your request for an example of a benefit is that I have always been an adherent of strict datatyping as a means of trapping inadvertent software development errors. It's just one of the many aspects of my self-checking software that makes certain everything is done on purpose and not by chance. Lee Crain -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Chapman Sent: Wednesday, February 06, 2008 8:23 AM To: D. Richard Hipp Cc: sqlite-users@sqlite.org Subject: Re: [sqlite] How to select Strict Affinity or No Affinity modes? I take it that there's no way to work around this currently? Scott Scott Chapman wrote: > D. Richard Hipp wrote: > >> On Feb 2, 2008, at 7:57 PM, Scott Chapman wrote: >> >> >> >>> I've looked high and low and can't find a way to invoke the other 2 >>> affinity modes. Are they available? I'm on 3.5.4. >>> >>> >> The concept of "strict" affinity mode was briefly discussed years >> ago, but we never implemented it, having never seen any benefit >> for such a thing. Can you explain why you think strict affinity mode >> might be beneficial to you? If somebody can provide a good >> enough rational to justify strict affinity mode, we might just put it >> in. >> >> > I'm working on a Python adapter that goes on top of APSW. It will > enable you to use the column types NUMERIC, DATE, TIME, TIMESTAMP and > automatically convert these to and from Python's respective data types. > > The case I'm dealing with that is not working like I want is the case of > NUMERIC column type. In SQLite, this column type gets an affinity of > REAL. If I put in a value to the column as a string literal, say > '123.23', it's stored as a REAL even though I specified it as a string > in quotes. I want it to store it as a string. The only way I've found > to fix this is to use a column type of NUMERIC_TEXT. The presense of > "TEXT" in the column type changes the affinity to string. This is not > very elegant and I was looking for any other way to make this work > correctly. "No Affinity" would probably work, if I understand it > correctly. > > I want to avoid the use of REAL types in this case because they can lead > to rounding errors, which is the whole purpose of the NUMERIC type to > begin with, in my understanding. I also would like to be able to make > the column type just NUMERIC as that is compilant with the SQL standard. > > Strict Affinity and No Affinity are mentioned in the SQLite3 Datatypes > page. If there are no plans to implement these, please consider > removing them from the docs. > > Thanks! > Scott > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS memory leak : During lock / unlock operations
Dennis Cote <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > > There are no lock leaks. But on the other hand, there is not > > a one-to-one mapping of lock to unlock calls. unlock is called > > more often than lock and there are often attempts to unlock files > > that have never been locked, irrc. This is harmless on windows > > and unix. > > > > Richard, > > The OP said they were measuring an excess of lock calls. That would > imply that SQLite is locking files it has already locked. Is that > possible with the POSIX APIs? > That might also be possible. It has been a while since I looked into this, but I think SQLite might be acquiring (for example) multiple read locks then releasing them all with a single unlock. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS memory leak : During lock / unlock operations
[EMAIL PROTECTED] wrote: > > There are no lock leaks. But on the other hand, there is not > a one-to-one mapping of lock to unlock calls. unlock is called > more often than lock and there are often attempts to unlock files > that have never been locked, irrc. This is harmless on windows > and unix. > Richard, The OP said they were measuring an excess of lock calls. That would imply that SQLite is locking files it has already locked. Is that possible with the POSIX APIs? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
On 2/8/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > > "P Kishor" replied... > > > > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > >> > >> "P Kishor" asked... > >> > >> > >> >I don't understand your question at all. > >> > >> I will answer it at the end. > >> > >> > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > >> >> > >> >> Greetings. > >> >> > >> >> I would like to have the results of a select be returned sorted in an > >> >> specific way. Let me show you what I mean: > >> >> > >> >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP > >> >> BY > >> > > >> > But, first... why are you using GROUP BY above when you have no > >> > aggregate function (Count, Max, Min, Sum, etc.) in your query? > >> > >> Because of this: > >> > >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190; > >> Pre-Proc > >> Post-Proc > >> DOC-Trans > >> DTP > >> Pre-Proc > >> Pre-Proc > >> Pre-Proc > >> Pre-Proc > >> Pre-Proc > >> Post-Proc > >> Post-Proc > >> Post-Proc > >> Post-Proc > >> Post-Proc > >> DOC-Trans > >> DOC-Trans > >> DOC-Trans > >> DOC-Trans > >> DOC-Trans > >> DTP > >> DTP > >> DTP > >> DTP > >> DTP > >> PM > >> PM > >> PM > >> PM > >> PM > >> PM > >> sqlite> > > > > In that case you need SELECT DISTINCT, not GROUP BY > > Oh, ok, thanks for the help. Weird how the ORDER BY works... I know what > each of these (ORDER BY and DISTINT) do, however is the usage of one of > these faster than the other? > > > > > > >> > >> > >> > > >> >> PSubClass; > >> >> DOC-Trans > >> >> DTP > >> >> PM > >> >> Post-Proc > >> >> Pre-Proc > >> >> sqlite> > >> >> > >> >> What I would like is to have the SELECT result be, > >> >> > >> >> Pre-Proc > >> >> Post-Proc > >> >> DOC-Trans > >> >> DTP > >> >> PM > >> >> > >> >> is this possible? Yes, I know I can sort it in the program, but how > >> >> can > >> >> I > >> >> get this special sort from the DB? > >> >> > >> > > >> > What is the logic above? Without you telling us why Pre-Doc is before > >> > Post-Proc is before DOC-Trans, and so on, how can we guess what your > >> > logic is for the sort? In other words, please explain what makes the > >> > above sort "special"? > >> > >> Aaah, ok. I am preparing an html file and in it I will list these > >> PSubClasses in this special logical display sequence: > >> 1. Pre-Proc > >> 2. Post-Proc > >> 3. DOC-Trans > >> 4. DTP > >> 5. PM > >> > >> I hope this explains it... > >> > > > > Nope, it doesn't at all. You logic is not clear to me, and even if it > > is, it won't be clear to the poor program that you want to write. If > > you want a specific order which is not dependent on a collation > > sequence, alpha order, etc., then enter your prescribed ordinal-ity in > > a separate column and ORDER BY that column. > > Ok Punkish, no problem. Thanks for trying. You have helped me many times > and I am thankful. Sometimes I am very hard to understand. However, other > folks in the list were able to understand what I meant and they have > provided a few solutions for me. Thanks for the help. And the poor program > understands me correctly and it is working as desired .-). It is a good > thing computers understand me. Which I can not say the same for people... > .-) Hi José, I apologize if my email came off as brusque and rude sounding. That was not the intent at all. I really mean what I say above, and what I suggest above is exactly what has been suggested by others you have replied to you as well. The logic that may be very clear in our brain has to be explicit for the program. Using ORDER BY depends on alphabetical sort or a numeric sort. If you want something else (for example, importance of a job title), you have to convert that hierarchy into something that the ORDER BY mechanism can understand. But, you have to do that explicitly. Remember, there is no inherent concept of order in an rdbms. Each row is independent of each other. Order is imposed *after* your result set has been extracted, and it only changes the presentation of the result set, not the original data. There is no inherent concept of "previous row" or "next row" unlike a spreadsheet. Puneet. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
"P Kishor" replied... > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> "P Kishor" asked... >> >> >> >I don't understand your question at all. >> >> I will answer it at the end. >> >> > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> >> >> Greetings. >> >> >> >> I would like to have the results of a select be returned sorted in an >> >> specific way. Let me show you what I mean: >> >> >> >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP >> >> BY >> > >> > But, first... why are you using GROUP BY above when you have no >> > aggregate function (Count, Max, Min, Sum, etc.) in your query? >> >> Because of this: >> >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190; >> Pre-Proc >> Post-Proc >> DOC-Trans >> DTP >> Pre-Proc >> Pre-Proc >> Pre-Proc >> Pre-Proc >> Pre-Proc >> Post-Proc >> Post-Proc >> Post-Proc >> Post-Proc >> Post-Proc >> DOC-Trans >> DOC-Trans >> DOC-Trans >> DOC-Trans >> DOC-Trans >> DTP >> DTP >> DTP >> DTP >> DTP >> PM >> PM >> PM >> PM >> PM >> PM >> sqlite> > > In that case you need SELECT DISTINCT, not GROUP BY Oh, ok, thanks for the help. Weird how the ORDER BY works... I know what each of these (ORDER BY and DISTINT) do, however is the usage of one of these faster than the other? > > >> >> >> > >> >> PSubClass; >> >> DOC-Trans >> >> DTP >> >> PM >> >> Post-Proc >> >> Pre-Proc >> >> sqlite> >> >> >> >> What I would like is to have the SELECT result be, >> >> >> >> Pre-Proc >> >> Post-Proc >> >> DOC-Trans >> >> DTP >> >> PM >> >> >> >> is this possible? Yes, I know I can sort it in the program, but how >> >> can >> >> I >> >> get this special sort from the DB? >> >> >> > >> > What is the logic above? Without you telling us why Pre-Doc is before >> > Post-Proc is before DOC-Trans, and so on, how can we guess what your >> > logic is for the sort? In other words, please explain what makes the >> > above sort "special"? >> >> Aaah, ok. I am preparing an html file and in it I will list these >> PSubClasses in this special logical display sequence: >> 1. Pre-Proc >> 2. Post-Proc >> 3. DOC-Trans >> 4. DTP >> 5. PM >> >> I hope this explains it... >> > > Nope, it doesn't at all. You logic is not clear to me, and even if it > is, it won't be clear to the poor program that you want to write. If > you want a specific order which is not dependent on a collation > sequence, alpha order, etc., then enter your prescribed ordinal-ity in > a separate column and ORDER BY that column. Ok Punkish, no problem. Thanks for trying. You have helped me many times and I am thankful. Sometimes I am very hard to understand. However, other folks in the list were able to understand what I meant and they have provided a few solutions for me. Thanks for the help. And the poor program understands me correctly and it is working as desired .-). It is a good thing computers understand me. Which I can not say the same for people... .-) thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.2.2
Mike McGonagle wrote:
>
> Could you site a reference on this? I have been looking for documentation on
> how SQL deals with quoted things for a while now, but have not found
> anything.
>
Mike,
Standard SQL is defined by several different standards; SQL-92,
SQL:1999, and SQL:2003. Ultimately they define how quoting is supposed
to be done. In standard SQL the single quote character is used to
enclose a literal string. The double quote character is used to enclose
an identifier (table or column name for example) that may contain
special characters like a space that could confuse the parser.
SQLite uses this basic scheme as described at
http://www.sqlite.org/lang_expr.html. From that page:
> A string constant is formed by enclosing the string in single quotes ('). A
> single quote within the string can be encoded by putting two single quotes in
> a row - as in Pascal. C-style escapes using the backslash character are not
> supported because they are not standard SQL.
SQLite also has several extensions to this syntax for better
compatibility with other database software that had previously adopted
non-standard quoting. It allows square bracket instead of double quotes
to enclose identifiers (as used by Microsoft products like Access and
MS-SQL Server). I also allows double quotes around literal strings (as
used by mySQL). This last case is complicated by the fact that the
literal can also be an identifier. If the literal value is a valid
identifier and an identifier is legal at that location, it is assumed to
be an identifier with standard double quote quoting.
These rules are described at http://www.sqlite.org/lang_keywords.html
> If you want to use a keyword as a name, you need to quote it. There are three
> ways of quoting keywords in SQLite:
>
> 'keyword' A keyword in single quotes is interpreted as a literal
> string if it occurs in a context where a string literal is allowed, otherwise
> it is understood as an identifier.
> "keyword" A keyword in double-quotes is interpreted as an
> identifier if it matches a known identifier. Otherwise it is interpreted as a
> string literal.
> [keyword] A keyword enclosed in square brackets is always
> understood as an identifier. This is not standard SQL. This quoting mechanism
> is used by MS Access and SQL Server and is included in SQLite for
> compatibility.
>
This also says that SQLite will accept a single quoted literal as an
identifier in certain situations. I'm not aware of any other database
that used single quotes that way, but I'm sure there was one somewhere
along the line.
Aren't standards wonderful, especially when everyone has their own. :-)
HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting the result of a select. Is this possible?
On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > > "P Kishor" asked... > > > >I don't understand your question at all. > > I will answer it at the end. > > > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > >> > >> Greetings. > >> > >> I would like to have the results of a select be returned sorted in an > >> specific way. Let me show you what I mean: > >> > >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP BY > > > > But, first... why are you using GROUP BY above when you have no > > aggregate function (Count, Max, Min, Sum, etc.) in your query? > > Because of this: > > sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190; > Pre-Proc > Post-Proc > DOC-Trans > DTP > Pre-Proc > Pre-Proc > Pre-Proc > Pre-Proc > Pre-Proc > Post-Proc > Post-Proc > Post-Proc > Post-Proc > Post-Proc > DOC-Trans > DOC-Trans > DOC-Trans > DOC-Trans > DOC-Trans > DTP > DTP > DTP > DTP > DTP > PM > PM > PM > PM > PM > PM > sqlite> In that case you need SELECT DISTINCT, not GROUP BY > > > > > >> PSubClass; > >> DOC-Trans > >> DTP > >> PM > >> Post-Proc > >> Pre-Proc > >> sqlite> > >> > >> What I would like is to have the SELECT result be, > >> > >> Pre-Proc > >> Post-Proc > >> DOC-Trans > >> DTP > >> PM > >> > >> is this possible? Yes, I know I can sort it in the program, but how can > >> I > >> get this special sort from the DB? > >> > > > > What is the logic above? Without you telling us why Pre-Doc is before > > Post-Proc is before DOC-Trans, and so on, how can we guess what your > > logic is for the sort? In other words, please explain what makes the > > above sort "special"? > > Aaah, ok. I am preparing an html file and in it I will list these > PSubClasses in this special logical display sequence: > 1. Pre-Proc > 2. Post-Proc > 3. DOC-Trans > 4. DTP > 5. PM > > I hope this explains it... > Nope, it doesn't at all. You logic is not clear to me, and even if it is, it won't be clear to the poor program that you want to write. If you want a specific order which is not dependent on a collation sequence, alpha order, etc., then enter your prescribed ordinal-ity in a separate column and ORDER BY that column. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS memory leak : During lock / unlock operations
<[EMAIL PROTECTED]> wrote: > Hi.. > >we are using sqlite3.3.4 with Integrity OS. we are facing a problem > where in the VFS memory is getting exhausted due >to large lock/unlock calls made by sqlite. Integrity support team > said that, for each file lock call made by sqlite, a definite amount >of memory is allocated, this memory is released only after the unlock > or when the file is closed. And they are claiming that >number of file unlock calls are not same as the number of file lock > calls. they have put traces and identified that for 1000 lock calls >there are only 950 unlock calls, which is a shortage of 50 unlock > calls. This will leak considerable amount of memory if the >system is left for long hours, with continuous sqlite operations > being made. > > can any one throw some light on this problem, is there any known > issue like this. Any information on this will be very helpful The os_unix.c backend to SQLite makes no attempt to match lock/unlock calls, because posix does not requires such. If you are running on an operating system that does require matching lock/unlock calls, you will probably need to modify the os_unix.c layer in order for it to work properly on your system. > > thanks > murthy > > Please do not print this email unless it is absolutely necessary. Spread > environmental awareness. > > The information contained in this electronic message and any attachments to > this message are intended for the exclusive use of the addressee(s) and may > contain proprietary, confidential or privileged information. If you are not > the intended recipient, you should not disseminate, distribute or copy this > e-mail. Please notify the sender immediately and destroy all copies of this > message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient should > check this email and any attachments for the presence of viruses. The company > accepts no liability for any damage caused by any virus transmitted by this > email. > > www.wipro.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] VFS memory leak : During lock / unlock operations
Brad House <[EMAIL PROTECTED]> wrote: > >> they have put traces and identified that for 1000 lock calls > >> there are only 950 unlock calls, which is a shortage of 50 unlock > >> calls. > > The os_unix.c backend to SQLite makes no attempt to match lock/unlock > > calls, because posix does not requires such. If you are running > > on an operating system that does require matching lock/unlock calls, > > you will probably need to modify the os_unix.c layer in order for it > > to work properly on your system. > > I'm not sure what you're getting at here. Are you saying that it's > possible that some mutexes will _never_ be unlocked? In what circumstance > does that occur? Is there a case where a mutex could be destroyed while > a lock is held? I know my linux 'man pthread_mutex_destroy' says > "Attempting to destroy a locked mutex results in undefined behavior". > > Under what circumstances does this "lock leakage" occur exactly? There are no lock leaks. But on the other hand, there is not a one-to-one mapping of lock to unlock calls. unlock is called more often than lock and there are often attempts to unlock files that have never been locked, irrc. This is harmless on windows and unix. -- D. Richard Hipp <[EMAIL PROTECTED]> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS memory leak : During lock / unlock operations
>> they have put traces and identified that for 1000 lock calls >> there are only 950 unlock calls, which is a shortage of 50 unlock >> calls. > The os_unix.c backend to SQLite makes no attempt to match lock/unlock > calls, because posix does not requires such. If you are running > on an operating system that does require matching lock/unlock calls, > you will probably need to modify the os_unix.c layer in order for it > to work properly on your system. I'm not sure what you're getting at here. Are you saying that it's possible that some mutexes will _never_ be unlocked? In what circumstance does that occur? Is there a case where a mutex could be destroyed while a lock is held? I know my linux 'man pthread_mutex_destroy' says "Attempting to destroy a locked mutex results in undefined behavior". Under what circumstances does this "lock leakage" occur exactly? -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS memory leak : During lock / unlock operations
Hi, We are working on different operating system other than unix and windows. We are using the os_win.c for porting, we have ported the code by making some changes with respect to the FAT32 file system apis in the code. We have not tested much, my worry is whether we also need to take care of such in the os_win.c In our OS also. Also, is this the OS dependent? Best Regards, A. Sreedhar. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, February 08, 2008 7:24 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] VFS memory leak : During lock / unlock operations <[EMAIL PROTECTED]> wrote: > Hi.. > >we are using sqlite3.3.4 with Integrity OS. we are facing a problem > where in the VFS memory is getting exhausted due >to large lock/unlock calls made by sqlite. Integrity support team > said that, for each file lock call made by sqlite, a definite amount >of memory is allocated, this memory is released only after the > unlock or when the file is closed. And they are claiming that >number of file unlock calls are not same as the number of file lock > calls. they have put traces and identified that for 1000 lock calls >there are only 950 unlock calls, which is a shortage of 50 unlock > calls. This will leak considerable amount of memory if the >system is left for long hours, with continuous sqlite operations > being made. > > can any one throw some light on this problem, is there any known > issue like this. Any information on this will be very helpful The os_unix.c backend to SQLite makes no attempt to match lock/unlock calls, because posix does not requires such. If you are running on an operating system that does require matching lock/unlock calls, you will probably need to modify the os_unix.c layer in order for it to work properly on your system. > > thanks > murthy > > Please do not print this email unless it is absolutely necessary. Spread environmental awareness. > > The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. > > WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. > > www.wipro.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
