Re: [sqlite] Date and age calculations
On Sep 16, 2009, at 10:02 PM, sqlite-users-requ...@sqlite.org wrote:
WHERE birth BETWEEN date('now','-24 years') AND date('now','-12
years')
Thank you Igor and D. Richard for your explanations and assistance. I
understand better now how the date comparisons function. Also, thank
you Igor for pointing out my redundant nested SELECT statement.
Craig Smith
cr...@macscripter.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date and age calculations
Hello:
I am having difficulties getting precise returns using a SELECT that
calculates the age of persons in my table. All dates are -MM-DD,
here is my SELECT statement:
SELECT db_id, Full_name, round((SELECT julianday('now') - julianday
(birth))/365,1) FROM members WHERE date ('now') - birth 12 AND date
('now') - birth 24 AND married = 'Single' AND moved = 0;
The problem is that anyone over the age of 12 does not show up in the
return until their age is actually nearer to 12.5. However, when I
change the SELECT to
birth = 12
I pick up a few persons whose ages are between 11 and 12. Is there
any way I can make the SELECT more precise?
Thank you very much,
Craig Smith
cr...@macscripter.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema design and/or SELECT construction
Hello: I have about 3000 electronic images that I am preparing to distribute to my family members. They use a variety of operating systems, so I am providing the images on remote hard drives, with the images divided into folders based on years the images were created. All images were obtained via scanning of negatives and slides, and each image has been keyworded in EXIF format. What I want is to create a master SQLite database catalog of all the images, and I have come up with a proposed schema: TABLE main (all pertinent image data such as date, location in folder, etc.) TABLE core_keywords (id, name) --This table would only hold the names of my immediate family members, each with a corresponding id TABLE other_keyword (id,name) -- all other keywords I have thought to create two keyword fields in the main table, one to hold the ids of the core_keywords (comma separated) and one to hold the ids of the other_keywords, also comma separated. What I cannot devise is an elegant method to SELECT based on the core_keywords to achieve the following sorts: 1- Find images with a single core_keyword id, that is, only images of a single person, no other core persons in the image 2- Find images with a specific set of core_keyword ids, such as 1 and 6 or 2 and 5 and 7, etc., with no other core persons in the image The idea is to create a document with lists of all images that are exclusive to single individuals, specific pairs, etc., so that family members can easily find themselves or groups, regardless of image catalog software they use on their particular systems, which may or may not be able to perform these types of sorts. I am not asking anyone to actually write the SELECT statements for me, but rather point me toward the operands that would achieve my goal. I have read through the documentation, and I cannot seem to generate the logic in my head to SELECT WHERE core_id is only 4. If anyone has an idea on a more efficient database design, or TABLE schema, please do not hesitate to proffer your thoughts. I am hoping to have it all figured out BEFORE I load up the tables with data. (I am actually still scanning images at this stage, but trying to prepare for the next phase.) Thank you very much for your time and consideration. Craig Smith cr...@macscripter.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema design and/or SELECT construction
Chris and Jay: Thank you both very much for your comments; that solves it. I am not a trained database designer, but I have resources on normalization; I simply neglected to consult them, thinking that this was a SELECT problem, not a design problem. Your input was just what I was hoping for. The concept of core and other keywords is a bit arbitrary. What is important (i.e. core) today might not be so tomorrow. Parsing comma separated lists in a single attribute is likely to be a bother. Generally, I would call this a bad idea. By most people's thinking it also breaks First Normal Form. Besides, you can't do database operations on comma separated lists. If you need a one-to-many (one pic to many keywords) then do it right and build an actual one-to-many relationship between two tables. Craig Smith cr...@macscripter.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Deleting duplicate records
By searching the archives of this list, I was able to come up with this syntax to identify duplicate records and place a single copy of each duplicate into another table: CREATE TABLE dup_killer (member_id INTEGER, date DATE); INSERT INTO dup_killer (member_id, date) SELECT * FROM talks GROUP BY member_id, date HAVING count(*)1; But, now that I have the copies in the dup_killer table, I have not been able to discover an efficient way to go back to the original table (talks) and delete them. My plan was to delete all the records from talks that match the criteria of the records now in dup_killer, and then INSERT the records from dup_killer back into talks before DROPPING dup_killer. At this point, I am stuck. Is there an efficient method to do this with SQLite, or should I just use a shell script? Craig Smith cr...@macscripter.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Deleting duplicate records
On Jan 6, 2009, at 6:14 PM, sqlite-users-requ...@sqlite.org wrote: delete from talks where exists (select 1 from talks t2 where talks.member_id = t2.member_id and talks.date = t2.date and talks.rowid t2.rowid); Igor, this worked fabulously, thank you very much. I also tried your other routine: delete from talks where rowid in (select t2.rowid from talks t2 where talks.member_id = t2.member_id and talks.date = t2.date order by t2.rowid offset 1); But I could not get anything to function, even when isolating the second SELECT, I continued to get error messages. I am working in the Mac OS X environment, with SQLite version 3.4.0, if that makes any difference. Alexey, thank you very much for your idea to put a CONSTRAINT on the table in the first place, that is the trick for a long term solution. Here is how I have put it together: CREATE TABLE talks (member_id INTEGER, date DATE, CONSTRAINT constraint_ignore_dup UNIQUE (member_id, date) ON CONFLICT IGNORE); I believe that I understand this statement, except for the term constraint_ignore_dup. Is that a variable name? Could it be pretty much anything I want, and if so, what is its purpose? Thank you to all the responders; you are the nicest internet contacts I have ever made. Craig Smith cr...@macscripter.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and Triggers
On Nov 17, 2008, at 12:45 AM, [EMAIL PROTECTED] wrote: I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id fields in my SQLite projects, yet I would like to try some triggers as well. Of course, every time I add a trigger that accesses a table with these types of id fields, all sorts of odd things happen. I read in the documentation about simply using PRIMARY KEY instead of INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability. To clarify, do you mean that an automatically generated autoincrement value is not available in BEFORE INSERT triggers? Presently, it is only available in AFTER INSERT triggers. Dan. Dan: Yes, I was using the AFTER INSERT on the trigger. Please see my followup posting in Issue 49, message number 1. I posted a clarification of the odd behavior I am observing. Does anyone have a solution that both provides AUTOINCREMENT (or a reasonable facsimile) to a PRIMARY KEY field and allows the usage of triggers on those tables? Thank you very much. Craig Smith Craig Smith [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INTEGER PRIMARY KEY and triggers
I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id fields in my SQLite projects, yet I would like to try some triggers as well. Of course, every time I add a trigger that accesses a table with these types of id fields, all sorts of odd things happen. I read in the documentation about simply using PRIMARY KEY instead of INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability. Does anyone have a solution that both provides AUTOINCREMENT (or a reasonable facsimile) to a PRIMARY KEY field and allows the usage of triggers on those tables? Thank you very much. Craig Smith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY and Triggers
On Nov 16, 2008, at 2:14 AM, [EMAIL PROTECTED] wrote: Do you mind specifying what are the odd things that happen? I have used TRIGGERs on tables with INTEGER PRIMARY KEY and have had no odd things happen at all. Right. I should have put more details up originally. Actually, the problems have all been related to the Objective-C / Cocoa interface that I have been working on. It is a small project with a small database consisting of 4 tables, one of which is a startup table with two integers. Those two integers correspond to primary keys of two other tables with the AUTOINCREMENT, precisely because I do not want recycling of id numbers. Originally, I had written Obj-C code to update the startup table integers whenever an UPDATE or INSERT was performed on the two tables with the AUTOINCREMENT id numbers, then I thought it would be much more efficient to have the triggers do the job, and result in less code in the actual front end. Well, all was working fine until I installed the triggers. My Obj-C code did indeed update the startup table, and then correctly read those integers to present the proper data to the GUI. After installing the triggers, and commenting out the relevant Obj-C code, it did not matter whether I did the INSERT or UPDATE via my GUI or via the command line; the startup table was consistently correct (i.e., the triggers were working), but my Obj-C code kept returning the integer 2, even when the startup table contained a 17. It was even more maddening when the same behavior was exhibited after dropping the triggers and un-commenting the Obj-C code. I ended up having to erase the database file completely and write a new one to get everything to function correctly as the original had. Yes, I am a beginner with both Obj-C and SQLite (6 months or so with both), so it is certainly possible that there is something involved with the timing of my commands within my Cocoa project that is doing this. The weird thing is that even when I shut down the sample app (running inside of XCode), and then re-launched it to read the tables fresh on startup, I would still consistently get a 2, regardless of what integer was actually residing in the startup table. In case you are wondering, I use 3 different Macs, depending on where I am during the month, and I want the startup table so that the database file can move from machine to machine, and my app will always have the same data and setup. The file will eventually reside on a flash drive, although for development purposes, right now I am keeping it on my desktop(s). Thank you again. Craig Smith [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
