[sqlite] Magic number in sqlite source code
On 2015-12-30 12:51 PM, Richard Hipp wrote: > On 12/30/15, Richard Hipp wrote: >> I'll continue look for an alternative way to make the intent of the >> code clearer. > > See https://www.sqlite.org/src/info/1541607d458069f5 for another > attempt at removing magic numbers. But I don't like it. It seems to > complicate more than it clarifies. My current thinking is that the > code should remain as it is on trunk. While kludgy itself, a possible compromise is to still use a named constant / macro but have '4' in the name of the macro, eg like 'SOME_FOO_4' where the SOME_FOO is a semblance of descriptive and the 4 says what the value is so you don't have to look it up. The key thing is that there may be multiple reasons to use the value 4 in a program and the named constant is illustrating which reason it is. If you change the value of the constant then you would also rename this particular constant to match the new value, but the key thing is you have something easily look-upable that shows all the 4 are connected. -- Darren Duncan
[sqlite] whish list for 2016
On 2015-12-24 6:21 PM, Stephen Chrzanowski wrote: > One thing I would suggest, if you're looking to add and delete columns > dynamically is not to worry about the order of the columns in the database, > but, have a second table hanging around that remembers the specified order > the user wants to see the columns in. Doing your update and insert calls > make no difference so long you specify the fields on either call (Or in > oter words, don't do [ insert into MyTable values (1,2,3) ]. Be aware on > tables that get large. Adding or deleting fields can get expensive when > the databases physical pages need to be updated, especially if the field > you're adding affects, or has indexes modified. It sounds like you're advocating a data dictionary of sorts, which I agree with. The order for displaying columns in is meta-data that should be defined separately for the users' sake, while the actual columns have no significant order in the database. Display order is also just one of many kinds of useful meta-data, and storing that separately allows you to have whatever kinds of meta you want without complicating the core system. -- Darren Duncan
[sqlite] whish list for 2016
On 2015-12-21 3:46 PM, lchishol at paradise.net.nz wrote: > Joining the throng, here are my requests: > a) Either an ORDER BY clause/equivalent for GROUP BY, or an assurance that the > kludge of sorting a sub-query first and then grouping the result does and will > continue to work - I need this functionality. You need what to work exactly? SQL tables are unordered by definition, and ORDER BY is more of a display attribute at the end. If you want to do something order-sensitive in the middle of a query then RANK is the proper generalized way to do it that SQL provides, that or, where applicable, order-insensitive aggregates like min/max/etc which still tell you what value you'd get first if you sorted a list without actually sorting it. > c) A vote for RANK, but I'm doing that in my application at present, post-SQL > but before printing. I know you can generate 1,2,3=,3=,5 type sequences from > self-joins but it seems a lot easier to do it in Delphi! I agree with adding RANK, it is very useful. -- Darren Duncan
[sqlite] whish list for 2016
On 2015-12-21 8:25 AM, Petite Abeille wrote: >> On Dec 21, 2015, at 4:08 AM, Darren Duncan >> wrote: >> >> If you want that feature, instead do it the better way that Postgres 9.5 >> did, which is as an extension to the INSERT statement in the form "ON >> CONFLICT DO UPDATE/IGNORE?. > > Please, enough of the comic act :P > > MERGE it is. Comic act? Do you consider the MERGE defined in the SQL standard to be a better designed feature than Postgres' alternative, or do you prefer the former soley because it is in the SQL standard? I recall that Postgres went with their brand-new alternative because it was a better design, more useable in practice; both simpler and more powerful, easier to express the user's intent and not have arbitrary limitations. -- Darren Duncan
[sqlite] whish list for 2016
On 2015-12-20 10:25 AM, Petite Abeille wrote:
>> On Dec 20, 2015, at 7:12 PM, Big Stone wrote:
>>
>> To prepare for 2016 greetings moment, here is my personnal whish list
>> for sqlite:
>
> MERGE statement! Yeah! :)
>
> https://en.wikipedia.org/wiki/Merge_(SQL)
If you want that feature, instead do it the better way that Postgres 9.5 did,
which is as an extension to the INSERT statement in the form "ON CONFLICT DO
UPDATE/IGNORE".
Relevant url:
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5#INSERT_..._ON_CONFLICT_DO_NOTHING.2FUPDATE_.28.22UPSERT.22.29
Example:
INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;
-- Darren Duncan
[sqlite] I have no idea what to install!
On 2015-12-12 4:02 PM, Nicolette Warner wrote: > Please forgive my lack of knowledge but for the life of me I can't figure > out what to install or how to install what I should be using. I'm using > VS2015 Community edition where I'm attempting to create a WPF desktop > application for windows (not specific to 8 or 10). I have a rather basic > understanding of VB. Right now I am just trying to use a DataGrid to > display the data from the database. I would truly appreciate any help at > all!! That's kind of broad. I find in situations like this the best bet is to try Google searching a few relevant terms together, for example "SQLite visual studio 2015 datagrid" and so on. Unless this is super-new, you're likely to find either manual pages or a forum such as stack overflow where someone asked a related thing already. You're likely to find answers with less effort on everyone's part if you do that. -- Darren Duncan
[sqlite] Putting an index on a boolean
For my part, in a database I designed that used a SHA-256 hash for a unique identifier that was then a foreign key from many other tables, I stored that as an integer and not as a hex string. If UUIDs are similarly numbers fundamentally, they possibly could do likewise. I agree with Mark's comment re binary. -- Darren Duncan On 2015-12-12 1:12 PM, Mark Hamburg wrote: > Though to the extent that speed is proportional to data size, it would be > good to use something other than hexadecimal to store UUIDs. Binary blobs > would be the most compact, but ASCII85 encoding would work well if you need > strings. > > Also, if these values are reused repeatedly as I suspect projectID and > groupID might be, then it may be useful to intern them into a table and use > integer keys. We got a noticeable performance improvement when I made that > sort of change recently in our project. (I also implemented a > string-to-integer-to-string cache that sits ahead of hitting the database.) > > Mark > >> On Dec 12, 2015, at 1:07 PM, Darren Duncan >> wrote: >> >> On 2015-12-12 12:56 PM, Cecil Westerhof wrote: >>>>> By the way: I am thinking about using UUID for projectID and groupID, >>>> but I >>>>> heard somewhere that it was a bad idea to use UUID for an indexed field. >>>> Is >>>>> this true?? >>>> >>>> I think you might have misunderstood. UUID is almost always a good >>>> field to index. >>> >>> ?I was told because of the nature of random UUID (what I will be using) it >>> is hard to create a good index. The article said that data that is really >>> random cannot be indexed very efficient. But I do not have to worry about >>> it then. :-) It has been a few years back, so it is also possible that the >>> problem is solved nowadays. >> >> Cecil, it isn't about randomness, it is about uniqueness or cardinality. >> The fields that index the best are ones with many different values, in >> particular key fields where every record has a different value from every >> other record. UUIDs have this quality in spades. It is even more important >> to index such fields if you will either be searching/filtering on them or if >> they are the parent in a foreign key constraint. This has always been the >> case, its not a new thing. -- Darren Duncan
[sqlite] Putting an index on a boolean
On 2015-12-12 12:56 PM, Cecil Westerhof wrote: >>> By the way: I am thinking about using UUID for projectID and groupID, >> but I >>> heard somewhere that it was a bad idea to use UUID for an indexed field. >> Is >>> this true?? >> >> I think you might have misunderstood. UUID is almost always a good >> field to index. > > ?I was told because of the nature of random UUID (what I will be using) it > is hard to create a good index. The article said that data that is really > random cannot be indexed very efficient. But I do not have to worry about > it then. :-) It has been a few years back, so it is also possible that the > problem is solved nowadays. Cecil, it isn't about randomness, it is about uniqueness or cardinality. The fields that index the best are ones with many different values, in particular key fields where every record has a different value from every other record. UUIDs have this quality in spades. It is even more important to index such fields if you will either be searching/filtering on them or if they are the parent in a foreign key constraint. This has always been the case, its not a new thing. -- Darren Duncan
[sqlite] Problem with accumulating decimal values
Frank, The problem you are having is due to SQLite not following the SQL standard regarding non-integral numeric types. The SQL standard specifies that the DECIMAL type is exact numeric and able to represent decimal numbers exactly. However, when you ask SQLite for a DECIMAL column, that is not what it will give you; instead, it will silently "succeed" but give you an inexact numeric type instead, a floating point number, as if you had said FLOAT/etc instead of DECIMAL. So the problem you are having is due to the actual numbers in the database not being what you told it to store, but just an approximation. Per another suggestion, the best workaround is to use an INTEGER type instead, and store an even multiple of whatever your smallest currency unit size is, eg cents rather than dollars. -- Darren Duncan On 2015-12-11 6:21 AM, Frank Millman wrote: > I am having a problem accumulating decimal values. > > I am actually using Python, but I can reproduce it in the sqlite3 interactive > terminal. > > SQLite version 3.8.6 2014-08-15 11:46:33 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> CREATE TABLE fmtemp (acno INT, balance DECIMAL); > sqlite> INSERT INTO fmtemp VALUES (1, 0); > > sqlite> UPDATE fmtemp SET balance = balance + 123.45; > sqlite> SELECT bal FROM fmtemp; > 123.45
[sqlite] Warnings for non-deterministic queries?
On 2015-11-27 5:46 AM, Keith Medcalf wrote: >> Is there a way I could programatically determine that a query is non- >> deterministic at query prepare time? > > What do you mean, non-deterministic? The result is deterministic in all > cases. > > It may be complicated and/or difficult for you to compute, but it is always > deterministic. The result is generated by running an unchanging algorithm on > unchanging data. If there is no random inputs and the computer hardware is > not broken, then the results are entirely determined by the algorithm > executed and the state of the data upon which it is operating. While what you say is true, deterministic if same algorithm and same data, I think there's a higher standard for determinism. The concept of "arbitrary row" presumably is based on certain implementation details like the structure of an index or other hidden metadata, which can change even if there are no user-visible changes to the database. Unless the algorithm guarantees that the exact same row will be selected whenever the user-visible parts of the database have the exact same value, it is not actually deterministic from the user's point of view, which I think is what really matters here. Selecting an "arbitrary row" can only be called deterministic otherwise if the user is able to query all of the conditions that would make it chosen, such as index details, as are applicable. Any visible change is significant; if the user can get a different answer to any question about the database, including 'hidden' parts, then the database is different, whereas if all questions they can ask return the same answer, then the "arbitrary row" should be the same row. -- Darren Duncan
[sqlite] Dont Repeat Yourself (DRY) and SQLite
I think Domingo is showing signs of cargo-culting, simply taking some mantra to
the extreme without even realizing the point of the mantra.
Logically speaking, enumerated values like this 'simple'/'tuple' are program
identifiers in the same way as variable or field names are. These enums are
part of the code, not part of the data. It makes as much sense to replace them
with numbers as it is to give all variables and fields names that are numbers,
because you don't want to repeat the variable/etc names all over the place.
The DRY concept is not meant to say use only numbers for enums. In this
particular case, using identifiers, which are strings, is the correct course of
action.
DRY is better applied where the subject may be mutable like data, such as a
person's name, not where it is code like these enums.
When I say like code, I mean that presumably with these 'simple'/'tuple' there
are various places in the application that specifically dispatch different
logic
depending on those values, whereas with data, such as an enumeration of country
names, it would not be the case.
-- Darren Duncan
On 2015-11-25 7:14 AM, Simon Slavin wrote:
> On 25 Nov 2015, at 2:23pm, Domingo Alvarez Duarte dev.dadbiz.es> wrote:
>
>> This way we repeat the string everywhere then it's not DRY !
>
> You know, I think you're the first person to mention DRY here. I had to look
> it up.
>
> <https://en.wikipedia.org/wiki/Don%27t_repeat_yourself>
>
> For some reason it seems that under DRY repeating a string in lots of places
> is bad, but repeating a number in lots of places is good. I'm not sure about
> the logic behind that.
>
> Okay, do this instead:
>
> CREATE TABLE enum_type (the_ID INTEGER PRIMARY KEY, the_value TEXT UNIQUE);
> INSERT INTO enum_type (the_value) VALUES ('simple'), ('tuple');
>
> CREATE TABLE use_mytype (...
> ...
> one_type TEXT REFERENCES enum_type(theID) DEFAULT 2
> )
>
> It is now harder to know which value to insert for one_type.
[sqlite] Dont Repeat Yourself (DRY) and SQLite
How Postgres stores enum values is an implementation detail that should be ignored. You always use them using string syntax, that is proper. The SQL syntax for comparisons is the same =, <, > etc for all types, there is no distinct "string comparison". See http://www.postgresql.org/docs/9.4/static/datatype-enum.html . Do what Simon says. I don't see a problem here. -- Darren Duncan On 2015-11-24 3:24 PM, Domingo Alvarez Duarte wrote: > If we do that we'll be repeating the same string on every column and need a > string comparison, with postgres enum types or foreign keys it's an integer > and no repetition. > > Cheers ! >> Tue Nov 24 2015 11:01:35 pm CET CET from "Simon Slavin" >> Subject: Re: [sqlite] Dont Repeat Yourself (DRY) >> and >> SQLite >> >> On 24 Nov 2015, at 7:09pm, Domingo Alvarez Duarte >> wrote: >> >> >>> one_type INTEGER NOT NULL REFERENCES mytype(id) NOT NULL, --how to use >>> a default here ? >>> > >> Include "DEFAULT 'tuple'" just like you would in PostgreSQL. >> >> Otherwise I'm with Igor. I don't see why you're using TRIGGERs and I don't >> see what problem you're having. Can you point out a specific section of your >> PostgreSQL code you can't translate into SQLite ?
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
On 2015-11-18 2:05 AM, Dominique Devienne wrote: > On Wed, Nov 18, 2015 at 10:58 AM, Darren Duncan > wrote: > >> On 2015-11-18 1:27 AM, Yuri wrote: >>> I agree they can be beneficial, but not in all cases. Depends on what you >>> do. It >>> would have been great if it was an option, ex. "CONSTRAINT LEVEL >>> [STATEMENT|TRANSACTION];". >> >> You can declare that behavior individually per foreign key constraint, and >> you can also change it at runtime with >> https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys if that's >> what you were looking for. -- Darren Duncan > > [DD] Oh, I didn't realize that pragma existed! Thanks for that. Didn't even > realize SQLite supported deferred per FK, I thought it was always for all > FKs in general. Given this pragma, then showing the FK name on immediate > mode is possible, no? --DD Well what does https://www.sqlite.org/foreignkeys.html tell you? -- Darren Duncan
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
Replying to myself... Often there is a single command available where one can say create a file on this particular filesystem path, and the system will go and create any nonexistent directories it needs to on the way. So from the user's point of view creating the multiple things is a single operation, which is my point. -- Darren Duncan On 2015-11-18 2:06 AM, Darren Duncan wrote: > On 2015-11-18 1:58 AM, Dominique Devienne wrote: >> On Wed, Nov 18, 2015 at 10:17 AM, Darren Duncan >> wrote: >> >>> Deferred constraints are definitely a benefit. >> >>> They allow you to express constraints otherwise not possible, for example >>> that a record may exist in table X if and only if a counterpart exists in >>> table Y, such as balancing records in a double-entry accounting system. >>> Granted all you really need for this is the ability to change multiple >>> tables as a single atomic operation, but failing that ability, deferred >>> constraints are the way SQL provides to do it. >> >> [DD] Once again, those cases are more the exception than the norm. Deferred >> constraints are only "required" when faced with circular FKs, since the >> work-around of using an appropriate order for your statements works for all >> other cases. > > I think its more considered an exception because it is typically hard to do so > people don't try. But if arbitrary database constraints were easy, people > would > probably be used to them and make more use, so less exceptional. > >>> Also, having to enter records in a specific order, eg parent/child, is a >>> contrivance given you're dealing with what should be a set-oriented >>> database, and a contrivance that can make using the database more >>> difficult. Ideally you just insert all the records in a change set at >>> once, and only the total effect of the change set is what is important for >>> enforcing constraints. SQL immediate constraints break this ease of use. >> >> [DD] That's ease of use you pay dearly for in terms of usability though. >> And that order-agnostic benefit you claim is IMHO "artificial" and more a >> "mathematical" concept than a physical reality. As an analogy, it's a bit >> like asking to create a file first in a non-existent directory, and later >> create that missing directly. The real world often requires to do things in >> a specific order, and schemas do model the real world most times, so being >> order dependent seems entirely "natural" to me. My $0.02. --DD > > While I agree that in the real world some things need to be in order, that > isn't > as true in the computer. Its all a matter of abstraction. > > In your analogy, one wants to create a file AND a directory; they should just > be > able to tell the system they want to create those 2 items as a single change, > and have it happen, without having to worry about order. > > But more importantly, a relational database is different than a file system, > and > what I propose is more appropriate there. I'm talking about putting records > in > several tables at once using a single SQL statement, which is like creating a > set of files only and not also directories, apples and oranges. > > That being said, your file/directory analogy is more like creating a table and > populating it in one statement, which incidentally can be done with a CREATE > TABLE AS SELECT statement, though combining actions on other tables into the > same statement isn't provided by SQL. > > -- Darren Duncan
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
On 2015-11-18 1:58 AM, Dominique Devienne wrote: > On Wed, Nov 18, 2015 at 10:17 AM, Darren Duncan > wrote: > >> Deferred constraints are definitely a benefit. > >> They allow you to express constraints otherwise not possible, for example >> that a record may exist in table X if and only if a counterpart exists in >> table Y, such as balancing records in a double-entry accounting system. >> Granted all you really need for this is the ability to change multiple >> tables as a single atomic operation, but failing that ability, deferred >> constraints are the way SQL provides to do it. > > [DD] Once again, those cases are more the exception than the norm. Deferred > constraints are only "required" when faced with circular FKs, since the > work-around of using an appropriate order for your statements works for all > other cases. I think its more considered an exception because it is typically hard to do so people don't try. But if arbitrary database constraints were easy, people would probably be used to them and make more use, so less exceptional. >> Also, having to enter records in a specific order, eg parent/child, is a >> contrivance given you're dealing with what should be a set-oriented >> database, and a contrivance that can make using the database more >> difficult. Ideally you just insert all the records in a change set at >> once, and only the total effect of the change set is what is important for >> enforcing constraints. SQL immediate constraints break this ease of use. > > [DD] That's ease of use you pay dearly for in terms of usability though. > And that order-agnostic benefit you claim is IMHO "artificial" and more a > "mathematical" concept than a physical reality. As an analogy, it's a bit > like asking to create a file first in a non-existent directory, and later > create that missing directly. The real world often requires to do things in > a specific order, and schemas do model the real world most times, so being > order dependent seems entirely "natural" to me. My $0.02. --DD While I agree that in the real world some things need to be in order, that isn't as true in the computer. Its all a matter of abstraction. In your analogy, one wants to create a file AND a directory; they should just be able to tell the system they want to create those 2 items as a single change, and have it happen, without having to worry about order. But more importantly, a relational database is different than a file system, and what I propose is more appropriate there. I'm talking about putting records in several tables at once using a single SQL statement, which is like creating a set of files only and not also directories, apples and oranges. That being said, your file/directory analogy is more like creating a table and populating it in one statement, which incidentally can be done with a CREATE TABLE AS SELECT statement, though combining actions on other tables into the same statement isn't provided by SQL. -- Darren Duncan
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
On 2015-11-18 1:27 AM, Yuri wrote: > On 11/18/2015 01:17, Darren Duncan wrote: >> >> Deferred constraints are definitely a benefit. >> >> They allow you to express constraints otherwise not possible, for example >> that >> a record may exist in table X if and only if a counterpart exists in table Y, >> such as balancing records in a double-entry accounting system. Granted all >> you really need for this is the ability to change multiple tables as a single >> atomic operation, but failing that ability, deferred constraints are the way >> SQL provides to do it. > > I agree they can be beneficial, but not in all cases. Depends on what you do. > It > would have been great if it was an option, ex. "CONSTRAINT LEVEL > [STATEMENT|TRANSACTION];". You can declare that behavior individually per foreign key constraint, and you can also change it at runtime with https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys if that's what you were looking for. -- Darren Duncan
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
On 2015-11-18 12:50 AM, Yuri wrote: > On 11/18/2015 00:45, Dominique Devienne wrote: >> True. But that's in the case where FK constraints validation is deferred to >> the transaction end. > > Why does SQLite defer constraint violation errors? Is there a benefit? > This only complicates things, I would rather see the statement fail > immediately. Deferred constraints are definitely a benefit. They allow you to express constraints otherwise not possible, for example that a record may exist in table X if and only if a counterpart exists in table Y, such as balancing records in a double-entry accounting system. Granted all you really need for this is the ability to change multiple tables as a single atomic operation, but failing that ability, deferred constraints are the way SQL provides to do it. Also, having to enter records in a specific order, eg parent/child, is a contrivance given you're dealing with what should be a set-oriented database, and a contrivance that can make using the database more difficult. Ideally you just insert all the records in a change set at once, and only the total effect of the change set is what is important for enforcing constraints. SQL immediate constraints break this ease of use. -- Darren Duncan
[sqlite] How hard is it to add the constraint name to the 'FOREIGN KEY constraint failed' message?
I think there's a solution for this, which is, loosely, to run the constraint tests twice, conditionally. That is, run it the current fast way as usual, and then only if there is a failure, run the tests again the slower way that keeps track of things so we know where the failure is. Since we only get the slowdown in the failure case, when the code is aborting anyway, it should be ok. The only negative then is that the code size increases somewhat, but it doesn't have to increase as much because the second run isn't to determine whether there will be a failure but to inform on a failure we already know happened. This add-on could also be a compile-time option to exclude if desired. -- Darren Duncan On 2015-11-17 2:32 PM, Richard Hipp wrote: > On 11/17/15, Yuri wrote: >> This message always leaves the user wondering: "Which constraint?" >> >> How hard is it to add this information to the message? Is this a matter >> of memorizing the ID of the constraint, and then printing its name in >> the message? > > It is a substantial change (basically a complete rewrite of the entire > foreign key constraint mechanism) which would negatively impact both > space and performance. > > The current foreign key constraint mechanism uses a single counter. > As constraints are violated, the counter increments, and as > constraints are resolved the counter decrements. At the end, if the > counter is greater than zero then a "foreign key constraint" error is > issued. > > To provide information about which constraint(s) failed, it would be > necessary to have a bag (a list or hash table or an associative array) > of all the constraints that have been violated and then remove > elements from the bag as constraints are resolved. > > A bag takes more run-time memory than a single counter. (Maybe a lot > more, depending on how many elements it holds.) Adding an element to > a bag takes more time than incrementing a counter. (In particular, > adding an element to a bag probably involves one or more calls to > malloc().) Removing an element from a bag takes more time than > decrementing a counter. >
[sqlite] SQLite list user phishing Alexa
Yes, both times I got the messages so far (and they were different messages, as if the fake Alexia was having an ongoing discussion), they were within an hour of my posting to the list. What most likely is happening is that someone subscribed to the list has been compromised or the scammer is subscribed with a different address than they are sending the scams from. The responses are too quick to just be an archive scrape, maybe. -- Darren Duncan On 2015-10-18 2:31 PM, jose isaias cabrera wrote: > > No, they are not. The emails are coming straight from theAlexa person to the > personal email after a reply. So, someone in the list is grabbing the emails > of > the responder and sending an email right away. > > > -Original Message- From: Richard Hipp > Sent: Sunday, October 18, 2015 6:30 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite list user phishing Alexa > > On 10/18/15, Stephan Beal wrote: >> On Sun, Oct 18, 2015 at 12:08 PM, Richard Hipp wrote: >> >>> I've gotten several. There is no such subscriber on the mailing list. >>> I've asked Mike to look into the matter, but he hasn't found anything >>> yet. >>> >> >> It didn't appear to come directly from the list - i suspect someone is >> scraping the ML archives. >> > > Are the messages you are receiving passing through the sqlite.org > server at any point? > > The server.nsadatemail.com server has been banished from accessing > sqlite.org using iptables. But if they are bypassing sqlite.org, that > won't matter. >
[sqlite] SQLite list user phishing Alexa
On 2015-10-14 10:30 AM, jose isaias cabrera wrote: > Someone is using the mailing list posts to send SPAM to the posters. Just > fyi. > I just received an email from someone name Alexa including pictures. The email > it came from was > > alexa at nsadatemail.com > > Just an fyi. Thanks. This just happened to me as well. I got a direct message with return address alexa at nsadatemail.com like Jose described and it looks like a phishing attempt; it had the email subject "Re: Re: [sqlite] Sqlite good on Windows XP but very very slow on Windows Seven", a post I replied to today. Someone on the SQLite mailing list has been hacked and/or a phisher has subscribed to the list. -- Darren Duncan
[sqlite] Sqlite good on Windows XP but very very slow on Windows Seven
There's also the obvious question of, what SQLite version are you using on each OS? -- Darren Duncan On 2015-10-17 12:57 PM, Simon Slavin wrote: > > On 17 Oct 2015, at 8:53pm, Lucas Ratusznei Fonseca gmail.com> wrote: > >> I am using sqlite for years with my software on Windows XP, no more than 1 >> or 2 milliseconds per transaction (insert), so speed has never been a >> concern. Until now. >> I had to migrate my system to Windows Seven recently, I am still doing >> tests and stuff. It happens that some processes became very slow. Digging >> in the source code, I found out that Sqlite transactions now take about 120 >> milliseconds, which is unacceptable for me. > > What extension does your database file have ? Is it something commonly used > like .db or .123 ? > > Try renaming the database file to have a weird extension like .k7x or .6p2 . > Tell us whether it helps. > > Also try turning off (temporarily, of course) any anti-virus package you have > running. > > Simon.
[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested
Jan, I see no merit to your proposal and plenty of downsides. SQLite's current release schedule works quite well, there is no good reason to formally do feature releases just twice a year, especially with that terrible terrible 9x kludge. There's also no reason to pander to guesses about what Linux distribution managers think about project stability, their knowing version numbers in advance has no value, and they can be explicitly told or read SQLite announcements to know what is stable or not. In reality, distro managers will cut releases on their own schedule, and use whatever's the newest SQLite at the time, and SQLite itself should be released on its own schedule. Also, while some projects like 6-month feature releases, that is far from a concensus. I know a bunch that like annual releases, Postgres and Perl for example, which work well. -- Darren Duncan On 2015-10-09 1:51 AM, Jan Nijtmans wrote: > 2015-10-08 15:38 GMT+02:00 Richard Hipp : >> Several users have proposed that SQLite adopt a new version numbering >> scheme. The proposed change is currently uploaded on the "draft" >> website: >> >> https://www.sqlite.org/draft/versionnumbers.html >> https://www.sqlite.org/draft/releaselog/3_9_0.html >> https://www.sqlite.org/draft/ >> >> If accepted, the new policy will cause the next release to be 3.9.0 >> instead of 3.8.12. And the second number in the version will be >> increased much more aggressively in future releases. >> >> Your feedback on the proposed policy change is appreciated. We will >> delay the next release until there is a semblance of consensus on the >> new policy. > > Reading the other reactions, there seems to be consensus on > the next release being 3.9.0, not 3.8.12. So I hope the delay > will not be that much. Details on the exact definition of > X/Y/Z is not that important to me, but since you ask > > One idea could be to lower the number of 'major' releases > to about twice a year. This means that Linux distributions, > like Ubuntu and Fedora can know in advance which > SQLite release will match their release. > Ubuntu: <https://wiki.ubuntu.com/Releases> > Fedora: <https://fedoraproject.org/wiki/Fedora_Release_Life_Cycle> > (everyone seems to think twice a year is optimal, don't know why) > > If there is a desire for new features to be released in between, > this could be done by intermediate 9x releases, at will. e.g.: > > 3.9.0 - okt 2015 > 3.9.1 - nov 2015 (performance improvement/bugfix only) > 3.9.90 - dec 2015 (well-tested, new feature 1 added + bugfixes) > 3.9.2 - jan 2016 (bugfixes only, without feature 1) > 3.9.91 - feb 2016 (well-tested, new feature 2 added + bugfixes) > 3.10.0 - april 2016 (well-tested, contains feature 1 + 2 + more) > 3.11.0 - okt 2016 > > 3.79.0 - okt 2050 > > 3.99.0 - okt 2060;-) > > Advantage: > 1) less 'major' releases gives the signal to managers that apparently > the software is more stable (even though we know that SQLite's > trunk is very stable always). > 2) No limitation when/what to release. It can be fully driven by the > desire of SQLite consortium members: Whenever a new feature > is implemented and ready to be released, it can always be done > in an official 3.x.9y release, outside of the half-yearly schedule. > 3) No need to adapt the tarball filename. > 4) All 3.x.0 and 3.x.9y releases can be done directly from trunk, > as done now. 3.x.[1-9]+ will generally be done from a branch. > Disadvantage: > 1) 3.x.9y releases will give the signal to managers being less > stable than 3.x,y releases. We know that's not necessarily > true, but that's the price for advantage 1) > > Just my 2c. > > Regards, > Jan Nijtmans
[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested
On 2015-10-08 6:03 PM, Richard Hipp wrote: > On 10/8/15, Darren Duncan wrote: >> >> 2. If two successive versions have an overlapping but not equal API and >> file format, meaning that a subset of data files but not all of such >> readable or >> writeable by one version is readable and writeable by the other, or that a >> subset of code but not all of such that is correctly working against one >> version is likewise against the other, then the X at least should be >> different. >> This mainly is for releases that add or remove or change features. > > SQLite has the additional restriction that it does not break legacy. > It only adds new features. Otherwise, this seems to be a reasonable > description of what I am trying to achieve. Thank you. In that case, I could generalize and simplify my proposal as follows... I would propose that with a W.X.Y semantic version scheme, the parts mean essentially [breaks-backward.breaks-forward.breaks-nothing], by which I mean: 1. If a newer version is incapable of doing something correctly that an older version is capable of doing correctly, such as supporting a particular API call with same behavior or such as reading or writing a particular file format, then the newer version should have a greater W than the older one. This is for when a backwards-compatibility break occurs such as because a feature was removed. The key point is a user can not simply take any code or file that works with the prior version and expect it to work without changes with the newer version. 2. Otherwise, if a newer version is capable of doing something correctly that an older version is incapable of doing correctly, such as supporting a particular API call with same behavior or such as reading or writing a particular file format, then the newer version should have a greater X than the older one. This is for when a forwards-compatibility break occurs such as because a feature was added. The key point is a user can not simply take any code or file that works with the newer version and expect it to work without changes with the prior version. 3. Otherwise, there are no known compatibility breaks, and the newer version only needs to have a greater Y than the older one. Users are free to move in both directions as long as any fixed (or created) bugs don't affect them. Note that while a backwards-compatibility break may happen in the same version as a forwards-compatibility break, such as a feature or API or file format substitution, this doesn't necessarily have to be the case; adding the new and removing the old could be done in separate versions. As an exception to the above definitions, when W is zero, then X is incremented for both backward-breaking and forward-breaking changes (while Y keeps its meaning); once W is greater than zero, that stops being the case. As with before, incrementing a number has no implication on the size of the change, just on how it is treated. For example, a large code refactoring that just affects performance but is non-breaking can still be a Y change. As with before, one can optionally increment a number position without being required to, such as for marketing reasons or to mark a maintenance branch. Note that the main break from my prior proposal is that incrementing W no longer needs to mean independent product or disjoint API etc, though that is allowed; if one wants a position to explicitly mean that only, then I would advocate having an extra digit, where a new leaving one, V, means disjoint, and the others have the meanings I said above. But given SQLite's goals, W may never increase anyway for decades, so we can save on that redundancy. -- Darren Duncan
[sqlite] Proposed new version numbering scheme for SQLite - Feedback requested
Richard, I agree with your proposal herein stated, at least as I understand it. I would propose that with a W.X.Y semantic version scheme, which I think is what you said, the parts mean essentially [disjoint.overlapping.equal], by which I mean: 1. If two successive versions have a disjoint API and file format, meaning separate namespaces as if they were unrelated projects, and they can't read or write each others' files, then the W at least should be different. You do this between SQLite 2 and 3. 2. If two successive versions have an overlapping but not equal API and file format, meaning that a subset of data files but not all of such readable or writeable by one version is readable and writeable by the other, or that a subset of code but not all of such that is correctly working against one version is likewise against the other, then the X at least should be different. This mainly is for releases that add or remove or change features. 3. If two successive versions have an equal API and file format, meaning that all files readable and writeable by one version are likewise by the other, and all code that works correctly with one version's API does so with the other, then the Y at least should be different. This mainly is for releases that just help performance or fix bugs. 4. Optionally a 4th part Z can be used to indicate maturity such as whether it is a pre-production (including RC) release or production, or be used by third party packagers for packaging version etc. Note that my above definition generally is invariant to the arrow of time, so users can either upgrade or downgrade versions using the same rules with the same expectation of compatibility. That is, the concept of forwards-compatibility and backwards-compatibility are effectively treated the same. The only exception regards fixing bugs, as that is a case where something that works with one version wouldn't work with the other, but in that case no one should be purposefully moving to a buggier version. Of course, newer versions should still always have higher numbers in the position incremented than their prior ones, I'm not suggesting otherwise. Note that optionally one can increment a higher-valued position when they otherwise don't need to based on compatibility, such as for reasons of wanting to define a parallel maintenance branch, or such as for marketing reasons. Richard, does that still seem to describe your intentions? -- Darren Duncan On 2015-10-08 6:38 AM, Richard Hipp wrote: > Several users have proposed that SQLite adopt a new version numbering > scheme. The proposed change is currently uploaded on the "draft" > website: > > https://www.sqlite.org/draft/versionnumbers.html > https://www.sqlite.org/draft/releaselog/3_9_0.html > https://www.sqlite.org/draft/ > > If accepted, the new policy will cause the next release to be 3.9.0 > instead of 3.8.12. And the second number in the version will be > increased much more aggressively in future releases. > > Your feedback on the proposed policy change is appreciated. We will > delay the next release until there is a semblance of consensus on the > new policy. >
[sqlite] SQLite version 3.8.12 enters testing
Semantic versioning in general doesn't have any specific format, it just means that within a project the succession of version numbers conveys information by itself. There's no requirement there has to be 3 parts or whatever. I consider SQLite's versioning scheme to both be very appropriate and it is semantic versioning, not just like such. As a digression, for my own projects I like to use a semantic versioning scheme of 4 parts like [major.branch.incompatible.compatible] that works like this (assume generally each increment of a part means subsequent parts reset to zeros): 1. Incrementing 'major' means what it typically means, a substantial rewrite that is free to be arbitrarily different and incompatible with the prior major. 2. The 'branch' is inspired by Perl's versioning scheme, in that it alternates between even and odd numbers where even means production and odd means development. When one is making changes that are large enough that there should be dev releases first, eg alpha/beta/etc, a development branch is created and those releases are x.odd.y.z until they're considered production ready, then, the development branch becomes a maintenance branch and releases become x.even.y.z etc. So example first production releases are 1.0.0.0 or 1.2.0.0 etc while first dev/alpha/etc releases are 1.1.0.0 and 1.3.0.0 etc. Each branch may be but isn't necessarily incompatible with prior ones. 3. Incrementing 'incompatible' means that some change was made that is known to break at least some use case, whatever the reason for it, and this was done without having a separate branch / dev-prod status flip. This includes security fixes that disallow something previously allowed. 4. Incrementing 'compatible' means that the authors consider the change to not break anything / be fully backwards-compatible, whether due to being a new feature or a bug fix. -- Darren Duncan On 2015-10-07 1:13 PM, Scott Robison wrote: > Really, the SQLite3 versioning isn't that far off from Semantic Versioning. > Instead of MAJOR.MINOR.PATCH we have FORMAT.MAJOR.MINOR.PATCH. > > Admittedly, the MAJOR.MINOR parts are a *little* intermingled, but reading > through the release history it is fairly clear that a change in MAJOR > usually results from MAJOR new functionality, MINOR is for relatively MINOR > new functionality, and PATCH is apparently never used outside that context. > > While I personally have no complaints with people who use Semantic > Versioning, I don't see SQLite versioning as being horribly incompatible > with it. In fact, if I were making the decision, I'd keep the current > versioning. >
[sqlite] Is it legal SQL to select a specific row of a group via a HAVING clause?
All of the responses I've seen to this question so far seem logically wrong, or at the very least are different than I would do it, and my proposal is one that should work reliably on any DBMS. You use a subquery in the FROM clause. select currency, price from ( select currency, day, max(time) as time from prices group by currency, day ) as filter inner join prices using (currency, day, time) The issue here is you want to return other details, the price, associated with the latest time per currency-day, and you can't do that in SQL without having a select query nested in another one; the inner determines the latest time per currency-day and the outer one looks up other info related to it. The above example should also perform very efficiently, besides being reliably correct rather than just accidentally correct. -- Darren Duncan
[sqlite] Feedback request: JSON support in SQLite
On 2015-09-11 9:31 AM, Richard Hipp wrote: > On 9/11/15, Petite Abeille wrote: >> serialization of the week > > The json.org website has been up since 2002. JSON itself predates > that. It is roughly the same age as SQLite itself and is older than > SQLite3. I'm thinking that maybe JSON is not just a passing fad. > Could be wrong though. True, I have known for years that JSON has effectively become the new XML, one that is less verbose but evenly flexible. Referring to the spec itself, storing the JSON as a character string is always a safe bet; having a future binary option is good, but AFAIK the primary benefit to it is faster indexing/processing speed, and not so much space; see also the (BSD-licensed) Postgres 9.4+ and its JSONB data type for prior art with a binary storage of JSON that is backwards-compatible, though its indexability is one of its key features. -- Darren Duncan
[sqlite] Aquameta Layer 0: meta - Writable System Catalog for PostgreSQL
On 2015-08-29 2:28 PM, Simon Slavin wrote: > >> On 2015-08-29 10:41 AM, Domingo Alvarez Duarte wrote: >>> It would be nice to have something like this on sqlite too ! >>> >>> http://blog.aquameta.com/2015/08/29/intro-meta/ > > I would prefer a system conforming to the information schema part of SQL-92: > > <https://en.wikipedia.org/wiki/Information_schema> That works so long as the INFORMATION_SCHEMA is exhaustive and it is possible to replicate every last significant detail of a database schema just from the information provided there. If a DBMS' standard database dump includes details that can't be gleaned from the DBMS' INFORMATION_SCHEMA, then the latter isn't sufficiently exhaustive, and would need to be extended in order for an updateable version to fully replace distinct DDL. (Case in point, MySQL is deficient in this way, I know from experience.) -- Darren Duncan
[sqlite] Aquameta Layer 0: meta - Writable System Catalog for PostgreSQL
You're most likely looking at a very outdated version. I'm in the process of rewriting it now to something a lot more refined; the spec version control https://github.com/muldis/Muldis-D/ is the most refined version yet (ignore the 'Outdated' files). The idiomatic syntax is a cross between the C style like most popular application languages and functional language style. I will make an announcement when you can run it, hopefully within about 2 months. -- Darren Duncan On 2015-08-29 2:55 PM, Domingo Alvarez Duarte wrote: > I like the overall idea but of Muldis D (like the aquameta) but the syntax is > a lot cryptic (a la perl), I do not beleive it'll get much traction. > > Cheers ! >> Sat Aug 29 2015 10:38:14 pm CEST CEST from "Darren Duncan" >> Subject: Re: [sqlite] Aquameta Layer 0: meta - >> Writable System Catalog for PostgreSQL >> >> On 2015-08-29 10:41 AM, Domingo Alvarez Duarte wrote: >> >>> It would be nice to have something like this on sqlite too ! >>> >>> http://blog.aquameta.com/2015/08/29/intro-meta/ >>> > >> I won't argue that it is right for SQLite specifically, but I do agree >> with the >> broad principle in general, being able to do all catalog/code manipulation >> as >> data, or homoiconicity. In fact this is a central pillar of my own Muldis D >> >> project, an industrial strength programming language with fully integrated >> database functionality. -- Darren Duncan
[sqlite] Aquameta Layer 0: meta - Writable System Catalog for PostgreSQL
On 2015-08-29 10:41 AM, Domingo Alvarez Duarte wrote: > It would be nice to have something like this on sqlite too ! > > http://blog.aquameta.com/2015/08/29/intro-meta/ I won't argue that it is right for SQLite specifically, but I do agree with the broad principle in general, being able to do all catalog/code manipulation as data, or homoiconicity. In fact this is a central pillar of my own Muldis D project, an industrial strength programming language with fully integrated database functionality. -- Darren Duncan
[sqlite] Enhance the SELECT statement?
On 2015-08-17 11:29 AM, John McKown wrote: > ?WONDERFUL! That just never entered my poor head. I guess that I was too > used to the way I do it in PostgreSQL. The way things are going for me > today, I feel that I'm in the lead role of "Moron Monday".? You know, PostgreSQL supports the same INSERT...SELECT syntax that SQLite does, and in any case that is the idiomatic way to do this operation. http://www.postgresql.org/docs/9.4/static/sql-insert.html So you can do it that way with both. The whole SELECT...INTO thing is more of a relic that should not be used in modern days even when supported, unless its the only option. AFAIK, INSERT...INTO was only ever for use within a SQL stored procedure for assigning to a lexical variable. At least I've never seen it used the way you introduced. -- Darren Duncan
[sqlite] Schema-less JSON SQLite DB?
Have a look at PostgreSQL 9.4 as well and its new JSONB data type. Gives you the goods of relational and hierarchical databases in one place, including the querying and indexing. -- Darren Duncan On 2015-07-13 5:43 PM, Hayden Livingston wrote: > Is there a concept of a schema-less JSON SQLite DB? > > My reason is simple: versioning. We have lot of business metrics that > get updated let's say once a month, and we need to be agile to get > them. Right now, we just put the version in the SQLite file, and then > make sure no queries cross the boundaries. > > Secondly, we have requirements for slightly hierarchal data, i.e. > mostly row form, but then some guy wants to put an object. > > What's the SQLite community heading towards if at all? > > Do others have experiences and requirements similar to this?
[sqlite] Tables and Columns of Database of Whatsapp
Actually, here's a way you can be even more clear legally... Don't download/use WhatsApp yourself, rather have someone else use it, and then give you a copy of their SQLite database it produced. You yourself only look at the SQLite database, and not the program. This is then essentially a clean-room scenario, you're just looking at your data file. -- Darren Duncan On 2015-06-29 3:19 PM, Darren Duncan wrote: > I think a WhatsApp database is analogous to a data file and falls outside the > concept of reverse engineering here. > > If say Microsoft Word had legalize against reverse-engineering it, a > reasonable > person wouldn't expect that to apply to reverse-engineering the format of MS > Word documents, rather just the program. > > At the very least, since WhatsApp databases store user data, it should be > reasonable to understand their structure in order that users can extract their > own data from them reliably. > > -- Darren Duncan > > On 2015-06-29 6:42 AM, John McKown wrote: >> On Sun, Jun 28, 2015 at 7:24 AM, wrote: >> >>> Hi, >>> >>> I teach pupils SQL in school. >>> >>> I want to create exercises about the SQLite database of Whatsapp. >>> >>> Can you tell me the names of tables and the names of columns? >>> >>> For the tables, I'll think of data. >>> >>> Thank you, >>> >>> Bob >>> >> >> I'm going to go a bit sideways on this, I hope it is not objectionable. >> Have you contacted Whatsapp about this? I ask because on their web site at >> https://www.whatsapp.com/legal/, it specifically has legalese saying " >> (iii) you will not attempt to reverse engineer, alter or modify any part of >> the Service;" I am not any kind of a lawyer. But it _might_ be argued >> (similar to Oracle vs. Google on the Java API) that the schema of the >> SQLite data base is "part of the Service" and that, especially by using it >> for teaching purposes, you are "reverse engineering" it. Yes, likely a >> extreme position. But IP lawyers can be sharks. Just myself, personally, >> I'd contact Whatsapp and simply ask permission, perhaps explaining what you >> want to do and why you thought that their DB would be a good teaching >> scenario for your students. >> >> Again, I'm just trying urge caution in today's litigious society. I don't >> mean to imply that you are doing anything illegal or immoral (or fattening >> ).
[sqlite] Tables and Columns of Database of Whatsapp
I think a WhatsApp database is analogous to a data file and falls outside the concept of reverse engineering here. If say Microsoft Word had legalize against reverse-engineering it, a reasonable person wouldn't expect that to apply to reverse-engineering the format of MS Word documents, rather just the program. At the very least, since WhatsApp databases store user data, it should be reasonable to understand their structure in order that users can extract their own data from them reliably. -- Darren Duncan On 2015-06-29 6:42 AM, John McKown wrote: > On Sun, Jun 28, 2015 at 7:24 AM, wrote: > >> Hi, >> >> I teach pupils SQL in school. >> >> I want to create exercises about the SQLite database of Whatsapp. >> >> Can you tell me the names of tables and the names of columns? >> >> For the tables, I'll think of data. >> >> Thank you, >> >> Bob >> > > I'm going to go a bit sideways on this, I hope it is not objectionable. > Have you contacted Whatsapp about this? I ask because on their web site at > https://www.whatsapp.com/legal/, it specifically has legalese saying " > (iii) you will not attempt to reverse engineer, alter or modify any part of > the Service;" I am not any kind of a lawyer. But it _might_ be argued > (similar to Oracle vs. Google on the Java API) that the schema of the > SQLite data base is "part of the Service" and that, especially by using it > for teaching purposes, you are "reverse engineering" it. Yes, likely a > extreme position. But IP lawyers can be sharks. Just myself, personally, > I'd contact Whatsapp and simply ask permission, perhaps explaining what you > want to do and why you thought that their DB would be a good teaching > scenario for your students. > > Again, I'm just trying urge caution in today's litigious society. I don't > mean to imply that you are doing anything illegal or immoral (or fattening > ). > >
[sqlite] Mozilla wiki 'avoid SQLite'
Thanks for your response, James, and I agree with what you've said. My own language family, Muldis D, takes all of those things into account. For examples of this: 1. Muldis D is a general purpose language like for applications but it also is fundamentally savvy to the needs and features of relational and SQL databases (and other kinds of databases). Users of typical application languages would find all the features they're used to having and that they work in familiar ways. Users of typical SQL DBMSs would also find all the features they're used to having and that they work in familiar ways. 2. One killer feature of Muldis D is that one can use one language to work in both the application and database worlds. The long-standing wishes of many developers, to either use SQL features in applications or application language features in the database, will be satisfied. Not to mention the desire to have things just work when moving data between the two. Data types and business logic constraints and routines are all shareable. 3. Another killer feature of Muldis D is that one can losslessly translate any DBMS' version of SQL to it and have it work as they expect. So users' investment in SQL is preserved. Even if the Muldis D syntax isn't a superset of SQL, its feature set is, and so it just takes a translation layer for existing SQL-using applications to continue to work unchanged. This includes the power features of SQL like stored procedures and triggers and custom data types, not just lowest common denominator stuff like simple CRUD. I'm not going to harp on this too strongly as I still have to make Muldis D execute, but hopefully it will by the end of this summer. The reference implementation is stand-alone like a typical application programming language, but subsequent implementations will cross-compile as possible to existing SQL DBMSs so the work in those engines can be leveraged as possible. The reference is standalone so I am not limited by what current DBMSs support to make it work. -- Darren Duncan On 2015-06-18 2:11 PM, James K. Lowden wrote: > On Wed, 17 Jun 2015 22:05:12 -0700 > Darren Duncan wrote: > >> I also believe the world is ripe to have SQL alternatives, its just >> a matter of ones appearing that are compelling to users for real work >> and not just an academic exercise. The fact we're still generally >> with SQL means this hasn't happened yet, but that doesn't mean it >> won't. > > Yes, I've been keeping track of TTM-inspired projects, and tried to > convince Ingres to commercialize its D implementation. What makes > David's andl unusual is that it's a new language atop a DBMS that is > used in production. > > Still, I'm not so sure the world is ready for a better SQL. The > evidence stands against, certainly. We already discarded one -- QUEL -- > which hardly anyone remembers. A great deal of effort has gone into > replacing SQL with less powerful constructs with no theoretical > foundation, and into neutering the DBMS with ORM toys. > > Do not underestimate SQL's enormous inertia and network effect. > Recognize that even if the syntax is better, engineering > challenges remain if the promise of fidelity to the relational model is > to be realized. > > The inertia stems from laziness and ignorance, never in short supply. A > fraction of those who write SQL understand the math and logic > underlying it. Forums like Stack Overflow are rife with questions that > demonstrate as much; the answers are often not much better. If you're > not thinking in terms of relational algebra and are unaware of the > benefits of using logic to accomplish your task, changing syntaxes will > only compound your problems. If you *are* thinking in those terms, > it's still work to learn a new language. It's not clear that a better > syntax -- if that's all you get -- would be seen as a worthwhile effort > for very many people. > > The network effect is important, too. The market recognizes knowledge > of SQL as a skill (even if it underestimates its value). That skill is > portable across jobs and DBMS implementations. It is a way that > programmers communicate to each other across the organization and > time. A new language has to clear a threshold of "better" to be > accepted. > > There are two engineering challenges that come to mind: performance and > set-theory semantics. > > As David outlines in his blog, it's no mean feat to swap out SQL from > any DBMS, including SQLite. Because the query language is > assumed/known to be SQL, query planning, rewriting, transformation, and > optimization is bound up in the grammar of SQL itself. Readers of this > list appreciate how much work goes into that. > > Set-theory touches on the implementation, too. DISTINCT is > i
[sqlite] Mozilla wiki 'avoid SQLite'
I should also clarify that I don't see SQL as *a* language but rather as a family of languages. Each actual SQL language in the family is whatever some particular SQL DBMS actually implements and how it behaves. So eg "SQLite SQL" is *a* SQL language, with generally a distinct version for each SQLite release. Then "SQLite SQL" is a considerably simpler language than say "Oracle SQL" or what have you. But I see this discussion about a SQL alternative to be relative to the SQL language family in general, which in my mind is its use case, rather than just SQLite specifically. That also means that the SQL alternative would in practice be a language family itself, assuming multiple implementations, though hopefully they would be a lot more consistent with each other than the SQL family languages have ended up being. -- Darren Duncan On 2015-06-18 1:27 PM, Darren Duncan wrote: > On 2015-06-18 3:35 AM, ajm at zator.com wrote: >> Darren: >> >> Maybe you're right. But to that relationship of "goodness" that would be >> desirable, perhaps we should add a few drops of pragmatism. Experience shows >> that in many cases, the perfect is the enemy of the good, and despite a >> disrupting transition (refusing the bad legacy of SQL) can avoid some >> drawbacks, however a non-disrupting evolution, has the advantage of being >> more >> easily accepted and assimilated by the users, as has happened before. > > I'm not proposing "perfect" at all, but rather I don't agree that the best > solution is to be perfectly backwards-compatible with SQL such that all SQL > code > is also valid code in the new language, as you are proposing. > > I don't think you realize just how complicated SQL as a whole is. What SQLite > implements is just a small subset of it. Just the core part of the ISO SQL > 9075-2:2011 language spec is about 1466 pages long and that leaves a lot out. > Besides this, various dozens SQL DBMS vendors have their own proprietary > additions or differences. Even just taking a subset an extending that, isn't > innovating beyond existing current practices, and we have dozens of those > variants. > > A lot of existing popular application languages have a much smaller language > complexity than SQL does. > > What I'm proposing as good is something more comparable in complexity to > those, > while still being able to express everything you can in SQL without any > greater > verbosity or difficulty in understanding. > > One primary way to achieve that is to represent nearly all operators / actions > using generic routine call syntax rather than custom syntax per operator, > which > is what languages like C, C++, C#, Java, etc do. For such as these, the > language itself is fairly small, and most of the details are just in the > libraries. SQL supports libraries too, eg stored procedures/functions/etc but > an inordinately large number of built-ins are not expressed in those terms. > > It is very reasonable to have generic syntax function calls to represent the > various parts of a SQL SELECT for example, similar to how we have array or set > or whatever routines in application languages today. That makes the syntax > relatively simple and user-extensible, and it by no means prevents a DBMS from > doing any query rewriting or optimizing or rearranging whatever it wants to do > to implement the over-all expression efficiently, in fact a DBMS may even have > an easier time of it. > > Don't get me wrong, a new language can have these qualities and still look > familiar to SQL users, its not like it means using APL etc. > > -- Darren Duncan > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Mozilla wiki 'avoid SQLite'
On 2015-06-18 3:35 AM, ajm at zator.com wrote: > Darren: > > Maybe you're right. But to that relationship of "goodness" that would be > desirable, perhaps we should add a few drops of pragmatism. Experience shows > that in many cases, the perfect is the enemy of the good, and despite a > disrupting transition (refusing the bad legacy of SQL) can avoid some > drawbacks, however a non-disrupting evolution, has the advantage of being > more easily accepted and assimilated by the users, as has happened before. I'm not proposing "perfect" at all, but rather I don't agree that the best solution is to be perfectly backwards-compatible with SQL such that all SQL code is also valid code in the new language, as you are proposing. I don't think you realize just how complicated SQL as a whole is. What SQLite implements is just a small subset of it. Just the core part of the ISO SQL 9075-2:2011 language spec is about 1466 pages long and that leaves a lot out. Besides this, various dozens SQL DBMS vendors have their own proprietary additions or differences. Even just taking a subset an extending that, isn't innovating beyond existing current practices, and we have dozens of those variants. A lot of existing popular application languages have a much smaller language complexity than SQL does. What I'm proposing as good is something more comparable in complexity to those, while still being able to express everything you can in SQL without any greater verbosity or difficulty in understanding. One primary way to achieve that is to represent nearly all operators / actions using generic routine call syntax rather than custom syntax per operator, which is what languages like C, C++, C#, Java, etc do. For such as these, the language itself is fairly small, and most of the details are just in the libraries. SQL supports libraries too, eg stored procedures/functions/etc but an inordinately large number of built-ins are not expressed in those terms. It is very reasonable to have generic syntax function calls to represent the various parts of a SQL SELECT for example, similar to how we have array or set or whatever routines in application languages today. That makes the syntax relatively simple and user-extensible, and it by no means prevents a DBMS from doing any query rewriting or optimizing or rearranging whatever it wants to do to implement the over-all expression efficiently, in fact a DBMS may even have an easier time of it. Don't get me wrong, a new language can have these qualities and still look familiar to SQL users, its not like it means using APL etc. -- Darren Duncan
[sqlite] Mozilla wiki 'avoid SQLite'
I disagree with the idea that a good SQL alternative would just be a superset of SQL as you propose. That has already been done numerous times, the principal manifestations being each SQL DBMS that has its own small or large differences in syntax and features from each other. SQL is already a very complex language due in part to most of its features each having their own custom syntax, often several variations per feature to boot, as well as lots of arbitrary limitations or specified inconsistent behaviors, a lot of these for keeping backwards compatibility with various old or vendor-specific ways of doing things. What a good SQL alternative would actually be is a much more self-consistent and less redundant than SQL. It would still have all of SQL's expressive power and features so that any SQL code can be translated to it, including automatically, without too much circumlocution. That is how you would simplify the transition and re-utilization of existing code. The good alternative would actually be easier for a DBMS to implement also without losing any power. -- Darren Duncan On 2015-06-17 11:52 PM, ajm at zator.com wrote: > Indeed, I'm agree with Darren, and continuing its thought, perhaps that > hypothetical new language would be a clean extensi?n of SQL in the same way > that C++ was respect to C, simplifying the transition and reutilization of > legacy code. > > Cheers. > > -- > A.J. Millan >> >> Mensaje original >> De: >> Para: "'General Discussion of SQLite Database'"> mailinglistssqlite.org> >> Fecha: Thu, 18 Jun 2015 14:50:40 +1000 >> Asunto: Re: [sqlite] Mozilla wiki 'avoid SQLite' >> >> The question for now is: does a new database programming language have a >> place?
[sqlite] Mozilla wiki 'avoid SQLite'
On 2015-06-17 9:50 PM, david at andl.org wrote: > The question for now is: does a new database programming language have a > place? When you ask the question as broadly as that, the answer is most definitely "yes". Just look at the wider world and you see there are dozens of application programming languages that have widespread use (and hundreds more that are more niche), and new ones that become widespread are appearing at a rate of around 1 a year on average, such as Swift and Go. The fact that these catch on means there are large numbers of people who think that there are worthwhile new languages, that don't just think we already have all the ones we need. I look at the database world in contrast, that is languages that are particularly savvy for the relational model like SQL, and I notice the world is largely passing it by in attempts to make a serious alternative. I also believe the world is ripe to have SQL alternatives, its just a matter of ones appearing that are compelling to users for real work and not just an academic exercise. The fact we're still generally with SQL means this hasn't happened yet, but that doesn't mean it won't. -- Darren Duncan
[sqlite] Mozilla wiki 'avoid SQLite'
On 2015-06-16 11:44 AM, James K. Lowden wrote: > On Tue, 16 Jun 2015 09:56:38 +1000 wrote: > >> The question is: what should a database language do? Andl can already >> match or surpass SQL on database programming tasks, but is that >> interesting enough? >> >> What would make a database programming better, or best? > > Two things I've often pointed to are namespaces and regular > expressions. Another is compound datatypes. > > SQL and C both suffer from a single variable namespace. We get around > it by using prefixes, e.g., "local_memcpy" or "annualized_returns". > > C++ added namespaces to the language. I suggest SQL's successor do the > same, but use the Unix filesystem's hierarchical namespace as a model. > Putatively, SQL in general actually DOES have namespaces in practice, though they are fixed-depth rather than variable-depth. Depending on the DBMS, you have the "catalog" level, the "schema" level, the Oracle "package" level, and then your functions and tables etc have otherwise unqualified names beneath those. Depending on the DBMS, each of those levels may exist or not, but the number of levels is fixed, that part unlike the Unix filesystem. I think the SQL standard specifies catalog/schema/object itself. For my part with my database-savvy Muldis D programming language (that I hope to have executing within a few months), namespaces for both routines and relations (tables) etc are arbitrary/variable depth like you propose, like either the Unix file system or like programming languages such as Perl or C# or others. Note that Muldis D and Andl have some influences in common, but David beat me to market as it were with an executable. -- Darren Duncan
[sqlite] relational algebra vs calculus (was Re: Mozilla wiki 'avoid SQLite')
Actually, SQL does a mixture of both relational calculus and relational algebra, but not necessarily all of either. Seeing the algebra is fairly straightforward, eg the UNION or WHERE etc. An example of the calculus is seen in SELECT...FROM... involving a JOIN, for example: SELECT person.name, person.age, department.deptname FROM people person INNER JOIN departments department ON (department.deptid = person.deptid); In SQL, each named source in the FROM clause is effectively a FORALL (or something) over a set (eg people) which has an associated variable which has the value of a set element in turn (eg person). Its commonly misunderstood that declaring an alias in FROM is aliasing the whole table, but it isn't, rather it is aliasing a single record of the table; if you don't have an explicit alias, the record variable defaults to the same name as the table. My above example should make more clear what's going on. -- Darren Duncan On 2015-06-15 5:46 PM, Christopher Vance wrote: > With a relational model you have a choice between relational calculus or > relational algebra. SQL does one of them. > > I have used a language which did the other, long ago in a land far away. > > On Tue, Jun 16, 2015 at 9:56 AM, wrote: > >>>>> I think the best database language should reflect how earthlings think >> about the data, and the best computer programming language would reflect >> easily the result we want get from them. >> >> Care to expand on that? >> >> I'm developing a new database language: Andl. My starting point has been >> the >> relational model, Codd-Date-Darwen and The Third Manifesto. My (only) >> competitor seems to be SQL, which has a 40+ year lead. Nothing like a >> challenge! >> >> The question is: what should a database language do? Andl can already match >> or surpass SQL on database programming tasks, but is that interesting >> enough? >> >> What would make a database programming better, or best? >> >> Regards >> David M Bennett FACS >> >> Andl - A New Database Language - andl.org
[sqlite] This mailing list seems to be public
Like most mailing lists about open source software, list archives are visible to the general public, which is what I think should be the norm. Generally restrictions to members just concern posting, its a simple way to keep spam out, one has to confirm an email address to post. -- Darren Duncan On 2015-06-15 4:42 AM, david at andl.org wrote: > I was under the impression that this mailing list was restricted to members. > However: > > http://sqlite.1065341.n5.nabble.com/Under-what-circumstances-can-a-table-be- > locked-when-the-database-is-first-opened-td82371.html > http://comments.gmane.org/gmane.comp.db.sqlite.general/95119 > http://osdir.com/ml/general/2015-06/msg09791.html > > Is this official, or what? > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org
[sqlite] How mature/stable is SQLite 4 now? ETA?
More like It'll be out in time for Christmas, where the specific year isn't mentioned. -- Darren Duncan On 2015-05-23 11:09 AM, Mikael wrote: > This sounds like it means we'll have it 2.5-5 years then.. so 2018 maybe, > > Sounds about correct? :) > > > 2015-05-23 23:06 GMT+05:30 Stephen Chrzanowski : > >> SQLite4 is a dev "toy". It isn't going to be released any time soon. >> >> On Sat, May 23, 2015 at 6:09 AM, Mikael wrote: >> >>> SQLite4 looks neat! >>> >>> Last code commit was in September, is this because it's so stable or >>> because other priorities took over? >>> >>> (https://sqlite.org/src4/tree?ci=trunk) >>> >>> Thanks! >>> Mikael
[sqlite] AUTOINC vs. UUIDs
On 2015-05-21 9:14 PM, Stephen Chrzanowski wrote:
> {{I just got a bounced message.. Reposting}}
Both of your attempts got through. You got a bounce because you sent it to an
invalid list address in addition to a valid one; one bounced the other didn't.
> I've been watching this thread from the beginning with great interest, and
> I still don't see the difference between using a UUID or an auto-inc
> integer as a PK at the very raw, basic level. The database will only see
> them as a string of bits or bytes and handle accordingly. IMO, using UUID
> is an extra overhead for humans to deal with, which is going to cause more
> grief than necessary.
Personally I'm a strong advocate of using natural keys only wherever possible,
which also has an effect on how you design your database. When followed
judiciously, it can lead to very well designed databases.
While table-specific surrogate keys like auto-inc or uuids make sense in some
situations, they are greatly over used, and most of the time natural keys can
be
used instead. I've seen many people use surrogate keys when there were
perfectly suitable natural keys available instead.
As to auto-inc vs uuids, the main difference I see is that the former gives you
tighter coupling to the database or between rows than is otherwise necessary.
To explain, when you use auto-inc, you are depending on the database to tell
you
what your row identifiers are, which gets more complicated if you're entering a
set of related records where you want to use the same identifiers in multiple
tables to indicate related records, eg parent-child.
When you don't use auto-inc, you can know in advance in the application before
talking to the database what the complete values of all your new rows are, you
know what values you are using to relate records to each other, you don't have
to insert one row to know how to associate other rows.
Also when you auto-inc, values tend to be serial, so the order you insert
records affects their final values, where when you don't auto-inc, your order
of
insertion has no impact on their final values (in the absense of triggers).
In that respect uuids can be better than auto-inc because you lose those
coupling problems. On the other hand uuids themselves should be used very
sparingly, and I haven't really seen a reason to use them yet.
-- Darren Duncan
[sqlite] AUTOINC vs. UUIDs
I agree with Keith and disagree with Simon. Regarding the current state for sequence generators that are specific to user databases, storing that state inside the same databases where they are used is exactly the right place. Everything necessary to understand a database should be stored in that database, this is the correct way to do it, there is no wall being broken. Sequence generators are NOT private, just as the ROWIDs of SQLite tables are NOT private either. Users can and do directly access this information. A sequence generator is nothing more than an abstraction over a variable in a database (a SQL "table" is also a variable in a database) combined with an atomic routine to return its current value plus optionally increment that variable. A table with an auto-increment column is nothing more than an ordinary table with an on-insert triggered action to invoke said routine and use its value to modify the incoming row. Some DBMSs don't store sequence generator state as their own thing, sometimes they just use the actual row value of the table being auto-incremented itself. Either way the principle is the same. Some DBMSs perform the sequence state variable increment in an autonomous transaction that automatically commits before the main one, so that even if the transaction using the sequence generator rolls back, the sequence generator doesn't repeat the same values; others just keep this increment in the main transaction and so also can be rolled back and repeat. That's all that standard SQL/etc sequence generators are, regular user data, and one shouldn't be fooled into thinking they are something else. -- Darren Duncan On 2015-05-20 4:21 PM, Keith Medcalf wrote: > All relational database engines store configuration data within the users' > database. Many of them just hide it behind varying layers of logically > imposed complication. For example, you could simulate SQL Servers' > obfuscation by simply changing the name of the primary database alias from > "main" to "master" in the SQLite code, and then requiring that all "user" > tables are stored in an attached database. > >> -Original Message- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin >> Sent: Wednesday, 20 May, 2015 14:38 >> To: sqlite-users at mailinglists.sqlite.org >> Subject: Re: [sqlite] AUTOINC vs. UUIDs >> >> On 20 May 2015, at 8:52pm, Kees Nuyt wrote: >> >>> The autoincrement clause causes an entry in the sqlite_sequence >>> table. >> >> It's interesting that SQLite uses tables inside the user database for >> private purposes like this. A certain wall is broken when the designers >> choose this option. SQLite does it for sqlite_master, sqlite_sequence, >> sqlite_stat*, and probably others I've forgotten. >> >> SQLite is handicapped by having no permanent data storage location. It >> has nowhere to store configuration information apart from inside the >> user's databases or as compilation settings. This is very unusual but, I >> think, contributes a lot to how portable SQLite is: no need to understand >> folder structure or safe places to keep configuration information; >> increased startup-speed; reduced code size, fewer file handles, slightly >> reduced memory. >> >> Simon.
[sqlite] AUTOINC vs. UUIDs
On 2015-05-21 12:16 PM, James K. Lowden wrote: > On Wed, 20 May 2015 19:05:29 +0100 > Simon Slavin wrote: > >> Posting this not because I agree with it but because the subject has >> come up here a couple of times. >> >> <https://www.clever-cloud.com/blog/engineering/2015/05/20/Why-Auto-Increment-Is-A-Terrible-Idea/ >>> >> >> "Today, I?ll talk about why we stopped using serial integers for our >> primary keys, and why we?re now extensively using Universally Unique >> IDs (or UUIDs) almost everywhere." > > "A relational database is a graph where nodes are called > entities and edges relations." > > It's hard to know where to start. But that's definitely not it. Indeed, that article had a rather poor idea what a relational database is. For one thing, it didn't seem to grasp that each entire tuple/row in a relation/table is by definition its own identity. If no subset of attributes/columns is a key, the entire tuple/row can be used to identify which tuple/row you're talking about. -- Darren Duncan
[sqlite] Please confirm what I THINK I know about blobs
In addition to this, where a BLOB represents something that could often be a file on a disk, methods used to identify the types of those files could often be used. For example, with many binary file types the first few bytes of the file are signatures for its type, eg with JPEG files for example. This can't be counted on for all BLOBs, but can work for some. Meta-data is good to have. -- Darren Duncan On 2015-05-09 3:20 AM, Richard Hipp wrote: > On 5/9/15, William Drago wrote: >> All, >> >> Say you encounter a blob in a database. There's no way to >> tell if that blob carries bytes, floats, doubles, etc, correct? > > As far as SQLite is concerned, a BLOB is just bytes. The > interpretation of those bytes (as floats, doubles, a JPEG thumbnail, a > file compressed with zlib, etc.) is entirely up to the application - > SQLite does not know or care. > >> >> Assuming the above is true, then is it always prudent to >> store some metadata along with your blobs so that they can >> be identified in the future? >> > > I suppose that depends on the application. If the field always holds > exactly the same thing (ex: a JPEG) then the metadata is not really > needed. On the other hand, I have added a separate "mimetype" column > to accompany BLOB fields in cases where the BLOB might be different > things. >
[sqlite] Two different Order By in one statement
I don't know what you mean by "technically impossible". What Bart asked for is entirely possible, and commonly done. You very well can put an arbitrary expression in an ORDER BY clause, including a CASE statement, so that it does what is requested. Failing that, you can use an inner query in the FROM clause to generate the fields you want to sort on, and then ORDER BY on them in the outer query. -- Darren Duncan On 2015-03-31 5:31 PM, R.Smith wrote: > On 2015-04-01 01:50 AM, Bart Smissaert wrote: >> Say I have a table with 3 fields. Depending on a value in field 1 (this >> value will be either 1 or 2) >> I want to do a different sort order sorting on fields 2 and 3. >> This will be either order by field2 desc, field3 desc or field3 desc, >> field2 desc. >> I thought of a union, but doesn't allow this. >> Any suggestions? > > Well, this is technically impossible (influencing an SQL statement from the > values returned from that statement is impossible for obvious reasons). > > But, if you do this: > > SELECT Field1, Field2, Field3, Field4, (CASE Field1 WHEN 1 THEN Field2 ELSE > Field3) AS Sort1, (CASE Field1 WHEN 0 THEN Field2 ELSE Field3) AS Sort2 > FROM SomeTable > WHERE 1 > ORDER BY Sort1, Sort2; > > You should achieve the exact result. > > NOTE: This is a really really bad way of doing things, the sort order should > not > be determined like this in any sane system - those settings should live in a > DB > or at least a table outside of the data being inspected.
[sqlite] When to disambiguate column names in queries?
If you design your database schemas such that, where possible, corresponding columns have the same names in all tables, and you do natural joins, the problem will basically go away. -- Darren Duncan On 2015-03-16 9:16 AM, Drago, William @ CSG - NARDA-MITEQ wrote: > All, > > Some of my Select statements are pretty long and I'm starting to think it'd > be a good idea to always include table names of columns instead of just when > they are not unique. This would make the Select statements longer, but > perhaps easier to understand if the reader knows where each column is from. > > Any thoughts on this? I realize something like this can be highly subjective, > but I'm wondering if there's a generally accepted practice in the SQLite > world. > > Thanks, > -- > Bill Drago > Senior Engineer > L3 Narda-MITEQ<http://www.nardamicrowave.com/> > 435 Moreland Road > Hauppauge, NY 11788 > 631-272-5947 / William.Drago at L-3COM.com<mailto:William.Drago at L-3COM.com> > > > CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any > attachments are solely for the use of the addressee and may contain > information that is privileged or confidential. Any disclosure, use or > distribution of the information contained herein is prohibited. In the event > this e-mail contains technical data within the definition of the > International Traffic in Arms Regulations or Export Administration > Regulations, it is subject to the export control laws of the U.S.Government. > The recipient should check this e-mail and any attachments for the presence > of viruses as L-3 does not accept any liability associated with the > transmission of this e-mail. If you have received this communication in > error, please notify the sender by reply e-mail and immediately delete this > message and any attachments. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] C++ ORM
On 2015-03-09 3:25 PM, Simon Slavin wrote: > On 9 Mar 2015, at 9:38pm, Scott Robison wrote: > >> A co-worker who is working on a project is interested in finding out if >> there is an effective ORM for C++ / SQLite. I've not used one so I'm >> turning to the list to see if anyone has a recommendation. > > For those playing along at home, ORM == Object Relational Mapping. In other > words you do Object-oriented programming in C++ and the objects are stored in > a SQLite database. > > I'm not aware that this problem has been solved well in any language or with > any database engine. It might seem like a terrific oppotunity to write a > demonstration library, but differences in how languages do OO and how > databases store data seem to make this a difficult problem to crack. I'd be > interested in any solutions that use SQLite with any popular OO language. I am developing a project right now that aims to out-do ORM at its own game, by changing the paradigm so the relational database and application programming realms are unified into one environment, where relations and tuples are first-class types you can use in applications, and the same arbitrary user-defined types you can use in applications can be used directly in databases, no "mapping" required. I hope to have a first version executing in about 2 months. -- Darren Duncan
[sqlite] restructuring databases (was Re: Can I copy one column of data to another table?)
On 2015-03-07 9:59 AM, Simon Slavin wrote: > On 7 Mar 2015, at 4:42pm, Dave wrote: > >> I am fairly new at this although I have wanted to learn and tried again and >> again...But I have a problem. I created a database and probably did it wrong >> and I am trying to fix it. I made a database with 7 tables in it all with a >> primary key and a record ID that matches the primary key. Now when trying to >> use the database I see that I should have made 1 table with all the related >> data (I think) and am trying to copy one column of data at a time to the >> "main" table. Can that be done and if so how? > > Without going into your situation in detail, I have a suggestion which may > help you approach the problem another way. The SQLite shell tool has a > '.dump' command which turns a database into SQL commands, and a '.read' > command which uses the commands to create schema and data in a new database. > > So dump the database into a text file. Then you can use editing tools > (usually global find-and-replace) mess with the text file so that all the > inserting is done to the same table. Then you can create your new database > by reading the altered text file. Frankly the idea (proposed by Simon here) of solving this by dumping everything to a text file and manipulating it there with editing tools sounds abysmal to me. The only time one might consider that reasonable is if the total number of records is just a handful and you're essentially just re-entering them from scratch. Once you've already got your data in SQLite, the best general solution by far is to use SQL to manipulate it; if you can't, you've already lost. What you want to do is create new table(s) with the new format you want, and then do INSERT INTO SELECT FROM such that the SELECT easily and reliably does all the hard work of collecting up all the data from the old tables and rearranging it into the new format. Depending on the complexity of the task, you may also create temporary tables for intermediate stages of the processing. Solving the problem with the likes of SQL UPDATE is hard, but using SELECT is easy. By a similar token, I believe SQL is often the best place to clean up data from external sources. Create temporary tables that are very lax in format and constraints that take the external data as pristine as possible, load into those, and then use SELECTs/etc to derive cleaner versions from those into the final tables (or other intermediaries), and you can use the SQL powers to filter or compensate for dirty data etc. Especially useful for dealing with duplicate data in the source, find or handle with SELECT GROUP BY etc rather than trying conditional INSERT logic or what have you. -- Darren Duncan
[sqlite] List duplication
So far so good. I only got one copy of your test message. I also sent my own message to just sqlite-users at sqlite.org and it was bounced as expected. -- Darren Duncan On 2015-03-02 8:14 PM, Mike Owens wrote: > Okay, I blocked the sqlite-users at sqlite.org address in the to address > so if it is sent alone, it will be blocked. > > On Mon, Mar 2, 2015 at 9:46 PM, Mike Owens wrote: >> Oh okay. I see. I'll look into it. >> >> On Mon, Mar 2, 2015 at 9:23 PM, Darren Duncan >> wrote: >>> >>> So in that case, still have the SQLite mail server reject messages to the >>> old list rather than forwarding them, and let the problematic MUAs deal with >>> it. The key thing is that by not forwarding but rejecting, the mail server >>> isn't sending out 2 copies of messages directly, and the rejecting is >>> reminding people to pay attention until the issue as a consequence goes >>> away. Thus any explicit Reply-To headers can be left unmunged by the list >>> server. -- Darren Duncan >>> >>> >>> On 2015-03-02 7:10 PM, Mike Owens wrote: >>>> >>>> The problem is that this is the very bone of contention in the reply-to >>>> religious war. Is it not? I may be wrong, but I thought this is the very >>>> setting that people get so defensive about changing. As we have it now, >>>> people have a suitable default pointing back to the (correct) list but >>>> also >>>> the freedom to change the reply-to header should they want to. If we >>>> strip >>>> the reply-to header in order to correct for the problematic MUA's, then >>>> the >>>> latter freedom is lost. And if I remember correctly, some people get very >>>> angry about this. >>>> >>>> >>>> On Mon, Mar 2, 2015 at 8:18 PM, Darren Duncan >>>> wrote: >>>> >>>>> On 2015-03-02 6:14 PM, Mike Owens wrote: >>>>> >>>>>> On Mon, Mar 2, 2015 at 5:27 PM, R.Smith wrote: >>>>>> >>>>>>> Ah, thank you, all makes sense now. If you change the first option to >>>>>>> YES >>>>>>> then nobody else's quirky reply-to headers will get into the list, and >>>>>>> the >>>>>>> second option remains as is (it should be setting the standard >>>>>>> @mailinglists reply-to field) - this should solve the duplication >>>>>>> issue, >>>>>>> but if it is disagreeable to anyone, more consideration is needed. >>>>>>> >>>>>> >>>>>> I almost don't want to even speak of this for fear that this issue will >>>>>> raise it's ugly head again. Per the Mailmain documentation ( >>>>>> http://www.gnu.org/software/mailman/mailman-admin/node11.html): >>>>>> >>>>>> Beware! Reply-To: munging is considered a religious issue and the >>>>>> policies >>>>>> >>>>>>> you set here can ignite some of the most heated off-topic flame wars >>>>>>> on >>>>>>> your mailing lists. We'll try to stay as agnostic as possible, but our >>>>>>> biases may still peak through. >>>>>>> >>>>>>> >>>>>> That's as much as I'll say about that. >>>>>> >>>>> >>>>> Well it doesn't have to be complete munging, rather just enough munging >>>>> to >>>>> remove references to the old mailing list name. -- Darren Duncan
[sqlite] List duplication
So in that case, still have the SQLite mail server reject messages to the old list rather than forwarding them, and let the problematic MUAs deal with it. The key thing is that by not forwarding but rejecting, the mail server isn't sending out 2 copies of messages directly, and the rejecting is reminding people to pay attention until the issue as a consequence goes away. Thus any explicit Reply-To headers can be left unmunged by the list server. -- Darren Duncan On 2015-03-02 7:10 PM, Mike Owens wrote: > The problem is that this is the very bone of contention in the reply-to > religious war. Is it not? I may be wrong, but I thought this is the very > setting that people get so defensive about changing. As we have it now, > people have a suitable default pointing back to the (correct) list but also > the freedom to change the reply-to header should they want to. If we strip > the reply-to header in order to correct for the problematic MUA's, then the > latter freedom is lost. And if I remember correctly, some people get very > angry about this. > > > On Mon, Mar 2, 2015 at 8:18 PM, Darren Duncan > wrote: > >> On 2015-03-02 6:14 PM, Mike Owens wrote: >> >>> On Mon, Mar 2, 2015 at 5:27 PM, R.Smith wrote: >>> >>>> Ah, thank you, all makes sense now. If you change the first option to YES >>>> then nobody else's quirky reply-to headers will get into the list, and >>>> the >>>> second option remains as is (it should be setting the standard >>>> @mailinglists reply-to field) - this should solve the duplication issue, >>>> but if it is disagreeable to anyone, more consideration is needed. >>>> >>> >>> I almost don't want to even speak of this for fear that this issue will >>> raise it's ugly head again. Per the Mailmain documentation ( >>> http://www.gnu.org/software/mailman/mailman-admin/node11.html): >>> >>> Beware! Reply-To: munging is considered a religious issue and the policies >>> >>>> you set here can ignite some of the most heated off-topic flame wars on >>>> your mailing lists. We'll try to stay as agnostic as possible, but our >>>> biases may still peak through. >>>> >>>> >>> That's as much as I'll say about that. >>> >> >> Well it doesn't have to be complete munging, rather just enough munging to >> remove references to the old mailing list name. -- Darren Duncan
[sqlite] List duplication
On 2015-03-02 6:14 PM, Mike Owens wrote: > On Mon, Mar 2, 2015 at 5:27 PM, R.Smith wrote: >> Ah, thank you, all makes sense now. If you change the first option to YES >> then nobody else's quirky reply-to headers will get into the list, and the >> second option remains as is (it should be setting the standard >> @mailinglists reply-to field) - this should solve the duplication issue, >> but if it is disagreeable to anyone, more consideration is needed. > > I almost don't want to even speak of this for fear that this issue will > raise it's ugly head again. Per the Mailmain documentation ( > http://www.gnu.org/software/mailman/mailman-admin/node11.html): > > Beware! Reply-To: munging is considered a religious issue and the policies >> you set here can ignite some of the most heated off-topic flame wars on >> your mailing lists. We'll try to stay as agnostic as possible, but our >> biases may still peak through. >> > > That's as much as I'll say about that. Well it doesn't have to be complete munging, rather just enough munging to remove references to the old mailing list name. -- Darren Duncan
[sqlite] List duplication
On 2015-03-02 6:08 PM, Mike Owens wrote: > On Mon, Mar 2, 2015 at 5:24 PM, Darren Duncan > wrote: >> As near as I can tell, the Reply-To header from this list only contains >> sqlite-users at mailinglists.sqlite.org and does not also contain >> sqlite-users at sqlite.org so therefore I don't see the problem you're >> stating. But if it sometimes does so, then the list manager needs to ensure >> that sqlite-users at sqlite.org is never in the Reply-To header of messages >> from the list. -- Darren Duncan > > It is. As mentioned above, the explicit reply-to header is empty. I think > some people's MUA's are doing this. I just answered an off-list email from > somebody and the reply to was set to the sqlite-users at sqlite.org email, > which makes me think it is the MUA and not Mailman. A benefit of my proposal is, if implemented, the instances of other people's explicit reply-to headers to sqlite-users at sqlite.org will disappear very quickly, as the messages they are replying to will be ones propagated through the list after the change rather than before. -- Darren Duncan
[sqlite] List duplication
On 2015-03-02 3:04 PM, R.Smith wrote: > On 2015-03-03 12:42 AM, Darren Duncan wrote: >> I think that what needs to be done is for each foo at sqlite.org to return an >> error/undeliverable message if someone sends a message to it, citing that all >> messages must be explicitly sent to the corresponding >> foo at mailinglists.sqlite.org. That should handily solve the problem. -- >> Darren >> Duncan > > I see where you are coming from, but if the Reply-To field contains 2 email > addresses and then the server penalizes you for using one of them, that might > go > down in history as the most-evil mailing-list quirk of all time. As near as I can tell, the Reply-To header from this list only contains sqlite-users at mailinglists.sqlite.org and does not also contain sqlite-users at sqlite.org so therefore I don't see the problem you're stating. But if it sometimes does so, then the list manager needs to ensure that sqlite-users at sqlite.org is never in the Reply-To header of messages from the list. -- Darren Duncan
[sqlite] List duplication
I think that what needs to be done is for each foo at sqlite.org to return an error/undeliverable message if someone sends a message to it, citing that all messages must be explicitly sent to the corresponding foo at mailinglists.sqlite.org. That should handily solve the problem. -- Darren Duncan On 2015-03-02 10:37 AM, Mike Owens wrote: > For what it is worth, the move to mailinglists.sqlite.org is a result of > the Mailman web interface having to be hosted under the following two > constraints: > > 1. It must be on port 80 > 2. It cannot be on sqlite.org port 80 > > I explained this reasoning in a previous email. The short version is > because we are using two web servers on the VM that hosts both the > sqlite.org website and fossil repos (althttpd) and the Mailman web > interface (Apache). We previously did this on a single IP where mailman was > on port 8080. However, we had a significant number of complaints from > people who could not reach the Mailman web interface via sqlite.org:8080 > due to firewall restrictions in their respective locations. So we did what > we could to move it to port 80. > > So to satisfy these two constraints, mailinglists.sqlite.org was born. > Unless somebody else knows better, Mailman does not allow one to use two > domains for a given list. Either something will screw up with the mail > routing or in the web interface if you try to use more than one. You have > to pick one domain and stick with it. Thus I could not continue to support > both the previous sqlite.org (:8080) domain and the new > mailinglists.sqlite.org (:80) for the users list. So I made the move from > the one to the other. > > Regarding the reply-to policy. I honestly don't remember the reasoning > behind it. I know there was a big long discussion about it in the past > (search the list) and after the dust settled we chose the current policy > and that is the way it is configured today. I do believe the policy was a > result of the consensus of the mailing list users. I can say that we do > everything we can to make most of the people happy most of the time. That > is the very reason we made this change to begin with -- to make it possible > for everyone to use the list. It would have been easier to just keep things > the same and let the people who can't reach port 8080 deal with it, but we > did what we had to to make it accessible for them as well. There are a lot > of variables in the system and we juggle them as best we can. > > Any feedback or suggestions are always welcome. > > > On Mon, Mar 2, 2015 at 5:18 AM, David Woodhouse > wrote: > >> On Mon, 2015-03-02 at 12:45 +0200, R.Smith wrote: >>> Ok, I've found the source of the list duplications. >>> >>> Some emails (Such as the one by J.K. Lowden 2-March-2015 re: Characters >>> corrupt after importing...) contains a "Reply-To" field in the header >>> with both list addresses which must have sneaked in there due to some >>> automatic list feature. (By "Both" I mean the old: >>> sqlite-users at sqlite.org and the new: >> sqlite-users at mailinglists.sqlite.org) >> >> You don't need that, do you? Just hitting Reply All to a message which >> is: >> To: sqlite-users at sqlite.org >> Reply-To: sqlite-users at mailinglists.sqlite.org >> >> would generate a message which ends up going to both, wouldn't it? >> >> (I can't easily test; I've configured my mailer to ignore abusive >> Reply-To: headers from mailing lists where it can detect them, so my >> Reply and Reply All buttons actually do what I *ask* them to.) >> >> But looking at the first message in the 'PhD Student' thread, it appears >> just as in my example above. And John KcKown's response of 26 Feb 2015 >> 07:16:47 -0600 is indeed to both addresses, as if he'd done the correct >> thing and simply hit 'Reply All'. >> >>> I usually use the "Reply to List" button (Thunderbird) which replies >>> correctly, >> >> Note that that is considered extremely anti-social in many cases, >> because it cuts some people out of discussions entirely. See >> http://david.woodhou.se/reply-to-list.html for a full discussion. >> >> -- >> dwmw2
[sqlite] List duplication
On 2015-02-28 3:15 AM, R.Smith wrote: > On 2015-02-28 05:02 AM, Darren Duncan wrote: >> I'm seeing a lot of message duplication too, but the ones I see are due to >> someone putting the list address twice as a recipient. >> >> For example a bunch of the "PhD student" messages say "To" >> sqlite-users at mailinglists.sqlite.org plus "Cc" sqlite-users at sqlite.org >> . >> >> But both of those are aliases for the same list. >> >> Whoever is doing this, sending your messages to both, please stop, just send >> to one, and then people would get one copy. > > I don't think it is someone specific, I've seen these from many people - I > think > it has to do with the way the reply-to field is interpreted and/or the way > mail > clients interpret it when you hit the "Reply" or "Reply-to-List" buttons. I'm > using an up-to-date Mozilla Thunderbird client which seems to avoid the issue > (unless I am not seeing my own duplicates). > > I find it hard to imagine that suddenly many list users changed the way they > respond to forum emails. The culprit is more likely a change in some > underlying > system set-up. My comment on "whoever" is meant to be plural. I think as a general principle that whenever people reply to list messages, they look at the headers of the message they are writing and make sure the list doesn't appear twice in the recipients field. If the result of hitting reply or reply-all to some message in a client puts the same recipient in the address bar twice, then the users should remove the extra copies, that's what I do, it can be simple as that. -- Darren Duncan
[sqlite] List duplication
I'm seeing a lot of message duplication too, but the ones I see are due to someone putting the list address twice as a recipient. For example a bunch of the "PhD student" messages say "To" sqlite-users at mailinglists.sqlite.org plus "Cc" sqlite-users at sqlite.org . But both of those are aliases for the same list. Whoever is doing this, sending your messages to both, please stop, just send to one, and then people would get one copy. -- Darren Duncan On 2015-02-27 12:09 PM, Peter Aronson wrote: > I've seen it too. All of the duplicate messages appear to have been sent to > both sqlite-dev at mailinglists.sqlite.org and sqlite-dev at sqlite.org or to > both sqlite-usersmailinglists.sqlite.org and sqlite-users at sqlite.org. > > Peter > > > On Fri, 2/27/15, R.Smith wrote: > > Subject: [sqlite] List duplication > To: sqlite-users at mailinglists.sqlite.org > Date: Friday, February 27, 2015, 12:38 PM > > Hi all, > > Just a quick question, I've been getting duplicated mails > via the forum. > It seems very random, roughly 1 in 4 mails I receive in > duplicate. Has > anyone else been getting duplicate mails? > > It's not a biggie, I just delete the duplicates. Also, it > might be my > email settings or setup being the culprit - if anyone has an > idea what I > might check, it will be most helpful - thanks.
Re: [sqlite] equivalent for JOIN LATERAL
I recall that http://blog.heapanalytics.com/postgresqls-powerful-new-join-type-lateral/ shows how Pg 9.3's LATERAL join is useful in practice, as it lets you do in declarational SQL what you may have needed procedural code for before, in which case it is an improvement. -- Darren Duncan On 2015-02-08 9:12 PM, James K. Lowden wrote: On Sun, 8 Feb 2015 23:52:43 +0100 Big Stone <stonebi...@gmail.com> wrote: I fall over this presentation of LATERAL, from postgresql guys. Does it exist in SQLITE ? Syntactically, no. Functionally, in part. If not, would it be possible too much effort ? I'm guessing the answer is No because the prerequisites are missing. Something like LATERAL (or APPLY in SQL Server) arises around table-valued functions, which really should be called parameterized views. You think you'd like to be able to say, SELECT S.* FROM T join F(T.t) as S on T.t < S.x where F is some function that produces a table for a scalar/row input. However, perfectly nothing new is really needed to express the idea: SELECT S.* FROM (select F(t) from T) as S WHERE EXISTS (select 1 from T where S.x > T.t) I suspect that new syntax like this is usually added to SQL for the wrong reasons. 1. Marketing. Now with LATERAL added! 2. User-imagined need, because don't know SQL 3. Punt on query optimization, invent keyword as hint In each case, they have added complexity without power. The "improved" system is harder to use and to develop. But, hey, it's progress. ?Perfection is achieved not when there is nothing left to add, but when there is nothing left to take away? ? Antoine de Saint-Exupery --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Autoincrement with rollback
On 2014-11-11 2:41 AM, Koen Van Exem wrote: I find it a bit confusing because when you create a PRIMARY KEY AUTOINCREMENT then a table named sqlite_sequence is created. According to the SQL (2003) standard multiple sessions are guaranteed to allocate distinct sequence values. (even when rollbacks are involved) See, this is the source of your confusion. I will explain. 1. Conceptually a sequence generator is just a database table with a single row and single column whose value is the integer. When the generator produces the next value, it is like these 2 statements being done as an atomic unit: "update seqgentbl set theint = theint + 1" and "select theint from seqgentbl". 2. The semantics that the SQL standard defines, and is commonplace with other SQL DBMSs, is that the aforementioned read+update of seqgentbl happens in its own autonomous database transaction that commits immediately, and serially prior to the main transaction that called upon the sequence generator. This is why in those cases a rollback of the main transaction doesn't rollback the sequence generator, because semantically that happened prior to the current transaction and successfully committed. 3. SQLite is different such that its read_update of seqgentbl happens within the current main transaction rather than a separate one, and therefore its actions rollback like anything else. So SQLite is purposefully being different than the SQL standard. Partly this is because supporting the standard means having to support multiple concurrent transactions trying to write the database, in contrast to what SQLite actually does which is only supporting one writing transaction at a time. If you want to use SQLite like the SQL standard, then invoke the sequence generator first in its own transaction and remember the value, then use that remembered value in your main transaction that you explicitly do afterwards. Do you understand what's going on now? -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check if a record exists
If all you want to know is whether a record matching a particular field value
exists, then what you did is appropriate. The EXISTS construct is generally for
filtering one table with another. That being said, you should be using bind
parameters rather than stitching id into the SQL string itself, as that practice
generally leads to huge security problems / SQL injection (although if your
language is strongly typed an int wouldn't do it, but a string would). -- Darren
Duncan
On 2014-11-04 1:47 PM, Drago, William @ CSG - NARDAEAST wrote:
All,
I've been pulling my hair out trying to figure how to use EXISTS. I've had no
luck with it (syntax errors) and I've resorted to this. Is there a
better/recommended way in SQLite to check if a record exists?
static bool IDisDuplicate(string dbFileName, int id)
{
int count;
string connectionString = String.Format("Data Source={0}",
dbFileName);
using (SQLiteConnection connection = new
SQLiteConnection(connectionString))
{
using (SQLiteCommand command = connection.CreateCommand())
{
connection.Open();
command.CommandText =
"SELECT count(1) DatasetID FROM UUT_Info where DatasetID = " +
id + ";";
count = Convert.ToInt32(command.ExecuteScalar());
}
}
if (count > 0)
{
return true;
}
else
{
return false;
}
}
Thanks,
--
Bill Drago
Senior Engineer
L3 Communications / Narda Microwave East<http://www.nardamicrowave.com/>
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com<mailto:william.dr...@l-3com.com>
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
attachments are solely for the use of the addressee and may contain information
that is privileged or confidential. Any disclosure, use or distribution of the
information contained herein is prohibited. In the event this e-mail contains
technical data within the definition of the International Traffic in Arms
Regulations or Export Administration Regulations, it is subject to the export
control laws of the U.S.Government. The recipient should check this e-mail and
any attachments for the presence of viruses as L-3 does not accept any
liability associated with the transmission of this e-mail. If you have received
this communication in error, please notify the sender by reply e-mail and
immediately delete this message and any attachments.
___
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] The IN (... ) clause
On 2014-09-13, 10:07 PM, jose isaias cabrera wrote:
I know that the IN clause contains a list of something. I.e.
IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')
So the question is, is there a shorter way for one to say something like,
IN ('2014-01-01', ..., '2014-01-05')
where the content of the IN would have the first item and the last item of the
list, but that's it? Thanks.
You're talking about a range/interval.
In SQL it is spelled like this:
BETWEEN '2014-01-01' AND '2014-01-05'
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transactions for read operations
As a general principle, database transactions should be held for as short a time as possible. You should start your transaction, then do all of the operations immediately that need to be mutually consistent, and then end the transaction appropriately; ideally a transaction is only open for a fraction of a second in typical cases. If you are wanting to do something that involves waiting for users, say, especially remote or web users, you should not be holding a transaction open while waiting for a user; doing so is generally a design problem with your application and you should change it so you use some other method for longer-term consistency. In a web context, web applications are supposed to be stateless, and you should not have a database transaction shared between multiple web client requests. The only common situation where its reasonable to have a transaction open for more than a split second is if that involves a single database-bound operation, such as a batch insert or a complicated report. Typical database activity does not involve this. On a tangent, if you know a database operation is only going to read, you should be using a read-only transaction; commit/rollback is only meaningful if you actually make a change. Barring that you did this, if you don't make a change, probably a rollback is the correct way to end it, as in theory that's just saying, I didn't intend to make any changes, and I want the db to ensure nothing actually changed by accident. -- Darren Duncan On 2014-09-06, 7:22 PM, Richard Warburton wrote: Hi, Brief: Should transactions be used for ensuring consistency between multiple queries? And if so, after I've finished is there a reason why I should not call commit? Background: I'm using SQLite for a web service. The database reference is passed to Page objects, which handle their specific url path. Not all pages will write data, but nearly all do multiple queries, which should be consistent with eachother. I was thinking that to simplify each page object's code, I would pass a transaction reference instead, and then call rollback if the page object returns an error, commit otherwise. However, given that the page will read many times more often than it writes, I'm wondering if this is a bad idea. Thoughts? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting by month with two dates input
On 2014-09-01, 6:50 PM, jose isaias cabrera wrote: Thanks for this, but how do I set a value to null? insert into foo (myfield) values (null); That's one way. If you're using some wrapper API, then the host language's analagy of an undefined value, eg undef in Perl, should do it. I thought null and '' were the same, but now I see it is not. Have you used Oracle before? Oracle treats NULL and '' as the same, but that's a problem with Oracle which gives its users no end of headaches. The SQL standard and basically every other SQL DBMS treats NULL as being distinct from every other value, which is how it is supposed to be. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting by month with two dates input
A common logical error that may be affecting you is, do your dates include a time portion or are they just year-month-day? If they include a time portion, then records from Dec 31 likely won't be counted as your 'between' arguments may be equivalent to '2013-12-31 00:00:00'. -- Darren Duncan On 2014-09-01, 6:41 PM, jose isaias cabrera wrote: "Darren Duncan" wrote... On 2014-08-31, 9:35 PM, Darren Duncan wrote: On 2014-08-31, 9:10 PM, jose isaias cabrera wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust; I'm surprised that even compiles, since AFAIK the SELECT clause is evaluated after the GROUP BY clause, and t doesn't exist yet at the GROUP BY stage (unless you have another one in LSOpenProjects). Maybe try using a subquery to force correct evaluation order? Kind of like this: SELECT cust, sum(ProjFund), sum(Xtra8), t FROM ( SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' ) x GROUP BY substr(t,1,7), cust; -- Darren Duncan Actually, isn't this more what you want? SELECT cust, sum(ProjFund), sum(Xtra8), t FROM ( SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' ) x GROUP BY t, cust; This one also does the same thing as mine. Hmmm... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting by month with two dates input
On 2014-08-31, 9:35 PM, Darren Duncan wrote: On 2014-08-31, 9:10 PM, jose isaias cabrera wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust; I'm surprised that even compiles, since AFAIK the SELECT clause is evaluated after the GROUP BY clause, and t doesn't exist yet at the GROUP BY stage (unless you have another one in LSOpenProjects). Maybe try using a subquery to force correct evaluation order? Kind of like this: SELECT cust, sum(ProjFund), sum(Xtra8), t FROM ( SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' ) x GROUP BY substr(t,1,7), cust; -- Darren Duncan Actually, isn't this more what you want? SELECT cust, sum(ProjFund), sum(Xtra8), t FROM ( SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' ) x GROUP BY t, cust; -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sorting by month with two dates input
On 2014-08-31, 9:10 PM, jose isaias cabrera wrote: SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust; I'm surprised that even compiles, since AFAIK the SELECT clause is evaluated after the GROUP BY clause, and t doesn't exist yet at the GROUP BY stage (unless you have another one in LSOpenProjects). Maybe try using a subquery to force correct evaluation order? Kind of like this: SELECT cust, sum(ProjFund), sum(Xtra8), t FROM ( SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate BETWEEN '2013-01-01' AND '2013-12-31' ) x GROUP BY substr(t,1,7), cust; -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: how best to determine # of rows in a table
Sorry, the count thing was actually Mark Halegua's question. -- Darren Duncan On 2014-08-27, 8:58 PM, Darren Duncan wrote: On 2014-08-27, 8:41 PM, Keith Medcalf wrote: this may seem like a small issue, but I'm not sure if the solutions I've found on the web will do what I want in a low memory situation. I'd like to iterate through a table one row at a time. I can do that in pysqlite, but I don't see a method for determining I'm at the end of the file Can anyone point me in the correct direction? Again, it's a low memory solution the the table could become quite large, so I don't want to load the whole thing with a teychall() call, and I'm not sure if a cursor won't take up too much memory as well. Good that you asked about this here Keith. Just use this SQL: select count(*) from table; Its unfortunate that so many people are out there making websites or whatever that don't know how to use SQL properly, and so they do things like "select * from table" and then try to filter it in their application. A main point of using a SQL database is using SQL to do the hard work of reporting for you; if you find yourself doing non-trivial work on the application side, you're probably missing out on a SQL feature. So good that you asked about this, and you can do things smart rather than hard. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: how best to determine # of rows in a table
On 2014-08-27, 8:41 PM, Keith Medcalf wrote: this may seem like a small issue, but I'm not sure if the solutions I've found on the web will do what I want in a low memory situation. I'd like to iterate through a table one row at a time. I can do that in pysqlite, but I don't see a method for determining I'm at the end of the file Can anyone point me in the correct direction? Again, it's a low memory solution the the table could become quite large, so I don't want to load the whole thing with a teychall() call, and I'm not sure if a cursor won't take up too much memory as well. Good that you asked about this here Keith. Just use this SQL: select count(*) from table; Its unfortunate that so many people are out there making websites or whatever that don't know how to use SQL properly, and so they do things like "select * from table" and then try to filter it in their application. A main point of using a SQL database is using SQL to do the hard work of reporting for you; if you find yourself doing non-trivial work on the application side, you're probably missing out on a SQL feature. So good that you asked about this, and you can do things smart rather than hard. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable number of parameters in a prepared statement's IN clause
On 2014-07-20, 5:07 PM, Donald Shepherd wrote: Is it possible to have a variable number of parameters in an IN clause in a prepared statement, i.e. "select * from Table where Col1 in (?,?,?,...);"? Or do I need a constant number of parameters in there to be able to re-use the prepared statement? If it were possible, the best way to do that design-wise would be to have a single parameter which was array-typed or relation-typed and then do a join on it, like this: select x.* from Table as x inner join ? as y using (Col1) Otherwise, the closest thing would be to stuff the parameters in a temporary table and then use that for the join in a subsequent select. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite-users list failed to block large attachment
Is something wrong with the configuration of this sqlite-users list? A message of subject "Porting SQLite to plain C RTOS" was allowed and distributed through it this morning with attachments. Not only attachments, but about 5MB of attachments. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] if possible point another table
On 2014-04-03, 7:19 PM, Andy Goth wrote: I don't believe this can be done in pure SQL since table names are not values. That doesn't mean it can't be done, though you will have to put some of the logic in your program itself. I expect that in the future this limitation will no longer exist. There's no reason that table names can't be values in principle. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] basic "Window function"
On 2014-03-13, 8:17 AM, big stone wrote: Hello again, Windowing functions are : - not easy to workaround with create_function(), - a truly "sql core" motor functionality. The only use case that I have, is the small subset I described earlier : - inside one 'select' : . several sum/min/avg/max (different fields) , . all with exactly the same "(over partition by ... a series of fields)", - no ranking, no order by inside this partition. ==> It allows to show a list of records at a certain level, with statistical analysis done at a completely different (higher or lower) level. Is it a feature SQLite team would like to do ? Is there anyone else, (besides little bee), that would "like" this request ? I know I would like to see this, a lot. Its one of those things that, if implemented in the core, should not be any more effort than it takes to implement aggregate functions with GROUP BY, and it would give users a great amount of power. I was very happy to see WITH get into the core, and windowing is similarly something you can get a lot of power from with relatively small core effort. If SQLite does this, I will be happy that there is yet another significant way in which SQLite is more powerful than MySQL (but not PostgreSQL), the WITH support being another, and subjecting data definition to transactions is another. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite destroys civilization.
On 3/2/2014, 9:34 AM, Richard Hipp wrote: Reports on twitter say that the "nanobots" in the TV drama "Revolution" have source code in the season two finale that looks like this: https://pbs.twimg.com/media/BhvIsgBCYAAQdvP.png:large Compare to the SQLite source code here: http://www.sqlite.org/src/artifact/69761e167?ln=1264-1281 Hahaha, that's great. Its always interesting to see when TV shows include programming code. Sometimes they actually make an effort to make it more realistic, such as in this case. I recall reading the source code shown in the original Tron is like that too. I have seen several others that are on the realistic side. But a counter-example is a show I saw where they had "programming code" but it was actually HTML source, which really shows those ones didn't do their homework. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why SQLITE_FLOAT instead of SQLITE_REAL?
On 3/1/2014, 12:16 AM, RSmith wrote:
On 2014/02/28 23:36, L. Wood wrote:
SQLite has the REAL data type:
https://www.sqlite.org/datatype3.html
Then why do we have SQLITE_FLOAT instead of SQLITE_REAL? All the other data
types (INTEGER, BLOB, TEXT, NULL) match with the SQLITE_ constants.
Quoting Shakespeare's Juliet:
"What's in a name? that which we call a rose by any other name would smell as
sweet..."
Of course in matters of love one can nod to that, but it can't be more wrong in
SQL or any code terms!
This may be a quirk, but in the defense, those type names are interchangeable
(or I should say Aliased) in most modern languages.
If you're going by semantics though, the meanings are quite different.
A real number represents a point on a line and can be either a rational or
irrational number. (And a complex number is a point on a plane.) An important
bit is that a real is a more abstract concept and doesn't imply a single right
representation.
In contrast, a float is much more specific, defining also a representation, and
as such a float can only be a rational number (x*y^z where all 3 are integers,
and y is typically 2) and not an irrational. (Or I suppose if you allow {x,y,z}
to be non-integers then a float is even more about a representation.)
Speaking in terms of programming language design, "real" is best suited for an
abstract type name, that is one that defines an interface for using a set of
types, same as "numeric". Whereas, "float" is best suited for the name of a
concrete type, like with "integer" and "ratio". (Well strictly speaking all of
these could be abstract types, but the latter set are more specific in meaning,
and in particular "ratio" and "float" imply a representation while the others don't.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How should I use parenthesis?
On 1/27/2014, 9:57 AM, Jean-Christophe Deschamps wrote: I'm trying to find the correct syntaxt for this, but I hit a syntax error each time: either SQLite shokes on outer parenthesis or on union all. Try something like this, which is a minimal change from yours: (select * from (select * from A where x in (subselectA)) dx left outer join (select * from B where y in (subselectB)) dy using (...) ) union all (select * from (select * from B where y in (subselectC)) dx left outer join (select * from A where x in (subselectD)) dy using (...) ) ... but replace the "using (...)" with a join condition saying which fields you want to be used for matching in the join, and also replace the "select *" with a specific list of fields you want to match up for the union. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Common Table Expression"
On 1/17/2014, 8:24 AM, Jan Nijtmans wrote: Not necessary. I noticed that CTE was just merged to SQLite's trunk, so it apparently will be part of SQLite 2.8.3. Ahh great, I look forward to seeing that released in February (regular schedule) or whenever. The greater maintainability of code due to the ability to refactor selects into named and reusable subcomponents, meaning bringing a benefit to SQL we take for granted with typical application languages, is coming to pass. That and the ability to have recursion, also taken for granted before. And no, VIEWs are not the same, those require creation of separate schema objects, while someone with read-only access to a db can use WITH, especially beneficial for ad-hoc reports. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive query?
On 1/11/2014, 7:33 AM, Petite Abeille wrote: On Jan 10, 2014, at 4:34 PM, Richard Hipp <d...@sqlite.org> wrote: FYI: The sponsor is now indicating that they want to go with WITH RECURSIVE. So the CONNECT BY branch has been closed and we are starting to work on a WITH RECURSIVE implementation. Much excellent. And much thanks to such rational sponsor :) So, considering that ‘with recursive’ is a superset of the plain vanilla ‘with’ clause, does it mean we can expect to see the regular ‘with’ clause in SQLite sometime in the near future as well? I would expect so; you can't have WITH RECURSIVE without WITH. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive query?
On 1/10/2014, 7:34 AM, Richard Hipp wrote: On Wed, Jan 8, 2014 at 2:35 PM, Richard Hipp <d...@sqlite.org> wrote: The sponsor might have a big pile of preexisting Oracle10g CONNECT BY code that they need to run. I feel sure that if they just want "recursive queries" for use in code that has not yet been written then we can convince them to go with SQL:1999 WITH RECURSIVE. But if the enhancement is needed to support legacy code, they might instead insist on CONNECT BY syntax. I still don't know what the situation is. Hopefully we'll here back soon FYI: The sponsor is now indicating that they want to go with WITH RECURSIVE. So the CONNECT BY branch has been closed and we are starting to work on a WITH RECURSIVE implementation. That's great news! This will be a serious step up in feature set for SQLite, both in ease of use and power, I look forward to it. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A read right after a write does not read changes
On 2013.12.10 6:59 PM, Paul Bainter wrote: I'm using Entity Framework to access my SQLite database and I wrote a routine today that edits an existing table record. Then in the same program (step 2), I use the key that I just used to modify the record and I create a new instance of the same table and read the record that I just modified, but the contents coming back from Entity Framework are the old values, like it was caching the old data somehow. I've checked the database with my database tool right after the initial modify statement and before the read, and everything gets changed, the subsequent read is just not getting the new values. Any help on this issue would be greatly appreciated. Generally speaking when one has a problem where a database wrapper such as Entity Framework is involved, and the underlying database is shown to work properly when used directly, one should be taking up with the makers or a users group of the wrapper for help. You should be asking in some support forum specific to Entity Framework about this problem. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested transactions
On 2013.11.23 7:20 AM, Simon Slavin wrote: Had the person who devised SQL thought it through, he'd have thought up savepoints instead of transactions and we wouldn't use transactions at all. This is an interesting proposal, and makes a lot of sense to me, especially given that savepoints today don't have the precondition of a "transaction" being active to use them, so on their own "savepoint" is like a generalization of a "transaction". -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested transactions
On 2013.11.23 1:31 AM, Clemens Ladisch wrote: Igor Korot wrote: If I understand correctly, I can do this (pseudo-code): BEGIN TRANSACTION; // some SQL statements BEGIN TRANSACTION; sqlite> begin; begin; Error: cannot start a transaction within a transaction This scenario will not end up with with unfinished transaction and I don't have to use SAVEPOINT/RELEASE in this case. You have to use SAVEPOINT/RELEASE. (Why don't you want to use it?) I agree. The concept of nested transactions is identical behaviorally to savepoints; they are just different syntax for the same thing, which is to let you undo a portion of the current transaction rather than the whole thing. But only the parentmost transaction is a real transaction, with the ACID properties, eg only the parentmost committing actually saves anything for good. Maybe what you're wanting is "autonomous transactions", which can be useful, but you can also implement that yourself just by having a second connection to the database from your application, which is behaviorally the same. Although with SQLite it may not be helpful if one connection would block for the other, but other DBMSs that don't lock the whole database they may be useful with. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger SQL and database schema
On 2013.10.15 10:34 PM, Petite Abeille wrote: On Oct 16, 2013, at 7:20 AM, Darren Duncan <dar...@darrenduncan.net> wrote: On 2013.10.14 11:58 PM, Sqlite Dog wrote: seems like SQLite is not checking trigger SQL for invalid column names until execution? What you describe sounds like the behavior of every SQL DBMS which has triggers whose trigger behavior I know. Hmmm… FWIW… Oracle, for one, will invalidate triggers, views, packages, etc if their underlying tables change. There is even a very handy ALL_DEPENDENCIES views to track all the explicit interdependencies between objects: http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1066.htm#i1576452 But the key thing here, and my point, is that even Oracle wouldn't block the underlying tables change due to the invalidation of other schema objects that would result. Oracle would allow the invalid trigger/view/package definitions to exist, rather than requiring the user to temporarily delete those first or update their definitions simultaneously with the underlying tables thereby enforcing compatibility. This is what I'm talking about, that invalid trigger/etc definitions are allowed to exist, by every SQL DBMS whose behavior I know about, and SQLite matching that behavior would best be maintained. Not checking trigger/etc validity until execution makes it possible to separately change the tables and other objects depending on them, or for that matter, altering underlying tables again to bring them back into compatibility with other objects' expectations of them, at which point the triggers/etc would become valid again without having ever changed. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger SQL and database schema
On 2013.10.14 11:58 PM, Sqlite Dog wrote: seems like SQLite is not checking trigger SQL for invalid column names until execution? What you describe sounds like the behavior of every SQL DBMS which has triggers whose trigger behavior I know. Seems better to me to retain this behavior than to reverse it, at least for default semantics. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using pragma user_version when doing updates
On 2013.09.17 4:28 PM, Richard Hipp wrote: On Tue, Sep 17, 2013 at 7:13 PM, Amit <amit.ut...@gmail.com> wrote: Hello, I would like to run the following update statement if pragma user_version == 2. How can I incorporate that in this query? UPDATE pass_key SET key = (SELECT textval FROM saved.pass_key b WHERE b.field='key') WHERE name="KeyLock"; I've tried using CASE..WHEN..ELSE but it doesn't seem to like the pragma statement in there. You can access the user version using "PRAGMA user_version". Unfortunately, you cannot combine a pragma with a larger query. Could you add information_schema analogies to SQLite so that information like what "pragma user_version" returns could then be accessed in arbitrary queries? This probably wouldn't be too much work and would be a very powerful addition. I'm not asking full information_schema support, but rather a representation of SQLite metadata as tablevars. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is SQLite a DBMS?
On 2013.09.02 6:06 AM, Simon Slavin wrote: The 'R' stands for 'relational' -- the sort of things SQLite implements with FOREIGN KEYS. A user model was assumed as part of Ted Codd's description of a 'proper' DBMS but he didn't require it, he only said that if you had a user model, the DBMS should let you manipulate it using data-base commands (i.e. users are entries in a table) rather than using a different system. In a general sense, the definition of a relational database is very simple, which is that the database presents all of its contents in terms of relation-typed variables, which SQL calls tables, and provides operators for working with them. People often think that the ability to do joins or having foreign keys is what makes a database relational, but that isn't true. Fundamentally "relationships" exist between all the individual attributes within a relation or columns within a table, where they are 1:1, eg that a business name is related to a business id, and such, and having multiple relations or tables lets you also have N:M relationships. Most of the stuff people associate with relational databases are strictly optional, though some of that is best to have in practice. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is SQLite a DBMS?
I don't think that being ACID and SQL compliant is the definition of a DBMS, far from it. While it is true that typically anything which is ACID and SQL compliant is a DBMS, lots of things can be a DBMS without being either ACID or SQL compliant. See dBASE for example. -- Darren Duncan On 2013.09.01 3:11 AM, Stefan Keller wrote: Hi, Wikipedia answers with yes and why (= because it's ACID and SQL compliant) within the first three sentences! http://en.wikipedia.org/wiki/SQLite Yours, S. 2013/9/1 kimtiago <kimti...@gmail.com> Hi, I need to know if SQLite is a DBMS and why. Please its urgent. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can column name come from a table?
On 2013.07.29 6:08 PM, Roman Fleysher wrote: Thank you, SQLiters, for confirmation. Yes, the set up is unusual. I have a table which essentially maps existing column names to the names the users want to see. columnRenameTable(table, column, showAS) The goal was to use this mapping for SELECT table.column AS showAS FROM table; when displaying the table. What kind of users are these? Shouldn't you be doing that in the presentation layer anyway? Besides the names you'd probably show fields formatted in different ways too, depending on their type or meaning. Also, SQLite does support views, you could define views having the names that the users want. In fact, that's what views are for, letting different users have different interfaces to the same database. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can column name come from a table?
On 2013.07.29 5:32 PM, Roman Fleysher wrote: Dear SQLiters, I think the answer to my question is "NO", but may be I missed something... Can column name come from a table, i.e. from another select? Example: SELECT (SELECT columnName FROM columnNameTable WHERE condition how to select limit 1) FROM table which has that columnName; Or this is not doable within SQL and I must execute internal select separately and have application compose second (external) select? Thank you, Roman I was going to say, try using a SQL prepared statement. For example, in Postgres you can write something like this: execute format( 'SELECT %I FROM table that has that columnName', (SELECT columnName FROM columnNameTable WHERE condition how to select limit 1) ); But I don't know if SQLite can do that in SQL; you might have to use the host language. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in Apress SQLite book. Was: Different result set of query in examples of SQLite book
On 2013.06.13 7:22 PM, Yongil Jang wrote: Thank you, Richard and James. 2013/6/14 James K. Lowden <jklow...@schemamania.org> Why not simply SELECT f.name, count(e.food_id) as 'episodes' FROM foods as f OUTER JOINfoods_episodes as e ON f.id = e.food_id GROUP BY f.name ORDER BY episodes DESC LIMIT 10; In my opinion, That example is used in beginning of SQL chapter, therefore, JOIN and GROUP BY is not explained yet. That seems kind of backwards. Joining is a simpler operation than subqueries, or at least is no more complicated. It seems strange to be talking about subqueries before you talk about joins or grouping. On the other hand, I suppose from an explanation point of view, a subquery in the SELECT list could actually be a simpler thing to explain to a SQL newbie, so maybe that's why it is first. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite and integer division 1/2=0
On 2013.05.12 11:42 AM, Simon Slavin wrote: I think your problem is just that you have columns declared as NUMERIC. You can have REAL behaviour if you want: just declare your columns as REAL instead: I agree with this. In principle, the behavior of addition should be tied to the data type or to the operator or both. If you want integer behavior, declare INTEGER types, if you want real behavior, declare REAL types; declaring NUMERIC types is saying you don't care about the behavior. That's the proper way to do this. (Or have 2 operators, say "/" and "div", where the former can produce a fractional result while the latter guarantees a whole number result.) The pragma is a bad idea. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL join with "or" condition gives unexpected results
On 2013.05.09 7:56 AM, Paolo Bolzoni wrote: Seriously? Care to explain? On Thu, May 9, 2013 at 4:48 PM, Petite Abeille <petite.abei...@gmail.com> wrote: On May 9, 2013, at 3:30 PM, Romulo Ceccon <romulocec...@gmail.com> wrote: But my application is (so far) database agnostic Reconsider. Agnosticism is not a feature. It's a bug. Its more accurate to say that agnosticism is about tradeoffs, which can be either mild or severe depending on context, and making tradeoffs could be considered as having bugs. Some DBMSs have features that others don't and sometimes the "best" solution for using a particular DBMS is to exploit features unique to it, even if you can't do that with other DBMSs. Working to the least common denominator exclusively in order to support less capable DBMSs means you don't exploit lots of features that will help you when using other DBMSs that support them. Working around the non-use of these features can make the applications less capable or more complicated or buggy as often the application's version of something is inferior to what the DBMS provides. I find that a hybrid approach is best, support multiple DBMSs, but don't be afraid to draw the line and say you don't support some, where their capabilities would drag things down too much. Especially in this world where many options are free, and as long as you at least support some of those, your potential users can use a different DBMS than otherwise easily to use your app. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Version 3.7.17 Preview
On 2013.04.26 8:34 AM, Richard Hipp wrote: Please visit http://www.sqlite.org/draft/releaselog/3_7_17.html to see the proposed enhancements and changes in SQLite version 3.7.17. Your comments, criticisms and suggestions are welcomed and encouraged. I think the extension mechanism is valuable, and a very appropriate way to bring in many kinds of extra functionality, such as regular expression support. And I'm glad an extension for that is provided now, which gives much more power to type constraint definitions. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite error near "16": syntax error
On 2013.02.19 5:15 AM, mikkelzuuu wrote:
1-2-2013 16:58|H2|NL-Radio 2|2013-01-03T00:00:00.000Z|172806528
An example of the output that I have to do. I see the 16 s on the first cell
and the time, but I wouldn't know why its giving me an error there. Would I
have to change the setup of the Cell in my SQLite Database?
Your problem is that you are stitching together a line of programming code in
the SQL language, and your data values are generally strings, but you are not
quoting those strings.
The minimal change you want to make is:
string StrQuery = @"INSERT INTO Test VALUES ("' +
dataGridView1.Rows[i].Cells["Column1"].Value + "', '" +
dataGridView1.Rows[i].Cells["Column2"].Value + "', '" +
dataGridView1.Rows[i].Cells["Column3"].Value + "', '" +
dataGridView1.Rows[i].Cells["Column4"].Value + "', '" +
dataGridView1.Rows[i].Cells["Column5"].Value + "');";
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Permalink to latest amalgamation
Kees Nuyt wrote: On Thu, 25 Oct 2012 00:12:16 +0200, Baruch Burstein <bmburst...@gmail.com> wrote: Is there a permanent link I can use that will always point to the latest amalgamation (or .zip containing it)? I would like to automate a make script that will use the latest sqlite. I know I can use a link to the latest release in the repo, but that means I would need to build the amalgamation as part of this make step. You make it sound very difficult to build the amalgamation source, but actually it's just "make sqlite3.c". So the easiest way really is to: * clone the fossil repo (once), * pull in updates periodically, * ./configure with the proper options/defines/omits for whatever your project needs, * make sqlite3.c, * make your project. I don't think that answer is appropriate for some common use cases, which may include the original requestor. Say for example and end user of the DBD::SQLite Perl module that wants to pull in the latest SQLite version to build it against, without having to specify a version. We shouldn't expect such a user to have a fossil client, they should just be able to pull the amalgamation tarball over the web. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac development question
Igor Korot wrote: Darren, On Tue, Oct 23, 2012 at 8:53 PM, Darren Duncan <dar...@darrenduncan.net> wrote: You should not have an application installer, at all. Instead, you can ask the question on where to store the database when the user opens your program. Or better yet, your application should have menu commands like "new database" and "open database", where if they choose the former then you ask them (using Apple's standard method) where that database goes. If they chose "open database", then you can let them open an existing one, or one should be able to double-click the database file in the Finder to open that one instead, which is what Mac users expect. As such, your application should support having multiple databases per user, even if they may typically just use one. If users open your program directly and not by double-clicking on a database file, you could automatically bring up a prompt to make a new one, as if they used the "new database" menu. -- Darren Duncan I doubt the user of the application will wait couple of minutes while the database will be created and the data will be populated for about 5000 records. They won't come from the internet and I'm planning to insert them as a bulk procedure from the Excel file I have. Thank you. Why would it take that long? Creating a database with 5000 records on a modern machine shouldn't take more than about 1 second or so. But even if it takes longer, my comment is about letting the user choose from the application itself where the data they edit goes, and your comments about the user waiting have nothing to say against that. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mac development question
Igor Korot wrote: Well, that the closest definition of the data I'm about to put into this database. I am making a sport related application and need to store the data about the teams, players etc. User should be able to add/edit/delete this information and this information will be presented in the GUI. Now the application is not creating the database - I am. The application/user is just uses the data in it. So on the first start there should be records in the db already. However, from you response I gather I will need an application installer which will ask the user "Where do you want to store the database with the data?" Am I correct? If yes, what I can use in order to create such distribution package? I presume it should be something like XCode installer, which asks couple of questions first and then copy everything. If no, then could you clarify, please. You should not have an application installer, at all. Instead, you can ask the question on where to store the database when the user opens your program. Or better yet, your application should have menu commands like "new database" and "open database", where if they choose the former then you ask them (using Apple's standard method) where that database goes. If they chose "open database", then you can let them open an existing one, or one should be able to double-click the database file in the Finder to open that one instead, which is what Mac users expect. As such, your application should support having multiple databases per user, even if they may typically just use one. If users open your program directly and not by double-clicking on a database file, you could automatically bring up a prompt to make a new one, as if they used the "new database" menu. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction inside transaction
Igor Korot wrote: Hi, ALL, Is it possible to have transaction inside transaction? Will it be handled correctly? What I mean is: crate transaction 1 by issuing "BEGIN", create transaction 2 by issuing "BEGIN". Close transaction 2 by issuing either "COMMIT" or "ROLLBACK". If "ROLLBACK" is issued, issue "ROLLBACK" on transaction 1. If "COMMIT" is issued, continue transaction 1. Thank you. Didn't you mean to say, "regardless of how (inner) transaction 2 ends, continue transaction 1"? Being able to rollback part of a main transaction without rolling back the whole thing is the whole point of nested transactions. (Likewise for "savepoints", which are different syntax but the same functionality.) If you want a rollback on the inner to rollback the outer unconditionally, you never needed a nested transaction in the first place, but rather just the one outer transaction 1, or you might have wanted an autonomous transaction, which is different, depending on what you want. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] new Windows versions?
TaxDetective wrote: Hi Darren are you on your own yet? Yes, I moved into my own place 3 months ago, in downtown Victoria. I'm currently at: unit 110, 915 Cook Street, Victoria, BC, V8V3Z4 My phone number is 778-265-1827. This is a land-line (Shaw digital, with unlimited NA long distance); I don't have a cellphone yet, though intend to get one within 6 months. I am still providing computer support to my mother though, who doesn't really have an alternative as savvy, most of it remote but sometimes I go over there to do maintenance. As for Windows I use what comes on the machine at the time and trade up every 3 years. No idea. Okay. Well, Windows 7 was released between July and October of 2009, so that means unless you got your machine almost 3 years ago you probably have that. Presumably, unless you explicitly request otherwise for your next machine, you'll have Windows 8 on your next trade-up, as it comes out later this month. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
