Help with macro; loading variable number of files

Help & Support
1

Hi hi,

I have a sql folder with n number of .sql files.

I want to get the contents of each file into my program. I was going to use ECR for this.

But I run into trouble when trying to iterate over an array, it seems this is only possible with Array literals.

There is certainly a more manual way to do this, but I thought it would be fun to automate.

I have several model files, and I want to scan for possible ./sql/*.sql files with related SQL statements to prepare the model (e.g., migrations, etc). The SQL files live on their own to make development easier; but I also want to include them in the program so I can execute the statements when the program runs.

I’ve been messing with something like the following: This version breaks on undefined macro variable.

   abstract struct Model
      @@statements = [] of String

      def self.migrate
        puts "running sql #{@@statements}"
      end

      macro inherited
        path = Path.new __FILE__
        search = path.parent.join "sql", "*.sql"
        globs = Dir.glob search
        if globs.size > 0
          @@statements << ECR.render {{ globs[0] }}. # YIKES!
        end

        migrate()
      end
   end
2

All your logic executes at runtime, but ECR runs at compile time.
So… which one do you want? Should the sql files be loaded at runtime or when you build the program (and then bake them into the binary)?

3

I want to load the SQL via ECR at build time.

Then execute those strings at runtime. :slight_smile:

4

Well, I’ve found a path forward. Not sure if it’s the correct way but it’ll do.

I was reading through the ECR docs and ended up in the source, saw the call to the {{ run ... }} command and thought that was interesting.

Turns out it’s a thing that let’s you compile a small Crystal program and its output is written to your binary. This is amazing! The things I find every time I write Crystal :heart_eyes:

So now my code looks like this:

macro sql(filename)
      MIGRATIONS = {{ run("./read", filename).stringify }}

      def self.migrate
        MIGRATIONS.split(";\n").each { |m| DB.execute(m) }
      end
      
      migrate
    end

The base model uses the inherit macro to add this to every actual model:

macro inherited
        DB.sql __FILE__
end

and then the small one off script finds all the sql files relative to each model:

filename = ARGV[0]

path = Path.new filename
search = path.parent.join "sql", "*.sql"
globs = Dir.glob search
# Split blocks by ;\n\n
output = [] of String

globs.each do |glob|
  content = File.read glob
  blocks = content.split(/;\n\n+/).reject(&.starts_with? /-- ?dev/)

  blocks.each do |block|
    output << block
  end
end

puts output.join ";\n"

What a day :drooling_face:

2 Likes
5

There is something called exec_all if you wanted try that.

  # sample from my code
  def unprepared(sql)
    @db.using_connection do |conn|
      conn.exec_all sql
    end
  end

It might only exist in the postgres driver Perform multiple commands in a single exec · Issue #113 · crystal-lang/crystal-db · GitHub

1 Like
6

I think so, I’m using SQLite and it is not available. :frowning: