AryaWu/sqlite

1#!/usr/bin/tclsh2#3# Generate SQL that will populate an SQLite database with about 100 megabytes4# of pseudo-random JSON text.5#6#     tclsh json-generator.tcl | sqlite3 json110mb.db7#8# srand() is used to initialize the random seed so that the same JSON9# is generated for every run.10#11expr srand(12345678)12set wordlist {13   ability   able      abroad    access    account   act14   action    active    actor     add       address   adept15   adroit    advance   advice    affect    age       ageless16   agency    agent     agile     agree     air       airfare17   airline   airport   alert     almond    alpha     always18   amend     amount    amplify   analyst   anchor    angel19   angelic   angle     ankle     annual    answer    antique20   anybody   anyhow    appeal    apple     apricot   apt21   area      argon     arm       army      arrival   arsenic22   art       artful    article   arugula   aside     ask23   aspect    assist    assume    atom      atone     attempt24   author    autumn    average   avocado   award     awl25   azure     back      bacon     bag       bagel     bake26   baker     balance   ball      balloon   bamboo    banana27   band      banjo     bank      barium    base      basil28   basin     basis     basket    bass      bat       bath29   battery   beach     beak      bean      bear      bearcub30   beauty    beef      beet      beige     being     bell31   belly     belt      bench     bend      benefit   best32   beta      better    beyond    bicycle   bid       big33   bike      bill      bird      biscuit   bismuth   bisque34   bit       black     blank     blest     blind     bliss35   block     bloom     blue      board     boat      body36   bokchoy   bone      bonus     book      bookish   boot37   border    boron     boss      bossy     bottle    bottom38   bow       bowl      bowtie    box       brain     brainy39   branch    brave     bravely   bread     break     breath40   breezy    brick     bridge    brie      brief     briefly41   bright    broad     broil     bromine   bronze    brother42   brow      brown     brush     buddy     budget    buffalo43   bug       bugle     bull      bunch     burger    burly44   burrito   bus       busy      butter    button    buy45   buyer     byte      cab       cabbage   cabinet   cable46   cadet     cadmium   caesium   cake      calcium   caliper47   call      caller    calm      calmly    camera    camp48   can       canary    cancel    candle    candy     cap49   capable   caper     capital   captain   car       carbon50   card      care      career    careful   carp      carpet51   carrot    carry     case      cash      cassava   casual52   cat       catch     catfish   catsear   catsup    cause53   cave      celery    cell      century   chain     chair54   chalk     chance    change    channel   chapter   chard55   charge    charity   chart     check     cheddar   cheery56   cheese    chicken   chicory   chiffon   child     chin57   chip      chives    choice    chowder   chum      church58   circle    city      claim     clam      class     classic59   classy    clay      clean     cleaner   clear     clearly60   clerk     click     client    climate   clock     clorine61   closet    clothes   cloud     clown     club      clue62   cluster   coach     coast     coat      cobbler   cobolt63   cod       code      coffee    colby     cold      collar64   college   comb      combine   comet     comfort   command65   comment   common    company   complex   concept   concern66   concert   conduit   consist   contact   contest   context67   control   convert   cook      cookie    copilot   copper68   copy      coral     cordial   corn      corner    corny69   correct   cost      count     counter   country   county70   couple    courage   course    court     cover     cow71   cowbird   crab      crack     craft     crash     crazy72   cream     credit    creek     cress     crevice   crew73   crimson   croaker   crop      cross     crowd     cube74   cuckoo    cuisine   culture   cup       current   curve75   cut       cyan      cycle     dagger    daily     dance76   dare      darter    data      date      day       daylily77   deal      dear      dearly    debate    debit     decade78   decimal   deep      deft      deftly    degree    delay79   deluxe    deposit   depth     design    desk      detail80   device    dew       diamond   diet      dig       dill81   dinner    dip       direct    dirt      dish      disk82   display   diver     divide    divine    doctor    dodger83   donut     door      dot       double    dough     draft84   drag      dragon    drama     draw      drawer    drawing85   dream     drill     drink     drive     driver    drop86   drum      dry       dryer     drywall   duck      due87   dump      dusk      dust      duty      dye       eagle88   ear       earring   earth     ease      east      easy89   eat       economy   edge      editor    eel       effect90   effort    egg       eight     elbow     elegant   element91   elf       elk       email     emerald   employ    end92   endive    endless   energy    engine    enjoy     enter93   entry     equal     equip     error     escape    essay94   eternal   evening   event     exam      example   excuse95   exit      expert    extent    extreme   eye       face96   fact      factor    factual   fail      failure   fair97   fajita    fall      family    fan       fang      farm98   farmer    fat       fault     feature   feed      feel99   feeling   fench     fennel    festive   few       fiber100   field     fig       figure    file      fill      film101   filter    final     finance   finding   finger    finish102   fire      fish      fishing   fit       fitting   five103   fix       flier     flight    floor     floral    florine104   flour     flow      flower    fly       flying    focus105   fold      folding   food      foot      force     forest106   forever   forgive   form      formal    format    fortune107   forum     frame     free      freedom   freely    fresh108   friend    frog      front     fruit     fuchsia   fuel109   fun       funny     future    gain      galaxy    gallium110   game      gamma     gap       garage    garden    garlic111   gas       gate      gather    gauge     gear      gem112   gene      general   gentle    gently    gherkin   ghost113   gift      give      glad      glass     gleeful   glossy114   glove     glue      goal      goat      goby      gold115   goldeye   golf      good      gouda     goulash   gourd116   grab      grace     grade     gram      grand     grape117   grapes    grass     gravy     gray      great     green118   grits     grocery   ground    group     grouper   grout119   growth    guard     guave     guess     guest     guide120   guitar    gumbo     guppy     habit     hacksaw   haddock121   hafnium   hagfish   hair      half      halibut   hall122   hammer    hand      handle    handy     hanger    happy123   hat       havarti   hay       haybale   head      health124   healthy   hearing   heart     hearty    heat      heavy125   heel      height    helium    hello     help      helpful126   herald    herring   hide      high      highly    highway127   hill      hip       hipster   hire      history   hit128   hoki      hold      hole      holiday   holly     home129   honest    honey     hook      hope      hopeful   horizon130   horn      horse     host      hotel     hour      house131   housing   human     humane    humor     hunt      hurry132   ice       icecube   icefish   icy       idea      ideal133   image     impact    impress   inch      income    indigo134   initial   inkpen    insect    inside    intense   invite135   iodine    iridium   iron      island    issue     item136   ivory     jacket    jargon    javelin   jello     jelly137   jewel     job       jocund    join      joint     joke138   jovial    joy       joyful    joyous    judge     juice139   jump      junior    jury      just      justice   kale140   keel      keep      kelp      ketchup   key       keyhole141   keyway    khaki     kick      kid       kidney    kiloohm142   kind      kindly    king      kitchen   kite      kiwi143   knee      knife     krill     krypton   kumquat   lab144   lace      lack      ladder    lake      lamp      lamprey145   land      laser     laugh     law       lawn      lawyer146   layer     lead      leader    leading   leaf      leafy147   league    leather   leave     lecture   leek      leg148   lemon     length    lentil    lesson    let       letter149   lettuce   level     library   life      lift      light150   lily      lime      limit     line      linen     link151   lip       list      listen    lithium   lively    living152   lizard    load      loan      lobster   local     lock153   log       long      longfin   look      lotus     love154   lovely    loving    low       lucid     luck      luffa155   lunch     lung      machine   magenta   magnet    mail156   main      major     make      mall      manager   mango157   manner    many      map       march     market    maroon158   martian   master    match     math      matter    maximum159   maybe     meal      meaning   meat      media     medium160   meet      meeting   melody    melon     member    memory161   mention   menu      mercury   merry     mess      message162   messy     metal     meter     method    micron    middle163   might     mile      milk      mind      mine      minimum164   minnow    minor     mint      minute    mirror    miss165   mission   misty     mix       mixer     mixture   mobile166   mode      model     moment    monitor   monk      month167   moon      moray     morning   most      motor     mouse168   mouth     move      mover     movie     much      mud169   mudfish   muffin    mullet    munster   muon      muscle170   music     mustard   nail      name      nation    native171   natural   nature    navy      neat      neatly    nebula172   neck      needle    neon      nerve     net       network173   neutron   news      nibble    nice      nickel    night174   niobium   nobody    noise     noodle    normal    north175   nose      note      nothing   notice    nova      novel176   number    nurse     nursery   oar       object    offer177   office    officer   oil       okay      okra      old178   olive     one       onion     open      opening   opinion179   option    orange    orbit     orchid    order     oregano180   other     ounce     outcome   outside   oven      owner181   oxygen    oyster    pace      pack      package   page182   pager     paint     pair      pale      pan       pancake183   papaya    paper     pardon    parent    park      parking184   parsley   parsnip   part      partner   party     pass185   passage   past      pasta     path      patient   pattern186   pause     pay       pea       peace     peach     peacock187   peahen    peak      peanut    pear      pearl     pen188   penalty   pencil    pension   people    pepper    perch189   perfect   period    permit    person    phase     phone190   photo     phrase    physics   piano     pick      picture191   pie       piece     pigeon    pike      pilot     pin192   pink      pinkie    pious     pipe      pitch     pizza193   place     plan      plane     planet    plant     planter194   plastic   plate     play      player    playful   plenty195   pliers    plum      pod       poem      poet      poetry196   point     police    policy    pollock   pony      pool197   pop       popover   poptart   pork      port      portal198   post      pot       potato    pound     powder    power199   present   press     price     pride     primary   print200   prior     private   prize     problem   process   produce201   product   profile   profit    program   project   promise202   prompt    proof     proper    protein   proton    public203   puff      puffer    pull      pumpkin   pup       pupfish204   pure      purple    purpose   push      put       quality205   quark     quarter   quiet     quill     quit      quote206   rabbit    raccoon   race      radiant   radio     radish207   radium    radon     rain      rainbow   raise     ramp208   ranch     range     rasp      rate      ratio     ray209   razor     reach     read      reading   real      reality210   reason    recipe    record    recover   red       redeem211   reed      reef      refuse    region    regret    regular212   relaxed   release   relief    relish    remote    remove213   rent      repair    repeat    reply     report    request214   reserve   resist    resolve   resort    rest      result215   return    reveal    review    reward    ribbon    rice216   rich      ride      ridge     right     ring      rise217   risk      river     rivet     road      roast     rock218   rocket    role      roll      roof      room      rope219   rose      rough     roughy    round     row       royal220   rub       ruby      rudder    ruin      rule      run221   runner    rush      rust      sacred    saddle    safe222   safety    sail      salad     salami    sale      salmon223   salt      sample    sand      sander    sandy     sauce224   save      saving    saw       scale     scampi    scene225   scheme    school    score     screen    script    sea226   search    season    seat      second    secret    sector227   seemly    self      sell      senate    senior    sense228   series    serve     set       shake     shape     share229   shark     shell     shift     shine     shiny     ship230   shock     shoe      shoot     shop      shovel    show231   side      sign      signal    silk      silly     silver232   simple    sing      singer    single    sink      site233   size      skill     skin      sky       slate     sleep234   sleepy    slice     slide     slip      smart     smell235   smelt     smile     smoke     smooth    snap      snipe236   snow      snowy     sock      socket    sodium    soft237   softly    soil      sole      solid     song      sorrel238   sort      soul      sound     soup      source    south239   space     spare     speech    speed     spell     spend240   sphere    spice     spider    spirit    spite     split241   spoon     sport     spot      spray     spread    spring242   squab     square    squash    stable    staff     stage243   stand     staple    star      start     state     status244   stay      steak     steel     step      stern     stew245   stick     still     stock     stone     stop      store246   storm     story     strain    street    stress    strike247   string    stroke    strong    studio    study     stuff248   style     sugar     suit      sulfur    summer    sun249   sunny     sunset    super     superb    surf      survey250   sweet     swim      swing     switch    symbol    system251   table     tackle    tail      tale      talk      tan252   tank      tap       tape      target    task      taste253   tau       tea       teach     teal      team      tear254   tell      ten       tender    tennis    tent      term255   test      tetra     text      thanks    theme     theory256   thing     think     thread    throat    thumb     ticket257   tidy      tie       tiger     till      time      timely258   tin       tip       title     toast     today     toe259   tomato    tone      tongue    tool      tooth     top260   topic     total     touch     tough     tour      towel261   tower     town      track     trade     train     trash262   travel    tray      treat     tree      trick     trip263   trout     trowel    truck     trupet    trust     truth264   try       tube      tuna      tune      turf      turkey265   turn      turnip    tutor     tux       tweet     twist266   two       type      union     unique    unit      upbeat267   upper     use       useful    user      usual     valley268   value     van       vase      vast      veil      vein269   velvet    verse     very      vessel    vest      video270   view      violet    visit     visual    vivid     voice271   volume    vowel     voyage    waffle    wait      wake272   walk      wall      warm      warmth    wasabi    wash273   watch     water     wave      wax       way       wealth274   wear      web       wedge     week      weekly    weight275   west      whale     what      wheat     wheel     when276   where     while     who       whole     why       will277   win       wind      window    wing      winner    winter278   wire      wish      witty     wolf      wonder    wood279   wool      woolly    word      work      worker    world280   worry     worth     worthy    wrap      wrench    wrist281   writer    xenon     yak       yam       yard      yarrow282   year      yearly    yellow    yew       yogurt    young283   youth     zebra     zephyr    zinc      zone      zoo284}285set nwordlist [llength $wordlist]286 287proc random_char {} {288  return [string index \289             "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" \290             [expr {int(rand()*52)}]]291}292proc random_label {} {293  set label [random_char]294  while {rand()>0.8} {295    append label [random_char]296  }297  if {rand()>0.9} {append label -}298  append label [format %d [expr {int(rand()*100)}]]299  return $label300}301proc random_numeric {} {302  set n [expr {(rand()*2-1.0)*1e6}]303  switch [expr {int(rand()*6)}] {304    0  {set format %.3f}305    1  {set format %.6E}306    2  {set format %.4e}307    default  {set format %g}308  }309  return [format $format $n]310}311 312 313proc random_json {limit indent} {314  global nwordlist wordlist315  set res {}316  if {$indent==0 || ($limit>0 && rand()>0.5)} {317    incr limit -1318    incr indent 2319    set n [expr {int(rand()*5)+1}]320    if {$n==5} {incr n [expr {int(rand()*10)}]}321    if {rand()>0.5} {322      set res \173\n323      for {set i 0} {$i<$n} {incr i} {324        append res [string repeat { } $indent]325        if {rand()>0.8} {326          if {rand()>0.5} {327            set sep ":\n   [string repeat { } $indent]"328          } else {329            set sep " : "330          }331        } else {332          set sep :333        }334        append res \"[random_label]\"$sep[random_json $limit $indent]335        if {$i<$n-1} {append res ,}336        append res \n337      }338      incr indent -2339      append res [string repeat { } $indent]340      append res \175341      return $res342    } else {343      set res \[\n344      for {set i 0} {$i<$n} {incr i} {345        append res [string repeat { } $indent]346        append res [random_json $limit $indent]347        if {$i<$n-1} {append res ,}348        append res \n349      }350      incr indent -2351      append res [string repeat { } $indent]352      append res \]353      return $res354    }355  } elseif {rand()>0.9} {356    if {rand()>0.7} {return "true"}357    if {rand()>0.5} {return "false"}358    return "null"359  } elseif {rand()>0.5} {360    return [random_numeric]361  } else {362    set res \"363    set n [expr {int(rand()*4)+1}]364    if {$n>=4} {set n [expr {$n+int(rand()*6)}]}365    for {set i 0} {$i<$n} {incr i} {366      if {rand()<0.05} {367        set w [random_numeric]368      } else {369        set k [expr {int(rand()*$nwordlist)}]370        set w [lindex $wordlist $k]371      }372      if {rand()<0.07} {373         set w \\\"$w\\\"374      }375      if {$i<$n-1} {376        switch [expr {int(rand()*9)}] {377          0       {set sp {, }}378          1       {set sp "\\n "}379          2       {set sp "-"}380          default {set sp { }}381        }382        append res $w$sp383      } else {384        append res $w385        if {rand()<0.2} {append res .}386      }387    }388    return $res\"389  }390}391 392puts "CREATE TABLE IF NOT EXISTS data1(x JSON);"393puts "BEGIN;"394set sz 0395for {set i 0} {$sz<100000000} {incr i} {396  set j [random_json 7 0]397  incr sz [string length $j]398  puts "INSERT INTO data1(x) VALUES('$j');"399}400puts "COMMIT;"401puts "SELECT sum(length(x)) FROM data1;"402