Re: [sqlite] Problem loading extension in Delphi (FTS2)

2007-08-16 Thread Zlatko Matic 

Ralf,
How about Lazarus version of DISQLite3?:)
Regards,

Zlatko

- Original Message - 
From: "Ralf Junker" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, August 16, 2007 11:54 AM
Subject: Re: [sqlite] Problem loading extension in Delphi (FTS2)



Hello Joe Wilson,


Your product is not useful to a few users like me who require custom
sqlite functions for their databases.


I am not sure I understand currectly. Only loadable extensions are 
currently omited from DISQLite3.


sqlite3_create_function() is very well available in DISQLite3 Pro to 
create custom SQL functions. DISQLite3 also includes units with ready-made 
function extensions:


* REGEXP regular expression support provided by DIRegEx in 
DISQLite3RegExp.pas.


* Mathematical utility functions [(acos(), asin(), atan(), atan(), 
atan2(), ceil(), ceiling(), cos(), cot(), degrees(), exp(), floor(), ln(), 
log(), log(), log2(), log10(), mod(), pi(), pow(), radians(), sign(), 
sin(), sqrt(), tan(), truncate()] in DISQLite3Functions.pas.


Ralf


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] incredible slow performance of a trigger

2007-08-09 Thread Zlatko Matic 

Hi!
Here is the table "stocks":

CREATE TABLE [stocks] (
[stocks_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0)  NULL,
[material] VARCHAR(0)  NULL,
[material_description] VARCHAR(0)  NULL,
[material_type] VARCHAR(0)  NULL,
[material_batch] VARCHAR(0)  NULL,
[material_stock_unrestricted] FLOAT  NULL,
[material_stock_restricted] FLOAT  NULL,
[material_stock_in_transfer] FLOAT  NULL,
[material_stock_qlty_insp] FLOAT  NULL,
[material_stock_blocked] FLOAT  NULL,
[material_stock_returns] FLOAT  NULL,
[material_stock_total] FLOAT  NULL,
[material_stock_units] VARCHAR(0)  NULL,
[material_value_unrestricted] FLOAT  NULL,
[material_value_restricted] FLOAT  NULL,
[material_value_in_transfer] FLOAT  NULL,
[material_value_qlty_insp] FLOAT  NULL,
[material_value_blocked] FLOAT  NULL,
[material_value_returns] FLOAT  NULL,
[material_value_total] FLOAT  NULL,
[material_value_units] VARCHAR(0)  NULL,
[storage_location] VARCHAR(0)  NULL
)

Regarding indexes, I have them defined...
Regarding TEXT/VARCHAR, well, originally it was TEXT, but my front-end 
(Lazarus) couldn't show text fields in DBGrid, so I changed it to VARCHAR. 
Varchar(0) instead of Varchar because SQLite Administrator didn't accept 
varchar without field length:(


I really don't understand what is the problem. Maybe I'm missing something 
obvious?
If I delete the trigger and delete where clause from 
qry_bom_components_input, then both INSERT INTO..SELECT FROM queries (for 
bom_products and bom_components) work well


Regards,

Zlatko

- Original Message - 
From: "Marco Antonio Abreu" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Thursday, August 09, 2007 10:50 PM
Subject: Re: [sqlite] incredible slow performance of a trigger



Hi Zlatko,

In your commands, I did't find table "stocks" used in view
"qry_stocks_sum".  In any case, try to use SQLite default types, like
REAL and TEXT in place of FLOAT and VARCHAR.  The type "VARCHAR(0)" is
not indicated for field type and length, please use some thing like
"TEXT(40)".  Finally, indexes help queries performances.

[]'s,

Marco Antonio Abreu
IT Quality Systems
[EMAIL PROTECTED]
http://www.itquality.com.br



Zlatko Matic wrote:

Hello.
I have terrible performance  when executing the following query, which
inserts rows from table "products" to table "bom_products":
INSERT INTO bom_products (
  plant,
  product,
  product_description,
  product_base_qty_units,
  product_base_qty)
SELECT DISTINCT
   products.plant,
   products.product,
   products.product_description,
   products.product_base_qty_units,
   products.product_base_qty
FROM
   products
ORDER BY
   products.plant,
   products.product;
The query fires trigger "bom_products_tr_after_row_insert" (see below)
that should populate table "bom_components" with corresponding rows
for every row in table "bom_products" (bom_products and bom_components
are one-to-many).

CREATE TABLE [products] (
[products_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0)  NULL,
[product] VARCHAR(0)  NULL,
[product_description] VARCHAR(0)  NULL,
[product_base_qty_units] VARCHAR(0)  NULL,
[product_base_qty] FLOAT  NULL
)

CREATE TABLE [bills_of_materials] (
[bills_of_materials_id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[plant] VARCHAR(0)  NULL,
[product] VARCHAR(0)  NULL,
[component] VARCHAR(0)  NULL,
[component_description] VARCHAR(0)  NULL,
[component_brutto_qty] FLOAT  NULL,
[component_brutto_qty_units] VARCHAR(0)  NULL,
[product_base_qty] FLOAT  NULL
)

CREATE TABLE [bom_products] (
[bom_products_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0)  NULL,
[product] VARCHAR(0)  NULL,
[product_description] VARCHAR(0)  NULL,
[product_base_qty_units] VARCHAR(0)  NULL,
[product_base_qty] FLOAT  NULL,
[product_target_qty] FLOAT  NULL
)

CREATE TRIGGER [bom_products_tr_after_row_insert]
AFTER INSERT ON [bom_products]
FOR EACH ROW
BEGIN

INSERT INTO bom_components(
  plant,
  product,
  component,
  component_description,
  component_brutto_qty,
  component_brutto_qty_units,
  product_base_qty,
  product_target_qty,
  component_stock_unrestricted,
  component_stock_restricted,
  component_stock_qlty_insp,
  component_stock_blocked,
  component_stock_in_transfer,
  component_stock_returns,
  component_stock_total)
SELECT DISTINCT
   qry_bom_components_input.plant,
   qry_bom_components_input.product,
   qry_bom_components_input.component,
   qry_bom_components_input.component_description,
   qry_bom_components_input.component_brutto_qty,
   qry_bom_components_input.component_brutto_qty_units,
   qry_bom_components_input.product_base_qty,
   NEW.product_target_qty,
   qry_bom_components_input.component_stock_unrestricted,
   qry_bom_components_input.component_st

Re: [sqlite] New User: Creating/Modifying tables

2007-08-09 Thread Zlatko Matic 

Has anybody successfuly ported DISQLite3 from Delphi to Lazarus?

- Original Message - 
From: "Michael Hooker" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, August 09, 2007 9:47 AM
Subject: Re: [sqlite] New User: Creating/Modifying tables



How do I create/alter tables with a GUI?<<


Short of buying some kind of commercial pre-written db manager, you could 
use the DisqLite3 you already have to write something in Delphi which you 
already have.  All you really need is an edit control into which you put 
your SQLite3 statements and a button to execute them, along with some more 
edit controls or a StringGrid to display the output,  but you can make it 
as complicated as you like.  For example, how you display the data you 
extract from the database is entirely up to you and you can reformat your 
dates however you like.  It's not very much harder than using the Delphi 
data-aware components, though if you only have a personal edition you 
won't have seen these.  Once you've written something useful, then you 
don't have to write a program every time - you just modify the one you've 
already written to add new functionality when/if you need it.  Statements, 
database paths and so on can be stored as text files so they can be 
re-used - just like SQLiteSpy.


Check out the example programs that come with DisqLite3.  Ralf Junker has 
put a lot of work into this, so look at the examples for beginners which 
he recently added - he wrote these at my request when I was stuck, and I'm 
very grateful to him.


If you need assistance with Delphi programming there is no shortage of 
helpful people on Forums such as Tek-Tips.  Obviously, this isn't the 
place to deal with those issues.



Michael Hooker

- Original Message - 
From: "mos" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, August 09, 2007 6:17 AM
Subject: [sqlite] New User: Creating/Modifying tables


I created my first Delphi program with DISQLite3 1.40 and I used the 
Importer component to create the table and I imported a few of the rows 
from MySQL 5. The problem is the dates in the SQLite table show up as 
floating point, which is how they are natively stored.


1) How can I determine what the date is with SQLiteSpy? I need the dates 
formatted as '2006-05-11'. Do I have to write a program every time I


2) How do I create/alter tables with a GUI? I can't find that option in 
SQLiteSpy or SQLite3 Mgr.


TIA
Mike

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] incredible slow performance of a trigger

2007-08-09 Thread Zlatko Matic 

Hello.
I have terrible performance  when executing the following query, which 
inserts rows from table "products" to table "bom_products":

INSERT INTO bom_products (
  plant,
  product,
  product_description,
  product_base_qty_units,
  product_base_qty)
SELECT DISTINCT
   products.plant,
   products.product,
   products.product_description,
   products.product_base_qty_units,
   products.product_base_qty
FROM
   products
ORDER BY
   products.plant,
   products.product;
The query fires trigger "bom_products_tr_after_row_insert" (see below) that 
should populate table "bom_components" with corresponding rows for every row 
in table "bom_products" (bom_products and bom_components are one-to-many).


CREATE TABLE [products] (
[products_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0)  NULL,
[product] VARCHAR(0)  NULL,
[product_description] VARCHAR(0)  NULL,
[product_base_qty_units] VARCHAR(0)  NULL,
[product_base_qty] FLOAT  NULL
)

CREATE TABLE [bills_of_materials] (
[bills_of_materials_id] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[plant] VARCHAR(0)  NULL,
[product] VARCHAR(0)  NULL,
[component] VARCHAR(0)  NULL,
[component_description] VARCHAR(0)  NULL,
[component_brutto_qty] FLOAT  NULL,
[component_brutto_qty_units] VARCHAR(0)  NULL,
[product_base_qty] FLOAT  NULL
)

CREATE TABLE [bom_products] (
[bom_products_id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[plant] VARCHAR(0)  NULL,
[product] VARCHAR(0)  NULL,
[product_description] VARCHAR(0)  NULL,
[product_base_qty_units] VARCHAR(0)  NULL,
[product_base_qty] FLOAT  NULL,
[product_target_qty] FLOAT  NULL
)

CREATE TRIGGER [bom_products_tr_after_row_insert]
AFTER INSERT ON [bom_products]
FOR EACH ROW
BEGIN

INSERT INTO bom_components(
  plant,
  product,
  component,
  component_description,
  component_brutto_qty,
  component_brutto_qty_units,
  product_base_qty,
  product_target_qty,
  component_stock_unrestricted,
  component_stock_restricted,
  component_stock_qlty_insp,
  component_stock_blocked,
  component_stock_in_transfer,
  component_stock_returns,
  component_stock_total)
SELECT DISTINCT
   qry_bom_components_input.plant,
   qry_bom_components_input.product,
   qry_bom_components_input.component,
   qry_bom_components_input.component_description,
   qry_bom_components_input.component_brutto_qty,
   qry_bom_components_input.component_brutto_qty_units,
   qry_bom_components_input.product_base_qty,
   NEW.product_target_qty,
   qry_bom_components_input.component_stock_unrestricted,
   qry_bom_components_input.component_stock_restricted,
   qry_bom_components_input.component_stock_qlty_insp,
   qry_bom_components_input.component_stock_blocked,
   qry_bom_components_input.component_stock_in_transfer,
   qry_bom_components_input.component_stock_returns,
   qry_bom_components_input.component_stock_total
FROM
qry_bom_components_input
WHERE qry_bom_components_input.product=NEW.product
ORDER BY
   qry_bom_components_input.plant,
qry_bom_components_input.product,
qry_bom_components_input.component;
END

CREATE VIEW qry_bom_components_input
AS
SELECT DISTINCT bills_of_materials.plant AS plant,
   bills_of_materials.product AS product,
   bills_of_materials.component AS component,
   bills_of_materials.component_description AS component_description,
   bills_of_materials.component_brutto_qty AS component_brutto_qty,
   bills_of_materials.component_brutto_qty_units AS 
component_brutto_qty_units,

   bills_of_materials.product_base_qty AS product_base_qty,
   bills_of_materials.product_base_qty AS product_target_qty,
   qry_stocks_sum.material_stock_unrestricted AS
   component_stock_unrestricted,
   qry_stocks_sum.material_stock_restricted AS
   component_stock_restricted, qry_stocks_sum.material_stock_qlty_insp AS
   component_stock_qlty_insp, qry_stocks_sum.material_stock_blocked AS
   component_stock_blocked, qry_stocks_sum.material_stock_in_transfer AS
   component_stock_in_transfer, qry_stocks_sum.material_stock_returns AS
   component_stock_returns, qry_stocks_sum.material_stock_total AS
   component_stock_total
FROM bills_of_materials bills_of_materials LEFT JOIN qry_stocks_sum
   qry_stocks_sum ON bills_of_materials.component =
   qry_stocks_sum.material AND bills_of_materials.plant =
   qry_stocks_sum.plant
ORDER BY bills_of_materials.plant, bills_of_materials.product,
   bills_of_materials.component, bills_of_materials.component_description,
   bills_of_materials.component_brutto_qty,
   bills_of_materials.component_brutto_qty_units,
   bills_of_materials.product_base_qty,
   qry_stocks_sum.material_stock_unrestricted,
   qry_stocks_sum.material_stock_restricted,
   qry_stocks_sum.material_stock_qlty_insp,
   qry_stocks_sum.material_stock_blocked,
   qry_stocks_sum.material_stock_in_transfer,
   qry_stocks_sum.material_stock_returns, 
qry_stocks_sum.material_stock_total,

   bills_of_materials.product_base_qty

CREATE VIEW [qry_stocks_sum] AS