[sqlite] View workarounds
On 2016/05/23 7:52 PM, Balaji Ramanathan wrote: > 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). You know you could add many views right? You could have one view with all columns, and another with only those columns that interest you for different purposes. You could even make views that use other views that already exist as a source. The way you describe "almost as painful as..." etc. sounds to me like there is something small missing, perhaps something you have yet to grok, because nobody usually finds the thing you describe painful, so I'm sure there is some little thing which, when realised, will make all this extremely much easier for you. I just wish I knew what it was. Perhaps you could be specific with your schemata and show us what you have, and what you want to see (and how) and which filtering / ordering you typically would want to do - we could then give you a quick script of how to achieve that and hopefully in there somewhere a light will go on. Even maybe post the database file somewhere, if it isn't full of nuclear launch codes or such. Btw: Access is a user-friendly data tool more than an actual database. You are now using a full-fledged RDBMS which is way more powerful and conversely, very much less user-friendly (or as I like to put it: More accurate and less short-cutty), so it would probably seem a bit more painful at first. It's a lot like going from driving a nice car with parking-assist to piloting a jet fighter - it's not as easy of course, but once you get the hang of it, it's spectacular.
[sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
On 23 May 2016, at 9:08pm, Piyush Shah wrote: > We know about sqldiff https://www.sqlite.org/sqldiff.html and would use it > to generate diffs and patch them but we were hoping there was a way to do do > a binary diff of the database because otherwise we will have to figure out a > way for the client to run the sqldiff utility to patch the database. There's no easy way to integrate Sparkle and sqldiff so that they work together like that. The amount of work it would take would be equivalent to writing your own tool which updated the database across the internet. If the database file is small, just let Sparkle update it with the other components of the application. If the database file is big, it may be worth doing work on it. Does the database file compress well as a ZIP file ? Then distribute it as a .zip. On startup, detect if the decompressed version is not present, or has a datestamp before the .zip version. Do you have roughly equal parts of the file taken up with tables which never change and tables which change with each update ? Then split the file up into two database files and use ATTACH to access them both via one connection. Simon.
[sqlite] Binary Difference in Sqlite database files instead of SQLdiff and patch ?
Hi, We are using the Sparkle Project https://sparkle-project.org/ to manage a Mac OS X application updates that has an embedded sqlite database. We were hoping that the Sparkle project would be able to detect differences in the SQLite database file and include that in the Delta it generates with bsdiff. We are observing that it doesn?t see the binary difference in SQLite and sends the entire database with the update. We know about sqldiff https://www.sqlite.org/sqldiff.html and would use it to generate diffs and patch them but we were hoping there was a way to do do a binary diff of the database because otherwise we will have to figure out a way for the client to run the sqldiff utility to patch the database. Appreciate any suggestions.
[sqlite] View workarounds
Thank you for continuing with this thread, Ryan. I don't have nuclear
launch codes in my database, but it is over 4MB in size. But the data in
it is not that important. Let me post the view I am interested in:
select Trip.TripID as 'Trip Number',
Mode.Mode as 'Mode',
TripOD.TripOD as 'Origin',
TripDescription.TripDescription as 'Description',
Trip.Distance as 'Distance (KM)',
TripTimings.TripDates as 'Trip Dates',
TripTimings.TripTimings as 'Trip Timings',
TripTimings.TripScheduledDates as 'Scheduled Dates',
TripTimings.TripScheduledTimings as 'Scheduled Timings',
TripTimes.DepartureDelay as 'Departure Delay',
TripTimes.ArrivalDelay as 'Arrival Delay',
TripTimes.TripTime as 'Trip Time (HH:MM)',
TripCost.TotalCostUSD as 'Cost (USD)',
TripCost.OutOfPocketCostUSD as 'Out of Pocket Cost (USD)',
TripCalculatedValues.Speed as 'Speed in KMPH',
TripCalculatedValues.CentsPerKM as 'Cost (c/KM)',
TripCalculatedValues.OutOfPocketCentsPerKM as 'Out of Pocket Cost (c/KM)',
TripCalculatedValues.DollarPerHour as 'Cost ($/Hour)',
TripCalculatedValues.OutOfPocketDollarPerHour as 'Out of Pocket Cost
($/Hour)',
AllTripNotes.AllTripNotes as 'Trip Notes',
P1.PlaceAlternates as 'Origin Alternates',
P2.PlaceAlternates as 'Destination Alternates',
P3.PlaceDetails as 'Origin Details',
P4.PlaceDetails as 'Destination Details',
P5.AllPlaceNotes as 'Origin Notes',
P6.AllPlaceNotes as 'Destination Notes',
AllTripGroups.AllTripGroups as 'Trip Groups',
AllTripGroupTripNotes.AllTripGroupTripNotes as 'Trip Group Notes',
AllVehicleNotes.AllVehicleNotes as 'Vehicle Notes',
AllModeNotes.AllModeNotes as 'Mode Notes'
from Trip
inner join Mode on Trip.ModeNumber = Mode.ModeID
inner join TripCost on Trip.TripID = TripCost.TripID
inner join TripDescription on Trip.TripID = TripDescription.TripID
inner join TripOD on Trip.TripID = TripOD.TripID
inner join TripTimes on Trip.TripID = TripTimes.TripID
inner join TripTimings on Trip.TripID = TripTimings.TripID
inner join TripCalculatedValues on Trip.TripID = TripCalculatedValues.TripID
inner join AllTripNotes on Trip.TripID = AllTripNotes.TripID
inner join AllTripGroups on Trip.TripID = AllTripGroups.TripID
inner join AllTripGroupTripNotes on Trip.TripID =
AllTripGroupTripNotes.TripID
inner join AllVehicleNotes on Trip.VehicleNumber = AllVehicleNotes.VehicleID
inner join AllModeNotes on Trip.ModeNumber = AllModeNotes.ModeID
inner join PlaceAlternateNames P1 on Trip.Origin = P1.PlaceID
inner join PlaceAlternateNames P2 on Trip.Destination = P2.PlaceID
inner join PlaceDetails P3 on Trip.Origin = P3.PlaceID
inner join PlaceDetails P4 on Trip.Destination = P4.PlaceID
inner join AllPlaceNotes P5 on Trip.Origin = P5.PlaceID
inner join AllPlaceNotes P6 on Trip.Destination = P6.PlaceID
As you can see it is a join of very many tables and views. And I have
given the columns nice names to make it easier to read and understand what
they are rather than guessing from cryptic camelCase names.
Now, this is the text of the TripTimes view that is one of the sources of
the view above:
select TripID,
(cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime)
+(StartGMTOffset - EndGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',EndDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset - EndGMTOffset)*3600 as
integer)%3600/60), -2,2)
as TripTime,
cast(strftime('%s',EndDateTime) - strftime('%s',StartDateTime) as
float)/3600.0 +(StartGMTOffset - EndGMTOffset) as TripTimeRaw,
case when (strftime('%s',StartDateTime) -
strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
StartGMTOffset)*3600) >= 0 then
(cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
+(ScheduledStartGMTOffset - StartGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',StartDateTime) -
strftime('%s',ScheduledStartDateTime) +(ScheduledStartGMTOffset -
StartGMTOffset)*3600 as integer)%3600/60), -2,2) else
'-'||(cast(strftime('%s',ScheduledStartDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset -
ScheduledStartGMTOffset)*3600 as integer)/3600)
|| ':' ||
substr('00'|| (cast(strftime('%s',ScheduledStartDateTime) -
strftime('%s',StartDateTime) +(StartGMTOffset -
ScheduledStartGMTOffset)*3600 as integer)%3600/60), -2,2) end
as DepartureDelay,
cast(strftime('%s',StartDateTime) - strftime('%s',ScheduledStartDateTime)
as float)/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) -
[sqlite] View workarounds
On Mon, May 23, 2016 at 4:49 PM, Steve Schow wrote: > My suggestion is add the extra columns you need to the view, then when you > make a query against that view, only specify the more limited set of output > columns you want in the final output > SQLite almost supports what's needed, but only for VIRTUAL tables. [1] The HIDDEN trick doesn't work in tables (parses fine, but ignored), and doesn't work in views (doesn't parse, at least in 3.10.2 when I tried). If that trick was extended to views, as in create view v (col1, col2, col3 HIDDEN) as select ... then Balaji could have `select * from v` only return col1 and col2, yet still be able to filter/sort on col3. i.e. the cake and ... --DD PS: I actually thought HIDDEN was supported for tables, but sadly it isn't apparently. PPS: Oracle12c uses INVISIBLE for such columns. See [2]. [1] https://www.sqlite.org/vtab.html#hiddencol [2] http://www.oracle.com/technetwork/articles/database/invisible-columns-odb12c-2331522.html
[sqlite] View workarounds
On 2016/05/23 3:02 PM, Balaji Ramanathan wrote: > 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. Firstly, you are not spoilt, you are deprived! MSSQL supports VIEWs very much like most other RDBMS systems. A views is essentially a table but without persistent data, it gets its data from a query. This means that on the front-end, it behaves very much like any other table and you cannot query, filter or sort by columns that are not part of the table. This is true for all RDBMS systems, SQLite, MSSQL and the like. What SQLite doesn't have is stored queries, nor does it have stored procedures, but it does support Triggers and Common table expressions. To achieve what you would like to achieve, the answer is probably TEMP tables formed by your complicated queries, but which contain a lot more columns than you mean to display. You can then select and display only the needed columns after filtering the TEMP table. You can achieve this more "live" with using Common Table Expressions, where you can setup a base complicated query with all needed columns from the base tables into the CTE, and then re-use that CTE everywhere with only the final select from it showing whatever you really need. WITH CTE1(c1, c2, c3 ... cn) AS ( SELECT x,y,z... [very complicatedquery here] ), CTE2 (d1, d2, d3 dn) AS ( SELECT x,y,z [Another very complicated query here] ) SELECT c1, d2, [very simple query here] FROM CTE1, CTE2 WHERE c3 > 10 ORDER BY d3 etc. But, if you are new to CTE's (MSSQL supports them too), then perhaps a bit of reading is needed first - we could suggest sources if needed. There is also nothing wrong with making a view that contain all of the above c1, c2, through d1, d2... dn and then simply selecting from it the c1, d2 you want to see and ordering by the other d3, c3 etc. columns. The advantage views have is that you never need to even see the complicated bits again. Why you would insist to NOT put any column into a view is beyond me, you only need to select the ones you want, unless of course you are really spoilt and want to just do SELECT * FROM myView WHERE stuff_that_isnt_in_my_view = true, but I'm sure that isn't the case. ;) HTH and good luck! Ryan
[sqlite] View workarounds
As long as you don't try to modify data, a view just behaves like a base table. So, like in base tables, you can't extract, filter, sort, group by, etc. based on non-existing columns. SQLite views are read-only, but modifying data through a view can be done with "instead of" triggers. J-L Hainaut >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 >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] constraint failed message and no clue what went wrong
> Dominique Devienne hat am 23. Mai 2016 um 13:42 > geschrieben: > > > On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl mailbox.org > > wrote: > > > > > Dominique Devienne hat am 23. Mai 2016 um 11:20 > > geschrieben: > > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl < > > bernd.lehmkuhl at mailbox.org > > > > [...] What might cause a "constraint failed" message following > > this command: [...] > > > > > > Which version of SQLite? More recent ones tell you which constraint > > failed, > > > when they are named, which yours are (a good thing IMHO). > > > > Most recent one - 3.12.2. Unfortunately without any name. > > [...] > > > /*** t_geometrie_index ***/ > CREATE VIRTUAL TABLE t_geometrie_index USING rtree( > id, > [...] > > sqlite> INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax) > >...> SELECT > >...> t.auto_id, [...] > >...> FROM > >...> ( > >...> SELECT > >...> k.id, [...] > >...> FROM > >...> t_geometrie_knoten k > >...> > >...> UNION ALL << > >...> > >...> SELECT > >...> p.id, [...] > >...> FROM > >...> t_geometrie_punkte p > >...> GROUP BY > >...> p.id > >...> ) sub JOIN t_geometrie_typ t > >...> ON sub.id = t.id; > > Error: constraint failed > > sqlite> rollback; > > sqlite> .quit > > > > OK, was worth a shot. I had a feeling it might be related to the RTREE > vtables. > > Never used RTREE myself, in SQLite, although I know what this is. > Could it be your you "knoten" and "punkte" tables have values with the same > IDs? Gotcha! Thanks. Even though I claimed having checked that auto_id is unique in that query, you just proofed me wrong: SELECT auto_id, COUNT(*) FROM ( SELECT t.auto_id, sub.xmin, sub.xmax, sub.ymin, sub.ymax FROM ( SELECT k.id, k.x AS xmin, k.x AS xmax, k.y AS ymin, k.y AS ymax FROM t_geometrie_knoten k UNION ALL SELECT p.id, MIN(p.x), MAX(p.x), MIN(p.y), MAX(p.y) FROM t_geometrie_punkte p GROUP BY p.id ) sub JOIN t_geometrie_typ t ON sub.id = t.id ) GROUP BY auto_id HAVING COUNT(*) > 1 gives me one auto_id with three occurrences. I should check my data more thoroughly, I guess. Thanks for saving my day :-)
[sqlite] Updating the 'default' clause of a column?
Thanks Simon! Don't worry about the type 'timestamp'. We know how SQLite works with types. We use timestamp because it is a good thing for documentation purpose. We actually store an integer or real in there according to our application rules. For the short story we initially chose to always store Juliandays in our databases. But recently decided to change for a custom binary integer format which includes date, time, and bias from UTC. This is why we need to patch some older existing databases to use default (now()) instead of default (julianday()). Our now() returns the properly encoded integer. We also have overloads for date(), time() and datetime() to returning readable string values when some hand work has to be done on the command line. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia (from mobile device), integral.be/om > Le 23 mai 2016 ? 13:12, Simon Slavin a ?crit : > > I notice only one thing, but you're at the time when it's easy to change it. > The 'type' you've picked for this column is 'timestamp'. SQLite has no such > type. I recommend you use one of INTEGER, REAL or TEXT depending on the > value that your 'now()' function returns. > > You won't have found a problem with it so far, but you may encounter one in > other parts of your program which set or obtain a value in that column.
[sqlite] constraint failed message and no clue what went wrong
On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl wrote: > > > Dominique Devienne hat am 23. Mai 2016 um 11:20 > geschrieben: > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl < > bernd.lehmkuhl at mailbox.org > > > [...] What might cause a "constraint failed" message following > this command: [...] > > > > Which version of SQLite? More recent ones tell you which constraint > failed, > > when they are named, which yours are (a good thing IMHO). > > Most recent one - 3.12.2. Unfortunately without any name. > [...] /*** t_geometrie_index ***/ CREATE VIRTUAL TABLE t_geometrie_index USING rtree( id, [...] sqlite> INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax) >...> SELECT >...> t.auto_id, [...] ...> FROM >...> ( >...> SELECT >...> k.id, [...] >...> FROM >...> t_geometrie_knoten k >...> >...> UNION ALL << >...> >...> SELECT >...> p.id, [...] >...> FROM >...> t_geometrie_punkte p >...> GROUP BY >...> p.id >...> ) sub JOIN t_geometrie_typ t >...> ON sub.id = t.id; > Error: constraint failed > sqlite> rollback; > sqlite> .quit > OK, was worth a shot. I had a feeling it might be related to the RTREE vtables. Never used RTREE myself, in SQLite, although I know what this is. Could it be your you "knoten" and "punkte" tables have values with the same IDs? Try manually adding two rows into t_geometrie_index using for example insert into t_geometrie_index values (1, ...), (1, ...) and see if you get the same error. I'm guessing it might be coming from this. --DD PS: 3.13.0 just released I think, so not the latest anymore :)
[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] constraint failed message and no clue what went wrong
> Dominique Devienne hat am 23. Mai 2016 um 11:20 > geschrieben: > > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl mailbox.org > > wrote: > > > [...] What might cause a "constraint failed" message following this > > command: [...] > > > > Which version of SQLite? More recent ones tell you which constraint failed, > when they are named, which yours are (a good thing IMHO). Most recent one - 3.12.2. Unfortunately without any name. Microsoft Windows [Version 10.0.10586] (c) 2015 Microsoft Corporation. Alle Rechte vorbehalten. D:\Programmierung\NAS\Diverse Echtdaten\Schortens\2016-03-31>sqlite3 000-0-katalog-1-3730-17851-2016-03-31-08-44-03__postnas_grafik.db3 SQLite version 3.12.2 2016-04-18 17:30:31 Enter ".help" for usage hints. sqlite> begin; sqlite> INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax) ...> SELECT ...> t.auto_id, ...> sub.xmin, ...> sub.xmax, ...> sub.ymin, ...> sub.ymax ...> FROM ...> ( ...> SELECT ...> k.id, ...> k.x AS xmin, ...> k.x AS xmax, ...> k.y AS ymin, ...> k.y AS ymax ...> FROM ...> t_geometrie_knoten k ...> ...> UNION ALL ...> ...> SELECT ...> p.id, ...> MIN(p.x), ...> MAX(p.x), ...> MIN(p.y), ...> MAX(p.y) ...> FROM ...> t_geometrie_punkte p ...> GROUP BY ...> p.id ...> ) sub JOIN t_geometrie_typ t ...> ON sub.id = t.id; Error: constraint failed sqlite> rollback; sqlite> .quit > > So using a newer version might help your troubleshooting. --DD > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL logic error or missing database no such table
Are we sure that data are being stored at-all? Also, how is the database being encrypted? On Sun, May 22, 2016 at 3:02 PM, Rajendra Shirhatti wrote: > Hi Randall, > Thank you so much for your reply. > I don't have enough data to confirm whether database file is missing or > it's due to some other reason. > According to my initial investigation, it looks like the application is > able to create database and perform all the operations successfully upon > service start. I'm leaning towards the possibility that the tables are > somehow getting deleted or the database file becomes inaccessible when the > service is RESTARTED. We open the database connection during service > startup and it stays alive as long as service is running. > Is there any scenario that could corrupt the database because the > connection was not closed successfully? > > Thank you once again. > > -Raj > > On Sun, May 22, 2016 at 4:54 AM, R.A. Nagy wrote: > > > Try: > > > > .schema [tablename] > > > > Since there is probably nothing there, you probably need to create a > table. > > > > If you are new to SQL / SQLite, then here are some helpful videos: > > > > https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy > > > > > > Cheers, > > > > -Randall Nagy > > President, Soft9000.com > > > > > > > > > > On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti < > rrshirhatti at gmail.com > > > > > wrote: > > > > > Hi, > > > We've a .Net Windows Service that uses SQLite 1.0.93.0 for database > > > operations. The SQLite package contains two binaries, > > > system.data.sqlite.dll and sqlite.interop.dll. > > > > > > The database is encrypted and the blob it contains is encrypted as > well. > > > SQLite is intermittently throwing following exceptions while retrieving > > > data from the database. > > > > > > > > >- *SQL logic error or missing database no such table*. > > > > > > The code is trying to execute a simple query which looks something > like > > > this: > > > SELECT x, y from [tablename] > > > > > > This exception is raised while executing SQLiteCommand.ExecuteReader(). > > The > > > stack trace points to > > > at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String > > strSql, > > > SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) > > > at System.Data.SQLite.SQLiteCommand.BuildNextCommand() > > > at System.Data.SQLite.SQLiteDataReader.NextResult() > > > > > > > > > > > > * - Data Source cannot be empty. Use :memory: to open an in-memory > > > database- unable to open database file * > > > > > > These two exceptions are raised while trying to open the connection. > > > > > > > > > Any help or guidance would be appreciated. > > > > > > Thank you, > > > Raj > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Updating the 'default' clause of a column?
Hello, Assume a table simplified for this discussion as: create table T(C timestamp default (julianday()) not null); I simply have to use a new function (let's call it 'now()' other than julianday() to define the default for this column. I have apparently successfully tested this: pragma writable_schema=1; update sqlite_master set sql='create table T(C timestamp default (now()) not null)' where type='table' and name='T'; pragma writable_schema=0; (and then disconnect / reconnect). The automated patch would actually select the sql column value for the right table, edit the string appropriately, changing nothing else, then update it as above. This will happen in a situation where a single connection (that one) is made to the db (that can be guaranteed by the host app). Apparently, from testing, I do not seem to suffer from side-effects and the default is properly applied when needed. But am I missing some important detail that could hurt me later? Or do you agree with this procedure? -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] Updating the 'default' clause of a column?
On 23 May 2016, at 11:14am, Olivier Mascia wrote: > But am I missing some important detail that could hurt me later? I notice only one thing, but you're at the time when it's easy to change it. The 'type' you've picked for this column is 'timestamp'. SQLite has no such type. I recommend you use one of INTEGER, REAL or TEXT depending on the value that your 'now()' function returns. You won't have found a problem with it so far, but you may encounter one in other parts of your program which set or obtain a value in that column. Simon.
[sqlite] SQL logic error or missing database no such table
Yes, I'm very positive the data is stored otherwise the application would throw some exception. The database is encrypted by calling passing a private key to the connection using ChangePassword(). Thank you, Raj On Mon, May 23, 2016 at 9:16 AM, R.A. Nagy wrote: > Are we sure that data are being stored at-all? > > Also, how is the database being encrypted? > > > On Sun, May 22, 2016 at 3:02 PM, Rajendra Shirhatti > > wrote: > > > Hi Randall, > > Thank you so much for your reply. > > I don't have enough data to confirm whether database file is missing or > > it's due to some other reason. > > According to my initial investigation, it looks like the application is > > able to create database and perform all the operations successfully upon > > service start. I'm leaning towards the possibility that the tables are > > somehow getting deleted or the database file becomes inaccessible when > the > > service is RESTARTED. We open the database connection during service > > startup and it stays alive as long as service is running. > > Is there any scenario that could corrupt the database because the > > connection was not closed successfully? > > > > Thank you once again. > > > > -Raj > > > > On Sun, May 22, 2016 at 4:54 AM, R.A. Nagy wrote: > > > > > Try: > > > > > > .schema [tablename] > > > > > > Since there is probably nothing there, you probably need to create a > > table. > > > > > > If you are new to SQL / SQLite, then here are some helpful videos: > > > > > > > https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy > > > > > > > > > Cheers, > > > > > > -Randall Nagy > > > President, Soft9000.com > > > > > > > > > > > > > > > On Sat, May 21, 2016 at 4:09 PM, Rajendra Shirhatti < > > rrshirhatti at gmail.com > > > > > > > wrote: > > > > > > > Hi, > > > > We've a .Net Windows Service that uses SQLite 1.0.93.0 for database > > > > operations. The SQLite package contains two binaries, > > > > system.data.sqlite.dll and sqlite.interop.dll. > > > > > > > > The database is encrypted and the blob it contains is encrypted as > > well. > > > > SQLite is intermittently throwing following exceptions while > retrieving > > > > data from the database. > > > > > > > > > > > >- *SQL logic error or missing database no such table*. > > > > > > > > The code is trying to execute a simple query which looks something > > like > > > > this: > > > > SELECT x, y from [tablename] > > > > > > > > This exception is raised while executing > SQLiteCommand.ExecuteReader(). > > > The > > > > stack trace points to > > > > at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String > > > strSql, > > > > SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) > > > > at System.Data.SQLite.SQLiteCommand.BuildNextCommand() > > > > at System.Data.SQLite.SQLiteDataReader.NextResult() > > > > > > > > > > > > > > > > * - Data Source cannot be empty. Use :memory: to open an in-memory > > > > database- unable to open database file * > > > > > > > > These two exceptions are raised while trying to open the connection. > > > > > > > > > > > > Any help or guidance would be appreciated. > > > > > > > > Thank you, > > > > Raj > > > > ___ > > > > sqlite-users mailing list > > > > sqlite-users at mailinglists.sqlite.org > > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > ___ > > > sqlite-users mailing list > > > sqlite-users at mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] constraint failed message and no clue what went wrong
On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl wrote: > [...] What might cause a "constraint failed" message following this > command: [...] > Which version of SQLite? More recent ones tell you which constraint failed, when they are named, which yours are (a good thing IMHO). So using a newer version might help your troubleshooting. --DD
[sqlite] System.Data.SQLite fails on rtree index creation (NuGet install)
Jason Doherty wrote: > >CREATE VIRTUAL TABLE IF NOT EXISTS TASK_SPX USING rtree(id, minx, maxx, > miny, maxy); > > fails with > >SQLite error (1); no such table: main.sqlite_stat1 > Could you run the SQL query "ANALYZE;" on the database and see if that clears the issue? -- Joe Mistachkin
[sqlite] constraint failed message and no clue what went wrong
Dear list,
having the following database schema:
/*** t_geometrie_typ ***/
CREATE TABLE t_geometrie_typ(
auto_id INTEGER PRIMARY KEY AUTOINCREMENT, -- automatically generated id as
link to the r*Tree index
id TEXT UNIQUE NOT NULL,
typ TEXT COLLATE NOCASE NOT NULL,
objektart TEXT NOT NULL,
crs TEXT,
qualitaetsangabenDatenerhebung TEXT);
CREATE INDEX i_geometrie_typ_crs
ON t_geometrie_typ (crs, id);
CREATE INDEX i_geometrie_typ_objektart
ON t_geometrie_typ (objektart, id);
/*** t_geometrie_umring ***/
CREATE TABLE t_geometrie_umring(
id TEXT,
umringnummer INTEGER NOT NULL,
typ TEXT NOT NULL COLLATE NOCASE,
CONSTRAINT fk_geometrie_umring_id FOREIGN KEY (id)
REFERENCES t_geometrie_typ (id) ON DELETE CASCADE,
CONSTRAINT c_geometrie_umring_typ CHECK (typ IN ('Exterior', 'Interior')),
CONSTRAINT u_geometrie_umring UNIQUE (id, umringnummer));
/*** t_geometrie_punkte ***/
CREATE TABLE t_geometrie_punkte(
id TEXT,
x REAL NOT NULL,
y REAL NOT NULL,
bulge REAL NOT NULL,
umringnummer INTEGER,
laufendenummer INTEGER NOT NULL ,
CONSTRAINT fk_geometrie_punkte_id FOREIGN KEY (id)
REFERENCES t_geometrie_typ (id) ON DELETE CASCADE,
CONSTRAINT u_geometrie_punkte UNIQUE (id, umringnummer, laufendenummer));
/*** t_geometrie_knoten ***/
CREATE TABLE t_geometrie_knoten(
id TEXT NOT NULL,
x REAL NOT NULL,
y REAL NOT NULL,
CONSTRAINT pk_geometrie_knoten PRIMARY KEY (id, x, y),
CONSTRAINT fk_geometrie_knoten_id FOREIGN KEY (id)
REFERENCES t_geometrie_typ (id) ON DELETE CASCADE);
/*** t_geometrie_index ***/
CREATE VIRTUAL TABLE t_geometrie_index USING rtree(
id,
xmin,
xmax,
ymin,
ymax);
What might cause a "constraint failed" message following this command:
INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax)
SELECT
t.auto_id,
sub.xmin,
sub.xmax,
sub.ymin,
sub.ymax
FROM
(
SELECT
k.id,
k.x AS xmin,
k.x AS xmax,
k.y AS ymin,
k.y AS ymax
FROM
t_geometrie_knoten k
UNION ALL
SELECT
p.id,
MIN(p.x),
MAX(p.x),
MIN(p.y),
MAX(p.y)
FROM
t_geometrie_punkte p
GROUP BY
p.id
) sub JOIN t_geometrie_typ t
ON sub.id = t.id;
I don't see any dependencies on the r*Tree table t_geometrie_index.
Executing just the SELECT part of the query I checked that all xmin's are less
or equal to the xmax's, the same holds true for the y-values. There are no
double auto_id's.
So what might cause the error message "constraint failed"?
[sqlite] View workarounds
My suggestion is add the extra columns you need to the view, then when you make a query against that view, only specify the more limited set of output columns you want in the final output As others have said already, don?t think of a view as a stored query. Think of it as multiple joined tables into a ?virtual? table which you can then do more simple queries against then you would have to do in some monster join. it basically will let you hide all the complicated stuff you say you have now into the view, then use very simple select statements on that view to produce your final report?which only shows the columns you want. On May 23, 2016, at 7:02 AM, Balaji Ramanathan wrote: > 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. > >
[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
[sqlite] input using a dropdown list
Everything works great. Again I had another typo but I saw it and once fixed run great. Thank you for all your help. On 5/22/16, 16:45, "Simon Slavin" wrote: > >On 23 May 2016, at 12:37am, Gary Ehrenfeld wrote: > >> It works but how do I get it to work with an input statement so I can insert >> it into the hive_type column in the hive table. > >I don't see anything you did wrong. Have it construct the and > tags, View the Source of the page and look at the HTML it constructs. > If you can't figure it out, break it into two test chunks: > >One chunk makes a and from fixed text. Don't do any SQLite. > >One chunk reads the SQLite rows, but makes just displays them in >paragraphs. > >Simon. >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] input using a dropdown list
On 23 May 2016, at 12:37am, Gary Ehrenfeld wrote: > It works but how do I get it to work with an input statement so I can insert > it into the hive_type column in the hive table. I don't see anything you did wrong. Have it construct the and tags, View the Source of the page and look at the HTML it constructs. If you can't figure it out, break it into two test chunks: One chunk makes a and from fixed text. Don't do any SQLite. One chunk reads the SQLite rows, but makes just displays them in paragraphs. Simon.
[sqlite] input using a dropdown list
On 22 May 2016, at 11:57pm, Gary Ehrenfeld wrote: > I corrected the results to result and everything works. Thank for seeing > that. I feel really stupid right now. Don't feel bad. We've all done it. Second pair of eyes always helps. Simon.
[sqlite] input using a dropdown list
On 22 May 2016, at 11:46pm, Gary Ehrenfeld wrote: > Yes. Here is the code I am using but when I run it all I get is an empty list. Just as a test, replace your code which makes the dropdown list with code which just shows the options on the display: "".$value."" Get that working first, then worry about a dropdown list. >$result = $dbh->query($sql); >while ($row = $results->fetchArray($result)) That looks wrong. Try to distinguish between your two variables $result and $results . Simon.
[sqlite] input using a dropdown list
On 22 May 2016, at 11:15pm, Gary Ehrenfeld wrote: > Any suggestions? I am using SQLIte3 and PHP. Before you try to make a dropdown list with options taken from a SQLite database, do you know how to make a dropdown list with fixed text options ? Simon.
