[01:57] *** dashcloud has joined #archiveteam-ot [03:00] *** systwiALT has joined #archiveteam-ot [03:09] *** ephemer0l has joined #archiveteam-ot [03:54] ivan_: So I got postgres installed and running under El Capitan and I am slowly learning how things work (like I said, first time messing with SQL stuff :-L ) [03:57] My only question is, how do I organize my hierarchical data in this? Spreadsheet-style databases (there's probably a better term for them, idk) don't really [seem] to fit the bill [03:58] *** m007a83_ has joined #archiveteam-ot [03:59] *** m007a83_ has quit IRC (Client Quit) [04:00] *** m007a83 has quit IRC (Ping timeout: 252 seconds) [04:49] Additionally, is there really much of a problem using hyphens in column names? I tried researching but wasn't exactly certain. I can use ALTER TABLE youtube_db ADD "foo-bar" varchar; with no problems, but it sounds like it could be? [05:57] *** m007a83 has joined #archiveteam-ot [06:20] *** Raccoon has quit IRC (Ping timeout: 258 seconds) [06:23] *** Raccoon has joined #archiveteam-ot [06:29] *** Raccoon has quit IRC (Ping timeout: 360 seconds) [07:12] I gave you an outline of a schema earlier [07:13] Yes, they're "spreadsheet style" [07:16] You can use more than one table to express different entities [07:16] You have channels, videos, and files [07:18] You can look up videos for a channel, then look up files for a video, and this is all fast with indexes [07:19] The primary key for a table is important and should be based on what makes the entity unique for that table [07:19] Re: dashes I would just use underscores [08:07] *** Dragnog2 has joined #archiveteam-ot [08:21] I'm sorry I wasn't online when you explained, my connection dropped and all previous messages cleared. [08:27] systwiALT: [08:27] I'd just store that in a simple relational DB with three tables (or more, depending on what else you want to store): channels, videos, and files. [08:27] to flesh that out: channels (channel_id, username, display_name) PK channel_id; videos (video_id, channel_id, video_title, ... .info.json stuff) PK video_id; files (video_id, file) PK (video_id, file) [08:27] add a channel_id index on videos [08:27] you can query to get videos in a channel, files for a video, etc [08:27] PK is primary key [08:29] when you have a primary key on something, it is indexed and you can do fast queries on it [08:30] you can also add indexes that aren't the primary key [08:30] also, when you have a primary key or index on (a, b, c) you can do fast lookups on (a, b), and (a) as well because of how the index is structured [08:39] *** deevious has quit IRC (Remote host closed the connection) [09:41] *** dhyan_nat has quit IRC (Read error: Operation timed out) [09:43] *** bluefoo has quit IRC (Ping timeout: 615 seconds) [09:58] *** dhyan_nat has joined #archiveteam-ot [10:15] *** dhyan_nat has quit IRC (Read error: Operation timed out) [10:56] systwiALT: Also, logs of (some) AT IRC channels: http://archive.fart.website/bin/irclogger_logs [11:06] *** Dragnog2 has quit IRC (Quit: Connection closed for inactivity) [11:41] *** dhyan_nat has joined #archiveteam-ot [12:19] *** BlueMax has quit IRC (Read error: Connection reset by peer) [13:44] *** dhyan_nat has quit IRC (Read error: Operation timed out) [16:03] systwiALT: another thing to think about is whether you want multiple variations of a video and how you're going to store that on disk and in the database [16:04] you would presumably want a way to have multiple rows in videos would mean something like a (video_id text, variant smallint) PK [16:14] *** ShellyRol has quit IRC (Ping timeout: 745 seconds) [16:38] + retrieval date most likely [16:38] Although the video files themselves can't get changed (I think?), the remaining info can I believe. [16:38] You'd probably want to keep track of the timeline. [16:55] *** systwiALT has quit IRC (Read error: Operation timed out) [16:59] *** ShellyRol has joined #archiveteam-ot [17:28] JAA: videos can be trimmed by anyone, modified in other ways with some kind of youtube partner status [17:28] yeah (video_id, retrieval_date) is a better key [17:40] Huh, interesting. [17:41] *** DogsRNice has joined #archiveteam-ot [17:44] and sometimes youtube reencodes videos with a new broken encoder and either the video or audio don't play properly any more :-) :-) [18:57] *** killsushi has joined #archiveteam-ot [20:24] *** DogsRNice has quit IRC (Read error: Connection reset by peer) [20:57] *** coderobe has quit IRC (Remote host closed the connection) [21:37] *** coderobe has joined #archiveteam-ot [22:28] *** Mateon1 has quit IRC (Read error: Operation timed out) [22:28] *** Mateon1 has joined #archiveteam-ot [22:42] *** Mateon1 has quit IRC (Ping timeout: 745 seconds) [22:43] *** Mateon1 has joined #archiveteam-ot [23:06] *** godane has quit IRC (Ping timeout: 258 seconds) [23:10] *** bluefoo has joined #archiveteam-ot [23:13] *** t3 has joined #archiveteam-ot [23:14] *** t3 has quit IRC (Client Quit) [23:29] *** BlueMax has joined #archiveteam-ot [23:29] *** godane has joined #archiveteam-ot [23:29] *** t3 has joined #archiveteam-ot