API tools faq
paste
Guest User

Untitled

a guest
Nov 12th, 2018
113
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.80 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/env perl
  2. ### USAGE EXAMPLES
  3. # $0 json
  4. # $0 md
  5. # $0 html
  6. ###
  7. use v5.10; use warnings; use strict; use autodie qw(:all);
  8. use utf8; no warnings 'utf8';
  9. use Encode qw(decode_utf8);
  10. use List::Util qw(first);
  11. use IPC::Open2;
  12. use JSON;
  13.  
  14. # db connection parameters
  15. my $host = '127.0.0.1';
  16. my $port = 5432;
  17. my $dbname = 'carma';
  18. my $user = 'carma';
  19.  
  20. die 'Unexpected arguments: ', join(' ', @ARGV)
  21. if scalar(@ARGV) != 1 || !(first {$ARGV[0] eq $_} qw(json md html));
  22.  
  23. # opening bi-directional pipe
  24. my @args = ('psql', '-h', $host, '-p', $port, '-d', $dbname, '-U', $user);
  25. open2 \*PSQL_OUT, \*PSQL_IN, @args;
  26. END {close PSQL_OUT; close PSQL_IN} # cleanup
  27.  
  28. my $sep = ('~' x 50).'SEPARATOR'.('~' x 50);
  29. sub separate {say PSQL_IN "SELECT '$sep';"}
  30.  
  31. say PSQL_IN '\d+'; # requesting list of tables
  32. separate; # marking end of output
  33. my @tables = (); # list of names (strings)
  34. my @views = (); # list of names (strings)
  35. {
  36. local $_;
  37. my $is_started = 0;
  38. while (chomp($_ = decode_utf8 <PSQL_OUT>)) {
  39. last if /$sep/; # end of output of a command
  40.  
  41. unless ($is_started) {
  42. # detecting table header and table body separator
  43. $is_started = 1 if /^[-+]+$/ && /\+/;
  44. next;
  45. }
  46.  
  47. my @columns = split /\|/;
  48. map s/(^\s+|\s+$)//g, @columns;
  49. next if scalar(@columns) < 2;
  50.  
  51. if ($columns[2] eq 'table') {
  52. push @tables, $columns[1];
  53. } elsif ($columns[2] eq 'view') {
  54. push @views, $columns[1];
  55. }
  56. }
  57. }
  58.  
  59. # put labeled debugging separator to log (STDERR)
  60. sub stitle {say STDERR (('-' x 20).' '.shift.' '.('-' x 20))}
  61.  
  62. # info just for debug
  63. stitle 'Tables';
  64. say STDERR for @tables;
  65. stitle 'Views';
  66. say STDERR for @views;
  67.  
  68. my %tables_data = ();
  69. my %views_data = ();
  70.  
  71. foreach my $table (@tables) {
  72. say PSQL_IN '\d+ "', $table, '"';
  73. separate; # marking end of output
  74. my $is_fields_started = 0;
  75. my $is_meta_started = 0;
  76. my $is_foreign_keys_meta = 0;
  77. my $is_done = 0;
  78.  
  79. my @fields = ();
  80. my %foreign_refs = ();
  81. my %table = (fields => \@fields, foreign_refs => \%foreign_refs);
  82.  
  83. while (chomp($_ = decode_utf8 <PSQL_OUT>)) {
  84. if (/$sep/) {$is_done = 1; last} # end of output of a command
  85.  
  86. unless ($is_fields_started) {
  87. # detecting table header and table body separator
  88. $is_fields_started = 1 if /^[-+]+$/ && /\+/;
  89. next
  90. }
  91.  
  92. my @columns = split /\|/;
  93. map s/(^\s+|\s+$)//g, @columns;
  94.  
  95. if (scalar(@columns) < 4) {
  96. if (/^[^|]+:$/) {
  97. $is_meta_started = 1;
  98. $is_foreign_keys_meta = 1 if $_ eq 'Foreign-key constraints:';
  99. last # in this case we're done with fields
  100. } else {next}
  101. }
  102.  
  103. my %field = (
  104. name => $columns[0],
  105. type => $columns[1],
  106. optional => $columns[3],
  107. );
  108.  
  109. push @fields, \%field;
  110. }
  111.  
  112. if (!$is_done && $is_meta_started) {
  113. while (chomp($_ = decode_utf8 <PSQL_OUT>)) {
  114. if (/$sep/) {$is_done = 1; last} # end of output of a command
  115.  
  116. if (/^[^|]+:$/) {
  117. $is_foreign_keys_meta = $_ eq 'Foreign-key constraints:'
  118. } elsif (/^\s{4}\S/ && $is_foreign_keys_meta) {
  119. /\s+FOREIGN KEY\s+\(([^)]+)\)\s+REFERENCES\s+("[^"]+"|[^(]+)\(([^)]+)\)/;
  120. my $field = $1;
  121. my $ref_table = $2;
  122. my $ref_field = $3;
  123. $ref_table =~ s/^"(.*)"$/$1/;
  124.  
  125. my %ref = (
  126. table => $ref_table,
  127. field => $ref_field,
  128. );
  129.  
  130. $foreign_refs{$field} = \%ref;
  131. }
  132. }
  133. }
  134.  
  135. $tables_data{$table} = \%table
  136. }
  137.  
  138. foreach my $view (@views) {
  139. my @fields = ();
  140.  
  141. say PSQL_IN '\d+ "', $view, '"';
  142. separate; # marking end of output
  143. my $is_fields_started = 0;
  144.  
  145. while (chomp($_ = decode_utf8 <PSQL_OUT>)) {
  146. last if /$sep/; # end of output of a command
  147.  
  148. unless ($is_fields_started) {
  149. # detecting table header and table body separator
  150. $is_fields_started = 1 if /^[-+]+$/ && /\+/;
  151. next
  152. }
  153.  
  154. my @columns = split /\|/;
  155. map s/(^\s+|\s+$)//g, @columns;
  156.  
  157. next if scalar(@columns) < 4;
  158.  
  159. my %field = (
  160. name => $columns[0],
  161. type => $columns[1],
  162. );
  163.  
  164. push @fields, \%field;
  165. }
  166.  
  167. $views_data{$view} = \@fields
  168. }
  169.  
  170. if ($ARGV[0] eq 'json') {
  171.  
  172. my %all = (tables => \%tables_data, views => \%views_data);
  173. say encode_json \%all
  174.  
  175. } elsif ($ARGV[0] eq 'md') {
  176.  
  177. say "# Tables";
  178. say '';
  179. while (my ($table_name, $table_data) = each %tables_data) {
  180. my $hash = $table_name; $hash =~ s/ /_/g;
  181. my @fields = @{$table_data->{fields}};
  182. my %foreign_refs = %{$table_data->{foreign_refs}};
  183.  
  184. $table_name = "`$table_name`" if $table_name ne '';
  185. say "- <a name='$hash'>$table_name</a>";
  186. say " - Fields:";
  187. foreach my $field (@fields) {
  188. my ($name, $type, $optional) =
  189. ($field->{name}, $field->{type}, $field->{optional});
  190. $name = "`$name`" if $name ne '';
  191. $type = "`$type`" if $type ne '';
  192. $optional = "`$optional`" if $optional ne '';
  193. say " - $name";
  194. say " - Type: $type";
  195. say " - Nullable: $optional";
  196. }
  197.  
  198. if (scalar(keys(%foreign_refs)) > 0) {
  199. say " - Foreign keys of the table";
  200. while (my ($k, $v) = each %foreign_refs) {
  201. my ($table, $field) = ($v->{table}, $v->{field});
  202. my $hash = $table; $hash =~ s/ /_/g;
  203. $k = "`$k`" if $k ne '';
  204. $table = "`$table`" if $table ne '';
  205. $field = "`$field`" if $field ne '';
  206. say " - $k";
  207. say " - Foreign table reference: <a href='#$hash'>$table</a>";
  208. say " - Foreign field reference: $field";
  209. }
  210. }
  211. }
  212.  
  213. say '';
  214. say "# Views";
  215. say '';
  216. while (my ($view_name, $view_fields) = each %views_data) {
  217. my $hash = $view_name; $hash =~ s/ /_/g;
  218. my @fields = @{$view_fields};
  219. $view_name = "`$view_name`" if $view_name ne '';
  220. say "- <a name='$hash'>$view_name</a>";
  221. foreach my $field (@fields) {
  222. my ($name, $type) = ($field->{name}, $field->{type});
  223. $name = "`$name`" if $name ne '';
  224. $type = "`$type`" if $type ne '';
  225. say " - $name";
  226. say " - Type: $type";
  227. }
  228. }
  229.  
  230. } elsif ($ARGV[0] eq 'html') {
  231. say "<!doctype html>";
  232. say "<html>";
  233. say "<head>";
  234. say "<meta charset='utf-8'>";
  235. say "<style>table th, table td { border: 1px solid gray; padding: 5px; }</style>";
  236. say "</head>";
  237. say "<body>";
  238.  
  239. say "<h1>Tables</h1>";
  240. while (my ($table_name, $table_data) = each %tables_data) {
  241. my $hash = $table_name; $hash =~ s/ /_/g;
  242. my @fields = @{$table_data->{fields}};
  243. my %foreign_refs = %{$table_data->{foreign_refs}};
  244.  
  245. say "<h2><a name='$hash'>$table_name</a></h2>";
  246. say "<table>";
  247. say "<thead><tr>";
  248. say "<th>Field name</th>";
  249. say "<th>Type</th>";
  250. say "<th>Nullable</th>";
  251. say "</tr></thead>";
  252. say "<tbody>";
  253. foreach my $field (@fields) {
  254. my ($name, $type, $optional) =
  255. ($field->{name}, $field->{type}, $field->{optional});
  256. say "<tr>";
  257. say "<td>$name</td>";
  258. say "<td>$type</td>";
  259. say "<td>$optional</td>";
  260. say "</tr>";
  261. }
  262. say "</tbody>";
  263. say "</table>";
  264.  
  265. if (scalar(keys(%foreign_refs)) > 0) {
  266. say "<h3>Foreign keys of the table</h3>";
  267. say "<table>";
  268. say "<thead><tr>";
  269. say "<th>Field name</th>";
  270. say "<th>Foreign table reference</th>";
  271. say "<th>Foreign field reference</th>";
  272. say "</tr></thead>";
  273. say "<tbody>";
  274. while (my ($k, $v) = each %foreign_refs) {
  275. my ($table, $field) = ($v->{table}, $v->{field});
  276. my $hash = $table; $hash =~ s/ /_/g;
  277. say "<tr>";
  278. say "<td>$k</td>";
  279. say "<td><a href='#$hash'>$table</a></td>";
  280. say "<td>$field</td>";
  281. say "</tr>";
  282. }
  283. say "</tbody>";
  284. say "</table>";
  285. }
  286. }
  287.  
  288. say "<hr>";
  289. say "<h1>Views</h1>";
  290. while (my ($view_name, $view_fields) = each %views_data) {
  291. my $hash = $view_name; $hash =~ s/ /_/g;
  292. my @fields = @{$view_fields};
  293.  
  294. say "<h2><a name='$hash'>$view_name</a></h2>";
  295. say "<table>";
  296. say "<thead><tr>";
  297. say "<th>Field name</th>";
  298. say "<th>Type</th>";
  299. say "</tr></thead>";
  300. say "<tbody>";
  301. foreach my $field (@fields) {
  302. my ($name, $type) = ($field->{name}, $field->{type});
  303. say "<tr>";
  304. say "<td>$name</td>";
  305. say "<td>$type</td>";
  306. say "</tr>";
  307. }
  308. say "</tbody>";
  309. say "</table>";
  310. }
  311.  
  312. say "</body>";
  313. say "</html>";
  314.  
  315. } else {die 'Unexpected arguments: ', join(' ', @ARGV)}
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!