picoSQL FAQ
Corso Italia, 178 - 56125 Pisa
phone/fax +39 050 46380
e-mail picosoft@picosoft.it
|
picoSQL FAQ |
Corso Italia, 178 - 56125 Pisa phone/fax +39 050 46380 e-mail picosoft@picosoft.it |
| What picoSQL is? |
| PicoSQL is a client/server relational database management system based on SQL language. Its main feature is the compactness, the small memory footprint, the simplicity of installation and configuration. However picoSQL supports a full-featured SQL, high cuncurrency and transactions. |
| Who developed it and why? |
| PicoSQL was fully developed
by Picosoft in Pisa and it derives from am ODBC(tm) driver, developed
by the same company, that allow to query COBOL files as a relational
database, tipically using common graphical tools like MS-Access (tm),
Excel(tm), Crystal report(tm) etc. Transforming this driver in a RDBMS has been relatively simple, so the company decided to make it available as Open Source. The idea is that, if the product meets the favor of the community, opportunities of job, in terms of support of the product and customization, can arise for those who are going to use picoSQL in production environments. Because of its 'lightness' and modularity, picoSQL can easy be adapted for any computer, from the pocket-PC to the mainframe. |
| Why I should prefer picoSQL instead of MySQL or PostGreSql? |
| PicoSQL is easier to install and to configure than PostGreSQL and supports updatable views (rel. 1.2) that MySQL do not support yet. PicoSQL is a young product and allows everyone to partecipate to its development, suggesting priorities and requirements. |
| Can I use PicoSQL in a production environment? |
| PicoSQL is already used in production environments: for example it is used by the Viareggio municipality to manage press news. It is used to store the message of the picoSQL forum. |
| Will Picosoft supply som form of support and/or assistance on picoSQL? |
| Yes, Picosoft will supply support and assistance on picoSQL. At the moment this seems the only way to get some revenue to justify new investments in the product. |
| May I distribute picoSQL with programs distributed with different kind of license? |
| The GPL, the license under which picoSQL is distributed, allows to use it without limitations with applications of whichever type, provided that it is not linked to them. The LGPL, the license under which the picosqlnet library is distributed, allows to use it in linked applications of whichever type. |
| The name "pico" means that funcionality will be minimal forever to allows a small memory footprint and a better efficiency? |
| The name "picoSQL" derives from the company name "Picosoft". Functionalities will be extended, someone are already in progress, also based on the users demands gotten through the forum. However, we think that many non standard functionalities in other products are mainly useful to bind applications to a specific RDBMS. |
| Have you done some benchmark, comparing picoSQL with MySQL and PostGreSQL? |
| We made only one benchmark and we compared picoSQL only with Oracle because the benchmark program do not work correctly with MySQL and MS-SQLServer. This program is written in Java and you can find it at http://www.picosoft.it/picosql/benchmark_en.html: if there is someone able to get it working on MySQL or MS-SQLServer and she/he tell us how to do, we 'll publish the results. DB speed estimating is a very hard task because there are to many conditions to evaluate. Some of this conditions are: transaction on/off, isolation level, concurrency level, memory requirements, type of operations, indexes usage, table size, hardware configuration, S.O. etc. Anyway, computers are getting faster and faster, so we think that speed is not the most important feature. In our tests we paid particular attention on reliability and performance constancy. We saw that picoSQL performances do not degrade increasing the table size (using indices) and increasing concurrency, and that the memory footprint remains small, (about 1 Mb while MySQL uses about 8 Mb and Oracle 50 Mb; however the architectures are very different). Putting our Linux box under stress never stops the computer. At the moment these are the only information, we'll do other test in the future. |
| Has Picosql any limitation on transactions? Does it support more than one transaction for each connection? |
| There aren't any limitation on transaction, but each connection has only one active transaction. |
| Is Picosql SQL-99 complaint? |
| The reference standard is SQL-92 but some extension are implemented. You can find the supported syntax at http://www.picosoft.it/picosql/doc/en/sql.html |
| Does picoSQL allow sub-query? |
| Yes, but only in the INSERT statement using the EXISTS clause. |
| What about picoSQL and OpenOffice.org 1.1 ? |
|
You can access picoSQL using the ODBC driver or
the JDBC driver. There is a problem if you want to
update the tables (affecting the ORACLE JDBC driver too),
but you can find the solution of this problem on the
OpenOffice site at the address:
http://dba.openoffice.org/howto/IgnoreDriverPrivileges.html
First of all, any updatable table MUST have a PRIMARY KEY, then you can execute the following macro for each picoSQL data source: REM ***** BASIC ***** Option Explicit Sub Main Dim sDataSourceName as String sDataSourceName = InputBox( "Please enter the name of the data source:" ) IgnoreDriverPrivileges(sDataSourceName ) End Sub Sub IgnoreDriverPrivileges(sDataSourceName as String ) ' the data source context (ehm - the service name is historical :) Dim aContext as Object aContext = createUnoService( "com.sun.star.sdb.DatabaseContext" ) If ( Not aContext.hasByName( sDataSourceName ) ) Then MsgBox "There is no data source named " + sDataSourceName + "!" Exit Sub End If ' the data source Dim aDataSource as Object aDataSource = aContext.getByName( sDataSourceName ) ' append the new IgnoreDriverPrivilegesflag Dim bFlag as Boolean bFlag = TRUE Dim aInfo as Variant aInfo = aDataSource.Info aInfo = AddInfo( aInfo, "IgnoreDriverPrivileges", bFlag ) ' and write back aDataSource.Info = aInfo ' flush (not really necessary, but to be on the safe side :) aDataSource.flush End Sub Function AddInfo( aOldInfo() as new com.sun.star.beans.PropertyValue,sSettingsName as String, aSettingsValue as Variant ) as Variant Dim nLower as Integer Dim nUpper as Integer nLower = LBound( aOldInfo() ) nUpper = UBound( aOldInfo() ) ' look if the setting is already present Dim bNeedAdd as Boolean bNeedAdd = TRUE Dim i As Integer For i = nLower To nUpper If ( aOldInfo( i ).Name = sSettingsName ) Then aOldInfo( i ).Value = aSettingsValue bNeedAdd = FALSE End If Next i ' allocate the new array Dim nNewSize as Integer nNewSize = ( nUpper - nLower ) If bNeedAdd Then nNewSize = nNewSize + 1 Dim aNewInfo( nNewSize ) as new com.sun.star.beans.PropertyValue ' copy the elements (a simply copy does not work in Basic) For i = nLower To nUpper aNewInfo( i ) = aOldInfo( i ) Next i ' append the new setting, if necessary If ( bNeedAdd ) Then aNewInfo( nUpper + 1 ).Name = sSettingsName aNewInfo( nUpper + 1 ).Value = aSettingsValue End If AddInfo = aNewInfo() End Function We tried with both ODBC and JDBC drivers and it seems work fine. |