Database Manager Engine
| |
AxleBase
Database Manager Engine |
|
|
home page description documentation requirements inquiries policy letter |
Relational Database Manager Very Large Databases Program Interface SQL Driven Embedded Documentation ( Please scroll down. ) |
free system change log demonstrator license support |
|
__________________________________________________ Copyright
__________________________________________________ Copyright __________________________________________________ AxleBase
AxleBase Documentation
AxleBase Web Site
_________________________ System License License to use AxleBase will be granted only upon the individual user's agreement to the license terms which are published on the web site http://www.axlebase.com/license.htm . The license in effect at the time of agreement is carried within the AxleBase system and is always available on demand.
__________________________________________________ Chapter 1 Introduction __________________________________________________
_________________________
Introduction AxleBase is a complete database manager and is one of the most powerful in existence. But he is described as an engine because he was created to be compiled into software projects. His primary objective is the very large. He is not designed for the usual mid-size databases. AxleBase does not just claim to be embeddable. He was built for that purpose so that he lives and functions entirely within a host system. He is a DLL component which is invisible and which a programmer can compile into a product. The host will use industry standard ANSI SQL-92 to control him. He has SQL enhancements to handle very large objects, but they do not interfere with standard SQL, and when a deviation is made from the standard, the deviation is openly and explicitly stated in this documentation. He provides a universal interface that can be tied into any production system. That and his light weight make him easily deployable. AxleBase's design target is Mankind's largest databases. He contains special mechanisms that give him the ability to handle unbelievably large tables. A secondary objective is cost reduction. Instead of using a super-computer or mainframes to handle very large databases, he is designed to do it on low cost P.C.'s under Ms. Windows. AxleBase is designed and internally coded for very large tables. Mid-range OLTP operation with heavy concurrency is not an objective and a big-name server may be a better choice for that kind of database.
Introduction A database manager that
( * Data tables and control files are totally visible to their owners.) ( ** If you know how to use a DLL in a program, then you can embed AxleBase.)
Introduction Version numbers: Version numbers became nearly eufunctional after the billionaires started playing marketing games with them. Therefore, AxleBase version numbers serve mainly to uphold tradition. The version is always stored in the product. A command is available for displaying the system's internal version number. Release numbers: Release numbers have been the best guide to the current level of my work for the past twenty years. They are seldom consecutive, but they are always sequential. They have been a five digit integer for years. A new release number is simply and truly the identification of the system that was released to the public. It may be for a major change or for minor cleanups. The release number is stored in the product and a command is available for displaying the system's internal release number. The web site download page shows the release number that will be downloaded. The web site change log page carries a history of changes showing the release number in which each change was made. SAM versions: The storage architecture model (SAM) version should usually be of little or no interest. It is mainly for internal use, but is made public because it can be seen in system files. The SAM has its own versioning identifier. Each time that the SAM is changed, it is assigned a new identifier which may or may not be the same as the AxleBase version or release number.
Introduction AxleBase is designed to these limits.
Introduction The concept of "very large" has historical precedence in database work. It was first applied to databases as "VLDB" and later applied to individual tables as "VLT". The concept has remained the same through the years to mean a larger than ordinary object, but its application has changed as hardware and software changed. That which was a very large table before the PC revolution is now routinely managed in a desktop database manager. It is, therefore, a relative and changing concept. This documentation, however requires a means of distinguishing between normal-sized objects and very large objects. Almost all tables around the world and in all hardware are normal-sized objects. AxleBase is designed to handle larger than normal objects. Therefore, this documentation requires the use of the concept in a slightly more precise manner. Generally speaking, the size of each row in a table is irrelevant because accessing a two-hundred byte row takes about the same time as accessing a two-thousand byte row. The number of rows in a table might be considered as indicative, but a table of twenty million customers that might seem large to a utility company would be inconsequential to AxleBase. The total number of bytes in a table is certainly not useful in defining the concept because that could be misleading. For example, a table used in an astronomy application might have petabytes of data with almost all of it residing in BLOBS. The BLOBS might present a storage problem, but their management would be inconsequential for AxleBase. AxleBase uses a management mechanism that may be of some help with the concept. He divides a table into segments. Table segmentation is normally transparent to the user and is known only by the DBA and AxleBase. Segment size, in bytes, is usually fixed for a database, and is therefore usually fixed, in rows, for each table. The maximum segment size is usually set at two billion bytes. When each table segment reaches the stipulated size, AxleBase automatically creates a new segment into which new data will be inserted. The segment, then, gives us a means of defining the "very large" concept, which then allows us to use it as a more precise technical descriptor. As it is used throughout this documentation, the term "very large" means that a table has multiple segments. It is frequently referred to as a "VLT". A very large database, a VLDB, is one that contains one or more VLT's. The actual number of segments is undefined other than the "multiple" stipulation. That is intentional and is left to the discretion of the professional DBA who manages the local VLDB.
Introduction The observant will notice an interesting and extremely useful naming convention in the AxleBase documentation and demonstrators. It is the work of the very capable data managers and profesionals who work for the Entergy corporation. For those who are interested, it is detailed here. 1. Each major type of object in a database has a standard prefix letter. Standard Prefix Letters
2. The prefix letter is followed by an underscore. 3. Names are upper case. For example, the citizen table in the AxleBase demonstration database is T_CITIZEN. Simple, but very effective. Note that the standard is designed for use with all of the major brands of database managers and it works well on enterprise servers and desktops. It has facilitated communication between many large and small databases and systems, and those fine people deserve the recognition.
_________________________ If something should be in the following lists and is not, it is an oversight. Recent fixes and upgrades are listed on the web site change log page. Current work and next work is not predictable. Since this is such a huge project, I tend to work on whatever interests me at the moment.
Introduction Bugs and breaks are not usually listed here because, when a bug or a break is discovered, all other work ceases until it is fixed. Changes, upgrades and fixes are posted in the web site change log as each is finished and uploaded.
Introduction SQL optimizer needs more work. Joins : Compound parenthetical joins are not yet supported. Joins : Group by and having. Math operations in SQL statements. Unique selects.
Table constraints : Only the primary key constraint is currently operational.
Introduction A decision has not been reached on whether or not to do the following items. Century leap-seconds are not handled in date functions. Column locks. Jobs. Commonly known as "stored procedures". The use of SQL as a programming language is a dangerous practice, but it is common, so this is undecided.
Introduction There is currently no intent to build these items into AxleBase.
Since a host app can modify data that has been returned, there is little need for that in AxleBase queries and it has a very low priority. For example, applying a date conversion to a column after it is selected should be done by the host. A unique index is maintained within table segments, but not across segment boundaries. (Tables of ordinary size do not need it and it would be disruptive in a trillion-row table.) AxleBase does support the ODBC protocol. But a driver is the responsibility of the hosts. The ability of AxleBase to support an unimaginable data load could allow an inexperienced DBA to crush an infrastructure by turning on mirroring. However, this may be re-evaluated. Slave databases: See above. Failover databases: See above. Backup databases: See above. This has been known by various names through the years such as transaction logging and journaling with checkpointing. Its purpose is database restoration from the checkpoint. The objectives of AxleBase do not require journaling and they might actually be impaired by it. It could, for example, seriously degrade the speed of a very large database. It would be simple and a fun bit of programming, but is not seriously considered because of its negative impact on operations. AxleBase is designed for databases containing very large and geographically distributed tables, and creating views with such objects would be disruptive at the very least. Since the mechanism for views would not be complex, it might be reconsidered later, but it currently appears that the benefits would not be worth the dangers.
_________________________
Introduction After AxleBase is installed on a computer, it will automatically appear among the tools or components in any development environment that is aware of components on the computer. For example, Visual Basic will show it in the list of components that can be referenced. When a reference is set to it in that kind of environment, its commands automatically become available within the development environment as part of the development language. Method 1 At a glance:
On the web site, click on the download link to download the current download package into a temporary directory on your local disk. That package is an executable packed file with an exe extension. It does not need WinZip or any other program. When it is run, it unpacks itself into several files in your temporary directory. When the download is told to unpack itself, it creates several files, one of which is setup.exe. When setup.exe is run, it installs AxleBase on the computer in the system directory. Save copies of your downloads. The AxleBase system is frequently updated, and you are responsible for maintaining the com object which you use in your host system. You may need to roll back to a previous release if an upgrade does not work for you. After installation, the database manager is available for embedding into your host system. Reference it and code to its interface in your project. When you compile and distribute your system, the referenced database manager will go with it. This will not uninstall a previously installed release. Use the command in Method 2 to do that. Method 2 A method which some developers use is to place the DLL where they want it and then manually install it. To do this:
That method can be especially useful when upgrading to a new release. Using the /u option in the command will uninstall. A ficticious example:
Method 3 A more complex, method is to use a system call from inside the host system to install it every time that the host system runs. That is the method that is used by AxHandle, but not by CoreReader. Every time that AxHandle is used, he re-registers his internal database manager. That method works well, but is not recommended because it can lead to confusing problems during system development. Method 4 If AxleBase was installed and shared by another system on the computer, then it will be available for use by your own system. If it is uninstalled and upgraded, then systems that use it may need to be upgraded to insure that they can run with the new AxleBase release.
Introduction Like any component, AxleBase should usually be installed on a production system as part of the installation process of the host system. What happens after installation is up to the host system. For example, when CoreReader starts up, he checks the environment to see if his database exists. If it does not, he gives AxleBase instructions for creating it. The tools for doing all of this are covered later in the documentation. Copies of the original download may be used to install on additional systems. If this method is used, the manufacturer of the host system is responsible for documentation and support. Do not direct the end consumer to download from the AxleBase site because the currently posted release may not be compatible with the older host software.
Introduction The change log page on the web site shows the current release number and a record of recent changes. The operating system utility can be used to uninstall the current installation. To upgrade a development environment:
Please check the change log on the web site to see if the database needs to be upgraded. AxleBase will not upgrade a database.
_________________________ AxleBase is designed only to be embedded in an application, so he has no GUI and no front end. The host apps should handle the control and administration of their databases. If a GUI is needed in an emergency, AxHandle may be pressed into service as an ad-hoc database administration tool, but that is not his purpose and he is not a production-quality app. If you need a GUI control for AxleBase, just create one. That is, after all, why AxleBase is embeddable.
_________________________ Download, unpack, and install the system as described in the installation section. Reference AxleBase in the host system. Decide how the database(s) should be constructed, used, and maintained. Write the commands in the host system to tell AxleBase what to do.
_________________________
Introduction The AxleBase test and development lab uses the cheapest equipment that can be found in retail stores. Computers are used until they break, so one machine has been in use since the mid nineties. Where fast computers are used in the following tests, it is because they were the cheapest computers being sold when they were bought. Disk drives are the cheapest standard off-the-shelf drives that can be found locally. They are the largest, and not the fastest, affordable. The network is gigabit switched. Tests are run without tuning and without special configuration except for disk defragmentation. The yieldProcessor setting is turned on unless otherwise indicated.
Introduction The tests described herein may be repeated and the results may be publicly and privately discussed. The free AxHandle test and demo app may be used to repeat the described tests and it can easily run many other tests. AxHandle is described in the chapter entitled Free Test And Demo Software. It may be downloaded without cost. It is shipped with AxleBase embedded in it for testing.
Introduction Objective:
Validation:
* Data only. Excluding indices and no BLOBs. ** Participated in the build.
Introduction Objective:
Validation :
* Data only. Excluding indices and no BLOBs. ** select * where name = 'lauren' Returned 20,000 tuples.
Introduction Objective:
The ability to operate as an embedded object is continually demonstrated in a publicly used application. AxleBase is embedded in CoreReader which can be found on the web site at http://www.CoreReader.com. He handles all of CoreReader's data. He runs day and night around the world in thousands of CoreReader installations. AxleBase has done a fine job in that app for years. AxleBase is embedded in the AxHandle demonstration app which can be downloaded from this web site. AxHandle is used constantly to test the embedded AxleBase. ( AxleBase is embedded in CoreModel, but CoreModel has not been updated in some years.)
Introduction Purpose:
Queries:
Note::
AxleBase attempts to retard the host system to avoid losing information when a production system exceeds the design limits of a database. But since the failure point has not yet been found, that situation should be avoided. The tests were run over a mixed network of 10 megabit, 100 megabit, and 1 gigabit connections.
Concurrent Insert Test Objective:
The CoreReader log table was chosen. Multiple CoreReader instances were directed against a single remote database, and a departmental job server was started on all instances. All activity, including job file writes, was on one disk. There was no system tuning or object dispersion. The default CoreReader installation was used.
* There was also an hourly database purge and backup from every instance. ** Does not include the file server and the database servers such as MySql and MsSql against which CoreReader jobs were run.
Introduction Objective:
The optional AxleBase LOCATE clause was used in the SQL statement to separate the build writes from the source reads which reduces contention for disk heads. (The LOCATE command can also be used to reduce operating system fragmentation.)
Introduction Objective:
This test is possible because of the AxleBase ability to quickly return a specific row by number. Test Logic:
example:
Introduction
* Time elapsed from handing each insert to AxleBase until he was ready for the next insert.
Introduction Purpose:
Query Design:
Results Validation:
Important:
Equipment:
* select * from t_citizen_status row ( 16000500000 , 200) ** Data only without indexing. Standard Index:
When AxleBase is told to index a table that has not been built, he uses a method which is more suitable for OLTP. The slower build is seldom noticed because it is spread over single row inserts. The following test is of that kind of index.
Introduction Note:
Purpose:
Equipment:
Query Design:
Test Setup
__________________________________________________ Chapter 2 Free Test And Demo Software __________________________________________________ The test software may be downloaded from the AxleBase web site under the AxleBase licensing agreement. It is simple software that is designed to demonstrate and test the more complex AxleBase system.
_________________________
Free Test And Demo Software The primary purpose of AxHandle is routine testing of AxleBase in his development environment. He is also made available for demonstration purposes. AxleBase is embedded in AxHandle so do not download AxleBase for AxHandle. In this case, the host has a point and click GUI interface for interactive control of AxleBase, but an unattended server can also have an embedded AxleBase. AxHandle is not intended for production use. His purpose is test and demonstration. A hidden problem is that he may be too powerful. He makes dropping a very large database easier than a query. The Windows operating system requires a new handle for almost every event and the demo apps cannot release those handles so they eventually reach the Windows limits. AxServer hits the limit quicker than AxHandle because Windows communication objects also use handles. AxHandle is multi-instancing. AxHandle is not multi-threaded and is not multi-tasking.
Free Test And Demo Software There are two interface objects, Manager and Vector, in AxleBase. They are covered in detail in later sections. For now, just be aware that they exist and that AxHandle tells AxleBase to create them as you need them. When AxHandle loads, he creates a Manager object for you. You will initialize it by opening a domain and a database. The easiest way to do that is by pressing the demo_db button. When the operation is complete, you will be connected to the new demo database. AxHandle creates a data Vector object when needed. When a new Vector object is created, AxHandle connects it to the Manager object which provides a database connection.
Free Test And Demo Software All commands are listed in the scrolling windows. The top window lists all of the Manager commands and the bottom window lists the commands that are used by the data Vector. Each set of commands has a do_it button. To run a command, highlight it and press its do_it button to execute it. For example, select the ShowCopyright command and press the do_it button. The copyright will appear in the return window. There is a parameter window next to each command window in which a parameter can be typed. When the do_it button is pressed, AxHandle will combine the parameter with the command before passing it to AxleBase. Example :
Free Test And Demo Software Messages, errors, and data returns will appear in the bottom window on the screen. Sometimes, AxHandle puts instructions in that window to show how he performed an action. For example, when you tell him to create a new demonstration database, he will list the commands that he used so you can create your own databases. If AxleBase returns an overwhelmingly large dataset, AxHandle will tell you that the return was too large for the window. When that happens, AxHandle will remind you that there are AxleBase commands that you can use to step through the dataset.
Free Test And Demo Software There are help options for each major operation on the help menu. The AxleBase command syntax is available from the help menu. Click on help and then click on documentation. It will tell you how to find and display what you need. If you need help only for a specific AxleBase command, display the documentation and double click that command. ( AxHandle uses the same documentation mechanism that is used in the CoreReader and CoreModel family. This is not because it is so great, but simply because Microsoft's was so bad. Since this was done, others have created and offered better documentation tools, but they were simply too late. ) For a minimal amount of help with the SQL language, look in the ExecuteSql sub-section of the API chapter. The entire syntax is presented for each of the commands. The SQL language is one of the simplest languages in existence, but its use is one of the most complex. If you are not familiar with it, you may want to buy one of the many books on the subject. For major assistance with constructing SQL queries, download the CoreReader tool from www.CoreReader.com . You can use it to query databases just by pointing and clicking on data objects and it will show you the SQL statements that it builds. ( The creation of CoreReader showed others how to create similar tools, so there are now many fine products on the market. However, aside from their user interfaces, they are no better than CoreReader at what CoreReader does, so you might as well use the original. )
Free Test And Demo Software Each time that the connect command is used by AxHandle, he stores the connection string. He also saves the connection strings when he creates the demo databases. If the new string is identical to one on file, he will not save it, but the slightest difference will cause it to be saved. Press the open_db button to display all stored connections. To open the desired database, highlight the one needed and press the do_it button, or just double click the desired string. In conformance with the AxleBase design philosophy, connection strings are saved in a text file which can be edited in notepad. It is named connects.txt.
Free Test And Demo Software When the AxleBase project began, every time that a test garbaged the database, it had to be laboriously reconstructed by hand. (Yuck!) Therefore, AxHandle was given the ability to destroy and re-create test databases on demand. Pressing the demo_db button will create a demonstration database domain, demoDom, under the app's location. Three databases will then be created in that domain; demo_main, demo_citizen, and demo_virtual. Each contains a dozen or so tables. AxHandle contains scripts which consist of the commands and SQL statements required for the operations. When the button is pressed, AxHandle starts passing the commands to AxleBase. The complete demo databases may be re-created at any time just by pressing the button. AxHandle asks AxleBase if they already exist, and if they do, AxHandle tells AxleBase to drop them, and then begins creating new ones. The domain is preserved after it is first created. This is done to allow creating and populating databases with various domain-level parameters. It also allows you to create other databases in that domain. The entire domain and the demo databases may be removed by deleting the db directory. The demos contain a few simple tables which contain some rows for experimentation. The column types are mixed so they can be used to test performance. The demo_virtual database also contains a few virtual tables. Some of the virtual tables can function only if their attributes are modified to fit the local environment. As it is being created, the system writes a script in the returns window which shows the commands that are used. This can be copied and saved for reference when creating other databases. If additional databases are created in the demo domain, AxHandle will retain them when re-creating the demo databases. Feel free to manually alter the demo database and bang on it to experiment with it. If it becomes so badly corrupted that AxleBase cannot even destroy it, manually delete the entire \db directory under the app, and then press the demo button again.
Free Test And Demo Software For VLDB testing, AxHandle has been given the ability to generate large test tables. Each table that is created in the demonstration database may be expanded by selecting the appropriate option on the enlarger menu. ( A study of the VLDB chapter may be needed to understand what happens when AxHandle begins building a large table. ) AxHandle uses individual SQL statements to insert rows into tables. He creates an individual statement for each insert and then passes the command to AxleBase. Allow the generator to run for a day or two or whatever is required to generate a table of the needed size. Each table has a different rate of growth. Depending upon which table it is, a four hundred megahertz machine can generate a million or so rows every day or two. Caution ! :
AxHandle gives unique rows to some tables. Select the AxleBase command ShowTables to get the table names, and then use the ShowTable command to inspect the structure of each table. HINT :
Free Test And Demo Software Instead of just using a script to write data to disk, AxHandle actually drives AxleBase for the task, which tests many of the AxleBase commands and functions when the button is pressed. AxHandle has additional miscellaneous tests built into it which he runs every time that he builds a database. They are not comprehensive, having just a few thousand lines of code, but they include many of the basic SQL statements that are not used by the database creation. You will see them run every time that you create a new demo database. Sometimes I forget to press that button before uploading a new version. Oh, well....
Free Test And Demo Software The previously described tests can be run in a continuous cycle from a menu option. This is designed for stressing AxleBase; running load tests. It is not designed to be a test of speed. Select the stop option from the same menu to stop the tests. A well designed application would recover when AxleBase returns an error. For example, if AxleBase reports a query timout, the app might regenerate the failed request or ask the user if he wants to retry. However, AxHandle is designed to stop on error and display it. The tests are designed to run against a fresh copy of the demo database. When they are started, AxHandle performs a few checks on the tables for that reason. The slowest test cycle puts a minute between tests. That test can take over two minutes to stop because of the length of the pauses. Since AxHandle is not multitasking, the cycling must be stopped before doing anything else. AxHandle should not be unloaded while a test is running. YieldProcessor :
Logging :
If logging is on, its overhead and the impact of the log size should be assessed. Some errors that can be logged cannot be passed back to the host, so AxHandle will not know about them. All errors that can be detected by AxleBase are logged if logging is on, whether or not they can be returned to the host. Hardware danger :
Test statistics and the SQL queries are displayed in the return window during the test cycling. A test setup can be designed as needed. A single instance can be run at low speed to watch the operation, or can be run at high speed to watch the effect. Multiple instances can be run to load the system, and remote multiple instances can be run against a database to watch the effect. ( These are NOT recommendations. See the previous warning. )
Free Test And Demo Software Load the system. Accept the license terms to display the main screen. Press the demo_db button. This will create demonstration databases under the app. The path may be something like c:\program files\AxHandle\db\demo\ After it creates the demo database, it will leave you connected to it so you can immediately begin querying and investigating. You can easily change to a different database with a couple of different methods. If using the CONNECT command, enter the domain path which will be something like c:\program files\AxHandle\db\demoDom and enter the name of the database such as demo_virtual. Then press the do_it button. Find the ShowDomainAttributes command and select it. Press the top do_it button. The domain's attributes will be displayed in the return window. Select the OpenDatabase command. Type the name of a database, demo or demo_virtual in the parameters window. Press the do_it button. The database will be opened. Select the ShowDatabaseAttributes command and press the do_it button. Attributes will appear which describe the currently open database. Select the ShowTables command and press the do_it button. The names of the tables in the database will appear. Select the ShowTable (singular) command. Enter the name of a table. Press the do_it button. The table's characteristics will appear. Now, try it with the ShowTableAttributes command. Select the ExecuteSql command in the lower window. Enter a query in the lower parameter window, such as "select * from t_log". Press the lower do_it button. The rows will appear. Try a select after unloading and reloading the app. You will find that it does not work. AxHandle creates a new Manager object and data Vector object for you when he loads, but you need to connect them to a database as was done previously.
_________________________
Free Test And Demo Software AxServer is a simple database server host. It demonstrates the server support that is built into AxleBase for a database server. It is available for download from the AxleBase web site under the AxleBase licensing agreement. ( The server works on a local area network. It will be made internet-aware when I get a round tuit. AxleBase is internet-ready and waiting for a server.) (Pursuant to the command of Him for whom I work, my software will transmit nothing other than that which is openly and simply specified. Furthermore, care is taken to insure that transmissions contain only what you want to be transmitted.) When it goes on line, AxServer loads an instance of AxleBase in the background to do its work. An AxHandle client on a different computer can send commands to it and it will return the data or results. When a communication session is established, only the server's AxleBase instance is used. The client passes commands straight to the server and the server passes them to AxleBase. The server then returns the result to the client. The demo systems are not intended for production work. The Windows operating system requires a new handle for almost every event and the demo apps cannot release those handles so they eventually reach the Windows limits. AxServer hits the limit quicker than AxHandle because Windows communication objects also use handles. ( Reminder: The AxleBase API supports ODBC if you want to build a driver for your server.)
Free Test And Demo Software This is a simple demonstration app. It is not a production-quality system. It cannot perform high speed operations. It is not designed for unattended operation. (It has a GUI.) It has limited error handling. It cannot handle multiple NIC's on either end. It accepts multiple clients only for research purposes. The server functions on a local area network. It will be made internet-aware when I get a round tuit. (AxleBase is internet-ready and waiting for a server.) The primary function of this database server is research and testing in the AxleBase lab so, unlike a true database server, this one is designed to stop processing when certain kinds of errors happen and display them in a popup window. Obviously, if for no other reason, that one prevents it from being used as a production server. AxServer is not solid. The majority of the development effort is put into AxleBase and secondarily into the AxHandle test app. That leaves little time for communication development and leaves the server in a primitive state.
Free Test And Demo Software The demonstration requires two computers which communicate with each other on a network. Perform these operations in the sequence shown. Uninstall AxHandle and AxServer if you have them. New versions are needed. Download AxHandle and AxServer from the web site. Install AxHandle on a computer. Install the server, AxServer, on the other computer. Start AxServer and AxHandle. Press the button on AxServer to start its server. Press the server button on AxHandle to display the comm panel. Enter the server's computer name and press the connect button. Press AxHandle's demo_db button to create the demonstration database on the other computer. If everything was done correctly, you will see the system building a database on the AxServer computer and then running a series of tests. The two systems will talk to each other during the process and will show you parts of their conversation. After the tests are complete, you will be connected to the remote database so you can run tests. Select the ExecuteSql option in the lower window, enter
Free Test And Demo Software Please ignore this protocol sub-section. This information is provided as a courtesy to those who want to build a system to communicate with the AxServer server or with the AxHandle client. Release Number 91101 is the current protocol release number. Changes in the release number will reflect changes in the protocol. Description SysLink is an application-level communication protocol. To facilitate development and debugging in this complex profession, the SysLink protocol is designed for human readability as well as for machine use. Because it is for machine use, the command strings must be precisely stated. Comments Outside Protocol AxleBase does not use this protocol because AxleBase does not communicate. AxServer and AxHandle use the SysLink communication protocol. The main purpose of SysLink is to enable the operation of a distributed AxleBase database manager. AxHandle And AxServer Usage High-speed interactive systems can encounter timing issues. Without pauses, some operations frequently produce a cascading series of errors with unforeseeable results. For example, pressing the button to build the demo databases kicks off thousands of complex operations with checks, traps, feedback, etc. which proceed at high speed with the systems on both ends trying and failing to coordinate with each other. The problem is usually seen when the client is on a slower computer. Media latency can also require adjustment of the time. The wait time protects AxHandle and AxServer from being overwhelmed by high speed operations. It allows a system to pause after sending each command up to the specified time.This is an application-level wait time which is used, in this case, by AxServer and AxHandle. Mechanisms within the communication objects have their own timing issues which are handled differently. A wait time value does not require a wait. If the operation gets a return from the server earlier than that, then the pause will end. The value is a maximum time in seconds. The default value can be changed in the GUI, and decimal values are accepted. AxServer and AxHandle currently use a socket buffer of 2,048. The AxServer port defaults to 3333 and AxHandle defaults to 3332. The AxHandle and AxServer demonstration apps check the protocol release number in transmissions to insure that they can communicate reliably with each other. Release Of Protocol This protocol may be freely used and distributed in whole or in part with the following exclusions. No other permission is required and no compensation or acknowledgement is required. Exclusion : This release applies only to this protocol and to no other intellectual property or work. This release does not in any way involve, include, or refer to AxleBase or to any other intellectual work. Exclusion : The SysLink name is not included in this release, but the name may be freely used in reference to this protocol whether the protocol is used in full or in part. Exclusion : Protocol development will not be bound by this release. The protocol is subject to change without notice. Development by the creator may continue, and if it does continue, the protocol may be changed in any manner at any time. Exclusion : This release does not include the right to develop or alter the protocol. The developer invites requests and suggestions. Transmission Envelope Transmissions are inside an envelope. The envelope consists of a header and a footer. The envelope is used by all transmissions, including commands, messages, errors, returns, etc. The transmission header contains twenty elements of variable length. The elements are each terminated by ASCII characters 13 and 10. The elements marked with a pound sign must contain the specified data.
- The value shown in the second position is a case sensitive literal that identifies this as a transmission envelope.
The footer has three elements, which are each terminated by ASCII characters 13 and 10.
- The ASCII character 127 is the first character in the footer.
The following rules are observed :
Double Encryption The protocol does not deny compounded encryption. Example : In this hypothetical situation, the currently running axsys is designed to use double encryption. All AxleBase instances are configured to encrypt all data returns. Each host will receive an enrypted return from its embedded AxleBase instance, request authentication from AxleBase, construct the transmission envelope which includes the authentication and the encrypted return, and then have AxleBase encrypt the entire transmission assembly. The receiving host would be designed to decrypt all inbound transmissions, so it would decrypt, validate protocol conformance, authenticate, and then decrypt the enclosed return. In that example, the entire transmission is encrypted. Within the transmission is a syslink envelope. In the envelope header is an authentication. Within the envelope is the return in its own encrypted state. ( AxleBase is designed to handle multi-level encryption. )
Control Command Strings The following control command strings are peculiar to and recognized within the protocol. Participating systems are expected to recognize and appropriately respond to them. To facilitate development and debugging, the communication control strings are designed for human readability as well as for machine use. Communication control commands are precisely 30 characters. Where a command or response requires parameters, the control string is followed by a right pointer (>) and a left pointer (<) which enclose the parameters. Communication control strings are case-sensitive literals. Each of them includes two asterisks on each end as shown. The header and footer strings are included in the following list only for clarity. The other control strings are placed inside the envelope. Other than the header and footer strings, a transmission may contain only one control command string. Currently recognized control strings:
001 ** open syslink transmission**
002 ** stop syslink transmission**
003 ** open new syslink session **
004 **break our comm connections**
005 **reverse connection to port**
006 **syslink session identifier**
007 ** execute local app command**
008 ** resend last transmission **
009
010
011 ** information return query **
012 ** information query return **
013 ** identification requested **
014 **identification is enclosed**
015 **comm check please respond **
016 **comm check 30 chr response**
017 **authenticate**authenticate**
018 ** authentication enclosed **
019 ** encryption specification **
020 ** initialize app or system **
021 **stop now. unload now. die.**
022 ** transmissions size limit **
023
024
025 ** * server return begin. * **
026 ** * server return cease. * **
Command : 001 ** open syslink transmission** This has nothing to do with beginning a SysLink communication session. This string is used in a header at the beginning of a transmission to declare the beginning of a single continuous transmission. Command : 002 ** stop syslink transmission** Used in a footer at the end of a transmission to declare the end of a single continuous transmission. Command : 003 ** open new syslink session ** The SysLink protocol governs communications at the application level. Levels below that, such as TCP/IP are expected to be governed by their own protocols which will not impinge upon the SysLink level. Therefore, the involved apps must open the SysLink communication session. A communication session may be opened either by this command or by the "reverse connection" command. If this command is used, then the "reverse connection" command may be sent subsequently. A response is not required. The sender will expect one of four possible results:
Responses may take any form. For example, the challenge might be a demand for authentication. Command : 004 **break our comm connections** This string is sent to advise the respondent of the cessation of communication. When that string is sent or received, the system is expected to immediately initialize communications without response or notice. Command : 005 **reverse connection to port** After the client connects to the server, the server will expect to transmit to the same port number on the client. This command may be used by the client to tell the server to use a different port number for transmissions to the client. If this command is used, then both connections will be used for the duration of the session. The request for a reverse connection is in the following format:
If the session has not been opened and established, then the establishment of a reverse connection will be construed as an accptance of the request for a SysLink session. ( This is not part of the protocol: AxHandle pauses for a tenth of a second to allow settling of the infrastructure and then sends this command to connect to his own port number. When AxServer receives the command, he creates the requested reverse connection to the specified port number. AxHandle will notify the operator via the GUI if one of the connections is not made. The operator should then initialize.) Command : 006 **syslink session identifier** If this command is sent by either side, then the enclosed value will become the indentifier of the current SysLink session indentifier until cessation. It will not be changed by either party. This identifier may be included in the header of every transmission. Transmission of this command will constitute acceptance of a session request. The complete command is in the form :
Command : 007 ** execute local app command** Commands may be sent to a system which is embedded within the respondent. The respondent is expected to extract the command and pass it to the embedded app. The command and its parameters are placed in the following data structure which has nine elements.
Command : 008 ** resend last transmission ** Requests that the respondent retransmit the last transmission. The command is followed by a pointer enclosure. The enclosure may contain the identifier of the last transmission if it is available.
If the transmission can be resent, the requesting entity will expect the re-transmission to contain the same identifiers as the original. The response identifier will identify the resend request. Command : 009 Command : 010 Command : 011 ** information return query ** This is a request for information. The command is followed by two pointers enclosing the information specification.
Example: The location for the demonstration databases can be known only by the server, so before beginning a database build and test, AxHandle sends the following:
Command : 012 ** information query return ** If the system chooses to respond to ** information return query **, this is the appropriate response that is followed by two pointers enclosing the information. Example: AxServer may respond to the dbroot command with:
Command : 013 ** identification requested ** This is a request to the respondent to return an identification. Failure to respond is not an error within this protocol. The complete command must include the attached variable enclosure which is two pointers, and is in the form:
Challenge and response encryption may be expected. Command : 014 **identification is enclosed** This is a response to a request for identification. The complete response includes the attached variable enclosure which is two pointers, and is in the form:
The identification is expected to be a string of identification elements. Each element consists of the name of the element, an equal sign, and the value. ASCII characters 13 and 10 terminate elements. The protocol does not stipulate the elements, but suggestions may be taken from the message header. Challenge and response encryption may be expected. Command : 015 **comm check please respond ** This control statement may be initiated by client or server to verify a functional communication link. The command will be the entire message. Command : 016 **comm check 30 chr response** This is the expected response to a communication check if the system chooses to respond. The entire message will consist of only the response. Failure to respond is not an error within this protocol. Command : 017 **authenticate**authenticate** Receipt of this command with no parameter is expected to cause the recipient to respond with an authentication string. If the command is followed by a start string enclosed by two pointers,
Command : 018 ** authentication enclosed ** This is a reponse to the request for immediate authentication if the system chooses to respond. The complete statement includes the authentication string and is in the form :
Command : 019 ** encryption specification ** (This command protocol is still under active review and should be expected to change.) This is a request to the respondent for encryption of subsequent transmissions. The complete statement includes an encryption specification which may be blank, but the enclosing characters must be present. The complete statement is in the form :
After this command is transmitted, the transmitting system will expect encryption until the command is rescinded. Rescission is accompished by transmitting the command with the "stop" string in the specification position. ( AxleBase will honor this command, but will use only his native encryption.) Command : 020 ** initialize app or system ** The command is followed by two pointers enclosing the name of the system.
This allows the client to tell the server to initialize an embedded app or the operating system. An optional notice of compliance or an error may be returned. For example, AxleBase is an extremely complex system, so a return lets the client know whether or not he can proceed. Depending upon the app and the characteristics of the infrastructure, it may be a good idea for the client to verify server-side system compliance. Example: AxHandle likes to initialize AxleBase in the server after heavy duty cycles and in some job streams. When AxServer receives this command, he stops, unloads, and reloads AxleBase. Command : 021 **stop now. unload now. die.** The receiving app, server or client, is expected to respond to this command by unloading itself and any client software. There is no provision for a restart. This command should not be expected to include the operating system. The initialization command is used for that. If the server is running a job, then this command may not be honored. Therefore, a comm-check is recommended after sending the command. (The protocol does not require compliance.) Command : 022 ** transmissions size limit ** Allows an application to limit the size of transmissions from the respondent. The value is not negotiable. If both sides request a limit, then the lower value wins. If a requested return exceeds that size, then the respondent will be expected to drop the response and send an appropriate error notice. The specification will remain in effect for the duration of the communication session. The command is followed by two pointers enclosing the return size specification.
Example: A 500,000 byte limit is currently used by AxServer and AxHandle to protect the computers. If larger returns are needed, see the optional AxleBase DeferReturn SQL clause in the API chapter. Designed for very large databases, it can safely return any amount of data under tightly controlled conditions. Server Envelope In a client-server exchange, as may be the case with a database manager, returns from the server are placed in a server envelope which consists of a header and a footer. Each is a thirty character literal terminated by chrs 13 and 10.
The server envelope is placed inside a transmission envelope. The server envelope should not be in a control transmission. An acceptable server return may be an error which will be placed within the server envelope. (AxServer formats data return tuples for the AxHandle return window.) The header and footer conform to the control command format. Error Handling This protocol does not currently include error handling. The following applies only to AxleBase demonstrators, AxServer and AxHandle. ( The AxleBase error protocol is not part of this communication protocol and is not released from its copyright. See the AxleBase Error Protocol section of the Embedding And Running AxleBase chapter.) Since all transmissions are in envelopes and since an error may pass through multiple systems before transmission, an error message may be embedded anywhere within a transmission so returns should be searched for AxleBase-type error headers. An invalid envelope will halt processing and an error notice will be returned specifying an erroneous envelope. The transmission will be cleared and nothing else will be known about it at that point. AxServer will tolerate almost anything within a correct envelope. The purpose of that is to allow experimentation and to speed development. AxleBase is not as forgiving. When AxleBase receives an invalid command, he generates an error which will be returned through the server. Infrastructure errors are trapped by the system or sub-system that has the processor at that instant and are handled within the AxleBase error protocol. Depending upon the type and source of an error report, the receiving system, whether client or server, may initiate a dialogue concerning it. See the preceeding control commands.
__________________________________________________ Chapter 3 Embedding And Running AxleBase __________________________________________________
_________________________
Embedding And Running AxleBase AxleBase has been tested on a computer with sixty-four meg of RAM and a 120 mhz CPU. He has been designed to run on computers with far less than that amount. AxleBase consumes no RAM while at rest. He needs RAM only when the host instantiates a Manager object. At that point, his needs will begin to vary. He may be expected to occupy at least 400k per instantiated Manager object. Each Manager may occupy up to 800k, depending on what he is currently being told to do. For example, each data Vector object requires at least 400k and a Manager object can support an unlimited number of data Vector objects. So a poorly constructed host app could overwhelm any amount of RAM by telling a Manager to construct data Vector objects without control. He will consume additional RAM for each dataset that he is told to build up to the size of the dataset. While building a dataset, he will use RAM for each table in the SQL and for working storage. (Note the deferReturn clause for large returns which is covered in the API chapter.) To conserve resources, he releases all working storage as he finishes each task. He diligently destroys all objects that he creates immediately after they finish the assigned task. When a data Vector object is release by the host, it is destroyed and it destroys its dataset as it goes down. Unlike other database managers, AxleBase does not maintain unneeded objects in RAM for speed. Finally, when the host app unloads him, he will again consume no RAM.
Embedding And Running AxleBase AxleBase is designed to be embedded inside a host application. He talks directly to the host. As soon as he is installed, the host application can send commands to him. He provides his own interface for programming. No driver is needed. No provider is needed. If AxleBase is embedded in a server, then the server will, of course, need an ODBC driver. The AxleBase interface is designed to support the ODBC protocol.
Embedding And Running AxleBase AxleBase indexing is different. A database manager cannot be optimized for both the large and the ordinary. One or the other must suffer, so AxleBase is internally optimized for very large data object indices. Since AxleBase is not intended for heavy concurrent access, the degradation on very small tables may not be noticable, but the improvement on very large tables will be obvious. Usually, boolean columns should not be indexed. When a boolean column is indexed, reading its index and then reading the table takes longer than a table scan. However, the inclusion of a boolean colum in a multi-column index can sometimes be very wise. Indexing a very large AxleBase table can have a tremendous positive impact on speed. Scanning a twenty billion row table for a value can take many days or weeks. An index can sometimes reduce those days to a fraction of a second. ( See the test section of the Introduction chapter for query tests. ) The AxleBase approach to indexing is unlike that of ordinary database managers. This is necessary to optimize AxleBase for his specialized abilities. A management method that sometimes appeals to the DBA is to index every column in the database, but that can slow some operations in a very large database. Since the slowed operations in normal databases are not heavily used by most people, the cost is seldom noticed. For example, if it takes two thousandths of a second to insert a row instead of one thousandth of a second, nobody notices. But what happens if the database manager is handling a truly large table? One thousandth of a second per row will add a week to the construction of a billion-row table. And if the table will become very big, that millisecond can turn into weeks or months. The big name brands also sometimes use that approach. In some cases, a database manager will automatically create an index without the knowledge of the DBA. However, to do that in an AxleBase database could be disastrous simply because AxleBase is designed for the management of very large data objects. Therefore, the DBA can expect no unexplained gain in speed as sometimes happens with a big name brand. AxleBase can index virtual tables which use external files and tables. Th AxleBase query optimizer will combine indices as needed, but will not divide a multi-column index. When a query uses one column of a multi-column index, other database managers will split that column out of the index and use it in the query. AxleBase requires that the single column have its own index. (This may be re-evaluated in the future, but index division can slow queries of very large tables.) However, the DBA should not use only single-column indices. A multi-column index is faster than multiple single-column indices, and the query optimizer knows that, so it mixes and matches as needed. AxleBase has special tools to help the DBA with building and indexing. A high speed table build, such as with the arrayed insert, while building its index will slow the build greatly. Dramatic speed improvement can sometimes be attained by first building table segments and then indexing completed segments in a separate operation. A normal table build usually hides the slow speed caused by indexing because the system spends much time just waiting for work. It is therefore sometimes advisable to allow the index to build during the table build for normal slow operations so that it can be immediately used. AxleBase recognizes complex relationships between data characteristics, data object size, and the environment. He also knows that the characteristics of that data may change in ways that are important but are too subtle for a human to detect. He uses specialized internal algorithms that help him decide upon the best way to index each data object for the fastest build speed and the fastest index concomitant with his specialized mission objectives. Before indexing a table segment, he analyzes the entire segment to choose the best options for it. If the segment has little or no data, then AxleBase makes his best guess about how to index it, but it is only a guess, so in that case, the index for that segment should be rebuilt after the segment is filled. Some analysis operations use the buffer to determine the characteristics of the final index. The larger the buffer, as long as it is not too large for the hardware, the more efficient will be those indices after they are built. When possible, the fastest insert and index operation, by far, is attained by first completely populating a segment and then indexing it afterwards. Creating an index as a table is built will result in much slower indices. If the system stops or has a problem whilce indexing a segment, then the entire index for that segment must be restarted. Reading a table that has been partially indexed may result in data being missed until that data is indexed. Therefore, in a very large database that is being built over a long period and which must be used during that period, it may be prudent to bring each table segment on line only after it is fully populated and entirely indexed. See also the VLDB Addendum to the CreateIndex command, and the ShowIndices command in the API chapter.
Embedding And Running AxleBase AxleBase is designed for the host, so he will always be running on the same machine on which his host is running. The objects that he creates are expected to be used only by his host on the local machine. There will be times, however, when it is desirable to share local data with remote locations and the organization does not want to build a database server into their host. In such a case, domains and databases can be shared across a network without a database server. Objects are shared after they are created. If a database is to be shared, then its controlling domain must also be shared. The ShareDatabase and the ShareDomain commands are covered in the API chapter. Of course, sharing in that manner obviates security controls.
Embedding And Running AxleBase Let us take an extremely complex subject which includes the broad areas of process design, application design, database design, etc., and pack it into a few paragraphs here and pretend that such simplicity reflects real life. AxleBase is designed for high speed operation of compact databases. When designing complex databases, design assistance may be obtained from the CoreModel tool at http://www.CoreModel.com . CoreModel is a professional quality database design tool. While designing an installation, these features may help:
There are only two objects in the AxleBase interface, the Manager object and the data Vector object. The following sections will address the nuts and bolts of their use. A recommended method is usually to instantiate a single copy of the database manager, use that one object throughout the system, and destroy on shutdown. (See the ShutDown command in the API chapter.) However, if the application requires continual high speed support of many clients, many instances of the database manager object can be created every second with each working on a different database task and perhaps various copies working on different databases. ( Such intensive and complex database operations require large hardware investments and coding care to avoid memory leaks.) The AxleBase data Vector object performs queries and returns data. In most cases, it is a good idea to destroy this object immediately after every use. However, for a high speed multi-cycle operation, the interface provides a means for continuous operation of a data object. Hardware is an important factor in any database operation and AxleBase is no exception. Because he is designed for extremely large data returns, AxleBase does not build datasets in RAM.
Embedding And Running AxleBase AxleBase manages databases by grouping them into domains. The attributes and data for each domain are stored in the domain's database, and the host application's data is stored in the client-production-working databases. A database domain is opened by the Manager object according to the attributes stored in the domain database. It does that by reading the attributes and using them to configure itself. After the domain is active, its client databases can be opened. A domain may be opened regardless of whether or not it exists. If it does not exist, and the specified location is valid, AxleBase initializes a new domain database for the domain at that location. After a domain is opened, its client databases may be created. If the domain and its clients already exist, after the domain is opened, its clients may be opened. Pseudo Code Example:
In steps 1 and 2 above, a new Manager object is created. The new Manager object is named oMgr in the example, but can be named anything appropriate. In step 3, oMgr is told to open the domain which is named demoDom. In step 4, oMgr is told to open the client database named prodClient3. oMgr is then ready to work with the prodClient3 database. A database domain may be distributed across spindles, controllers, and computers. The client databases are not required to be on the same medium and may be located as desired. The same is true of other objects as covered in subsequent sections.
Embedding And Running AxleBase Concurrent users of a database server may not present much of a problem because all go through the same interface and the server can control them. AxleBase is not a server. It was designed to be embedded in other apps. Therefore, many people or systems can be hitting the same database and the same table simultaneously. In that situation, the instances might bump into each other, trying to lock each other out, and trying to update the same data with catastrophic results. Although he is not a server, AxleBase was designed with the concurrency problem in mind. If the app in which AxleBase is embedded is a database server, using AxleBase as the engine, then the problem is solved. However if the host is not a server, it can still take advantage of the AxleBase features to work with many other users and apps in the same database. In a low speed or a low usage situation, the host probably needs to do nothing. The Testing section of the Introduction chapter presents the results of concurrency stress tests. As can be seen from those results, AxleBase may handle quite a load with no outside assistance. In a high traffic situation, the host should be prepared to react to reports from AxleBase. One such report could be a lock timeout. When instance A tries to use a table, he normally runs through it without ado. However, if instance B has the table locked for an operation, instance A cannot use it, so he immediately stops, identifies the problem, checks the query timeout setting, and begins waiting. While waiting, instance A periodically rechecks the table to see if it has been released. If he gains access to the table, he proceeds as usual. If he cannot gain access, and the duration of the wait exceeds the timeout setting, he aborts the process and returns an error message. A query timeout is not actually an error. Although it generates an error message, a query timeout error is actually just a report that a data object could not be accessed within the specified time. A lock timeout is usually simply indicative of heavy traffic on a computer. Note that there can be no queuing since there is no server. The first instance to encounter the lock might be the first to gain access when the lock is released, or it might not. For reasons beyond the scope of this documentation, that design is intentional. A decision should be made during the design of the host app concerning what the host should do when AxleBase reports a lock timeout.
Embedding And Running AxleBase AxleBase tries to make life as easy as possible for the developer. (Unencumbered by managers or by a marketing department, AxleBase's developer is building a system for people like himself.) Character case is usually unimportant. Do it the way that you want. The system will accept anything and try to standardize internally for you. If white space is required for delimitation, be sure that at least one character is present. Otherwise, use it as you want and AxleBase will clean up after you. Speaking of white space, AxleBase does not store space padding. He trims leading and trailing spaces from data before saving it. All in-code comparisons must be constructed accordingly. This is not ANSI SQL-92 compliant, but I do not plan to change it. Where parentheses, commas, colons, etc., are used, such as in standard SQL commands, be sure that they are present. However, do not be overly concerned about the presence or absence of a space before or after a parenthesis or a comma or whatever. Wherever possible, AxleBase attempts to read what you mean and not what you write. AxleBase will accept formatting in a SQL command. Most of us format long SQL statements with paragraphs etc. so that we can understand them when they quit working six months later. Never use an undocumented feature that you might stumble across. Such features tend to change or go away during development. AxleBase does not return nulls. If a value is null, there is nothing to return, so he returns nothing.
Embedding And Running AxleBase I am just plain tired of my own inability to use bad logic, regardless of how many people support it. After many years of programming, I am well aware that the zeroth element is how the programming community identifies the first element of a set, but that is far from logical. Of course, it is possible to become comfortable with logical flaws, as evidenced by the many old programmers around the world. And it is possible to aspire to poor logic, as evidenced by the many young wannabe programmers. Therefore, AxleBase reports the first element of a set as the first element. The number of elements in a set is the number of elements in the set. The number of elements in a set is not the number of elements in the set minus one. If you want the fourth row, tell AxleBase that you want row number 4; not row number 3. If you want the first row, tell AxleBase that you want row number 1; not row number 0. ( Please look at the absolute stupidity of that last paragraph to see why I am adamant about this matter.) I am hoping that few thinking people will object to this particular deviation from an industry standard. The fact that something is accepted, commonplace, and standard sometimes means only that millions of people are equally stupid. ( If this becomes the accepted way of the industry, then this section will be removed from the documentation.) Additionally, where a command tells AxleBase whether or not to do something, he requires a yes or no. The state may or may not become true or false after the command is executed, but that state is after the command is submitted.
Embedding And Running AxleBase There are two objects in the AxleBase programming interface. Each is covered in the following sub-sections. AxleBase is just another DLL on the computer. When he is installed on a computer, his programming interface is immediately available to any host system. If you know how to use Windows objects in your code, then you can use AxleBase. He runs as a COM server inside a host system and only inside a host system. His entire interface resides in two objects: The Manager object and the Vector object. The interface is immediately available when those objects are instantiated. Commands are sent to those two objects and all responses are from them. The syntax that is used to instantiate the objects depends upon the language of the host system and how the developer wants to use the objects. If the developer wants a tightly coupled interface, then he can compile a reference to AxleBase within the host system. The code can then refer directly to the objects. The AxHandle system does not have a reference compiled into it. The objects change so often in the development environment that a hard coded reference interfered with testing, so AxHandle creates the objects on the fly. An existing DLL can be unregistered, a new one placed where AxHandle can see it, and when AxHandle starts, he registers the new one and creates objects on the fly.
Embedding And Running AxleBase The Manager object handles the management and administrative affairs of databases and domains. He creates domains, databases, tables, etc.; destroys objects; maintains them; handles security; performs backups and purges; etc. (Data is handled by the other object.) Depending upon the language, using the Manager object follows this pattern:
A database must be opened before any action can be taken on it. If it does not exist, it must be created and opened. Before a database can be opened, the appropriate database domain must be opened. After a domain is opened, its databases can be opened. Opening a database requires the execution of thousands of lines of code and many disk reads and writes. When the same database will be opened and closed repeatedly throughout the host system, a developer may decide to open and configure a Manager once at the top of his code and close it only as the host prepares to shut down.
Embedding And Running AxleBase The Vector object handles data. He updates, inserts, deletes, retrieves, etc. He is the system vector for data. SQL queries such as select, insert, update, and delete are passed to him to tell him what to manipulate or return. The Vector object is a vector in both the mathematical and biological sense. There are two ways to create a data Vector object. One uses an object creation command such as the VB CreateObject command, and the other way tells a Manager object to create it. Creating it outside a Manager object creates a free-standing object which is sometimes referred to as a disconnected dataset. This allows it to remain active after the Manager object is unloaded for continued access to its dataset. A free-standing Vector object is connected to a database by passing it a reference to the Manager object which has opened the database. (For command syntax, see the API chapter's Data section and the Connect sub-section.) When the other method is used, a Manager object is told to create a Vector object. The Manager object should already have an open database to which it will connect the data Vector object, and it will then assist with managing the object. Each Manager object can create any number of Vector objects and each may be in use. ( For command syntax, see the API chapter, Manager section, DataObjectCreate command. ) In either case, a Manager object may support any number of data Vector objects. The AxHandle system was creating free-standing Data objects at one time. Since the Manager object can now handle the job, AxHandle now first creates a Manager object and then tells it to create a Vector object as needed. A line of code from AxHandle which creates a free-standing object: A line of code from AxHandle which tells the Manager to create an object: Depending on the language, using the data object may follow this pattern:
Embedding And Running AxleBase ( ! The identifiers that are generated are linked ONLY to that which is stated. The systems that I build do not secretly obtain, store, or transmit any information about you or anything that you own! Simply because He for whom I work commanded us to not be deceitful with each other.) The system contains the following object and process identifiers which are available to the host through the API.
The identifiers (ID's) are in addition to names that are assigned to objects by human operators. All identifiers are unique. They are generated by AxleBase. A vector name may be assigned by the host. If a vector name is not assigned by the host, it will be assigned by the system. The vector name is unique when assigned by the system. Identifiers are stored only for permanent objects such as a database. They are stored only as part of the object's properties. When a transient object dies, such as a connection, it's identifier dies. When a permanent object is destroyed, such as a table, it's identifier is destroyed. Note the excessive amount of identification. It is provided because the differences between some of the objects are subtle but important while the system is active. Not only do the identifiers provide control, they can be helpful in debugging.
Embedding And Running AxleBase Visual Basic code to open a database might look something like this:
Visual Basic code to open a client database and run queries might look something like this:
Embedding And Running AxleBase See the previous Visual Basic examples.
Embedding And Running AxleBase WinBatch code to open and/or create a database would resemble the following. ( Some code lines are broken for readability. )
_________________________
Embedding And Running AxleBase The AxleBase locking mechanism is designed to support his very special objectives which include very large data entities and low client concurrency. For exaple, if an AxleBase instance needs to use an index, he suspends other operations until finished with the index, applies the appropriate lock to the entire index, does the operation, and unlocks the index. This results in faster over-all operation in the AxleBase enviornment. Locks have precedence over security grants.
Embedding And Running AxleBase
A lock space domain is a concept and is not a functional object. Its application to the subject helps describe how the system actually works. Discretionary Lock Domain Discretionary locks may be applied and removed by the host or user at any time by using the LockObject command. For example, the DBA might lock the database in preparation for a backup. That type of lock will exist until the user exits, or the user removes the lock, or the lock expires. If an instance abends and leaves a discretionary lock, it will be ignored after it expires and will be removed by the next system purge. Persisted Lock Domain Persisted locks are similar to discrtionay locks because they are set by the human or host app as desired. The difference is that they are object attributes and, therefore, are persisted indefinitely. They are controlled by the AlterTableAttribute, AlterDatabaseAttribute, and the AlterDomainAttribute commands. The distributed architecture and independent nature of the AxleBase instances requires disk storage of discretionary as well as persisted locks. Autonomic Lock Domain Autonomic locks are system locks and are introduced only to complete the picture. They are seldom noticed and are visible only when a desired action is denied because of a system lock. A complete description of the autonomic locks would require separate documentation. AxleBase is continually applying and removing autonomic locks during any operation. The autonomic locks are applied and removed by the system as needed to support user activity. The previous index example used an autonomic lock. Share locking is used whenever possible. Share locks permit simultaneous reads, but prevent writes to the object while it is being read. In some areas, such as small system tables, he uses only write locks to preclude all reads until the write is complete and employs spinlocking to prevent read failures. Autonomic locks allow data updates to use dirty reads or read-through locks which allows the data to be read during an update. They increase speed and are used with success in many database manager brands. A row update failure is not retried. If the update fails, an error is returned and the host or user is expected to take the appropriate action. Row updates are checked by rereads. Discretionary locks, persisted locks, and autonomic locks are operationally integrated and respect each other. Autonomic locks take precedence, but discretionary and persisted locks honor each other.
Embedding And Running AxleBase Before addressing concurrency, it is important to observe a few very important facts in the AxleBase operation.
Concurrency is handled entirely by autonomic locks. Their activity is never registered in the lock tables since they are answerable only to each other, but they generate error messages appropriate to each concurrency problem. The host must be prepared to react to those error messages. An important part of concurrency control is the spinlock value which may be adjusted by the DBA. When an instance encounters an object with a system lock, it performs a spinlock until the object is available up to the specified interval. If the object is not released in that period, the instance executes an access failure. The spinlock value must be neither too large nor too small to adequately support concurrency for each environment. (See the Spinlock section of the Configuration chapter.) The query timeout is used in a way that is similar to the spinlock. In general, the query timout is applied to a table object and the spinlock value is applied to system objects. As AxleBase operates, he uses many kinds of locks on various objects and sometimes locks objects similtaneously and sometimes sequentially in a single operation. It is, therefore, difficult to describe the operations in a small document. In general, he uses share locks for reads and exclusive locks for data updates, but that is only a general statement. When he begins reading a table, he places share locks on all of the objects that constitute a table. As he finishes with each object, he removes the lock. Therefore, it is possible that a complex query could, in some situations, be interrupted by another instance putting a write lock on the table's index or pointer table, for example. That could happen if the query accessed the index multiple times. It might also happen if the table has billions of rows because its pointers are accessed every one hundred segments. Although unlikely, those events are possible. Tests, however, show AxleBase performing well at his design objective of low concurrency. The requirements of updates are more complex than those of reads. Keep in mind the fact that AxleBase must be prepared to update petabyte-sized tables. The rows must be located that will be updated and then read like a query. Then they must be prepared for the update, and then the new rows must be committed, and finally, the old rows must be deleted. The easiest way would be to lock the entire table and all of its component objects, and that will work for small tables, but that would ruin concurrency in very large tables, so it is not a viable option for AxleBase. Traditional transactions were considered, but they are unwanted because they would slow operations. The AxleBase solution for updates is to update with shared locking. All of the rows are found and read and new rows are constructed. Then the writes begin. The new updated rows are all added to the table and the old ones are then. For very large tables, all segment updates are performed simultaneously for the entire table. Notice that a reader can jump into the middle of an update and read a row before the update is committed. That is because the primary objective of AxleBase is data storage and OLTP, on line transaction processing, is secondary. The AxleBase position is that data may change now, five minutes from now, or next week, and the reader must know that fact regardless of when it changes. A simple insert is another matter. If it is a normal insert, which is usually a relatively small number of rows, it will be a quick operation, and locks will usually not even be noticed. If it is a very large table build, then the DBA wants a high speed operation so that millions of rows can be inserted around the clock and may even build off site. In either case, a write lock is needed, so a write lock is placed on each table segment until the write is complete or until the segment is filled. Note that those concurrency locks are not recorded and will not show up in the ShowLocks report. All of those lock operations are expected to be far quicker than the generation of a report. Even the build of a very large table uses quick intermittent locking. (See the Testing section for results of concurrency stress tests.)
Embedding And Running AxleBase
Column and row level granularity are not functional. They may be considered later, but they do not presently seem to be consonant with the unusual objectives of AxleBase. Locks may be placed at each level, but are vertically additive through the object hierarchy. For example, an operation involving a table will apply locks for the table, database, and domain before attempting the operation. An operation at the database level will first check both the database and the domain locks.
Embedding And Running AxleBase
The lock types follow an extended crud model. Each is a denial of a type of service. They are additive and multiple types may be set simultaneously. They are also additive hierarchically, so no level over-rides another. (Note that the denial of service is unlike the security system which grants service.) A type x exclusive lock takes an object offline and locks out all other users and processes until it is removed. A lock type may be used in multiple levels and multiple lock domains. For example, an exclusive lock is discretionary and not persisted. A "z" lock is the only system lock that may sometimes appear as an explicit code.
_________________________
Embedding And Running AxleBase A column-data type is neither a column type nor a data type, but is a combination of the two. They agree in most cases, so we tend to refer to them as one or the other, but the technical difference should be remembered because the two are not always the same. AxleBase data storage is in human readable form regardless of data type. If you have a favorite name-brand database manager, there is a comparison table in the Data Type sub-section of the Virtual Databases chapter. (If a type in your current database manager is not shown, send a note because I have only worked with a dozen or so different brands. )
Embedding And Running AxleBase BLOB is an abbreviation for Binary Large OBject. A blob can be anything that can be stored by a computer in a file. It is therefore, not considered data. It might be a photograph, music, text, etc. A BLOB is any object that can be retrieved and manipulated by software that is specially built for that type of object. Every BLOB in the database has a handle which completely and uniquely identifies the BLOB and which completely and uniquely identifies its location. The BlobHandle in the Windows operating system is the path and name. A query for the BLOB object will return its handle which is a pointer. The pointer can be used by BLOB handling software to load the BLOB object. AxleBase may be embedded in the BLOB handler or the handler can use a different host to do the querying. The blob column width is discretionary because it must be able to contain the entire BlobHandle. Other characteristics are identical to the STRING type. The operation of this type is unlike any of the others.
Embedding And Running AxleBase The boolean type has only two value states which specify affirmation and negation. Columns are defined as boolean with no length. The boolean type is stored as a single numeric character of either a 1 or a 0. The one is affirmative and the zero is negative. The following boolean values are recognized by AxleBase in SQL statements.
Embedding And Running AxleBase Columns are defined as date with no length. Storage and return are in the standard coredate format which has been truncated to its leftmost eight digits; i.e., YYYYMMDD; e.g, 19620130, which many people read as January 30, 1962 or perhaps as 30 January 62. Since the DATE type is a truncated version of the coredate protocol, it has lost the BC descriptor character. If dates BC are required, then the standard DATETIME type or the standard DATETIMEX type must be used. The DATE type is based upon the coredate protocol which may be found in the appendices. All operations use the truncated coredate format. The convert function may be used to reformat other date formats. See also DATETIME, DATETIMEX, TIME, and TIMEX data types. ( Storage is in human readable form without tokenization in consonance with the AxleBase design objectives.)
Embedding And Running AxleBase Columns are defined as datetime with no length. Storage and return are in the standard sixteen digit coredate format; i.e., YYYYMMDDHHMMSSNN; e.g. 2010012410230400, which many people read as January 24, 2010 at 10 23 04 AM or perhaps as 24 January 10, 10 23 04 AM. The coredate protocol specifies that the final character of the string may be a digit or a minus sign descriptor character. If it is a digit value, the date is AD; if it is a minus sign, the date is BC. The last two digits are decimal parts of a second. Tenths and hundredths. If greater precision is required, see the DATETIMEX type. The acceptable DATETIME range is from 99990101 BC to 99991231 AD. Dates outside that twenty thousand year range must be stored as STRING types. The DATETIME type is based upon the coredate protocol which may be found in the appendices. All operations use the standard coredate format. The convert function may be used to reformat other date formats. See also DATE, DATETIMEX, TIME, and TIMEX data types and the protocol appendix. ( Storage is in human readable form without tokenization in consonance with the AxleBase design objectives.)
Embedding And Running AxleBase Columns are defined as datetimex with no length. Storage and return are in the standard extended twenty one digit coredate format without the optional time zone designator; i.e., YYYYMMDDHHMMSSNNNNNNN; e.g. 201001241023040062384, which many people read as January 24, 2010 at 10 23 04 AM or perhaps as 24 January 10, 10 23 04 AM. The coredate protocol specifies that the final character of the string may be a digit or a minus sign descriptor character. If it is a digit value, the date is AD; if it is a minus sign, the date is BC. The acceptable DATETIMEX range is from 99990101 BC to 99991231 AD. Dates outside that twenty thousand year range must be stored as STRING types. The last seven numeric characters are decimal parts of a second. Accuracy is limited to one millionth of a second. The DATETIMEX type is based upon the coredate protocol which may be found in the appendices. All operations use the standard extended coredate format. The convert function may be used to reformat other date formats. See also DATE, DATETIME, TIME, and TIMEX data types. ( Storage is in human readable form without tokenization in consonance with the AxleBase design objectives.)
Embedding And Running AxleBase A column width should not be specified for this type. The actual storage width is 20 characters. The INTEGER data type accepts any literal printable whole number up to twenty characters which may include a minus sign. Commas are disallowed. The INTEGER data type was added to speed indexing because indexing the numeric type is far slower and the operation of the indices is slower. AxleBase does not tokenize, categorize, segregate or alter numbers. They are stored as literal strings. The twenty characters sets the maximum size. If the number of characters in a integer exceeds twenty, the number is truncated to the left-most twenty characters. Therefore, a number may be no larger than 99, 999, 999, 999, 999, 999, 999, and no smaller than -9, 999, 999, 999, 999, 999, 999. See also the NUMERIC data type. ( Storage is in human readable form without tokenization in consonance with the AxleBase design objectives.)
Embedding And Running AxleBase A column width should not be specified for this type. The actual storage width is 20 characters. The NUMERIC data type accepts any literal printable number up to twenty characters which may include a sign and a decimal point. Decimals must follow the American standard. Symbolic representation is not acceptable. Commas are disallowed. Where possible, the INTEGER type is recommended for speed. The INTEGER data type was added to speed indexing because indexing the numeric type is far slower and the operation of the resulting indices is slower. AxleBase does not tokenize, categorize, segregate or alter numbers. Thus, float, decimal, tinyint, bigint, integer, plus, minus, etc. ad infinitum, are all stored as the numeric type. This also means that AxleBase respects precision. If a fifteen decimal place number is handed to AxleBase, he precisely stores all fifteen places. The twenty characters set the value limit and precision of the number. If the character count in a number exceeds twenty, the number is truncated to the left-most twenty characters.
See also the INTEGER data type. ( Storage is in human readable form without tokenization in consonance with the AxleBase design objectives.)
Embedding And Running AxleBase A column width should not be specified for this type. The actual storage width is 20 characters. A table can have only one serial type. The serial type is numeric and is maintained by the system. Each row of a column may be expected to have a unique numeric value for that column and row. Values are incremented for each new row, but are not necessarily consecutive and cannot be predicted. Where the system requires a value, such as in an arrayed insert, any value or a null value may be specified, and the actual insert will become a system-generated value. A speed increase may be achieved in some cases by using a SERIAL column as the table key. This is certainly true where a table requires a key and there are no unique indices in it. Consistent with the AxleBase objectives, this column is not automatically indexed as some database managers do. The database administrator makes those decisions. The values may be expected to be unique through the entire column across segment boundaries. However, be cautious of assuming uniqeness in every serial column. Virtual concatenated tables, for example, will certainly not be unique, and there are other exceptions. At this time, the maximumn value for each column is 1x10^16 including deleted rows. To add a serial column to a table that is in production :
Embedding And Running AxleBase It is called STRING because it is any string of characters including the IBM ASCII extension. The maximum length must be specified when creating a table, and that will become the enforced width of the column. (This type is sometimes referred to as "alphanumeric", which is fine as long as we remember that the AxleBase system can also includes all of the other characters.) Control characters and control strings are removed from string data before it is inserted into a database to safeguard the database. Therefore, it is better to store strings containing any extended or unprintable ASCII characters as BLOB data. AxleBase trims leading and trailing white space in and out. (Since this is contrary to the ANSI 92 standard, it may be revisited in the future.) The maximum STRING length is determined by the maximum row length, which is approximately two gigabytes. For long STRING data, BLOB storage is sometimes a better management alternative. Review the AxleBase Limits sub-section before designing data objects.
Embedding And Running AxleBase Columns are defined as time with no length. Storage and return are in the standard coredate format which has been truncated to its six digit time; i.e., HHMMSS; e.g. 202304, which many people read as 8:23:04 P.M., or as 20:23:04. For precision better than a second, see the TIMEX data type. BC times cannot be stored in this data type. See the DATETIME and the DATETIMEX types for BC time storage. The TIME type is based upon the coredate protocol which may be found in the appendices. All operations use the truncated coredate format. The convert function may be used to reformat other date formats. See also DATE, DATETIME, DATETIMEX, and TIMEX data types. ( Storage is in human readable form without tokenization in consonance with the AxleBase design objectives.)
Embedding And Running AxleBase Columns are defined as timex with no length. Storage and return are in the extended coredate format which has been truncated to its thirteen digit time; i.e., HHMMSSNNNNNNN; e.g. 2023040060523, which many people read as 8:23:04 P.M., or as 20:23:04. The last seven numeric characters are decimal parts of a second. Accuracy is limited to one millionth of a second. If accuracy of that depth is not needed, perhaps the TIME data type might be more viable for the application. BC times cannot be stored in this data type. See the DATETIME and the DATETIMEX types for BC time storage. The TIMEX type is based upon the coredate protocol which may be found in the appendices. All operations use the truncated extended coredate format. The convert function may be used to reformat other date formats. See also DATE, DATETIME, DATETIMEX, and TIME data types. ( Storage is in human readable form without tokenization in consonance with the AxleBase design objectives.)
Virtual Databases Unfortunately, I do not have time to study all of the products on the market. The following are some data types from a few of the more popular database managers with suggested data type mappings. If you do not recognize a source type, then you probably do not need to recognize it.
_________________________
Embedding And Running AxleBase The host system is expected to be able to react to all error situations. AxleBase's deeply embedded nature precludes all GUI error messages, so all user interaction must be handled by the host system. If the host system is incapable of capturing errors which AxleBase returns, then Axlebase logging may be turned on to capture them. All operations are capable of returning error messages as variable length character strings. If an unexpected return is detected, it is probably an error message. In any event, all returns should be inspected for an error notification. The last error is stored and is not cleared until the next error overwrites it. The host system can clear that message. (The LastError functions are covered in the API chapter. ) If the activity log has been turned on, when an error message is returned, it is additionally written into the activity log. A message is sometimes written as multiple log rows. The first row always contains the header and the identifier. A logged error may be terminated by the entry, "End of error report.**". For most errors, AxleBase will simply raise the error and abort the process. In any case, AxleBase will attempt to recover and continue operation through the error situation. In the event of a system crash, AxleBase will attempt to log the situation before going down, but that is not possible in some operating system crashes. In the event that a table becomes corrupted beyond repair, and there are no backups, the host application may delete the table. AxleBase will re-initialize it and continue operations. With Reservations :
Embedding And Running AxleBase Format of error messages:
The return consists of seven elements. The element separator consists of a colon and two spaces. Elements may sometimes be zero length. Note that only elements 1, 4, and 6 are required, but all separators are always present. AxleBase may sometimes include responses from other systems in the addendum when those might be helpful. Therefore, when evaluating an error, the host should proceed from the header to avoid the confusion of appended responses. The first element is the message header. The message header is always present and consists of the eight character string, "**ERROR:". No other type of AxleBase return will have that header. If AxleBase can identify the type of error, the type may follow the header. Otherwise, the type may be blank. The type has variable content. The source of the error follows the type. It is usually blank. The source is the source of the error message and not the cause of the error. The source has variable content. So that the host can positively identify the message, the string "AxleBase" follows the source. Case will usually be as shown, but may be changed under some circumstances or by other systems. The message identifier, msgId, is a five byte alpha-numeric identifier. It always follows the AxleBase identifier and precedes the text message. (During development this identifier is subject to change for any given error.) Host code that references specific errors must be reviewed when upgrading to a new release of AxleBase. The "text" in the illustration is the literal text description. The error text is intended for human communication. Its text string is standard and associated with only one message identifier. To enhance human readability, text stings will sometimes contain characters 13 and 10 and white space for log formatting. The addendum is additional explication that is sometimes added onto the string. It is intended to amplify the standard message. It is frequently an empty string. A given message may be generated by various sources for various reasons and causes. Therefore, an error message may sometimes appear to be duplicated by some identifiers, sources, etc.
Embedding And Running AxleBase The exception master list is made available to the host application by the Manager object. Use the ShowErrorList command to return a list of all of the errors, or use the optional error number parameter to return the specified error. Syntax:
Returns the message specified by errorNumber. If errorNumber is blank, the entire list is returned with each message terminated by characters 13 and 10, carriage return and line feed. The identification number and the text are returned. The size of the entire list varies and depends upon the current system release. It may be fifteen to twenty thousand bytes. Example:
Example:
The interface also provides a means of recalling the last error.
_________________________
Embedding And Running AxleBase AxleBase security is designed to be:
It is unobtrusive because, unlike the big-name brands, AxleBase does not force the use of its security. AxleBase has been designed primarily for the kinds of databases that are usually accessible only by a few. A new AxleBase installation requires no security configuration, no passwords, no user setup, etc. Tables can be read as soon as they are created. However, it is possible to design and implement an extremely complex security structure for an installation. Since AxleBase is embedded, the host application and the host operating system are expected to share security responsibility. Security features are effective only when the host is a database server. (See the Demonstration Database Server in the AxHandle chapter.) For example, password access means little when somebody can locally access the password file. By restricting access to remote connections, the database server completes the security. AxleBase is designed to provide extreme security when it is needed. Implementing and administering AxleBase security can require a great deal of thought and time because it allows detailed and tight control.
Embedding And Running AxleBase AxleBase installs with security turned off. When he creates a domain or database, the object defaults open to all access. The security toggle is the password for each object. As long as a domain or database has no password, there is no security for it. Giving it a password turns on security for it. Removing the password turns it off. If a database has a master password, then access is restricted only for that database and for that entire database. That is true for each database. Access can be restricted for the entire domain by setting a password on the domain. If a password is set on the domain and no database in the domain has a password, then entry into the domain will permit unlimited access to all databases in the domain. The AlterDomainAttribute and the AlterDatabaseAttribute commands are used to set passwords. The password parameter in the "Configuration" chapter has guidance and examples. If that were all of the security, then security would be a state of all or nothing. But finer granularity is provided by the GRANT and REVOKE commands. (See GRANT and REVOKE in the API chapter.) Individual grants over-ride master passwords. If security is turned on for a database and an individual has been granted access to it, then the individual can enter the database without knowing the master password. However, that over-ride only allows passage through that level and gives access to objects only insofar as the grant allows. The GRANT and REVOKE commands provide additional granularity. Providing the master password in the connection command is not what is usually desired because it immediately allows unconditional access to the object as an owner. When access is controlled by granted privileges, then those privileges are each explicitly granted to the individual. The AxleBase GRANT and REVOKE commands allow control of domain and database ownership. For example, granting ownership to the domain allows the individual to use the GRANT and REVOKE commands. Before turning on security, insure that the administrators have been granted the necessary privileges. Before turning on domain-level security, be sure that individuals have been granted access to the domain so that they can pass through it to get to their databases. To do that, create a grant to the domain with no privileges. The domain will recognize the individual and let him pass through. If the grant is made with a password, then the individual must enter his password before he can pass through. Recommendation: Domain level security is not required for database level security. For the implementation of serious security for an installation, the domain level security may be turned on and configured in addition to the database security.
Embedding And Running AxleBase A security stop is a refusal by the system to perform an operation for somebody who does not have the required permission/rights/clearance. It is customary for high end database managers to hide security stops. That is not a bad thing and it certainly enhances security. However, millions of manhours have been wasted by hidden security stops. People who have a tendency to assume responsibility have spent endless hours debugging a healthy app because the database server simply had not been correctly updated by an administrator. Additionally, security stops can sometimes hide valid problems. I have personally found problems in big-name brand systems which appeared to either be in my code or was a security problem. Another problem is simply the complexity of all of the involved systems. Telling a DBA that you cannot access a table does not give him much information. The problem could simply be in your local network router. So security can be equally problematic on the server side. AxleBase allows security stops to show. When the time comes to put the database into production, if security is a serious issue, the administrator can turn off the return of stops. The value for each database and domain defaults to no. When security is turned on for an object, the value of HideSecurityStops should be evaluated. In a development environment, the value can be turned off in dev databases and turned on in prod. Although a security stop is not a system error, it will be an error as far as the host system or operator is concerned, so security stops are returned as a error messages. See also the "Hide Security Stops" section of the "Configuration" chapter.
Embedding And Running AxleBase AxleBase is designed to maintain canonical integrity at all times. Those who seek the undocumented tricks that can be done in other database managers will not find them. Other systems have excellent security that was created for design objectives that are very different from those of AxleBase. Oracle's security is legendary although it uses canonical transparency. Ms. SQL Server has great security although it uses canonical permeability. So this is not to suggest that the canonical transparency or canonical permeability of Ms Sql and others is wrong. But AxleBase has chosen to strive for canonical integrity. The closest that AxleBase might come to behaving like others would be in his virtual objects. Please refer to the Virtual Databases chapter.
Embedding And Running AxleBase The architecture of an AxleBase installation is designed to present multiple levels of administrative control, and each of those levels is segmented to permit zoned administration. That administrative control includes the realm of security. Multiple levels are achieved by placing a group of databases under the control of a domain. A database can be opened only by first opening its domain and then opening the database through the domain. Security can be turned on and applied at the domain level or at the database level or at both levels. AxleBase is universally vertically segmented so that parts of an installation can go to any extreme or degree of security independently of other parts. For example, multiple domains may be established in an installation so that databases can be grouped under the various segmented security umbrellas. The same applies to the various databases. ( Additional lower levels and segmentation at those levels are being considered. ) All of those points apply to the SAM ( Storage Architecture Model ) which has been developed for AxleBase.
An AxleBase installation may also be designed as an axsys which is also discussed in the Tuning section. An axsys is expected to be distributed across computers and disks to give the installation more processing power. Distribution may be of AxleBase instances, database domains, databases within domains, or within databases. If the installation is distributed, then the physical attributes of the segments may be used to further increase security. For example, segments may be placed on computers which limit access through the operating system permissions.
Embedding And Running AxleBase AxleBase provides the ability to set master passwords for each database domain and for each database in the domain. The security system is turned on by setting a master password. It must be turned on for each domain and for each database. A master password is set or cleared with the AlterDomainAttribute and the AlterDatabaseAttribute commands. See the command in the API section for syntax and the appropriate section in the Permanent Configuration Of Objects section for discussion. Opening an object with its master password allows operation of the object with ownership privileges. Ownership operation also reduces audit logging. It is therefore prudent practice for the DBA to grant access rights to himself as well as others so he can log on with his identity. Unique passwords may also be assigned for each individual-object combination which would be required when the object is accessed by that individual. (See also the GRANT and REVOKE commands in the API chapter.) Individual passwords are effective only when security is turned on. Commands are designed to fail silently when incorrect passwords are passed. If the host application fails to check the state of the object, the host may continue processing, oblivious to the fact that it is talking to nothing. This can be expected to produce inexplicable errors and potential instability in the entire system. Passwords should not be considered locally secure. With a little work, a system that can physically access the host system can find and read them. Maximum password length is 100 characters, but fewer characters may be used. Passwords are case sensitive. Semi-colons are permitted in passwords, but those passwords cannot be used in a connection string. End of line markers and carriage returns are not permitted in the password. Any other character that can be passed by the host system may be used. If the leading or trailing characters in the password might be confusing to the systems, the password may be delimited by apostrophes. Delimiting apostrophes will not be included in the password. For example, AxleBase routinely strips leading and trailing blanks from variables that are handed to him. If the password begins or ends with blank characters, then delimit it with apostrophes to protect those blanks. To protect internal apostrophes, the value is not checked for escaped apostrophe's. If the password has an external apostrophe, it will need delimiting apostrophes. (The system does not normally require apostrophes. They are allowed when needed to delimit a value to insure its literal acceptance.) Use care when selecting extended ASCII characters. If they are also used by a participating system such as the operating system, the results will be unpredictable.
Embedding And Running AxleBase This section assumes that the host app does not have a GUI to assist with the task. If it does have a GUI, then this section will tell you how the GUI works. AxleBase offers more flexibility and power than may be common. For that reason, what may appear to the user to be a simple command can actually produce powerful and complex results. When granting and denying access, the user should give thought to even the simplest command. A crud table specifies the type of access to an object that is allowed for each person or entity. When AxleBase receives a request for access to an object, he reads the appropriate crud table to determine actions that are permitted for the person or entity. The following are configurable permissions for each object:
The type of operation that is being controlled is interpreted by AxleBase. For example, the create right for a table allows the insertion of rows, for a database allows the creation of tables, and for a domain allows the creation of databases. And if the DBA does not have update rights on a database, he cannot alter a table. Creating and dropping indices are actually updates of a table's characteristics, so they require update rights. Also, the crud table can allow passage without rights. If, for example, security is turned on in the domain and in all databases, then granting a user-domain access with no privileges will allow that person to pass through the domain to get to his database. The crud table is updated by the GRANT and REVOKE commands. See their usage in the API chapter. Rights at the domain level should be granted only for administrators at that level. For example, domain update rights allow the person to grant privileges at that level. Delete rights will allow dropping an entire database. The flexibility and power given to the DBA in the GRANT command makes it possible for a careless DBA to create duplicate, ambiguous, or conflicting grants. When he receives a GRANT command, AxleBase attempts to find any existing grants that may be duplicates, conflicts, or ambiguous, but the subtle needs of that task sometimes exceed his ability. If the host app does not provide a GUI to ease the operation of granting access, it may be prudent to first use the ShowPermissions command and then analyze the existing security state. Rights at the database level should be granted only for administrators at that level. When security has been turned on, each person must be granted access rights to each object in the database. Please recall that a design objective of AxleBase is to return ownership of the database to the rightful owner, so now that we have a strong and sophisticated security system in place, let's honor that objective. Many who need security do not need such a detailed system. Therefore, AxleBase provides a means of crud table simplification by allowing the keyword "all". The keyword "all" can be used in any combination in the grants, the type of object, the object name, and the person's name. For example, a person may be granted rights to all tables in a database. The administrator must insure that such grants do not conflict with detailed grants. When used for the object type, it will not be applied to the domain and database objects. Permissions can be especially confusing for the inexperienced and for those who do them rarely. Permissions at the domain level are usually needed only by the domain DBA. Everybody else is just passing through. Likewise, permissions at the database level are usually needed only by the database DBA, and perhaps by the domain DBA. Domain and database permissions are both maintained at the domain level. Permissions at the table and job level are needed by all who work with the data. But the create and delete permissions that are granted for those objects pertain to the rows within them. The ability to create and drop tables and jobs is controlled by the create and delete at the database level. Likewise, the ability to create and drop databases is controlled by permissions at the domain level. Updating permissions must be done with the revoke and/or grant command. It may seem easier at first glance, but it is actually more complicated. The most positive way to insure that the correct permissions are given is to first revoke all permissions, check the file, then grant a new set, and check the file again.
Embedding And Running AxleBase Security begins at the domain level, then to the database level, and then to the objects within the database. A very light security that might be appropriate for a single user might be set simply by setting the domain password. Since no grants had been made, access could be attained only by presentation of the password. The problem with that is that only the owner of the domain could get to any of the databases, and if the password were shared with others, then they would have all of the owner's permissions. To prevent sharing domain ownership, permissions could be set at the domain level for individuals without giving any permissions. That would allow them to pass through the domain level to the databases. We will now try some heavy security in the following example. Hypothetical situation: AxleBase has been embedded in a server. It serves only a small department, but the very large database cost a great deal to build the institution directors are considering selling access to other institutions, so they want to lock it down very tightly. Since the database is being used already, the lockdown must have a minimal impact. The senior DBA takes the following steps in sequence. 1. Every employee is granted access to the domain.
2. At the domain level, the senior DBA grants himself "all" rights. This is not entirely necessary but it will allow him to work in the system without using the master password so as to maintain adequate audit trails. 3. Each database DBA is given ownership of that database by granting each of them "owner" rights to the database. That will allow them to administer the database including security.
4. Every employee is granted access to the appropriate database.
5. Each is granted appropriate access to tables and other objects in the database. 6. A connection string is prepared for each employee and he/she is briefed on how to open the database the next day.
7. If it is not already on, AxleBase logging is turned on at all levels. 8. The "hide security stops" parameter is set to yes for maximum security. Note that security is not yet on. Everybody continues to use the system as before. 9. After working hours, the senior DBA sets a master password for the domain and for each database which turns on security for the entire installation. This example may be too extreme for many installations and is presented only to show what might be possible.
_________________________
Embedding And Running AxleBase These are only suggestions and ideas for those who consider embedding AxleBase in a server to create a database server. These are suggested areas of consideration and a few thoughts for handling problematic areas and are not an attempt to be comprehensive.
Embedding And Running AxleBase The AxleBase VLDB abilities present an excellent opportunity for a server. A high-end VLDB operation will create a very large distributed operation which can benefit from management tools. For example, without a server, all computers on the VLDB network must be shared. Since the server will be using AxleBase, it can communicate directly with its computers without that administrative chore. A full featured VLDB server could be an extremely interesting and valuable asset. Before beginning the project, the builder should understand the nature of a VLDB including its value and its problems. An understanding of its theoretical nature would be beneficial in the conceptual design phase. A VLDB server should be aware of two major features that could present problems or opportunities for it. One is the shear mass involved. The other is extreme distribution. AxleBase is designed to handle tables so large that they can be comprehended only in mathematical terms. More than with any other database manager, an AxleBase server must stay out of the way so that AxleBase can manage massive operations. The distributed nature of an AxleBase database can be extreme. Even a single table can be scattered across the network on thousands of computers. That scattering is encouraged by making it a simple task and by making its use transparent to the operator. The AxleBase instance that is embedded in the server is designed to transparently handle massively distributed objects to relieve the host of that worry. The server could ignore that distribution, but the working DBA cannot, so it should be considered by the server designer. AxleBase has many tools which the DBA will use to manage his database. If your interest is in the VLDB realm, a careful and reading of the VLDB chapter is recommended. It contains ideas which are addressed to the specialized VLDB server.
Embedding And Running AxleBase AxleBase is engineered in such a way that multiple instances can be used in unison to work on an extraordinarily large task. That unit is known as an axsys. That is more than useful. AxleBase is designed to manage databases containing the largest imaginable tables so the axsys object is a necessity. The axsys and its use is described in the "The Distributed Database Manager" section of the VLDB Operations chapter. The nature of the axsys must be understood before the ANS concept can be understood. To help him handle massive data objects, AxleBase is designed so that he can function as multiple instances working on the same database and even on the same data entities. That ability presents interesting opportunities for the server builder. An axsys can work on multiple tasks on various objects or on the same object. All instances can even perform the same task on one object. The complexity increases when the DBA staff can understand and architect multi-dimensional operations. A node host could extend that power tremendously through inter-process communication across the network which would give the DBA more immediate control of distributed operations. (Remember that each instance will usually run on its own computer.) That tighter control of the distributed processes might in turn increase the number of instances that the DBA staff could use simultaneously. There are axsys configuration tables within each database. The functionality of an ANS can be extended through them. When the server is a node host, then it may be distributed with a central server console to manage the database operation. Various specialized server components, each with an embedded AxleBase instance, may be suggested by a reading of the VLDB chapter. The builder of a node host will find that many of the AxleBase commands are functions which are designed with that end in mind. The small size of the AxleBase object can be misleading. His design makes him one of the most powerful tools in existence. That power can also present powerful surprises which are counter-intuitive. For example, the builder of an ANS should become familiar with the synchronization warning in the VLDB chapter.
Embedding And Running AxleBase Error handling in the server might begin with an assessment of the AxleBase error protocol which is constructed especially for a host system. The AxleBase error sub-system tries to work in concert with the host to handle all possible errors including unrecognized and unhandled errors. There might be a classification of errors in the server so that those that must be handled at the central console are passed through and the rest are handled by the local server. The AxleBase protocol includes error numbering specifically for the purpose of identification by the host. The AxleBase error protocol is designed to allow a host to easily strip out parts of the error return before retransmission.
Embedding And Running AxleBase Most of the security sub-system is intended for use by a server. For example, the password feature is obviously secure only when it is used by a server. A server should be aware of those commands that are designed to fail silently although action may not be needed by the server. Some thought might be given to the SAM's impact on security. The server design, for example, may account for the manner in which domain's are handled. A domain must be opened before one of its databases can be opened, but that could be done by the server to prevent the creation of new domains, or the server might entirely filter out domain openings. The server design may elect to filter commands. For example, the builder might elect to deactivate the locate command. AxleBase allows all ASCII characters in passwords and in some areas outside security. That could require more programming esoterica than desired, so the server might be designed to filter or reject extended characters in command parameters.
Embedding And Running AxleBase The AxleBase interface is designed to support the ODBC protocol for the benefit of servers and ODBC drivers. A few of its more esoteric features were intentionally ignored. The AxleBase interface also supports Oracle style native connections which do not use the ODBC protocol. That type of connection is demonstrated in the AxHandle server. If that approach is used, it may be possible to reduce the complexity of the server by passing AxleBase commands from the remote client directly to AxleBase on the server side. A native connection need not necessarily locally instantiate a Manager object. That is touched on in the AxHandle section where the command string is discussed.
Embedding And Running AxleBase Although he supports concurrency, it must be remembered that AxleBase is designed for purposes other than heavy concurrency. The use of one of the big-name brands is recommended for heavy concurrency; MsSql, Oracle, MySql, Access, etc. (See the test section in the introduction.) Since he is designed to be embedded, AxleBase provides some assistance in this area, but cannot do the whole job. For example, queuing must be handled entirely by the server. The use of a server with the AxleBase features with queuing should increase concurrency. The addition of a server queue and other mechanisms are possibilities. The server builder might also consider how AxleBase handles concurrency issues. It might be decided to pass all concurrency errors back to the client, or the server might monitor error numbers and intervene to modify system behavior.
__________________________________________________ Chapter 4 A.P.I. __________________________________________________
_________________________
API Lexicon And Syntactical Reference If post-return processing is needed, then the host is expected to provide it. The AxleBase API is intended to provide the functionality that is required for storage and retrieval. It is not intended to be a full function programming language although some DBA's attempt to use SQL for that purpose. See also the intentional abridgements in the introduction.
API Lexicon And Syntactical Reference The parser permits commenting SQL. Requirements:
Only one comment is allowed. (But it can be of any length and can be formatted.) Begin with the two character string "/*". End with the two character string "*/". Example:
API Lexicon And Syntactical Reference The following symbols are, or were at one time in database history, standard logic operators. The 'like' and the 'sounds' operators do not take advantage of indices. They always do a table scan. To help beginners and those who know only one or two SQL dialects, AxleBase supports more conditional constructs than necessary. (Some of the lesser known are, or have been, used in extant or dead big-name database managers.) The following logical operators are currently supported by AxleBase in the SQL where clause. Those who want to continue using their favorite syntax can do so without penalty. The conversion is shown as a suggestion toward the simplicity of uniformity.
The "SOUNDS LIKE" uses the original soundex algorithm. (Courtesy of the American taxpayer like so much of what we take for granted despite the Big Names taking credit.) Soundex delivers approximate results and works for standard American pronunciation of people's first names. It can increase processing time tremendously for large files. The logical constructs that use null follow the same conversions. Some attempt is made to standardize incorrect null constructs, but that cannot be depended upon simply because I have moved on to other areas. There is currently no plan to support the old "isnull". It must be "is null".
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. It adds the specified number of units to the specified date. The unitType is one of those shown in the datediff table. Only whole number quantities are accepted. The date must be one of the AxleBase date types; date, dateTime, dateTimex. (See the Column Data Types section of the Embedding And Running AxleBase chapter.) The operation construct must be consonant with the fact that dates BC are respected. Accuracy : AxleBase does not yet account for leap-seconds and leap-centuries. The calling operation must be prepared for the return of an error message. If a datum is encountered in the job stream which cannot be coerced to a valid value and value type, then AxleBase returns an error. (The host is expected to be able to perform its own calculations, so functions are not acceptable in the SQL column return clause.) Return expected: Date.
Example:
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements to convert a string to a date datatype. Variable is a date which is formatted in one of the commonly used date formats or is an AxleBase date format. It may be either the name of a column or a delimited literal. The return will be in the format that is specified by the datatype parameter. If the request includes data that is not contained in the variable, then the extra data will be returned as zeros. Fictitious data may be generated by specifying the fictitious DateTimeF datatype. Datatype is a literal expression which specifies an AxleBase data type. If a datum is encountered in the job stream which cannot be coerced to a valid datatype, AxleBase returns an error. (The host is expected to be able to perform its own calculations, so functions are not acceptable in the SQL column return clause.)
The calling operation must be prepared for the return of an error message. Example:
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. It returns the number of whole units by which the two dates differ. The second date is subtracted from the first. The unitType specification must be one of those listed. (Note: One purpose of the coredate protocol is to ease data handling. If the objective is a date comparison and the two dates are in the same format, then dateDiff is not needed and a direct comparison will work. For example, '2007090214253604' > '1997082523153245' will work. Permissible unitTypes:
The data type of the values must be date, datetime, or datetimex, although they are not required to be the same. (See the Column Data Types section of the Embedding And Running AxleBase chapter.) The operation construct is consonant with the fact that dates B.C. are respected. The complexities of date arithmetic can be misleading due to its nonlinear logic and due to psychology. For example,
Accuracy :
The calling operation must be prepared for the return of a non-numeric value to allow for an error return. If a datum is encountered in the job stream which cannot be coerced to a valid value and value type, then AxleBase returns an error. (The host is expected to be able to perform its own calculations, so functions are not acceptable in the SQL column return clause.) Return expected: Numeric.
Example:
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. It returns a date from the specified number of days. Negative numbers return dates B.C.. A number outside the valid date range will generate an error. The date will be an AxleBase DATE datatype. Accuracy: AxleBase does not yet account for leap-seconds and leap-centuries. The calling operation must be prepared for the return of an error message. (The host is expected to be able to perform its own calculations, so functions are not acceptable in the SQL column return clause.) Example:
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. It may be used to insert the current date as a literal in the SQL string. The optional parameter may be one of the AxleBase data types; date, datetime, datetimex, time, or timex. If omitted, the datetime type will be returned. The parentheses in this function are not optional. Times that are generated by computers are generally suspect. Times that include seconds are especially suspect and greater precision than that increasingly so. AxleBase will return a time that includes hundredths of a second, but the caller must be aware that the precision is only a relative value. Timex and datetimex values will contain only placeholder zeros after that value. Testing sometimes requires the generation of unique datetimex values. The host can coerce AxleBase into returning fictitious values in place of the placeholder zeros by entering the fictitious DateTimef parameter in the type option. DateTimef values are assigned sequentially from a pool of 100,000 for all current processes by AxleBase. The generation of 100,000 values per second may be expected to produce unique values for each object at the current speed of computers. (The host is expected to be able to perform its own calculations, so functions are not acceptable in the SQL column return clause.) The calling operation must be prepared for the return of an error message. Spaces are not permitted in the string. Return expected: Date string.
Example:
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. It returns the number of days in the specified date as a long integer. In other words, the return is a numeric variable and is not a string. Dates B.C. are returned as negative numbers. The specified date must be one of the AxleBase date data types; date, dateTime, dateTimex. Accuracy :
If a datum is encountered in the job stream which cannot be coerced to a valid datatype, AxleBase returns an error. (The host is expected to be able to perform its own calculations, so functions are not acceptable in the SQL column return clause.) Example:
API Lexicon And Syntactical Reference The DeferReturn tells the data vector to compile the data return on disk so that none of the computer's RAM is used. It is an AxleBase-specific SQL clause designed for working with very large data returns. It tells AxleBase to stage the result of a select and to defer building a dataset until requested. The deferReturn is used inside the SQL statement. After the SQL runs, it appears that nothing happens, but a ShowAttributes command will show the size of the staged return. The ReturnDeferred command will perform controlled returns of segments of the dataset. ( This is a non-standard AxleBase SQL extension to support a VLT. Beginners are advised to avoid non-standard SQL in any database manager and to be wary of those brands which do not provide this warning.) All other operations in the query are performed up to the dataset build. Then, everything pauses until a return is requested. When a return is requested, the dataset will be built. The number of tuples that are staged is available upon request, but no operations can be performed on them because the dataset is empty. When used, DeferReturn must be the last clause in the SQL statement. See also: The Defer Returns section of the Configuration chapter. The data Vector object ReturnDeferred command. Return expected: None.
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
Provides a way to specify a match of any single value in a list of values. Values are evaluated from left to right until all are tested. The process exits at the first match. Hint :
Hint :
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. Returns a boolean evaluation of the value. If the value is a valid date or datetime value, the evaluation is positive. This example returns yes.
This example returns no.
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. Returns a boolean evaluation of the value. If the value is a valid time or timex value, the evaluation is positive. This example returns yes.
API Lexicon And Syntactical Reference ( Under construction or consideration and not yet operational. ) Locate is a function that can be placed in a SQL clause to locate a data source in a very large and distributed table. When used, each tuple in a dataset is tagged with its source. To avoid confusion, it may be used only in a select query that has no joins. It must be placed at the end of the query immediately preceeding the deferReturn. It is a function so the parentheses are expected. The source tag is sixty characters long, consisting of three elements, the segment number, the row number, and the computer. The computer is available only if the segment location included the computer name or address when the segment was created. The count for each number begins with the read, so the number is relative to where the SQL statement starts the read. If the read is permitted to begin at the beginning of the table, then the location numbers will match the table's segment and row numbers. If it begins at the first row of a segment, then the row numbers will match those in that segment. ( The Locate clause is a non-standard AxleBase SQL extension to support a VLT. Beginners are advised to avoid non-standard SQL in any database manager and to be wary of those brands which do not provide this warning.) Return expected: Locations of all tuples.
Example:
API Lexicon And Syntactical Reference ( Under construction and not yet available. ) Math operators are:
Math operators are supported only in the select and where clauses. Hosts are expected to be capable of manipulating returns.
API Lexicon And Syntactical Reference Syntax:
The row clause is an AxleBase-specific function which may be used to target specific table rows in a SQL statement. It was developed primarily as a VLDB tool to target a range of rows in a very large table. A SQL statement may contain multiple row clauses to control multiple tables in the query. There may be only one per table and each must immediately follow the table to which it will be applied. ( The row clause is a non-standard AxleBase SQL extension to support a VLT. Beginners are advised to avoid non-standard SQL in any database manager and to be wary of those brands which do not provide this warning.) The Row clause may be used in the following types of SQL statements :
Deleted Rows :
Updated Rows :
Since deleted rows are no longer bypassed, the location of a target row now remains unchanged by deletes and updates until the table is purged. However, a target row may have been deleted. The result is that targeting the billionth row will return nothing if that row has been deleted and not purged. A large table segment may be purged of deleted rows. Parameters :
The first AxleBase row is number 1 and not number 0. (Please do not laugh because that is a serious problem for some older programmers.) When a row clause is used in a SQL operation, AxleBase will begin at the specified row and will read no more than the specified quantity of rows from the table. In a multi-table join, each table may have a row clause. Speed Variance :
Exceeding Table Size :
Deletes :
Updates :
Segment Boundaries :
Row Clause With Segment Clause :
Indexed Tables :
Return expected: Dependent upon the application.
Example:
API Lexicon And Syntactical Reference Syntax:
The segment clause is an AxleBase-specific function which may be used to target specific table segments in a SQL statement. It was developed primarily as a VLDB tool to target a segment range in a very large table. It is sometimes used to achieve high speed through systemic parallelization. A SQL statement may contain a segment clause for each table. There may be only one per table and each must immediately follow the table to which it will be applied. ( The segment clause is a non-standard AxleBase SQL extension to support a VLT. Beginners are advised to avoid non-standard SQL in any database manager and to be wary of those brands which do not provide this warning.) The Segment clause may be used in the following types of SQL statements :
Parameters :
The base table file is always treated as segment number one regardless of its name. If a segment is added to the table, then that segment will be identified as number two regardless of its name. Without a segment clause, a SQL statement is applied to all segments including the base table. The segment numbers begin at one, which is the base table. Therefore, in a SQL statement, the first segment is the primary table segment. The segment clause constructs a fixed query window and may be used without concern for table topology. If a validly constructed segment clause exceeds the entity segment count, the excess is simply ignored. Thus, an operation starting at segment number 2000 of a 1000 segment entity will simply return nothing. That SQL statement will not generate an error and an error will be returned only if there is an operation failure. The segment clause may also be used without concern for location of the table or its components and without concern for table distribution. The system will handle all background tasks during the qurey. When a segment clause is specified in a SQL operation, AxleBase will read no more than the specified segments from the table. In a multi-table join, each segment clause will be applied only to its table. Deletes :
Updates :
Row Clause With Segment Clause :
Exceeding Table Size :
Index :
Return expected: Dependent upon the application.
Example:
Example:
API Lexicon And Syntactical Reference The following summary functions may be used in the SQL select clause. (See the data Vector object - Select sub-section for discussion of the SQL select.
AxleBase allows the count function to be abbreviated to cnt( ) to make it uniform with the other functions. A select function returns a calculated value which is derived from the contents of the specified column. The column must be specified and only the count function accepts an asterisk. Function returns are not permitted with tuple selections. A query that contains a function will return a single tuple containing the function results. An AxleBase function may be run against any column regardless of the defined data type and the data type will not produce an error. The validity of the return should be ascertained by the host. If the defined type is numeric, then the function will treat the data as such in the evaluations and calculations, and a string type will be treated as such. If a sum or average is requested for a non-numeric field, then only non-numeric values will be included in the result. The maximum value bound for a function is 1.79769313486232E308. ( A standard deviation function is not available because it is easily done in the host app. However, this is subject to re-evaluation. ) Each of the following examples will return a one-row dataset with the specified value(s) contained in that row. Example uses sReturn to catch error returns. Example:
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
This is an AxleBase function which is provided for use in SQL statements. It may be used to perform one or more of any of the following operations on a string of characters:
The concatenation operator is the ampersand. Leave a space on each side of the ampersand so that it can be recognized as a stand-alone character and not part of a variable. Values may be literals and may be column names in the where clause,. (The host is expected to be able to perform its own calculations, so functions are not acceptable in the SQL column return clause.) Example:
API Lexicon And Syntactical Reference Variables in the where clause may include wildcard characters. Those characters are recognized by AxleBase as wildcards only in the like construct. The AxleBase default multiple-character wildcard character is an asterisk. The multiple-character wildcard character represents any string of characters. The AxleBase default single-character wildcard character is a question mark. The single-character wildcard character represents any single character in that position in the variable. AxleBase permits changing the wildcard characters in each domain, but such is discouraged. See the Configuration chapter, Wildcard sub-sections. Example:
Example:
_________________________
API Lexicon And Syntactical Reference The Manager is an object which controls the databases. The general manner in which this is done is covered in the Embedding And Running AxleBase section and the commands that are used are covered in this section. The Manager object is also the link and pipe from databases; sometimes called the connection. A Manager object must be created before a data object can be used. It links the data object into a database and provides the pipe through which data is returned. Where a return is expected from a command, the return should be checked for an error message.
API Lexicon And Syntactical Reference Syntax:
This command is provided to stop very long running jobs without data loss or system disruption. The command is sent only to the Manager object, but it will also intercept Data Vector jobs. It is not recognized by all jobs. It is not provided to stop big jobs; just long-running ones. In tests, for example, AxleBase has shown that he can empty a ten billion row table spread across a network in a split second. The command is therefore entirely ignored by those jobs. If a massive select is started without the DeferReturn clause, this might be able to stop it before the computer crashes, but that is improbable. This command will work only if the YieldProcessor property is set to yes. (See the Yield Processor section of the Configuration chapter.) If it is set to no, the command cannot be passed. A job may not immediately respond to this command. Jobs can check for an abort only at certain points. The host should be capable of sending the command and returning a wait notice. (See the AxHandle demo for an example.) When the job is stopped, AxleBase returns a notice of success in the form of a standard error message. ( The addition of this ability was considered for a very long time because its existence certainly slows AxleBase. Normal database managers do not need it, but AxleBase is built for running massive operations. It has therefore been added with some misgivings and may be removed if complaints are received about speed.) See also: Return expected: Notice of a successful abort.
Example:
API Lexicon And Syntactical Reference ( The axsys object is operational, but it is still under construction and is subject to dramatic changes.) Syntax:
AxleBase contains an extended and distributed database manager called an "axsys". This command configures that object so that it can come on line. (See the VLDB chapter for extensive discussion of the axsys operation and control.) This configuration should not be confused with the domain, database, and table configurations which are covered in the Configuration chapter. ( Each axsys object belongs to a database so the axsys configuration belongs with the database properties. However, the large size and operational complexity of the axsys object prompted the removal of its management from database configurations.) Operation Type :
Attribute Name :
Computer and Login :
Value List :
Create Operations :
Delete Operations :
Node Value List :
For a node update or create, all values must be passed. Any not passed will be set to null. Individual Node Column Update :
Map Value List :
Row Value List :
Segment Value List :
Temp Value List :
Node ID :
Row Attribute :
Segment Attribute :
Map Attribute :
Temp Attribute :
See also: ReloadNode; ShowAxsys; AlterAxsysRunningNode; The Axsys section of the VLDB chapter. Return expected: Nothing.
Example drops the entire axsys configuration:
Example creates a node:
Example updates a node:
Example of same update using only the value name:
Example removes all of a node's row records:
Example removes a node:
Example adds a row record for a node:
Example adds a temp work location:
API Lexicon And Syntactical Reference ( The axsys object is operational, but it is still under construction and is subject to dramatic changes.) Syntax:
This command will tell the running instance to reconfigure itself into the specifications. It is a run-time command and will not save the alterations. Whereas the ReloadNode command tells the instance to reload from the stored configurations, this command directly changes the node configuration in the running instance. Specifications :
The entire node cannot be updated in a single string. Each of the node's parameters must be updated separately. Map, row, and segment settings will be appended to any existing settings. To clear all map, row, and segment values, submit the name with a blank value. Map :
Row and Segment :
See also: ReloadNode, ShowAxsys, AlterAxsys, AlterAxsysRunningNode, The VLDB chapter. Return expected: Nothing.
Example adds a temp work location:
API Lexicon And Syntactical Reference Syntax:
Sets the value of the named attribute of the currently open database. By design, each command can set only a single attribute. It is the responsibility of the host app to insure that all other user processes have closed the database and no tables are open before executing this command. If not controlled, this command can produce an inconsistent database state with corrupted data. This command makes one of the specified changes to a parameter for the currently open database. Although not necessary, it may be a good idea to close and re-open the database after this command is executed. If possible, it is probably best to reload everything. For locks, see the LockObject section. See the "Configuration" chapter for attributes and discussion. See also: ShowDatabaseAttributes. Return expected: Nothing.
Example setting a password:
Example dropping the password:
Example relocating a database:
API Lexicon And Syntactical Reference Syntax:
Changes a characteristic of the currently open database domain object. By design, each command can set only a single attribute. A database domain must be open before this command can be used. Some changes become effective only after the domain is closed and the master database is reopened. This command makes one of the specified changes to a parameter for the currently open database. Although not necessary, it may be a good idea to close and re-open the domain after this command is executed. If possible, it is probably best to reload everything. For locks, see the LockObject section. See the "Configuration" chapter for attributes and discussion. See also: ShowDomainAttributes. Return expected: Nothing.
Example:
API Lexicon And Syntactical Reference ( Under consideration or construction. ) Syntax:
Where sqlStatement is a complete SQL statement in the form:
May be used to add or to drop table keys. Only the key constraint is operational at this time. Note that the 'alter table' string is optional when using this command. The redundency engendered by employing standard SQL may cause some confusion. However, it is hoped that the use of standard SQL may make life easier for the experienced DBA. The command is used by the Manager object instead of by the Vector object because it treats a Vector object and not the data. The constraint name must be unique within the table constraints and indices. It must adhere to the AxleBase naming requirements. At the present time, constraint names are limited to ten characters. The table must be reopened after a structure change. See also: CreateIndex; DropIndex. Return expected: Nothing.
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
Sets the value of the named extended attribute of the named table. Note the syntactical identification of the target. The target table is specified by linking its name to the attribute name. This command is introduced to allow maintenance of a table's non-standard configuration values. A non-standard configurable feature of an object is an AxleBase attribute of the object. It is the responsibility of the host app to insure that no other hosts are using the table when the command is submitted. Incorrect use of this command can result in an inconsistent or corrupted database. Although not necessary, it may be a good idea to close and re-open the database after this command is executed. Most certainly, open datasets must be reloaded. See also: "Configuration" chapter. See also: ShowTable, ShowIndices. Return expected: Nothing.
Example:
Example changing to fixed width. Note the escaped commas separating the parameters.
API Lexicon And Syntactical Reference Syntax:
Behaviour depends upon the parameter. Parameter :
KEY :
Return :
Errors :
An AxleBase design objective is to run on low-power desktop PC's in addition to more powerful machines. To support that objective, the authentication mechanism must produce very large authentication strings. An authentication string should be expected to contain three to five thousand characters. This command is primarily for the support of an axsys that is running on a shared network. This is not a digital signature, but is a heavyweight exchange that is designed for use between AxleBase instances. Compromise of the internal algorithm is expected, so it may be changed at any time. For that reason, the release numbers of AxleBase instances in the organization should be the same. When one instance is upgraded, all should be upgraded. ( The algorithm is expected to be changed without warning, but not periodically. If local compromise is suspected, then a request may be honored for a new AxleBase release with a change. New algorithms should not be expected to be more secure, but only different.) Return expected: None.
Example requesting an authentication value :
Example requesting authentication of a value. :
API Lexicon And Syntactical Reference Syntax:
Creates an archive copy of the specified object. Optional Parameters :
ObjectType :
ObjectName :
ArchiveLocation :
Generational :
RetentionType :
RetentionValue :
The optional retention directives may be used only if the generational parameter is yes. When entered, the optional retention directives tell AxleBase to monitor the archives. All archived objects that are older than the specification will be immediately terminated without question. NodeSpec :
A domain must be opened before it can be archived. A database must be open before it can be archived. A domain backup will get only those objects at the domain level and wll not include its databases. To backup everything in the domain, backup the domain and then backup each database in the domain. If all are wanted, the ShowDatabaseCatalogue command can be used to cycle through them. Invalid objects in the target location may be purged by the backup. When the optional retention directives are used, anything in the target location that is not an AxleBase generational archive will be endangered. A root directory should never be used. External Objects :
Virtual Tables :
Distributed Tables :
Lock Requirement :
Contention Errors :
Purge :
Disk Space :
See also: Restore. Return expected: Nothing.
Example:
Example:
Example:
Example:
VLDB Addendum To Backup The following comments and instructions do not apply to normal databases. Base Segments :
ArchiveLocation :
NodeSpec :
Segment Ranges :
Instance Exclusions :
Operation Exclusions :
Table Exclusions :
Contention Area :
Indices :
Note the exclusions above. If the DBA wants an entire axsys to do a general backup of all tables, then the asterisk should be used in the node spec. In that case, all nodes in the axsys will participate and each will check its segment range for each table. Nodes that have no segment range for a table will skip it. Despite the provision of VLDB extensions, a VLDB backup should be monitored. The DBA staff should monitor resource utilization during the process and perform a detailed evaluation afterwards. A lock is applied to each segment as it is encountered and then removed when the operation is complete for that segment. Thus, a VLT will never be entirely locked. Errors :
Completion :
API Lexicon And Syntactical Reference Syntax:
A row update consists of a set of multiple operations which is treated as a transaction. The operations in a transaction set are performed sequentially. To improve speed for very large tables, if multiple rows are involved, then the operations are performed sequentially for the entire dataset; i.e., the first operation is performed for all rows before the second operation is started. A failed update can sometimes leave the database in an inconsistent state. For example, a computer crash in the middle of an update might hide rows that should be available. Such a problem cannot be automatically handled as it is in a normal database because it may involve a VLT, distributed objects, or other sophisticated requirements. This command allows the DBA to correct such problems. A SQL update normally clears all history of the update when it completes satisfactorily. Also, if an error causes an automatic update rollback, the history will be cleared. If the update fails and cannot be automatically reversed, then the system makes a detailed record of the query and its failure. This command will clear only a single failure each time that it is run. Caution and planning are encouraged if the failure involves a VLT. The command itself does not need locks. A computer crash will sometimes corrupt data on disk, which will render this recovery impossible. When that happens, the utility will report the situation and will attempt to clear the error. Be alert for repetitive failure. If the condition that caused the initial failure still exists, then the repair may also fail. Failure of the repair may render another attempt impossible. Returns :
Recommendation :
ShowFailure :
ShowRows :
DropHistory :
Security :
See also: Update command in The Vector Object Interface for extended problem discussion. Return expected: Report of operation.
Example:
API Lexicon And Syntactical Reference Syntax:
AxleBase saves a copy of the last error message that was generated anywhere in the system. The error remains available to the host system until the next error or until the host system uses this command to clear it. See also: ShowLastError. Return expected: Nothing.
API Lexicon And Syntactical Reference Syntax:
Closes the currently open database connection. If free-standing data Vector objects are open when this command is executed, they will be disconnected from the database. They will thereafter return errors for all commands executed against the database. They may be reconnected by issuing the OpenDatabase command or by issuing the Connect command, or the data Vector objects may be destroyed. If there are Vector objects which were created by the Manager object, they will be destroyed by this command. See also: OpenDatabase. Return expected: Nothing.
API Lexicon And Syntactical Reference Syntax:
Passing this command to a Manager object will open the domain and open the database. Some connections can only be made with a connection string because they require that extended parameters be included in the connection request. The connection string is a string of parameters which define the desired connection. The string is nearly freeform. The sequence of parameters, the letter case, and spacing are ignored. The string may consist of only those parameters that are needed. Parameters with empty values may be included without detriment. If a value is not needed for a connection, the parameter may be entirely omitted. Each parameter consists of four elements; the name, the equal sign, the value, and the closing semicolon. (name=value;) The parameter value is not required unless the parameter is required (name=;), but of course, it may be entirely omitted in that case if desired. A connection does not need to be closed before making a new connection. If a connection is open when the command is received, the object will close it and clean up before attempting the new one. However, professional development should explicitly close connections in case of system failure. If the new one fails, no connection will be open. Note that any dependent Vector objects must be considered. If the requested database is already open in the current connection, this command will close and re-open it. Therefore, if the database is open, the host must want to re-initialize the connection with this command. Comments, requirements, and cautions expressed in the OpenDomain and openDatabase sections are applicable to this command. ( If the domain does not exist, unlike the OpenDomain command, the connect command will not create a new domain. ) Errors :
Parameters that are under construction may be included without detriment. description:
When using a description in the connection, do not include reserved words. The recommended position is at the end of the string in case a reserved word is accidentally used in the text. driver:
domain:
domainPassword:
database:
databasePassword:
userDomainId:
userDomainPassword:
userDatabaseId:
userDatabasePassword:
server:
connectTimeOut:
queryTime:
See also: OpenDatabase; OpenDomain; The Default Database attribute. Return expected: Nothing.
Example with minimum requirements:
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
The Manager object must open a domain before it can create a database. The new database will be created in that domain. A database cannot be open when this command is executed. After the new database is created, it is opened for immediate use in the database object which created it. Name :
Location :
Operating System:
CAUTION:
DatabaseType:
See also: DropDatabase; ShowDatabaseCatalogue; The Default Database attribute. Return expected: Nothing.
API Lexicon And Syntactical Reference Syntax:
Where sqlStatement is a statement in the form:
Index names may be duplicated in the database, but must be unique within each table. They must adhere to the AxleBase naming requirements. At the present time, index names are limited to ten characters. The index name is not required. If it is left blank, then a meaningless randomly generated character string name will be assigned to it. The creation of a primary key also creates an index to support the primary key. The ExecuteSql command requires the complete SQL statement, but the 'create index' keywords are optional when using the AxleBase CreateIndex command. This feature allows the same command string to be used with the ExecuteSql command for uniformity. The Indexing sub-section of the Embedding And Running AxleBase section comments on the subject of indexing. VLT Extensions :
Virtual tables which use an external text file source may be indexed. It may be unwise to index objects that are outside the database, so such action should be well considered beforehand. Unique :
Segment Size :
Index Size :
High Speed Build :
Normal Build :
AxleBase is optimized for very large tables. He monitors table segment data as the table grows and may sometimes decide that a segment needs to be reindexed. That will happen only if the index is built as the table is built. It will not happen if the table segments are indexed after they are built. It will also seldom happen in small tables. The AxleBase indexing mechanism is specially designed to reduce disk I/O and network traffic in axsys operations on a VLT. The result is that a single-engine index operation is slightly slower than a big name brand, but it reduces the load on the target computer and on the network to allow an axsys to work faster. Build Time :
Locks :
See also: DropIndex, ShowIndices, AlterTable, ForceIndexEvaluation in the Configuration chapter. Return expected: Nothing.
Example:
VLDB Addendum To CreateIndex The following comments and instructions do not apply to normal databases. This addendum should be ignored unless very large tables are used. When using the specialized VLDB mechanisms, the DBA staff must insure that the index names are controlled and accurately used by all instances. If an index operation is interrupted, the index file will be corrupted for the segment which was being indexed at that time and that file must be deleted and rebuilt. Parameter Control :
Locate :
ForceValue :
Segment :
If a segment index file exists, then the index command will bypass it. That feature protects index files and prevents instance contention in a large operation. That also means that a corrupt segment index can be rebuilt only after its file is deleted. NodeSpec :
Instance Exclusions :
This example marks the table as indexed and builds the first segment index.
Another computer runs this command simultaneously to index the second segment.
While a third computer runs this command to build twenty index files offsite.
Note that all three submit a complete command for the index. To create an index without building a segment index, the segment number or count may be 0. In that case, the index will be created without an index build for any segment. This example creates the index and quits without creating any index data files.
If the index file for a segment is lost, it may be rebuilt with the segment specification. Note that this would temporarilly create an incomplete index for the table, so a query using that index would return erroneous results until the file is replaced. The table might be taken off-line while the file is being rebuilt to avoid such problems.
API Lexicon And Syntactical Reference Syntax:
Where sqlStatement is a SQL statement in the form:
AxleBase checks name validity within the SQL statement. Names must begin with a letter. They cannot contain spaces or unprintable ASCII characters. They cannot be the root or the master database. They cannot be a SQL command word or a data type. There is a word list and a character list in the appendix. Standard SQL uses the words 'create table' in the statement. The words 'create table' are optional when using this command. The command is used by the Manager object and not by the Vector object because it treats a data object and not the data. Constraints :
Default :
AxleBase does not yet recognize computed columns and column constraints. See also: DropTable, ShowTables Return expected: Nothing.
Example:
API Lexicon And Syntactical Reference Syntax:
CreateVirtualTable instantiates one or more external objects in the local database. The table appears real, but it is a local manifestation of a remote data entity by a virtualization mechanism. Clarification :
The command string is a standard SQL CREATE TABLE command to which is appended the virtual parameter string. Parameter sequence is optional. See the Configuration chapter for additional discussion of the various parameter values. Virtualization should not attempt to circumvent the AxleBase constraints. For example, a text file source must meet the segment size constraints. Separator :
Locations :
Reserved Words :
Text File Source
Source Validation :
Index :
Caution :
End Of Text File Source External AxleBase Table
Database Source :
Shared Attribute :
Attributes :
Columns :
Source Changes :
Indexing :
Security :
Chains :
End Of External Table Source Concatenated External Tables
Comments for the External AxleBase Table are applicable to concatenations. Sources are checked at the time of creation and all must pass. Other than names, all source structures and attributes must be identical. Column names are taken from the first table in the list. Limitation :
Source List :
CAUTION :
CAUTION :
Indexing :
Table Refresh :
End Of Concatenated Tables Source ODBC Source Source type : O When a problem occurs in a big-name database manager, the connected system is blamed, so it is doubtful that AxleBase will use ODBC sources. It would be a simple addition and most of the internal structure was built for it, so this may be reconsidered if there is a serious need for it, but that is doubtful. End Of ODBC Source See also: DropTable, ShowTables, ShowVirtualTables, ShowTableAttributes commands and the Virtual Databases chapter. Return expected: Nothing.
Example instantiating a remote text file in a table:
Example instantiating an external AxleBase table:
Example virtualization from a different domain:
Example virtualizing concatenated tables:
API Lexicon And Syntactical Reference Syntax:
This function encrypts and decrypts whatever is passed to it. The crypt technology was developed specifically for AxleBase and without review of cryptographic literature, so it may deviate from the norm. Operation :
Input :
Key :
Crypt2 Command :
ReturnType :
Returns :
Spoken Language :
The crypt technology is intended only for communication between AxleBase instances. AxleBase will not participate in external encryption and will not accept external participation in its encryption. If third party encryption is desired, then it must be handled by the host. Attempting to read an AxleBase encryption in transit sometimes corrupts the message. Release Numbers :
Level :
Encryption Strength :
Requests :
RAM Usage :
Number String :
Byte Array :
EncryptReturn :
See also: ReturnDataStream; ReturnDataset; the EncryptionLevel and EncryptReturn sections of the Configuration chapter; DeferReturn . Return expected: None.
Example:
Example:
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
If the Manager object is used to create the data Vector objects, this command will return the quantity of extant Vector objects. The first Data created is 1, and succeeding objects are numbered sequentially. (Please do not laugh. Some older programmers have a hard time remembering that.) When the last created object is destroyed, it is set to nothing and the count is decremented by one. When an object that is not the last is destroyed, it is set to nothing, but the total count is unchanged; i.e., the count includes one which is nothing. See also: DataObjectCreate; DataObjectDestroy; the Embedding And Running AxleBase chapter. Return expected: An integer value.
Example:
API Lexicon And Syntactical Reference Syntax:
Tells a Manager object to create a data Vector object. If successful, the command returns a reference to the new object. A Manager may create and use any number of Vector objects. When a Manager object creates a Vector object, it automatically connects the Vector object to itself. When the Manager object opens a database, the Vector object has an immediate connection to the database and can immediately query it. If the optional internalName is passed with the command, the internal name of the new object will be set by the Database manager. Internal names are checked for validity. An invalid internal name will cause the function to fail without an error. Names are not checked for duplication. This method of creating data Vector objects uses the Manager object to do the work and to help with subsequent control of the object. A programming note :
TIP: Since this command returns a reference to an object, there is no way for it to return an error notice. After running this command, if the object is nothing, then the ShowLastError command will return the error. See also: DataObjectDestroy; DataObjectCount; The Embedding And Running AxleBase chapter. Return expected: Reference to the new data object.
Example:
API Lexicon And Syntactical Reference Syntax:
If a Manager object is used to create data Vector objects, this command may be used to tell it to destroy the Vector. If the optional DataIdentifier is omitted, then all Vectors that were created by that Manager will be destroyed. ( Although the Manager tries to clean up when he is destroyed, good programming techniques would always run this command to insure cleanups.) DataIdentifier :
When the last created object is destroyed, it is set to nothing and the count is decremented by one. When an object that is not the last is destroyed, it is set to nothing, but the total count is unchanged; i.e., the count includes the one that is nothing. If the specified object is already nothing or if it does not exist, no error is returned. A programming note :
See also: DataObjectCreate; DataObjectCount; The Embedding And Running AxleBase chapter. Return expected: Nothing.
Example:
API Lexicon And Syntactical Reference Syntax:
Destroys the data, all objects, the definition, and all references to the named database. The action is immediate and irrevocable. This happens right now, and your database is gone in an eye-blink!! The command is accepted for execution even if the database contains data. The database must be open when the command is submitted. The command will be attempted regardless of the state or condition of the database. Lock Requirement :
Notice that if the host has an open data object which has been connected to the database, then the database is still in use. The result will be an error and an inconsistent database. The data object should be closed before the command is submitted. (This was a long-running problem in AxHandle because the developer (me) forgot to clear the dataset after creating and testing the demo database.) When told to drop a database, AxleBase traps errors only during the early phases of the operation. After object destruction has begun, an error will create an inconsistent database with no possibility of a rollback, so he will bypass errors after that point and continue the destruction of objects. After the DropDatabase command is used, remote and local objects should be checked to insure total destruction. This is especially true of an extensive VLDB. See also: CreateDatabase, ShowDatabaseCatalogue Return expected: Nothing.
API Lexicon And Syntactical Reference Syntax:
Destroys the entire database domain. The action is immediate and irrevocable. The domain must first be opened. Client Databases :
Rollback :
Lock Requirement :
See also: OpenDomain. Return expected: Nothing.
API Lexicon And Syntactical Reference Syntax:
Destroys the specified index. The action is immediate and irrevocable. CAUTION :
Primary keys are dropped by using the alterTable command; not with this command. The index name parameter accepts an asterisk wildcard character. If the index name is an asterisk, then all indices will be dropped from that table including the key. If the index name is a single asterisk without qualification or an asterisk qualified by another asterisk, then all indices in the entire database will be dropped. See also: CreateIndex; ShowIndices; AlterTable. Return expected: Nothing.
Example:
VLDB Addendum To DropIndex The following comments and instructions do not apply to normal databases. A single AxleBase instance can usually drop an index quickly even if it is very large and distributed across a network. If there are no errors or network problems, and if the operating system has not been allowed to fragment files, an index of fifty billion rows in thousands of segments scattered across the network will take less than a second. The system tables are updated before beginning the deletes of the segment index files. That makes the table available quicker and it makes a cleaner drop. A process that is engaged in working with many files scattered across a network is susceptible to encountering problems. After the process updates the system tables, it will attempt to work through all errors without returning error messages until it is finished with the deletes of the segment index files. That allows the process to clean up as well as possible without corrupting the system tables, but the DBA should check the index locations afterwards. If the system shows that the index has been dropped, but a segment index file remains, it can be manually deleted. Most networks are susceptible to producing spurious noise which can interfere with an extended drop process of a VLT. Therefore, the dropIndex process applies the spinlock value to each segment index operation. When dropping a large index, it should be remembered that the operating system is extremely inept at file management and may have fragmented the files extensively. It is not unusual to find that the operating system has split a two gig segment file into a half million storage fragments. When that happens, each file deletion can take a very very long time. As an example, in the test and development environment, a well-maintained index with five hundred segment index files will be dropped in a split second. However, when the operating system is allowed to fragment that same index, dropping it can take hours or days.
API Lexicon And Syntactical Reference Syntax:
Destroys the data, the definition, and all references to the named table. The action is immediate and irrevocable. The words 'drop table' are optional when using this command. The redundency engendered by employing standard SQL may cause some confusion. However, it is hoped that the use of standard SQL may make life easier for the experienced DBA. If the object is a VLT with extensive remote objects, their drop should be verified afterwards. See also: DropTable; ShowTables. Return expected: Nothing.
Example:
API Lexicon And Syntactical Reference Syntax:
Allows the Manager object to execute the SQL commands that would otherwise be passed to the customized AxleBase commands. The customized AxleBase commands provide an interface that is designed for easy learning and control. The ExecuteSql command is provided as a single point of access for those who are more comfortable with SQL. It is recommended that when using this command, one should be familiar with the documentation for the customized commands. The currently accepted SQL statements include:
Return expected: Nothing.
Example:
API Lexicon And Syntactical Reference Syntax in the industry standard:
Syntax to use extended AxleBase features:
The Grant command grants permission to access or perform operations on specified objects. The first syntax is provided in conformance with the ANSI standard. The second syntax is the non-standard AxleBase version which is a bit easier to understand because the permissions are more logical. It also provides additional object control and adds the user password. AxleBase adjusts the meanings of the parameters to match the object type. For example, if the second syntax is used for a table type of object, then the CREATE option includes row inserts and READ includes selects. This command takes a single parameter which is the entire command string. To simplify host operations, the string may contain the grant keyword when the ExecuteSql command is used and when the Grant command is used. This is a security component. As discussed in the Security section of the Embedding And Running AxleBase chapter, this feature is effective only when the host is a server such as a web server or an odbc server so that activity can be limited to remote connections. AxleBase deviates from the standard to allow the command to cover entire domains and entire databases as well as tables. The keyword DOMAIN applies the operation to the currently open domain, but the name of the object is always required after the object type keyword. The optional user password is also a deviation from the standard. It sets a password that the person/system must present to gain access to the object. It can be required at any or all levels or objects. For example, its presentation might be required at a table level and not at the domain level. Using the password requires some caution in the construction of the statement. If the string 'password ' keyword is encountered (note the space at the end of the string), then all characters that follow the space constitute the password. For extended discussion of password use and construction, see the Embedding And Running AxleBase chapter, Security section, Passwords sub-section. Names cannot contain spaces or other formatting characters. Names are case insensitive. Components of the command are separated by spaces. There are no commas or other separating characters. The rights keywords ALL, CREATE, READ, UPDATE, DELETE, and EXECUTE may be used in any combination to grant permission to perform the indicated operation. The keyword ALL may be used by itself to grant all of those permissions, but it will not include OWNER. They are case insensitive. The rights keywords are optional so that the command can be used to allow passage through a level without giving rights to it. If, for example, it is a domain grant without a privilege keyword, then the person will be allowed to pass through the domain level to reach a database. Upon installation, AxleBase defaults open to provide an unobtrusive security mechanism. At that point, all objects are available to all processes and do not require any grants. (See also Turning On Security in the Embedding And Running AxleBase chapter.) See also: Revoke, ShowPermissions. Return expected: Nothing.
Example granting ownership control of the demo database.:
Example giving limited access to the T_LOG table.:
Example allowing betty to enter the domain with no rights so she can get to her database:
API Lexicon And Syntactical Reference Syntax:
This command will move specified objects and update system catalogues. Following are valid LocationTypes:
To help insure that the location parameter is indeed the desired location, the new location must be an existing location. The system will immediately try to move all objects to the new location regardless of any system using them. The database should be closed, cleared and locked before issuing this command. If an error is encountered during the operation, AxleBase will not attempt to roll back; rollback is the responsibility of the operator. AxleBase will update system tables only if the operation is a success. If the failure happens before relocation, no action is required. If the operation moves any files, then no existing files are deleted until all files have been copied to the new location. It is possible to locate a client database under the domain database. Do not do that. At an unforeseen time in the future, the database will be destroyed. The domain master database must contain only those things which AxleBase puts there. Caution:
Shared Tables :
This example relocates the database log.
This example relocates the temp work directory.
VLDB Addendum To Locate The following comments and instructions do not apply to normal databases. A write map can be used to bypass the base table segment file. This might be done to reduce the load on the local system. The base table segment file is the first segment in a table and is written into the primary database location. If it is bypassed, it will remain empty and subsequent table segments can be directed to remote locations. See the discussion of the write map in the VLDB chapter. Indices are always located under their table locations. If a table has multiple segment files, each segment index will be written under its segment. The relocation of large files is not recommended because the movement of large files is subject to many operating system errors. This is especially true when the operating system has created great fragmentation in files. If the relocation is necessary, consider a manual relocation.
API Lexicon And Syntactical Reference Syntax:
Sets or removes a temporary discretionary lock on the currently open database or domain. ObjectType :
LockType :
Exlusive Lock :
Lock Removal :
Lock Storage :
Timeout :
Attempting to open a locked object returns an error which tells the process that the object is locked. See also the ShowLocks command and the Lock Timeout section of the Configuration chapter. Return expected: Nothing.
Example placing a lock on the current database:
Example removing the previous lock:
Example removing all locks:
Example locking the current domain:
API Lexicon And Syntactical Reference ( Under construction or consideration. Currently unavailable. ) Syntax:
This utility is designed for VLDB operations and will not usually be needed for normal databases. The network is defined by the database; the utility looks for locations which are used by the database. A database must be open before execution. This command is not designed to replace the network administrator's tools, but it can identify potential problems for database operations. Such problems are frequently of little or no interest to the network administrator. It is designed to be run in a cyclical loop. A pause between loops is recommended. The recommended procedure is to dedicate an instance to running it. Since it is intense, the YieldProcessor should be set to yes so that it can be stopped. ReturnToggle:
If run interactively, an offline computer or network segment will cause each internal operation to take up to a minute. If the table has many segments on the faulty infrastructure, the report can take a very long time to return. Each time that it is run, it checks the validity of all locations in the database. If the optional TableName is used, then only that table is checked. Otherwise, it checks all tables. (If that optional parameter is passed, then the returnToggle must also be passed.) More discussion of operation and usage may be found in the Axsys State Assessment sub-section of the VLDB chapter. This command is not interchangeable with the TableCheck command. See also: TableCheck Return expected: Nothing unless a return is requested.
Example:
API Lexicon And Syntactical Reference Syntax:
Opens and makes a connection to the database. If the database does not exist, a trappable error message is returned. (The existence of a database may first be determined with the ShowDatabaseCatalogue command.) The domain must be opened before one of its databases can be opened. Optional apostrophe parameter delimiters may be used with the password option when AxleBase might otherwise not accept the literal string of characters. For example, leading spaces would be dropped by AxleBase, but if the string is inside apostrophe delimiters, then leading spaces are protected. A database does not need to be closed before opening a new one. If a database is open when the command is received, the Manager object will close it and clean up before attempting to open the new one. If the new one fails, no database will be open. Note that the developer must consider any dependent data Vector objects. If the database is already open, the object will be initialized and the database will be reopened. Errors :
See also: CloseDatabase, ShowDatabaseCatalogue. Return expected: Nothing.
Example:
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
This command opens the specified domain for administration and for access to its databases. Purpose :
Path :
Security :
Domain Creation :
Connection String :
Name Problem :
The AxleBase system is designed to support any number of database domains in an installation. Each domain can control any number of databases. Errors :
See also: DropDomain; CreateDatabase; Connect Return expected: Nothing.
Example:
That example will open, connect to, or create the master in
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
This utility reorganizes and deletes unused objects and data. (All brands of database managers must purge their databases, although some hide the operation.) An update failure must be recovered before purging because rows that were involved in incomplete transactions will be purged. Optional Parameters :
Object parameter may be :
Database Object :
Domain Object :
System Object :
Table Object :
Action :
Force Database :
Force Domain :
Count :
NodeSpec :
Backups :
TableCheck :
Frequency :
Component Procedures :
Empty Segments :
The following will not be purged. :
Indices :
Restarts :
The operation is intense and is especially so for a VLDB. Lock Requirement :
For those who are accustomed to other database managers :
Return expected: Nothing.
Example purges everything in the database :
Example purges all except the t_log table :
Example forces a purge of t_log whether or not it needs it :
Example forces a purge of 10 segments starting with segment 50 :
Example purges only the system tables :
VLDB Addendum To Purge The following comments and instructions do not apply to normal databases. The behavior of an axsys in purge operations is different from its behavior in queries. Generally speaking, the entire axsys is vectored in a purge and the parameters can be used to increase the vectorization. NodeSpec :
Segment Ranges :
Instance Exclusions :
Operation Exclusions :
Contention Area :
Index :
Work Areas :
When selecting dbtemp locations, insure that the media can support them. AxleBase sometimes creates work files of gigabytes and some disk types cannot handle files of that size. Note the exclusions above. If the DBA wants an entire axsys to do a general purge of all tables, then the asterisk should be used in the node spec. In that case, all nodes will participate and each will check its segment range for each table. Nodes that have no segment range for a table will skip the table. Despite the provision of VLDB extensions, a purge should be used with caution in a VLDB. The DBA staff should monitor resource utilization during the process and perform a detailed evaluation afterwards. The fact that it is designed to fail graciously should not be construed to mean that a VLDB would not be at risk from the enormous system load. The greater the degree of table segmentation, the greater the danger. A VLDB should be locked, cleared, and sealed before a purge if at all possible. Locks :
Errors :
System :
Completion :
API Lexicon And Syntactical Reference ( The axsys is operational, but it is still under construction and is subject to dramatic changes.) Syntax:
This command tells the local AxleBase instance to reload its axsys configuration from storage and then reconfigure itself accordingly. Whereas the AlterAxsysRunningNode command directly changes the node configuration in the running instance, this tells the instance to reload from the stored configurations. Identity :
Return :
(See the VLDB chapter for extensive discussion of the use and control of the axsys object which is an extended and distributed database manager.) See also: ShowAxsysNode, AlterAxsys, ShowAxsys, AlterAxsysRunningNode, VLDB chapter. Return expected: Reload string.
Example:
API Lexicon And Syntactical Reference Syntax:
Performs a restoration of a table, database, or domain from the specified location. All of the data in the object will be replaced by the archived data. All changes and object creations since the backup will be lost. A dropped table will not be recognized as a valid table and cannot be restored. Re-create the table, ignoring indexing, and it can then be restored from the archive. BackupLocation :
Domain :
Object :
Lock Requirement :
Disconnect :
Archive Validity :
Restart :
See also: Backup; Return expected: Nothing.
Example restoring a database:
Example restoring a domain:
Example restoring a single table named t_log:
VLDB Addendum To Backup The following comments and instructions do not apply to normal databases. Although the Backup command provides the ability to address requirements of a distributed database, the RestoreBackup does not. That increases the solidity for distributed operations by insuring the backup of the primary files. Distributed table segments must be copied manually.
API Lexicon And Syntactical Reference Syntax:
The Revoke command revokes the ability to access or perform operations on specified objects. AccessType :
ObjectType :
The keyword ALL or an asterisk may be used in the FROM clause. When used there, the revoke is applied to all entities. The object name:
This is a security component. As discussed in the Security section of the Embedding And Running AxleBase chapter, this feature is effective only when the host is a server such as a web server or an odbc server which limits activity to remote connections. Of significance is the fact that AxleBase requires no access security until security is turned on. AxleBase deviates from the standard to allow the command to cover domains and databases as well as tables. See also: Grant, ShowPermissions. See also Turning On Security in the Embedding And Running AxleBase chapter. Return expected: Nothing.
Example:
Example that revokes all of Mary's access within a database:
Example locking Mary out of every database:
Example clearing all rights within a database:
API Lexicon And Syntactical Reference Syntax:
For those who are unable to build a database server host, this command allows a database to be shared across a network after it is created. The domain must also be shared before database shares become effective. The database must be open when the command is issued to help you insure that you are updating the correct database. Therefore, its name is not required. Valid and complete paths are required for all of the parameters. Connections will fail if any path cannot be used from a remote workstation. Therefore, all of the parameters must be shared paths. For example, the database may exist on the local drive in the path, c:\db\data\, and the share parameter may be a share name such as \\server\db\data\ . This command will not relocate objects. It updates parameters that allow remote access. DomainPath is the location of the domain which controls the database. This command may be re-issued at any time to update shared locations. Optional parameters :
Defaults :
Security :
CAUTION :
See also: ShareDomain Return expected: None.
Example:
API Lexicon And Syntactical Reference Syntax:
For those who are unable to build a database server into their host, this command allows a domain to be shared across a network after it is created. To share each of the databases in the domain, the ShareDatabase command will be used. The domain must be opened before issuing the command. Valid paths are required for all of the parameters. Connections will fail if any path cannot be used from a remote workstation. Therefore, all of the parameters must be shared paths. For example, the domain may exist on the local drive in the path, c:\db\domain\, and the share parameter may be a share name such as \\server\db\domain\ . This command may be re-issued at any time to update shared locations. Optional parameters :
Defaults :
Security :
CAUTION :
See also: ShareDatabase Return expected: None.
Example:
API Lexicon And Syntactical Reference ( The axsys object is operational, but it is still under construction and is subject to dramatic changes.) Syntax:
Returns the entire axsys configuration for the currently open database. The report is formatted for readability. The axsys configuration can be considered part of the database attributes, but its size can become very large, so it has its own report. Option :
See also: AlterAxsys, AlterAxsysRunningNode, ReloadNode, ShowDatabaseAttributes and the VLDB chapter. Return expected: Axsys configuration.
Example:
Example: sReturn contained the following report.
Example will return a ping list:
API Lexicon And Syntactical Reference Syntax:
Return the current node configuration if one was loaded. (See the VLDB chapter for extensive discussion of the use and control of the axsys object which is an extended and distributed database manager.) See also: ReloadNode, ShowAxsys, AlterAxsys AlterAxsysRunningNode,. Return expected: Current configuration.
Example:
API Lexicon And Syntactical Reference Syntax:
Returns the legal copyright. Any attempt to mask, bypass, alter, alias, camouflage, or qualify either this command or its return legally revokes the right of the individual and/or organization to use AxleBase and its supporting software and may precipitate legal consequences. Return expected: Copyright notice.
Example:
API Lexicon And Syntactical Reference Syntax:
This report shows the structure of the currently open database by returning its SQL construct. The SQL construct is the series of SQL statements that would be used to create the database. Each element of the report is prefixed by an end-of-line. That includes the first one to bypass the zerothe element when arrayed. The structure elements begin with the "create" keyword. Header, footer, and comments are encapsulated by SQL comment characters. See also: ShowDatabaseCatalogue, ShowDatabaseAttributes, ShowTable, ShowTables. Return expected: A string of multiple SQL statements.
Example:
API Lexicon And Syntactical Reference Syntax:
Returns the characteristics of the currently open database. Return Structure :
Since the return consists of the characteristics of the currently open database, if no database is open, the return is simply blank; it is not an error. Toggle values in the return may be true, false, yes, no, t, f, y, or n, depending on the development stage of the system. Storage Architecture Model :
Release Number :
The return includes the connectionId, which is the same as the ID number of the Manager object which created the connection.
See also: ShowDatabaseCatalogue, ShowDomainAttributes, ShowInstanceAttributes, ShowTableAttributes, ShowAxsys. Return expected: Current database characteristics.
Example with return from the AxHandle demo database:
API Lexicon And Syntactical Reference ( Replaces the ShowDatabases command. ) Syntax:
Returns a list of the specified attributes for databases in the current domain. A line break precedes each item in the list. That includes the first one to skip the zerothe element. There are times when a database name may be part of the name of another; e.g. parts and parts_auto. In that case, added programming would be required to find both. Therefore, an end of line is added to the last row so the host system can search for database name and eol. AttributeName :
Database Name :
If no domain is open, a trappable error message is returned. If there are no databases in the catalogue, an empty string is returned. See also: ShowDatabaseAttributes. Return expected: Database list.
Example:
Example:
API Lexicon And Syntactical Reference Syntax:
Returns descriptions of all columns in the database. TableName :
Return Structure :
Before the command is issued, the domain must be active and the database must be open; i.e., a connection must be made. See also: ShowTable, ShowTableColumns. Return expected: Character string.
Example:
API Lexicon And Syntactical Reference Syntax:
Returns a string containing the identification and attributes of the currently open database domain. Return Structure :
If a domain has not been opened by the current database manager object, a trappable error message is returned. This provides a way of insuring that an open command succeeded. See also: ShowDatabaseAttributes, ShowInstanceAttributes, ShowTableAttributes. Return expected: Character string.
Example from AxHandle's demo. In this case, the host converted each line break to a comma and a space:
API Lexicon And Syntactical Reference Syntax:
Returns the entire list of standard errors. If an error number is entered, only that error is returned. Return Structure :
|