This is where the BIN Hackers and definition junkies discuss the inner workings of the EEC code and hardware. General tuning questions do not go here. Only technical/hardware-specific/code questions and discussions belong here.

Moderators: cgrey8, EDS50, Jon 94GT, 2Shaker

Post Reply
User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

feedback please - moving to SQLite database files

Post by tvrfan » Fri Feb 21, 2020 4:17 pm

For consideration and comments -

Do you like SAD806x and its graphical format ???

If yes, then this is the right time to ask the question about how to share data between different applications.
We already have xdef files for tunerpro (etc), I use plain text files for SAD (which doesn't truly store anything), and it looks like SAD806X uses its own
s6x files. I know I'm a bit of a dinosaur with my command line version of the world, and I need to learn more about graphical interfaces.

I've been wondering on and off for a while ...... Playing with sad806x last day or so might be a tipping point for me.
It's neat the way you can pick a table or function and view its attributes. So.........

What we do when analysing a binary is a repeated iteration of tinkering - a 'save point' of knowledge and then look for more, and add as we go.
What if we had a single database file for each binary ? Would that help ? What if you use several tools and swop between them ?

Sqlite is used by a big range of developers (firefox for example) and is a multiplatform standard, with graphical user tools and also code libraries for most languages - which means it could make a single transferable 'database file' of all knowledge assembled for a particular binary, and it could be copied between win, linux, android, mac, etc. with whatever tools all using the same file, (and bits used by one app can be safely ignored or shared by others)
inside this file is actually a true SQL compliant database. I must double check, but I believe Access, Excel, and Libreoffice equivs Base and Calc, all have direct access to SQLite files (you may have to add an extension), so you can examine and play with the data and knowledge in whatever format you are happy with.

SAD itself uses a lot of in memory data structures for 'binary chop' searches, which would suit a database perfectly (OK, perhaps a bit slower as it's file instead of RAM), and in some cases would make it easier for me to keep track of. It's hard to catch some data structures already, and I'm thinking of stuff to capture that may help.....(for example, for each subroutine - what registers it reads, so they must be set up on entry, what registers it changes, conditions,limits (which may change what tables it reads), etc etc. I admit at the moment the way SAD does some analysis means it will NEVER get all the data structures.... so I need a different method, and right now I'm at a loss . Also the idea of interactively identifying tables etc. would be nice.

So you could then import an xdef file (with a tool) and then use SAD806X or SAD or something else ..... and as features get added the file can have new internal tables added to store them., which won't affect anything else. (actually, we could even store the binary itself in the file too, say bank by bank to fix the ordering problem ... )

For example I just downloaded a sqlite db browser and examined some internal firefox configurations - all works fine....

(sorry if this is a bit long)

Just an idea but what do you think ?? Plus or minus ?? Any other ideas to go with this ?

Comments ?
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

jsa
Tuning Addict
Posts: 1157
Joined: Sat Nov 23, 2013 7:28 pm
Location: 'straya

Re: feedback please - moving to SQLite database files

Post by jsa » Fri Feb 21, 2020 8:51 pm

tvrfan wrote: Fri Feb 21, 2020 4:17 pm For consideration and comments -

Do you like SAD806x and its graphical format ???
I have used both at various times as well as the old BL disassembler.
SAD & SAD806x are my preferred tools.

Neither SAD or SAD806x give perfect results without the user intervening to control disassembly. Both give different results which can be compared to speed up understanding of a BIN. Much the same could be said about SAD 3.08 and 4.04, by using the strengths of both.

IMHO one of the greatest strength of SAD806x, is that it produces a definition file directly. That saves a lot of work transferring information from disassembly output to definition file. The trouble with Tuerpro is the limit on number of data streams. So using BE for a greater number of data streams, means that TP XDF is of very little help.

With SAD806x, where disassembly was imperfect, I found I was drilling down to the LST layer to resolve errors. The process of doing that felt more cumbersome than just using SAD 3.0x for disassembly. The other issue with an application defined GUI is that the positions, quantities and size of window pains are inflexible. The advantage of SAD's txt files is that one can have any GUI window configuration desired. I often have 1 DIR, 1 CMT and 1-4 LST windows open in whatever layout suits at the time. The number of open LST windows is driven by jumps and calls in code and the need to follow back and forth.

In my view the underlying cold start disassembly needs to be extremely good before burying the nitty gritty in a GUI.
If yes, then this is the right time to ask the question about how to share data between different applications.
When Pym started writing SAD806x, he put considerable effort into ensuring SAD DIR and CMT could be imported. If memory serves me correct, export was also provided. IIRC that was SAD3.0x syntax, but I don't think SAD4.04 would be a clean transfer.

We already have xdef files for tunerpro (etc), I use plain text files for SAD (which doesn't truly store anything), and it looks like SAD806X uses its own
s6x files.
XDF is in essence an XML format file. Having a quick look at S6X, it appears to be XML as well, which makes sense seeing as XML handling is already part of the code base for XDF support.

To view XML files I use;
* https://xml-copy-editor.sourceforge.io/
* https://www.microsoft.com/en-au/downloa ... px?id=7973
* Drag and Drop to Excel 2010 with varying results

I know I'm a bit of a dinosaur with my command line version of the world, and I need to learn more about graphical interfaces.
Well, it has to be acknowledged that not everything is improved with the clutter and confines of a GUI.
I've been wondering on and off for a while ...... Playing with sad806x last day or so might be a tipping point for me.
It's neat the way you can pick a table or function and view its attributes.
Export a listing, and have a good look at it.

So.........

What we do when analysing a binary is a repeated iteration of tinkering - a 'save point' of knowledge and then look for more, and add as we go.
Very much so. A bit like driving a wedge.
What if we had a single database file for each binary ?
That is a definition file/s, XLSX and XDF/ADX .
The end goal of disassembly is to tune and/or log.
Would that help ?
Yes of course, direct control of disassembly from definition files would be excellent. The existence of s6x suggests it is unattainable.
What if you use several tools and swop between them ?
Tool list;
* SAD 3.0x & 4.04
* SAD806x
* Notetab text editor
* XML editors noted above
* Ultra Compare
* Excel

The ultimate mega disassembler would need to provide all that functionality or in no way inhibit easy transfer of content to other applications.
Sqlite is used by a big range of developers (firefox for example) and is a multiplatform standard, with graphical user tools and also code libraries for most languages - which means it could make a single transferable 'database file' of all knowledge assembled for a particular binary, and it could be copied between win, linux, android, mac, etc. with whatever tools all using the same file, (and bits used by one app can be safely ignored or shared by others)
inside this file is actually a true SQL compliant database. I must double check, but I believe Access, Excel, and Libreoffice equivs Base and Calc, all have direct access to SQLite files (you may have to add an extension), so you can examine and play with the data and knowledge in whatever format you are happy with.
Sqlite is a bit of a learning curve in itself. I don't think it is as easy as copy & paste or drag & drop to transfer between applications. I like the KISS principle.

I think excel needs a Jet database engine extension installed.
Is the excel xlsx file a compressed XML?
XML has libraries available and is used as a data store as well.

SAD itself uses a lot of in memory data structures for 'binary chop' searches, which would suit a database perfectly (OK, perhaps a bit slower as it's file instead of RAM), and in some cases would make it easier for me to keep track of. It's hard to catch some data structures already, and I'm thinking of stuff to capture that may help.....(for example, for each subroutine - what registers it reads, so they must be set up on entry, what registers it changes, conditions,limits (which may change what tables it reads), etc etc. I admit at the moment the way SAD does some analysis means it will NEVER get all the data structures.... so I need a different method, and right now I'm at a loss . Also the idea of interactively identifying tables etc. would be nice.
Yep, sounds good, still need to be able to get your hands dirty with ease unless cold start disassembly is perfect.
So you could then import an xdef file (with a tool) and then use SAD806X or SAD or something else ..... and as features get added the file can have new internal tables added to store them., which won't affect anything else. (actually, we could even store the binary itself in the file too, say bank by bank to fix the ordering problem ... )
Well xdf, adx and s6x all walk, squawk and look like XML format files.
Don't like the idea of polluting the XML with a BIN, why not make the bank order a field or table in the xml.
For example I just downloaded a sqlite db browser and examined some internal firefox configurations - all works fine....
Yes, sqlite works, from my experience of crossing paths with a couple applications that use it.
I think XML might be a better fit.

Just an idea but what do you think ?? Plus or minus ?? Any other ideas to go with this ?

Comments ?
I think interoperability between SAD, SAD806x, BE and TP is highly desirable.
I think transfer to editing tools must be simple.
I think disassembly accuracy is the highest priority.

I think Pym's open to cross compatibility, likewise the author of TP.
While BE uses xlsx for the open def file format, it has various config settings in XML.
XML can be taken into excel, but would need manipulation to suit the BE xlsx layout.
Cheers

John

95 Escort RS Cosworth - CARD QUIK COSY ANTI / GHAJ0
Moates QH & BE
ForDiag

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Fri Feb 28, 2020 4:21 am

Hi John, Andy,
interesting subject. I will try to help, even if I have never used SQLite for now.
My experience on .s6x format, which is finally really recent, is often near this topic.

First thing was ability to have a simple format, portable, easy to load and to save,
this is why .s6x is a xml file, which can be serialized, deserialized quickly.
But, clearly, with this format, main part of time is used to manage lists,
find items in them, by creating unique ids and so on.
SQLite could be useful for that at least and other principles seems to be valid too.

Even without modifying cores of the disassembly tools at the beginning,
it will be easy to convert from SQLite to any tool format, technically,
but the main thing is to have the right information in SQLite db.

Yes, .xdf files (not encrypted) uses xml format too,
but many required items, required for a disassembly, are not present,
like structures, registers, routines or signatures.
I do not talk about what I call 'Short label'.
.xdf files use unique id, which often changes and in fact,
generates visual ordering in application, so not a good base for us.
What I call 'Duplicates' with SAD806x, is also in place to manage ability in TunerPro,
to have multiple different things at the same address, which is a bit complicated for a disassembly.

The perfect information would be an extension of TunerPro one, removing (or adapting duplicates),
replacing its unique Id with a real unique Id (which is address and bank in a strategy for me)
and adding all additional items, required for proper disassembly (routines, registers, structures, signatures, ...).
The unique Id is really important, because it is the key for synchronizing between different tools.

User avatar
cgrey8
Administrator
Posts: 11234
Joined: Fri Jun 24, 2005 5:54 am
Location: Acworth, Ga (Metro Atlanta)
Contact:

Re: feedback please - moving to SQLite database files

Post by cgrey8 » Fri Feb 28, 2020 6:19 am

The bank selection is nothing more than overcoming the 64k limitation of the 16-bit processor. As such, if there are 4 banks, then that would mean you essentially have 18-bit addresses, where the most-significant 2 bits are the bank addresses. The processor has to work with banks. But is there any good reason why the defs do? Why can't the defs (and the software that uses them) be modified to understand that 0x19999 represents 0x9999 in bank 1 and 0x29999 represents 0x9999 in bank 2? Would that make things any easier? Or does that just add another layer of complication to an already complicated situation?
...Always Somethin'

89 Ranger Supercab, 331 w/GT40p heads, ported Explorer lower, Crane Powermax 2020 cam, FMS Explorer (GT40p) headers, aftermarket T5 'Z-Spec', GUFB, Moates QuarterHorse tuned using BE&EA

Member V8-Ranger.com

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Fri Feb 28, 2020 2:28 pm

My answer from a disassembly point of view -

I agree that the simplest universal address system would be to use 5 hex digits.

The CPU itself is only a 16 bit unit. It expands by keeping the top 4 bits of its address banks in separate places, and adding them by default to the 16 bit address operations. When going through a binary for disassembly as SAD does, it has to keep a 'default data bank' and a 'default code bank' at all times, and provide an override for when the opcode has a bank prefix.

I have seen several different expand methods like this in my IT career with computers that had 18, 20 and 24 bit address ranges with a 16 bit CPU, and different ways to get at this extra memory.

SAD treats ALL addresses as FIVE hex digits (0 - 0xFFFFF) already, where the highest digit is the bank. (except 0-0x3ff for registers)
SAD is designed to handle 16 banks. It currently limits this to 0,1,8,9 only to match what we see in the handbook.
Internally SAD treats a single bank binary (and all 8061) as a single bank 8. It does not show this bank in printouts but silently adds it to commands.


The only complication would be how to handle single banks versus multiple and backwards compatibility (effectively where 0x1234 could mean either 0x01234 or 0x81234). SAD knows by checking if there is more than one bank. I guess the xdf, s6x, etc. must have similar arrangements

Ford chose to use only two bits of the bank, but the 8065 can handle 4 bits, so therefore it's wise to just support the full 5 digits.
Then we know for sure it will cover every possible layout.
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Fri Feb 28, 2020 5:22 pm

It seems we all agree with key/id/unique address, based on a bank address on 1 digit and address in bank on 4 digits.
It is not required with SQLite to have this in a five digits format (0xfffff), it could be stored in 2 different fields,
this is how I am already processing it, even if I use an additional unique id with everything combined
(a string including base 10 values, to sort them properly).
TunerPro does not manage banks, it is using pure address in binary file.
For all items not related directly with addresses in binary, I use different and dedicated keys, in dedicated objects.
For registers, even if they are not direct addresses in binary, I use their address on 4 digits (with some 0x1zzz addresses).

Finally for duplicates, I just add ".###"' to the first item key, but they are stored and managed in different lists, to let
the disassembly (and the output) work only on main defined items.

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Sat Feb 29, 2020 5:35 am

Please find attached, what could be a good starting point for a database.
It has to be enriched, but always keeping in mind that, it is not dedicated to disassemble files only.
The first objective is to write a complete definition for a strategy.
Additional tables will be added for each tool, to permit to map this 'generic' format to all tools.

If you prefer a version with some data, I can prepare one.
Attachments
806x Template.db
(128 KiB) Downloaded 545 times

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Sat Feb 29, 2020 6:31 pm

I just want to be clear here, so there is no misunderstanding.

It doesn't actually matter how you choose to store your data internally (hey, it's YOUR program after all).

I am only aiming for a shared db format which is as simple as possible (KISS principle), for the easy sharing of the DECODED CALIBRATION DATA and CODE ALGORITHMS. So it helps to understand what each binary does, and how to modify it if you want.

Anything else is secondary.

Sure, any shared DB is almost certainly going to have data which is not used by some programs, and that's fine by me.
I think storing the binary data itself in the DB, bank by bank might be a good idea too. Perhaps not.

An example with the addresses, I would stick with 5 hex digits, because we all know that the top digit is the bank, so it's simple.
The only thing extra we need is the number of banks to know whether that 5th digit is a valid bank when it's zero.
This would be most backwards compatible for utilities which still use 4 digits, or are single bank only.

Or we can go for an 0x8nnnn format, but personally I don't like that for sharing (even though that's what SAD does internally).
Or we can enforce a rigid format of 0x08888 versus 0x8888 (I don't like that much either, too error prone)

But it's all up for discussion.

Pym, - I tried opening that .db file but my SQLite explorer program gives me an error, even though a binary check shows file says "SQLite format 3" at the top. I managed to get the text out another way though, and that looks like a good start for the schema.

I can see some extra fields required already ...

For tables we would need row and column size and element size out (signed or unsigned, byte or word), Yes, they are all bytes so far, but....
For functions we would need element size in and out (i.e. signed/unsigned, word or byte),
For everything we would need a divisor/multiplier to convert to 'real' values (like AD voltage to degrees C (or F) and so on.
Probably time too (e.g. CPU ticks -> millisecs).

There are also STRUCTURES (e.g the A9L injection table) which have a mix of data types (pointers, bytes,words, bit masks,...) so we would need somehow more actual data structure information as well.

Subroutines can have arguments, which are effectively a STRUCTURE. (That's the way SAD does it anyway)

Hope this helps , that's the way it is intended.
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Sun Mar 01, 2020 3:03 am

The provided file was created with SQLiteStudio, yes with the last available format (I think) SQLite 3.
I have to understand what are compatibility issues with that.

I add the SQL version, you will be able to recreate a new database with that.

And you will see which calculated unique id, I have managed for now for classic elements,
even if I store separately bank, address and iteration (0 for main and other things for duplicates).

From my point of view, the core should be dedicated to creation of the best possible definition.
It should be as generic as possible and I insist on this duplicates management.
First additional layer should be to help disassembly tools to do their job.
And the last layer, will be for everything else, like included binaries and so on.

Andy, you will find everything you need for tables, functions and scalars, in this sql creation script.

Additional data, like the category (1 to 3 like TunerPro) or some kind of certainty percent should certainly be added.
But I am not happy to find everything like this in flat db tables,
for example units and scaling(divisor/multiplier) should certainly be managed in foreign tables,
to be able to manage personal choices without having to update this part.
For example, I always want to see speed in Km/h, but I prefer to duplicate element to prevent updating the original one.
Same thing for temperature in °C, MAP in Bar or Lambda visible as AFR.
So yes a foreign key to a default scaling and units, based on what exists today and this key giving children for personal understanding.

For structures, yes, no issue, but the structure definition format has to be generic too.
Mine is not perfect, but includes many things like conditional parts.
I have not included all details in DB structure for now.

Routines and operations are present too, bit with no details for now.
Yes, arguments will be necessary.
Attachments
806x Template SQL.txt
(12.51 KiB) Downloaded 557 times

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Sun Mar 01, 2020 2:22 pm

Pym,

My extract of the original DB file left out some fields I think. So my extra fields comments are wrong.
The text one is much better.... thanks.

I seem to be getting an error with " Skip BOOLEAN DEFAULT (False) " in the create table in the linux versions of SQlite tools.
"Error: default value of column [Skip] is not constant" which is strange, I don't see an error in that syntax.

It works if I drop the default part.
I'll have a detailed look at your schema and reply later.
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Mon Mar 02, 2020 3:36 am

Oh damn..........., I don't know how to say this gently, so I will not....

I HATE, HATE, HATE, DATABASE TRIGGERS.
THEY ARE THE INVENTION OF THE DEVIL.
THEY VIOLATE THE KISS PRINCIPLE.
THEY NEARLY ALWAYS EXIST TO COVER UP BAD DESIGN.

Truly, I don't understand why you need them at all for a simple DB, which this will be - it's really only a bunch of directives and information about the binary code and calibration data.

Sorry Pym, but I had to say that. Why do you have triggers ?? Why do you recalculate this "UcalcID" field all the time ?

What I see is that there is a natural PRIMARY KEY, this being start address, or start address + bank, as is your preference. There are some items which will need a different PK but not many, and they can use ROWID or a simple sequence number. If you want to keep versions, then add a DATE to the PK, or a version number.

So I am confused by that schema ...
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Mon Mar 02, 2020 8:19 am

You are right, I prefer to never use triggers too,
but I have not found for now (yes I have not really searched for)
another way to manage on the same time a proper primary key
on multiple fields and a concatenated field to be easily declared for foreign keys.

As I have described, we need a shared unique key, which can be generated by different
software, at different times, by different users and which should always give the same value.
So a timestamp, a row id or a sequence number are not the best option.
The iteration field is something like that, and it works perfectly for main key with a defaulted 0 value,
it is less generic for duplicates, but I have not found anything else for now.

I still have to see, which compatibility mode is the best and if sql functions could be added,
to get this calculated field directly or to get bank, address and iteration extracted from it.

From my point of view, it could also be possible to edit directly values in the database,
so some basic should be implemented.

User avatar
cgrey8
Administrator
Posts: 11234
Joined: Fri Jun 24, 2005 5:54 am
Location: Acworth, Ga (Metro Atlanta)
Contact:

Re: feedback please - moving to SQLite database files

Post by cgrey8 » Mon Mar 02, 2020 8:51 am

In college, they always taught us about natural primary keys. And here at my office, we built our product's database on what seemed obvious to us as a primary key. Then we had scenarios where those keys needed to change from time to time. And that's where the problems began.

What we've learned is to go against the grain, and have all tables primary key made up of an arbitrary UUID that actually has no meaning to the rest of the application, isn't visible to the user, isn't editable, and is only used in inter-connecting data/tables within the scope of the database. Since doing this, we've not had any further problems with common things like upgrades, exports, imports, and merges of databases. That doesn't mean we haven't had our own application-related issues of coordinating our own data. But at least we don't have near the low-level database management issues we had when a component of our actual user-facing data was also used as the primary key in our databases.
...Always Somethin'

89 Ranger Supercab, 331 w/GT40p heads, ported Explorer lower, Crane Powermax 2020 cam, FMS Explorer (GT40p) headers, aftermarket T5 'Z-Spec', GUFB, Moates QuarterHorse tuned using BE&EA

Member V8-Ranger.com

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Mon Mar 02, 2020 1:49 pm

Pym, thank you for taking my comment in a positive way.

My DB experience -
I did quite a few database designs for various systems (Police, govt ministries, commercial product tracking).
This had different design aims, but all stored the various kinds of information, text (= comments), semi-formatted (addresses, names, doc references), and 'lookup' or strict format types (status, priority, barcode ID). I never used a trigger, anywhere. We did have stored procedures, but I never called one from another, to keep it as simple as possible.

We simply used the inbuilt ROWID where we did not have a suitable primary key. Unfortunately this 'virtual' rowid cannot be used as a foreign key in SQLite which is a shame.

But basically all we need is a unique combination of fields for our primary keys. It doesn't matter what.

Address [Address+bank] is an obvious one, as there can only be one thing at one address. That has to be (IMHO) the most obvious. In this particular design, it doesn't matter that the primary key is visible to the users, it still has to be unique.

Some tables won't have addresses, so we will need to think of something. For example, if we store each binary bank as a BLOB, then bank number can also be its primary key. As I said above, if we want to keep versions, we either have a date or a 'version number' added to the primary key.

Honestly, I can't see why that would not work.

[oops - realised later - just in case, this idea defines that we would have one db file per binary, otherwise addresses would not be unique]
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Mon Mar 02, 2020 3:52 pm

I prefer one db file per strategy and it was a good idea, to include the different binaries in it, different versions or different bank orders, anything available, but still compatible. This is why I have added this 'binaries' table.

I was working on an ecu pinout template too, this morning, not for a database but it could be a good idea too.
In fact everything related with strategy is a good idea and why not generic attachments.

To come back to this unique id, from my experience each software uses its own unique id type, not visible for users.
It is the unique primary key and this is the software which creates the rules on the other fields; which become the logical primary key.
So yes for me, no technical primary key should be required, no unique id, which is ok for a database used by one software only.
With a shared database, it is required to have a rule reused by everyone, if it is not coded into database,
not sure it will be applied by tools and this is the main issue, to keep compatibility.

Andy, [Address+bank] key is not enough for me. With TunerPro you can create 2 items at the same address.
I have reproduced this principle with SAD806x and its duplicates.
On disassembly, even if I hate this, some scalars are sometimes used as word and sometimes with low byte and top byte,
so I have to manage this in definition, same address one word or one byte and another one.
Same thing with 'fe' operations, sometimes used with or without, but for me this is the same operation at the same address, but with 2 variants.

So the best thing to do it to let the tool manage everything, no primary keys (we will have to add indexes), no foreign keys, no constraints at all.
But each tools will have to manage creation and updates properly and will have to be able to load data even if some records are not properly filled.
I have removed UCalcId, which can be managed internally in each tool, but I keep 'Iteration' modified to 'UniqueAddCode' to let more freedom to the tool.
For me binary internal items will always be identified as unique with 'Bank', 'Address' (start address) and 'UniqueAddCode (Empty for main elements, filled for duplicates).

Andy, you are talking about tables without addresses, you are thinking about signatures, otherwise, no need to store them in definition or in directives.

I have rebuilt everything in Sqlite2, I hope it is more compliant, but sql script is still there.

Next step, being able to work with this type of base, without adding tons of setups to the tool, just a basic dll.
Same principle for me, compatibility from Windows XP to Windows 10 (32/64) (and next versions) without installing anything else.
Attachments
806x Template SQLite2.txt
(4.76 KiB) Downloaded 534 times
806x_SQLite2.db
(46 KiB) Downloaded 534 times

jsa
Tuning Addict
Posts: 1157
Joined: Sat Nov 23, 2013 7:28 pm
Location: 'straya

Re: feedback please - moving to SQLite database files

Post by jsa » Mon Mar 02, 2020 4:23 pm

tvrfan wrote: Mon Mar 02, 2020 1:49 pm
Address [Address+bank] is an obvious one, as there can only be one thing at one address.
How does that pan out for scratch registers that are assigned different values for different address ranges?

How about something like MAP that might be defined multiple times for units of measure Hg", PSI, KPa and Bar.

How about bit flags, the address for the byte and up to 8 repeats for the bits.
Cheers

John

95 Escort RS Cosworth - CARD QUIK COSY ANTI / GHAJ0
Moates QH & BE
ForDiag

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Mon Mar 02, 2020 5:56 pm

Right - I'll do this backwards, JSA first. Remember that a Primary Key can be multiple fields.

Scratch registers - I'm assuming you mean different NAMES/SYMBOLS, but anyway (Register_addr + Range) is then a primary key.
Again, MAP (or its equivalent register whatever) becomes (address+Unit type) for primary key
Flags - guess what I'm going to say ??? Address+bit is a primary key, if you use a construct like a whole byte/word symbol has a bit number of -1 (or 32, or 100, or something). In SAD, I internally store (bit+1) and shift the address up by five, so zero is <no bit>, and bit 0 becomes ADDR+1, so I also get free ordering of the symbols in one integer value.

By combining some fields, it's still reasonably easy to come up with a unique ID.

Pym - Ahh I see. I did not realise that some tools allow multiple items at the same address (why though ?) It doesn't seem to make sense to me, but if that's the way they work, then I agree we do need something else, or something extra added to make it unique.

So that messes up my nice clean model already......Same applies for jsa's points above (which is why I did them first).

Professionally, I've always used either the internal ROWID, or a simple ascending integer sequence (with "SELECT MAX(PK column) from TABLE" for the next insert, as it is a very fast lookup as a (long) integer, because it will be indexed as a primary key....OK, you do get 'holes' for deleted rows, but it's never been a problem.

Words versus bytes. Honestly I haven't seen anywhere (so far) where a binary uses a 'bottom' byte interchangeably as as byte and a word, EXCEPT
for math operations (e.g 'top' byte as 'bottom' byte is x/128 and such tricks). Do we need to reference those ??

Sure, scratch registers ARE used as both bytes and words, in separate places, so could be covered by address ranges.
There is some fiddling around to get addresses (e.g. in subroutine arguments), but it's routine stuff.
(But I might be wrong on that... so I understand your point.)


The 0xfe prefix "NO it's worse that that Jim" to quote Star Trek..... for multibanks, you can have sequences of [0x10, <bank>, 0xfe, opcode], as well.

In SAD I decided to treat the whole thing as one opcode, so every opcode possibly can have 3 prefix bytes, with a maximum size of 8 bytes. This is 'strictly correct' I guess. SAD lists the bank prefix as if it was separate, for ease of reading, but it's not really.

Now I WOULD have indexes where -
You need to find something quickly (or often)
You need to enforce uniqueness ( primary and foreign keys fall in here).
And I would think about using foreign keys where the linkages are important, as they effectively are a 'free check' and don't require any effort once set up.

But thanks for that, we do need to make sure everything is covered.

Note. In case you didn't know, I'm 95% sure that the 'big' database engines like Oracle, Sql Server, Informix, etc. actually create an index for each field defined as UNIQUE and so use the same logic as a primary key, so you get an index in any case. I suspect SQLite will do that too.
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Tue Mar 03, 2020 6:04 am

We will see later for indexes, if required, but because I have removed primary keys, no default one.
Each tool is free to work like it wants, even with duplicated lines.

Bad news, functions/stored procedures can not be embedded,
they will have to be created by the tools for each connection.
It will not be a great deal and the use of views could be an idea.

I have tested different formats, SQLite3, SQLite2, System.Data.SQLite.
From SAD806x, I can easily work with SQLite3 and System.Data.SQLite,
not SQLite2 without additional libraries.
If you know differences, it is interesting, because we have to select the right format.

Some updates on database template, for a new proposal :
- 806x_ tables are core tables, shared by all tools.
- TunerPro_, SAD_, SAD806x_ tables are extensions for each tool.
- _DB_Information tables are related with version which can evolve separately, tool has to know them.
- New fields, tables and repositories have been added
- Category fields could be free text or repository link, no foreign key, it is free.
- Status works in the same way and is the status of identification for the item (or for the whole strategy).
- Units works in the same way.
- ...
Attachments
806x Template 20200303.txt
(10.97 KiB) Downloaded 551 times
806x_SQLite3 20200303.db
(180 KiB) Downloaded 529 times

User avatar
86GT
BE/EA Developer
Posts: 5138
Joined: Sat Feb 22, 2003 11:19 pm
Location: Dixon, California
Contact:

Re: feedback please - moving to SQLite database files

Post by 86GT » Tue Mar 03, 2020 12:29 pm

I have not read all of this as some of it is over my head. I dont get into the disassembly much as that is over my head too LOL. When BE was designed it was for my personal use only and has since migrated over the years. BE was originally intended to be as open as possible on the strategies for ease of maintenance. It used XLS in the early days and then started using XLSX for the compression.

Then as time went on people wanted to encrypt their hard work and thus the CRY files came out. This presented a challenge for me to allow people to encrypt there own strategies and allow them to license them. In the end it has worked out for a few which is good.

Next came along the database. Working with Core Tuning we came up with a database format that allows for different strategies to share common things like the descriptions and comments and few other things. This has really streamlined things. It allows us to make a simple change in the database and it propagates to all strategies. It is powerful but dangerous at the same time.

As for the addressing scheme the first 2 numbers would work as the bank configuration but that only works on the EEC4 and EEC 5. Keep in mind BE is a Bin editor and it edits as a raw file no mater where it came from. The Power PC and the Copperhead and the TriCore are all supported too and they do not address like the EEC did.

Now in the XLS you can turn on the logical addressing which address it like what you guys described above. It will use the bank number in the 5th digit. I think Derek (Sailor Bob) wanted this years ago and it is still in BE. Just keep in mind this will not work in any of the PPC and above processors.

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Tue Mar 03, 2020 3:42 pm

86GT - Thanks for that.

I started off by suggesting the SQLite system because it's available on almost every platform, and it allows the binary itself to be stored within that same database. Therefore only ONE file could hold everything for everyone. It's a worry already though that the Linux versions are producing errors against Pym's Windows versions. That should not happen.

NB. Happy to provide a quick plain English (and I DO mean quick) description of what relational databases do - they are actually simple in concept, despite all the fancy jargon people use to make it all seem more mysterious.

................BUT.............

Honestly, if we have another method, that's also perfect as long as it's portable between apps and platforms.
That's what I posted this thread for - different ideas and info on how to share.

I did wonder about an XML type text format. It's just a big list of formatted stuff in text, lots of "[category] item" entries, and it can be nested down as much as you like, think [subsubsubcategory] item). The downside of XML is it tends to be slow (but hey we've all got multicore devices right?) and has to be read into your own internal program data structures. You can't store the binary in it (as far as I know ? perhaps you can?). XML format has the benefit that if your program/app doesn't care about a particular [category] it can just ignore that. It's also just text, so very, very, portable.

XLS spreadsheets are portable too, and when dumped as CSV, even more portable. For what we want I guess it would look like the XML more or less. But we would have to agree a format, which might be trickier with a spreadsheet or CSV format. Open to those ideas too.

I have an A5.xdf in my collection of stuff (can't remember where I got it) and in a lot of ways, that's very XML like already.
and the GUFB.xlsx I have also could be changed easily I think. As it's got title headers, they could become the [Category] type defines for example.

But hey, these are just ideas, not saying anything is right or wrong ....
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

User avatar
86GT
BE/EA Developer
Posts: 5138
Joined: Sat Feb 22, 2003 11:19 pm
Location: Dixon, California
Contact:

Re: feedback please - moving to SQLite database files

Post by 86GT » Tue Mar 03, 2020 6:14 pm

Getting all of the different software manufactures to agree on a format is most likely not going to happen. Each one has a different name for different things and so on. There is a part in the XDF that is specific to Tuner Pro just like the config tab in the XLS is specific to BE and others have specific things to their own software.

Yes those could be removed and put into a separate file but that is not good either. This would mean multiple files for the same strategy (Not Good). Thing like KAMRF tables are defined in the strategy file (XLS) and software like tuner pro do not use these tables. Why would tuner pro have things in a shared file that they dont use and vise versa why would BE have things in a shared file that it would never use. I wish it was something simple but it is just not that easy.

Also putting the Binary files in a databases is a good thought, but the database file gets very large very fast if not compressed. Even with compression on databases can be in excess of a gig or more. Having a flat file is the best option if you are going to share info from software to software. Databases are harder due to the nature of the table relations. Software vendors would have to be on the same page during the initial design of the relations to make it more efficient. .

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Wed Mar 04, 2020 4:59 am

I do not see this idea like this. Replacing directives or definition files of each software, is not my goal,
but creating a common file or database, which can be imported, exported and synchronized in each tools,
is far more interesting.
The real goal is to be able to enrich definition or directives, simply. The data is in this database,
you can use it in your tool or not, your tool has not this type of data, it is available if wanted.
I will continue to use my own format, but I will make it compliant with this common core.
I will add some specific tables in it to be able to easily adapt this common core, to what I need to use.
But my own specific formats, will stay in the tool only, until it becomes useful for someone else.

I agree with you, text files are easier to open everywhere, like csv, xml or json. Performance issue is not existing,
because I suppose you load everything, into your own memory schema, but it does not permit to store complex or big data
and it is too easy to edit manually generating issues in tools using them.
I do not talk about Xls format, which is always for me hard to implement and absolutely not usable everywhere.

A database can be a good thing, if it can be used and edited everywhere. This is the main key to check.
It really permits to include things, which would not be present in other formats. For its size, yes, the goal is not to include all your own variants for the strategy, oem binaries are enough + some really validated tuned ones, 10x 256ko should be managed and with other attachments, a really complete
database should reach at maximum 10mb.

Andy, talking about your issues on Linux, this is still related with SQLite3 ?

User avatar
tvrfan
Tuning Addict
Posts: 581
Joined: Sat May 14, 2011 11:41 pm
Location: New Zealand

Re: feedback please - moving to SQLite database files

Post by tvrfan » Sat Mar 07, 2020 9:57 pm

I could not think of a good reply, so instead I have posted a DB version of what SAD uses for its internal data structures when disassembling.
Of course this is quite different to an 'information database' which will have a lot more comments and user based items than a plain disassembly would.

SAD currently uses ordered pointer lists to items (C structures), and searches via the classic "binary chop" algorithm.
So in many ways this is quite 'database like' already.

If I was to move SAD to a SQLite setup, these are the tables and layouts I would use, there may be a few odd fields not covered here.

These tables mostly use ADDRESS as their primary key, which is the 5 digit version, so includes the bank.
I know this would not work for some apps as we discussed.

Where there are dependent tables, I've added an ID field as a Primary key, and the subtables refer to the ID.

The two subtables are my way of defining a list of different data types which may occur, with LEVEL being used to order the cells into a row.
This is how I define subroutine arguments and data structures like the injection table (the A9L has a collection of pointers, bitmasks, words and bytes
in each row, so this is necessary). This equivalent setup works well for SAD

I hope this at least gives a different viewpoint.

The comments table is for comments which would be added to the listing, not for each table or function etc, so have only an address and sequence
(to allow more than one comment per address)

Also this is a chance too check if the Windows tools work with a linux based DB..
Attachments
SADsql.txt
(2.12 KiB) Downloaded 360 times
SAD.db
(52 KiB) Downloaded 356 times
TVR, kit cars, classic cars. Ex IT geek, development and databases.
https://github.com/tvrfan/EEC-IV-disassembler

Pym
Gear Head
Posts: 10
Joined: Sat Feb 22, 2020 5:06 pm

Re: feedback please - moving to SQLite database files

Post by Pym » Tue Mar 10, 2020 2:47 pm

No issue to read your file, with system.data.sqlite (from SAD806x), SQLite studio or DB Browser for SQLite.
No real information on its version, SQLite studio shows it as SQLite3, but I am not so confident.

I knew your model was globally looking like this, mine is much more extended for each type of object,
but that is my issue, I always split everything ...
For comments, I know it is a bit complicated. Definition based tools require comments at elements level,
which is the way I have chosen too, but you principle has some advantages like flexibility.
On my side, I have a kind of 'Other Addresses' part to permit this flexibility,
which could be equivalent to your comments, if I do a bit of work. We will review this part.

Everything confirms what I was thinking about, this database requires a common part ('806x_*' tables in provided file)
and specific tools ones ('SAD_*' for SAD, 'SAD806x_*' for SAD806x and so on),
to permit conversion and compliance for a direct use. This common part is essential,
because it should let all tools being able to convert data for themselves, by their own tables,
which will be extensions or model compliance ones. No common table should prevent a tools from using its data.

With some data, it will be more interesting. Definition used is coming from Michael and is already available for everyone.
It is probably not up to date. Please tell me if you have issues to read the file.
Attachments
806x_SQLite3.db
(828 KiB) Downloaded 354 times

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests