![]() > /*,datetime(m.date + 978307200, 'unixepoch', 'localtime') as TextDate - date stored as ticks since */ > else coalesce(h2.id, h.id) end as ToPhoneNumber > ,case when m.is_from_me = 0 then m.account > ,case when m.is_from_me = 1 then m.account > ,coalesce(m.cache_roomnames, h.id) ThreadId Or you can do it in Terminal with a few incredibly detailed and finicky / unforgiving-of-even-a-single-typo commands: sqlite3 chat.db You can use different applications - I have used dbHarbour (free, I think). I can then see all my messages (I have squashed up some of the columns to partially hide my content): (h2.service is null or m.service = h2.service) ![]() try to eliminate duplicates due to non-unique message.cache_roomnames/chat.room_name Left join handle as h2 on ch.handle_id = h2.rowid Left join chat_handle_join as ch on c.rowid = ch.chat_id Left join chat as c on m.cache_roomnames = c.room_name /* note: chat.room_name is not unique, this may cause one-to-many join */ Left join handle as h on m.handle_id = h.rowid *,datetime(m.date + 978307200, 'unixepoch', 'localtime') as TextDate - date stored as ticks since */ ,case when m.is_from_me = 0 then m.accountĮlse coalesce(h2.id, h.id) end as ToPhoneNumber ,case when m.is_from_me = 1 then m.account ,coalesce(m.cache_roomnames, h.id) ThreadId Make a copy of the database - I copied mine to the Desktop.I have "cheated" and found one here SQL query for chat. An application - I use "SQLPro for SQLite" from the Apple App Store.To make sense of a SQLite database, you need: The power (and complexity) of relational databases comes with connecting data in two or more tables via relationships. A "relational database" organises data into one or more tables (or "relations") of rows and columns. You have many choices - search in the Mac App Store for SQLite (or similar) or use the Terminal command sqlite3.īut a SQLite database is intimidating to the first time user, even though it is amongst the simplest of relational databases. You should post these to /r/learnsql instead.The file you have found is a SQLite database and you need an application which can read SQLite databases. ![]() Note /r/SQL does not allow links to basic tutorials to be posted here. Please view the Wiki for online resources. Learning SQLĪ common question is how to learn SQL. SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3įor those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this: This will greatly increase your chances of receiving the help you desire. If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. ![]() We will gladly help where we can as long as you post the work you have already done or show that you have attempted to figure it out on your own. If you are a student or just looking for help on your code please do not just post your questions and expect the community to do all the work for you. While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform. When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so: The goal of /r/SQL is to provide a place for interesting and informative SQL content and discussions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |