1#************************************************************** 2# 3# Licensed to the Apache Software Foundation (ASF) under one 4# or more contributor license agreements. See the NOTICE file 5# distributed with this work for additional information 6# regarding copyright ownership. The ASF licenses this file 7# to you under the Apache License, Version 2.0 (the 8# "License"); you may not use this file except in compliance 9# with the License. You may obtain a copy of the License at 10# 11# http://www.apache.org/licenses/LICENSE-2.0 12# 13# Unless required by applicable law or agreed to in writing, 14# software distributed under the License is distributed on an 15# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16# KIND, either express or implied. See the License for the 17# specific language governing permissions and limitations 18# under the License. 19# 20# usage: runpy.sh fixtures/base/make.py <port> <outdir> [<workdir>] 21# Writes base-<variant>.odb data source documents into <outdir>; dbf, csv and hsqldb files go to <workdir>. 22import os, shutil, sys, traceback, uno 23from unohelp import connect, pv 24from com.sun.star.beans import NamedValue 25from com.sun.star.awt import Size, Point 26from com.sun.star.lang import Locale 27 28port, out = int(sys.argv[1]), os.path.abspath(sys.argv[2]) 29work = os.path.abspath(sys.argv[3]) if len(sys.argv) > 3 else os.path.join(os.path.dirname(out), "work") 30ctx = connect(port) 31sm = ctx.ServiceManager 32dbc = sm.createInstanceWithContext("com.sun.star.sdb.DatabaseContext", ctx) 33os.makedirs(out, exist_ok=True) 34os.makedirs(work, exist_ok=True) 35FURL = uno.systemPathToFileUrl 36ANCHOR = uno.Enum("com.sun.star.text.TextContentAnchorType", "AT_PARAGRAPH") 37 38 39def nv(n, v): 40 return NamedValue(n, v) 41 42 43def set_info(ds, **kw): 44 # Info= with several settings crashes the office in comphelper::OPropertyBag; set them one by one 45 bag = ds.Settings 46 for k, v in kw.items(): 47 if bag.getPropertySetInfo().hasPropertyByName(k): 48 uno.invoke(bag, "setPropertyValue", (k, v)) 49 else: 50 uno.invoke(bag, "addProperty", (k, 128 | 1, v)) 51 52 53def new_source(url, **props): 54 ds = dbc.createInstance() 55 ds.URL = url 56 for k, v in props.items(): 57 setattr(ds, k, v) 58 return ds 59 60 61def sql(conn, stmts): 62 st = conn.createStatement() 63 for s in stmts: 64 st.executeUpdate(s) 65 st.close() 66 67 68def hsql_connect(ds): 69 # the first statement on a fresh embedded database fails on a null context class loader; a retry works 70 conn = ds.getConnection("", "") 71 st = conn.createStatement() 72 for _ in range(3): 73 try: 74 st.executeUpdate('CREATE MEMORY TABLE "PROBE" ("ID" INTEGER)') 75 st.executeUpdate('DROP TABLE "PROBE"') 76 break 77 except Exception: 78 pass 79 st.close() 80 return conn 81 82 83def numkey(ds, code): 84 nf = ds.NumberFormatsSupplier.NumberFormats 85 loc = Locale("en", "US", "") 86 k = nf.queryKey(code, loc, False) 87 return k if k != -1 else nf.addNew(code, loc) 88 89 90def make_query(ds, name, command, **props): 91 q = ds.QueryDefinitions.createInstance() 92 q.Command = command 93 for k, v in props.items(): 94 setattr(q, k, v) 95 ds.QueryDefinitions.insertByName(name, q) 96 return ds.QueryDefinitions.getByName(name) 97 98 99def add_columns(owner, specs): 100 cols = owner.Columns 101 for name, props in specs: 102 d = cols.createDataDescriptor() 103 d.Name = name 104 for k, v in props.items(): 105 d.setPropertyValue(k, v) 106 cols.appendByDescriptor(d) 107 108 109def make_table_def(ds, name, columns=(), **props): 110 tables = ds.Tables 111 if tables.hasByName(name): 112 t = tables.getByName(name) 113 for k, v in props.items(): 114 t.setPropertyValue(k, v) 115 else: 116 t = tables.createInstance() 117 for k, v in props.items(): 118 t.setPropertyValue(k, v) 119 tables.insertByName(name, t) 120 t = tables.getByName(name) 121 add_columns(t, columns) 122 return t 123 124 125def new_doc_def(container, name, service, parent=None): 126 args = (nv("Name", name), nv("DocumentServiceName", service)) 127 d = container.createInstanceWithArguments("com.sun.star.sdb.DocumentDefinition", args) 128 (parent or container).insertByName(name, d) 129 return d 130 131 132def open_def(container, name, conn): 133 return container.loadComponentFromURL(name, "", 0, (pv("OpenMode", "openDesign"), pv("ActiveConnection", conn))) 134 135 136def def_command(container, name, command): 137 cmd = uno.createUnoStruct("com.sun.star.ucb.Command") 138 cmd.Name = command 139 cmd.Handle = -1 140 container.getByName(name).execute(cmd, 0, None) 141 142 143def store_def(container, name): 144 def_command(container, name, "store") 145 def_command(container, name, "close") 146 147 148def add_control(doc, form, service, name, x, y, w=4000, h=700, **props): 149 m = doc.createInstance("com.sun.star.form.component." + service) 150 for k, v in props.items(): 151 setattr(m, k, v) 152 form.insertByName(name, m) 153 shape = doc.createInstance("com.sun.star.drawing.ControlShape") 154 shape.AnchorType = ANCHOR 155 shape.Size = Size(w, h) 156 shape.Position = Point(x, y) 157 shape.Control = m 158 doc.DrawPage.add(shape) 159 return m 160 161 162CUSTOMER_CONTROLS = ( 163 ("FixedText", "Title", dict(Label="Customers")), 164 ("TextField", "NameBox", dict(DataField="NAME", MaxTextLen=40)), 165 ("NumericField", "IdBox", dict(DataField="ID", DecimalAccuracy=0, ValueMin=0.0, ValueMax=9999.0)), 166 ("CheckBox", "ActiveBox", dict(DataField="ACTIVE", Label="Active", TriState=True)), 167 ("DateField", "BornBox", dict(DataField="BORN", DateFormat=4)), 168 ("CurrencyField", "BalBox", dict(DataField="BALANCE", CurrencySymbol="$", PrependCurrencySymbol=True)), 169 ("ListBox", "CityList", dict(DataField="CITY", ListSourceType=2, ListSource=('SELECT DISTINCT "CITY" FROM "CUSTOMERS"',), 170 Dropdown=True)), 171 ("ComboBox", "CityCombo", dict(DataField="CITY", ListSourceType=0, StringItemList=("Berlin", "Paris"))), 172 ("CommandButton", "Ok", dict(Label="Save", ButtonType=2)), 173) 174 175 176def fill_form(doc, command, controls=CUSTOMER_CONTROLS, sub=True): 177 forms = doc.DrawPage.Forms 178 main = doc.createInstance("com.sun.star.form.component.DataForm") 179 main.Command = command 180 main.CommandType = 0 181 main.Filter = '"ID" > 0' 182 main.ApplyFilter = True 183 main.Order = '"NAME"' 184 forms.insertByName("MainForm", main) 185 y = 300 186 for service, name, props in controls: 187 add_control(doc, main, service, name, 500, y, **props) 188 y += 900 189 if sub: 190 child = doc.createInstance("com.sun.star.form.component.DataForm") 191 child.Command = command 192 child.CommandType = 0 193 child.MasterFields = ("ID",) 194 child.DetailFields = ("ID",) 195 main.insertByName("SubForm", child) 196 add_control(doc, child, "GridControl", "Grid", 500, y, w=6000, h=2500) 197 add_control(doc, child, "RadioButton", "R1", 7000, 300, Label="One", RefValue="1", DataField="ID") 198 add_control(doc, child, "RadioButton", "R2", 7000, 1200, Label="Two", RefValue="2", DataField="ID") 199 200 201def populate(ds, conn, command="CUSTOMERS", folder=True): 202 doc = ds.DatabaseDocument 203 fd = doc.FormDocuments 204 new_doc_def(fd, "Form1", "com.sun.star.text.TextDocument") 205 f = open_def(fd, "Form1", conn) 206 fill_form(f, command) 207 store_def(fd, "Form1") 208 if folder: 209 grp = fd.createInstanceWithArguments("com.sun.star.sdb.Forms", (pv("Name", "Folder"),)) 210 fd.insertByName("Folder", grp) 211 new_doc_def(fd, "Form2", "com.sun.star.text.TextDocument", parent=grp) 212 f = open_def(grp, "Form2", conn) 213 fill_form(f, command, controls=CUSTOMER_CONTROLS[:3], sub=False) 214 store_def(grp, "Form2") 215 rd = doc.ReportDocuments 216 new_doc_def(rd, "Report1", "com.sun.star.text.TextDocument") 217 r = open_def(rd, "Report1", conn) 218 fill_form(r, command, controls=CUSTOMER_CONTROLS[:2], sub=False) 219 store_def(rd, "Report1") 220 221 222def new_hsqldb(name, **props): 223 path = os.path.join(out, name) 224 for p in (path, path + ".lck"): 225 if os.path.exists(p): 226 os.remove(p) 227 ds = new_source("sdbc:embedded:hsqldb", **props) 228 ds.DatabaseDocument.storeAsURL(FURL(path), ()) 229 return ds 230 231 232def new_dir(name): 233 d = os.path.join(work, name) 234 shutil.rmtree(d, ignore_errors=True) 235 os.makedirs(d) 236 return d 237 238 239def finish(ds, name, conn=None): 240 doc = ds.DatabaseDocument 241 doc.store() 242 if conn is not None: 243 conn.close() 244 doc.close(True) 245 lck = os.path.join(out, name + ".lck") 246 if os.path.exists(lck): 247 os.remove(lck) 248 249 250def save_new(ds, name): 251 path = os.path.join(out, name) 252 if os.path.exists(path): 253 os.remove(path) 254 ds.DatabaseDocument.storeAsURL(FURL(path), ()) 255 256 257def variant_hsqldb(): 258 ds = new_hsqldb("base-hsqldb.odb", TableFilter=("%",), TableTypeFilter=("TABLE", "VIEW"), User="", IsPasswordRequired=False) 259 conn = hsql_connect(ds) 260 sql(conn, ['CREATE MEMORY TABLE "CUSTOMERS" ("ID" INTEGER NOT NULL PRIMARY KEY, "NAME" VARCHAR(50), "CITY" VARCHAR(30), ' 261 '"ACTIVE" BOOLEAN, "BORN" DATE, "BALANCE" DECIMAL(10,2))', 262 'CREATE MEMORY TABLE "ORDERS" ("OID" INTEGER NOT NULL PRIMARY KEY, "CID" INTEGER, "TOTAL" DOUBLE, ' 263 'FOREIGN KEY ("CID") REFERENCES "CUSTOMERS"("ID"))', 264 'CREATE VIEW "BIGORDERS" AS SELECT * FROM "ORDERS" WHERE "TOTAL" > 100']) 265 set_info(ds, IsAutoRetrievingEnabled=True, AutoRetrievingStatement="CALL IDENTITY()", 266 AutoIncrementCreation="GENERATED BY DEFAULT AS IDENTITY", BooleanComparisonMode=1, 267 SuppressVersionColumns=False) 268 make_query(ds, "Q_ByCity", 'SELECT "ID", "NAME", "CITY" FROM "CUSTOMERS"', 269 Filter='"CITY" = \'Berlin\'', ApplyFilter=True, Order='"NAME"', 270 UpdateTableName="CUSTOMERS", UpdateSchemaName="PUBLIC", UpdateCatalogName="") 271 make_query(ds, "Q_Native", 'SELECT * FROM "ORDERS"', EscapeProcessing=False) 272 q = make_query(ds, "Q_Cols", 'SELECT "OID", "TOTAL" FROM "ORDERS"', RowHeight=800, TextColor=0xFF0000, FontName="Arial") 273 add_columns(q, [("OID", dict(Hidden=True, HelpText="order id")), 274 ("TOTAL", dict(HelpText="the total", ControlDefault="1.5", Width=4000, Align=2, 275 FormatKey=numkey(ds, "#,##0.00")))]) 276 make_table_def(ds, "CUSTOMERS", [("NAME", dict(Width=5000, HelpText="customer name", Align=1)), 277 ("BALANCE", dict(Hidden=True, ControlDefault="0"))], 278 Filter='"ACTIVE" = TRUE', ApplyFilter=True, Order='"NAME" DESC', 279 SchemaName="PUBLIC", CatalogName="CAT", RowHeight=700, TextColor=0x0000FF, FontName="Courier") 280 make_table_def(ds, "ORDERS") 281 populate(ds, conn) 282 finish(ds, "base-hsqldb.odb", conn) 283 284 285def make_dbf(conn): 286 tbls = conn.Tables 287 t = tbls.createDataDescriptor() 288 t.Name = "CUSTOMERS" 289 cols = t.Columns 290 for name, typ, prec, scale in (("ID", 4, 9, 0), ("NAME", 12, 40, 0), ("CITY", 12, 30, 0), ("ACTIVE", -7, 0, 0), 291 ("BORN", 91, 0, 0), ("BALANCE", 3, 10, 2), ("NOTES", -1, 0, 0)): 292 c = cols.createDataDescriptor() 293 c.Name, c.Type, c.Precision, c.Scale = name, typ, prec, scale 294 cols.appendByDescriptor(c) 295 tbls.appendByDescriptor(t) 296 ps = conn.prepareStatement('INSERT INTO "CUSTOMERS" VALUES (?,?,?,?,?,?,?)') 297 for i, name, city, active, born, bal, note in ((1, "Ada", "Berlin", True, (31, 1, 1990), 12.5, "first"), 298 (2, "Bob", "Paris", False, (4, 7, 1985), 0.0, "second")): 299 ps.setInt(1, i) 300 ps.setString(2, name) 301 ps.setString(3, city) 302 ps.setBoolean(4, active) 303 ps.setDate(5, uno.createUnoStruct("com.sun.star.util.Date", *born)) 304 ps.setDouble(6, bal) 305 ps.setString(7, note) 306 ps.executeUpdate() 307 ps.close() 308 309 310def variant_dbase(): 311 d = new_dir("dbase") 312 ds = new_source("sdbc:dbase:" + FURL(d) + "/", TableFilter=("CUSTOMERS",), TableTypeFilter=("TABLE",)) 313 set_info(ds, Extension="dbf", ShowDeleted=True, CharSet="utf-8") 314 save_new(ds, "base-dbase.odb") 315 conn = ds.getConnection("", "") 316 make_dbf(conn) 317 make_query(ds, "Q_Active", 'SELECT * FROM "CUSTOMERS"', Filter='"ACTIVE" = TRUE', ApplyFilter=True, Order='"NAME"', 318 UpdateTableName="CUSTOMERS") 319 make_table_def(ds, "CUSTOMERS", [("NAME", dict(Width=4000))], Order='"NAME"') 320 populate(ds, conn) 321 finish(ds, "base-dbase.odb", conn) 322 323 324def variant_flat(name, ext, header, field, text, dec, thousand, charset): 325 d = new_dir(name) 326 sep = field 327 rows = [["ID", "NAME", "CITY", "BALANCE"], ["1", "Ada", "Berlin", "1,250.50"], ["2", "Bob", "Paris", "0.00"]] 328 with open(os.path.join(d, "CUSTOMERS." + ext), "w", encoding="utf-8") as fh: 329 for i, r in enumerate(rows): 330 if i == 0 and not header: 331 continue 332 fh.write(sep.join(text + c + text if c in ("Ada", "Bob", "Berlin", "Paris", "NAME", "CITY") else c for c in r) + "\n") 333 ds = new_source("sdbc:flat:" + FURL(d) + "/", TableFilter=("CUSTOMERS",)) 334 set_info(ds, Extension=ext, CharSet=charset, FieldDelimiter=field, StringDelimiter=text, DecimalDelimiter=dec, 335 ThousandDelimiter=thousand, HeaderLine=header) 336 save_new(ds, "base-%s.odb" % name) 337 make_query(ds, "Q_All", 'SELECT * FROM "CUSTOMERS"') 338 finish(ds, "base-%s.odb" % name) 339 340 341def variant_flat_csv(): 342 variant_flat("flat", "csv", True, ",", '"', ".", ",", "UTF-8") 343 344 345def variant_flat_txt(): 346 variant_flat("flat-txt", "txt", False, ";", "'", ",", ".", "ISO-8859-1") 347 348 349def variant_mysql(): 350 ds = new_source("sdbc:mysql:jdbc:db.example.invalid:3306/shop", User="shopuser", IsPasswordRequired=True, 351 TableFilter=("shop.%", "!shop.tmp%"), TableTypeFilter=("TABLE", "VIEW", "SYSTEM TABLE")) 352 set_info(ds, JavaDriverClass="org.example.jdbc.Driver", LocalSocket="/var/run/mysqld/mysqld.sock", 353 SuppressVersionColumns=True, IsAutoRetrievingEnabled=True, 354 AutoRetrievingStatement="SELECT LAST_INSERT_ID()", AutoIncrementCreation="AUTO_INCREMENT") 355 set_info(ds, ServerTimeout=uno.Any("long", 30)) 356 save_new(ds, "base-mysql.odb") 357 make_query(ds, "Q_Orders", "SELECT * FROM `orders` ORDER BY `id`", UpdateTableName="orders", UpdateCatalogName="shop") 358 finish(ds, "base-mysql.odb") 359 360 361def variant_settings(): 362 ds = new_source("sdbc:odbc:SettingsDSN") 363 set_info(ds, MyStringSetting="text", MyBoolSetting=uno.Any("boolean", True), MyIntSetting=uno.Any("long", 42), 364 MyDoubleSetting=2.5, MyListSetting=("a", "b", "c")) 365 save_new(ds, "base-settings.odb") 366 finish(ds, "base-settings.odb") 367 368 369def variant_oracle(): 370 ds = new_source("jdbc:oracle:thin:@db.example.invalid:1521:XE", User="scott", IsPasswordRequired=True) 371 save_new(ds, "base-oracle.odb") 372 finish(ds, "base-oracle.odb") 373 374 375def variant_odbc(): 376 ds = new_source("sdbc:odbc:ShopDSN", User="odbcuser", IsPasswordRequired=False, TableFilter=("%",)) 377 set_info(ds, SystemDriverSettings="DSN=ShopDSN;Timeout=5", UseCatalog=True, EnableSQL92Check=True, AppendTableAliasName=True, 378 IgnoreDriverPrivileges=False, ParameterNameSubstitution=True, NoNameLengthLimit=True, 379 BooleanComparisonMode=3) 380 save_new(ds, "base-odbc.odb") 381 make_query(ds, "Q_Odbc", "SELECT 1 FROM x") 382 finish(ds, "base-odbc.odb") 383 384 385def variant_ldap(): 386 ds = new_source("sdbc:address:ldap:ldap.example.invalid:389") 387 set_info(ds, BaseDN="dc=example,dc=invalid", MaxRowCount=uno.Any("long", 25)) 388 save_new(ds, "base-ldap.odb") 389 finish(ds, "base-ldap.odb") 390 391 392def variant_adabas(): 393 ds = new_source("sdbc:adabas:dbhost.example.invalid:MYDB", User="control", IsPasswordRequired=True) 394 save_new(ds, "base-adabas.odb") 395 finish(ds, "base-adabas.odb") 396 397 398VARIANTS = [("hsqldb", variant_hsqldb), ("dbase", variant_dbase), ("flat", variant_flat_csv), ("flat-txt", variant_flat_txt), 399 ("mysql", variant_mysql), ("settings", variant_settings), ("oracle", variant_oracle), ("odbc", variant_odbc), ("ldap", variant_ldap), 400 ("adabas", variant_adabas)] 401 402failed = [] 403for name, fn in VARIANTS: 404 if len(sys.argv) > 4 and name not in sys.argv[4:]: 405 continue 406 try: 407 fn() 408 print("OK", name) 409 except Exception: 410 traceback.print_exc() 411 print("FAIL", name) 412 failed.append(name) 413sys.exit(1 if failed else 0) 414