ServU 使用SQLite ODBC 和 Sqlite 数据库管理工具 (初探)

参考这篇文章:http://www.51windows.net/data/?url=/data/files/file_524.asp

经实践Serv-U ODBC数据源对Sqlite貌似支持的不太好...

Serv-U官方提供有四种种数据库的ODBC示例MSSQL,MYSQL,Access,Oracle 并提供下载

http://www.serv-u.com/addons.asp

 

我使用的方法比较笨,先在SqlServer 里面使用脚本创建数据库 然后再使用SqliteConvert 从MsSql导出 Sqilte文件

SQlite 数据库脚本:

CREATE TABLE [groupaccounts] (
    "id"        integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    "Index"        integer DEFAULT 0,
    "Name"        nvarchar(50) COLLATE NOCASE DEFAULT '',
    "Notes"        nvarchar(255) COLLATE NOCASE DEFAULT ''

);

CREATE TABLE [groupdiraccess] (
    "id"        integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    "Access"        nvarchar(255) COLLATE NOCASE DEFAULT '',
    "Index"        integer DEFAULT 0,
    "Name"        nvarchar(50) COLLATE NOCASE DEFAULT ''

);

CREATE TABLE [groupipaccess] (
    "id"        integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    "Access"        nvarchar(255) COLLATE NOCASE DEFAULT '',
    "Index"        integer DEFAULT 0,
    "Name"        nvarchar(50) COLLATE NOCASE DEFAULT ''

);

CREATE TABLE [useraccounts] (
    "id"        integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    "Access"        nvarchar(255) COLLATE NOCASE DEFAULT '',
    "AlwaysLogin"        integer DEFAULT 0,
    "ChangePass"        integer DEFAULT 0,
    "Disable"        integer DEFAULT 0,
    "Expirationtype"        integer DEFAULT 0,
    "Expiration"        datetime DEFAULT '1980-1-1',
    "Groups"        nvarchar(50) COLLATE NOCASE DEFAULT '',
    "HideHidden"        integer DEFAULT 0,
    "HomeDir"        nvarchar(100) COLLATE NOCASE DEFAULT '',
    "idleTimeOut"        integer DEFAULT 0,
    "LogMesfile"        nvarchar(100) COLLATE NOCASE DEFAULT '',
    "MaxIp"        integer DEFAULT -1,
    "MaxSpeedDown"        numeric DEFAULT 0,
    "MaxSpeedUp"        numeric DEFAULT 0,
    "MaxUsers"        integer DEFAULT -1,
    "Name"        nvarchar(50) COLLATE NOCASE DEFAULT '',
    "Needsecure"        integer DEFAULT 0,
    "Notes"        nvarchar(255) COLLATE NOCASE DEFAULT '',
    "PassType"        integer DEFAULT 0,
    "Password"        nvarchar(50) COLLATE NOCASE DEFAULT '',
    "Privilege"        integer DEFAULT 0,
    "QuotaCurrent"        numeric DEFAULT 0,
    "QuotaEnable"        integer DEFAULT 0,
    "QuotaMax"        numeric DEFAULT 0,
    "RatioCredit"        numeric DEFAULT 0,
    "RatioDown"        integer DEFAULT 0,
    "RatioType"        integer DEFAULT 0,
    "RatioUP"        integer DEFAULT 0,
    "RelPaths"        integer DEFAULT 0,
    "SessionTimeOut"        integer DEFAULT 0,
    "SkeyValues"        nvarchar(50) COLLATE NOCASE DEFAULT ''

);

CREATE TABLE [userdiraccess] (
    "id"        integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    "Access"        nvarchar(255) COLLATE NOCASE DEFAULT '',
    "Index"        integer DEFAULT 0,
    "Name"        nvarchar(50) COLLATE NOCASE DEFAULT ''

);

CREATE TABLE [useripaccess] (
    "id"        integer PRIMARY KEY AUTOINCREMENT NOT NULL,
    "Access"        nvarchar(255) COLLATE NOCASE DEFAULT '',
    "Index"        integer DEFAULT 0,
    "Name"        nvarchar(50) COLLATE NOCASE DEFAULT ''

);

CREATE INDEX [groupaccounts_Name]
ON [groupaccounts]
([Name]);

CREATE INDEX [groupdiraccess_Name]
ON [groupdiraccess]
([Name]);

CREATE INDEX [groupipaccess_Name]
ON [groupipaccess]
([Name]);

CREATE INDEX [useraccounts_Name]
ON [useraccounts]
([Name]);

CREATE INDEX [userdiraccess_Name]
ON [userdiraccess]
([Name]);

CREATE INDEX [useripaccess_Name]
ON [useripaccess]
([Name]);

Serv-U   ServUDaemon.ini

[GLOBAL]
Version=6.4.0.6
RegistrationKey=TXfGWCPayL66BgSBmxZnzYV1ypfcodp3neEGixGVonfCXiHjRPm6BgSBZOllzYcikIe+w6k36oho8XnwIxDoO0y3
ProcessID=5744
[DOMAINS]
Domain1=192.168.2.50||21|192.168.2.50|1|0|1
[Domain1]
CacheTTL=60
ODBCSource=sqliteftp||
ODBCTables=useraccounts|groupaccounts|userdiraccess|groupdiraccess|useripaccess|groupipaccess
ODBCColumns=Name|Password|SkeyValues|HomeDir|LogMesfile|Access|Disable|Needsecure|RelPaths|HideHidden|AlwaysLogin|ChangePass|QuotaEnable|MaxIp|MaxSpeedUp|MaxSpeedDown|MaxUsers|idleTimeOut|SessionTimeOut|RatioUP|RatioDown|RatioCredit|QuotaCurrent|QuotaMax|Expiration|Privilege|PassType|RatioType|Groups|Notes|Index|Expirationtype|

在网上找了个Sqlite Odbc 工具

Slite ODBC 的配置:

 可以参考安装完Odbc驱动之后默认安装的Sqlite数据源的设置,也就图中其他三个Sqilite ODBC

Sqlite Odbc 使用说明

http://www.ch-werner.de/sqliteodbc/html/index.html

 连接字符串:

Title

  Win32:

    Driver=SQLite ODBC Driver;Database=这里应使用数据库完整路径;...
    Driver=SQLite3 ODBC Driver;Database=x:\abc\abc.db;...

管理工具:

SqliteSpy ..

让人无语..它打开数据库的时候会以独占方式打开,打开之后我的另一个程序不能使用这个数据库了

找了半天问题才知道是工具的原因

SqliteAdmin 和 Firefox 的插件Sqlite manager 则不会出现此问题 ,而且还支持动态更新数据(手动点其他表再切回来),不需要重新打开数据库

如果你有更好的建议,希望提出来大家分享一下