#archiveteam-ot 2019-08-27,Tue

↑back Search

Time Nickname Message
01:57 🔗 dashcloud has joined #archiveteam-ot
03:00 🔗 systwiALT has joined #archiveteam-ot
03:09 🔗 ephemer0l has joined #archiveteam-ot
03:54 🔗 systwiALT 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 🔗 systwiALT 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 🔗 systwiALT 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 🔗 ivan_ I gave you an outline of a schema earlier
07:13 🔗 ivan_ Yes, they're "spreadsheet style"
07:16 🔗 ivan_ You can use more than one table to express different entities
07:16 🔗 ivan_ You have channels, videos, and files
07:18 🔗 ivan_ You can look up videos for a channel, then look up files for a video, and this is all fast with indexes
07:19 🔗 ivan_ The primary key for a table is important and should be based on what makes the entity unique for that table
07:19 🔗 ivan_ Re: dashes I would just use underscores
08:07 🔗 Dragnog2 has joined #archiveteam-ot
08:21 🔗 systwiALT I'm sorry I wasn't online when you explained, my connection dropped and all previous messages cleared.
08:27 🔗 ivan_ systwiALT:
08:27 🔗 ivan_ <JAA> 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 🔗 ivan_ <ivan_> 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 🔗 ivan_ <ivan_> add a channel_id index on videos
08:27 🔗 ivan_ <ivan_> you can query to get videos in a channel, files for a video, etc
08:27 🔗 ivan_ <ivan_> PK is primary key
08:29 🔗 ivan_ when you have a primary key on something, it is indexed and you can do fast queries on it
08:30 🔗 ivan_ you can also add indexes that aren't the primary key
08:30 🔗 ivan_ 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 🔗 JAA 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 🔗 ivan_ 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 🔗 ivan_ 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 🔗 JAA + retrieval date most likely
16:38 🔗 JAA Although the video files themselves can't get changed (I think?), the remaining info can I believe.
16:38 🔗 JAA 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 🔗 ivan_ JAA: videos can be trimmed by anyone, modified in other ways with some kind of youtube partner status
17:28 🔗 ivan_ yeah (video_id, retrieval_date) is a better key
17:40 🔗 JAA Huh, interesting.
17:41 🔗 DogsRNice has joined #archiveteam-ot
17:44 🔗 ivan_ 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

irclogger-viewer