|
|
<!-- Automatically generated by emacsconf-publish-after-page -->
<div class="transcript transcript-mainVideo"><a name="pgmacs-mainVideo-transcript"></a><h1>Transcript</h1>
<div class="transcript-heading">[[!template new="1" text="""Introduction""" start="00:00:01.260" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""Hi, this is a short presentation about PGmacs,""" start="00:00:01.260" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""which is a browsing and editing interface for""" start="00:00:04.180" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Postgres databases.""" start="00:00:07.100" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""My name is Eric Marsden, and I'm the""" start="00:00:08.920" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""developer of this Emacs Lisp library.""" start="00:00:11.320" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""PGmacs was inspired by sqlite-mode, which is a""" start="00:00:14.800" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""great feature available from Emacs 29 onwards.""" start="00:00:17.420" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It allows you to view and modify SQLite""" start="00:00:21.060" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""databases you might have lying around.""" start="00:00:24.560" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It's a really great feature.""" start="00:00:26.760" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It would be even better, however, if it""" start="00:00:28.140" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""were able to use a real database.""" start="00:00:30.840" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And I happen to know that was possible""" start="00:00:34.540" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""because a few years ago, I wrote an""" start="00:00:36.200" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Emacs Lisp library, pg.el, which implements the wire""" start="00:00:38.120" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""protocol used for communication over the network between""" start="00:00:42.920" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a Postgres client and the Postgres backend, the""" start="00:00:46.280" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Postgres server.""" start="00:00:49.600" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""I've included here an example of what it""" start="00:00:51.580" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""looks like to use this library.""" start="00:00:53.560" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""You connect to the database by specifying the""" start="00:00:54.820" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""database name, your username, your password, potentially the""" start="00:00:58.240" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""hostname that you're connecting to.""" start="00:01:01.520" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Once you're connected, you can then execute SQL""" start="00:01:03.800" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""statements, for example, to create a new table,""" start="00:01:06.680" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""to insert values into that table, and to""" start="00:01:10.180" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""count the number of rows in a table.""" start="00:01:13.860" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And pg.el does automatic type conversion to""" start="00:01:17.100" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""and from Emacs Lisp types and Postgres types for""" start="00:01:20.300" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""you.""" start="00:01:24.180" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Demo""" start="00:01:26.710" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""Probably the best is to jump straight into""" start="00:01:26.710" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a demo of PGmacs.""" start="00:01:29.170" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""When we connect to a database, we see""" start="00:01:31.210" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""up the top of the buffer, some information""" start="00:01:34.130" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""about the backend that we're connected to, some""" start="00:01:36.330" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""version information and the total database size on""" start="00:01:39.070" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""disk.""" start="00:01:42.010" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""There are some shortcuts to some commands we""" start="00:01:43.090" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""can run on the database, and there's a""" start="00:01:45.170" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""list of tables that we have access to,""" start="00:01:47.370" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""with again, metainformation about their size on""" start="00:01:50.230" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""disk and their owner.""" start="00:01:52.650" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Now, we can enter a table by pressing""" start="00:01:53.650" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""return.""" start="00:01:56.490" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Here we again see some metainformation about""" start="00:01:58.510" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the table, such as the list of columns,""" start="00:02:01.230" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the SQL type of each column, any defaults""" start="00:02:05.510" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""that might be present and any SQL constraints.""" start="00:02:08.490" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We see any indexes that might be present""" start="00:02:11.970" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""on this table, and then we see the""" start="00:02:14.370" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""list of rows of data in the table.""" start="00:02:16.790" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""If we see any information which is incorrect""" start="00:02:19.830" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""or incomplete, we can easily fix it by""" start="00:02:22.870" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""pressing enter again, and this updates the information""" start="00:02:26.710" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""present in the database.""" start="00:02:36.300" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""PGmacs shows us this in the minibuffer""" start="00:02:38.200" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""it has updated one row.""" start="00:02:39.840" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can look at help for the key""" start="00:02:43.540" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""bindings that are present in this type of""" start="00:02:45.440" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""buffer.""" start="00:02:47.440" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""If we scroll down, we can see that""" start="00:02:48.120" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""we can upcase the value of a cell""" start="00:02:49.580" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""with M-u.""" start="00:02:51.580" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Let's try that out.""" start="00:02:52.920" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Okay, here we have updated the value of""" start="00:02:56.430" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the cell in the database.""" start="00:02:58.950" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can now lowercase it, and we can""" start="00:03:00.830" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""put it back to the initial""" start="00:03:03.330" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""title case of the value.""" start="00:03:06.510" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can also run a shell command on""" start="00:03:09.010" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a cell value, to count the number of""" start="00:03:11.550" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""characters, for example.""" start="00:03:14.570" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can run a shell command with a""" start="00:03:17.570" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""prefix argument, which updates the value in the""" start="00:03:20.390" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""database with the output from the shell command.""" start="00:03:22.990" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""So that has updated the database.""" start="00:03:27.470" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""If we do that again, that will reverse""" start="00:03:30.270" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the value. If we come back""" start="00:03:32.790" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""to the list of tables, we see""" start="00:03:39.750" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""that there is a table which is called""" start="00:03:41.590" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""deleteme.""" start="00:03:43.010" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can try renaming this table with R,""" start="00:03:43.890" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""we can look at what is in this""" start="00:03:50.380" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""table.""" start="00:03:52.880" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Deletion""" start="00:03:53.960" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""Okay, there is only one row of information""" start="00:03:53.960" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""that doesn't seem very important, so let's delete""" start="00:03:56.600" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""that.""" start="00:03:59.280" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Let's now delete the table with DEL.""" start="00:04:02.160" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""PGmacs asks for confirmation, and we've deleted the""" start="00:04:07.980" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""table.""" start="00:04:11.000" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Now when we have a big table with""" start="00:04:13.390" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a lot of data, PGmacs is going to""" start="00:04:15.470" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""show us the results paginated, so we can""" start="00:04:18.250" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""go chunk by chunk through the table.""" start="00:04:21.150" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can implement a where filter on the""" start="00:04:24.370" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""rows to only show the rows that match""" start="00:04:28.070" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a certain SQL clause.""" start="00:04:30.570" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""So for example, here we have some temperature""" start="00:04:33.110" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""measurements.""" start="00:04:35.450" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We want to focus on measurements that are""" start="00:04:36.490" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""more than 40 degrees Celsius, for example.""" start="00:04:38.690" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Okay, we have now filtered on a certain""" start="00:04:45.980" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""number of rows.""" start="00:04:48.120" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""The filter is shown to us just up""" start="00:04:49.540" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""here, and we see here that we have""" start="00:04:51.240" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""some values that look anomalous.""" start="00:04:53.540" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""There's one, 140, and one is 61.""" start="00:04:56.500" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We probably want to delete them, they represent""" start="00:04:59.840" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""errors.""" start="00:05:02.980" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can mark them for deletion with d,""" start="00:05:04.080" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""and then really delete them, expunge them, as""" start="00:05:06.560" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""in Dired, with x.""" start="00:05:09.680" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Export""" start="00:05:12.880" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""We can export this table in CSV format,""" start="00:05:12.880" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""thanks to this little button up here.""" start="00:05:16.480" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Here we have our table conveniently formatted as""" start="00:05:19.860" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""CSV.""" start="00:05:22.760" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can also export a particular row to""" start="00:05:23.400" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""JSON by typing j, let's look at""" start="00:05:28.480" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""what that looks like.""" start="00:05:32.080" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Okay, here's the JSON for one of the""" start="00:05:37.810" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""rows in the table.""" start="00:05:40.230" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""HStore""" start="00:05:42.250" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""Now let's look at a table that contains""" start="00:05:42.250" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a column of type HStore.""" start="00:05:44.270" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""This is a Postgres-specific key-value map.""" start="00:05:46.430" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Then the attributes column is of type""" start="00:05:49.110" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""JSON, which can be stored natively in Postgres.""" start="00:05:51.170" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It's possible to edit these values using a""" start="00:05:54.750" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""widget-based interface by typing w, and here""" start="00:05:56.910" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""we have a convenient interface for modifying these""" start="00:06:00.490" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""types of values.""" start="00:06:03.630" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Likewise, for the JSON type parameter, we can""" start="00:06:05.410" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""update using a widget-based interface.""" start="00:06:08.390" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Connecting to a different database""" start="00:06:11.510" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""Let's connect to a different database.""" start="00:06:11.510" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We do that with a M-x pgmacs, and""" start="00:06:14.490" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""then enter our username and password.""" start="00:06:18.910" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Here we have the list of tables present""" start="00:06:27.310" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""in this database.""" start="00:06:29.390" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""SchemaSpy""" start="00:06:31.110" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""PGmacs has some support for running the SchemaSpy""" start="00:06:31.110" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""utility, which generates a graphical representation of the""" start="00:06:33.690" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""relationships between tables in this database, which can""" start="00:06:37.950" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""be useful when you're discovering the database.""" start="00:06:41.710" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Here is the SVG that's output by this""" start="00:06:45.170" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""utility.""" start="00:06:47.870" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""This is a test database containing information about""" start="00:06:50.860" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a digital media store.""" start="00:06:53.840" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It has a table with information about the""" start="00:06:55.880" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""tracks that are available.""" start="00:06:58.160" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""If we look at this table, we""" start="00:07:00.960" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""see that we have the name of the""" start="00:07:03.480" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""track, a reference to the album, album ID.""" start="00:07:04.780" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""This is a reference to a foreign key,""" start="00:07:09.240" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a key in the table which is called""" start="00:07:11.660" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""album.""" start="00:07:13.520" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Now we can follow this foreign key reference""" start="00:07:14.820" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""simply by pressing Enter, and here we find""" start="00:07:16.840" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""it's the album called For Those About To""" start="00:07:20.580" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Rock.""" start="00:07:23.100" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And in the same way, here we have""" start="00:07:24.260" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a foreign key reference to the relevant artist""" start="00:07:26.420" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""which we can follow.""" start="00:07:28.780" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""That's, of course, AC/DC.""" start="00:07:30.080" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Convenience queries""" start="00:07:32.620" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""And finally, PGmacs has some convenience queries that""" start="00:07:32.620" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""allows us to list the procedures which are""" start="00:07:35.720" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""defined in this database.""" start="00:07:38.280" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Here we have the built-in procedures, mostly""" start="00:07:40.260" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""used by the PostGIS extension.""" start="00:07:42.820" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can also display some more information about""" start="00:07:48.430" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""our backend.""" start="00:07:52.110" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Here we have the list of extensions which""" start="00:07:53.090" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""are available.""" start="00:07:56.450" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can load one of these extensions if""" start="00:07:57.470" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""we have the rights to do that.""" start="00:08:00.250" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Here we've loaded the relevant extension.""" start="00:08:02.350" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can show some information about the Postgres""" start="00:08:06.210" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""settings also, and update them if we have""" start="00:08:08.850" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the rights to do that.""" start="00:08:16.650" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Emacs as an application development platform""" start="00:08:18.850" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""Now I'm preaching to the converted, but Emacs""" start="00:08:18.850" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""is a really great application development platform.""" start="00:08:21.650" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""PGmacs is currently around 3000 lines of code.""" start="00:08:25.510" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""A first point of comparison, PGCLI, which is""" start="00:08:29.630" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a text user interface for accessing Postgres implemented""" start="00:08:32.510" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""in Python, is 17,000 lines of code.""" start="00:08:36.409" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""PGAdmin4, which is a well-known Python GUI""" start="00:08:40.390" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""for administrating Postgres databases, is almost half a""" start="00:08:43.190" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""million lines of code.""" start="00:08:47.010" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And DBeaver, implemented in Java, is almost a""" start="00:08:48.410" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""million lines of code.""" start="00:08:51.450" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Developing things on Emacs, you get for""" start="00:08:53.130" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""free portability between different platforms.""" start="00:08:56.030" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""As I'm showing you, it works great on""" start="00:08:58.470" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Linux, which is where I developed this library.""" start="00:09:00.130" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It also works perfectly in the terminal, except""" start="00:09:03.070" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""for some of the functionality I showed, which""" start="00:09:06.530" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""requires SVG support.""" start="00:09:08.530" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It also works well, if you're into that""" start="00:09:10.970" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""kind of thing, on MacOS and on Windows.""" start="00:09:12.630" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And it even works perfectly on Haiku, which""" start="00:09:15.830" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""is a free BeOS clone.""" start="00:09:19.130" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Emacs is actually really pretty on this operating""" start="00:09:21.300" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""system, so congratulations to the people who did""" start="00:09:23.870" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the port there.""" start="00:09:26.590" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Now, of course, the main advantage of building""" start="00:09:27.530" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""on the Emacs development platform, is that the""" start="00:09:29.350" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""application is easy for the user to extend.""" start="00:09:32.430" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Extending pgmacs""" start="00:09:36.250" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""To illustrate that, previously we were looking at""" start="00:09:36.250" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a table of temperature measurements.""" start="00:09:38.690" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Imagine we want to highlight rows in this""" start="00:09:42.070" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""table, which look anomalous, where the value looks""" start="00:09:44.510" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a bit extreme.""" start="00:09:48.430" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""That's quite easy to do with a bit""" start="00:09:50.090" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""of Emacs Lisp.""" start="00:09:52.150" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We define a function, which, if the cell""" start="00:09:54.610" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""value is bigger than 40, is going to""" start="00:09:58.410" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""display it in a face which has a""" start="00:10:01.570" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""dark red foreground.""" start="00:10:03.850" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can now register this display function for""" start="00:10:05.950" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the measurement column in the temperatures table.""" start="00:10:08.890" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And if we reopen the table now, we""" start="00:10:12.190" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""see that the anomalous measurements are indeed highlighted""" start="00:10:16.490" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""in red.""" start="00:10:19.950" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Another example, imagine we have a table which""" start="00:10:24.100" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""contains image data.""" start="00:10:27.320" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""There's a column which is of BYTEA type,""" start="00:10:29.200" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""which contains images in binary form.""" start="00:10:31.740" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can display these inline as follows.""" start="00:10:35.340" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We create an inline image display function, using""" start="00:10:40.340" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Emacs' image support.""" start="00:10:44.900" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And then we'd register this function to display""" start="00:10:47.080" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the image column in the inline image table.""" start="00:10:49.540" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""If we now reopen the inline image table,""" start="00:10:52.780" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""we see the images are displayed inline.""" start="00:10:56.060" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And as a final example of customisation, here's""" start="00:10:59.660" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""how to bind a key to a specific""" start="00:11:03.160" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""function, in the row-list buffer.""" start="00:11:05.440" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Let's define a function that does a DuckDuckGo""" start="00:11:08.800" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""lookup for a particular value in""" start="00:11:12.080" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""the Emacs web browser.""" start="00:11:15.500" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""We can define a function which does a""" start="00:11:18.080" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""funcall on the cell value for this""" start="00:11:22.140" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""DuckDuckGo lookup function.""" start="00:11:24.400" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And finally we can define a key, the""" start="00:11:27.100" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""capital D key, in the row-list map,""" start="00:11:30.060" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""which calls this function that does a""" start="00:11:33.540" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""DuckDuckGo lookup.""" start="00:11:35.480" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And now if I'm browsing information in a""" start="00:11:37.240" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""table that I want to do a web""" start="00:11:39.600" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""search on, I can use my D shortcut""" start="00:11:41.580" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""and see some web results concerning the cell.""" start="00:11:45.380" video="mainVideo-pgmacs" id="subtitle"]]
<div class="transcript-heading">[[!template new="1" text="""Conclusion""" start="00:11:49.400" video="mainVideo-pgmacs" id="subtitle"]]</div>[[!template text="""So to conclude, the source and installation instructions""" start="00:11:49.400" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""for PGmacs are available on GitHub.""" start="00:11:53.880" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It requires Emacs 29.""" start="00:11:56.680" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""There is a prebuilt container image which you""" start="00:11:59.260" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""can use for testing.""" start="00:12:01.620" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It's a Docker image.""" start="00:12:02.980" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""The Docker image only works in terminal mode.""" start="00:12:04.580" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And of course that's a recommended way of""" start="00:12:06.160" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""testing Emacs Lisp code that you load""" start="00:12:08.380" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""into your Emacs before having read it.""" start="00:12:11.020" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It works with any recent version of Postgres""" start="00:12:13.360" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""over the past 10 years, including the latest""" start="00:12:15.720" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""release, Postgres 17.""" start="00:12:18.580" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It does work with a certain number of""" start="00:12:20.440" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""databases that are compatible with Postgres, such as""" start="00:12:22.700" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""ParadeDB, TimescaleDB and IvorySQL.""" start="00:12:26.440" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""However, it doesn't work with all databases that""" start="00:12:30.940" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""claim to be Postgres compatible.""" start="00:12:33.560" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""It doesn't work, for example, with CrateDB or""" start="00:12:35.340" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""with CockroachDB or some others that you see""" start="00:12:37.840" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""listed here.""" start="00:12:39.960" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""The reason for that is that these databases""" start="00:12:40.760" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""don't implement the system tables that PGmacs queries""" start="00:12:42.660" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""to obtain information about the columns present in""" start="00:12:46.440" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""a table, the size on disk, the ownership""" start="00:12:49.600" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""of tables, etc.""" start="00:12:52.220" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""PGmacs supports TLS encrypted connections to the database,""" start="00:12:54.600" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""as well as local Unix socket connections.""" start="00:12:58.380" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""And in terms of stability, I would classify""" start="00:13:02.040" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""it as beta status.""" start="00:13:04.600" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""I do use it myself in production on""" start="00:13:06.680" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""some not very important data.""" start="00:13:09.200" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Really, it works quite well.""" start="00:13:11.560" video="mainVideo-pgmacs" id="subtitle"]]
[[!template text="""Thanks for your attention.""" start="00:13:14.300" video="mainVideo-pgmacs" id="subtitle"]]
</div>
Captioner: eric
Questions or comments? Please e-mail [eric.marsden@risk-engineering.org](mailto:eric.marsden@risk-engineering.org?subject=Comment%20for%20EmacsConf%202023%20pgmacs%3A%20PGmacs%3A%20browsing%20and%20editing%20PostgreSQL%20databases%20from%20Emacs)
<!-- End of emacsconf-publish-after-page -->
|
