<commit_before>#!/usr/bin/perl 

undef $/; 
open(F, "create.sql"); 
$fc = <F>;
close (F);

$fc =~ s|/\*.*?\*/||sg;

$schema = {};

$types = {
  'varchar' => 'String', 
  'int' => 'int', 
  'intNull' => 'Integer', 
  'numeric' => 'float', 
  'float' => 'float', 
  'datetime' => 'Date', 
  'timestamp' => 'Date', 
  'boolean' => 'boolean', 
};

foreach my $block (split /\s*;\s*/, $fc) {
  $block =~ s/--.*//g;
  $block =~ s/\s*\n\s*/\n/g;

  $ja = ""; 
  $jb = ""; 
  my ($table) = ($block =~ /create table\s*([a-z_]+)/);
  next if ! $table;
  $_table = $table;


  $schema->{$_table} = {};
  $schema->{$_table}->{'_table'} = $_table;

  my ($pk) = ($block =~ /(\S+)\s*int.*primary key/i);
  $schema->{$_table}->{'pk'} = $pk;


  $table =~ s/^type(.)/"type\U$1"/e;
  $table =~ s/^subtype(.)/"subtype\U$1"/e;
  $table =~ s/(.)user$/$1User/;
  $table =~ s/(.)pendingmeta$/$1PendingMeta/;
  $table =~ s/(.)pending$/$1Pending/;
  $table =~ s/(.)mapping$/$1Mapping/;
  $table =~ s/(.)software$/$1Software/;
  $table =~ s/(.)meta/$1Meta/;
  $table =~ s/(.)draft/$1Draft/;
  $table =~ s/(.)cell/$1Cell/;

  $table =~ s/^job(.)/"job\U$1"/ie;
  $table =~ s/^project(.)/"project\U$1"/ie;
  $table =~ s/^sample(.)/"sample\U$1"/ie;
  $table =~ s/^software(.)/"software\U$1"/ie;
  $table =~ s/^run(.)/"run\U$1"/ie;
  $table =~ s/^resource(.)/"resource\U$1"/ie;
  $table =~ s/^quote(.)/"quote\U$1"/ie;
  $table =~ s/_(.)/"\U$1"/gie;

  $schema->{$_table}->{'table'} = $table;



  $Table = $table;
  $Table =~ s/^(.)/"\U$1"/e; 


  $schema->{$_table}->{'Table'} = $Table;
  $schema->{$_table}->{'cols'} = {};
  $schema->{$_table}->{'colorder'} = [];
  $schema->{$_table}->{'uniq'} = [];
  $schema->{$_table}->{'fka'} = [];
  $schema->{$_table}->{'fkb'} = [];


  $block =~ s/.*?\(//s;  
  $block =~ s/\)[^)]*$//s;  

  foreach my $row (split/\s*\n\s*/, $block ) {
    $row =~ s/,$//g;
    next if (! $row);
    if ($row =~ /^foreign key/ ) {
      # foreign key fk_job_pid (projectid) references project(projectid),
      # foreign key fk_sample_sjid (submitter_jobid) references job(jobid),
      my ($fkname, $col, $ptable, $pcol) = ($row =~ /foreign key (.*) \((.*)\) references (.*)\s*\((.*)\)/);

# print "$fkname, $col, $ptable, $pcol\n";

      my $fk = {};
      $fk->{'fkname'}  = $fkname;
      $fk->{'table'}  = $_table;
      $fk->{'col'}  = $col;
      $fk->{'ptable'}  = $ptable;
      $fk->{'pcol'}  = $pcol;
      
      # next if defined($schema->{$ptable}->{'cols'}->{$pcol}->{'fkb'}->{$_table'}->{$col});
      next if $schema->{$ptable}->{'cols'}->{$pcol}->{'fkb'}->{$_table}->{$col};

      push @{$schema->{$_table}->{'fka'}}, $fk;
      push @{$schema->{$ptable}->{'fkb'}}, $fk;
      schema->{$ptable}->{'cols'}->{$pcol}->{'fkb'}->{$_table}->{$col} = 1;

    } elsif ($row =~ /^index/ ) {
      # todo create get by list 

    } elsif ($row =~ /^constraint/ ) {

      # constraint unique index u_samplesource_sid (sampleid, multiplexindex)
      my ($index, $cols) = ($row =~ /index\s+(\S+)\s*\((.*)\)/);

      my $uniq = {}; 
      $uniq->{'index'} = $index; 
      $uniq->{'cols'} = [ split /\s*,\s*/, $cols ] ;
 

      $schema->{$_table}->{'cols'}->{'uniq'} = $uniq;
      push @{$schema->{$_table}->{'uniq'}}, $uniq;

    } else {
      my ($var, $type, $len) = ($row =~ /([a-z0-9_]+)\s+([a-z]+)\s*(\(\s*(\d+)\s*\))?/); 
      $len =~ s/\D//g;
      $_var = $var;
      push @{$schema->{$_table}->{'colorder'}}, $_var;

      if ($type eq "int" && $row !~ /not null/) {
        $type = "intNull"; 
      }


      $var =~ s/$_table/$table/ei;
      $var =~ s/lastupduser/lastUpdUser/;
      $var =~ s/lastupdts/lastUpdTs/;
      $var =~ s/userid$/UserId/;
      $var =~ s/id$/Id/;
      $var =~ s/dts$/Dts/;
      $var =~ s/(.)name$/$1Name/;
      $var =~ s/^is(.)/"is\U$1"/e;
      $var =~ s/^type(.)/"type\U$1"/e;<commit_msg>endts fix<commit_after>#!/usr/bin/perl 

undef $/; 
open(F, "create.sql"); 
$fc = <F>;
close (F);

$fc =~ s|/\*.*?\*/||sg;

$schema = {};

$types = {
  'varchar' => 'String', 
  'int' => 'int', 
  'intNull' => 'Integer', 
  'numeric' => 'float', 
  'float' => 'float', 
  'datetime' => 'Date', 
  'timestamp' => 'Date', 
  'boolean' => 'boolean', 
};

foreach my $block (split /\s*;\s*/, $fc) {
  $block =~ s/--.*//g;
  $block =~ s/\s*\n\s*/\n/g;

  $ja = ""; 
  $jb = ""; 
  my ($table) = ($block =~ /create table\s*([a-z_]+)/);
  next if ! $table;
  $_table = $table;


  $schema->{$_table} = {};
  $schema->{$_table}->{'_table'} = $_table;

  my ($pk) = ($block =~ /(\S+)\s*int.*primary key/i);
  $schema->{$_table}->{'pk'} = $pk;


  $table =~ s/^type(.)/"type\U$1"/e;
  $table =~ s/^subtype(.)/"subtype\U$1"/e;
  $table =~ s/(.)user$/$1User/;
  $table =~ s/(.)pendingmeta$/$1PendingMeta/;
  $table =~ s/(.)pending$/$1Pending/;
  $table =~ s/(.)mapping$/$1Mapping/;
  $table =~ s/(.)software$/$1Software/;
  $table =~ s/(.)meta/$1Meta/;
  $table =~ s/(.)draft/$1Draft/;
  $table =~ s/(.)cell/$1Cell/;

  $table =~ s/^job(.)/"job\U$1"/ie;
  $table =~ s/^project(.)/"project\U$1"/ie;
  $table =~ s/^sample(.)/"sample\U$1"/ie;
  $table =~ s/^software(.)/"software\U$1"/ie;
  $table =~ s/^run(.)/"run\U$1"/ie;
  $table =~ s/^resource(.)/"resource\U$1"/ie;
  $table =~ s/^quote(.)/"quote\U$1"/ie;
  $table =~ s/_(.)/"\U$1"/gie;

  $schema->{$_table}->{'table'} = $table;



  $Table = $table;
  $Table =~ s/^(.)/"\U$1"/e; 


  $schema->{$_table}->{'Table'} = $Table;
  $schema->{$_table}->{'cols'} = {};
  $schema->{$_table}->{'colorder'} = [];
  $schema->{$_table}->{'uniq'} = [];
  $schema->{$_table}->{'fka'} = [];
  $schema->{$_table}->{'fkb'} = [];


  $block =~ s/.*?\(//s;  
  $block =~ s/\)[^)]*$//s;  

  foreach my $row (split/\s*\n\s*/, $block ) {
    $row =~ s/,$//g;
    next if (! $row);
    if ($row =~ /^foreign key/ ) {
      # foreign key fk_job_pid (projectid) references project(projectid),
      # foreign key fk_sample_sjid (submitter_jobid) references job(jobid),
      my ($fkname, $col, $ptable, $pcol) = ($row =~ /foreign key (.*) \((.*)\) references (.*)\s*\((.*)\)/);

# print "$fkname, $col, $ptable, $pcol\n";

      my $fk = {};
      $fk->{'fkname'}  = $fkname;
      $fk->{'table'}  = $_table;
      $fk->{'col'}  = $col;
      $fk->{'ptable'}  = $ptable;
      $fk->{'pcol'}  = $pcol;
      
      # next if defined($schema->{$ptable}->{'cols'}->{$pcol}->{'fkb'}->{$_table'}->{$col});
      next if $schema->{$ptable}->{'cols'}->{$pcol}->{'fkb'}->{$_table}->{$col};

      push @{$schema->{$_table}->{'fka'}}, $fk;
      push @{$schema->{$ptable}->{'fkb'}}, $fk;
      schema->{$ptable}->{'cols'}->{$pcol}->{'fkb'}->{$_table}->{$col} = 1;

    } elsif ($row =~ /^index/ ) {
      # todo create get by list 

    } elsif ($row =~ /^constraint/ ) {

      # constraint unique index u_samplesource_sid (sampleid, multiplexindex)
      my ($index, $cols) = ($row =~ /index\s+(\S+)\s*\((.*)\)/);

      my $uniq = {}; 
      $uniq->{'index'} = $index; 
      $uniq->{'cols'} = [ split /\s*,\s*/, $cols ] ;
 

      $schema->{$_table}->{'cols'}->{'uniq'} = $uniq;
      push @{$schema->{$_table}->{'uniq'}}, $uniq;

    } else {
      my ($var, $type, $len) = ($row =~ /([a-z0-9_]+)\s+([a-z]+)\s*(\(\s*(\d+)\s*\))?/); 
      $len =~ s/\D//g;
      $_var = $var;
      push @{$schema->{$_table}->{'colorder'}}, $_var;

      if ($type eq "int" && $row !~ /not null/) {
        $type = "intNull"; 
      }


      $var =~ s/$_table/$table/ei;
      $var =~ s/lastupduser/lastUpdUser/;
      $var =~ s/lastupdts/lastUpdTs/;
      $var =~ s/userid$/UserId/;
      $var =~ s/id$/Id/;
      $var =~ s/dts$/Dts/;
      $var =~ s/endts$/endts/i;
      $var =~ s/(.)name$/$1Name/;
      $var =~ s/^is(.)/"is\U$1"/e;
      $var =~ s/^type(.)/"type\U$1"/e;
{'fraction_non_alphanumeric': 0.3271124935199585, 'fraction_numerical': 0.006480041472265423, 'mean_word_length': 2.5431588613406797, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 131, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2527255', 'n_tokens_mistral': 3568, 'n_tokens_neox': 3419, 'n_words': 514}