Re: [sqlite] INSERT is corrupting a database
Kevin O'Gorman wrote: > If I go on to the second table, it appears to finish normally, but when I > try to look at the database with sqlite3, a command-line tool for > interacting with SQLite, it says the database is corrupt. What version? > If however, I split the program into two programs, one for each table, and > run them one after another, all is well. Same code, each with parts of it > if-else-ed out. It's possible that there is a bug in your code. Which you have not shown. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help with System.Data.SQLite
SQLite does not really care about precision and scale. See https://sqlite.org/datatype3.html for more information. The culprit lies in the use of SqlDecimal which cannot be used by System.Data.SQLite directly and has no implicit conversion to something understandable by System.Data.SQLite (for more information about SqlDecimal see https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx). However, an explicit conversion exists, so an explicit cast to System.Decimal would work. Maybe you could try this at the Point where you assign the Value (more information about the explicit operator can be found here: https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx). Also have a close look at what is being fed into the other numeric columns. I doubt these are SqlDecimals too. Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33: Thank you for response. It looks like we are on different pages here. Let me start from the beginning. We are moving data between SQLite database and Microsoft SQL Server. At this time we are using Excel files but we run into some Excel limitation. I am trying to change Excel files to SQLite database files. The test table has several NUMERIC type columns. We have NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) column. It looks like the problem is not with NUMERIC type in general, but only when precision is equal to scale. Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB Sent: Tuesday, January 10, 2017 2:08 AM To: SQLite mailing list Subject: Re: [sqlite] Need help with System.Data.SQLite System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and thus the SQLite provider doesn't know how to handle it. Try using System.Decimal as a more generic approach. If you need to be portable across providers, you will be better off using classes from System.Data.Common anyway. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This message, and any of its attachments, is for the intended recipient(s) only, and it may contain information that is privileged, confidential, and/or proprietary and subject to important terms and conditions available at http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the intended recipient, please delete this message and immediately notify the sender. No confidentiality, privilege, or property rights are waived or lost by any errors in transmission. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT is corrupting a database
On 11 Jan 2017, at 3:28am, Kevin O'Gorman wrote: > I have a modest amount of data that I'm loading into an SQLite database for > the first time. For the moment it contains just two tables and a few > indices, nothing else. The first table loads okay, and if I stop the > process at that point, all is well and I can look at the database. > > If I go on to the second table, it appears to finish normally, but when I > try to look at the database with sqlite3, a command-line tool for > interacting with SQLite, it says the database is corrupt. Make absolutely sure you’re starting with a new database file each time, not continuing to write to an already-corrupt file. At stages during your Python program, including after you’ve finished loading the first table, use the following command to check to see whether the database is correct: PRAGMA integrity_check Use the same command in the command-line tool. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key error...
On 11 Jan 2017, at 1:02am, Keith Medcalf wrote: > You are correct, however, if there were a unique constraint placed on > tracks.name, then a given track could only appear once (in the first case), > or in multiple places (in the second case). _The Power of Love_ was recorded by Frankie Goes to Hollywood, Jennifer Rush, and Huey Lewis and The News. Not only are these different recordings, they’re different songs. Nor can you place a UNIQUE requirement on the combination of (title,authors). There are three different /studio/ edits of Bowie’s _Heroes_, and two of them are different enough that someone might like one and not the other. Not to mention numerous different live versions which appear on convert albums and DVDs. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT is corrupting a database
This is a problem I don't quite know how to report in a way that will be useful. I'm using Python 3.5 and its builtin sqlite package. I have a modest amount of data that I'm loading into an SQLite database for the first time. For the moment it contains just two tables and a few indices, nothing else. The first table loads okay, and if I stop the process at that point, all is well and I can look at the database. If I go on to the second table, it appears to finish normally, but when I try to look at the database with sqlite3, a command-line tool for interacting with SQLite, it says the database is corrupt. If however, I split the program into two programs, one for each table, and run them one after another, all is well. Same code, each with parts of it if-else-ed out. I don't know what to blame, or what to try. -- word of the year: *kakistocracy* ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key error...
You are correct, however, if there were a unique constraint placed on tracks.name, then a given track could only appear once (in the first case), or in multiple places (in the second case). > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Ken Wagner > Sent: Monday, 9 January, 2017 23:46 > To: SQLite mailing list > Subject: Re: [sqlite] Foreign key error... > > Keith, > > "this does not allow the same track on multiple albums" with the same > trackno, but a different trackno seems to work. Thus results cannot be > guaranteed valid? > > Ken > > > On 01/08/2017 06:57 AM, Keith Medcalf wrote: > > On Sunday, 8 January, 2017 05:05, Ken Wagner > wrote: > > > >> Keith, > >> > >> Ahh, better to be specific and avoid simplistic assumptions. > >> > >> For foreign keys which is better: 'trackerartist' or 'artistid' in the > >> track file? Does it matter? Is it personal preference? > > It is a matter of personal preference. Personally, I use the same name > for the same data, and do not add useless prefaces, and usually do not > preface the id with the table name. For example: > > > > create table artists ( > > id integer primary key, > > name text collate nocase unique > > ); > > create table albums ( > > id integer primary key, > > name text collate nocase unique, > > artistid integer references artists > > ); > > create table tracks ( > > id integer primary key, > > seq integer, > > name text collate nocase, > > artistid integer references artists, > > albumid integer references albums, > > unique (albumid, seq) > > ); > > > > select albums.name as albumname, > > albumartists.name as albumartist, > > tracks.seq as trackno, > > tracks.name as trackname, > > trackartists.name as trackartist > >from albums, tracks, artists as albumartists, artists as trackartists > > where tracks.artistid = trackartists.id > > and tracks.albumid = albums.id > > and albums.artistid = albumartists.id; > > > > Of course, this does not allow the same track on multiple albums. For > that you need another table to do the N:M mapping: > > > > create table artists ( > > id integer primary key, > > name text collate nocase unique > > ); > > create table albums ( > > id integer primary key, > > name text collate nocase unique, > > artistid integer references artists > > ); > > create table tracks ( > > id integer primary key, > > name text collate nocase, > > artistid integer references artists, > > ); > > create table albumtracks ( > > id integer primary key, > > albumid integer references albums, > > trackid integer references tracks, > > seq integer, > > unique (albumid, seq), > > unique (albumid, trackid), > > unique (trackid, albumid) > > ); > > > > select albums.name as albumname, > > albumartists.name as akbumartist, > > albumtracks.seq as trackno, > > tracks.name as trackname, > > trackartists.name as trackartist > >from albums, albumtracks, tracks, artists as albumartists, artists as > trackartists > > where tracks.artistid = trackartists.id > > and albumtracks.albumid = albums.id > > and albumtracks.trackid = tracks.id > > and albums.artistid = albumartists.id; > > > >> On 01/08/2017 05:46 AM, Keith Medcalf wrote: > >>> ... join ... using (column) has nothing whatever to do with foreign > >> keys. > >>> "FROM a JOIN b USING (c) is "syntactic sugar" (meaning that it is > >> sweetening and full of calories, but has no nutritional value) for the > >> expression "FROM a, b WHERE a.c = b.c" > >>> This is so, for example, if you use really long stupid names it saves > >> considerable space and typing: > >>> SELECT * FROM TheFirstTableToBeJoined JOIN TheSecondTableToBeJoined > >> USING (TheCommonColumnNameBetweenTheFirstAndTheSecondTable); > >>> -vs- > >>> SELECT * FROM TheFirstTableToBeJoined, TheSecondTableToBeJoined WHERE > >> > TheFirstTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTabl > >> e = > >> > TheSecondTableToBeJoined.TheCommonColumnNameBetweenTheFirstAndTheSecondTab > >> le; > >>> > -Original Message- > From: sqlite-users [mailto:sqlite-users- > >> boun...@mailinglists.sqlite.org] > On Behalf Of Ken Wagner > Sent: Sunday, 8 January, 2017 04:04 > To: SQLite mailing list > Subject: Re: [sqlite] Foreign key error... > > Thanks, Kees, > > The message is helpful as a warning. > > select artistname, trackname from artist inner join track on > trackartist = artistid; works just fine. > > But isn't the efficiency of 'using (artistid)' more desirable? > > Is the use of a 'trackerartist' as the foreign key used because it is > more informative? > > I.e., wherever it is seen it shows the track-artist link? But is more > demanding when coding: > > 'on trackerartist = art
Re: [sqlite] Bug in mkFullPathname
On Jan 10, 2017, at 3:17 PM, Richard Hipp wrote: > > On 1/10/17, skywind mailing lists wrote: >> zout will contain finally "//abc.def". This is an invalid >> pathname, I believe. > > Actually, //abc.def and /abcdef are equivalent, on every unix system I > have ever used. Try it! HTTP servers generally obey that, too, but Fossil doesn’t: https://www.fossil-scm.org/index.html//timeline fails. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in mkFullPathname
On 1/10/17, skywind mailing lists wrote: > zout will contain finally "//abc.def". This is an invalid > pathname, I believe. Actually, //abc.def and /abcdef are equivalent, on every unix system I have ever used. Try it! ls /bin ls //bin ls //bin -- 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] Bug in mkFullPathname
Assume I have got a zPath like abc.def passed to mkFullPathname. Inside mkFullPathname zPath[0] != '/' will evaluate to true. Assume further that osGetcwd(..) will return '/' (which can actually be the case under Android and theoretically under other systems as well but probably less likely). In this case zout will contain finally "//abc.def". This is an invalid pathname, I believe. Best regards, Hartwig ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key error...
On the foreign key page (http://www.sqlite.org/foreignkeys.html) at the very end of section 3 is has: CREATE TABLE artist( artistidINTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist ); CREATE INDEX trackindex ON track(trackartist); The block above uses a shorthand form to create the foreign key constraint. Attaching a "REFERENCES " clause to a column definition creates a foreign key constraint that maps the column to the primary key of . Refer to the CREATE TABLE documentation for further details. In the create table page if you expand column-def, then column-constraint, then foreign-key-clause, you can see there's a flow path that skips the column names of the parent table. Unfortunately I don't see any explainatory text on the page for what that actually signifys, so yeah, it's a little hidden. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of James K. Lowden Sent: Tuesday, January 10, 2017 1:14 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Foreign key error... On Sun, 08 Jan 2017 05:57:46 -0700 "Keith Medcalf" wrote: > artistid integer references artists Hmph. Learn something new every day. Where is that abbreviated form documented? I looked for "references" on the Create Table page, and didn't find anything about its default arguments. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CSV.c
On Jan 10, 2017, at 3:03 AM, tbuck...@frontier.com wrote: > > I can manually do it with the dot (.) tools, but I want to automate this by > executing one exe file. Ah, I see, you want to reinvent the wheel[*] because your platform of choice doesn’t have shell scripts. Got it. In that case, I’d dig into the implementation for those existing commands, and wrap them the same way src/shell.c in the SQLite source base does. [*]: https://sqlite.org/cli.html#csv_import ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign key error...
On Sun, 08 Jan 2017 05:57:46 -0700 "Keith Medcalf" wrote: > artistid integer references artists Hmph. Learn something new every day. Where is that abbreviated form documented? I looked for "references" on the Create Table page, and didn't find anything about its default arguments. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] About ticket c92ecff2ec5f1784 LEFT JOIN problem
Hello Richard ! Now that you are dealing with this ticket http://www.sqlite.org/src/info/c92ecff2ec5f1784 could be a good moment to acknowledge the problem reported before (see bellow), I'm not sure why you have answered this way without acknowledge any further action on it. I was expecting a bug ticket for it or at minimum a documentation of this weird behavior of the sqlite3 planner so other people could be aware of it. Cheers ! === On 1/5/17, Domingo Alvarez Duarte wrote: Hello ! Today I found this unexpected behavior when using sqlite3 trunk: When using views with joins sqlite3 is choosing expected plans except for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior. === create table if not exists a(id integer primary key, val text); create table if not exists b(id integer primary key, a_id integer not null, val text); create view if not exists b_view as select b.*, a.* from b left join a on b.a_id=a.id; create table if not exists c(id integer primary key, b_id integer not null, val text); select 'bad unexpected plan'; explain query plan select c.*, b_view.* from c left join b_view on c.b_id=b_view.id; Can you rewrite your query as: SELECT * FROM c LEFT JOIN b ON c.b_id=b.id LEFT JOIN a ON b.id=a.id; -- D. Richard Hipp === ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Hello Intro verify
Hello All, I am using SQLite with LiveCode 8.1.2 As per LiveCode recommendations I am moving from a CardBase to an SQLite DataBase. I really like the speed increases I have witnessed and thought I should join this mailing list. Greeting to you from BC Canada. Thanks Rob ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on using dates and hours for bin counts, time-zone agnostic
I agree with Dave Raymond, I would use "-MM-DD HH:MM:SS" format because it is both sortable and comparable as well as an ISO standard (ISO 8601). In addition, you don't have to know the base date the number of days were calculated since. The base date (epoch) for MS Excel Windows, MS Excel Mac and MS Access are all different. Besides it is human readable and you can eyeball if the string seams reasonable. Are you using just one datetime for each interval? or are you using start and end times? Are the intervals GUARANTEED to be 15 minutes? Really? Has everyone agreed whether the timestamp is the start or end time? What reporting applications (besides SQLite) will be used? (for example, is anyone going to produce charts?) How do these applications represent time? How and when are the times synchronized (is it an automatic process? does it require human intervention? is it performed manually?) National Institute of Standards and Technology (NIST) Time http://www.time.gov/ Ideally, you would like to store the location, timezone and a delta (hours) from GMT. Time Zone database http://www.iana.org/time-zones Time Zone details http://www.iana.org/time-zones/repository/tz-link.html Some best practices https://www.w3.org/TR/timezone/ But, as Einstein said, it's all relative. Jim Callahan Orlando, FL On Tue, Jan 10, 2017 at 10:01 AM, Jeffrey Mattox wrote: > My application will be counting events and saving the totals for every > 15-minute time period over many days -- that table will have an eventCount > and a date/time for each bin. I'll be displaying the data as various > histograms: the number of events per day over the year (365 values), the > number of events for every day of the week (7 values), the number of events > for each quarter hour of every day (24*7 values), etc. > > Pseudo SQL: >UPDATE Events SET count = count + 1 WHERE eventTime = > integer(now_minutes/15) >SELECT TOTAL(count) FROM Events WHERE eventTime is Jan 3 of every year >SELECT TOTAL(count) FROM Events WHERE eventTime is a Friday >SELECT TOTAL(count) FROM Events WHERE eventTime is between 10:00 am > and 10:15 am >etc. > > How should I define "eventTime" to facilitate the storage and retrieval of > the counts? E.g., one field or a pair of fields (the date and the > 15-minute-period)? I'm unsure whether to use date and time, or datetime, > or julianday. > > One snake pit I want to avoid is timezone hassle. Everything should be > based on the local clock time, regardless of where the computer is located > later. If I store a value for "1/1/17 at noon" when I'm in the eastern TZ > and later ask for the value at "1/1/17 at noon" when I'm in any other time > zone, I want the same result regardless of the time zone -- as if it's > always the same date and time everywhere. (I've handled this in the past > by converting the local time to a string, and saving that without any TZ > indicator.) > > How should I define the columns for date and time? > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential small incompatibility 3.15 -> 3.16
This is, arguably, a bug fix. On 1/10/17, Martin Raiber wrote: > Hi, > > with 3.15: > > sqlite> PRAGMA quick_check; > integrity_check > ok > > with 3.16: > > sqlite> PRAGMA quick_check; > quick_check > ok > > The second one is more intuitive, but existing applications may use the > first column name. > > Regards, > Martin Raiber > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- 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] Potential small incompatibility 3.15 -> 3.16
Hi, with 3.15: sqlite> PRAGMA quick_check; integrity_check ok with 3.16: sqlite> PRAGMA quick_check; quick_check ok The second one is more intuitive, but existing applications may use the first column name. Regards, Martin Raiber ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simplify multiple use of value in a trigger
On Sat, 7 Jan 2017 17:54:20 -0500 Gan Uesli Starling wrote: > As for the suggestion to 'normalize' my four tables to one ... yes, I > could do that ... but it would make for a LOT of redundant data > duplicated in plural columns, which seems to me in my doubtless > ignorant newbie status, as missing the point for a database. Hello Gan! It's been 15 years and counting. Funny our paths should cross here. Regarding your table design, I wouldn't necessarly combine your table regions, but I would change how you define your aggregations. I noticed in your SQL the comment "SQL doesn't support arrays". Understanding *why* there are no arrays in SQL is a step in understanding how to design and use SQL databases. In SQL generally -- and in the relational model, strictly -- all data are dealt with as *sets*. Not lists, not arrays. Set elements have no defined order. That is, unlike an array, all attributes are explicitly expressed as data. No meaning attaches to the fact that an element is the Nth element of the set. (You doubtless at some time have come across the problem of dealing with an array element and not knowing where it stood in the array. In SQL, that information must be explicit in order to exist!) The advantage of dealing with a single datatype is manifested in the simplicity of the relational operators. By virtue of adding just one column, arrays become tables and are, yes, supported. To a one, flavors of SQL with array support do so with limitations, even though they *only* add complexity. There is no array operation that cannot be expressed in SQL; ergo there is is no need for array notation in SQL. Once you think of your arrays as vertical instead of horizontal, if you will, your tables become easier to design and manipulate. For example, instead of CREATE TABLE world ( iso CHAR(2) PRIMARY KEY, un CHAR(3), country VARCHAR(45), count SMALLINT DEFAULT 0, /* Total Count, needs daily update */ _160m SMALLINT DEFAULT 0, /* Band Count, need daily update for each */ _80m SMALLINT DEFAULT 0, _60m SMALLINT DEFAULT 0, ... I would suggest two tables: CREATE TABLE world ( iso CHAR(2) PRIMARY KEY, un CHAR(3), country VARCHAR(45) ); Create Table Aggr ( iso CHAR(2) not NULL foreign key references world(iso), size int not NULL, total int not NULL, -- not "count" because keyword primary key (iso, size) ); Now, it's not clear to me that the Aggr table is well advised. I think it might better be a view. Definitely I would define it as a view and try updating the table from that view. If you're frequently referencing whole chunks of Aggr, it might make sense to maintain the table. If you're usually referencing only a few rows of it, it probably makes more sense to keep just the view. In any case, updating the above table is simpler and almost certainly faster. Instead of one update per column (which IIUC is the genesis of this thread), UPDATE world SET _160m = (SELECT count from country_160m WHERE country = world.country) WHERE country IN (SELECT country FROM country_160m); use one update per table. First, make a tiny static table Bands of the interesting bands you want (160, 80, etc.). Then: update Aggr set total = ( select count(*) from "something" -- not sure what the real source is where size = Bands.size and iso = Aggr.iso and size = Aggr.size ) where exists ( select 1 from "something" where iso = Aggr.iso and size = Aggr.size ); That updates all bands for all countries in one swell foop. If you prefer to see bands-by-country, as in your world table, create a view that uses a technique known as "folding". It's verbose (as SQL is wont) but it can be surprisingly fast. But don't be two hasty: except for reports, most queries against the Aggr table won't need more than a view rows. It is more efficient and as easy to use "world" would be. Instead of, select ... from world where _160m > 7; you have select ... from Aggr where size = 160 and total > 7; The SQL becomes more regular because you don't have to write different queries depending on which size (band) you're interested in. The execution is faster because there's less I/O: the row is narrower, and it's found by a binary search on the index supporting the primary key. By contrast, your "world" table would need an index on every _nnnm column which, besides being tedious to define, would increase the cost of your inserts. One last word on aggregation and views. Many programmers seem to come to SQL with the instinct of pre-computing their aggregates. Most programming languages have no support for computation with sets, no built-in support for aggregation and quantification. Furthermore, because most applications deal with a static snapshot of the data, the instinct to laboriously mas
Re: [sqlite] Advice on using dates and hours for bin counts, time-zone agnostic
I'm not quite sure on that first update statement, seems like you're not using
it right.
Otherwise though I think I'd recommend storing things as text "-MM-DD
HH:MM:SS" as that's the input type for strftime, which is what would be used
for the queries. It also doesn't include the time zone, which is also what you
were looking for. (Or at least I think it assumes everything is GMT)
SELECT count(*) FROM Events WHERE strftime('%m%d', eventTime) = '0103';--Jan 3rd
SELECT count(*) FROM Events WHERE strftime('%w', eventTime) = '4';--Friday
SELECT count(*) FROM Events WHERE strftime('%H%M', eventTime) between '1000'
and '1014';--10:00 up to 10:15
Heck, with that standardized text format you could even use LIKE to do a few of
those.
...WHERE eventTime LIKE '-01-03%';
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Jeffrey Mattox
Sent: Tuesday, January 10, 2017 10:02 AM
To: SQLite mailing list
Subject: [sqlite] Advice on using dates and hours for bin counts, time-zone
agnostic
My application will be counting events and saving the totals for every
15-minute time period over many days -- that table will have an eventCount and
a date/time for each bin. I'll be displaying the data as various histograms:
the number of events per day over the year (365 values), the number of events
for every day of the week (7 values), the number of events for each quarter
hour of every day (24*7 values), etc.
Pseudo SQL:
UPDATE Events SET count = count + 1 WHERE eventTime =
integer(now_minutes/15)
SELECT TOTAL(count) FROM Events WHERE eventTime is Jan 3 of every year
SELECT TOTAL(count) FROM Events WHERE eventTime is a Friday
SELECT TOTAL(count) FROM Events WHERE eventTime is between 10:00 am and
10:15 am
etc.
How should I define "eventTime" to facilitate the storage and retrieval of the
counts? E.g., one field or a pair of fields (the date and the
15-minute-period)? I'm unsure whether to use date and time, or datetime, or
julianday.
One snake pit I want to avoid is timezone hassle. Everything should be based
on the local clock time, regardless of where the computer is located later. If
I store a value for "1/1/17 at noon" when I'm in the eastern TZ and later ask
for the value at "1/1/17 at noon" when I'm in any other time zone, I want the
same result regardless of the time zone -- as if it's always the same date and
time everywhere. (I've handled this in the past by converting the local time
to a string, and saving that without any TZ indicator.)
How should I define the columns for date and time?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on using dates and hours for bin counts, time-zone agnostic
On 10 Jan 2017, at 3:01pm, Jeffrey Mattox wrote: > Pseudo SQL: > UPDATE Events SET count = count + 1 WHERE eventTime = > integer(now_minutes/15) > SELECT TOTAL(count) FROM Events WHERE eventTime is Jan 3 of every year > SELECT TOTAL(count) FROM Events WHERE eventTime is a Friday > SELECT TOTAL(count) FROM Events WHERE eventTime is between 10:00 am and > 10:15 am > etc. > > How should I define "eventTime" to facilitate the storage and retrieval of > the counts? E.g., one field or a pair of fields (the date and the > 15-minute-period)? I'm unsure whether to use date and time, or datetime, or > julianday. Because of the various different things you may base your requests on … Day of the year Day of week Time of day I think you may need to save multiple time/date columns. It can be extremely difficult to express a concept like "Every January 3rd" when all you have is a simple timestamp. So I would be saving /all/ the following columns: date-as-text, unixepoch, Julian Day, day-of-month, month, year, weekday, timezone Depending on your search you may select using one or more of the above. > One snake pit I want to avoid is timezone hassle. Everything should be based > on the local clock time, regardless of where the computer is located later. > If I store a value for "1/1/17 at noon" when I'm in the eastern TZ and later > ask for the value at "1/1/17 at noon" when I'm in any other time zone, Which local clock ? Which computer ? If the computer is not in the same timezone as the event, how does it know the timezone of the event ? Does 'January 3rd' mean that date in the locality of the event or in the locality of the person doing the search ? > I want the same result regardless of the time zone -- as if it's always the > same date and time everywhere. The normal way to do that is to standardise all timestamps as if they happened in UTC (what used to be thought of as Timezone +00, Greenwich Mean Time). Friday here can be Thursday there. In fact, if you allow for Summer Time adjustments, it can be Tuesday there. When you’re scanning for "WHERE eventTime is a Friday" you need to know what 'Friday' means. There are two ways I see this handled: The first is to save everything with a timezone and when you do your searching, compensate for it. The second is to convert all event times to UTC before any storage, but to save the event’s timezone as well as saving the UTC timestamp. Depending on how much flexibility you need to support in your queries, you may need to save all of these timezone, date-as-text, unixepoch, Julian Day, day-of-month, month, year, weekday, time for both the local time of the event, and for that datetime converted to UTC. This means you end up recording 15 different columns. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Advice on using dates and hours for bin counts, time-zone agnostic
My application will be counting events and saving the totals for every 15-minute time period over many days -- that table will have an eventCount and a date/time for each bin. I'll be displaying the data as various histograms: the number of events per day over the year (365 values), the number of events for every day of the week (7 values), the number of events for each quarter hour of every day (24*7 values), etc. Pseudo SQL: UPDATE Events SET count = count + 1 WHERE eventTime = integer(now_minutes/15) SELECT TOTAL(count) FROM Events WHERE eventTime is Jan 3 of every year SELECT TOTAL(count) FROM Events WHERE eventTime is a Friday SELECT TOTAL(count) FROM Events WHERE eventTime is between 10:00 am and 10:15 am etc. How should I define "eventTime" to facilitate the storage and retrieval of the counts? E.g., one field or a pair of fields (the date and the 15-minute-period)? I'm unsure whether to use date and time, or datetime, or julianday. One snake pit I want to avoid is timezone hassle. Everything should be based on the local clock time, regardless of where the computer is located later. If I store a value for "1/1/17 at noon" when I'm in the eastern TZ and later ask for the value at "1/1/17 at noon" when I'm in any other time zone, I want the same result regardless of the time zone -- as if it's always the same date and time everywhere. (I've handled this in the past by converting the local time to a string, and saving that without any TZ indicator.) How should I define the columns for date and time? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Connecting DataBases files into one
On 10 Jan 2017, at 12:08pm, rmroz80 wrote: > I have one question with sqlite database files. In my company (school) there > is an application called SIO (System Informacji Oświatowej - Educational > Information System). This program is running on 2 independent computers. Each > program has own sqlite file called SIO2.sqlite and data are written > separately on each machine. Do you need to run the program on both computers at the same time ? If you do not, then it may be easy to make them use the same data file. You could share filespace over a network. And you should do a lot of testing to make sure the application works when the data is shared this way. (Take a backup first !) If you need to run the program on both computers at the same time, and the program was not designed to allow this, then this could lead to corruption of the database. The program on one computer would make one change to the database file, but the program on the other computer might not expect the database file to be changed while it was running. So it will assume no changes have been made and make a different change to the database file. This second computer may not know a change was made by the other computer and it will save its own changes, reversing that change. You might want to ask the programmers of the the application if they have a solution. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Connecting DataBases files into one
Agree with Stephen and Dr. Hipp 1. Make copies of the SQLite data files when the SIO program is not in use and without using a network -- use a USB stick or removable disk (prevent corruption, always good to have a backup) 2. Using the SQLite command line interface (CLI) https://sqlite.org/cli.html use the "ATTACH DATABASE" SQL statement https://sqlite.org/lang_attach.html 3. Again at the SQLite command line use the ".databases" "dot-command" to verify the connection The ".databases" command shows a list of all databases open in the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file. sqlite> *.databases * https://sqlite.org/cli.html 4. Check the schemas of the two databases with the .tables, .schema and .indexes command 5. If the tables have EXACTLY the SAME FIELDS you can merge tables using the procedure described in this StackOverflow answer. Pay attention, however, the difference between UNION and UNION ALL http://stackoverflow.com/questions/30292367/sqlite-append-two-tables-from-two-databases-that-have-the-exact-same-schema Jim Callahan Orlando, FL On Tue, Jan 10, 2017 at 7:08 AM, rmroz80 wrote: > Good morningI have one question with sqlite database files. In my > company (school) there is an application called SIO (System Informacji > Oświatowej - Educational Information System). This program is running on 2 > independent computers. Each program has own sqlite file called SIO2.sqlite > and data are written separately on each machine. Few days ago my boss, ask > me is this possible to join data from two systems and create one file > containing data from both computers. Data files containing various > information about our school like: names, surnames, adressess and > equipment. Some data are on comp1, some on comp2, and now it is work for me > to create 3 comp containing data from 2 and 1. Sorry for my weak English, > because my native language is Polish.Thanks in advance Rafał Mroziński > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help with System.Data.SQLite
Thank you for response. It looks like we are on different pages here. Let me start from the beginning. We are moving data between SQLite database and Microsoft SQL Server. At this time we are using Excel files but we run into some Excel limitation. I am trying to change Excel files to SQLite database files. The test table has several NUMERIC type columns. We have NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) column. It looks like the problem is not with NUMERIC type in general, but only when precision is equal to scale. Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB Sent: Tuesday, January 10, 2017 2:08 AM To: SQLite mailing list Subject: Re: [sqlite] Need help with System.Data.SQLite System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and thus the SQLite provider doesn't know how to handle it. Try using System.Decimal as a more generic approach. If you need to be portable across providers, you will be better off using classes from System.Data.Common anyway. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This message, and any of its attachments, is for the intended recipient(s) only, and it may contain information that is privileged, confidential, and/or proprietary and subject to important terms and conditions available at http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the intended recipient, please delete this message and immediately notify the sender. No confidentiality, privilege, or property rights are waived or lost by any errors in transmission. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Connecting DataBases files into one
On 1/10/17, Stephen Chrzanowski wrote: > (Do not access the files remotely > while there is even a small chance that the remote computers will access > the database -- Corruption can occur) Everything Stephen says is correct. I'd just like to add that the possibility of corruption due to remote access is the fault of the network filesystem, not SQLite :-) Network filesystems have gotten better over the years, so even if you do access the files remotely, you will probably be ok. But you are still safer to make a copy of the databases and work off of the copy. That has the added benefit that if you make a programming mistake while doing the merge, you can always recopy the original databases and start over. -- 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] Connecting DataBases files into one
It is possible, but not automatically, and there are a couple of options available. It also depends on how the data is structured. For any interaction, you'll need to pull the data from the remote machines to the machine you'll be working on. (Do not access the files remotely while there is even a small chance that the remote computers will access the database -- Corruption can occur) The choices you have at that point are: 1> Copy the tables in the 'now-local' databases into a new database using SELECT * FROM DB1.T1 INTO NEWDB (Or some variation of that) 2> Use the ATTACH mechanism to pseudo-join all the databases together then run the required selects. (Note a VIEW will not work). Once you have either setup, you can build your queries around that. The issue with taking two distinctly different sets of data is data collision. If any of the tables in the two files contain primary keys that overlap, you'll need to come up with a mechanism to ensure that all Foreign Key relationships work. That'd mean looking at individual rows, and their relationships. On Tue, Jan 10, 2017 at 7:08 AM, rmroz80 wrote: > Good morningI have one question with sqlite database files. In my > company (school) there is an application called SIO (System Informacji > Oświatowej - Educational Information System). This program is running on 2 > independent computers. Each program has own sqlite file called SIO2.sqlite > and data are written separately on each machine. Few days ago my boss, ask > me is this possible to join data from two systems and create one file > containing data from both computers. Data files containing various > information about our school like: names, surnames, adressess and > equipment. Some data are on comp1, some on comp2, and now it is work for me > to create 3 comp containing data from 2 and 1. Sorry for my weak English, > because my native language is Polish.Thanks in advance Rafał Mroziński > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Connecting DataBases files into one
Good morningI have one question with sqlite database files. In my company (school) there is an application called SIO (System Informacji Oświatowej - Educational Information System). This program is running on 2 independent computers. Each program has own sqlite file called SIO2.sqlite and data are written separately on each machine. Few days ago my boss, ask me is this possible to join data from two systems and create one file containing data from both computers. Data files containing various information about our school like: names, surnames, adressess and equipment. Some data are on comp1, some on comp2, and now it is work for me to create 3 comp containing data from 2 and 1. Sorry for my weak English, because my native language is Polish.Thanks in advance Rafał Mroziński ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CSV.c
Maybe the following example may help: https://github.com/elau1004/TFR4SQLite/blob/master/src/test/031_testColumn.init Check out the wiki too regarding using the "-init" command line option. -Original Message- From: tbuckner To: sqlite-users Sent: Tue, Jan 10, 2017 2:03 am Subject: [sqlite] Using CSV.c Hello,I am having a little trouble implementing the csv.c program for SQLite. Are their any programming examples available?All I want to do is import a csv file to a database, sort the file and output the results to another csv file all in c code. I can manually do it with the dot (.) tools, but I want to automate this by executing one exe file.Tom...___sqlite-users mailing listsqlite-users@mailinglists.sqlite.orghttp://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using CSV.c
I would recommend using a script or batch file to automate the process using the command line tool. It would be easier to maintain and remove a level or two of complexity. On Tue, Jan 10, 2017 at 4:03 AM, tbuck...@frontier.com < tbuck...@frontier.com> wrote: > Hello,I am having a little trouble implementing the csv.c program for > SQLite. Are their any programming examples available? > All I want to do is import a csv file to a database, sort the file and > output the results to another csv file all in c code. I can manually do it > with the dot (.) tools, but I want to automate this by executing one exe > file.Tom... > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using CSV.c
Hello,I am having a little trouble implementing the csv.c program for SQLite. Are their any programming examples available? All I want to do is import a csv file to a database, sort the file and output the results to another csv file all in c code. I can manually do it with the dot (.) tools, but I want to automate this by executing one exe file.Tom... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
