0 votes

Hi,

To install a custom module to an other base I have to create lookup tables in the new base.
Since the dbstaging doesn't work fine in this context (the iscustom field is not taken into consideration plus the keygen is not managed by this tool and since in the new base the Key of the lookup table is not the same)

I had to make it work so this is the script I finally used. Please note that The keygen will works now, plus the ISCUSTOM field issue has been fixed as well.

I would recommand to perticulary look after the use of ADMIN user and schema in my code because this part is not dynamic.

NB: I chose to ignore the sys label of my table since it's a configuration table

So far it works fine (on MSSQL) cheers,

EXECUTE AS USER = 'ADMIN'

create table LK_DP_HYPERLINKS (
    K_DP_HYPERLINKS INTEGER NOT NULL,
    K_SORT INTEGER NULL,
    K_LABEL INTEGER NULL,
    DISABLED VARCHAR(1) NOT NULL DEFAULT '0',
    F_VALUE NVARCHAR(128) NULL,
    F_URL NVARCHAR(255) NULL,
    F_SECURITY NVARCHAR(32) NULL, constraint PKLK_DP_HYPERLINKS Primary Key (K_DP_HYPERLINKS)
)

declare @K1 float
exec admin.GETNEWKEY @FIELDNAME = 'K_TABLE', @NEWIDVAL = @K1 OUTPUT

Insert into ADMIN.SYS_TABLES (K_TABLE, NAME, D_CREATE, D_CHANGE, KIND, K_LABEL, RANKING, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 'LK_DP_HYPERLINKS', GETDATE(), GETDATE(), 'L', NULL, '0', GETDATE(), 0, '1')

   Insert into ADMIN.SYS_FIELDS (K_TABLE, K_FIELD, NAME, D_CREATE, D_CHANGE, DATATYPE, DBALLOWNULL, USAGE, ALLOWNULL, LABELPOS, SEARCH, QUERY, PUBLISH, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 1, 'K_DP_HYPERLINKS', getdate(), getdate(), 'I', '0', 'K', '0', 0, '0', '1', '0', getdate(), 0, '1')
Insert into ADMIN.SYS_FIELDS (K_TABLE, K_FIELD, NAME, D_CREATE, D_CHANGE, DATATYPE, DBALLOWNULL, ALLOWNULL, LABELPOS, SEARCH, QUERY, PUBLISH, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 2, 'K_SORT', getdate(), getdate(), 'I', '1', '1', 0, '0', '1', '0', getdate(), 0, '1')
Insert into ADMIN.SYS_FIELDS (K_TABLE, K_FIELD, NAME, D_CREATE, D_CHANGE, DATATYPE, DBALLOWNULL, ALLOWNULL, LABELPOS, SEARCH, QUERY, PUBLISH, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 3, 'K_LABEL', getdate(), getdate(), 'I', '1', '1', 0, '0', '1', '0', getdate(), 0, '1')
Insert into ADMIN.SYS_FIELDS (K_TABLE, K_FIELD, NAME, D_CREATE, D_CHANGE, DATATYPE, DBDEFAULT, DBALLOWNULL, DEFAULTVALUE, ALLOWNULL, LABELPOS, SEARCH, QUERY, PUBLISH, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 4, 'DISABLED', getdate(), getdate(), 'L', '0', '0', '0', '0', 0, '0', '1', '0', getdate(), 0, '1')
Insert into ADMIN.SYS_FIELDS (K_TABLE, K_FIELD, NAME, D_CREATE, D_CHANGE, DATATYPE, DATALENGTH, DBALLOWNULL, ALLOWNULL, LABELPOS, SEARCH, QUERY, PUBLISH, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 1924, 'F_VALUE', getdate(), getdate(), 'A', 128, '1', '0', 0, '0', '1', '0', getdate(), 0, '1')
Insert into ADMIN.SYS_FIELDS (K_TABLE, K_FIELD, NAME, D_CREATE, D_CHANGE, DATATYPE, DATALENGTH, DBALLOWNULL, ALLOWNULL, LABELPOS, SEARCH, QUERY, PUBLISH, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 1925, 'F_URL', getdate(), getdate(), 'A', 255, '1', '0', 0, '0', '1', '0', getdate(), 0, '1')
Insert into ADMIN.SYS_FIELDS (K_TABLE, K_FIELD, NAME, D_CREATE, D_CHANGE, DATATYPE, DATALENGTH, DBALLOWNULL, ALLOWNULL, LABELPOS, SEARCH, QUERY, PUBLISH, D_REPLIC, K_REPLICUSER, ISCUSTOM) values (@K1, 1926, 'F_SECURITY', getdate(), getdate(), 'A', 32, '1', '0', 0, '0', '1', '0', getdate(), 0, '1')
asked in Efficy/ Client side by (983 points)

1 Answer

0 votes

Hi Louis

If you connect your database connection using SQL Server Autentication with logon ADMIN and your password, you don't have to prefix the schema.

The DbStaging utility is designed to help with staging your database from development all the way up to production. It's not designed to isolate and extract specific features or developments from one DB to a totally different other DB.

If you consider this lookup as part of a "custom module" that you want to apply to multiple Efficy installations, you could consider to attribute a K_TABLE and K_FIELD value in a high number range to avoid collision with existing or new custom and with standard objects.

Anyway, I would drop the actual CREATE TABLE statement (and any other DDL statement) because if you update the database dictionary tables (SYS_*), the DbUpgrade or Conficy Apply Structure operation will create the DDL for you on the spot and then you are sure that the physical database objects will match their definition.

answered by (6.9k points)
that sure will help. thank you
I'm glad you share your experiences on overflow.

As you might have experienced, Efficy is quite difficult to learn. It's a proprietary framework with a lot of possibilities to do things right, but even more to do it wrong. Wrong in the sense of "not designed to be used like that"

The more interesting questions we have on overflow, the bigger our question base is for our developers!
1,173 questions
1,433 answers
1,726 comments
325 users