Re: [sqlite] sqlite-users Digest, Vol 147, Issue 13
Thank you, thank you! Balaji Ramanathan -- Forwarded message -- From: Richard Hipp To: General Discussion of SQLite Database < sqlite-users@mailinglists.sqlite.org> Cc: Bcc: Date: Thu, 12 Mar 2020 16:17:59 -0400 Subject: [sqlite] New SQLite Forum established - this mailing list is deprecated I have set up an on-line forum as a replacement for this mailing list: https://sqlite.org/forum https://www.sqlite.org/forum/forumpost/a6a27d79ac Please consider subscribing to the new Forum. The intent is that the forum will eventually replace this mailing list. The Forum is powered by Fossil. It has been in active use in the Fossil community for a couple of years, and has worked well. See the second link above for more information. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 146, Issue 27
On Thu, Feb 27, 2020 at 6:00 AM < sqlite-users-requ...@mailinglists.sqlite.org> wrote: > 18. Cannot export 'sqlite3' file to CSV (Rachael Courts) > From: Rachael Courts > To: "sqlite-users@mailinglists.sqlite.org" < > sqlite-users@mailinglists.sqlite.org> > Cc: > Bcc: > Date: Thu, 27 Feb 2020 01:05:50 + > Subject: [sqlite] Cannot export 'sqlite3' file to CSV > Hi All, > > I'm a couple of months into my PhD, studying bioacoustics. I am using > SQLiteStudio to open '.sqlite3' files, which I need to convert to CSV files > to import into MATLAB for further analyses. > > Currently I have attempted the data export function, selecting which > database and table I would like to export, however no option for 'CSV' > exportation is available. The only options that appear are 'JSON, SQL, > HTML, and PDF' which can't be read into excel. > > I have also attempted the queries tab in SQLiteStudio, in an attempt to > write code for exportation. This did not work. > > I also attempted opening the command window on my computer, to change the > '.sqlite3' file to 'CSV' but it seems that the '.sqlite3' file doesn't have > a '.db' attached, and therefore will not open in the command window for > editing. > > I'm seeking ANY advice on how to export these files into CSV, so they can > be read in excel, and easily imported into MATLAB. > > I've attached an example file, if this helps. > > I hope to hear from someone soon, > Rachael > I am not sure exactly what you are trying to do, but SQLiteStudio does provide you the ability to export as CSV. Are you trying to export just the table or the table + table indexes + table triggers? CSV export is supported under Data ->Export only for table export (not for table + indexes or table + triggers, etc.). If you are trying to export the whole database, it is best to think of a database with multiple tables as a spreadsheet with multiple tabs. You can export each tab as a CSV, but you can't export an entire multi-tab spreadsheet as one CSV. Same thing applies to databases. You can either export each table as a CSV, then import them into multiple tabs of a spreadsheet and use vlookups to relate the data into one bigger dataset with more columns - or better yet, write some SQL to "join" the tables and export the results of that query as a CSV for import into a spreadsheet. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Window functions in sqlite 3.26.0
Thank you, Dan. My eyes saw the underscore, but my brain did not process it! And many thanks to the SQLite development team for introducing this feature and making it so efficient. I had the need to compute running sums and averages in a query, and I have views which use regular queries and recursive queries. The recursive queries perform better, but still take about 10 minutes to process the data. I replaced them with queries using window functions, and the results came back in under a second. Amazing! For a related question: I am trying to calculate a percentile score using a query like below: select ID, (count(Quantity) over Win1) *100.0/count(ID) as Percentile, from myTable Window Win1 as (order by Quantityrange between unbounded preceding and current row) This gives me the error: misuse of aggregate: count(). So, I replaced it with the following: select ID, (count(Quantity) over Win1) *100.0/(count(ID) over Win2) as Percentile, from myTable Window Win1 as (order by Quantity range between unbounded preceding and current row), Win2 as (order by ID range between unbounded preceding and unbounded following) This works, but use of a window just to get the total count of ID's using a range "between unbounded preceding and unbounded following" just seems wrong! Is there a simpler construct I am missing? Thank you. Balaji Ramanathan > From: Dan Kennedy > To: sqlite-users@mailinglists.sqlite.org > Cc: > Bcc: > Date: Mon, 24 Dec 2018 14:40:58 +0700 > Subject: Re: [sqlite] Window functions in sqlite 3.26.0 > On 12/24/2018 01:43 AM, Balaji Ramanathan wrote: > > Hi, > > > > Are window functions enabled by default in the sqlite command line > > shell program that is available for download on the sqlite website? I > get > > the error message "no such function: rownumber()" when I try to use that > > window function. > > > They are in 3.26.0. Try "row_number", with an underscore. Or, if that's > not the problem, please post the failing SQL statement. > > Thanks, > Dan. > > > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Window functions in sqlite 3.26.0
Hi, Are window functions enabled by default in the sqlite command line shell program that is available for download on the sqlite website? I get the error message "no such function: rownumber()" when I try to use that window function. The documentation only mentions that window functions were added in version 3.25.0, and does not give any indication as to whether they are enabled or disabled in the command line tool. Is there perhaps a pragma setting I have to turn on for window functions to work? Thank you. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible bug in Alter Table
Thank you, Richard. I can understand how not naming the columns of a view can lead to ambiguities and other problems down the line. Hopefully the documentation will be updated so that users are aware that the alter table command can't really deal with cascading effects such as in the case of views referring to other views, etc. Is there a way to unravel a complex schema so that you can identify which views are based directly on tables and which views refer to other views, and what those other views are? At this point, the simple solution seems to be to just use .dump to dump out the contents of the database into a text file, use search and replace to do the column rename, and then use .read to read it back into a SQLite database. Anything I have to watch out for if I do the above? Thank you. Balaji Ramanathan From: Richard Hipp To: SQLite mailing list Cc: Bcc: Date: Mon, 26 Nov 2018 14:11:54 -0500 Subject: Re: [sqlite] Possible bug in Alter Table On 11/25/18, Balaji Ramanathan wrote: > I expected the Alter Table command to find and replace all occurrences of > that column name in my schema with the new name. Here is simplified SQL that illustrates the problem: CREATE TABLE t1(x); CREATE VIEW v1 AS SELECT x FROM t1; CREATE VIEW v2 AS SELECT x FROM v1; ALTER TABLE t1 RENAME x TO y; The ALTER TABLE fails because after changing the name of t1.x to t1.y, the definition of the view v2 is no longer valid. This is not something we intend to "fix" in SQLite. The root of the problem is that the column names for the v1 view are not specified. And since they are not specified, that means SQLite is free to pick whatever arbitrary names it wants for those columns. The definition of view v2 depends on one particular algorithm for picking the column names of view v1, but there are no guarantees that every version of SQLite will use that particular algorithm. Hence, the definition of view v2 is under-specified and prone to failure, such as in this case. If you are careful to defined the names of all columns within your views, then the problem goes away. For example: CREATE TABLE t1(x); CREATE VIEW v1(y) AS SELECT x FROM t1; CREATE VIEW v2(z) AS SELECT y FROM v1; ALTER TABLE t1 RENAME x TO y; Or: CREATE TABLE t1(x); CREATE VIEW v1 AS SELECT x AS y FROM t1; CREATE VIEW v2 AS SELECT y AS z FROM v1; ALTER TABLE t1 RENAME x TO y; If you do not specify the names of columns in views, then SQLite is free to choose whatever names it wants for those columns, and the choices might shift after an ALTER TABLE, which could then break queries and/or downstream views. So it is best not to do that. Admittedly, this is not well-documented. I will strive to improve the documentation for the next release. Perhaps I will also add a "warning" mechanism to alert programmers to gotchas like this in some subsequent release, though there probably is not time to get warnings in to the forthcoming 3.26.0 release. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Possible bug in Alter Table
Hi, I am writing to report a possible bug in the Alter Table command. It seems to fail on a somewhat complex schema with multiple tables and views, and views that reference other views. I was not able to come up with a simple one table, one view schema where this error actually shows up. That is why I am not able to post the schema here for you to view directly. So, please refer to the database test.db located on Google Drive at the following address: https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg In this database, I have a table Trip, that has a column "StartGMTOffset". I have multiple views referencing this column, but no indexes or triggers on this column. I am running SQLite version 3.25.3. I now issue the following command at the SQLite command prompt and I get the response below: SQLite> Alter Table Trip rename column StartGMTOffset to StartUTCOffset; Error: error in view CumulativeStatisticsByPlaceName after rename: no such column: StartGMTOffset Well, duh! There is no such column because I just renamed it. And I expected the Alter Table command to find and replace all occurrences of that column name in my schema with the new name. But maybe, it only works for a table and views that reference it directly, but not views that reference views that reference the table? I am not sure. But I thought I would report it anyway. I apologize if I misunderstood the documentation regarding the alter table command, or mangled the syntax in some way. Thank you. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 130, Issue 12
>8. Re: sqlite-users Digest, Vol 130, Issue 11 (Shawn Wagner) > >On Thu, 11 Oct 2018 09:51:15 -0500, Balaji Ramanathan < > balaji.ramanat...@gmail.com> wrote: > >> > >>2. Re: SQLite mailing list > >> > > > > The 1990's called and they want their mailing lists back. So, let us > > switch to 21st century technology already. > > And yet you read the digest! A very good way to be overwhelmed, one of > the good things about mailing lists that you can see everything > separately and only read the ones you are interested in. And you want > sub-forums, so we'll all have to go through each sub-forum looking for > things of interest. > > > Well, if there is one thing that is more annoying than one email, it is multiple emails. If it weren't for the digest option, I probably wouldn't be subscribed to the sqlite mailing list at all. Emails are one of the worst productivity killers ever invented, which is why every company wants to move from emails, and will try practically anything else to try to kill the beast. Believe me, email is the bathwater that came with the internet baby. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 130, Issue 11
> >2. Re: SQLite mailing list > > The 1990's called and they want their mailing lists back. So, let us switch to 21st century technology already. Count me in as an enthusiastic YES vote for proper forums (including subforums - so that I can read what I want and skip the rest instead of drowning in irrelevant emails with hundreds or thousands of lines of included, quoted emails). Or maybe we can have everybody fax everybody else? An entire mailing list with no technical discussion except whether to retain the mailing list or not. I can only shake my head in amazement . . . Balaji Ramanahan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] questions
> >2. questions (am...@juno.com) > From: "am...@juno.com" > To: sqlite-users@mailinglists.sqlite.org, am...@juno.com > Cc: > Bcc: > Date: Sat, 18 Aug 2018 16:24:45 GMT > Subject: [sqlite] questions > August 18, 2018 Good Morning Good People: I am in the process of building > a database for where I work. I can do a lot of tasks with SQLite. But I am > stuck on the dilemmas below. If any of you have any programming solutions > for any/all of these dilemmas I am in--in a way that I can download what yo > have so someone who has no programming experience and can just 'click and > use' (i.e., turnkey solutions, please advise. Once I hear back that you > have any/all solutions to my dilemmas below, I will give you an e-mail > address to send them to. Thank you vern much in advance for helping me > eliminate these dilemmas. 1) How Can I do a search--say for people trained > on one task, or on a given date, or for people who know how to operate one > specific piece of equipment? 2) How can I set up SQLite so some people can > only read SQLite? 3) How can I sent up a table/report of my results? 4) How > can I put in ascending or descending order all the information on one > field, say by date I gave out safety glasses? Respectfully yours, Alex > Stavis > > Hi Alex, As others here have suggested, SQLite is just a database engine with no user interface (except for the command line interface). You need to build your own interface by writing a program that includes SQLite as the data storage and retrieval engine. You can write such a program in most programming languages or in a scripting language like Tcl. I have also experimented with using ODBC to use MS Access as a front end to a SQLite database. But if you have Access, you may not even need SQLite unless you are dealing with large amounts of data that Access doesn't scale to properly. MS Access gives you the ability to use forms for data input, printable reports, etc. that you can fine-tune to your heart's content. It includes the programming language Visual Basic for Access (VBA) that you can use to do lots of things that SQL alone may not be able to do (or requires jumping through hoops to accomplish). An alternative to that is to use a generic user interface program. There are are several available out there, and the two most commonly used ones seem to be SQLiteStudio (www.sqlitestudio.pl) and SQLiteExpert ( www.sqliteexpert.com). They allow you to administer a sqlite database in a familiar windows environment, add and edit data in the tables, pull data out using queries and views, etc. You do have to be familiar with how to design a relational database and be able to use SQL to access the data in such a relational database. Personally, I use Tcl for one of my uses of SQLite. I am not an expert in Tcl by any stretch of the imagination, and it can be quite frustrating to get it to do exactly what you want. Tcl allows you to create forms and reports for interacting with the database. If you use a programming language or MS Access as a front end to the database, you can limit people to read-only access if necessary, by locking down the forms and exposing only the reports. For another of my uses, I use SQLite Studio. I find it very quick and robust and has all the capabilities I need for simply being able add, edit and retrieve data in an intuitive and straightforward user interface. However, it is a very generic tool that has limited customization potential, for instance, for creating forms, reports, etc. I experimented with SQLite Expert, but found its user interface less to my liking. Neither of these tools will limit some people to read-only access to the database. Hope I have given you some things to think about, and hopefully try out to get an idea of what the advantages and disadvantages of each approach are. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite query to get the offset of an entry in the list
SELECT COUNT(*) FROM TABLE WHERE NAME <= (SELECT NAME FROM TABLE WHERE ID = 3 ORDER BY NAME ASC) Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting a non-unique column name in a WHERE clause
OK, sorry for spamming the list. I figured it out a few minutes after I
sent that email. The trick is to say:
where country <> "country:1"
It took me a bit of sleuthing, but using .mode insert helped because I
could see that the resulting insert query had these postfixed column names
enclosed in double-quotes. I had tried single quotes (sqlite interpreted
those as strings to compare country against, so that did not work), but not
double-quotes in my various attempts.
But I have another question. In the example I posted, the column was only
repeated twice and the repeated column had a ":1" at the end, but in my
actual query, I have some columns repeated multiple times, and I find that
some of the repeated columns have postfixes like ":52449101",
":3016716403", ":197485499", ":3551085770", etc. Where do these postfixes
come from? If I know a column is going to be repeated n times in my view,
can I predict what the column names in the resulting output will be?
Thank you.
Balaji Ramanathan
On Thu, Mar 8, 2018 at 9:41 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:
> Hi,
>
> Interesting problem I have run into. I have a table called TRIP that
> has various columns including origin and destination. Both of these are
> foreign key columns, referring to placeid's in the table PLACE that
> contains things like placename, latitude, longitude, country, etc.
>
> So, the table structures are:
>
> CREATE TABLE Trip (
> TripID integer PRIMARY KEY ASC,
> ...
> Origin integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
> DELETE Cascade,
> Destination integer NOT NULL references Place(PlaceID) ON UPDATE Cascade
> ON DELETE Cascade,
> }
>
> CREATE TABLE Place (
> PlaceID integer PRIMARY KEY ASC,
> PlaceName text NOT NULL,
> Latitude float,
> Longitude float,
> Elevation float,
> Country text,
> )
>
> I have a view that pulls out all the columns of TRIP as well as
> details of the origin and destination using two joints with PLACE.
>
> CREATE VIEW TripDetailsRaw AS select *
> from Trip
> inner join Place P1 on Trip.Origin = P1.PlaceID
> inner join Place P2 on Trip.Destination = P2.PlaceID;
>
> When I look at the output of this view (I type in "select * from
> tripdetailsraw" at the sqlite command line, I see one set of columns from
> P1 and another set of columns from P2 with ":1" appended to the repeated
> column names ("PlaceName:1", "Country:1", etc.). But I have no idea how to
> add a WHERE clause to my select that references these columns.
>
> In the view itself, I can add a WHERE clause such as:
>
> WHERE p1.country <> p2.country
>
> There is no problem there and the view produces the filtered rows
> without any issues.
>
> But when I try to use the view in a query, those column names don't
> work. So, when I try to say "select * from TripDetailsRaw WHERE p1.country
> <> p2.country", sqlite complains that those columns don't exist.
> Similarly, I can't use "country:1", "country1", or "country_1", etc. in my
> WHERE clause to refer to the country corresponding to P2 either. In short,
> I don't know how to refer to whatever column names sqlite is using
> internally for these non-unique columns (even though, in the command line,
> they are revealed to me as "country" and "country:1").
>
> Anyways, long description of a problem that has probably been
> encountered and solved many times before. If someone could give me the
> answer or point me to the documentation that will allow me to figure the
> answer out by myself, I would appreciate it. Thank you.
>
> Balaji Ramanathan
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selecting a non-unique column name in a WHERE clause
Hi,
Interesting problem I have run into. I have a table called TRIP that
has various columns including origin and destination. Both of these are
foreign key columns, referring to placeid's in the table PLACE that
contains things like placename, latitude, longitude, country, etc.
So, the table structures are:
CREATE TABLE Trip (
TripID integer PRIMARY KEY ASC,
...
Origin integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
DELETE Cascade,
Destination integer NOT NULL references Place(PlaceID) ON UPDATE Cascade ON
DELETE Cascade,
}
CREATE TABLE Place (
PlaceID integer PRIMARY KEY ASC,
PlaceName text NOT NULL,
Latitude float,
Longitude float,
Elevation float,
Country text,
)
I have a view that pulls out all the columns of TRIP as well as details
of the origin and destination using two joints with PLACE.
CREATE VIEW TripDetailsRaw AS select *
from Trip
inner join Place P1 on Trip.Origin = P1.PlaceID
inner join Place P2 on Trip.Destination = P2.PlaceID;
When I look at the output of this view (I type in "select * from
tripdetailsraw" at the sqlite command line, I see one set of columns from
P1 and another set of columns from P2 with ":1" appended to the repeated
column names ("PlaceName:1", "Country:1", etc.). But I have no idea how to
add a WHERE clause to my select that references these columns.
In the view itself, I can add a WHERE clause such as:
WHERE p1.country <> p2.country
There is no problem there and the view produces the filtered rows
without any issues.
But when I try to use the view in a query, those column names don't
work. So, when I try to say "select * from TripDetailsRaw WHERE p1.country
<> p2.country", sqlite complains that those columns don't exist.
Similarly, I can't use "country:1", "country1", or "country_1", etc. in my
WHERE clause to refer to the country corresponding to P2 either. In short,
I don't know how to refer to whatever column names sqlite is using
internally for these non-unique columns (even though, in the command line,
they are revealed to me as "country" and "country:1").
Anyways, long description of a problem that has probably been
encountered and solved many times before. If someone could give me the
answer or point me to the documentation that will allow me to figure the
answer out by myself, I would appreciate it. Thank you.
Balaji Ramanathan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update the SQLite package in Tcl
Are there equivalent instructions for Windows? Thank you. Balaji Ramanathan -- Forwarded message -- From: Richard Hipp To: SQLite mailing list Cc: Bcc: Date: Sat, 18 Nov 2017 13:22:45 -0500 Subject: Re: [sqlite] Update the SQLite package in Tcl On 11/18/17, Balaji Ramanathan wrote: > Hi, > > I have installed Tcl/Tk from a couple of places on the web (activetcl > and magicsplat), and I find that neither of them has the latest version of > sqlite3 as the standard sqlite3 package. ActiveTcl seems to be linked to > sqlite 3.13 while magicsplat's version comes with sqlite 3.20. > > What do I need to do to get them both up to sqlite 3.21? On the SQLite download page, you will find both a Pre-release Snapshot and a "sqlite-autoconf" tarball. Download either of these. (I suggest the Pre-release Snapshot so that you can help us beta test!) Untar, and cd into the "tea" subdirectory. Then type: "./configure; make install". That is suppose to install the latest SQLite for TCL. "TEA" is the "Tcl Extension Architecture". -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion about DISTINCT keyword
This is great information. Thank you very much for a clear explanation, Keith. I guess I have to go back to using CTE's to get what I want in this case. Balaji Ramanathan -- Forwarded message -- From: Keith Medcalf To: SQLite mailing list Cc: Bcc: Date: Sat, 18 Nov 2017 09:16:57 -0700 Subject: Re: [sqlite] Confusion about DISTINCT keyword Neither. It has nothing to do with the DISTINCT keyword, which causes only DISTINCT rows to be returned (duplicates are removed). You misunderstanding is on the nature of a SCALAR. A Scalar means ONE value. A correlated SCALAR subquery (a correlated subquery embedded as a column in a select statement) can only return a SINGLE SCALAR result. It matters not whether your subquery returns 1 or 1,000,000 rows. Only the value from the first row is returned. Once this first row has been determined the subquery is terminated. (That is, it always has " LIMIT 1" no matter what you might specify). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update the SQLite package in Tcl
Hi,
I have installed Tcl/Tk from a couple of places on the web (activetcl
and magicsplat), and I find that neither of them has the latest version of
sqlite3 as the standard sqlite3 package. ActiveTcl seems to be linked to
sqlite 3.13 while magicsplat's version comes with sqlite 3.20.
What do I need to do to get them both up to sqlite 3.21? I see that
their installations include a lib folder with a sqlite dll in it. Is it
sufficient simply to replace that dll with the sqlite 3.21 dll? I tried
that but it gave me error messages ("invalid argument, couldn't load dll",
etc.), so I am guessing there is more to it than that.
Better yet, is there a version of tcl/tk that is considered "official"
that is kept updated with the latest versions of all these packages so that
I don't have to wonder what comes packaged with which version of tcl I
download from where?
Thank you.
Balaji Ramanathan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confusion about DISTINCT keyword
I have the query below with one column in the final output coming from the main query and two columns in the final output coming from correlated subqueries: SELECT DISTINCT *modenumber*, (SELECT tripid FROM trip WHERE modenumber = T.modenumber ORDER BY distance DESC LIMIT 3 ) AS *tripid*, (SELECT distance FROM trip WHERE modenumber = T.modenumber ORDER BY distance DESC LIMIT 3 ) AS *distance* FROM trip T ORDER BY modenumber; I expected this query to produce at least 3x the number of distinct modenumbers because the results of the subqueries (especially the tripid) are different for each trip. However, when I run it, I get only one row per modenumber. It is as if the DISTINCT keyword is applying only to the modenumber, and not to the other two columns in the select. Is that because the other two columns are coming from subqueries? The documentation does not explicitly mention subqueries, and only says distinct removes duplicate rows. In this case, it seems to think that the row consists of just the column from the main query and does not include the columns from the subqueries. Is this a bug or is this the expected behavior of the DISTINCT keyword? Thank you. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
Thank you, Peter. Perhaps it would be a good idea to update the SQLite website and point links to tcl.tk rather than tcl-lang.org. Balaji Ramanathan -- Forwarded message -- From: Peter Da Silva To: SQLite mailing list Cc: Bcc: Date: Wed, 15 Nov 2017 13:11:56 + Subject: Re: [sqlite] Best way to develop a GUI front-end I contacted the Tcl core team and this is the response from Steve Landers: > tcl-lang.org was a temporary measure a few years ago when the .tk DNS went missing. It wasn’t advertised but I guess it is now. > I’ve fixed it The official site is still at tcl.tk. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
Thank you very much for all your suggestions. For now, I am going to start with a windows forms application in vb.net or forms in OpenOffice. Tcl/Tk is a steeper learning curve, and if someone can point me to some good resources that will walk a beginner through the development of a windows GUI on that platform, I would appreciate it. Thank you again. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to develop a GUI front-end
Thank you everyone for your suggestions. Looked at Xojo. Not sure if the free version will stick around. And there is no way to save and reuse reports from one session to another in the free version. Creating and recreating the report every time is going to become tedious very soon. Continuing to use Access as a front end and connecting to my db using odbc is definitely an option. I wanted to move away fully from Access, so I am going to keep that as a backup option. I am leaning towards tcl/tk given the endorsement from both Peter and DRH. So, I downloaded tcl/tk version 8.6.7 from magicsplat ( http://www.magicsplat.com/tcl-installer/index.html), and then ran tclsh.exe. In the resulting command window, I typed "sqlite3 db1 https://sqlite.org/tclsqlite.html . And all I got was "invalid command name sqlite3". So, I am stuck. What did I do wrong? BTW, the www.tcl-lang.org website has been down for at least the past 3 days it looks like. Not sure how to interpret the reliability of a technology whose home website is down for days at a time. Balaji Ramanathan On Tue, Nov 14, 2017 at 6:00 AM, < sqlite-users-requ...@mailinglists.sqlite.org> wrote: > > -- Forwarded message -- > From: Peter Da Silva > To: SQLite mailing list > Cc: > Bcc: > Date: Mon, 13 Nov 2017 22:03:34 + > Subject: Re: [sqlite] Best way to develop a GUI front-end > Since sqlite originated as a Tcl extension it’s got excellent Tcl > bindings, and Tcl has an excellent platform-independent GUI in Tk, so it > seems to me that would be the best and simplest way to create a GUI front > end for sqlite. > > > > -- Forwarded message -- > From: Richard Hipp > To: SQLite mailing list > Cc: > Bcc: > Date: Mon, 13 Nov 2017 17:06:20 -0500 > Subject: Re: [sqlite] Best way to develop a GUI front-end > On 11/13/17, Peter Da Silva wrote: > > Since sqlite originated as a Tcl extension it’s got excellent Tcl > bindings, > > and Tcl has an excellent platform-independent GUI in Tk, so it seems to > me > > that would be the best and simplest way to create a GUI front end for > > sqlite. > > +1 > > -- > D. Richard Hipp > d...@sqlite.org > > > > -- Forwarded message -- > From: Tim Streater > To: SQLite mailing list > Cc: > Bcc: > Date: Mon, 13 Nov 2017 22:22:29 + > Subject: Re: [sqlite] Best way to develop a GUI front-end > On 13 Nov 2017, at 21:54, Balaji Ramanathan > wrote: > > > Is there a third party free tool like MS Access that would allow me > to > > connect to a SQLite db in the back-end and enable me to create a custom > > front-end to it with forms and reports? All my searches for this kind of > > tool only lead me to tools like SqliteStudio, which is a GUI front end > for > > SQLite, but not a programmable one like I want. If anyone can point me > > towards a programmable GUI front-end development tool (preferrably > > open-source, but just free and well-maintained is sufficient), I would > > appreciate it greatly. > > There is Xojo (www.xojo.com) which has SQLite built in and has reports > (which I have never used). It's cross-platform and you create your own GUI > front end with windows, buttons etc. It's free to use for development, but > if you want to compile and build a stand-alone application (.exe or .app) > then you buy a licence. There is a Lite licence to build for one platform > which is not a high cost. > > > > -- > Cheers -- Tim > > > -- Forwarded message -- > From: Bart Smissaert > To: SQLite mailing list > Cc: > Bcc: > Date: Mon, 13 Nov 2017 23:52:18 + > Subject: Re: [sqlite] Best way to develop a GUI front-end > As you are familiar with VBA I can see two other options: > > 1. Use Christian Werner's ODBC driver from Access (or Excel): > http://www.ch-werner.de/sqliteodbc/ > > 2. Use Olaf Schmidt's COM dll with Access (or Excel): > http://www.vbrichclient.com/#/en/About/ > > RBS > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 119, Issue 14
Very interesting. Thank you David. I have never used a recursive CTE before, so I am still trying to understand exactly how it works. But it does work, so I will definitely take a closer look. Thank you. Balaji Ramanathan On Tue, Nov 14, 2017 at 6:00 AM, < sqlite-users-requ...@mailinglists.sqlite.org> wrote: > -- Forwarded message -- > From: David Raymond > To: SQLite mailing list > Cc: > Bcc: > Date: Mon, 13 Nov 2017 17:20:58 + > Subject: Re: [sqlite] Running sums and averages > As other folks have mentioned, doing it in an external language is going > to be easiest. > > That being said, CTE's are almost a full language in themselves. > > I'm just gonna go with 1 value here for the example, but how about > something like... > > with recursive foo (RowNumber, F1, sumF1, OverallAvgF1, avgF1, F1notNull) > as( > select > rowNumber, > F1, > ifnull(F1, 0.0), > ifnull(F1, 0.0), > ifnull(F1, 0.0), > F1 is not null > from mytable where RowNumber = 1 > > union all > > select > mytable.RowNumber, > mytable.F1, > foo.sumF1 + ifnull(mytable.F1, 0.0), > (foo.sumF1 + ifnull(mytable.F1, 0.0)) / mytable.RowNumber, > (foo.sumF1 + ifnull(mytable.F1, 0.0)) / (foo.F1notNull + (mytable.F1 is > not null)), > foo.F1notNull + (mytable.F1 is not null) > > from > > foo inner join mytable on mytable.RowNumber = foo.RowNumber + 1) > > select RowNumber, F1, sumF1, OverallAvgF1, avgF1 from foo; > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Best way to develop a GUI front-end
Hi, I have been using SQLite without any programming language so far. I maintain the data in SQLite and use SqliteStudio (www.sqlitestudio.pl) for inputting data into it and running queries to look at outputs. However SqliteStudio does not have a programmable back-end that allows me to take the outputs of a query and do things with it (such as calculating running sums and averages, for instance). So, if things become too expensive for standard SQL, I just do without. In the past, I have maintained my data in an Access database and developed a pretty extensive set of forms and reports to input and extract data. I moved to SQLite because I wanted to move off a proprietary platform onto a free one. And SQLite is faster and includes more SQL functionality like CTE's also. But I miss the nice front-end with forms to take inputs (especially parent and child forms to input data related to foreign keys) and the reports to format my outputs the way I wanted them. I could also do running sums and averages, percentile scores and other things that require looking at a data dump of an entire columns of contents to do efficiently. I am familiar with programming in C and Java, but none of my programming has been for the windows platform (except for the programming inside MS Access, which was done in VBA). I have never developed GUI front ends with forms, buttons, etc. Is there a third party free tool like MS Access that would allow me to connect to a SQLite db in the back-end and enable me to create a custom front-end to it with forms and reports? All my searches for this kind of tool only lead me to tools like SqliteStudio, which is a GUI front end for SQLite, but not a programmable one like I want. If anyone can point me towards a programmable GUI front-end development tool (preferrably open-source, but just free and well-maintained is sufficient), I would appreciate it greatly. Thank you very much. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Running sums and averages
OK, thanks for the short and sweet answer! I just wanted to make sure I wasn't missing something. Balaji Ramanathan > -- Forwarded message -- > From: Simon Slavin > To: SQLite mailing list > Cc: > Bcc: > Date: Sun, 12 Nov 2017 16:37:24 + > Subject: Re: [sqlite] Running sums and averages > > > On 12 Nov 2017, at 4:05pm, Balaji Ramanathan > wrote: > > > Is there anything I can do to reduce the time taken? > > Do it in your favourite programming language rather than SQL. > Congratulations on finding a way of doing it in SQL, but running sums and > averages are trivial in a programming language and will be far faster and > you can be as fussy as you like about whether nulls are counted and such > things. > > Simon. > > > -- Forwarded message -- > From: "Joseph R. Justice" > > I am *FAR* from a SQLite (or SQL) guru, but it seems obvious to me that > your calculations for sumF1, avgF1, OverallAvgF1, etc as written are > running in quadratic time based on the number of rows in table T. Worse > yet, you're recalculating all these values entirely from scratch every time > you're recalculating them, rather than use the previously calculated value. > > Is there any way you can calculate table T with just RowNumber, F1, F2, > ..., F10, and then as you subsequently step through T row by row calculate > all the sums and averages as you step, saving the calculations for each row > as you calculate them in temporary variables for use in calculating the > values for the next row? This might be doable in pure SQL code, or you > might have to do it in whatever language you're making calls to SQLite > from. That should be linear time, at worst 2 times the number of rows in T > (once to construct T, once to step through it). > > Be well. > > > > Joseph > > > > -- Forwarded message -- > From: Dennis Clarke > > < Simon correctly advised > > > Do it in your favourite programming language rather than SQL. > > Let me be even more clear : > > Memory is cheap and most servers have plenty. > > Processors are fast and most servers have multiple with many cores. > > Select the entire table of columns you need into memory. > Write a little code. > > No it won't scale very well into millions of rows but I could easily run > a test and I will bet many mnay many dollars that processing the sums in > memory is orders of magnitude faster than SQL. > > > Dennis > > ps: if your db is MySQL or Oracle db then the problem is trivial with > the C API > > > > -- Forwarded message -- > From: Keith Medcalf > Subject: Re: [sqlite] Running sums and averages > > EXPLAIN QUERY PLAN > > is the first step. > > -- Forwarded message -- > From: Graham Holden > > You shouldn't even need to read the entire table (or view) into > memory: just read row-by-row, and for each field, keep a running > total and the count of non-NULL values. From these you can calculate > your total and both types of average. > > Graham > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Running sums and averages
Hi, I have a table (mytable) with several numerical fields. Let us call them F1, F2, etc., etc. The table also has a row number field (RowNumber) that I make sure has incremental values from 1 through the number of rows in the table. In addition to printing out the values of F1 through Fn, I also want to print out, for each row of the table, the running sums and averages of these fields. There are two averages: one that uses only the count of non-null values of each field as the denominator and another that uses the count of both null and non-null values of each field. So, I wrote a query as below to calculate and print those values: select RowNumber, F1, (select sum(F1) from mytable where mytable.RowNumber <= T.RowNumber) as sumF1, (select avg(F1) from mytable where mytable.RowNumber <= T.RowNumber) as avgF1, --average excluding null values (select sum(F1) from mytable where mytable.RowNumber <= T.RowNumber)/T.RowNumber as OverallAvgF1, --average including null values F2, (select sum(F2) from mytable where mytable.RowNumber <= T.RowNumber) as sumF2, (select avg(F2) from mytable where mytable.RowNumber <= T.RowNumber) as avgF2, (select sum(F2) from mytable where mytable.RowNumber <= T.RowNumber)/T.RowNumber as OverallAvgF2, --Other fields, their running sums and averages go here from mytable as T order by RowNumber I have about 3000 rows in my table and about 10 fields I am doing this for. When I select F1 through F10 alone, the query runs in negligible time. But when I run the above query with running sums and averages included (total of 40 columns instead of 10 columns), the time increases to well over 5 minutes. When I run the query at the SQLite command line, the first 1000 or so records are produced very fast, but as the row number increases, I can see SQLite struggling to produce outputs. By the time I get to the end of the query, the records are being produced at about a rate of only 1 or 2 per second. Is there anything I can do to reduce the time taken? Is this the most efficient way to query for running sums and averages? Any other ideas to get what I need any quicker? The added wrinkle in all this is that mytable is actually not a table, but a view. F1 through F10 are computed from various tables and brought together in this view. So, I don't think I can create indexes on any of these fields. I have thought about creating a temp table out of this view so that I can index RowNumber (I don't know whether it will help) but it goes against my minimalist instincts. Thank you. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 119, Issue 12
Thank you very much, Igor. I knew there would be a simple explanation, but I was focused on operand affinities instead of order of operations. So, I have to be careful about combining mathematical expressions with the concatenation operator. I am so used to thinking of exponentiation, multiplication and division having higher precedence than anything else in an expression (PEMDAS) that I got blind-sided by the fact that concatenation has even higher precedence than those. I will use parentheses more liberally in the future to specify exactly what I want. Thank you again. Balaji Ramanathan From: Igor Tandetnik > To: sqlite-users@mailinglists.sqlite.org > Cc: > Bcc: > Date: Sat, 11 Nov 2017 21:17:24 -0500 > Subject: Re: [sqlite] Can someone explain these outputs for me? > On 11/11/2017 8:55 PM, Balaji Ramanathan wrote: > >> 3.When there is a mathematical expression after the string, I get a 0. >> My string is nowhere to be seen in the output >> SQLite> select '- '||cast(-1.5 as integer)*-1 >> 0 >> > > || has the highest precedence. Your expression is interpreted as ( '- > '||cast(-1.5 as integer) ) * -1 . The string produced by the stuff in > parentheses doesn't look like a valid number, and so becomes 0 when coerced > to the same. Basically, you are doing > > select 'foobar' * -1 > > 4.But when I add 1 instead of multiplying, it produces output that >> seems to evaluate everything before the addition to zero >> SQLite> select '- '||cast(-1.5 as integer)+1 >> 1 >> > > 0 * -1 == 0 > 0 + 1 == 1 > > 5.Enclosing the mathematical expression in a printf produces the >> correct output >> SQLite> select '- '|| printf(cast(-1.5 as integer)*-1) >> - 1 >> > > So would enclosing in parentheses. The point is not printf() call, but > changing the order of evaluation. > > 6.If the output starts with a number, then it doesn't seem to matter >> what follows. Notice that the last part of the expression below is the >> same as the expression in query number 3 above, but it works fine now >> whereas previously it produced a zero as the output >> SQLite> select cast(1.5 as integer)||'-'||(cast(-1.5 as integer)*-1) >> 1-1 >> > > The last part is parenthesized here, whereas it wasn't in prior examples. > That makes all the difference. > > I am sure it has something to do with order of operations and the affinity >> of the operands, but can someone give me a summary that I can understand >> readily? The only mentions of the "||" operator on the SQLite website ( >> https://sqlite.org/lang_expr.html) don't really explain what is going on >> in >> the above examples. >> > > The part of the article you quote that you seem to overlook is "in order > from highest to lowest precedence" > -- > Igor Tandetnik > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can someone explain these outputs for me?
I am not sure what exactly is going on here. When an expression starts with a string and I try to append the results of a mathematical operation to it, sometimes it works, sometimes, it doesn't, and I can't find the pattern behind what works and what doesn't. 1.Simple mathematical expression by itself works SQLite> select cast(-1.5 as integer)*-1 1 2.Adding a string in front of a simple cast by itself works SQLite> select '- '||cast(-1.5 as integer) - -1 3.When there is a mathematical expression after the string, I get a 0. My string is nowhere to be seen in the output SQLite> select '- '||cast(-1.5 as integer)*-1 0 4.But when I add 1 instead of multiplying, it produces output that seems to evaluate everything before the addition to zero SQLite> select '- '||cast(-1.5 as integer)+1 1 5.Enclosing the mathematical expression in a printf produces the correct output SQLite> select '- '|| printf(cast(-1.5 as integer)*-1) - 1 6.If the output starts with a number, then it doesn't seem to matter what follows. Notice that the last part of the expression below is the same as the expression in query number 3 above, but it works fine now whereas previously it produced a zero as the output SQLite> select cast(1.5 as integer)||'-'||(cast(-1.5 as integer)*-1) 1-1 I am sure it has something to do with order of operations and the affinity of the operands, but can someone give me a summary that I can understand readily? The only mentions of the "||" operator on the SQLite website ( https://sqlite.org/lang_expr.html) don't really explain what is going on in the above examples. Any help would be much appreciated. Thank you. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Something broke between version 3.15 and 3.19.3
I am glad you were able to fix it quickly. I assume the next version of SQLite (3.19.4 or 3.20 or whatever) will include the fix? Thank you. Balaji Ramanathan On 6/11/17, Balaji Ramanathan wrote: > > Everything was working fine under 3.15. I just use the commandline > tool (sqlite.exe) to interact with my database. I am not a programmer and > don't have a need for programmatic access to this database. I recently > updated to 3.19.3, and now when I select from that summary view, I get no > results - the query just runs for minutes on end, and I eventually lose > patience and kill the process with a ctrl-c. SQLite is still working. It is just picking an inefficient query plan. The fix is here: https://www.sqlite.org/src/timeline?c=87aceb417a813a29 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Something broke between version 3.15 and 3.19.3
Thank you very much, Simon. I was able to downgrade to 3.18 using your instructions and everything seems to work now. Balaji Ramanathan On 11 Jun 2017, at 2:56pm, Balaji Ramanathan wrote: > This is going to sound stupid, but I would like to know how to > download older versions of sqlite. Although the download page gives links only for the current version of SQLite, several older versions are still on the server and you can download them by making up the URL yourself. So, for instance, one of the links currently on the Download page is <https://www.sqlite.org/2017/sqlite-dll-win32-x86-3190300.zip> If you want the version before 3.18.0 you can look it up in <https://www.sqlite.org/changes.html> and find that it is 3.17.0 which was released in 2017. Then you can make up the appropriate URL: <https://www.sqlite.org/2017/sqlite-dll-win32-x86-317.zip> and it works ! Hope this helps. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Something broke between version 3.15 and 3.19.3
Thank you very much, Dr. Hipp. I am glad I was not imagining a problem that nobody else could reproduce. If you need any additional details or if you can think of any other way I can help with the investigation, please let me know. Balaji Ramanathan PS: This is going to sound stupid, but I would like to know how to download older versions of sqlite. The sqlite.org website seems to have links only to downloads for the latest version. I can read the changelog and other details for older versions, but I can't seem to find a link to download the older version of the program itself. For instance, the latest release before the bug was introduced seems to be version 3.18.0, released on 3/30. I can read the changelog and bugfixes at https://www.sqlite.org/releaselog/3_18_0.html, but no link on that page seems to lead to a download page. I wanted to downgrade to an older version while I wait for this to be fixed, but I can't seem to figure out how to do that. Thank you in advance for any pointers. > Hi, > > I maintain a personal database on sqlite. It is quite small, with > about 30 tables, and an equal number of views. One of these views is an > inner join of the contents of about 15 of these views, producing a summary > view of my data. The views combined in this summary view contain about > 3200 rows each, and the summary view usually runs in about 2 seconds or > less. > > Everything was working fine under 3.15. I just use the commandline > tool (sqlite.exe) to interact with my database. I am not a programmer and > don't have a need for programmatic access to this database. I recently > updated to 3.19.3, and now when I select from that summary view, I get no > results - the query just runs for minutes on end, and I eventually lose > patience and kill the process with a ctrl-c. Thanks for the test case! Bisecting shows that the problem is the optimization introduced here: https://www.sqlite.org/src/timeline?c=9e35c89dbe744312 I still do not understand the details. But we'll be working on it. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Something broke between version 3.15 and 3.19.3
Hi, I maintain a personal database on sqlite. It is quite small, with about 30 tables, and an equal number of views. One of these views is an inner join of the contents of about 15 of these views, producing a summary view of my data. The views combined in this summary view contain about 3200 rows each, and the summary view usually runs in about 2 seconds or less. Everything was working fine under 3.15. I just use the commandline tool (sqlite.exe) to interact with my database. I am not a programmer and don't have a need for programmatic access to this database. I recently updated to 3.19.3, and now when I select from that summary view, I get no results - the query just runs for minutes on end, and I eventually lose patience and kill the process with a ctrl-c. I have produced an anonymized version of my database and loaded it to https://drive.google.com/open?id=0B5B_T2PA2u7ddTdlc1JST0xyVjg for anybody to access. If you load this database (named test.db) into the sqlite command line shell of version 3.15 and run the command "select * from TripDetails;", you will see that results appear in under 2 seconds. If you load the exact same database into the command line shell of version 3.19.3 and run the exact same select statement, it never produces results (or it takes so long that I have never had the patience to hang around and see if it does produce results). I have verified that even under version 3.19.3, all the individual views that contribute to the summary view produce results by themselves. In fact pretty much everything except this summary view seems to work. So, it looks like the massive inner join between these views is the cause of the delay or failure in the latest version of sqlite. I am not sure how or why, but I would appreciate it if others on this list who are more knowledgeable about these things can take a look and let me know what they think. I am sure my db design leaves a lot to be desired in terms of normalization, optimization, etc. I am open to suggestions on those aspects, but my primary concern is that something that worked fine under a previous version of sqlite does not work anymore. Whatever the flaws in what I have done, I do expect things to not break simply when I upgrade to the latest version of sqlite from a previous version. I would be open to modifying my database in such a way that it is more efficient and faster, and perhaps that enables me to produce the results I want from this query in the latest version of sqlite. But to me that is secondary. I don't want to be tweaking my database on an ongoing basis to make it perform well with each new release of sqlite. The symptoms point to some kind of regression in sqlite between 3.15 and 3.19.3, and I would like to see if there is a fix that does not involve modifying my database. Thank you very much. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Messages posted on Nabble not getting to list
Nope, not true. You can make standalone applications using Access that don't require Access to be installed on the target machine. Not sure how the licensing works, but it is definitely possible. It just requires the Access run-time which you can get from MS according to this website: https://support.office.com/en-us/article/Deploy-an-Access-2007-application-7bb4f2ba-30ee-458c-a673-102dc34bf14f and https://www.microsoft.com/en-us/download/details.aspx?id=50040 . Thanks for the pointers to Lazarus and MSVC. Have to look into them. The first programming language I learned in college and one I still have a soft spot for after all these years is Pascal. As for using SQLite with Access, once I got to that website and saw the references to Windows XP, I beat a hasty retreat! I am not that desperate, thank you very much!! Balaji Ramanathan >On 2016/05/30 7:09 PM, Balaji Ramanathan wrote: >> The strength of Access is not in having a good built-in front end, but in >>giving the user the ability to build a good custom front-end. Forms and >>reports with a visual basic based programming language (VBA) behind it to...// >Ah ok, but those are not real applications - they require Access to be >on the target machines. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Messages posted on Nabble not getting to list
Thank you for those suggestions, Ryan. I have used SQLiteExpert as well as SQLiteStudio. I actually like the latter. It has some interesting features such as cell-by-cell rollback and commit, the ability to add new rows to the bottom of a table regardless of where your cursor is at currently, etc. But it seems to be a single-person development effort, so improvements are slow in coming. SQlite Expert has its own advantages including built-in viewers for images so that images stored as blobs in a SQLite db can be browsed instead of having to export them as files before looking at them. I have never tried SQLiteSpeed, and from its description, it seems to be very similar to the other two. The strength of Access is not in having a good built-in front end, but in giving the user the ability to build a good custom front-end. Forms and reports with a visual basic based programming language (VBA) behind it to enable event-based computations, etc. The ability to arrange inputs and outputs using boxes that can be moved around on a page so that you get exactly the look and feel you are looking for instead of the straight (but endless) scrolling between rows and columns. The flexibility to make the forms and reports single-record or continuous. A multitude of charting options to visualize data rather than just looking at numbers. And the parent-child nesting of forms and subforms in Access is an elegant solution to the problem of updating the main table while simultaneously adding new entries to a related table to keep foreign keys in the main table in sync. Nothing like that exists in any of the front ends I have looked at for SQLite. And unfortunately, Access can't read SQLite files directly either. I don't blame SQLite for not having something like that. In fact, I know no other db product that comes with all this built-in. There are bolt-on products like crystal reports that will work with other db products to provide this kind of reporting capability, but that just increases the complexity of what all you have to pay for and keep up to date, and you can completely forget about platform independence. The only other way to handle what I used to do in Access is for me to learn PHP and use a web browser as the front end. That is now on my to-do list. In the meantime, sqlite studio and sqlite expert are passable. On the android side, there is a front end similar to sqlite expert called aSQLiteManager ( https://play.google.com/store/apps/details?id=dk.andsen.asqlitemanager). But this also seems to be more of a 1-person development effort, so I am not sure how feature-rich it will eventually become. Balaji Ramanathan >I agree on the user-oriented front-end that MSSQL has, both in the SMS and the Access application, but you'll find there are quite a few >that makes life with SQLite a pleasure too - some of them very user oriented. May I suggest, if you use Windows as a vehicle, you try >either: >SLite Expert from: http://www.sqliteexpert.com/ >or SQLitespeed from: http://www.sqlc.rifin.co.za/ > >If between those you don't get most of the features you need (or you need it on other platforms or perhaps connectors), there are many >others that a simple Google will find. >Best of luck, >Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Messages posted on Nabble not getting to list
It was too much database for my purposes as far as this project was concerned. Same with PostgreSQL. Also, I wanted something that would be easily accessible from my android phone/tablet as well as my PC. I have been using MS Access all along, and I really like the nice front end and reporting capabilities that come with that. But it costs money to keep Access around, and it is usable only on my PC. I decided I could live without the prettiness so that I would have something that is more flexible and will remain accessible even if I don't have a Windows machine in the future. Balaji Ramanathan > Any reasons for the thumbs down on MySQL? Their workbench is better that > Toad ... > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Messages posted on Nabble not getting to list
Ryan Smith wrore: >A valid point indeed - for most of us this is simply achieved by e-mail f>iltering or perhaps using a different mail account. >As an aside - If your company makes software that uses SQLite in any >way, you should probably receive the SQLite forum mails somewhere into a >folder in your company mails as reference material. > >This forum is one of the pillars of SQLite's usability. Well, truth be told, this mailing list gets so little traffic precisely because it is a mailing list. Most forums I am a member of have 1000's of messages a day compared to the few dozen this mailing list gets in a typical day. I have seen more SQLite questions answered on a single day in forums like stack overflow than are answered in this mailing list in a month or more. But then again, if this mailing list actually had a few hundred or thousand posts to it daily, I wouldn't tolerate the flood of emails into my inbox. As it stands, it works fine precisely because it is so low-volume. And of course, the quality of contributors is probably much higher because only the hard-core, dedicated users of SQLite sign up and contribute on this mailing list. But when I was debating between MySQL and SQLite for my project, I almost didn't choose SQLite because of the archaic look and feel of the sqlite.org website and support options available there. Flash for the sake of flash is not good, but sometimes you have to show people that you and your product are keeping up with the times, not already obsolete before you even download it and start using it. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Messages posted on Nabble not getting to list
Interesting. I have no idea what a facebook-style interface looks like since I don't have a facebook account. The main advantage of forums, and I follow a bunch of them, is that I choose when I want to stop my regular day job and be distracted by them rather than emails coming in and distracting me all the time. And a forum can be set up to email you every time someone posts something if you want to follow along in real-time. But if the main contributors prefer a mailing list then I can get used to it too. Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I need to merge tables from two databases
Your case statement syntax is completely off. Also, the concatenation operator in sqlite is ||, not +. Try this: insert into Customer(id) select case when b.id = id then b.id||'A' else b.id end from customerb b Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Messages posted on Nabble not getting to list
Thank you, Simon and Igor. I have put in a support request at Nabble, let us see what happens. Why does SQLite use a mailing list instead of a proper web-hosted forum/bulletin board type setup? That way it would be possible to set up sub-forums for different interests (SQL, Adiministratioin, Bug Reports, etc., etc.) and readers don't have to wade through all the stuff they are not interested in just to participate in the stuff they are interested in. I understand that mailing lists were the norm in the 1990's, but times have changed. Balaji Ramanathan On Thu, May 26, 2016 at 4:53 PM, Balaji Ramanathan < balaji.ramanat...@gmail.com> wrote: > Hi, > > I keep getting bounce messages from the sqlite mailing list when I try > to post replies from nabble (http://sqlite.1065341.n5.nabble.com/). The > message shows up on nabble, but never seems to get to the mailing list and > is not included in the next day's digest of messages sent to the list. It > looks like Nabble is sending the messages to sqlite-us...@sqlite.org > rather than sqlite-users@mailinglists.sqlite.org. Does somebody need to > change settings in nabble to fix this or do I have to do something > different to get this to work correctly? > > > My latest bounce message is as below: > > This is the mail system at host mbob.nabble.com. > > I'm sorry to have to inform you that your message could not > be delivered to one or more recipients. It's attached below. > > For further assistance, please send mail to postmaster. > > If you do so, please include this problem report. You can > delete your own text from the attached returned message. > >The mail system > > : host mail.sqlite.org[67.18.92.124] said: 554 > 5.7.1 > : Recipient address rejected: Access denied > (in > reply to RCPT TO command) > > Final-Recipient: rfc822; sqlite-us...@sqlite.org > Original-Recipient: rfc822;sqlite-us...@sqlite.org > Action: failed > Status: 5.7.1 > Remote-MTA: dns; mail.sqlite.org > Diagnostic-Code: smtp; 554 5.7.1 : Recipient > address > rejected: Access denied > > > -- Forwarded message -- > From: Balaji Ramanathan > To: sqlite-us...@sqlite.org > Cc: > Date: Thu, 26 May 2016 13:18:14 -0700 (MST) > Subject: Re: I need to merge tables from two databases > Try this: > > insert into Customer(id) > select case when b.id = id then b.id||'A' else b.id end from customerb b > > Balaji Ramanathan > > > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Messages posted on Nabble not getting to list
Hi, I keep getting bounce messages from the sqlite mailing list when I try to post replies from nabble (http://sqlite.1065341.n5.nabble.com/). The message shows up on nabble, but never seems to get to the mailing list and is not included in the next day's digest of messages sent to the list. It looks like Nabble is sending the messages to sqlite-us...@sqlite.org rather than sqlite-users@mailinglists.sqlite.org. Does somebody need to change settings in nabble to fix this or do I have to do something different to get this to work correctly? My latest bounce message is as below: This is the mail system at host mbob.nabble.com. I'm sorry to have to inform you that your message could not be delivered to one or more recipients. It's attached below. For further assistance, please send mail to postmaster. If you do so, please include this problem report. You can delete your own text from the attached returned message. The mail system : host mail.sqlite.org[67.18.92.124] said: 554 5.7.1 : Recipient address rejected: Access denied (in reply to RCPT TO command) Final-Recipient: rfc822; sqlite-us...@sqlite.org Original-Recipient: rfc822;sqlite-us...@sqlite.org Action: failed Status: 5.7.1 Remote-MTA: dns; mail.sqlite.org Diagnostic-Code: smtp; 554 5.7.1 : Recipient address rejected: Access denied -- Forwarded message ------ From: Balaji Ramanathan To: sqlite-us...@sqlite.org Cc: Date: Thu, 26 May 2016 13:18:14 -0700 (MST) Subject: Re: I need to merge tables from two databases Try this: insert into Customer(id) select case when b.id = id then b.id||'A' else b.id end from customerb b Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] View workarounds
Thanks again, Ryan. The options right now come down to either expanding the view with all the raw columns so that I can filter and sort directly using a select * from view. Or I can use the view for unfiltered, unsorted look at my data, and use the query of the view to do filtering and sorting. Decisions, decisions . . . I vote for more extensive support of hidden columns in tables, views, etc. Is there some site for submitting enhancement requests for SQLite? Balaji Ramanathan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] View workarounds
3600.0 +(ScheduledStartGMTOffset - StartGMTOffset) as
DepartureDelayRaw,
case when (strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
+(ScheduledEndGMTOffset - EndGMTOffset)*3600) >= 0 then
(cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime)
+(ScheduledEndGMTOffset - EndGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',EndDateTime) -
strftime('%s',ScheduledEndDateTime) +(ScheduledEndGMTOffset -
EndGMTOffset)*3600 as integer)%3600/60), -2,2) else
'-'||(cast(strftime('%s',ScheduledEndDateTime) - strftime('%s',EndDateTime)
+(EndGMTOffset - ScheduledEndGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',ScheduledEndDateTime) -
strftime('%s',EndDateTime) +(EndGMTOffset - ScheduledEndGMTOffset)*3600 as
integer)%3600/60), -2,2) end
as ArrivalDelay,
cast(strftime('%s',EndDateTime) - strftime('%s',ScheduledEndDateTime) as
float)/3600.0 +(ScheduledEndGMTOffset - EndGMTOffset) as ArrivalDelayRaw
from Trip
As you can see, I calculate times in HH:MM format for display and I also
calculate the raw number of hours as a floating point number. I want to be
able to filter my original view based on the value of things like
TripTimeRaw rather than TripTime (which is a string in HH:MM format). The
problem is that I don't want to expand my display grid any further (it is
already way too wide) by including TripTimeRaw as one of the selected
columns of my main view. But I do want to be able to filter that view
based on this value.
Now, I can copy the text of the view definition and paste it in as a plain
old query, add any filter conditions I want based on the "raw" columns in
my sub-views, and it works perfectly fine. I can also add ORDER BY clauses
to this query based on these "raw" columns, and that also works fine. I
just wish there was an easier way to do this using just the view rather
than copying and pasting massive amounts of text between windows.
Balaji Ramanathan
[sqlite] View workarounds
Thank you very much for all your comments. I thought about including all the columns in my view and then selecting just what I need, but that is almost as painful as repeating the view's query in adding the filters I want. Modifying both the select clause and the WHERE clause of the query is twice the work. You see, I use the view to quickly review the contents of multiple tables and whether they make sense, and adding these extraneous, unformatted columns just makes the work harder, that is why I did not just throw every column into the view. I am familiar with CTE's, but I am not sure how they would help in this situation. I guess I could throw everything into my view and use that as a CTE in a select, but all I have is that one complicated view, so creating a CTE out of it seems like wasted effort. I am intrigued by Dominique's suggestion of virtual tables and their hidden column feature. Enabling hidden columns in views would be the best of both worlds - allow me to display exactly what I want while allowing me to filter and sort on other columns. Why don't normal tables and views have hidden columns? That would be an excellent enhancement to SQLite, I think. In the meantime, copying the view's definition as a query and adding the filtering and sorting clauses to the query gets me there. It is a little bit of work, but keeps my formatting so that I can scan each row quickly and verify the data (which is the primary aim of my view). Balaji Ramanathan
[sqlite] View workarounds
Hi, I have created some views in my database by joining multiple tables to pull out specific columns from these tables without having to remember the exact SQL and joins (easy repeatability). But it looks like I have misunderstood how views work and have run into some limitations when using these views. I was wondering if any of you have any workarounds for these limitations. 1. I can't filter the view on any column that is not explicitly part of the SELECT clause of the view. These are columns that are part of the tables included in the view, but they are not in the SELECT statement, so I am not able say: SELECT * from myView where [column that is not part of the select] = 'myValue'. I am able to copy the SQL of the view and add that WHERE condition to its end, and it filters perfectly fine, but I can't use the view directly, I have to use the SQL of the view 2. Similar, probably related: I can't order the view by any column that is not part of the SELECT clause of the view. Again, this is a column in a table included in the view, but the view itself does not include it in the SELECT, and so I can't sort by it. Is there something similar to a view in SQLite that I should be using instead to get around these? I don't want to keep using the query because it is long and complicated and I am afraid I will introduce errors into it when I try to modify it to add sorting and filtering. And I don't want to include these columns in my view because my view already includes some calculations based on these columns (for example, a cost field is output as a string with a leading $ sign, so I don't want to include the raw numerical column in the select, but I want to be able to filter and sort by that raw numerical value). I have a lot of experience with SQL, and have worked with MS Access extensively, so I am used to saving queries in the database and using them as needed. MS Access does not have views, and saved queries are MS Access' alternative to views. But they behave more like queries than SQLite views: they give me access to all the columns in the tables involved, not just those in the SELECT clause. Maybe I am just spoilt! Thank you in advance for your thoughts on this. Balaji Ramanathan
